MySQL常用查詢語句

數據表：

 查詢語句

-- 查詢姓“猴”的學生名單
SELECT * from student WHERE 姓名 like "猴%";
-- 查詢姓“孟”老師的個數
SELECT COUNT(教師姓名) FROM teacher WHERE 教師姓名 LIKE "孟%";
-- 查詢課程編號為“0002”的總成績
SELECT SUM(成績) from score WHERE 課程號=00002;
-- 查詢選了課程的學生人數
SELECT COUNT(DISTINCT 學號) as 學生人數 from score;
-- 查詢各科成績最高和最低的分， 以如下的形式顯示：課程號，最高分，最低分
SELECT 課程號, min(成績) as 最低分, max(成績) as 最高分 from score GROUP BY 課程號;
-- 查詢每門課程被選修的學生數
SELECT 課程號, count(學號) as 學生數 from score  GROUP BY  課程號;
-- 查詢男生、女生人數
SELECT 性別, count(*) from student GROUP BY 性別;
-- 查詢平均成績大於60分學生的學號和平均成績
SELECT 學號, avg(成績) as 平均成績 from score GROUP BY 學號 HAVING AVG(成績) > 60;
-- 查詢至少選修兩門課程的學生學號
SELECT 學號, count(課程號) as 選修科目數 from score GROUP BY 學號 HAVING COUNT(課程號)>=2;
-- 查詢同名同姓學生名單並統計同名人數
SELECT 姓名, count(*) as 同名人數 from student GROUP BY 姓名 HAVING count(*)>=2;
-- 查詢不及格的課程並按課程號從大到小排列
SELECT 課程號,成績 from score WHERE 成績 < 60 ORDER BY 課程號 DESC;
-- 查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列
SELECT 課程號, avg(成績) as 平均成績 from score GROUP BY 課程號 HAVING AVG(成績)  ORDER BY 平均成績 asc, 課程號 desc;
-- 檢索課程編號為“0004”且分數小於60的學生學號，結果按按分數降序排列
SELECT 學號,成績 from score WHERE 課程號=00004 and 成績 < 60 ORDER BY 成績 desc;
-- 統計每門課程的學生選修人數(超過2人的課程才統計)，輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序
SELECT 課程號,count(學號) as 選修人數 from score GROUP BY 課程號 having COUNT(學號) > 2 
ORDER BY count(學號) desc, 課程號 asc;
-- 查詢兩門以上不及格課程的同學的學號及其平均成績
SELECT 學號, AVG(成績) as 平均成績 from score WHERE 成績 < 60 GROUP BY 學號 having COUNT(課程號) > 2 ;
-- 查詢學生的總成績併進行排名
SELECT 學號, SUM(成績) as 總成績 from score GROUP BY 學號 ORDER BY 總成績 asc;
-- 查詢平均成績大於60分的學生的學號和平均成績
SELECT 學號, AVG(成績) as 平均成績 from score  GROUP BY 學號 having AVG(成績)>60;
-- 查詢所有課程成績小於60分學生的學號、姓名
SELECT 學號,姓名 FROM student WHERE 學號 in (SELECT 學號 from score WHERE 成績 < 60);
-- 查詢學全沒有選所有課的學生的學號、姓名
SELECT 學號,姓名 from student WHERE 學號 in (SELECT 學號 from score
GROUP BY 學號 having COUNT(課程號) < (SELECT COUNT(課程號) from course));
-- 查詢出只選修了兩門課程的全部學生的學號和姓名
SELECT 學號,姓名 from student WHERE 學號 in (SELECT 學號 from score GROUP BY 學號 having COUNT(課程號) = 2);
-- 查找1990年出生的學生名單
SELECT 姓名,出生日期 from student where YEAR(出生日期) = '1990';
-- 查詢各學生的年齡
SELECT 學號, TIMESTAMPDIFF(MONTH,出生日期,NOW())/12 as 年齡 FROM student;
-- 找出本月過生日的學生
SELECT 學號 FROM student where MONTH(出生日期) = MONTH(CURRENT_DATE);
-- 查詢所有學生的學號、姓名、選課數、總成績
SELECT a.學號, a.姓名, COUNT(b.課程號) as 選課數, SUM(b.成績) as 總成績 from student as a
LEFT JOIN score as b on a.學號=b.學號 GROUP BY a.學號; 
-- 查詢平均成績大於85的所有學生的學號、姓名和平均成績
SELECT a.學號, a.姓名, AVG(b.成績) as 平均成績 from student as a LEFT JOIN score as b on a.學號=b.學號
GROUP BY a.`學號` having 平均成績>85;
-- 查詢學生的選課情況：學號，姓名，課程號，課程名稱
SELECT a.學號, a.姓名, c.課程號, c.課程名稱 from student as a INNER JOIN score as b on a.`學號` = b.學號
INNER JOIN course as c on b.`課程號`=c.`課程號`;
-- 查詢出每門課程的及格人數和不及格人數
SELECT 課程號, SUM(case WHEN 成績<60 then 1 else 0 end) as 不及格人數,
SUM(case when 成績>=60 THEN 1 else 0 end) as 及格人數 from score GROUP BY 課程號;
-- 使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱
SELECT a.課程號,b.課程名稱,SUM(case WHEN a.成績 BETWEEN 85 and 100 then 1 else 0 end) as '[100-85]',
SUM(case when a.成績 BETWEEN 70 AND 85 then 1 else 0 END) as '[85-70]',
SUM(case WHEN a.成績 BETWEEN 60 AND 70 then 1 ELSE 0 end) as '[70-60]',
SUM(case when a.成績 < 60 then 1 else 0 end) as '[<60]' from score as a RIGHT JOIN course as b on 
a.`課程號`=b.`課程號` GROUP BY a.`課程號`, b.`課程名稱`;
-- 查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名|
SELECT a.學號, a.姓名, b.課程號, b.成績 FROM student as a LEFT JOIN score as b on a.學號=b.`學號`
WHERE b.`課程號`=0003 and b.`成績`>80;
-- 檢索"0001"課程分數小於60，按分數降序排列的學生信息
SELECT a.*, b.成績 FROM student as a INNER JOIN score as b on a.學號=b.學號
WHERE b.`課程號` =00001 AND b.`成績`<60 ORDER BY b.`成績` DESC;
-- 查詢不同老師所教不同課程平均分從高到低顯示
SELECT a.教師號,a.教師姓名, AVG(c.成績) as 平均分 from teacher as a
INNER JOIN course as b on a.教師號=b.教師號 INNER JOIN score as c on b.課程號=c.課程號
GROUP BY a.教師號 ORDER BY AVG(c.`成績`) DESC;
-- 查詢課程名稱為"數學"，且分數低於60的學生姓名和分數
select a.姓名, b.成績 from student as a INNER JOIN score as b on a.學號=b.學號
inner join course as c on b.課程號=c.課程號 where b.成績<60 and c.課程名稱='數學';
-- 查詢任何一門課程成績在70分以上的姓名、課程名稱和分數
SELECT a.姓名, b.課程名稱, c.成績 from student as a inner join score as c on a.學號=c.學號
inner join course as b on b.課程號=c.課程號  WHERE c.`成績` > 70;
-- 查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
SELECT a.學號, b.姓名, AVG(a.成績) as 平均成績 from score as a 
inner join student as b on a.學號=b.學號 WHERE a.`成績` < 60 GROUP BY a.學號 having COUNT(a.`學號`) > 2;