day43 多表查询和pymysql

复习

增删改查全语法

# 增
insert into db1.t1(字段2, 字段1, ..., 字段n)|省略 values (值2, 值1, ..., 值n)|(值1, 值2, ..., 值n)[, (值2, 值1, ..., 值n)|(值1, 值2, ..., 值n), ...]

# 删除
delete from db1.t1 [条件]
truncate db1.t1

# 改
update db1.t1 set 字段1=值1, ..., 字段n=值n [条件]

# 查
select [distinct] from db1.t1
	where 条件
    group by 分组
    having 筛选
    order by 排序
    limit 限制

作业

# 1
# 按教学部作为分组依据 => 分组dep
select dep, max(area), avg(salary) from emp where area = "山东" group by dep having dep = "教学部";

select max(dep), area, avg(salary) from emp where dep = "教学部" group by area having area = "山东";

select max(dep), max(area), avg(salary) from emp where dep = "教学部" and area = "山东";

# 2
select * from emp where area = "上海" and name like '%n%';

# 3
select * from emp where name like '%n%' and name not regexp '.*[0-9].*';
    
# 4
select dep, avg(age) from emp group by dep order by avg(age) asc;

# 5
select * from emp order by age asc, name desc;

# 6
name, concat(area, "-", port) 在最大年龄基础上再进行查找
group by dep
max(age) => 子查询

select name, concat(area, "-", port), age, dep from emp where (age, dep) in (select max(age), dep from emp group by dep);

今日内容

1.多表查询 => 转化为一张联合大表

2.可视化工具

3.pymysql模块

4.内存管理

多表数据

create table dep(
	id int primary key auto_increment,
	name varchar(16),
	work varchar(16)
);
create table emp(
	id int primary key auto_increment,
	name varchar(16),
	salary float,
	dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);

笛卡尔积(交叉查询)

# 需求: 
# 查看每位员工的部门的所有信息
select * from emp;
select * from dep;

# 子查询, 最终结果只能显示单表的信息, 但需求是同时显示两张表的信息 => 先将两张表合成一张表
select * from emp where dep_id in (select id from dep);

'''
'''
笛卡尔积: 集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}
交叉查询: select * from emp, dep; | select * from emp course join dep;
'''

''' 做了筛选, 结果<=完整数据, 非笛卡尔积
select * from emp, dep where db2.emp.dep_id = db2.dep.id;  # 同sql语句上表现是从两张表拿数据
# 注意: 同时查询两张表形成新的表,可以称之为虚拟表, 原表与表之间可能存在重复字段, 同时使用时需要明确所属表,必要时还需明确所属数据库

多表连接(*****) 虚拟的单表

inner join on

内连接：结果为两张表有对应关系的数据(emp有dep没有,emp没有dep有的记录均不会被虚拟表展示)

从两张表共有的部分筛选数据,不共有的部分不显示在结果中
语法：左表 inner join 右表 on 两表有关联的字段的条件, on就是产生对于关系的(连接的依据)
eg：select * from emp inner join dep on emp.dep_id = dep.id;
上面sql语句就相当于:
select * from emp, dep where emp.dep_id=dep.id;

外连接之左连接(在左连接基础上显示左表有数据右边没对应数据的记录)

left join on
左连接：在内连接的基础上还保留左表特有的记录
语法：左表 left join 右表 on 两表有关联的字段的条件
eg：select emp.name '员工', dep.name '部门', dep.work '职责' from emp left join dep on emp.dep_id = dep.id;

select * from dep left join emp on emp.dep_id=dep.id;
会显示左表内所有数据,有对应的右表信息就会显示在左表的右侧,没有的话,右侧就位null填充多余字段

外连接之右连接(和左连接对应,会显示右表有数据,左表没有数据的记录)

right join on
右连接：在内连接的基础上还保留右表特有的记录
语法：左表 right join 右表 on 两表有关联的字段的条件
eg：select * from emp right join dep on emp.dep_id = dep.id;

在连接语法join 前就是左表, 后就是右表
采用的是left关键词就是左连接, right关键词就是右连接, inner关键词就是内连接

全部连接

全连接是在内连接的基础上单独保留了左表和右表独有的记录
语法:mysql没有full join in的语法,但可以通过去重达到效果
eg：
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
以上是一条结合语句,合并左连接和右连接数据

练习

1.查询每一位员工对应的工作职责
# 每一位员工 => 左表为emp表, 那么左表的所有数据均需要被保留, 所有采用左连接
		   => 左表为dep表, 那么右表的所有数据均需要被保留, 所有采用右连接
# select emp.name, dep.work from emp left join dep on emp.dep_id = dep.id;
select emp.name, dep.work from dep right join emp on emp.dep_id = dep.id;

2.查询每一个部门下的员工们及员工职责
# select max(dep.name), max(dep.work), group_concat(emp.name) from emp right join dep on emp.dep_id = dep.id group by dep_id;

# 分析过程
# 每一个部门 => dep的信息要被全部保留, 需要分组
# 员工职责 => dep.work, 由于分组不能直接被查询 => 需要用聚合函数处理
# 员工们 => emp.name做拼接 => group_concat(emp.name)
# 分组的字段 => 部门 => emp.dep_id => emp.dep_id可以直接被查询,但没有显示意义 => dep.name用来显示 => dep.name需要用聚合函数处理


select max(dep.name), max(dep.work), group_concat(emp.name) from dep left join emp on  emp.dep_id = dep.id group by emp.dep_id;

# 注: on在where条件关键词之左(前面)

navicat(图形化界面)

1. 安装navicat
2.连接数据库，并建库
3.创建表、设置字段、插入数据
4.新建查询

python使用mysql

# 模块pymysql
# 按照并导入pymysql: pip3 insatll pymysql
# 通过pymysql操作数据库分四步:
'''
1.建立连接
conn = pymysql.connect(host="localhost", port=3306, db='db2', user='root', password='root')

2.设置字典类型游标(这样可以把数据存成字典形式进行展示)
cursor = conn.cursor(pymysql.cursors.DictCursor)

3.执行sql语句并使用执行结果

# 书写sql语句
sql = 'select * from emp'
# 执行sql语句, 有返回值, 返回值为得到的记录行数
line = cursor.execute(sql)
print(line)

# 使用执行的结果: 
		fetchone())当前游标往后获取一行记录 
		fetchall()当前游标往后所有的记录
		scroll(num, mode="relative|absolute")
				relative: 游标从当前位置往后移动num行
				ablolute: 游标从头往后移动num行, 一般可以结合line来使用能定位到任意位置
tag = cursor.fetchone() # 第一条
print(tag)
print(tag['salary'])
tag = cursor.fetchone() # 第二条
print(tag)
cursor.scroll(1, mode='relative') # 按当前位置做相对偏移,第一个参数表示偏移数字个数偏移掉第三条,去取第四条
# cursor.scroll(line - 1, mode='absolute') # 指针绝对偏移,相对于数据的开头进行偏移 ,游标永远从头开始偏移
tags = cursor.fetchall() # 第四条到最后
print(tags)

4.断开连接
cursor.close()
conn.close()

pymysql处理了sql注入

# 什么是sql注入:
# 通过书写sql包含(注释相关的)特殊字符, 让原有的sql执行顺序发生改变, 从而改变执行得到的sql
# 目的:
# 绕过原有的sql安全认证, 达到对数据库攻击的目的

# 没有处理sql注入的写法
sql = 'select * from user where usr="%s" and pwd="%s"' % (usr, pwd)
res = cursor.execute(sql)

# sql注入(因为--语法是sql语句的注释内容,导致后续输入内容被当作注释)
# 1.知道用户名:  abc" -- hehe | ooo
# select * from user where usr="abc" -- hehe" and pwd="ooo"
# 2.不知道用户名 aaa" or 1=1 -- hehe | 000
# select * from user where usr="aaa" or 1=1 -- hehe" and pwd="000"

# 处理sql注入
sql = 'select * from user where usr=%s and pwd=%s' # 用占位符写好sql语句
res = cursor.execute(sql, (usr, pwd)) # 使用pymsql模块的执行方式进行传参,该模块会有一定的防sql注入的保护措施

增删改

# 增
# 增sql语句
sql1 = 'insert into user(usr, pwd) values (%s, %s)'
# 在内存中一次插入一条
cursor.execute(sql1, ("opq", "123"))
# 在内存中一次插入多条
cursor.executemany(sql1, [("aaa", "000"), ("bbb", "111")])
# 将内存中的数据提交到硬盘中
conn.commit()

# 删
sql2 = 'delete from user where usr=%s'
cursor.execute(sql2, ("aaa"))
conn.commit()

# 改
sql3 = 'update user set pwd=%s where usr=%s'
res = cursor.execute(sql3, ("222", "bbb"))
conn.commit()