SQL陳述句實戰學習-有解無憂

參考：https://zhuanlan.zhihu.com/p/38354000
再次感謝作者的整理！！

1.資料已提前準備好了，已知有如下4張表：
學生表：student

成績表：score（學號，課程號，成績）

課程表：course

教師表：teacher

2.1 簡單查詢

2.1.1 查詢姓"曹"的學生名單

SELECT * FROM student WHERE `name` LIKE '曹%'

2.1.2 查詢姓名中最后一個是"玉"的學生名單

SELECT * FROM student WHERE `name` LIKE '%玉'

2.1.3 查詢姓名中帶"星"的學生名單

SELECT * FROM student WHERE `name` LIKE '%星%'

2.2 匯總查詢

2.2.1 查詢課程編號為"0002"的總成績

SELECT SUM(grade) as '總成績' FROM score WHERE course_id='0002'

2.2.2 查詢選了課程的學生人數

SELECT COUNT(DISTINCT stu_id)  as '選了課程的學生人數' FROM score # DISTINCT  去重

2.3 分組查詢

2.3.1 查詢各科成績最高和最低得分

SELECT MAX(grade) as '最高分',MIN(grade) as '最低分' FROM score ORDER BY grade

2.3.2 查詢每門課程被選修的學生數

SELECT course_id as '課程號' ,COUNT(stu_id) as '學生人數' FROM score GROUP BY course_id

2.3.3 查詢學生中男、女人數

SELECT sex,COUNT(sex) as '人數' FROM student GROUP BY sex

2.4 帶條件的分組查詢

2.4.1 查詢平均成績大于60分學生的學號和平均成績

SELECT stu_id as '學號', AVG(grade) as '平均成績' FROM score GROUP BY stu_id HAVING AVG(grade) > 60

2.4.2 查詢至少選修兩門課程的學生學號以及課程數目

SELECT stu_id as '學生學號', COUNT(course_id) as '課程數目' FROM score GROUP BY stu_id HAVING COUNT(course_id) > 2

2.4.3 查詢同名同姓學生名單并統計同名人數

SELECT name as '學生名單' ,COUNT( name) as '同名人數' FROM student GROUP BY `name`

2.4.4 查詢不及格的課程并按照課程號從大到小排序

SELECT course_id,grade from score WHERE grade < 85 ORDER BY grade DESC

2.4.5 查詢每門課程的平均成績，結果按照平均成績升序排序，平均成績相同時，按照課程好降序排序

SELECT course_id as '課程號',AVG(grade) as '平均成績' from score GROUP BY course_id ORDER BY AVG(grade) and course_id

2.4.6 查詢其中課程編號為"0003"且分數小于90的學生學號，結果按照分數降序排列

SELECT course_id , stu_id, grade from score WHERE course_id='0003' AND grade < 90 ORDER BY grade DESC

2.4.7 查詢課程號和選修此課程人數，查詢結果按照人數排序降序，若人數相同，按照課程號升序排序

SELECT course_id as '課程號', COUNT(course_id) as '課程人數' from score GROUP BY course_id ORDER BY COUNT(course_id) DESC,course_id ASC

2.4.8 查詢兩門以上成績不滿85分的同學的學號及其平均成績

SELECT stu_id as '學號', AVG(grade) as '平均成績' from score WHERE grade <= 85 GROUP BY stu_id HAVING COUNT(course_id) >= 2

2.4.9 查詢各科成績前兩名的記錄

(SELECT *  from score WHERE course_id = '0001' ORDER BY grade DESC LIMIT 2) union
(SELECT *  from score WHERE course_id = '0002' ORDER BY grade DESC LIMIT 2) union
(SELECT *  from score WHERE course_id = '0003' ORDER BY grade DESC LIMIT 2);

2.5 匯總查詢

2.5.1 查詢學生的成績并進行排名

SELECT stu_id , SUM(grade) FROM score GROUP BY stu_id ORDER BY SUM(grade)

2.5.2 查詢平均成績大于80分的學生的學號和平均成績

SELECT stu_id ,AVG(grade) FROM score GROUP BY stu_id HAVING AVG(grade) > 80

2.5.3 查詢所有課程成績小于85分的學生的學號、姓名

SELECT id , name  FROM student WHERE  id in (SELECT stu_id  FROM score GROUP BY stu_id HAVING AVG(grade) < 85);

2.5.3 查詢沒有學全所有課程的學生的學號、姓名

SELECT id , name  FROM student WHERE  id in ( SELECT stu_id  FROM score GROUP BY stu_id HAVING COUNT(course_id) < 3);

2.5.4 查詢1996年出生的學生名單

SELECT * FROM student WHERE year(brith) = 1996

2.5.5 查詢各學生的年齡

SELECT id,`name`,TIMESTAMPDIFF(year,brith,now()) from student;

2.6 多表查詢

2.6.1 查詢所有學生的學號、姓名、選課數、總成績

SELECT s1.id , s1.`name`, COUNT(s2.course_id) as 'count', SUM(grade) FROM student as s1 JOIN score as s2 WHERE s1.id = s2.stu_id GROUP BY s2.stu_id

2.6.2 查詢平均成績大于85分的所有學生的學號、姓名、平均成績

SELECT s1.id , s1.`name`, AVG(grade) FROM student as s1 JOIN score as s2 WHERE s1.id = s2.stu_id GROUP BY s2.stu_id HAVING  AVG(grade) > 85

2.6.3 查詢學生的選課情況：學號、姓名、課程號、課程名稱

SELECT s1.id as '學號', s1.`name` as '姓名', s2.course_id as '課程號' ,c1.`name` as '課程名稱' FROM student as s1 JOIN score as s2 JOIN course as c1 WHERE s1.id = s2.stu_id  and s2.course_id = c1.id;

2.6.4 查詢出每門課程的大于80得人數和不大于80的人數

SELECT course_id, SUM(CASE WHEN grade > 80 THEN 1 ELSE 0 END) as '大于80', SUM(CASE WHEN grade <= 80 THEN 1 ELSE 0 END) as '小于80' FROM score  GROUP BY course_id

2.6.5 使用分段[90,100],[80-90),[70,80),[60,70)區間統計各科成績，統計各分段人數和，課程號，課程名稱

SELECT s.course_id as '課程號',c1.`name` as '課程號',
sum(case when s.grade >= 60 and s.grade < 70 then 1 else 0 end) as '[60,70)',
sum(case when s.grade >= 70 and s.grade < 80 then 1 else 0 end) as '[70,80)',
sum(case when s.grade >= 80 and s.grade < 90 then 1 else 0 end) as '[80,90)',
sum(case when s.grade >= 90 and s.grade < 100 then 1 else 0 end) as '[90,100)'
from score as s join course as c1 where s.course_id = c1.id group by s.course_id;

2.6.6. 查詢課程編號為"0003"且課程成績在90分以上的學生的學號和姓名

SELECT  s2.id as '學號',s2.`name` as '姓名'
from score as s1 join student as s2 where s1.stu_id = s2.id AND s1.course_id = '0003' AND s1.grade > 90;

2.6.7 資料的行列如何互換？

# 使用max()聚合函式將三個結果中的最大的提取出
select stu_id,
MAX(case course_id when '0001' then grade else 0 end )  as '課程號0001',
max((case course_id when '0002' then grade else 0 end)) as '課程號0002',
max((case course_id when '0003' then grade else 0 end)) as '課程號0003'
FROM score group by stu_id;

2.7 多表連接查詢

2.7.1 查詢課程號為"0001"的課程分數小于90的學生資訊，按照分數降序排列

select s2.id,s2.name,s1.course_id,s1.grade FROM score as s1 JOIN student as s2 
WHERE s1.stu_id = s2.id AND s1.course_id = '0001' HAVING grade < 90 ORDER BY grade desc

2.7.2 查詢不同老師所教的不同課程的平均分從高到低顯示

select c1.teacher_id, s1.course_id, c1.`name`, avg(grade) 
FROM score as s1 JOIN course as c1
WHERE s1.course_id = c1.id GROUP BY c1.teacher_id ORDER BY AVG( s1.grade) DESC

2.7.3 查詢課程名稱為"數學"，且分數低于90的學生姓名和分數

select s2.`name` as '學生姓名', s1.grade as '分數'
FROM score as s1 JOIN course as c1 JOIN student as s2
WHERE s1.course_id = c1.id AND s1.stu_id = s2.id
AND c1.`name` = '數學' and s1.grade < 90

2.7.4 查詢兩門及其以上課程小于85的同學的學號，姓名及其平均成績

select s1.id as '學號',s1.name as '姓名',avg(s2.grade) as '平均成績' 
from student as s1 join score as s2 on s1.id = s2.stu_id and s2.grade < 85 
group by s2.stu_id having count(s1.id) >= 2;

2.7.5 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

SELECT 
DISTINCT s1.stu_id as '學生編號', s1.course_id as '課程編號', s1.grade as '學生成績'
FROM score AS s1 JOIN score AS s2
ON s1.stu_id = s2.stu_id
WHERE s1.course_id != s2.course_id  AND s1.grade = s2.grade

2.7.6 查詢課程編號為“0002”的課程比“0001”的課程成績高的所有學生的學號

SELECT 
DISTINCT  a.stu_id,a.grade as '0002成績',b.grade as '0001成績'
FROM 
(SELECT stu_id,grade FROM score WHERE course_id = '0002' ) as a 
join
(SELECT stu_id,grade FROM score WHERE course_id = '0001' ) as b
ON a.stu_id = b.stu_id 
where a.grade > b.grade;

2.7.7 查詢學過編號為“0001”的課程并且也學過編號為“0002”的課程的學生的學號、姓名

SELECT 
a.stu_id as '學號',s1.`name` as '姓名'
FROM 
(SELECT stu_id FROM score WHERE course_id = '0002' ) as a 
join
(SELECT stu_id FROM score WHERE course_id = '0001' ) as b
ON a.stu_id = b.stu_id 
JOIN student as s1 on s1.id = b.stu_id

2.7.8 查詢學過“陳獨秀”老師所教的所有課的同學的學號、姓名

SELECT 
DISTINCT s1.stu_id as '學號',s2.`name` as '姓名'
FROM 
(SELECT course_id,t1.`name` FROM teacher as t1 JOIN score as s1 on t1.id = s1.course_id WHERE t1.`name` = '陳獨秀') as a
join
score as s1
JOIN 
student as s2
WHERE a.course_id = s1.course_id AND s1.stu_id = s2.id

2.7.9 查詢至少有一門課與學號為“0001”的學生所學課程相同的學生的學號和姓名`

SELECT
s1.id,s1.`name`
FROM student as s1
WHERE s1.id in
(SELECT DISTINCT(stu_id) from score WHERE course_id in (SELECT course_id FROM score WHERE stu_id = '0001'))
AND s1.id != '0001';

2.7.10 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

SELECT
s1.stu_id as '學生號',
MAX(CASE WHEN c1.`name` = '語文' THEN s1.grade ELSE 0 END) as '語文',
MAX(CASE WHEN c1.`name` = '數學' THEN s1.grade ELSE 0 END) as '數學',
MAX(CASE WHEN c1.`name` = '英語' THEN s1.grade ELSE 0 END) as '英語',
avg(s1.grade) as '平均成績'
FROM course as c1 JOIN score as s1
ON c1.id = s1.course_id
GROUP BY s1.stu_id

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/500145.html

標籤：MySQL

上一篇：MySQL實戰45講 11

下一篇：Redis持久化技術淺析