MySql常用函数

@幻影忍者

已于 2023-05-31 14:10:00 修改

阅读量209

点赞数

分类专栏：数据库文章标签： mysql 数据库

于 2023-04-17 10:45:02 首次发布

本文链接：https://blog.csdn.net/zw899004/article/details/130194821

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

建表语句

create table 表名 ( 
    字段名 字段类型 comment '字段的注释'
) comment='表注释';

示例：

CREATE TABLE `rab_msg_log` (
`id` bigint primary key AUTO_INCREMENT COMMENT '主键',
`msg_id` varchar(255) NOT NULL DEFAULT '' COMMENT '消息唯一标识',
`msg` json NULL COMMENT '消息体, json格式化',
`exchange` varchar(500) NULL DEFAULT NULL COMMENT '交换机',
`queues` varchar(500) NULL DEFAULT NULL COMMENT '队列',
`routing_key` varchar(500) NULL DEFAULT NULL COMMENT '路由Key',
`status` int NOT NULL DEFAULT 0 COMMENT '状态: 0投递中 1投递成功 2投递失败 3已消费',
`create_by` varchar(100) NULL DEFAULT NULL COMMENT '创建者',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(100) NULL DEFAULT NULL COMMENT '修改者',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间'
) COMMENT = 'rabbit消息投递失败日志';

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

示例：

添加字段

ALTER TABLE `product_base`
ADD COLUMN `joins` varchar(10) DEFAULT NULL COMMENT '连接方式',
ADD COLUMN `power_method` varchar(10) DEFAULT NULL COMMENT '通电方式',
ADD COLUMN `funs` varchar(10) DEFAULT NULL COMMENT '功能',
ADD COLUMN `product_model` varchar(10) DEFAULT NULL COMMENT '产品型号';

修改字段类型

ALTER TABLE product_device MODIFY power varchar(50);

设置唯一键
ALTER TABLE product_device ADD unique key(device_num);

字符串拼接

CONCAT(str1,str2,…)
CONCAT_WS(separator,str1,str2,…)
GROUP_CONCAT(expr)

CONCAT、CONCAT_WS其实差不多，就是CONCAT_WS能自己设置分隔符，而且这两个字段是拼接同一行的数据。

测试用例：

CONCAT

SELECT
   pb.product_code,
   pd.device_num,
   CONCAT('https://XXXXX/download/index.html?d=',pb.product_code,'&sn=',pd.device_num) as qrcode_url
FROM
   product_device pd

JOIN product_base pb ON pb.id = pd.product_id

输出结果：

CONCAT_WS

SELECT
   pb.product_code,
   pd.device_num,
   CONCAT_WS(':','https://xxxxxxx/download/index.html?d=',pb.product_code,'&sn=',pd.device_num) as qrcode_url
FROM
   product_device pd
   JOIN product_base pb ON pb.id = pd.product_id

结果：

GROUP_CONCAT

将所有结果都拼接到一起；

SELECT
   pb.product_code,
   pd.device_num,
   GROUP_CONCAT('https://xxx/download/index.html?d=',pb.product_code,'&sn=',pd.device_num) as qrcode_url
FROM
   product_device pd
   JOIN product_base pb ON pb.id = pd.product_id

结果:

日期

1.DATE(date)

返回指定日期/时间表达式的日期部分或将文本转为日期格式

示例

mysql> select date("2022-3-15");2022-03-15

示例

mysql> select date('2022-4-15 12:30:48');2022-04-15

2.YEAR(date)

返回指定日期的年份(范围在1000到9999)

示例

mysql> select year('2022-4-15 12:30:48');2022

类似的，MONTH(date)返回指定日期的月份(范围在1到12）；DAY(date)返回指定日期的日(范围在1到31)；HOUR(datetime)返回指定时间的小时(范围在0-23)；minute(datetime)返回指定时间的分(范围在0-59)；second(datetime)返回指定时间的秒(范围在0-59)。

3.对日期时间进行加减运算

ADDDATE(date,interval expr type)

DATE_ADD(date,interval expr type)

SUBDATE(date,interval expr type)

DATE_SUB(date,interval expr type)

其中，date是一个datetime或date值；expr是对date进行加减法的一个表达式字符串或一个数字；type指明表达式expr应该如何被解释，是减去1天还是一年等。

示例

mysql>select adddate('2022-4-1',interval 5 day);2022-04-06

select adddate('2022-4-15 13:30:28',interval '3 1:2' day_minute);2022-04-18 14:32:28

4.DATE_FORMAT(date,format)

根据format字符串格式化date值，常用于获取日期的年月日和时间

在format字符串中可用标志符

%M 月名字(january……december)

%Y 年, 数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(sun……sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(jan……dec)

%j 一年中的天数(001……366)

%h 十二时制的小时(00……12)

%k 二十四时制的小时(0……23)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [ap]m)

%s 秒(00……59) %p am或pm

%w 一个星期中的天数(0=sunday ……6=saturday ）

%u 一年中的周数(1……53)

示例

mysql> select date_format('2022-4-15 13:30:28','%Y-%m-%d');
2022-04-15

5.CURDATE()

以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字)

示例

mysql> select curdate();2022-04-15

mysql> select curdate()+0;20220415

6.CURTIME()

以'hh:mm:ss'或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字)

mysql> select curtime();22:26:44

7.NOW()

以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字符串或数字

示例

mysql> select now();2022-04-15 22:28:33

8.TIMESTAMPDIFF(type,expr1,expr2)

返回起始日expr1和结束日expr2之间的时间差整数。

时间差的单位由type指定：

second 秒

minute 分

hour 时

day 天

month 月

year 年

示例

select timestampdiff(day,'2022-4-1','2022-4-15') 14

9.UNIX_TIMESTAMP([date])

返回一个unix时间戳(从'1970-01-01 00:00:00'开始的秒数,date默认值为当前时间)

mysql> select unix_timestamp('2022-4-15');1649952000

10.FROM_UNIXTIME(unix_timestamp)

以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符串或数字)

mysql> select from_unixtime(1649952001);2022-04-15 00:00:01

日期运算

MySQL自带的日期函数TIMESTAMPDIFF计算两个日期相差的秒数、分钟数、小时数、天数、周数、季度数、月数、年数，当前日期增加或者减少一天、一周等等。

相差的秒数：
SELECT TIMESTAMPDIFF(SECOND,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的分钟数：
SELECT TIMESTAMPDIFF(MINUTE,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的小时数：
SELECT TIMESTAMPDIFF(HOUR,'1993-03-23 00:00:00 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的天数：
SELECT TIMESTAMPDIFF(DAY,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的周数：
SELECT TIMESTAMPDIFF(WEEK,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的季度数：
SELECT TIMESTAMPDIFF(QUARTER,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的月数：
SELECT TIMESTAMPDIFF(MONTH,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))