1 数据完整性
1.1 保证实体完整性（如：表中行数据无法区分）
- 1.主键约束
- 2.唯一键约束
- 3.自动增长列
1.2 保证域完整性（如：表中列数据类型错误）
- 1.数据类型约束
- 2.非空约束
- 3.默认值约束
1.3 保证引用完整性
- 1.外键约束
2 外键
2.1 外键作用
- 1.主表没有的，从表无法插入
- 2.从表中有的，主表无法删除
- 3.先删除从表，再删除主表
2.2 外键的创建
方法1
//主表
create table stu(
id int primary key,
name varchar(20) not null,
);
//从表
create table stu_score(
num int primary key,
score int unsigned,
foreign key (num) references stu(id),
);
方法2
alter table stu_score add foreign key(num) references stu(id);
2.3 外键的删除
alter table 从表 drop foreign key 外键名 ;
2.4 外键的操作
1.严格操作（前面讲的都是严格操作）
2.级联（set null）：如果主表记录删除或更新，从表置空
3.置空：如果主表记录删除或更新，从表级联
语法：
foreign key (从表字段) references 主表名(字段) [on delete set null] [on update cascade];
3 数据库设计
3.1 实体与实体的关系
1.一对多（表的主键和其他表的非主键建立关系）
2.一对一（表的主键和其他表的主键建立关系）
3.多对多（表的主键和其他表的主键建立关系）
3.2 数据库规范化
1.第一范式：确保每个字段（列）不可再分
| id | address |
|---|---|
| 1 | 中国北京 |
| 2 | 美国纽约 |
应该拆分为
| id | country | city |
|---|---|---|
| 1 | 中国 | 北京 |
| 2 | 美国 | 纽约 |
2.第二范式：非键字段必须依赖键字段
或者说，非主键字段都应该和主键有关系，如果没有关系则可以拆分
下面的天气和主键id没关系，应该删除
| 学生id | 姓名 | 年龄 | 天气 |
|---|---|---|---|
| 1 | 小红 | 10 | 晴 |
| 2 | 小军 | 21 | 晴 |
3.第三范式：消除传递依赖
传递依赖只发生在非主键与非主键之间
3.3 查询语句
//语法：
select [选项] 列名 [from 表名] [where 条件] [order by 排序] [group by 分组] [having 条件] [limit 限制]
- as 给字段取别名
//as可省略
select 列名 [as] 别名;
dual伪表
为了保证语句的完整性，添加一个不存在的伪表where子句
支持的运算 > , < , >= , <= , != , = , not , and , orin | not in
select * from stu where address = '上海' or address='北京'；
等价于
select * from stu where address in ( '上海','北京')；
- between...and | not between...and
select * from stu where age >= 18 and age <= 20；
等价于
select * from stu where age between 18 and 20；
- is null | is not null
//这里不能用math = null
select * from stu where math is null；
- 聚合函数
select sum(math) '数学总分', avg(math) '数学平均分', max(math) '数学最高分',min(math) '数学最低分', count(*) '总人数' from stu;
- 模糊查询
通配符
1.`_`(下划线)表示任意一个字符
2.`%`表示任意个字符
//查找姓张的学生
select * from stu where name like '张%';
- 排序
1.asc:升序（默认）
2.desc：降序
//单列排序
select * from stu where order by math [asc];
//多列排序(按年龄升序，年龄相同的按总分降序)
select *，(math + ch) as '总分' from stu where order by age asc,(math + ch) desc;
- 分组查询
1.如果是分组查询，查询字段必须是分组字段和聚合函数
2.如果查询字段是普通字段，只取第一个值
3.group_concat(name),能连接查询到的分组数据
4.group by后面如果带有多个字段，叫多列分组（会按字段的排列组合分组）
select avg(age) as '年龄' ,address from stu group by address;
|年龄 |address|
| --- | --- |
| 30.0000 |北京|
| 50.0000 |河北|
//查询字段都是普通字段，那么只会查到数据中出现的第一条相关数据
//只查到第一个男生，和第一个女生
select name ,sex from stu group by sex;
select group_concat(name) ,sex from stu group by sex;
| group_concat(name) |sex|
| --- | --- |
|小红，小绿，小紫 |女|
|小黑，小灰，小军 |男|
- having条件（对查询结果表再次筛选）
having 和 where区别
where是从数据库中的原始数据进行查找，having是对查询结果集中进行筛选
//此处不能用where，原始数据没有total字段
select sex,count(*) total from stu group by sex having total > 5;
- limit条件（有点像截取数据）
起始位置默认从0开始，不是从1开始
select * from stu limit 0,2;
13.选项
1.默认是all
2.distinct 去除重复数据
select distinct address from stu;
14.union
作用：将多个select语句结果集纵向联合起来
语法：
select 语句 union [选项] select 语句 union [选项] select 语句
//只要查询的字段数量一致就行，类型可以不一致
select id , name1 from stu1 union select uid , name2 from stu2;
union中的选项默认是distinct
4 多表联查
分类：
- 内连接
- 外连接
- 2.1 左外连接
- 2.2 右外连接
- 交叉连接
- 自然连接
4.1 内连接(Inner join)
取各个表中公共字段相等的数据
//语法1
select 列名 from 表1 inner join 表2 on 表1.公共字段 = 表2.公共字段;
//语法2
select 列名 from 表1,表2 where 表1.公共字段 = 表2.公共字段;
//3表联查
select 列名 from 表1 inner join 表2 on 表1.公共字段 = 表2.公共字 inner join 表3 on 表3.公共字段 = 表2.公共字段 ;
4.2 外连接
- 左外连接（left join）
以左边的表为标准，如果右边的表没有对应的记录，用NULL填充
//语法
select 列名 from 表1 left join 表2 on 表1.公共字段 = 表2.公共字段;
- 右外连接（right join）
以右边的表为标准，如果左边的表没有对应的记录，用NULL填充
//语法
select 列名 from 表1 right join 表2 on 表1.公共字段 = 表2.公共字段;
4.3 交叉连接
- 如果没有连接表达式，返回的是笛卡尔积
select * from t1 cross join t2;
- 如果有连接表达式，返回的是内连接
select * from t1 cross join t2 where t1.id = t2.id;
4.4 自然连接
自动的判断连接条件，它是通过同名字段来简化内连接和外连接
结论：
- 表连接通过同名字段来连接的
- 如果没有同名字段，返回笛卡尔积
- 会对结果进行整理，整理规则如下：
- 连接字段保留一个
- 连接字段放在最前面
- 左外连接，左表在前；右外连接，右表在前
自然连接分为：
- 自然内连接 natural join
select * from t1 natural join t2;
- 自然左外连接 natural left join
select * from t1 natural left join t2;
- 自然右外连接 natural right join
select * from t1 natural right join t2;
4.5 using()
- 用来指定连接字段
- using()也会对连接字段进行整理，整理方式和自然连接一样
//当有多个同名字段时，不能用自然连接，可通过内连接指定连接字段
select * from t1 inner join t2 using(id);
4.6 子查询
子查询的结果作为父查询的条件
1.外面的查询是父查询，括号中的是子查询
2.子查询为父查询提供查询条件
//语法
select 语句 where 条件 (select ... from 表)
例：
- 下面的例题中子查询返回一个结果，可以用
=
//找出笔试成绩等于80分的学生
select * from stu_info where id = (select id from stu_score where score = 80);
//查找分数最高的学生
select * from stu_info where id = (select id from stu_score order by score desc limit 1);
select * from stu_info where id = (select * from stu_score where score = (select max(score) from stu_score));
- 下面的例题中子查询返回多个结果，需要用
in，不能用=
//找出笔试成绩大于等于80分的学生
select * from stu_info where id in (select id from stu_score where score >= 80);
//找出不及格的学生（不包含缺考的）
select * from stu_info where id in (select id from stu_score where score < 60);
//找出没通过的（包含缺考的）
select * from stu_info where id not in (select id from stu_score where score >= 60);
- esists 和 not exists
//如果有人超过80分就显示所有学生
select * from stu_info where exists (select * from stu_score where score >= 80);
//如果没有超过80分就显示所有学生
select * from stu_info where not exists (select * from stu_score where score >= 80);
- 子查询分类
- 标量子查询：子查询返回一个结果
- 列子查询：子查询结果返回一个列表
- 行子查询：子查询结果返回多行
- 表子查询：子查询结果当成一个表（必须设置别名）
//子查询返回行
select * from stu where (sex , math) in (select sex,max(math) from stu group by sex);
//子查询返回一个表
select * from (select * from stu order by math desc) as t1 group by sex;
