数据库高级

最新推荐文章于 2024-09-04 17:23:03 发布

Wood_Ren

最新推荐文章于 2024-09-04 17:23:03 发布

阅读量327

点赞数

分类专栏： Python

本文链接：https://blog.csdn.net/Wood_Ren/article/details/84559781

版权

Python 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

一.视图

什么是视图

视图是有一张表或多张表的查询结果构成一张虚拟表

为什么使用视图

当我们在使用多表查询时,sql语句可能很复杂,如果每次都编写一遍sql语气,无疑是一件很麻烦的事情,这时候就可以使用视图来避免多次编写sql的问题

视图可以帮我们节省sql的编写
视图另一个作用:可以不同的视图来展示开放不同数据的访问

例如,同一张工资表,老板可以查看全部,部门主管可以查看该部门所有人,员工只能看自己的一条记录,也是一中限制

使用方法

创建视图

create [or replace] view view_name [(column_list)] as select_statement

加上OR RERLACE 时如果已经存在相同视图则替换原有视图
column_list指定哪些字段要出现在视图中
注意:由于是一张虚拟表,视图中的数据实际上来源其他表,所有在视图中的数据不会出现在硬盘上

使用视图

视图是一张虚拟表所有使用方式与普通表没有任何区别

查看视图
1. desc view_name; //查看数据结构
2. show create view view_name; //查看创建语句
修改视图
- alter view_name select_statement
删除视图
- drop view view_name

案例1: 简化多表sql语句

# 准备数据
create database db02 charset utf8;
use db02
create table student(
	s_id int(3),
	name varchar(20),
	math float,
	chinese float
);
insert into student values(1,"tom",80,70),(2,"jack",80,80),(3,"rose",60,75);

create table stu_info(
	s_id int(3),
	class varchar(50),
	addr varchar(100)
);
insert into stu_info values(1,"二班","安徽"),(2,"二班","湖南"),(3."三班","黑龙江");

# 创建视图包含 编号 学生的姓名 和班级
create view stu_v (编号,姓名,班级) as 
select
student.s_id,student.name,stu_info.class
from student,stu_info
where student.s_id = stu_info.s_id;
# 查看视图中的数据
select *from stu_v;

案例2: 隔离数据

# 创建工资表
create table sarlary(
id int primary key,
name char(10),
salary double,
dept char(10)
);
insert into salarys values
(1,"刘强东",900000,"市场"),
(2,"马云",800090,"市场"),
(3,"李彦宏",989090,"财务"),
(4,"马化腾",87879999,"财务");

# 创建市场部视图
create view dept_sc as select *from salarys where dept = "市场";
# 查看市场部视图
select *from dept_sc;

注意: 对视图数据的insert update delete 会同步到原表中,但由于视图可能是部分字段,很多时候回失败

总结: mysql可以分担程序中的部分逻辑,但这样一来后续的维护会变得更麻烦

如果需要改表结构,那意味着视图也需要相应的修改,没有直接在程序中修改sql来的方便

二.触发器

什么是触发器

触发器是一段与表有关的mysql程序,当这个表在某个时间点发生了某种事件时,将会自动执行相应的触发器程序

何时使用触发器

当我们想要在一个表记录被更新时做一些操作时就可以使用触发器

但是我们完全可以在python中来完成这个事情,因为python的扩展性更强,语法更简单

触发器使用

创建触发器

# 语法:
create trigger t_name t_time t_event on table_name for each row 
begin
stmts......
end

支持的时间点(t_time) : 时间发生前和发生后 before|after

支持的事件(t_event) : update insert delete

在触发器中可以访问到将被修改的那一行数据,根据事件不同,能访问也不同 update 可以old访问旧数据,new访问新数据, insert 可用 new 访问新数据, delete 可用 old 访问旧数据

可以将 new 和 old 看做一个对象其中封装了这列数据的所有字段

案例:

有cmd表和错误日志表,需求: 在cmd执行失败时自动将信息存储到错误日志表中

# 准备数据
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 datatime
);
# 创建触发器
delimiter //
create trigger trigger1 after insert on cmd for each row 
begin
if new.success = "no" then
	insert into errlog values(null,new.cmd,new.sub_time);
end if;
end //
delimiter ;

# 网表cmd中插入记录,触发触发器,根据if的条件决定是否插入错误日志
insert into cmd (
	user,
	priv,
	cmd,
	sub_time,
	success
)
values
	("egon","0755","ls -1 /etc",now(),"yes"),
	("egon","0755","cat /etc/passwd",now(),"no"),
	("egon","0755","useradd xxx",now(),"no"),
	("egon","0755","ps aux",now(),"yes");

# 查看错误日志表中的记录是否有自动插入
select *from errlog;

delimiter 用于修改默认的行结束符,由于在触发器中有多条sql语句他们需要使用分号来结束,但是触发器是一个整体,所有我们需要先更换默认的结束符,在触发器编写完后在将结束符设置回分号

注意:

外键不能触发器事件,主表删除了某个主键,从表也会相应删除,但是并不会执行触发器,触发器中不能使用事务,相同时间点的相同事件的触发器,不能同时存在

删除触发器

语法:
drop trigger trigger_name;
案例:
drop trigger trigger1;

同样的这种需求我们完全可以在python中来完成! mysql最想完成的事情是讲所有能处理的逻辑全部放到mysql中,那样一来应用程序开发者的活儿就变少了,相应的数据库管理员的工资就高了,可惜大多中小公司都没有专门的DBA;

三.事务

什么是事务

事务是逻辑上的一组操作,要么成功,要么失败

为什么需要事务

很多时候一个数据操作,不是一个sql语句就完成的,可能有很多个sql语句,如果部分sql执行成功而部分sql执行失败将导致数据错乱!

例如转账操作:

从原有的账号减去转账金额
给目标账号加上转账金额
若中间突然断电了或系统崩了,钱就没了

使用事务

start transaction; —>开启事务,在这条语句之后的sql将处在同一事务,并不会立即修改数据库

commit; —>提交事务,让这个事物中的sql立即执行数据的操作

rollback; —>回滚事务,取消这个事务,这个事务不会对数据库中的数据产生任何影响

**案例:**转账过程中发生异常

# 准备数据
create table account(
	id int primary key auto_increment,
	name char(20),
	money double
);
insert into account values(1,"赵大儿子",1000);
insert into account values(2,"刘大牛",1000);
insert into account values(3,"猪头三",1000);
insert into account values(4,"王进",1000);
insert into account values(5,"黄卉",1000);

# 赵大儿子刘大牛转账1000块
# 未使用事务
update account set money = money - 1000 where id = 1;
# money打错了导致执行失败
update account set moneys = money - 1000 where id = 1;

# 在python中使用事务处理
sql = "update account set money = money - 1000 where id = 1;"
sql2 = "update account set moneys = money - 1000 where id = 2;"

# money 打错了导致执行失败
try:
	cursor.execute(sql)
	cursor.execute(sql2)
	conn.commit()
except:
	conn.rollback()

注意: 事务的回滚的前提是能捕捉到异常,否则无法决定何时回滚,Python中很简单就实现了,另外mysql中需要使用存储过程才能捕获异常!

事务的四个特性:

原子性:

事务是一组不可分割的单位,要么同时成功,要么不成功
一致性:

事务前后的数据完整性应该保持一致,(数据库的完整性:如果数据库在某一时间点下,所有的数据都符号所有的约束,则称数据库为完整性的状况);
隔离性:

事务的隔离性是指多个用户并发访问数据时,一个用户的事务不能被其他用户的事务所干扰,多个并发事务之间数据要相互隔离
持久性:

持久性是指一个事务一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

事务的用户隔离级别:

数据库使用者可以控制数据库工作在哪个级别下,就可与防止不同的隔离性问题

read uncommitted —> 不做任何隔离,可能脏读,幻读

read committed —> 可以防止脏读,不能防止不可重复读,和幻读

repeatable read —> 可以防止脏读,可重复读,不能防止幻读

serializable —> 数据库运行在串行化实现,所有问题都没有,就是性能低

修改隔离级别:

select @@tx_isolation; —> 修查询当前级别

set [session | global] transaction isolation level …; —> 修改级别

实例:

set global transaction isolation level repeatable read;

四.存储过程

什么是存储过程

存储过程是一组任意的sql语句集合,存储在mysql中,调用存储过程时将会执行其包含的所有sql语句;与python中函数类似;

为什么使用存储过程

回顾触发器与视图都是为了简化应用程序中sql语句的书写,但是还是需要编写,而存储过程中可以包含任何的sql语句,包括视图,事务,流程控制等,这样一来,应用程序可以从sql语句中完全解放,mysql可以替代应用程序完成数据相关的逻辑处理!

三种开发方式对比

1.应用程序仅负责业务逻辑编写,所有与数据相关的逻辑都交给mysql来完成,通过存储过程(推荐使用)
- 优点:应用程序与数据处理完解耦合,一堆复杂的sql被封装成了一个简单的存储过程,考虑到网络环境因素,效率高
- 缺点:python语法与mysql语法区别巨大,学习成本高,并且各种数据库的语法大不相同,所有移植性非常差,应用程序开发者与BDA的跨部门沟通成本高,造成整体效率低
2.应用程序不仅编写业务逻辑,还需要编写所有的sql语句
- 优点:扩展性高,对于应用程序开发者而言,扩展性和维护性相较于第一种都是所提高
- 缺点:sql语句过于复杂,导致开发效率低,且需要考虑sql优化问题
3.应用程序仅负责业务逻辑,sql语句的编写交给ORM框架(常用解决方案)
- 优点:应用程序开发者不需要编写sql语句,开发效率高
- 缺点:执行效率低,由于需要将对象的操作转化为sql语句,且需要通过网络发送大量sql

创建存储过程

create procedure pro_name(p_type p_name data_type)
begin
sql语句......流程控制
end

p_type 参数类型
in 表示输入参数
out 表示输出参数
inout 表示既能输入又能输出
p_name 参数名称
data_type 参数类型可以是mysql支持的数据类型

案例: 使用存储过程完成对student表的查询

delimiter //
create procedure p1(in m int,in n int,out res int)
begin
select *from student where chinese > m and chinese < n;
# select *from student where chineseXXX > m and chinese < n; 修改错误的列名以测试执行失败
set res = 100;
end //
delimiter;
set @res = 0;
# 调用存储过程
call p1(70,80,@res);
# 查看执行结果
select @res;

需要注意的是,存储过程的out类参数必须是一个变量,不能是值

在python中调用存储过程

import pymysql
# 建立连接
conn = pymysql.connect(
	host = "127.0.0.1",
	user = "root",
	password = "admin",
	database = "db02"
)
# 获取游标
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 调用存储过程
cursor.callproc("p1",(70,80,0))  # p1位存储过程名 会自动为每个值设置变量,名称为 @_p1_0,@_p1_1,@_p1_2
# 提取执行结果是否有结果取决于存储过程中的sql语句
print(cursor.fetchall())
# 获取执行状态
cursor.execute("select @_p1_2")
print(cursor.fetchone())

此处pymysql会自动将参数都设置一个变量,所有直接传入一个值,当然值如果作为输出参数的话,传入什么都无所谓!

删除存储过程

drop procedure 过程名称;

修改存储过程意义不大,不如删除重写!
查看存储过程

# 当前库所有存储过程名称
select 'name' from mysql.proc where db = "db02" and 'type' = 'procedure';
# 查看创建语句
show create procedure p1;

存储过程中的事务应用

存储过程中支持任何的sql语句包括事务!

案例: 模拟转账中发送异常,进行回滚

delimiter //
create procedure p5(
	out p_return_code tinyint
)
begin
	declare exit handler for sqlexception
	begin
		-- error
		set p_return_code = 1;
		rollback;
	end;
	# exit 也可以换成continue 鄙视发送异常时继续执行
	declare exit handler for sqlwarning
	begin
		--warning
		set p_return_code = 2;
		rollback;
	end;
	
	start transaction;
	update account set money = money - 1000 where id = 1;
	update account set moneys = money - 1000 where id = 1;   # moneys字段导致异常
	commit;

	-- success
	set p_return_code = 0; # 0代表执行成功

end //
delimiter ;

# 在mysql中调用存储过程
set @res = 123;
call p5(@res);
select @res;

总结: 抛开沟通成本,学习成本,存储过程无疑是效率最高的处理方式

五.函数

函数与python中的定义一致

内置函数

日期相关:
字符串相关:
数字相关:
其他函数:

还有之前的聚合函数

自定义函数

语法:

create function f_name(paramters)
returns dataType;
return value;

说明: paramters 只能是in 输入参数\参数名\类型必须有返回值,不能加begin和end returns 后面是返回值的类型,这里不加分号return 后面是要返回的值

案例: 将两数相加

create function addfuntion(a int,b int)
returns int return a + b;
# 执行函数
select addfuntion(1,1);

注意:

函数只能返回一个值,函数一般不涉及数据的增删改查,就是一个通用的功能,调用自定义的函数,与调用系统的一致,不需要call 使用select可获得返回值,函数中不能使用sql语句,就像在java中不能识别sql语句一样

六.数据备份与恢复

使用mysqldump程序进行备份

mysqldump -u -p db_name [table_name,,,] > fileName.sql

可以选择备份哪些表如果不指定代表全部备份

# 示例:
# 单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

使用mysql进行恢复

1.退出数据库后
mysql -u -p < filename.sql;
2.不用退出数据库
- 2.1 创建空数据库
- 2.2选择数据库
- 2.3然后使用source filename;来进行还原
```
use db1;
sourse /root/db1.sql
```

数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标
IP -uroot -p456

七.流程控制

if语句的使用

if条件then语句; end if;

if else if条件 then语句1;
else if 条件 then语句2;
else 语句3;
end if;

案例:编写过程,实现输入一个整数type范围1 - 2输出type = 1 or type = 2 or type = other;

create procedure showtype(in type int,out result char(20))
begin
if type = 1 then
set result = "type = 1";
elseif type = 2 then 
set result = "type = 2";
else 
set result = "type = other";
end if;
end

CASE语句

大体意思与swtich一样,你给我一个值,我对它进行选择,然后执行匹配上的语句

语法:

create procedure caseTest(in type int)
begin
CASE type 
when 1  then select "type = 1";
when 2  then select "type = 2";
else select "type = other";
end case;
end

定义变量

declare 变量名类型 default 值;

例如: declare i int default 0;

WHILE循环

循环输出10次hello mysql
create procedure showHello()
begin 
declare i int default 0;
while  i < 10 do
select "hello mysql";
set i  = i + 1;
end while;
end

LOOP循环

没有条件,需要自己定义结束语句

语法:

输出十次hello mysql;
create procedure showloop()
begin 
declare i int default 0;
aloop: LOOP
select "hello loop";
set i = i + 1;
if i > 9 then leave aloop;
end if;
end LOOP aloop;
end

REPEAT循环

#类似do while
#输出10次hello repeat
create procedure showRepeat()
begin
declare i int default 0;
repeat
select "hello repeat";
set i = i + 1;
until i > 9
end repeat;
end

#输出0-100之间的奇数
create procedure showjishu()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end