数据库增删改查（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不会去重