Day 41 数据库高级1
数据库高级
视图
视图本质上是一个虚拟的表,看的见实际不存在
使用场景
- 我们希望某些查询语句只能看到某个表中的一部分记录就可以用视图
- 简化sql语句的编写
使用方法
create [or replace] view view_name as 查询语句;
or replace 如果视图已经存在就替换里面的查询语句;
#修改视图
alter view view_name as 新的查询语句;
#删除视图
drop view view_name;
#查看
desc view_name; #查看视图结构
show create view view_name; #查看视图创建语句
注意:修改视图也会引起原表的变化,所以不能这么操作,视图仅用于查询
触发器
触发器就是一段与某个表相关的SQl语句,当满足某个条件后自动触发运行
时间点:事件发生前 before | 事件发生后 after
事件:update delete insert
在触发器中包含两个对象
old update,delete中可用
new update,insert中可用
用于当表的数据被修改时,自动记录一些数据执行一些sql语句
create trigger t_name t_time t_event on table_name for each row
begin
sql语句...
end
#删除触发器
deop trigger trigger_name;
#查看所有触发器
show triggers;
#查看某个触发器的语句
show create trigger trigger_name;
当我们使用触发器的时候 需要在触发器前使用delimiter来转换结束符,取消触发器代码中;的含义然后在触发器结束后再还原
事物
事物就是一系列sql语句的组合,是一个整体
事物的特点:
- 原子性:值的是这个事物中的sql语句时一个整体,不能拆分,要么都执行,要么全部失败
- 一致性:事务执行结束手,表的了关联关系一定是正确的,不会发生数据错乱
- 隔离性,事物之间相互隔离,数据不会相互影响,本质上就是加锁,根据锁的颗粒度不同分为几个隔离级别
- 持久性:事物执行成功后数据将永久保存,无法恢复
- 在官方提供的cmd的myusql客户端下,事物是默认开启,会江一条sql语句作为一个事物没回自动提交
事物的应用场景
1.开启事物
start transaction
sql 语句...
sql 语句...
rollback #回滚操作 撤销没有提交之前的所有操作
sql 语句...
commit #提交事物
2.回归到保存点
#设置保存点
savepoint 保存点名
#回滚到某个保存点
rollback to 保存点名
read committed
幻读和重复读
幻读是因为别人在执行插入和删除
重复读是因为别人在做update
修改隔离级别
#修改全局的
set global transaction isolation level read committed;
set @@tx_isolation="asasasassas-read";
#修改局部的
set session transaction isolation level read committed;
@@ 系统内置变量
@ 表示用户自定义的变量
存储过程
存储过程就是任意sql语句的组合,被放到某一个存储过程,类似于一个函数,有一个函数,有参数,还是函数体
用来干什么:其中可以包含任何的sql语句,逻辑处理,事务处理
三种数据处理方式:
-
应用程序只关注业务逻辑,所有与数据相关的逻辑封装到mysql中
优点:应用程序要处理的事情变少了,可以减少网络传输
缺点:增加人力成本,沟通成本,降低了整体开发效率
-
应用程序纪要处理业务逻辑,还要自己编写sql语句
优点:降低沟通成本,人力成本
缺点,网络阐述增加,sql语句编写非常繁琐,容易出错
-
通过ORM框架 对象关系映射自动生成sql语句并执行
优点:不需要在编写sql语句,明显提升开发速度
缺点:不够灵活,应用程序开发者和数据库完全隔离,可能导致仅冠层上层开发,而不清楚底层数据库原理
使用存储过程
create procedure p_name(p_type p_name p_date_type)
begin
sql语句
end
p_type 参数类型 in输入 out输出 inout即可输出也可输入 #out参数必须是一个变量不能是值
p_name 参数的名字
p_data_type 参数的数据类型 如int float
函数
函数和存储过程的区别
函数中不能包含任何sql语句,只能是逻辑处理
自定义函数
create function func_name(参数 数据类型)
函数体代码
returns 返回值的类型
return 返回值
#示例
delimiter |
create function add2 (a int ,b int)
returns int
return a+b |
delimiter ;
#查看创建语句
show create function func_name;
#查看所有函数的状态
show function status;
#查看某个库下所有函数
select name from mysql.proc where db = 'database_name ' and type = 'FUNCTION';
#删除
drop function func_name;
备份与恢复
#备份
mysqldump -u用户名 -p密码 数据库名 表1 表2 ... > 备份文件路径 #第一个表示数据库后面全是表名
#示例
mysqldump -uroot -p day41 student > filename
#备份多个数据库
mysqldump -uroot -p111 --databases day41 day40 > x3x.sql
#指定 --databases 后导出的文件包含 创建库的语句 而上面的方式不包含
#备份所有数据
mysqldump -uroot -p111 --all-databases > all.sql
#自动备份
linux crontab 指令可以定时执行某一个指令
# 恢复数据:
没有登录mysql
mysql < 文件的路径
已经登录了MySQL
source 文件路径
注意: 如果导出的sql中没有包含选择数据库的语句 需要手动加上
流程控制
delimiter |
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|
delimiter ;
正则匹配
select *from table where 字段名 regexp "表达式!";
create table info(name char(20));
insert into info values("jack sbaro"),("jack rose"),("jerry sbaro"),("sbaro jerry"),("jerry");
# 注意: 不能使用类似 \w 这样的符号 需要找其他符号来代替
