mysql 中 add2_MySQL--高级

视图

什么是视图

视图就是一张虚拟的表, 通过一张表或多表查询构成的

为什么要是用视图

当我们查询一些表时， 我们的sql语句非常复杂， 如果每一次都要写一遍的话，无疑会疯的，所以我们可以通过视图来避免这样的操作

又或者我们只想向别人展示部分数据而不是全部，也可以通过视图实现，例如工资表，员工只能看到自己的那部分，而老板可以看到所有人的

所以视图有两大作用：

避免重复编写相同的sql语句

进行数据隔离

使用方法

创建视图语法

create [or repalce] view view_name as sql查询语句

or replace 表示当视图存在时，替换原来的视图

视图的使用方法

# 查看视图

1. desc view_name; # 查看数据结构

2. show create view view_name; # 查看创建语句

# 修改视图

alter view view_name as 新的sql语句;

# 删除视图

drop view view_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;

+------+------+--------+

| s_id | name | class |

+------+------+--------+

| 1 | tom | 二班 |

| 2 | jack | 二班 |

| 3 | rose | 三班 |

+------+------+--------+

注意：

当原表发生变化时，视图也会进行相应的变化

当修改视图时，原表也会进行变化，所以一般不要对视图进行修改

触发器

触发器是一段与某个表相关的sql语句，会在某一个时间点，满足某个条件后自动触发执行，可以用来自动记录一些东西，比如：当某个表被修改时，自动记录一些数据，执行sql语句

这其中有两个关键因素：

时间点

事件发生前 before

事件发生后 after

事件

update

insert

delete

触发器自动包含两个对象

old 旧的数据，update、delete中可用

new 新的数据，update、insert中可用

使用方法

创建触发器语法

create trigger t_name t_time t_event on table_name for each row

begin

sql……;

end |

补充：delimiter是对结束符进行重载，主要是因为在触发器中的sql语句要添加;，但是在终端中会默认;为结束了，所以我们需要进行结束符重载

触发器的使用方法

# 删除触发器

drop trigger t_name;

# 查看所有触发器

show triggers;

# 查看某个触发器的语句

show create trigger t_name;

案例

# 准备数据

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表的时候，如果执行状态为失败，那么将信息插入到errlog中

# 1. 创建一个触发器叫cmd_insert

# 2. 触发器会在插入数据到cmd表后执行

# 3. 当插入的记录success为no时，自动插入到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.sun_time);

end if;

end |

delimiter ; # 还原结束符

事务 *****

什么是事务

事务就是一系列sql语句的组合，是一个整体

为什么需要事务

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

例如转账操作,

从原有账户减去转账金额

给目标账户加上转账金额

若中间突然断电了或系统崩溃了,钱就不翼而飞了!

注意：在官方提供的cmd的客户端下，事务是默认开启的，会将一条sql语句作为一个事务

事物的特点

原子性：指的是这个事务中的sql语句是一个整体，不能进行拆分，要么全部成功，要么全部失败

一致性：事务执行结束后，表的关联关系一定是正确的，不会发生数据错乱

隔离性：事务之间相互隔离，数据不会相互影响，即使操作了同一个表，本质就是加了互斥锁，根据锁的粒度不同分为几个隔离级别

事务的用户隔离级别：

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

read uncommitted：可读取未提交，不做任何隔离，不可防止脏读、不可重复读和幻读

read committed：可读已提交，可防止脏读，不防止不可重复读和幻读

repeatable read：可重复读，可防止脏读和不可重复度，不防止幻读

Serializable：序列化，将数据库运行串行化，可防止所有问题

补充

脏读：也就是当数据库的一个事务A正在使用一个数据但还没有提交，另外一个事务B也访问到了这个数据，还使用了这个数据，这就会导致事务B使用了事务A没有提交之前的数据

不可重复度：在一个事务A中多次操作一个数据，在这两次或多次访问这个数据的中间，事务B也操作此数据，并使其值发生了改变，这就导致同一个事务A在两次操作这个数据的时候值不一样，这就是不可重复读

幻读：是指事务不独立执行产生的一种现象。事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。这样就会导致当A本来执行的结果包含B执行的结果，这两个本来是不相关的，对于A来说就相当于产生了“幻觉”

持久性：事务执行成功后，数据将永久保存，无法恢复

使用方法

语法

# 开启事务

start transaction;

sql...语句

sql...语句

rollback # 回滚操作

sql...语句

commit; # 提交事务，一旦提交就持久化了

案例

create table account(

name char(10),

money float

);

insert into account values('一只穿云箭',1000);

insert into account values('千军万马',1000);

# 案例一：转账操作

delimiter | # 重载结束符

start transaction;

update account set money = money - 100 where name = "一只穿云箭";

update account set money = money + 100 where name = "千军万马";

commit;

delimiter ;

# 当一个事务执行过程中出现了异常，就需要进行回滚

# 当事务中的语句全部执行成功了，就进行提交

# 案例二：保存点

delimiter |

start transaction;

update account set money = money - 100 where name = "一只穿云箭";

savepoint a;

update account set money = money - 100 where name = "一只穿云箭";

savepoint b;

update account set money = money - 100 where name = "一只穿云箭";

savepoint c;

select * from account

rollback to 保存点名字

delimiter ;

修改隔离级别

修改全局的

set global transaction isolation level read committed;

或者:

set @@tx_isolation = "asasasasas-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 参数的类型

1. in 输入

2. out 输出

3. inout 输入输出

p_name 参数的名字

p_date_type 参数的数据类型

'''

# 注意：out参数必须是一个变量，不能是值

# 调用

call p_name(p_name p_date_type);

# 删除

drop procedure p_name;

# 查看

show create procedure p_name;

# 查看全部db02库下的存储过程

select name from mysql.proc where db = "库名" and type = "procedure";

案例

# 案例一

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(12.4,3,@res);

select @res;

# 案例二

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 ;

函数

函数和python中一样

内置函数

时间相关

字符串相关

数字相关

其他函数

自定义函数

语法

# 创建函数

create function func_name(参数 类型)

函数体

returns 返回值的类

return 返回值

# 查看函数创建语句

show create functiojn func_name;

# 查看所有函数状态

show function status;

# 查看db02库下的函数

select name from mysql.proc where db = "库名" and type = "function";

# 删除函数

drop function func_name;

案例

delimiter |

create function add2(a int,b int)

returns int

return a + b|

delimiter ;

# 执行函数

select 函数名(参数1,参数2……);

备份与恢复

备份

# 备份某个库里的表

mysqldump -u用户名 -p密码 数据库 表名1 表名2…… > 文件路径

# 备份库

mysqldump -u用户名 -p密码 --databases 数据库1 数据库2…… > 文件路径

# 备份所有数据

mysqldump -u用户名 -p密码 --all-databases > 文件路径

恢复

# 没有登录mysql

mysql < 文件路径

# 已经登陆mysql

source 文件路径

注意: 如果导出的sql中没有包含选择数据库的语句 需要手动加上

流程控制

语法

# number one

if 条件 then

语句;

end if;

# number two

# if elseif

if 条件 then

语句1;

elseif 条件 then

语句2;

else 语句3;

end if;

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 值

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

案例

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 ;