1 介绍

参考常见SQL面试题：经典50题，使用Python的pandas、sqlalchemy和pymysql库来完成练习题的所有操作。其中练习题数据库和表创建是在MySQLWorkbench中完成的。

• 数据库名：exercises
• 表名：
  • student：学号、姓名、出生日期、性别
  • score：学号、课程号、成绩
  • course：课程号、课程名称、教师号
  • teacher：教师号、教师姓名

pandas操作mysql数据库，具体使用方法，参考使用Python库操作Mysql数据库
系统环境：

• Mysql版本： 8.0.15 MySQL Community Server - GPL
• pymysql版本： 0.9.3
• pandas版本：0.23.1
• sqlalchemy版本：1.2.8

  2 导入需要的包

  ```python import pandas as pd import pymysql import warnings from sqlalchemy import create_engine

warnings.filterwarnings(‘ignore’)

<a name="XAjzN"></a>
# 3 封装查询函数和插入函数
<a name="C2Jny"></a>
## 3.1 封装查询函数
```python
def query(sql_str, db_name='exercises'):
    param = 'mysql+pymysql://root:123456@localhost:3306/{db_name}?charset=utf8'.format(db_name=db_name)
    engine = create_engine(param)
    df = pd.read_sql(sql=sql_str, con=engine)
    return df

3.2 封装插入函数¶

def insert(df, tb_name, db_name='exercises'):
    param = 'mysql+pymysql://root:123456@localhost:3306/{db_name}?charset=utf8'.format(db_name=db_name)
    engine = create_engine(param)
    df.to_sql(tb_name, con=engine, if_exists='append', index=False)

3.3 查看连接状态

用query函数查看数据库有哪些表来判断数据库是否连接正常。

sql = 'SHOW TABLES'
query(sql)

能够正常查询到exercises数据库内的表，说明数据库连接正常。

4 初始化表内数据

4.1 向student表内添加数据

df_student = pd.DataFrame({'学号': ['0001', '0002', '0003', '0004'], 
                           '姓名': ['猴子', '猴子', '马云', '王思聪'], 
                           '出生日期': ['1989-01-01', '1990-12-21', '1991-12-21', '1990-05-20'], 
                           '性别': ['男', '女', '男', '男']}
                         )
df_student

# 将df_student插入到student表中
insert(df_student, 'student')

# 查询student表
sql = '''SELECT * 
         FROM student
      '''
query(sql)

4.2 向score表内添加数据

df_score = pd.DataFrame({'学号': ['0001', '0001', '0001', '0002', '0002', '0003', '0003', '0003'], 
                         '课程号': ['0001', '0002', '0003', '0002', '0003', '0001', '0002', '0003'], 
                         '成绩': [80, 90, 50, 60, 80, 80, 80, 80]}
                       )
df_score

# 将df_score插入到score表内
insert(df_score, 'score')

# 查询score表
sql = '''SELECT * 
         FROM score
      '''
query(sql)

4.3 向course表内插入数据

df_course = pd.DataFrame({'课程号': ['0001', '0002', '0003'], 
                          '课程名称': ['语文', '数学', '英语'], 
                          '教师号': ['0002', '0001', '0003']}
                        )
df_course

# 将df_course插入到course表内
insert(df_course, 'course')

# 查询course表
sql = '''SELECT * 
         FROM course
      '''
query(sql)

4.4 向teacher表内插入数据

df_teacher = pd.DataFrame({'教师号': ['0001', '0002', '0003', '0004'], 
                           '教师姓名': ['孟扎扎', '马化腾', '', '']}
                         )
df_teacher

# 将df_teacher插入到teacher表中
insert(df_teacher, 'teacher')

# 查询teacher表
sql = '''SELECT * 
         FROM teacher
      '''
query(sql)

5 练习题

5.1 简单查询

当字符串包含“%”而不转义时

5.1.1 查询姓“猴”的学生名单

# 使用like关键字进行模糊匹配
sql = '''SELECT * 
         FROM student 
         WHERE 姓名 LIKE '猴%%'
      '''
query(sql)

5.1.2 查询姓名中包含“猴”的学生名单

# 使用like关键字进行模糊匹配
sql = '''SELECT * 
         FROM student 
         WHERE 姓名 LIKE '%%猴%%'
      '''
query(sql)

5.1.3 查询姓名以“猴”结尾的学生名单

# 使用like关键字进行模糊匹配
sql = '''SELECT * 
         FROM student 
         WHERE 姓名 LIKE '%%猴'
      '''
query(sql)

5.1.4 查询姓“孟”老师的个数

# 使用count函数计数
sql = '''SELECT count(教师号) AS 数量 
         FROM teacher 
         WHERE 教师姓名 LIKE '孟%%'
      '''
query(sql)

5.2 汇总分析

5.2.1 查询课程号“0002”的总成绩

# 使用sum函数进行求和
sql = '''SELECT sum(成绩) AS 总成绩 
         FROM score 
         WHERE 课程号='0002'
      '''
query(sql)

5.2.2 查询选了课程的学生人数

# 使用distinct关键字去重
sql = '''SELECT count(DISTINCT 学号) AS 学生人数 
         FROM score
      '''
query(sql)

5.3 分组

5.3.1 查询各科成绩最高分和最低分

# 使用group by关键字进行分组
sql = '''SELECT 课程号, max(成绩) AS 最高分, min(成绩) AS 最低分 
         FROM score 
         GROUP BY 课程号
      '''
query(sql)

5.3.2 查询每门课程被选修的学生数

# 使用group by关键字进行分组
sql = '''SELECT 课程号, count(学号) AS 学生数 
         FROM score 
         GROUP BY 课程号
      '''
query(sql)

5.3.3 查询男、女学生人数

# 使用group by关键字进行分组
sql = '''SELECT 性别, count(学号) AS 人数 
         FROM student 
         GROUP BY 性别
      '''
query(sql)

5.4 分组结果的条件

5.4.1 查询平均成绩大于60分学生的学号和平均成绩

# 使用having关键字进行过滤
sql = '''SELECT 学号, avg(成绩) AS 平均成绩
         FROM score
         GROUP BY 学号
         HAVING avg(成绩)>60
      '''
query(sql)

5.4.2 查询至少选修两门课程的学生学号

# 使用having关键字进行过滤
sql = '''SELECT 学号, count(课程号) AS 课程数 
         FROM score
         GROUP BY 学号
         HAVING count(课程号)>=2
      '''
query(sql)

5.4.3 查询同名同姓学生名单并统计同名人数

# 按姓名分组后，人数大于等于2的即为
sql = '''SELECT 姓名, count(*) AS 人数
         FROM student
         GROUP BY 姓名
         HAVING count(*) >=2
      '''
query(sql)

5.4.4 查询不及格的课程并按课程号降序排列

# 使用order by关键字进行排序
sql = '''SELECT 课程号
         FROM score
         WHERE 成绩<60
         ORDER BY 课程号 DESC
      '''
query(sql)

5.4.5 查询每门课平均成绩，结果按平均升级升序、课程号降序排列

# 使用order by关键字进行排序
sql = '''SELECT 课程号, avg(成绩) AS 平均成绩
         FROM score
         GROUP BY 课程号
         ORDER BY avg(成绩) ASC, 课程号 DESC
      '''
query(sql)

5.4.5 查询课程编号为“0004”且分数小于60的学生号，结果按分数降序排列

# 使用order by关键字进行排序
sql = '''SELECT 学号
         FROM score
         WHERE 课程号='0004' AND 成绩<60
         ORDER BY 成绩 DESC
      '''
query(sql)

5.4.6 查询每门课程的选修人数（超过2人的课程才统计）

# 使用order by关键字进行排序
sql = '''SELECT 课程号, count(*) AS 人数
         FROM score
         GROUP BY 课程号
         HAVING count(*)>=2
         ORDER BY count(*) DESC, 课程号 ASC
      '''
query(sql)

5.5 复杂查询

5.5.1 查询所有课程成绩小于60分学生的学号和姓名

# 使用子查询进行条件筛选
sql = '''SELECT 学号, 姓名
         FROM student
         WHERE 学号 IN (SELECT 学号 
                        FROM score
                        WHERE 成绩<60)
      '''
query(sql)

5.5.2 查询所有没有学全所有课的学生的学号和姓名

# 使用子查询进行条件筛选
sql = '''SELECT 学号, 姓名
         FROM student
         WHERE 学号 IN (SELECT 学号
                        FROM score
                        GROUP BY 学号
                        HAVING count(课程号)<(SELECT count(课程号) 
                                              FROM course))
      '''
query(sql)

5.5.3 查询只选修了两门课的学生的学号和姓名

# 使用子查询进行条件筛选
sql = '''SELECT 学号, 姓名
         FROM student
         WHERE 学号 IN (SELECT 学号
                        FROM score
                        GROUP BY 学号
                        HAVING count(课程号)=2)
      '''
query(sql)

5.5.4 查询1990年出生的学生名单

日期函数解释：

# 使用日期函数进行筛选
sql = '''SELECT 姓名
         FROM student
         WHERE year(出生日期)=1990
      '''
query(sql)

5.5.5 查询各科成绩前两名的记录

关联子查询解决TOPN问题
关联子查询内部逻辑

# 用关联子查询来实现
sql = '''(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)
      '''
query(sql)

5.6 多表查询

5.6.1 查询所有学生的学号、姓名、选课数和总成绩

# 左连接student和score表，再用group by关键字进行分组
sql = '''SELECT a.学号, a.姓名, count(b.课程号) AS 选课数, sum(b.成绩) AS 总成绩
         FROM student AS a 
         LEFT JOIN score AS b
         ON a.学号=b.学号
         GROUP BY a.学号
      '''
query(sql)

5.6.2 查询平均成绩大于70的所有学生的学号、姓名和平均成绩

# 左连接student和score表，再用group by进行分组，最后用having进行筛选
sql = '''SELECT a.学号, a.姓名, avg(b.成绩) AS 平均成绩 
         FROM student AS a
         LEFT JOIN score AS b
         ON a.学号=b.学号
         GROUP BY a.学号
         HAVING avg(b.成绩)>70
      '''
query(sql)

5.6.3 查询学生的选课情况：学号、姓名、课程号和课程名称

#  先左连接score和course表，再用student表左连接上述结果表
sql = '''SELECT a.学号, a.姓名, c.课程号, c.课程名称
         FROM student AS a
         LEFT JOIN score  AS b
         ON a.学号=b.学号
         LEFT JOIN course AS c
         ON b.课程号=c.课程号
         '''
query(sql)

5.6.4 查询每门课程的及格人数和不及格人数

MySQL case when 用法

# 使用 case when then else来对分组后的小组进行筛选
sql = '''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 课程号
      '''
query(sql)

5.6.5 使用分段来统计各科成绩

# 左连接score和course表，再用group by进行分组，然后用case when进行筛选
sql = '''SELECT a.课程号, b.课程名称,
                sum(CASE WHEN a.成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '85分-100分',
                sum(CASE WHEN a.成绩 BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '75分-85分',
                sum(CASE WHEN a.成绩 BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '60分-70分',
                sum(CASE WHEN a.成绩<60 THEN 1 ELSE 0 END) AS '不及格'
         FROM score AS a
         LEFT JOIN course AS b
         ON a.课程号=b.课程号
         GROUP BY a.课程号
      '''
query(sql)

6 解决异常

6.1 连接Mysql拒绝访问异常

• 异常：使用pymysql和sqlalchemy连接mysql时，提示“Access denied for user ‘root’@’localhost’ (using password: NO)”异常，原因是pymysql版本太低。

• 方案：将其升级到最高版本0.7.9可以消除该异常。

  6.2 tx_isolation隔离异常

• 异常：参考MySQL查询隔离级别报错1193。

• 方案：将sqlalchemy升级到1.2.10，解决该问题。

  6.3 Incorrect string value警告

• 警告：使用sqlalchemy进行查询或者其它操作时，都提示Incorrect string value: ‘\xD6\xD0\xB9\xFA\xB1\xEA…’ for column ‘VARIABLE_VALUE’ at row 3375。

• 方案：参考MySQL 5.7.13 的一个BUG，为了美观使用warnings包，将警告消除。