表名和字段

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

測試數(shù)據(jù)

--建表
--學(xué)生表
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`)
);
--插入學(xué)生表測試數(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('08' , '王菊' , '1990-01-20' , '女');
--課程表測試數(shù)據(jù)
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03');

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

--成績表測試數(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);

表數(shù)據(jù)如下

student 學(xué)生表：

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 分?jǐn)?shù)表：

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	數(shù)學(xué)	01
03	英語	03

teacher 老師表：

t_id	t_name
01	張三
02	李四
03	王五

-- 準(zhǔn)備條件,去掉 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實現(xiàn)了對功能依賴的檢測。如果啟用了only_full_group_by SQL模式(在默認(rèn)情況下是這樣)，
-- 那么MySQL就會拒絕選擇列表、條件或順序列表引用的查詢，這些查詢將引用組中未命名的非聚合列，而不是在功能上依賴于它們。
-- (在5.7.5之前，MySQL沒有檢測到功能依賴項，only_full_group_by在默認(rèn)情況下是不啟用的。關(guān)于前5.7.5行為的描述，請參閱MySQL 5.6參考手冊。)
-- 執(zhí)行以下個命令，可以查看 sql_mode 的內(nèi)容。
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';

練習(xí)題和sql

-- 1、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù) 
select st.*,sc.s_score as '語文' ,sc2.s_score '數(shù)學(xué)' 
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"課程成績低的學(xué)生的信息及課程分?jǐn)?shù)
select st.*,sc.s_score '語文',sc2.s_score '數(shù)學(xué)' 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_scoresc2.s_score

-- 3、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
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分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
  -- (包括有成績的和無成績的)
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、查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績
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、查詢"李"姓老師的數(shù)量 
select t.t_name,count(t.t_id) from teacher t
group by t.t_id having t.t_name like "李%"; 

-- 7、查詢學(xué)過"張三"老師授課的同學(xué)的信息 
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、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息 
 -- 張三老師教的課
 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的學(xué)生信息,即沒學(xué)過張三老師授課的同學(xué)信息
 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、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息
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"
)


網(wǎng)友提供的思路(厲害呦~):
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、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息
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、查詢沒有學(xué)全所有課程的同學(xué)的信息
 -- 太復(fù)雜,下次換一種思路,看有沒有簡單點方法
 -- 此處思路為查學(xué)全所有課程的學(xué)生id,再內(nèi)聯(lián)取反面
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"
))
-- 來自一樓網(wǎng)友的思路，左連接，根據(jù)學(xué)生id分組過濾掉 數(shù)量小于 課程表中總課程數(shù)量的結(jié)果(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、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
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"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
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、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
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、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績
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_score60 or sc.s_score is NULL
group by sc.s_id having COUNT(sc.s_id)>=2
)
group by st.s_id

-- 16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
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_score60
order by sc.s_score desc

-- 17、按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
 -- 可加round,case when then else end 使顯示更完美
select st.s_id,st.s_name,avg(sc4.s_score) "平均分",sc.s_score "語文",sc2.s_score "數(shù)學(xué)",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，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
-- 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=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_score80 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_score90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "優(yōu)良率"
,((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)) "優(yōu)秀率"
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、按各科成績進(jìn)行排序，并顯示排名(實現(xiàn)不完全)
-- mysql沒有rank函數(shù)
-- 加@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、查詢學(xué)生的總成績并進(jìn)行排名
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名的學(xué)生信息及該課程成績
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、統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號,課程名稱,[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、查詢學(xué)生平均成績及其名次 
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、查詢每門課程被選修的學(xué)生數(shù) 
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、查詢出只有兩門課程的全部學(xué)生的學(xué)號和姓名
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、查詢男生、女生人數(shù)
select st.s_sex,count(1) from student st group by st.s_sex

-- 29、查詢名字中含有"風(fēng)"字的學(xué)生信息
select st.* from student st where st.s_name like "%風(fēng)%";

-- 30、查詢同名同性學(xué)生名單，并統(tǒng)計同名人數(shù) 
select st.*,count(1) from student st group by st.s_name,st.s_sex having count(1)>1

-- 31、查詢1990年出生的學(xué)生名單
select st.* from student st where st.s_birth like "1990%";

-- 32、查詢每門課程的平均成績，結(jié)果按平均成績降序排列，平均成績相同時，按課程編號升序排列 
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的所有學(xué)生的學(xué)號、姓名和平均成績
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、查詢課程名稱為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù) 
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_score60
inner join course c on c.c_id=sc.c_id and c.c_name ="數(shù)學(xué)" 

-- 35、查詢所有學(xué)生的課程及分?jǐn)?shù)情況；
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、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分?jǐn)?shù)
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_score60
inner join course c on c.c_id=sc.c_id 

-- 38、查詢課程編號為01且課程成績在80分以上的學(xué)生的學(xué)號和姓名
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、求每門課程的學(xué)生人數(shù)
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、查詢選修"張三"老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績 
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、查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績 
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
 
-- 借鑒(更準(zhǔn)確,漂亮):
 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、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，
--  若人數(shù)相同，按課程號升序排列 
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、檢索至少選修兩門課程的學(xué)生學(xué)號 
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、查詢選修了全部課程的學(xué)生信息
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、查詢各學(xué)生的年齡
 select st.*,timestampdiff(year,st.s_birth,now()) from student st

-- 47、查詢本周過生日的學(xué)生
 -- 此處可能有問題,week函數(shù)取的為當(dāng)前年的第幾周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期幾(%w),
 -- 再判斷本周是否會持續(xù)到下一個月進(jìn)行判斷,太麻煩,不會寫
select st.* from student st 
where week(now())=week(date_format(st.s_birth,'%Y%m%d'))

-- 48、查詢下周過生日的學(xué)生
select st.* from student st 
where week(now())+1=week(date_format(st.s_birth,'%Y%m%d'))

-- 49、查詢本月過生日的學(xué)生
select st.* from student st 
where month(now())=month(date_format(st.s_birth,'%Y%m%d'))

-- 50、查詢下月過生日的學(xué)生
 -- 注意:當(dāng) 當(dāng)前月為12時,用month(now())+1為13而不是1,可用timestampadd()函數(shù)或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'))

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

您可能感興趣的文章:

最全50個Mysql數(shù)據(jù)庫查詢練習(xí)題

標(biāo)簽：仙桃衡水崇左湘潭蘭州黃山銅川湖南

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Mysql Sql 語句練習(xí)題（50道）》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。