【MySQL】数据库约束与聚合查询和联合查询等进阶操作知识汇总（上）

1.数据库约束:

1. 数据库约束是对数据库中的记录做出更详细的检查(检查数据是否合法,不能完全靠人工来检查)
2. 和interface/abstract class的作用效果是类似的.

1.1 约束的类型:

(对于数据库中的记录做出更详细的检查)

和NULL比较:

= 或者 != 都不能和NULL比较

<=> 或者 is null 或者 is not null 可以和NULL比较

1.2 unique:

1. unique的值唯一,每次插入/修改数据,都会针对当前的数据在表里进行查找,看这个值是否能查到!(如果查到了说明不唯一,就修改失败)
   
2. 当使用了unique之后,每次插入操作都会涉及到先查询再插入的操作.查询这里就涉及到遍历,遍历的效率比较低,因此就用到了索引!
   
3. 使用unique约束,数据库就会自动的给对应的列创建索引.
   

1.3 primary key:

1. 就类似与身份证,作为一个人的身份标识一样.
   
2. 主键是一个身份标识,既要保证唯一性又不能为空;因此primary key相当于是unique + not null;
   
3. 一个表里,主键只能有一个, unique则是可以有多个的.
   
4. 使用主键需要给这个列设置一个唯一的值,如果这个分配值的工作人工来完成的话就很麻烦(需要人工来保证使用的身份标识没有重复), 因此MySQL中就引入了一个机制自增主键(通过自增这样的方法来给主键设置值).
   
5. 使用自增主键primary key auto_increment的时候,就不需要手动来设置值了,只需要把这一列设置为null即可.
   
6. 虽然自增主键是自动分配的,但是也能手动设置.
   
7. 自增主键可以理解成是数据库里记录了当前最大的id是多少,每次都是从最大值之后再进行增加的.
   

1.3.1 分布式系统下,自增主键如何生成唯一id:

1. 如果数据库服务器就一个,那自增主键就可以保证主键的唯一性,但是如果数据时分布式部署的(数据太大了,一台机器存放不下,使用多台机器存储).这个时候就要分库分表.但是这些数据逻辑上还是同一张表只不过是存储在不同的机器上,此时原有的自增主键就失效了(MySQL的自增主键只能保证单机上不会重复,不能保证分布式部署不重复).
   
2. 分布式系统下生成唯一ID的算法: 生成公式 = (时间戳 + 机房编号 / 主机的编号 + 随机因子) => 计算 哈希值;
   
3. 随机因子:随机数,理论上保证不了俩次生成的随机数是不同的,但是工程上实验上随机因子冲突的概率是非常小的因此就忽略不计了.
   

1.4 foreign key:

1. 作用是把俩张表关联起来.
   
2. 例如:现有学生表和班级表.学生表里的每个记录中都包含了班级编号,这个班级编号要在班级表中存在!
   
3. 举个例子,有一天一个人加我好友,说是和我一个专业的3班的同学,我一听就不对劲.俺们专业没有3班呐. 这就叫外键约束.
   
4. 班级表就对学生表产生了约束.班级表(约束别人的表)称为是父表;学生表(收到约束的表)称为是子表;
   
5. 在外键的约束下插入数据会触发查询.往学生表中插入数据就会自动的在班级表中查询,看这个classId是否存在, 如果存在则插入成功,否则插入失败.
   
6. 这里也有要求,子表中引用父表的这一列,务必是primary key 或者 unique . 这里指的是父表.
   
7. 在外键中,父表对于子表产生了约束.子表也是会对父表有约束的.如果在子表存在的情况下,直接删除父表就会出现问题.父表删除记录也要看当前这个记录是否被子表引用,如果被引用的就不能删除,不被引用的才能删除/修改.
   

1.4.1 逻辑删除:

1. 父表->商品表(goodsId,name,price…); 子表->订单表(orderld,time,goodsld…);如果某个商品下架了,就需要把goodsId给删了,但是子表中引用了父表的goodsId所以删不掉,此时就涉及到了逻辑删除.将商品表的数据标记成无效,这里说的逻辑删除就是对这个标记位进行修改.
2. 但是这样的话,就会导致硬盘空间被持续占用,岂不是很浪费空间嘛??其实硬盘空间很大的,而且很便宜.

2.表的设计/数据库的设计:

2.1 数据库是如何设计的?

1. 这个问题从这个几个方面回答: 数据库中有几个表?/每个表都是干啥的?/每个表里都有哪些列?/这些列又是干啥的?
2. 数据库设计主要思路: a)根据需求找到实体 —> b)再梳理清楚实体之间的关系(一对一/一对多/多对多关系)
3. 找实体的过程说的就是需求中的关键性的名词, 一般来说每个实体都会安排一个表.
4. **一对一关系:**一个人只有一个身份证号,一个身份证也只对应一个人.
5. **一对多关系:**一个班级可以包含多个学生,一个学生只属于一个班级.
6. **多对多关系:**一个学生可以选择多门课程,一门课程也可以拥有多个学生.多对多的表结构通常需要使用一个"关联表"把俩个实体的表给联系起来.
7. 除此之外的关系就是没关系.

3.进阶插入操作:

insert into 表名1 select 列名 from 表名2:

1. 可以把查询的结果插入到另一个表中, insert into student2 select * from student1;把student1里查询出来的结果插入到student2中.
2. 这里的插入不一定是把student1里的数据全都插入过去,也可以只插入一部分(通过条件来筛选)
3. insert into student2 select * from student1; 这里查询的结果的列要和插入的表的列要匹配(列的个数和类型是要匹配)

4.进阶查询:

4.1 聚合查询:

1. 查询的时候带表达式是把列和列放到一起进行计算.
2. 聚合查询是行和行放到一起进行计算
3. SQL里面提供了一些函数, 通过这些函数就可以进行行和行的运算.

函数: count( ) / sum( ) :

1. count(*)是查询结果中有多少行,即使有一行是空值也会记录到行数里.
2. count(列名)如果遇到有一行是空值,则不会记录.
3. sum(列名),如果这一列中有null,那么null不参与运算,直接跳过.
4. sum(列名)这样的运算只能针对数字来进行,不能针对字符串.

4.2 group by 列名:

1. 可以使用, group by 列名;来根据查询的结果进行分组操作,把值相同的记录分成一组,然后就可以针对每一组分别进行聚合了.
   
2. group by 的效果:
   
3. 如果直接写成select * from emp group by role;展示出的就是每一组的第一条数据.
   
4. 在进行分组查询的时候,只有用来分组的这一列,可以进行查询,其他的列则必须要搭配聚合函数来查询.
   
5. 分组查询的时候还可以指定条件,在分组之前使用条件筛选需要用到where ; 在分组之后使用条件查询需要用到having .(where和having可以同时使用!)