系列文章說明
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
⑦查詢所有老師所教學生不及格的資訊
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分的同學的學號和平均成績
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