SQL中常用的一些函数

每天记录开发中的一个小问题

今天开发中遇到一条很有意思的mysql 这条sql基本上涵盖了所有我们平时常用的一些函数

以下SQL为MYSQL数据库

SELECT
	*
FROM
	TABLE
	LEFT JOIN TAB_PERSON ON S_PERSON_ID = TAB_PERSON.S_ID
	LEFT JOIN TAB_USER AS OBJPOLICE1 ON TABLE.S_POLICE_ID_1 = OBJPOLICE1.S_ID
	LEFT JOIN TAB_USER AS OBJPOLICE2 ON TABLE.S_POLICE_ID_2 = OBJPOLICE2.S_ID
	LEFT JOIN TAB_DEPARTMENT AS DEPTOBJ ON TABLE.S_DEPT_ID = DEPTOBJ.S_ID 
WHERE
	1 = 1 
	AND INSTR(
		CONCAT( TABLE.S_POLICE_ID_1, ',', TABLE.S_POLICE_ID_2, ',', TABLE.S_STANDBOOK_USER_ID ),
	?)> 0 
	AND INSTR(
	CONCAT_WS( ' , ' , TAB_PERSON.S_NAME, OBJPOLICE1.S_CAPTION, OBJPOLICE2.S_CAPTION ),?)> 0 
	AND TABLE.DT_CREATE_TIME >=? 
	AND TABLE.DT_CREATE_TIME = ? 
	AND TABLE.I_CHECK_STATUS = ? 
	AND TABLE.S_DOMAIN_ID IS NULL 
	AND TABLE.S_ID = ? 
	AND INSTR(
	TABLE,?) > 0 
	AND INSTR(
	CONCAT( ' , '  , OBJPOLICE1.S_CAPTION, OBJPOLICE2.S_CAPTION ) , ?)> 0 
	AND TABLE.S_INQUEST_DEPT IN ( ? ) 
	AND TABLE.DT_END_USER_TIME IS NOT NULL 
	AND CEILING( TIMESTAMPDIFF ( SECOND, TABLE.DT_START_USETIME, IFNULL ( TABLE.DT_END_USE_TIME, NOW()))/ 60 ) < 60 
ORDER BY
	TABLE.DT_CREATE_TIME DESC
 LIMIT     ? , ?

表结构演示所用

DROP TABLE IF EXISTS `Person`;
CREATE TABLE `Person` (
  `birth` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `id` bigint NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `phone` bigint DEFAULT NULL,
  `momeny` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `Person` VALUES ('2020-06-16 23:24:21', 1, '大一', 10086, 110.11);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:26', 2, '大二', 110, 334.11);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:30', 3, '大三', 120, 334.23);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:35', 4, '李四', 114, 213.00);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:39', 5, '王五', 111, 123.00);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:42', 6, '赵六', 112, 565.00);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:46', 7, '孙七', 125, 454.00);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:49', 8, '王八', 12345, 454.00);
INSERT INTO `Person` VALUES ('2020-06-16 23:24:52', 9, '李九', 119, 898.00);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

解析：

• ？ 绑定参数
• WHERE 1=1 为拼接提供便利
• LEFT JOIN …… ON 左连接
• AS 别名
• INSTR(STR,SUBSTR) INSTR()函数一般适用于模糊查询，比传统的 like 方式速度更快。
  参数说明：STR……搜索的字符串 SUBSTR……要搜索的子字符串
  在字符串STR里面,字符串SUBSTR出现的第一个位置(INDEX)，INDEX是从1开始计算，如果没有找到就直接返回0，不会返回负数。
  上图：

SELECT	* FROM Person WHERE INSTR( NAME, "大" )>0

SELECT INSTR(NAME, '大')  from Person

• >= <= 用于时间的比较

• IS NULL IS NOT NULL 用于判断为空或者不为空，也可以用 ！=来判断

• CONCAT(str1,str2,...) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。
  上图：

SELECT CONCAT(NAME,',',id),birth FROM Person

SELECT CONCAT(NAME,id),birth FROM Person

• CONCAT_WS(separator,str1,str2,...) 指定参数之间的分隔符
  使用函数CONCAT_WS（）。使用语法为：CONCAT_WS(separator,str1,str2,…)
  CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）
  听不懂？上图：

SELECT CONCAT_WS('_',birth,NAME) AS aaa FROM Person 

SELECT CONCAT_WS(',','ww',NULL,'zz');

• ORDER BY 用来对数据库的一组数据进行排序 (ORDER BY按字段排序)

• DESC：降序 ASC 升序

• GROUP BY “By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。 (GROUP BY 按字段分类)

• SUM(列名) 求和

• MAX(列名) 最大值

• MIN(列名) 最小值

• AVG(列名) 平均值

• FIRSET(列名) 第一条记录……（仅Access支持）

• LAST(列名) 最后一条记录……（仅Access支持）

• COUNT(列名) 统计记录数……（注意和count(*)的区别）

  CUOUNT(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
  CUOUNT(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
  CUOUNT(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

• CEILING(X) 表示向上取整，只返回值X的整数部分，小数部分舍弃。

SELECT CEILING(123.654)

• FLOOR(X)表示向下取整，只返回值X的整数部分，小数部分舍弃。

SELECT FLOOR('123.456')

• ROUND(X) 表示将值 X 四舍五入为整数，无小数位
• ROUND(X,D) – 表示将值 X 四舍五入为小数点后 D 位的数值，D为小数点后小数位数。若要保留 X 值小数点左边的 D 位，可将 D 设为负值。

SELECT ROUND('123.654')

SELECT ROUND('123.456',2)

• IFNULL(expression, alt_value) 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

SELECT IFNULL(NULL, NOW());

• / 除法运算
• * 乘法运算
• % 取余运算
• + 加法运算
• - 减法运算
• TIMESTAMPDIFF(unit,begin,end)根据单位返回时间差,对于传入的begin和end不需要相同的数据结构,可以存在一个为Date一个DateTime
  unit 支持的单位有MICROSECOND、SECOND、MINUTEHOUR、DAY、WEEK、MONTH、QUARTER、YEAR
  上图：

SELECT TIMESTAMPDIFF(SECOND, birth, NOW()) as  result FROM Person

• LIMIT i , n 用于限制查询结果返回的数量，常用于分页查询

  i： 为查询结果的索引值(默认从0开始)，当i=0时可省略i
  n：为查询结果返回的数量
  i 与 n 之间使用英文逗号","隔开
  limit n 等同于 limit 0,n

  上图：

  查询10条数据，索引从0到9，第1条记录到第10条记录
  SELECT * FROM Person LIMIT 10
  SELECT * FROM Person LIMIT 0,10
  查询8条数据，索引从5到12，第6条记录到第13条记录
  SELECT * FROM Person LIMIT 5,8