MySQL视图、触发器、事务、存储过程、函数、流程控制、索引
视图
视图的概念：通过SQL语句的执行得到的一张虚拟表，保存下来之后就称之为'视图'。
创建视图语法：
create view 视图名 as SQL语句
视图只能用于数据的查询，不能做增、删、改的操作，可能会影响原始数据，因为视图里面的数据是直接来源于原始表，而不是拷贝一份。
优点：如果需要频繁的使用一张虚拟表，可以考虑制作成视图，降低操作难度。
缺点：视图虽然看似很好用，但是会造成表的混乱，再使用show tables时，视图也会一并展示出来，会让人分不清这是表还是视图。
触发器
触发器概念：在对表数据进行增、删、改的具体操作下，自动触发的功能。
作用：专门针对表数据的操作，定制个性化配套功能。
| 触发器种类 | 含义 |
|---|---|
| before insert | 新增数据之前触发 |
| before update | 新增数据之前触发 |
| before delete | 删除数据之前触发 |
| after insert | 删除数据之后触发 |
| after update | 更新数据之后触发 |
| after delete | 更新数据之后触发 |
创建触发器语法：
delimiter $$ # 需要先将mysql默认的结束符由;换成$$
create trigger 触发器名字 before/after insert/update/delete on 表名 for each row
begin
触发后执行的SQL语句;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
查看当前库中所有的触发器信息：
show triggers;
删除当前库下指定的触发器信息：
drop trigger 触发器名称;
事务
事务的概念：由数据库操作语句构成，事务可以用来维护数据库的数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务的四大特性（ACID）：
- 原子性(A)：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
- 一致性(C)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
- 隔离性(I)：一个数据库可以有多个事务，事务之间是互相隔离的，彼此不干扰。
- 持久性(D)：事务处理结束后，对数据的修改就是永久的。
基本使用
| 使用 | 含义 |
|---|---|
| start transaction; | 开启事务 |
| rollback; | 事务回滚(返回执行事务操作之前的数据库状态) |
| commit; | 提交事务(提交后事务结束，无法回滚了) |
案例
# 创建表并添加数据
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values('jason',1000),('kevin',1000),('tank',1000);
# 开启事务
start transaction;
# 事务内容(SQL语句)
update user set balance=900 where name='jason';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='tank';
# 事务回滚
# rollback; 如果回滚了那么以上的数据都会变回原来的状态
# 提交事务
commit;
存储过程
存储过程相当于一个函数。
定义与调用：
无参存储过程：
delimiter $$
create procedure 存储过程名()
begin
SQL语句;
end $$
delimiter ;
调用：
call 存储过程名();
有参存储过程：
delimiter $$
create procedure 存储过程名(
in|out|inout 参数1 参数1类型,
in|out|inout 参数2 参数2类型,
...
)
begin
SQL语句;
end $$
delimiter ;
- in表示这个参数必须只能是传入不能被返回出去
- out表示这个参数可以被返回出去
- inout表示即可以传入也可以被返回出去
对于可以返回出去的参数，需要提前定义：
set @返回的参数=值; # 定义
call 存储过程(各项参数) # 调用存储过程
select @返回的参数 # 查看是否变化
# 比如有存储过程
create procedure p1(in m int, out res int)
set @res=10; # 定义返回的参数
call p1(5,@res) # 调用存储过程
select @res # 查看是否变化
| 关于存储过程的SQL语句 | 作用 |
|---|---|
| show create procedure 存储过程名; | 查看存储过程具体信息 |
| show procedure status; | 查看所有存储过程 |
| drop procedure 存储过程名; | 删除存储过程 |
函数
mysql有许多内置的函数。
| 内置函数 | 作用 |
|---|---|
| Trim(字符串) | 移除字符串两边空格 |
| Trim(leading 字符 from 字符串) | 移除开头的指定字符 |
| Trim(both 字符 from 字符串) | 移除两边指定字符 |
| Trim(trailing 字符 from 字符串) | 移除末尾的指定字符 |
| LTrim(字符串) | 移除字符串左边空格 |
| RTrim(字符串) | 移除字符串右边空格 |
| Lower(字符串) | 字符串全转小写 |
| Upper(字符串) | 字符串全转大写 |
| Left(字符串,m) | 获取字符串左边m个字符 |
| Right(字符串,m) | 获取字符串右边m个字符 |
| Soundex(字符串) | 返回读音相似字符 |
| date_format(时间字符串,转成的格式) | 把时间字符串转成想要的格式 |
trim()
Examples:
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
ltrim()
Examples:
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
rtrim()
Examples:
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
lower()
mysql> SELECT LOWER('QUADRATICALLY');
-> 'quadratically'
upper()
mysql> SELECT UPPER('Hej');
-> 'HEJ'
left()
Examples:
mysql> SELECT LEFT('foobarbar', 5);
-> 'fooba'
right()
Examples:
mysql> SELECT RIGHT('foobarbar', 4);
-> 'rbar'
soundex()
Examples:
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
-> 'Q36324'
date_format()
Examples:
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
流程控制
MySQL if判断，只能放在begin·end中
if 条件 then
子代码
elseif 条件 then
子代码
else
子代码
end if;
MySQL while循环，只能放在begin·end中
while 条件 do
子代码
end while;
索引
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构：
- 主键：primary key
- 唯一键：unique key
- 索引键：index key
上面三种键前两种除了有加速查询的效果之外还有额外的约束条件，而index key没有任何约束功能只会帮你加速查询。
索引的影响:
- 在表中有大量数据的前提下，创建索引速度会很慢
- 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低，因为每次更新数据的同时也会更新索引。
索引的底层数据结构b+树
只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据。
查询次数由树的层级决定，层级越低次数越少。
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项。
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段(一个整型肯定比其他类型小)
聚集索引(primary key)
如果在表中创建了主键约束，SQL Server将自动为其产生唯一性约束。在创建主键约束时，指定了CLUSTERED关键字或干脆没有制定该关键字，SQL Sever将会自动为表生成唯一聚集索引。
辅助索引(unique key,index key)
查询数据的时候不可能都是用主键作为筛选条件，也可能会用其他字段，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引。
特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
覆盖索引与非覆盖索引
覆盖索引指只在辅助索引的叶子节点中就已经找到了所有我们想要的数据，比如：
select name from user where name='jason';
非覆盖索引指查询的时候命中了索引字段，但是要查的是其他字段，所以还需要利用主键才去查找
select age from user where name='jason';
