MySQL数据库二 Li.059
视图 视图介绍 视图：是一种虛拟存在的数据表，这个虚拟表并不在数据库中实际存在。 作用：将一些较为复系的查询语句的结果，封装到-个虚拟表中，后期再有相同需求时，直接道询该虚拟表即可 # 准备数据 create database mydb5; use mydb5; create table country( id int primary key auto_increment, NAME varchar(30) ); insert into country values (null,'中国'),(null,'美国'),(null,'俄罗斯'); create table city( id int primary key auto_increment, NAME varchar(30), cid int, constraint cc_fk1 foreign key (cid) references country(id) ); insert into city values (null,'北京',1),(null,'上海',1),(null,'纽约',2),(null,"莫斯科",3); 视图的创建和查询 创建视图语法: create view 视图名称 [(列名列表)] as 查询语句; 查询视图语法: select * from 视图名称; ## 创建视图 create view city_country (city_id,city_name,country_name) as select c1.id,c1.NAME,c2.NAME from city c1, country c2 where c1.cid = c2.id; select * from city_country; 修改视图数据语法: update 视图名称 set 列名=值 where 条件; 修改视图的数据,原表的数据也会修改 ## 修改视图数据 update city_country set city_name ='深圳' where city_name='北京'; 修改视图结构语法: alter view 视图名称 (列名列表) as 查询语句; 删除视图语法: drop view [if exists] 视图名称; ## 修改视图结构 alter view city_country (city_id,city_name,NAME) as select c1.id,c1.NAME,c2.NAME from city c1, country c2 where c1.cid = c2.id; ## 删除视图 drop view if exists city_country; 数据库备份和恢复 备份-命令行方式 登录到MySQL服务器, mysqldump -uroot -p 数据库名称>文件保存路径(sql结尾文件) 恢复 登录MySQL数据库 删除已备份的数据库 重新创建名称相同的数据库 使用该数据库 导入文件执行: source 备份文件全路径 # 备份-命令行操作 mysqldump -uroot -p backupdatabase > /root/backupdatabase.sql # 恢复-命令行操作 mysqldump -uroot -p show databases; drop backupdatabase; create database backupdatabase; use backupdatabase; source /root/backupdatabase.sql; 备份-使用Datagrip Export with ‘mysqldump’ 恢复 使用Datagrip 删除已备份的数据库 重新创建名称相同的数据库 在数据库右键 Run SQL Script, 选择导出的文件.sql 存储过程和函数介绍 存储过程和函数是事先经过编译并存储在数据库的一段SQL说一句的集合 存储过程和函数的好处 提高代码的复用性 减少数据在数据库和应用服务器之间的传输, 提高效率 减少代码层面的业务处理 存储过程和函数的区别 存储函数必须有返回值 存储过程可以没有返回值 存储过程的创建和调用 创建存储过程 # 修改结束分隔符 delimiter $ # 创建存储过程 create procedure 存储过程名称(参数列表) BEGIN SQL语句列表; END$ # 修改结束分隔符 DELIMITER ; 调用存储过程 CALL 存储过程名称(实际参数); ## 存储过程数据准备 create database mydb6; use mydb6; create table student( id int primary key auto_increment, NAME varchar(20), age int, gender varchar(5), score int ); insert into student values (null,'张三',23,'男',95), (null,'李四',24,'男',98), (null,'王五',25,'女',100), (null,'赵六',26,'女',90); # 按照性别进行分级, 查询每组学生的总成绩. 按钮总成绩的升序排序 select gender,SUM(score) as getSum from student group by gender order by getSum asc; delimiter $ create procedure stu_group() BEGIN select gender,SUM(score) as getSum from student group by gender order by getSum asc; END $ delimiter ; call stu_group(); 存储过程的查看和删除 查看数据库中所有的存储过程 select * from mysql.proc where db=‘数据库名称’; 删除存储过程 drop procedure [if exists] 存储过程名称; # 查看存储过程 select * from mysql.proc where db='mydb6'; # 删除存储过程 drop procedure if exists stu_group; 存储过程语法 - 变量 定义变量: declare 变量名 数据类型 [default 默认值]; 变量赋值方式一: set 变量名=变量值; 变量赋值二: select 列名 into 变量名 from 表名 [where 条件]; # 按照性别进行分级, 查询每组学生的总成绩. 按钮总成绩的升序排序 select gender,SUM(score) as getSum from student group by gender order by getSum asc; delimiter $ create procedure stu_group() BEGIN select gender,SUM(score) as getSum from student group by gender order by getSum asc; END $ delimiter ; call stu_group(); # 查看存储过程 select * from mysql.proc where db='mydb6'; # 删除存储过程 drop procedure if exists stu_group; delimiter $ create procedure pro_test1() begin declare num int default 10; select num; end $ delimiter ; call pro_test1(); delimiter $ create procedure pro_test2() begin declare NAME varchar(10); set NAME='存储过程'; select NAME; end $ delimiter ; call pro_test2(); delimiter $ create procedure pro_test3() begin declare woman_sum_score,man_sum_score int; select sum(score) into woman_sum_score from student where gender='女'; select sum(score) into man_sum_score from student where gender='男'; select woman_sum_score,man_sum_score; end $ delimiter ; call pro_test3(); 存储过程语法 - if语句 if语句 if 判断条件1 then 执行的SQL语句1; [elseif 判断条件2 then 执行的SQL语句2] ... [else 执行的SQL语句n] end if; # 定义变量int 存储班级总成绩 # 定义vachar变量量, 用于存储分数描述 # 根据总成绩判断: 380分以上 学习优秀, 320~380 学习不错, 320以下 学习一般 delimiter $ create procedure pro_test4() begin declare all_sum_score int; declare info varchar(30); select sum(score) into all_sum_score from student; if all_sum_score>380 then set info='学习优秀'; elseif (all_sum_score<=380 && all_sum_score >=320) then set info='学习不错'; else set info='学习一般'; end if; select all_sum_score,info; end $ delimiter ; call pro_test4(); 存储过程语法 - 参数传递 存储过程的参数和返回值 delimiter $ create procedure 存储过程名称 ([IN|OUT|INOUT] 参数名 数据类型) begin SQL语句; end$ delimiter ; 参数 IN: 代表输入参数,需要由调用者传递实际数据(默认) OUT: 代表输入参数, 该参数可以作为返回值 INOUT: 代表既可以作为输入参数, 也可以作为输入参数 delimiter $ create procedure pro_test5(IN all_sum_score int,OUT info varchar(30)) begin if all_sum_score>380 then set info='学习优秀'; elseif (all_sum_score<=380 && all_sum_score >=320) then set info='学习不错'; else set info='学习一般'; end if; end $ delimiter ; call pro_test5(320,@info); call pro_test5((select sum(score) from student),@info); select @info; 存储过程语法 - while循环 while循环语法 初始化语句; while 条件判断语句 do 循环体语句; 条件控制语句; end while; delimiter $ create procedure pro_test6() begin declare result int default 0; declare i int default 1 ; while i < 101 do if i%2=0 then set result = result + i; end if; set i = i + 1; end while; select result; end $ delimiter ; call pro_test6; 存储函数 存储函数和存储过程是非常相似的, 区别在于存储函数必须有返回值 创建存储函数 delimiter $ create function 函数名称(参数列表) returns 返回值类型 begin SQL语句列表; RETURN结果; end$ delimiter ; 调用存储函数 select 函数名称(实际参数); 删除存储函数 drop function 函数名称; delimiter $ create function fun_test1() RETURNS int begin declare num int; select count(*) into num from student where score>95; return num; end $ delimiter ; select fun_test1(); drop function fun_test1; MySQL 触发器 触发器是与表有关的数据库对象, 可以在insert,update,delete之前或之后触发并执行触发器中定义的SQL语句. 这种特性可以协助应用系统在数据库端确保数据的完整性,日志记录,数据校验等操作. 使用别名NEW和OLD来引用触发器中发生变化的内容记录 触发器分类 触发器类型 OLD NEW INSERT型触发器 无(插入前无数据) NEW表示将要或者已经新增的数据 UPDATE型触发器 OLD表示修改之前的数据 NEW表示将要或已经修改后的数据 DELETE型触发器 OLD表示将要或者已经删除的数据 无(删除删除后状态无数据) 触发器的操作 创建触发器 delimiter $ create trigger 触发器名称 before|after insert|update|delete on 表名 fro each row begin 触发器要执行的功能; end $ delimiter ; # 数据准备 create database mydb7; use mydb7; create table account( id int primary key auto_increment, NAME varchar(20), money double ); insert into account values (null,'张三',1000),(null,'李四',1000); create table account_log( id int primary key auto_increment, operation varchar(20), operation_time datetime, operation_id int, operation_params varchar(200) ); # 插入触发器 delimiter $ create trigger account_insert after INSERT on account for each row begin insert into account_log values (null,'INSERT',now(),new.id, CONCAT('插入后{id=',new.id,',name=',new.NAME,',money=',new.money,'}')); end $ delimiter ; insert into account values (null,'王五',2000); select * from account; select * from account_log; # 更新触发器 delimiter $ create trigger account_update after UPDATE on account for each row begin insert into account_log values (null,'UPDATE',now(),new.id, CONCAT('更新前{id=',old.id,',name=',old.NAME,',money=',old.money,'}', '更新后{id=',new.id,',name=',new.NAME,',money=',new.money,'}')); end $ delimiter ; select * from account; update account set money=2000 where id=2; select * from account_log; # 删除触发器 delimiter $ create trigger account_delete after DELETE on account for each row begin insert into account_log values (null,'DELETE',now(),old.id, CONCAT('删除前{id=',old.id,',name=',old.NAME,',money=',old.money,'}')); end $ delimiter ; select * from account; delete from account where id=3; select * from account_log; 触发器的操作 查看触发器: show triggers; 删除触发器: drop trigger 触发器名称; # 查看触发器 show triggers ; # 删除触发器 drop trigger account_delete; MySQL 事务 事务: 一条或多条SQL语句组成一个执行单元, 其特点是这个单元要么同时成功要么同时失败 单元中的每条SQL语句都相互依赖, 形成一个整体 如果某条SQL语句执行失败或者出现错误, 那么整个单元就会撤回到事务最初的状态. 如果单元中所有的SQL语句都执行成功, 则事务就顺利执行. 事务的操作 开启事务: start transaction; 回滚事务: rollback; 提交事务: commit; # 准备数据 create database db8; use db8; create table account( id int primary key auto_increment, NAME varchar(20), money double ); insert into account values (null,'张三',1000),(null,'李四',1000); select * from account; # 开启事务 start transaction ; update account set money=money-500 where NAME='张三'; update account set money=money+500 where NAME='李四'; # 回滚事务 rollback ; # 提交事务 commit ; 事务的提交方式 事务提交方式的分类 自动提交(MySQL默认) 手动提交 查看事务提交方式 select @@autocommit; 修改事务提交方式 set @@autocommit=数字; # 查询事务的提交方式 select @@autocommit; # 修改事务的提交方式 # 默认是1, 不可修改 set @@autocommit=0; 事务的四大特征 原子性(Atomicity) 原子性是指事务包含的所有操作要么全部成功 ，要么全部失败回滚。 因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。 一致性(Consistency) 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。 也就是说一个事务执行之前和执行之后都必须处于一致性状态。 隔离性(isolcation) 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务。 不能被其他事务的操作所千扰，多个并发事务之问要相互隔离。卜 持久性(durability) 持久性是指 一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。 事务的隔离级别 事务的隔离级别 多个客户端操作时，各个客户端的事务之间应该是隔离的，相互独立的，不受影响的。 而如果多个事务操作同一批数据时，就会产生不同的问题，我们需要设置不同的隔离级别来解决这些问题。 隔离级别分类 隔离级别 会引发的问题 read uncommitted 读末提交 脏读、不可重复读、幻读 read committed 读已提交 不可重复读、幻读 repeatable read 可重复读 幻读 serializable 串行化 无 引发的问题 问题 现象 脏读 在一个事务处理过程中读取到了另一个未提交事务中的数据,导致2次查询结果不一致 不可重复读 在一个事务处理过程中读取到了另一个事务中修改并已提交的数据,导致2次查询结果不一对我 幻读 查询某数据不存在,准备插入此记录,但执行插入时发现此记录已存在,无法插入.或查询数据不存在执行删除操作,却发现删除成功 事务的隔离级别 查询数据库隔离级别 select @@tx_isolation; 修改数据库隔离级别(需要重新连接) set global transaction isolation level 级别字符串; # 查询事务隔离级别 select @@tx_isolation; # 修改事务隔离级别(修改后需要重新连接) set global transaction isolation level read committed ; 隔离级别问题演示 脏读问题演示 set global transaction isolation level read uncommitted ; start transaction ; update account set money=money-500 where NAME='张三'; update account set money=money+500 where NAME='李四'; # 提交事务 select * from account; rollback ; # 同时开启, 在上一用户transaction执行期间查询, 把transcation未commit的数据读取出来了. start transaction ; select * from account; COMMIT; 解决脏读设置为: set global transaction isolation level read committed ; ...