單表查詢
多表查詢
第1章 多表查詢套路
1.多表查詢的套路
第一步: 先分析題意 找出需要哪些表
第二步: 找出這些表之間的關(guān)聯(lián)關(guān)系
第三步: 將這些表聯(lián)合成一張大表
第四步: 對(duì)這個(gè)大表在做查詢
2.多表關(guān)聯(lián)技巧
兩張表有直接關(guān)系:
select *
from a
join b
on a.num1 = b.num1
兩張表沒有直接關(guān)系,通過第三張表關(guān)聯(lián):
select *
from a
join b
on a.num1 = b.num1
join c
on b.num2 = c.num2
第2章 練習(xí)題
0.關(guān)聯(lián)模板
SELECT *
FROM 第一張表
JOIN 第二張表
ON 第一張表.字段 = 第二張表.字段
1.顯示老師名字和他教的課程名稱
第一步: 先分析題意 找出需要哪些表
teacher
course
第二步: 找出這些表之間的關(guān)聯(lián)關(guān)系
teacher.tno = course.tno
第三步: 將這些表聯(lián)合成一張大表
select *
from teacher
join course
on teacher.tno = course.tno
第四步: 對(duì)這個(gè)大表在做查詢
SELECT teacher.tname,course.cname
FROM teacher
JOIN course
ON teacher.tno = course.tno
SELECT tname,cname FROM course
JOIN teacher
ON course.tno = teacher.tno
2.統(tǒng)計(jì)每個(gè)學(xué)員的姓名及其學(xué)習(xí)課程的門數(shù),并且打印出學(xué)的課程名稱
第一步: 先分析題意 找出需要哪些表
student sc course
第二步: 找出這些表之間的關(guān)聯(lián)關(guān)系
student.sno = sc.sno
sc.cno = course.cno
第三步: 將這些表聯(lián)合成一張大表
SELECT *
FROM student
JOIN sc
ON student.sno = sc.sno
JOIN course
ON sc.cno = course.cno
第四步: 對(duì)這個(gè)大表在做查詢
SELECT student.sno'學(xué)號(hào)',student.sname'學(xué)生姓名',COUNT(sc.cno)'課程門數(shù)',GROUP_CONCAT(course.cname)'課程名稱'
FROM student
JOIN sc
ON student.sno = sc.sno
JOIN course
ON sc.cno = course.cno
GROUP BY student.sno
SELECT sc.sno'學(xué)號(hào)',student.sname'學(xué)生姓名',COUNT(course.cname)'課程門數(shù)',GROUP_CONCAT(course.cname)'課程名稱' FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
GROUP BY sc.sno
3.每位老師姓名及其教的學(xué)生數(shù)量和學(xué)生名列表
select *
from teacher
join course
on teacher.tno = course.tno
join sc
on course.cno = sc.cno
join student
on sc.sno = student.sno
3.每位老師姓名及其教的學(xué)生數(shù)量和學(xué)生名列表
SELECT course.cname'課程名稱',teacher.tname'老師姓名',COUNT(student.sno)'學(xué)生數(shù)量',GROUP_CONCAT(student.sname)'學(xué)生名單'
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
GROUP BY teacher.tno,course.cname
SELECT teacher.tno'教師號(hào)碼',teacher.tname'教師姓名',COUNT(student.sname)'學(xué)生數(shù)量',GROUP_CONCAT(student.sname)'學(xué)生名列表' FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
JOIN teacher
ON course.tno=teacher.tno
GROUP BY teacher.tno
4.每位老師教所教課程的平均分.最高分以及最低分
SELECT teacher.tname'老師姓名',course.cname'課程名稱',AVG(sc.score)'平均分',MAX(sc.score)'最高分',MIN(sc.score)'最低分'
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
GROUP BY teacher.tno,course.cname
SELECT teacher.tno'教師編號(hào)',teacher.tname'教師姓名',course.cname'課程名稱',AVG(sc.score)'課程平均分',MAX(sc.score)'課程最高分',MIN(sc.score)'課程最低分' FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN teacher
ON course.tno=teacher.tno
GROUP BY teacher.tno,teacher.tname,course.cname
5.查找學(xué)習(xí)了hesw但沒學(xué)習(xí)oldguo課程的學(xué)生名
方法1:
SELECT student.sname,GROUP_CONCAT(teacher.tname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
GROUP BY student.sno
HAVING GROUP_CONCAT(teacher.tname) LIKE '%hesw%'
AND
GROUP_CONCAT(teacher.tname) NOT LIKE '%oldguo%'
SELECT student.sname'學(xué)生姓名',GROUP_CONCAT(teacher.tname)'老師姓名' FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
JOIN teacher
ON course.tno=teacher.tno
GROUP BY student.sname
HAVING GROUP_CONCAT(teacher.tname) LIKE '%hesw%'
AND
GROUP_CONCAT(teacher.tname) NOT LIKE '%oldguo%'
方法2:
6.查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名
SELECT student.sno'學(xué)號(hào)',student.sname'學(xué)生姓名',COUNT(sc.cno)'課程門數(shù)',GROUP_CONCAT(course.cname)'課程名稱'
FROM student
JOIN sc
ON student.sno = sc.sno
JOIN course
ON sc.cno = course.cno
GROUP BY student.sno
HAVING COUNT(sc.cno) = 1
SELECT sc.sno'學(xué)號(hào)',student.sname'學(xué)生姓名',COUNT(course.cname)'課程數(shù)',GROUP_CONCAT(course.cname)'課程名稱' FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
GROUP BY sc.sno,student.sname
HAVING COUNT(course.cname)=1
7.查詢平均成績大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績
SELECT student.sno,student.sname,AVG(sc.score)
FROM student
JOIN sc
ON student.sno = sc.sno
GROUP BY student.sno
HAVING AVG(sc.score) > 85
SELECT sc.sno,student.sname,AVG(sc.score) FROM
sc
JOIN student
ON sc.sno=student.sno
GROUP BY sc.sno,student.sname
HAVING AVG(sc.score) >85
8.統(tǒng)計(jì)每門課程: 優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學(xué)生列表
SELECT
course.cname'課程名稱',
GROUP_CONCAT(CASE WHEN sc.score >85 THEN CONCAT(student.sname,':',sc.score) END)'優(yōu)秀',
GROUP_CONCAT(CASE WHEN sc.score > 70 AND sc.score < 85 THEN CONCAT(student.sname,':',sc.score) END)'良好',
GROUP_CONCAT(CASE WHEN sc.score > 60 AND sc.score < 70 THEN CONCAT(student.sname,':',sc.score) END)'及格',
GROUP_CONCAT(CASE WHEN sc.score < 60 THEN CONCAT(student.sname,':',sc.score) END)'不及格'
FROM student
JOIN sc
ON student.sno = sc.sno
JOIN course
ON course.cno = sc.cno
GROUP BY course.cname
SELECT
course.cname'課程名稱',
GROUP_CONCAT(CASE WHEN sc.score >85 THEN CONCAT(student.sname,':',sc.score) END)'優(yōu)秀',
GROUP_CONCAT(CASE WHEN sc.score > 70 AND sc.score < 85 THEN CONCAT(student.sname,':',sc.score) END)'良好',
GROUP_CONCAT(CASE WHEN sc.score > 60 AND sc.score < 70 THEN CONCAT(student.sname,':',sc.score) END)'及格',
GROUP_CONCAT(CASE WHEN sc.score < 60 THEN CONCAT(student.sname,':',sc.score) END)'不及格'
FROM student as s
JOIN sc as b
ON s.sno = b.sno
JOIN course as c
ON c.cno = b.cno
GROUP BY c.cname
SELECT course.cname'課程名稱',
GROUP_CONCAT(CASE WHEN sc.score >85 THEN student.sname END)'優(yōu)秀',
GROUP_CONCAT(CASE WHEN? sc.score >=70 AND sc.score <=85? THEN student.sname END)'良好',
GROUP_CONCAT(CASE WHEN? sc.score >=60 AND sc.score < 70? THEN student.sname END)'一般',
GROUP_CONCAT(CASE WHEN? sc.score < 60? THEN student.sname END)'不及格'
FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
GROUP BY course.cname
設(shè)置成名字加分?jǐn)?shù)顯示
使用concat
MySQL中concat函數(shù)
MySQL的concat函數(shù)可以連接一個(gè)或者多個(gè)字符串
CONCAT(student.sname,':',sc.score)
SELECT course.cname'課程名稱',
GROUP_CONCAT(CASE WHEN sc.score >85 THEN CONCAT(student.sname,':',sc.score) END)'優(yōu)秀',
GROUP_CONCAT(CASE WHEN? sc.score >=70 AND sc.score <=85? THEN CONCAT(student.sname,':',sc.score) END)'良好',
GROUP_CONCAT(CASE WHEN? sc.score >=60 AND sc.score < 70? THEN CONCAT(student.sname,':',sc.score) END)'一般',
GROUP_CONCAT(CASE WHEN? sc.score < 60? THEN CONCAT(student.sname,':',sc.score) END)'不及格'
FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
GROUP BY course.cname
MySQL 數(shù)據(jù)的表設(shè)置別名
AS
SELECT * FROM
course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
改寫
SELECT * FROM
course AS c
JOIN sc AS s
ON c.cno=s.cno
JOIN student AS st
ON s.sno=st.sno
