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中可以有两种方式进行事务的操作：
- 手动提交事务：先开启，再提交
- 自动提交事务(默认的):即执行一条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;
注意：只要提交事务，那么数据就会长久保存了，就不能回滚事务了。即提交或者回滚事务都是代表结束当前事务的操作。
小结
- 如何开启事务: start transaction;
- 如何提交事务: commit;
- 如何回滚事务: 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
-
将数据还原到1000
-
开启事务
-
让a用户减2次钱，每次100块
-
回滚事务
-
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
- 将数据还原到1000
- 开启事务
- 让a用户先减100块
- 设置回滚点：savepoint itcast;
- 然后让a用户再次减100块
- 回到回滚点：rollback to itcast
- 最后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;
原理图
原理说明
- 一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。
- 如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件。
- 如果开启事务，将所有的写操作写到日志文件中。
- 如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中。
- 如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作。
事务操作小结
说出事务原理？ 开启事务后,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。
小结
- 能够理解并发访问的三个问题 赃读:一个事务读取另一个事务还没有提交的数据 不可重复读:一个事务读取多次数据内容不一样 幻读:一个事务读取多次数量不一样
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、脏读的演示
查询和设置隔离级别
-
查询全局事务隔离级别
show variables like '%isolation%'; -- 或 select @@tx_isolation;
0. 设置事务隔离级别，需要退出MSQL再进入MYSQL才能看到隔离级别的变化
```
set global transaction isolation level 隔离级别;
-- 如:
set global transaction isolation level read uncommitted;
```
脏读的演示
脏读：一个事务读取到了另一个事务中尚未提交的数据。
-
打开一个窗口,设置为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块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决脏读的问题：将全局的隔离级别进行提升
-
在A窗口设置全局的隔离级别为
read committedset global transaction isolation level read committed;
0. B窗口退出MySQL，B窗口再进入MySQL
0. AB窗口同时开启事务
-
A更新2个人的账户，未提交
update account set money=money-500 where id=1; update account set money=money+500 where id=2;
0. B窗口查询账户
- A窗口commit提交事务
0. B窗口查看账户
结论：read committed的方式可以避免脏读的发生
小结
- 查询全局事务隔离级别？ show variables like ‘%isolation%’; select @@tx_isolation;
- 设置全局事务隔离级别？ set global transaction isolation level 隔离级别字符串;
- 如何解决赃读? 将隔离级别设置为:read committed
