MySQL进阶

---

一、数据库约束

约束类型

NOT NULL：指示某列不能存储NULL值
UNIQUE：保证某列的每行必须有唯一的值(唯一约束)
DEFAULT：规定没有给列赋值时的默认值(默认值约束)
PRIMARY KEY：NOT NULL和UNIQUE的结合(主键约束)
FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性(外键约束)
CHECK：保证列中的值符合指定条件

约束存在的目的是为了让数据更加可靠，数据库做了一些检验，在实际试用数据库时，还需要在应用程序上做出检验，两重校验，可以带来更好的可靠性

二、表的设计

三大范式：一对一、一对多、多对多

设计数据库的思路：
1.找到需求中的实体(Java中的面向对象)
2.实体之间的关系(一对一，一对多，多对多，没关系)

多对多表设计时，需要引入一张中间表(用Id表示)
Id可以保证不重复，保存和比较操作开销都比字符串低

三、新增

把一个表的返回结果插入另一张表

insert into 表名 [待插表列名] select 待查表列名 from 待查表名;

四、查询

1.聚合查询

1.1聚合函数

count 返回查询到的数据的数量
sum 返回查询到的数据的总和
avg 返回查询到的数据的平均值
max 返回查询到的数据的最大值
min 返回查询到的数据的最小值

select count(列名) from 表名;

1.2分组查询group by

select可以使用group by子句对指定列进行分组查询，需满足使用group by进行分组查询时，select指定的字段必须是“分组依据字段”，其它字段若想出现在select中则必须包含在聚合函数中

select 查询列 from 表名 group by 列名;
--如果需要加上where条件。需加在group by 前面，这个天健针对于分组查询前的筛选

1.3having

group by子句进行分组以后，需对分组结果再进行条件过滤时，不能使用where，而需要使用having

select 查询列 from 表名 group by 列名 having 条件;

2.联合查询(多表查询)

笛卡尔积：针对两张表，会得到一张新表，列数为两表列数之和，行数为两表行数之和
2,1内连接

select 表.列 from 表1 别名1 join 表2 别名2 on 连接条件 and 其他条件;
select 表.列 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

注： 1.先找出要查找的信息在哪个表
2.针对这些表进行笛卡尔积
3.找到连接条件
4.加上其他条件，最终结果符合要求

2.2外连接
a.左连接
join前的表在左，保证左表的数据都能够被记录，若右表不存在，其他列为null
b.右连接
join后的表在左，保证右表的数据都能够被记录，若左表不存在，其他列为null

--左外连接，表1完全显示
select 字段 from 表1 left join 表2 on 连接条件;
--右外连接
select 字段 from 表1 right join 表2 on 连接条件;

2.3自连接

自连接是指同一张表连接自身进行查询(自己与自己进行笛卡尔积)

select 字段 from 表1，表1 where 条件;
select 字段 from 表1 join 表1 on 条件;

2.4子查询

子查询是指嵌入其他sql语句中的select语句，也叫嵌套查询
a.单行子查询：返回一行子记录的子查询
b.多行子查询：返回多行记录的子查询

select 字段 from 表1 where 字段1 = (select ... from ...);
--in关键字
select ... from 表1 where 字段1 in (select... from ...);
--exists关键字
select ... from 表1 where exists (select ... from ... where 条件);
--临时表
select ... from 表1 (select ... from ...)as tmp where 条件

2.5合并查询

为了合并多个select的执行结果，可以使用集合操作符union，union all，使用union和union all时，前后查询的结果集中，字段需求一致

--union去除重复数据
select ... from ... where 条件 union select .. from ... where 条件;

五、sql查询中各关键字的执行先后顺序

from > on > join > where > group by > with > having > select > distinct > order by > limit