# distinct去重select count(distinct *) from 表名select sum(distinct quantity) from 表名select avg(distinct quantity) from 表名select max(distinct quantity) from 表名select min(distinct quantity) from 表名
select length(username) from account //返回9select char_length(username) from account //返回3
select concat(username, password) from account;
len为插入的长度,s2为插入的字符串;
select insert(username, 3, 3, "大帅逼") from account;
select replace(username, "大帅逼", "大丑逼") from account;
select upper(username) from account
select left(username, 2) from accountselect substring(username, 2) from account #返回第2个字符串开始的字符串
select lpad(username, 10, "??") from account
# 删除两侧空格select trim(username) from account#删除两侧s字符串select trim(s from username) from account
select repeat(username, 3) from account
select concat(s1, space(5), s2) from account
SELECT CURDATE(), CURTIME(), LOCALTIMESTAMP()
SELECT * FROM work_hours WHERE check_date > '2022-07-01'
date_add(日期1, INTERVAL n {year, month, day, minute, second, ...}) 日期1增加n个年月日
datediff(日期1, 日期2) 日期1和日期2相差多少天(正负都有)
timediff(日期1, 日期2) 日期1和日期2相差多少时间(时分秒表示)
set @a = NOW();set @b = DATE_ADD(@a,INTERVAL 1 year);SELECT DATEDIFF(@a,@b)
SELECT DATE_FORMAT(CURDATE(), "%y/%m/%d")SELECT DATE_FORMAT(CURDATE(), "%y-%m-%d")SELECT DATE_FORMAT(CURDATE(), "%y/%m/%d")SELECT DATE_FORMAT(LOCALTIMESTAMP(), "%y/%m/%d %h-%i-%s")
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())
SELECT ABS(-100), CEILING(1.1), FLOOR(1.1), ROUND(1.5), EXP(2), CAST(RAND()*100 AS SIGNED), LOG(4,2), PI(), POW(2,4), SQRT(4), SIN(PI())
SELECT 1+'2', CONCAT(1, '2');
cast(数据 as 数据类型)
BINARY[(N)] :二进制字符串,转换后长度小于N个字节
CHAR[(N)] :字符串,转换后长度小于N个字符
DATE :日期
DATETIME :日期时间
DECIMAL[(M[,N])] :浮点数,M为数字总位数(包括整数部分和小数部分),N为小数点后的位数
SIGNED [INTEGER] :有符号整数
TIME :时间
UNSIGNED [INTEGER] :无符号整数
SELECT CAST(pi() AS SIGNED), CAST('123a456' as SIGNED)
SELECT IF(1 = 1, 1, 0)SELECT ISNULL(NULL)
原文出处:2. 函数