分别使用 SQL 和 Python 实现MySQL经典50题
总有一天，你做过的那些看似没有用途的事情，会在生命的某一时刻连接在一起，形成一条价值线。
大家好！我是风一、对于和数据打交道的职场人员来说；数据库的使用是必不可少的,针对于 MySQL 经典50道练习题、我想多多少少都略知一二,尤其是刚接触数据库或者准备面试的人员、或多或少都会拿这些题目来练手，当然、风一认为，这些题目、作为有一定数据库操作经验的人员,也是具备一定练习的价值，不但可以加强SQL语感、还可以提升写SQL语句的思维。那么闲话就不多说、这里风一挑选了部分题目，分别使用SQL语句和Python代码来实现，强烈建议需要人员、自行先实现脚本或代码。
一、数据准备
--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
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' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
# Python的数据、这里直接使用 read_clipboard() 进行数据库的数据分别查询、复制。
student = pd.read_clipboard()
course = pd.read_clipboard()
teacher = pd.read_clipboard()
score = pd.read_clipboard()
二、SQL 和 Python 实现
-
1、查询名字中含有"风"字的学生信息
# SQL 实现:
SELECT * from student where s_name like '%风%'
# Python 实现:
student[student['s_name'].str.contains('风')]
-
2、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
# SQL 实现:
SELECT t1.*,t2.s_score
from Student as t1
inner join Score as t2 on t1.s_id = t2.s_id
inner join Score as t3 on t2.s_id = t3.s_id
where t2.c_id = '01' and t3.c_id = '02' and t2.s_score > t3.s_score
# Python 实现:
df1 = score[score['c_id'] == 1]
df2 = score[score['c_id'] == 2]
df3 = pd.merge(df1, df2, how='inner', on='s_id')
df4 = df3[['s_id', 's_score_x']][df3['s_score_x'] > df3['s_score_y']]
pd.merge(student, df4, how='inner', on='s_id')
-
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
# SQL 实现:
SELECT t1.*,avg(t2.s_score) avg_score from Student t1
inner join Score t2 on t1.s_id=t2.s_id
group by t2.s_id HAVING avg(t2.s_score) >= 60
# Python 实现:
df1 = score.groupby('s_id')['s_score'].mean('s_score').reset_index()
pd.merge(student, df1[df1['s_score'] >= 60], how='inner', on='s_id')
-
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
# SQL 实现:
SELECT t3.s_id,t3.s_name,t3.s_birth,t3.s_sex,avg(t3.s_score) avg_score from (
SELECT t1.*,IFNULL(t2.s_score,0) s_score FROM Student t1
left join Score t2 on t1.s_id = t2.s_id
) t3 group by t3.s_id,t3.s_name,t3.s_birth,t3.s_sex HAVING avg(t3.s_score) < 60
# Python 实现:
df1 = score.groupby('s_id')['s_score'].mean('s_score').reset_index()
rs = pd.merge(student, df1, how='left', on='s_id')
pd.concat([rs[rs['s_score'] < 60], rs[pd.isnull(rs['s_score'])]])
-
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
# SQL 实现:
SELECT t1.*,IFNULL(t2.c_counts,0) c_counts,IFNULL(t2.s_scores,0) s_scores from Student t1
left join (SELECT s_id,count(c_id) c_counts, sum(s_score) s_scores from Score group by s_id) t2 on t1.s_id=t2.s_id
# Python 实现:
df1 = score.groupby('s_id').agg({'c_id':'count', 's_score':'sum'}).reset_index()
rs = pd.merge(student, df1, how='left', on='s_id')
rs.fillna(0)
-
6、查询"李"姓老师的数量
# SQL 实现:
SELECT count(t_id) from teacher where t_name like '李%'
# Python 实现:
rs = teacher[teacher['t_name'].str.startswith('李')]
rs.shape[0]
-
7、查询学过"张三"老师授课的同学的信息
# SQL 实现:
SELECT t1.* from Student t1
inner join Score t2 on t1.s_id = t2.s_id
inner join Course t3 on t2.c_id = t3.c_id
inner join Teacher t4 on t3.t_id = t4.t_id
where t4.t_name = '张三'
# Python 实现:
df1 = teacher[teacher['t_name'] == '张三']
df2 = pd.merge(df1, course, on='t_id', how='inner')
df3 = pd.merge(df2, score, on='c_id', how='inner')
rs = pd.merge(df3, student, on='s_id', how='inner')
rs[['s_id','s_score', 's_name', 's_birth', 's_sex']]
-
8、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
# SQL 实现:
SELECT * from Student where s_id in (
SELECT s_id from score where c_id in ('01', '02') GROUP BY s_id HAVING count(s_id) > 1
)
# Python 实现:
df1 = score[score['c_id'] == 1]
df2 = score[score['c_id'] == 2]
df3 = pd.merge(df1, df2, on='s_id', how='inner')
student[student['s_id'].isin(df3['s_id'])]
-
9、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
# SQL 实现:
SELECT * from Student where s_id in (
SELECT s_id from Score where c_id = '01'
) and s_id not in (
SELECT s_id from Score where c_id = '02'
)
# Python 实现:
df1 = score[score['c_id'] == 1]
df2 = score[score['c_id'] == 2]
student[(student['s_id'].isin(df1['s_id'])) & (~student['s_id'].isin(df2['s_id']))]
-
10、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
# SQL 实现:
SELECT * from Student where s_id in (
SELECT DISTINCT t2.s_id from Score t1
inner join Score t2 on t1.c_id = t2.c_id and t2.s_id <> '01'
where t1.s_id = '01'
)
# Python 实现:
df1 = score['c_id'][score['s_id'] == 1]
df2 = score['s_id'][(score['c_id'].isin(df1)) & (score['s_id'] != 1)].unique()
student[student['s_id'].isin(df2)]
-
11、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
# SQL 实现:
SELECT t1.s_id,
(SELECT s_score from score where t1.s_id = s_id and c_id = '01') as '语文',
(SELECT s_score from score where t1.s_id = s_id and c_id = '02') as '数学',
(SELECT s_score from score where t1.s_id = s_id and c_id = '03') as '英语',
avg(s_score) a_score
from Score t1
group by t1.s_id
order by a_score desc
# Python 实现:
df1 = score.groupby('s_id')['s_score'].mean('s_score').reset_index().sort_values(by = 's_score', ascending = False)
df2 = pd.merge(df1, score[score['c_id'] == 1], on = 's_id', how = 'left').rename(
columns = {'s_score_x': '平均分', 's_score_y': '语文'})
df3 = pd.merge(df2, score[score['c_id'] == 2], on = 's_id', how = 'left')
rs = pd.merge(df3, score[score['c_id'] == 3], on = 's_id', how = 'left').rename(
columns = {'s_score_x' : '数学', 's_score_y' : '英语'})
rs[['s_id', '语文', '数学', '英语', '平均分']]
-
12、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
# SQL 实现:
SELECT t2.s_name, t3.c_name,t1.s_score from score t1
inner join Student t2 on t1.s_id=t2.s_id
inner join Course t3 on t1.c_id = t3.c_id
where t1.s_id in (
SELECT s_id from Score GROUP BY s_id having count(s_id) = (SELECT count(c_id) from Course) and min(s_score) >= 70
)
# Python 实现:
df1 = score.groupby('s_id').agg({'c_id' : 'count', 's_score' : 'min'}).reset_index()
df2 = df1[(df1['c_id'] == course.shape[0]) & (df1['s_score'] >= 70)]
df3 = score[score['s_id'].isin(df2['s_id'])]
df4 = pd.merge(df3, student, on = 's_id', how = 'inner')
rs = pd.merge(df4, course, on = 'c_id', how = 'inner')
rs[['s_name', 'c_name', 's_score']].sort_values(by = 's_name')
最后：这里风一只随机挑选了部分题目，大家感兴趣可以全部都去刷完。SQL练习好比狙击手、一个好的的狙击手绝对是需要用子弹才能喂出来的，SQL编写能力也是一样、多刷多练，这样SQL语感、思维都会有不错的提升。
往期文章：
「在生命的旅途中，即便我们不能成为一轮明月，也要努力成为一颗星星，在漫天中安静的发光、发亮。保持着内心的清宁与干净，温暖与明媚 — 风一」