多表查询练习与Python操作数据库

多表查询练习与Python操作数据库

概要

• 多表查询练习题

• Python操作MySQL数据库

• PyMySQL模块基本使用

---

详情

• 多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名

思路:这题两张表表
1.将课程表的老师序号与老师表序号对应
2.连接课程表与老师表,将结果看作一张大表
3.从这张大表查询课程名称和老师姓名
select c.cname as '课程名称',t.tname as '任课老师姓名' from course as c left join teacher as t on c.teacher_id=t.tid;

2、查询平均成绩大于八十分的同学的姓名和平均成绩

思路:这题需要两张表
1.先按学生序号分组求平均分,过滤出平均分大于80的学生
2.再将上述结果当作一张表和学生表连接成大表
3.在大表中查询学生姓名和平均成绩
select st.sname as '学生姓名',sc.an as '平均成绩' from student as st 
inner join (
select student_id,avg(num) as an from score group by student_id having avg(num)>80
) as sc 
on st.sid=sc.student_id;

3、查询没有报李平老师课的学生姓名

思路:这题需要四张表
1.查询李平老师教授哪些课,获得课程序号
2.根据授课编号去分数表查询报了李平老师课的学生序号
3.根据上述学生序号去学生表查询没报的学生姓名
select sname from student 
where sid not in (
    select distinct student_id from score 
    where course_id in (
        select cid from course 
        where teacher_id in(
            select tid from teacher 
            where tname='李平老师'
        )
    )
);

4、查询没有同时选修物理课程和体育课程的学生姓名

思路:这题需要三张表
1.先在课程表获得物理和体育的课程序号
2.根据课程序号在分数表查询报了物理或者体育的学生序号
3.按照上述学生序号分组,过滤课程序号只出现一次的学生序号
4.通过上述学生序号去学生表查询学生姓名
select sname from student where sid in(    
    select student_id from score 
        where course_id in(
            select cid from course 
            where cname in ('物理','体育')
        )
        group by student_id
        having count(course_id)=1
);

5、查询挂科超过两门(包括两门)的学生姓名和班级

思路:这题需要三张表
1.先在分数表筛选出分数小于60分的记录
2.再按学生序号分组求挂科数,过滤挂科超两门的记录
3.按上述获取的学生序号在学生表查询姓名
4.通过学生表的班级序号连接班级表查询班级名称
select student.sname,class.caption from student 
inner join class on student.class_id=class.cid 
where student.sid in(
　　select student_id from score 
　　where num<60 
　　group by student_id 
　　having count(num)>=2
);

---

• Python操作MySQL数据库

通过Python代码可以操作MySQL数据库,需要借助第三方的模块。
第三方模块:非内置的模块
# 通过网络下载第三方模块
下载步骤:
    1.将Python解释器下scripts目录配置到环境变量

    2.下载模块
        (1) pip3 install pymysql 
            # 在cmd或者pycharm终端输入)
        (2) settings --> python interpreter --> +号再搜索               # pycharm界面操作

    3.远程仓库
        通过pip3下载模块时默认从国外的仓库下载模块文件
        有时会很慢,可以尝试使用国内的仓库
（1）阿里云 http://mirrors.aliyun.com/pypi/simple/
（2）豆瓣 http://pypi.douban.com/simple/
（3）清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/
（4）中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/
（5）华中科技大学http://pypi.hustunique.com/
    # 切换库
    1.cmd终端
        pip3 install 模块名 -i 仓库地址
    2.pycharm更改仓库地址
        下载模块的界面点击 Manage Repositories
    3.修改Python解释器内置配置文件 # 永久更改

# pip3命令报错
1.timeout
    网络不稳定,需多次尝试或更换网络
2.warning
    pip3工具版本过低,需要更新,提示信息中有更新命令
3.无明显关键字,有红色提示信息
    当前环境不支持,需先按要求准备环境

---

• PyMySQL模块基本使用

1.引入模块
import pymysql
2.创建连接对象
conn = pymysql.connect(
    host='127.0.0.1',    # 服务端地址
    port=3306,          # 端口,MySQL默认3306
    user='root',         # 用户名
    password='123456'    # 密码
    database='db_dbcx'    # 指定库
    charset='utf8'       # 字符编码
)
3.生成游标对象,等待用户输入命令(参数为将结果按记录封装成字典)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
4.书写SQL语句
sql = 'select * from teacher'
5.执行SQL语句
cursor.execute(sql)
6.获取执行结果
res = cursor.fetchall()
print(res)

 

---

作业

1、基于Python与MySQL实现用户登录

import pymysql

def getUserInfo():
    """获取用户输入的信息"""
    # 1.输入用户名和密码
    userName = input('请输入用户名:').strip()
    password = input('请输入密码:').strip()
    # 2.将用户输入的用户名和密码组合成字典后返回
    userInfo = {'userName': userName, 'password': password}
    return userInfo

def isNone(userInfo):
    """校验用户名和密码是否为空"""
    if userInfo.get('userName') == '' or userInfo.get('password') == '':
        print('用户名或密码为空,请重新登录！！！')
        return True
    return False

def selectInfo(sql):
    """查询数据库"""
    # 1.创建连接对象
    conn = pymysql.connect(
        host='127.0.0.1',  # 服务端地址
        port=3306,  # 端口,MySQL默认3306
        user='root',  # 用户名
        password='ultra12345',  # 密码
        database='db_dbcx',  # 指定库
        charset='utf8'  # 字符编码
    )
    # 2.生成游标对象,等待用户输入命令(参数为将结果按记录封装成字典)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 3.执行传入的SQL语句
    cursor.execute(sql)
    # 4.获取执行结果
    res = cursor.fetchall()
    return res

def register():
    """注册"""

    return


def login():
    """登录"""
    # 1.获取输入的用户信息
    userInfo = getUserInfo()
    # 2.判断输入的用户名或密码是否为空,是就中断
    if isNone(userInfo):
        return False
    # 3.用户信息不为空,提取用户名和密码
    userName = userInfo.get('userName')
    password = userInfo.get('password')
    # 4.书写SQL语句,从数据库查询该用户的信息
    sql = 'select user_name,password from user_info where user_name="{}"'.format(userName)
    realUserInfo = selectInfo(sql)[0]
    # 5.判断数据库中有无该用户
    if realUserInfo == {}:
        print('用户名或密码为空,请重新登录！！！')
        return False
    # 6.比对输入的用户信息与数据库中的用户信息是否一致
    if userName == realUserInfo.get('user_name').strip() and password == realUserInfo.get('password').strip():
        print('~~~用户{}登录成功!!!~~~'.format(userName))
    else:
        print('用户名或密码错误!!!')
        return False
    return True

"""主流程"""
print('*********用户注册登录系统 ver4.0*********')
print('*********** 1、注册 *******************')
print('*********** 2、登录 *******************')
funcDict = {'1': register, '2': login}
while True:
    funcNum = input('请输入想要执行的功能:').strip()
    if funcNum in funcDict:
        funcName = funcDict.get(funcNum)
        funcName()
    else:
        print('该功能不存在,清重新输入!!!')