中国嫩模一级毛片,欧美日韩中文制服人妻

主頁(yè) > 知識(shí)庫(kù) > Mysql Sql 語(yǔ)句練習(xí)題（50道）

Mysql Sql 語(yǔ)句練習(xí)題（50道）

表名和字段

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

測(cè)試數(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`)
);
--成績(jī)表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入學(xué)生表測(cè)試數(shù)據(jù)
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢(qián)電' , '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' , '女');
--課程表測(cè)試數(shù)據(jù)
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '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);

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

student 學(xué)生表：

s_id	s_name	s_birth	s_sex
01	趙雷	1990-01-01	男
02	錢(qián)電	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	語(yǔ)文	02
02	數(shù)學(xué)	01
03	英語(yǔ)	03

teacher 老師表：

t_id	t_name
01	張三
02	李四
03	王五

-- 準(zhǔn)備條件,去掉 sql_mode 的 ONLY_FULL_GROUP_BY 否則此種情況下會(huì)報(bào)錯(cuò):
-- Expression #1 of select list is not in group by clause and contains nonaggregated column 'userinfo.
-- 原因：
-- MySQL 5.7.5和up實(shí)現(xiàn)了對(duì)功能依賴(lài)的檢測(cè)。如果啟用了only_full_group_by SQL模式(在默認(rèn)情況下是這樣)，
-- 那么MySQL就會(huì)拒絕選擇列表、條件或順序列表引用的查詢(xún)，這些查詢(xún)將引用組中未命名的非聚合列，而不是在功能上依賴(lài)于它們。
-- (在5.7.5之前，MySQL沒(méi)有檢測(cè)到功能依賴(lài)項(xiàng)，only_full_group_by在默認(rèn)情況下是不啟用的。關(guān)于前5.7.5行為的描述，請(qǐng)參閱MySQL 5.6參考手冊(cè)。)
-- 執(zhí)行以下個(gè)命令，可以查看 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、查詢(xún)"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù) 
select st.*,sc.s_score as '語(yǔ)文' ,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、查詢(xún)"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)
select st.*,sc.s_score '語(yǔ)文',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、查詢(xún)平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
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、查詢(xún)平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
  -- (包括有成績(jī)的和無(wú)成績(jī)的)
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、查詢(xún)所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)
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、查詢(xún)"李"姓老師的數(shù)量 
select t.t_name,count(t.t_id) from teacher t
group by t.t_id having t.t_name like "李%"; 

-- 7、查詢(xún)學(xué)過(guò)"張三"老師授課的同學(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、查詢(xún)沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息 
 -- 張三老師教的課
 select c.* from course c left join teacher t on t.t_id=c.t_id where t.t_name="張三"
 -- 有張三老師課成績(jī)的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é)生信息,即沒(méi)學(xué)過(guò)張三老師授課的同學(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、查詢(xún)學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"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、查詢(xún)學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"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、查詢(xún)沒(méi)有學(xué)全所有課程的同學(xué)的信息
 -- 太復(fù)雜,下次換一種思路,看有沒(méi)有簡(jiǎn)單點(diǎn)方法
 -- 此處思路為查學(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"
))
-- 來(lái)自一樓網(wǎng)友的思路，左連接，根據(jù)學(xué)生id分組過(guò)濾掉 數(shù)量小于 課程表中總課程數(shù)量的結(jié)果(show me his code),簡(jiǎn)潔不少。
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、查詢(xún)至少有一門(mén)課與學(xué)號(hào)為"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、查詢(xún)和"01"號(hào)的同學(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、查詢(xún)沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(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、查詢(xún)兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
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、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
 -- 可加round,case when then else end 使顯示更完美
select st.s_id,st.s_name,avg(sc4.s_score) "平均分",sc.s_score "語(yǔ)文",sc2.s_score "數(shù)學(xué)",sc3.s_score "英語(yǔ)" 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.查詢(xún)各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程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ī)進(jìn)行排序，并顯示排名(實(shí)現(xiàn)不完全)
-- mysql沒(méi)有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、查詢(xún)學(xué)生的總成績(jī)并進(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、查詢(xún)不同老師所教不同課程平均分從高到低顯示 
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、查詢(xún)所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)
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ì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱(chēng),[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、查詢(xún)學(xué)生平均成績(jī)及其名次 
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、查詢(xún)各科成績(jī)前三名的記錄
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、查詢(xún)每門(mén)課程被選修的學(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、查詢(xún)出只有兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名
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、查詢(xún)男生、女生人數(shù)
select st.s_sex,count(1) from student st group by st.s_sex

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

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

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

-- 32、查詢(xún)每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列 
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、查詢(xún)平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
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、查詢(xún)課程名稱(chēng)為"數(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、查詢(xún)所有學(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、查詢(xún)?nèi)魏我婚T(mén)課程成績(jī)?cè)?0分以上的姓名、課程名稱(chēng)和分?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、查詢(xún)不及格的課程
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、查詢(xún)課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名
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、求每門(mén)課程的學(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、查詢(xún)選修"張三"老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī) 
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、查詢(xún)不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī) 
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、查詢(xún)每門(mén)功成績(jī)最好的前兩名 
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)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢(xún)結(jié)果按人數(shù)降序排列，
--  若人數(shù)相同，按課程號(hào)升序排列 
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、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào) 
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、查詢(xún)選修了全部課程的學(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、查詢(xún)各學(xué)生的年齡
 select st.*,timestampdiff(year,st.s_birth,now()) from student st

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

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

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

-- 50、查詢(xún)下月過(guò)生日的學(xué)生
 -- 注意:當(dāng) 當(dāng)前月為12時(shí),用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 語(yǔ)句練習(xí)題（50道）的文章就介紹到這了,更多相關(guān)Mysql練習(xí)題內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

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

標(biāo)簽：珠海拉薩移動(dòng) 黔東徐州沈陽(yáng) 鹽城沈陽(yáng)

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

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

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

好湿?好紧?好多水好爽自慰,久久久噜久噜久久综合,成人做爰A片免费看黄冈,机机对机机30分钟无遮挡

Mysql Sql 語(yǔ)句練習(xí)題（50道）