python学习 day43之MySQL之多表查询与pymysql模块

多表连接查询

外链接语法（重点）

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

交叉连接：不适用任何匹配条件。生成笛卡尔积

select * from t1,t2;

内连接：只连接匹配的行

找两张表共有的部分(外链ID)，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

select t1.id,t1.name,t2.name from t1 inner join t2 on t1.t2_id = t2.id; 

# 同等于：
select t1.id, t1.name, t2.name from t1,t2 where t1.t2_id = t2.id;

外链接之左连接：优先显示左表全部记录

以左表为准，即找出所有员工信息，当然包括没有部门的员工
本质就是：在内连接的基础上增加左边有右边没有的结果

select t1.id,t1.name,t2.name as t2_name from t1 left join t2 on t1.t2_id=t2.id;

外链接之右连接：优先显示右表全部记录

以右表为准，即找出所有部门信息，包括没有员工的部门
本质就是：在内连接的基础上增加右边有左边没有的结果

select t1.id,t1.name,t2.name as t2_name from t1 right join t2 on t1.t2_id=t2.id;

全外连接：显示左右两个表全部记录

在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
注意：mysql不支持全外连接 full JOIN
mysql可以使用如下方式间接实现全外连接

select * from t1 left join t2 on t1.t2_id = t2.id
union
select * from t1 right join t2 on t1.t2_id = t2.id
;

• union与union all的区别：union会去掉相同的纪录

符合条件连接查询

示例1：

以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

示例2：

以内连接的方式查询employee和department表，并且以age字段的升序方式显示

select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;

子查询

理论知识

• 1：子查询是将一个查询语句嵌套在另一个查询语句中。

• 2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

• 3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

• 4：还可以包含比较运算符：= 、 !=、> 、<等

带IN关键字的子查询

注意：not in 不支持 null

#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

带ANY关键字的子查询

在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。

ANY 和 IN 运算符不同之处1

ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义

例如：使用 IN 和使用 ANY运算符得到的结果是一致的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);

select * from employee where salary in (
select max(salary) from employee group by depart_id);

结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符

ANY和 IN 运算符不同之处2

ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的

SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005) 

带ALL关键字的子查询

all同any类似，只不过all表示的是所有，any表示任一

查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);


查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);


查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工
select * from employee where salary < any ( select avg(salary) from employee group by depart_id); 


查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any (
select avg(salary) from employee group by depart_id); 

带比较运算符的子查询

比较运算符：=、!=、>、>=、<、<=、<>

#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录,
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

select * from t1 where exists (select id from t2 where id=200);
# 当t2中id不存在200时，这行命令不会执行

in与exists深度剖析

！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
==============================exists==============================

exists进阶

exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，
否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。

案例示例

查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)

exists的执行原理为：

• 1、依次执行外部查询：即select * from class
• 2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
• 3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录

==============================in==============================

in

in后跟的都是子查询，in()后面的子查询 是返回结果集的

案例示例：

查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')

in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

not in与 not exists

!!!not exists查询的效率远远高与not in查询的效率。!!!

==============================not in==============================

not in

not in()子查询的执行顺序是：
为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。

==============================not exists==============================

not exists

not exists：
如果主查询表中记录少，子查询表中记录多，并有索引。

例如:查询那些班级中没有学生的班级

select * from class

where not exists

(select * from student where student.cid = class.cid)

not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

pymysql模块

cmd安装：pip3 install pymysql

一 链接、执行sql、关闭（游标）

import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').strip()

#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon',charset='utf8')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)


#执行sql语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(res)

cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')

二 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的规矩来。

三 增、删、改：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()

四 查：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)
'''

五 获取插入的最后一条数据的自增ID

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
cursor=conn.cursor()

sql='insert into userinfo(name,password) values("xxx","123");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #在插入语句后查看

conn.commit()

cursor.close()
conn.close()