建立數據庫
create database if not exists test;
建立學生表
CREATE TABLE if not exists 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 if not exists course(
c_id varchar(20),
c_name varchar(20) not null default '',
t_id varchar(20) not null ,
primary key (c_id)
);
建立教師表
CREATE TABLE IF NOT EXISTS 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)
插入學生表測試數據
insert into student values('01' , '趙雷' , '1990-01-01' , '男');
insert into student values('02' , '錢電' , '1990-12-21' , '男');
insert into student values('03' , '孫風' , '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' , '女');
插入課程表測試數據
insert into course values('01' , '語文' , '02');
insert into course values('02' , '數學' , '01');
insert into course values('03' , '英語' , '03');
插入教師表測試數據
insert into teacher values('01' , '張三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
插入成績表測試數據
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);
1、查詢"01"課程比"02"課程成績高的學生的資訊及課程分數
select a.s_id '學生id',a.s_name '學生姓名',a.s_birth '學生生日',a.s_sex '學生性別',b.s_score '分數'
from student a,(
select s_id,s_score
from score
group by s_id
having max(s_score)) b
where a.s_id=b.s_id;
+----------+--------------+--------------+--------------+--------+
| 學生id | 學生姓名 | 學生生日 | 學生性別 | 分數 |
+----------+--------------+--------------+--------------+--------+
| 01 | 趙雷 | 1990-01-01 | 男 | 80 |
| 02 | 錢電 | 1990-12-21 | 男 | 70 |
| 03 | 孫風 | 1990-05-20 | 男 | 80 |
| 04 | 李雲 | 1990-08-06 | 男 | 50 |
| 05 | 周梅 | 1991-12-01 | 女 | 76 |
| 06 | 吳蘭 | 1992-03-01 | 女 | 31 |
| 07 | 鄭竹 | 1989-07-01 | 女 | 89 |
+----------+--------------+--------------+--------------+--------+
2、查詢"01"課程比"02"課程成績低的學生的資訊及課程分數
select t1.s_id '學生id',t1.s_name '學生姓名',t1.s_birth '學生生日',t1.s_sex '學生性別',t2.課程01分數,t2.課程02分數
from student t1,(
select a.s_id,a.s_score '課程01分數',b.s_score '課程02分數'
from (
select s_id,s_score
from score
where c_id='01') a,(
select s_id,s_score
from score
where c_id='02') b
where a.s_score<b.s_score
and a.s_id=b.s_id)t2
where t1.s_id=t2.s_id;
+----------+--------------+--------------+--------------+----------------+----------------+
| 學生id | 學生姓名 | 學生生日 | 學生性別 | 課程01分數 | 課程02分數 |
+----------+--------------+--------------+--------------+----------------+----------------+
| 01 | 趙雷 | 1990-01-01 | 男 | 80 | 90 |
| 05 | 周梅 | 1991-12-01 | 女 | 76 | 87 |
+----------+--------------+--------------+--------------+----------------+----------------+
3、查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績
select t1.s_name '學生姓名',t1.s_id '學生編號',t2.avgscore '平均成績'
from student t1, (
select s_id,avg(s_score) avgscore
from score
group by s_id
having avg(s_score)>=60) t2
where t1.s_id=t2.s_id;
+--------------+--------------+--------------+
| 學生姓名 | 學生編號 | 平均成績 |
+--------------+--------------+--------------+
| 趙雷 | 01 | 89.6667 |
| 錢電 | 02 | 70.0000 |
| 孫風 | 03 | 80.0000 |
| 周梅 | 05 | 81.5000 |
| 鄭竹 | 07 | 93.5000 |
+--------------+--------------+--------------+
4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績(包括有成績的和無成績的)
select t1.s_id '學生編號',t1.s_name '學生姓名',avg(t2.s_score) '平均成績'
from student t1,score t2
where t1.s_id=t2.s_id
group by t1.s_id, t1.s_name
having avg(t2.s_score)<60
union
select s_id,s_name,0
from student
where s_id not in (
select distinct s_id
from score);
+--------------+--------------+--------------+
| 學生編號 | 學生姓名 | 平均成績 |
+--------------+--------------+--------------+
| 04 | 李雲 | 33.3333 |
| 06 | 吳蘭 | 32.5000 |
| 08 | 王菊 | 0.0000 |
+--------------+--------------+--------------+
5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績
select t1.s_id '學生編號',t1.s_name '學生姓名',count(*) '選課總數',sum(t2.s_score) '課程總成績'
from student t1,score t2
where t1.s_id=t2.s_id
group by t1.s_id, t1.s_name;
+--------------+--------------+--------------+-----------------+
| 學生編號 | 學生姓名 | 選課總數 | 課程總成績 |
+--------------+--------------+--------------+-----------------+
| 01 | 趙雷 | 3 | 269 |
| 02 | 錢電 | 3 | 210 |
| 03 | 孫風 | 3 | 240 |
| 04 | 李雲 | 3 | 100 |
| 05 | 周梅 | 2 | 163 |
| 06 | 吳蘭 | 2 | 65 |
| 07 | 鄭竹 | 2 | 187 |
+--------------+--------------+--------------+-----------------+
6、查詢"李"姓老師的數量
select count(*) '數量'
from teacher
where t_name like '李%';
+--------+
| 數量 |
+--------+
| 1 |
+--------+
7、查詢學過"張三"老師授課的同學的資訊
select student.*
from teacher,course,score,student
where t_name='張三'
and teacher.t_id=course.t_id
and score.c_id=course.c_id
and student.s_id=score.s_id;
+------+--------+------------+-------+
| 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-01 | 女 |
| 07 | 鄭竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
8、查詢沒學過"張三"老師授課的同學的資訊
select *
from student t1
where t1.s_id not in (
select s_id
from score t2
join course t3
where t2.c_id=t3.c_id
and t3.t_id in (
select t_id
from teacher t4
where t4.t_name='張三'));
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
9、查詢學過編號爲"01"並且也學過編號爲"02"的課程的同學的資訊
select *
from student t1
where t1.s_id in (
select s_id
from score t2
where t2.c_id='01'
and t2.s_id in(
select s_id
from score
where c_id='02'));
+------+--------+------------+-------+
| 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-01 | 女 |
+------+--------+------------+-------+
10、查詢學過編號爲"01"但是沒有學過編號爲"02"的課程的同學的資訊
select t1.*
from student t1
where t1.s_id in (
select s_id
from score t2
where t2.c_id='01'
and t2.s_id not in (
select t3.s_id
from score t3
where t3.c_id='02'));
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吳蘭 | 1992-03-01 | 女 |
+------+--------+------------+-------+
11、查詢沒有學全所有課程的同學的資訊
select t1.*
from student t1
left join score t2
on t1.s_id=t2.s_id
group by t2.s_id
having count(*)<(
select count(*)
from course);
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 08 | 王菊 | 1990-01-20 | 女 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 07 | 鄭竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
12、查詢至少有一門課與學號爲"01"的同學所學相同的其他同學的資訊
select distinct t1.*
from student t1,score t2
where t2.c_id in (
select t2.c_id
from score t3
where t3.s_id='01' )
and t1.s_id=t2.s_id
and t1.s_id!='01';
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 07 | 鄭竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
13、查詢和"01"號的同學學習的課程完全相同的其他同學的資訊
select t1.*
from student t1
where t1.s_id in (
select distinct s_id
from score
group by s_id
having count(*)=(
select count(distinct c_id)
from score
where score.s_id='01'
group by s_id))
and t1.s_id not in(
select s_id
from score
where c_id in (
select t2.c_id
from course t2
where t2.c_id not in (
select t3.c_id
from score t3
where t3.s_id='01') ))
and t1.s_id!='01';
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
+------+--------+------------+-------+
14、查詢沒學過"張三"老師講授的任一門課程的學生姓名
select distinct t1.s_name
from student t1 ,score t2
where t1.s_id=t2.s_id
and t2.c_id not in (
select c_id
from course
where t_id = (
select t_id
from teacher
where t_name='張三'));
+--------+
| s_name |
+--------+
| 趙雷 |
| 錢電 |
| 孫風 |
| 李雲 |
| 周梅 |
| 吳蘭 |
| 鄭竹 |
+--------+
15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
select t1.s_id,t1.s_name,avg(t2.s_score)
from student t1,score t2
where t1.s_id in(
select s_id
from score
where s_score<60
group by s_id
having count(*)>=2)
and t1.s_id=t2.s_id
group by t1.s_id;
+------+--------+-----------------+
| s_id | s_name | avg(t2.s_score) |
+------+--------+-----------------+
| 04 | 李雲 | 33.3333 |
| 06 | 吳蘭 | 32.5000 |
+------+--------+-----------------+
16、檢索"01"課程分數小於60，按分數降序排列的學生資訊
select t1.*
from student t1,score t2
where t1.s_id=t2.s_id
and t2.c_id='01'
and t2.s_score<60
order by t2.s_score desc;
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 04 | 李雲 | 1990-08-06 | 男 |
| 06 | 吳蘭 | 1992-03-01 | 女 |
+------+--------+------------+-------+
17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
select t1.s_id,
avg(s_score) '平均成績',
(select t2.s_score from score t2 where t1.s_id=t2.s_id and t2.c_id='01') '語文',
(select t3.s_score from score t3 where t1.s_id=t3.s_id and t3.c_id='02') '數學',
(select t4.s_score from score t4 where t1.s_id=t4.s_id and t4.c_id='02') '英語'
from score t1 group by t1.s_id;
+------+--------------+--------+--------+--------+
| s_id | 平均成績 | 語文 | 數學 | 英語 |
+------+--------------+--------+--------+--------+
| 01 | 89.6667 | 80 | 90 | 90 |
| 02 | 70.0000 | 70 | 60 | 60 |
| 03 | 80.0000 | 80 | 80 | 80 |
| 04 | 33.3333 | 50 | 30 | 30 |
| 05 | 81.5000 | 76 | 87 | 87 |
| 06 | 32.5000 | 31 | NULL | NULL |
| 07 | 93.5000 | NULL | 89 | 89 |
+------+--------------+--------+--------+--------+
18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
–及格爲>=60，中等爲：70-80，優良爲：80-90，優秀爲：>=90
select t1.c_id '課程ID',
t2.c_name '課程name',
max(t1.s_score) '最高分',
min(t1.s_score) '最低分',
avg(t1.s_score) '平均分',
round(100*sum(case when t1.s_score>=60 then 1 else null end)/sum(case when t1.s_score then 1 else null end ),2) '及格率',
round(100*sum(case when t1.s_score >=70 and t1.s_score<80 then 1 else null end )/sum(case when t1.s_score then 1 else null end ),2) '中等率',
round(100*sum(case when t1.s_score>=80 and t1.s_score<90 then 1 else null end )/sum(case when t1.s_score then 1 else null end),2) '優良率',
round(100*sum(case when t1.s_score>=90 then 1 else null end)/sum(case when t1.s_score then 1 else null end),2) '優秀率'
from score t1,course t2 where t1.c_id=t2.c_id group by t1.c_id;
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 課程ID | 課程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 優良率 | 優秀率 |
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01 | 語文 | 80 | 31 | 64.5000 | 66.67 | 33.33 | 33.33 | NULL |
| 02 | 數學 | 90 | 30 | 72.6667 | 83.33 | NULL | 50.00 | 16.67 |
| 03 | 英語 | 99 | 20 | 68.5000 | 66.67 | NULL | 33.33 | 33.33 |
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
19、按各科成績進行排序，並顯示排名
MySQL8的方法
select c_id,s_id,s_score,
rank() over (partition by c_id order by s_score desc ) '排名rank',
row_number() over (partition by c_id order by s_score desc ) '排名row_number',
dense_rank() over (partition by c_id order by s_score desc ) '排名dense_rank'
from score;
MySQL5.6的方法
由於市場上大部分的數據庫爲MySQL5.5、MySQL5.6和MySQL5.7無法使用開窗函數
select t1.*,@a:=@a+1 '排名' from (select * from score where c_id='01' order by s_score desc) t1,(select @a:=0 )t2
union
select t1.*,@b:=@b+1 '排名' from (select * from score where c_id='02' order by s_score desc) t1,(select @b:=0 )t2
union
select t1.*,@c:=@c+1 '排名' from (select * from score where c_id='03' order by s_score desc) t1,(select @c:=0 )t2;
+------+------+---------+--------+
| s_id | c_id | s_score | 排名 |
+------+------+---------+--------+
| 01 | 01 | 80 | 1 |
| 03 | 01 | 80 | 2 |
| 05 | 01 | 76 | 3 |
| 02 | 01 | 70 | 4 |
| 04 | 01 | 50 | 5 |
| 06 | 01 | 31 | 6 |
| 01 | 02 | 90 | 1 |
| 07 | 02 | 89 | 2 |
| 05 | 02 | 87 | 3 |
| 03 | 02 | 80 | 4 |
| 02 | 02 | 60 | 5 |
| 04 | 02 | 30 | 6 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 4 |
| 06 | 03 | 34 | 5 |
| 04 | 03 | 20 | 6 |
+------+------+---------+--------+
20、查詢學生的總成績並進行排名
MySQL8的方法
select
s_id,sum(s_score) '總成績' ,
rank() over (order by sum(s_score) desc) '排名'
from score group by s_id;
MySQL5.6的方法
由於市場上大部分的數據庫爲MySQL5.5、MySQL5.6和MySQL5.7無法使用開窗函數
select t1.*,
@a:=@a+1 '排名'
from (
select s_id,sum(s_score)
from score
group by s_id
order by sum(s_score) desc) t1,(select @a:=0)t2 ;
+------+--------------+--------+
| s_id | sum(s_score) | 排名 |
+------+--------------+--------+
| 01 | 269 | 1 |
| 03 | 240 | 2 |
| 02 | 210 | 3 |
| 07 | 187 | 4 |
| 05 | 163 | 5 |
| 04 | 100 | 6 |
| 06 | 65 | 7 |
+------+--------------+--------+
21、查詢不同老師所教不同課程平均分從高到低顯示
MySQL8的方法
select
avg(t1.s_score),
t3.t_name,
t1.c_id,rank() over (order by avg(t1.s_score) desc ) '排名'
from score t1,course t2,teacher t3 where t1.c_id=t2.c_id and t2.t_id=t3.t_id group by t1.c_id,t3.t_name;
MySQL5.6的方法
由於市場上大部分的數據庫爲MySQL5.5、MySQL5.6和MySQL5.7無法使用開窗函數
select t4.* ,
@a:=@a+1 '排名'
from (
select t1.t_name,avg(t2.s_score),t2.c_id
from teacher t1,score t2,course t3
where t1.t_id=t3.t_id
and t2.c_id=t3.c_id
group by t2.c_id
order by avg(t2.s_score) desc ) t4,(select @a:=0) t5;
+--------+-----------------+------+--------+
| t_name | avg(t2.s_score) | c_id | 排名 |
+--------+-----------------+------+--------+
| 張三 | 72.6667 | 02 | 1 |
| 王五 | 68.5000 | 03 | 2 |
| 李四 | 64.5000 | 01 | 3 |
+--------+-----------------+------+--------+
22、查詢所有課程的成績第2名到第3名的學生資訊及該課程成績
MySQL8的方法
select * from (
select
t1.c_id,
t1.s_score,
t2.*,rank() over (partition by c_id order by s_score desc) a
from score t1,student t2
where t1.s_id=t2.s_id) t3
where t3.a=2 or t3.a=3;
MySQL5.6的方法
由於市場上大部分的數據庫爲MySQL5.5、MySQL5.6和MySQL5.7無法使用開窗函數
select * from (
select t1.*,@a:=@a+1 a from (select* from score where c_id='01' order by s_score desc)t1 ,(select @a:=0)t2
union
select t1.*,@b:=@b+1 a from (select* from score where c_id='02' order by s_score desc)t1 ,(select @b:=0)t2
union
select t1.*,@c:=@c+1 a from (select* from score where c_id='03' order by s_score desc)t1 ,(select @c:=0)t3) t4,student t5 where a between 2 and 3 and t4.s_id=t5.s_id;
+------+------+---------+------+------+--------+------------+-------+
| s_id | c_id | s_score | a | s_id | s_name | s_birth | s_sex |
+------+------+---------+------+------+--------+------------+-------+
| 02 | 03 | 80 | 3 | 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 01 | 80 | 2 | 03 | 孫風 | 1990-05-20 | 男 |
| 05 | 01 | 76 | 3 | 05 | 周梅 | 1991-12-01 | 女 |
| 05 | 02 | 87 | 3 | 05 | 周梅 | 1991-12-01 | 女 |
| 07 | 03 | 98 | 2 | 07 | 鄭竹 | 1989-07-01 | 女 |
| 07 | 02 | 89 | 2 | 07 | 鄭竹 | 1989-07-01 | 女 |
+------+------+---------+------+------+--------+------------+-------+
23、統計各科成績各分數段人數：課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]及所佔百分比
select c_id ,
round(100*sum(case when s_score>=0 and s_score<60 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[0-60]',
round(100*sum(case when s_score>=60 and s_score<70 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[60-70]',
round(100*sum(case when s_score>=70 and s_score<85 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[70-85]',
round(100*sum(case when s_score>=85 and s_score<100 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[85-100]'
from score group by c_id;
+------+--------+---------+---------+----------+
| c_id | [0-60] | [60-70] | [70-85] | [85-100] |
+------+--------+---------+---------+----------+
| 01 | 33.33 | NULL | 66.67 | NULL |
| 02 | 16.67 | 16.67 | 16.67 | 50.00 |
| 03 | 33.33 | NULL | 33.33 | 33.33 |
+------+--------+---------+---------+----------+
24、查詢學生平均成績及其名次
MySQL8的方法
select * ,
rank() over (order by avg desc) '排名'
from (select s_id,avg(s_score) avg from score group by s_id) t1;
MySQL5.6的方法
由於市場上大部分的數據庫爲MySQL5.5、MySQL5.6和MySQL5.7無法使用開窗函數
select t1.*,
@a:=@a+1 '排名'
from (
select s_id,avg(s_score) '平均分'
from score
group by s_id
order by avg(s_score) desc)t1,(select @a:=0) t2 ;
+------+-----------+--------+
| s_id | 平均分 | 排名 |
+------+-----------+--------+
| 07 | 93.5000 | 1 |
| 01 | 89.6667 | 2 |
| 05 | 81.5000 | 3 |
| 03 | 80.0000 | 4 |
| 02 | 70.0000 | 5 |
| 04 | 33.3333 | 6 |
| 06 | 32.5000 | 7 |
+------+-----------+--------+
25、查詢各科成績前三名的記錄
MySQL8的方法
select * from (
select
c_id,
s_id,
s_score,
row_number() over (partition by c_id order by s_score desc) a from score) t1 where t1.a<=3;
MySQL5.6的方法
由於市場上大部分的數據庫爲MySQL5.5、MySQL5.6和MySQL5.7無法使用開窗函數
select * from (
select t1.*,@a:=@a+1 a from (select* from score where c_id='01' order by s_score desc)t1 ,(select @a:=0)t2
union
select t1.*,@b:=@b+1 a from (select* from score where c_id='02' order by s_score desc)t1 ,(select @b:=0)t2
union
select t1.*,@c:=@c+1 a from (select* from score where c_id='03' order by s_score desc)t1 ,(select @c:=0)t3) t4 where a between 1 and 3 ;
+------+------+---------+------+
| s_id | c_id | s_score | a |
+------+------+---------+------+
| 01 | 01 | 80 | 1 |
| 03 | 01 | 80 | 2 |
| 05 | 01 | 76 | 3 |
| 01 | 02 | 90 | 1 |
| 07 | 02 | 89 | 2 |
| 05 | 02 | 87 | 3 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
| 02 | 03 | 80 | 3 |
+------+------+---------+------+
26、查詢每門課程被選修的學生數
select c_id '課程編號',count(distinct s_id) '學生數' from score group by c_id;
+--------------+-----------+
| 課程編號 | 學生數 |
+--------------+-----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+--------------+-----------+
27、查詢出只有兩門課程的全部學生的學號和姓名
select t1.*
from student t1
join (
select s_id
from score
group by s_id
having count(distinct c_id)=2) t2 on t1.s_id=t2.s_id;
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 07 | 鄭竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
28、查詢男生、女生人數
select s_sex,count(1) from student group by s_sex;
+-------+----------+
| s_sex | count(1) |
+-------+----------+
| 女 | 4 |
| 男 | 4 |
+-------+----------+
29、查詢名字中含有"風"字的學生資訊
select * from student where s_name like '%風%';
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 03 | 孫風 | 1990-05-20 | 男 |
+------+--------+------------+-------+
30、查詢同名同性學生名單，並統計同名人數
select s_name,s_sex,count(*) from student group by s_name,s_sex having count(*)>1;
31、查詢1990年出生的學生名單
select * from student where substr(s_birth,1,4)='1990';
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
32、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
select avg(s_score),c_id from score group by c_id order by avg(s_score) desc ,c_id;
+--------------+------+
| avg(s_score) | c_id |
+--------------+------+
| 72.6667 | 02 |
| 68.5000 | 03 |
| 64.5000 | 01 |
+--------------+------+
33、查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
select t1.s_id,t2.s_name,avg(t1.s_score)
from score t1,student t2
where t1.s_id=t2.s_id
group by t1.s_id
having avg(t1.s_score) >=85;
+------+--------+-----------------+
| s_id | s_name | avg(t1.s_score) |
+------+--------+-----------------+
| 01 | 趙雷 | 89.6667 |
| 07 | 鄭竹 | 93.5000 |
+------+--------+-----------------+
34、查詢課程名稱爲"數學"，且分數低於60的學生姓名和分數
select t1.s_name,t3.c_name,t2.s_score
from student t1,score t2,course t3
where t1.s_id=t2.s_id
and t2.c_id=t3.c_id
and t3.c_name='數學'
and t2.s_score<60;
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 李雲 | 數學 | 30 |
+--------+--------+---------+
35、查詢所有學生的課程及分數情況
select t1.s_id,t2.s_name,
sum(case t1.c_id when '01' then t1.s_score else 0 end) '語文',
sum(case t1.c_id when '02' then t1.s_score else 0 end) '數學',
sum(case t1.c_id when '03' then t1.s_score else 0 end) '英語',
sum(t1.s_score) '總分'
from score t1,student t2 where t1.s_id=t2.s_id group by t1.s_id;
+------+--------+--------+--------+--------+--------+
| s_id | s_name | 語文 | 數學 | 英語 | 總分 |
+------+--------+--------+--------+--------+--------+
| 01 | 趙雷 | 80 | 90 | 99 | 269 |
| 02 | 錢電 | 70 | 60 | 80 | 210 |
| 03 | 孫風 | 80 | 80 | 80 | 240 |
| 04 | 李雲 | 50 | 30 | 20 | 100 |
| 05 | 周梅 | 76 | 87 | 0 | 163 |
| 06 | 吳蘭 | 31 | 0 | 34 | 65 |
| 07 | 鄭竹 | 0 | 89 | 98 | 187 |
+------+--------+--------+--------+--------+--------+
36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數
select t1.s_name,t3.c_name,t2.s_score
from student t1,score t2,course t3
where t1.s_id=t2.s_id
and t2.c_id=t3.c_id
and t2.s_score>=70;
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 趙雷 | 語文 | 80 |
| 錢電 | 語文 | 70 |
| 孫風 | 語文 | 80 |
| 周梅 | 語文 | 76 |
| 趙雷 | 數學 | 90 |
| 孫風 | 數學 | 80 |
| 周梅 | 數學 | 87 |
| 鄭竹 | 數學 | 89 |
| 趙雷 | 英語 | 99 |
| 錢電 | 英語 | 80 |
| 孫風 | 英語 | 80 |
| 鄭竹 | 英語 | 98 |
+--------+--------+---------+
37、查詢不及格的課程
select t1.s_score,t2.s_name,t2.s_id,t3.c_name,t3.c_id
from score t1,student t2,course t3
where t1.s_id=t2.s_id
and t1.c_id=t3.c_id
and t1.s_score<60;
+---------+--------+------+--------+------+
| s_score | s_name | s_id | c_name | c_id |
+---------+--------+------+--------+------+
| 50 | 李雲 | 04 | 語文 | 01 |
| 31 | 吳蘭 | 06 | 語文 | 01 |
| 30 | 李雲 | 04 | 數學 | 02 |
| 20 | 李雲 | 04 | 英語 | 03 |
| 34 | 吳蘭 | 06 | 英語 | 03 |
+---------+--------+------+--------+------+
38、查詢課程編號爲01且課程成績在80分以上的學生的學號和姓名
select t1.s_id,t1.s_name,t2.s_score,t2.c_id
from student t1,score t2
where t1.s_id=t2.s_id
and t2.s_score>=80
and t2.c_id='01';
+------+--------+---------+------+
| s_id | s_name | s_score | c_id |
+------+--------+---------+------+
| 01 | 趙雷 | 80 | 01 |
| 03 | 孫風 | 80 | 01 |
+------+--------+---------+------+
39、求每門課程的學生人數
select count(*),c_id from score group by c_id;
+----------+------+
| count(*) | c_id |
+----------+------+
| 6 | 01 |
| 6 | 02 |
| 6 | 03 |
+----------+------+
40、查詢選修"張三"老師所授課程的學生中，成績最高的學生資訊及其成績
select t5.*,t4.max
from student t5 ,(
select max(t1.s_score) max,t2.c_id
from score t1,course t2,teacher t3
where t1.c_id=t2.c_id
and t2.t_id=t3.t_id
and t3.t_name = '張三'
group by t3.t_name,t2.c_id) t4 ,score t6
where t4.max=t6.s_score
and t6.c_id=t4.c_id
and t5.s_id=t6.s_id;
+------+--------+------------+-------+------+
| s_id | s_name | s_birth | s_sex | max |
+------+--------+------------+-------+------+
| 01 | 趙雷 | 1990-01-01 | 男 | 90 |
+------+--------+------------+-------+------+
41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
select s_id,c_id,s_score
from score
where s_score in(
select s_score
from score
group by s_score
having count(*)>1);
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01 | 01 | 80 |
| 02 | 03 | 80 |
| 03 | 01 | 80 |
| 03 | 02 | 80 |
| 03 | 03 | 80 |
+------+------+---------+
42、查詢每門功成績最好的前兩名
MySQL8的方法
select
c_id,
s_score,
s_name,
rank() over (partition by c_id order by s_score desc ) '排名'
from score t1,student t2 where t1.s_id=t2.s_id;
MySQL5.6的方法
由於市場上大部分的數據庫爲MySQL5.5、MySQL5.6和MySQL5.7無法使用開窗函數
select * from (
select t1.*,@a:=@a+1 a from (select* from score where c_id='01' order by s_score desc)t1 ,(select @a:=0)t2
union
select t1.*,@b:=@b+1 a from (select* from score where c_id='02' order by s_score desc)t1 ,(select @b:=0)t2
union
select t1.*,@c:=@c+1 a from (select* from score where c_id='03' order by s_score desc)t1 ,(select @c:=0)t3) t4,student t5 where a between 1 and 2 and t4.s_id=t5.s_id;
+------+------+---------+------+------+--------+------------+-------+
| s_id | c_id | s_score | a | s_id | s_name | s_birth | s_sex |
+------+------+---------+------+------+--------+------------+-------+
| 01 | 02 | 90 | 1 | 01 | 趙雷 | 1990-01-01 | 男 |
| 01 | 03 | 99 | 1 | 01 | 趙雷 | 1990-01-01 | 男 |
| 01 | 01 | 80 | 1 | 01 | 趙雷 | 1990-01-01 | 男 |
| 03 | 01 | 80 | 2 | 03 | 孫風 | 1990-05-20 | 男 |
| 07 | 02 | 89 | 2 | 07 | 鄭竹 | 1989-07-01 | 女 |
| 07 | 03 | 98 | 2 | 07 | 鄭竹 | 1989-07-01 | 女 |
+------+------+---------+------+------+--------+------------+-------+
43、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
select t1.c_id,count(*)
from score t1
group by t1.c_id
having count(*)>5
order by count(*) desc ,c_id asc;
+------+----------+
| c_id | count(*) |
+------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+----------+
44、檢索至少選修兩門課程的學生學號
select s_id from score group by s_id having count(distinct c_id)>=2;
+------+
| s_id |
+------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
| 06 |
| 07 |
+------+
45、查詢選修了全部課程的學生資訊
select *
from student
where s_id in(
select s_id
from score
group by s_id
having count(distinct c_id)=(select count(1) from course));
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
+------+--------+------------+-------+
先給大家看看當前日期
select date_format(now(),'%Y-%m-%d') '當前年月日';
+-----------------+
| 當前年月日 |
+-----------------+
| 2020-08-14 |
+-----------------+
46、查詢各學生的年齡
按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
select (case when dayofyear(now())>dayofyear(s_birth) then year(now())-year(s_birth) else year(now())-year(s_birth)-1 end ) '年齡' ,student.* from student;
+--------+------+--------+------------+-------+
| 年齡 | s_id | s_name | s_birth | s_sex |
+--------+------+--------+------------+-------+
| 30 | 01 | 趙雷 | 1990-01-01 | 男 |
| 29 | 02 | 錢電 | 1990-12-21 | 男 |
| 30 | 03 | 孫風 | 1990-05-20 | 男 |
| 30 | 04 | 李雲 | 1990-08-06 | 男 |
| 28 | 05 | 周梅 | 1991-12-01 | 女 |
| 28 | 06 | 吳蘭 | 1992-03-01 | 女 |
| 31 | 07 | 鄭竹 | 1989-07-01 | 女 |
| 30 | 08 | 王菊 | 1990-01-20 | 女 |
+--------+------+--------+------------+-------+
47、查詢本週過生日的學生
select * from student where week(s_birth)=week(now());
48、查詢下週過生日的學生
select * from student where weekday(s_birth)=week(now())+1;
49、查詢本月過生日的學生
select * from student where month(s_birth)=month(now());
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 04 | 李雲 | 1990-08-06 | 男 |
+------+--------+------------+-------+
50、查詢下月過生日的學生
select * from student where month(s_birth)=month(now())+1;