<rp id="kut09"><nav id="kut09"></nav></rp>
<rp id="kut09"></rp>
  • <tt id="kut09"></tt>
    <tt id="kut09"><tbody id="kut09"></tbody></tt>
    1. <u id="kut09"></u>
    2. <tt id="kut09"><noscript id="kut09"></noscript></tt>
    3. 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練習題內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家!

      您可能感興趣的文章:

      相關文章

      最新評論

      218彩票 www.greenvocational.com:乌审旗| www.air-max-run.com:上蔡县| www.oudianfamen.com:乌拉特后旗| www.postnuk.com:大名县| www.porcoespirito.com:邹城市| www.northgateterrace.org:马龙县| www.btsszjx.com:当涂县| www.aganinsuranceagency.com:石家庄市| www.specificatii.com:津南区| www.812760.com:长宁区| www.arfengwork.com:连山| www.dj-ruki.com:通渭县| www.steven-z.com:安仁县| www.spreadlovenotoil.com:天气| www.wagescout.com:启东市| www.cnlokuki.com:翁牛特旗| www.jnshengping.com:进贤县| www.cp7576.com:东山县| www.223980.com:连城县| www.bxwol.com:全椒县| www.leafwell.org:成安县| www.cp6783.com:卢氏县| www.idoltheory.com:濮阳市| www.xmsmly.com:万全县| www.onlinesocialnetworkingsite.com:襄垣县| www.ppmss.com:普安县| www.wmckorea.com:闸北区| www.yantailantian.com:库尔勒市| www.bjxdby.com:衡南县| www.kates-garden.com:布尔津县| www.ijazzclub.com:吉首市| www.alarmsunrise.com:巴楚县| www.actcci.com:邯郸县| www.tsukamoto-co.com:鄂托克前旗| www.feeling2007.com:永修县| www.jettersite.com:东平县| www.pj88891.com:海宁市| www.qm-cz.com:柳州市| www.bikeleads.com:崇信县| www.cmacgmlogistics.com:富锦市| www.gxsgx.com:营口市| www.uhugame.com:丰宁| www.suntikputihdahlia.net:麻阳| www.bromoijenvacation.com:会泽县| www.zxphy.com:合肥市| www.thisdayinmusicapps.com:垣曲县| www.rbstt.com:田东县| www.dlbdl.com:龙海市| www.continue1.com:宁强县| www.fsxianxin.com:南部县| www.bslhw.com:沿河| www.silvermx5.com:柘荣县| www.inpoker88.com:临沧市| www.corsidilinguaitaliana.com:栾川县| www.oblocals.com:来凤县| www.rdknw.cn:乐都县| www.shnanyabxg.com:阿尔山市| www.boshichiji.com:钦州市| www.ps3usbjailbreak.com:鲜城| www.breakerror.com:和林格尔县| www.serrurier-houilles.net:望奎县| www.poboyzbarandgrill.com:灵川县| www.youhumitwesingit.com:惠水县| www.posthostelprague.com:宁陕县| www.bjgyxw.com:嘉荫县| www.mzsgs.com:衡水市| www.davidroemerphotography.com:永春县| www.nksl-soccer.org:夏河县| www.ratenest.com:阿拉善左旗| www.poeticasvisuais.com:隆子县| www.cp6220.com:辽宁省| www.askabin.net:安达市| www.40photography.com:道孚县| www.108ccc.com:丰原市| www.vsassociatesbiz.com:新巴尔虎左旗| www.colangelosbakery.com:金平| www.mymcmz.com:精河县| www.auto-exclusive67.com:永济市| www.kmrln.cn:莎车县| www.tssth.org:蒙自县| www.intercritics.com:绥江县| www.thejoyryders.com:同德县| www.hnwwt.cn:新郑市| www.sanwencaipiao.com:平湖市|