MySQL练习题15道

目录

• 一、表结构要求
• 二、题目( 内连接 INNER JOIN 、左连接 LEFT JOIN)
• • 01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
  • 02）查询同时存在" 01 "课程和" 02 "课程的情况
  • 03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
  • 04）查询不存在" 01 "课程但存在" 02 "课程的情况
  • 05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
  • 06）查询在t_score表存在成绩的学生信息
  • 07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
  • 08）查询「李」姓老师的数量
  • 09）查询学过「张三」老师授课的同学的信息
  • 10）查询没有学全所有课程的同学的信息
  • 11）查询没学过"张三"老师教授的任一门课程的学生
  • 12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
  • 13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
  • 14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
  • 15）查询各科成绩最高分、最低分和平均分

一、表结构要求

-- 1.学生表-t_student
-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别
CREATE TABLE t_student(
	sid VARCHAr(20),
	sname VARCHAr(20),
	sage date,
	ssex VARCHAr(20)
)

-- 2.教师表-t_teacher
-- tid 教师编号,tname 教师名称
create table t_teacher(
	tid VARCHAr(20),
	tname VARCHAr(20)
)

-- 3.课程表-t_course
-- cid 课程编号,cname 课程名称,tid 教师名称
create table t_course(
	cid VARCHAr(20),
	cname VARCHAr(20),
	tid VARCHAr(20)
)

-- 4.成绩表-t_score
-- sid 学生编号,cid 课程编号,score 成绩
CREATE table t_score(
	sid VARCHAr(20),
	cid VARCHAr(20),
	score INT
)

-- 学生表
insert into t_student values('01' , '赵雷' , '1990-01-01' , '男');
insert into t_student values('02' , '钱电' , '1990-12-21' , '男');
insert into t_student values('03' , '孙风' , '1990-12-20' , '男');
insert into t_student values('04' , '李云' , '1990-12-06' , '男');
insert into t_student values('05' , '周梅' , '1991-12-01' , '女');
insert into t_student values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_student values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_student values('09' , '张三' , '2017-12-20' , '女');
insert into t_student values('10' , '李四' , '2017-12-25' , '女');
insert into t_student values('11' , '李四' , '2012-06-06' , '女');
insert into t_student values('12' , '赵六' , '2013-06-13' , '女');
insert into t_student values('13' , '孙七' , '2014-06-01' , '女');

-- 教师表
insert into t_teacher values('01' , '张三');
insert into t_teacher values('02' , '李四');
insert into t_teacher values('03' , '王五');

-- 课程表
insert into t_course values('01' , '语文' , '02');
insert into t_course values('02' , '数学' , '01');
insert into t_course values('03' , '英语' , '03');

-- 成绩表
insert into t_score values('01' , '01' , 80);
insert into t_score values('01' , '02' , 90);
insert into t_score values('01' , '03' , 99);
insert into t_score values('02' , '01' , 70);
insert into t_score values('02' , '02' , 60);
insert into t_score values('02' , '03' , 80);
insert into t_score values('03' , '01' , 80);
insert into t_score values('03' , '02' , 80);
insert into t_score values('03' , '03' , 80);
insert into t_score values('04' , '01' , 50);
insert into t_score values('04' , '02' , 30);
insert into t_score values('04' , '03' , 20);
insert into t_score values('05' , '01' , 76);
insert into t_score values('05' , '02' , 87);
insert into t_score values('06' , '01' , 31);
insert into t_score values('06' , '03' , 34);
insert into t_score values('07' , '02' , 89);
insert into t_score values('07' , '03' , 98);

二、题目( 内连接 INNER JOIN 、左连接 LEFT JOIN)

1.inner join，内连接,显示两个表中有联系的所有数据;
2.left join，左链接,以左表为参照,显示所有数据,右表中没有则以null显示
语法：
select 查询列表
from 表1 别名 【连接类型】
inner join(left join) 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

01）查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

步骤：通过将‘01’课程的b表和‘02’课程的c表的sid进行联系

SELECt a.*,b.score 01score,c.score 02score
FROM t_student as a
INNER JOIN t_score as b
ON a.sid = b.sid
INNER JOIN t_score as c
ON a.sid = c.sid and b.cid = '01' and c.cid = '02'
where b.score > c.score;

结果：

02）查询同时存在" 01 “课程和” 02 "课程的情况

同时选了‘01’课程和‘02’课程的学生通过sid进行联系

SELECt * FROM 
(SELECt * FROM t_score WHERe cId = '01') AS a
INNER JOIN (SELECt * FROM t_score WHERe cId = '02') AS b
ON a.sId = b.sId;

结果：

03）查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

选了‘01’课程但是不一定选了‘02’课程的同学sid进行联系

SELECt * from 
(SELECt * from t_score where cid ='01') as a
LEFT JOIN t_score AS b
ON a.sId = b.sId AND b.cId = '02';

结果：

04）查询不存在" 01 “课程但存在” 02 "课程的情况

没有选了‘01’课程但是选了‘02’课程的同学

SELECt * from t_score 
WHERe sid NOT IN (SELECt sid FROM t_score WHERe cid = '01') 
and cid = '02';

结果：

05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

步骤：将平均成绩大于等于 60 分的同学的sid和平均成绩查出来作b表，然后同sid进行联系

SELECt a.sid,a.sname,b.pjf from
t_student as a
INNER JOIN (SELECt sid,AVG(score) AS pjf
            FROM t_score
            GROUP BY sid
            HAVINg AVG(score) >= 60) AS b
ON a.sid = b.sid;

结果：

06）查询在t_score表存在成绩的学生信息

步骤：将t_score有成绩的sid作a表，然后通过sid进行联系

SELECt b.* from 
(SELECt sid from t_score GROUP BY sid) a
LEFT JOIN t_student b
on a.sid=b.sid

结果：

07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

步骤：将t_score表的学生编号，学生选课总数，所有课程的总成绩查出来作b表，然后通过sid进行联系

SELECt a.sid,a.sname,b.zs,b.zcj
FROM t_student AS a
LEFT JOIN (SELECt sid,COUNT(cid) AS zs,SUM(score) AS zcj
           FROM t_score
           GROUP BY sid) AS b
ON a.sid = b.sid;

结果：

08）查询「李」姓老师的数量

SELECt COUNT(*) FROM t_teacher where tname like '李%'

结果：

09）查询学过「张三」老师授课的同学的信息

步骤：将表与表之间通过编号建立联系，再把‘「张三」老师’条件加进去

第一种：
SELECt a.*,d.Tname
FROM t_student AS a
INNER JOIN t_score AS b
ON a.sid = b.sid
INNER JOIN t_course AS c
ON b.cid = c.cid
INNER JOIN t_teacher AS d
ON c.tid = d.tid
WHERe tname = '张三';

第二种：
SELECt d.* from t_score a,t_course b,t_teacher c,t_student d 
where a.sid=d.sid and b.tid=c.tid and b.cid=a.cid and c.tname='张三'

结果：

10）查询没有学全所有课程的同学的信息

步骤：将所有课程总数查出来，再通过t_score把没有学全所有课程的sid跟t_student进行联系

SELECt a.*,kc
FROM t_student AS a
INNER JOIN (SELECt sid,COUNT(cid) AS kc
            FROM t_score
            GROUP BY sid
            HAVINg kc < (SELECt COUNT(cid) FROM t_course)) AS b
ON a.sid = b.sid;

结果：

11）查询没学过"张三"老师教授的任一门课程的学生

步骤：将学过‘张三’教授的课程的sid查出来，然后sid不存在于t_student

SELECt sname
FROM t_student AS a
WHERe sid NOT IN (SELECt sid
                  FROM t_score AS a
                  LEFT JOIN t_course AS b
                  ON a.cid = b.cid
                  INNER JOIN t_teacher AS c
                  ON b.tid = c.tid
                  WHERe tname = '张三');

结果：

12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

步骤：将两门及其以上不及格课程的同学的sid查出来作b表，接着将t_score通过sid进行分组，把查出来的sid，和平均分作c表，然后通过sid进行联系

SELECt c.sid,d.sname,pjf
FROM(SELECt a.sid,AVG(score) AS pjf
     FROM t_score AS a
     INNER JOIN(SELECt sid
                FROM t_score
                WHERe score < 60
                GROUP BY sid
                HAVINg COUNT(cid) >= 2) AS b
     ON a.sid = b.sid
     GROUP BY a.sid) AS c
LEFT JOIN t_student AS d
ON c.sid = d.sid;

结果：

13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息

步骤：将‘01’课程分数小于 60的查出来作a表，通过sid进行联系排序

SELECt b.*,a.score
FROM(SELECt sid,score
     FROM t_score
     WHERe cid = '01' AND score < 60) AS a
LEFT JOIN t_student AS b
ON a.sid = b.sid
ORDER BY a.score desc;

结果：

14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

步骤：将t_student与t_score通过sid查出来作a表，接着t_score通过sid分组查出sid，平均成绩作b表，然后通过sid进行联系排序

SELECt a.sid,a.cid,a.score,pjcj
FROM(SELECt c.sid,b.cid,b.score
     FROM t_student c, t_score b
     WHERe c.sid = b.sid) AS a
INNER JOIN (SELECt sid,AVG(score) AS pjcj
           FROM t_score
           GROUP BY sid) AS b
ON a.sid = b.sid
ORDER BY b.pjcj DESC;

结果：

15）查询各科成绩最高分、最低分和平均分

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

MySQL 的 case when(用于计算条件列表并返回多个可能结果表达式之一)

语法：

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end

SELECt a.*,b.Cname
FROM(SELECt 
     cid,
     COUNT(*)   AS 选修人数,
     MAX(score) AS 最高分,
     MIN(score) AS 最低分,
     AVG(score) AS 平均分,
     SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS 及格率,
     SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / COUNT(*) AS 中等率,
     SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优良率,
     SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优秀率
     FROM t_score
     GROUP BY cid
     ORDER BY COUNT(*) DESC,CId ASC) AS a
LEFT JOIN t_course AS b
ON a.cid = b.cid;

结果：