mysql高级部分

视图 view

概念

为了不让使用数据库的程序员看到敏感/隐私数据（比如别人的账户多少钱），DBA就可以创建视图，让程序员看到DBA想让他看到的东西，专心于事务就好了，是一种“外模式/模式”的映射，实现了逻辑独立性

本质呢，是虚表，不是真的表，隐藏了表的结构

从某种层面说呢，他也降低了SQL的复杂度

创建视图

就是把表的查询结果的表作为视图，其实也就是保存查询语句了

mysql> select * from score;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      98 |      99 |  100 |
|    2 |      90 |      92 |   80 |
+------+---------+---------+------+
2 rows in set (0.00 sec)

mysql> create view vw_score as select id,math from score;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from vw_score;
+------+------+
| id   | math |
+------+------+
|    1 |  100 |
|    2 |   80 |
+------+------+
2 rows in set (0.00 sec)

使用、查看视图

（同表）

更新视图

这个就不一样了，它是用的alter

mysql> alter view vw_score as select id,chinese from score;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from vw_score;
+------+---------+
| id   | chinese |
+------+---------+
|    1 |      98 |
|    2 |      90 |
+------+---------+
2 rows in set (0.00 sec)

删除视图

用的是drop，比如：

drop view vw_score;

算法

• MERGE
• TEMPTABLE

要让视图的子查询生效，要这么创建：

create algorithm=TEMPTABLE view vw_score as select id,math from score;

事务 Transaction

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。（执行单个逻辑功能的一组指令或操作称为事务）

ACID 四大特性

Atomicity原子性

事务不可再分，要么执行，要么不执行

Consistency一致性

事务开始前和事务结束后**，**数据库的完整性约束没有被破坏

这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性

比如A给B转账，不论转账的事务操作是否成功，其两者的存款总额不变（这是业务逻辑的一致性，至于数据库关系约束的完整性就更好理解了）

Isolation隔离性

多个事务并发访问时，事务间是隔离的，比如A和B的交易、B和C的交易之间是隔离开的，运行结果是没问题的

在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间

Durability持久性

事务提交了，结果就定下来了，该事务所对数据库所作的更改便持久的保存在数据库之中，不会再改变，回滚也没用！

事务使用

• 开启事务

start transaction;

• 事务提交

commit;

• 回滚事务（提交前都能滚）

rollback;

• 设置回滚点

savepoint p1;

• 回滚到回滚点

rollback to p1;

什么时候使用

• 引擎要为InnoDB

• 涉及敏感数据，比如金钱转账

索引 index

作用

最直接的就是加快查询，但带来的副作用就是对表的增删改慢了不少，而且还占空间；所以确实经常要查某个字段的数据，再设置索引；还有，要是表小的话也别用索引，没必要

类型

• 主键索引（创建主键时候就默认添加索引了，不用我们手动去加）
• 唯一键索引
• 普通键索引
• 全局（文）索引

创建索引

• 普通索引

create index balance_index on wallet(ballence);

• 唯一索引

create unique index balance_index on wallet(ballence);

删除索引

drop index balance_index on wallet;

存储过程

它不像视图，只能查，它是支持增删改的，就像是SQL里的函数；它的出现就是为了实现“模块化”，一般是DBA来写的

• 首先要把SQL语句集合起来，但是一遇到;就会执行，怎么办呢？用delimiter

# 把语句结束符改为//，;就无效了
delimiter //

• 然后就是写函数了

create procedure proc()
begin
# 操作
end //

• 写完之后准备调用，记得把;调回来

delimiter ;
call proc();

涉及敏感数据别忘了嵌套上“事务”

• 删除存储过程

drop procedure proc;

一些有趣的函数

number 数字相关

• 用rand()随机排序

select * from student order by rand();

• 用rand()搞个抽奖

# 抽奖
select * from student order by rand() limit 3;

在SQL层就能搞定的就在SQL搞定，别交给service再搞

• ceil()向上取整

mysql> select ceil(3.1);
+-----------+
| ceil(3.1) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

• floor()向下取整（用法同上）

• round()四舍五入（用法同上）

• truncate()截取

mysql> select truncate(3.1415926535,2);
+--------------------------+
| truncate(3.1415926535,2) |
+--------------------------+
|                     3.14 |
+--------------------------+
1 row in set (0.00 sec)

string 字符串相关

• ucase(string)转化为大写

• lcase(string)转化为小写

• left(string,num)左截取string的前num个字符

• right(string,num)右截取string的后num个字符

• substring(string,start,num)从start位置开始截取num个字符

• concat(str1,str2)拼接字符串

time 时间相关

• now()获取现在日期时间
• unix_timestamp()获取unix时间戳

加密相关

• sha()sha算法加密