MySQL高级—约束与关系
🍎道阻且长，行则将至。🍓
目录
一、约束
1.约束的基本概念
2.分类
二、关系
1.一对多
2.多对多
3.一对一
三、多表查询
1.多查
2.连接查询
3.子查询
一、约束
1.约束的基本概念
数据库的表示用来存储数据的，为了保证表数据的正确性、有效性和完整性，我们设置一种列的规则用于限制加入表的数据，这称之为约束。
例如对一张学生表如果不加以约束就会出现各种奇奇怪怪的数据。
2.分类
-
非空约束： 关键字是 NOT NULL
保证列中所有的数据不能有null值。
用法：
-- 创建表时添加非空约束
CREATE TABLE 表名(列名 数据类型 NOT NULL,…
);
-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
-- 删除约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;-
唯一约束：关键字是 UNIQUE
保证列中所有数据各不相同。
-- 创建表时添加唯一约束，两种方法
CREATE TABLE 表名(列名 数据类型 UNIQUE [AUTO_INCREMENT],-- AUTO_INCREMENT: 值自动增长…
);
CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [约束名称] UNIQUE(列名)
);
-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
-- 删除约束
ALTER TABLE 表名 DROP INDEX 字段名;-
主键约束： 关键字是 PRIMARY KEY
主键是一行数据的唯一标识，非空且唯一。一个表只能有一个主键来唯一标识数据。例如：id作为主键，来标识每条数据。
-- 创建表时添加主键约束
CREATE TABLE 表名(列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],…
);
CREATE TABLE 表名(列名 数据类型,[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);
-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
-- 删除约束
ALTER TABLE 表名 DROP PRIMARY KEY;-
检查约束： 关键字是 CHECK
保证列中的值满足某条件。【MySQL不支持 】
例如：我们可以给age列添加一个范围，1-200，这样增加的数据才更合理。
-
默认约束： 关键字是 DEFAULT
给表里加数据时，没有指定值则采用默认值。
例如：我们在给成绩列添加约束默认值是0，这样在添加数据时没有指定具体值时就会采用默认给定的0。
-- 创建表时添加默认约束
CREATE TABLE 表名(列名 数据类型 DEFAULT 默认值,…
);
-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
-- 删除约束
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;-
外键约束： 关键字是 FOREIGN KEY
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。简单来说就是两个表之间存在一种对应或者匹配关系。
例如如下两个表：员工表和部门表，
一个部门可以有多名员工，就是一种一对多关系。当这两个表建立外键约束之后，数据就不能任意修改了，例如删除部门表的第二条数据，或者修改员工张三的dep_id=3；在Navicat里面我们也可以通过逆向表到模型来直观看到这种关系，
从模型可以看到两个表通过一个键链接起来了，并且连接线有不同的接头代表多和一。
创建外键约束的时候谁是多就对应修改谁的表，外键名称可以随意起名，一般：fk_表名_主表名，设置表的一个匹配列为外键，目标的表就是主表，目标列就是主键。
例如前面的员工表和部门表中，员工表是多的一方就在员工表里面修改添加外键，并且设置dep_id为外键，匹配部门表的id为主键。
-- 创建表时添加外键约束
CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;p：在建表的时候添加外键约束的时候要注意先建立主表。
二、关系
前面的外键部分提到了表和表有个一对多的关系，这就是数据库表关系中的一种，还有多对多和一对一关系。
1.一对多
在多的一方建立外键，指向一的一方的主键。
2.多对多
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，就是说在中间表添加两个外键约束。
3.一对一
一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，提升查询性能。
在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)。
三、多表查询
上一节提到了表和表之间存在一些关系，所以查表的时候就需要考虑到这些存在关系的表。所以就有了多表查询，多表查询就是从多张表中一次性的查询出我们想要的数据。
1.多查
多表查询语句：select * from emp , dept;
这样查询的结果是两个表的完全组合，对于m条数据的emp和n条数据的dept，最后查出来m*n条数据，这明显不是我们想要的。
2.连接查询
内连接查询 ：相当于查询两个表交集数据；
-- 隐式内连接
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 条件;在这个方法中两个表所写的位置是不变的，使用left和right来表示左边表全查询还是右表全查询。
例如前面的员工-部门表：
我们查询是需要查出所有员工及其对应的部门，可能存在一些新员工还没有加入部门。如果使用内连接查询将查不到没加入部门的员工，这时候就使用外连接查询，并且使用左连接(员工表在左)。
我们查询是需要查出所有部门及其对应的员工，可能存在一些新部门还没有员工。如果使用内连接查询将查不到新的部门，这时候就也使用外连接查询，并且使用右连接(部门表在右)。
3.子查询
查询中嵌套查询，称嵌套查询为子查询。
例：查询工资高于张三的员工信息。
来实现这个需求，我们就可以通过二步实现，第一步：先查询出来张三的工资，第二步：查询工资高于张三的员工信息；
select * from emp where salary > (select salary from emp where name = '张三');子查询根据查询结果不同：
a.子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断。
b.子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断。
例：查询 '财务部' 和 '市场部' 所有 的员工信息。
-- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did from dept where dname = '财务部' or dname = '市场部';select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');c.子查询语句结果是多行多列，子查询语句作为虚拟表。
例：查询入职日期是 '2011-11-11' 之后的员工信息和部门信息。
-- 查询入职日期是 '2011-11-11' 之后的员工信息
select * from emp where join_date > '2011-11-11' ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;
☕物有本末，事有终始，知所先后。🍭