MySQL数据库5事务、视图、触发器、函数、数据库的备份

发布于 2019-12-12 17:36:14

9100

发布于 2019-12-12 17:36:14

文章被收录于专栏：python、mysql、go知识点积累python、mysql、go知识点积累

一、事务（important）

1.1什么是事务？

事务指一组操作要么成功要么失败，在成功修改数据前原来的数据不会受影响，如果修改成功则数据将被更改，如果失败，则原数据库数据不变。

思考：银行转账，转账人如果已经进行了转账操作，而在对方还未收到转账时如果发生网络故障，对方没有收到钱，这个转着账如何处理？这时候就用到了事务，转账要么成功，两边数据都修改，要么失败，两边的数据都不变。

代码演示

create table user (
id int auto_increment primary key ,
name varchar (32) not null default '',
salary int not null default 0)charset utf8;
insert into user (name,salary) values ('zgh',1000);
insert into user (name,salary) values ('xiaoyu',1000);

1.2解决办法

1.2.1事务的语法

start transaction ;
sql语句，即要执行的任务
commit / rollback;(commit 是提交，rollback 是撤回，这里的撤回是撤回事务里的所有SQL语句)

1.2.2使用事务解决转账问题代码演示

mysql> create table user (id int auto_increment primary key,
    -> name varchar(32) not null default '',
    -> salary decimal(10,2) not null default 0) charset utf8;
Query OK, 0 rows affected (0.62 sec)

mysql> insert into user(name,salary) values('zgh',1000);
Query OK, 1 row affected (0.13 sec)

mysql> insert into user(name,salary) values('xiaoyu',1000);
Query OK, 1 row affected (0.10 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set salary = 900 where name = 'zgh';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 | zgh    |  900.00 |
|  2 | xiaoyu | 1000.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> update user set salary = 1100 where name = 'xiaoyu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 | zgh    |  900.00 |
|  2 | xiaoyu | 1100.00 |
+----+--------+---------+
2 rows in set (0.01 sec)

mysql> commit;#在commit之前前面的所有对数据的操作都不会在数据库中生效，
#只有提交之后对数据库的修改才会真正的生效
Query OK, 0 rows affected (0.09 sec)

mysql> select * from user;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 | zgh    |  900.00 |
|  2 | xiaoyu | 1100.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

1.2.3rollback

代码演示

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set salary = 800 where name = 'zgh';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 | zgh    |  800.00 |
|  2 | xiaoyu | 1100.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> update user set salary = 1200 where name = 'xiaoyu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 | zgh    |  800.00 |
|  2 | xiaoyu | 1200.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from user;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 | zgh    |  900.00 |
|  2 | xiaoyu | 1100.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

1.3事务的特性(important)

原子性（atomicity)：原子意思是最小的粒子，即不能再分的事务，要么全部执行，要么全部取消（如银行转账）。

一致性(consistency)：指事务发生前和发生后，数据的总额依然匹配。

隔离性(isolation)：简单点说，某个事物的操作对其他事物不可见。

持久性(durability)：当时事务完成后，其影响会保留下来，不能撤销，只能通过“补偿性事务”来抵消之前的错误。

补偿事务就是再执行一个事物，把错误的数据修改过来。

二、存储引擎（important）

2.1 innodb

MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。现在公司一般使用innodb的比较多。

2.2 myisam

MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。

2.3两种引擎的区别

innodb支持事务，myisam不支持
innodb支持行锁，myisam支持表锁行锁和表锁参考此链接

三、视图

视图的功能就是将数据表显示出来，但是不能通过视图对表内的数据进行更改，相当于给原来的表格拍了一张照片，查看表格时可以不用写查询表格的SQL语句，直接通过查询视图来查看。

语法：增加视图：create view 视图名 as SQL语句；

删除视图：drop view 视图名；

例子

mysql> select * from user where name = 'zgh';
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | zgh  | 900.00 |
+----+------+--------+
1 row in set (0.00 sec)

mysql> create view v1 as select * from user where name = 'zgh';
Query OK, 0 rows affected (0.17 sec)

mysql> select * from v1;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | zgh  | 900.00 |
+----+------+--------+
1 row in set (0.04 sec)

mysql> select name from v1;
+------+
| name |
+------+
| zgh  |
+------+
1 row in set (0.00 sec)

注意：视图功能实际工作中尽量不要用，会消耗数据库的性能。

四、触发器

4.1简述

功能：当某一个命令执行时会触发另一个命令的执行。

应用场景：当网点上有人下单时，订单表中需要增加一条记录，同时库存表中需要减少1，这两个操作是前一个操作触发了后一个操作。

4.2用法

4.2.1增加

create table user1 (id int auto_increment primary key,
name varchar(32) not null default '',
salary decimal(10,2) not null default 0) charset utf8;

mysql> delimiter //
mysql> create trigger tri_before_insert_user before insert on user for each row begin insert into user1 (name) values ('zgh2');
    -> end //
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;#这里要加一个空格否则无法退出触发器的创建
mysql> insert into user(name) values ('xiaoyu');
Query OK, 1 row affected (0.10 sec)

mysql> select * from user1;
+----+----------+--------+
| id | name     | salary |
+----+----------+--------+
|  1 | guanghao |   0.00 |
|  2 | zgh2     |   0.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from user;
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
|  1 | zgh    |  900.00 |
|  2 | xiaoyu | 1100.00 |
|  3 | zgh1   |    0.00 |
|  4 | xiaoyu |    0.00 |
+----+--------+---------+
4 rows in set (0.00 sec)

4.2.2删除

语法：drop trigger 触发器名；

触发器的查看语法：

show triggers\G;

五、存储过程

存储过程如同编程语言中的函数，我们先定义好它的功能，然后在以后使用的时候只需要调用它就可以了。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

5.1存储过程的创建

mysql> delimiter //
mysql> create procedure p1()
    -> begin
    -> select * from user where id=1;
    -> end //
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter ;
mysql> call p1();#调用存储过程的方法
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | zgh  | 900.00 |
+----+------+--------+
1 row in set (0.12 sec)

Query OK, 0 rows affected (0.12 sec)

5.2存储过程的删除

drop procedure 存储过程名；

六、函数

这里的函数指的是MySQL内置的函数，在实际工作中涉及到函数部分的功能尽量在程序里面完成，不要使用MySQL的函数，否则会加大数据库的负担。

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

七、数据库的备份

目的：将重要的数据保存下来。

7.1数据库的备份

语法：

mysqldump -h 服务器 -u用户名 -p密码数据库名表名1，表名2……> 备份的文件名.sql

这里的服务器指的是服务器的ip注意ip不要加引号，直接跟在-p后面，备份程序在cmd或者其他环境下直接执行，不要在已经登录MySQL的环境下执行备份程序。

例子

PS C:\Users\ZGH> mysqldump -uroot -p123zgh test1 user > test_mysql.sql
#注意test_mysql.sql这里要加存储路径否则会默认c盘的这个相对路径
Warning: Using a password on the command line interface can be insecure.

7.2数据库的导入

如果这个sql文件是在windows的powershell里导出来的直接通过powershell或者cmd导入该文件会报如下错误。

ERROR:
ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'ÿþ-'.

原因：powershell导出的文件默认编码格式是utf-16，须要将其转换为ASCII编码格式才能正常导入，可以用windows文件转码命令进行转码具体可参考此链接。另外网上也有在线转码工具，不过为了避免这种错误的发生，还是直接用cmd去备份文件然后用cmd导入。

导入命令：source 地址；

例子

mysql> source D:/test2-u7-u6.sql;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......

注意：导入的时候不能出现和文件中相同的表名。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-11-01 ，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法