株洲网站建设推广报价,惠州网站建设英语,app开发公司排名 上市企业,重庆建设工程质量监督信息网MySQL数据库练习【二】 一、MySQL基本语法二、建库建表-数据准备三、MySQL经典50题-题目四、MySQL经典50题-答案4.1、查询01课程比02课程成绩高的学生的信息及课程分数4.2、查询01课程比02课程成绩低的学生的信息及课程分数4.… MySQL数据库练习【二】 一、MySQL基本语法二、建库建表-数据准备三、MySQL经典50题-题目四、MySQL经典50题-答案4.1、查询01课程比02课程成绩高的学生的信息及课程分数4.2、查询01课程比02课程成绩低的学生的信息及课程分数4.3.0、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩4.3.1、总分超过200分的同学的学生编号和学生姓名和总成绩4.4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩4.5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩4.6、查询李姓老师的数量4.7、查询学过张三老师授课的同学的信息4.8、查询没学过张三老师授课的同学的信息4.9、查询学过编号为01并且也学过编号为02的课程的同学的信息4.10、查询学过编号为01但是没有学过编号为02的课程的同学的信息4.11、查询没有学全所有课程的同学的信息4.12、查询至少有一门课与学号为01的同学所学相同的同学的信息4.13.0、查询和01号的同学学习的课程完全相同的其他同学的信息4.13.1、MySQL中聚合函数GROUP_CONCAT()用指定分隔符连接字符串4.14、查询没学过张三老师讲授的任一门课程的学生姓名4.15、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩4.16、检索01课程分数小于60按分数降序排列的学生信息4.17.1、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩4.17.2、CASE WHEN函数4.17.3、CASE WHEN函数使用4.18、查询各科成绩最高分、最低分和平均分4.19、按各科成绩进行排序并显示排名4.20、查询学生的总成绩并进行排名4.21、查询不同老师所教不同课程平均分从高到低显示4.22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩4.23、统计各科成绩各分数段人数课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比4.24、查询学生平均成绩及其名次4.24.1、MySql中符号的使用【经典】4.25、查询各科成绩前三名的记录【经典】4.26、查询每门课程被选修的学生数4.27、查询出只有两门课程的全部学生的学号和姓名4.28、查询男生、女生人数4.29、查询名字中含有风字的学生信息4.30、查询同名同性学生名单并统计同名人数4.31、查询1990年出生的学生名单4.32、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列4.33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩4.34、查询课程名称为数学且分数低于60的学生姓名和分数4.35、查询所有学生的课程及分数情况4.36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数4.37、查询不及格的课程4.38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名4.39、求每门课程的学生人数4.40、查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩4.41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩4.42、查询每门功课成绩最好的前两名【经典】4.43、统计每门课程的学生选修人数超过5人的课程才统计要求输出课程号和选修人数查询结果按人数降序排列。4.44、检索至少选修两门课程的学生学号4.45、查询选修了全部课程的学生信息4.46、查询各学生的年龄4.47、查询本周过生日的学生4.48、查询下周过生日的学生4.49、查询本月过生日的学生4.50、查询下月过生日的学生 end 一、MySQL基本语法
-- 创建数据库时指定编码方式
create database students character set utf8;
-- 查看数据库
show databases;
-- 查看已经创建的数据库信息
show create database students;
-- 修改数据库的编码
alter database students default character set utf8;
-- 删除数据库
drop database students;-- 查看数据表
show create table student;
-- 查看表结构
desc student;
-- 修改创建好的表名 将原来的表名改为stu
alter table student rename stu;
-- 修改表中字段 将原来的字段名name改为sname字段名
alter table stu change name sname char(10);
-- 添加字段 在stu表中添加一个age整形字段
alter table student add age int;
-- 删除字段 刚刚创建的age整形字段
alter table student drop age;
-- 删除表
drop table if exists student;二、建库建表-数据准备
create database school;
use school;--1.学生表
CREATE TABLE student (sid varchar(10) NOT NULL COMMENT 学生编号,sname varchar(20) NOT NULL COMMENT 学生姓名,sage DATETIME NOT NULL COMMENT 出生年月,ssex varchar(10) NOT NULL COMMENT 学生性别
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT学生表;INSERT INTO student VALUES(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 , 女),(08 , 王菊 , 1990-01-20 , 女);select * from student;
desc student;--2.课程表
CREATE TABLE course (cid varchar(10) NOT NULL COMMENT 课程编号,cname varchar(10) NOT NULL COMMENT 课程名称,tid varchar(10) NOT NULL COMMENT 教师编号
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT课程表;INSERT INTO course VALUES(01 , 语文 , 02),(02 , 数学 , 01),(03 , 英语 , 03);select * from course;
desc course;--3.教师表
CREATE TABLE teacher (tid varchar(10) NOT NULL COMMENT 教师编号,tname varchar(10) NOT NULL COMMENT 教师姓名
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT教师表;INSERT INTO teacher VALUES(01 , 张三),(02 , 李四),(03 , 王五);select * from teacher;
desc teacher;--4.成绩表
CREATE TABLE sc (sid varchar(10) NOT NULL COMMENT 学生编号,cid varchar(10) NOT NULL COMMENT 课程编号,score DECIMAL(18,1) NOT NULL COMMENT 分数
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT成绩表;INSERT INTO sc VALUES(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 , 02 , 87),(06 , 01 , 31),
(06 , 03 , 34),(07 , 02 , 89),(07 , 03 , 98);select * from sc;
desc sc;三、MySQL经典50题-题目
4.1、查询01课程比02课程成绩高的学生的信息及课程分数
4.2、查询01课程比02课程成绩低的学生的信息及课程分数
4.3.0、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
4.3.1、总分超过200分的同学的学生编号和学生姓名和总成绩
4.4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
4.5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
4.6、查询李姓老师的数量
4.7、查询学过张三老师授课的同学的信息
4.8、查询没学过张三老师授课的同学的信息
4.9、查询学过编号为01并且也学过编号为02的课程的同学的信息
4.10、查询学过编号为01但是没有学过编号为02的课程的同学的信息
4.11、查询没有学全所有课程的同学的信息
4.12、查询至少有一门课与学号为01的同学所学相同的同学的信息
4.13.0、查询和01号的同学学习的课程完全相同的其他同学的信息
4.13.1、MySQL中聚合函数GROUP_CONCAT()用指定分隔符连接字符串
4.14、查询没学过张三老师讲授的任一门课程的学生姓名
4.15、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
4.16、检索01课程分数小于60按分数降序排列的学生信息
4.17.1、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
4.17.2、CASE WHEN函数
4.17.3、CASE WHEN函数使用
4.18、查询各科成绩最高分、最低分和平均分
4.19、按各科成绩进行排序并显示排名
4.20、查询学生的总成绩并进行排名
4.21、查询不同老师所教不同课程平均分从高到低显示
4.22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
4.23、统计各科成绩各分数段人数课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
4.24、查询学生平均成绩及其名次
4.24.1、MySql中符号的使用【经典】
4.25、查询各科成绩前三名的记录【经典】
4.26、查询每门课程被选修的学生数
4.27、查询出只有两门课程的全部学生的学号和姓名
4.28、查询男生、女生人数
4.29、查询名字中含有风字的学生信息
4.30、查询同名同性学生名单并统计同名人数
4.31、查询1990年出生的学生名单
4.32、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
4.33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
4.34、查询课程名称为数学且分数低于60的学生姓名和分数
4.35、查询所有学生的课程及分数情况
4.36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
4.37、查询不及格的课程
4.38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名
4.39、求每门课程的学生人数
4.40、查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩
4.41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
4.42、查询每门功课成绩最好的前两名【经典】
4.43、统计每门课程的学生选修人数超过5人的课程才统计要求输出课程号和选修人数查询结果按人数降序排列。
4.44、检索至少选修两门课程的学生学号
4.45、查询选修了全部课程的学生信息
4.46、查询各学生的年龄
4.47、查询本周过生日的学生
4.48、查询下周过生日的学生
4.49、查询本月过生日的学生
4.50、查询下月过生日的学生四、MySQL经典50题-答案
4.1、查询01课程比02课程成绩高的学生的信息及课程分数
-- 学生表student、成绩表sc、课程表course、教师表teacher
-- 表结构student、sc、course
-- 连接条件student.sidsc.sid
-- 先将课程为01和02的课程及对应分数筛选出来,再INNER JOIN,ON为01.sid 02.sid,WHERE条件为01.score 02.score,结果存为新t3表
-- 再将student表和t3表INNER JOINSELECT student.*,t3.score FROM
(SELECT t1.sid,t1.score FROM (SELECT sid,score FROM sc WHERE cid 01) as t1 INNER JOIN (SELECT sid,score FROM sc WHERE cid 02) as t2 ON t1.sidt2.sid WHERE t1.score t2.score
)as t3
INNER JOIN student
ON t3.sid student.sid;-----------------------------------------------
| sid | sname | sage | ssex | score |
-----------------------------------------------
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 |
-----------------------------------------------
2 rows in set (0.00 sec)selectstu.*,s1.score 01课程分数,s2.score 02课程分数
fromstudent stu,(select sid,score from sc where cid 01) s1,(select sid,score from sc where cid 02) s2
where s1.score s2.score -- 前者成绩高and s1.sid s2.sid and s1.sid stu.sid;mysql select- stu.*,- s1.score 01课程分数,- s2.score 02课程分数- from- student stu,- (select sid,score from sc where cid 01) s1,- (select sid,score from sc where cid 02) s2- where s1.score s2.score -- 前者成绩高- and s1.sid s2.sid- and s1.sid stu.sid;
------------------------------------------------------------------------
| SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 |
------------------------------------------------------------------------
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 |
------------------------------------------------------------------------
2 rows in set (0.01 sec)selectstu.*,s1.score 01课程分数,s2.score 02课程分数
fromstudent stu,sc s1,sc s2
where s1.score s2.score -- 前者成绩高and s1.sid s2.sidand s1.cid 01and s2.cid 02and s1.sid stu.sid;mysql select- stu.*,- s1.score 01课程分数,- s2.score 02课程分数- from- student stu,- sc s1,- sc s2- where s1.score s2.score -- 前者成绩高- and s1.sid s2.sid- and s1.cid 01- and s2.cid 02- and s1.sid stu.sid;
------------------------------------------------------------------------
| SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 |
------------------------------------------------------------------------
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 |
------------------------------------------------------------------------
2 rows in set (0.00 sec)select stu.*,s1.score 01课程分数,s2.score 02课程分数
from student stu
inner join sc s1 on s1.sid stu.sid and s1.cid 01 -- 两个表通过学号连接指定01
left join sc s2 on stu.sid s2.sid and s2.cid 02 or s2.cid NULL -- 指定02或者c中的c_id直接不存在
-- 为NULL的条件可以不存在因为左连接中会直接排除c表中不存在的数据包含NULL
where s1.score s2.score; -- 判断条件mysql select - stu.*,- s1.score 01课程分数,- s2.score 02课程分数- from - student stu- inner join sc s1 on s1.sid stu.sid and s1.cid 01 -- 两个表通过学号连接指定01- left join sc s2 on stu.sid s2.sid and s2.cid 02 or s2.cid NULL -- 指定02或者c中的c_id直接不存在- -- 为NULL的条件可以不存在因为左连接中会直接排除c表中不存在的数据包含NULL- where s1.score s2.score; -- 判断条件
------------------------------------------------------------------------
| SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 |
------------------------------------------------------------------------
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 |
------------------------------------------------------------------------
2 rows in set (0.00 sec)4.2、查询01课程比02课程成绩低的学生的信息及课程分数
selectstu.*,s1.score 01课程分数,s2.score 02课程分数
fromstudent stu
left join sc s1 on stu.sid s1.sid and s1.cid 01 or s1.cid NULL -- 包含NULL的数据
inner join sc s2 on stu.sid s2.sid and s2.cid 02
where s1.score s2.score;mysql select- stu.*,- s1.score 01课程分数,- s2.score 02课程分数- from- student stu- left join sc s1 on stu.sid s1.sid and s1.cid 01 or s1.cid NULL -- 包含NULL的数据- inner join sc s2 on stu.sid s2.sid and s2.cid 02- where s1.score s2.score;
------------------------------------------------------------------------
| SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 |
------------------------------------------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 80.0 | 90.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 76.0 | 87.0 |
------------------------------------------------------------------------
2 rows in set (0.00 sec)4.3.0、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
selectstu.sid 学生编号,stu.sname 学生姓名,round(avg(score),2) 平均成绩 -- round四舍五入函数
from student stu
inner join sc on stu.sid sc.sid
group by stu.sid -- 根据sid进行分组,分组之后查询每个人的平均成绩
having avg(score) 60; -- 先分组后条件过滤mysql select- stu.sid 学生编号,- stu.sname 学生姓名,- round(avg(score),2) 平均成绩- from student stu- inner join sc on stu.sid sc.sid- group by stu.sid -- 根据sid进行分组,分组之后查询每个人的平均成绩- having avg(score) 60; -- 先分组后条件过滤
------------------------------------------
| 学生编号 | 学生姓名 | 平均成绩 |
------------------------------------------
| 01 | 赵雷 | 89.67 |
| 02 | 钱电 | 70.00 |
| 03 | 孙风 | 80.00 |
| 05 | 周梅 | 81.50 |
| 07 | 郑竹 | 93.50 |
------------------------------------------
5 rows in set (0.00 sec)4.3.1、总分超过200分的同学的学生编号和学生姓名和总成绩
selectstu.sid 学生编号,stu.sname 学生姓名,round(sum(score),2) 总成绩
from student stu
inner join sc on stu.sid sc.sid
group by stu.sid
having(sum(score)) 200;mysql select- stu.sid 学生编号,- stu.sname 学生姓名,- round(sum(score),2) 总成绩- from student stu- inner join sc on stu.sid sc.sid- group by stu.sid- having(sum(score)) 200;
---------------------------------------
| 学生编号 | 学生姓名 | 总成绩 |
---------------------------------------
| 01 | 赵雷 | 269.00 |
| 02 | 钱电 | 210.00 |
| 03 | 孙风 | 240.00 |
---------------------------------------
3 rows in set (0.00 sec)4.4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
selectstu.sid 学生编号,stu.sname 学生姓名,round(avg(score)) 平均成绩
from student stu
inner join sc on stu.sid sc.sid
group by stu.sid
having avg(score) 60;mysql select- stu.sid 学生编号,- stu.sname 学生姓名,- round(avg(score)) 平均成绩- from student stu- inner join sc on stu.sid sc.sid- group by stu.sid- having avg(score) 60;
------------------------------------------
| 学生编号 | 学生姓名 | 平均成绩 |
------------------------------------------
| 04 | 李云 | 33 |
| 06 | 吴兰 | 33 |
------------------------------------------
2 rows in set (0.00 sec)王菊成绩表中无数据 select distinct sid from sc; -- 查询所有学号
selectstu.sid 学生编号,stu.sname 学生姓名,0 平均成绩
from student stu
where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中mysql select- stu.sid 学生编号,- stu.sname 学生姓名,- 0 平均成绩- from student stu- where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中
------------------------------------------
| 学生编号 | 学生姓名 | 平均成绩 |
------------------------------------------
| 08 | 王菊 | 0 |
------------------------------------------
1 row in set (0.00 sec)selectstu.sid 学生编号,stu.sname 学生姓名,round(avg(score)) 平均成绩
from student stu
inner join sc on stu.sid sc.sid
group by stu.sid
having avg(score) 60
union -- 连接两张表
selectstu.sid 学生编号,stu.sname 学生姓名,0 平均成绩
from student stu
where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中mysql select- stu.sid 学生编号,- stu.sname 学生姓名,- round(avg(score)) 平均成绩- from student stu- inner join sc on stu.sid sc.sid- group by stu.sid- having avg(score) 60- union -- 连接两张表- select- stu.sid 学生编号,- stu.sname 学生姓名,- 0 平均成绩- from student stu- where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中
------------------------------------------
| 学生编号 | 学生姓名 | 平均成绩 |
------------------------------------------
| 04 | 李云 | 33 |
| 06 | 吴兰 | 33 |
| 08 | 王菊 | 0 |
------------------------------------------
3 rows in set (0.00 sec)selectstu.sid 学生编号,stu.sname 学生姓名,round(avg(ifnull(sc.score,0))) 平均成绩
from student stu
inner join sc on stu.sid sc.sid
group by stu.sid
having avg(sc.score) 60;mysql select- stu.sid 学生编号,- stu.sname 学生姓名,- round(avg(ifnull(sc.score,0))) 平均成绩- from student stu- inner join sc on stu.sid sc.sid- group by stu.sid- having avg(sc.score) 60;
------------------------------------------
| 学生编号 | 学生姓名 | 平均成绩 |
------------------------------------------
| 04 | 李云 | 33 |
| 06 | 吴兰 | 33 |
------------------------------------------
2 rows in set (0.00 sec)4.5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
selectstu.sid 学生编号,stu.sname 学生姓名,count(sc.sid) 选课总数,sum(sc.score) 总成绩
from student stu
left join sc on stu.sid sc.sid
group by stu.sid;mysql select- stu.sid 学生编号,- stu.sname 学生姓名,- count(sc.sid) 选课总数,- sum(sc.score) 总成绩- from student stu- left join sc on stu.sid sc.sid- group by stu.sid;
-----------------------------------------------------
| 学生编号 | 学生姓名 | 选课总数 | 总成绩 |
-----------------------------------------------------
| 01 | 赵雷 | 3 | 269.0 |
| 02 | 钱电 | 3 | 210.0 |
| 03 | 孙风 | 3 | 240.0 |
| 04 | 李云 | 3 | 100.0 |
| 05 | 周梅 | 2 | 163.0 |
| 06 | 吴兰 | 2 | 65.0 |
| 07 | 郑竹 | 2 | 187.0 |
| 08 | 王菊 | 0 | NULL |
-----------------------------------------------------
8 rows in set (0.00 sec)4.6、查询李姓老师的数量
selectcount(t.tname)
from teacher t
where t.tname like 李%; -- 通配符mysql select- count(t.tname)- from teacher t- where t.tname like 李%; -- 通配符
----------------
| count(t.tname) |
----------------
| 1 |
----------------
1 row in set (0.00 sec)selectt.tname,count(t.tname)
from teacher t
where left(t.tname,1) in (李); -- left函数mysql select- t.tname,- count(t.tname)- from teacher t- where left(t.tname,1) in (李); -- left函数
------------------------
| tname | count(t.tname) |
------------------------
| 李四 | 1 |
------------------------
1 row in set (0.00 sec)4.7、查询学过张三老师授课的同学的信息
-- 查询课程cid
select cid
from teacher,course
where teacher.tid course.tid and teacher.tname 张三;selectstu.*
from student stu,sc
where stu.sid sc.sid
and sc.cid in (select cid from teacher,coursewhere teacher.tid course.tid and teacher.tname 张三);mysql select- stu.*- from student stu,sc- where stu.sid sc.sid- and sc.cid in (- select cid - from teacher,course- where teacher.tid course.tid and teacher.tname 张三);
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
----------------------------------------
6 rows in set (0.00 sec)select stu.*
from teacher t
left join course c on t.tid c.tid -- 课程表和教师表
left join sc on c.cid sc.cid -- 成绩表和课程表
left join student stu on sc.sid stu.sid -- 学生表和成绩表
where t.tname 张三;mysql select stu.*- from teacher t- left join course c on t.tid c.tid -- 课程表和教师表- left join sc on c.cid sc.cid -- 成绩表和课程表- left join student stu on sc.sid stu.sid -- 学生表和成绩表- where t.tname 张三;
-----------------------------------------
| sid | sname | sage | ssex |
-----------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
-----------------------------------------
6 rows in set (0.00 sec)4.8、查询没学过张三老师授课的同学的信息
select distinct sc.sid
from teacher t
left join course c on t.tid c.tid
left join sc on c.cid sc.cid
where t.tname 张三;select stu.*
from student stu
where sid not in (select distinct sc.sidfrom teacher tleft join course c on t.tid c.tidleft join sc on c.cid sc.cidwhere t.tname 张三);mysql select stu.*- from student stu- where sid not in (- select distinct sc.sid- from teacher t- left join course c on t.tid c.tid- left join sc on c.cid sc.cid- where t.tname 张三);
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
----------------------------------------
2 rows in set (0.00 sec)4.9、查询学过编号为01并且也学过编号为02的课程的同学的信息
select s1.sid
from sc s1
inner join sc s2 on s1.sid s2.sid
where s1.cid 01 and s2.cid 02;select stu.*
from student stu
where stu.sid in (select s1.sidfrom sc s1inner join sc s2 on s1.sid s2.sidwhere s1.cid 01 and s2.cid 02);mysql select stu.*- from student stu- where stu.sid in (- select s1.sid- from sc s1- inner join sc s2 on s1.sid s2.sid- where s1.cid 01 and s2.cid 02);
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
----------------------------------------
5 rows in set (0.00 sec)select stu.*
from student stu,sc s1,sc s2
where s1.sid s2.sid
and stu.sid s1.sid
and s1.cid 01
and s2.cid 02;mysql select stu.*- from student stu,sc s1,sc s2- where s1.sid s2.sid- and stu.sid s1.sid- and s1.cid 01- and s2.cid 02;
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
----------------------------------------
5 rows in set (0.00 sec)4.10、查询学过编号为01但是没有学过编号为02的课程的同学的信息
-- 查询学过02课程的学生sid
select sid from sc where sc.cid 02;select sid
from sc
where sc.cid 01 -- 修过01课程的学号
and sc.sid not in (select sid from sc where sc.cid 02); -- 同时学号不能在修过02课程中出现select stu.*
from student stu
where stu.sid in(select sid from sc where sc.cid 01 and sc.sid not in (select sid from sc where sc.cid 02)
);mysql select stu.*- from student stu- where stu.sid in(- select sid - from sc - where sc.cid 01 - and sc.sid not in (select sid from sc where sc.cid 02)- );
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
----------------------------------------
1 row in set (0.00 sec)4.11、查询没有学全所有课程的同学的信息
select stu.*
from student stu
left join sc on stu.sid sc.sid
group by stu.sid -- 学号分组
having count(sc.cid) (select count(*) from course); -- 筛选分组后学生的课程数3mysql select stu.*- from student stu- left join sc on stu.sid sc.sid- group by stu.sid -- 学号分组- having count(sc.cid) (select count(*) from course); -- 筛选分组后学生的课程数3
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
----------------------------------------
4 rows in set (0.00 sec)select sc.sid
from sc
group by sc.sid
having count(sc.cid) (select count(*) from course);mysql select sc.sid- from sc- group by sc.sid- having count(sc.cid) (select count(*) from course);
-----
| sid |
-----
| 01 |
| 02 |
| 03 |
| 04 |
-----
4 rows in set (0.00 sec)select stu.*
from student stu
where stu.sid not in (select sc.sidfrom scgroup by sc.sidhaving count(sc.cid) (select count(*) from course));mysql select stu.*- from student stu- where stu.sid not in (- select sc.sid- from sc- group by sc.sid- having count(sc.cid) (select count(*) from course));
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
----------------------------------------
4 rows in set (0.00 sec)4.12、查询至少有一门课与学号为01的同学所学相同的同学的信息
-- 查询01号同学的所学课程cid
select sc.cid from sc where sc.sid 01;select stu.*
from student stu
inner join sc on stu.sid sc.sid -- 学生表和成绩表的关联
and sc.cid in (select sc.cid from sc where sc.sid 01) -- 对课程进行限制只在01学生的课程内
group by stu.sid; -- 根据学号进行分组mysql select stu.*- from student stu- inner join sc on stu.sid sc.sid -- 学生表和成绩表的关联- and sc.cid in (select sc.cid from sc where sc.sid 01) -- 对课程进行限制只在01学生的课程内- group by stu.sid; -- 根据学号进行分组
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
----------------------------------------
7 rows in set (0.00 sec)4.13.0、查询和01号的同学学习的课程完全相同的其他同学的信息
-- 查询01号同学学习的课程cid
select sc.cid from sc where sc.sid 01;
-- 查询01号同学学习的课程cid总数
select count(sc.cid) from sc where sc.sid 01;select stu.*
from student stu
inner join sc on stu.sid sc.sid and sc.cid in (select sc.cid from sc where sc.sid 01) -- 和01同学课程相同and sc.sid ! 01 -- 排除自身
group by stu.sid -- 根据sid进行分组
having (select count(sc.cid) from sc where sc.sid stu.sid) -- 和01同学所学课程总数相同(select count(sc.cid) from sc where sc.sid 01);mysql select stu.*- from student stu- inner join sc on stu.sid sc.sid - and sc.cid in (select sc.cid from sc where sc.sid 01) -- 和01同学课程相同- and sc.sid ! 01 -- 排除自身- group by stu.sid -- 根据sid进行分组- having (select count(sc.cid) from sc where sc.sid stu.sid) -- 和01同学所学课程总数相同- (select count(sc.cid) from sc where sc.sid 01);
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
----------------------------------------
3 rows in set (0.00 sec)4.13.1、MySQL中聚合函数GROUP_CONCAT()用指定分隔符连接字符串
-- 语法
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator 分隔符])select sid,group_concat(cid order by cid)
from sc
group by sid;mysql select sid,group_concat(cid order by cid)- from sc- group by sid;
-------------------------------------
| sid | group_concat(cid order by cid) |
-------------------------------------
| 01 | 01,02,03 |
| 02 | 01,02,03 |
| 03 | 01,02,03 |
| 04 | 01,02,03 |
| 05 | 01,02 |
| 06 | 01,03 |
| 07 | 02,03 |
-------------------------------------
7 rows in set (0.00 sec)select sid
from sc
group by sid
having group_concat(cid order by cid) ( -- 找出和01号学生分组合并结果相同的学号sidselect group_concat(cid order by cid) -- 01号学生分组合并同时进行排序from scgroup by sidhaving sid 01)and sid ! 01; -- 排除01号同学mysql select sid- from sc- group by sid- having group_concat(cid order by cid) (- select group_concat(cid order by cid)- from sc- group by sid- having sid 01)- and sid ! 01;
-----
| sid |
-----
| 02 |
| 03 |
| 04 |
-----
3 rows in set (0.00 sec)4.14、查询没学过张三老师讲授的任一门课程的学生姓名
-- 查询张三老师的课程cid
select cid
from course s
inner join teacher t on s.tid t.tid and t.tname 张三;-- 查询学过张三老师课程的学生sid
select sid
from sc
where cid in (select cidfrom course sinner join teacher t on s.tid t.tid and t.tname 张三);select stu.*
from student stu
where stu.sid not in (
select sid from sc where cid in (select cid from course s inner join teacher t on s.tid t.tid and t.tname 张三)
);mysql select stu.*- from student stu- where stu.sid not in (- select sid from sc where cid in (- select cid from course s inner join teacher t on s.tid t.tid and t.tname 张三)- );
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
----------------------------------------
2 rows in set (0.00 sec)4.15、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
-- 查询成绩不合格同学的学号
select sid
from sc
where score 60 -- 小于60分不及格
group by sid
having count(*) 2; -- 不及格两门以上select stu.sname,stu.sid,round(avg(score),2)
from student stu
left join sc on stu.sid sc.sid
where stu.sid in (select sidfrom scwhere score 60group by sidhaving count(*) 2)
group by stu.sid;mysql select stu.sname,stu.sid,round(avg(score),2)- from student stu- left join sc on stu.sid sc.sid- where stu.sid in (- select sid- from sc- where score 60- group by sid- having count(*) 2)- group by stu.sid;
----------------------------------
| sname | sid | round(avg(score),2) |
----------------------------------
| 李云 | 04 | 33.33 |
| 吴兰 | 06 | 32.50 |
----------------------------------
2 rows in set (0.00 sec)select stu.sname,sc.sid,round(avg(score),2)
from sc
inner join student stu on sc.sid stu.sid
where sc.score 60
group by sc.sid
having count(sc.score) 2;mysql select stu.sname,sc.sid,round(avg(score),2)- from sc- inner join student stu on sc.sid stu.sid- where sc.score 60- group by sc.sid- having count(sc.score) 2;
----------------------------------
| sname | sid | round(avg(score),2) |
----------------------------------
| 李云 | 04 | 33.33 |
| 吴兰 | 06 | 32.50 |
----------------------------------
2 rows in set (0.00 sec)select stu.sid,stu.sname,round(avg(score),2)
from student stu
inner join sc on stu.sid sc.sid and sc.score 60 -- 内连接且分数小于60
group by stu.sid -- 根据sid分组
having count(sc.sid) 2; -- 两门课mysql select stu.sid,stu.sname,round(avg(score),2)- from student stu- inner join sc on stu.sid sc.sid and sc.score 60 -- 内连接且分数小于60- group by stu.sid -- 根据sid分组- having count(sc.sid) 2; -- 两门课
----------------------------------
| sid | sname | round(avg(score),2) |
----------------------------------
| 04 | 李云 | 33.33 |
| 06 | 吴兰 | 32.50 |
----------------------------------
2 rows in set (0.00 sec)4.16、检索01课程分数小于60按分数降序排列的学生信息
select stu.*,sc.score
from student stu
left join sc on stu.sid sc.sid
where sc.cid 01 and sc.score 60
order by score desc;mysql select stu.*,sc.score- from student stu- left join sc on stu.sid sc.sid- where sc.cid 01 and sc.score 60- order by score desc;
-----------------------------------------------
| sid | sname | sage | ssex | score |
-----------------------------------------------
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 31.0 |
-----------------------------------------------
2 rows in set (0.01 sec)4.17.1、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 查询每位同学平均成绩并降序
select sc.sid,avg(score) from sc group by sc.sid order by avg(score) desc;selects.sid,(select sname from student where student.sid s.sid) 姓名,(select score from sc where sc.sid s.sid and sc.cid 01) 语文,(select score from sc where sc.sid s.sid and sc.cid 02) 数学,(select score from sc where sc.sid s.sid and sc.cid 03) 英语,round(avg(s.score),2) 平均成绩
from sc s
group by s.sid
order by avg(s.score) desc;mysql select- s.sid,- (select sname from student where student.sid s.sid) 姓名,- (select score from sc where sc.sid s.sid and sc.cid 01) 语文,- (select score from sc where sc.sid s.sid and sc.cid 02) 数学,- (select score from sc where sc.sid s.sid and sc.cid 03) 英语,- round(avg(s.score),2) 平均成绩- from sc s- group by s.sid - order by avg(s.score) desc;
---------------------------------------------------
| sid | 姓名 | 语文 | 数学 | 英语 | 平均成绩 |
---------------------------------------------------
| 07 | 郑竹 | NULL | 89.0 | 98.0 | 93.50 |
| 01 | 赵雷 | 80.0 | 90.0 | 99.0 | 89.67 |
| 05 | 周梅 | 76.0 | 87.0 | NULL | 81.50 |
| 03 | 孙风 | 80.0 | 80.0 | 80.0 | 80.00 |
| 02 | 钱电 | 70.0 | 60.0 | 80.0 | 70.00 |
| 04 | 李云 | 50.0 | 30.0 | 20.0 | 33.33 |
| 06 | 吴兰 | 31.0 | NULL | 34.0 | 32.50 |
---------------------------------------------------
7 rows in set (0.00 sec)没有第八号学生 4.17.2、CASE WHEN函数
CASE SCORE WHEN A THEN 优 ELSE 不及格 END
-- THEN后边的值与ELSE后边的值类型应一致否则会报错
-- 使用 IF 函数进行替换
IF(SCORE A, 优, 不及格)-- CASE WHEN条件表达式函数
CASE WHEN condition THEN result[WHEN...THEN...]ELSE resultENDCASE WHEN SCORE 90 THEN 优WHEN SCORE 70 THEN 良WHEN SCORE 60 THEN 中 ELSE 不及格 END4.17.3、CASE WHEN函数使用
selectstu.sname,sc.sid,sum(case when sc.cid 01 then ifnull(sc.score,0) else 0 end) 语文,sum(case when sc.cid 02 then ifnull(sc.score,0) else 0 end) 数学,sum(case when sc.cid 03 then ifnull(sc.score,0) else 0 end) 英语,round(avg(sc.score),2) 平均成绩
from sc
left join student stu on sc.sid stu.sid
group by sc.sid
union
select stu.sname,stu.sid,0 语文,0 数学,0 英语,0 平均成绩
from student stu
where stu.sid not in (select distinct sid from sc)
order by 6 desc;mysql select- stu.sname,- sc.sid,- sum(case when sc.cid 01 then ifnull(sc.score,0) else 0 end) 语文,- sum(case when sc.cid 02 then ifnull(sc.score,0) else 0 end) 数学,- sum(case when sc.cid 03 then ifnull(sc.score,0) else 0 end) 英语,- round(avg(sc.score),2) 平均成绩- from sc- left join student stu on sc.sid stu.sid- group by sc.sid- union- select stu.sname,stu.sid,0 语文,0 数学,0 英语,0 平均成绩- from student stu- where stu.sid not in (select distinct sid from sc)- order by 6 desc;
---------------------------------------------------
| sname | sid | 语文 | 数学 | 英语 | 平均成绩 |
---------------------------------------------------
| 郑竹 | 07 | 0.0 | 89.0 | 98.0 | 93.50 |
| 赵雷 | 01 | 80.0 | 90.0 | 99.0 | 89.67 |
| 周梅 | 05 | 76.0 | 87.0 | 0.0 | 81.50 |
| 孙风 | 03 | 80.0 | 80.0 | 80.0 | 80.00 |
| 钱电 | 02 | 70.0 | 60.0 | 80.0 | 70.00 |
| 李云 | 04 | 50.0 | 30.0 | 20.0 | 33.33 |
| 吴兰 | 06 | 31.0 | 0.0 | 34.0 | 32.50 |
| 王菊 | 08 | 0.0 | 0.0 | 0.0 | 0.00 |
---------------------------------------------------
8 rows in set (0.00 sec)有第八号学生 selectstu.sname,stu.sid,s1.score 语文,s2.score 数学,s3.score 英语,round(avg(s.score),2) 平均成绩
from student stu
left join sc s on stu.sid s.sid
left join sc s1 on s.sid s1.sid and s1.cid 01
left join sc s2 on s.sid s2.sid and s2.cid 02
left join sc s3 on s.sid s3.sid and s3.cid 03
group by stu.sid
order by 6 desc;mysql select- stu.sname,- stu.sid,- s1.score 语文,- s2.score 数学,- s3.score 英语,- round(avg(s.score),2) 平均成绩- from student stu- left join sc s on stu.sid s.sid- left join sc s1 on s.sid s1.sid and s1.cid 01- left join sc s2 on s.sid s2.sid and s2.cid 02- left join sc s3 on s.sid s3.sid and s3.cid 03- group by stu.sid- order by 6 desc;
---------------------------------------------------
| sname | sid | 语文 | 数学 | 英语 | 平均成绩 |
---------------------------------------------------
| 郑竹 | 07 | NULL | 89.0 | 98.0 | 93.50 |
| 赵雷 | 01 | 80.0 | 90.0 | 99.0 | 89.67 |
| 周梅 | 05 | 76.0 | 87.0 | NULL | 81.50 |
| 孙风 | 03 | 80.0 | 80.0 | 80.0 | 80.00 |
| 钱电 | 02 | 70.0 | 60.0 | 80.0 | 70.00 |
| 李云 | 04 | 50.0 | 30.0 | 20.0 | 33.33 |
| 吴兰 | 06 | 31.0 | NULL | 34.0 | 32.50 |
| 王菊 | 08 | NULL | NULL | NULL | NULL |
---------------------------------------------------
8 rows in set (0.00 sec)-- 非空判断
selectstu.sname,stu.sid,if(s1.score is NULL,0,s1.score) 语文,if(s2.score is NULL,0,s2.score) 数学,if(s3.score is NULL,0,s3.score) 英语,if(round(avg(s.score),2) is NULL,0,round(avg(s.score),2)) 平均成绩
from student stu
left join sc s on stu.sid s.sid
left join sc s1 on s.sid s1.sid and s1.cid 01
left join sc s2 on s.sid s2.sid and s2.cid 02
left join sc s3 on s.sid s3.sid and s3.cid 03
group by stu.sid
order by 6 desc;mysql select- stu.sname,- stu.sid,- if(s1.score is NULL,0,s1.score) 语文,- if(s2.score is NULL,0,s2.score) 数学,- if(s3.score is NULL,0,s3.score) 英语,- if(round(avg(s.score),2) is NULL,0,round(avg(s.score),2)) 平均成绩- from student stu- left join sc s on stu.sid s.sid- left join sc s1 on s.sid s1.sid and s1.cid 01- left join sc s2 on s.sid s2.sid and s2.cid 02- left join sc s3 on s.sid s3.sid and s3.cid 03- group by stu.sid- order by 6 desc;
---------------------------------------------------
| sname | sid | 语文 | 数学 | 英语 | 平均成绩 |
---------------------------------------------------
| 郑竹 | 07 | 0.0 | 89.0 | 98.0 | 93.50 |
| 赵雷 | 01 | 80.0 | 90.0 | 99.0 | 89.67 |
| 周梅 | 05 | 76.0 | 87.0 | 0.0 | 81.50 |
| 孙风 | 03 | 80.0 | 80.0 | 80.0 | 80.00 |
| 钱电 | 02 | 70.0 | 60.0 | 80.0 | 70.00 |
| 李云 | 04 | 50.0 | 30.0 | 20.0 | 33.33 |
| 吴兰 | 06 | 31.0 | 0.0 | 34.0 | 32.50 |
| 王菊 | 08 | 0.0 | 0.0 | 0.0 | 0 |
---------------------------------------------------
8 rows in set (0.00 sec)4.18、查询各科成绩最高分、最低分和平均分 以如下形式显示课程ID课程name最高分最低分平均分及格率中等率优良率优秀率 及格60中等为70-80优良为80-90优秀为90 case when score then 1 else 0 end -- 总人数
case when score 60 then 1 else 0 end -- 及格
case when score 70 and score 80 then 1 else 0 end -- 中等
case when score 80 and score 90 then 1 else 0 end -- 优良
case when score 90 then 1 else 0 end -- 优秀selectsc.cid 课程ID,c.cname 课程name,max(sc.score) 最高分,min(sc.score) 最低分,round(avg(sc.score),2) 平均分,round(100*(sum(case when score 60 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 及格率,round(100*(sum(case when score 70 and score 80 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 中等率,round(100*(sum(case when score 80 and score 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优良率,round(100*(sum(case when score 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优秀率
from sc
left join course c on sc.cid c.cid
group by sc.cid,c.cname;mysql select- sc.cid 课程ID,- c.cname 课程name,- max(sc.score) 最高分,- min(sc.score) 最低分,- round(avg(sc.score),2) 平均分,- round(100*(sum(case when score 60 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 及格率,- round(100*(sum(case when score 70 and score 80 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 中等率,- round(100*(sum(case when score 80 and score 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优良率,- round(100*(sum(case when score 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优秀率- from sc- left join course c on sc.cid c.cid- group by sc.cid,c.cname;
---------------------------------------------------------------------------------------------------
| 课程ID | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
---------------------------------------------------------------------------------------------------
| 01 | 语文 | 80.0 | 31.0 | 64.50 | 66.67 | 50.00 | 0.00 | 0.00 |
| 02 | 数学 | 90.0 | 30.0 | 72.67 | 83.33 | 16.67 | 33.33 | 16.67 |
| 03 | 英语 | 99.0 | 20.0 | 68.50 | 66.67 | 33.33 | 0.00 | 33.33 |
---------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)count(1) 替换 sum(case when score then 1 else 0 end) 总人数 selectsc.cid 课程ID,c.cname 课程name,max(sc.score) 最高分,min(sc.score) 最低分,round(avg(sc.score),2) 平均分,round(100*(sum(case when score 60 then 1 else 0 end) / count(1)),2) 及格率,round(100*(sum(case when score 70 and score 80 then 1 else 0 end) / count(1)),2) 中等率,round(100*(sum(case when score 80 and score 90 then 1 else 0 end) / count(1)),2) 优良率,round(100*(sum(case when score 90 then 1 else 0 end) / count(1)),2) 优秀率
from sc
left join course c on sc.cid c.cid
group by sc.cid,c.cname;mysql select- sc.cid 课程ID,- c.cname 课程name,- max(sc.score) 最高分,- min(sc.score) 最低分,- round(avg(sc.score),2) 平均分,- round(100*(sum(case when score 60 then 1 else 0 end) / count(1)),2) 及格率,- round(100*(sum(case when score 70 and score 80 then 1 else 0 end) / count(1)),2) 中等率,- round(100*(sum(case when score 80 and score 90 then 1 else 0 end) / count(1)),2) 优良率,- round(100*(sum(case when score 90 then 1 else 0 end) / count(1)),2) 优秀率- from sc- left join course c on sc.cid c.cid- group by sc.cid,c.cname;
---------------------------------------------------------------------------------------------------
| 课程ID | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
---------------------------------------------------------------------------------------------------
| 01 | 语文 | 80.0 | 31.0 | 64.50 | 66.67 | 50.00 | 0.00 | 0.00 |
| 02 | 数学 | 90.0 | 30.0 | 72.67 | 83.33 | 16.67 | 33.33 | 16.67 |
| 03 | 英语 | 99.0 | 20.0 | 68.50 | 66.67 | 33.33 | 0.00 | 33.33 |
---------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)4.19、按各科成绩进行排序并显示排名
select s1.sid,s1.cid,s1.score,(select count(distinct score) from sc where cid s1.cid and score s1.score)1 AS 排名
from sc s1
order by s1.cid,4;mysql select - s1.sid,- s1.cid,- s1.score,- (select count(distinct score) from sc where cid s1.cid and score s1.score)1 AS 排名 - from sc s1- order by s1.cid,4;
-------------------------
| sid | cid | score | 排名 |
-------------------------
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 2 |
| 02 | 01 | 70.0 | 3 |
| 04 | 01 | 50.0 | 4 |
| 06 | 01 | 31.0 | 5 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 03 | 02 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 04 | 02 | 30.0 | 6 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
| 06 | 03 | 34.0 | 4 |
| 04 | 03 | 20.0 | 5 |
-------------------------
18 rows in set (0.00 sec)select stu.sname 学生姓名,c.cname 课程名,s1.score 分数,(select count(distinct score) from sc where cid s1.cid and score s1.score)1 AS 排名
from sc s1
join student stu on s1.sid stu.sid
join course c on s1.cid c.cid
order by s1.cid,4;mysql select - stu.sname 学生姓名,- c.cname 课程名,- s1.score 分数,- (select count(distinct score) from sc where cid s1.cid and score s1.score)1 AS 排名 - from sc s1- join student stu on s1.sid stu.sid- join course c on s1.cid c.cid- order by s1.cid,4;
-----------------------------------------
| 学生姓名 | 课程名 | 分数 | 排名 |
-----------------------------------------
| 赵雷 | 语文 | 80.0 | 1 |
| 孙风 | 语文 | 80.0 | 1 |
| 周梅 | 语文 | 76.0 | 2 |
| 钱电 | 语文 | 70.0 | 3 |
| 李云 | 语文 | 50.0 | 4 |
| 吴兰 | 语文 | 31.0 | 5 |
| 赵雷 | 数学 | 90.0 | 1 |
| 郑竹 | 数学 | 89.0 | 2 |
| 周梅 | 数学 | 87.0 | 3 |
| 孙风 | 数学 | 80.0 | 4 |
| 钱电 | 数学 | 60.0 | 5 |
| 李云 | 数学 | 30.0 | 6 |
| 赵雷 | 英语 | 99.0 | 1 |
| 郑竹 | 英语 | 98.0 | 2 |
| 孙风 | 英语 | 80.0 | 3 |
| 钱电 | 英语 | 80.0 | 3 |
| 吴兰 | 英语 | 34.0 | 4 |
| 李云 | 英语 | 20.0 | 5 |
-----------------------------------------
18 rows in set (0.01 sec)4.20、查询学生的总成绩并进行排名
selectstu.sname 学生姓名,sum(score) 总成绩
from sc
inner join student stu on sc.sid stu.sid
group by sc.sid
order by 2 asc;mysql select- stu.sname 学生姓名,- sum(score) 总成绩- from sc- inner join student stu on sc.sid stu.sid- group by sc.sid- order by 2 asc;
-------------------------
| 学生姓名 | 总成绩 |
-------------------------
| 吴兰 | 65.0 |
| 李云 | 100.0 |
| 周梅 | 163.0 |
| 郑竹 | 187.0 |
| 钱电 | 210.0 |
| 孙风 | 240.0 |
| 赵雷 | 269.0 |
-------------------------
7 rows in set (0.00 sec)select sid,sum(score) sscore from sc group by sid;selectstu.sname 学生姓名,t1.sscore 总成绩,(select count(distinct t2.sscore) from (select sid,sum(score) sscore from sc group by sid) t2 where t2.sscore t1.sscore)1 排名
from student stu,(select sid,sum(score) sscore from sc group by sid) t1
where stu.sid t1.sid
order by 3;mysql select- stu.sname 学生姓名,- t1.sscore 总成绩,- (select count(distinct t2.sscore) - from (select sid,sum(score) sscore from sc group by sid) t2 - where t2.sscore t1.sscore- )1 排名- from student stu,(select sid,sum(score) sscore from sc group by sid) t1- where stu.sid t1.sid- order by 3;
---------------------------------
| 学生姓名 | 总成绩 | 排名 |
---------------------------------
| 赵雷 | 269.0 | 1 |
| 孙风 | 240.0 | 2 |
| 钱电 | 210.0 | 3 |
| 郑竹 | 187.0 | 4 |
| 周梅 | 163.0 | 5 |
| 李云 | 100.0 | 6 |
| 吴兰 | 65.0 | 7 |
---------------------------------
7 rows in set (0.00 sec)4.21、查询不同老师所教不同课程平均分从高到低显示
select c.cid,c.cname,t.tname
from course c
join teacher t on c.tid t.tid;mysql select c.cid,c.cname,t.tname- from course c- join teacher t on c.tid t.tid;
---------------------
| cid | cname | tname |
---------------------
| 02 | 数学 | 张三 |
| 01 | 语文 | 李四 |
| 03 | 英语 | 王五 |
---------------------
3 rows in set (0.00 sec)select cid,avg(score) from sc group by cid;mysql select cid,avg(score) from sc group by cid;
-----------------
| cid | avg(score) |
-----------------
| 01 | 64.50000 |
| 02 | 72.66667 |
| 03 | 68.50000 |
-----------------
3 rows in set (0.00 sec)select c.cid 课程ID,c.cname 课程名,t.tname 教师名,score.avgscore 课程均分
from course c
join teacher t on c.tid t.tid
join (select cid,avg(score) avgscore from sc group by cid) score on c.cid score.cid
order by 4 desc;mysql select c.cid 课程ID,c.cname 课程名,t.tname 教师名,score.avgscore 课程均分- from course c- join teacher t on c.tid t.tid- join (select cid,avg(score) avgscore from sc group by cid) score on c.cid score.cid- order by 4 desc;
----------------------------------------------
| 课程ID | 课程名 | 教师名 | 课程均分 |
----------------------------------------------
| 02 | 数学 | 张三 | 72.66667 |
| 03 | 英语 | 王五 | 68.50000 |
| 01 | 语文 | 李四 | 64.50000 |
----------------------------------------------
3 rows in set (0.00 sec)select c.cid 课程ID,c.cname 课程名,t.tname 教师名,round(avg(score),2) 课程均分
from course c
left join teacher t on c.tid t.tid
left join sc on c.cid sc.cid
group by c.cid
order by 4 desc;mysql select c.cid 课程ID,c.cname 课程名,t.tname 教师名,round(avg(score),2) 课程均分- from course c- left join teacher t on c.tid t.tid- left join sc on c.cid sc.cid- group by c.cid- order by 4 desc;
----------------------------------------------
| 课程ID | 课程名 | 教师名 | 课程均分 |
----------------------------------------------
| 02 | 数学 | 张三 | 72.67 |
| 03 | 英语 | 王五 | 68.50 |
| 01 | 语文 | 李四 | 64.50 |
----------------------------------------------
3 rows in set (0.00 sec)4.22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT a.* , (SELECT COUNT(DISTINCT score) FROM SC WHERE CID a.CID AND score a.score) 1 AS 排名
FROM SC AS a HAVING 排名 BETWEEN 2 AND 3 ORDER BY a.CID,排名;selects1.*,(select count(distinct score) from sc where cid s1.cid and score s1.score)1 排名
from sc s1;mysql select- s1.*,- (select count(distinct score) from sc where cid s1.cid and score s1.score)1 排名- from sc s1;
-------------------------
| sid | cid | score | 排名 |
-------------------------
| 01 | 01 | 80.0 | 1 |
| 01 | 02 | 90.0 | 1 |
| 01 | 03 | 99.0 | 1 |
| 02 | 01 | 70.0 | 3 |
| 02 | 02 | 60.0 | 5 |
| 02 | 03 | 80.0 | 3 |
| 03 | 01 | 80.0 | 1 |
| 03 | 02 | 80.0 | 4 |
| 03 | 03 | 80.0 | 3 |
| 04 | 01 | 50.0 | 4 |
| 04 | 02 | 30.0 | 6 |
| 04 | 03 | 20.0 | 5 |
| 05 | 01 | 76.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 06 | 01 | 31.0 | 5 |
| 06 | 03 | 34.0 | 4 |
| 07 | 02 | 89.0 | 2 |
| 07 | 03 | 98.0 | 2 |
-------------------------
18 rows in set (0.00 sec)selectstu.sname 学生名字,c.cname 课程名,s1.score 分数,(select count(distinct score) from sc where cid s1.cid and score s1.score)1 rank
from sc s1
inner join student stu on s1.sid stu.sid
inner join course c on s1.cid c.cid
having rank between 2 and 3
order by 4 desc;mysql select- stu.sname 学生名字,- c.cname 课程名,- s1.score 分数,- (select count(distinct score) from sc where cid s1.cid and score s1.score)1 rank- from sc s1- inner join student stu on s1.sid stu.sid- inner join course c on s1.cid c.cid- having rank between 2 and 3- order by 4 desc;
---------------------------------------
| 学生名字 | 课程名 | 分数 | rank |
---------------------------------------
| 钱电 | 英语 | 80.0 | 3 |
| 钱电 | 语文 | 70.0 | 3 |
| 孙风 | 英语 | 80.0 | 3 |
| 周梅 | 数学 | 87.0 | 3 |
| 郑竹 | 数学 | 89.0 | 2 |
| 周梅 | 语文 | 76.0 | 2 |
| 郑竹 | 英语 | 98.0 | 2 |
---------------------------------------
7 rows in set (0.01 sec)4.23、统计各科成绩各分数段人数课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
selectsc.cid 课程编号,sum(case when score 0 and score 60 then 1 else 0 end) 0-60人数,round(100 * (sum(case when score 0 and score 60 then 1 else 0 end) / count(*) ),2) 0-60占比
from sc
group by sc.cid; -- 分课程统计mysql select- sc.cid 课程编号,- sum(case when score 0 and score 60 then 1 else 0 end) 0-60人数,- round(100 * (sum(case when score 0 and score 60 then 1 else 0 end) / count(*) ),2) 0-60占比- from sc- group by sc.cid; -- 分课程统计
--------------------------------------
| 课程编号 | 0-60人数 | 0-60占比 |
--------------------------------------
| 01 | 2 | 33.33 |
| 02 | 1 | 16.67 |
| 03 | 2 | 33.33 |
--------------------------------------
3 rows in set (0.00 sec)selectsc.cid 课程编号,c.cname 科目,count(*) 课程人数,sum(case when score 85 then 1 else 0 end) 85-100人数,round(100 * (sum(case when score 85 then 1 else 0 end) / count(*) ),2) 85-100占比,sum(case when score 85 and score 70 then 1 else 0 end) 70-85人数,round(100 * (sum(case when score 85 and score 70 then 1 else 0 end) / count(*) ),2) 70-85占比,sum(case when score 70 and score 60 then 1 else 0 end) 60-70人数,round(100 * (sum(case when score 70 and score 60 then 1 else 0 end) / count(*) ),2) 60-70占比,sum(case when score 0 and score 60 then 1 else 0 end) 0-60人数,round(100 * (sum(case when score 0 and score 60 then 1 else 0 end) / count(*) ),2) 0-60占比
from sc
left join course c on sc.cid c.cid
group by sc.cid -- 分课程统计
order by sc.cid;mysql select- sc.cid 课程编号,- c.cname 科目,- count(*) 课程人数,- sum(case when score 85 then 1 else 0 end) 85-100人数,- round(100 * (sum(case when score 85 then 1 else 0 end) / count(*) ),2) 85-100占比,- sum(case when score 85 and score 70 then 1 else 0 end) 70-85人数,- round(100 * (sum(case when score 85 and score 70 then 1 else 0 end) / count(*) ),2) 70-85占比,- sum(case when score 70 and score 60 then 1 else 0 end) 60-70人数,- round(100 * (sum(case when score 70 and score 60 then 1 else 0 end) / count(*) ),2) 60-70占比,- sum(case when score 0 and score 60 then 1 else 0 end) 0-60人数,- round(100 * (sum(case when score 0 and score 60 then 1 else 0 end) / count(*) ),2) 0-60占比- from sc- left join course c on sc.cid c.cid- group by sc.cid -- 分课程统计- order by sc.cid;--------------------------------------------------------------------------------------------------------------------------------------------
| 课程编号 | 科目 | 课程人数 | 85-100人数 | 85-100占比 | 70-85人数 | 70-85占比 | 60-70人数 | 60-70占比 | 0-60人数 | 0-60占比 |
--------------------------------------------------------------------------------------------------------------------------------------------
| 01 | 语文 | 6 | 0 | 0.00 | 4 | 66.67 | 0 | 0.00 | 2 | 33.33 |
| 02 | 数学 | 6 | 3 | 50.00 | 1 | 16.67 | 1 | 16.67 | 1 | 16.67 |
| 03 | 英语 | 6 | 2 | 33.33 | 2 | 33.33 | 0 | 0.00 | 2 | 33.33 |
--------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)4.24、查询学生平均成绩及其名次
select sc.sid,avg(score) 平均成绩
from sc
group by sc.sidmysql select - sc.sid,- avg(score) 平均成绩- from sc- group by sc.sid;
-------------------
| sid | 平均成绩 |
-------------------
| 01 | 89.66667 |
| 02 | 70.00000 |
| 03 | 80.00000 |
| 04 | 33.33333 |
| 05 | 81.50000 |
| 06 | 32.50000 |
| 07 | 93.50000 |
-------------------
7 rows in set (0.00 sec)-- 查询平均成绩并去重
select distinct avg(score) avg_score from sc group by sid;mysql select distinct avg(score) avg_score from sc group by sid;
-----------
| avg_score |
-----------
| 89.66667 |
| 70.00000 |
| 80.00000 |
| 33.33333 |
| 81.50000 |
| 32.50000 |
| 93.50000 |
-----------
7 rows in set (0.00 sec)select stu.sid,t.avg_score 平均成绩,(select count(*) from (select distinct avg(score) avg_score from sc group by sid) t2 -- 查询平均成绩并去重where t2.avg_score t.avg_score)1 排名
from student stu,(select avg(score) avg_score,sid from sc group by sid) t
where stu.sid t.sid
group by stu.sid
order by 3;mysql select - stu.sid,- t.avg_score 平均成绩,- (select count(*) - from (select distinct avg(score) avg_score from sc group by sid) t2 -- 查询平均成绩并去重- where t2.avg_score t.avg_score- )1 排名- from student stu,(select avg(score) avg_score,sid from sc group by sid) t- where stu.sid t.sid- group by stu.sid- order by 3;
---------------------------
| sid | 平均成绩 | 排名 |
---------------------------
| 07 | 93.50000 | 1 |
| 01 | 89.66667 | 2 |
| 05 | 81.50000 | 3 |
| 03 | 80.00000 | 4 |
| 02 | 70.00000 | 5 |
| 04 | 33.33333 | 6 |
| 06 | 32.50000 | 7 |
---------------------------
7 rows in set (0.00 sec)select stu.sid,t.avg_score 平均成绩,(select count(*) from (select distinct avg(score) avg_score from sc group by sid) t2 -- 查询平均成绩并去重where t2.avg_score t.avg_score)1 排名
from student stu,(select avg(score) avg_score,sid from sc group by sid) t
where stu.sid t.sid
group by stu.sid;4.24.1、MySql中符号的使用【经典】 符号的作用后接变量名用以定义一个变量该变量的有效期为语句级即再一次执行中始终有效 -- 由于通常情况下被认为是等于比较运算符因此赋值运算符一般使用:
SELECT lt:1, lt:lt1, lt:lt1;mysql SELECT lt:1, lt:lt1, lt:lt1;
--------------------------------
| lt:1 | lt:lt1 | lt:lt1 |
--------------------------------
| 1 | 2 | 3 |
--------------------------------
1 row in set (0.00 sec)selects.sid, -- 学号i:i1 as 不保留空缺排名, -- 直接i的自加k:(case when avgscores.avg_score then k else i end) as 保留空缺排名,avgscore:avg_score as 平均分 -- 表s中的值from (select sid,round(avg(score), 2) as avg_score from scgroup by sidorder by 2 desc) s -- 表s平均成绩的排序和学号,(select avgscore:0, i:0, k:0) b; -- 表b通过变量设置初始值mysql select- s.sid, -- 学号- i:i1 as 不保留空缺排名, -- 直接i的自加- k:(case when avgscores.avg_score then k else i end) as 保留空缺排名,- avgscore:avg_score as 平均分 -- 表s中的值- - from (select sid,round(avg(score), 2) as avg_score from sc- group by sid- order by 2 desc) s -- 表s平均成绩的排序和学号- ,(select avgscore:0, i:0, k:0) b; -- 表b通过变量设置初始值
-----------------------------------------------------------
| sid | 不保留空缺排名 | 保留空缺排名 | 平均分 |
-----------------------------------------------------------
| 07 | 1 | 1 | 93.50 |
| 01 | 2 | 2 | 89.67 |
| 05 | 3 | 3 | 81.50 |
| 03 | 4 | 4 | 80.00 |
| 02 | 5 | 5 | 70.00 |
| 04 | 6 | 6 | 33.33 |
| 06 | 7 | 7 | 32.50 |
-----------------------------------------------------------
7 rows in set (0.00 sec)4.25、查询各科成绩前三名的记录【经典】
先看
select *
from sc s1
inner join sc s2 on s1.cid s2.cid and s1.score s2.score; -- 将分数表sc自连接通过课程id对应起来每个分数表s2的成绩最大然后是select s1.*,count(s2.score)
from sc s1
inner join sc s2 on s1.cid s2.cid and s1.score s2.score
group by s1.sid,s1.cid,s1.score;然后是select s1.*,count(distinct s2.score)
from sc s1
inner join sc s2 on s1.cid s2.cid and s1.score s2.score
group by s1.sid,s1.cid,s1.score;最后是select s1.*,count(distinct s2.score) rank
from sc s1
inner join sc s2 on s1.cid s2.cid and s1.score s2.score
group by s1.sid,s1.cid,s1.score
having count(distinct s2.score) 3
order by s1.cid,rank;mysql select s1.*,count(distinct s2.score) rank- from sc s1- inner join sc s2 on s1.cid s2.cid and s1.score s2.score- group by s1.sid,s1.cid,s1.score- having count(distinct s2.score) 3- order by s1.cid,rank;
-----------------------
| sid | cid | score | rank |
-----------------------
| 03 | 01 | 80.0 | 1 |
| 01 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 2 |
| 02 | 01 | 70.0 | 3 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
-----------------------
11 rows in set (0.00 sec)4.26、查询每门课程被选修的学生数
selectc.cid,c.cname,count(sc.cid)
from course c
inner join sc on c.cid sc.cid
group by c.cid;mysql select- c.cid,- c.cname,- count(sc.cid)- from course c- inner join sc on c.cid sc.cid- group by c.cid;
----------------------------
| cid | cname | count(sc.cid) |
----------------------------
| 01 | 语文 | 6 |
| 02 | 数学 | 6 |
| 03 | 英语 | 6 |
----------------------------
3 rows in set (0.00 sec)4.27、查询出只有两门课程的全部学生的学号和姓名
selectstu.sid 学号,stu.sname 姓名
from student stu
inner join sc on stu.sid sc.sid
group by stu.sid
having count(sc.sid) 2;mysql select- stu.sid 学号,- stu.sname 姓名- from student stu- inner join sc on stu.sid sc.sid- group by stu.sid- having count(sc.sid) 2;
----------------
| 学号 | 姓名 |
----------------
| 05 | 周梅 |
| 06 | 吴兰 |
| 07 | 郑竹 |
----------------
3 rows in set (0.00 sec)4.28、查询男生、女生人数
selectssex 性别,count(ssex) 人数
from student
group by ssex;mysql select- ssex 性别,- count(ssex) 人数- from student- group by ssex;
----------------
| 性别 | 人数 |
----------------
| 女 | 4 |
| 男 | 4 |
----------------
2 rows in set (0.00 sec)selectcount(case when ssex 男 then 1 else 0 end) 男,count(case when ssex 女 then 1 else 0 end) 女
from student;mysql select- count(case when ssex 男 then 1 else 0 end) 男,- count(case when ssex 女 then 1 else 0 end) 女- from student;
----------
| 男 | 女 |
----------
| 8 | 8 |
----------
1 row in set (0.00 sec)select count(ssex女) 女,count(ssex男) 男 from student;mysql select count(ssex女) 女,count(ssex男) 男 from student;
----------
| 女 | 男 |
----------
| 8 | 8 |
----------
1 row in set (0.00 sec)4.29、查询名字中含有风字的学生信息
selectstudent.*
from student
where sname like %风%;mysql select- student.*- from student- where sname like %风%;
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
----------------------------------------
1 row in set (0.00 sec)4.30、查询同名同性学生名单并统计同名人数
INSERT INTO student VALUES(09 , 赵雷 , 2013-05-21 , 男),(10 , 王菊 , 1314-12-25 , 女);selects1.sname,s1.ssex,count(*)
from student s1
inner join student s2 on s1.sid s2.sid and s1.sname s2.sname and s1.ssex s2.ssex
group by s1.sname,s1.ssex;mysql INSERT INTO student VALUES(09 , 赵雷 , 2013-05-21 , 男),(10 , 王菊 , 1314-12-25 , 女);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select- s1.sname,- s1.ssex,- count(*)- from student s1- inner join student s2 on s1.sid s2.sid and s1.sname s2.sname and s1.ssex s2.ssex- group by s1.sname,s1.ssex;
------------------------
| sname | ssex | count(*) |
------------------------
| 王菊 | 女 | 2 |
| 赵雷 | 男 | 2 |
------------------------
2 rows in set (0.00 sec)mysql delete from student where sid in (09,10);
Query OK, 2 rows affected (0.00 sec)delete from student where sid in (09,10);4.31、查询1990年出生的学生名单
selectstudent.*
from student
where year(sage) 1990;mysql select- student.*- from student- where year(sage) 1990;
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
----------------------------------------
5 rows in set (0.00 sec)selectstudent.*
from student
where sage like 1990%;mysql select- student.*- from student- where sage like 1990%;
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
----------------------------------------
5 rows in set, 1 warning (0.00 sec)4.32、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
selectsc.cid 课程编号,avg(score) 平均成绩
from sc
group by cid
order by 2 desc,1 asc;mysql select- sc.cid 课程编号,- avg(score) 平均成绩- from sc- group by cid- order by 2 desc,1 asc;
----------------------------
| 课程编号 | 平均成绩 |
----------------------------
| 02 | 72.66667 |
| 03 | 68.50000 |
| 01 | 64.50000 |
----------------------------
3 rows in set (0.00 sec)4.33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
selectstu.sid 学号,stu.sname 姓名,avg(score) 平均成绩
from sc
join student stu on sc.sid stu.sid
group by sc.sid
having avg(score) 85;mysql select- stu.sid 学号,- stu.sname 姓名,- avg(score) 平均成绩- from sc- join student stu on sc.sid stu.sid- group by sc.sid- having avg(score) 85;
------------------------------
| 学号 | 姓名 | 平均成绩 |
------------------------------
| 01 | 赵雷 | 89.66667 |
| 07 | 郑竹 | 93.50000 |
------------------------------
2 rows in set (0.00 sec)4.34、查询课程名称为数学且分数低于60的学生姓名和分数
selectstu.sname 学生姓名,sc.score 分数
from sc
inner join student stu on sc.sid stu.sid
inner join course c on sc.cid c.cid and c.cname 数学
where sc.score 60;mysql select- stu.sname 学生姓名,- sc.score 分数- from sc- inner join student stu on sc.sid stu.sid- inner join course c on sc.cid c.cid and c.cname 数学- where sc.score 60;
----------------------
| 学生姓名 | 分数 |
----------------------
| 李云 | 30.0 |
----------------------
1 row in set (0.00 sec)4.35、查询所有学生的课程及分数情况
selectstu.sid,stu.sname,sum(case when sc.cid 01 then sc.score else 0 end) 语文,sum(case when sc.cid 02 then sc.score else 0 end) 数学,sum(case when sc.cid 03 then sc.score else 0 end) 英语,sum(sc.score)
from student stu
left join sc on stu.sid sc.sid
group by stu.sid,stu.sname;mysql select- stu.sid,- stu.sname,- sum(case when sc.cid 01 then sc.score else 0 end) 语文,- sum(case when sc.cid 02 then sc.score else 0 end) 数学,- sum(case when sc.cid 03 then sc.score else 0 end) 英语,- sum(sc.score)- from student stu- left join sc on stu.sid sc.sid- group by stu.sid,stu.sname;
----------------------------------------------------
| sid | sname | 语文 | 数学 | 英语 | sum(sc.score) |
----------------------------------------------------
| 01 | 赵雷 | 80.0 | 90.0 | 99.0 | 269.0 |
| 02 | 钱电 | 70.0 | 60.0 | 80.0 | 210.0 |
| 03 | 孙风 | 80.0 | 80.0 | 80.0 | 240.0 |
| 04 | 李云 | 50.0 | 30.0 | 20.0 | 100.0 |
| 05 | 周梅 | 76.0 | 87.0 | 0.0 | 163.0 |
| 06 | 吴兰 | 31.0 | 0.0 | 34.0 | 65.0 |
| 07 | 郑竹 | 0.0 | 89.0 | 98.0 | 187.0 |
| 08 | 王菊 | 0.0 | 0.0 | 0.0 | NULL |
----------------------------------------------------
8 rows in set (0.00 sec)selectstu.sid,stu.sname,sum(case c.cname when 语文 then sc.score else 0 end) 语文,sum(case c.cname when 数学 then sc.score else 0 end) 数学,sum(case c.cname when 英语 then sc.score else 0 end) 英语,sum(sc.score)
from student stu
left join sc on stu.sid sc.sid
left join course c on sc.cid c.cid
group by stu.sid;mysql select- stu.sid,- stu.sname,- sum(case c.cname when 语文 then sc.score else 0 end) 语文,- sum(case c.cname when 数学 then sc.score else 0 end) 数学,- sum(case c.cname when 英语 then sc.score else 0 end) 英语,- sum(sc.score)- from student stu- left join sc on stu.sid sc.sid- left join course c on sc.cid c.cid- group by stu.sid;
----------------------------------------------------
| sid | sname | 语文 | 数学 | 英语 | sum(sc.score) |
----------------------------------------------------
| 01 | 赵雷 | 80.0 | 90.0 | 99.0 | 269.0 |
| 02 | 钱电 | 70.0 | 60.0 | 80.0 | 210.0 |
| 03 | 孙风 | 80.0 | 80.0 | 80.0 | 240.0 |
| 04 | 李云 | 50.0 | 30.0 | 20.0 | 100.0 |
| 05 | 周梅 | 76.0 | 87.0 | 0.0 | 163.0 |
| 06 | 吴兰 | 31.0 | 0.0 | 34.0 | 65.0 |
| 07 | 郑竹 | 0.0 | 89.0 | 98.0 | 187.0 |
| 08 | 王菊 | 0.0 | 0.0 | 0.0 | NULL |
----------------------------------------------------
8 rows in set (0.00 sec)4.36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
selectstu.sname,c.cname,sc.score
from sc
join student stu on sc.sid stu.sid
join course c on sc.cid c.cid
having score 70;mysql select- stu.sname,- c.cname,- sc.score- from sc- join student stu on sc.sid stu.sid- join course c on sc.cid c.cid- having score 70;
-----------------------
| sname | cname | score |
-----------------------
| 赵雷 | 语文 | 80.0 |
| 赵雷 | 数学 | 90.0 |
| 赵雷 | 英语 | 99.0 |
| 钱电 | 英语 | 80.0 |
| 孙风 | 语文 | 80.0 |
| 孙风 | 数学 | 80.0 |
| 孙风 | 英语 | 80.0 |
| 周梅 | 语文 | 76.0 |
| 周梅 | 数学 | 87.0 |
| 郑竹 | 数学 | 89.0 |
| 郑竹 | 英语 | 98.0 |
-----------------------
11 rows in set (0.00 sec)4.37、查询不及格的课程
selectsc.sid,c.cname,sc.score
from sc
join course c on sc.cid c.cid
where sc.score 60;mysql select- sc.sid,- c.cname,- sc.score- from sc- join course c on sc.cid c.cid - where sc.score 60;
--------------------
| sid | cname | score |
--------------------
| 04 | 语文 | 50.0 |
| 04 | 数学 | 30.0 |
| 04 | 英语 | 20.0 |
| 06 | 语文 | 31.0 |
| 06 | 英语 | 34.0 |
--------------------
5 rows in set (0.00 sec)4.38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名
selectstu.sid,stu.sname,sc.score
from sc
join student stu on sc.sid stu.sid
where sc.score 80 and sc.cid 01;mysql select- stu.sid,- stu.sname,- sc.score- from sc- join student stu on sc.sid stu.sid- where sc.score 80 and sc.cid 01;
--------------------
| sid | sname | score |
--------------------
| 01 | 赵雷 | 80.0 |
| 03 | 孙风 | 80.0 |
--------------------
2 rows in set (0.00 sec)4.39、求每门课程的学生人数
selectc.cname 课程名,count(*) 学生人数
from course c
join sc on c.cid sc.cid
group by c.cid;mysql select- c.cname 课程名,- count(*) 学生人数- from course c- join sc on c.cid sc.cid- group by c.cid;
-------------------------
| 课程名 | 学生人数 |
-------------------------
| 语文 | 6 |
| 数学 | 6 |
| 英语 | 6 |
-------------------------
3 rows in set (0.01 sec)4.40、查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩
selectstu.sid,stu.sname,sc.score,c.cname,t.tname
from sc
left join course c on c.cid sc.cid
left join teacher t on c.tid t.tid
left join student stu on sc.sid stu.sid
where t.tname 张三;mysql select- stu.sid,- stu.sname,- sc.score,- c.cname,- t.tname- from sc- left join course c on c.cid sc.cid- left join teacher t on c.tid t.tid- left join student stu on sc.sid stu.sid- where t.tname 张三;
-------------------------------------
| sid | sname | score | cname | tname |
-------------------------------------
| 01 | 赵雷 | 90.0 | 数学 | 张三 |
| 02 | 钱电 | 60.0 | 数学 | 张三 |
| 03 | 孙风 | 80.0 | 数学 | 张三 |
| 04 | 李云 | 30.0 | 数学 | 张三 |
| 05 | 周梅 | 87.0 | 数学 | 张三 |
| 07 | 郑竹 | 89.0 | 数学 | 张三 |
-------------------------------------
6 rows in set (0.00 sec)selectstu.sid,stu.sname,max(sc.score),c.cname,t.tname
from sc
left join course c on c.cid sc.cid
left join teacher t on c.tid t.tid
left join student stu on sc.sid stu.sid
where t.tname 张三;mysql select- stu.sid,- stu.sname,- max(sc.score),- c.cname,- t.tname- from sc- left join course c on c.cid sc.cid- left join teacher t on c.tid t.tid- left join student stu on sc.sid stu.sid- where t.tname 张三;
---------------------------------------------
| sid | sname | max(sc.score) | cname | tname |
---------------------------------------------
| 01 | 赵雷 | 90.0 | 数学 | 张三 |
---------------------------------------------
1 row in set (0.00 sec)4.41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
selectdistinct s1.sid,s1.cid,s1.score
from sc s1
join sc s2 on s1.score s2.score and s1.cid ! s2.cid and s1.sid ! s2.sid;mysql select- distinct s1.sid,- s1.cid,- s1.score- from sc s1- join sc s2 on s1.score s2.score and s1.cid ! s2.cid and s1.sid ! s2.sid;
-----------------
| sid | cid | score |
-----------------
| 02 | 03 | 80.0 |
| 03 | 02 | 80.0 |
| 03 | 03 | 80.0 |
| 01 | 01 | 80.0 |
| 03 | 01 | 80.0 |
-----------------
5 rows in set (0.00 sec)-- 查询成绩相同的分数
select score from sc group by score having count(1)1;selectsc.sid,sc.cid,sc.score
from sc
where sc.score in (select score from sc group by score having count(1)1);mysql select- sc.sid,- sc.cid,- sc.score- from sc- where sc.score in (select score from sc group by score having count(1)1);
-----------------
| sid | cid | score |
-----------------
| 01 | 01 | 80.0 |
| 02 | 03 | 80.0 |
| 03 | 01 | 80.0 |
| 03 | 02 | 80.0 |
| 03 | 03 | 80.0 |
-----------------
5 rows in set (0.00 sec)4.42、查询每门功课成绩最好的前两名【经典】
selects1.*,count(distinct(s2.score)) rank
from sc s1
join sc s2 on s1.cid s2.cid and s1.score s2.score
group by s1.sid,s1.cid,s1.score
having rank 2
order by s1.cid,rank;mysql select- s1.*,- count(distinct(s2.score)) rank- from sc s1- join sc s2 on s1.cid s2.cid and s1.score s2.score- group by s1.sid,s1.cid,s1.score- having rank 2- order by s1.cid,rank;
-----------------------
| sid | cid | score | rank |
-----------------------
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 2 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
-----------------------
7 rows in set (0.00 sec)selectstu.sname,c.cname,t.tname,s1.score,count(distinct(s2.score)) rank
from sc s1
join sc s2 on s1.cid s2.cid and s1.score s2.score
join student stu on s1.sid stu.sid
join course c on s1.cid c.cid
join teacher t on c.tid t.tid
group by s1.sid,s1.cid,s1.score
having rank 2
order by s1.cid,rank;mysql select- stu.sname,- c.cname,- t.tname,- s1.score,- count(distinct(s2.score)) rank- from sc s1- join sc s2 on s1.cid s2.cid and s1.score s2.score- join student stu on s1.sid stu.sid- join course c on s1.cid c.cid- join teacher t on c.tid t.tid- group by s1.sid,s1.cid,s1.score- having rank 2- order by s1.cid,rank;
-------------------------------------
| sname | cname | tname | score | rank |
-------------------------------------
| 孙风 | 语文 | 李四 | 80.0 | 1 |
| 赵雷 | 语文 | 李四 | 80.0 | 1 |
| 周梅 | 语文 | 李四 | 76.0 | 2 |
| 赵雷 | 数学 | 张三 | 90.0 | 1 |
| 郑竹 | 数学 | 张三 | 89.0 | 2 |
| 赵雷 | 英语 | 王五 | 99.0 | 1 |
| 郑竹 | 英语 | 王五 | 98.0 | 2 |
-------------------------------------
7 rows in set (0.00 sec)selects1.*
from sc s1
where (select count(1) -- count(1)类似count(*):统计表s2中分数大的数量from sc s2where s2.cids1.cid -- 课程相同and s2.score s1.score) 2 -- 前2名
order by s1.cid;此方法有缺陷重复分数未统计 mysql select- s1.*- from sc s1- where (select count(1) -- count(1)类似count(*):统计表s2中分数大的数量- from sc s2- where s2.cids1.cid -- 课程相同- and s2.score s1.score) 2 -- 前2名- order by s1.cid;
-----------------
| sid | cid | score |
-----------------
| 01 | 01 | 80.0 |
| 03 | 01 | 80.0 |
| 01 | 02 | 90.0 |
| 07 | 02 | 89.0 |
| 01 | 03 | 99.0 |
| 07 | 03 | 98.0 |
-----------------
6 rows in set (0.00 sec)selects1.*
from sc s1
where (select count(distinct(s2.score)) -- 根据分数去重并统计表s2中分数大的数量from sc s2where s2.cids1.cid -- 课程相同and s2.score s1.score) 2 -- 前2名
order by s1.cid;mysql select- s1.*- from sc s1- where (select count(distinct(s2.score)) -- 根据分数去重并统计表s2中分数大的数量- from sc s2- where s2.cids1.cid -- 课程相同- and s2.score s1.score) 2 -- 前2名- order by s1.cid;
-----------------
| sid | cid | score |
-----------------
| 01 | 01 | 80.0 |
| 03 | 01 | 80.0 |
| 05 | 01 | 76.0 |
| 01 | 02 | 90.0 |
| 07 | 02 | 89.0 |
| 01 | 03 | 99.0 |
| 07 | 03 | 98.0 |
-----------------
7 rows in set (0.00 sec)4.43、统计每门课程的学生选修人数超过5人的课程才统计要求输出课程号和选修人数查询结果按人数降序排列。
selectsc.cid 课程号,c.cname 课程名,count(*) 选修人数
from sc
join course c on sc.cid c.cid
group by sc.cid
having(count(*)) 5;mysql select- sc.cid 课程号,- c.cname 课程名,- count(*) 选修人数- from sc- join course c on sc.cid c.cid- group by sc.cid- having(count(*)) 5;
------------------------------------
| 课程号 | 课程名 | 选修人数 |
------------------------------------
| 01 | 语文 | 6 |
| 02 | 数学 | 6 |
| 03 | 英语 | 6 |
------------------------------------
3 rows in set (0.00 sec)4.44、检索至少选修两门课程的学生学号
selectsc.sid,stu.sname,count(*)
from sc
join student stu on sc.sid stu.sid
group by sc.sid
having count(*) 2;mysql select- sc.sid,- stu.sname,- count(*)- from sc- join student stu on sc.sid stu.sid- group by sc.sid- having count(*) 2;
-----------------------
| sid | sname | count(*) |
-----------------------
| 01 | 赵雷 | 3 |
| 02 | 钱电 | 3 |
| 03 | 孙风 | 3 |
| 04 | 李云 | 3 |
| 05 | 周梅 | 2 |
| 06 | 吴兰 | 2 |
| 07 | 郑竹 | 2 |
-----------------------
7 rows in set (0.00 sec)4.45、查询选修了全部课程的学生信息
selectsc.sid,stu.sname,count(*)
from sc
join student stu on sc.sid stu.sid
group by sc.sid
having count(*) (select count(*) from course);mysql select- sc.sid,- stu.sname,- count(*)- from sc- join student stu on sc.sid stu.sid- group by sc.sid- having count(*) (select count(*) from course);
-----------------------
| sid | sname | count(*) |
-----------------------
| 01 | 赵雷 | 3 |
| 02 | 钱电 | 3 |
| 03 | 孙风 | 3 |
| 04 | 李云 | 3 |
-----------------------
4 rows in set (0.00 sec)4.46、查询各学生的年龄
-- 通过date_format函数能够指定返回的数据
select year(now());
select date_format(now(), %Y);
select date_format(now(), %Y-%m-%d);mysql select year(now());
-------------
| year(now()) |
-------------
| 2024 |
-------------
1 row in set (0.00 sec)mysql select date_format(now(), %Y);
--------------------------
| date_format(now(), %Y) |
--------------------------
| 2024 |
--------------------------
1 row in set (0.00 sec)mysql select date_format(now(), %Y-%m-%d);
--------------------------------
| date_format(now(), %Y-%m-%d) |
--------------------------------
| 2024-02-08 |
--------------------------------
1 row in set (0.00 sec)selectstu.sname 姓名,TIMESTAMPDIFF(year,stu.sage,now()) 年龄 -- TIMESTAMPDIFF函数返回年差
from student stu;mysql select- stu.sname 姓名,- TIMESTAMPDIFF(year,stu.sage,now()) 年龄- from student stu;
----------------
| 姓名 | 年龄 |
----------------
| 赵雷 | 34 |
| 钱电 | 33 |
| 孙风 | 33 |
| 李云 | 33 |
| 周梅 | 32 |
| 吴兰 | 31 |
| 郑竹 | 34 |
| 王菊 | 34 |
----------------
8 rows in set (0.00 sec)selectsname,sage,date_format(now(), %Y) - date_format(sage, %Y) - (case when date_format(now(), %m%d) date_format(sage, %m%d) then 0 else 1 end) as age -- 当前日期大说明已经过生了年龄正常反之说明今年还没有到年龄-1
from student;mysql select- sname- ,sage- ,date_format(now(), %Y) - date_format(sage, %Y) - (case when date_format(now(), %m%d) date_format(sage, %m%d) then 0 else 1 end) as age -- 当前日 期大说明已经过生了年龄正常反之说明今年还没有到年龄-1- from student;
-----------------------------------
| sname | sage | age |
-----------------------------------
| 赵雷 | 1990-01-01 00:00:00 | 34 |
| 钱电 | 1990-12-21 00:00:00 | 33 |
| 孙风 | 1990-05-20 00:00:00 | 33 |
| 李云 | 1990-08-06 00:00:00 | 33 |
| 周梅 | 1991-12-01 00:00:00 | 32 |
| 吴兰 | 1992-03-01 00:00:00 | 31 |
| 郑竹 | 1989-07-01 00:00:00 | 34 |
| 王菊 | 1990-01-20 00:00:00 | 34 |
-----------------------------------
8 rows in set (0.00 sec)4.47、查询本周过生日的学生
INSERT INTO student VALUES(09 , 李白 , date_format(now(),%Y-%m-%d), 男),
(10 , 玛利亚 , date_format(now(),%Y-%m-%d), 女);select * from student;mysql INSERT INTO student VALUES(09 , 李白 , date_format(now(),%Y-%m-%d), 男),- (10 , 玛利亚 , date_format(now(),%Y-%m-%d), 女);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql
mysql select * from student;
-------------------------------------------
| sid | sname | sage | ssex |
-------------------------------------------
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
| 09 | 李白 | 2024-02-08 00:00:00 | 男 |
| 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 |
-------------------------------------------
10 rows in set (0.01 sec)selectstu.*
from student stu
where week(date_format(now(),%Y%m%d)) week(sage);mysql select- stu.*- from student stu- where week(date_format(now(),%Y%m%d)) week(sage);
-------------------------------------------
| sid | sname | sage | ssex |
-------------------------------------------
| 09 | 李白 | 2024-02-08 00:00:00 | 男 |
| 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 |
-------------------------------------------
2 rows in set (0.00 sec)select * from student where WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)0;mysql select * from student where WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)0;
-------------------------------------------
| sid | sname | sage | ssex |
-------------------------------------------
| 09 | 李白 | 2024-02-08 00:00:00 | 男 |
| 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 |
-------------------------------------------
2 rows in set (0.00 sec)4.48、查询下周过生日的学生
selectstu.*
from student stu
where week(date_format(now(),%Y%m%d))1 week(sage);select * from student where WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)-1;4.49、查询本月过生日的学生
selectstu.*
from student stu
where month(date_format(now(),%Y%m%d)) month(sage);mysql select- stu.*- from student stu- where month(date_format(now(),%Y%m%d)) month(sage);
-------------------------------------------
| sid | sname | sage | ssex |
-------------------------------------------
| 09 | 李白 | 2024-02-08 00:00:00 | 男 |
| 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 |
-------------------------------------------
2 rows in set (0.00 sec)4.50、查询下月过生日的学生
selectstu.*
from student stu
where month(date_format(now(),%Y%m%d))1 month(sage);mysql select- stu.*- from student stu- where month(date_format(now(),%Y%m%d))1 month(sage);
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
----------------------------------------
1 row in set (0.00 sec)-- 假设现在是12月份那么下个月就是明年的1月份,解决当前月份与12的余数
selectstu.*
from student stu
where mod(month(now()),12)1 month(sage);mysql select- stu.*- from student stu- where mod(month(now()),12)1 month(sage);
----------------------------------------
| sid | sname | sage | ssex |
----------------------------------------
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
----------------------------------------
1 row in set (0.00 sec)end
