系列文章說明MySQL系列文章包含了軟體安裝、具體使用、備份恢複等内容，主要用于記錄個人的學習筆記，主要使用的MySQL版本為5.7.28，伺服器系統版本為CentOS 7.5。本章節為select多表連接配接查詢内容，本章節使用到了 school資料庫。 環境準備： ①建立資料庫
CREATE DATABASE school CHARSET utf8mb4;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 utf8mb4;
③建立教師表
CREATE TABLE teacher(tno INT NOT NULL PRIMARY KEY COMMENT '教師編号',tname VARCHAR(20) NOT NULL COMMENT '教師名字')ENGINE=INNODB CHARSET utf8mb4;
④建立課程表
CREATE TABLE course(cno INT NOT NULL PRIMARY KEY COMMENT '課程編号',cname VARCHAR(20) NOT NULL COMMENT '課程名稱',tno INT NOT NULL COMMENT '教師編号')ENGINE=INNODB CHARSET utf8mb4;
⑤建立成績表
CREATE TABLE sc(sno INT NOT NULL COMMENT '學号',cno INT NOT NULL COMMENT '課程标号',score INT NOT NULL DEFAULT 0 COMMENT '成績')ENGINE=INNODB CHARSET utf8mb4;
⑥插入資料
INSERT INTO student(sno,sname,sage,ssex) VALUES(1,'zhang3',18,'m'),(2,'zhang4',18,'m'),(3,'li4',18,'m'),(4,'wang5',19,'f'),(5,'zh4',18,'m'),(6,'zhao4',18,'m'),(7,'ma6',19,'f'),(8,'yunwei',20,'m'),(9,'arp',20,'f'),(10,'oldp',25,'m');INSERT INTO teacher(tno,tname) VALUES(101,'hansir'),(102,'yanhui'),(103,'hongca');INSERT INTO course(cno,cname,tno) VALUES(1001,'linux',101),(1002,'python',102),(1003,'mysql',103);INSERT INTO sc(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 列1，列2... from 表1 join 表2... on 條件；
多表連接配接
笛卡爾乘積
何為笛卡爾乘積？
例如，A={a,b}, B={0,1,2}，則
A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
B×A={(0, a), (0, b), (1, a), (1, b), (2, a), (2, b)}
最簡單的多表連接配接：
select * from teacher,course;
多表連接配接
内連接配接
内連接配接是多表連接配接中使用最廣泛也是最簡單的連接配接查詢，内連接配接及多表的交集部分。
select * from teacher join course on teacher.tno=course.tno;
多表連接配接
外連接配接
左連接配接(left join)：左表所有的資料+右表滿足條件的資料
select * from course left join teacher on teacher.tno=course.tno;
右連接配接：右表所有的資料+左表滿足條件的資料
select * from course right join teacher on teacher.tno=course.tno;
多表連接配接
實戰
①查詢zhang3學習了幾門課程
select sname,count(sc.sno) from student join sc on student.sno=sc.sno and student.sname='zhang3' group by sc.sno;
②查詢zhang3學習的課程名稱有哪些
select student.sno,student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno and student.sname='zhang3' group by student.sno;
③查詢yanhui老師教的學生名
select teacher.tname,group_concat(student.sname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on teacher.tno=course.tno where teacher.tname='yanhui' group by teacher.tno;
④查詢yanhui所教課程的平均分數
select teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on sc.cno=course.cno where teacher.tname='yanhui' group by teacher.tno,course.cno;
⑤每位老師所教課程的平均分，并按照平均分排序
select teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on sc.cno=course.cno group by teacher.tno order by avg(sc.score);
⑥查詢yanhui老師所教的不及格的學生
select teacher.tname,student.sname from teacher join course on teacher.tno=course.tno and teacher.tname='yanhui' join sc on sc.cno=course.cno and sc.score<60 join student on student.sno=sc.sno;
⑦查詢所有老師所教學生不及格的資訊
select teacher.tname,group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc on sc.cno=course.cno and sc.score<60 join student on student.sno=sc.sno group by teacher.tno;
⑧查詢平均成績大于60分的同學的學号和平均成績
select student.sname,avg(sc.score) from student join sc on student.sno=sc.sno group by sc.sno having avg(sc.score)>60;
⑨查詢所有同學的學号、姓名、選課數、總成績
select student.sno,student.sname,count(sc.sno),sum(sc.score) from student join sc on student.sno=sc.sno group by sc.sno;
⑩查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
select sc.cno,min(sc.score),max(sc.score) from sc group by sc.cno;
end