随笔- 79 文章- 0 评论- 0 阅读- 9540

六

pymysql增删改查

# 针对增删改需要二次确认才会起作用
import pymysql


conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_user',
    charset='utf8',
    autocommit=True  # 加了只后就可以不用再确认了，直接提交
)
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 增
sql = 'insert into user(name, password) values(%s, %s)'
rows = cursor.executemany(sql, [('egon', 123), ('tank', 123), ('aaa', 123)])  # 增加多个数据
# rows = cursor.execute(sql, ('json', 123))  # 增加单个数据
# print(rows)
# conn.commit()  # 确认

# 改
# sql = 'update user set name="jsonnb" where id=4'
# rows = cursor.execute(sql)
# print(rows)
# conn.commit()

# 删
# sql = 'delete from user where id=4'
# rows = cursor.execute(sql)
# print(rows)
# conn.commit()

# 查
# sql = 'select * from user'
# cursor.execute(sql)
# print(cursor.fetchall())

"""
增删改查中
    增删改他们的操作涉及到数据的修改
    需要二次确认
"""

# 还可以一次性插入n多条数据
cursor.executemany(sql, [('egon', 123), ('tank', 123), ('aaa', 123)])

今日内容概要

今日内容基本都是了解知识点，作为一名开发人员在工作中基本用不到

视图
触发器
事务（需要掌握）
存储过程
内置函数
流程控制
索引理论

今日内容详细

视图

什么是视图

"""
视图就是通过查询得到一张虚拟的表，然后保存下来，下次可以直接使用
其实视图也是表
"""

为什么要用

"""
如果要频繁的操作一张虚拟表（拼表组成的），你就可以制作成视图，后续直接操作
"""

如何使用

# 固定语法
create view 表名 as  # 虚拟表的查询SQL语句

# 具体操作
create view teacher2course as
select * from teacher inner join course 
on teacher.tid = course.teacher_id
;

注意

"""
1、创建视图在硬盘上只会有表结构，没有表数据（数据来自之前的表）
2、视图一般只用来查询，不要修改，可能会影响真正的表
"""

视图使用的频率高不高

"""
不高
当你创建了很多视图后，会使表的维护会非常麻烦
"""

# 结论
	视图了解即可，基本不用！！！！

触发器

在满足对表数据进行增、删、改的情况下，自动触发的功能

使用触发器可以帮助我们实现监控、日志、自动处理异常等等。。。

触发器可以在六种情况下自动触发，增前增后，删前删后，改前改后

基本的语法结构

create trigger 触发器的名字 before/agter insert/update/delete 
on 表名 
for each row
begin
	sql语句
end

# 具体使用 针对触发器的名字，我们通常需要做到见名知意
# 针对增
create trigger tri_before_insert_t1 before insert 
on t1 
for each row
begin
	sql语句
end

create trigger tri_after_insert_t1 after insert 
on t1 
for each row
begin
	sql语句
end
"""针对删除和修改书写格式一致"""

ps：修改mysql默认的语句结束符  只作用于当前窗口
	delimiter $$  将默认的结束符由;改为$$
    delimiter ;
    
# 案例
create table cmd(
	id int primary key auto_increment,
    user char(32),
    priv char(10),
    cmd char(64),
    sub_time datetime,  # 提交时间
    success enum('yes','no')  # 0代表执行失败
)；

create table errlog(
	id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);
"""
当cmd表中的记录success字段是no那么就触发触发器的执行去errlog表中插入数据
NEW指代的就是一条条数据对象
"""
delimiter $$
create trigger tri_agter_insert_cmd after insert on cmd
for each row
begin
	if NEW.success = 'no' then
    	insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

# 朝cmd表插入数据
insert into cmd(
	user,
    priv,
    cmd,
    sub_time,
    success
)
values
	('jason','0755','ls -l /ect',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');


# 删除触发器
drop trigger tri_agter_insert_cmd;

事务

什么是事务

"""
开启一个事务可以包含多条SQL语句，这些SQL语句要么同时成功要么一个都别想成功
称之为事务的原子性
"""

事务的作用

"""
保证了对数据操作的安全性
"""

事务的四大特性

"""
ACID
A：原子性
	一个事务是一个不可分割的单位，事务中包含的诸多操作要么同时成功，要么同时失败
C：一致性
	事务必须是使数据库从一个一致性变到另外一个一致性的状态
	一致性跟原子性是密切相关的
I：隔离性
	一个事务的执行不能被其他事务干扰，
	即一个事务内部的操作及使用到的数据对并发的其他事务之间也是互相不干扰的
D：持久性
	也叫‘永久性’
	一个事务一旦提交成功执行成功，那么他对数据库中数据的修改应该是永久的
	接下来的其他操作或者故障不应该对其有任何的影响
"""

如何使用事务

# 事务相关的关键字
# 1、开启事务
start transaction;
# 2、回滚（回到事务执行之前的状态）
rollback
#3、确认（确认之后就无法回滚了）
commit;

"""模拟转账功能"""
create table user(
	id int primary key auto_increment,
    name char(16),
    balance int
);

insert into user(name,balance) values
('jason',1000),
('egon',1000),
('tank',1000);

# 1、先开启事务
start transaction;
# 2、多条SQL语句
update user set balance=900 where name='jason';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='tank';

"""
总结
	当你想让多条SQL语句保持一致性，要么同时成功要么同时失败
	你就应该考虑事务
"""

存储过程

存储过程就类似于Python中的自定义函数

他的内部包含了一系列可以执行的SQL语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部SQL语句的执行

基本使用

create procedure 存储过程中的名字(形参1，形参2,...)
begin
	sql 代码
end

# 调用
call 存储过程中的名字();

三种开发模式

第一种

"""
应用程序：程序员写代码开发
MySQL：提前编写好存储过程，供程序员调用

好处：开发效率提升了，执行效率上去了
坏处：考虑到人为因素，跨部门沟通问题，后续的存储过程的扩展性差
"""

第二种

"""
应用程序：程序员写代码开发之外，涉及到数据库操作也自己动手写
优点：扩展性很高
缺点：
	开发效率降低
	编写SQL语句太过繁琐，而且后续还需要考虑SQL优化的问题
"""

第三种

"""
应用程序：只写程序代码，不写SQL语句，基于别人写好的操作MySQL的Python框架直接调用操作即可	
		ORM框架
优点：开发效率比上面两种情况都要高
缺点：语句的扩展性差，可能会出现效率低下的问题
"""

第一种基本不用，一般都是第三种，出现效率问题再手动学SQL

存储过程具体演示

delimiter $$
create procedure p1(
	in m int,  # 只进不出  m不能返回出去
    in n int,
    out res int  # 该形参可以返回出去
)
begin
	select tname from teacher where tid>m and tid<n;
    set res=0;  # 将res变量修改，用来标识当前存储过程代码确实执行了
end $$
delimiter ;

# 针对形参res，不能直接传数据，应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;

在pymysql模块中调用存储过程

import pymysql


conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='day48',
    charset='utf8',
    autocommit=True  # 加了只后就可以不用再确认了，直接提交
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1', (1, 4, 10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2')
print(cursor.fetchall())

内置函数

跟存储过程是有区别的，存储过程相当于自定义函数

('jason','0755','ls -l /ect',NOW(),'yes')  # NOW:查看当前时间

create table blog(
	id int primary key auto_increment,
    name char (32),
    sub_time datetime
);

insert into blog(name,sub_time)
values
	('第一篇','2015-03-01 11:31:21'),
    ('第二篇','2015-03-11 12:41:54'),
    ('第三篇','2016-11-01 07:45:27'),
    ('第四篇','2017-08-07 01:44:23');
    
select date_format(sub_time,'%Y-%m'),count(id) from blog group by
date_format(sub_time,'%Y-%m');

流程控制

# if判断
delimiter //
create procedure proc_if()
begin
	declare i int default 0;
    if i = 1 then
    	select 1;
    elseif i = 2 then
    	select 2;
    else
    	select 7;
    end if;
end //
delimiter ;

# while循环
delimiter //
create procedure proc_whlie()
begin
	declare num int;
    set num = 0;
    whlie num<10 DO
    	select
        	num;
        set num = num + 1;
    end while ;

索引

ps:数据都是存在于硬盘上的，查询数据不可避免的需要进行IO操作

索引：就是一种数据结构，类似于书的目录。意味着以后在查询数据的时候应该先找目录再找数据，而不是一页一页的翻书，从而提升查询速度降低IO

索引在MySQL中也叫“键”，是存储引擎用于快速查找记录的一种数据结构

primary key
unique key
index key

注意foreign key不是用来加速查询用的，不在我们的研究范围内

上面三种key，前面两种除了可以增加查询速度之外各自还具有约束条件，而最后一种index key没有任何的约束条件，只是用来帮助快速查询数据

本质

通过不断的缩小想要的数据范围筛选出最终的结果，同时将随机的事件（一页一页的翻）变成了顺序事件（先找目录、找数据）

也就是说有了索引机制，我们可以总是用一种固定的方式查找数据

一张表中可以有多个索引（多个目录）

索引虽然能够帮助你加快查询速度但是也有缺点

"""
1、当表中有大量数据存在的前提下，创建索引的速度会非常的慢
2、在索引创建完毕之后，对表的查询性能会大幅度的提升，但是写的性能也会大幅度的降低
"""
所以索引不要随意的创建！！！

b+树

"""
只有叶子节点存放的是真实的数据，其他节点存放的是虚拟数据，仅仅只是用来指路的
树的层级越高查询数据所需要经历的步骤就越多（树有几层，查询数据就要有几步）

一个磁盘块存储是有限制的
	用ID作为索引，是因为占得空间少，一个磁盘能够存储的数据多
	那么就降低了树的高度，从而减少查询次数
"""

聚集索引（primary key）

"""
聚集索引指的就是主键
Innodb	只有两个文件，直接将主键存放在了idb表中
myisam	三个文件，单独将索引存放在一个文件
"""

辅助索引（unique，index）

查询数据的时候不可能一直使用到主键，也有可能用到name，password等其他字段

那么这个时候你是没有办法利用聚集索引，这个时候你就可以根据情况给其他字段设置辅助索引（也是一个b+树）

"""
叶子节点存放的是数据对应的主键值
	先按照辅助索引拿到数据的主键值
	之后还是需要取主键的聚集索引里面查询数据
"""

覆盖索引

在辅助索引的叶子节点就已经拿到了需要的数据

# 给name设置辅助索引
select name from user where name='jason';

# 非覆盖索引
select age from user where name='jason';

posted @ 2023-01-31 20:28 7七柒阅读(20) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 第二章：Django的基础操作及配置

· 第六章：模型层

· MySQL(五）

· 4月10日课后总结

· mysql6

公告

昵称： 7七柒
园龄： 3年1个月
粉丝： 6
关注： 3

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

随笔档案

css样式

调整border-radius的自定义形状

编程学习网站

工具网址

can you run it

前端网址

有bug上这里

StackOverFlow

liuhousheng

六