同时用SQL和python实现【SQL经典50题--01查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号】

题⽬知乎链接：  

https://zhuanlan.zhihu.com/p/38354000 

https://zhuanlan.zhihu.com/p/43289968

表结构：

一，创建数据库，创建表并插入数据

0.创建库 new_students;

1.1创建学生表-student

CREATE TABLE student(

    学生编号 VARCHAR(20),

    姓名 VARCHAR(20),

    出生日期 VARCHAR(20),

    性别  VARCHAR(10)

);

1.2.向学生表插入数据

insert into student(学号,姓名,出生日期,性别)  

values('0001' , '猴子' , '1989-01-01' , '男'),

('0002' , '猴子' , '1990-12-21' , '女'),

('0003' , '马云', '1991-12-21' , '男'),

('0004' , '王思聪' , '1990-05-20' , '男'),

('0005' , '陈五', '1995-11-11' , '男'),

('0006' , '杨六六', '1998-03-21' , '女'),

;

2.1 创建成绩表-score

CREATE TABLE score(

    学号 VARCHAR(20),

    课程号 VARCHAR(20),

    成绩 FLOAT(3)

);

2.2向成绩表插入数据

insert into score(学号,课程号,成绩)

values('0001' , '0001' , 80),

('0001' , '0002' , 90),

('0001' , '0003' , 99),

('0002' , '0002' , 60),

('0002' , '0003' , 80),

('0003' , '0001' , 80),

('0003' , '0002' , 80),

('0003' , '0003' , 80),

('0004' , '0001' , 90),

('0004' , '0002' , 88),

('0004' , '0003' , 80),

('0005' , '0001' , 96),

('0005' , '0002' , 89),

('0005' , '0003' , 80),

;

3.1 创建课程表-course

CREATE TABLE course(

    课程号 VARCHAR(20),

    课程名称  VARCHAR(20),

    教师号  VARCHAR(20)

);

3.2向 课程表 插入数据

insert into course(课程号,课程名称,教师号)

values('0001' , '语文' , '0002'),

('0002' , '数学' , '0001'),

('0003' , '英语' , '0003');

4.1 创建教师表-teacher

CREATE TABLE teacher(

    教师号  VARCHAR(20),

    教师姓名  VARCHAR(20)

);

4.2向 教师表 插入数据

insert into teacher(教师号,教师姓名)

values('0001' , '孟扎扎'),

('0002' , '马化腾'),

('0003' , null),

('0004' , '');

---

二，SQL处理思路

1.1思路，先把score那个表变形成：

1.2 先学号和课程0001汇总：

1.3再学号和课程号0002汇总：

1.4把上面两个表用inner join连接起来。

SELECT student.学号, A.成绩  AS score01, B.成绩 AS score02

FROM  (  select * from score where score.课程号 = '0001'    )   AS A

INNER JOIN  ( select * from score where score.课程号 = '0002'   )   AS B  ON A.学号 = B.学号

INNER JOIN student  ON A.学号 = student.学号 ;

1.5要在1.4的基础上，加个where筛选，就可以把结果筛出来了。

WHERE A.成绩 > B.成绩 ;

【注意！WHERE里面 要用A.成绩 > B.成绩 ，而不能用新的变量名 score01>score02 来写】

完整的SQL语句：

SELECT student.学号, A.成绩  AS "score01", B.成绩 AS "score02"

FROM  (  select * from score where score.课程号 = '0001'    )   AS A

INNER JOIN  ( select * from score where score.课程号 = '0002'   )   AS B  ON A.学号 = B.学号

INNER JOIN student  ON A.学号 = student.学号

WHERE A.成绩 > B.成绩 ;

三，用python实现

import pandas as pd
import requests
import pymysql
from lxml import etree
import time
import csv
import random
import sys
###重点。

#1.连接数据库
conn = pymysql.connect(host='localhost', user='root', passwd='root', db='new_students', port=3306, charset='utf8')
cursor = conn.cursor()

#2.读取数据
sql_student = 'select * from student'
sql_score = "select * from score"
df_student = pd.read_sql(sql_student,conn)
print(df_student)
df_score = pd.read_sql(sql_score,conn)
print(df_score)
print("-"*100)

#3.根据学号构建一个新的dataframe。
for i in range(len(df_student)):
    for j in range(len(df_score)):
        if df_student.loc[i,"学号"] == df_score.loc[j,"学号"]   and   df_score.loc[j,"课程号"] == "0001":
            df_student.loc[i, "课程01的成绩"]  = df_score.loc[j,"成绩"]

        if df_student.loc[i,"学号"] == df_score.loc[j,"学号"]   and   df_score.loc[j,"课程号"] == "0002":
            df_student.loc[i, "课程02的成绩"]  = df_score.loc[j,"成绩"]
print(df_student)
print("-"*100)

#4.筛选出 课程01的成绩 > 课程02的成绩 所在行。
df_final = df_student[df_student["课程01的成绩"] > df_student["课程02的成绩"]]
print(df_final)
print("-"*100)

#5.打印出学号
print("课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号为：")
print(df_final["学号"].to_list())