多表查询练习与pymysql模块
多表查询练习
思路
在编写较为复杂的sql查询的时候不要想着一口气写完
可以写一点查一点看一点然后再写
所有复杂的sql都是慢慢拼凑出来的
在编写复杂sql的时候可以先写中文思路，然后再拼凑sql
题1：查询所有的课程的名称以及对应的任课老师姓名
思路：
查询所有的课程的名称以及对应的任课老师姓名
涉及到的表：课程表、老师表，需要利用多表查询，思考确定为联表操作
最后确定select需要的字段名称
SELECT course.cname, teacher.tname FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid;
写完代码之后可以按‘美化SQL’，调整格式
加注释
和pycharm一样，选中需要注释掉的内容，然后按cirl+？，就可以注释掉了。
题2：查询平均成绩大于80分的同学的姓名和平均成绩
思路
涉及到的表：学生表、成绩表
先求分数表中平均成绩大于80的学生id
由于最终的需求是学生姓名和步骤2中的平均成绩，所以此处应该采用连表操作更合适
步骤1：
先求每个学生的平均成绩，按照学生id分组再利用聚合函数avg
select student_id,avg(num) from score group by student_id having avg(num)>80;
步骤2：将上面sql求解出的表与学生表连接到一起
select student.sname,t1.avg_num from student inner join (student_id,avg(num) as avg_num from score group by student_id having avg(num)>80) as t1 on student.sid = t1.student_id;
题3：查询没有报李平老师课程的学生姓名
思路：
涉及的表：老师表，学生表，课程表，分数表
求出报了李平老师课程的学生id
再去学生表中取反操作获取没有报李平老师课程的学生姓名
步骤1：
查询李平老师的id号
select tid from teacher where tname='李平老师'
步骤2：
根据id号筛选出课程id号
select cid from course where teacher_id=(select tid from teacher where tname='李平老师');
步骤3：
根据id去分数表中筛选出对应的学生id号
select distinct student_id from score
where course_id in(
select cid from course
where teacher_id=(
select tid from teacher where tname='李平老师')
);
步骤4：
去学生表中依据学生id取反
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 = (
SELECT tid FROM teacher WHERE tname = '李平老师' ) ) );
题4：查询没有同时选修物理课程和体育课程的学生（只要报了一门的，两门都报道和都不报的不要）
思路
涉及到的表：课程表，分数表，学生表
步骤一：
先获取物理和体育课程的id号
select cid from course where cname in ('物理','体育');
步骤2：
根据课程id筛选出所有报了物理和体育的学生id，包括两门都报了和只报了一门的
select * from score where course_id in(select cid from course where cname in('物理','体育'))
步骤3：
按照学生id分组，统计分组下的课程数量，筛选出数量为1的即可
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;
步骤4：
根据学生id去学生表中筛选出学生姓名
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：查询挂科（<60）超过两门（包括两门）的学生姓名和班级
思路：
涉及的表：分数表，学生表，班级表
步骤1：
筛选出所有num<60的数据
select * from score where num < 60;
步骤2：
按照学生id分组统计每个学生挂科的次数筛选出挂了两门及以上的
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
步骤3：
根据学生id获取对应的学生姓名和班级号
select sname,class_id from student where sid in (select student_id from score where num<60 group by student_id having count(course_id) >= 2)
python操作MySQL
第三方模块
python代码操作MySQL需要借助于第三方模块，也就是网上其他人写的模块。
python模块下载
环境配置
找到python解释器所在的文件夹中的scripts文件夹中的pip3.exe文件，并将文件路径添加到系统变量中。
下载模块的基本语句
方式1：cmd终端
pip3 install pymysql
方式2：pycharm终端
方式3：pycharm快捷方式
远程仓库
pip3下载模块的时候默认都是从国外的仓库下载模块数据
国内仓库
由于从国外下载会很慢，可以切换到国内的库
下面是一些常见仓库，也可以自己百度‘pip源’
（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/
数据库切换
cmd命令临时切换
pip3 install 模块名 -i 仓库地址
pycharm更改仓库地址
永久修改
需要修改python解释器内置的配置文件
pip下载模块报错与解决
报错信息里面含有timeout关键字
原因:当前计算机网络不稳定
措施:多执行几次或者更换网络
报错信息里面含有warning警告版本过低
原因:pip3工具版本过低需要更新
措施:直接拷贝提示的更新命令更新即可
报错信息里面没有任何关键字就是一堆红色字体
原因:可能是即将下载的模块对计算机环境有要求
措施:下载之前需要先准备好环境(百度搜一下)
pymysql模块
import pymysql # 创建连接对象 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db6', charset='utf8' ) cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 生成游标对象 等待用户输入命令 # 自定义sql语句 sql = 'select * from teacher' # 执行sql语句 cursor.execute(sql) # 获取执行的结果 res = cursor.fetchall() print(res)
