MySQL 多表操作

一、外键

1、 什么是外键

外键是指引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。

2、为表添加外键约束

语句格式：alter table 表名 add constraint FK_ID foreign key （外键字段名）REFERENCES 外表表名（主键字段名）;
（1）创建班级表：

（2）创建学生表：

（3）为表 student 添加外键约束：

主表中的数据被删除或修改，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据，可以在建立外键时添加 ON DELETE 或 ON UPDATE 子句来告诉数据库，怎样避免垃圾数据的产生。
语法格式如下：

alter table 表名 add constraint FK_ID foreign key （外键字段名）REFERENCES 外表表名（主键字段名）;
[ ON DELETE { CASCADE | SET NULL | NO ACTION | RESTRICT } ]
[ ON UPDATE { CASCADE | SET NULL | NO ACTION | RESTRICT } ]

参数名称	功能描述
CASCADE	删除包含与已删除键值有参照关系的所有记录
SET NULL	修改包含与已删除键值有参照关系的所有记录，使用 NULL 值替换
NO ACTION	不进行任何操作
RESTRICT	拒绝主表删除或修改外键关联列

3、删除外键约束

语法格式：alter table 表名 drop foreign key 外键名;

二、操作关联表

1、 关联关系

（1）多对一关系（最常见的）；
（2）多对多关系；
（3）一对一关系。

2、添加数据

实例：在表 student 和表 grade 中添加外键约束来建立两个表的关联关系（多对一）。

alter table 表名 add constraint FK_ID foreign key （gid）REFERENCES grade（id）;

如果要为两个表添加数据，就需要为主表 grade 添加数据，具体语句如下：

INSERT INTO grade(id,name) VALUES(1,'物联一班'）；
INSERT INTO grade(id,name) VALUES(2,'物联二班'）；

在上述语句中添加的主键 id 为1和2，因此 gid 值只能是1或2 。

INSERT INTO student (sid,sname,gid) VALUES(1,'王浩',1）；
INSERT INTO student (sid,sname,gid) VALUES(2,'李莉',1）；
INSERT INTO student (sid,sname,gid) VALUES(3,'郝赫',2）；
INSERT INTO student (sid,sname,gid) VALUES(4,'赵谢',2）；

查询数据用 select ，删除数据用 delete 。

三、连接查询

1、交叉连接

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积，也就是返回第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
语法格式：select * from 表 1 cross join 表 2;
交叉查询的结果：

2、内连接

内连接(nmer Join)又称简单连接或自然连接，是一种常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较，并列出与连接条件匹配的数据行，组合成新的记录，也就是说在内连接查询中，只有满足条件的记录才能出现在查询结果中。
内连接查询的语法格式:SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段;
INNER JOIN 用于连接两个表，ON 来指定连接条件，其中INNER可以省略。

从上述结果可以看出，只有 department.did 与 employee.did 相等的员工才会被显示。也可以使用whee条件语句来实现同样的功能。

3、外连接

外连接分为左连接和右连接。
外连接的语法格式如下:

SELECT 所在字段 FRON 表 1 LEFT | RIGHT | OUTER JOIN 表2
ON 表1.关系字段 = 表2.关系字段 WHERE条件;

外连接的语法格式和内连接类似，只不过使用的是LEFT JOIN、RIGHT JOIN关键字，其中关键字左边的表被称为左表，关键字右边的表被称为右表。在使用左连接和右连接查询时，查询结果是不一致的具体如下。
(1) LEFT JOIN(左连接)：返回包括左表中的所有记录和右表中符合连接条件的记录。
(2) RIGHT JOIN(右连接)：返回包括右表中的所有记录和左表中符合连接条件的记录。

实例：LEFT JOIN(左连接)

实例：RIGHT JOIN(右连接)

4、复合条件连接查询

复合条件连接查询就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。

从上述结果可以看出，使用复合条件在询的结果更加精确，符合实际需求。

四、子查询

子查询是指个查询语句嵌套在另一个查询语句内部的查询。它可以嵌套在一个SELECT、SELECT···INTO 语句、INSERT···INTO 等语句中。在执行查询语句时，首先会执行子查询中的语句，然后将返回的结果作为外层查询的过滤条件，在子查询中通常可以使用 IN、EXISTS、ANY、ALL操作符。

1、带 IN 关键字的子查询

使用IN关键字进行子查询时，内层查询语句仅返回一个数据列，这个数据列中的值将供外层查询语句进行比较操作。

在查询的过程中，首先会执行内层子查询，得到年龄为20岁的员工的部门 id，然后根据部门 id 与外层查询的比较条件，最终得到符合条件的数据。SELECT 语句中还可以使用 NOTIN关键字，其作用正好与N相反。

2、带 EXISTS 关键字的子查询

EXISTS 关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不产生任何数据，只返回 TRUE 或 FALSE，当返回值为TRUE时，外层查询才会执行。

EXISTS 关键字比 IN 关健字的运行效率高，所以在实际开发中，特别是大数据量时，推存使用EXISTS 关键字。

3、带 ANY 关键字的子查询

ANY 关键字表示满足其中任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

首先子查询会将 employee 表中的所有 did 查询出来，分别为1、1、2、4，然后将 department 表中 did 的值与之进行比较，只要大于 employee.did中的任意一个值，就是符合条件的查询结果，由于 department 表中的媒体部、研发部、人事部的 did 都大于employee 表中的 did(did=1)，因此输出结果为媒体部、研发部和人事部。

4、带 ALL 关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子查询返回的结果需同时满足所有内层查询条件。

5、带比较运算符的子查询

子查询中还可以使用其他的比较运算符，如“<”、“>=”、“=”、“！=”等。