MySql - 函数、日期处理 [COUNT、SUM、MAX、NOW、UNIX_TIMESTAMP]

---------------------------------
---------------------------------

-- 函数操作

---------------------------------
---------------------------------

-- COUNT 计算指定字段出现的个数，不包含NULL值
-- SUM 计算某一列值的总和
-- AVG 计算某一列值的平均数
-- MAX 获取某一列值的最大数
-- MIN 获取某一列值的最小数
-- CONCAT 将多个字符串连接成一个字符串
-- CONCAT_WS 和concat()一样，但是可以指定分隔符（concat_ws就是concat with separator）
-- GROUP_CONCAT 将分组中括号里对应的字符串进行连接.如果分组中括号里的参数xxx有多行，那么就会将这多行的字符串连接，每个字符串之间会有特定的符号进行分隔。
-- LEFT 是一个字符串函数，它返回具有指定长度的字符串的左边部分。
-- RIGHT 是一个字符串函数，它返回具有指定长度的字符串的右边边部分。
-- SUBSTRING 截取字符串
-- SUBSTR 截取字符串
-- FROM_UNIXTIME 时间戳转字符串 FROM_UNIXTIME(timestamp/TimestampField, '%Y-%m-%d %H:%i:%s')
-- UNIX_TIMESTAMP() 获取当前时间戳 秒
-- DATE_FORMAT
-- CURRENT_DATE 获取当前日期 YYYY-MM-DD
-- CURRENT_TIME 获取当前时间 HH:II:SS



-- 统计总数COUNT
-- COUNT(expr) ，返回SELECT语句检索的行中expr的值不为NULL的数量。结果是一个BIGINT值。
-- COUNT(*) 的统计结果中，会包含值为NULL的行数。
-- COUNT(*) 是SQL92定义的标准统计行数的语法, Mysql对其进行了优化，跟数据库无关,跟NULL和非NULL无关
-- COUNT(FieldColumn) 查询FieldColumn不为空的总行数

-- MyIsam执行COUNT(*)可直接返回记录的总条数，不能带有WHERE的情况下。MyIsam已经把表的总行数放到磁盘文件中了。
-- InnoDB因为有行级锁和事务，总数容易出现变化，所以没有存储到磁盘文件中。[统计和并发写执行的时候写的统计要比查的统计多]
-- 【MySQL 8+】InnoDB执行COUNT(*)会优先按照成本较低的索引进行扫表统计。如果只有聚簇索引就只能按照聚簇索引统计，如果有普通索引，会按照成本较小的索引进行统计
    -- 在执行统计的sql中不能包含where和group by等
-- COUNT(常量[1,2,3...])与COUNT(*)效率等同，没有谁比谁好。COUNT(*)是sql标准语法，所以建议用count(*)
-- COUNT(FieldColumn) Mysql执行的时候会按照指定字段列进行统计，全表扫描，每行做判断不为NULL就累加，为NULL就跳过。所以比COUNT(*)慢
-- 没有WHERE、GROUP BY的时候 count(FieldColumn) < COUNT(*) COUNT(1)基本等于COUNT(*)
    -- 所以统计的时候建议直接使用COUNT(*)就行了。
-- 参考: https://blog.csdn.net/weixin_50966947/article/details/126769434
SELECT COUNT(*) FROM users;
SELECT COUNT(`id`) FROM users;
SELECT COUNT(1) FROM users;


-- 求总和SUM
-- SELECT SUM(FieldColumn) as sum FROM TABLENAME WHERE expr...;
-- 如果在没有返回匹配行 SELECT 语句中使用 SUM 函数，则 SUM 函数返回 NULL，而不是 0。
-- 忽略计算中的 NULL 值。

ALTER TABLE users ADD integral INT(11) DEFAULT 0 NULL COMMENT '用户积分';
SELECT SUM(integral) as total_integral FROM users;


-- 求平均数AVG
-- SELECT AVG(FieldColumn) FROM TABLENAME WHERE expr...;
-- 返回float
-- 忽略计算中的 NULL 值[值为NULL的行不参与计算]。

SELECT AVG(integral) as avg_num FROM users;


-- 获取单列的最大值 MAX
-- 获取单列的最小值 MIN
-- SELECT MAX(FieldColumn) FROM TABLENAME WHERE expr...;
-- SELECT MIN(FieldColumn) FROM TABLENAME WHERE expr...;
-- 忽略计算中的NULL行，值为NULL的行被忽略

SELECT MAX(integral) as max_num FROM users;
SELECT MIN(integral) as min_num FROM users;


-- 字符串拼接 CONCAT、CONCAT_WS
-- SELECT CONCAT(Field1, Field2...) AS newField FROM TABLENAME WHERE expr...;
-- SELECT CONCAT_WS(separator, Field1, Field2...') AS newField FROM TABLENAME WHERE expr...;
-- [CONCAT] NULL：要拼接的字段只要有一个字段的值为NULL 结果就为NULL
-- [CONCAT_WS] NULL：拼接的字符串类型的字段有NULL值，会被忽略掉，把剩下的非NULL值按照分隔符拼接

SELECT `id`,CONCAT(username, status) as us FROM users;
SELECT `id`,CONCAT_WS(',', username, status) as us FROM users;
SELECT `id`, CONCAT(username, `status`, `integral`) as ust FROM users;
SELECT `id`, CONCAT_WS(',', username, `status`, `integral`) as ust FROM users;


-- 分组拼接字符串GROUP_CONCAT
-- SELECT GROUP_CONCAT(field ORDER BY Field SEPARATOR sep) FROM TABLENAME WHERE expr... GROUP BY FieldColumn;
-- 如果没有写ORDER BY句段，那么连接是没有顺序的，基本按照查出来的数据顺序拼接。
SELECT `status`,GROUP_CONCAT(CONCAT_WS(',', username, integral) ORDER BY `integral` DESC SEPARATOR ';') as n FROM users GROUP BY `status`;
SELECT `status`,GROUP_CONCAT(CONCAT_WS(',', username, integral) SEPARATOR ';') as n FROM users GROUP BY `status`;


-- LEFT、RIGHT
-- SELECT LEFT(FieldColumn, length) AS field FROM TABLENAME;
-- SELECT field1,field2... FROM TABLENAME WHERE LEFT(FieldColumn, length) = VALUE ...;
-- SELECT RIGHT(FieldColumn, length) AS field FROM TABLENAME;
-- SELECT field1,field2... FROM TABLENAME WHERE RIGHT(FieldColumn, length) = VALUE ...;
-- 如果FieldColumn值为NULL则LEFT与RIGHT的返回值都是NULL
-- 如果length为小于等于0的值，返回结果为空字符串[NULL还是NULL]
-- 参考：https://blog.csdn.net/moakun/article/details/82086078

SELECT LEFT(username, 2) as username FROM users;
SELECT `id`, username FROM users WHERE LEFT(username, 2) = 'He' OR LEFT(username, 2) LIKE '%h';
SELECT RIGHT(username, 2) as username FROM users;
SELECT RIGHT(username, -1) as username FROM users;
SELECT `id`, username FROM users WHERE RIGHT(username, 2) = 'LO' OR RIGHT(username, 2) LIKE '%l%';


-- 截取字符串 SUBSTR、SUBSTRING
-- SUBSTR(Start, Length) Start开始位置 Length 长度
-- SUBSTRING(Start, Length) Start开始位置 Length 长度
-- 位置：第N个字符，不是第N个字符的下标[从1开始的，不是从0开始的]
-- NULL一直都是NULL
-- SELECT SUBSTR(FieldColumn, start, length) FROM TABLENAME;
-- SELECT * FROM TABLENAME WHERE SUBSTR(FieldColumn, start, length) = Value ...;
-- SELECT SUBSTRING(FieldColumn, start, length) FROM TABLENAME;
-- SELECT * FROM TABLENAME WHERE SUBSTRING(FieldColumn, start, length) = Value ...;

SELECT SUBSTR(username, 1, 3) FROM users;
SELECT SUBSTRING(username, 2, 3) FROM users;
SELECT * FROM users WHERE SUBSTR(username, 2, 3) = 'est';
SELECT * FROM users WHERE SUBSTRING(username, 2, 3) = 'est';


-- 日期时间处理
-- https://www.w3school.com.cn/sql/func_date_format.asp

SELECT UNIX_TIMESTAMP(); -- 1677141154
SELECT UNIX_TIMESTAMP('2023-02-23 16:32:34'); -- 1677141154
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s'); -- 2023-02-23 16:32:34
SELECT CURRENT_DATE; -- 2023-02-23
SELECT CURRENT_TIME; -- 16:32:34
SELECT CURRENT_TIMESTAMP; -- 2023-02-23 16:32:34
SELECT CURRENT_TIMESTAMP(); -- 2023-02-23 16:32:34
SELECT NOW(); -- 2023-02-23 16:32:34
SELECT LOCALTIME; -- 2023-02-23 16:32:34
SELECT LOCALTIME(); -- 2023-02-23 16:32:34
SELECT LOCALTIMESTAMP; -- 2023-02-23 16:32:34
SELECT LOCALTIMESTAMP(); -- 2023-02-23 16:32:34
SELECT DATE_FORMAT(NOW(), '%Y-%m[%b]-%d %H:%i:%s.%f[%j:%M, %a]'); -- 2023-02[Feb]-23 16:40:30.000000[054:February, Thu]
SELECT DATE_FORMAT(UNIX_TIMESTAMP(), '%Y-%m[%b]-%d %H:%i:%s.%f[%j:%M, %a]'); -- NULL DATE_FORMAT参数为字符串，非时间戳