SQL-约束-多表查询

目录

约束

分类

外键约束

添加外键

删除外键

删除/更新行为

多表查询

多表关系

一对多（多对一）

多对多

一对一

多表查询概述

多表查询分类

连接查询

内连接：查询A、B交集部分数据

外连接：

自连接：可以内连接查询，也可以是外连接查询

联合查询-Union，union all

子查询（子查询位置：WHERE之后、FROM之后、SELECT之后）

约束

分类

NOT NULL 非空约束 

UNIQUE  唯一约束

PRIMARY KEY 主键约束

DEFAULT 默认约束

CHECK 检查约束（8.0.16版本之后）保证字段值满足某一个条件

FOREIGN KEY 外键约束

AUTO_INCREMENT自动增长

外键约束

添加外键

CREATE TABLE 表名（

          字段名 数据类型，

           ...

          [CONSTRAINT] [外键名称]FOREIGN KEY (外键字段名) REFERENCES 主表（主表列名）

）

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

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称；

删除/更新行为

NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。与（RESTRICT一致）

RESTRICT  当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。与（RESTRICT一致）

CASCADE  当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。

SET NULL  当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。

SET DEFAULT  父表有变更时，子表将外键列设置成一个默认的值（lnnodb不支持）

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表（主表列名）ON UPDATE CASCADE ON DELETE CASCADE；

多表查询

多表关系

一对多（多对一）

部门与员工

多对多

学生和课堂（建立第三张中间表，中间表至少包含两个外键，分别关联两方主键）

一对一

用户与用户详情的关系（在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的UNIQUE）

多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

多表查询概述

SELEC *from table1,table2

多表查询分类

连接查询

内连接：查询A、B交集部分数据

隐式内连接：SELECT 字段列表 FROM 表1，表2 WHERE 条件...；

先做笛卡尔积，然后过滤。

显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

先匹配链接条件，再返回。

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...

右外连接：查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...

自连接：可以内连接查询，也可以是外连接查询

当前表与自身的连接查询，自连接必须使用表别名

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

联合查询-Union，union all

SELECT 字段列表 FROM 表A...

UNION [ALL]

SELECT 字段列表 FROM 表B ...;

子查询（子查询位置：WHERE之后、FROM之后、SELECT之后）

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

1) 标量子查询

返回结果为单个值（数字、字符串、日期等）

常用操作符：=、<>、>、>=、<、<=

2) 列子子查询

返回结果为一列（多行）

常用操作符：IN、NOT IN、ANY、SOME、ALL

3) 行子查询

返回结果为一行（多列）

常用操作符：=、<>、IN、NOT IN

4)表子查询

返回结果为多行多列

常用操作符：=、<>、IN、NOT IN