1101 事务存储引擎触发器函数

目录

• 1. 事务
  • 为什么要使用
  • 使用
  • 特性
    • 原子性
    • 一致性
    • 隔离性
    • 持久性
• 2.存储引擎
  • InnoDB
  • MYIsam
  • 区别
• 3.视图
  • 定义
  • 增加视图
  • 查看视图
  • 删除视图
• 4.触发器
  • 语法
  • 增加触发器
  • 查看触发器
  • 删除触发器
• 5.存储过程
  • 定义
    • 优点
    • 缺点
    • 总结
  • 语法
  • 创建
  • 使用
  • 删除
• 6.函数
• 7.数据库备份
  • 语法

1. 事务

mysql主要用于处理操作量大,复杂度高的数据,比如在人员管理系统 你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在mysql中只有使用了InnoDB数据库引擎的数据库或表才支持事务
• 事务处理可以维护数据库的完整性,保证成批的sql语句要么全部执行,要么全部不执行
• 事务用来管理insert,update,delete语句

为什么要使用

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

eg: 转账 == 转入转出均成功,才能认为操作成功

使用

开启事务:	start transaction
sql语句:	
提交:		 commit	

回滚:		 rollback	// 影响所有,回滚到初始

start transaction;
--开启事物,在这条语句之后的sql将处在同一事务,并不会立即修改数据库
    
commit;
--提交事务,让这个事物中的sql立即执行数据的操作,

rollback;
--回滚事务,取消这个事物,这个事物不会对数据库中的数据产生任何影响 #（包括没有语法错误的数据变更语句） 

例子

1.// 创建文件夹
create database day111;
2.// 更改
use day111;
3.//创建表
create table aaa(
	id int auto_increment primary key,
    name varchar(32) not null default '',
    salary int not null default 0
)charset utf8;
	// Query OK, 0 rows affected (0.02 sec)
4.//添加数据
insert into aaa (name,salary) values ('lucy',100);
insert into aaa (name,salary) values ('jack',100);
5.//查询数据
    select * from aaa;
/*	+----+------+--------+
    | id | name | salary |
    +----+------+--------+
    |  1 | lucy |    100 |
    |  2 | jack |    100 |
    +----+------+--------+
    2 rows in set (0.00 sec)	*/

解决方法

1.//开启事务:
    start transaction;
2.//开始转账操作
    update aaa set salary=50 where name='lucy';
3.//查询余额
    select * from aaa;
/*	+----+------+--------+
    | id | name | salary |
    +----+------+--------+
    |  1 | lucy |     50 |
    |  2 | jack |    100 |
    +----+------+--------+
    2 rows in set (0.00 sec)	*/

4. // 在另一客户端中查看余额
mysql> select * from aaa;
/*  +----+------+--------+
    | id | name | salary |
    +----+------+--------+
    |  1 | lucy |    100 |
    |  2 | jack |    100 |
    +----+------+--------+
    2 rows in set (0.00 sec)	*/ 事务间是相互独立的
5. // 修改jack数据(模拟收账)
    update aaa set salary=150 where name='jack';

6. //提交事务
    commit;

7.两个客户端查询结果 结果一样
/*  mysql> select * from aaa;
    +----+------+--------+
    | id | name | salary |
    +----+------+--------+
    |  1 | lucy |     50 |
    |  2 | jack |    150 |
    +----+------+--------+
    2 rows in set (0.00 sec)

回滚

8.//回滚操作在开启事务之后,commit之前.回到之前
    start transaction;
	update aaa set salary=50 where name='lucy';
	select * from aaa;
    rollback;
	select * from aaa;

//回到了更改之前的数据
    

特性

原子性

事务是一组不可分割的单位,要么同时成功,要么同时不成功

一致性

事务前后的数据完整性应该保持一致

隔离性

多个用户并发访问数据时,一个用户的事务不能被其他用户的事务所干扰,多个并发事务间数据要相互隔离

持久性

一个事务一旦被提交,它对数据的改变就是永久的,即使数据库故障也不会对其有影响.

2.存储引擎

引擎是建表的规定,提供给表使用,而不是数据库

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)engine=innodb charset=utf8; #默认引擎为innodb，设置了配置文件后的默认编码为utf8。

InnoDB

• mysql 5.5以上engine默认添加innodb

• InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

MYIsam

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。

区别

1. InnoDB支持事务,MYIsam不支持
2. InnoDB支持行锁,MYIsam支持表锁.

InnoDB对数据行进行加锁,处理度较细小

3.视图

定义

存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色.

项目， 有100个SQl， 其中80个SQL都是：select * from user where name='xxx'; 

• 如果要对一张表或者多张表进行查询,可以通过写复杂的SQL语句来实现
• 如果要这些SQL语句存储为视图,那么查询的时候,就直接查询这个视图就可以了.

其相当于从原来的数据表中获取部分数据，然后新建一个只可创建、查询和删除的新表来存放这些数据（一般情况下），可以理解成把想要的数据部分截图下来保存，供以后查询用，此时视图只是为了满足某些数据查询而建立的对象。

例子

1. // 增加视图
create view v1 as select * from aaa ;
// create view v2 as select * from aaa where name='lucy';
2. // 查看视图
    select * from v2;
/*  +----+------+--------+
    | id | name | salary |
    +----+------+--------+
    |  1 | lucy |     50 |
    +----+------+--------+
    1 row in set (0.00 sec)			*/
3. //

增加视图

create view 视图名(列1,列2...) as select语句 ;

查看视图

select * from 视图名;

在视图创建完成后，就可以把其当做一个只读的表来用，此时就可以正常的进行查询了，举例：

mysql> create view aaa (name, psd) as select username, password from peoples;
#创建视图aaa，里面记录了peoples表的用户名和密码
mysql> select * from aaa;
#发现可以正常查询

查看当前库下的所有视图

show full tables where table_type like 'VIEW';

删除视图

drop view 视图名;

4.触发器

在表发生数据更新时,会自动触发的功能称之为触发器

当一个表在发生数据更新时，需要去完成一些操作，可以为具体数据更新的方式添加触发器。

当我下一个订单的时候订单表中需要增加一个记录,同时库存表中需要减一,这两个操作是同时发生的,并且前一个操作触发后一个操作

语法

delimiter //  #此行的作用是转换mysql执行语句的结尾标识
create trigger 触发器名 before|after insert|update|delete on 表名 for each row
begin 
    需要触发执行的sql代码们;
end //
delimiter ;

注：delimiter是用来修改sql的语句结束标识符
使语句中的 ; 不再结束

增加触发器

// 创建t1表
create table t1(
	id int auto_increment primary key,
    name varchar(32) not null 
)charset utf8;
	// 添加数据
	insert into t1 (name) values ('uzi'),('xiaogou');

// 创建t2表
create table t2(
	id int auto_increment primary key,
    name varchar(32) not null 
)charset utf8;
	// 添加数据
	insert into t2 (name) values ('55k'),('white');

添加触发器

delimiter //
create trigger t1_t2 before insert on t1 for each row
begin
    insert into t2 (name) values ('aa');
end //
delimiter ;
/*
给t2添加 t1触发器, 当给t1添加值时,t2自动增加数据'aa'
    而给t2加值,t1不会出现数据.
// 当向t1表中添加值的的同时,向t2表添加一条数据
*/




/*
mysql> insert into t1 (name) values ('nihao');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+---------+
| id | name    |
+----+---------+
|  1 | uzi     |
|  2 | xiaogou |
|  3 | nihao   |
+----+---------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+----+-------+
| id | name  |
+----+-------+
|  1 | 55k   |
|  2 | white |
|  3 | aa    |
+----+-------+
3 rows in set (0.00 sec)

mysql> insert into t2 (name) values ('buhao');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+-------+
| id | name  |
+----+-------+
|  1 | 55k   |
|  2 | white |
|  3 | aa    |
|  4 | buhao |
+----+-------+
4 rows in set (0.00 sec)

mysql> select * from t1;
+----+---------+
| id | name    |
+----+---------+
|  1 | uzi     |
|  2 | xiaogou |
|  3 | nihao   |
+----+---------+
3 rows in set (0.00 sec)

查看触发器

show triggers G;		# G 是将输出内容格式化

'''
*************************** 1. row ***************************
             Trigger: t1_t2
               Event: INSERT
               Table: t1
           Statement: begin
    insert into t2 (name) values ('aa');
end
              Timing: BEFORE
             Created: 2019-11-01 19:06:35.37
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

删除触发器

drop trigger 触发器名;

//	Query OK, 0 rows affected (0.01 sec)

5.存储过程

MySQL 5.0 版本开始支持存储过程。

定义

像一个sql函数

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

用于完成指定功能的sql语句块,类似于python中的函数

优点

将能指定功能的sql语句块建立成存储过程，不仅将sql语句逻辑化了，更是功能化了，那我们要完成相同的事，只需要重复使用建立的存储过程，就不需要再重复书写sql语句了。

• 存储过程可封装,隐藏复杂商业逻辑
• 存储过程可以回传值,也可接受参数
• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
• 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调校与撰写，受限于各种数据库系统。

总结

存储过程可以让sql语句具有 复用性, 从而提高开发效率

语法

delimiter //

create procedure 存储名 ()
BEGIN
sql语句; # 类似 select * from user where id=2;
END //

delimiter ;

注：
1.输入输出类型：in | out | inout
2.call 存储过程名(实参们)来调用存储过程
call 存储过程名(实参们)  sql语句中没有任何打印语句时执行后不会得到任何结果，查看结果需要执行查询语句（select 实参中的输出实参）

创建

delimiter //
create procedure pp11()
begin
select * from t1 where id =1;
end //

delimiter ;

使用

调用 call 存储名();

call pp11();
/*
mysql> call pp11();
+----+------+
| id | name |
+----+------+
|  1 | uzi  |
+----+------+
1 row in set (0.00 sec)

删除

drop procedure 存储名;
drop procedure pp11;
//	Query OK, 0 rows affected (0.01 sec)

6.函数

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

CONCAT(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个字符组成的子序列

更多函数:
http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions
		

7.数据库备份

将重要的数据保存下来

备份的文件在mysql中是mysqldump.exe

语法

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 表名,  表名,.... > aaa.sql

例子

在cmd中输入

#单库备份
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

# 重新导入：
mysql> source D:/test3.sql;