条件判断，系统信息，加密及其他函数的使用解析
- 1 条件判断函数
- 1.1 IF(expr,v1,v2)函数
- 1.2 IFNULL(v1,v2)函数
- 1.3 CASE函数
- 2 系统信息函数
- 2.1 获取MySQL版本号、连接数和数据库名的函数
- 2.2 获取用户名的函数
- 2.3 获取字符串的字符集和排序方式的函数
- 2.4 获取最后一个自动生成的ID 值的函数
- 3 加密函数
- 3.1 加密函数MD5(str)
- 3.2 加密函数 SHA(str)
- 3.3 加密函数 SHA2(str,hash length)
- 4 其它函数
- 4.1 格式化函数FORMAT(x,n)
- 4.2 不同进制的数字进行转换的函数
- 4.3 IP 地址与数字相互转换的函数
- 4.4 加锁函数和解锁函数
- 4.5 重复执行指定操作的函数
- 4.6 改变字符集的函数
- 4.7 改变数据类型的函数
- 5 窗口函数
1 条件判断函数
条件判断函数也称为控制流程函数，根据满足的不同条件，执行相应的流程。MySOL 中进行条件判断的函数有 IF、IFNULL 和CASE。
1.1 IF(expr,v1,v2)函数
IF(expr,vl,v2): 如果表达式expr 是 TRUE(expr0andexprNULL)，则返回值为vl;否则返回值为v2。IFO的返回值为数字值或字符串值，具体情况视其所在语境而定。
expr 表达式的返回值必须是一个布尔值或者可以被隐式转换为布尔值的类型，比如 0 或者非零整数等。
mysql> SELECT IF(1>2,'yes ','no'),
-> IF(1<2,'yes ','no'),
-> IF(STRCMP('test','1test'),'no','yes');
+---------------------+---------------------+---------------------------------------+
| IF(1>2,'yes ','no') | IF(1<2,'yes ','no') | IF(STRCMP('test','1test'),'no','yes') |
+---------------------+---------------------+---------------------------------------+
| no | yes | no |
+---------------------+---------------------+---------------------------------------+
1 row in set (0.00 sec)1.2 IFNULL(v1,v2)函数
IFNULL(vl.v2):假如1不为NULL,则IFNULLO的返回值为vl;否则其返回值为2。IFNULLO的返回值是数字或者字符串，具体情况取决于其所在的语境。
mysql> SELECT IFNULL(1,2), IFNULL(NULL,100), IFNULL(5*0, 'wrong'),IFNULL(5/0, 'wrong');
+-------------+------------------+----------------------+----------------------+
| IFNULL(1,2) | IFNULL(NULL,100) | IFNULL(5*0, 'wrong') | IFNULL(5/0, 'wrong') |
+-------------+------------------+----------------------+----------------------+
| 1 | 100 | 0 | wrong |
+-------------+------------------+----------------------+----------------------+
1 row in set, 1 warning (0.00 sec)1.3 CASE函数
CASE expr WHEN VI THEN rl[WHEN V2 THEN 2]…[ELSE +l] END: 如果expr 值等于某个vn，则返回对应位置 THEN 后面的结果;如果与所有值都不相等，则返回 ELSE 后面的 m+l。
mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one |
+------------------------------------------------------------+
1 row in set (0.00 sec)2 系统信息函数
2.1 获取MySQL版本号、连接数和数据库名的函数
1. VERSIONO返回指示MySQL 服务器版本的字符串。这个字符串使用utf8 字符集。
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)2. CONNECTION IDO返回 MySQL 服务器当前连接的次数，每个连接都有各自唯一的ID。
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 2871 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 2903 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | SHOW FULL PROCESSLIST |
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
2 rows in set (0.00 sec)processlist 命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态、帮助识别出有问题的查询语句等。
如果是 root 账号，能看到所有用户的当前连接。如果是其他普通账号，则只能看到自己占用的连接。show processlist 只列出前 100 条，如果想全部列出可使用 show full processlist 命令。
各个列的含义和用途:
(1) Id 列，用户登录 MySQL 时，系统分配的是“connection id”。
(2) User 列，显示当前用户。如果不是 root，这个命令就只显示用户权限范围内的 SQL 语句。
(3) Host 列，显示这个语句是从哪个 IP 的哪个端口上发出的，可以用来追踪出现问题语句的用户。
(4) db 列，显示这个进程目前连接的是哪个数据库。
(5) Command 列，显示当前连接执行的命令，一般取值为休眠 (Sleep)、查询(Query)、连接(Connect)。
(6) Time 列，显示这个状态持续的时间，单位是秒。
(7) State 列，显示使用当前连接的 SOL 语句的状态，很重要的列。后续会有所有状态的描述,State 只是语句执行中的某一个状态。一个SQL语句,以查询为例，可能需要经过 Copying to tmptable、Sorting result、Sending data 等状态才可以完成。
(8) Info列，显示这个SOL 语句，是判断问题语句的一个重要依据。
3. DATABASEO和 SCHEMA函数返回使用 utf8 字符集的默认(当前)数据库名
两个函数的作用相同。
mysql> SELECT DATABASE(),SCHEMA();
+------------+----------+
| DATABASE() | SCHEMA() |
+------------+----------+
| NULL | NULL |
+------------+----------+
1 row in set (0.00 sec)
mysql> use TEST
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT DATABASE(),SCHEMA();
+------------+----------+
| DATABASE() | SCHEMA() |
+------------+----------+
| TEST | TEST |
+------------+----------+
1 row in set (0.00 sec)2.2 获取用户名的函数
USER0、CURRENT USER、CURRENT USERO、SYSTEM USERO和 SESSION USERO这几个函数返回当前被 MySQL 服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的 MySOL 账户。一般情况下，这几个函数的返回值是相同的。
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER();
+----------------+----------------+----------------+
| USER() | CURRENT_USER() | SYSTEM_USER() |
+----------------+----------------+----------------+
| root@localhost | root@% | root@localhost |
+----------------+----------------+----------------+
1 row in set (0.00 sec)2.3 获取字符串的字符集和排序方式的函数
CHARSET(str)返回字符串 str 自变量的字符集。
mysql> SELECT CHARSET('abc'),
-> CHARSET(CONVERT('abc' USING latin1)),
-> CHARSET(VERSION());
+----------------+--------------------------------------+--------------------+
| CHARSET('abc') | CHARSET(CONVERT('abc' USING latin1)) | CHARSET(VERSION()) |
+----------------+--------------------------------------+--------------------+
| utf8mb4 | latin1 | utf8mb3 |
+----------------+--------------------------------------+--------------------+
1 row in set (0.00 sec)COLLATION(str)返回字符串 str 的字符排列方式。
mysql> SELECT COLLATION('abc'),COLLATION(CONVERT('abc' USING utf8));
+--------------------+--------------------------------------+
| COLLATION('abc') | COLLATION(CONVERT('abc' USING utf8)) |
+--------------------+--------------------------------------+
| utf8mb4_0900_ai_ci | utf8mb3_general_ci |
+--------------------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)2.4 获取最后一个自动生成的ID 值的函数
LAST INSERT IDO函数返回最后生成的AUTO INCREMENT 值。
使用SELECT LAST_INSERT_ID查看最后一个自动生成的列值，执行过程如下：
1．一次插入一条记录
首先创建表tab_09，其Id字段带有AUTO_INCREMENT约束，输入语句如下：
mysql> CREATE TABLE tab_09 (Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> Name VARCHAR(30));
Query OK, 0 rows affected (0.02 sec)分别单独向表worker中插入2条记录：
mysql> INSERT INTO tab_09 VALUES(NULL, 'jimy');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tab_09 VALUES(NULL, 'Tom');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tab_09;
+----+------+
| Id | Name |
+----+------+
| 1 | jimy |
| 2 | Tom |
+----+------+
2 rows in set (0.00 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)3 加密函数
加密函数主要用来对数据进行加密和界面处理，以保证某些重要数据不被别人获取。这些函数在保证数据库安全时非常有用。
3.1 加密函数MD5(str)
MD5(str)为字符串算出一个MD5 128 比特校验和该值以32 位十六进制数字的二进制字符串形式返回，若参数为 NULL，则会返回NULL。
mysql> SELECT MD5 ('Mysql');
+----------------------------------+
| MD5 ('Mysql') |
+----------------------------------+
| 9edb3c572b56b91542af659480518681 |
+----------------------------------+
1 row in set (0.00 sec)3.2 加密函数 SHA(str)
SHA(str)从原明文密码 str 计算并返回加密后的密码字符串，当参数为 NULL 时，返回NULL。
SHA 加密算法比MD5 更加安全。
mysql> SELECT SHA ('Mysql');
+------------------------------------------+
| SHA ('Mysql') |
+------------------------------------------+
| 6c19d660d330cf1edf2de58132ea0259b6b7ecd9 |
+------------------------------------------+
1 row in set (0.00 sec)3.3 加密函数 SHA2(str,hash length)
SHA2(str,hash length)使用 hash length 作为长度，加密 str。hash length 支持的值为 224、256、384、512和0。其中，0等同于256。
mysql> SELECT SHA2 ('Mysql',0);
+------------------------------------------------------------------+
| SHA2 ('Mysql',0) |
+------------------------------------------------------------------+
| 5dcf78114c488f6d6b9ac5e317c0d572d6cc5928218ed6385aa2454855943579 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)4 其它函数
4.1 格式化函数FORMAT(x,n)
FORMAT(x.n)将数字 x格式化，并以四舍五入的方式保留小数点后n 位，结果以字符串的形式返回。若 n为 0，则返回结果函数不含小数部分。
mysql> SELECT FORMAT(12345.12345, 4), FORMAT(12345.12345,14), FORMAT(12345.12345,0);
+------------------------+------------------------+-----------------------+
| FORMAT(12345.12345, 4) | FORMAT(12345.12345,14) | FORMAT(12345.12345,0) |
+------------------------+------------------------+-----------------------+
| 12,345.1235 | 12,345.12345000000000 | 12,345 |
+------------------------+------------------------+-----------------------+
1 row in set (0.00 sec)4.2 不同进制的数字进行转换的函数
CONV(N,from base,to base)函数进行不同进制数间的转换。返回值为数值N的字符串表示由from base 进制转化为 to base 进制。如有任意一个参数为 NULL，则返回值为 NULL。自变量N被理解为一个整数，但是可以被指定为一个整数或符串。最小基数为 2，最大基数为 36。
mysql> SELECT CONV('a',16,2),
-> CONV(10,10,2),
-> CONV(10,10,8),
-> CONV(10,10,16);
+----------------+---------------+---------------+----------------+
| CONV('a',16,2) | CONV(10,10,2) | CONV(10,10,8) | CONV(10,10,16) |
+----------------+---------------+---------------+----------------+
| 1010 | 1010 | 12 | A |
+----------------+---------------+---------------+----------------+
1 row in set (0.00 sec)进制说明:
二进制，采用0和1两个数字来表示的数。它以2为基数，逢二进一。
八进制，采用 0、1、2、3、4、5、6、7八个数字，逢入进一，以数字0开头。
十进制，采用 0~9，共 10 个数字表示，逢十进一。
十六进制，由0-9、A~F 组成，以数字 0x 开头。与十进制的对应关系是: 09对应09,AF对应1015。
4.3 IP 地址与数字相互转换的函数
INET ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4或 8bit 地址。
mysql> SELECT INET_ATON('192.168.6.63');
+---------------------------+
| INET_ATON('192.168.6.63') |
+---------------------------+
| 3232237119 |
+---------------------------+
1 row in set (0.00 sec)INET_NTOA(expr)给定一个数字网络地址 (4 或 8bit)，返回作为字符串的该地址的点地址表。
mysql> SELECT INET_NTOA('3232237119');
+-------------------------+
| INET_NTOA('3232237119') |
+-------------------------+
| 192.168.6.63 |
+-------------------------+
1 row in set (0.00 sec)4.4 加锁函数和解锁函数
GET LOCK(str,timeout)设法使用字符串 str 给定的名字得到一个锁，超时为 timeout 秒。若成功得到锁,则返回 1:若操作超时,则返回0;若发生错误,则返回 NULL。
RELEASE LOCK(str)解开被 GET LOCK获取的、用字符串 str 所命名的锁。若锁被解开,则返回 1:若该线程尚未创建锁，则返回 0(此时锁没有被解开);若命名的锁不存在，则返回 NULL若该锁从未被 GET LOCK的调用获取，或锁已经被提前解开，则该锁不存在。
IS FREE LOCK(str)检查名为 str 的锁是否可以使用(换言之，没有被封锁)。若锁可以使用,则返回 1(没有人在用这个锁):若这个锁正在被使用，则返回 0:出现错误，则返回 NULL(诸如不正确的参数)。
IS USED LOCK(str)检查名为 str 的锁是否正在被使用(言之，被封锁)。若被封锁，则返回使用该锁的客户端的连接标识符 (connection ID):否则，返回NULL。
mysql> SELECT GET_LOCK('lock1',10) AS GetLock,
-> IS_USED_LOCK('lock1') AS ISUsedLock,
-> IS_FREE_LOCK('lock1') AS ISFreeLock,
-> RELEASE_LOCK('lock1') AS ReleaseLock;
+---------+------------+------------+-------------+
| GetLock | ISUsedLock | ISFreeLock | ReleaseLock |
+---------+------------+------------+-------------+
| 1 | 8 | 0 | 1 |
+---------+------------+------------+-------------+
1 row in set (0.00 sec)4.5 重复执行指定操作的函数
BENCHMARK(count,expr)函数重复count 次执行表达式expr。它可以用于计算 MySQL处理表达式的速度。结果值通常为 0(0 只是表示处理过程很快，并不是没有花费时间)。另一个作用是它可以在 MySOL 客户端内部报告语句执行的时间。
mysql> SELECT MD5 ( 'mysql' );
+----------------------------------+
| MD5 ( 'mysql' ) |
+----------------------------------+
| 81c3b080dad537de7e10e0987a4bf52e |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT BENCHMARK( 500000, MD5 ('mysql') );
+------------------------------------+
| BENCHMARK( 500000, MD5 ('mysql') ) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.07 sec)4.6 改变字符集的函数
CONVERT(…USING…: 带有 USING 的CONVERTO函数被用来在不同的字符集之间转化数据。
mysql> SELECT CHARSET('string'), CHARSET(CONVERT('string' USING GBK));
+-------------------+--------------------------------------+
| CHARSET('string') | CHARSET(CONVERT('string' USING GBK)) |
+-------------------+--------------------------------------+
| utf8mb4 | gbk |
+-------------------+--------------------------------------+
1 row in set (0.00 sec)4.7 改变数据类型的函数
CAST(x，AS type)和CONVERT(x, type)函数将一个类型的值转换为另一个类型的值，可转换的type 有 BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。
mysql> SELECT CAST(100 AS CHAR(2)), CONVERT('2010-10-01 12:12:12',DATE);
+----------------------+-------------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2010-10-01 12:12:12',DATE) |
+----------------------+-------------------------------------+
| 10 | 2010-10-01 |
+----------------------+-------------------------------------+
1 row in set, 1 warning (0.00 sec)5 窗口函数
在 MySQL 8.0 版本之后，MySQL 支持窗口函数（Window Functions）的使用，可以通过 OVER 子句实现对数据分组的各种统计操作，如排名、行号、累加等。
用它可以实现很多新的查询方式。窗口函数类似于 SUM0、COUNTO 那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要 GROUP BY 的。
窗口函数的语法如下：
<窗口函数> OVER (
[PARTITION BY partition_expression, ... ]
[ORDER BY order_expression [ASC | DESC], ... ]
[ROWS {N | RANGE} {BETWEEN UNBOUNDED PRECEDING | expr PRECEDING}
AND {UNBOUNDED FOLLOWING | expr FOLLOWING}]
)PARTITION BY 指定了分组的表达式列表
ORDER BY 指定了排序的表达式列表
ROWS 指定了行的范围。
常用的窗口函数有：
ROW_NUMBER()：返回结果集中每一行的行号，不受 ORDER BY 限制；
RANK()：根据 ORDER BY 语句中的列排序，返回相同值的行排名相同；（相同的行排名相同，但是会跳过下一个排名）
DENSE_RANK()：根据 ORDER BY 语句中的列排序，返回相同值的行排名相同；（相同的行排名相同，但是不会跳过下一个排名）
NTILE(n)：将排序结果分为 n 组，返回每个分组中的行号；
SUM(), AVG(), MIN(), MAX() 等：执行通常的聚合函数操作，并在 OVER 子句中指定分组或排序规则。
mysql> CREATE TABLE scores (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> student VARCHAR(50) NOT NULL,
-> course VARCHAR(50) NOT NULL,
-> score INT NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO scores (student, course, score) VALUES
-> ('Alice', 'Math', 85),
-> ('Bob', 'Math', 92),
-> ('Alice', 'English', 88),
-> ('Bob', 'English', 90);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from scores;
+----+---------+---------+-------+
| id | student | course | score |
+----+---------+---------+-------+
| 1 | Alice | Math | 85 |
| 2 | Bob | Math | 92 |
| 3 | Alice | English | 88 |
| 4 | Bob | English | 90 |
+----+---------+---------+-------+
4 rows in set (0.00 sec)通过成绩大小来排名，窗口函数写法和日常SQL写法对比
mysql> SELECT id, student, course, score, RANK() OVER (ORDER BY score DESC) AS rank_number
-> FROM scores;
+----+---------+---------+-------+-------------+
| id | student | course | score | rank_number |
+----+---------+---------+-------+-------------+
| 2 | Bob | Math | 92 | 1 |
| 4 | Bob | English | 90 | 2 |
| 3 | Alice | English | 88 | 3 |
| 1 | Alice | Math | 85 | 4 |
+----+---------+---------+-------+-------------+
4 rows in set (0.00 sec)
mysql>
mysql> SELECT id, student, course, score FROM scores ORDER BY score DESC;
+----+---------+---------+-------+
| id | student | course | score |
+----+---------+---------+-------+
| 2 | Bob | Math | 92 |
| 4 | Bob | English | 90 |
| 3 | Alice | English | 88 |
| 1 | Alice | Math | 85 |
+----+---------+---------+-------+
4 rows in set (0.00 sec)计算总成绩
mysql> SELECT id, student,
-> SUM(score) OVER (PARTITION BY student) AS total_score
-> FROM scores;
+----+---------+-------------+
| id | student | total_score |
+----+---------+-------------+
| 1 | Alice | 173 |
| 3 | Alice | 173 |
| 2 | Bob | 182 |
| 4 | Bob | 182 |
+----+---------+-------------+
4 rows in set (0.00 sec)
mysql> SELECT student, SUM(score) as total_score
-> FROM scores
-> GROUP BY student;
+---------+-------------+
| student | total_score |
+---------+-------------+
| Alice | 173 |
| Bob | 182 |
+---------+-------------+
2 rows in set (0.00 sec)
