MYSQL练习题

程序员文章站 2022-09-17 19:16:10

这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入欢迎使用Markdown编辑器你好！这是你第一次使用 Markdown编辑器所展示的欢迎页。如果你想学习如何使用Mar...

工作一年，发现自己sql写不好，所以从其他地方抄来一些题目，自己练习写了一下,写的都很基础

准备工作如下
–1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
–3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
–4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
*/
–创建测试数据
create table Student(s_id varchar(10),s_name nvarchar(10),s_birth datetime,s_sex nvarchar(10));
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
create table Course(c_id varchar(10),c_name varchar(10),t_id varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);
create table Teacher(t_id varchar(10),t_name varchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);
create table Score(s_id varchar(10),c_id varchar(10),s_score decimal(18,1));
insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(‘01’ , ‘02’ , 90);
insert into SC values(‘01’ , ‘03’ , 99);
insert into SC values(‘02’ , ‘01’ , 70);
insert into SC values(‘02’ , ‘02’ , 60);
insert into SC values(‘02’ , ‘03’ , 80);
insert into SC values(‘03’ , ‘01’ , 80);
insert into SC values(‘03’ , ‘02’ , 80);
insert into SC values(‘03’ , ‘03’ , 80);
insert into SC values(‘04’ , ‘01’ , 50);
insert into SC values(‘04’ , ‘02’ , 30);
insert into SC values(‘04’ , ‘03’ , 20);
insert into SC values(‘05’ , ‘01’ , 76);
insert into SC values(‘05’ , ‘02’ , 87);
insert into SC values(‘06’ , ‘01’ , 31);
insert into SC values(‘06’ , ‘03’ , 34);
insert into SC values(‘07’ , ‘02’ , 89);
insert into SC values(‘07’ , ‘03’ , 98);

下面进入正题
练习题

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
1.1 遇事不会不要慌，先查一组数据：把01的分数查找出来　（单表查询）

SELECT
s.s_id,s.s_score as 课程01分数
from Score s
where s.c_id = 01

执行结果：
MYSQL练习题
1.2　在1.1的基础上把02的分数查出来加进去：（关联一张表出来）

SELECT
s.s_id,s.s_score as 课程01分数,s1.s_score as 课程02分数
from Score s,Score s1
where s.c_id = 01  
and s1.c_id = 02
and s.s_id = s1.s_id

执行结果：
MYSQL练习题
1.3　在1.2的基础上继续添加，增加条件：01的分数比02的分数高：

SELECT
s.s_id,s.s_score as 课程01分数,s1.s_score as 课程02分数
from Score s,Score s1
where s.c_id = 01  
and s1.c_id = 02
and s.s_id = s1.s_id
and s.s_score > s1.s_score

执行结果：
MYSQL练习题
1.4　接下来把学生信息添加进去：（再关联一张表）

SELECT
c.*,s.s_id,s.s_score as 课程01分数,s1.s_score as 课程02分数
from Score s,Score s1,Student c
where s.c_id = 01  
and s1.c_id = 02
and s.s_id = s1.s_id
and s.s_score > s1.s_score
and s.s_id = c.s_id

执行结果：
MYSQL练习题
哈哈哈哈写到这里往回看一下，有没有发现问题，有数据缺失了哦，当某个学生有课程0１的分数但是没有课程0２的分数，这种数据会被漏掉，重新来：
1.1　一定要先确认主表，这里主表是Stuent表：
先将所有学生信息查询出来：

Select * from Student

执行结果：
MYSQL练习题
1.2　向查询结果中添加01课程的成绩（关联用于查询01成绩Score表）

SELECT
s.*,a.s_score as 01score
from Student s 
LEFT JOIN Score a on s.s_id = a.s_id and a.c_id = 01

执行结果：可以看到成绩为空的也查询出来了
MYSQL练习题
1.3　向其中添加02课程的成绩（继续关联用于查询02成绩Score表）

SELECT
s.*,a.s_score as 01score,b.s_score as 02score
from Student s 
LEFT JOIN Score a on s.s_id = a.s_id and a.c_id = 01
LEFT JOIN Score b on s.s_id = b.s_id and b.c_id = 02

执行结果：
MYSQL练习题
1.4　增加条件用来筛选01成绩大于02成绩的数据：

SELECT
s.*,a.s_score as 01score,b.s_score as 02score
from Student s 
LEFT JOIN Score a on s.s_id = a.s_id and a.c_id = 01
LEFT JOIN Score b on s.s_id = b.s_id and b.c_id = 02
where ifnull(a.s_score,0) > ifnull(b.s_score,0)　//如果成绩是null，那么取0值

执行结果：
MYSQL练习题
ps:其实这里也能看出来内连结和左连结的区别啦
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
2.1 确定主表

SELECT s.s_id,s.s_name from Student s

执行结果：
MYSQL练习题
2.2　关联成绩表,并且查询出成绩

SELECT
s.s_id,s.s_name,a.s_score
from Student s LEFT JOIN Score a on s.s_id = a.s_id

执行结果：
MYSQL练习题

2.3　GROUP BY 语句和求平均值的函数AVG结合使用，求平均分

SELECT
s.s_id,s.s_name,AVG(a.s_score) avg_score 
from Student s LEFT JOIN Score a on s.s_id = a.s_id
GROUP BY s.s_id,s.s_name

执行结果：
MYSQL练习题
2.4 　HAVING子句筛选出来平均分大于60分的数据

SELECT
s.s_id,s.s_name,AVG(a.s_score) avg_score 
from Student s LEFT JOIN Score a on s.s_id = a.s_id
GROUP BY s.s_id,s.s_name
HAVING AVG(a.s_score)>=60

执行结果：
MYSQL练习题
ps:用HAVING的原因是 WHERE 关键字无法与聚合函数一起使用，最后一句HAVING AVG(a.s_score)>=60可以用来筛选数据，但是如果写WHERE AVG(a.s_score)>=60，就会报错
3.　查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
依然是HAVING和函数结合使用

SELECT
s.s_id,s.s_name,count(a.c_id) 选课总数, sum(a.s_score) 总成绩
from Student s LEFT JOIN Score a on s.s_id = a.s_id 
GROUP BY s.s_id,s.s_name

执行结果：
MYSQL练习题
4.　查询"李"姓老师的数量
LIKE 操作符和通配符％的使用

SELECT count(0) from Teacher t WHERE t.t_name like'李%'

执行结果：
MYSQL练习题
5.　查询学过"张三"老师课程的同学的学生信息
5.1　先把所有学生的学生信息和他们上的课查询出来

SELECT
s.*,a.c_id
from Student s ,Score a
where s.s_id = a.s_id

执行结果：
MYSQL练习题

5.2　看看张三老师教的课课程编号是多少

select c.c_id from Course c,Teacher t where c.t_id = t.t_id and t.t_name = '张三'

MYSQL练习题
5.3　将5.1和5.2组合起来

SELECT
s.*,a.c_id
from Student s ,Score a
where s.s_id = a.s_id and a.c_id = (select c.c_id from Course c,Teacher t where c.t_id = t.t_id and t.t_name = '张三')

执行结果：
MYSQL练习题
哈哈哈这么写的话大体意思还是容易理解的，但是看着有些蠢，改良一下：

SELECT
s.*,a.c_id
from Student s,
Score a,
Course c,
Teacher t
where s.s_id = a.s_id 
and a.c_id = c.c_id
and c.t_id = t.t_id 
//这部分SQL分为２块，第一块（这句话上面）是把需要的表按照关联字段关联起来，第二块（这句话下面）是加上条件
and t.t_name = '张三'

6.　查询没学过"张三"老师授课的同学的信息
在学过课程的基础上进行操作：

SELECT * from Student where s_id not in(SELECT
s.s_id
from Student s,
Score a,
Course c,
Teacher t
where s.s_id = a.s_id 
and a.c_id = c.c_id
and c.t_id = t.t_id 
and t.t_name = '张三')

执行结果：
MYSQL练习题
7.　查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s.* 
from Student s 
//学生id既在学过01课程的学生id里面
where (s.s_id in (SELECT c.s_id from Score c where c.c_id = 01))
//学生id也在学过02课程的学生id里面
and (s.s_id in (SELECT c.s_id from Score c where c.c_id = 02))

执行结果：
MYSQL练习题
8.　查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT s.* 
from Student s 
where (s.s_id in (SELECT c.s_id from Score c where c.c_id = 01))
//在７的基础上加一个not
and (s.s_id not in (SELECT c.s_id from Score c where c.c_id = 02))

执行结果：
MYSQL练习题
9.　查询没有学全所有课程的同学的信息
9.1　统计出每个学生学习的课程数量

SELECT s.s_id,count(a.c_id) 课程数量
FROM Student s LEFT JOIN Score a on s.s_id = a.s_id
GROUP BY s.s_id

执行结果：
MYSQL练习题
9.2　统计出一共有多少门课

select count(c.c_id) from Course c

执行结果：
MYSQL练习题
9.3　将9.1和9.2结合，筛选所学课程数比总课程数少的数据

SELECT s.*
FROM Student s LEFT JOIN Score a on s.s_id = a.s_id
GROUP BY s.s_id
HAVING count(a.c_id) < (select count(c.c_id) from Course c)

执行结果：
MYSQL练习题
10　查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select s.* 
FROM Student s,Score a
where s.s_id = a.s_id
and a.c_id in (select c_id from Score where s_id = 01)
and s.s_id !=01
GROUP BY s.s_id

执行结果：
MYSQL练习题
11　查询和"01"号的同学学习的课程完全相同的其他同学的信息
11.1　将每个学生学习了课程统计在一个字段里面

SELECT a.s_id,GROUP_CONCAT(a.c_id order by a.c_id) from Score a GROUP BY a.s_id

执行结果：
MYSQL练习题
11.2　在11.1的基础上查询出所学课程和１完全相同的学生id

SELECT s_id 
FROM Score
GROUP BY s_id 
HAVING GROUP_CONCAT(c_id order by c_id) = 
(SELECT GROUP_CONCAT(a.c_id order by a.c_id) from Score a where a.s_id = 01 GROUP BY a.s_id)

执行结果：
MYSQL练习题
11.3　查询出学生信息

SELECT * 
from Student 
where s_id in
(SELECT s_id 
FROM Score
GROUP BY s_id 
HAVING GROUP_CONCAT(c_id order by c_id) = 
(SELECT GROUP_CONCAT(a.c_id order by a.c_id) from Score a where a.s_id = 01 GROUP BY a.s_id

执行结果：
MYSQL练习题
12　查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT Student.s_name 
from Student
where Student.s_id not in (
SELECT s.s_id 
FROM Student s,Score a,Course c,Teacher t 
where s.s_id = a.s_id
and a.c_id = c.c_id
and c.t_id = t.t_id
and t.t_name = '张三'
GROUP BY s.s_id
)

执行结果：
MYSQL练习题
13　查询两门及两门以上成绩不及格的同学的学号，姓名及其平均成绩
13.1 　查询有不及格成绩的学生id和不及格课程的数量

select a.s_id,count(a.s_score)
from Score a
where a.s_score < 60
GROUP BY a.s_id

MYSQL练习题
13.2　选出不及格课程数量大于等于２的学生id

select a.s_id
from Score a
where a.s_score < 60
GROUP BY a.s_id
HAVING count(a.s_id)>=2

MYSQL练习题
13.3　查询学生姓名及平均成绩

SELECT Student.s_id,Student.s_name,AVG(Score.s_score)
from Student,Score
WHERE Student.s_id = Score.s_id
and Score.s_id in (
select a.s_id
from Score a
where a.s_score < 60
GROUP BY a.s_id
HAVING count(a.s_id)>=2
)
GROUP BY Score.s_id

MYSQL练习题
14 检索"01"课程分数小于60的学生的学生信息，按分数降序排列

SELECT s.*,a.s_score
from Student s,Score a
where s.s_id = a.s_id
and a.c_id = 01
and a.s_score < 60
ORDER BY a.s_score DESC

MYSQL练习题
15 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s.s_name ,a.s_score,b.s_score,c.s_score,AVG(d.s_score)
from Student s 
LEFT JOIN Score a on s.s_id = a.s_id and a.c_id = 01
LEFT JOIN Score b on s.s_id = b.s_id and b.c_id = 02
LEFT JOIN Score c on s.s_id = c.s_id and c.c_id = 03
LEFT JOIN  Score d on s.s_id = d.s_id
GROUP BY s.s_id
ORDER BY AVG(d.s_score) DESC

MYSQL练习题
16 　统计各科考试成绩情况：
以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
其中：及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

更新中

本文地址：https://blog.csdn.net/qq_37371375/article/details/109449690

MYSQL练习题

mysql 8.0.15 winx64安装配置方法图文教程

mysql 8.0.11 安装配置方法图文教程(win10)

Mysql通过存储过程分割字符串为数组

mysql 8.0.15 安装配置方法图文教程（Windows10 X64）

mysql实现查询数据并根据条件更新到另一张表的方法示例

pycharm2017实现python3.6与mysql的连接

window下mysql 8.0.15 winx64安装配置方法图文教程

详解数据库_MySQL: mysql函数

linux下mysql乱码问题的解决方案

MySQL执行状态的查看与分析