九、联合查询之内联

多表连接查询

案例环境

构建数据库跟表

#创建数据库school
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school


#创建学生表
CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

#创建课程表
CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

#创建成绩表
CREATE TABLE score (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

#创建老师表
CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

插入数据样本

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

INSERT INTO score(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

#验证
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM score;

案例

#统计zhang3学习了多少门课
select student.sname,COUNT(score.cno) 
from student 
join score 
on student.sno = score.sno 
where student.sname = 'zhang3';

#统计zhang3学习的课程有哪些
select student.sname,course.cname 
from student 
join score 
on student.sno = score.sno 
join course
on score.cno = course.cno
where student.sname = 'zhang3';

#使用group_concat()多行转一行，as为别名
select student.sname,group_concat(course.cname) as course_name 
from student 
join score 
on student.sno = score.sno 
join course
on score.cno = course.cno
where student.sname = 'zhang3';

#查询oldguo老师教的学生名
SELECT teacher.tname ,GROUP_CONCAT(student.sname)
FROM student 
JOIN score
ON student.sno=score.sno
JOIN course 
ON score.cno=course.cno
JOIN teacher
ON course.tno=teacher.tno
WHERE teacher.tname='oldguo';

#查询oldguo所教课程的平均分数
select teacher.tname,avg(score.score)
from score
join course 
on score.cno=course.cno
join teacher
on course.tno=teacher.tno
where teacher.tname='oldguo';
#这里需要注意，由于oldguo老师只教了一门课mysql，所以不需要使用group by，如果是多门课需要加上分组
#即group by score.cno

#每个老师所教课程的平均分，并按平均分排序
select teacher.tname,course.cname,avg(score.score) as avg_score
from teacher
join course
on teacher.tno=course.tno
join score
on score.cno=course.cno
group by course.cname,teacher.tname
order by avg_score;
#这里防止一个老师教了多门课所以需要以老师名跟课程名进行分组。

#查询oldguo老师所教课程考试成绩不及格的学生姓名
select teacher.tname,course.cname,student.sname,score.score
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
join teacher
on course.tno=teacher.tno
where teacher.tname='oldguo'
having score.score < 60;

#查询所有老师所教的课程不及格的学生信息
select teacher.tname,course.cname,student.sname,score.score
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
join teacher
on course.tno=teacher.tno
having score.score < 60;

#第二种写法，分组
select teacher.tname,group_concat(concat(course.cname,"课程，",student.sname,"，成绩：",score.score))
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
join teacher
on course.tno=teacher.tno
where score.score < 60
group by teacher.tno;

关于多表连接语法规则

1. 首先找涉及到的所有表
2. 找到表和表之间的关联列
3. 关联条件写在on后面
4. 所有需要查询的信息放在select后
5. 其他的过滤条件如where group by having order by limit 按顺序放

注意: 对多表连接中，驱动表即from后面的表尽量选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（主键或唯一键一般在表设计就做好了）。

别名

分为表别名跟列别名。
使用as定义别名。

表别名

select teacher.tname,course.cname,student.sname,score.score
from student as st
join score as sc
on st.sno=sc.sno
join course as co
on sc.cno=co.cno
join teacher as te
on co.tno=te.tno
having sc.score < 60;

表别名可以全局调用

列别名

select teacher.tname as 老师名字,course.cname as 课程名,student.sname as 学生名,score.score as 分数
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
join teacher
on course.tno=teacher.tno
having 分数 < 60;

列别名可以被having跟order by调用

学习来自：郭老师博客，老男孩深标DBA课程 第三章