注册登录

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', '', 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', '', null, 'go', null, '2019-09-26 16:20:03');
知识兔View Code

group_concat函数

select GROUP_CONCAT(name) from t_person GROUP BY department;    --根据部门分组，把相同部门的人员名称连接起来，使用默认分隔符（逗号）
知识兔

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个
知识兔
计算机