使用的表结构
CREATE TABLE `cuser` (
`id` bigint(20) NOT NULL COMMENT '主键',
`user_name` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(100) NOT NULL COMMENT '密码',
`phone` varchar(20) NOT NULL COMMENT '手机号',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`id_card` varchar(30) DEFAULT NULL COMMENT '身份证号码',
`birthday` date NOT NULL COMMENT '生日',
`sex` tinyint(1) NOT NULL COMMENT '性别:1男、0女',
`nick_name` varchar(50) DEFAULT NULL COMMENT '昵称',
`head_picture` varchar(255) DEFAULT NULL COMMENT '头像',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态:0禁用、1启用',
`last_login_ip` varchar(100) DEFAULT NULL COMMENT '最后登录IP',
`last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
`crt_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`upt_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='C端用户表';
字符串函数
函数 | 描述 | 示例 | 结果 |
---|---|---|---|
FIELD(s,s1,s2…) | 字符串s在字符串列表s1,s2…中的位置 不存在时值为0 | SELECT FIELD('c', 'a', 'b', 'c'); | 3 |
FIND_IN_SET(s,s1) | 字符串s在字符串列表s1的位置 s1为逗号隔开的字符串,不存在时值为0 | SELECT FIND_IN_SET('c', 'a,b,c'); | 3 |
CONCAT(s1,s2…sn) | 合并字符串 | SELECT CONCAT('a','b','c'); | abc |
CONCAT_WS(x, s1,s2…) | 合并字符串,分隔符为x | SELECT CONCAT_WS('-','a','b'); | a-b |
RPAD(s1,len,s2) | 字符串s1的结尾处添加字符串s2,使字符串的长度达到 len | SELECT RPAD('abc',10,'123'); | abc1231231 |
REPEAT(s,n) | 字符串s重复n次 | SELECT REPEAT('abc',3); | abcabcabc |
SPACE(n) | 返回n个空格 | SELECT SPACE(10); | 10个空格 |
LEFT(s,n) | 截取,截取字符串s的前n个字符 | SELECT LEFT('abc',2); | ab |
RIGHT(s,n) | 截取,截取字符串s的后n个字符 | SELECT RIGHT('abc',2); | bc |
MID(s,n,len) | 截取,从字符串s的n位置截取长度为len的子字符串 | SELECT MID('abc',1,2); | ab |
SUBSTR(s, start, length) | 截取,从字符串s的start位置截取长度为length的子字符串 | SELECT SUBSTR('abc',1,2); | ab |
SUBSTRING(s, start, length) | 截取,从字符串s的start位置截取长度为length的子字符串 | SELECT SUBSTRING('abc',1,2); | ab |
REPLACE(s,s1,s2) | 替换,字符串s中的子字符串s1替换为字符串s2 | SELECT REPLACE('abc','a','x'); | xbc |
INSERT(s1,x,len,s2) | 替换,字符串s1的x位置开始长度为len的部分使用字符串使用s2替换 | SELECT INSERT('abd', 2, 1, '123'); | a123d |
LTRIM(s) | 去空格,去掉字符串开始处的空格 | SELECT LTRIM(' abc'); | abc |
RTRIM(s) | 去空格,去掉字符串结尾处的空格 | SELECT RTRIM('abc '); | abc |
TRIM(s) | 去空格,去掉字符串开始和结尾处的空格 | SELECT TRIM(' abc '); | abc |
LOCATE(s1,s) | 字串位置,字符串s1在字符串s中的位置(开始位置) | SELECT LOCATE('c','abcde'); | 3 |
POSITION(s1 IN s) | 字串位置,字符串s1在字符串s中的位置(开始位置) | SELECT POSITION('c' IN 'abcde'); | 3 |
LCASE(s) | 大小写转换,字母转为小写 | SELECT LCASE('aBc1'); | abc1 |
LOWER(s) | 大小写转换,字母转为小写 | SELECT LOWER('aBc1'); | abc1 |
UCASE(s) | 大小写转换,字母转为大写 | SELECT UCASE('aBc1'); | ABC1 |
UPPER(s) | 大小写转换,字母转为大写 | SELECT UPPER('aBc1'); | ABC1 |
REVERSE(s) | 字符串反转,返回与原始字符串顺序相反的字符串 | SELECT REVERSE('abc'); | cba |
STRCMP(s1,s2) | 字符串比较,比较字符串s1和s2 s1=s2返回0 s1>s2返回1 s1<s2 返回-1 | SELECT STRCMP('abc','ab'); | 1 |
SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串s的第number个出现的分隔符delimiter之后的子串。 number是正数,返回第number个字符左边的字符串。 number是负数,返回第(number的绝对值(从右边数))个字符右边的字符串。 | SELECT SUBSTRING_INDEX('a*b','*',1); SELECT SUBSTRING_INDEX('a*b','*',-1); | a b |
数值函数
函数 | 描述 | 示例 | 结果 |
---|---|---|---|
COUNT(expr) | 记录总条数,expr参数是一个字段、*、数值等 | SELECT COUNT(*) FROM cuser; | 6 |
SUM(expr) | 求和 | SELECT SUM(id) FROM cuser; | 21 |
AVG(expr) | 平均值 | SELECT AVG(id) FROM cuser; | 2.0 |
MAX(expr) | 最大值 | SELECT MAX(id) FROM cuser; | 6 |
MIN(expr) | 最小值 | SELECT MIN(id) FROM cuser; | 1 |
ABS(x) | 绝对值 | SELECT ABS(-1); | 1 |
x DIV y | 求商(整除),x为被除数,y为除数 | SELECT 11 DIV 5; | 2 |
MOD(x,y) | 求余,x除以y以后的余数 | SELECT MOD(11,5); | 1 |
EXP(x) | 次方,e的x次方 | SELECT EXP(2); | 7.38905609893065 |
POW(x,y) | 次方,x的y次方 | SELECT POW(2,3) | 8 |
POWER(x,y) | 次方,x的y次方 | SELECT POW(2,3) | 8 |
SQRT(x) | 平方根,x的平方根 | SELECT SQRT(4); | 2 |
LN(x) | 对数,x的自然对数,以e为底 | SELECT LN(2); | 0.6931471805599453 |
LOG(b,x) | 对数,x的自然对数,以为b底(没有b时,以e为底) | SELECT LOG(2,8); | 3 |
LOG10(x) | 对数,x的自然对数,以10为底 | SELECT LOG10(100); | 2 |
LOG2(x) | 对数,x的自然对数,以2为底 | SELECT LOG2(16); | 4 |
CEIL(x) | 向上取整,大于或等于x的最小整数 | SELECT CEIL(1.5); | 2 |
CEILING(x) | 向上取整,大于或等于x的最小整数 | SELECT CEILING(1.5); | 2 |
FLOOR(x) | 向下取整,小于或等于x的最大整数 | SELECT FLOOR(1.5); | 1 |
ROUND(x) | 四舍五入取整,离x最近的整数 | SELECT ROUND(1.5); | 2 |
TRUNCATE(x,y) | 数值x保留到小数点后y位的值(与ROUND最大的区别是不会进行四舍五入) | SELECT TRUNCATE(1.5,0); | 1 |
GREATEST(v1,v2,v3, …) | 列表中的最大值 | SELECT GREATEST(1,3,2); SELECT GREATEST('滚','哈哈','嗯'); | 3 滚 |
LEAST(v1,v2,v3, …) | 列表中的最小值 | SELECT LEAST(1,3,2); SELECT LEAST('滚','哈哈','嗯'); | 1 哈哈 |
RAND() | 随机数, 0到1的随机数 | SELECT RAND(); | 0.8001284439117913 |
SIGN(x) | 符号,x的符号,x是负数、0、正数分别返回-1、0和1 | SELECT SIGN(-5) | -1 |
PI() | 圆周率 | SELECT PI(); | 3.141593 |
DEGREES(x) | 弧度转换为角度 | SELECT DEGREES(PI()); | 180 |
RADIANS(x) | 角度转换为弧度 | SELECT RADIANS(180); | 3.141592653589793 |
SIN(x) | 正弦值(x为弧度) | SELECT SIN(RADIANS(90)); | 1 |
COS(x) | 余弦值(x为弧度) | SELECT COS(RADIANS(0)); | 1 |
TAN(x) | 正切值(x为弧度) | SELECT TAN(RADIANS(45)); | 0.9999999999999999 |
COT(x) | 余切值(x为弧度) | SELECT COT(RADIANS(45)); | 1.0000000000000002 |
ASIN(x) | 反正弦值(x为弧度) | SELECT ASIN(RADIANS(0)); | 0 |
ACOS(x) | 反余弦值(x为弧度) | SELECT ACOS(RADIANS(45)); | 0.6674572160283838 |
ATAN(x) | 反正切值(x为弧度) | SELECT ATAN(RADIANS(45)); | 0.6657737500283538 |
ATAN2(n, m) | 反正切值 | SELECT ATAN2(0,1); | 0 |
注意:
- 弧度与角度关系:
- 一周的弧度数为2π
- 2π弧度=360°角
- 1弧度约为57.3°,即57°17’44.806’’
- 1°为π/180弧度,近似值为0.01745弧度
- 周角为2π弧度,平角为π弧度,直角为π/2弧度。
日期函数
函数 | 描述 | 示例 | 结果 |
---|---|---|---|
ADDDATE(d,n) | 起始日期(或日期时间)d加n天的日期 | SELECT ADDDATE('2021-06-15',-10); | 2021-06-05 |
ADDDATE(d,INTERVAL n unit) | 起始日期(或日期时间)d加n个单位的日期(或日期时间) | SELECT ADDDATE('2021-06-15', INTERVAL -10 DAY); | 2021-06-05 |
DATE_ADD(d,INTERVAL n unit) | 起始日期(或日期时间)d加n个单位的日期(或日期时间) | SELECT DATE_ADD('2021-06-15', INTERVAL -10 DAY); | 2021-06-05 |
DATE_SUB(d,INTERVAL n unit) | 起始日期(或日期时间)d减n个单位的日期(或日期时间) | SELECT DATE_SUB('2021-06-15', INTERVAL 10 DAY); | 2021-06-05 |
ADDTIME(t,n) | 时间t加上时间表达式n(n为秒时不能超过59) | SELECT ADDTIME('2021-01-01 11:11:11', 59); SELECT ADDTIME('2021-01-01 11:11:11', '02:00:00'); | 2021-01-01 11:12:10 2021-01-01 13:11:11 |
CURDATE() | 当前日期 | SELECT CURDATE(); | 2022-02-10 |
CURRENT_DATE() | 当前日期 | SELECT CURRENT_DATE() | 2022-02-10 |
CURTIME() | 当前时间 | SELECT CURTIME(); | 19:59:02 |
CURRENT_TIME | 当前时间 | SELECT CURRENT_TIME(); | 19:59:02 |
LOCALTIME() | 当前日期和时间 | SELECT LOCALTIME(); | 2022-02-10 19:59:06 |
LOCALTIMESTAMP() | 当前日期和时间 | SELECT LOCALTIMESTAMP(); | 2022-02-10 19:59:06 |
CURRENT_TIMESTAMP() | 当前日期和时间 | SELECT CURRENT_TIMESTAMP(); | 2022-02-10 19:59:06 |
NOW() | 当前日期和时间 | SELECT NOW(); | 2022-02-10 19:59:06 |
SYSDATE() | 当前日期和时间 | SELECT SYSDATE(); | 2022-02-10 19:59:06 |
DATE(d) | 提取日期中的日期部分 | SELECT DATE('2022-01-02 00:00:01'); | 2022-01-02 |
TIME(expr) | 提取时间部分 | SELECT TIME('2022-01-02 01:22:33'); | 01:22:33 |
YEAR(d) | 提取日期中的年部分 | SELECT YEAR('2022-01-02 00:00:01'); | 2022 |
MONTH(d) | 提取日期中的月部分 | SELECT MONTH('2022-01-02 00:00:01'); | 1 |
DAY(d) | 提取日期中的日部分 | SELECT DAY('2022-01-02 00:00:01'); | 2 |
HOUR(d) | 提取日期中的小时部分 | SELECT HOUR('2022-01-02 00:00:01'); | 0 |
MINUTE(d) | 提取日期中的分钟部分 | SELECT MINUTE('2022-01-02 00:00:01'); | 0 |
SECOND(d) | 提取日期中的秒部分 | SELECT SECOND('2022-01-02 00:00:01'); | 1 |
MICROSECOND(d) | 提取日期中的微秒部分 | SELECT MICROSECOND('2022-01-02 00:00:01'); | 0 |
TIME_FORMAT(t,f) | 按表达式f的要求显示时间t | SELECT TIME_FORMAT('01:22:33','%h时%i分%s秒'); | 01时22分33秒 |
TIME_TO_SEC(t) | 将时间 t 转换为秒 | SELECT TIME_TO_SEC('00:00:01') | 1 |
QUARTER(d) | 日期d是第几季节,返回 1 到 4 | SELECT QUARTER('2022-01-02 00:00:01'); | 1 |
MONTHNAME(d) | 日期当中的月份名称,如:November | SELECT MONTHNAME('2022-01-02 00:00:01'); | January |
DAYNAME(d) | 日期d是星期几,如 Monday,Tuesday | SELECT DAYNAME('2022-11-11 23:11:11'); | Friday |
DAYOFMONTH(d) | 日期d是本月的第几天 | SELECT DAYOFMONTH('2011-11-11 11:11:11') | 11 |
LAST_DAY(d) | 日期对的那一月份的最后一天 | SELECT LAST_DAY("2017-06-20"); | 2017-06-30 |
DAYOFWEEK(d) | 日期d是星期几(索引位置),1星期日、2星期一,以此类推 | SELECT DAYOFWEEK('2011-11-11 11:11:11'); | 6 |
WEEKDAY(d) | 日期d是星期几(索引位置),0星期一、1星期二,以此类推 | SELECT WEEKDAY('2011-11-11 11:11:11'); | 4 |
WEEK(d) | 日期d是本年的第几个星期,范围是0到53 | SELECT WEEK('2011-11-11 11:11:11'); | 45 |
WEEKOFYEAR(d) | 日期d是本年的第几个星期,范围是0到53 | SELECT WEEKOFYEAR('2011-11-11 11:11:11'); | 45 |
YEARWEEK(date, mode) | 返回年份及第几周(0到53),mode 中 0 表示周天,1表示周一,以此类推 当mode没值时,默认mode=0,mode具体规则在下面有描述 | SELECT YEARWEEK('2018-01-01'); SELECT YEARWEEK('2018-01-01',1); | 201753 201801 |
DAYOFYEAR(d) | 日期d是本年的第几天 | SELECT DAYOFYEAR('2011-11-11 11:11:11') | 315 |
EXTRACT(unit FROM d) | 日期d中获取指定的值,unit指定返回的值。 | SELECT EXTRACT(MINUTE FROM '2022-2-11 12:05:26'); | 5 |
FROM_DAYS(n) | 计算从0000年1月1日开始n天后的日期 | SELECT FROM_DAYS(777777); | 2129-06-25 |
TO_DAYS(d) | 计算日期d距离0000年1月1日的天数 | SELECT TO_DAYS('2129-06-25 01:01:01'); | 777777 |
DATE_FORMAT(d,f) | 按表达式f的要求显示日期(或者时间、或者日期时间等)d | SELECT DATE_FORMAT('2022-1-1 13:1:1','%H:%i'); | 13:01 |
SEC_TO_TIME(s) | 将以秒为单位的时间 s 转换为时分秒的格式 | SELECT SEC_TO_TIME(23456); | 06:30:56 |
STR_TO_DATE(string, format_mask) | 字符串转变为日期,其中format_mask为string的格式 | SELECT STR_TO_DATE("2022/01/22", "%Y/%m/%d"); | 2022-01-22 |
PERIOD_ADD(period, number) | 为年-月组合日期添加一个时段(月份) | SELECT PERIOD_ADD(202203,-5); | 202110 |
PERIOD_DIFF(period1, period2) | 两个年-月组合之间的月份差值 | SELECT PERIOD_DIFF(202201,202203); | -2 |
SUBDATE(d,n) | 日期d减去n天后的日期 | SELECT SUBDATE('2022-02-01 11:22:33', 1); | 2022-01-31 11:22:33 |
SUBTIME(t,n) | 时间t减去n秒的时间(n小于59) | SELECT SUBTIME('2022-02-01 11:22:33',1); | 2022-02-01 11:22:32 |
MAKETIME(hour, minute, second) | 组合时间,参数分别为小时、分钟、秒 | SELECT MAKETIME(12, 35, 41); | 12:35:41 |
MAKEDATE(year, day-of-year) | 基于给定参数年份year和所在年中的天数序号day-of-year返回一个日期 | SELECT MAKEDATE(2022, 33); | 2022-02-02 |
TIMESTAMP(expr) | 日期或日期时间格式转为日期时间 | SELECT TIMESTAMP('2022-02-01 00:01'); | 2022-02-01 00:01:00 |
TIMESTAMP(expr1,expr2) | 两个日期或日期时间格式时间相加后的日期时间 | SELECT TIMESTAMP('2022-02-01 00:00:01','01:01'); | 2022-02-01 01:01:01 |
DATEDIFF(d1,d2) | 计算日期d1、d2 之间相隔的天数,d1-d2 | SELECT DATEDIFF('2022-01-02 0:0:1','2022-02-05 0'); | -34 |
TIMEDIFF(time1, time2) | 计算时间差,time1-time2,结果为时间格式。 值的范围:-838:59:59到838:59:59 | SELECT TIMEDIFF('2022-01-05 0:2:1','2022-01-02 0:0:1'); | 72:02:00 |
TIMESTAMPDIFF(unit,expr1,expr2) | 计算时间差,返回 expr2 − expr1 的时间差 | SELECT TIMESTAMPDIFF(DAY,'2022-02-01','2022-05-01'); | 89 |
注意:
-
unit的取值
值 含义 MICROSECOND 微秒 SECOND 秒 MINUTE 分 HOUR 时 DAY 日 WEEK 星期 MONTH 月 QUARTER 季 YEAR 年 SECOND_MICROSECOND 秒微秒 MINUTE_MICROSECOND 分微秒 MINUTE_SECOND 分秒 HOUR_MICROSECOND 时微秒 HOUR_SECOND 时秒 HOUR_MINUTE 时分 DAY_MICROSECOND 日微秒 DAY_SECOND 日秒 DAY_MINUTE 日分 DAY_HOUR 日时 YEAR_MONTH 年月 -
日期格式
格式 描述 %a 缩写星期名 %b 缩写月名 %c 月,数值 %D 带有英文前缀的月中的天 %d 月的天,数值(00-31) %e 月的天,数值(0-31) %f 微秒 %H 小时 (00-23) %h 小时 (01-12) %I 小时 (01-12) %i 分钟,数值(00-59) %j 年的天 (001-366) %k 小时 (0-23) %l 小时 (1-12) %M 月名 %m 月,数值(00-12) %p AM 或 PM %r 时间,12-小时(hh:mm:ss AM 或 PM) %S 秒(00-59) %s 秒(00-59) %T 时间, 24-小时 (hh:mm:ss) %U 周 (00-53) 星期日是一周的第一天 %u 周 (00-53) 星期一是一周的第一天 %V 周 (01-53) 星期日是一周的第一天,与 %X 使用 %v 周 (01-53) 星期一是一周的第一天,与 %x 使用 %W 星期名 %w 周的天 (0=星期日, 6=星期六) %X 年,其中的星期日是周的第一天,4 位,与 %V 使用 %x 年,其中的星期一是周的第一天,4 位,与 %v 使用 %Y 年,4 位 %y 年,2 位 -
mode参数含义
mode值 每周的第一天 周数范围 判断某周是否属于当年的周 0 周日 0-53 该周的周日必须属于今年 1 周一 0-53 该周必须有3天以上属于今年 2 周日 1-53 该周的周日必须属于今年 3 周一 1-53 该周必须有3天以上属于今年 4 周日 0-53 该周必须有3天以上属于今年 5 周一 0-53 该周的周一必须属于今年 6 周日 1-53 该周必须有3天以上属于今年 7 周一 1-53 该周的周一必须属于今年 -
mysql time类型范围:-838:59:59到838:59:59
其他函数
函数 | 描述 | 示例 | 结果 |
---|---|---|---|
ASCII(s) | 第一个字符的ASCII码 | SELECT ASCII('abc'); SELECT ASCII(12); | 97 49 |
LENGTH(s) | 字节数 | SELECT LENGTH('哈哈'); SELECT LENGTH(12); | 6 2 |
CHAR_LENGTH(s) | 字符数 | SELECT CHAR_LENGTH('哈哈'); SELECT CHAR_LENGTH(12); | 2 2 |
CHARACTER_LENGTH(s) | 字符数 | SELECT CHARACTER_LENGTH('哈哈'); SELECT CHARACTER_LENGTH(12); | 2 2 |
BIN(x) | x的二进制编码 | SELECT BIN(15); | 11 |
BINARY s | 将字符串s转换为二进制字符串 | SELECT birthday FROM cuser WHERE birthday LIKE BINARY CONCAT('%','1995-08','%'); | 1995-08-01 1995-08-02 1995-08-01 |
CAST(x AS type) | 转换数据类型 | SELECT CAST('2022-01-01 00:12:23' AS time); | 00:12:23 |
COALESCE(expr1, expr2,…) | 参数列表中的第一个非空表达式(从左向右) | SELECT COALESCE(NULL,'aa',NULL,'bb'); | aa |
CONNECTION_ID() | 唯一的连接ID | SELECT CONNECTION_ID(); | 5435 |
CONV(x,f1,f2) | x从f1进制转换为f2进制 | SELECT CONV(20,10,2); | 10100 |
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result END | CASE 表示函数开始,END 表示函数结束。 如果 condition1 成立,则返回 result1, 如果 condition2 成立,则返回 result2,当全部不成立则返回 result,而当有一个成立之后,后面的就不执行了。 | SELECT CASE CEIL(RAND()*10) WHEN 5 THEN ‘=5’ ELSE ‘!=5’ END; | !=5 |
FOMRAT(X,D[,locale]) | 格式化,将X进行格式化 “#,###.##”。 D:保留的小数位数; locale:一个可选参数,用于确定千个分隔符和分隔符之间的分组。 | SELECT FORMAT(10003.1425926,2); | 10,003.14 |
CONVERT(s USING cs) | 函数将字符串s的字符集变成cs | SELECT CONVERT('ABC' USING gbk); | ABC |
IF(expr,v1,v2) | 如果表达式expr成立,返回结果v1;否则,返回结果v2。 | SELECT IF(1 > 0,'正确','错误'); | 正确 |
IFNULL(v1,v2) | 如果v1的值不为NULL,则返回v1,否则返回v2。 | SELECT IFNULL(null,'Hello Word'); | Hello Word |
ISNULL(expression) | 判断表达式是否为NULL | SELECT ISNULL(NULL); | 1 |
LAST_INSERT_ID() | 最近生成的 AUTO_INCREMENT 值 | SELECT LAST_INSERT_ID(); | 0 |
NULLIF(expr1, expr2) | 比较两个字符串,如果字符串expr1与expr2相等返回NULL,否则返回expr1 | SELECT NULLIF(24,25); | 24 |
CURRENT_USER() | 当前用户 | SELECT CURRENT_USER(); | root@% |
SESSION_USER() | 当前用户 | SELECT SESSION_USER(); | root@127.0.0.1 |
SYSTEM_USER() | 当前用户 | SELECT SYSTEM_USER(); | root@127.0.0.1 |
USER() | 当前用户 | SELECT USER(); | root@127.0.0.1 |
DATABASE() | 当前数据库名 | SELECT DATABASE(); | demo |
VERSION() | 数据库的版本号 | SELECT VERSION(); | 5.7.34 |