SQL語(yǔ)句的執(zhí)行順序：

創(chuàng)建school數(shù)據(jù)庫(kù)

create database school;
use school;

創(chuàng)建四張表

create table student(
    s_id varchar(10),
    s_name varchar(20),
    s_age date,
    s_sex varchar(10)
);

create table course(
    c_id varchar(10),
    c_name varchar(20),
    t_id varchar(10)
);


create table teacher (
t_id varchar(10),
t_name varchar(20)
);

create table score (
    s_id varchar(10),
    c_id varchar(10),
    score varchar(10)
);

往表里插值

insert into student (s_id, s_name, s_age, s_sex)
values  ('01' , '趙雷' , '1990-01-01' , '男'),
        ('02' , '錢(qián)電' , '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' , '女'),
        ('08' , '王菊' , '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)
values  ('01' , '語(yǔ)文' , '02'),
        ('02' , '數(shù)學(xué)' , '01'),
        ('03' , '英語(yǔ)' , '03');

insert into teacher (t_id, t_name)
values  ('01' , '張三'),
        ('02' , '李四'),
        ('03' , '王五');

insert into score (s_id, c_id, score)
values  ('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' , '02' , 87),
        ('06' , '01' , 31),
        ('06' , '03' , 34),
        ('07' , '02' , 89),
        ('07' , '03' , 98);

看下建好的四張表

四張表

創(chuàng)建一張總總表

create table total(
select a.s_id as s_id,a.s_name as s_name,a.s_age as s_age,a.s_sex as s_sex,
b.c_id as c_id,b.score as score,c.t_id as t_id,d.t_name as t_name
from student a
left join
score  b on a.s_id=b.s_id
left join
course c on b.c_id=c.c_id
left join
teacher d on c.t_id=d.t_id
);
select * from total;

學(xué)生課程成績(jī)查詢(xún)總表total

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

select a.s_id as s_id,score1,score2 from
(select s_id, score as score1 from score where c_id='01') a
inner join
(select s_id, score as score2 from score where c_id='02') b
on a.s_id=b.s_id
where score1>score2;

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

select a.s_id as s_id,score1,score2 from
(select s_id, score as score1 from score where c_id='01') a
inner join
(select s_id, score as score2 from score where c_id='02') b
on a.s_id=b.s_id
where score1<score2;

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

select student.s_id as s_id,student.s_name as s_name,b.avg_score as avg_score from student 
right join 
(select s_id,avg(score) as avg_score from score
group by s_id having avg_score>60) b
on student.s_id=b.s_id;

4、查詢(xún)平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

select student.s_id as s_id,student.s_name as s_name,b.avg_score as avg_score from student 
right join 
(select s_id,avg(score) as avg_score from score
group by s_id having avg_score<60) b
on student.s_id=b.s_id;

5、查詢(xún)所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)

select s_id, s_name, count(c_id) as c_num, sum(score) as total_score
from total
group by s_id ;

6、查詢(xún)"李"姓老師的數(shù)量

select count(t_name) from teacher
where t_name like '李%';

7、查詢(xún)學(xué)過(guò)"張三"老師授課的同學(xué)的信息

select distinct s_id,s_name,s_age,s_sex
from total
where t_name='張三';

8、查詢(xún)沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息

select * from student
where s_id not in
(select distinct s_id
from total
where t_name='張三');

9、查詢(xún)學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息

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

10、查詢(xún)學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息

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

11、查詢(xún)沒(méi)有學(xué)全所有課程的同學(xué)的信息

select s_id, s_name, s_age, s_sex from total
group by s_id having count(c_id) <3 ;

12、查詢(xún)至少有一門(mén)課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

思路：先找出‘01’同學(xué)學(xué)過(guò)的c_id，再找出學(xué)過(guò)任一門(mén)的s_id，再根據(jù)s_id在student找學(xué)生信息。

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'));

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

思路：先找學(xué)過(guò)‘01’同學(xué)學(xué)過(guò)的課程的學(xué)生，然后通過(guò)group by找這些人里面學(xué)的課程數(shù)和‘01’相同的人。比如下面，表a是‘01’同學(xué)學(xué)過(guò)的課程，b則是所有學(xué)過(guò)‘01’同學(xué)學(xué)過(guò)的任一門(mén)課程的人。

select * from student
where s_id in
(select s_id from
(select score.s_id,a.c_id from 
(select c_id from score where s_id='01') a 
inner join  score
on a.c_id=score.c_id) b
where s_id<>'01' 
group by s_id having count(c_id)=
(select count(c_id) from score where s_id='01'));

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

select s_id,s_name from student
where s_id not in
(select distinct s_id from total
where t_name='張三');

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

思路：先找不及格超過(guò)兩門(mén)的s_id，為表a，再根據(jù)表a連接學(xué)生信息表student和平均分表b。

select a.s_id,student.s_name,b.avg_score from
(select s_id from score
where score<60
group by s_id having count(*)>=2) a
left join
student on a.s_id=student.s_id
left join
(select s_id,avg(score) as avg_score
from score
group by s_id) b
on a.s_id=b.s_id;

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

select a.s_id,student.s_name,student.s_age,student.s_sex,a.score from
(select s_id,score from score
where c_id='01' and score<60
order by score desc) a
left join student on a.s_id=student.s_id;

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

select s_id as '學(xué)號(hào)',
sum(case c_id when '01' then score else 0 end) as '語(yǔ)文',
sum(case c_id when '02' then score else 0 end) as '數(shù)學(xué)',
sum(case c_id when '03' then score else 0 end) as '英語(yǔ)',
avg(score) as '平均成績(jī)'
from score
group by s_id
order by '平均成績(jī)' desc;

*****18、查詢(xún)各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率

----及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90

select a.c_id as '課程ID',course.c_name as '課程name',
max(a.score) as '最高分',min(a.score) as '最低分',
cast(avg(a.score) as decimal(5,2)) as '平均分',
concat(cast(sum(pass)/count(*)*100 as decimal(5,2)),'%') as '及格率',
concat(cast(sum(medi)/count(*)*100 as decimal(5,2)),'%') as '中等率',
concat(cast(sum(good)/count(*)*100 as decimal(5,2)),'%') as '優(yōu)良率',
concat(cast(sum(excellent)/count(*)*100 as decimal(5,2)),'%') as '優(yōu)秀率' from
(select * ,
case when score>=60 then 1 else 0 end as pass,
case when score>=70 and score<80 then 1 else 0 end as medi,
case when score>=80 and score<90 then 1 else 0 end as good,
case when score>=90 then 1 else 0 end as excellent
from score) a
left join course on a.c_id=course.c_id
group by a.c_id;

19、按各科成績(jī)進(jìn)行排序，并顯示排名

select a.*,@rank:=@rank+1 as rank from
(select c_id,sum(score) as '成績(jī)' from score
group by c_id order by sum(score) desc) a,
(select @rank:=0) b;

20、查詢(xún)學(xué)生的總成績(jī)并進(jìn)行排名

select a.*,@rank:=@rank+1 as rank from 
(select s_id,sum(score) as '總成績(jī)' from score
group by s_id order by sum(score) desc) a,
(select @rank:=0) b;

21、查詢(xún)不同老師所教不同課程平均分從高到低顯示

select t_id,t_name,c_id,avg(score) as avg_score 
from total
group by t_id,c_id
order by avg_score desc;

****22、查詢(xún)所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)

先得到一張每門(mén)課程的成績(jī)排序表

select c_id,s_id,score from score 
group by c_id,s_id order by c_id,score desc;

添加兩個(gè)輔助變量用來(lái)生成分組排名

select *,if(@pa=a.c_id,@rank:=@rank+1,@rank:=1) AS rank,@pa:=a.c_id
from
(select c_id,s_id,score from score 
group by c_id,s_id order by c_id,score desc) a,
(select @rank:=0,@pa:=NULL) b;

選出排名為2-3名與student表連接查詢(xún)

select result.c_id,result.s_id,result.score,
student.s_name,student.s_age,student.s_sex from
(select *,if(@pa=a.c_id,@rank:=@rank+1,@rank:=1) AS rank,@pa:=a.c_id
from
(select c_id,s_id,score from score 
group by c_id,s_id order by c_id,score desc) a,
(select @rank:=0,@pa:=NULL) b) result
left join student on result.s_id=student.s_id
where rank between 2 and 3
group by c_id,score desc;

這樣寫(xiě)其實(shí)也是有問(wèn)題的，就是沒(méi)有考慮分?jǐn)?shù)相同的人

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

select a.c_id as '課程編號(hào)',course.c_name as '課程名稱(chēng)',
sum(level1) as '[100-85]人數(shù)', sum(level1)/count(1) as '[100-85]占比',
sum(level2) as '[85-70]人數(shù)', sum(level2)/count(1) as '[85-70]占比',
sum(level3) as '[70-60]人數(shù)', sum(level3)/count(1) as '[70-60]占比',
sum(level4) as '[0-60]人數(shù)', sum(level4)/count(1) as '[0-60]占比' from
(select *,
(case when score between 85 and 100 then 1 else 0 end) as 'level1',
(case when score between 70 and 84 then 1 else 0 end) as 'level2',
(case when score between 60 and 69 then 1 else 0 end) as 'level3',
(case when score between 0 and 59 then 1 else 0 end) as 'level4'
from score) a
left join course on a.c_id=course.c_id
group by a.c_id;

24、查詢(xún)學(xué)生平均成績(jī)及其名次

select a.*,@rank:=@rank+1 as rank from 
(select s_id,avg(score) as '平均成績(jī)' from score
group by s_id order by avg(score) desc) a,
(select @rank:=0) b;

****25、查詢(xún)各科成績(jī)前三名的記錄

select a.c_id,a.s_id,a.score 
from score a 
where (select count(b.s_id) from score b where a.c_id=b.c_id and a.score<b.score)<3 
group by a.c_id,a.s_id; 

26、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)

select c_id,count(s_id) as '選修人數(shù)' 
from score group by c_id; 

27、查詢(xún)出只有兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名

select student.* from
(select s_id from score
group by s_id having count(c_id)=2) a
left join student on a.s_id=student.s_id;

28、查詢(xún)男生、女生人數(shù)

select s_sex as '性別',count(1) as '人數(shù)'
from student group by s_sex;

29、查詢(xún)名字中含有"風(fēng)"字的學(xué)生信息

select * from student
where s_name like '%風(fēng)%';

30、查詢(xún)同名同姓學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)

select s_name,num as '同名人數(shù)' from 
(select *,count(s_id)-1 as num 
from student group by s_name) a;

31、查詢(xún)1990年出生的學(xué)生名單(注：Student表中Sage列的類(lèi)型是datetime)

select s_name from student where year(s_age)='1990';

32、查詢(xún)每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)

select c_id,avg(score) as '平均成績(jī)'
from score group by c_id
order by 平均成績(jī) desc,c_id;

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

select a.s_id,s_name,avg_score from 
(select s_id,avg(score) as avg_score from score
group by s_id having avg(score)>=85) a
left join student on a.s_id=student.s_id;

image.png

34、查詢(xún)課程名稱(chēng)為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)

select s_name,c_name,score from total
where c_name='數(shù)學(xué)' and score<60;

35、查詢(xún)所有學(xué)生的課程及分?jǐn)?shù)情況

select s_id,
sum(case when c_id='01' then score else 0 end) as '語(yǔ)文',
sum(case when c_id='02' then score else 0 end) as '數(shù)學(xué)',
sum(case when c_id='03' then score else 0 end) as '英語(yǔ)'
from total
group by s_id;

36、查詢(xún)?nèi)魏我婚T(mén)課程成績(jī)?cè)?0分以上的姓名、課程名稱(chēng)和分?jǐn)?shù)

select s_name,c_name,score
from total where score>70;

37、查詢(xún)不及格的課程

select score.c_id,course.c_name,score
from score left join course
on score.c_id=course.c_id
where score<60;

38、查詢(xún)課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名

select student.s_id,s_name from student
right join score on student.s_id=score.s_id
where c_id='01' and score>80;

因?yàn)椤?1’課程最高分為80，所以查詢(xún)結(jié)果為空。

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

select c_id,count(1) as '選課人數(shù)'
from score group by c_id;

40、查詢(xún)選修"張三"老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

select student.*,a.score from
(select s_id,score
from total where t_name='張三'
order by score desc limit 1) a
left join student on a.s_id=student.s_id;

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

select a.s_id,a.c_id,a.score
from score a,score b
where a.c_id=b.c_id and a.s_id<>b.s_id and a.score=b.score;

42、查詢(xún)每門(mén)功成績(jī)最好的前兩名

(select c_id,s_id from score where c_id='01' order by score limit 2)
union
(select c_id,s_id from score where c_id='02' order by score limit 2)
union
(select c_id,s_id from score where c_id='03' order by score limit 2);

43、統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢(xún)結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

select c_id,count(s_id) as 選修人數(shù) from score
group by c_id having 選修人數(shù)>5
order by 選修人數(shù) desc,c_id;

44、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)

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

45、查詢(xún)選修了全部課程的學(xué)生信息

select * from student
where s_id in
(select s_id from score
group by s_id having count(c_id)=(select count(*) from course));

46、查詢(xún)各學(xué)生的年齡

select s_id,s_name,(year(now())-year(s_age)) as '年齡' from student;

***47、查詢(xún)本周過(guò)生日的學(xué)生

思路：找到這周的起始日期(一周的開(kāi)始從周日算起)

select s_name,s_age from student
where date_format(s_age,'2019-%m-%d') 
between adddate(curdate(),-(date_format(now(),'%w')))
and adddate(curdate(),7-date_format(now(),'%w'));

因?yàn)闆](méi)有人這周過(guò)生日，因此查詢(xún)記錄為空

48、查詢(xún)下周過(guò)生日的學(xué)生

select s_name,s_age from student
where date_format(s_age,'2019-%m-%d') 
between adddate(curdate(),7-(date_format(now(),'%w')))
and adddate(curdate(),14-date_format(now(),'%w'));

沒(méi)人下周過(guò)生日，查詢(xún)記錄同樣為空

49、查詢(xún)本月過(guò)生日的學(xué)生

select s_name,s_age from student
where date_format(s_age,'%m')=date_format(now(),'%m');

（小白寫(xiě)這篇的時(shí)候是7月）

50、查詢(xún)下月過(guò)生日的學(xué)生

select s_name,s_age from student
where date_format(s_age,'%m')=date_format(now(),'%m')+1;