MySQL操作之数据查询语言：(DQL)（四-2）（多表查询）-摩杜云开发者社区

一、主外键特点

主表的主键和从表的外键形成主外关系
从表外键的值是对主表主键的引用。
从表外键类型，必须与主表主键类型一致。
建立外键的表必须是InnDB型，不能是临时表。
外键名不能用引号。FK_ID错误。应为FK_ID。、
添加数据时：从表的外键，只能添加主表主键中存在的数据。
删除数据时：需要先删除从表中与主表关联数据，再删除主表中数据。

1.1 添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键) REFERENCES 主表 (主键);

真实：

ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY (gid) REFERENCES grade (id);

1.2 外键约束

建立外键约束是为了保证数据的完整性和一致性，但是如果主表中数据被删除或修改，从表中数据应该如何？

参数名称	功能描述
CASCADE	删除包含与已删除键值有参照关系的所有记录
SET NULL	修改包括与已删除键值有参照关系的所有记录，使用NULL值替换(不能用于已标记为NOT NULL的字段)
NO ACTION	不进行任何操作
RESTRICT	拒绝主键删除或修改外键关联列。(在不定义ON DELETE 和ON UPDATE子句时，这是默认设置，也是最安全的设置)

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键) REFERENCES 主表 (主键);
	[ON DELETE{
    
      CASCADE | SET NULL | NO ACTION | RESTRICT}]
	[ON UPDATE{
    
      CASCADE | SET NULL | NO ACTION | RESTRICT}]

1.3 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

具体:

ALTER TABLE student DROP FOREIGN KEY FK_ID;

二、一对多操作

1.添加主外键约束

ALTER TABLE 从表名 ADD [constraint 外键约束名称] foreig key 从表(外键) references 主表(主键);

ALTER TABLE product ADD CONSTRAINT fk_cid FOREIGN KEY product(cid) REFERENCES category(cid);

2.添加数据

INSERT INTO product(pid,pname,price,cid) VALUES(1,'冰箱',8000,1);

当主表category，id只有1时，从表product的cid，只能添加值为1的数据。其余报错。

3.删除数据

需要先删除从表product中cid为1的数据，再删除主表category中id为1的数据。

DELETE FROM product WHERE cid =1;
DELETE FROM category WHERE id =1;

三、多对多操作

需要有1张中间表进行关联。

1、创建中间表，给中间表添加两个外键约束
2、创建表、添加数据
订单表和订单项表的主外键关系

alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);

商品表和订单项表的主外键关系

alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid)

向中间表中添加数据

INSERT INTO pro_ord VALUES(2,1);

四、多表查询

INNER JOIN ：用来连接两个表。
ON：用来指定连接条件。

多表查询：

select * from A,B;

隐式内连接

select * from A,B where 条件;
SELECT p.* FROM products p , category c WHERE p.category_id=c.cid AND c.cname = '化妆品';

显式内连接(inner join – inner可以省略)

select * from A inner join B on 条件;

SELECT p.* FROM products p INNER JOIN category c ON p.category_id=c.cid AND c.cname = '化妆品';

左外连接（left outer join）

返回左表(A)中全部数据，以及右表(B)中符合条件数据。

select * from A left outer join B on 条件;

右外连接：（right outer join）

返回右表(B)中全部数据，以及左表(A)中符合条件数据。

select * from A right outer join B on 条件;

交叉连接：

SELECT * FROM A CROSS JOIN B;

查询结果为AxB的总数据量(2x3)。
查询结果为所有数据的集合。

自关联查询

查询王红所在部门的联系人列表

SELECT * FROM  empolyee p1 JOIN empolyee p2 ON p1.did=p2.did WHERE p2.name='王红';

MySQL操作之数据查询语言：(DQL)（四-2）（多表查询）

五、子查询

5.1 带IN 关键字的子查询

使用IN关键字进行子查询时，内层查询返回一个数据列，这个数据列中值将供外层查询语句作为查询条件比较。
可以使用IN和NOT IN。

查询年龄age为20岁的员工的部门

SELECT * FROM department WHERE did IN(SELECT did FROM employee WHERE age=20);

5.2 带 EXISTS 关键字的子查询

关键字后面的参数可以是任何一子查询，子查询只用于测试，不产生数据。只返回true和false。
当返回值为TRUE时，外层查询才会执行。
EXISTS关键字比IN运行效率高

SELECT * FROM department WHERE EXISTS(select did from employee where age >21);

5.3 带 ANY 关键字的子查询

满足其中任意一个条件，就将查询结果返回。
当any中任意一条数据满足department中did>其中值，则被查询出来。

SELECT * FROM department WHERE did > any(select did from employee);

5.4 带 ALL 关键字的子查询

满足所有任意一个条件，就将查询结果返回。
当any中任意一条数据满足department中 did等于其中值(select did from employee)，则被查询出来。

SELECT * FROM department WHERE did = all(select did from employee);

5.5 带比较运算符的子查询

包括：>、<、>=、<=、=、!=

SELECT * FROM department WHERE did = (select did from employee where name='赵四');

5.6 其它案例

一条select语句结果作为另一条select语法一部分

select ....查询字段 ... from ... 表.. where ... 查询条件

隐式内连接（查询“化妆品”分类上架商品详情）

SELECT p.* FROM products p , category c WHERE p.category_id=c.cid AND c.cname = '化妆品'

作为查询条件

SELECT *FROM products p WHERE p.category_id =(SELECT c.cid FROM category cWHERE c.cname='化妆品');

作为另一张表

SELECT * FROM products p ,
(SELECT * FROM category WHERE cname='化妆品') c WHERE p.category_id = c.cid

子条件查询，可以作为查询的条件，也就是查询出来的结果是一个id。或者是名称；
作为另一张表。也就是把查询的结果作为另一张表使用；