开发工程师必备————【Day15】python操作Mysql及SQL语法补充

今日内容概要

• Navicat可视化软件
• 多表查询练习题
• python操作MySQL
• 小知识点补充

Navicat可视化软件

1.作用
Navicat可以充当很多数据库软件的客户端 提供了图形化界面能够让我们更加快速的操作数据库。

2.下载
navicat有很多版本 并且默认都是收费使用
下载地址：https://navicat.com.cn/

3.使用
内部封装了SQL语句 用户只需要鼠标点点点就可以快速操作
连接数据库 创建库和表 录入数据 操作数据
外键 SQL文件 逆向数据库到模型 查询(自己写SQL语句)
使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写
SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)

多表查询练习题

（1）查询所有的课程的名称以及对应的任课老师姓名
思路分析：

1.先确定需要几张表 课程表 老师表

2.简单查看每张表中的数据
select * from course;
select * from teacher;

3.思考查询逻辑  多表查询(连表操作)
SELECT
	course.cname,
	teacher.tname 
FROM
-	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;
1
2
3
4
5
6
7
8
9
10
11
12
13

（2）查询平均成绩大于八十分的同学的姓名和平均成绩
思路分析：

1.先确定需要几张表 成绩表 学生表

2.简单查看表中数据
select * from student;
select * from score;

3.先查询成绩表中平均成绩大于80分的数据
	3.1.按照学生编号分组 利用聚合函数avg求出所有学生编号对应的平均成绩
		select student_id,avg(num) from score group by student_id;
	3.2.基于上述分组之后的结果筛选出平均成绩大于80的数据
		select student_id,avg(num) from score group by student_id having avg(num) > 80;

/*针对select后面通过函数或者表达式编写的字段为了后续取值方便 一般需要重命名成普通字段*/-- select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;

4.最终的结果需要从上述sql语句的结果表中获取一个字段和学生表中获取一个字段
select * 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语句当做表来使用 连接的时候需要使用as起表名*/-- 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（3）查询没有报李平老师课的学生姓名
思路分析：

1.先确定需要用到的表 老师表 课程表 分数表 学生表

2.简单的查看表中数据

3.解题思路:
思路一：直接查其他老师教的课然后一步步查到学生
思路二：查报了李平老师课的学生编号然后取反即可(推荐)

4.先获取李平老师教授的课程id号
select tid from teacher where tname='李平老师'

5.子查询获取课程编号
select cid from course where teacher_id=(select tid from teacher where tname='李平老师')

6.根据课程编号去成绩表中筛选出所有报了课程编号的数据
select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'))

7.根据上述学生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 = '李平老师' )) )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

（4）查询没有同时选修物理课程和体育课程的学生姓名(只要选了其中一门的 两门都选和都没选的都不要)

1.先确定需要几张表

2.简单的查看表里面的数据

3.先获取物理和体育课程的id号
select cid from course where cname in ('物理','体育');

4.根据课程的id号先去成绩表中过滤掉没有选择这些课程的数据
select * from score where course_id in (select cid from course where cname in ('物理','体育'))

5.基于上述表统计每个学生编号报了几门课
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

6.根据上述学生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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

（5）查询挂科超过两门(包括两门)的学生姓名和班级
思路分析：

1.先筛选出分数小于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.先连接班级表和学生表
select * from class inner join student on class.cid = student.class_id;

4.合并23的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 );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

python操作Mysql

首先需要导入第三方模块：pip3 install pymysql

1.基本使用

import pymysql
# 1.链接服务端
conn = pymysql.connect(
   host='127.0.0.1',     # MySQL服务端的IP地址
   port=3306,           # MySQL默认PORT地址(端口号)
   user='root',         # 用户名
   password='123',        # 密码  也可以简写 passwd  
   database='db5',        # 库名称  也可以简写 db
   charset='utf8mb4',         # 字符编码 千万不要加杠utf-8
   autocommit=True  # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
sql1 = 'select * from userinfo'
# 4.发送给服务端
cursor.execute(sql1)
print(affect_rows)  # 执行SQL语句之后受影响的行数
# 5.获取命令的执行结果
res = cursor.fetchall()
print(res)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2.获取游标——conn.cursor()
要想操作数据库，光连接数据是不够的，必须拿到操作数据库的游标，才能进行后续的操作，比如读取数据、添加数据。通过获取到的数据库连接实例conn下的cursor()方法来创建游标。游标用来接收返回结果

cursor.fetchone()   # 获取结果集中一条数据
cursor.fetchall()   # 获取结果集中所有数据
cursor.fetchmany()  # 获取结果集中指定条的数据
'''类似于文件光标的概念'''
# cursor.scroll(2, mode='relative')  # 基于当前位置往后移动
cursor.scroll(0, mode='absolute')  # 基于数据集开头的位置往后移动
1
2
3
4
5
6

3.SQL注入问题
（1）前戏：只需要用户名即可登录；不需要用户名和密码也能登录。
（2）问题：SQL注入

	select * from userinfo where name='jason' -- haha' and pwd=''
	select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''

本质
	利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑

措施
	针对用户输入的数据不要自己处理 交给专门的方法自动过滤
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password))  # 自动识别%s 并自动过滤各种符合 最后合并数据

补充
cursor.executemany()
1
2
3
4
5
6
7
8
9
10
11
12
13

4.二次确认
数据的增删改查四个操作是有轻重之分的
查 不会影响真正的数据 重要程度最低
增、改、删 都会影响真正的数据 重要程度较高
pymysql针对增、改、删三个操作 都设置了二次确认 如果不确认则不会真正影响数据库

• 方式一：代码直接编写

affect_row = cursor.execute(sql)
conn_obj.commit()  # 手动二次确认
1
2

• 方式二：配置固定参数

conn_obj = pymysql.connect(
	autocommit=True  # 自动二次确认
)
1
2
3

小知识点补充

1,as语法
给字段起别名，起表名

2.comment语法
给表，字段添加注释信息

create table server(id int) comment '这个server意思是服务器表'
create table t1(
   	id int comment '用户编号',
      name varchar(16) comment '用户名'
) comment '用户表';
   
"""
查看注释的地方
		show create table 
		use information_schema
"""
1
2
3
4
5
6
7
8
9
10
11

3.concat，concat_ws语法
concat用于分组之前多个字段数据的拼接
concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码

4.exists语法

select * from userinfo where exists (select * from department where id<100)
1

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