mysql 07.15
1.视图
本质上是一个虚拟的表
语法：
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;
# 限制可以查看的记录
create table salarys(id int,name char(10),money float);
insert into salarys values(1,"张三丰",50000),(2,"张无忌",40000);
# 创建视图 限制只能查看张无忌的工资
create view zwj_view as select *from salarys where name='张无忌';
# 简化sql编写
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 class_info as select student.s_id,name,class from student join stu_info on student.s_id = stu_info.s_id;
select *from class_info;
2.触发器
触发器是一段与某个表相关的sql语句，会在某个时间点，满足某个条件后自动的触发执行
关键因素：
- 时间点：事件发生前before |事件发生后 after
- 事件： update delete insert
- 触发器包含两个对象 old（update,delete)，new （update，insert）
语法：
create trigger t_name t_time t_event on table_name for each row
begin
#sql语句......
end
#准备数据
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表 的时候 如果执行状态时失败的 那么将信息插入到errlog中
# 将结束符设置为|
delimiter |
create trigger cmd_insert 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_insert
# 触发器会在 插入数据到cmd表后执行
# 当插入的记录的success为no时 自动插入记录到errlog中
# 错误原因 遇到分号自动提交了 , 需要重定义 行结束符
delimiter |
#删除触发器
drop trigger cmd_insert;
#查看 所有触发器
show triggers；
#查看某个触发器语句
show create trigger t_name;
3.事物
事物就是一系列sql 语句的组合，比较重要
事物的特点：
- 原子性：指的是这个事物中的sql 语句是一个整体，不能拆分，要么都执行，要么全部都失败
- 一致性：事物执行结束后，表的关联关系一定是正确的，不会发生数据错乱
- 隔离性：事务之间相互隔离，数据不会互相影响，即使操作了同一张表，本质就是加锁，根据锁的粒度风味以下几个隔离级别
- 持久性:事务执行成功后数据将永久保存，无法恢复
注意: 在官方提供的cmd的mysql客户端下 事务是默认就开启,会将一条sql语句作为一个事务 会自动提交
#开启事务
start transaction
sql 语句......
sql 语句......
rollback #回滚操作 即撤销之前提交之前的所有操作
sql 语句......
commit #提交事务 一旦提交就会持久化
create table account(name char(10),money float);
start transaction;
update account set money = money - 100 where name = "一只穿云箭";
update account set money = money + 100 where name = "千军万马";
commit;
# 何时应该回滚 当一个事务执行过程中出现了异常时
# 何时提交 当事务中所有语句都执行成功时
start transaction;
update account est money =money -100 where name="一只穿云箭";
savepoint a;
update account est money =money -100 where name="一只穿云箭";
savepoint b;
update account est money =money -100 where name="一只穿云箭";
savepoint c;
select *from account;
# 回滚至某个保存点
rollback to 保存点名称
read committed
事务的用户隔离级别:
数据库使用者可以控制数据库工作在哪个级别下,就可与防止不同的隔离性问题
- read uncommitted --不做任何隔离,可能脏读,幻读
- read committed----可以防止脏读,不能防止不可重复读,和幻读,
- Repeatable read --可以防止脏读,不可重复读,不能防止幻读
- Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低
幻读 是因为 别人在执行 插入 和删除
不可重复度 是因为 比人在做update
修改隔离级别:
修改全局的
set global transaction isolation level read committed;
或者：
set @@tx_isolation = "asasasasas-read";
修改局部
set session transaction isolation level read committed;
@@系统内置变量
@表示用户自定义的变量
4.存贮过程
存储过程：是任意的sql语句的组合,被放到某一个存储过程中,类似于一个函数,有一个函数,有参数,还是函数体
其中可以包含任何的sql语句,逻辑处理,事务处理,所有的我们学过的sql语句都可以放到里面
三种数据处理方式
1.应用程序只关注业务逻辑,所有与数据相关的逻辑封装到mysql中
优点:应用程序要处理的事情变少了, 可以减少网络传输
缺点:增加了人力成本,沟通成本,降低整体开发效率
2.应用程序既要处理业务逻辑,还要自己编写sql语句
优点:降低了沟通成本,人力成本
缺点:网络传输增加,sql语句的编写非常繁琐,易出错
3.通过ORM框架 对象关系映射 自动生成sql语句并执行
优点:不需要要在编写sql语句,明显提升和开发速度
缺点: 不够灵活,应用程序开发者和数据库完全隔离了,可能导致仅关注上层开发,而不清楚底层原理
#使用存储过程：
create procedure p_name(p_type p_name p_date_type)
begin
sql.......
end
p_type 参数的类型 in输入 out输出 inout即可输出也可输入
p_name 参数的名字
p_data_type 参数的数据类型 如 int float
delimiter |
create procedure add1(in a float,in b float,out c float)
begin
set c = a + b;
end|
delimiter;
#调用
set @res = 0;
call add1(100,10,@res);
#删除
drop procedure 名称；
#查看
show create procedure 名称；
# 查看全部 db02库下的所有过程
select name from mysql.proc where db = 'day41' and type = 'PROCEDURE';
delimiter |
create procedure transfer2(in aid int,in bid int,in m float,out res int)
begin
declare exit handler for sqlexception
begin
# 异常处理代码
set res = 99;
rollback;
end;
start transaction;
update account set money = money - m where id = aid;
update account set money = moneys + m where id = bid;
commit;
set res = 1;
end|
delimiter ;
5.函数
create funcation func_name(参数 类型) 函数体
returns 返回值的类
return 返回值
delimiter |
create function add2(a int,b int)
returns int
return a + b|
delimiter ;
#查看创建语句
show create function name;
#查看所有函数的状态
show function status;
#查看某个库下所有函数
select name from mysql.proc where db = "库名称" and type = "FUNCTION";
# 删除
drop function name;
6.备份与恢复
备份
mysqldump.exe
mysqldump -u用户名 -p密码 数据库 表名1 表名2 .... > 文件路径....
# 注意 第一个表示数据库 后面全都是表名
mysqldump -uroot -p day41 student >
#备份多个数据库
mysqldump -uroot -p111 --databases day41 day40 > x3x.sql
#指定 --databases 后导出的文件包含 创建库的语句 而上面的方式不包含
#备份所有数据
mysqldump -uroot -p111 --all-databases > all.sql
#自动备份
linux crontab 指令可以定时执行某一个指令
#恢复数据
没有登录mysql
mysql < 文件的路径
已经登录了MySQL
source 文件路径
注意: 如果导出的sql中没有包含选择数据库的语句 需要手动加上
7.流程控制
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 '表达式1'；
create table info(name char(20));
insert into info values("jack sbaro"),("jack rose"),("jerry sbaro"),("sbaro jerry"),("jerry");
# 注意: 不能使用类似 \w 这样的符号 需要找其他符号来代替
浙公网安备 33010602011771号