学生表 成绩表 课程表 教师表
學(xué)生表：
Student(s_id,s_name,s_birth,s_sex) –學(xué)生編號(hào),學(xué)生姓名, 出生年月,學(xué)生性別
課程表：
Course(c_id,c_name,t_id) – –課程編號(hào), 課程名稱(chēng), 教師編號(hào)
教師表：
Teacher(t_id,t_name) –教師編號(hào),教師姓名
成績(jī)表：
Score(s_id,c_id,s_s_score) –學(xué)生編號(hào),課程編號(hào),分?jǐn)?shù)
根據(jù)以上信息按照下面要求寫(xiě)出對(duì)應(yīng)的SQL語(yǔ)句。
ps：這些題考察SQL的編寫(xiě)能力，對(duì)于這類(lèi)型的題目，需要你先把4張表之間的關(guān)聯(lián)關(guān)系搞清楚了，最好的辦法是自己在草稿紙上畫(huà)關(guān)聯(lián)圖，然后再編寫(xiě)對(duì)應(yīng)的SQL語(yǔ)句就比較容易了。
下圖是我在草稿紙上畫(huà)的這4張表的關(guān)系圖，不好理解，你可以列舉一些數(shù)據(jù)案例來(lái)輔助理解：
案例數(shù)據(jù)建立參考如下
表名和字段
–1.學(xué)生表
Student(s_id,s_name,s_birth,s_sex) –學(xué)生編號(hào),學(xué)生姓名, 出生年月,學(xué)生性別
–2.課程表
Course(c_id,c_name,t_id) – –課程編號(hào), 課程名稱(chēng), 教師編號(hào)
–3.教師表
Teacher(t_id,t_name) –教師編號(hào),教師姓名
–4.成績(jī)表
Score(s_id,c_id,s_score) –學(xué)生編號(hào),課程編號(hào),分?jǐn)?shù)
建立數(shù)據(jù)庫(kù)、建表和插入數(shù)據(jù)
題目：思路、關(guān)鍵函數(shù)、解題語(yǔ)句、結(jié)果
知識(shí)點(diǎn)
語(yǔ)句表
建表和插入數(shù)據(jù)
新建數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)屬性 utf8 -- UTF-8 Unicode
排序規(guī)則 utf8_unicode_ci
建表（創(chuàng)建查詢(xún)，復(fù)制下方語(yǔ)句到查詢(xún)中，運(yùn)行即可）
-- 學(xué)生表 -- Student（s_id，s_name，s_birth，s_sex） -- 學(xué)生編號(hào),學(xué)生姓名, 出生年月,學(xué)生性別 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); -- 課程表 -- Course(c_id,c_name,t_id) -- 課程編號(hào), 課程名稱(chēng), 教師編號(hào) CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); -- 教師表 -- Teacher(t_id,t_name) -- 教師編號(hào),教師姓名 CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); -- 成績(jī)表 -- Score(s_id,c_id,s_s_score) -- 學(xué)生編號(hào),課程編號(hào),分?jǐn)?shù) CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) )插入數(shù)據(jù)（創(chuàng)建查詢(xún)，復(fù)制下方語(yǔ)句到查詢(xún)中，運(yùn)行即可）
-- 學(xué)生表數(shù)據(jù) insert into Student values('01' , '趙雷' , '1990-01-01' , '男'); insert into Student values('02' , '錢(qián)電' , '1990-12-21' , '男'); insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吳蘭' , '1992-03-01' , '女'); insert into Student values('07' , '鄭竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女'); -- 教師表數(shù)據(jù) insert into Teacher values('t01' , '張三'); insert into Teacher values('t02' , '李四'); insert into Teacher values('t03' , '王五'); -- 課程表數(shù)據(jù) insert into Course values('01' , '語(yǔ)文' , '02'); insert into Course values('02' , '數(shù)學(xué)' , '01'); insert into Course values('03' , '英語(yǔ)' , '03');-- 成績(jī)表數(shù)據(jù) insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98);?
刷新‘表‘就能看見(jiàn)建立的表了
題目
-- 1.查詢(xún)課程編號(hào)為‘01’的課程比‘02’的課程成績(jī)高的所有學(xué)生的學(xué)號(hào)、姓名和各自‘01’‘02’課程成績(jī)
思路：通過(guò)學(xué)生編號(hào)將成績(jī)表的課程‘01’成績(jī)和課程‘02’成績(jī)構(gòu)建一張新表包含：學(xué)生編號(hào)、課程‘01’成績(jī)、課程‘02成績(jī)’
關(guān)鍵函數(shù)：SELECT...FROM...INNER JOIN...ON...WHERE...
解題語(yǔ)句：
SELECT a.s_id ,c.s_name,a.s_score '01',b.s_score '02'FROM (SELECT s_id,c_id,s_score FROM score WHERE c_id='01')AS a INNER JOIN (SELECT s_id,c_id,s_score FROM score WHERE c_id='02')AS b ON a.s_id=b.s_id INNER JOIN student AS c ON a.s_id=c.s_id WHERE a.s_score>b.s_score;結(jié)果：
-- 2、查詢(xún)平均成績(jī)大于60分的學(xué)生的學(xué)號(hào)和平均成績(jī)
思路：以學(xué)生學(xué)號(hào)進(jìn)行分組，算平均成績(jī)，篩選輸出平均成績(jī)大于60分的學(xué)生學(xué)號(hào)和平均成績(jī)
關(guān)鍵函數(shù)：GROUP BY、 AVG（）
解題語(yǔ)句：
SELECT s_id,AVG(s_score) FROM score GROUP BY s_id HAVING AVG(s_score)>60結(jié)果：
-- 3、查詢(xún)所有學(xué)生的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)
思路：學(xué)生表通過(guò)學(xué)生學(xué)號(hào)左關(guān)聯(lián)成績(jī)表，以學(xué)生學(xué)號(hào)進(jìn)行分組，count函數(shù)計(jì)算選課數(shù)，sum函數(shù)計(jì)算總成績(jī)，ifnull函數(shù)將由于左關(guān)聯(lián)產(chǎn)生的成績(jī)表中沒(méi)有的同學(xué)數(shù)據(jù)null變成0
關(guān)鍵函數(shù)：GROUP BY、COUNT()、SUM()、IFNULL( , )、LEFT JOIN
解題語(yǔ)句：
SELECT a.s_id,a.s_name,COUNT(b.c_id),SUM(IFNULL(b.s_score,0)) FROM student AS a LEFT JOIN score AS b ON a.s_id=b.s_id GROUP BY s_id結(jié)果：
-- 4、查詢(xún)姓“張”的老師的個(gè)數(shù)
思路：用like篩選老師姓名，避免姓名重復(fù)通過(guò)計(jì)算老師id來(lái)計(jì)算個(gè)數(shù)
關(guān)鍵函數(shù)：LIKE、COUNT
解題語(yǔ)句：
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '張%'結(jié)果：
-- 5、查詢(xún)沒(méi)學(xué)過(guò)“張三”老師課的學(xué)生的學(xué)號(hào)、姓名
思路1：多層嵌套、子查詢(xún)
通過(guò)教師表查詢(xún)得姓名為‘張三’的教師編號(hào)，用老師編號(hào)在課程表查詢(xún)?cè)摾蠋熃淌诘恼n程編號(hào)，通過(guò)課程編號(hào)在成績(jī)表中查詢(xún)沒(méi)有該課程成績(jī)的學(xué)生編號(hào)，最后通過(guò)學(xué)生編號(hào)在學(xué)生表中得出這些同學(xué)的學(xué)號(hào)姓名
關(guān)鍵函數(shù)：where、in
解題語(yǔ)句1：
SELECT s_id,s_name FROM student WHERE s_id NOT IN( SELECT s_id FROM score WHERE c_id = (SELECT c_id FROM course WHERE t_id= (SELECT t_id FROM teacher WHERE t_name = '張三')))結(jié)果：
思路2：成績(jī)和課程表通過(guò)課程編號(hào)內(nèi)連接，再與學(xué)生成績(jī)表通過(guò)學(xué)生編號(hào)進(jìn)行內(nèi)連接。篩選處有‘張三’的課程成績(jī)的學(xué)生編號(hào)，在學(xué)生表中選出學(xué)生編號(hào)不在上面選中的學(xué)生編號(hào)的學(xué)生姓名編號(hào)和姓名
關(guān)鍵函數(shù)：inner jion 、not in
解題語(yǔ)句2：
SELECT s_id,s_name FROM student WHERE s_id NOT IN( SELECT a.s_id FROM score AS a INNER JOIN course AS c ON c.c_id=a.c_id INNER JOIN teacher AS d ON d.t_id=c.t_id WHERE d.t_name='張三')結(jié)果：
-- 6、查詢(xún)學(xué)過(guò)“張三”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名
理解1：學(xué)過(guò)張三老師的課的學(xué)生信息
思路:構(gòu)建一張表包含：學(xué)生學(xué)號(hào)、學(xué)生姓名、課程成績(jī)、課程老師。再通過(guò)老師姓名篩選
用分組去重（雖然本題沒(méi)有重復(fù)結(jié)果）
關(guān)鍵函數(shù)：inner jion
解題語(yǔ)句：
SELECT st.s_id,st.s_name FROM student AS st INNER JOIN score AS s ON st.s_id=s.s_id INNER JOIN course AS c ON s.c_id=c.c_id INNER JOIN teacher AS t ON t.t_id=c.t_id WHERE t.t_name ='張三' GROUP BY st.s_id結(jié)果：
理解2：學(xué)了張三老師所有課的學(xué)生信息
思路:構(gòu)建一張表包含：學(xué)生學(xué)號(hào)、學(xué)生姓名、課程成績(jī)、課程老師。通過(guò)老師姓名篩選。再選出學(xué)生所選課程數(shù)等于張三老師所教的課程數(shù)的學(xué)生信息
解題語(yǔ)句：
SELECT st.s_id,st.s_name FROM student AS st INNER JOIN score AS s ON st.s_id=s.s_id INNER JOIN course AS c ON s.c_id=c.c_id INNER JOIN teacher AS t ON t.t_id=c.t_id WHERE t.t_name ='張三' GROUP BY st.s_id HAVING COUNT(st.s_id) IN (SELECT COUNT(c_name) FROM course INNER JOIN teacher ON course.t_id=teacher.t_id WHERE teacher.t_name='張三')結(jié)果：
-- 7、查詢(xún)學(xué)過(guò)編號(hào)為“01”的課程并且也學(xué)過(guò)編號(hào)為“02”的課程的學(xué)生的學(xué)號(hào)、姓名
思路1：通過(guò)內(nèi)連接將成績(jī)表課程‘01’和課程‘02’的信息相連接得出新表，從學(xué)生表中獲取學(xué)生編號(hào)在新表中的學(xué)生標(biāo)號(hào)和姓名
關(guān)鍵函數(shù)：inner jion、in
解題語(yǔ)句1：
SELECT s_id,s_name FROM student WHERE s_id IN ( SELECT a.s_id FROM (SELECT s_id FROM score WHERE c_id='01')AS a INNER JOIN (SELECT s_id FROM score WHERE c_id='02')AS b ON a.s_id=b.s_id )結(jié)果：
思路2：在學(xué)生表中選出滿(mǎn)足兩個(gè)條件的學(xué)生學(xué)號(hào)和學(xué)生姓名，條件1：有課程編號(hào)‘01’成績(jī)的學(xué)生編號(hào)，條件2：有課程編號(hào)‘02’成績(jī)的學(xué)生編號(hào)
關(guān)鍵函數(shù)：WHERE...IN...AND
解題語(yǔ)句2：
SELECT s_id,s_name FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id='01') AND s_id IN (SELECT s_id FROM score WHERE c_id='02')結(jié)果：
-- 8、查詢(xún)各門(mén)的總成績(jī)、平均成績(jī)和人數(shù)
關(guān)鍵函數(shù)：sum、avg、count
解題語(yǔ)句：
SELECT c_id '課程編號(hào)',SUM(s_score) '總成績(jī)', AVG(s_score ) '平均成績(jī)',COUNT(s_score) '人數(shù)' FROM score GROUP BY c_id結(jié)果：
-- 9、查詢(xún)所有課程成績(jī)小于60分的學(xué)生的學(xué)號(hào)、姓名
思路：得出各同學(xué)課程成績(jī)小于60分的課程數(shù)，統(tǒng)計(jì)各同學(xué)共學(xué)了幾門(mén)課，選出二者相等的學(xué)生學(xué)號(hào)、姓名
關(guān)鍵函數(shù)：group_by、inner jion
解題語(yǔ)句：
SELECT a.s_id,t.s_name FROM ( SELECT s_id,COUNT(c_id) 'count' FROM score WHERE s_score<60 GROUP BY s_id )AS a INNER JOIN ( SELECT s_id,COUNT(c_id) 'count' FROM score GROUP BY s_id )AS b ON a.s_id=b.s_id INNER JOIN student AS t ON a.s_id=t.s_id WHERE a.count=b.count結(jié)果：
-- 10、查詢(xún)沒(méi)有學(xué)全所有課的學(xué)生的學(xué)號(hào)、姓名
思路：將學(xué)生與成績(jī)表通過(guò)學(xué)生學(xué)號(hào)左連接，計(jì)算每個(gè)學(xué)生的成績(jī)數(shù)，選出小于課程總數(shù)的學(xué)生學(xué)號(hào)和姓名
關(guān)鍵函數(shù)：group_by、count、distinct、left jion
解題語(yǔ)句：
SELECT st.s_id '學(xué)生學(xué)號(hào)',st.s_name '學(xué)生姓名' FROM student AS st LEFT JOIN score AS sc ON st.s_id=sc.s_id GROUP BY st.s_id HAVING COUNT(DISTINCT sc.c_id)<(SELECT count(DISTINCT c_id) FROM course)結(jié)果：
-- 11、查詢(xún)至少有一門(mén)課程與學(xué)號(hào)為“01”的學(xué)生所學(xué)課程相同的其他同學(xué)的學(xué)號(hào)
思路：選出課程與‘01’學(xué)生學(xué)過(guò)的課程相同的學(xué)生學(xué)號(hào)，去重，去掉‘01’同學(xué)
關(guān)鍵函數(shù)：in、and
解題語(yǔ)句：
SELECT s_id,s_name FROM student WHERE s_id in ( SELECT DISTINCT s_id FROM score WHERE c_id in ( SELECT c_id FROM score WHERE s_id='01' ) AND s_id!='01' )結(jié)果：
-- 12、查詢(xún)和“01”號(hào)同學(xué)所學(xué)課程完全相同的其他同學(xué)的學(xué)號(hào)
思路1：將學(xué)號(hào)不為‘01’的學(xué)生課程編號(hào)連接形成新的字段，選出與學(xué)號(hào)為‘01’的學(xué)生課程編號(hào)連接形成字段相同的學(xué)生學(xué)號(hào)
關(guān)鍵函數(shù)：group_concat、group by
解題語(yǔ)句1：
SELECT s_id FROM score WHERE s_id <>'01' GROUP BY s_id HAVING GROUP_CONCAT(c_id)=( SELECT GROUP_CONCAT(c_id) FROM score WHERE s_id='01')結(jié)果：
思路2：將學(xué)號(hào)不為‘01’的學(xué)生成績(jī)表左連接學(xué)號(hào)為‘01’的學(xué)生成績(jī)表，計(jì)算每一個(gè)學(xué)號(hào)不為‘01’的學(xué)生課程數(shù)，選出課程數(shù)和‘01’學(xué)生相同的學(xué)生學(xué)號(hào)
關(guān)鍵函數(shù)：left jion、group_by、count
解題語(yǔ)句：
SELECT a.s_id FROM (SELECT * FROM score WHERE s_id!='01') AS a LEFT JOIN(SELECT * FROM score WHERE s_id='01')AS b ON a.c_id=b.c_id GROUP BY a.s_id HAVING COUNT(b.s_id)=(SELECT COUNT(c_id) FROM score WHERE s_id='01')結(jié)果：
-- 15、查詢(xún)兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
思路：成績(jī)表和學(xué)生表通過(guò)學(xué)生學(xué)號(hào)關(guān)聯(lián)得，查詢(xún)學(xué)號(hào)滿(mǎn)足以下：選出成績(jī)表中小于60分的成績(jī)，再通過(guò)學(xué)生學(xué)號(hào)分組，最后選出此表中大于一門(mén)成績(jī)的學(xué)生生學(xué)號(hào)。
關(guān)鍵函數(shù)：AVG，INNER JOIN ，GROUP BY，COUNT
解題語(yǔ)句：
SELECT a.s_id,a.s_name,AVG(b.s_score) FROM student AS a INNER JOIN score AS b ON a.s_id=b.s_id WHERE a.s_id IN (SELECT s_id FROM score AS a WHERE s_score<60 GROUP BY s_id HAVING COUNT(s_score)>1) GROUP BY a.s_id結(jié)果：
-- 16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
思路：學(xué)生表和成績(jī)表通過(guò)學(xué)生學(xué)號(hào)鏈接，用課程編號(hào)為‘01’和課程成績(jī)小于60兩個(gè)條件篩選，最后通過(guò)分?jǐn)?shù)降序
關(guān)鍵函數(shù)：INNER JOIN，ORDER BY ， DESC
解題語(yǔ)句：
SELECT * FROM student AS a INNER JOIN score AS b ON a.s_id=b.s_id WHERE b.c_id='01' AND b.s_score<60 ORDER BY b.s_score DESC結(jié)果：
-- 17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
思路：用avg計(jì)算平均成績(jī)，按照學(xué)生學(xué)號(hào)進(jìn)行分組排序，利用max來(lái)顯示每一門(mén)課的成績(jī)（max函數(shù)沒(méi)有實(shí)際意義，只是用來(lái)顯示），用case when（課程編號(hào)為‘01’，輸出成績(jī)，否則輸出null）得到每一門(mén)的課程成績(jī)
關(guān)鍵函數(shù)：GROUP BY、ORDER BY、CASE WHEN THEN ELSE
解題語(yǔ)句：
SELECT s_id, MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END)'01', MAX(CASE WHEN c_id='03' THEN s_score ELSE NULL END)'02', MAX(CASE WHEN c_id='02' THEN s_score ELSE NULL END)'03', AVG(s_score) FROM score GROUP BY s_id ORDER BY AVG(s_score) DESC結(jié)果：
-- 18、查詢(xún)各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
-- 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
思路：課程表和成績(jī)表通過(guò)課程編號(hào)鏈接，通過(guò)課程編號(hào)分組，取出課程編號(hào)、課程名、最高分、最低分和平均分。將是否滿(mǎn)足條件的分?jǐn)?shù)通過(guò)case when函數(shù)變成01變量，求和便是符合條件的學(xué)生數(shù)，除以參與這一門(mén)考試的學(xué)生總數(shù)，便是各個(gè)分?jǐn)?shù)段的概率
關(guān)鍵函數(shù)：group by，sum，case when ，count，max，min，avg，inner jion
解題語(yǔ)句：
SELECT b.c_id,b.c_name, MAX(a.s_score)'最高分',MIN(a.s_score)'最低分',AVG(a.s_score)'平均分', SUM(CASE WHEN a.s_score>=60 THEN 1 ELSE 0 END)/COUNT(a.s_id)'及格率', SUM(CASE WHEN a.s_score>=70 AND a.s_score<80 THEN 1 ELSE 0 END)/COUNT(a.s_id)'中等率', SUM(CASE WHEN a.s_score>=80 AND a.s_score<90 THEN 1 ELSE 0 END)/COUNT(a.s_id)'優(yōu)良率', SUM(CASE WHEN a.s_score>=90 THEN 1 ELSE 0 END)/COUNT(a.s_score)'優(yōu)秀率' FROM score AS a INNER JOIN course AS b ON a.c_id=b.c_id GROUP BY a.c_id結(jié)果：
-- 19、按各科成績(jī)進(jìn)行排序，并顯示排名
關(guān)鍵函數(shù)：rank（）
解題語(yǔ)句：
SELECT s_score,rank() over(PARTITION by s_id ORDER BY s_score DESC) FROM score;-- 20、查詢(xún)學(xué)生的總成績(jī)并進(jìn)行排名
關(guān)鍵函數(shù)：sum、group by、order by
解題語(yǔ)句：
SELECT *,SUM(s_score) FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC結(jié)果：
-- 21 、查詢(xún)不同老師所教不同課程平均分從高到低顯示
思路：成績(jī)表通過(guò)課程編號(hào)和課程表進(jìn)行連接為了獲得課程名，再通過(guò)老師編號(hào)和教師表進(jìn)行連接為了獲得老師名，通過(guò)課程編號(hào)或者課程名字進(jìn)行分組，輸出課程編號(hào)、課程名、教師名、平均分，最后按照平均分排序
關(guān)鍵函數(shù)：avg、INNER JOIN、GROUP BY、ORDER BY
解題語(yǔ)句：
SELECT a.c_id,b.c_name,c.t_name,AVG(a.s_score) FROM score AS a INNER JOIN course AS b ON a.c_id=b.c_id INNER JOIN teacher AS c ON b.t_id=c.t_id GROUP BY a.c_id ORDER BY AVG(a.s_score) DESC結(jié)果：
-- 22、查詢(xún)所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)
思路：學(xué)生表和成績(jī)表通過(guò)課程編號(hào)相連接，通過(guò)row_number函數(shù)增加一列課程成績(jī)?cè)谠撜n程的排名，最后通過(guò)子查詢(xún)篩選出排名，即增加的排名列數(shù)字為2，3的數(shù)據(jù)
關(guān)鍵函數(shù)：row_number、ORDER BY、INNER JOIN
解題語(yǔ)句：
SELECT * FROM ( SELECT b.s_id,b.s_name,b.s_birth,,b.s_sex,a.c_id,a.s_score, row_number()over(PARTITION by a.c_id ORDER BY a.s_score DESC) m FROM score AS a INNER JOIN student AS b ON a.s_id=b.s_id ) WHERE m IN(2,3)-- 23、使用分段[100-85],[85-70],[70-60],[<60]來(lái)統(tǒng)計(jì)各科成績(jī)，分別統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù)：課程ID和課程名稱(chēng)
思路：通過(guò)課程編號(hào)將成績(jī)表和課程表連接，再通過(guò)課程編號(hào)分組，得出課程成績(jī)和課程編號(hào)，通過(guò)case when函數(shù)將每個(gè)分?jǐn)?shù)段的人數(shù)轉(zhuǎn)換為0/1或者1/null 形式，通過(guò)sum（0/1）或者count（1/null）得出每個(gè)分?jǐn)?shù)段的人數(shù)
關(guān)鍵函數(shù)：SUM/COUNT、case when、inner jion
解題語(yǔ)句：
SELECT a.c_id,b.c_name, SUM(CASE WHEN a.s_score<60 THEN 1 ELSE 0 END) '不及格', SUM(CASE WHEN a.s_score>=60 AND a.s_score<70 THEN 1 ELSE 0 END) '及格', COUNT(CASE WHEN a.s_score>=70 AND a.s_score<85 THEN 1 ELSE NULL END) '良', COUNT(CASE WHEN a.s_score>=85 THEN 1 ELSE NULL END) '優(yōu)' FROM score AS a INNER JOIN course AS b ON a.c_id=b.c_id GROUP BY a.c_id結(jié)果：
-- 24、查詢(xún)學(xué)生平均成績(jī)及其名次
關(guān)鍵函數(shù)：rank
解題語(yǔ)句：
SELECT s_id,AVG(s_score),rank() over(PARTITION by s_id ORDER BY AVG(s_score)DESC) FROM score-- 25、查詢(xún)各科成績(jī)前三名的記錄（不考慮成績(jī)并列情況）
思路：學(xué)生表和成績(jī)表通過(guò)課程編號(hào)相連接，通過(guò)row_number函數(shù)增加一列課程成績(jī)?cè)谠撜n程的排名，最后通過(guò)子查詢(xún)篩選出排名，即增加的排名列數(shù)字為1,2,3的數(shù)據(jù)
關(guān)鍵函數(shù)：row_number、ORDER BY、INNER JOIN
解題語(yǔ)句：
SELECT * FROM (SELECT b.s_id,b.s_name,b.s_birth,,b.s_sex,a.c_id,a.s_score, row_number()over(PARTITION by a.c_id ORDER BY a.s_score DESC) m FROM score AS a INNER JOIN student AS b ON a.s_id=b.s_id ) a WHERE m IN(1,2,3)-- 26、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)
解題語(yǔ)句：
SELECT c_id,COUNT(c_id) FROM score GROUP BY c_id結(jié)果：
-- 27、查詢(xún)出只有兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名
思路1：通過(guò)學(xué)生學(xué)號(hào)將成績(jī)表和學(xué)生表連接，通過(guò)學(xué)生學(xué)號(hào)分組，計(jì)算學(xué)生選課成績(jī)的數(shù)量，用having篩選出數(shù)量為2的信息，輸出學(xué)生學(xué)號(hào)和姓名
關(guān)鍵函數(shù)：LEFT JOIN、GROUP BY、HAVING、COUNT
解題語(yǔ)句1：
SELECT a.s_id,b.s_name,COUNT(a.c_id) FROM score AS a LEFT JOIN student AS b ON a.s_id=b.s_id GROUP BY a.s_id HAVING COUNT(a.c_id)=2結(jié)果：
思路2：成績(jī)表通過(guò)學(xué)生學(xué)號(hào)分組，選出選課成績(jī)?yōu)?的學(xué)生學(xué)號(hào)；通過(guò)子查詢(xún)，在學(xué)生表中去的學(xué)生學(xué)號(hào)和姓名
關(guān)鍵函數(shù)：GROUP BY
解題語(yǔ)句2：
SELECT s_id,s_name FROM student WHERE s_id IN( (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id)=2) )結(jié)果：
-- 28、查詢(xún)男生、女生人數(shù)
思路1：通過(guò)性別進(jìn)行分組，計(jì)算每個(gè)性別人數(shù)
關(guān)鍵函數(shù)：GROUP BY、count
解題語(yǔ)句：
SELECT s_sex,COUNT(s_sex) FROM student GROUP BY s_sex結(jié)果：
思路2：將性別變成01變量，通過(guò)sum/count得出每個(gè)性別人數(shù)
關(guān)鍵函數(shù)：case when、count、sum
解題語(yǔ)句：
SELECT SUM(CASE WHEN s_sex='男' THEN 1 ELSE 0 END)'男生個(gè)數(shù)', count(CASE WHEN s_sex='女' THEN 1 ELSE NULL END) '女生個(gè)數(shù)' FROM student結(jié)果：
?
-- 29 查詢(xún)名字中含有"風(fēng)"字的學(xué)生信息
思路：用like進(jìn)行字符串匹配
關(guān)鍵函數(shù)：like
解題語(yǔ)句：
SELECT * FROM student WHERE s_name LIKE '%風(fēng)%'結(jié)果：
?
-- 31、查詢(xún)1990年出生的學(xué)生名單
思路1：用like進(jìn)行字符串匹配的方法得到出生日期為1990年
關(guān)鍵函數(shù)：like
解題語(yǔ)句：
SELECT * FROM student WHERE s_birth LIKE '1990%'結(jié)果：
思路2：用year函數(shù)去除日期中的年份，篩選出1990年的數(shù)據(jù)
關(guān)鍵函數(shù)：year
解題語(yǔ)句：
SELECT * FROM student WHERE YEAR(s_birth)=1990結(jié)果：
-- 32、查詢(xún)平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
關(guān)鍵函數(shù)：INNER JOIN...ON、GROUP BY...HAVING
解題語(yǔ)句：
SELECT a.s_id,a.s_name,AVG(b.s_score) FROM student AS a INNER JOIN score AS b ON a.s_id=b.s_id GROUP BY b.s_id HAVING AVG(b.s_score)>=85結(jié)果：
-- 33、查詢(xún)每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排序，平均成績(jī)相同時(shí)，按課程號(hào)降序排列
關(guān)鍵函數(shù)：avg、select...from...group by...order by ...,...
解題語(yǔ)句：
SELECT c_id,AVG(s_score) FROM score GROUP BY c_id ORDER BY AVG(s_score),c_id DESC結(jié)果：
-- 34、查詢(xún)課程名稱(chēng)為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
思路：將學(xué)生表（需要學(xué)生姓名）、成績(jī)表（需要數(shù)學(xué)成績(jī)）、課程表（需要課程名）連接起來(lái)篩選即可
關(guān)鍵函數(shù)：left jion....on
解題語(yǔ)句：
SELECT a.s_name,b.s_score FROM student AS a LEFT JOIN score AS b ON a.s_id=b.s_id LEFT JOIN course AS c ON b.c_id=c.c_id WHERE c.c_name='數(shù)學(xué)' AND b.s_score<60結(jié)果：
?
-- 35、查詢(xún)所有學(xué)生的課程及分?jǐn)?shù)情況
思路1：需要形成的新表包括學(xué)生學(xué)號(hào)、學(xué)生姓名、各科成績(jī)。將學(xué)生表左連接各科成績(jī)表，各科成績(jī)表通過(guò)將成績(jī)表和課程表用課程編號(hào)連接后用課程名篩選后得出
關(guān)鍵函數(shù)：SELECT... FROM...LEFT JOIN...ON...
解題語(yǔ)句1：
SELECT a.s_id'學(xué)號(hào)',a.s_name'姓名',d.s_score'語(yǔ)文',e.s_score'數(shù)學(xué)',f.s_score'英語(yǔ)' FROM student AS a LEFT JOIN (SELECT b.s_id,c.c_name,b.s_score FROM score AS b LEFT JOIN course AS c ON b.c_id=c.c_id WHERE c_name='語(yǔ)文')AS d ON a.s_id=d.s_id LEFT JOIN (SELECT b.s_id,c.c_name,b.s_score FROM score AS b LEFT JOIN course AS c ON b.c_id=c.c_id WHERE c_name='數(shù)學(xué)')AS e ON a.s_id=e.s_id LEFT JOIN (SELECT b.s_id,c.c_name,b.s_score FROM score AS b LEFT JOIN course AS c ON b.c_id=c.c_id WHERE c_name='英語(yǔ)')AS f ON a.s_id=f.s_id結(jié)果：
思路2：成績(jī)表通過(guò)學(xué)生編號(hào)和學(xué)生表連接再通過(guò)課程編號(hào)和課程表連接，通過(guò)學(xué)生編號(hào)進(jìn)行分組，通過(guò)case when輸出各科成績(jī)，判斷課程名稱(chēng)并輸出，由于輸出只會(huì)輸出第一條判斷情況，所以用sum或者max
關(guān)鍵函數(shù)：MAX(CASE WHEN...THEN...ELSE NULL END)、SELECT... FROM...INNER JOIN...ON...
解題語(yǔ)句2:
SELECT c.s_id,c.s_name, MAX(CASE WHEN b.c_name='語(yǔ)文' THEN a.s_score ELSE NULL END)'語(yǔ)文', MAX(CASE WHEN b.c_name='數(shù)學(xué)' THEN a.s_score ELSE NULL END)'數(shù)學(xué)', MAX(CASE WHEN b.c_name='英語(yǔ)' THEN a.s_score ELSE NULL END)'英語(yǔ)' FROM score AS a INNER JOIN course AS b ON a.c_id=b.c_id INNER JOIN student AS c ON a.s_id=c.s_id GROUP BY c.s_id結(jié)果：
-- 36、查詢(xún)課程成績(jī)?cè)?0分以上的學(xué)生姓名、課程名稱(chēng)和分?jǐn)?shù)
思路：將學(xué)生表成績(jī)表課程表相連接
關(guān)鍵函數(shù)：SELECT... FROM...LEFT JOIN...ON...WHERE...
解題語(yǔ)句：
SELECT a.s_name,c.c_name,b.s_score FROM student AS a LEFT JOIN score AS b ON a.s_id=b.s_id LEFT JOIN course AS c ON b.c_id=c.c_id WHERE b.s_score>70結(jié)果：
-- 37、查詢(xún)不及格的課程并按課程號(hào)從大到小排列
關(guān)鍵函數(shù)：SELECT... FROM...INNER JOIN...ON...WHERE...ORDER BY...DESC
解題語(yǔ)句：
SELECT a.s_id,b.s_name,a.c_id,a.s_score FROM score AS a INNER JOIN student AS b ON a.s_id=b.s_id WHERE a.s_score<60 ORDER BY a.c_id DESC結(jié)果：
--?38、查詢(xún)課程編號(hào)為03且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名
關(guān)鍵函數(shù)：SELECT... FROM...INNER JOIN...ON...WHERE...AND...
解題語(yǔ)句：
SELECT a.s_id,b.s_name,a.s_score FROM score AS a INNER JOIN student AS b ON a.s_id=b.s_id WHERE a.s_score>80 AND a.c_id='03'結(jié)果：
-- 39、求每門(mén)課程的學(xué)生人數(shù)
解題語(yǔ)句:
SELECT c_id,COUNT(s_score) FROM score GROUP BY c_id結(jié)果：
-- 40、查詢(xún)選修“張三”老師所授課程的學(xué)生中成績(jī)最高的學(xué)生姓名及其成績(jī)
思路：用窗口函數(shù)，rank
?
-- 41.查詢(xún)不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)
思路：從成績(jī)表中通過(guò)用學(xué)生學(xué)號(hào)分組計(jì)算課程成績(jī)數(shù)來(lái)選出至少學(xué)了有兩門(mén)課的學(xué)生與成績(jī)表內(nèi)連接再通過(guò)學(xué)生學(xué)號(hào)和成績(jī)分組篩選只有一個(gè)數(shù)據(jù)的學(xué)生學(xué)號(hào)
關(guān)鍵函數(shù)：INNER JOIN、GROUP BY、HAVING、COUNT(DISTINCT ...)
解題語(yǔ)句:
SELECT s_id FROM (SELECT a.s_id,a.s_score FROM score AS a INNER JOIN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(DISTINCT c_id)>1)AS b ON a.s_id=b.s_id GROUP BY a.s_id,a.s_score )AS c GROUP BY s_id HAVING COUNT(s_id)=1結(jié)果：
-- 43、統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）。
-- 要求輸出課程號(hào)和選修人數(shù)，查詢(xún)結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列
關(guān)鍵函數(shù)：COUNT()、GROUP BY、HAVING、ORDER BY
解題語(yǔ)句:
SELECT c_id,COUNT(s_score) FROM score GROUP BY c_id HAVING COUNT(s_score)>5 ORDER BY COUNT(s_score) DESC,c_id結(jié)果：
-- 44、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)和選課數(shù)
關(guān)鍵函數(shù)：COUNT()、GROUP BY、HAVING
解題語(yǔ)句：
SELECT s_id,COUNT(s_score) FROM score GROUP BY s_id HAVING COUNT(s_score)>=2結(jié)果：
-- 45、 查詢(xún)選修了全部課程的學(xué)生信息
思路：由于要獲得學(xué)生信息就通過(guò)學(xué)生學(xué)號(hào)將學(xué)生表和成績(jī)表連接，將新表通過(guò)學(xué)生學(xué)號(hào)進(jìn)行分組，篩選條件為學(xué)生課程數(shù)等于課程表中的課程數(shù)
關(guān)鍵函數(shù)：LEFT JOIN、GROUP BY、COUNT(DISTINCT ...)
解題語(yǔ)句：
SELECT * FROM student AS a LEFT JOIN score AS b ON a.s_id=b.s_id GROUP BY a.s_id HAVING COUNT(DISTINCT c_id) =( SELECT COUNT(c_id) FROM course)結(jié)果：
-- 46、查詢(xún)各學(xué)生的年齡（精確到月份）
關(guān)鍵函數(shù)：DATEDIFF()
解題語(yǔ)句：
SELECT s_id,s_birth,DATEDIFF('2020-5-20',s_birth)/365 FROM student結(jié)果：
?
-- 50、查詢(xún)下個(gè)月過(guò)生日的學(xué)生
思路：用now()獲得現(xiàn)在的時(shí)間month取出現(xiàn)在的月份，生日的月份等于通過(guò)round取余數(shù)加一則是下周過(guò)生日
關(guān)鍵函數(shù)：=MONTH()、date()、NOW()、%
解題語(yǔ)句：
SELECT * FROM student WHERE MONTH(s_birth)=MONTH(date(NOW()))%12+1結(jié)果：
知識(shí)點(diǎn)：
1.inner jion、right jion、left jion的區(qū)別
inner join為“有效的連接”，就是根據(jù)on后面的關(guān)聯(lián)條件，兩張表中都有的數(shù)據(jù)才會(huì)顯示
left join為主表全顯示，連接后的表看on后面的選擇條件，left join后面的條件，并不會(huì)影響左表的數(shù)據(jù)顯示，左表數(shù)據(jù)會(huì)全部顯示出來(lái)，連接的表如果沒(méi)有數(shù)據(jù)，則全部顯示為null
right join為“主表看on,后表全顯”(右表數(shù)據(jù)不受影響)，即右表數(shù)據(jù)全部顯示，主表數(shù)據(jù)看on后面的選擇條件
?
2.字符匹配
_代表一個(gè)字符， %代表0個(gè)及以上字符
開(kāi)頭是m m%
結(jié)尾是m %m
第二個(gè)字母為m _m%
非首字母字母有m _%m%
?
3.rank，dense_rank，row_number的區(qū)別
rank(跳躍排序)
分?jǐn)?shù) 排名
92 1
82 2
82 2
67 4
dense_rank（連續(xù)排序）
分?jǐn)?shù) 排名
92 1
82 2
82 2
67 3
row_number（無(wú)重復(fù)值排序）
分?jǐn)?shù) 排名
92 1
82 2
82 3
67 4
?
語(yǔ)句表：
1、插入數(shù)據(jù)：
insert into?表名?values( )?#輸入的值要跟原表對(duì)應(yīng)
insert into Teacher values('02' , '李四')
?
2、查詢(xún)表中的數(shù)據(jù)：
select?*?from?表名 # *號(hào)代表取全部列的數(shù)據(jù)
select * from student
?
select?指定列?from?表名
SELECT t_id,s_score FROM score
?
查詢(xún)表中數(shù)據(jù)的統(tǒng)計(jì)量：min、max、avg、count、sum，
select?avg(列名)?from?表名
SELECT c_id '課程編號(hào)',SUM(s_score) '總成績(jī)',AVG(s_score ) '平均成績(jī)',COUNT(s_score) '人數(shù)' FROM score
GROUP BY c_id
?
條件查詢(xún)：
select?指定列?from?表名?where?條件
SELECT?s_id?FROM?score?WHERE?s_id <>'01'
條件符號(hào)：
（1）>大于 ；<小于； =等于； <>和!=不等于
（2）like 字符匹配 select?*?from?teacher?where?t_name?like?'張%'
（3）between...and 區(qū)間 SELECT?*?FROM?score?WHERE?s_score?BETWEEN?70?AND?90
（4）in 指定數(shù)據(jù)集作為條件，也常用于子查詢(xún)
?
3、GROUP BY 語(yǔ)句
根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組，分組后的輸出的統(tǒng)計(jì)量是每一組的統(tǒng)計(jì)量
SELECT?列名或統(tǒng)計(jì)量?FROM?表名?GROUP BY?列名
SELECT c_id,SUM(s_score),AVG(s_score ),COUNT(s_score)
FROM score
GROUP BY c_id
?
GROUP BY后的條件查詢(xún)用having
?
4、ORDER BY 語(yǔ)句
排序
SELECT *,SUM(s_score) FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC
?
5、year、month、day、now語(yǔ)句
用來(lái)從日期中提取需要的年/月/日
SELECT?*?FROM?student?WHERE?YEAR(s_birth)=1990；
SELECT?*?FROM?student?WHERE MONTH(s_birth)=01；
SELECT?*?FROM?student?WHERE DAY(s_birth)='01'；
條件可以用數(shù)字或者字符串
日期類(lèi)型可以是YYYY-MM-DD、YYYYMMDD、YYYY/MM/DD
2020-05-20、20200520、2020/05/20
注意：對(duì)于YYYYMMDD這種格式日期，0不能少，一月份必須是01不能是1，但年份可以是后兩位，即格式為YYMMDD
now()
獲得當(dāng)前時(shí)間有時(shí)分秒
?
6、LIMIT語(yǔ)句
用于選取第幾行數(shù)據(jù)，SQL從0開(kāi)始計(jì)數(shù)
SELECT?c_id?FROM?score
LIMIT 0，1?#表示從0開(kāi)始（第一行開(kāi)始），選取第一行數(shù)據(jù)
?
7、DATEDIFF語(yǔ)句
用于計(jì)算日期間隔
SELECT s_id,s_birth,DATEDIFF('2020-5-20',s_birth)/365 FROM student
?
?
總結(jié)
以上是生活随笔為你收集整理的学生表 成绩表 课程表 教师表的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： C++：最小二乘法拟合直线
- 下一篇： 地震频繁