MySQL 数据库的常用系统变量和系统函数

[TOC]

## 1. 系统变量
查询使用 `show` 关键字，修改使用 `set` 关键字

### 1.1. 查看系统变量 show variables

**语法：**show variables，默认查询的是所有系统变量，要筛选查看的变量可以使用 like 关键字搭配通配符进行查询

#### 1.1.1. 查看 MySQL 相关的系统编码

**示例：**

```SQL
-- 查询使用 show 关键字
show variables like 'character%';
```

**示例查询结果：**

| Variable_name            | Value                                        |
|--------------------------|----------------------------------------------|
| character_set_client     | gbk                                          |
| character_set_connection | gbk                                          |
| character_set_database   | utf8mb4                                      |
| character_set_filesystem | binary                                       |
| character_set_results    | gbk                                          |
| character_set_server     | utf8mb4                                      |
| character_set_system     | utf8mb3                                      |
| character_sets_dir       | D:\MySQL\mysql-8.0.32-winx64\share\charsets\ |

#### 1.1.1. 查看可编辑函数设置

**示例：**

```SQL
show variables like '%log_bin_trust_function_creators%';
-- 输出结果为ON表示开启了，OFF表示关闭状态
set global log_bin_trust_function_creators = 1;
-- 输出结果为ON表示开启了
```

**示例查询结果：**

| Variable_name| Value |
|--|--|
| log_bin_trust_function_creators | ON|

## 2. 系统函数

### 2.1. 查询MySQL信息
#### 2.1.1. 查询当前MySQL版本 version()

```SQL
select version();
-- 输出：8.0.32
```
#### 2.1.2. 查询当前登录的账户 user()

```SQL
select user();
-- 输出：root@localhost
```
***

### 2.2. 数字函数
#### 2.2.1. 随机数函数 rand()
获取到一个0到1之间的随机数

```SQL
Select Rand
-- 输出：0.23685342933689563
```

#### 2.2.2. 向下取整函数 floor()

```SQL
select floor(0.23685342933689563 * 20);
-- 输出：4
```
***

### 2.3. 字符串函数
#### 2.3.1. 指定位置截取函数 substring()

以字符为单位进行截取

**语法：**select substring(字符串,index,length)，index：截取的开始位置，MySQL中截取的位置默认是从`1`开始的length：截取的长度，，中文和英文截取的长度是一样的

**示例：**从第一位开始截取一个字符串

```SQL
set @cn = '你好，世界';
set @en = 'hello world';
select substring(@cn,1,1);
-- 输出：你
select substring(@en,1,1);
-- 输出：h
```

#### 2.3.2. 左截取函数 left() 有截取函数 right()
从左开始截取字符串和从右开始截取字符串

**语法：**select left/rigth(字符串,length)，length：需要取字符串的长度

**示例：**从左截取个字符，和从右边截取1个字符串

```SQL
select left(name,1) namel ,left(name,1) namer from my_student;
```

**示例结果：**

| namel| namer|
|--|--|
| 关| 羽|
| 张| 飞|
| 甄| 宓|

#### 2.3.3. 拼接函数 concat()
**语法：**select concat(字段1,字段2,字段3) from 表，这里的字段可以是变量或字符串

**示例：**将名字性别年龄连接成一个字段显示

```SQL
select concat(name,',',gender,',',age) 信息 from my_student;
```

**示例结果：**

| 信息|
|--|
| 关羽,男,52|
| 张飞,男,50|
| 甄宓,女,38|

#### 2.3.4. 获取编码类型 charset()
**语法：**select charset(字段) from 表

**示例：**查询名字字段的编码类型

```SQL
select charset(name) 编码类型 from my_student;
```

**示例结果：**

#### 2.3.5. 字符长度 char_length()
**语法：**select char_length(字段) from 表

**示例：**查询名字字段的字符长度

```SQL
select char_length(name) 字符长度 from my_student;
```

**示例结果：**

| 字符长度 |
|--|
|2 |
|2 |
|2 |

#### 2.3.6. 字节长度 length()
**语法：**select length(字段) from 表

**示例：**查询名字字段的字节长度

```SQL
select length(name) 字节长度 from my_student;
```

**示例结果：**

| 字符长度 |
|--|
|6 |
|6 |
|6 |

#### 2.3.7. 字符串包含字符串 instr()
判断字符串是否在某个具体的字符串存在，存在返回位置，不存在返回0

**语法：**select instr(字段,字符串) from 表，判断`字段`中是否存在`字符串`

**示例：**查询所有姓张的人

```SQL
select instr(name,'张') from my_student;
```

**示例结果：**

|  instr(name,'张') |
|--|
|0 |
|1 |
|0 |

#### 2.3.8. 左填充 lpad() 右填充 rpad()
将字符串按照某个自定的填充方式，填充到指定长度，单位是字符

**语法：**select lpad/rpad(字段,填充长度,字符串) from 表，往 `字段` 参数里面填充字符，填充的内容是 `字符串` 参数 ,填充后，整个字段的长度是 `填充长度` 参数，也就是说`需要填充长度 = 填充长度-字段本身的长度`，如果 `字符串` 参数长度大于需要填充的长度，那么它会截取需要的字符进行填充，如果 `字符串` 参数长度小于需要填充的长度，那么它会重复使用`字符串` 参数进行填充，直到达到取需要的字符长度

**示例：**给名字左边添加字符串，给民族右边加字符串

```SQL
select lpad(name,9,'三国') name,rpad(nation,2,'族') nation from my_student;
```

**示例结果：**

| name| nation |
|--|--|
| 三国三国三国三关羽 | 苗族|
| 三国三国三国三张飞 | 汉族|
| 三国三国三国三甄宓 | 汉族|

#### 2.3.9. 替换函数 insert()
找到目标位置，指定长度的字符串，替换成目标字符串

**语法：**select insert(字段,目标开始位置,指定长度,字符串) from 表，在字段中的目标开始位置往后数指定长度，然后用字符串替换这段长度的字符串

**示例：**将下面变量中的 `呆在` 替换为 `丢进`

```SQL
-- 这里使用变量来演示：
set @title='垃圾就应该呆在垃圾桶里' ;
select insert(@title,6,2,'丢进') title ;
```

**示例结果：**`呆在` 在变量中出现的位置是6，然后算2个字符，然后用 `丢进` 替换

#### 2.3.10. 比较函数 strcmp()
cmp是compare的简写，按正序排序比较，第一参数排序比第二个参数排序，小返回-1，等于返回0，大于则返回1，strcmp()比较函数不区分大小写

**语法：**select strcmp(字符串1,字符串2)字符串1 > 字符串2 返回1，字符串1 = 字符串2 返回0，字符串1 < 字符串2 返回-1

**示例：**

```SQL
-- 这里使用变量来演示：
set @param1='我的貂蝉在哪里' ; --w
set @param2='你的貂蝉在这里' ; --n
set @param3='垃圾就应该呆在垃圾桶里' ;--l
select strcmp(@param1,@param2) param1, strcmp(@param1,@param3) param2, strcmp(@param3,@param1) param3,length(@param1) p1,length(@param2) p2,length(@param3) p3;
```

**示例结果：**按排序`我的貂蝉在哪里(w) > 你的貂蝉在这里(n) > 垃圾就应该呆在垃圾桶里(l)`

| param1 | param2 | param3 |
|--------|--------|--------|
|      1 |      1 |     -1 |

#### 2.3.11. 重复指定的次数 repeat()

**语法：**select repeat(字符串,次数) from 表将 `字符串` 重复拼接指定 `次数`

**示例：**将name字段重复拼接3次

```SQL
select repeat(name,3) from my_student;
```

**示例结果：**

#### 2.3.12. 反转显示函数 reverse()

**语法：**select reverse(字符串) from 表

**示例：**将name字段反向显示

```SQL
select reverse(name) from my_student;
```

**示例结果：**

| repeat(name,3)|
|--|
| 羽关|
| 飞张|
| 宓甄|

***

### 2.4. 统计函数
#### 2.4.1. 总行数统计 count()
count()函数中可以是\*符号，也可以是0，还可以是字段名，为字段名时，字段中有NULL的数据不参与统计，\*符号和0统计的效果一样

**语法：**select count(*) from 表

**示例：**查看总共有多少条学生记录

```SQL
select count(*) as total from my_student;
```

**示例结果：**

| total |
|--|
|9 |

#### 2.4.2. 计算最大值 max()

**语法：**select MAX(字段) from 表

**示例：**查看学生中最大年龄是多少

```SQL
select MAX(age) as max from my_student;
```

**示例结果：**

| max |
|--|
|68 |

#### 2.4.3. 计算最小值 min()

**语法：**select min(字段) from 表

**示例：**查看学生中最小年龄是多少

```SQL
select min(age) as min from my_student;
```

**示例结果：**

| min |
|--|
|26 |

#### 2.4.4. 计算平均值 avg()

**语法：**select avg(字段) from 表

**示例：**查看学生中平均年龄是多少

```SQL
select avg(age) as 平均年龄 from my_student;
```

**示例结果：**

| 平均年龄 |
|--|
| 45.0000 |

#### 2.4.5. 计算和 sum()

**语法：**select sum(字段) from 表

**示例：**查看学生总年龄

```SQL
select sum(age) as 年龄 from my_student;
```

**示例结果：**

| 年龄 |
|--|
| 405 |

***

### 2.5. 时间函数
#### 2.5.1. 获取当前时间 now()

**语法：**select now();

**结果：**

|now()|
|--|
|2020-05-27 18:20:24|