查詢結(jié)果就不放了，語句是否成功運(yùn)行，結(jié)果是否正確都容易自行判斷
一 測試數(shù)據(jù)的準(zhǔn)備
–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ù)
-- 建表
-- 學(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ù)查詢
1、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
寫法1：
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a
join score b on a.s_id=b.s_id and b.c_id='01'
left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score
寫法2：
select a.*,b.s_score as 01_score,c.s_score as 02_score from student a,score b,score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score>c.s_score
查詢結(jié)果如下：
2、查詢"01"課程比"02"課程成績低的學(xué)生的信息及課程分?jǐn)?shù)
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a left join score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL
join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score<c.s_score
3、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score >=60;
4、查詢平均成績小于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
– (包括有成績的和無成績的)
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
left join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score <60
union
select a.s_id,a.s_name,0 as avg_score from
student a
where a.s_id not in (
select distinct s_id from score);
5、查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from
student a
left join score b on a.s_id=b.s_id
GROUP BY a.s_id,a.s_name;
查詢結(jié)果如下所示：
6、查詢"李"姓老師的數(shù)量
select count(t_id) from teacher where t_name like '李%';
查詢結(jié)果如下所示：
7、查詢學(xué)過"張三"老師授課的同學(xué)的信息
select a.* from student a
join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = '張三'));
8、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息
select * from student c
where c.s_id not in(
select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
select a.c_id from course a join teacher b on a.t_id = b.t_id where t_name ='張三'));
9、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息
方法1:
select a.* from student a,score b,score c
where a.s_id = b.s_id and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';
方法2：
select a.* from student a
where a.s_id in (select s_id from score where c_id='01' ) and a.s_id in(select s_id from score where c_id='02')；
10、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息
select a.* from student a
where a.s_id in (select s_id from score where c_id='01' ) and a.s_id not in(select s_id from score where c_id='02')；
11、查詢沒有學(xué)全所有課程的同學(xué)的信息
方法1：
select s.* from student s
left join Score s1 on s1.s_id=s.s_id
group by s.s_id having count(s1.c_id)<(select count(*) from course)
方法2：
select *
from student
where s_id not in(
select s_id from score t1
group by s_id having count(*) =(select count(distinct c_id) from course))
12、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(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')
and s_id !='01'
)
13、查詢和"01"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
方法1
SELECT
student.*
FROM
student
WHERE
s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(s_id) = (
#下面的語句是找到'01'同學(xué)學(xué)習(xí)的課程數(shù)
SELECT COUNT(c_id) FROM score WHERE s_id = '01'
)
)
AND s_id NOT IN (
#下面的語句是找到學(xué)過‘01’同學(xué)沒學(xué)過的課程，有哪些同學(xué)。并排除他們
SELECT s_id FROM score
WHERE c_id IN(
#下面的語句是找到‘01’同學(xué)沒學(xué)過的課程
SELECT DISTINCT c_id FROM score
WHERE c_id NOT IN (
#下面的語句是找出‘01’同學(xué)學(xué)習(xí)的課程
SELECT c_id FROM score WHERE s_id = '01'
)
) GROUP BY s_id
) #下面的條件是排除01同學(xué)
AND s_id NOT IN ('01')
方法2：
SELECT
t3.*
FROM
(
SELECT
s_id,
group_concat(c_id ORDER BY c_id) group1
FROM
score
WHERE
s_id <> '01'
GROUP BY
s_id
) t1
INNER JOIN (
SELECT
group_concat(c_id ORDER BY c_id) group2
FROM
score
WHERE
s_id = '01'
GROUP BY
s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id
14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
select a.s_name from student a where a.s_id not in (
select s_id from score where c_id =
(select c_id from course where t_id =(
select t_id from teacher where t_name = '張三')));
15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from
student a
left join score b on a.s_id = b.s_id
where a.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(1)>=2)
GROUP BY a.s_id,a.s_name;
16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
select a.*,b.c_id,b.s_score from
student a,score b
where a.s_id = b.s_id and b.c_id='01' and b.s_score<60 ORDER BY b.s_score DESC;
17、按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
方法1:
select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as 語文,
(select s_score from score where s_id=a.s_id and c_id='02') as 數(shù)學(xué),
(select s_score from score where s_id=a.s_id and c_id='03') as 英語,
round(avg(s_score),2) as 平均分 from score a GROUP BY a.s_id ORDER BY 平均分 DESC;
方法2:
SELECT a.s_id,MAX(CASE a.c_id WHEN '01' THEN a.s_score END ) 語文,
MAX(CASE a.c_id WHEN '02' THEN a.s_score END ) 數(shù)學(xué),
MAX(CASE a.c_id WHEN '03' THEN a.s_score END ) 英語,
avg(a.s_score),b.s_name FROM Score a JOIN Student b ON a.s_id=b.s_id GROUP BY a.s_id ORDER BY 5 DESC
18、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
–及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 優(yōu)良率,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 優(yōu)秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name
19、按各科成績進(jìn)行排序，并顯示排名
– mysql沒有rank函數(shù)文章來源：http://www.zghlxwxcb.cn/news/detail-477695.html
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0) s
寫法2文章來源地址http://www.zghlxwxcb.cn/news/detail-477695.html
(select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='01') rank
FROM score t1 where t1.c_id='01'
order by t1.s_score desc) t1)
union
(select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='02') rank
FROM score t1 where t1.c_id='02'
order by t1.s_score desc) t2)
union
(select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
FROM score t1 where t1.c_id='03'
order by t1.s_score desc) t3)
到了這里，關(guān)于【MySQL】- 05 sql 語句練習(xí)題的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
