day06--Navicat可视化界面/数据库查询题目讲解（多表操作）/pymysql模块-sql注入以及解决办法

一：连表查询复习

题目：查询平均年纪在25岁以上的部门名称

#只要是多表查询就有两种方法 连表和子查询

方法1：连表
select depart.name from depart inner join emplo on emplo.dep_id =depart.id  group by depart.name  having avg(age)>25;

+--------------+
| name         |
+--------------+
| 人力资源     |
| 销售         |
+--------------+


方法2：子查询
select name from depart where id in (select dep_id from emplo group by dep_id having avg(age)>25);

+--------------+
| name         |
+--------------+
| 人力资源     |
| 销售         |
+--------------+

二：Navicat可视化界面

1.使用技巧和注意事项

（1）外键的添加是用空格

（2）运行和转储SQL文件，逆向数据库到模型

运行和转储SQL文件：是将外部的打开或者转存出去；逆向数据库到模型：呈现出表和表关系

（3）添加外键

右键表名--设计表--添加外键--选择即可

（4）自己写sql语句

注释是ctrl+？右键选中命令单独运行命令

三：pymysql模块

（1）pymysql的安装

cmd管理员打开后--输入pip3 install pymysql 确认安装成功的话在python中输入 import pymysql 就可确认

（2）pymysql 在py中的使用

import pymysql
#链接数据库
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',
    database='db1'
)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)    #产生一个游标对象，用来帮我们执行命令的,括号内是查询结果以字典的方式返回
sql='select *from emp;'
res=cursor.execute(sql)                                  #此条必须输入
print(res)   #返回的是你当前sql语句的行数
print(cursor.fetchone())   #只返回一条
print(cursor.fetchall())  #返回所有的
print(cursor.fetchmany(2))  #可以指定拿几条，结果是空的，类似于文件的光标，此时文件光标在最后，所以内容是空的

（3）sql注入以及解决办法

import pymysql
#链接数据库

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',
    database='db1'
)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)    #产生一个游标对象，用来帮我们执行命令的,括号内是查询结果以字典的方式返回

username=input('>>>')
password=input('>>>')

sql='select *from user where name=%s and password=%s'
#不要手动的去拼接数据，先用%s进行占位，之后需要拼接数据直接交给 execute 方法即可
print(sql)

rows=cursor.execute(sql,(username,password))

if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('用户名密码错误')

（4）对mysql的增删改

import pymysql
#链接数据库
改：
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',
    database='db1',
    autocommit=True
)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)    #产生一个游标对象，用来帮我们执行命令的,括号内是查询结果以字典的方式返回

sql="update emplo set name='zz' where id=1;"
print(cursor.execute(sql))

增：
单个的增加

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)    #产生一个游标对象，用来帮我们执行命令的,括号内是查询结果以字典的方式返回

sql="insert emplo values(7,'mm','male',19,201)"
print(cursor.execute(sql))

多个的

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)    #产生一个游标对象，用来帮我们执行命令的,括号内是查询结果以字典的方式返回

sql="insert emplo(name,password) values(%s,%s)"
rows=cursor.executemany(sql,[('zz',123),('mm',456)])
print(rows)