mysql 根据性别统计各部门人数_MySQL练习——总结篇

于 2021-01-23 05:39:42 发布
阅读量6.8k 收藏 18
点赞数 5
文章标签： mysql 根据性别统计各部门人数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_28958733/article/details/113088655
版权
这篇博客主要介绍了MySQL的数据库和数据表创建，包括简单查询、汇总分析、复杂查询和多表查询的实践。通过案例解析了group by和case在不同场景下的应用，并探讨了子查询、联结操作及窗口函数的使用，是MySQL学习者的实用教程。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

创建数据库和数据表

新建查询中，输入以下语句：

/*创建数据库，并设定字符集和排序规则*/
CREATE DATABASE school CHARACTER SET utf8 COLLATE utf8_unicode_ci;
/*创建数据表，包括结构和数据*/
USE school;
/*创建数据表student，主键设置 ，非空值设定，字符串、时间类型设置*/
CREATE TABLE student (学号 VARCHAR(4) PRIMARY KEY,姓名 VARCHAR(10) NOT NULL,出生日期 DATE NOT NULL,性别 VARCHAR(2) NOT NULL) ;
INSERT INTO student VALUES ('0001','猴子','1989-01-01','男');
INSERT INTO student VALUES ('0002','猴子','1980-12-21','女');
INSERT INTO student VALUES ('0003','马云','1991-12-21','男');
INSERT INTO student VALUES ('0004','王思聪','1990-05-20','男');
/*创建数据表score，主键设置 ，联合主键设置，整型类型设置*/
CREATE TABLE score(学号 VARCHAR(4) ,课程号 VARCHAR(4) NOT NULL,成绩 INT NOT NULL,PRIMARY KEY(学号,课程号)) ;
INSERT INTO score VALUES ('0001','0001','80');
INSERT INTO score VALUES ('0001','0002','90');
INSERT INTO score VALUES ('0001','0003','99');
INSERT INTO score VALUES ('0002','0002','60');
INSERT INTO score VALUES ('0002','0003','80');
INSERT INTO score VALUES ('0003','0001','80');
INSERT INTO score VALUES ('0003','0002','80');
INSERT INTO score VALUES ('0003','0003','80');
/*创建数据表course*/
CREATE TABLE course(课程号 VARCHAR(4) PRIMARY KEY,课程名称 VARCHAR(20) NOT NULL,教师号 VARCHAR(4)NOT NULL) ;
INSERT INTO course VALUES ('0001','语文','0002');
INSERT INTO course VALUES ('0002','数学','0001');
INSERT INTO course VALUES ('0003','英文','0003');
/*创建数据表teacher*/
CREATE TABLE teacher(教师号 VARCHAR(4) PRIMARY KEY,教师姓名 VARCHAR(20)) ;
INSERT INTO teacher VALUES ('0001','孟扎扎');
INSERT INTO teacher VALUES ('0002','马化腾');
INSERT INTO teacher VALUES ('0003',Null);
INSERT INTO teacher VALUES ('0004',' ');

由此得到school数据库，其中包含：

student（学号，姓名，出生日期，性别）

score（学号，课程号，成绩）

course（课程号，课程名称，教师号）

teacher（教师号，教师姓名）

075474a711fdab5f4ea9552614be843c.png

传送门：

寓言：MySQL练习——1​zhuanlan.zhihu.com
zhihu-card-default.svg

简单查询

/*模糊查找
1.查询姓“猴”的学生名单
2.查询姓名中最后一个字是“猴”的学生名单
3.查询姓名中带“猴”的学生名单
4.查询姓“孟”老师的个数*/

select * from student where 姓名 like '猴%';
select * from student where 姓名 like '%猴';
select * from student where 姓名 like '%猴%';
select 教师姓名,count(教师号) as 姓“孟”人数 from teacher where 教师姓名 like '孟%';

19a2c2dd865950a01821e5b00e902b56.png

1bb0f990d2028fcf68ea7581ec3d09f3.png

bcef8aaf9ec451ba0abd031a9bd2ac48.png

b5befa6bc5cef0fec12de7349cbfdf91.png

传送门：

寓言：MySQL练习——2​zhuanlan.zhihu.com
zhihu-card-default.svg

汇总分析

/*
汇总分析
1.查询课程编号为“0002”的总成绩
2.查询选了课程的学生人数
分组
3.查询各科成绩最高和最低的分
4.查询每门课程被选修的学生数
5.查询男生、女生人数
分组结果的条件
6.查询平均成绩大于60分学生的学号和平均成绩
7.查询至少选修两门课程的学生学号
8.查询同名同姓学生名单并统计同名人数
9.查询不及格的课程及按课程号从大到小排列
10.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
11.检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列
12.统计每门课程的学生选修人数(超过2人的课程才统计)
   要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
13.查询两门以上不及格课程的同学的学号及其平均成绩  ！！！同时存在where和having，注意区别！！！
*/
-- 1.
select 课程号,sum(成绩) as 总成绩 from score where 课程号='0002';
-- 2.
select COUNT(DISTINCT 学号) as 选课人数 from score;
-- 3.
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分 from score group by 课程号;
-- 4.
select 课程号,count(学号) as 选修人数 from score group by 课程号;
-- 5.
select sum(case when 性别='男' then 1 else 0 end) as 男生人数,sum(case when 性别='女' then 1 else 0 end) as 女生人数 from student;
-- 6.
select 学号,avg(成绩) as 平均成绩 from score group by 学号 having avg(成绩)>60; 
-- 7.
select 学号, count(课程号) as 选修课程数 from score group by 学号 having count(课程号)>=2;
-- 8.
select 姓名,count(姓名) as 同名人数 from student group by 姓名 having count(姓名)>1;
-- 9.
select 课程号 from score where 成绩<60 order by 课程号 desc;
-- 10.
select 课程号,avg(成绩) as 平均成绩 from score group by 课程号 order by 平均成绩,课程号 desc;
-- 11.
select 学号,成绩 from score where 课程号='0004' and 成绩<60 order by 成绩 desc;
-- 12.
select 课程号,count(学号) as 选修人数 from score group by 课程号 having count(学号)>2 order by 选修人数 desc,课程号;
-- 13.
select 学号,avg(成绩) as 平均成绩 from score where 成绩<60 group by 学号 having count(成绩)>=2;
-- 14.（5题的第二种解法）
select 性别,count(学号) as 人数 from student group by 性别;

dbc3e8396cd357cf65d74706ff9558a2.png

7dd9302bb7252fdfe89a790817baba61.png

621ab403c0fc7ee678e1b3f56364861c.png

e4bcf171c5c239a7fd08751ac037d78d.png

f36c30cb4a3cc54c5fb502e7cf9b8ad1.png

8a38ddec3e3238b1fa324ef9fa822f20.png

b4fba780149192308013666f90734971.png

a975d73bfb1e5baa7852c99e6a3de9f2.png

228a096b9d729e4c6d7997a2f9796315.png

380c2cdb64691c7da077d4c92cc20c79.png

8f5e95107485bf46bfb527ae8aa433d8.png

6295ef0348d71f8ce32f74502a21a214.png

7d3ec6d9d2909ebd39c9abe5fe925b25.png

9af786388f07fc04ddf92cb898cbe31c.png

由题5的两种解法，可以看到：当需求的情况为原数据时，用group by较简单；若需求的情况是原数据的加工（如分段的数据），则只能由case实现。group by（n行）与case（1行），显示方式略有不同。

传送门：

寓言：MySQL练习——3​zhuanlan.zhihu.com
zhihu-card-default.svg

复杂查询

/*
子查询
1.查询所有课程成绩小于60分学生的学号、姓名
子查询+in
2.查询没有学全所有课的学生的学号、姓名|
3.查询出只选修了两门课程的全部学生的学号和姓名
日期函数
4.1990年出生的学生名单
topN问题  ！！！隐藏分组，重点掌握！！！
5.按课程号分组取成绩最大值所在行的数据（分组取每组最大值）
6.按课程号分组取成绩最小值所在行的数据（分组取每组最小值）
7.查询各科成绩前两名的记录（每组最大的N条记录）  ！！！表的加法、自定义变量！！！
*/
-- 1.
select A.学号,A.姓名 from student as A left join score as B on A.学号=B.学号 group by B.学号 having max(B.成绩<60);
-- 2.
select 学号,姓名 from student where 学号 in(select 学号 from score group by 学号 having count(课程号)<(select count(课程号) from course));
-- 3.
select 学号,姓名 from student where 学号 in(select 学号 from score group by 学号 having count(课程号)=2);
-- 4.
select 学号,姓名 from student where year(出生日期)='1990';
-- 5.
select * from score as A where 成绩=(select max(成绩) from score as B where A.课程号=B.课程号);
-- 6.
select * from score as A where 成绩=(select min(成绩) from score as B where A.课程号=B.课程号); 
-- 7.
(select * from score where 课程号='0001' order by 成绩 desc limit 2)
union all
(select * from score where 课程号='0002' order by 成绩 desc limit 2)
union all
(select * from score where 课程号='0003' order by 成绩 desc limit 2);
-- 8.(1题的第二种解法)
select 学号,姓名 from student where 学号 in (select 学号 from score group by 学号 having max(成绩<60));

721ac7521658616242b98b31c059fd8c.png

4251cb583833ca23898e6cc6fff0e476.png

c29e9889672af4dcbc14c292589414fa.png

34e804aab108053f22392b71b052d5c3.png

d2153aca8ce40c195c1a96d2c8d52ded.png

aa5a9544ae7c152f48a16345bab1e4e4.png

84bfd1798557788d6da74ab8d266d654.png

fc34e2a184a6240d5d2c3d768cabb89a.png

注意：

第七题另几种写法，存在问题：

问题一：

select * from score as A where 成绩 in (select 成绩 from score as B where A.课程号=B.课程号 order by 成绩 desc limit 2);

[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

问题二：

select * from score as A where 2>(select count(*) from score as B where (A.课程号=B.课程号 and A.成绩<B.成绩)) order by 课程号;

思路：遍历A表，子查询指A表与B表在同课程号下对比：若A表成绩最高，则count为0；若第二高，则为1。所以count（*）小于2满足题意。

问题在于，若前两名存在成绩相同的情况，则都会显示出来，某学科会存在不只两个记录。

传送门：

寓言：MySQL练习——4​zhuanlan.zhihu.com
zhihu-card-default.svg

多表查询

/*
1.查询所有学生的学号、姓名、选课数、总成绩
2.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
3.查询学生的选课情况：学号，姓名，课程号，课程名称(双联)
4.查询出每门课程的及格人数和不及格人数
5.使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
6.查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
*/
-- 1.
select A.学号,A.姓名,count(B.课程号) as 选课数,sum(B.成绩) as 总成绩 
from student as A left join score as B on A.学号=B.学号 group by B.学号;
-- 2.
select A.学号,A.姓名,avg(B.成绩) as 平均成绩 
from student as A left join score as B on A.学号=B.学号
group by B.学号 having avg(B.成绩)>85;
-- 3.
select A.学号,A.姓名,B.课程号,C.课程名称 
from student as A left join score as B on A.学号=B.学号 inner join course as C on B.课程号=C.课程号;
-- 4.
select 课程号,
sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from score group by 课程号;
-- 5.
select A.课程号,A.课程名称,
sum(case when B.成绩 between 85 and 100 then 1 else 0 end) as [100-85],
sum(case when B.成绩<85 and B.成绩>=70 then 1 else 0 end) as [85-70],
sum(case when B.成绩<70 and B.成绩>=60 then 1 else 0 end) as [70-60],
sum(case when B.成绩<60 then 1 else 0 end) as [<60]
from course as A left join score as B on A.课程号=B.课程号 group by A.课程号,A.课程名称;
-- 6.
select 学号,姓名 from student as A left join score as B on A.学号=B.学号 
where 课程号='0003' and 成绩>80;

547fd886551f45a2e42a5e09f2d4baa0.png

0df5906dc1bcecade54d5780ec9b4840.png

bb2cfcabfe7caefe78d5f813caee0eb9.png

1a09c1ff3fc366a21269b9bd2e7515a8.png

e938f1bd0f3d059b89fbf8870ef3e16a.png

15fd92b0d42b2987494f5deaf9530992.png

使用sql实现将该表行转列为下面的表结构（行列互换，使用常量列，max/case when/group by）

3c8d0abd1fb808b2e9c486d6aea026fb.png

2a45e8b2f63a71fe1d8f09dfdcad36fd.png 
select 学号,
(case when 课程号='0001' then 成绩 else 0 end) as '课程号0001',
(case when 课程号='0002' then 成绩 else 0 end) as '课程号0002',
(case when 课程号='0003' then 成绩 else 0 end) as '课程号0003' 
from score;

select 学号,
max(case when 课程号='0001' then 成绩 else 0 end) as '课程号0001',
max(case when 课程号='0002' then 成绩 else 0 end) as '课程号0002',
max(case when 课程号='0003' then 成绩 else 0 end) as '课程号0003' 
from score group by 学号;

1bc9570b912fbdf688466d97687a2c1c.png

ad463d6a238463b1ed53e90c83ba2e07.png

传送门：

寓言：MySQL练习——5​zhuanlan.zhihu.com
zhihu-card-default.svg

总结：

  • 芝士网

891376faed633171372d7e9140fa21fd.png
  • 运行顺序

② ③ ④ ⑤ → ① → (窗口函数)→ ⑥ ⑦ （各部分内子查询优先）

①select 查询结果

②from 从哪张表中查找数据（联结）

③where 查询条件（运算符、模糊查询）

④group by 分组（每个）

⑤having 对分组结果指定条件

⑥order by 对查询结果排序

⑦limit 从查询结果中取出指定行

条件判断的先后顺序：（） not and or

  • 区分子查询和视图

0fc9a2875c2d2281c62a8ec99d817fd7.png

在子查询中：

普通子查询中的量无法显示出来，因为结果仅存在于运行子查询时，而关联子查询则可以显示

标量子查询返回一个数值，而其他子查询返回一个集合，写语句时需要注意

  • 各种联结关系

bc7c8dc522e4f0cbe2872b086c179a40.png

窗口函数学习：

（官方版）

12.21.2 Window Function Concepts and Syntax​dev.mysql.com

（总结版）

万隼舞：Hive 窗口函数​zhuanlan.zhihu.com
zhihu-card-default.svg

时间相关函数学习：

https://blog.youkuaiyun.com/en_joker/article/details/83145098​blog.youkuaiyun.com
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值