本文深入探讨MySQL的外键约束，讲解其作用、语法及级联操作。接着，文章重点介绍多表操作，包括一对一、一对多、多对多关系的建表原则，以及连接查询（内连接、外连接）、子查询和自关联查询的用法，通过实例解析各种查询方式的应用。
【第一章】约束【重点】
外键约束
问题：什么是外键约束以及外键约束的作用是什么？
当一个表中需要使用到另一个表的的数据时，但是又要保证数据的正确性，可以是用外键约束。如下图。
外键约束语法
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
添加外键
建表时添加外键
-- 创建user用户表(主表)
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
NAME VARCHAR(20) NOT NULL -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
-- 创建orderlist订单表(从表)
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
number VARCHAR(20) NOT NULL, -- 订单编号
uid INT, -- 外键列
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2);
建表后添加外键
-- 语法
alter table 表名 add constraint 外键名称 foreign key(外键列) references 主表(主键);
-- orderlist中的uid关联user表中的id列
alter table orderlist add constraint fk foreign key(uid) references user(id);
删除外键
-- 语法
alter table 表名 drop foreign key 外键名称;
-- 删除orderlist表中的外键
alter table orderlist drop foreign key ou_fk1;
外键级联操作(了解)
外键让多个表之间产生了关系，但是如果主表中的数据所有改变，从表中关联的数据也必须跟着改变。这叫做外键级联操作。
-- 添加外键级联更新、级联删除
-- ON UPDATE CASCADE 级联更新
-- ON DELETE CASCADE 级联删除
ALTER TABLE 从表 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列) REFERENCES 主表(主键列) ON UPDATE CASCADE ON DELETE CASCADE;
【第二章】多表操作【重点】
多表关系
问题1：多表关系有哪几种？
问题2：思考：学生和班级属于哪一种？商品和订单属于哪一种？
在实际项目开发的时候可能设计到多张表，表与表之间存在一定关系，如何设计表之间的关系让系统更加优化，需要考虑到多表之间关系，学习多表关系主要就是学习多表的建表原则。
1. 一对一(了解)
* 如：人和身份证
* 分析：一个人只有一个身份证，一个身份证只能对应一个人
2. 一对多(多对一)
* 如：部门和员工
* 分析：一个部门有多个员工，一个员工只能对应一个部门
3. 多对多
* 如：学生和课程
* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
一对一关系
问题：一对一关系中如何添加外键？
一个人有一个身份证号码，一个身份证号码只对应一个人
-- 创建身份证表
CREATE TABLE card(
id INT PRIMARY KEY,
number VARCHAR(18)
);
-- 设置身份证号码唯一
ALTER TABLE card MODIFY number VARCHAR(20) UNIQUE;
INSERT INTO card(id,number) VALUES(1,420923199211090512);
INSERT INTO card(id,number) VALUES(2,420923199310230807);
INSERT INTO card(id,number) VALUES(3,420923199007080908);
-- 创建人员表
CREATE TABLE person(
id INT PRIMARY KEY,
NAME VARCHAR(20),
cid INT
);
-- 给人员表添加外键
ALTER TABLE person ADD CONSTRAINT cid_kf FOREIGN KEY(cid) REFERENCES card(id);
-- 设置外键唯一
ALTER TABLE person MODIFY cid INT UNIQUE;
-- 向person表中添加数据
INSERT INTO person(id,NAME,cid) VALUES(1,"张三",1);
INSERT INTO person(id,NAME,cid) VALUES(2,"李四",2);
INSERT INTO person(id,NAME,cid) VALUES(3,"王五",3);
==建表原则：在任意一个表建立唯一(UNIQUE)外键，去关联另外一个表的主键。==
一对多关系
问题：一对多关系中如何添加外键？
一个部门可以有多个员工，一个员工只能对应一个部门
-- 创建部门表(主表)
CREATE TABLE department(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
-- 向部门表中添加数据
INSERT INTO department(id,NAME) VALUES(1,"教研部");
INSERT INTO department(id,NAME) VALUES(2,"学工部");
INSERT INTO department(id,NAME) VALUES(3,"就业部");
SELECT * FROM department;
-- 创建员工表(从表)
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
age INT,
dep_id INT,
CONSTRAINT emp_depid_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);
-- 向员工表中添加数据
INSERT INTO employee(NAME,age,dep_id) VALUES("张三",18,1);
INSERT INTO employee(NAME,age,dep_id) VALUES("李四",20,2);
INSERT INTO employee(NAME,age,dep_id) VALUES("王五",21,1);
INSERT INTO employee(NAME,age,dep_id) VALUES("赵六",19,3);
INSERT INTO employee(NAME,age,dep_id) VALUES("田七",24,2);
INSERT INTO employee(NAME,age,dep_id) VALUES("周八",23,3);
INSERT INTO employee(NAME,age,dep_id) VALUES("黄九",25,1);
SELECT * FROM employee;
==建表原则：在多的一方建立外键约束指向一的一方主键。==
多对多关系
问题：多对多关系中如何添加外键？
一个学生可以选择很多门课程，一个课程也可以被很多学生选择
-- 创建学生表
CREATE TABLE student(
sid INT PRIMARY KEY,
NAME VARCHAR(20),
age INT
);
INSERT INTO student(sid,NAME,age) VALUES(1,"张三",20);
INSERT INTO student(sid,NAME,age) VALUES(2,"李四",19);
INSERT INTO student(sid,NAME,age) VALUES(3,"王五",18);
INSERT INTO student(sid,NAME,age) VALUES(4,"赵六",21);
INSERT INTO student(sid,NAME,age) VALUES(5,"田七",24);
INSERT INTO student(sid,NAME,age) VALUES(6,"周八",23);
-- 创建课程表
CREATE TABLE class(
cid INT PRIMARY KEY,
NAME VARCHAR(20)
);
INSERT INTO class(cid,NAME) VALUES(1,"Java基础");
INSERT INTO class(cid,NAME) VALUES(2,"HTML");
INSERT INTO class(cid,NAME) VALUES(3,"Javascript");
INSERT INTO class(cid,NAME) VALUES(4,"mysql");
-- 创建外键表
CREATE TABLE foreign_table(
sid INT,
cid INT,
CONSTRAINT sid_fk FOREIGN KEY(sid) REFERENCES student(sid), -- 外键sid 关联 student sid主键
CONSTRAINT cid_fk FOREIGN KEY(cid) REFERENCES class(cid) -- 外键cid 关联 class cid主键
);
INSERT INTO foreign_table(sid,cid) VALUES(1,1);
INSERT INTO foreign_table(sid,cid) VALUES(1,2);
INSERT INTO foreign_table(sid,cid) VALUES(1,3);
INSERT INTO foreign_table(sid,cid) VALUES(1,4);
INSERT INTO foreign_table(sid,cid) VALUES(2,1);
INSERT INTO foreign_table(sid,cid) VALUES(2,2);
INSERT INTO foreign_table(sid,cid) VALUES(3,4);
INSERT INTO foreign_table(sid,cid) VALUES(3,1);
==建表原则：创建一张中间表，在中间表中至少有两列作为外键分别指向多对多双方的主键。==
建表原则总结
一对一建表原则：在任意一方添加唯一外键指向另一方的主键字段或者唯一非空字段
一对多/多对一建表原则：在多的一方添加外键指向一的一方主键字段或者唯一非空字段。
多对多建表原则：创建一张中间表，在中间表中至少有两个字段作为外键分别指向多对多双方的主键。
多表查询
准备数据
-- 创建db4数据库
CREATE DATABASE db4;
-- 使用db4数据库
USE db4;
-- 创建user表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id
NAME VARCHAR(20), -- 用户姓名
age INT -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);
-- 订单表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- 订单id
number VARCHAR(30), -- 订单编号
uid INT, -- 外键字段
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);
-- 商品分类表
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品分类id
NAME VARCHAR(10) -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');
-- 商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id
NAME VARCHAR(30), -- 商品名称
cid INT, -- 外键字段
CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);
-- 中间表
CREATE TABLE us_pro(
upid INT PRIMARY KEY AUTO_INCREMENT, -- 中间表id
uid INT, -- 外键字段。需要和用户表的主键产生关联
pid INT, -- 外键字段。需要和商品表的主键产生关联
CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);
连接查询
内连接查询
语法
-- 显示内连接查询【了解】，中括号中的内容表示可以省略
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件 [where 其他条件];
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件 [INNER] JOIN 表名3 ON 关联条件 ... [where 其他条件];
-- 隐式内连接查询
SELECT 列名 FROM 表名1,表名2,... WHERE 关联条件或者其他条件;
查询原理
内连接查询的是两张表有交集的部分数据(有主外键关联的数据)。
举例
-- 查询用户信息和对应的订单信息，使用显示内连接
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
-- 查询用户信息和对应的订单信息，使用隐式内连接
select * from user,orderlist where user.id=orderlist.uid;
-- 查询用户姓名、用户年龄和对应的订单编号。起别名
select -- 查询的列
u.name,
u.age,
o.number
from -- 查询的表
user u,
orderlist o
where -- 查询的条件
u.id=o.uid;
外连接查询
语法
-- 左外连接查询：左表中所有的数据，以及左右表交集的数据
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件 [where 其他条件];
-- 右外连接查询：右边中所有的数据，以及左右表交集的数据
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件 [where 其他条件];
左外连接查询原理
查询左表的全部数据，和左右两张表有交集部分的数据。
右外连接查询原理
查询右表的全部数据，和左右两张表有交集部分的数据。
举例
-- 查询所有用户信息，以及用户对应的订单信息，使用左外连接
SELECT
u.*,
o.number
FROM
USER u
LEFT OUTER JOIN
orderlist o
ON
o.uid=u.id;
-- 查询所有订单信息，以及订单所属的用户信息，使用右外连接
SELECT
u.*,
o.number
FROM
USER u
RIGHT OUTER JOIN
orderlist o
ON
o.uid=u.id;
子查询
概念
查询语句中嵌套了查询语句，我们就将嵌套的查询称为子查询。
换一句话说：将一个查询的结果作为另一个查询的条件或者虚拟表连接，这样的查询就叫做子查询。
子查询的结果是单行单列
使用场景：子查询的结果是一行一列，可以将子查询的结果当做某一个字段的值，作为where子句的条件；
-- 查询用户最大的年龄
SELECT MAX(age) FROM USER;
-- 查询年龄最大的用户姓名
select name ,age from user where age=(select max(age) from user);
子查询的结果是多行单列
使用场景：子查询的结果是多行单列，可以将子查询的结果当做某一个字段的多个值，作为where子句的条件。
-- 查询张三和李四的id
select id from user where name in('张三','李四');
-- 查询张三和李四的订单信息
select * from orderlist where uid in (select id from user where name in('张三','李四'));
子查询的结果是多行多列
使用场景：子查询的结果是多行多列，可以将子查询的结果当做一个表来看待，并和其他表进行连接查询。
-- 查询订单表中id>4的订单信息
select * from orderlist where id>4;
-- 查询订单表中id大于4的订单信息和所属用户信息
select u.name,o.number from user u ,(select * from orderlist where id>4) o where o.uid=u.id;
==总结：子查询可以作为条件也可以作为虚拟表和其他表连接查询，如果是作为条件，那么子查询结果一定是单列的，如果子查询结果有多列，那么必须作为虚拟表和其他表连接查询。==
自关联查询
概念
在同一张表中数据有关联性，我们可以把这张表当成多个表来查询。
准备数据
-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(20), -- 员工姓名
mgr INT, -- 上级编号
salary DOUBLE -- 员工工资
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);
-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析
员工信息 employee表
条件：employee.mgr = employee.id
查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
SELECT
e1.id,
e1.name,
e1.mgr,
e2.id,
e2.name
FROM
employee e1
LEFT OUTER JOIN
employee e2
ON
e1.mgr = e2.id;
多表查询练习
查询用户的编号、姓名、年龄。订单编号
/*
分析：
查询的表：用户表(user),订单表(orderlist)
查询的字段：用户编号,姓名,年龄,订单编号
查询的条件：user.id=orderlist.uid
*/
select
u.id,u.name,u.age,o.number
from
user u,orderlist o
where
u.id=o.uid;
查询所有的用户。用户的编号、姓名、年龄。订单编号
/*
分析：
查询的表：用户表(user),订单表(orderlist)。 所有用户，左外连接
查询的字段：用户编号,姓名,年龄,订单编号
查询的条件：orderlist.uid = user.id
*/
select
u.id,u.name,u.age,o.number
from
user u
left join
orderlist o
on
u.id=o.uid;
查询所有的订单。用户的编号、姓名、年龄。订单编号
/*
分析：
查询的表：用户表(user),订单表(orderlist)。所有订单，忧外连接
查询的字段：用户的编号、姓名、年龄、订单编号
查询的条件：orderlist.uid = user.id
*/
select
u.id,u.name,u.age,o.number
from
user u
right join
orderlist o
on
o.uid=u.id;
查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析：
查询的表：用户表(user),订单表(orderlist)
查询的字段：用户的编号、姓名、年龄。订单编号
查询的条件：user.id=orderlist.uid and user.age>23
*/
select
u.id,u.name,u.age,o.number
from
user u,orderlist o
where
u.id=o.uid and u.age>23;
查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析：
查询的表：用户表(user),订单表(orderlist)
查询的字段：显示用户的编号、姓名、年龄。订单编号
查询的条件：user.name in ("张三","李四") and user.id=orderlist.uid;
*/
select
u.id,u.name,u.age,o.number
from
user u,
orderlist o
where
u.id=o.uid and u.name in ("张三","李四");
查询商品分类的编号、分类名称。分类下的商品名称
/*
分析：
查询的表：商品表(product)、分类表(category)
查询的字段：商品分类的编号、分类名称、商品名称
查询的条件：product.cid=category.id
*/
select
c.id,c.name,p.name
from
product p,
category c
where
p.cid=c.id;
查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*
分析：
查询的表：商品表(product)、分类表(category)
查询的字段：商品分类的编号(id)、分类名称(name)、商品名称(name)
查询的条件：product.cid=category.id
*/
select
c.id,c.name,p.name
from
product p
right join
category c
on
p.cid=c.id;
查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
/*
分析：
查询的表：商品表(product)、分类表(category)
查询的字段：商品分类的编号(id)、分类名称(name)、商品名称(name)
查询的条件：product.cid=category.id
*/
select
c.id,c.name,p.name
from
product p
left join
category c
on
p.cid=c.id;
查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析：
查询的表：用户表(user),商品表(product),中间表(us_pro)
查询的字段：用户的编号、姓名、年龄。商品名称
查询的条件：user.id=us_pro.uid and product.id=us_pro.pid;
*/
select
u.id,u.name,u.age,p.name
from
user u,
product p,
us_pro up
where
u.id=up.uid and p.id=up.pid;
查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析：
查询的表：用户表(user),商品表(product),中间表(us_pro)
查询的字段：用户的编号、姓名、年龄。商品名称
查询的条件：
user.name in ("张三","李四")
and us_pro.uid=user.id;
and us_pro.pid=product.id;
*/
select
u.id,u.name,u.age,p.name
from
user u,product p,us_pro up
where
u.name in ("张三","李四") and up.uid=u.id and up.pid=p.id;
总结
1 约束:对表中的数据进行限制，保证数据的正确性、有效性。
单表约束：
主键约束：主键字段默认非空且唯一，一个表中只能有一个主键，一般主键作为一条数据的唯一标识
主键自增约束：一般是给主键字段添加自增，自增字段的类型必须是整型。
唯一约束：保证某列数据不重复
非空约束：保证某列数据没有null值
核心：创建表时添加约束
create table 表名(
id int primary key auto_increment,
列2 值2 unique not null ...,
...
列n 值n 约束 约束 ...
);
create table 表名(
id int auto_increment,
列2 值2 unique not null ...,
...
列n 值n 约束 约束 ...,
primary key(id)
);
多表约束：
外键约束：当一个表(A表)中的某个字段值都来自于另一张表(B表)的主键时，那么就需要约束A表的字段的值，这个约束就叫做外键约束。
语法：
create table 表名(
id int auto_increment,
列2 值2 unique not null ...,
...
列n 值n 约束 约束 ...,
CONSTRAINT 外键名称 FOREIGN KEY(列名) REFERENCES 主表(主键)
);
2 多表关系以及建表原则(添加外键)
一对一：在任意一方添加一个外键指向另一方的主键，但是外键还必须添加唯一约束(unique)
一对多/多对一：在多的一方添加外键指向一的一方的主键。
多对多：创建一张中间表，中间表中至少有两个字段作为外键分别指向多对多双方的主键。
3 多表查询
3.1 内连接
语法：
显示内连接：
select * from 表1 [inner] join 表2 on 关联条件 [where 其他条件]
select * from 表1 [inner] join 表2 on 关联条件 [inner] join 表3 on 关联条件 ... [where 其他条件]
隐式内连接：
select * from 表1,表2,... where 关联条件或者其他条件
查询结果：多张表的交集数据，也就是主外键能连接的数据。
3.2 外连接
语法：
左外连接：
select * from 表1 left [outer] join 表2 on 关联条件 [where 其他条件]
右外连接：
select * from 表1 right [outer] join 表2 on 关联条件 [where 其他条件]
查询结果：
左外连接：查询左边表(前面的表)的全部数据，加上和右边表的交集数据。
右外连接：查询右边表(后面的表)的全部数据，加上和左边表的交集数据。
3.3 子查询
概念：查询中嵌套查询，将一个查询的结果作为另一个查询的条件或者虚拟表进行连接，就叫做子查询。
语法：
子查询作为条件:子查询结果必须是单列的
select * from 表 where 列名=(子查询); -- 子查询结果是单行单列的
select * from 表 where 列名 in (子查询); -- 子查询结果是多行单列的
子查询作为虚拟表：子查询结果可以是多列的
select * from 表1,(子查询) where 条件
3.4 自关联查询
概念：表自己关联自己，可以使用内连接、外连接。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
