多表查询练习和pymysql

数据导入：

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;

# 练习题!!!（注重思路）

1.该题涉及到几张表?
2.需要的数据要用连接查询还是子查询？
3.最后确定select后需要的字段名称

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

# 用到两张表格——老师表和课程表

# 连接查询

# select后面加course.cname,teacher.tname

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

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

# 成绩表与学生表与分数表

# 分析题意可知先求解分数表中平均成绩大于80分的学生id号，由于最终的需求是学生姓名和第二步中的平均成绩 所以此处应该采用连表操作更合适

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

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

将上述SQL求解出来的表与学生表连接到一起，得出：

select student.sname,t1.avg_num from student inner join (select student_id,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去分数表中找到相应的学生id，去重!再用剩下的学生id去对应成学生姓名。
'以上去重为错误思路'(误以为去重将两个相同都会去除)

下面为正确解题思路：

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

# 需要用到成绩表，学生表，班级表
# 先查询成绩小于六十的学生id
　　select student_id from score where num < 60;

# 再计数，取大于等于2的学生id
　　select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 连接学生表，取学生姓名和班级id,再连接班级表，用班级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);

# 最后剩下学生名字和班级名字
　　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代码操作MysSQL

# python代码操作MySQL需要借助于第三方模块
第三方模块
本质也是模块只不过是网络上其他人写的

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

'''python如何下载模块'''
pip3 # 环境变量 python解释器文件夹下的scripts目录

'''下载模块的基本语句'''
1.方式1:cmd终端
pip3 install pymysql
2.方式2:pycharm终端
pip3 install pymysql
3.方式3:pycharm快捷方式
settings>>>如下图

 

 

 

'''远程仓库'''
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.命令临时切换
pip3 install 模块名 -i 仓库地址
2.pycharm更改仓库地址
settings 下载模块界面下方点击manage...
3.永久更改
需要修改python解释器内置的配置文件

'''pip3下载模块报错'''
1.报错信息里面含有timeout关键字
原因:当前计算机网络不稳定
措施:多执行几次或者更换网络
2.报错信息里面含有warning警告版本过低
原因:pip3工具版本过低需要更新
措施:直接拷贝提示的更新命令更新即可
3.报错信息里面没有任何关键字就是一堆红色字体
原因:可能是即将下载的模块对计算机环境有要求
措施:下载之前需要先准备好环境(百度搜一下)

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)