Mysql笔记

## 简介

> 本文用于记录MySQL中的一些函数或语法。
> 
> 持续更新中。。。

<!-- more -->


## 关键字

### 临时表dual

> dual用于创建虚拟的临时表，保证sql正常执行

```sql
select now() from dual;
```

### 分析explain

> 在一条sql的开头使用，用于分析sql的执行情况。

```sql
explain select * from dncc_dist where id = 1;
```

输出：

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|--|----|--|----|--|----|--|----|--|--|----|----|
|1|SIMPLE|dncc_dist|		|const|PRIMARY|PRIMARY|4|const|1|100|	


### 分页limit

- `limit N`: 返回 N 条记录
- `limit N,M` : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

## 函数

### 日期格式化DATE_FORMAT
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

- 语法：`DATE_FORMAT(date, format)`，date 参数是合法的日期。format 规定日期/时间的输出格式。


可以使用的格式有：

|格式|	描述 |
|--|----|
|%a	|缩写星期名                                      |
|%b	|缩写月名                                        |
|%c	|月，数值                                        |
|%D	|带有英文前缀的月中的天                          |
|%d	|月的天，数值(00-31)                             |
|%e	|月的天，数值(0-31)                              |
|%f	|微秒                                            |
|%H	|小时 (00-23)                                    |
|%h	|小时 (01-12)                                    |
|%I	|小时 (01-12)                                    |
|%i	|分钟，数值(00-59)                               |
|%j	|年的天 (001-366)                                |
|%k	|小时 (0-23)                                     |
|%l	|小时 (1-12)                                     |
|%M	|月名                                            |
|%m	|月，数值(00-12)                                 |
|%p	|AM 或 PM                                        |
|%r	|时间，12-小时（`hh:mm:ss` AM 或 PM）              |
|%S	|秒(00-59)                                       |
|%s	|秒(00-59)                                       |
|%T	|时间, 24-小时 (`hh:mm:ss`)                        |
|%U	|周 (00-53) 星期日是一周的第一天                 |
|%u	|周 (00-53) 星期一是一周的第一天                 |
|%V	|周 (01-53) 星期日是一周的第一天，与 %X 使用     |
|%v	|周 (01-53) 星期一是一周的第一天，与 %x 使用     |
|%W	|星期名                                          |
|%w	|周的天 （0=星期日, 6=星期六）                   |
|%X	|年，其中的星期日是周的第一天，4 位，与 %V 使用  |
|%x	|年，其中的星期一是周的第一天，4 位，与 %v 使用  |
|%Y	|年，4 位                                        |
|%y	|年，2 位                                        |


### 字符串转日期str_to_date

 

- 语法：`str_to_date(str,format)`；format格式必须和str的格式相同，否则返回空

- 例子：`select str_to_date('2017-10-29', '%Y-%m-%d %H:%i:%S')`

format可用的格式见上表。
 
### 随机数RAND

> 生成1以内随机数，包含小数点后16位

- 应用示例：

```sql
-- 生成10以内随机数，并取整
select ROUND(RAND() * 10) from dual;
```

### 取整ROUND

- ROUND(X)，对数字X取整
- ROUND(X, N)，对数字X取小数点后N位

- 应用示例：

```sql
-- 保留2为小数
select ROUND(10.12345, 2) from dual;
```

### 时间加减adddate

> 对时间类型数据进行加减，通过参数可以指定对时间加减秒、分钟、小时、天等等。

- 应用示例：

```sql
-- 对当前时间增加60分钟
select adddate(SYSDATE(), interval 60 minute) from dual;
```

### 分组聚合group_concat()

- 引用示例

```sql
select id, group_concat(score) as scores, group_concat(subject) as subjects from student_score group by id
```

student_score为学生成绩表，subject为科目名称，score为科目成绩，id为学生id。

此sql表示按学生id分组，将每个学生的科目聚合，成绩聚合。输出结果可能为：
|id|scores|subjects|
|--|----|-----|
|1|78,98,88|语文,数学,英语|
|2|89,80,94|语文,数学,英语|

## 数据类型

### VARBINARY (存储IP示例)

> VARBINARY 是一种 MySQL 数据库中用于存储可变长度二进制数据的数据类型。它与 VARCHAR 类似，但用于存储二进制数据而不是字符数据。VARBINARY 可以存储任意长度的二进制数据，包括字节、图片、文件等。这种数据类型在存储 IP 地址时非常有用，特别是当处理 IPv6 地址时，因为 IPv6 地址比 IPv4 地址更长。

假设你有一张 `log` 表，有 `ip` 和 `v` 两个字段，分别用于存储IP地址和类型。

- ip 字段：`VARBINARY(16)`
- v 字段：`int`，4代表IPv4，6代表IPv6。

示例：

```sql
-- 插入IPv4数据
insert into iplog(ip,v) value (INET_ATON('192.168.0.1'), 4);
-- 查询IPv4数据
select INET_NTOA(ip) from iplog where v = 4;

-- 插入IPv6数据
insert into iplog(ip,v) value (INET6_ATON('2409:8a3c:46d:5b70:48e:3882:11dd:3119'), 6);
-- 查询IPv6数据
select INET6_NTOA(ip) from iplog where v = 6;
```