联合分组、子查询、视图、事务、python操作mysql、索引,python与mysql, 目录
联合分组、子查询、视图、事务、python操作mysql、索引,python与mysql, 目录
目录
联合分组、子查询、视图、事务、python操作mysql、索引 一、联合分组 二、子查询 三、all 与any:区间修饰条件 四、视图:view 视图的增删改 五、事务 5.1、事务的概念 5.2、事务的四大特性 六、pymysql 模块:python操作mysql 6.1 安装pymysql 模块 6.2 python用pymysql 操作mysql步骤 6.3 游标操作 6.4 pymysql事务 6.5 sql注入 七、索引联合分组、子查询、视图、事务、python操作mysql、索引
一、联合分组
# 数据来源:在之前的单表emp下# 联合分组:按多个字段综合结果进行分组# 按area与port组合后的结果进行分组,只有分组后的结果还一致,才认为是一组
二、子查询
"""子查询语法: 增:insert into 表名 select子查询 删:delete from 表名 条件是select子查询结果 查:select 字段 from 表名 条件是select子查询 改:update 表名 set 字段 条件是select子查询"""
# 数据来源: 在单表emp下# 子查询:将一条sql查询的结果作为另一条sql查询的条件# 思考:查询每个部门最高薪资的那个人所有信息# 子查询的sqlmysql> select dep,max(salary) from emp group by dep;# 子查询的父查询mysql> select * from emp where (dep,salary) in (select dep,max(salary) from emp group by dep);# 将子查询转换为一张表mysql> create table t1(dep_name varchar(64),max_salary decimai(5,2)); # 创建一个存子查询的数据结果的一张表# 子查询--增加数据mysql> insert into t1 select dep,max(salary) from emp group by dep;# 需求mysql> select name,dep_name,salary from emp join t1 on emp.dep=t1.dep_name and emp.salary=t1.max_salary;# 子查询--修改数据(update更新的表不能与子查询select的表同表)mysql> update t1 set max_salary=max_salary+1; # 每个部门最大薪资+1mysql> insert into t1 values('打杂部',100); # 给t1增加一个新部门mysql> update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep from emp); update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep_name from t1); # 错误:update更新的表 与 子查询select的表 相同# 子查询--删除数据mysql> delete from t1 where dep_name in (select distinct dep from emp); mysql> delete from t1 where dep_name in (select distinct dep_name from t1); # 错误: delete删除的表 与 子查询select的表 相同
三、all 与any:区间修饰条件
"""语法规则:where id in (1,2,3) >>>> id是1或2或3where id not in (1,2,3) >>>> id不是1,2,3where salary < all(3,6,9) >>>> salary必须小于all后所有情况(也就是薪资必须小于最小的3)where salary > all(3,6,9) >>>> salary必须大于all后所有情况(也就是薪资必须大于最大的9)where salary < any(3,6,9) >>>> salary只要小于一种情况(也就是小于最大的9就行)where salary > any(3,6,9) >>>> salary只要大于一种情况(也就是大于最小的3就行)"""eg:mysql> select * from emp where salary < all(select salary from emp where id>11); # 薪资小于id=11的人的薪资的所有人的信息
四、视图:view
1、视图是存在内存中的临时表
2、视图的创建依赖select语句,所以就是select语句操作的结果形成的表
3、视图支持对数据的增删查改
4、视图不允许对视图表的字段做修改
5、视图不仅支持创建,也支持更新与删除
# 数据还是依赖之前的单表emp# 语法# 创建视图mysql> create view 视图名[(别名们)] as select 语句; # 视图的字段个数和约束条件与select查询的结果一样eg:mysql> create view v1 as select dep,max(salary) from emp group by dep;# 创建或替换视图mysql> create or replace view 视图名[(别名们)] as select 语句; # 没有就创建,有就修改mysql> alter view 视图名[(别名们)] as select 语句; # 视图存在才能修改eg:mysql> create or replace view v1(dep_name.max_salary) as select dep,max(salary) from emp group by dep;mysql> alter view v1(dep_name.max_salary) as select dep,max(salary) from emp group by dep;# 删除视图mysql> drop view 视图名eg:mysql> drop view v1;# 视图可以作为正常表完成连表查询mysql> select name,dep_name,salary from emp join v1 on emp.dep=v1.dep_name and emp.salary=v1.max_salary;
视图的增删改
"""前提:视图的增删改操作可以直接映射给真实表(本质就是对真实表进行操作)视图可以完成增删改,增删改本质是直接对创建视图的真实表进行操作"""eg:# 创建视图v2create or replace view v2 as select id,name,age,salary from emp;# 改update v2 set salary=salary+1 where id=1;# 删delete from v2 where id=1;# 增create or replace view v3 as select * from emp;insert into v3 values(1,'dqg','男',66,1.11,'上海','疙瘩','教职部');# 总结:操作视图,会影响真实表,反之也会影响
五、事务
5.1、事务的概念
事务:通常一些业务需要多条sql参与,参与的sql会形成一个执行整体,该整体我们称之为事务
简而言之:事务>>>>就是包含多条执行的sql语句
比如:转账就是一个事务:从一个用户将资金转出,再将资金转入到另一个用户
5.2、事务的四大特性
1、原子性:事务是一组不可分割的单位,要么同时成功,要么同时不成功
2、一致性:事物前后的数据完整性应该保持一致(数据库的完整性:如果数据库在某一时间点下,所有的数据都符合所有的约束,则称数据库为完整性的状态)
3、隔离性:多个用户并发访问数据时,一个用户的事物不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离
4、持久性:一个事务一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
# mysql中事务的执行create table bank( id int, name varchar(16), money decimal(65,2));insert into bank values(1,'Zyl',10),(2,'Ypp',6);# 假设出现以下执行情况# 没有事务支持情况下,Ypp的钱就丢了update bank set money=money-1 where name='Ypp';update bank set money=money+1 where name='Wwb'; # 没有Wwb这个人# 将两条sql看做事务处理# 开启事务begin;update bank set money=money-1 where name='Ypp';update bank set money=money+1 where name='Wwb'; # 确认无误,提交事务commit;# 确认有误,回滚rollback;
六、pymysql 模块:python操作mysql
6.1 安装pymysql 模块
> pip3 install pymysql
6.2 python用pymysql 操作mysql步骤
注意:pymysql不能提供创建数据库的服务,数据库要提前创建
pymysql连接数据库的必要参数:主机、端口、用户名、密码、数据库
步骤:
? 1、建立数据库连接对象 conn
? 2、通过conn创建操作sql的游标对象 cursor
? 3、编写sql语句交给cursor 执行
? 4、如果是查询数据,通过cursor对象获取结果
? 5、操作完毕,关闭端口操作与连接
1、建立数据库连接对象 conn
import pymysqlconn = pymysql.connect(user='root',passwd='root',database='oldboy')# conn = pymysql.connect(user='root', passwd='root', database='oldboy', autocommit=True) # # 重点:在创建conn对象时,不设置autocommit,默认开启事务,增删改操作不会直接映射到数据库中,需要执行 conn.commit() 动作提交事务
2、通过conn创建操作sql的游标对象 cursor
注意:游标不设置参数,查询的结果就是数据元组,数据没有标识性,设置pymysql.cursors.DictCursor,查询的结果是字典,key是表的字段。
cursor = conn.cursor(pymysql.cursors.DictCursor) # 设置字典类型游标
3、编写sql语句交给cursor 执行
cursor.execute(sql) # 一次执行一条sql语句cursor.executemany(sql) # 一次执行多条sql语句
创建表
sql1 = 'create table t1(id int,x int,y int)'cursor.execute(sql1) # 执行sql1语句
增
sql2 = 'insert into t1 values(%s,%s,%s)' # 增加数据的sql语句cursor.execute(sql2,(1,10,100)) # 一次增加一条数据cursor.execute(sql2,(2,20,200))# 重点:在创建conn对象时,不设置autocommit,默认开启事务,增删改操作不会直接映射到数据库中,# 需要执行 conn.commit() 动作conn.commit() # 提交事务cursor.executemany(sql2,[(3,30,300),(4,40,400)]) # 一次增加多条数据conn.commit()
删
sql3 = 'delete from t1 where id=%s' # 删除数据的sql语句cursor.execute(sql3,4) # 删除id=4的那条数据conn.commit()
改
sql4 = 'update t1 set y=666 where id=2' # 修改数据的sql语句,把id=2的那条数据的y改为666cursor.execute(sql4)conn.commit()
查
sql5 = 'select * from t1' # 查询数据的sql语句row = cursor.execute(sql5) # 执行sql语句有返回值,返回值是受影响的行数print(row)
4、如果是查询数据,通过cursor对象获取结果
使用执行的结果:
1、fetchone() 当前游标偏移一条记录取出 2、fetchmany(n) 当前游标偏移n条记录取出3、fetchall() 当前游标往后所有的记录取出,括号里如果有数值参数n,就是游标偏移n条记录往后的所有记录4、scroll(num, mode="relative|absolute") relative: 游标从当前位置往后移动num行 absolute: 游标从头往后移动num行, 一般可以结合line来使用能定位到任意位置
查询数据
r1 = cursor.fetchone() # 游标初始位置在头部,取出第一条记录print(r1) r2 = cursor.fetchone() # 取出第二条记录print(r2)r3 = cursor.fetchmany(1) # 取出第四条记录print(r3)r4 = cursor.fetchall() # 取出剩余所有记录print(r4)
5、操作完毕,关闭端口操作与连接
cursor.close()conn.close()
6.3 游标操作
import pymysqlfrom pymysql.cursors import DictCursor# 1)建立数据库连接对象 connconn = pymysql.connect(user='root', passwd='root', db='oldboy')# 2)通过 conn 创建操作sql的 游标对象cursor = conn.cursor(DictCursor)# 3)编写sql交给 cursor 执行sql = 'select * from t1'# 4)如果是查询,通过 cursor对象 获取结果row = cursor.execute(sql)if row: r1 = cursor.fetchmany(2) print(r1) # 操作游标 # cursor.scroll(0, 'absolute') # absolute绝对偏移,游标重置,从头开始偏移 cursor.scroll(-2, 'relative') # relative相对偏移,游标在当前位置进行左右偏移 r2 = cursor.fetchone() print(r2)# 5)操作完毕,端口操作与连接cursor.close()conn.close()
6.4 pymysql事务
import pymysqlfrom pymysql.cursors import DictCursorconn = pymysql.connect(user='root', passwd='root', db='oldboy')cursor = conn.cursor(DictCursor)try: sql = 'create table t2(id int, name char(4), money int)' row = cursor.execute(sql) print(row)except: print('表已创建') pass# 空表才插入row = cursor.execute('select * from t2')if not row: sql = 'insert into t2 values(%s,%s,%s)' row = cursor.executemany(sql, [(1, 'tom', 10), (2, 'Bob', 10)]) conn.commit()# 可能会出现异常的sql"""try: sql1 = 'update t2 set money=money-1 where name="tom"' cursor.execute(sql1) sql2 = 'update t2 set moneys=money+1 where name="Bob"' cursor.execute(sql2)except: print('转账执行异常') conn.rollback()else: print('转账成功') conn.commit()"""try: sql1 = 'update t2 set money=money-1 where name="tom"' r1 = cursor.execute(sql1) sql2 = 'update t2 set money=money+1 where name="ruakei"' # 转入的人不存在 r2 = cursor.execute(sql2)except: print('转账执行异常') conn.rollback()else: print('转账没有异常') if r1 == 1 and r2 == 1: print('转账成功') conn.commit() # 确认无误,提交事务 else: conn.rollback() # 确认事务有误,回滚
6.5 sql注入
什么是sql注入:
通过书写sql包含(注释相关的)特殊字符, 让原有的sql执行顺序发生改变, 从而改变执行得到的sql
目的: 绕过原有的sql安全认证, 达到对数据库攻击的目的
import pymysqlfrom pymysql.cursors import DictCursorconn = pymysql.connect(user='root', passwd='root', db='oldboy')cursor = conn.cursor(DictCursor)try: sql = 'create table user(id int, name char(4), password char(6))' row = cursor.execute(sql) print(row)except: print('表已创建') pass# 空表才插入row = cursor.execute('select * from user')if not row: sql = 'insert into user values(%s,%s,%s)' row = cursor.executemany(sql, [(1, 'tom', '123'), (2, 'bob', 'abc')]) conn.commit()# 用户登录usr = input('usr: ')pwd = input('pwd: ')# 自己拼接参数一定有sql注入,将数据的占位填充交给pymysql"""没有处理sql注入的写法sql = 'select * from user where name="%s" and password="%s"' % (usr, pwd)row = cursor.execute(sql)if row: print('登录成功')else: print('登录失败')"""sql = 'select * from user where name=%s and password=%s' # 处理sql注入的写法row = cursor.execute(sql, (usr, pwd))if row: print('登录成功')else: print('登录失败')# 知道用户名时# 输入用户时:# tom => select * from user where name="tom" and password="%s"# tom" # => select * from user where name="tom" #" and password="%s"# 不自定义用户名时# " or 1=1 # => select * from user where name="" or 1=1 #" and password="%s"
七、索引
索引就是 键 >>>key
1)键 是添加给数据库表的 字段 的
2)给表创建 键 后,该表不仅会形参 表结构、表数据,还有 键的B+结构图
3)键的结构图是需要维护的,在数据完成增、删、改操作时,只要影响到有键的字段,结构图都要维护一次
所以创建键后一定会降低 增、删、改 的效率
4)键可以极大的加快查询速度(开发需求中,几乎业务都和查有关系)
5)建立键的方式:主键、外键、唯一键、index
import pymysqlfrom pymysql.cursors import DictCursorconn = pymysql.connect(user='root', passwd='root', db='oldboy')cursor = conn.cursor(DictCursor)# 创建两张表sql1 = """create table a1( id int primary key auto_increment, x int, y int)"""cursor.execute(sql1)sql2 = """create table a2( id int primary key auto_increment, x int, y int, index(x))"""cursor.execute(sql2)# 每个表插入5000条数据import randomfor i in range(1, 5001): x = i y = random.randint(1, 5000) cursor.execute('insert into a1(x, y) values(%s, %s)', (x, y)) cursor.execute('insert into a2(x, y) values(%s, %s)', (x, y))conn.commit()import time# 查询a1的id、a1的x、a2的xb_time = time.time()sql = 'select * from a1 where id=4975' # a1的id有索引cursor.execute(sql)e_time = time.time()print(e_time - b_time)b_time = time.time()sql = 'select * from a1 where x=4975' # a1的x没有索引cursor.execute(sql)e_time = time.time()print(e_time - b_time)b_time = time.time()sql = 'select * from a2 where x=4975' # a2的x有索引cursor.execute(sql)e_time = time.time()print(e_time - b_time)# 上面的三个查询结果,a1的id和a2的x是会出现直接查到的情况,不需要时间,而a1的x没有索引,始终是有查询时间的
联合分组、子查询、视图、事务、python操作mysql、索引
相关内容
- 第一个Python程序,什么是程序,首先创建一个.py文
- python基础(局部、全局变量、函数、文件的操作),全局
- python数据分析之ipython,python与ipython,在用python进行
- python3中的编码,python3编码,python2字符串
- python3_module_sys,pythonmodule,__author__
- 对python课程的一些感悟,python小课,学习python已经
- 40行python代码写一个桌面翻译器,python翻译器,大多数有
- python中的is、==和cmp()比较字符串,,python 中的i
- Python中模块之random的功能介绍,,random的功能介
- python中的__enter__ __exit__,,我们前面文章介绍了迭
评论关闭