MySQL（三）

发表于 更新于 阅读次数： 本文字数： 2k 阅读时长 ≈ 7 分钟

因为内容太多了，所以将其拆分为以下内容

参考

https://www.bilibili.com/video/BV1NJ411J79W

https://www.runoob.com/mysql/mysql-tutorial.html

MySQL 函数

字符串函数

函数 描述 实例
CHAR_LENGTH(s) 返回字符串 s 的字符数 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn) 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FORMAT(x,n) 函数可以将数字 x 进行格式化 "#,##.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 格式化数字 "#,##.##" 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com
LEFT(s,n) 返回字符串 s 的前 n 个字符 返回字符串 runoob 中的前两个字符：SELECT LEFT('runoob',2) -- ru
LOWER(s) 将字符串 s 的所有字母变成小写字母 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob
LTRIM(s) 去掉字符串 s 开始处的空格 去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc
REVERSE(s) 将字符串s的顺序反过来 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba
RIGHT(s,n) 返回字符串 s 的后 n 个字符 返回字符串 runoob 的后两个字符：SELECT RIGHT('runoob',2) -- ob
RTRIM(s) 去掉字符串 s 结尾处的空格 去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
STRCMP(s1,s2) 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
UPPER(s) 将字符串转换为大写 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB

数字函数

函数名 描述 实例
SUM(expression) 返回指定字段的总和 计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
ABS(x) 返回 x 的绝对值 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1
AVG(expression) 返回一个表达式的平均值，expression 是一个字段 返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x) 返回大于或等于 x 的最小整数 SELECT CEIL(1.5) -- 返回2
COUNT(expression) 返回查询的记录总数，expression 参数是一个字段或者 * 号 返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
FLOOR(x) 返回小于或等于 x 的最大整数 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1
MAX(expression) 返回字段 expression 中的最大值 返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression) 返回字段 expression 中的最小值 返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
RAND() 返回 0 到 1 的随机数 SELECT RAND() --0.93099315644334
ROUND(x) 返回离 x 最近的整数 SELECT ROUND(1.23456) --1

日期函数

函数名 描述 实例
CURRENT_DATE() 返回当前日期 SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME 返回当前时间 SELECT CURRENT_TIME(); -> 19:59:02
CURRENT_TIMESTAMP() 返回当前日期和时间 SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
NOW() 返回当前日期和时间 SELECT NOW() -> 2018-09-19 20:57:43

更多

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

ACID 原则

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离所导致的一些问题

脏读：

当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。

不可重复读：

在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。

虚读（幻读）：

在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；
  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • ROLLBACK TO identifier 把事务回滚到标记点；
  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 模拟转账

create table `account`(
	`id` int(3) not null AUTO_INCREMENT,
	`name` varchar(30) not null,
	`money` decimal(9,2) not null,
	primary key(`id`)
)engine=innodb default charset=utf8

insert into account (`name`,`money`)
VALUES('a',2000.00),('b',10000.00)

set AUTOCOMMIT = 0;	-- 关闭自动提交

begin

update account set money=money-500 where name='a'
update account set money=money+500 where name='b'

commit;
rollback;

set AUTOCOMMIT = 1;	-- 开启自动提交

因为内容太多了，所以将其拆分为以下内容