本文详细介绍了数据库的约束类型，包括非空、唯一、默认值和主键外键；探讨了表设计遵循的三范式原则；深入讲解了CRUD操作的高级技巧，如批量插入、聚合函数和多表查询（内连接、外连接、自连接、子查询和合并查询）。
目录
2. 查询所有同学的总成绩，以及这些同学的个人信息（学生学号，学生姓名，学生班级id）
3. 查询每个同学的姓名，学号，选修的课程名称以及该课程的分数
eg：查询成绩表中'计算机原理（3）'优于'Java（1）'的成绩信息
eg：查询出所有成绩比 '中文系2019级3班' 的平均分高的所有成绩信息
一、数据库约束
数据库约束：对于某一列的值能添加哪些内容做了一定的限制，这种限制的手段就称为约束。之前创建的表，表的属性没有任何约束，这是一个非常大的隐患。
1. 约束类型
1) 非空约束 not null
若在某一属性定义时，规定该属性not null，则不能将空值存放在该属性中。
约定了非空约束的列，在插入时若没有插入。则必须有默认值或者必须显示插入。
通过desc查看非空约束的字段
如何在创建表之后添加非空约束？
表约束都属于表结构上的修改，alter- change。
必须将当前表中所有 id 为 null 的先删除，将id属性添加为非空约束，或者修改为非空。
2) 唯一约束 unique
唯一约束指的是对应字段是唯一的，不能重复。一个表的唯一约束可以有多个。
插入重复id时，报错。
查看唯一约束show keys from tb_name;
唯一约束存放null的情况，null不受唯一键的约束！
创建表之后想修改某个字段为唯一约束
现在修改name属性为唯一约束
alter table stu_unique add unique(name); //给name属性加上唯一约束
3) 默认值约束 default
规定了默认值的表，在插入数据时，若没有指定该列，则使用默认值插入数据。
插入时没有显示指定sex列，则读取sex的默认值
若显示对默认值的列插入null值，不会触发默认值。
4) 主键约束(索引) primary key——索引
一张表只能有一个主键，primary key = unique + not null。
主键约束的列值不能重复，且不能为null。
主键约束可以由多个列共同组成——联合主键。
不能为null
查看当前表的主键约束
在创建表之后添加主键（当前表中没有主键约束）
alter table tb_name add primary key(作为主键的属性);
当前表中有主键约束，添加失败。
删除表中主键
alter table tb_name drop primary key;
复合主键
创建时
id-name为复合主键
修改时
alter table stu_primary add primary key(id,name);
复合主键必须 id-name 完全相同才认为相同，部分属性不同就是不同的两条记录。
在MySQL中，若某个属性使用unique + not null ，默认会将该属性置为主键！
此时就把第一个not null + unique共同约束的属性作为主键。
id为primary，因为主键只有一个，name保留unique。
d. 自增主键auto_increment
因为主键不重复且不为空，一般来说作为主键的列都是int或者定长的char类型，因此我们可以将主键的增长交给数据库自动执行。
1) 自增主键可以显示的插入null或者不写，都会触发自增操作
2) 关于自增主键删除后的自增情况
自增主键是以当前已经出现过的数字最大值+1
delete 删除后的主健，下次再自增时，是以出现过的最大值作为基准来自增。
truncate删除表数据后自增主键的情况
truncate之后，会还原自增主键的值。
插入报错时，id也会自增
3) 显示给自增主键插入一个值
下一次触发自增操作时，仍然还是以当前出现过的最大值为基准自增。
此时1...100还能作为主键吗？
只能显示插入，不能自增插入！
仍是出现过的最大值自增。
5) 外键约束
涉及多个表之间的关联约束
例如：当前有一个学生表，还有一个班级表，学生表中有一列属性为该学生的班级信息。
假设现在小明第一次QQ私信老师，请求老师解决问题。小明自报家门是1班的，老师先在班级表中查看是否有1班这个班级，再在学生表中查看是否存在小明这个人。
学生表中class_id关联class表的id属性。
学生表插入数据时，class_id要能正确插入，必须在class表中id值存在。foreign key (当前表中的哪个属性) references主表 (关联属性);
在class表中不存在id为1的值，student中插入失败。
当class主表中存在了id为1的班级，此时就可以在student从表中插入class id = 1的数据了，先有班级，才有该班级的学生，这种约束就叫外键约束。
删除值
在student从表中有2条记录关联到了主表class的id为1的这一行，若要删除主表class的id行，必须确保所有从表中关联的数据先删除。
插入时，先看主表，主表中有这个属性值才能在从表中插入。
删除时，先看从表，从表中该属性值关联的所有记录都删除之后才能在主表中删除。
先要解除从表中所有class id =1的关联，才能返回主表删除。
相当于：
学生入班——插入操作，首先得保证先有该班级，才能把学生放入该班级中。
删除某个班级，解散某个班级。首先得保证这个班级中所有的学生已经毕业了，不再关联这个班级了，才能最后删除这个班级信息。
二、表的设计 （数据库三范式）
实体(表)间的四种关系
一对一
一对一的两个属性一般都可以放在一张表中。
如：学生和学号关系都放在学生表中，姓名和身份证号的关系都放在公民信息表中。
一对多
一对多：一个同学只能在一个班级，一个班级可以包含多个同学。
学生和班级就属于一(班级)对多(学生)的关系。
问题：方式1和方式2哪种更合适？
方式1：班级(id,name,students)、学生(id,name)
方式2：班级(id,name)、学生(id,name,class_id)
答：方式2
students中含有(id,name)需要再次拆分。某个属性不是原子的不满足第一范式。
套路：一对多的关系都可以使用这个套路来创建两张表，使用一对多的关联关系来创建表。
多对多
多对多∶学生和课程的关系，一个学生可以选择多个课程，一个课程也可以同时被多个学生选择。
多对多的关系，创建一个学生-课程中间表来记录多对多的关系。
没关系
三范式
第一范式：确保每列的原子性（设计表时，每一列都不能再次分解）。
第二范式：当前表中所有属性都和主键相关。
第三范式：表中所有属性都和主键直接相关而不是间接相关。第二和第三范式要保证表中所有属性都和主键相关且直接相关，若不相关或者不是直接相关就需要拆分表操作。
三、CURD进阶
1. 根据查询结果一次性插入多条记录
insert into tb_name (属性) select ...;
根据select结果集插入表中数据，select出的属性要和插入的属性一一对应。
将stu表中name和qq_mail插入到stu_test中的name和email中，只插入不为null的记录 。
根据stu表中查询的结果插入stu_test，查询的属性个数以及类型要——对应。
2. 聚合函数
聚合查询指的是把行之间的数据进行聚合，和列无关。
使用后4个函数都必须传入数值属性，不然没有意义。
3. count函数
count(属性名)
统计stu表中有多少同学
1) count(*)相当于select(*)效率比较低，全表扫描，统计总行数。
2) count(属性)
去除所有值为null的行，只统计不为null个数
3) count(任意数值)
效果等同于count(*)，相当于在临时表中创建了一列属性，值都是count(数值)，统计当前表中有多少行，速度比count(*)快，会使用索引。
4. sum函数
sum
查看emp表中所有雇员的总月薪
根据聚合函数得到的属性可以起别名as
5. max、min、avg函数
max
min
avg
6. group by分组
一般聚合函数搭配group by分组查询使用，select使用group by可以对指定查询的列进行分组。
1. 统计每个岗位的平均工资需要根据查询出来的结果按照role进行分组
select role,avg(salary) from emp group by role;
先按照role进行分组，分组之后使用avg进行求平均值
2. 查询每个岗位的最高和最低薪资
group by能否使用select中的别名?
先得到临时表才聚合
having
分组之后进行条件查询必须使用having（和group by搭配使用，聚合后的条件过滤使用），必须是聚合函数之后的条件。
1. 统计三种岗位的平均薪资，保留平均薪资>400的记录
聚合之后，已经计算出平均值了，这个条件，使用having
2. group by语句不是不能用where，是不能在聚合之后使用where。
聚合之前的条件使用where
聚合之后的条件使用having
3. 统计三种岗位的平均薪资，去除name = 张老师的薪资，保留平均薪资>400的记录select role,avg(salary) from emp where name != '张老师' group by role having avg(salary) > 400;
聚合之前，select阶段，还没分组，故使用用where。分组聚合后聚合之后用having。
先过滤where条件之后的临时表，进行分组，最后avg的聚合。
4. having的条件一定是分组后的聚合函数过滤
报错，分组之后，找不到name。
四、联合查询——多表查询
题目：查找班级名字为'一班'的同学信息
classes表 stu表
在一条SQL语句中同时查出这两个属性，就需要两张表一起查询。
多表查询[2...N]比较复杂，牵扯多个表的属性之间的关联关系和计算。
重点掌握多表查询的计算过程——笛卡尔积（本质上就是排列组合）。
联合查询其实就是在进行两张表的笛卡尔积，本质上就是两个表的排列组合
1. 先试验笛卡尔积的结果
多表查询得到的结果是两张表的组合
列数：多表的列数之和
行数：多表的行数之积
2. 按照条件一步步筛选，根据两张表的关联关系，学生的班级id要和班级表的id配过滤正确的数据。
mysql> select stu.id,stu.name,stu.class_id,classes.id,classes.name from stu,class where stu.class_id = classes.id;
3. 只需要加上班级名称 = '一班'的条件，再次对结果进行过滤。
mysql> select stu.id,stu.name,stu.class_id,classes.id,classes.name from stu,classes where stu.class_id = classes.id and classes.name = '一班';
内连接
就是多表的笛卡尔积根据关联关系严格筛选结果，内连接的结果两张表的数据都不为空
select stu.name from stu inner join classes on stu.class_id = classes.id where classes.name = '一班';
连接条件和筛选条件分离，逻辑更清楚
练习
1. 查询许仙同学的成绩
a. 先进行两个表的内连接，此时student.id = score_student_id，得到每个同学的成绩
b. 加上过滤条件，此时查询的是许仙同学的成绩，name = '许仙'。
-- 写法1
select name,score from student inner join score on student.id = score.student_id where name = '许仙';
-- 写法2
select name,score from student,score where student.id = score.student_id and name = '许仙';
2. 查询所有同学的总成绩，以及这些同学的个人信息（学生学号，学生姓名，学生班级id）
student.id = score.student_id，查询成绩表中每个同学自己的成绩
-- a. 先在成绩表中求出每个同学的总成绩-单表查询
select student_id,sum(score) as `总成绩` from score group by student_id;
-- b. 两张表的连接，关联关系同样是student.id = score.student_id
select sum(score) as `总成绩`,sn,name,classes_id from student inner join score on score.student_id = student.id group by score.student_id;
3. 查询每个同学的姓名，学号，选修的课程名称以及该课程的分数
select student.name,student.id,course.name,score.score from student inner join score on score.student_id = student.id inner join course on score.course_id = course.id;
内连接一定都是多个表同时都存在关联数据的情况
假设表1存在数据，表2没有数据，此时这条记录关联之后就不会显示，要显示则需要使用外连接。
外连接
外连接：两张表查询时，若某个表存在空数据，仍然显示出来。
左外连接：将tb1完全显示，tb2中不匹配的数据就显示为nullfrom tb_name1 left join tb_name2 on 条件右外连接：将tb2完全显示，tb1中不匹配的数据就显示为null。
from tb_name1 right join tb name2 on 条件
测试
1. 内连接
select user.*,account.* from user inner join account on user.id = account.id;
2. 左外连接，将左表数据完全显示，右表中的数据不存在则显示为null。
select user.*,account.* from user left join account on user.id = account.id;
3. 右外连接
select user.*,account.* from user right join account on user.id = account.id;
自连接
自连接：一般MySQL筛选数据时，都是不同行的列之间进行数据的比较，涉及到同一张表中行数据的筛选，用到自连接。
eg：显示出成绩表中所有成绩比Java成绩高的成绩信息
所有信息都在score表中，此时对比不同行之间的数据。比较的基础在于知道Java成绩是多少(这个信息蕴藏在某一行中，而不是具体的哪个列)。
如：显示出所有成绩 > 70的成绩信息(score > 70)，其实比较的就是每一行的score这一列属性。
1. 先写笛卡尔积score自己和自己连接一下查看结果
select s1.score,s1.student_id,s2.score,s2.student_id from score s1,score s2;
2. 过滤
a. 查询所有成绩优于Java成绩的成绩信息
select s1.score,s1.student_id,s2.score,s2.student_id from score s1 inner join score s2 on s1.student_id = s2.student_id;b. 在课程表中找到课程名字为Java的课程id
select id from course where name = 'Java';c. 再去自连接表中过滤所有成绩 > Java成绩的记录
select s1.score,s1.student_id,s2.score,s2.student_id from score s1 inner join score s2 on s1.student_id = s2.student_id where s1.score > s2.score and s2.course_id = 1;
eg：查询成绩表中'计算机原理（3）'优于'Java（1）'的成绩信息
select s1.* from score s1 inner join score s2 on s1.student_id = s2.student_id where s1.score > s2.score and s1.course_id = 3 and s2.course_id = 1;
子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
1. 单行子查询：内部的查询sql返回单条记录的查询
eg：查询所有和 '不想毕业' 同学同班的其他同学。
查询不想毕业同学的class id，外部查询过滤这个classes_id即可。
a. 查询'不想毕业'同学的classes_id，只能返回一条记录
select classes_id from student where name = '不想毕业';
b. 过滤classes_id这个条件即可
select * from student where classes_id = 1;
c. 组装a和b两个步骤的sql
select * from student where classes_id =( select classes_id from student where name = '不想毕业' );
2. 多行子查询：内部嵌套的查询sql返回多条记录的查询
eg：查询语文或英文的成绩信息
第一种方法
a. 查询语文或英文的课程编号
select id from course where name = '语文' or name = '英文';
b. 在成绩表中查询语文4或英语6的成绩信息
select score,course_id from score where course_id in (4,6);
c. 组装sql
select score,course_id from score where course_id in ( select id from course where name = '语文' or name = '英文' );
第—种方法in，执行过程就是两次查询。
先根据内部子查询得到课程id,score.course_id，然后拿着这个id去外部条件中过滤。
第二种方法
使用exists关键字
select score from score where exists( select id from course where (name = '语文' or name = '英文') and course.id = score.course_id );第二种方法先执行外部查询，依次取出外部查询的每一条语句去和内部查询匹配，匹配上就保留结果，否则丢弃。
3. 多行子查询使用in或exists的区别
1. 第一种写法in。
执行过程只有两次查询，先执行内部查询，根据内部查询的结果筛选外部条件。子查询的结果会缓存到内存中，适用于子查询结果集较小的情况。效率比较高，但是需要使用内存空间。
2. 第二种写法exists。
比较耗时，每次都是从外部查询中取出记录和内部查询匹配，内部子查询不会产生临时表，不耗费内存空间。适用于子查询结果集比较大，且内存放不下的情况！
eg：查询出所有成绩比 '中文系2019级3班' 的平均分高的所有成绩信息
a. 先查询出 '中文系2019级3班' 的平均成绩
select avg(score) from score inner join student on (student.id = score.student_id) inner join classes on (student.classes_id = classes.id) where classes.name = '中文系2019级3班';
b. 将查询出来的结果看作临时表
select score.score from score, (select avg(score) as score from score inner join student on (student.id = score.student_id) inner join classes on (student.classes_id = classes.id) where classes.name = '中文系2019级3班') as temp where score.score > temp.score;
把子查询的结果当做临时表，放在from语句的后面。
增删改练习
合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。
使用union和union all时，前后查询的结果集中，字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
eg：查询id小于3，或者名字为“英文”的课程。
select * from course where id<3 union select * from course where name='英文'; -- 或者使用or来实现 select * from course where id<3 or name='英文';
union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
eg：查询id小于3，或者名字为“Java”的课程。
-- 可以看到结果集中出现重复数据Java select * from course where id<3 union all select * from course where name='Java';
2623
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
