Day42

今日内容：

　　1.子查询补充

　　2.正则表达式

　　3.pymysql

一、子查询补充

什么是子查询？

　　　　将上一次查询的结果作为下一次查询的条件或原数据

　　　　又称为内查询

　　　　作用：当你的需求，一次查询无法满足的时候（也就是一次select找不到你要的数据）

　　　　注：子查询能实现的效果，多表联查也可以是实现

实例演示：

准备数据

员工表：
create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);
insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(10,"刘备2","男",26,2,"总监",5800),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);
 
部门表：
create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");

问1：财务部有哪些人？

　　第一步我们需要直到财务部的id

　　select id from dept where name ="财务";

第二步我们用查询到的id作为判断条件来查询emp实现效果（用关键字in来实现子查询）

问2查询平均年龄大于25的部门名称

　　第一步先求出每个部门的平均年龄

　　select dept_id from emp group by dept_id having avg(age)>25;

第二步通过得到的部门id去部门表中查询

exists关键字子查询

　　exists 后跟子查询 子查询有结果为True ,没有结果为False(为True时外层执行，为False外层不执行)

　　select *from emp where exists (select *from emp where salary > 1000);

注：子查询的语法特点：一个select  a  中 包含另外一个select  b（b只能位于a的where后面）

二、正则表达式

正则表达式用于模糊查询，模糊查询已经讲过了

　　like 仅支持 % 和 _远没有正则表达式灵活

　　当然绝大多数情况下 like足够使用

　　语法：select *from table where name regexp "正则表达式";

实例示范：

　　select * from emp where name regexp "^刘";

select * from emp where name regexp "司+";

select * from emp where name regexp "备$";

三、pymysql模块

1.链接.执行sql,关闭(游标)

# 创建链接得到一个链接对象
conn = pymysql.Connect(
    host="127.0.0.1",  # 数据库服务器主机地址
    user="root",  # 用户名
    password="admin",  # 密码
    database="day42",  # 数据库名称
    port=3306,  # 端口号 可选 整型
    charset="utf8"  # 编码  可选
)
# 获取游标对象  pymysql.cursors.DictCursor指定 返回的结果类型 为字典  默认是元祖类型
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 查询数据
sql = "select *from emp"

# 执行sql  如果是select 语句返回的是查询成功的记录数目
res = cursor.execute(sql)
print(res)

# 关闭链接
cursor.close()
conn.close()

2.execute()之sql注入

　　注意:符号"--"会注释掉它之后的sql,正确的语法:"--"后至少有一个任意字符

　　根本原理:就根据程序的字符串拼接name='%s',输入一个xxx'-- haha,用输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

最后那一个空格，在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了

#1、sql注入之：用户存在，绕过密码
egon' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字

解决方法:

# 原来是我们对sql进行字符串拼接
# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
# print(sql)
# res=cursor.execute(sql)

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

3.增.删.改:conn.commit()

import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
#游标
cursor=conn.cursor()

#执行sql语句
#part1
# sql='insert into userinfo(name,password) values("root","123456");'
# res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
# print(res)

#part2
# sql='insert into userinfo(name,password) values(%s,%s);'
# res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数
# print(res)

#part3
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数
print(res)

conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()

4.查:fetchone,fetchmany,fetchall

import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
#游标
cursor=conn.cursor()

#执行sql语句
sql='select * from userinfo;'
rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动
# cursor.scroll(3,mode='relative') # 相对当前位置移动
res1=cursor.fetchone()
res2=cursor.fetchone()
res3=cursor.fetchone()
res4=cursor.fetchmany(2)
res5=cursor.fetchall()
print(res1)
print(res2)
print(res3)
print(res4)
print(res5)
print('%s rows in set (0.00 sec)' %rows)



conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()

'''
(1, 'root', '123456')
(2, 'root', '123456')
(3, 'root', '123456')
((4, 'root', '123456'), (5, 'root', '123456'))
((6, 'root', '123456'), (7, 'lhf', '12356'), (8, 'eee', '156'))
rows in set (0.00 sec)
'''