sql 常用函数

 初始化数据库语句：

DROP TABLE IF EXISTS `t_person`;
CREATE TABLE `t_person` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `birthdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `isMale` bit(1) DEFAULT b'0',
  `height` double DEFAULT NULL,
  `department` varchar(255) DEFAULT 'dev',
  `desc` varchar(255) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of t_person
-- ----------------------------
INSERT INTO `t_person` VALUES ('1', 'Perter', '18', '2019-09-26 16:13:28', 0, null, 'dev', null, '2019-09-26 16:20:00');
INSERT INTO `t_person` VALUES ('2', 'Alan', null, '2019-09-26 16:14:05', '\0', null, 'dev', null, '2019-09-26 16:14:05');
INSERT INTO `t_person` VALUES ('3', 'Helen', null, '2019-09-26 16:17:18', 1, null, 'dev', null, '2019-09-26 16:20:01');
INSERT INTO `t_person` VALUES ('4', 'Halo', null, '2019-09-26 16:19:25', 0, null, 'op', null, '2019-09-26 16:19:25');
INSERT INTO `t_person` VALUES ('5', 'Zed', null, '2019-09-26 16:19:33', 0, null, 'op', null, '2019-09-26 16:19:33');
INSERT INTO `t_person` VALUES ('6', 'Linda', null, '2019-09-26 16:19:48', 1, null, 'go', null, '2019-09-26 16:20:03');

 

group_concat函数

select GROUP_CONCAT(name order by name desc) from t_person group by department;    --根据部门分组，把相同部门的人员名称连接起来，使用默认分隔符（逗号），并且对name之间的连接进行降序排序

 

SUBSTR函数

select SUBSTR(name, 1) from t_person;    -- 截取name，从第1个字符到最后
select SUBSTR(name, 2,2) from t_person;    --截取name，从第2个字符，截取长度为2
select SUBSTR(name, -2) from t_person;    --截取name，从倒数第2个字符，截取到最后

 

SUBSTRING函数：用法同SUBSTR

 

SUBSTRING_INDEX函数

SELECT SUBSTRING_INDEX("a,b,c,d,e,f", "," , 3);     -- 分割字符串，分割符为逗号，获取前面3个
SELECT SUBSTRING_INDEX("a,b,c,d,e,f", "," , -2);      -- 分割字符串，分割符为逗号，获取后面2个

 

SUBDATE, ADDDATE,函数

select SUBDATE(now(),interval 10 second);    -- 获取当前时间的，前10秒的时间
select SUBDATE(now(),interval 1 minute);    -- 获取当前时间的，前1分钟的时间
select SUBDATE(now(),interval 1 hour);    -- 获取当前时间的，前1小时的时间
select SUBDATE(now(),interval 1 day);    -- 获取当前时间的，前1天的时间
select SUBDATE(now(),interval 1 week);    -- 获取当前时间的，前1星期的时间
select SUBDATE(now(),interval 1 month);    -- 获取当前时间的，前1个月的时间
select SUBDATE(now(),interval 1 year);    -- 获取当前时间的，前1年的时间

select ADDDATE(now(),interval 10 second);    -- 获取当前时间的，后10秒的时间
select ADDDATE(now(),interval 1 minute);    -- 获取当前时间的，后1分钟的时间
select ADDDATE(now(),interval 1 hour);    -- 获取当前时间的，后1小时的时间
select ADDDATE(now(),interval 1 day);    -- 获取当前时间的，后1天的时间
select ADDDATE(now(),interval 1 week);    -- 获取当前时间的，后1星期的时间
select ADDDATE(now(),interval 1 month);    -- 获取当前时间的，后1个月的时间
select ADDDATE(now(),interval 1 year);    -- 获取当前时间的，后1年的时间

 

批量插入

insert into t_person (name, department, isMale, update_time) (
  select name, department, isMale, CURRENT_TIMESTAMP from t_person where name = "Zed"
);    -- 从当前表，复制并冗余指定字段的数据到该表中
insert into t_person (
  select * from t_person where name = "Zed"
);    -- 从当前表，复制并冗余所有字段的数据到该表中，（注意，有唯一约束的字段的时候，会报错Duplicate entry '5' for key 'PRIMARY'）