<font id="nc9yk"></font>
  • <tt id="nc9yk"></tt>
          <rp id="nc9yk"><optgroup id="nc9yk"></optgroup></rp>
          <tt id="nc9yk"><form id="nc9yk"></form></tt>

            <cite id="nc9yk"></cite>

            Mysql Sql 語句練習題(50道)

             更新時間:2020年12月29日 14:29:55   投稿:mdxy-dxy  
            mysql一直作為比較熱門的數據庫存儲,搭配php使用簡直是絕配,mysql的sql語句也是很重要的一門課,這里為大家分享一下sql語句,大家可以試試

            表名和字段

            –1.學生表
            Student(s_id,s_name,s_birth,s_sex) –學生編號,學生姓名, 出生年月,學生性別
            –2.課程表
            Course(c_id,c_name,t_id) – –課程編號, 課程名稱, 教師編號
            –3.教師表
            Teacher(t_id,t_name) –教師編號,教師姓名
            –4.成績表
            Score(s_id,c_id,s_score) –學生編號,課程編號,分數

            測試數據

            --建表
            --學生表
            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`)
            );
            --課程表
            CREATE TABLE `Course`(
            `c_id` VARCHAR(20),
            `c_name` VARCHAR(20) NOT NULL DEFAULT '',
            `t_id` VARCHAR(20) NOT NULL,
            PRIMARY KEY(`c_id`)
            );
            --教師表
            CREATE TABLE `Teacher`(
            `t_id` VARCHAR(20),
            `t_name` VARCHAR(20) NOT NULL DEFAULT '',
            PRIMARY KEY(`t_id`)
            );
            --成績表
            CREATE TABLE `Score`(
            `s_id` VARCHAR(20),
            `c_id` VARCHAR(20),
            `s_score` INT(3),
            PRIMARY KEY(`s_id`,`c_id`)
            );
            --插入學生表測試數據
            insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
            insert into Student values('02' , '錢電' , '1990-12-21' , '男');
            insert into Student values('03' , '孫風' , '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' , '女');
            --課程表測試數據
            insert into Course values('01' , '語文' , '02');
            insert into Course values('02' , '數學' , '01');
            insert into Course values('03' , '英語' , '03');
            
            --教師表測試數據
            insert into Teacher values('01' , '張三');
            insert into Teacher values('02' , '李四');
            insert into Teacher values('03' , '王五');
            
            --成績表測試數據
            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);
            

            表數據如下

            student 學生表:

            s_id s_name s_birth s_sex
            01 趙雷 1990-01-01
            02 錢電 1990-12-21
            03 孫鳳 1990-05-20
            04 李云 1990-08-06
            05 周梅 1991-12-12
            06 吳蘭 2017-12-13
            07 鄭竹 1989-07-01
            08 王菊 1990-01-20
            09 趙雷 1990-01-21
            10 趙雷 1990-01-22

            score 分數表:

            s_id c_id s_score
            01 01 80
            01 02 90
            01 03 99
            02 01 70
            02 02 60
            02 03 80
            03 01 80
            03 02 80
            03 03 80
            04 01 50
            04 02 30
            04 03 20
            05 01 76
            05 03 87
            06 01 31
            06 03 34
            07 03 89
            07 01 98

            course 課程表

            c_id c_name t_id
            01 語文 02
            02 數學 01
            03 英語 03

            teacher 老師表:

            t_id t_name
            01 張三
            02 李四
            03 王五
            -- 準備條件,去掉 sql_mode 的 ONLY_FULL_GROUP_BY 否則此種情況下會報錯:
            -- Expression #1 of select list is not in group by clause and contains nonaggregated column 'userinfo.
            -- 原因:
            -- MySQL 5.7.5和up實現了對功能依賴的檢測。如果啟用了only_full_group_by SQL模式(在默認情況下是這樣),
            -- 那么MySQL就會拒絕選擇列表、條件或順序列表引用的查詢,這些查詢將引用組中未命名的非聚合列,而不是在功能上依賴于它們。
            -- (在5.7.5之前,MySQL沒有檢測到功能依賴項,only_full_group_by在默認情況下是不啟用的。關于前5.7.5行為的描述,請參閱MySQL 5.6參考手冊。)
            -- 執行以下個命令,可以查看 sql_mode 的內容。
            SHOW SESSION VARIABLES;
            SHOW GLOBAL VARIABLES;
            select @@sql_mode;
            -- 更改
            set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
            set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
            

            練習題和sql

            -- 1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數 
            select st.*,sc.s_score as '語文' ,sc2.s_score '數學' 
            from student st
            left join score sc on sc.s_id=st.s_id and sc.c_id='01' 
            left join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02' 
            where sc.s_score>sc2.s_score
            
            -- 2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數
            select st.*,sc.s_score '語文',sc2.s_score '數學' from student st
            left join score sc on sc.s_id=st.s_id and sc.c_id='01'
            left join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'
            where sc.s_score<sc2.s_score
            
            -- 3、查詢平均成績大于等于60分的同學的學生編號和學生姓名和平均成績
            select st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) cjScore from student st
            left join score sc on sc.s_id=st.s_id
            group by st.s_id having AVG(sc.s_score)>=60
            
            -- 4、查詢平均成績小于60分的同學的學生編號和學生姓名和平均成績
              -- (包括有成績的和無成績的)
            select st.s_id,st.s_name,(case when ROUND(AVG(sc.s_score),2) is null then 0 else ROUND(AVG(sc.s_score)) end ) cjScore from student st
            left join score sc on sc.s_id=st.s_id
            group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL
            
            -- 5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績
            select st.s_id,st.s_name,count(c.c_id),( case when SUM(sc.s_score) is null or sum(sc.s_score)="" then 0 else SUM(sc.s_score) end) from student st
            left join score sc on sc.s_id =st.s_id 
            left join course c on c.c_id=sc.c_id
            group by st.s_id
            
            -- 6、查詢"李"姓老師的數量 
            select t.t_name,count(t.t_id) from teacher t
            group by t.t_id having t.t_name like "李%"; 
            
            -- 7、查詢學過"張三"老師授課的同學的信息 
            select st.* from student st 
            left join score sc on sc.s_id=st.s_id
            left join course c on c.c_id=sc.c_id
            left join teacher t on t.t_id=c.t_id
             where t.t_name="張三"
            
            -- 8、查詢沒學過"張三"老師授課的同學的信息 
             -- 張三老師教的課
             select c.* from course c left join teacher t on t.t_id=c.t_id where t.t_name="張三"
             -- 有張三老師課成績的st.s_id
             select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name="張三")
             -- 不在上面查到的st.s_id的學生信息,即沒學過張三老師授課的同學信息
             select st.* from student st where st.s_id not in(
             select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name="張三")
             )
            
            -- 9、查詢學過編號為"01"并且也學過編號為"02"的課程的同學的信息
            select st.* from student st 
            inner join score sc on sc.s_id = st.s_id
            inner join course c on c.c_id=sc.c_id and c.c_id="01"
            where st.s_id in (
            select st2.s_id from student st2 
            inner join score sc2 on sc2.s_id = st2.s_id
            inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02"
            )
            
            
            網友提供的思路(厲害呦~):
            SELECT st.*
            FROM student st
            INNER JOIN score sc ON sc.`s_id`=st.`s_id`
            GROUP BY st.`s_id`
            HAVING SUM(IF(sc.`c_id`="01" OR sc.`c_id`="02" ,1,0))>1
            
            -- 10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息
            select st.* from student st 
            inner join score sc on sc.s_id = st.s_id
            inner join course c on c.c_id=sc.c_id and c.c_id="01"
            where st.s_id not in (
            select st2.s_id from student st2 
            inner join score sc2 on sc2.s_id = st2.s_id
            inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02"
            )
            
            -- 11、查詢沒有學全所有課程的同學的信息
             -- 太復雜,下次換一種思路,看有沒有簡單點方法
             -- 此處思路為查學全所有課程的學生id,再內聯取反面
            select * from student where s_id not in (
            select st.s_id from student st 
            inner join score sc on sc.s_id = st.s_id and sc.c_id="01"
            where st.s_id in (
            select st2.s_id from student st2 
            inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id="02"
            ) and st.s_id in (
            select st2.s_id from student st2 
            inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id="03"
            ))
            -- 來自一樓網友的思路,左連接,根據學生id分組過濾掉 數量小于 課程表中總課程數量的結果(show me his code),簡潔不少。
            select st.* from Student st
            left join Score S
            on st.s_id = S.s_id
            group by st.s_id
            having count(c_id)<(select count(c_id) from Course)
            
            
            
            
            
            -- 12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息
            select distinct st.* from student st 
            left join score sc on sc.s_id=st.s_id
            where sc.c_id in (
            select sc2.c_id from student st2
            left join score sc2 on sc2.s_id=st2.s_id
            where st2.s_id ='01'
            )
            
            -- 13、查詢和"01"號的同學學習的課程完全相同的其他同學的信息
            select st.* from student st 
            left join score sc on sc.s_id=st.s_id
            group by st.s_id
            having group_concat(sc.c_id) = 
            (
            select group_concat(sc2.c_id) from student st2
            left join score sc2 on sc2.s_id=st2.s_id
            where st2.s_id ='01'
            )
            
            -- 14、查詢沒學過"張三"老師講授的任一門課程的學生姓名
            select st.s_name from student st 
            where st.s_id not in (
            select sc.s_id from score sc 
            inner join course c on c.c_id=sc.c_id
            inner join teacher t on t.t_id=c.t_id and t.t_name="張三"
            )
            
            -- 15、查詢兩門及其以上不及格課程的同學的學號,姓名及其平均成績
            select st.s_id,st.s_name,avg(sc.s_score) from student st
            left join score sc on sc.s_id=st.s_id
            where sc.s_id in (
            select sc.s_id from score sc 
            where sc.s_score<60 or sc.s_score is NULL
            group by sc.s_id having COUNT(sc.s_id)>=2
            )
            group by st.s_id
            
            -- 16、檢索"01"課程分數小于60,按分數降序排列的學生信息
            select st.*,sc.s_score from student st 
            inner join score sc on sc.s_id=st.s_id and sc.c_id="01" and sc.s_score<60
            order by sc.s_score desc
            
            -- 17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
             -- 可加round,case when then else end 使顯示更完美
            select st.s_id,st.s_name,avg(sc4.s_score) "平均分",sc.s_score "語文",sc2.s_score "數學",sc3.s_score "英語" from student st
            left join score sc on sc.s_id=st.s_id and sc.c_id="01"
            left join score sc2 on sc2.s_id=st.s_id and sc2.c_id="02"
            left join score sc3 on sc3.s_id=st.s_id and sc3.c_id="03"
            left join score sc4 on sc4.s_id=st.s_id
            group by st.s_id 
            order by SUM(sc4.s_score) desc
            
            -- 18.查詢各科成績最高分、最低分和平均分:以如下形式顯示:課程ID,課程name,最高分,最低分,平均分,及格率,中等率,優良率,優秀率
            -- 及格為>=60,中等為:70-80,優良為:80-90,優秀為:>=90
            select c.c_id,c.c_name,max(sc.s_score) "最高分",MIN(sc2.s_score) "最低分",avg(sc3.s_score) "平均分" 
            ,((select count(s_id) from score where s_score>=60 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "及格率"
            ,((select count(s_id) from score where s_score>=70 and s_score<80 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "中等率"
            ,((select count(s_id) from score where s_score>=80 and s_score<90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "優良率"
            ,((select count(s_id) from score where s_score>=90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "優秀率"
            from course c
            left join score sc on sc.c_id=c.c_id 
            left join score sc2 on sc2.c_id=c.c_id 
            left join score sc3 on sc3.c_id=c.c_id 
            group by c.c_id
            
            -- 19、按各科成績進行排序,并顯示排名(實現不完全)
            -- mysql沒有rank函數
            -- 加@score是為了防止用union all 后打亂了順序
            select c1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1 from (select c.c_name,sc.* from course c 
            left join score sc on sc.c_id=c.c_id
            where c.c_id="01" order by sc.s_score desc) c1 ,
            (select @i:=0) a
            union all 
            select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 from (select c.c_name,sc.* from course c 
            left join score sc on sc.c_id=c.c_id
            where c.c_id="02" order by sc.s_score desc) c2 ,
            (select @ii:=0) aa 
            union all
            select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 from (select c.c_name,sc.* from course c 
            left join score sc on sc.c_id=c.c_id
            where c.c_id="03" order by sc.s_score desc) c3;
            set @iii=0;
            
            
            -- 20、查詢學生的總成績并進行排名
            select st.s_id,st.s_name
            ,(case when sum(sc.s_score) is null then 0 else sum(sc.s_score) end)
             from student st
            left join score sc on sc.s_id=st.s_id
            group by st.s_id order by sum(sc.s_score) desc
            
            -- 21、查詢不同老師所教不同課程平均分從高到低顯示 
            select t.t_id,t.t_name,c.c_name,avg(sc.s_score) from teacher t 
            left join course c on c.t_id=t.t_id 
            left join score sc on sc.c_id =c.c_id
            group by t.t_id
            order by avg(sc.s_score) desc
            
            -- 22、查詢所有課程的成績第2名到第3名的學生信息及該課程成績
            select a.* from (
            select st.*,c.c_id,c.c_name,sc.s_score from student st
            left join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id =sc.c_id and c.c_id="01"
            order by sc.s_score desc LIMIT 1,2 ) a
            union all
            select b.* from (
            select st.*,c.c_id,c.c_name,sc.s_score from student st
            left join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id =sc.c_id and c.c_id="02"
            order by sc.s_score desc LIMIT 1,2) b
            union all
            select c.* from (
            select st.*,c.c_id,c.c_name,sc.s_score from student st
            left join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id =sc.c_id and c.c_id="03"
            order by sc.s_score desc LIMIT 1,2) c
            
            -- 23、統計各科成績各分數段人數:課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比
            select c.c_id,c.c_name 
            ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=100 and sc.s_score>80)/(select count(1) from score sc where sc.c_id=c.c_id )) "100-85"
            ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=85 and sc.s_score>70)/(select count(1) from score sc where sc.c_id=c.c_id )) "85-70"
            ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=70 and sc.s_score>60)/(select count(1) from score sc where sc.c_id=c.c_id )) "70-60"
            ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=60 and sc.s_score>=0)/(select count(1) from score sc where sc.c_id=c.c_id )) "60-0"
            from course c order by c.c_id
            
            -- 24、查詢學生平均成績及其名次 
            set @i=0;
            select a.*,@i:=@i+1 from (
            select st.s_id,st.s_name,round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end),2) "平均分" from student st
            left join score sc on sc.s_id=st.s_id
            group by st.s_id order by sc.s_score desc) a
            
            -- 25、查詢各科成績前三名的記錄
            select a.* from (
             select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
             left join score sc on sc.s_id=st.s_id
             inner join course c on c.c_id=sc.c_id and c.c_id='01'
             order by sc.s_score desc LIMIT 0,3) a
            union all 
            select b.* from (
             select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
             left join score sc on sc.s_id=st.s_id
             inner join course c on c.c_id=sc.c_id and c.c_id='02'
             order by sc.s_score desc LIMIT 0,3) b
            union all
            select c.* from (
             select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
             left join score sc on sc.s_id=st.s_id
             inner join course c on c.c_id=sc.c_id and c.c_id='03'
             order by sc.s_score desc LIMIT 0,3) c
            
            -- 26、查詢每門課程被選修的學生數 
            select c.c_id,c.c_name,count(1) from course c 
            left join score sc on sc.c_id=c.c_id
            inner join student st on st.s_id=c.c_id
            group by st.s_id
            
            -- 27、查詢出只有兩門課程的全部學生的學號和姓名
            select st.s_id,st.s_name from student st 
            left join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id=sc.c_id 
            group by st.s_id having count(1)=2
            
            -- 28、查詢男生、女生人數
            select st.s_sex,count(1) from student st group by st.s_sex
            
            -- 29、查詢名字中含有"風"字的學生信息
            select st.* from student st where st.s_name like "%風%";
            
            -- 30、查詢同名同性學生名單,并統計同名人數 
            select st.*,count(1) from student st group by st.s_name,st.s_sex having count(1)>1
            
            -- 31、查詢1990年出生的學生名單
            select st.* from student st where st.s_birth like "1990%";
            
            -- 32、查詢每門課程的平均成績,結果按平均成績降序排列,平均成績相同時,按課程編號升序排列 
            select c.c_id,c.c_name,avg(sc.s_score) from course c
            inner join score sc on sc.c_id=c.c_id 
            group by c.c_id order by avg(sc.s_score) desc,c.c_id asc
            
            -- 33、查詢平均成績大于等于85的所有學生的學號、姓名和平均成績
            select st.s_id,st.s_name,avg(sc.s_score) from student st
            left join score sc on sc.s_id=st.s_id
            group by st.s_id having avg(sc.s_score)>=85
            
            -- 34、查詢課程名稱為"數學",且分數低于60的學生姓名和分數 
            select st.s_id,st.s_name,sc.s_score from student st
            inner join score sc on sc.s_id=st.s_id and sc.s_score<60
            inner join course c on c.c_id=sc.c_id and c.c_name ="數學" 
            
            -- 35、查詢所有學生的課程及分數情況;
            select st.s_id,st.s_name,c.c_name,sc.s_score from student st
            left join score sc on sc.s_id=st.s_id
            left join course c on c.c_id =sc.c_id
            order by st.s_id,c.c_name
            
            -- 36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數
            select st2.s_id,st2.s_name,c2.c_name,sc2.s_score from student st2
            left join score sc2 on sc2.s_id=st2.s_id
            left join course c2 on c2.c_id=sc2.c_id 
            where st2.s_id in(
            select st.s_id from student st 
            left join score sc on sc.s_id=st.s_id 
            group by st.s_id having min(sc.s_score)>=70)
            order by s_id
            
            -- 37、查詢不及格的課程
            select st.s_id,c.c_name,st.s_name,sc.s_score from student st
            inner join score sc on sc.s_id=st.s_id and sc.s_score<60
            inner join course c on c.c_id=sc.c_id 
            
            -- 38、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名
            select st.s_id,st.s_name,sc.s_score from student st
            inner join score sc on sc.s_id=st.s_id and sc.c_id="01" and sc.s_score>=80
            
            -- 39、求每門課程的學生人數
            select c.c_id,c.c_name,count(1) from course c
            inner join score sc on sc.c_id=c.c_id
            group by c.c_id
            
            -- 40、查詢選修"張三"老師所授課程的學生中,成績最高的學生信息及其成績 
            select st.*,c.c_name,sc.s_score,t.t_name from student st
            inner join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id=sc.c_id 
            inner join teacher t on t.t_id=c.t_id and t.t_name="張三"
            order by sc.s_score desc
            limit 0,1
            
            -- 41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績 
            select st.s_id,st.s_name,sc.c_id,sc.s_score from student st 
            left join score sc on sc.s_id=st.s_id
            left join course c on c.c_id=sc.c_id
            where (
            select count(1) from student st2 
            left join score sc2 on sc2.s_id=st2.s_id
            left join course c2 on c2.c_id=sc2.c_id
            where sc.s_score=sc2.s_score and c.c_id!=c2.c_id 
            )>1
            
            -- 42、查詢每門功成績最好的前兩名 
            select a.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
            left join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id=sc.c_id and c.c_id="01"
            order by sc.s_score desc limit 0,2) a
            union all
            select b.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
            left join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id=sc.c_id and c.c_id="02"
            order by sc.s_score desc limit 0,2) b
            union all
            select c.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
            left join score sc on sc.s_id=st.s_id
            inner join course c on c.c_id=sc.c_id and c.c_id="03"
            order by sc.s_score desc limit 0,2) c
             
            -- 借鑒(更準確,漂亮):
             select a.s_id,a.c_id,a.s_score from score a
             where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 order by a.c_id
            
            -- 43、統計每門課程的學生選修人數(超過5人的課程才統計)。要求輸出課程號和選修人數,查詢結果按人數降序排列,
            --  若人數相同,按課程號升序排列 
            select sc.c_id,count(1) from score sc
            left join course c on c.c_id=sc.c_id
            group by c.c_id having count(1)>5
            order by count(1) desc,sc.c_id asc
            
            -- 44、檢索至少選修兩門課程的學生學號 
            select st.s_id from student st 
            left join score sc on sc.s_id=st.s_id
            group by st.s_id having count(1)>=2
            
            -- 45、查詢選修了全部課程的學生信息
            select st.* from student st 
            left join score sc on sc.s_id=st.s_id
            group by st.s_id having count(1)=(select count(1) from course)
            
            -- 46、查詢各學生的年齡
             select st.*,timestampdiff(year,st.s_birth,now()) from student st
            
            -- 47、查詢本周過生日的學生
             -- 此處可能有問題,week函數取的為當前年的第幾周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期幾(%w),
             -- 再判斷本周是否會持續到下一個月進行判斷,太麻煩,不會寫
            select st.* from student st 
            where week(now())=week(date_format(st.s_birth,'%Y%m%d'))
            
            -- 48、查詢下周過生日的學生
            select st.* from student st 
            where week(now())+1=week(date_format(st.s_birth,'%Y%m%d'))
            
            -- 49、查詢本月過生日的學生
            select st.* from student st 
            where month(now())=month(date_format(st.s_birth,'%Y%m%d'))
            
            -- 50、查詢下月過生日的學生
             -- 注意:當 當前月為12時,用month(now())+1為13而不是1,可用timestampadd()函數或mod取模
            select st.* from student st 
            where month(timestampadd(month,1,now()))=month(date_format(st.s_birth,'%Y%m%d'))
            -- 或
            select st.* from student st where (month(now()) + 1) mod 12 = month(date_format(st.s_birth,'%Y%m%d'))
            
            

            到此這篇關于Mysql Sql 語句練習題(50道)的文章就介紹到這了,更多相關Mysql練習題內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家!

            您可能感興趣的文章:

            相關文章

            最新評論

            hao500彩票 www.alpacitnz.com:泗阳县| www.rldmw.cn:广灵县| www.m8667.com:托里县| www.dessertsstraightup.com:潢川县| www.johnmarquisford.com:陇南市| www.cp7753.com:灵山县| www.guitartrick.net:孙吴县| www.uclubct.com:元氏县| www.leijindianqi.com:剑阁县| www.apexelpaso.com:信宜市| www.tianhaoyule.com:公主岭市| www.rq6.net:扶余县| www.photo-vs.com:社会| www.amphorahandbags.com:黎川县| www.57pinche.com:博湖县| www.fjfl.org:耿马| www.yipaidaipai.com:闻喜县| www.curtisdemarce.com:漳浦县| www.themobilitypov.com:永州市| www.edongphoto.com:苗栗县| www.xhttw.com:太白县| www.sustainablenepal.com:涟水县| www.azzurroscipioni.com:吉林市| www.zhugangfamen.com:常熟市| www.classicblindscc.com:吉林省| www.cjbrw.cn:龙游县| www.mastersengenharia.com:双桥区| www.sgiphone.com:高要市| www.cox2go.com:天津市| www.oopsireadabookagain.com:两当县| www.cssmuseum.com:南澳县| www.fjmejd.com:肃南| www.speaklan.com:两当县| www.lamaihotelpatong.net:永泰县| www.scrusquash.com:江达县| www.cnsxmr.com:连云港市| www.whatssparkling.com:宝鸡市| www.georgepappasltd.com:屏南县| www.resetv.com:洪泽县| www.abcqg.com:临朐县| www.janainaewilliam.com:石泉县| www.u-lott.com:平远县| www.joikoi.com:德清县| www.sincerely-0501.com:调兵山市| www.thejoyryders.com:富川| www.apics-hawaii.org:来宾市| www.clarebirth.com:鸡西市| www.gaindealsspot.com:伽师县| www.g8285.com:宜城市| www.1140745.com:河西区| www.m6885.com:苗栗市| www.modbus-ida.com:茌平县| www.qunfengdesign.com:视频| www.sunsetinnusa.com:林甸县| www.7vwp.com:潮州市| www.bristoldoors.net:锡林浩特市| www.bulgariatourguide.com:巴楚县| www.woodenfences.org:东光县| www.hautdeals.com:岢岚县| www.diextro.com:沽源县| www.xfkqf.com:邢台市| www.hsmyy.com:深州市| www.yungtsai.com:吐鲁番市| www.total-cover.com:锦屏县| www.earmaps.com:德阳市| www.domrestaurante.com:泽州县| www.z8683.com:元朗区| www.lamaihotelpatong.net:香港| www.kieanna.com:北辰区| www.brwmf.com:邹城市| www.798666t.com:通河县| www.rcsellshomes.com:关岭| www.lenserver.com:铁岭县| www.s9867.com:栾川县| www.taipeisailing.org:巨鹿县| www.gillysnow.com:洞口县| www.012559.com:读书| www.vfrsballooning.org:梅州市| www.wx-culture.com:武定县| www.silviatenenti.com:神池县| www.nbtbf.com:垦利县| www.testsite03.com:浮梁县| www.izhuoji.net:虹口区| www.denimrecords.com:营口市|