—
约束
1、分类
| 约束 | 描述 | 关键字 |
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DEFAULT |
| 检查约束（8.0.1版本后） | 保证字段值满足某一个条件 | CHECK |
| 外键约束 | 用来让两张图的数据之间建立连接，保证数据的一致性和完整性 | FOREIGN KEY |
2、案例
create table user(id int primary key auto_increment comment 'id主键',name varchar(10) not null unique comment '姓名',age int check ( age > 0 and age <= 120 ) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别') comment '用户表';
3、常用约束
| 约束条件 | 关键字 |
| 主键 | PRIMARY KEY |
| 自动增长 | AUTO_INCREMENT |
| 不为空 | NOT NULL |
| 唯一 | UNIQUE |
| 逻辑条件 | CHECK |
| 默认值 | DEFAULT |
4、外键约束
①添加外键关联之创建表时添加
CREATE TABLE 表名(字段名 字段类型,...[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名));
②添加外键关联之创建表后添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);5、删除/更新行为
| 行为 | 说明 |
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致） |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致） |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录 |
| SET NULL | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null） |
| SET DEFAULT | 父表有变更时，子表将外键设为一个默认值（Innodb不支持） |
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;举例：
alter table emp1 add constraint fk_emp1_dept_id foreign key (dept_id) references dept(id) on update CASCADE on delete CASCADE ;—
多表查询
模糊查询：XX like 。
1、多表关系
- 一对多（多对一）
- 多对多
- 一对一
2、一对多
案例：部门与员工
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
3、多对多
案例：学生与课程
关系：一个学生可以选多门课程，一门课程也可以供多个学生选修
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
-- 多对多 --------------------------------------------------------create table student(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',no varchar(10) comment '学号') comment '学生表';insert into studentvalues (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');create table course(id int auto_increment primary key comment '主键ID',name varchar(10) comment '课程名称') comment '课程表';insert into coursevalues (null, 'Java'),(null, 'PHP'),(null, 'MySQL'),(null, 'Hadoop');create table student_course(id int auto_increment comment '主键' primary key,studentid int not null comment '学生ID',courseid int not null comment '课程ID',constraint fk_courseid foreign key (courseid) references course (id),constraint fk_studentid foreign key (studentid) references student (id)) comment '学生课程中间表';insert into student_coursevalues (null, 1, 1),(null, 1, 2),(null, 1, 3),(null, 2, 2),(null, 2, 3),(null, 3, 4);
4、一对一
案例：用户与用户详情
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一（UNIQUE）
-- 一对一 -------------------------------------------------------------------create table tb_user(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1: 男 , 2: 女',phone char(11) comment '手机号') comment '用户基本信息表';create table tb_user_edu(id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户ID',constraint fk_userid foreign key (userid) references tb_user (id)) comment '用户教育信息表';insert into tb_user(id, name, age, gender, phone)values (null, '黄渤', 45, '1', '18800001111'),(null, '冰冰', 35, '2', '18800002222'),(null, '码云', 55, '1', '18800008888'),(null, '李彦宏', 50, '1', '18800009999');insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)values (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),(null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),(null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);
—
多表查询之合并查询（笛卡尔积）
合并查询（笛卡尔积，会展示所有组合结果）
table1=子表，table2=父表
select * from table1, table2;比如table1有3条，table2有3条，那么他们总共就有 3*3 条数据
1、消除无效的笛卡尔积
select * from table1, table2 where table1.xx_id = table2.id;2、对查询结果去重distinct
-- 查询拥有员工的部门ID、部门名称select distinct e.dept_id, d.name from emp as e, dept as d where e.dept_id = d.id;
04
—
多表查询之连接查询
1、内连接
内连接查询的是两张表交集的部分
The joining of two or more tables is based on common field between them.
①隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;②显示内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... where ...;③案例
-- 查询员工姓名，关联部门名称 (隐式)SELECT emp.name, dept.name FROM emp, dept WHERE emp.dept_id = dept.id ;SELECT e.name, d.name FROM emp e, dept d WHERE e.dept_id = d.id;-- 如果为表起了别名，就不能再通过原表名来限定字段-- 查询员工姓名，关联部门名称 (显式)SELECT e.name, d.name FROM emp AS e JOIN dept AS d ON e.dept_id = d.id;
2、外连接
①左外连接
查询左表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;②右外连接
查询右表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;③案例
-- 查询emp表和对应的部门信息（左外）SELECT e.* , d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;-- 查询dept表和对应的员工信息（右外）SELECT d.* , e.* FROM dept d RIGHT OUTER JOIN emp e ON e.dept_id = d.id;
左连接可以查询到没有dept的employee，右连接可以查询到没有employee的dept；
左外用的比较多。
3、自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;1、案例
-- 查询员工 以及 所述领导的名字 (内连接自连接)SELECT e1.name, e2.name FROM emp e1 JOIN emp e2 ON e1.managerid = e2.id;-- 查询员工 以及 所述领导的名字, 如果无，也要显示 (外连接自连接)SELECT e1.name, e2.name FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.managerid = e2.id;
原创文章，作者：guozi，如若转载，请注明出处：https://www.sudun.com/ask/82540.html
