多表操作

外键

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

首先，创建两个名为student和class的表并给表添加数据

（student表中的class_id是引入了class表中的主键id，从而建立了两个表数据之间的连接。即class_id作为student表的外键）

（被引用的表class是主表，引用外键的表student是从表，两表是主从关系）

（注意：建立的表都必须是INNODB型，不能是临时表，不然不能使用外键）

 

 

 

（注意：引入外键后，外键列只能插入参照列存在的值，参照列被参照的值不能被删除，保证了数据的参照完整性）

（例：下图class表中的id被student表中的class_id引用，那么class表中的id列就不能被删除。如果将student表中class_id列的1913都改为1923或者把1913的那几条学生信息都删掉，那class表中id列的1913那一行就可以成功删除了。可以用语句DELETE FROM class WHERE 条件；进行删除）

 

 

②为表添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 外表表名（主键字段名）；

(注意：定义外键名时，不能加引号。如：constraint 'FK_ID' 或 constraint " FK_ID "都是错误的)

 

 

添加外键约束的参数说明

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 外表表名（主键字段名）；

【ON DELETE {CASCADE或SET NULL或NO ACTION或RESTRICT }】

【ON UPDATA {CASCADE或SET NULL或NO ACTION或RESTRICT }】

 

③删除外键约束

ALTER TABLE表名DROP FOREIGN KEY 外键名；

 

关联关系

①多对一

比如，学生与班级之间的关系，一个班级可以有多个学生，而一个学生不能属于多个班级，也就是说班级表中的一行在学生表中可以有许多匹配行，但学生表中的一行在班级表中只能有一个匹配行。

在多对一的表关系中，应该将外键建在多的一方，否则会造成数据的冗余。

②多对多

比如学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程也供多个学生选择，也就是说学生表中的一行在课程表中可以有许多匹配行，课程表中的一行在学生表中也有许多匹配行。

通常情况下，为了实现这种关系需要定义一张中间表（称为连接表），该表会存在两个外键。连接表的两个外键都是可以重复的，但是两个外键之间的关系是不能重复的，所以这两个外键又是连接表的联合主键。

③一对一

比如人与身份证之间就是一对一的关系，一个人对应一张身份证，一张身份证只能匹配一个人。

一对一关系的两张表在建立外键时，首先要分清主从关系。从表需要主表的存在才有意义，就像身份证需要人的存在才有意义。因此人为主表，身份证为从表，要在身份证表中建立外键。由实际经验可知，身份证中的外键必须是非空唯一的，因此通常会直接用从表中的主键作为外键。

在实际开发中，一对一关联关系可以应用于：分割具有很多列的表、由于安全原因而隔离表的一部分、保存临时的数据，并且可以毫不费力地通过删除该表而删除这些数据。

连接查询

①交叉连接

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。例如department表中有4个部门，employee表中有4个员工，则交叉连接的结果就有4*4=16条数据。

 

 

 

SELECT * from 表1 CROSS JOIN 表2；

 

②内连接又称简单连接或自然连接

内连接使用比较运算符对两个表中的数据进行比较，并列出与连接条件匹配的数据行，组合成新的记录。

SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段；

例：在department表和employee表之间使用内连接查询

 

例：在department表和employee表之间使用WHERE

 

例：在department表和employee表之间使用自连接查询

 

③外连接分为左连接和右连接

SELECT 所查字段 FROM 表1（左表） LEFT或RIGHT【OUTER】JOIN 表2（右表） ON 表1.关系字段=表2.关系字段 WHERE 条件；

LEFT JOIN（左连接）：左连接的结果包括LEFT JOIN子句中指定的左表的所有记录，和所有满足连接条件的记录。如果左表的某条记录在右表中不存在，则在右表中显示为空。

RIGHT JOIN（右连接）：右连接与左连接正好相反，返回右表中所有指定的记录和所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。

例：在department表和employee表之间使用左连接查询

 

例：在department表和employee表之间使用右连接查询

 

④复合条件连接查询

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

例：在department表和employee表之间使用内连接查询并将查询结果按照年龄的升序排序

 

⑤子查询

1.带IN关键字的子查询

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

例：查询存在年龄为20岁的员工的部门

 

例：查询不存在年龄为20岁的员工的部门

 

2.带EXISTS关键字的子查询

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

例：查询employee表中是否存在年龄大于21岁的员工，如果存在，则查询department表中的所有记录

 

3.带ANY关键字的子查询

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

例：使用带ANY关键字的子查询，查询满足条件的部门

 

4.带ALL关键字的子查询

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

例：使用带ALL关键字的子查询，查询满足条件的部门。

 

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

例：使用带比较运算符的子查询，查询赵四是哪个部门的员工