mysql高级

第一章 MySQL常用函数(理解)

1 字符串函数

函数	描述	实例
CHAR_LENGTH(s)	返回字符串 s 的字符数	SELECT CHAR_LENGTH('ita') AS '长度';
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	SELECT CONCAT('I','love','you');
LOWER(s)	将字符串 s 的所有字母变成小写字母	SELECT LOWER('ITA');
UPPER(s)	将字符串转换为大写	SELECT UPPER("ita");
SUBSTR(s, start,length)	从字符串 s 的 start 位置(从1开始)截取长度为 length 的子字符串	SELECT SUBSTR("ita",1,2);
TRIM(s)	去掉字符串 s 开始和结尾处的空格	SELECT TRIM(' ita ')

​

示例:

​

SELECT CHAR_LENGTH('ita') AS '长度';
--执行结果为: 3
SELECT CONCAT('I','love','you');
--执行结果为: Iloveyou
SELECT LOWER('ITA');
--执行结果为: ita
SELECT UPPER("ita");
--执行结果为: ITA
SELECT SUBSTR("ita",1,2);
--执行结果为: it
SELECT TRIM(' it a ');
--执行结果为: it a

2 数字函数

函数	描述	实例
RAND()	返回 0 到 1 的随机数	SELECT RAND();
ROUND(小数 , 小数点后保留小数位数)	四舍五入保留几位小数	SELECT ROUND(3.1415926,2) ;
TRUNCATE(小数 , 小数点后保留小数位数)	不会四舍五入保留几位小数	SELECT TRUNCATE(3.1415926,3);
LEAST(expr1, expr2, expr3, ...)	返回列表中的最小值	SELECT LEAST(13, 14, 521, 74, 1)
GREATEST(expr1, expr2,expr3, ...)	返回列表中的最大值	SELECT GREATEST(13, 14, 521, 74, 1)

​

示例:

​

SELECT RAND();  -- 返回0-1之间的随机数 0.21809973867433122
SELECT ROUND(3.1415926,3) ; -- 执行结果: 3.142
select TRUNCATE(3.1415926,3);-- 执行结果:3.141
SELECT LEAST(13, 14, 521, 74, 1);   -- 执行结果: 1
SELECT GREATEST(13, 14, 521, 74, 1); -- 执行结果: 521

3 日期函数

函数名	描述	实例
NOW() 和 SYSDATE()	返回系统的当前日期和时间	SELECT NOW(); 或 SELECT SYSDATE();
CURDATE()	返回当前日期	SELECT CURDATE();
CURTIME()	返回当前系统时间	SELECT CURTIME();
YEAR(d)	返回d的中的年份	SELECT YEAR(NOW());
MONTH(d)	返回d的中的月份	SELECT MONTH(NOW());
DAY(d)	返回d中的日	SELECT DAY(NOW());

​

示例:

​

SELECT NOW(); 或 SELECT SYSDATE(); -- 返回系统的当前时间: 年-月-日 时:分:秒 
SELECT CURDATE(); -- 返回系统当前日期: 年-月-日
SELECT CURTIME(); -- 返回系统当前时间: 时:分:秒
SELECT YEAR(NOW()); -- 返回当前日期中的年份
SELECT MONTH(NOW()); -- 返回当前日期中的月份
SELECT DAY(NOW()); -- 返回当前日期中的日

4 高级函数

函数名	描述	实例
CURRENT_USER()	返回当前用户	SELECT CURRENT_USER();
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	SELECT IFNULL(null,'Hello Word')
ISNULL(expression)	判断表达式是否为 NULL	SELECT ISNULL(NULL);

select current_user() -- 结果：root@localhost
select ifnull(null,'ita')-- 结果：ita
select ifnull('it程序员','ita')-- 结果：it程序员
select isnull(null); -- 结果：1 表示真 是null
select isnull('大哥'); -- 结果：0 表示假 不是null

第二章 事务(掌握)

1、事务的概念

事务的应用场景说明

关于事务在实际中的应用场景：

假设我在淘宝买了一部手机，然后当我付完款，钱已经从我的账户中扣除。正当此时，淘宝转账系统崩溃了，那么此时淘宝还没有收到钱，而我的账户的钱已经减少了，这样就会导致我作为买家钱已经付过，而卖家还没有收到钱，他们不会发货物给我。这样做显然是不合理。实际生活中是如果淘宝出问题，作为用户的账户中钱是不应该减少的。这样用户就不会损失钱。

还有种情况，就是当我付完款之后，卖家看到我付款成功，然后直接发货了，我如果有权限操作，我可以撤销，这样就会导致我的钱没有减少，但是卖家已经发货，同样这种问题在实际生活中也是不允许出现的。

关于上述两种情况，使用数据库中的事务可以解决。具体解决方案如下图所示：

说明：在数据库中查询不会涉及到使用事务，都是增删改。

什么是事务

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

简而言之，事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败。

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

小结

什么是事务？多条SQL组合再一起完成某个功能.

2、手动提交事务

MYSQL中可以有两种方式进行事务的操作：

1. 手动提交事务：先开启，再提交
2. 自动提交事务(默认的):即执行一条sql语句提交一次事务。

事务有关的SQL语句：

SQL语句	描述
start transaction;	开启手动控制事务
commit;	提交事务
rollback;	回滚事务

手动提交事务使用步骤

第1种情况：开启事务 -> 执行SQL语句 -> 成功 -> 提交事务 ​ 第2种情况：开启事务 -> 执行SQL语句 -> 失败 -> 回滚事务

准备数据:

# 创建一个表：账户表.
create database day04_db;
# 使用数据库
use day04_db;
# 创建账号表
create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);
# 初始化数据
insert into account values (null,'a',1000);
insert into account values (null,'b',1000);

案例演示1：需求：演示提交事务，a给b转账100元。

---

案例演示2：演示回滚事务，a给b转账100元。（失败）

注意：

事务是如何处理正常情况的呢？

a=1000 b=1000

开启事务(start transaction;)

update account set money = money -100 where name='a';

update account set money = money +100 where name='b';

提交事务(commit;) (事务提交之后，sql语句对数据库产生的操作才会被永久的保存)

事务是如何处理异常情况的呢？

a=1000 b=1000

开启事务(start transaction;)

update t_account set money = money -100 where name='a'; a=900

出现异常

update t_account set money = money +100 where name='b';

事务的回滚(rollback;)(撤销已经成功执行的sql语句，回到开启事务之前的状态)

a=1000 b=1000;

注意：只要提交事务，那么数据就会长久保存了，就不能回滚事务了。即提交或者回滚事务都是代表结束当前事务的操作。

小结

1. 如何开启事务: start transaction;
2. 如何提交事务: commit;
3. 如何回滚事务: rollback;

3、自动提交事务

MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。自动提交，通过修改mysql全局变量“autocommit”进行控制。

1.通过以下命令可以查看当前autocommit模式：

show variables like '%commit%';

2.设置自动提交的参数为OFF:

set autocommit = 0;  -- 0:OFF  1:ON

小结

1）MySql默认自动提交。即执行一条sql语句提交一次事务。

2）设置autocommit为off状态，只是临时性的，下次重新启动mysql，autocommit依然变为on状态。

3）如果设置autocommit为off状态，那么当我们执行一条sql语句，就不会自动提交事务，重新启动可视化工具，数据并没有改变。

4）如果设置autocommit为on状态，如果我们先执行 start transaction; 然后在执行修改数据库的语句：

update account set money = money-100 where name='a';

update account set money = money+100 where name='b';

那么此时就表示上述修改数据库的sql语句都在同一个事务中，此时必须手动提交事务，即commit;

换句话说，如果我们手动开启事务 start transaction; 那么此时mysql就不会自动提交事务，必须手动提交事务。

5）如果设置autocommit为on状态，如果我们不执行 start transaction; 直接执行修改数据库的语句：

update account set money = money-100 where name='a';

update account set money = money+100 where name='b';

那么此时mysql就会自动提交事务。即上述每条sql语句就是一个事务。

课堂代码演示：

show variables like '%commit%';
​
set autocommit = 0;
start transaction;
​
update account set money = money-100 where name='a';
​
update account set money = money+100 where name='b';
​
commit;
​
rollback;

4、事务的回滚点和执行原理

什么是回滚点

上面的操作，如果回滚，直接回滚到事务开始前。有时我们并不需要回滚到最开始的状态，可能只需要回滚到中间的某个位置，就可以设置回滚点。

语句

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

操作1

1. 将数据还原到1000

2. 开启事务

3. 让a用户减2次钱，每次100块

4. 回滚事务

5. commit提交事务---数据没有更改

   代码演示如下：

-- 没有设置回滚点，回到开启事务之前的状态
-- 开启事务
start transaction;
-- a用户扣除100
update account set money = money - 100 where name='a';
-- a用户再次扣除100
update account set money = money - 100 where name='a';
-- 回滚事务
rollback;
commit;

操作2

1. 将数据还原到1000
2. 开启事务
3. 让a用户先减100块
4. 设置回滚点：savepoint itcast;
5. 然后让a用户再次减100块
6. 回到回滚点：rollback to itcast
7. 最后commit提交事务

代码演示如下：

-- 设置回滚点
-- 开启事务
start transaction;
-- a用户扣除100
update account set money = money - 100 where name='a';
-- 设置回滚点
savepoint itcast;
-- a用户再次扣除100
update account set money = money - 100 where name='a';
-- 回滚事务
-- rollback;
-- 回到回滚点
rollback to itcast; 
commit;

原理图

原理说明

1. 一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。
2. 如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件。
3. 如果开启事务，将所有的写操作写到日志文件中。
4. 如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中。
5. 如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作。

事务操作小结

说出事务原理？ 开启事务后,SQL语句会放在临时的日志文件中,如果提交事务,将日志文件中SQL的结果放在数据库中

如果回滚事务清空日志文件.

事务的操作	MySQL操作事务的语句
手动开启事务	start transaction
手动提交事务	commit
手动回滚事务	rollback
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字
查询事务的自动提交情况	show variables like '%commit%';
设置事务的手动提交方式	set autocommit = 0 -- 关闭自动提交

5、事务的四大特性(ACID)(面试)

数据库的事务必须具备ACID特性，ACID是指 Atomicity（原子性）、Consistensy（一致性）、Isolation（隔离性）和Durability（持久性）的英文缩写。

1、隔离性（Isolation）

多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。

一个事务的成功或者失败对于其他的事务是没有影响。2个事务应该相互独立。

举例：

a 给b转账 -----》叫做事务A

c 给d 转账 -----》叫做事务B

事务A和事务B之间不会相互影响。

2、持久性（Durability）

指一个事务一旦被提交，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。

举例：

a=1000、b=1000转账

开启事务

a-100

b+100

提交

结果： a 900 b 1100

即使事务提交以后再发生异常，a和b的数据依然不会变。a就是900 b就是1100。

3、原子性（Atomicity）

原子性是指事务包装的一组sql(一组业务逻辑)是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

4、一致性（Consistency）

一个事务在执行之前和执行之后 数据库都必须处于一致性状态。

如果事务成功的完成，那么数据库的所有变化将生效。

如果事务执行出现错误，那么数据库的所有变化将会被回滚（撤销），返回到原始状态。

事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性。

举例1： a=1000、b=1000 转账 100

a - 100

b + 100

结果： a + b = 2000

如果a转账失败了，那么b也得失败。不能因为a失败了，a依然是1000.但是b却成功了，b却变成了1100.那么结果是2100了，这样是不符合事务的一致性的。

小结

事务四个特性？ 原子性 一致性 隔离性 持久性

事务特性	含义
一致性（Consistency）	事务前后数据的完整性必须保持一致
原子性（Atomicity）	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
隔离性（Isolation）	是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。
持久性（Durability）	指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

6、事务的并发访问引发的三个问题(面试)

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据。最严重，杜绝发生。
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是不一致的，这是事务update时引发的问题
幻读	一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同。这是insert或delete时引发的问题

1.脏读：指一个事务读取了另外一个事务未提交的数据。(非常危险)

脏读具体解释如下图所示：注意脏读的前提是没有事务的隔离性。

说明：事务a首先执行转账操作，然后事务a还没有提交数据的情况下，事务b读取了数据库的数据。紧接着事务a执行回滚操作，导致事务b读取的结果和数据库的实际数据是不一样的。

一个事务读取了另一个事务未提交的数据叫做脏读。

举例：

a 转账 给b 100，未提交

b 查询账户多了100

a 回滚

b 查询账户那100不见了。

一个事务读取了另一个事务没有提交的数据，非常严重，必须避免脏读。

2.不可重复读：在一个事务内多次读取表中的数据，多次读取的结果不同。

说明：事务b首先读取数据库的数据，然后事务a对数据修改并提交。之后事务b对数据库再次进行读取。这时发现在事务b中2次读取的结果不一致。

一个事务内读取了另一个事务提交的数据。这个叫做不可重复读。

不可重复读和脏读的区别：

脏读：强调的是读取了未提交的数据。

不可重复读：一个事务内2次读取，其中一次读取了另一个事务提交了的数据。

例如: 银行想查询A账户的余额，第一次查询的结果是200元，A向账户中又存了100元。此时，银行再次查询的结果变成了300元。两次查询的结果不一致，银行就会很困惑，以哪次为准。

和脏读不同的是：脏读读取的是前一事务未提交的数据，不可重复度 读取的是前一事务已提交的事务。

很多人认为这有啥好困惑的，肯定是以后面的结果为准了。我们需要考虑这样一种情况，查询A账户的余额，一个打印到控制台，一个输出到硬盘上，同一个事务中只是顺序不同，两次查询结果不一致，到底以哪个为准，你会不会困惑呢？

当前事务查询A账户的余额为100元，另外一个事务更新余额为300元并提交，导致当前事务使用同一查询结果却变成了300元。

3.幻读（虚读）:一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同

说明：事务b首先读取数据的数量，然后事务a添加了一条数据，并且提交了。接着事务b再次读取了数据的数量。2次读取不一致。

同一个事务内，2次读取的数据的数量不一致，叫做幻读或者虚读。

虚读(幻读)和不可重复读的区别：

不可重复读：强调的是数据内容的不一致。另一个事务是update操作。

虚读(幻读)：强调的数据的数量(记录数)的不一致。另一个事务是insert或者delete操作。

注意：

指在一个事务中 读取 另一个事务 插入或删除 数据记录，导致当前事务读取数据的记录数前后不一致。

一个事务读取另一个事务已经提交的数据，强调的是记录数的变化，常用sql类型为 insert和 delete。

小结

1. 能够理解并发访问的三个问题 赃读:一个事务读取另一个事务还没有提交的数据 不可重复读:一个事务读取多次数据内容不一样 幻读:一个事务读取多次数量不一样

7、事务的隔离级别

1、通过以上问题演示，我们发现如果不考虑事务的隔离性，会遇到脏读、不可重复读和虚读等问题。所以在数据库中我们要对上述三种问题进行解决。MySQL数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

2、安全和性能对比

安全性：serializable > repeatable read > read committed > read uncommitted

性能 ： serializable < repeatable read < read committed < read uncommitted

3、注意：其实三个问题，开发中最严重的问题就是脏读，这个问题一定要避免，而关于不可重复读和虚读其实只是感官上的错误，并不是逻辑上的错误。就是数据的时效性，所以这种问题并不属于很严重的错误。如果对于数据的时效性要求不是很高的情况下，我们是可以接受不可重复读和虚读的情况发生的。

小结

能够说出mysql的四种隔离级别 读未提交:read uncommitted 读已提交:read committed 可重复读:repeatable read 串行化:serializable

8、脏读的演示

查询和设置隔离级别

1. 查询全局事务隔离级别

   show variables like '%isolation%';
   -- 或
   select @@tx_isolation;
   

0. 设置事务隔离级别，需要退出MSQL再进入MYSQL才能看到隔离级别的变化

```
set global transaction isolation level 隔离级别;
-- 如:
set global transaction isolation level read uncommitted;
```

脏读的演示

脏读：一个事务读取到了另一个事务中尚未提交的数据。

1. 打开一个窗口,设置为A窗口，登录MySQL，设置全局的隔离级别为最低

   -- 设置窗口名字A
   title A
   -- 登录mysql数据库
   mysql -u root -p
   1234
   -- 设置事务隔离级别
   set global transaction isolation level read uncommitted;
   

注意：设置事务隔离级别，需要重新打开一个窗口才能看到隔离级别的变化.

2.重新打开一个新的窗口,设置为B窗口，登录mysql

-- 设置窗口名字B
title B;
-- 登录mysql数据库
mysql -u root -p
1234
-- 查询隔离级别
select @@tx_isolation;

3.AB窗口都开启事务

use day05_db;
start transaction;

4.A窗口更新2个人的账户数据，未提交

update account set money=money-500 where id=1;
update account set money=money+500 where id=2;

5.B窗口查询账户

select * from account;

6.A窗口回滚

rollback;

7.B窗口查询账户，钱没了

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入500块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升

1. 在A窗口设置全局的隔离级别为read committed

   set global transaction isolation level read committed;
   

0. B窗口退出MySQL，B窗口再进入MySQL

0. AB窗口同时开启事务

1. A更新2个人的账户，未提交

   update account set money=money-500 where id=1;
   update account set money=money+500 where id=2;
   

0. B窗口查询账户

1. A窗口commit提交事务

0. B窗口查看账户

结论：read committed的方式可以避免脏读的发生

小结

1. 查询全局事务隔离级别？ show variables like '%isolation%'; select @@tx_isolation;
2. 设置全局事务隔离级别？ set global transaction isolation level 隔离级别字符串;
3. 如何解决赃读? 将隔离级别设置为:read committed