mysql进阶

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 限制]

1. as 给字段取别名

//as可省略
select  列名 [as] 别名;

2. dual伪表
   为了保证语句的完整性，添加一个不存在的伪表

3. where子句
   支持的运算 > , < , >= , <= , != , = , not , and , or

4. in | not in

select * from stu where address = '上海' or address='北京'；
等价于
select * from stu where address in ( '上海','北京')；

5. between...and | not between...and

select * from stu where age >= 18  and age <= 20；
等价于
select * from stu where age between 18 and 20；

6. is null | is not null

//这里不能用math = null
select * from stu where math is null；

7. 聚合函数

select sum(math) '数学总分', avg(math) '数学平均分', max(math) '数学最高分',min(math) '数学最低分', count(*) '总人数' from stu;

8. 模糊查询

通配符
1.`_`(下划线)表示任意一个字符
2.`%`表示任意个字符
//查找姓张的学生
select * from stu where name like '张%';

9. 排序

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;

10. 分组查询

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|
| --- | --- |
|小红，小绿，小紫 |女|
|小黑，小灰，小军 |男|

11. having条件（对查询结果表再次筛选）

having 和 where区别
where是从数据库中的原始数据进行查找，having是对查询结果集中进行筛选
//此处不能用where，原始数据没有total字段
select sex,count(*) total from stu group by sex having total > 5;

12. 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 多表联查

分类：

1. 内连接
2. 外连接
   • 2.1 左外连接
   • 2.2 右外连接
3. 交叉连接
4. 自然连接

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 外连接

1. 左外连接（left join）
   以左边的表为标准，如果右边的表没有对应的记录，用NULL填充

//语法
select 列名 from 表1 left join 表2 on 表1.公共字段 = 表2.公共字段;

2. 右外连接（right join）
   以右边的表为标准，如果左边的表没有对应的记录，用NULL填充

//语法
select 列名 from 表1 right join 表2 on 表1.公共字段 = 表2.公共字段;

4.3 交叉连接

1. 如果没有连接表达式，返回的是笛卡尔积

select * from t1 cross join t2;

2. 如果有连接表达式，返回的是内连接

select * from t1 cross join t2 where t1.id = t2.id;

4.4 自然连接

自动的判断连接条件，它是通过同名字段来简化内连接和外连接
结论：

1. 表连接通过同名字段来连接的
2. 如果没有同名字段，返回笛卡尔积
3. 会对结果进行整理，整理规则如下：
   • 连接字段保留一个
   • 连接字段放在最前面
   • 左外连接，左表在前；右外连接，右表在前

自然连接分为：

1. 自然内连接 natural join

select * from t1 natural join t2;

2. 自然左外连接 natural left join

select * from t1 natural left join t2;

3. 自然右外连接 natural right join

select * from t1 natural right join t2;

4.5 using()

1. 用来指定连接字段
2. using()也会对连接字段进行整理，整理方式和自然连接一样

//当有多个同名字段时，不能用自然连接，可通过内连接指定连接字段
select * from t1 inner join t2 using(id);

4.6 子查询

子查询的结果作为父查询的条件
1.外面的查询是父查询，括号中的是子查询
2.子查询为父查询提供查询条件

//语法
select 语句 where 条件 (select ... from 表)

例：

1. 下面的例题中子查询返回一个结果，可以用=

//找出笔试成绩等于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));

2. 下面的例题中子查询返回多个结果，需要用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);

3. 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);

4. 子查询分类

• 标量子查询：子查询返回一个结果
• 列子查询：子查询结果返回一个列表
• 行子查询：子查询结果返回多行
• 表子查询：子查询结果当成一个表（必须设置别名）

//子查询返回行
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;