MySQL-3（常用函数、事务、索引、触发器）

MySQL常用函数

字符串

char_length(字符串)
concat(字符串1,字符串2,...)  -- 将多个字符串合并
lower（字符串）-- 将字符串所有字母变成小写
upper(字符串) -- 将字符串所有字母换成大写
substr(s,start,length) -- 截取
trim（字符串）-- 去掉字符串前后的空格

数字函数

rand() -- 返回0到1的随机数
rand(小数,保留位数)-- 四舍五入保留小数
least（expr1,expr2,...）-- 返回列表最小值
greatest(expr1,expr2,...) -- 返回列表最大值

日期函数

now() 和 sydate() -- 返回当前系统日期和时间
curdate() -- 返回当前时间
curtime() -- 返回当前系统时间
year(d) -- 返回d年中的年份
month(d) -- 返回d年中的月份
day(d) -- 返回d年中的日

高级函数

current_user() -- 返回当前用户
ifnull(v1,v2)--如果v1的值为null则返回v2的值
isnull(表达式) 

事务

事务：

如果在执行一个业务的操作时，需要执行多条sql语句，必须保证所有sql语句都执行成功，否则所有语句都要进行回滚

事物的特性：（ACID）

-- 原子性
	事务不可分割
-- 一致性
	事务执行前后数据状态要一致
-- 隔离性
	事务可以并发，但事务与事务之间不能相互影响
-- 持久性
	进行事务操作后，对数据库中的数据影响时持久的

事务的开启与提交：

• 开启事务：start transaction

• 提交事务：commit

• 回滚事务：rollback

  -- 当执行update delete insert 时必须要开启事务，select 可以有，也可以不开启
  事务的开启方式有两种：
  	-- 手动开启  start transaction；
  	-- 自动开启  默认
  
  

查看当前的事务开启方式：

select @@autocommit；  1代表自动  0代表手动

事务回滚与执行原理

执行原理：

• 直接sql语句写操作，执行的结果会同步到数据库表中

• 开启事务–sql语句操作–查询操作结果–提交事务：

• --开启事务后的sql操作结果不会直接将结果传入到数据库中，而是通过创建临时日志文件，结果存储到这个临时文件中，输入commit语句后数据才会永久性的存入数据库表中，断开连接，文件自动清空
  

  回滚点：

  设置回滚点： savepoint 名字
  回滚到指定回滚点：rollback to 名字
  

  事务的并发产生问题

  并发产生的问题有
  脏读		 -- 一个事务读取了另一个事务未提交的数据
  不可重复读  -- 一个事务读取到了另一个事务未提交的数据，【同一个数据的改变  update】
  幻读 		 -- 一个事务读取到了另一个事务未提交的数据，【表中的行数不同	insert】
  

  以上的问题可以用数据库隔离级别来解决

  read uncommitted --不能解决任何问题，会存在脏读，不可重复读，虚读问题，但效率高
  read committed	 --能解决脏读问题，效率比较高，Oracle默认隔离级别
  repeatable read	 -- 能解决脏读和不可重复读的问题，效率一般，MySQL默认隔离级别
  serializable	 -- 能解决以上三种问题【锁表】，效率低
  

  查看隔离级别:
  	select @@tx_isolation;
  修改隔离级别：
  	set session transaction isolation level 事务隔离级别；
  

索引

​ MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

​ 优势：性能提高

​ 劣势: 维护成本高

​ 分类：单值、唯一、复合、主键、full_text

索引：
	index是帮助MySQL高效获取数据的有序的数据结构，这些数据结构以某种引用数据，这样在数据结构上实现高级的查找算法，这个数据结构就是索引。【】

作用：
	索引是数据库中用来提高性能的最常用的工具

优：
	1、降低数据库的IO成本；2、通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣：
	索引也是一张表，会占用内存；降低表的更新速度，【因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。】

索引的创建与删除

创建：create index index_name ON 表名（字段名...）
查看：show index from 表名
删除：drop index索引名 on 表名

1） alter table tb_name add primary key(column_list);
	该语句添加一个主键，这意味着索引值必须时唯一的，且不能未null
2） alter table tb_name add unique index_name(column_list);
	这条语句创建的索引必须是唯一的
3）alter table tb_name add index index_name(column_list);
	添加普通索引，索引值可以出现好多次
4）alter table tb_name add fulltext index_name(column_list);
	该语句指定了索引为fulltext,用于全文索引

索引的结构

索引的创建原则

对查询频次较高，且数据量比较大的表建立索引。

索引字段的选择，最佳候选列应当从 where子句的条件中提取

使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O效率，也可以提升总体的访问效率

视图

​ 视图是一张虚拟表，它的作用可以方便我们查询常用不经常修改的数据。

• 创建视图 create view 视图名 as select语句

• 查看视图 show tables;

• 删除视图 drop view 视图名;

  对于视图，我们就可以把它当成一张表一样来操作

视图相对于普通的表的优势主要包括以下几项。
	简单：-- 使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
	安全：-- 使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
	数据独立：-- 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。

创建触发器

create trigger 	
	触发器名 
before/after  insert/update/delete 
on 表
for each row 
	sql语句


-- 创建 insert 型触发器，完成插入数据时的日志记录 【new】
-- 创建 update 型触发器，完成更新数据时的日志记录【new / old 都可以】
-- 创建delete 行的触发器 , 完成删除数据时的日志记录【old】