MySQL入门篇：约束（主键，外键），多表查询（连接查询，联合查询，子查询）

1.约束

①概念:约束是作用于表中字段上的规则，用于限制存储在表中的数据。
②目的:保证数据库中数据的正确、有效性和完整性。

1.约束分类

注意:约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

2.演示

需求:

代码：

create table user
(
    id     int primary key auto_increment,
    name   varchar(10) not null unique,
    age    int check ( age > 0 and age < 120 ),
    status char(1) default '1',
    gender char(1)
);
-- 插入数据
insert into user(name, age, status, gender)
values ('Tom', 19, '1', '男'),
       ('Jack', 25, '2', '男');

3.外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

1.语法

1.添加外键：

CREATE TABLE 表名(
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

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

2.删除外键：

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

2.删除/更新行为

在添加外键的基础语法上增加：

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

若需要采用其他删除更新策略时，将CASCADE修改成对应的语法即可。

2.多表查询

①概述:指从多张表中查询数据
②笛卡尔积:是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

1.多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:
①—对多(多对一)
实现:在多的一方建立外键，指向一的一方的主键
②多对多
实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
③—对一
多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现:在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

2.多表查询分类

1.连接查询

1.内连接

内连接查询的是两张表交集的部分。

1.隐式内连接：

SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

2.显示内连接：

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

2.外连接

1.左外连接

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

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

2.右外连接

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

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

3.自连接

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

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

2.联合查询(union)

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
①对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
②union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

SELECT 字段列表 FROM 表A...
UNION[ALL]
SELECT 字段列表 FROM 表B...;

3.子查询

概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
子查询外部的语句可以是INSERT /UPDATE/DELETE/ SELECT的任何一个。

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

1.标量子查询

子查询返回的结果是单个值（数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。
常用的操作符:= <> > >= < <=

2.列子查询

子查询返回的结果是一列（可以是多行)，这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、ANY 、SOME 、 ALL

3.行子查询

子查询返回的结果是一行（可以是多列)，这种子查询称为行子查询。
常用的操作符:= > IN NOT IN

4.表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符:IN