day37_mysql扩展知识

目录

• 1、事务
  • 1.1 事务
  • 1.2 事务的特征
  • 1.3 存储引擎
• 2、视图
  • 2.1 什么是视图
  • 2.2 使用视图
    • 2.2.1 创建视图
    • 2.2.2 使用视图
    • 2.2.3 修改视图
    • 2.2.4 删除视图
    • 2.2.5 注意事项
• 3、触发器
  • 3.1 创建触发器
  • 3.2 使用触发器
  • 3.3 删除触发器
• 4、存储过程
  • 4.1 什么是存储过程
  • 4.2 存储过程优缺点
  • 4.3 创建存储过程
    • 4.3.1 无参
    • 4.3.2 有参
  • 4.4 执行存储过程
  • 4.5 删除存储过程
• 5、函数
• 6、运维方向
  • 6.1 数据库备份
  • 6.2 重新导入
• 7 三大范式
  • 7.1 范式
  • 7.2 三大范式：第一范式、第二范式、第三范式

1、事务

1.1 事务

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

即操作数据前设置事务开始，数据操作完成，提交事物

操作数据期间，数据只是临死的，只有提交事务之后，才能真正保存到数据库

语法：

start transaction;
'''数据操作'''

# 若有异常，即可回滚到start transaction;
rollback;

# 若无异常，提交commit  若没有提交，那么别的用户查看该数据表不会更新，并且当前用户退出后再登陆查看也不会保存数据
commit;

1.2 事务的特征

原子性（Atomicity）：原子意为最小的粒子，即不能再分的事务，要么全部执行，要么全部取消

一致性（Consistency)：指事务发生前后，数据的总额依然匹配

隔离性（Isolation)：简单点说，某个事务的操作，对其他事务是不可见的

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

1.3 存储引擎

InnoDB：支持事务，支持行锁

MyIsam：不支持事务，支持表锁

mysql5.5版本以后，默认使用InnoDB

建表的时候，会自动设置存储引擎，也可手动设置

create table user(
id int,
name varchar(32),
salary int not null default 0,
)engine=InooDB charset utf8;

2、视图

2.1 什么是视图

视图是一个虚拟表，本质是根据sql语句获取动态的数据集，并为其命名

使用视图名即可获取结果集，并将结果集当作表来使用

使用视图保存临时表，存放在数据库中

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

2.2 使用视图

2.2.1 创建视图

语法：

create view 视图名称 as sql语句

2.2.2 使用视图

视图存储的是临时表，即将sql语句的结果集作为一个表，因此我们在需要用到这个语句时，或者需要用到这个结果集里的数据时，就能使用视图，将之当成一个表来使用，无需再重写sql语句

2.2.3 修改视图

语法：

alter view 视图名称 as sql语句

2.2.4 删除视图

语法：

drop view 视图名称

2.2.5 注意事项

1、对视图的操作会改变原表数据

2、虽然无需再重写sql语句，但是效率并不高，甚至还不如重写sql语句

3、一旦sql需要修改，且涉及到视图的部分，那么必须去数据库中修改，因为视图时存储在数据库中的，很麻烦

4、在涉及多个表的情况下，根本无法修改视图中的记录

3、触发器

使用触发器，可以定制用户对表进行【增删改】操作时前后的行为，需要注意，没有查询

3.1 创建触发器

语法：

create 触发器名 触发条件 数据库操纵语言DML on 表名
[for each row]
when (condition)
declare
begin
 --触发器代码  #  触发器的程序体可以是一句sql语句，也可以是begin和end包含起来的多条语句
end;

触发条件：before/after/instead of

数据库操纵语言DML：insert/update/delete

[for each row]:可选项，选了，说明该触发器是一个行级触发器，代表处理每一条记录都会执行触发器；不选，代表是一个语句级别的触发器，每个DML语句触发一次

when (condition):当为行级触发器时，可以设置触发器的响应条件，当操作的记录满足condition时，触发器才被执行;condition中可以通过new和old对象来引用操作的记录

delimiter的作用

设定sql分隔符的命令，默认为;

当使用begin和end包含多个sql语句时，由于;是分隔符，解释器遇到它就会执行sql语句，但是此时没有检测和begin匹配的end就会报错

因此，在这个情况下，需要将sql分隔符设定改成别的，在end之后再改回默认值;

实例：

# 创建触发器：当向tb1表中添加一条数据的同时， 向tb2表添加一条数据

delimiter //
				
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON t2 FOR EACH ROW
BEGIN
INSERT INTO t3 (NAME) VALUES ('aa');
END //

delimiter ;

查看触发器结构：

语法：

show triggers\G;

3.2 使用触发器

触发器无法被用户直接调用，只有再触发条件出发时，才会被动执行

3.3 删除触发器

语法：

drop trigger 触发器名

4、存储过程

4.1 什么是存储过程

存储过程包含了一系列的可执行sql代码

存储过程存放于mysql中，通过调用它的名字可以执行内部的一堆sql

4.2 存储过程优缺点

优点：

1、用于替代程序写的sql语句，实现程序与sql解耦

2、基于网络传输，传别名的数据量小，而直接传sql数据量大

缺点：

程序扩展性差

4.3 创建存储过程

4.3.1 无参

delimiter //
		
create procedure 存储过程别名()
BEGIN
sql语句
END //
			
delimiter ;

实例：

delimiter //
		
create procedure p1()
BEGIN
select * from user where id=2;
END //
			
delimiter ;

4.3.2 有参

delimiter //
		
create procedure 存储过程别名(
		in   	# 用于传入参数
		out  	# 用于返回值
		inout   # 既可以传入又可以当作返回值)
BEGIN
sql语句
END //
			
delimiter ;

实例：

delimiter //
		
create procedure p1()
BEGIN
select * from user where id=2;
END //
			
delimiter ;

4.4 执行存储过程

语法：

call 存储过程别名();

4.5 删除存储过程

语法：

drop procedure 存储过程别名  # 注意，不带括号

5、函数

mysql提供了许多内置函数，

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

6、运维方向

6.1 数据库备份

语法：

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

6.2 重新导入

语法：

source .sql备份文件路径

7 三大范式

7.1 范式

1、设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小

2、目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）

7.2 三大范式：第一范式、第二范式、第三范式

第一范式：数据库表的每一列必须是一个值唯一确定的信息，不能是列表，集合等不确定信息

第二范式：

1、在第一范式的基础上

2、数据库的表中每一条记录都是唯一的，依赖于主键来实现

第三范式：

1、在第二范式的基础上

2、多表之间的数据可以通过主键唯一找到的时候，这些数据字段只能在一张表中，避免冗余