day 37

数据库补充

1. 事物

事务用于将某些操作的多个 sql 作为原子性操作，一旦某一个出现错误，即可回滚到原来状态，从而保证数据库数据完整性。

• 使用方法：

  start transaction;	# 创建一个事物
  
  SQL指令1;
  SQL指令2;
  ...
  SQL指令n；
  
  # 如果数据出现异常，或由于某些原因导致不能继续进行下去，可以随时将事物开始后的数据操作取消
  rool back；# 回滚数据到事物开始前	
  
  # 如果所有操作正常完成，在提交事物前，别的用户是不能看到更改后的数据的，且当前用户如果没有提交，操作也不会保存
  commit;	# 提交事物更新数据
  

• 事物的特性：(ACID)

  1. 原子性 (Atomicity)：原子意为最小的粒子，即不能再分的事务，要么全部执行，要么全部取消
  2. 一致性 (Consistency)：指事务发生前后，数据的总额依然匹配
  3. 隔离性 (Isolation)：简单点说，某个事务的操作，对其他事务是不可见的
  4. 永久性 (Durability)：当事务完成后，其影响应该保留下来，不能撤销，只能通过“补偿性事务”来抵消之前的错误

2. 视图

视图是一个虚拟表，本质是根据 SQL 语句获取动态的数据集，并为其命名使用视图名即可获取结果集，并将结果集当作表来使用，视图就是用来保存这个虚拟表的。

视图也是表，所以可以使用 show tables; 进行查看。

• 使用方法

  # 一般用于需要大量重复使用某一个查询 SQL，每次都需要重写SQL指令会很麻烦，可以使用视图将这个查询 SQL 的结果虚拟成一个表进行保存，
  
  创建视图
  create view 视图名 as SQL指令;	# 将 SQL 指令的结果保存成一个视图，并命名
  
  使用视图
  select * from 视图名
  
  修改视图中的数据
  	- 修改
  		update 视图名 set 列=值;
  	- 插入
  		insert into 视图名 values(值1,值2,...)
  		
  删除视图
  drop view 视图名;
  

• 注意

  1. 对视图的操作会改变原表数据
  2. 视图虽然可以不用重复写相同的 SQL 的指令，但是查询效率低，创建视图不如想办法优化 SQL 指令
  3. 如果 SQL 指令需要修改，如果涉及到视图部分，必须去数据库中修改，需要浪费大量时间和 DBA 沟通
  4. 如果涉及多个表，无法修改视图中的记录

如果使用 SQL 过分依赖数据库中的视图，即具有强耦合性，那么扩展 SQL 将极为不便，因此不推荐使用。

3. 触发器

定制用户对表进行增、删、改操作前后的行为。

• 创建触发器

  create or replace trigger 触发器名 (before/after/instead of) (inser/delete/update) on 表名 
  [for each row]
  when (condition)
  declare
  begin
  	触发器代码	# 一句或多句	
  end;
  
  (before/after/instead of)可以选择 before 或 after 或 instead of
  	- before：语句实施前执行触发器
  	- after：语句实施之后执行触发器
  	- instead of：用在对视图的更新上
  
  (inser/delete/update)：DML语句，可以选择一个或多个，如果选择多个，用 or 隔开
  
  [for each row]：可选项，如果注明了 for each row ，则说明了该触发器是一个行级的触发器，DML语句处理每条记录都会执行触发器；如果没有注明，则是一个语句级的触发器，每个DML语句触发一次。
  
  when 后的(condition)是触发器的响应条件，只对行级触发器有效，当操作的记录满足 condition 时，触发器才被执行，否则不执行
  
  condition 中可以通过 new 对象和 old 对象来引用操作的记录
  

• 使用触发器

  # delimiter：设定 SQL分隔符，默认为分号‘ ；’，mysql执行语句的标志
  
  创建触发器
  delimiter //
  # 当向表1中添加数据的时候，表2添加指定的数据
  create trigger tri_before_insert_tb1 before insert on t2 for each row
  begin
  	insert into t3 (name) values ('aa');
  end //
  
  delimiter ;
  
  查看触发器结构
  show teiggers\G
  
  删除触发器
  drop teigger 触发器名;
  

4. 存储过程

存储过程包含了一系列的可执行sql代码，存放于 MySQL 中，通过调用它的名字可以执行内部的一堆 SQL。

• 优点：

  1. 用于替代程序写的sql语句，实现程序与sql解耦
  2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

• 缺点： 扩展性差

1. 创建存储过程

   • 无参

     delimiter //
             
     create procedure 存储过程别名()
     begin
     SQL指令
     end //
                 
     delimiter ;
     

   • 有参

     delimiter //
             
     create procedure 存储过程别名(
             in      # 传入参数
             out     # 返回值
             inout   # 既可以传入参数又可以当作返回值
     begin
     SQL指令
     end //
                 
     delimiter ;
     

2. 执行存储过程

   # 无参数
   call 存储过程名();
   
   # 有参数，全 in
   call 存储过程名(1,2)
   
   # 有参数，有 in，out，inout
   set @t1=0;
   set @t2=3;
   call proc_name(1,2,@t1,@t2)
   

3. 删除存储过程

   drop procedure 存储过程名;
   

5. 数据库内置函数

MySQL中提供的内置函数

char_length(str)
	返回值为字符串 str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
	对于一个包含五个二字节字符集, length()返回值为 10, char_length()的返回值为5。

contact(str1,str2,...)
	字符串拼接
	如有任何一个参数为NULL ，则返回值为 NULL。

format(x,d)
	将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
	select format(12332.1,4); 结果为： '12,332.1000'
	instr(str,substr)
	返回字符串 str 中子字符串的第一个出现位置。

left(str,len)
	返回字符串 str 从开始的 len 位置的子序列字符。

lower(str)
	字符串变小写

upper(str)
	字符串变大写

ltrim(str)
	返回字符串 str ，其引导空格字符被删除

rtrim(str)
	返回字符串 str ，结尾空格字符被删去

substring(str,pos,len)
	获取字符串子序列

locate(substr,str,pos)
	获取子序列索引位置

repeat(str,count)
	返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count
	若 count <= 0,则返回一个空字符串。
	若str 或 count 为 NULL，则返回 NULL 。

replace(str,from_str,to_str)
	返回字符串 str 以及所有被字符串 to_str 替代的字符串 from_str

reverse(str)
	返回字符串 str ，顺序和字符顺序相反。

right(str,len)
	从字符串 str 开始，返回从后边开始len个字符组成的子序列

6. 数据库备份

将重要的数据另外保存一份，防止意外丢失、故障等问题

• 语法

  mysqldump -h 服务器 -u用户名 -p密码 数据库名 表1名, 表2名,.... > aaa.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
  

• 导入备份恢复数据

  source 备份文件路径.sql