6 数据库 MySQL pymysql模块视图触发器事务

最新推荐文章于 2023-10-26 20:42:12 发布

Jianhao92

最新推荐文章于 2023-10-26 20:42:12 发布

阅读量356

点赞数

分类专栏： MySQL学习文章标签： mysql

本文链接：https://blog.csdn.net/qq_36565509/article/details/106003201

版权

MySQL学习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据库 MySQL

1 pymysql模块

1.1 增删改操作

查操作不会影响数据，而增删改操作需要修改数据。

增删改操作默认需要使用commit函数进行确认才能生效。

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    database='db2',
    charset='utf8'
)

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

# 增
sql_insert = 'insert into userinfo(name, password) values(%s, %s);'
cursor.execute(sql_insert, ('Elliot', '234'))

# 删
sql_delete = 'delete from userinfo where id = 4'
cursor.execute(sql_delete)

# 改
sql_update = 'update userinfo set password=%s where id = %s;'
cursor.execute(sql_update, ('1234', 2))
conn.commit()

修改pymysql.connect的参数autocommit=True，实现自动提交。

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    database='db2',
    charset='utf8',
    autocommit=True
)

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

# 增
sql_insert = 'insert into userinfo(name, password) values(%s, %s);'
cursor.execute(sql_insert, ('Elliot', '234'))

# 删
sql_delete = 'delete from userinfo where id = 4'
cursor.execute(sql_delete)

# 改
sql_update = 'update userinfo set password=%s where id = %s;'
cursor.execute(sql_update, ('1234', 2))

1.2 批量执行 executemany

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    database='db2',
    charset='utf8',
    autocommit=True
)

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

sql_insert = 'insert into userinfo(name, password) values(%s, %s);'
cursor.executemany(sql_insert, [('Elliot', '234'), ('Marry', '789'), ])

2 视图

2.1 什么是视图？

视图是查询后得到的虚拟表，将其保存起来以供下次使用。

2.2 为什么使用视图？

频繁地操作一张虚拟表，例如拼接表，可以将其以视图的形式保存起来。

2.3 如何使用？

create view 视图名 as SQL语句;

CREATE VIEW teacher2course_view as 
	SELECT
		* 
	FROM
	teacher
	INNER JOIN course ON teacher.tid = course.teacher_id

注意：

视图在硬盘上(data文件夹内)只存储表结构，数据来自于已存在的表；
视图一般只用于查询数据，不能修改数据；
过多的视图会使数据库维护更加困难。

2.4 对比：创建表

CREATE TABLE teacher2course_table as 
	SELECT
		* 
	FROM
	teacher
	INNER JOIN course ON teacher.tid = course.teacher_id

语法结构相似，创建了一张新表。

3 触发器

3.1 介绍

在对表进行增、删、改时，自动触发的功能。
触发时机：6种 before/after + insert/update/delete

create trigger 触发器名 before/after insert/update/delete on 表名 
for each row
begin
	SQL语句
end

create trigger tri_before_t1 before insert t1
for each row
begin
	SQL语句
end

删除触发器

drop trigger 触发器名;

3.2 修改MySQL中默认的结束符

修改MySQL中默认的结束符
MySQL中默认结束符是分号;
修改结束符只在前窗口生效。

# 将结束符修改为$$
delimiter $$

# 将结束符修改为;
delimiter ;

3.3 案例

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' )

);

CREATE TABLE errlog ( 
    id INT PRIMARY KEY auto_increment, 
    err_cmd CHAR ( 64 ), err_time datetime 
);

当向cmd表中插入的记录的succes字段为no时，触发器会向errlog表中插入数据。

delimiter $$

CREATE TRIGGER tri_after_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 ;

INSERT INTO cmd ( USER, priv, cmd, sub_time, success )
VALUES
	( 'jason', '0755', 'ls -l /etc', NOW(), 'yes' ),
	( 'jason', '0755', 'cat /etc/passwd', NOW(), 'no' ),
	( 'jason', '0755', 'useradd xxx', NOW(), 'no' ),
	( 'jason', '0755', 'ps aux', NOW(), 'yes' );

4 事务

开启一个事务可以包含多条SQL语句，这些SQL语句要么全部成功，要么全部失败。
事务用于保证数据安全。

4.1 事务的四个特性 ACID

原子性 Atomicity
事务是不可分割的单位，事务中包含的所有操作要么同时成功，要么全部回滚。
事务中只要有一个操作失败了，全部操作都会复原，即失败的操作不会对数据库产生影响。
一致性 Consistency
数据库从一个一致性的状态变换到另一个一致性的状态。
一个事务在执行前和执行后都必须处于一致性状态，保证了数据的完整性。
例如，数据库中保存了事务中所有操作全部完成的结果，此时数据库处于一致性状态。
如果某些操作在中途中断了，此时数据库中只保存了部分操作完成的数据，此时数据库处于不一致性状态，是一种数据错误混乱的状态。
隔离性 Isolation
事务之间不会相互影响，一个事务的执行不会受到其它事务干扰。
一个事务的所有操作以及使用的数据对并发存在的其它事务是隔离的，并发执行的事务之间是互不干扰的。
例如，当多个用户并发地访问数据库并操作同一张表时，数据库会为每一位用户开启单独的事务，用户操作需要隔离，不能相互干扰。
持久性 Durability
一个事务一旦被提交，对数据库中的修改是永久的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
例如，事务提交后，数据库即使出现了问题，也必须要将提交的事务执行完成。

4.2 操作

事务相关的关键字

开启事务
start transaction;
回滚 (回到事务执行之前的状态)
rollback;
确认提交 (确认后就无法回滚了)
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';

# 3 提交事务，将所有的修改刷到硬盘中。
COMMIT;

5 存储过程

5.1 存储过程介绍

存储过程类似于python中的自定义函数。
存储过程内部包含了一系列可以执行的SQL语句，存储过程存放于MySQL服务端中，可以通过调用(call)存储过程来执行其SQL语句。

create procedure 存储过程名(形参1, 形参2,...)
begin
	SQL代码
end

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

代码示例

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

# 对于形参res，不能直接传数据，应该传变量名。
# 定义变量
set @ret = 10;

call p1(1, 5, @ret);

# 查看变量对应的值
select @ret;

5.2 pymysql模块中调用存储过程

import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123',
    db = 'db3',
    charset = 'utf8',
    autocommit = True
)

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

# 调用存储过程
cursor.callproc('p1', (1, 5, 10))
"""
形参变形
@_p1_0 = 1
@_p1_1 = 5
@_p1_2 = 10
"""
print(cursor.fetchall())  # 返回存储过程中的查询结果
cursor.execute('select @_p1_2;')

5.3 三种开发模式

模式1
应用程序: 程序员负责代码开发；
MySQL操作: 提前编写好存储过程，供应用程序调用。
优点：提升了开发效率和执行效率；
缺点：考虑到人为元素和跨部门沟通的问题等，后续的存储过程的扩展性较差。
模式2
程序员除了负责程序开发外，还需要负责数据库的操作。
优点：扩展性高；
缺点：开发效率低，编写SQL语句繁琐，而且后续还需要考虑数据库优化问题。
模式3
程序员只负责写程序代码，不负责写SQL语句，而是基于别人写好的操作MySQL的Python框架(ORM框架)，直接调用操作即可。
优点：开发效率比上面两种模式都高；
缺点：语句的扩展性差，可能会出现效率低下的问题。

6 内置函数

存储过程相当于自定义函数，而函数是内置函数。

例如，使用date_format对时间进行格式化。

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇', '2015-03-01 11:31:21'),
    ('第2篇', '2015-03-11 16:31:21'),
    ('第3篇', '2016-07-01 10:21:31'),
    ('第4篇', '2016-07-22 09:23:21'),
    ('第5篇', '2016-07-23 10:11:11'),
    ('第6篇', '2016-07-25 11:21:31'),
    ('第7篇', '2017-03-01 15:33:21'),
    ('第8篇', '2017-03-01 17:32:21'),
    ('第9篇', '2017-03-01 18:31:21');

select date_format(sub_time, '%Y-%m'), count(id) from blog group by date_format(sub_time, '%Y-%m');

7 流程控制

7.1 if判断

# 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 ;

7.2 while循环

# while循环
delimiter //

CREATE PROCEDURE proc_while ()
BEGIN
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE;
END //

delimiter ;

8 索引

8.1 介绍

数据都是硬盘上存储的，查询数据不可避免地需要进行IO操作。
索引是一种数据结构，类似于书的目录，用于快速定位到目标数据。在查询数据时应该先找目录再找数据，而不是逐页翻书查找。
索引有助于提升查询速度，减少IO操作。

8.2 键

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

能够作为索引的键包括 primary key，unique key，index key。
注意：foreign key不能作为索引，它只用于建立表与表之间的关系。

primary key的约束条件是 unique 和 not null；
unique key的约束条件是 unique；
index key 没有约束条件。

键的本质是通过不断缩小目标数据查找范围来获取目标数据，同时将随机事件(逐页翻书)变成顺序事件(先找目录再找数据)。

一张表中可以有多个索引。

索引缺点

当表中已经存储了大量数据时，创建索引的速度会很慢；
索引创建完毕后，表的查询性能会大幅度提升，但是写的性能(例如在中间插入数据)也会大幅度降低。

结论：
索引不要随意创建。

8.3 b+树

在这里插入图片描述
蓝色方框代表键值，例如主键id，黄色代表子树。

只有叶子节点存放的是真实数据，其它节点存放的是虚拟数据，仅用于指路。
树的层级数等于找到目标数据所经历的步骤数，因此树的层级越深，查询数据所需要必须经历的步骤就越多。

为什么建议使用id字段作为索引？
硬盘的一个磁盘块所存储的数据量是有限的。
id字段一般是整形数据，占得空间较少，一个磁盘块能存储更多的id数据。
一个磁盘块能存储的数据越多，所需要的树的层数就越小，从而减少查询次数，加快查询速度。

8.4 索引分类

8.4.1 聚集索引 primary key

聚集索引指的是主键(primary key)。

InnoDB在硬盘上只有两个文件，直接将聚集索引(主键)存放在idb表中(.ibd文件)；
MyISAM在硬盘上有三个文件，单独将索引存在一个文件中(.MYI文件)。

8.4.2 辅助索引(unique key, index key)

辅助索引指的是除了主键以外的索引，即unique key和index key。
查询数据的时候不可能一直使用主键，也有可能会使用其它字段，此时无法利用聚集索引，可以根据情况为其它字段设置辅助索引。
辅助索引也是一个b+树，其叶子节点存放的是数据对应的主键值。
检索流程：先按照辅助索引拿到数据的主键值，然后根据主键去聚集索引中查询数据。

8.4.3 覆盖索引

在辅助索引的叶子节点就获取了所需要的数据字段值，此时无需再去聚集索引中查询数据。

# name字段为辅助索引

# 覆盖索引
select name from user where name='jason';
# 非覆盖索引，即检索条件命中了索引字段，但所需字段不是该索引字段。
select age from user where name='jason';

8.4.5 测试索引是否有效的代码

准备

# 1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

# 2. 创建存储过程，实现批量插入记录
delimiter $$  # 声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000) do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END $$ #$$结束
delimiter ;  # 重新声明分号为结束符号

# 3. 查看存储过程
show create procedure auto_insert1\G 

# 4. 调用存储过程
call auto_insert1();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢

"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快