简介

本文用于记录MySQL中的一些函数或语法。

持续更新中。。。

关键字

临时表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;