07.多表查询实战、python代码操作MySQL

　　今天结合例题来详细讲解一下多表查询该怎么用。

多表查询实战

数据准备

　　首先要将数据导入库中。因为接下来思考的深度、编写的语句都会越来越复杂，所以推荐使用Navicat或其他可视化软件导入数据。

/*
 数据导入：
 Navicat Premium Data Transfer
​
 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam
​
 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8
​
 Date: 10/21/2016 06:46:46 AM
*/
​
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
​
-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
​
-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
​
-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
​
-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
​
-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
​
-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
​
-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
​
-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
​
-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
​
-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
​
SET FOREIGN_KEY_CHECKS = 1;

　　将上述数据复制后保存入.sql文件中，然后在Navicat界面中将鼠标移至左侧任意一个库上，右键点击“运行SQL文件”。

　　在之后弹出的对话框中，在文件这一行点击最右边的图标可以选取文件。选择保存了上述数据的sql文件。接下来点击开始即可。

　　数据载入完毕后先不急着做题，可以先检查一下导入的数据是否正常，如果出现乱码就是字符编码出问题了，需要在左侧栏对存放数据的库按右键点击编辑数据库。

答题

　　在做题前还可以再做一些准备工作，在左侧栏对存放数据的库按右键点击逆向数据库到模型，把各个表之间的关系图拎出来帮助厘清思路。

　　看到图后先利用换位思考的口诀来判断一下各表的外键关系。

　　一个班级可以对应多个学生，一个学生只能对应一个班级（一对多）

　　一个学生可以对应多个成绩，一个成绩只能对应一个学生（一对多）

　　一个成绩只能对应一门课程，一门课程只能对应一个成绩（一对一）

　　一门课程只可对应一个老师，一个老师可以对应多门课程（一对多）

　　图标可以根据需求随意拖动，我们可以根据他们的外键关系做一些调整。

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

　　技巧：在编写较为复杂的SQL查询语句时不要想着一口气写完，写一点查一点看一点在写。

　　可以先按照外键关系图理清答题思路。

步骤：

1.首先思考该题涉及到几张表？课程表和老师表

2.需要的数据在两张表中，我们可以使用联表操作来将两表中所需的数据合并在一起。

3.最后确定select后需要的字段名称，选取老师名和课程名

select course.cname,teacher.tname from course inner join teacher on course.teacher_id=teacher.tid;

　　在Navicat的查询功能中，也可以书写SQL语句，并在书写完后可以点击运行，在程序内直接看到运行结果。

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

步骤：

1.首先思考涉及几张表？成绩表、学生表

2.先求解分数表中平均成绩大于80分的学生id

3.由于最终的需求是学生姓名和平均成绩，所以此处应该采用联表操作更合适

4.求每个学生的平均成绩，按照学生id分组再利用聚合函数avg

select student_id,avg(num) from score group by student_id having avg(num)>80;

5.将上一步求得的表与学生表连接到一起。

select student.sname,t1.avg_num from student inner join (select 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;
# 这里可以把上一步求出来的SQL起一个别名，这样不光方便编写语句，也不会影响MySQL内部的逻辑判断

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

思路：

　　1.该题需要四张表：teacher,course,score,student

　　2.先求出报了李平老师课程的学生id

　　3.再去学生表中取反操作获取没有报李平老师课程的学生姓名

步骤（子查询）：

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.查询没有同时选修物理课程和体育课程的学生姓名（只要报了一门的，两门都报和都不报的都不要）

思路：

　　1.该题需要三张表：course、score、student

　　2.首先要在课程表通过课程名寻找课程id

　　3.然后在分数表中寻找学生id

　　4.最后在学生表中寻找学生名

步骤：

1.先获取物理和体育课程的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 * 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.查询挂科超过两门（包括两门）的学生姓名和班级

思路：

　　1.该题需要三张表：score、student、class

　　2.在分数表中寻找挂科超过两门的学生id

　　3.在学生表中寻找学生姓名和class_id

　　4.在班级表中寻找班级名

步骤：

1.先筛选出所有num＜60的数据

select * from score where num < 60;

2.按照学生id分组，统计每个学生挂科的次数，筛选出两门以上的

select * 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);

4.将步骤3的表和class连起来

select class.caption,t1.sname from class inner join (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)) as t1 on class.cid = t1.class_id;

python代码操作MySQL

　　其实不光是Navicat或者其他可视化数据库软件，使用python一样也可以操作MySQL。不过，python代码操作MySQL需要借助于第三方模块。

　　所谓第三方模块，其实本质也是模块，只不过是由其他非官方程序员编写并发布到互联网上的。

　　如果想要使用第三方模块，需要基于网络先下载。

下载模块准备工作

　　想要下载python模块，需要使用pip命令。可以直接先在cmd中使用看看能否开启下载功能。

pip3

　　没有打开也不用惊慌，这是因为没有把pip命令添加到环境变量中。

　　那么现在就从头开始来示范一遍。

　　首先确认一下，现在pycharm中使用的python解释器是什么版本。可以通过pycharm查看到。

　　进入pycharm后，在左上角File选项中选择带扳手图标的Settings。

　　之后点击Project选项卡下的Python Interpreter，可以看到pycharm使用的python解释器的地址。我们不需要看它写出的最终地址，只需要知道python解释器在哪里就可以了。

　　随后按图索骥找到python的安装地址，打开。其中会有一个叫Scripts的文件夹。

　　在上方的地址框复制目录地址，然后添加到系统的环境变量中。

　　接下来再回到cmd，重新输入pip命令。如果打印出一串列表，那么就表示没问题了。

下载pymysql模块

　　接下来就可以下载模块了。可以用python操控MySQL的模块名字叫“pymysql”。

方式1：cmd终端

pip3 install 模块名

　　黄字部分是在提示你pip3文件版本老了，可以更新最新版本。如果pip不是最新版的话，可能某些模块就无法下载了。

　　虽然在最开始学习时强调过软件最好不用最新版，不过pip3文件还是推荐更新到最新版。

　　想要更新的话，只要把黄字中用单引号包起来的字段直接复制黏贴就可以了。

python -m pip install --upgrade pip

　　如果有时候下载失败，黄字中出现Time Out字样时，说明网速有问题，在下载外国网站资源很有可能出现，不过只要重新下载就可以。

　　想要查看版本，还是回到pycharm的Project选项卡下选择Settings，Python Interpreter，就可以看到pip已经更新为最新版本了。

方式2：pycharm终端

　　在pycharm左下角有个Terminal选项卡，点击之后可以在这里通过输入指令来下载模块，代码还是一样的。

python -m pip install --upgrade pip

方式3：pycharm快捷方式

　　还是在Project选项卡下选择Settings，Python Interpreter。点击左上角的+号或者双击列表中的任意一个模块，就会弹出一个模块下载对话框。在搜索框中输入需要的模块，找到之后点击左下角的Install Package即可下载。

远程仓库

　　pip3工具下载模块时，默认都是从国外的仓库下载模块数据，下载的过程有时候会非常的慢，我们可以切换到国内的仓库。

1.命令临时切换

pip3 install 模块名 -i 仓库地址

　　国内仓库地址怎么找？百度一下你就知道。

（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/

　　使用国内仓库的好处是：缩短下载模块的耗时，尤其是大型模块。

2.pycharm更改仓库地址

　　在Project选项卡下选择Settings，Python Interpreter。点击左上角的+号或者双击列表中的任意一个模块，就会弹出一个模块下载对话框。

　　默认使用的是国外地址，我们现在复制上去一个国内仓库地址，完毕后会有两个网站。将来下载其他模块时就可以选择是从国外仓库下载模块还是从国内仓库下。

　　自此开始，在搜索一个模块时就会显示两个同名的模块，仔细看后面的地址会发现它们是不一样的。

3.永久更改

　　还可以永久更该默认的仓库地址，不过这需要修改python解释器内置的配置文件（较繁琐，不推荐）。

pip3下载模块报错

　　在下载模块时，有可能会遇到多种报错。不过不用担心，有些报错并不严重。

1.报错信息中含有timeout关键字

　　原因：当前计算机网络不稳定

　　措施：多执行几次或者更换网络

2.报错信息中含有warning警告版本过低

　　原因：pip工具版本过低，需要更新

　　措施：直接拷贝提示的更新命令，更新pip即可

3.报错信息中没有任何关键字，只有一堆红色字体

　　原因：可能是即将下载的模块对计算机环境有要求

　　措施：下载前要先准备好环境（具体步骤百度一下）

pymysql模块基本使用

　　pymysql模块下载完毕之后，import pymysql即可使用。

# 创建连接对象
import pymysql
conn = pymysql.connect(  # 将python和MySQL相连接，接下来输入服务端的参数
    host='127.0.0.1',
    port=3306,
    password='',
    user='root',
    database='db1',
    charset='utf8',
)
cursor = conn.cursor()  # 生成游标对象，相当于cmd中的光标，等待用户输入命令
sql = 'show tables'  # 自定义SQL语句
cursor.execute(sql)  # 执行SQL语句
res = cursor.fetchall()  # 获取执行结果
print(res)

　　如此一来，不用打开cmd或者Navicat，直接在python中就能操作MySQL了！

　　现在获取到的数据不多，看起来没什么感觉，但如果数据量很大的话，反馈的结果都挤在一团，完全无法分清各自对应的是什么数据。所以对于获取到的数据最好的呈现方式是字典。

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 生成游标对象，括号内是固定搭配，将获取到的数据转化成字典组成的大列表
sql = 'select * from emp'  # 自定义SQL语句
cursor.execute(sql)  # 执行SQL语句
res = cursor.fetchall()  # 获取执行结果
print(res)

　　这么做可以把数据库中的数据转变成一个个字典，让python能够通过操作基本数据类型的方法来操作MySQL中存储的数据。