數(shù)據(jù)表

• 1.學(xué)生表 Student(SId,Sname,Sage,Ssex)
  SId 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別
• 2.課程表 Course(CId,Cname,TId)
  CId 課程編號(hào),Cname 課程名稱,TId 教師編號(hào)
• 3.教師表 Teacher(TId,Tname)
  TId 教師編號(hào),Tname 教師姓名
• 4.成績(jī)表 SC(SId,CId,score)
  SId 學(xué)生編號(hào),CId 課程編號(hào),score 分?jǐn)?shù)

創(chuàng)建測(cè)試數(shù)據(jù)

學(xué)生表 Student：

導(dǎo)入數(shù)據(jù)方法：將以下 mysql 語(yǔ)句，完整復(fù)制到 workbench 語(yǔ)句窗口（或者是 mysql 的黑窗口），然后運(yùn)行即可導(dǎo)入，不需要另外創(chuàng)建表，下面表的操作一樣。這些語(yǔ)句第一條是創(chuàng)建表（create table），后面都是插入數(shù)據(jù)到表中（insert into table ）。

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '趙雷' , '1990-01-01' , '男');

insert into Student values('02' , '錢電' , '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('09' , '張三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2017-12-30' , '女');

insert into Student values('12' , '趙六' , '2017-01-01' , '女');

insert into Student values('13' , '孫七' , '2018-01-01' , '女');

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

insert into Course values('01' , '語(yǔ)文' , '02');

insert into Course values('02' , '數(shù)學(xué)' , '01');

insert into Course values('03' , '英語(yǔ)' , '03');

教師表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));

insert into Teacher values('01' , '張三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

成績(jī)表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

練習(xí)題目

1.查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

-1.1 查詢同時(shí)存在" 01 "課程和" 02 "課程的情況

-1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )

-1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況

2.查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

3.查詢?cè)?SC 表存在成績(jī)的學(xué)生信息

4.查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)(沒(méi)成績(jī)的顯示為 null ) 4.1 查有成績(jī)的學(xué)生信息

5.查詢「李」姓老師的數(shù)量

6.查詢學(xué)過(guò)「張三」老師授課的同學(xué)的信息

7.查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息

8.查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息

9.查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程 完全相同的其他同學(xué)的信息

10.查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名

11.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

12.檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息

13.按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

14.查詢各科成績(jī)最高分、最低分和平均分： 以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90 要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

15.按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)保留名次空缺 15.1 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)合并名次

16.查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺 16.1 查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺

17.統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

18.查詢各科成績(jī)前三名的記錄

19.查詢每門課程被選修的學(xué)生數(shù)

20.查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名

21.查詢男生、女生人數(shù)

22.查詢名字中含有「風(fēng)」字的學(xué)生信息

23.查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)

24.查詢 1990 年出生的學(xué)生名單

25.查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

26.查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

27.查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)

28.查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒(méi)成績(jī)，沒(méi)選課的情況）

29.查詢?nèi)魏我婚T課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)

30.查詢不及格的課程

31.查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名

32.求每門課程的學(xué)生人數(shù)

33.假設(shè)成績(jī)不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

34.假設(shè)成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

35.查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)

36.查詢每門功成績(jī)最好的前兩名

37.統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）。

38.檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

39.查詢選修了全部課程的學(xué)生信息

40.查詢各學(xué)生的年齡，只按年份來(lái)算

41.按照出生日期來(lái)算，當(dāng)前月日 < 出生年月的月日則，年齡減一

42.查詢本周過(guò)生日的學(xué)生

43.查詢下周過(guò)生日的學(xué)生

44.查詢本月過(guò)生日的學(xué)生

45.查詢下月過(guò)生日的學(xué)生

答案如下：

1.查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

SELECT * FROM student a
INNER JOIN (
        SELECT s1.sid sid,s1.CId cld_1,s1.score sc_1,s2.CId cld_2,s2.score sc_2
        FROM sc s1
        INNER JOIN sc s2  ON s1.sid = s2.sid
        WHERE s1.cid = '01' AND s2.cid = '02'
        AND s1.`score`>s2.`score`  #s1展示'01'課程，s2展示'02'課程，s1的成績(jī) > s2的成績(jī)，即'01'>'02'的成績(jī)
                        ) b 
ON a.SId = b.sid;

1.1 查詢同時(shí)存在" 01 "課程和" 02 "課程的情況

SELECT * 
FROM sc s1
INNER JOIN sc s2
ON s1.`SId` = s2.`SId` 
AND s1.`CId` = '01' AND s2.`CId`='02'  
#篩選條件后，表1和表2分別表示'01'，'02'課程
INNER JOIN student
ON s1.`SId` = student.`SId`;
#插入學(xué)生表查看學(xué)生信息

1.2查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )

SELECT *
FROM sc s1
LEFT JOIN sc s2
ON s1.sid = s2.sid
AND s2.cid = '02'
#左連接且'AND'條件加在'ON'后面，表示會(huì)取主表的所有內(nèi)容，且僅滿足條件的才進(jìn)行關(guān)聯(lián)，不滿足條件的顯示為NULL
#此題中：將所有s2.cid = '02'的拿去關(guān)聯(lián)，若s1中的沒(méi)有內(nèi)容與s2關(guān)聯(lián)，則顯示為null
WHERE s1.cid = '01';
#關(guān)聯(lián)后，篩選關(guān)聯(lián)后的表中s1.cid='01'的內(nèi)容

1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況

SELECT * FROM sc 
WHERE sid NOT IN (SELECT sid FROM sc WHERE cid ='01')
#條件一：sid不在 "有cid='01'的sid" 中
AND sid IN (SELECT sid FROM sc WHERE cid = '02');
#條件二：sid在 "有cid='02'的sid"

2.查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

SELECT a.sid,b.sname,ag
FROM (
    SELECT sid,AVG(score) AS ag
    FROM sc
    GROUP BY sid
    HAVING ag>=60) a
#篩選平均成績(jī)大于等于60分同學(xué)的sid和平均成績(jī)
INNER JOIN student b
ON a.`SId` = b.`SId`;

3.查詢?cè)趕c表存在成績(jī)的學(xué)生信息

SELECT *
FROM student
WHERE sid IN (SELECT sid FROM sc);

4.查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)(沒(méi)成績(jī)的顯示為 null )

SELECT a.sid,sname,COUNT(cid) counts,SUM(score) sum_score
FROM student a
LEFT JOIN sc b
ON a.`SId` = b.`SId`
GROUP BY a.sid

4.1查有成績(jī)的學(xué)生信息

SELECT *
FROM student
WHERE sid IN (SELECT sid FROM sc);

5.查詢「李」姓老師的數(shù)量

SELECT COUNT(1)
FROM teacher
WHERE tname LIKE '李%'

6.查詢學(xué)過(guò)「張三」老師授課的同學(xué)的信息

#需要連接4表
#1.查詢課程老師為'張三'的cid   教師表和課程表
#2.查詢有成績(jī)且滿足條件1的sid   成績(jī)表
#3.查詢滿足條件2的學(xué)生信息    學(xué)生表
#--------------------------------------------------------------
#sql99語(yǔ)法如下
SELECT s.* 
FROM student s
INNER JOIN sc 
ON s.sid = sc.sid
WHERE cid IN (
    SELECT cid
    FROM course c
    INNER JOIN teacher t
    ON c.tid = t.tid
    WHERE tname = '張三')
#----------------------------------------------------------------
#sql92語(yǔ)法
SELECT s.*
FROM student s,sc,teacher t,course c
WHERE t.tname = '張三'
AND c.tid = t.tid
AND s.sid = sc.sid
AND sc.cid = c.cid;
#直接粗暴連接三張表，然后各自篩選條件即可

7.查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息

SELECT student.*
FROM sc
RIGHT JOIN student
ON sc.`SId` = student.`SId`
GROUP BY student.sid
HAVING  COUNT(cid) <(
        SELECT COUNT(1)
        FROM course);  #所有課程數(shù)量

8.查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息

SELECT DISTINCT student.*
FROM student
INNER JOIN sc
ON student.`SId` = sc.`SId`
WHERE sc.`CId` IN (
        SELECT cid
        FROM sc
        WHERE sid='01')

9.查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
（篩選完全相同的需要使用NOT IN，例如篩選僅有(1,2)的，需要篩選not in( not in(1,2) )，即：沒(méi)有沒(méi)有1和2的。因?yàn)?IN"的作用可以篩選與條件完全相同，或者有其中一個(gè)的）

SELECT s.*
FROM student s
INNER JOIN sc
ON s.sid = sc.sid 
WHERE s.sid NOT IN(
    SELECT sid 
    FROM sc     #篩選沒(méi)有'01'同學(xué)的課程的同學(xué)sid
    WHERE cid NOT IN ( 
        SELECT cid
        FROM sc
        WHERE sid = '01'))
AND NOT s.sid = '01'
#篩選sid不在 "沒(méi)有'01'同學(xué)的課程的同學(xué)的sid" ，且sid !='01'的學(xué)生信息
#此時(shí)，若'01'同學(xué)學(xué)習(xí)的1,2課程，那么篩選后的人必定有1或者2或者1，2均有
#若課程數(shù)大于二則必定包含1、2，
GROUP BY s.sid
HAVING COUNT(cid) = (
    SELECT COUNT(1) 
    FROM sc 
    WHERE sid ='01' 
    GROUP BY sid)

10.查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名

SELECT  Sname
FROM student 
WHERE sid NOT IN (
    SELECT sid 
    FROM sc 
    WHERE cid IN(
        SELECT CID
        FROM course c
        INNER JOIN teacher t
        ON c.Tid = t.Tid
        WHERE Tname='張三'
             )
        );

11.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

SELECT s.sid,sname,AVG(score) AS ag
FROM student s
INNER JOIN sc
ON s.sid = sc.sid
WHERE score<60
GROUP BY s.sid
HAVING COUNT(score)>=2;

12、檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息

SELECT s.*
FROM student s
INNER JOIN sc
ON s.sid = sc.sid 
WHERE cid = '01' AND score<60
ORDER BY score DESC

13、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
(若查詢某人所有成績(jī)以及平均分，則需要先創(chuàng)建一個(gè)平均分的子查詢，然后將所有科目關(guān)聯(lián)子查詢）

SELECT *
FROM student a
LEFT JOIN (
    SELECT sc.*,ag
    FROM sc 
    INNER JOIN (
            SELECT sid,AVG(score) ag 
            FROM sc
            GROUP BY sid) s2
    ON sc.sid=s2.sid) b
ON a.sid = b.sid
ORDER BY ag DESC;

14.查詢各科成績(jī)最高分、最低分和平均分：
以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

SELECT c.cid,cname,COUNT(1) counts,MAX(score) max_socre,MIN(score) min_score,AVG(score) avg_score,
       SUM(IF(score>=60,1,0))/COUNT(1) AS 及格率,
       SUM(IF(score BETWEEN 70 AND 79,1,0))/COUNT(1) AS 中等率,
       SUM(IF(score BETWEEN 81 AND 90,1,0))/COUNT(1) AS 優(yōu)良率,
       SUM(IF(score>=90,1,0))/COUNT(1) AS 優(yōu)秀率
FROM course c
INNER JOIN sc
ON c.cid = sc.cid
GROUP BY c.cid
ORDER BY counts DESC, CAST(c.cid AS SIGNED INTEGER) ASC;  #將字符串cid轉(zhuǎn)變?yōu)閿?shù)值型（其實(shí)這里不轉(zhuǎn)變也可以排序）

15.按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)保留名次空缺
排名函數(shù)：

• <排名方式>() OVER(PARTITION <分組依據(jù)> ORDER BY <排序依據(jù)>)
• 排名方式：
  -- row_number:排名時(shí)序號(hào)連續(xù)并且不重復(fù)，遇到相同的數(shù)值，仍然連續(xù)排名
  -- rank :排名時(shí)，相同數(shù)值取同一序號(hào)，且取較高值，序號(hào)有間隔
  -- dense_rank：排名時(shí)，相同數(shù)值取同一序號(hào)，且取較高值，序號(hào)連續(xù)不間斷

SELECT cid,score,rank() over(PARTITION BY cid ORDER BY score DESC) AS 'rank'
FROM sc;

15.1 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)合并名次
SELECT cid,score,dense_rank() over(PARTITION BY cid ORDER BY score DESC) AS 'rank'
FROM sc;

16、查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺

SELECT a.*,rank() over(ORDER BY sum_score DESC) 'rank'
FROM(
    SELECT sid,SUM(score) sum_score
    FROM sc
    GROUP BY sid) a;

17.統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT cid,SUM(IF(score BETWEEN 85 AND 100,1,0))/COUNT(1) '[100-85]',SUM(IF(score BETWEEN 85 AND 100,1,0)) a,
                SUM(IF(score BETWEEN 70 AND 84,1,0))/COUNT(1) '(85-70]',SUM(IF(score BETWEEN 70 AND 84,1,0)) b,
                SUM(IF(score BETWEEN 60 AND 69,1,0))/COUNT(1) '(70-60]',SUM(IF(score BETWEEN 60 AND 69,1,0)) c,
                SUM(IF(score BETWEEN 0  AND 59,1,0))/COUNT(1) '(60-0]' ,SUM(IF(score BETWEEN 0  AND 59,1,0)) d
FROM sc
GROUP BY cid;

18.查詢各科成績(jī)前三名的記錄

SELECT *
FROM (
    SELECT cid,sid,rank() over(PARTITION BY cid ORDER BY score DESC) 'rank'
    FROM sc) a
WHERE a.rank <= 3;
#解法二：大于此成績(jī)的小于3個(gè)人即為第三名
SELECT *
FROM sc a
WHERE (
    SELECT COUNT(*) 
    FROM sc b 
    WHERE a.cid = b.cid 
    AND b.score > a.score
       )< 3;

19、查詢每門課程被選修的學(xué)生數(shù)

SELECT cid,COUNT(1)
FROM sc
GROUP BY cid;

20、查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名

SELECT s.sid,sname,COUNT(s.sid)
FROM student s
INNER JOIN sc
ON s.sid = sc.`SId`
GROUP BY s.sid
HAVING COUNT(s.sid) = 2

21.查詢男生、女生人數(shù)

SELECT ssex,COUNT(1)
FROM student
GROUP BY ssex;

22、查詢名字中含有「風(fēng)」字的學(xué)生信息

SELECT *
FROM student
WHERE sname LIKE '%風(fēng)%';

23.查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名同姓人數(shù)

SELECT a.sname,COUNT(a.sname) counts
FROM student a
INNER JOIN student b
ON a.sname = b.sname 
AND a.`Ssex` = b.`Ssex`
AND a.sid != b.sid
GROUP BY a.sname ;

24.查詢 1990 年出生的學(xué)生名單

SELECT *
FROM student 
WHERE YEAR(sage)=1990;

25.查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

SELECT cid,AVG(score) ag
FROM sc 
GROUP BY cid
ORDER BY ag DESC,cid ASC

26.查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

SELECT s.sid,sname,AVG(score) ag
FROM student s
INNER JOIN sc
ON s.sid = sc.sid 
GROUP BY s.sid 
HAVING ag>=85;

27、查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)

SELECT sname,cname,score
FROM sc 
INNER JOIN student s
ON s.sid = sc.sid AND score < 60
INNER JOIN course c
ON sc.cid = c.cid AND cname = '數(shù)學(xué)';

28、查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒(méi)成績(jī)，沒(méi)選課的情況）

SELECT *,sc.cid,sc.`score`
FROM student s
LEFT JOIN sc
ON s.sid = sc.sid;

29、查詢?nèi)魏我婚T課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)

SELECT sname,cname,score
FROM sc
INNER JOIN student s
ON s.sid = sc.sid AND score > 70
INNER JOIN course c
ON sc.cid = c.cid;

30.查詢存在不及格的課程

SELECT DISTINCT cid
FROM sc
WHERE score < 60;

31.查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名

SELECT s.sid,sname
FROM student s
INNER JOIN sc
ON s.sid = sc.sid 
AND cid = '01' 
AND score >= 80;

32、求每門課程的學(xué)生人數(shù)

SELECT cid, COUNT(1)
FROM sc
GROUP BY cid;

33、假設(shè)成績(jī)不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

SELECT s.*,score
FROM student s
INNER JOIN sc
ON s.sid = sc.sid
WHERE cid IN (
    SELECT cid
    FROM course c
    INNER JOIN teacher t
    ON c.tid = t.tid
    AND tname = '張三')
ORDER BY score DESC
LIMIT 1;

34、假設(shè)成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

SELECT *    #選第一名的信息
FROM (
    SELECT a.*,dense_rank() over(ORDER BY score DESC) AS ranks    #根據(jù)成績(jī)排名
    FROM (    #創(chuàng)建子查詢：張三老師所授課學(xué)生的信息
        SELECT s.*,score
        FROM student s
        INNER JOIN sc
        ON s.sid = sc.sid
        WHERE cid IN (
            SELECT cid
            FROM course c
            INNER JOIN teacher t
            ON c.tid = t.tid
            AND tname = '張三')) a
    ) b
WHERE ranks = 1;

35.查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)

SELECT *
FROM sc a
INNER JOIN sc b
ON a.sid = b.sid
AND a.cid != b.cid
AND a.score = b.score
GROUP BY a.`SId`,a.cid;
#這里用兩個(gè)group by可以多次分類，達(dá)到去重效果

36.查詢每門功成績(jī)最好的前兩名

SELECT *
FROM sc a
WHERE   (
    SELECT COUNT(1) 
    FROM sc b
    WHERE a.cid=b.cid 
    AND b.score>a.score
    )<2;
#子查詢?yōu)椋捍笥诖顺煽?jī)的數(shù)量

37.統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）

SELECT cid,COUNT(1)
FROM sc 
GROUP BY cid
HAVING COUNT(1) > 5;

38.檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

SELECT *
FROM sc
GROUP BY sid
HAVING COUNT(cid) >=2;

39、查詢選修了全部課程的學(xué)生信息

SELECT *
FROM student s
INNER JOIN sc 
ON s.sid = sc.`SId`
GROUP BY s.sid
HAVING COUNT(cid) =(
    SELECT COUNT(1)
    FROM course);
#解法二:
SELECT *
FROM student a
WHERE (SELECT COUNT(1) FROM sc b WHERE a.sid=b.sid)  
               #這一步，每一個(gè)條件查詢內(nèi)容都會(huì)根據(jù)括號(hào)中where后的條件去關(guān)聯(lián)
             =(SELECT COUNT(1) FROM course)

40.查詢各學(xué)生的年齡，只按年份來(lái)算

SELECT sid,sname,YEAR(NOW())-YEAR(sage) AS age
FROM student;

41、按照出生日期來(lái)算，當(dāng)前月日 < 出生年月的月日則，年齡減一
substr(參數(shù)1，參數(shù)2，參數(shù)3):截取字符串某一位置開(kāi)始的固定字符數(shù)
參數(shù)1：被截取的字符串，參數(shù)2：截取開(kāi)始位置（sql從1開(kāi)始排序），參數(shù)3：截取位數(shù)

SELECT sid,sname,TIMESTAMPDIFF(YEAR,sage,NOW()) AS age
FROM student;
#解法二：
select   *,
    case when substr(sage,6,5)<substr(now(),6,5) then year(now())-year(sage) 
            when substr(sage,6,5)>=substr(now(),6,5) then year(now())-year(sage) -1 
            end as age
from student;

42.查詢本周過(guò)生日的學(xué)生
week:查詢今年的第幾周，參數(shù)為從星期幾開(kāi)始算新的一周

SELECT *
FROM student
WHERE WEEK(sage,1) = WEEK(NOW(),1);

43、查詢下周過(guò)生日的學(xué)生

SELECT *
FROM student
WHERE WEEK(sage,1) = WEEK(NOW(),1) + 1;

44.查詢本月過(guò)生日的學(xué)生

SELECT *
FROM student
WHERE MONTH(sage) = MONTH(NOW());

45.查詢下月過(guò)生日的學(xué)生

SELECT *
FROM student
WHERE MONTH(sage) = MONTH(NOW()) + 1;