课后练习：
1、查询”李”姓老师的数量
2、查询1990年出生的学生名单
3、查询男生、女生人数
4、查询学生的总成绩、平均成绩并进行排名
5、查询每门课程被选修的学生数
6、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息
7、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
8、检索至少选修两门课程的学生学号
9、统计各科成绩各分数段[100-85],[85-70],[70-60],[0-60]的人数
-- 1.学生表create table S(s_id varchar(2) comment '学生编号' primary key,s_name varchar(10) comment '学生姓名',s_birth date comment '出生年月',s_gend enum('男','女') comment '学生性别');insert into S 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','女');-- 2.课程表create table C(c_id varchar(2) comment '课程编号' primary key,c_name varchar(10) comment '课程名称',t_id varchar(2) comment '教师编号');insert into C values('01','语文','02'),('02','数学','01'),('03','英语','03');-- 3.教师表create table T(t_id varchar(2) comment '教师编号' primary key,t_name varchar(10) comment '教师姓名');insert into T values('01','张三'),('02','李四'),('03','王五');-- 4.成绩表create table Score(s_id varchar(2) comment '学生编号',c_id varchar(2) comment '课程编号',s_score float comment '分数',primary key(s_id,c_id));insert into Score values('01','01',80),('03','01',80),('05','01',76),('01','02',90),('03','02',80),('05','02',87),('01','03',99),('03','03',80),('06','01',31),('02','01',70),('04','01',50),('06','03',34),('02','02',60),('04','02',30),('07','02',89),('02','03',80),('04','03',20),('07','03',98);-- 1、查询"李"姓老师的数量select count(*) from T where t_name like '李%';+----------+| count(*) |+----------+| 1 |+----------+-- 2、查询1990年出生的学生名单select s_id,s_name,s_birth from S where s_birth like '1990%';+------+--------+------------+| s_id | s_name | s_birth |+------+--------+------------+| 01 | 赵雷 | 1990-01-01 || 02 | 钱电 | 1990-12-21 || 03 | 孙风 | 1990-05-20 || 04 | 李云 | 1990-08-06 || 08 | 王菊 | 1990-01-20 |+------+--------+------------+-- 3、查询男生、女生人数select s_gend,count(*) from S group by s_gend;+--------+----------+| s_gend | count(*) |+--------+----------+| 男 | 4 || 女 | 4 |+--------+----------+-- 4、查询学生的总成绩、平均成绩并进行排名select S.s_id,S.s_name,sum(s_score),round(avg(s_score),2)from S,Score where Score.s_id=S.s_idgroup by Score.s_idorder by sum(s_score) desc;+------+--------+--------------+-----------------------+| s_id | s_name | sum(s_score) | round(avg(s_score),2) |+------+--------+--------------+-----------------------+| 01 | 赵雷 | 269 | 89.67 || 03 | 孙风 | 240 | 80 || 02 | 钱电 | 210 | 70 || 07 | 郑竹 | 187 | 93.5 || 05 | 周梅 | 163 | 81.5 || 04 | 李云 | 100 | 33.33 || 06 | 吴兰 | 65 | 32.5 |+------+--------+--------------+-----------------------+-- 5、查询每门课程被选修的学生数select C.c_id,C.c_name,count(*)from C,Scorewhere Score.c_id=C.c_idgroup by Score.c_id;+------+--------+----------+| c_id | c_name | count(*) |+------+--------+----------+| 01 | 语文 | 6 || 02 | 数学 | 6 || 03 | 英语 | 6 |+------+--------+----------+-- 6、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息-- 思路,先筛选01和02，然后分组后count=2select s.*from S inner join Score on Score.s_id=S.s_idwhere c_id='01' or c_id='02'group by Score.s_idhaving count(*) = 2;+------+--------+------------+--------+| s_id | s_name | s_birth | s_gend |+------+--------+------------+--------+| 01 | 赵雷 | 1990-01-01 | 男 || 02 | 钱电 | 1990-12-21 | 男 || 03 | 孙风 | 1990-05-20 | 男 || 04 | 李云 | 1990-08-06 | 男 || 05 | 周梅 | 1991-12-01 | 女 |+------+--------+------------+--------+-- 7、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩select S.s_id,S.s_name,round(avg(Score.s_score),2)from S inner join Score on Score.s_id=S.s_idgroup by Score.s_idhaving round(avg(Score.s_score),2) >= 85;+------+--------+-----------------------------+| s_id | s_name | round(avg(Score.s_score),2) |+------+--------+-----------------------------+| 01 | 赵雷 | 89.67 || 07 | 郑竹 | 93.5 |+------+--------+-----------------------------+-- 8、检索至少选修两门课程的学生学号select s_idfrom Scoregroup by s_idhaving count(c_id) >= 2;+------+| s_id |+------+| 01 || 02 || 03 || 04 || 05 || 06 || 07 |+------+-- 9、统计各科成绩各分数段[100-85),[85-70),[70-60),[0-60]的人数-- 方式一：case whenselect C.c_name,case when s_score <= 100 and s_score > 85 then 1 else 0 end as '[100-85)',case when s_score <= 85 and s_score > 70 then 1 else 0 end as '[85-70)',case when s_score <= 70 and s_score > 60 then 1 else 0 end as '[70-60)',case when s_score <= 60 and s_score >= 0 then 1 else 0 end as '[0-60]'from C inner join Score on Score.c_id=C.c_id--方式二:if()select C.c_name,if(s_score <= 100 and s_score > 85,1,0) as '[100-85)',if(s_score <= 85 and s_score > 70,1,0)as '[85-70)',if(s_score <= 70 and s_score > 60,1,0)as '[70-60)',if(s_score <= 60 and s_score >= 0,1,0)as '[0-60]'from C inner join Score on Score.c_id=C.c_id+--------+----------+---------+---------+--------+| c_name | [100-85) | [85-70) | [70-60) | [0-60] |+--------+----------+---------+---------+--------+| 语文 | 0 | 1 | 0 | 0 || 数学 | 1 | 0 | 0 | 0 || 英语 | 1 | 0 | 0 | 0 || 语文 | 0 | 0 | 1 | 0 || 数学 | 0 | 0 | 0 | 1 || 英语 | 0 | 1 | 0 | 0 || 语文 | 0 | 1 | 0 | 0 || 数学 | 0 | 1 | 0 | 0 || 英语 | 0 | 1 | 0 | 0 || 语文 | 0 | 0 | 0 | 1 || 数学 | 0 | 0 | 0 | 1 || 英语 | 0 | 0 | 0 | 1 || 语文 | 0 | 1 | 0 | 0 || 数学 | 1 | 0 | 0 | 0 || 语文 | 0 | 0 | 0 | 1 || 英语 | 0 | 0 | 0 | 1 || 数学 | 1 | 0 | 0 | 0 || 英语 | 1 | 0 | 0 | 0 |+--------+----------+---------+---------+--------+-- 以课程分组，通过聚合函数聚合一下,count和sum都可以select C.c_name,sum(case when s_score <= 100 and s_score > 85 then 1 else 0 end) as '[100-85)',sum(case when s_score <= 85 and s_score > 70 then 1 else 0 end) as '[85-70)',sum(case when s_score <= 70 and s_score > 60 then 1 else 0 end) as '[70-60)',sum(case when s_score <= 60 and s_score >= 0 then 1 else 0 end) as '[0-60]'from C inner join Score on Score.c_id=C.c_idgroup by Score.c_id;+--------+----------+---------+---------+--------+| c_name | [100-85) | [85-70) | [70-60) | [0-60] |+--------+----------+---------+---------+--------+| 语文 | 0 | 3 | 1 | 2 || 数学 | 3 | 1 | 0 | 2 || 英语 | 2 | 2 | 0 | 2 |+--------+----------+---------+---------+--------+
若有收获，就点个赞吧
0 人点赞
