MySQL表操作（进阶）

一、数据库约束

1、约束类型

NOT NULL - 指示某列不能存储 NULL 值

UNIQUE - 保证某列的每行必须有唯一的值

DEFAULT - 规定没有给列赋值时的默认值

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性

2、NOT NULL约束

创建列表的时候，指定某一列不为空

3、UNIQUE约束

指定某一列为唯一的，不重复的

不仅限制插入，也限制修改

该约束会让后续插入/修改数据的时候，都触发一次查询，通过这个查询来判断这个记录是否存在

4、DEFAULT约束

喵叔这一列的默认值，默认的默认值是null，可通过default约束来修改默认值

5、PRIMARY KEY约束

一张表里只能有一个primary约束

对带有主键的表来说，每次插入/修改也会触发先前查询的操作

auto_increment，把主键的生成交给数据库来执行

但是id的自动分配有一定的局限性

分布式系统生成唯一id的算法

时间戳+主机编号+随机因子

时间戳：格林威治时间1970年01月01日00时00分00秒（北京时间1970年01月01日08时00分00秒）起至现在的总秒数 

主机编号：为了保证同一时间，添加到不同主机的商品编号是不同的

6、FOREIGN KEY约束

foreign key，外键约束，描述两个表的关联关系

若father表约束child表，则把father表称为父表，child称为子表

reference:引用，此处表明了该表的这一列数据应出自另一个表的哪一列

foreign key（name） reference father（name）子表的name应出自父表的name

若针对父表进行修改/删除操作，当前被操作的值被引用了这样的操作也会失败

外键约束要保持子表的数据在对应的父表中要存在，万一父表将数据修改了，就打破了约束

外键：两个列产生联系，其他列不受影响

想删除父表，若父表中的数据被引用了，是不能删除的

指定外键约束的时候，要求父表被关联的那一列是主键或unique

注：

数据库引入约束的时候，执行效率会受到影响，降低很多

数据库是比较慢的，很吃资源的系统，部署数据库的服务器容易成为一个系统的瓶颈

二、表的设计

根据实际的需求场景，明确当前要创建几个表，这些表是否存在一定的联系

梳理清楚需求的实体(对象)，再确定好实体之间的关系

每个实体都需要安排一个表，表的列对应实体的各个属性

一对一：如一个学生只能有一个帐号，一个账号只能被一个学生拥有

一对多：一个同学只在一个班级里，一个班级有多个同学

多对多:一个同学可选多个课程，多个课程包含多个学生

插入搭配查询：将查询到的语句作为插入的数值

要求插入的结果集合列数/类型和插入的这个表匹配

三、聚合查询

相当于在行与行之间运算

通过聚合函数完成上述运算

1、聚合函数

count()：查询行数，另外count(*)得到的结果可以被用来运算

若当前的列中有空值，两种方式[count(*)和count(列名)]计算的count就不同了

指定具体列可进行去重：count(dlistinct 列名)

Sum()：仅回查询的数据总和，不是数字没有意义

avq()：返回查询的数据平均值，不是数字无意义

max()：返回查询的数据最大值，不是数字无意义

min()：返回查询的数据最小值，不是数字无意义

sum(列名，列名...)：把对应的列名相加，得到一个临时表，再把临时表的结果进行行与行的相加

2、group by

group by：进行分组,针对每个组再行聚合查询

若进行分组时不进行聚合，结果就是查询每一组的某个代表函数

往往还是搭配聚合函数来使用，否则这里的查询结果就没有意义(group by要搭配聚合函数使用)

select 列名，列名...from 表名 group by 指定列

指定列每组所记录的指定数据是相同的

使用group by，还可以搭配条件，但应区分清是该条件楚分组前的还是分组后的

分组之前的条件：直接使用where即可，where-般写在 group by 前面

分组之后的条件：用heaving来描述条件，heaving-般写在qroupby后面

同时完成以上两类条件的筛选

同时使用heaving 和 where，且 where 放在 group by 前,heaving 放后面

3、联合查询/多表查询

笛卡尔积是通过排列组合来得到个更大的表，列数是两个表的列数相加，行数是两个表的行数相乘

笛卡尔积是简单无脑的排列组合，把所有的可能都穷举了一遍，包含一些合法的数据，也包含非法、无意义的数据，进行多表查询的时候，需要把有意义的数据筛选出来，无意义的数据过滤

3.1、内连接

1、先对两个表进行笛卡尔积

2、加上连接条件

3、结合需求，进一步添加条件，针对结果进行筛选

4、针对查询到的结果进行精简，只保留需求中关心的列

select 字段 （聚合查询） from 表1 别名1，表2 别名2 where连接条件 and 其他条件 （group.by)

一旦表的数据量大或表的数目多，此时得到的笛卡尔积就非常庞大，针对大表进行笛卡尔积，就会生成大量的临时结果，非常耗时

如果多表查询间涉及的表数目非常多，此时sql就会非常复杂，可读性会大大降低

3.2、外连接

若两个表中，里面的记录存在对应关系，内连接和外连接的结果是一致的，若存在不对应的记录,内连接和外连接就会存在差别

左外连接就是以左侧表为是准,保证左侧表的每个数据都会出现在右侧表，若右侧表中不存在，对应的列就填成null

右侧表同理