mysql关联表更新可视化_1.多表查询 => 转化为一张联合大表 2.可视化工具 3.pymysql模块...

多表数据

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;

'''

左连接

'''

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;

'''

右连接

'''

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 on语法，但可以通过去重达到效果

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;

其中union就是将两个表连接起来并达到去重的效果

'''

练习

'''

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.查询每一个部门下的员工们及员工职责

分析:

每一个部门=>那么部门的信息要被全部保留并且需要分组

员工职责=>dep.work,由于分组不能直接被查询=>需要用聚合函数处理

员工们=>emp.name做拼接=>group_concat(emp.name)

分组的字段=>部门=>emp.dep_id

左表如果为emp表,那么右表的信息要被全部保留,使用右连接

左表如果为dep表,那么左表的信息要被全部保留,使用左连接

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

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的优先级高于group by 所以on在group by 的左边

'''

navicat

'''

1. 安装navicat

2.连接数据库，并建库

3.创建表、设置字段、插入数据

4.新建查询

查询在上面点击有新建查询

'''

sql文件注释:单行注释 -- abc; 不会报错,有分号,--后要加空格

多行注释 /*abc*/ 不会报错,没有分号

# abc; 有分号,#后要加空格

python使用mysql

# 模块pymysql

# 按照并导入pymysql: pip3 insatll pymysql

# 通过pymysql操作数据库分四步:

'''

1.建立连接

conn = pymysql.connect(host="localhost", port=3306, db='db3', user='root', password='root') #user和密码必须加引号,密码是数字也要加引号

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() # fetch 拿取,第一次执行拿到第一条结果

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注入的写法

'''

import pymysql

conn = pymysql.connect(host='localhost',port=3306,user='root',password='940828',db='db3')

cursor = conn.cursor(pymysql.cursors.DictCursor)

#登录功能

#得到用户输入的账户密码

user = input('请输入用户名:').strip()

pwd = input('请输入密码:').strip()

#和数据库的账号密码进行比对

sql = 'select * from user where user="%s" and pwd="%s"' %(user,pwd)

#%s为什么要用双引号????如果后面数据是字符串形式必须加双引号,是数字形式可加可不加.所以必须加上

res=cursor.execute(sql)

if res:

print('登录成功!')

else:

print('登录失败!')

cursor.close()

conn.close()

'''

# sql注入

# 1.知道用户名: henry" -- hehe | ooo

# select * from user where usr="henry" -- hehe" and pwd="ooo" #这样是把-- 后面的代码当成注释

# 2.不知道用户名 aaa" or 1=1 -- hehe | 000

# select * from user where usr="aaa" or 1=1 -- hehe" and pwd="000" #第一个or判断为真,-- 后面的代码当成注释

# 处理sql注入

# 处理方式

# 对输入的账户密码做完全处理 => 不可能形成达到sql注入的特殊语法 => 正则

sql = 'select * from user where usr=%s and pwd=%s'

res = cursor.execute(sql, (usr, pwd))

#这个%s为什么不用双引号?用双引号会报错!

增删改

# 增

# 增sql语句

sql1 = 'insert into user(user, pwd) values (%s, %s)'

#cursor执行sql语句,在内存中完成了对数据的插入, 但不能将数据存放到硬盘

# 会将id完成自增,如果第一次运行没有将数据放到硬盘,那么接下来运行并将数据写到硬盘id会跳过一位.

# 在内存中一次插入一条

cursor.execute(sql1, ("opq", "123"))

# 在内存中一次插入多条

cursor.executemany(sql1, [("aaa", "000"), ("bbb", "111")])

# 将内存中的数据提交到硬盘中

conn.commit()

#删

sql2 = 'delete from user where user=%s'

cursor.execute(sql2,('lzq'))

conn.commit()

#改

sql3 = 'update user set pwd=%s where user=%s'

cursor.execute(sql3,('wade','wade'))

conn.commit()