python cursor执行多个查询sql python多表联合查询

多表查询练习题

/*
 数据导入：
 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;

View Code

将数据表创建为sql文件

 

 

navicat中导入sql文件

首先建立连接,选择mysql

 

 

 

输入密码

 

 

右键新建数据库,字符编码选择 utf8mb4

 

 

 

右键 t2 运行sql文件,文件选择建好的sql文件 刷新表 数据内容关系如下:

 

 

 

 

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

 

 

 

 解题思路要先考虑要到哪几张表 这道题用到了老师表与课程表,做inner join 拼接 课程表中的teacheri_id对应教师表中的id

然后在根据题目意思 把获取到的数据表格看做一张新的数据表 ,从中获取想要的 课程名和老师名

 

 

 

 

 

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

第一步 考虑需要用到哪两张表  学生表&成绩表

第二步 先获取学生表中的平均成绩

select student_id,avg(num) from score group by student_id

 

 

 第三步 获取大于八十分的同学

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

 

 

 第四步 做连表操作 与学生表进行相连 需要给这个avg(num)重命名一个名字,不然在取值时 因为是个函数 而不是一个名字 会报错

select * from student inner join(select  student_id,avg(num) as av_num

 

 

 第五步 将* 替换成想要的数据

 

 

   

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

 

 

 第一步 需要用到课程表 学生表 老师表 成绩表 

第二步 获取李平老师的授课id号

select tid from teacher where tname='李平老师'; 

 

 

 

 第三步 从课程表中获取李平老师教课的id号

select cid FROM course where teacher_id=(select tid from teacher where tname='李平老师');  

 

 

 第四步 从成绩表中获取选了李平老师课程的学生id 并进行去重

SELECT DISTINCT student_id FROM score where course_id in( select cid FROM course where teacher_id=(select tid from teacher where tname='李平老师'));

 

 

 第五步 与student 表做对比 筛选出没有选李平老师的课的学生的名字

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='李平老师')));

View Code

 

 

 

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

       (只要报了一门的 两门都报和都不报的都不要)

第一步  需要用到课程表,学生表,成绩表

第二部 先获取 体育和物理的编号 

select cid from course where cname in('物理','体育');

 

 

 第三步 获取报了物理和体育 以及都报了的学生编号

select student_id from score where course_id in(select cid from course where cname in('物理','体育'));

 

 

 第四步 获取只报了体育或者物理的学生id

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. 查询挂科超过两门(包括两门)的学生姓名和班级

第一步 需要用到 学生表 成绩表 班级表

第二步 先获取所有挂科的人

select student_id from score where num<60;

 

 

 第三步 在对挂了两次的人进行分组筛选

 

 

 

 

 

 第四步 获取姓名 以及班级

 

 

 

 

 

 

第五步 对应班级表 做拼接

 

 

 

 

 

用 python操作mysql

python 代码操作mysql 需要借助第三方模块

 从本质上来说 第三方模块也是模块,网上其他人写的模块

如果需要使用就要从网络中下载,就类似于打一个dlc

首先需要使用一个工具 pip

打开 cmd 输入pip 

 

 

 

打开pycharm 

查看 现在使用的python 解释器是哪个

 

 

 找到路径D:\Python\Scripts添加到环境变量中去

下载模块的基本语句

1.通过cmd终端 输入

 pip3 install pymysql  即可下载

2.通过pychram 下载

 

 

 点击 terminal 输入 pip3 instal pymysql

 

 

 再输入python -m pip install --upgrade pip更新到最新版本

 

 

关于远程工具

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/

命令临时切换

pip3 install 模块名 -i 网站地址

pycharm 更改仓库地址

 

 

 点击下方

 

 

 

 

 #在下载模块中报错信息里有timeout关键字意为网络连接不稳定 

warning 警告版本过低  可以直接拷贝提示的更新命令 更新就行

如果是一堆红字的警告 可能是环境出了问题

pymsql模块

 

 

import pymysql

# 创建连接对象
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='t2',
    charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor #固定搭配 可以显示每一个数据对应的内容并且以列表套字典的形式打包返回方便以后for 取值)  # 生成游标对象 等待用户输入命令
# 自定义sql语句
sql = 'select * from teacher'
# 执行sql语句
cursor.execute(sql)
# 获取执行的结果
res = cursor.fetchall()
print(res)