《多表操作》
目录
一、表关系
实体间的关系
- 一对一:如人与身份证之间的关系;
- 一对多:如班主任与所带班级学生之间的关系;
- 多对一:如学生与班主任之间的关系(一对多反过来);
- 多对多:如学生选课,一个学生可选多门课,一门课也可以由多个学生选择。
student表初始化(沿用前一章节teacher表)
-- 1.创建表
drop table if exists student;
create table if not exists student(
student_id int primary key auto_increment comment '学生ID',
student_name varchar(20) comment '学生姓名',
chinese int comment '语文',
math int comment '数学',
english int comment '英语',
teacher_id int comment '班主任ID'
)comment '学生表';
-- 2.数据初始化
insert into student values
(1001,'Alice',80,90,100,101),
(1002,'Bob',60,70,80,101),
(1003,'Clover',95,90,80,101),
(1004,'Divid',85,90,100,102),
(1005,'Elen',80,70,40,102),
(1006,'French',80,80,80,109);
二、多表查询
-- 1.自连接
select s.*,t.teacher_name `班主任` from teacher t,student s where t.teacher_id=s.teacher_id;
-- 2.内连接
select * from teacher t inner join student s on t.teacher_id=s.teacher_id;
-- 3.左外连接(左表数据全部显示,右表仅显示匹配数据)
select * from teacher t left join student s on t.teacher_id=s.teacher_id;
-- 4.右外连接(右表数据全部显示,左表仅显示匹配数据)
select * from teacher t right join student s on t.teacher_id=s.teacher_id;
-- 5.全连接(联合左右外连接求并集)
select * from teacher t left join student s on t.teacher_id=s.teacher_id union
select * from teacher t right join student s on t.teacher_id=s.teacher_id;
-- 6.子查询(一个查询的结果是另一个查询的条件)
select * from student where teacher_id in(select teacher_id from teacher where age=25);
三、复杂查询
聚合函数
-- 1.计数
select count(*) from student;
-- 2.求和
select sum(chinese) from student;
-- 3.求平均
select avg(chinese) from student;
-- 4.求最大
select max(chinese) from student;
-- 5.求最小
select min(chinese) from student;
分组查询
-- 分组查询(一般结合聚合函数一起使用,分组依据必须被select)
select teacher_id,avg(chinese) from student group by teacher_id;
-- group_concat():将分组归类的字段拼接成字符串
select teacher_id,group_concat(student_name) from student group by teacher_id;
总结
重点
- 表与表之间的关系;
- 多表联合查询;
- 聚合函数及分组查询。
难点
- 多表联合查询;
- 分组查询。