MySQL經(jīng)典練習(xí)題&面試題（持續(xù)更新……）

最近在學(xué)MySQL，網(wǎng)上有很多版本的MySQL經(jīng)典50題解答，有時(shí)間會(huì)二刷并且加入性能方面的考量，后續(xù)也會(huì)繼續(xù)整理加入收集到的面試題目滑黔。

原始表格及題目來源網(wǎng)絡(luò)，分為四張表

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), 課程名稱, 教師編號(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ù)

------------------------------創(chuàng)建數(shù)據(jù)庫(kù)环揽、表略荡、插入數(shù)據(jù)------------------------------

#創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE test;

#選擇數(shù)據(jù)庫(kù)
USE test;

#建表
#學(xué)生表
CREATE TABLE Student(
   s_id VARCHAR(20) COMMENT '學(xué)生編號(hào)',
   s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '學(xué)生姓名',
   s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
   s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '學(xué)生性別',
   PRIMARY KEY(s_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '學(xué)生表';

#課程表
CREATE TABLE Course(
   c_id  VARCHAR(20) COMMENT '課程編號(hào)',
   c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '課程名稱',
   t_id VARCHAR(20) NOT NULL COMMENT '教師編號(hào)',
   PRIMARY KEY(c_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '課程表';

#教師表
CREATE TABLE Teacher(
   t_id VARCHAR(20) COMMENT '教師編號(hào)',
   t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教師姓名',
   PRIMARY KEY(t_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '教師表';

#成績(jī)表
CREATE TABLE Score(
   s_id VARCHAR(20) COMMENT '學(xué)生編號(hào)',
   c_id  VARCHAR(20) COMMENT '課程編號(hào)',
   s_score INT(3) COMMENT '分?jǐn)?shù)',
   PRIMARY KEY(s_id,c_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '成績(jī)表';

#插入學(xué)生表測(cè)試數(shù)據(jù)
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' , '女');


#課程表測(cè)試數(shù)據(jù)
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03');

#教師表測(cè)試數(shù)據(jù)
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

#成績(jī)表測(cè)試數(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);

-----------------------------------------習(xí)題解答-----------------------------------------

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

select s.*,sc.s_score AS c1,sc2.s_score AS c2 from score sc left join student s on sc.s_id=s.s_id inner join score sc2 on sc.s_id=sc2.s_id and sc.c_id='01' and sc2.c_id='02' where sc.s_score>sc2.s_score;

正確輸出:
02  錢電  1990-12-21  男   70  60
04  李云  1990-08-06  男   50  30

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

A.
select a.*,b.c_id,b.s_score 
  from score as a left join score as b 
  on a.s_id=b.s_id 
  where a.c_id='01' and b.c_id='02';
B.
select a.*,b.c_id,b.s_score
  from score as a inner join score as b 
  on a.s_id=b.s_id and a.c_id='01' and b.c_id='02';

#left join后一定要接where
#因?yàn)閍nd會(huì)合并滿足a.s_id=b.s_id and a.c_id='01' and b.c_id='02'的位置
#但最后會(huì)以left表為基準(zhǔn)汛兜，輸出null值記錄

正確輸出：
+------+------+---------+------+---------+
| s_id | c_id | s_score | c_id | s_score |
+------+------+---------+------+---------+
| 01   | 01   |      80 | 02   |      90 |
| 02   | 01   |      70 | 02   |      60 |
| 03   | 01   |      80 | 02   |      80 |
| 04   | 01   |      50 | 02   |      30 |
| 05   | 01   |      76 | 02   |      87 |
+------+------+---------+------+---------+

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

select a.*,b.c_id,b.s_score 
  from score as a left join score as b 
  on a.s_id=b.s_id and b.c_id='02' 
  where a.c_id='01';

正確輸出：
+------+------+---------+------+---------+
| s_id | c_id | s_score | c_id | s_score |
+------+------+---------+------+---------+
| 01   | 01   |      80 | 02   |      90 |
| 02   | 01   |      70 | 02   |      60 |
| 03   | 01   |      80 | 02   |      80 |
| 04   | 01   |      50 | 02   |      30 |
| 05   | 01   |      76 | 02   |      87 |
| 06   | 01   |      31 | NULL |    NULL |
+------+------+---------+------+---------+

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

select s_id from score where s_id not in 
  (select s_id from score where c_id='01')
  and c_id='02';

正確輸出：
+------+
| s_id |
+------+
| 07   |
+------+

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

select s.*,round(avg(s_score),2) avg from student s inner join score sc on s.s_id=sc.s_id group by s_id having avg>=60;

正確輸出：
+------+--------+------------+-------+-------+
| s_id | s_name | s_birth    | s_sex | avg   |
+------+--------+------------+-------+-------+
| 01   | 趙雷   | 1990-01-01 | 男    | 89.67 |
| 02   | 錢電   | 1990-12-21 | 男    | 70.00 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    | 80.00 |
| 05   | 周梅   | 1991-12-01 | 女    | 81.50 |
| 07   | 鄭竹   | 1989-07-01 | 女    | 93.50 |
+------+--------+------------+-------+-------+

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

Q:
①怎么看哪種方法更好辫塌？
set profiling=1;show profiles;結(jié)果好像有波動(dòng)漏策，數(shù)據(jù)量小的時(shí)候看不出哪種方法更快
查了一些explain結(jié)果的含義，但不知道是不是具體效率和extra里的內(nèi)容也有關(guān)系臼氨。
例如這題掺喻，按理說join更快，但A.B兩種方法的type储矩，key感耙，row都相同，explain只有extra細(xì)微區(qū)別持隧，
實(shí)際show profiles看執(zhí)行速度的時(shí)候似乎有波動(dòng)即硼，不能確定哪個(gè)更快。
②用row_number()去重是不是只能多加一層嵌套屡拨，如C谦絮，是不是沒有g(shù)roup高效？

A.select * from student where s_id in (select s_id from score group by s_id);

B.select s.* from student s inner join score sc on s.s_id=sc.s_id group by s.s_id;

C.select t.s_id,t.s_name,t.s_birth,t.s_sex from (select s.*,row_number() over (partition by s_id) as rank1 from student s inner join score sc on s.s_id=sc.s_id) t where rank1=1 ;

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 趙雷   | 1990-01-01 | 男    |
| 02   | 錢電   | 1990-12-21 | 男    |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吳蘭   | 1992-03-01 | 女    |
| 07   | 鄭竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

A.explain select * from student where s_id in (select s_id from score group by s_id);
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+------------------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra                  |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+------------------------+
|  1 | SIMPLE      | score   | NULL       | index  | PRIMARY       | PRIMARY | 124     | NULL            |   18 |    38.89 | Using index; LooseScan |
|  1 | SIMPLE      | student | NULL       | eq_ref | PRIMARY       | PRIMARY | 62      | test.score.s_id |    1 |   100.00 | NULL                   |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+------------------------+
B.explain select s.* from student s inner join score sc on s.s_id=sc.s_id group by s.s_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+------------------------------+
|  1 | SIMPLE      | sc    | NULL       | index  | PRIMARY       | PRIMARY | 124     | NULL         |   18 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY       | PRIMARY | 62      | test.sc.s_id |    1 |   100.00 | NULL                         |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+------------------------------+
C.explain select t.s_id,t.s_name,t.s_birth,t.s_sex from (select s.*,row_number() over (partition by s_id) as rank1 from student s inner join score sc on s.s_id=sc.s_id) t where rank1=1 ;
+----+-------------+------------+------------+--------+---------------+-------------+---------+--------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys | key         | key_len | ref          | rows | filtered | Extra                                        |
+----+-------------+------------+------------+--------+---------------+-------------+---------+--------------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key0>   | <auto_key0> | 8       | const        |    1 |   100.00 | NULL                                         |
|  2 | DERIVED     | sc         | NULL       | index  | PRIMARY       | PRIMARY     | 124     | NULL         |   18 |   100.00 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | s          | NULL       | eq_ref | PRIMARY       | PRIMARY     | 62      | test.sc.s_id |    1 |   100.00 | NULL                                         |
+----+-------------+------------+------------+--------+---------------+-------------+---------+--------------+------+----------+----------------------------------------------+

4洁仗、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名性锭、選課總數(shù)赠潦、所有課程的總成績(jī)

select s.*,(case when s_score is not null then count(1) else 0 end) as num,sum(case when s_score is not null then s_score else 0 end) as total from student s left join score sc on s.s_id=sc.s_id group by s.s_id;

正確輸出：
+------+--------+------------+-------+-----+-------+
| s_id | s_name | s_birth    | s_sex | num | total |
+------+--------+------------+-------+-----+-------+
| 01   | 趙雷   | 1990-01-01 | 男    |   3 |   269 |
| 02   | 錢電   | 1990-12-21 | 男    |   3 |   210 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |   3 |   240 |
| 04   | 李云   | 1990-08-06 | 男    |   3 |   100 |
| 05   | 周梅   | 1991-12-01 | 女    |   2 |   163 |
| 06   | 吳蘭   | 1992-03-01 | 女    |   2 |    65 |
| 07   | 鄭竹   | 1989-07-01 | 女    |   2 |   187 |
| 09   | 張三   | 2017-12-20 | 女    |   0 |     0 |
| 10   | 李四   | 2017-12-25 | 女    |   0 |     0 |
| 11   | 李四   | 2017-12-30 | 女    |   0 |     0 |
| 12   | 趙六   | 2017-01-01 | 女    |   0 |     0 |
| 13   | 孫七   | 2018-01-01 | 女    |   0 |     0 |
+------+--------+------------+-------+-----+-------+

5、查詢"李"姓老師的數(shù)量

SELECT COUNT(t_name) AS TeacherNamedLi FROM teacher WHERE t_name LIKE "李%"

正確輸出：
1

6草冈、查詢學(xué)過"張三"老師授課的同學(xué)的信息

select s.* from student s inner join score sc on s.s_id=sc.s_id inner join course c on c.c_id=sc.c_id inner join teacher t on t.t_id=c.t_id where t.t_name="張三";

正確輸出：
01  趙雷  1990-01-01  男
02  錢電  1990-12-21  男
03  孫風(fēng)  1990-05-20  男
04  李云  1990-08-06  男
05  周梅  1991-12-01  女
07  鄭竹  1989-07-01  女

7她奥、查詢沒有學(xué)全所有課程的同學(xué)的信息

select s.* from student s left join score sc on s.s_id=sc.s_id group by s.s_id having count(1)<3;

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吳蘭   | 1992-03-01 | 女    |
| 07   | 鄭竹   | 1989-07-01 | 女    |
| 09   | 張三   | 2017-12-20 | 女    |
| 10   | 李四   | 2017-12-25 | 女    |
| 11   | 李四   | 2017-12-30 | 女    |
| 12   | 趙六   | 2017-01-01 | 女    |
| 13   | 孫七   | 2018-01-01 | 女    |
+------+--------+------------+-------+

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

Q:這題和3有些像，還是不清楚哪種情況更快哩俭。
老師之前有在群里說過distinct和子查詢最好不要用
但看explain結(jié)果type绷跑，ref、row都是子查詢方法更佳凡资。

A.用join
select s.* from student s left join score sc on s.s_id=sc.s_id where c_id in (select c_id from score where s_id='01') group by s_id;
explain select s.* from student s left join score sc on s.s_id=sc.s_id where c_id in (select c_id from score where s_id='01') group by s_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra                                                           |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | score | NULL       | ref    | PRIMARY       | PRIMARY | 62      | const        |    3 |   100.00 | Using index; Using temporary                                    |
|  1 | SIMPLE      | sc    | NULL       | index  | PRIMARY       | PRIMARY | 124     | NULL         |   18 |    10.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY       | PRIMARY | 62      | test.sc.s_id |    1 |   100.00 | NULL                                                            |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-----------------------------------------------------------------+

B.子查詢group
select * 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');
explain select * 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');
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------------------------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra                               |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------------------------------+
|  1 | SIMPLE      | score   | NULL       | range  | PRIMARY       | PRIMARY | 62      | NULL                  |   16 |    43.75 | Using where; Using index; LooseScan |
|  1 | SIMPLE      | score   | NULL       | eq_ref | PRIMARY       | PRIMARY | 124     | const,test.score.c_id |    1 |   100.00 | Using index; FirstMatch(score)      |
|  1 | SIMPLE      | student | NULL       | eq_ref | PRIMARY       | PRIMARY | 62      | test.score.s_id       |    1 |   100.00 | NULL                                |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------------------------------+

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 02   | 錢電   | 1990-12-21 | 男    |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吳蘭   | 1992-03-01 | 女    |
| 07   | 鄭竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

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

Q:想問下老師還有沒有更好的方法？

select @group:=group_concat(c_id) 
  from score group by s_id having s_id='01';

select s.* from student s inner join score sc on sc.s_id=s.s_id 
  where s.s_id!='01' group by s.s_id having group_concat(c_id)=@group;

正確輸出
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 02   | 錢電   | 1990-12-21 | 男    |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

10隙赁、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息

select s.* from student s inner join score sc on s.s_id=sc.s_id inner join course c on c.c_id=sc.c_id inner join teacher t on t.t_id=c.t_id where t.t_name!="張三";

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 06   | 吳蘭   | 1992-03-01 | 女    |
| 09   | 張三   | 2017-12-20 | 女    |
| 10   | 李四   | 2017-12-25 | 女    |
| 11   | 李四   | 2017-12-30 | 女    |
| 12   | 趙六   | 2017-01-01 | 女    |
| 13   | 孫七   | 2018-01-01 | 女    |
+------+--------+------------+-------+

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

select s.s_id,s.s_name,round(avg(s_score),2) as avg from student s inner join score sc on s.s_id=sc.s_id where s.s_id in(select s_id from score where s.s_score<60 group by s_id having count(1)>=2) group by s_id;

正確輸出：
+------+-------+--------+
| s_id | s_avg | s_name |
+------+-------+--------+
| 04   | 33.33 | 李云   |
| 06   | 32.50 | 吳蘭   |
+------+-------+--------+

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

select s.*,sc.s_score from student as s
  inner join score as sc
  on s.s_id=sc.s_id and sc.c_id='01' and sc.s_score<60 
  order by s_score desc;

正確輸出：
+------+--------+------------+-------+---------+
| s_id | s_name | s_birth    | s_sex | s_score |
+------+--------+------------+-------+---------+
| 04   | 李云   | 1990-08-06 | 男    |      50 |
| 06   | 吳蘭   | 1992-03-01 | 女    |      31 |
+------+--------+------------+-------+---------+

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

Q:看到老師在群里說數(shù)據(jù)量大的時(shí)候不要出現(xiàn)太多join厚掷，但是又說應(yīng)該減少子查詢
所以我不太清楚究竟什么時(shí)候用join什么時(shí)候子查詢弟灼？

select t3.*,round(s_avg,2) as s_avg from 
  (select t2.*,s3.s_score as 03_score from
    (select t1.*,s2.s_score as 02_score from 
      (select s.*,s1.s_score as 01_score from 
        student as s left join score as s1 on s.s_id=s1.s_id and s1.c_id='01') as t1
      left join score as s2 on t1.s_id=s2.s_id and s2.c_id='02') as t2
    left join score as s3 on t2.s_id=s3.s_id and s3.c_id='03') as t3
  left join (select s_id,avg(s_score)as s_avg from score group by s_id) as t4 
on t4.s_id=t3.s_id order by s_avg desc;

正確輸出：
+------+--------+------------+-------+----------+----------+----------+-------+
| s_id | s_name | s_birth    | s_sex | 01_score | 02_score | 03_score | s_avg |
+------+--------+------------+-------+----------+----------+----------+-------+
| 07   | 鄭竹   | 1989-07-01 | 女    |     NULL |       89 |       98 | 93.50 |
| 01   | 趙雷   | 1990-01-01 | 男    |       80 |       90 |       99 | 89.67 |
| 05   | 周梅   | 1991-12-01 | 女    |       76 |       87 |     NULL | 81.50 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |       80 |       80 |       80 | 80.00 |
| 02   | 錢電   | 1990-12-21 | 男    |       70 |       60 |       80 | 70.00 |
| 04   | 李云   | 1990-08-06 | 男    |       50 |       30 |       20 | 33.33 |
| 06   | 吳蘭   | 1992-03-01 | 女    |       31 |     NULL |       34 | 32.50 |
| 11   | 李四   | 2017-12-30 | 女    |     NULL |     NULL |     NULL |  NULL |
| 12   | 趙六   | 2017-01-01 | 女    |     NULL |     NULL |     NULL |  NULL |
| 13   | 孫七   | 2018-01-01 | 女    |     NULL |     NULL |     NULL |  NULL |
| 09   | 張三   | 2017-12-20 | 女    |     NULL |     NULL |     NULL |  NULL |
| 10   | 李四   | 2017-12-25 | 女    |     NULL |     NULL |     NULL |  NULL |
+------+--------+------------+-------+----------+----------+----------+-------+

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.c_name,t.* from 
(select c_id,
max(s_score) as max,
min(s_score) as min,
round(avg(s_score),2) as avg,
count(1) as num,
round(sum(if(s_score>=60,1,0))*100/count(1),2) as 'pass rate',
round(sum(if(s_score>=70,1,0))*100/count(1),2) as 'medium rate',
round(sum(if(s_score>=80,1,0))*100/count(1),2) as 'good rate',
round(sum(if(s_score>=90,1,0))*100/count(1),2) as 'excellent rate'
from score group by c_id) as t left join course as c on c.c_id=t.c_id 
order by num desc,c_id asc;

正確輸出：
+--------+------+------+------+-------+-----+-----------+-------------+-----------+----------------+
| c_name | c_id | max  | min  | avg   | num | pass rate | medium rate | good rate | excellent rate |
+--------+------+------+------+-------+-----+-----------+-------------+-----------+----------------+
| 語文   | 01   |   80 |   31 | 64.50 |   6 |     66.67 |       66.67 |     33.33 |           0.00 |
| 數(shù)學(xué)   | 02   |   90 |   30 | 72.67 |   6 |     83.33 |       66.67 |     66.67 |          16.67 |
| 英語   | 03   |   99 |   20 | 68.50 |   6 |     66.67 |       66.67 |     66.67 |          33.33 |
+--------+------+------+------+-------+-----+-----------+-------------+-----------+----------------+

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

select *,rank() over (partition by c_id order by s_score) as 'rank' from score;

正確輸出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 06   | 01   |      31 |    1 |
| 04   | 01   |      50 |    2 |
| 02   | 01   |      70 |    3 |
| 05   | 01   |      76 |    4 |
| 01   | 01   |      80 |    5 |
| 03   | 01   |      80 |    5 |
| 04   | 02   |      30 |    1 |
| 02   | 02   |      60 |    2 |
| 03   | 02   |      80 |    3 |
| 05   | 02   |      87 |    4 |
| 07   | 02   |      89 |    5 |
| 01   | 02   |      90 |    6 |
| 04   | 03   |      20 |    1 |
| 06   | 03   |      34 |    2 |
| 02   | 03   |      80 |    3 |
| 03   | 03   |      80 |    3 |
| 07   | 03   |      98 |    5 |
| 01   | 03   |      99 |    6 |
+------+------+---------+------+

15.1 按各科成績(jī)進(jìn)行排序，并顯示排名早处， Score 重復(fù)時(shí)合并名次

select *,dense_rank() over (partition by c_id order by s_score) as 'rank' from score;

正確輸出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 06   | 01   |      31 |    1 |
| 04   | 01   |      50 |    2 |
| 02   | 01   |      70 |    3 |
| 05   | 01   |      76 |    4 |
| 01   | 01   |      80 |    5 |
| 03   | 01   |      80 |    5 |
| 04   | 02   |      30 |    1 |
| 02   | 02   |      60 |    2 |
| 03   | 02   |      80 |    3 |
| 05   | 02   |      87 |    4 |
| 07   | 02   |      89 |    5 |
| 01   | 02   |      90 |    6 |
| 04   | 03   |      20 |    1 |
| 06   | 03   |      34 |    2 |
| 02   | 03   |      80 |    3 |
| 03   | 03   |      80 |    3 |
| 07   | 03   |      98 |    4 |
| 01   | 03   |      99 |    5 |
+------+------+---------+------+

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

select s.*,t.total,t.rank from(select s_id,sum(s_score) as total,rank() over (order by sum(s_score) desc)as 'rank' from score group by s_id)as t right join student as s on s.s_id=t.s_id;

正確輸出：
+------+--------+------------+-------+-------+------+
| s_id | s_name | s_birth    | s_sex | total | rank |
+------+--------+------------+-------+-------+------+
| 01   | 趙雷   | 1990-01-01 | 男    |   269 |    1 |
| 02   | 錢電   | 1990-12-21 | 男    |   210 |    3 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |   240 |    2 |
| 04   | 李云   | 1990-08-06 | 男    |   100 |    6 |
| 05   | 周梅   | 1991-12-01 | 女    |   163 |    5 |
| 06   | 吳蘭   | 1992-03-01 | 女    |    65 |    7 |
| 07   | 鄭竹   | 1989-07-01 | 女    |   187 |    4 |
| 09   | 張三   | 2017-12-20 | 女    |  NULL | NULL |
| 10   | 李四   | 2017-12-25 | 女    |  NULL | NULL |
| 11   | 李四   | 2017-12-30 | 女    |  NULL | NULL |
| 12   | 趙六   | 2017-01-01 | 女    |  NULL | NULL |
| 13   | 孫七   | 2018-01-01 | 女    |  NULL | NULL |
+------+--------+------------+-------+-------+------+

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

select s.*,t.total,t.rank from(select s_id,sum(s_score) as total,dense_rank() over (order by sum(s_score) desc)as 'rank' from score group by s_id)as t right join student as s on s.s_id=t.s_id;

正確輸出：
+------+--------+------------+-------+-------+------+
| s_id | s_name | s_birth    | s_sex | total | rank |
+------+--------+------------+-------+-------+------+
| 01   | 趙雷   | 1990-01-01 | 男    |   269 |    1 |
| 02   | 錢電   | 1990-12-21 | 男    |   210 |    3 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |   240 |    2 |
| 04   | 李云   | 1990-08-06 | 男    |   100 |    6 |
| 05   | 周梅   | 1991-12-01 | 女    |   163 |    5 |
| 06   | 吳蘭   | 1992-03-01 | 女    |    65 |    7 |
| 07   | 鄭竹   | 1989-07-01 | 女    |   187 |    4 |
| 09   | 張三   | 2017-12-20 | 女    |  NULL | NULL |
| 10   | 李四   | 2017-12-25 | 女    |  NULL | NULL |
| 11   | 李四   | 2017-12-30 | 女    |  NULL | NULL |
| 12   | 趙六   | 2017-01-01 | 女    |  NULL | NULL |
| 13   | 孫七   | 2018-01-01 | 女    |  NULL | NULL |
+------+--------+------------+-------+-------+------+

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

select c.c_name,t.* from(select c_id,
sum(if(s_score <60,1,0)) as '[0,60)',
round(sum(if(s_score <60,1,0))/count(1)*100,2) as '[0,60)%',
sum(if(s_score >=60 and s_score<70,1,0)) as '[60,70)',
round(sum(if(s_score >=60 and s_score<70,1,0))/count(1)*100,2) as '[60,70)%',
sum(if(s_score >=70 and s_score<85,1,0)) as '[70,85)',
round(sum(if(s_score >=70 and s_score<85,1,0))/count(1)*100,2) as '[70,85)%',
sum(if(s_score >=85 and s_score<=100,1,0)) as '[85,100]',
round(sum(if(s_score >=85 and s_score<=100,1,0))/count(1)*100,2) as '[85,100]%'
from score group by c_id) as t left join course as c on c.c_id=t.c_id;

正確輸出：
+--------+------+--------+---------+---------+----------+---------+----------+----------+-----------+
| c_name | c_id | [0,60) | [0,60)% | [60,70) | [60,70)% | [70,85) | [70,85)% | [85,100] | [85,100]% |
+--------+------+--------+---------+---------+----------+---------+----------+----------+-----------+
| 語文   | 01   |      2 |   33.33 |       0 |     0.00 |       4 |    66.67 |        0 |      0.00 |
| 數(shù)學(xué)   | 02   |      1 |   16.67 |       1 |    16.67 |       1 |    16.67 |        3 |     50.00 |
| 英語   | 03   |      2 |   33.33 |       0 |     0.00 |       2 |    33.33 |        2 |     33.33 |
+--------+------+--------+---------+---------+----------+---------+----------+----------+-----------+

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

select * from (select *,rank() over (partition by c_id order by s_score) as 'rank' from score)as t where t.rank<=3;

正確輸出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 06   | 01   |      31 |    1 |
| 04   | 01   |      50 |    2 |
| 02   | 01   |      70 |    3 |
| 04   | 02   |      30 |    1 |
| 02   | 02   |      60 |    2 |
| 03   | 02   |      80 |    3 |
| 04   | 03   |      20 |    1 |
| 06   | 03   |      34 |    2 |
| 02   | 03   |      80 |    3 |
| 03   | 03   |      80 |    3 |
+------+------+---------+------+

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

select c_id,count(1) as num from score group by c_id;

正確輸出：
+------+-----+
| c_id | num |
+------+-----+
| 01   |   6 |
| 02   |   6 |
| 03   |   6 |
+------+-----+

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

select s.s_id,s.s_name from student s inner join score sc on s.s_id=sc.s_id group by s_id having count(1)=2媒熊；

正確輸出：
+------+--------+
| s_id | s_name |
+------+--------+
| 05   | 周梅   |
| 06   | 吳蘭   |
| 07   | 鄭竹   |
+------+--------+

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

select s_sex,count(1)as num from student group by s_sex;

正確輸出：
+-------+-----+
| s_sex | num |
+-------+-----+
| 男    |   4 |
| 女    |   8 |
+-------+-----+

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

select * from student where s_name like '%風(fēng)%';
如果s_name是索引字段用%風(fēng)%會(huì)導(dǎo)致索引失效，最好用風(fēng)%
Q:那請(qǐng)問老師這種情況的查詢就要先搜索%風(fēng)再搜索風(fēng)%最后合并還是怎樣泛释？

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |
+------+--------+------------+-------+

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

select s_name,s_sex,count(1)as num from student group by s_name,s_sex having num>1;

正確輸出：
+--------+-------+-----+
| s_name | s_sex | num |
+--------+-------+-----+
| 李四   | 女    |   2 |
+--------+-------+-----+

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

select * from student where year(s_birth)=1990;
如果s_birth是索引的話where字段用函數(shù)會(huì)導(dǎo)致索引失效，因此最好用where s_birth like "1990%"

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 趙雷   | 1990-01-01 | 男    |
| 02   | 錢電   | 1990-12-21 | 男    |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

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

select c.c_id,c.c_name,round(avg(s_score),2) as avg from course c inner join score sc on sc.c_id=c.c_id group by c_id order by avg desc,c_id asc;

正確輸出：
+--------+------+-------+
| c_name | c_id | avg   |
+--------+------+-------+
| 數(shù)學(xué)   | 02   | 72.67 |
| 英語   | 03   | 68.50 |
| 語文   | 01   | 64.50 |
+--------+------+-------+

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

select s.s_id,s.s_name,round(avg(s_score),2) as avg from student s inner join score sc on sc.s_id=s.s_id group by s_id having avg(s_score)>=85;

正確輸出：
+--------+------+-------+
| s_name | s_id | avg   |
+--------+------+-------+
| 趙雷   | 01   | 89.67 |
| 鄭竹   | 07   | 93.50 |
+--------+------+-------+魂贬、

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

select s.s_name,sc.* from student as s inner join score as sc on sc.s_id=s.s_id inner join course c on c.c_id=sc.c_id where s_score<60 and c_name="數(shù)學(xué)";

正確輸出：
+--------+------+------+---------+
| s_name | s_id | c_id | s_score |
+--------+------+------+---------+
| 李云   | 04   | 02   |      30 |
+--------+------+------+---------+

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

select s.*,sc.c_id,sc.s_score from student s left join score sc on s.s_id=sc.s_id;

正確輸出：
+------+--------+------------+-------+------+---------+
| s_id | s_name | s_birth    | s_sex | c_id | s_score |
+------+--------+------------+-------+------+---------+
| 01   | 趙雷   | 1990-01-01 | 男    | 01   |      80 |
| 01   | 趙雷   | 1990-01-01 | 男    | 02   |      90 |
| 01   | 趙雷   | 1990-01-01 | 男    | 03   |      99 |
| 02   | 錢電   | 1990-12-21 | 男    | 01   |      70 |
| 02   | 錢電   | 1990-12-21 | 男    | 02   |      60 |
| 02   | 錢電   | 1990-12-21 | 男    | 03   |      80 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    | 01   |      80 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    | 02   |      80 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    | 03   |      80 |
| 04   | 李云   | 1990-08-06 | 男    | 01   |      50 |
| 04   | 李云   | 1990-08-06 | 男    | 02   |      30 |
| 04   | 李云   | 1990-08-06 | 男    | 03   |      20 |
| 05   | 周梅   | 1991-12-01 | 女    | 01   |      76 |
| 05   | 周梅   | 1991-12-01 | 女    | 02   |      87 |
| 06   | 吳蘭   | 1992-03-01 | 女    | 01   |      31 |
| 06   | 吳蘭   | 1992-03-01 | 女    | 03   |      34 |
| 07   | 鄭竹   | 1989-07-01 | 女    | 02   |      89 |
| 07   | 鄭竹   | 1989-07-01 | 女    | 03   |      98 |
| 09   | 張三   | 2017-12-20 | 女    | NULL |    NULL |
| 10   | 李四   | 2017-12-25 | 女    | NULL |    NULL |
| 11   | 李四   | 2017-12-30 | 女    | NULL |    NULL |
| 12   | 趙六   | 2017-01-01 | 女    | NULL |    NULL |
| 13   | 孫七   | 2018-01-01 | 女    | NULL |    NULL |
+------+--------+------------+-------+------+---------+

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

select s.s_name,sc.* from student s left join score sc on s.s_id=sc.s_id where s_score>70;

正確輸出：
+--------+------+------+---------+
| s_name | s_id | c_id | s_score |
+--------+------+------+---------+
| 趙雷   | 01   | 01   |      80 |
| 趙雷   | 01   | 02   |      90 |
| 趙雷   | 01   | 03   |      99 |
| 錢電   | 02   | 03   |      80 |
| 孫風(fēng)   | 03   | 01   |      80 |
| 孫風(fēng)   | 03   | 02   |      80 |
| 孫風(fēng)   | 03   | 03   |      80 |
| 周梅   | 05   | 01   |      76 |
| 周梅   | 05   | 02   |      87 |
| 鄭竹   | 07   | 02   |      89 |
| 鄭竹   | 07   | 03   |      98 |
+--------+------+------+---------+

30.查詢不及格的課程

select score.* from score where s_score<60;

正確輸出：
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
+------+------+---------+

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

select s.s_name,sc.* from student s inner join score sc on s.s_id=sc.s_id where c_id="01" and s_score>80;

正確輸出：
Empty set

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

select c_id,count(1) as num from score group by c_id;

正確輸出：
+------+-----+
| c_id | num |
+------+-----+
| 01   |   6 |
| 02   |   6 |
| 03   |   6 |
+------+-----+

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

select s.*,sc.c_id,sc.s_score from student s inner join score sc on s.s_id=sc.s_id and c_id=(select c_id from teacher t inner join course c on t.t_id=c.t_id and t.t_name="張三") order by s_score desc limit 1;

正確輸出：
+------+--------+------------+-------+------+---------+
| s_id | s_name | s_birth    | s_sex | c_id | s_score |
+------+--------+------------+-------+------+---------+
| 01   | 趙雷   | 1990-01-01 | 男    | 02   |      90 |
+------+--------+------------+-------+------+---------+

和27題相似的問題键科，因?yàn)?7題加入一個(gè)子查詢比較快，所以我試圖在33題中也加入一個(gè)漩怎，相比直接 inner join確實(shí)快了
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       47 | 0.00080375 | select s.*,sc.c_id,sc.s_score from score sc inner join student s on s.s_id=sc.s_id inner join course c on sc.c_id=c.c_id inner join teacher t on c.t_id=t.t_id and t.t_name='????' order by sc.s_score desc limit 1 |
|       48 | 0.00072225 | select s.*,sc.c_id,sc.s_score from student s inner join score sc on s.s_id=sc.s_id and c_id=(select c_id from teacher t inner join course c on t.t_id=c.t_id and t.t_name="????") order by s_score desc limit 1     |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

子查詢explain:
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | sc    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |   18 |    10.00 | Using where; Using filesort                        |
|  1 | PRIMARY     | s     | NULL       | eq_ref | PRIMARY       | PRIMARY | 62      | test.sc.s_id |    1 |   100.00 | NULL                                               |
|  2 | SUBQUERY    | t     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    3 |    33.33 | Using where                                        |
|  2 | SUBQUERY    | c     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL         |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+

join explain:
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    3 |    33.33 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | c     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | sc    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |   18 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY       | PRIMARY | 62      | test.sc.s_id |    1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+

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

select * from(select s.*,sc.c_id,sc.s_score,rank() over (order by s_score desc) as 'ran' from student s inner join score sc on s.s_id=sc.s_id and c_id=(select c_id from teacher t inner join course c on t.t_id=c.t_id and t.t_name="張三"))as t where ran=1;

正確輸出：
+------+--------+------------+-------+------+---------+-----+
| s_id | s_name | s_birth    | s_sex | c_id | s_score | ran |
+------+--------+------------+-------+------+---------+-----+
| 01   | 趙雷   | 1990-01-01 | 男    | 02   |      90 |   1 |
+------+--------+------------+-------+------+---------+-----+

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

select * from score sc inner join score sc2 on sc.s_id=sc2.s_id where sc.c_id!=sc2.c_id and sc.s_score=sc2.s_score group by sc.s_id ,sc.c_id;

正確輸出：
+------+------+---------+------+------+---------+
| s_id | c_id | s_score | s_id | c_id | s_score |
+------+------+---------+------+------+---------+
| 03   | 01   |      80 | 03   | 02   |      80 |
| 03   | 02   |      80 | 03   | 01   |      80 |
| 03   | 03   |      80 | 03   | 01   |      80 |
+------+------+---------+------+------+---------+

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

select * from (select s_id,s_score,c_id,rank() over (partition by c_id order by s_score desc) as ran from score)as t where ran<=2 ;

正確輸出：
+------+---------+------+-----+
| s_id | s_score | c_id | ran |
+------+---------+------+-----+
| 01   |      80 | 01   |   1 |
| 03   |      80 | 01   |   1 |
| 01   |      90 | 02   |   1 |
| 07   |      89 | 02   |   2 |
| 01   |      99 | 03   |   1 |
| 07   |      98 | 03   |   2 |
+------+---------+------+-----+

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

select c_id,count(1) as num from score group by c_id having num>5;

正確輸出：
+------+-----+
| c_id | num |
+------+-----+
| 01   |   6 |
| 02   |   6 |
| 03   |   6 |
+------+-----+

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

select s_id from score group by s_id having count(1)>=2;

正確輸出：
+------+
| s_id |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 06   |
| 07   |
+------+

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

select s.* from student s inner join (select s_id,count(1)as num from score group by s_id) as t on s.s_id=t.s_id where num=(select count(1) from course);

#如果用having+子查詢效率非常低

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 趙雷   | 1990-01-01 | 男    |
| 02   | 錢電   | 1990-12-21 | 男    |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

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

select *,year(now())-year(s_birth)as age from student;

正確輸出：
+------+--------+------------+-------+------+
| s_id | s_name | s_birth    | s_sex | age  |
+------+--------+------------+-------+------+
| 01   | 趙雷   | 1990-01-01 | 男    |   30 |
| 02   | 錢電   | 1990-12-21 | 男    |   30 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |   30 |
| 04   | 李云   | 1990-08-06 | 男    |   30 |
| 05   | 周梅   | 1991-12-01 | 女    |   29 |
| 06   | 吳蘭   | 1992-03-01 | 女    |   28 |
| 07   | 鄭竹   | 1989-07-01 | 女    |   31 |
| 09   | 張三   | 2017-12-20 | 女    |    3 |
| 10   | 李四   | 2017-12-25 | 女    |    3 |
| 11   | 李四   | 2017-12-30 | 女    |    3 |
| 12   | 趙六   | 2017-01-01 | 女    |    3 |
| 13   | 孫七   | 2018-01-01 | 女    |    2 |
+------+--------+------------+-------+------+

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

select *,timestampdiff(year,s_birth,now()) as age from student;

正確輸出：
+------+--------+------------+-------+------+
| s_id | s_name | s_birth    | s_sex | age  |
+------+--------+------------+-------+------+
| 01   | 趙雷   | 1990-01-01 | 男    |   30 |
| 02   | 錢電   | 1990-12-21 | 男    |   29 |
| 03   | 孫風(fēng)   | 1990-05-20 | 男    |   29 |
| 04   | 李云   | 1990-08-06 | 男    |   29 |
| 05   | 周梅   | 1991-12-01 | 女    |   28 |
| 06   | 吳蘭   | 1992-03-01 | 女    |   27 |
| 07   | 鄭竹   | 1989-07-01 | 女    |   30 |
| 09   | 張三   | 2017-12-20 | 女    |    2 |
| 10   | 李四   | 2017-12-25 | 女    |    2 |
| 11   | 李四   | 2017-12-30 | 女    |    2 |
| 12   | 趙六   | 2017-01-01 | 女    |    3 |
| 13   | 孫七   | 2018-01-01 | 女    |    2 |
+------+--------+------------+-------+------+

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

Q：這種方法好像也會(huì)讓索引失效谈宛，不知道有沒有更好的方法次哈？

select * from student where weekofyear(date_format(s_birth,"2020-%m-%d"))=weekofyear(now());

Empty set (0.00 sec)

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

select * from student where weekofyear(date_format(s_birth,"2020-%m-%d"))=weekofyear(now())+1;

Empty set (0.00 sec)

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

select * from student where s_birth like concat("____-0",month(now()),"%");

正確輸出：
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 趙雷   | 1990-01-01 | 男    |
| 12   | 趙六   | 2017-01-01 | 女    |
| 13   | 孫七   | 2018-01-01 | 女    |
+------+--------+------------+-------+

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

select * from student where s_birth like concat("____-0",month(now())+1,"%");

Empty set (0.00 sec)

最后編輯于：2020.03.10 12:13:14

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市吆录，隨后出現(xiàn)的幾起案子亿乳，更是在濱河造成了極大的恐慌，老刑警劉巖，帶你破解...
沈念sama閱讀 212,222評(píng)論 6贊 493
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件葛假，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡滋恬，警方通過查閱死者的電腦和手機(jī)聊训，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,455評(píng)論 3贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來恢氯，“玉大人带斑，你說我怎么就攤上這事⊙猓” “怎么了勋磕？”我有些...
開封第一講書人閱讀 157,720評(píng)論 0贊 348
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長(zhǎng)敢靡。經(jīng)常有香客問我挂滓，道長(zhǎng)，這世上最難降的妖魔是什么啸胧？我笑而不...
開封第一講書人閱讀 56,568評(píng)論 1贊 284
?港島之戀（遺憾婚禮）
正文為了忘掉前任赶站，我火速辦了婚禮，結(jié)果婚禮上纺念，老公的妹妹穿的比我還像新娘贝椿。我一直安慰自己，他們只是感情好陷谱，可當(dāng)我...
茶點(diǎn)故事閱讀 65,696評(píng)論 6贊 386
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布烙博。她就那樣靜靜地躺著，像睡著了一般烟逊。火紅的嫁衣襯著肌膚如雪渣窜。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,879評(píng)論 1贊 290
城市分裂傳說
那天焙格，我揣著相機(jī)與錄音图毕，去河邊找鬼。笑死眷唉，一個(gè)胖子當(dāng)著我的面吹牛予颤，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播冬阳，決...
沈念sama閱讀 39,028評(píng)論 3贊 409
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼蛤虐，長(zhǎng)吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來了肝陪？” 一聲冷哼從身側(cè)響起驳庭，我...
開封第一講書人閱讀 37,773評(píng)論 0贊 268
萬榮殺人案實(shí)錄
序言：老撾萬榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后饲常，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體蹲堂，經(jīng)...
沈念sama閱讀 44,220評(píng)論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,550評(píng)論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年贝淤，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了柒竞。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 38,697評(píng)論 1贊 341
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡播聪，死狀恐怖朽基，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情离陶，我是刑警寧澤稼虎，帶...
沈念sama閱讀 34,360評(píng)論 4贊 332
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站招刨，受9級(jí)特大地震影響霎俩，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜计济，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 40,002評(píng)論 3贊 315
男人毒藥：我在死后第九天來索命
文/蒙蒙一茸苇、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧沦寂，春花似錦学密、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,782評(píng)論 0贊 21
一樁弒父案腻暮，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至毯侦，卻和暖如春哭靖，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背侈离。一陣腳步聲響...
開封第一講書人閱讀 32,010評(píng)論 1贊 266
情欲美人皮
我被黑心中介騙來泰國(guó)打工试幽，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人卦碾。一個(gè)月前我還...
沈念sama閱讀 46,433評(píng)論 2贊 360
代替公主和親
正文我出身青樓铺坞，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親洲胖。傳聞我的和親對(duì)象是個(gè)殘疾皇子济榨，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,587評(píng)論 2贊 350

MySQL經(jīng)典練習(xí)題&面試題（持續(xù)更新……）

推薦閱讀更多精彩內(nèi)容