数据库增删改查（CRUD）进阶版
目录
数据库约束
约束类型
表的设计
1.一对一
2.一对多
3.多对多
增删查改进阶操作
1. 插入查询结果
2.查询
聚合查询
聚合函数
group by
having
联合查询
内连接
外连接
自连接
子查询
合并查询
数据库约束
创建表的时候制定的一些规则，在后续插入/删除/查询/修改都要遵循这个规则。
可以帮助校验数据，让数据正确性得到保证
约束类型
not null：
unique：
插入张三的数据后还能再插入张三的数据，在某些情况下是不允许的
加上unique就可以避免重复插入
default：
默认情况下默认值是null，对于指定列插入时，其他未被插入数据的列就会被设置为默认值
primary key：
主键，相当于给某列数据赋一个身份证号
注意：一个表只能有一个主键
主键不允许重复，那当我插入了很多个数据之后，想插入一个新数据，怎么保证它能不重复呢
MySQL为我们提供了一种机制：自增主键
这样的自增有什么用呢？
我们在插入id的时候可以设置成null，数据库可以帮我们自动转成1插入，后面再插入名字就依次递增（当然，自增的前提是这个数据类型是整型）
如果程序员手动指定了id插入数据，在下一次用null插入时会继续上一个id进行自增
而且这里的自增是不会重复利用之前的值的，当我们把id = 100和id = 101的行删掉之后，下一次插入id依旧在101的基础上自增到102
foreign key：
描述两个表之间的关系
从上面两张表我们发现王五同学不在班级表钟存在，所以这是一个不太科学的数据。如果希望学生表中的classId都要在班级表里面存在，就可以用外键约束来校验
前面的约束都是在定义表的时候，哪一列需要约束，就创建到哪一列的后面；
外键约束是写到最后的，把所有的列定义之后在最后面通过foreign key创建外键约束
这种情况下可以认为班级表约束了学生表，此时班级表被称为父表，而学生表被称为子表
1. 如果父表是空的，那么子表的插入操作会被认为错误
2. 通过外键约束之后，刚刚王五同学classId = 100 会被认为错误，无法插入
3. 受外键约束后，不仅插入不科学数据不行，错误修改也不被允许
4. 不仅父表约束子表，子表也在约束父表
当我们想删除父表的一行数据的时候，如果子表有父表对应的数据就可能出现问题
5. 创建表时要给需要被引用的列后面加上索引，常见的方式就是加上主键，有了主键就会自动创建处索引
表的设计
根据需求，把需要的表设计出来的过程
（1）先根据需求，找到实体
（2）梳理清楚实体之间的关系
1.一对一
图书馆借阅系统，需要表示（概念）学生实体和账户，就要搞学生表和账户表来描述这些实体
一个学生可以有一个账户（学生不能注册小号，相当于游戏里防止你开小号炸鱼是一样的）
一个账户，也只能给一个学生使用（相当于游戏里禁止你代练一样）
这种关系就是1对1
方案1：两个表搞成一个表，学生账户表(accountId, accountName, password, ...)
方案2：分两个表，使用id建立练习
student(studentId, studentName,..., accountId)
account(accountId,username, password, ...)
2.一对多
一个班级可以包含多个学生，但一个学生只能从属于一个班级。这种关系就是1对多
方案1（不支持）：
student(studentId, name,... )
class(classId, name, studentList)
因为MySQL不支持数组类型，所以方案1不支持
方案2：
class(classId, name)
student(studentId, name, classId)
3.多对多
一个学生，可以选择多门课程
一个课程，也可以被多个学生选择
student(studentId, name...)
course(courseId, name...)
student-course(studentId, courseId)
增删查改进阶操作
1. 插入查询结果
insert into 表名 select * from 表2名
注意这里student查询出来的结果结合，列数/类型要和student2表结构匹配
2.查询
聚合查询
查询带表达式：只能拿着某几个列来查询
聚合查询：进行“行和行”之间的运算，但是只能通过聚合函数来操作
聚合函数
我们客户端里面出现的这个行数不是由聚合函数得来的，如果是编程操作还是得使用聚合操作比较方便，因为聚合函数可以进行一些条件的判断
count操作
上面这个操作先执行select * from student2，再用count进行计数
⚠使用count(*)与使用count(列名)大部分情况没啥区别
⚠使用count(*)的时候即使全是null的行也会记录下来
sum操作
我们试试看sum(name)会怎么样
会报警告：代码有问题但是不影响程序能跑
sql很多时候会把字符串转成数字来进行算术运算，但是上面这些名字sql转不了数字
其他的聚合函数例子
使用聚合函数的时候，列和列之间的顺序已经被打散了，所以大部分情况聚合的列和非聚合的列不能在一起使用，除非下面的操作
group by
把所有的行分成若干组，每个组分别进行聚合
这里的role就是没有使用聚合函数的列
having
分组之前的条件使用where
现在统计每个岗位的平均薪资，但是刨除“李四”
这种情况属于先刨除再分组
分组之后的条件使用having
统计每个岗位的平均薪资，抛出平均薪资>20000的情况（这个时候就一定得先分组计算平均薪资后，才能按条件刨除）
联合查询
多张有关系的表一块查询，核心是笛卡尔积的计算方法
笛卡尔积得到的是一张更大的表
该表的列数是之前两张表列数之和
该表的行数是之前两张表行数之积
如果是三张或者以上的，就得两两进行笛卡尔积
这样制造出笛卡尔积之后就方便我们比较哪个同学属于哪个班
但是怎么筛选出正确的数据呢？
这里的"."可以理解成“的”，where带的条件称为连接条件
一般多表联合查询的步骤：
（1）笛卡尔积
（2）指定连接条件
（3）指定其他条件
（4）针对列进行精简/表达式运算/聚合查询
内连接
现在有下面的四张表
学生表
班级表
课程表
分数表
（1）查询许仙同学成绩
先笛卡尔积，把学生表和分数表联合起来
发现足足有160行
接着指定连接条件，精简结果
再根据需求（找许仙同学）指定其他条件
最后针对上面的列进行精简
另一种联合方法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
（2）查询所有同学的总成绩，及同学的个人信息
省略步骤了直接跳到最后一步
（3）查询出每个同学，每个课程的成绩
需要三个进行笛卡尔积，可以理解成
student和score先进行笛卡尔积，连接条件student.id = score.student_id
上述结果再和course表计算笛卡尔积，连接条件score.course_id = course.id
其实用join on来写更清楚
外连接
多数情况，内外连接查询结果没区别
外连接分为左外连接，右外连接
现在重新创建学生表和分数表
外连接写法
现在修改这两个表的数据，使其不再一一对应
对于内连接，得到的结果是两个表共有的数据
左外连接以左表为主，会保证左侧表的数据都体现在最终结果里，如果这个记录右表里面没有匹配的，以null代替
右外连接和左外连接相反，保证右表的数据
⚠MySQL不支持全外连接
自连接
同一张表，自己和自己进行笛卡尔积。
条件查询只能在列和列之间查询，但是我们在特定情况下要针对行进行查询，我们就可以用自连接把行关系转成列关系
这里需要进行二者的比较需要把行转成列才方便比较
我们不能直接用score自己给自己笛卡尔积，而是要给他起个别名
指定连接条件
筛选出左表为3，右表为1的所有记录
最后调整
子查询
就是套娃，把多个简单的sql语句合并成一个复杂的sql语句
太套娃了就不演示
合并查询
把两个查询的结果集合合并成一个集合
使用union关键字
select 1 union select 2;
这里其实使用or就行，但是在查询两张不同的表时就只能用union
而且union有去重的功能，但union all不会去重