MySQL笔记 —— 表连接（三大范式，连接语句，topn问题）

sql的三大范式

1. 第一范式：原子性
   确保每一个字段都是不可分割的最小数据单元
   比如一条数据是 张 20 男 大学 安徽省合肥市xx区xx街道
   像最后一个地址字段就明显可以继续切分成省份——市——区——街道四个字段

2. 第二范式：唯一性
   在第一范式的基础上，除了主键之外的字段都应该依赖于主键
   比如说一个表格的字段是这样的
   
   这样的表格里面，学生姓名和学生年龄依赖于学生学号。而教师姓名和教师工资依赖于教师工号。这样表格中就出现了学生姓名不依赖于教师工号主键，而教师工资不依赖于学生学号主键的情况。

   正确做法是将表格拆分
   

3. 第三范式：冗余性
   在第二范式的基础上，确保每个字段都与主键直接相关，而不是间接相关
   假设字段 A，B，C之间，A是主键，B和C都可以说依赖于A，但是实际上是B依赖于A，C依赖于B，并且根据依赖关系得出C依赖于A，此时就应该去掉字段C，然后将字段C放入以字段B为主键的表中。
   比如
   
   这个表格中的主键是订单号，一个订单号对应一个订单人姓名和一个订单金额。而一个订单号同样也只对应一个订单上的商品。所以商品号是依赖于订单号的，但是商品名和商品颜色都依赖于商品号，间接依赖于订单号。此时应该将这两个字段摘出来放入以商品号为主键的表中。
   

表连接

union 合并查询结果

要求：两张表的字段数量一样，显示的字段以第一个查询语句为基准

-- 先创建两个表并填入数据
create table person1(
id int not null auto_increment,
name varchar(255) not null,
primary key (id)
)engine=innodb;

create table person2(
id int not null auto_increment,
name varchar(255) not null,
primary key (id)
)engine=innodb;

insert into person1 values(001,'zhang');
insert into person1 values(002,'chen');
insert into person1 values(003,'wang');

insert into person2 values(001,'zhang');
insert into person2 values(002,'alice');
insert into person2 values(003,'john');

-- 使用union合并查询
select * from person1
union
select * from person2;

这里因为，两个表中有重复的数据，所以union会只显示去重后的数据，如果想显示全部数据，可以用union all

select * from person1
union all
select * from person2;

左外连接 left join

1. 以左表为基准关联右表中的数据
2. 左表数据匹配数多于右表，右表部分以null补全
   左表数据匹配数少于右表，右表多余部分被裁去不显示

select * from student;

select * from score;

select * from score
left join student
on score.studentId=student.id;

左表即score表的studentid到1010，而右表即student表的id没有1010，则右表缺失部分用null补全

select * from student
left join score
on score.studentId=student.id;

左表即student表的id只到1009，而右表即score表的studentid超过1009，则多余部分不显示

右外连接 right join

1. 以右表为基准关联左表中的数据
2. 左表数据匹配数多于右表，左表多余部分被裁去不显示
   左表数据匹配数少于右表，左表部分以null补全

select * from score
right join student
on score.studentId=student.id;

左表即score表的studentid到1010，而右表即student表的id没有1010，则左表多余部分不显示

select * from student
right join score
on score.studentId=student.id;

左表即student表的id只到1009，而右表即score表的studentid超过1009，则左表缺失部分用null补全

内连接 inner join

求两个表的交集

select * from student
inner join score
on student.id=score.studentId;

全外连接 full join

求并集

select * from student
inner join score
on score.studentId=student.id;

topn

给定学生student表，和成绩score表，分别求取男女生中总成绩的前三名
第一步：连接表

-- 连接学生表和成绩表
select * from student
inner join score
on student.id=score.studentId;

第二步：求每个学生的总成绩

-- 求取每个学生的总成绩
select id,name,sex,sum(score) as s from (select * from student
inner join score
on student.id=score.studentId) as stu
group by id;

第三步：求男女生中的前三名
由于代码太多，所以建议先写一个模板，搭一个架子。先把分组比较的代码写出来，具体的表用x来代替，最后再把x换成第二步里求出的总成绩表

select * from x as a where 3>(select count(*) from x as b where a.sex=b.sex and a.s<b.s);

大体的架子写出来后，用第二步产生的结果，也就是一个虚拟表替换掉x
注意这里用的是子查询的方式，子查询是将一个查询语句嵌套在另一个查询语句中，嵌入时，子查询必须放在小括号里面

select * from (select id,name,sex,sum(score) as s from (select * from student
inner join score
on student.id=score.studentId) as stu
group by id) as a where 3>(select count(*) from (select id,name,sex,sum(score) as s from (select * from student
inner join score
on student.id=score.studentId) as stu
group by id) as b where a.sex=b.sex and a.s<b.s);