数据库约束与多表联查
一、数据库约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据
用于保证数据库中数据的正确性、有效性和完整性。
约束
主键约束：值唯一且不为NULL，一张表只能有一个主键约束,用于唯一标识记录
唯一约束：值必须唯一，允许有一个为null,确保列值唯一性
非空约束：不能为null
检查约束：限制列的取值范围,保证数据域的正确性
默认值约束：给这一列设置一个默认值
外键约束：从表的某个列的值来源于主表某列的值,维护表间引用完整性
1. 主键约束
三种设置方式：
方法1：
//方法1： create table tb_teacher( tid int primary key, tname varchar(20) );方法2：
create table tb_teacher( tid int, tname varchar(20), primary key(tid) );方法3：修改表添加
create table tb_teacher( tid int, tname varchar(20) ); ALTER TABLE tb_teacher ADD CONSTRAINT pk_teacher PRIMARY KEY (tid);组合主键
多列共同构成主键，多对多关联表常用
create table tb_teacher( tid int, tname varchar(20), cardno char(18), primary key(tid, cardno) -- 组合主键 );*2. 检查约束
create table tb_teacher( tid int primary key, tname varchar(20) not null, cardno char(18) unique, sex char(2) check(sex='男' or sex = '女') );3. 外键约束
注意事项：
· 创建表：先主表后从表
· 删除表：先从表后主表
· 已有数据的表添加外键可能失败
-- 修改表方式添加外键（与主键相同，有三种，创建方式相同，关键字不同）
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);·一对一：外键加在任意一方
·一对多：外键加在"多"的一方
·多对多：需要中间表（命名：tb表1表2）
二、多表联查
1. 联合查询（集合操作）
1. UNION - 并集（去重）
-- 将薪资低于2000的员工和10号部门的员工全部查询出来 select * from emp where sal < 2000 union select * from emp where deptno = 10;2. UNION ALL - 并集（不去重）
select * from emp where sal < 2000 union all select * from emp where deptno = 10;注意事项：
列名取自第一个查询结果集
3. INTERSECT - 交集
-- 查询既是10号部门又是CLERK的员工 select * from emp where deptno = 10 intersect select * from emp where job = 'CLERK';注意事项：
INTERSECT会自动去除重复行
所有查询必须有相同数量的列
对应列的数据类型必须兼容
结果集的列名取自第一个查询
4 .EXCEPT - 差集
-- 查询10号部门中不是CLERK的员工 select * from emp where deptno = 10 except select * from emp where job = 'CLERK';注意事项：
EXCEPT会自动去除重复行
所有查询必须有相同数量的列
对应列的数据类型必须兼容
2. 连接查询
| 连接类型 | 使用场景 | 特点 |
|---|---|---|
| 内连接 | 需要完全匹配的数据 | 只返回满足条件的记录· 查询结果必须满足连接条件 |
| 左外连接 | 需要显示左表所有记录 | 以左表为主表， 左表所有记录都会显示，右表不满足条件的显示NULL |
| 右外连接 | 需要显示右表所有记录 | 以右表为主表， 左表所有记录都会显示，右表不满足条件的显示NULL |
| 全外连接 | 需要显示所有记录 | 两边表记录都显示，不匹配为NULL |
| 自连接 | 查询层级关系（如上下级关系） | 表自身连接，需要别名 |
2.1 笛卡尔积（自然连接）
连接查询会产生笛卡尔积，即两个表的乘积（实际开发中不使用，无意义)
SELECT * FROM emp, dept;内连接
标准写法
SELECT * FROM emp e JOIN dept d ON e.deptno=d.deptno;非标准写法
SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;24 外连接
2.4.1 左外连接（LEFT JOIN）
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;2.4.2 右外连接（RIGHT JOIN）
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;2.4.3 全外连接（FULL JOIN）
SELECT * FROM emp e FULL JOIN dept d ON e.deptno = d.deptno;2.5 自连接（SELF JOIN）
表自身与自身连接
-- 查询员工及其所属领导的名字
-- 使用JOIN语法
SELECT 字段列表 FROM 表A 别名A JOIN 表B 别名B ON 条件 ...;
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
