【MySQL】第5回 python操作MySQL

1. Navicat可视化软件

1.1 下载

下载Navicat Premium 尽量下载最新版本，功能比较多。(可自行百度下载安装)
Navicat Premium 下载路径：
链接：http://www.navicat.com.cn/download/navicat-premium
激活工具：
链接：https://pan.baidu.com/s/1pB5xh9CvFOnaWXWnjQX6zg?pwd=ir74
提取码：ir74

1.2 功能简介

Navicat premium是一款数据库管理工具,是一个可多重连线资料库的管理工具，它可以让你以单一程式同时连线到 MySQL、SQLite、Oracle、MariaDB、Mssql、及PostgreSQL 资料库，让管理不同类型的资料库更加的方便。
不同的资料库的批次处理任务也可以设定排程并在指定的时间执行。其他功能包括汇入/汇出精灵、查询建立工具、报表产生器、资料同步、备份、工作排程及更多。

2. 多表查询练习题

2.1 导入数据

粘贴复制下列语句，在Navicat Premium 库中右键选择运行SQL语句

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

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

读题先确定需要几张表：课程表，老师表
简单的查看由1得出的表数据
思考查询逻辑：多表查询（连表操作）

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

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

先确定有几张表：成绩表，学生表
简单的查看由1得出的表数据
先查询成绩表中平均成绩大于80分的数据
按照学生编号分组，利用聚合函数avg求出所有学生编号对应的平均成绩
基于上述分组之后的结果筛选出平均成绩大于80的数据
针对select后面通过函数或者表达式编写的字段为了后续取值方便一般需要重命名成普通字段
最终的结果需要从第六步SQL语句的结果表中获取一个字段和学生表中获取一个字段
将SQL语句当做表来使用连接的时候需要使用as起表名

select * from student inner join (select  student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80) as t on student.sid = t.student_id

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

先确定需要用到的表：老师表，课程表，分数表，学生表
简单的查看由1得出的表数据
两种解题思路：
1. 直接查看其他老师教的课然后一步步查看学生
2. 查报了李平老师课的学生编号然后取反即可（推荐）
先获取李平老师教授的课程id号
子查询获取课程编号
根据课程编号取成绩表中筛选出所有报了课程编号的数据
根据上述学生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='李平老师')))

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

先确定需要几张表：学生表，课程表，分数表
简单的查看由1得出的表数据
先获取物理和体育课程的id号
根据课程的id号先去成绩表中过滤没有选择这些课程的数据
基于上述表统计每个学生编号报了几门课
根据上述学生id获取学生姓名

select sname from student where sid in (select score.student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
group by score.student_id having count(score.course_id) = 1)

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

先确定需要几张表：学生表，课程表，分数表，班级表
简单的查看由1得出的表数据
先筛选出分数小于60的数据
按照学生id分组然后计数即可
先连接班级表和学生表
合并2，3的sql

select class.caption,student.sname  from class inner join student on class.cid = student.class_id where student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

3. python操作MySQL

3.1 第三方模块的下载两种方式

方式一：pip install 模块名==版本号
方式二：在pycharm中settings>>project中，或直接在py文件插入点击报错直接下载

3.2 python操作MySQL

基础代码，只进行查看服务,结果为列表套字典
如重新接收则打印是空列表

# 第三方模块：pip3 install pymysql


import pymysql

# 1. 链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
)
# 2. 产生一个游标对象（等待输入命名）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3. 编写sql语句
sql1 = 'select * from emp1'
# 4. 发送给服务端
cursor.execute(sql1)
# 5. 获取命名的执行结果
res = cursor.fetchall()
print(res)
res1= cursor.fetchall()
print(res1)

3.3 执行增、改、删操作，二次确认机制

autocommit=True # 执行增、改、删操作自动执行conn.commit 二次确认机制
执行结果5：res = cursor.fetchall()返回结果为（）
执行结果4：ret = cursor.execute(sql1)返回结果为执行sql语句受影响的行数
编写sql语句代码增、删、改

insert into t1(name,age) values("jason","18")
insert into t1(name,age) values("wnag","19")
update t1 set age=20 where id=2
delete from t1 where id=2

# 1. 链接服务库
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)
# 2. 产生一个游标对象（等待输入没命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3. 编写sql语句
sql1 = 'insert into t1(name,age) values("wnag","19")'
# 4. 发送给服务端
ret = cursor.execute(sql1)
print(ret)  # 返回执行sql语句受影响的行数
# conn.commit() # 执行数据的增、改、删操作，在这写不方便，容易忘，在上门面配置参数 autocommit=True 
# 5. 获取命令的执行结果
# res = cursor.fetchall()
# print(res)

3.4 获取结果

import pymysql

# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
    autocommit=True # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写sql语句
sql1 = 'select * from t1'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令2的执行结果
# res = cursor.fetchall()  # 获取结果集中所有数据
# res = cursor.fetchone()  # 获取结果集中一条数据
# res = cursor.fetchmany(3) # 获取结果集中指定条的数据
res = cursor.fetchone()
print(res)
# 类似于文件光标的概念
# cursor.scroll(1,mode='relative') # 基于当前位置往后移动
cursor.scroll(0,mode='absolute') # 基于数据集开头的位置往后移动
res = cursor.fetchall()
print(res)

cursor.fetchone() 获取结果集中一条数据
cursor.fetchall() 获取结果集中所有数据
cursor.fetchmany() 获取结果集中指定条的数据
cursor.scroll(2, mode='relative') 基于当前位置往后移动
cursor.scroll(0, mode='absolute') 基于数据集开头的位置往后移动

3.5 SQL注入问题

3.5.1 实现简答的登录功能

import pymysql

# 1. 链接服务器
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
    autocommit=True
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.获取用户数据
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 4.编写sql语句
sql = "select * from t1 where name='%s' and pwd='%s'"%(username,password)
print(sql)
# 5.执行sql语句
cursor.execute(sql)
# 6.获取结果
res = cursor.fetchall()
if res:
    print('登录成功')
    print(res)
else:
    print('用户名或密码错误')

3.5.2 前戏

只需要用户也可登录。不需要用户名和密码也能登录

3.5.3 问题 SQL注入

只需要用户名即可登录
select * from t1 where name='jason' -- 哈黑' and pass=''
不需要用户名和密码也能登录
select * from t1 where name='aaa' or 1=1 -- 嘿哈' and pass=''
在navicat查看

3.5.4 本质与措施

# mysql 三种注释语法
# 号注释
-- --注释
/* 注释*/
select * from t1 where name='jason' -- 哈黑' and pass=''
# where 后面条件如果为True 相当与没有条件
select * from t1 where name='aaa' or 1=1 -- 嘿哈' and pass=''

本质：利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
措施
措施：针对用户输入的数据不要自己处理交给专门的方法自动过滤

# # 4.编写sql语句
# sql = "select * from t1 where name='%s' and pass='%s'"%(username,password)
# print(sql)
# # 5.执行sql语句
# cursor.execute(sql)
# 4.编写sql语句
sql = "select * from t1 where name=%s and pass=%s"
print(sql)
# 5.执行sql语句
cursor.execute(sql,(username,password)) # 自动识别%s 并自动过滤各种符合 最后合并数据

3.5.4 一次性执行多条sql语句

cursor.executemany() 一次性执行多条sql语句
代码实例

import pymysql

# 1. 链接服务器
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
    autocommit=True
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'insert into t1(name,pass) values(%s,%s)'
# [(),(),()]
cursor.executemany(sql,[('jason1',111),('jason2',222),('jaosn',333)])

4. 知识点补充了解

4.1 as语法

给字段起别名，起表名

4.2 comment 语法

给表，字段添加注释信息

create table t3(id int primary key auto_increment comment '用户id', name varchar(32) comment '用户名') comment ' 用户表';
# 查看注释
 show create table t3;

2. 查看注释的地方

# 打开库
use information_schema
# 查看库里表
show tables; 
# 查看以名字筛选
select * from tables where table_name='t3'/G;

4.3 concat、concat_ws语法

concat用于分组之前多个字段数据的拼接
concat_ws如果有多个字段并且分隔符一致可以使用该方法减少代码（字段越多效果越明显）

4.4 exists语法

exists后面的sql语句如果有结果那么执行前面的sql语句，如果没有结果则不执行

5.作业练习

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

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

2、查询学生表中男女生各有多少人

select gender,count(1) from student group by gender;

3、查询物理成绩等于100的学生的姓名

select student.sname from student where sid in (select score.student_id from score inner join course on score.course_id = course.cid where course.cname = '物理' and score.num = 100);

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

select * from student inner join (select  student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80) as t on student.sid = t.student_id

5、查询姓李老师的个数

select count(tid) from teacher where tname like '李%';

posted @ 2022-08-18 22:37 |相得益张| 阅读(91) 评论(0) 收藏举报

刷新页面返回顶部

|相得益张|

人而不学其犹正墙面而立

【MySQL】第5回 python操作MySQL

1. Navicat可视化软件

1.1 下载

1.2 功能简介

2. 多表查询练习题

2.1 导入数据

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

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

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

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

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

3. python操作MySQL

3.1 第三方模块的下载两种方式

3.2 python操作MySQL

3.3 执行增、改、删操作，二次确认机制

3.4 获取结果

3.5 SQL注入问题

3.5.1 实现简答的登录功能

3.5.2 前戏

3.5.3 问题 SQL注入

3.5.4 本质与措施

3.5.4 一次性执行多条sql语句

4. 知识点补充了解

4.1 as语法

4.2 comment 语法

4.3 concat、concat_ws语法

4.4 exists语法

5.作业练习

公告

|相得益张|

人而不学 其犹正墙面而立

【MySQL】第5回 python操作MySQL

1. Navicat可视化软件

1.1 下载

1.2 功能简介

2. 多表查询练习题

2.1 导入数据

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

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

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

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

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

3. python操作MySQL

3.1 第三方模块的下载两种方式

3.2 python操作MySQL

3.3 执行增、改、删操作，二次确认机制

3.4 获取结果

3.5 SQL注入问题

3.5.1 实现简答的登录功能

3.5.2 前戏

3.5.3 问题 SQL注入

3.5.4 本质与措施

3.5.4 一次性执行多条sql语句

4. 知识点补充了解

4.1 as语法

4.2 comment 语法

4.3 concat、concat_ws语法

4.4 exists语法

5.作业练习

公告

人而不学其犹正墙面而立