mysql 练习
跟着视频学习了部分mysql用法。又看了《mysql必知必会》，应该找数据练习一下了。
只有在应用中才能将学过的熟练掌握，变成自己的。
1、练习题介绍
–1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
–3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
–4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
2、创建表格
--创建数据库
create database jinddianti charset = uif8;
-- 学习表
CREATE TABLE Student(
s_id VARCHAR(20) COMMENT '学生编号',
s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别',
PRIMARY KEY(s_id)
);
**# comment：备注，注释**
-- 课程表
CREATE TABLE Course(
c_id VARCHAR(20) COMMENT '课程编号',
c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
t_id VARCHAR(20) NOT NULL COMMENT '教师编号',
PRIMARY KEY(c_id)
);
-- 教师表
CREATE TABLE Teacher(
t_id VARCHAR(20) COMMENT '教师编号',
t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名',
PRIMARY KEY(t_id)
);
-- 成绩表
CREATE TABLE Score(
s_id VARCHAR(20) COMMENT '学生编号',
c_id VARCHAR(20) COMMENT '课程编号',
s_score INT(3) COMMENT '分数',
PRIMARY KEY(s_id,c_id)
);
3、插入数据
-- 插入学生表数据
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)；
4、经典练习题
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
#法1：建立内连接，从大表中取数据。
select a.*,b.s_score,c.s_score from student as a
inner join score as b on a.s_id=b.s_id
inner join score as c on b.s_id=c.s_id
where b.c_id='01'and c.c_id='02' and b.s_score>c.s_score;
#法2：先找到满足条件的学生学号，再建立连接获取学生信息
#select a1.s_id,a1.s_score,a2.s_score from
(select s_id,s_score from score where c_id =01) as a1 ,
(select s_id,s_score from score where c_id =02) as a2
where a1.s_id=a2.s_id and a1.s_score>a2.s_score;
select a.*,b.s_score from student as a
inner join (select a1.s_id,a1.s_score from
(select s_id,s_score from score where c_id =01) as a1 ,
(select s_id,s_score from score where c_id =02) as a2
where a1.s_id=a2.s_id and a1.s_score>a2.s_score) as b
on a.s_id=b.s_id;
#在inner join 后面的查询中如果写两个s_score会报错，所以要不写别名，要不写一个。
select a.*,b.s1,b.s2 from student as a
inner join (select a1.s_id,a1.s1,a2.s2 from
(select s_id,s_score as s1 from score where c_id =01) as a1 ,
(select s_id,s_score as s2 from score where c_id =02) as a2
where a1.s_id=a2.s_id and a1.s1>a2.s2) as b
on a.s_id=b.s_id;
+------+--------+------------+-------+---------+
| s_id | s_name | s_birth | s_sex | s_score |
+------+--------+------------+-------+---------+
| 02 | 钱电 | 1990-12-21 | 男 | 70 |
| 04 | 李云 | 1990-08-06 | 男 | 50 |
+------+--------+------------+-------+---------+
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*,b.s_score,c.s_score from student as a
inner join score as b on a.s_id =b.s_id
inner join score as c on b.s_id =c.s_id
where b.c_id=01 and c.c_id=02 and b.s_score<c.s_score;
+------+--------+------------+-------+---------+
| s_id | s_name | s_birth | s_sex | s_score |
+------+--------+------------+-------+---------+
| 01 | 赵雷 | 1990-01-01 | 男 | 80 |
| 05 | 周梅 | 1991-12-01 | 女 | 76 |
+------+--------+------------+-------+---------+
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
#法1：先连接再分组
select a.s_id,a.s_name,avg(b.s_score) as avg_score from student as a
join score as b on a.s_id=b.s_id group by b.s_id having avg_score >=60;
#法2：先找出平均成绩大于60的学生编号，再连接表找姓名
select s.s_id ,avg(s_score) from score as s group by s_id having avg(s_score)>=60;
select a.s_id,a.s_name,c.avg_score from student as a
inner join (select b.s_id ,avg(b.s_score) as avg_score from score as b
group by b.s_id having avg(b.s_score)>=60) as c on a.s_id=c.s_id;
+------+--------+------------+-------+----------------+
| s_id | s_name | s_birth | s_sex | avg(s.s_score) |
+------+--------+------------+-------+----------------+
| 01 | 赵雷 | 1990-01-01 | 男 | 89.6667 |
| 02 | 钱电 | 1990-12-21 | 男 | 70.0000 |
| 03 | 孙风 | 1990-05-20 | 男 | 80.0000 |
| 05 | 周梅 | 1991-12-01 | 女 | 81.5000 |
| 07 | 郑竹 | 1989-07-01 | 女 | 93.5000 |
+------+--------+------------+-------+----------------+
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
#法1：使用or 函数 distinct(去掉重复的行)
select a.s_id, a.s_name, AVG(b.s_score) as avg_score from student as a
LEFT JOIN score as b ON a.s_id = b.s_id GROUP BY a.s_id
HAVING avg_score < 60
OR a.s_id NOT IN (SELECT DISTINCT a.s_id from student as a inner join score as b on a.s_id=b.s_id);
#法2：使用union (将两个结果合并后返回)
select a.s_id,a.s_name, ROUND(AVG(b.s_score),2) AS avg_score from student as a left JOIN score AS b ON a.s_id = b.s_id GROUP BY a.s_id HAVING AVG(b.s_score) < 60 UNION SELECT a.s_id,a.s_name , 0 AS avg_score FROM student as a where a.s_id not in (SELECT DISTINCT s_id from score);
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04 | 李云 | 33.3333 |
| 06 | 吴兰 | 32.5000 |
| 08 | 王菊 | NULL |
+------+--------+-----------+
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04 | 李云 | 33.33 |
| 06 | 吴兰 | 32.50 |
| 08 | 王菊 | 0.00 |
+------+--------+-----------+
**#使用Union，则所有返回的行都是唯一的，如同您已经对整个结果集合使用了DISTINCT
#使用Union all，则不会排重，返回所有的行
# round(取几位余数)**
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id),sum(b.s_score) from student as a left join score
as b on a.s_id=b.s_id group by a.s_id;
+------+--------+---------------+----------------+
| s_id | s_name | count(s.c_id) | sum(s.s_score) |
+------+--------+---------------+----------------+
| 01 | 赵雷 | 3 | 269 |
| 02 | 钱电 | 3 | 210 |
| 03 | 孙风 | 3 | 240 |
| 04 | 李云 | 3 | 100 |
| 05 | 周梅 | 2 | 163 |
| 06 | 吴兰 | 2 | 65 |
| 07 | 郑竹 | 2 | 187 |
| 08 | 王菊 | 0 | NULL |
+------+--------+---------------+----------------+
-- 6、查询"李"姓老师的数量
select count(*) from teacher where t_name like '李%';
+---------------+
| count(t_name) |
+---------------+
| 1 |
+---------------+
-- 7、查询学过"张三"老师授课的同学的信息
法1：使用子查询
select t.t_id from teacher as t where t.t_ame=张三；
select c.c_id from course as c where c.t_id=(select t.t_id from teacher as t where t.t_name='张三')；
select s.s_id from score as s where s.c_id=(select c.c_id from course as c where c.t_id=(select t.t_id from teacher as t where t.t_name='张三'))；
select * from student as a where a.s_id in(select s.s_id from score as s where s.c_id=(select c.c_id from course as c where c.t_id=(select t.t_id from teacher as t where t.t_name='张三')));
法2：使用自联结
select a.* from student as a inner join score as s on a.s_id=s.s_id inner join course as c on s.c_id=c.c_id inner join teacher as t on c.t_id=t.t_id where t.t_name='张三';
+------+--------+------------+-------+
| 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 as a where a.s_id not in(select s.s_id from score as s where s.c_id in (select c.c_id from course as c where c.t_id in (select t.t_id from teacher as t where t.t_name='张三')));
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吴兰 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
#法1：将表格两次链接，筛选出cid为01的和cid为02的学号，根据学号找学生信息。
select a.* from student as a where a.s_id in ( select s1.s_id from score as s1 join score as s2 on s1.s_id=s2.s_id where s1.c_id=1 and s2.c_id=2);
法2：找出cid为01的，再去找cid为02的
select a.* from student as a join score as b on a.s_id=b.s_id and b.c_id= '01' join score as c on c.s_id=b.s_id and c.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 * 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');
#将学过课程1的学生学号筛选出来，将学过02 的筛选出来，最后找01中有而02中没有的。
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吴兰 | 1992-03-01 | 女 |
+------+--------+------------+-------+
易错点：s_id in （） and s_id not in （）。。。
11、查询没有学全所有课程的同学的信息**
法1：(建立子查询)
select stu.s_id from student as stu where stu.s_id in (select s.s_id from score as s group by s.s_id having count(s.c_id)!=3);
select stu.s_id from student as stu where stu.s_id not in (select s.s_id from score as s group by s.s_id having count(s.c_id)=3);#[注：此方法将没选课的学生信息查询出]
法2：（建立自联结，查询s_id 不在（c_id为1，2，3））
select * from student as stu where stu.s_id in (select s.s_id from score as s where s.s_id not in (select s1.s_id from score as s1 inner join score as s2 on s1.s_id =s2.s_id and s2.c_id='02' inner join score as s3 on s1.s_id=s3.s_id and s3.c_id ='03' where s1.c_id='01'));
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select stu.* from student as stu where stu.s_id in (select s.s_id from score as s where s.c_id in (select s.c_id from score as s where s.s_id =01));
#in（啊，啊，）里面是重复的，外面只取一个
+------+--------+------------+-------+
| 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 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息**
#科目相同and科目都在01学过的科目中
select * from student where s_id in (select distinct s_id from score where s_id!=01 and c_id in (select c_id from score where s_id =01) group by s_id having count(1)=(select count(1) from score where s_id =01));
#（科目数相同 and 排除学过01没学过的）
select stu.* from student as stu where stu.s_id in(select s_id from score group by s_id having count(c_id) =(select count(c_id) from score where s_id=01))
and s_id not in(select s_id from score where c_id in (select c_id from score where c_id not in (select c_id from score where s_id =01)) and s_id not in (01);
+------+--------+------------+-------+
| s_ id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+------+--------+------------+-------+
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
法1：
select stu.* from student as stu where stu.s_id not in (select s_id from score where c_id in (select c_id from course where t_id in (select t_id from teacher where t_name='张三')));
法2：
select student.* from student where student.s_id not in
(select distinct score.s_id from score,course,teacher where score.c_id = course.c_id and course.t_id =teacher.t_id and teacher.t_name ='张三')order by student.s_id;
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吴兰 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu.s_id,stu.s_name,s1.平均分数 from student as stu inner join
(select s_id,avg(s_score) as 平均分数 ,count(s_score) from score where s_id
in (select s_id from score where s_score<60 group by s_id having count(s_score)>=2) group by s_id) as s1
on stu.s_id=s1.s_id ;
#:找出不及格两门以上的学生信息，再取平均数所有的平均成绩
#:先筛选60分之下的成绩然后分组，用where限定条件
+------+--------+--------------+
| s_id | s_name | 平均分数 |
+------+--------+--------------+
| 04 | 李云 | 33.3333 |
| 06 | 吴兰 | 32.5000 |
+------+--------+--------------+
select stu.s_id,stu.s_name,s1.av from student as stu inner join (select s_id,avg(s_score)as av,count(s_score)as co from score where
s_score<60 group by s_id having co>=2) as s1
on stu.s_id=s1.s_id;
**#:[取得平均数只是不及格分数的平均数，但是题目是求不合格超过两门的学生的平均成绩]**（两次犯错）
16、检索"01"课程分数小于60，按分数降序排列的学生信息及01分数
select stu.*,s.s_score as 01分数 from student as stu inner join score as s on stu.s_id=s.s_id and s.c_id=01 where s.s_score<60 order by s.s_score desc;
+------+--------+------------+-------+----------+
| s_id | s_name | s_birth | s_sex | 01分数 |
+------+--------+------------+-------+----------+
| 04 | 李云 | 1990-08-06 | 男 | 50 |
| 06 | 吴兰 | 1992-03-01 | 女 | 31 |
+------+--------+------------+-------+----------+
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu.*,s.c_id,s.s_score,s1.平均成绩 from student as stu right join score as s on stu.s_id=s.s_id right join (select s_id,avg(s_score) as 平均成绩 from score group by s_id order by 平均成绩 desc)as s1 on s.s_id =s1.s_id ;
+------+--------+------------+-------+------+---------+--------------+
| s_id | s_name | s_birth | s_sex | c_id | s_score | 平均成绩 |
+------+--------+------------+-------+------+---------+--------------+
| 07 | 郑竹 | 1989-07-01 | 女 | 02 | 89 | 93.5000 |
| 07 | 郑竹 | 1989-07-01 | 女 | 03 | 98 | 93.5000 |
| 01 | 赵雷 | 1990-01-01 | 男 | 01 | 80 | 89.6667 |
| 01 | 赵雷 | 1990-01-01 | 男 | 02 | 90 | 89.6667 |
| 01 | 赵雷 | 1990-01-01 | 男 | 03 | 99 | 89.6667 |
| 05 | 周梅 | 1991-12-01 | 女 | 01 | 76 | 81.5000 |
| 05 | 周梅 | 1991-12-01 | 女 | 02 | 87 | 81.5000 |
| 03 | 孙风 | 1990-05-20 | 男 | 01 | 80 | 80.0000 |
| 03 | 孙风 | 1990-05-20 | 男 | 02 | 80 | 80.0000 |
| 03 | 孙风 | 1990-05-20 | 男 | 03 | 80 | 80.0000 |
| 02 | 钱电 | 1990-12-21 | 男 | 01 | 70 | 70.0000 |
| 02 | 钱电 | 1990-12-21 | 男 | 02 | 60 | 70.0000 |
| 02 | 钱电 | 1990-12-21 | 男 | 03 | 80 | 70.0000 |
| 04 | 李云 | 1990-08-06 | 男 | 01 | 50 | 33.3333 |
| 04 | 李云 | 1990-08-06 | 男 | 02 | 30 | 33.3333 |
| 04 | 李云 | 1990-08-06 | 男 | 03 | 20 | 33.3333 |
| 06 | 吴兰 | 1992-03-01 | 女 | 01 | 31 | 32.5000 |
| 06 | 吴兰 | 1992-03-01 | 女 | 03 | 34 | 32.5000 |
+------+--------+------------+-------+------+---------+--------------+
其他答案：
select stu.s_id ,stu.s_name,s1.s_score as 语文, s2.s_score as 数学, s3.s_score as 英语, avg(s.s_score) as 平均成绩 from student as stu left join score as s1 on stu.s_id=s1.s_id and s1.c_id=01 left join score as s2 on stu.s_id=s2.s_id and s2.c_id=02 left join score as s3 on stu.s_id=s3.s_id and s3.c_id=03 left join score as s on stu.s_id=s.s_id group by stu.s_id order by avg(s.s_score) desc;
**#自联结三次区分科目**
+------+--------+--------+--------+--------+--------------+
| s_id | s_name | 语文 | 数学 | 英语 | 平均成绩 |
+------+--------+--------+--------+--------+--------------+
| 07 | 郑竹 | NULL | 89 | 98 | 93.5000 |
| 01 | 赵雷 | 80 | 90 | 99 | 89.6667 |
| 05 | 周梅 | 76 | 87 | NULL | 81.5000 |
| 03 | 孙风 | 80 | 80 | 80 | 80.0000 |
| 02 | 钱电 | 70 | 60 | 80 | 70.0000 |
| 04 | 李云 | 50 | 30 | 20 | 33.3333 |
| 06 | 吴兰 | 31 | NULL | 34 | 32.5000 |
| 08 | 王菊 | NULL | NULL | NULL | NULL |
+------+--------+--------+--------+--------+--------------+
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90，要求输出 课程号* 和 选修人数 ，查询结果按 人数 降序排列，若人数相同，按 课程号 升序排列
分步骤：
select c_id,max(s_score),avg(s_score) from score group by c_id;
select c_id,count(1)/8 as 及格率 from score where s_score between 60 and 69 group by c_id;
select c_id,count(1)/8 as 中等率 from score where s_score between 70 and 79 group by c_id;
select c_id,count(1)/8 as 优良率 from score where s_score between 80 and 89 group by c_id;
select c_id,count(1)/8 as 优秀率 from score where s_score between 90 and 100 group by c_id;
select c.c_id,c.c_name, s1.最大值,s1.平均成绩,s2.及格率,s3.中等率,s4.优良率,s5.优秀率 from course as c
left join (select c_id,max(s_score) as 最大值,avg(s_score) as 平均成绩 from score group by c_id) as s1 on c.c_id=s1. c_id
left join (select c_id,count(1)/8 as 及格率 from score where s_score between 60 and 69 group by c_id) as s2 on c. c_id=s2. c_id
left join (select c_id,count(1)/8 as 中等率 from score where s_score between 70 and 79 group by c_id) as s3 on c. c_id=s3. c_id
left join (select c_id,count(1)/8 as 优良率 from score where s_score between 80 and 89 group by c_id ) as s4 on c. c_id=s4. c_id
left join (select c_id,count(1)/8 as 优秀率 from score where s_score between 90 and 100 group by c_id) as s5 on c. c_id=s5. c_id;
#：[变通性差]
select s.c_id as 课程id,c.c_name as 课程name,max(s.s_score) as 最高分 ,min(s.s_score) as 最低分,avg(s.s_score) as 平均分,round(sum(case when s.s_score>=60 then 1 else 0 end)/count(1),2) as 及格率,round(sum(case when 80>s.s_score and s.s_score>=70 then 1 else 0 end)/count(1),2) as 中等率,round(sum(case when 90>s.s_score and s.s_score>=80 then 1 else 0 end)/count(1),2) as 优良率,round(sum(case when s.s_score>=90 then 1 else 0 end)/count(1),2) as 优秀率 from score as s inner join course as c on s.c_id=c.c_id group by c.c_id;
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 课程id | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01 | 语文 | 80 | 31 | 64.5000 | 0.67 | 0.33 | 0.33 | 0.00 |
| 02 | 数学 | 90 | 30 | 72.6667 | 0.83 | 0.00 | 0.50 | 0.17 |
| 03 | 英语 | 99 | 20 | 68.5000 | 0.67 | 0.00 | 0.33 | 0.33 |
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
# 新知识：case when ** then 1 else 0 end（我的理解：当**时输出1，否则，要分组，要sum）
#简单的函数形式：case 字段 when 值 then 结果 else 其他情况 end
#表达式的形式：case when 字段=值（这里写表达式，例如 score=80） then 结果 else 其他情况 end
19、按各科成绩进行排序，并显示排名**
（找出比各科成绩大的成绩，找出各科成绩的名次）
select a.*,count(b.s_score)+1 as 名次 from score as a left join score as b on a.c_id=b.c_id and a.s_score<b.s_score group by a.s_id,a.c_id order by a.c_id,count(a.s_score);
select * ,(select count( b.s_score)+1 from score as b where b.c_id = a.c_id and b.s_score > a.s_score) as 名次 from score as a order by a.c_id,名次;
+------+------+---------+--------+
| s_id | c_id | s_score | 名次 |
+------+------+---------+--------+
| 03 | 01 | 80 | 1 |
| 01 | 01 | 80 | 1 |
| 05 | 01 | 76 | 3 |
| 02 | 01 | 70 | 4 |
| 04 | 01 | 50 | 5 |
| 06 | 01 | 31 | 6 |
| 07 | 02 | 89 | 2 |
| 01 | 02 | 90 | 1 |
| 05 | 02 | 87 | 3 |
| 03 | 02 | 80 | 4 |
| 02 | 02 | 60 | 5 |
| 04 | 02 | 30 | 6 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
| 03 | 03 | 80 | 3 |
| 02 | 03 | 80 | 3 |
| 06 | 03 | 34 | 5 |
| 04 | 03 | 20 | 6 |
+------+------+---------+--------+
select a.*,count(b.s_score)+1 as 名次 from score as a left join score as b on a.c_id=b.c_id and (a.s_score < b.s_score or (a.s_score = b.s_score and a.s_id > b.s_id)) group by a.s_id,a.c_id order by a.c_id,名次;
+------+------+---------+--------+
| 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、查询学生的总成绩并进行排名
select stu.*,c.总成绩,c.bank from student as stu,(select a.*,count(b.s_id)+1 as bank from (select s_id,sum(s_score) as 总成绩 from score group by s_id)as a left join (select s_id,sum(s_score) as b1 from score group by s_id)as b on a.总成绩<b.b1 group by a.s_id) as c where stu.s_id=c.s_id order by c.bank;
+------+--------+------------+-------+-----------+------+
| s_id | s_name | s_birth | s_sex | 总成绩 | bank |
+------+--------+------------+-------+-----------+------+
| 01 | 赵雷 | 1990-01-01 | 男 | 269 | 1 |
| 03 | 孙风 | 1990-05-20 | 男 | 240 | 2 |
| 02 | 钱电 | 1990-12-21 | 男 | 210 | 3 |
| 07 | 郑竹 | 1989-07-01 | 女 | 187 | 4 |
| 05 | 周梅 | 1991-12-01 | 女 | 163 | 5 |
| 04 | 李云 | 1990-08-06 | 男 | 100 | 6 |
| 06 | 吴兰 | 1992-03-01 | 女 | 65 | 7 |
+------+--------+------------+-------+-----------+------+
21、查询不同老师所教不同课程平均分从高到低显示
select t.*,c.c_name,s.c_id,s.s_score,avg(s_score) from teacher as t left join course as c on t.t_id=c.t_id inner join score as s on s.c_id=c.c_id group by t.t_id order by avg(s.s_score) desc;
+------+--------+--------+------+---------+--------------+
| t_id | t_name | c_name | c_id | s_score | avg(s_score) |
+------+--------+--------+------+---------+--------------+
| 01 | 张三 | 数学 | 02 | 90 | 72.6667 |
| 03 | 王五 | 英语 | 03 | 99 | 68.5000 |
| 02 | 李四 | 语文 | 01 | 80 | 64.5000 |
+------+--------+--------+------+---------+--------------+
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩*
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select c.c_id as 课程编号,c.c_name as 课程名称 ,
sum(case when s.s_score>85 and s.s_score<=100 then 1 else 0 end) as '[100-85]',
concat (round(sum(case when s.s_score>85 and s.s_score<=100 then 1 else 0 end)/count(s.s_id)*100,2),'%') as 百分比,
sum(case when s.s_score>70 and s.s_score<=85 then 1 else 0 end) as '[70-85]',
concat (round(sum(case when s.s_score>70 and s.s_score<=85 then 1 else 0 end)/count(s.s_id)*100,2),'%') as 百分比,
sum(case when s.s_score>60 and s.s_score<=70 then 1 else 0 end) as '[60-70]',
concat (round(sum(case when s.s_score>60 and s.s_score<=70 then 1 else 0 end)/count(s.s_id)*100,2),'%') as 百分比,
sum(case when s.s_score>0 and s.s_score<=60 then 1 else 0 end) as '[0-60]',
concat (round(sum(case when s.s_score>0 and s.s_score<=60 then 1 else 0 end)/count(s.s_id)*100,2),'%') as 百分比
from course as c left join score as s on c.c_id =s.c_id group by c.c_id;
+--------------+--------------+----------+-----------+---------+-----------+---------+-----------+--------+-----------+
| 课程编号 | 课程名称 | [100-85] | 百分比 | [70-85] | 百分比 | [60-70] | 百分比 | [0-60] | 百分比 |
+--------------+--------------+----------+-----------+---------+-----------+---------+-----------+--------+-----------+
| 01 | 语文 | 0 | 0.00% | 3 | 50.00% | 1 | 16.67% | 2 | 33.33% |
| 02 | 数学 | 3 | 50.00% | 1 | 16.67% | 0 | 0.00% | 2 | 33.33% |
| 03 | 英语 | 2 | 33.33% | 2 | 33.33% | 0 | 0.00% | 2 | 33.33% |
+--------------+--------------+----------+-----------+---------+-----------+---------+-----------+--------+-----------+
24、查询学生平均成绩及其名次
select s1.*,count(s2.s_id)+1 as bank from (select s_id,avg(s_score) as a1 from score group by s_id) as s1 left join (select s_id,avg(s_score) as a2 from score group by s_id) as s2 on s1.a1<s2.a2 group by s1.s_id order by bank;
+------+---------+------+
| s_id | a1 | bank |
+------+---------+------+
| 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、查询各科成绩前三名的记录*
26、查询每门课程被选修的学生数
select c_id,count(c_id) from score group by c_id ;
+------+-------------+
| c_id | count(c_id) |
+------+-------------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+-------------+
27、查询出只有两门课程的全部学生的学号和姓名
select stu.s_id,stu.s_name from student as stu where s_id in ( select s_id from score as s group by s.s_id having count(c_id)=2);
+------+--------+
| s_id | s_name |
+------+--------+
| 05 | 周梅 |
| 06 | 吴兰 |
| 07 | 郑竹 |
+------+--------+
28、查询男生、女生人数
select s_sex,count(s_sex) from student group by s_sex;
+-------+--------------+
| s_sex | count(s_sex) |
+-------+--------------+
| 女 | 4 |
| 男 | 4 |
+-------+--------------+
29、查询名字中含有"风"字的学生信息
select * from student where s_name like '%风%';
# %表示可有可无，_表示任一字符
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 03 | 孙风 | 1990-05-20 | 男 |
+------+--------+------------+-------+
30、查询同名同性学生名单，并统计同名人数
select *,count(s1.s_id) from student as s1 inner join student as s2 on s1.s_name=s2.s_name and s1.s_sex=s2.s_sex and s1.s_id!=s2.s_id group by s1.s_name,s1.s_sex;
31、查询1990年出生的学生名单
select * from student where s_birth like '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、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
35、查询所有学生的课程及分数情况；
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
37、查询不及格的学生id,姓名，及其课程名称，分数
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
39、求每门课程的学生人数
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄
-- 按照出生日期来算，当前月日<出生年月的月日则，年龄减一>
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询下月过生日的学生
扫一扫
1089
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
