标签:返回 函数 date 日期 str MySQL 字符串 部分
单行函数
数值函数
基本函数
函数 | 用法 |
ABS(x) |
返回x的绝对值 |
SIGN(X) |
返回X的符号。正数返回1,负数返回-1,0返回0 |
PI() |
返回圆周率的值 |
CEIL(x),CEILING(x) |
返回大于或等于某个值的最小整数 |
FLOOR(x) |
返回小于或等于某个值的最大整数 |
LEAST(e1,e2,e3…) |
返回列表中的最小值 |
GREATEST(e1,e2,e3…) |
返回列表中的最大值 |
MOD(x,y) |
返回X除以Y后的余数 |
RAND() |
返回0~1的随机值 |
RAND(x) |
返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机数 |
ROUND(x) |
返回一个对x的值进行四舍五入后,最接近于X的整数 |
ROUND(x,y) |
返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 |
TRUNCATE(x,y) |
返回数字x截断为y位小数的结果 |
SQRT(x) |
返回x的平方根。当X的值为负数时,返回NULL |
角度与弧度互换函数
函数 | 用法 |
RADIANS(x) |
将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) |
将弧度转化为角度,其中,参数x为弧度值 |
三角函数
函数 | 用法 |
SIN(x) |
返回x的正弦值,其中,参数x为弧度值 |
ASIN(x) |
返回x的反正弦值,即获取正弦为x的值。如果x的值不在-1到1之间,则返回NULL |
COS(x) |
返回x的余弦值,其中,参数x为弧度值 |
ACOS(x) |
返回x的反余弦值,即获取余弦为x的值。如果x的值不在-1到1之间,则返回NULL |
TAN(x) |
返回x的正切值,其中,参数x为弧度值 |
ATAN(x) |
返回x的反正切值,即返回正切值为x的值 |
ATAN2(m,n) |
返回两个参数的反正切值 |
COT(x) |
返回x的余切值,其中,X为弧度值 |
指数与对数函数
函数 | 用法 |
POW(x,y),POWER(X,Y) |
返回x的y次方 |
EXP(X) |
返回e的X次方,其中e是一个常数,2.718281828459045 |
LN(X),LOG(X) |
返回以e为底的X的对数,当X <= 0 时,返回的结果为NULL |
LOG10(X) |
返回以10为底的X的对数,当X <= 0 时,返回的结果为NULL |
LOG2(X) |
返回以2为底的X的对数,当X <= 0 时,返回NULL |
进制间转换函数
函数 | 用法 |
BIN(x) |
返回x的二进制编码 |
HEX(x) |
返回x的十六进制编码 |
OCT(x) |
返回x的八进制编码 |
CONV(x,f1,f2) |
返回f1进制数变成f2进制数 |
字符串函数
函数 | 用法 |
ASCII(S) |
返回字符串S中的第一个字符的ASCII码值 |
CHAR_LENGTH(s) |
返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
LENGTH(s) |
返回字符串s的字节数,和字符集有关 |
CONCAT(s1,s2,…,sn) |
连接s1,s2,…,sn为一个字符串 |
CONCAT_WS(x,s1,s2,…,sn) |
同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上x |
INSERT(str, idx, len,replacestr |
将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr |
REPLACE(str, a, b) |
用字符串b替换字符串str中所有出现的字符串a |
UPPER(s) 或 UCASE(s) |
将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) |
将字符串s的所有字母转成小写字母 |
LEFT(str,n) |
返回字符串str最左边的n个字符 |
RIGHT(str,n) |
返回字符串str最右边的n个字符 |
LPAD(str, len, pad) |
用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) |
用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) |
去掉字符串s左侧的空格 |
RTRIM(s) |
去掉字符串s右侧的空格 |
TRIM(s) |
去掉字符串s开始与结尾的空格 |
TRIM(s1 FROM s) |
去掉字符串s开始与结尾的s1 |
TRIM(LEADING s1FROM s) |
去掉字符串s开始处的s1 |
TRIM(TRAILING s1FROM s) |
去掉字符串s结尾处的s1 |
REPEAT(str, n) |
返回str重复n次的结果 |
SPACE(n) |
返回n个空格 |
STRCMP(s1,s2) |
比较字符串s1,s2的ASCII码值的大小 |
SUBSTR(s,index,len) |
返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、MID(s,n,len)相同 |
LOCATE(substr,str) |
返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substrIN str)、INSTR(str,substr)相同。未找到,返回0 |
ELT(m,s1,s2,…,sn) |
返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如果m=n,则返回sn |
FIELD(s,s1,s2,…,sn) |
返回字符串s在字符串列表中第一次出现的位置 |
FIND_IN_SET(s1,s2) |
返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一个以逗号分隔的字符串 |
REVERSE(s) |
返回s反转后的字符串 |
NULLIF(value1,value2) |
比较两个字符串,如果value1与value2相等,则返回NULL,否则返回value1 |
注意:MySQL中,字符串的位置是从1开始的。
时间日期函数
获取日期、时间
函数 | 用法 |
CURDATE() ,CURRENT_DATE() |
返回当前日期,只包含年、月、日 |
CURTIME() , CURRENT_TIME() |
返回当前时间,只包含时、分、秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP() |
返回当前系统日期和时间 |
UTC_DATE() |
返回UTC(世界标准时间)日期 |
UTC_TIME() |
返回UTC(世界标准时间)时间 |
日期与时间戳转换
函数 | 用法 |
UNIX_TIMESTAMP() |
以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884 |
UNIX_TIMESTAMP(date) |
将时间date以UNIX时间戳的形式返回。 |
FROM_UNIXTIME(timestamp) |
将UNIX时间戳的时间转换为普通格式的时间 |
获取月份、星期、星期数、天数等函数
函数 | 用法 |
YEAR(date) / MONTH(date) / DAY(date) |
返回具体的日期值 |
HOUR(time) / MINUTE(time) /SECOND(time) |
返回具体的时间值 |
MONTHNAME(date) |
返回月份:January,… |
DAYNAME(date) |
返回星期几:MONDAY,TUESDAY…SUNDAY |
WEEKDAY(date) |
返回周几,注意,周1是0,周2是1,。。。周日是6 |
QUARTER(date) |
返回日期对应的季度,范围为1~4 |
WEEK(date) , WEEKOFYEAR(date) |
返回一年中的第几周 |
DAYOFYEAR(date) |
返回日期是一年中的第几天 |
DAYOFMONTH(date) |
返回日期位于所在月份的第几天 |
DAYOFWEEK(date) |
返回周几,注意:周日是1,周一是2,。。。周六是7 |
日期操作函数
函数 | 用法 |
EXTRACT(type FROM date) |
返回指定日期中特定的部分,type指定返回的值 |
EXTRACT(type FROM date)函数中type的取值与含义:
type函数 | 含义 |
MICROSECOND |
type取值 |
SECOND |
返回秒数 |
MINUTE |
返回分钟数 |
HOUR |
返回小时数 |
DAY |
返回天数 |
WEEK |
返回日期在一年中的第几个星期 |
MONTH |
返回日期在一年中的第几个月 |
QUARTER |
返回日期在一年中的第几个季度 |
SECOND MICROSECOND |
返回日期的年份 |
SECOND_MICROSECOND |
返回秒和毫秒值 |
MINUTE_MICROSECOND |
返回分钟和毫秒值 |
MINUTE_SECOND |
返回分钟和秒值 |
HOUR_MICROSECOND |
返回小时和毫秒值 |
HOUR SECOND |
返回小时和秒值 |
HOUR_MINUTE |
返回小时和分钟值 |
DAY_MICROSECOND |
返回天和毫秒值 |
DAY SECOND |
返回天和秒值 |
DAY_MINUTE |
返回天和分钟值 |
DAY HOUR |
返回天和小时 |
YEAR_MONTH |
返回年和月 |
时间和秒钟转换函数
函数 | 用法 |
TIME_TO_SEC(time) |
将 time 转化为秒并返回结果值。转化的公式为: 小时3600+分钟60+秒 |
SEC_TO_TIME(seconds) |
将 seconds 描述转化为包含小时、分钟和秒的时间 |
计算日期和时间函数
函数 | 用法 |
DATE_ADD(datetime, INTERVAL expr type),ADDDATE(date,INTERVAL expr type) |
返回与给定日期时间相差INTERVAL时间段的日期时间 |
DATE_SUB(date,INTERVAL expr type),SUBDATE(date,INTERVAL expr type) |
返回与date相差INTERVAL时间间隔的日期 |
间隔类型 | 含义 |
HOUR |
小时 |
MINUTE |
分钟 |
SECOND |
秒 |
YEAR |
年 |
MONTH |
月 |
DAY |
日 |
YEAR_MONTH |
年和月 |
DAY_HOUR |
日和小时 |
DAY_MINUTE |
日和分钟 |
DAY_SECOND |
日和秒 |
HOUR_MINUTE |
小时和分钟 |
HOUR_SECOND |
小时和秒 |
MINUTE_SECOND |
分钟和秒 |
函数 | 用法 |
ADDTIME(time1,time2) |
返回time1加上time2的时间。当time2为一个数字时,代表的是秒 ,可以为负数 |
SUBTIME(time1,time2) |
返回time1减去time2后的时间。当time2为一个数字时,代表的是 秒 ,可以为负数 |
DATEDIFF(date1,date2) |
返回date1 - date2的日期间隔天数 |
TIMEDIFF(time1, time2) |
返回time1 - time2的时间间隔 |
FROM_DAYS(N) |
返回从0000年1月1日起,N天以后的日期 |
TO_DAYS(date) |
返回日期date距离0000年1月1日的天数 |
LAST_DAY(date) |
返回date所在月份的最后一天的日期 |
MAKEDATE(year,n) |
针对给定年份与所在年份中的天数返回一个日期 |
MAKETIME(hour,minute,second) |
将给定的小时、分钟和秒组合成时间并返回 |
PERIOD_ADD(time,n) |
返回time加上n后的时间 |
日期的格式化与解析
函数 | 用法 |
DATE_FORMAT(date,fmt) |
按照字符串fmt格式化日期date值 |
TIME_FORMAT(time,fmt) |
按照字符串fmt格式化时间time值 |
GET_FORMAT(date_type,format_type) |
返回日期字符串的显示格式 |
STR_TO_DATE(str, fmt) |
按照字符串fmt对str进行解析,解析为一个日期 |
上述非GET_FORMAT 函数中fmt参数常用的格式符:
格式符 | 说明 | 格式符 | 说明 |
%Y |
4位数字表示年份 |
%y |
表示两位数字表示年份 |
%M |
月名表示月份(January,…) |
%m |
两位数字表示月份(01,02,03。。。) |
%b |
缩写的月名(Jan.,Feb.,…) |
%c |
数字表示月份(1,2,3,…) |
%D |
英文后缀表示月中的天数(1st,2nd,3rd,…) |
%d |
两位数字表示月中的天数(01,02…) |
%e |
数字形式表示月中的天数(1,2,3,4,5…) |
|
|
%H |
两位数字表示小数,24小时制(01,02…) |
%h和%I |
两位数字表示小时,12小时制(01,02…) |
%k |
数字形式的小时,24小时制(1,2,3) |
%l |
数字形式表示小时,12小时制(1,2,3,4…) |
%i |
两位数字表示分钟(00,01,02) |
%S和%s |
两位数字表示秒(00,01,02…) |
%W |
一周中的星期名称(Sunday…) |
%a |
一周中的星期缩写(Sun.,Mon.,Tues.,…) |
%w |
以数字表示周中的天数(0=Sunday,1=Monday…) |
|
|
%j |
以3位数字表示年中的天数(001,002…) |
%U |
以数字表示年中的第几周,(1,2,3。。)其中Sunday为周中第一天 |
%u |
以数字表示年中的第几周,(1,2,3。。)其中Monday为周中第一天 |
|
|
%T |
24小时制 |
%r |
12小时制 |
%p |
AM或PM |
%% |
表示% |
GET_FORMAT函数中date_type和format_type参数取值如下:
日期类型 | 格式化类型 | 返回的格式化字符串 |
DATE |
USA |
%m.%d.%Y |
DATE |
JIS |
%Y-%m-%d |
DATE |
ISo |
%Y-%m-%d |
DATE |
EUR |
%d.%m.%Y |
DATE |
INTERNAL |
%Y%m%d |
TIME |
USA |
%h:%i:%s %p |
TIME |
JIS |
%H:%i:%s |
TIME |
ISo |
%H:%i:%s |
TIME |
EUR |
%H.%i.%s |
TIME |
INTERNAL |
%H%i%s |
DATETIME |
USA |
%Y-%m-%d %H.%i.%s |
DATETIME |
JS |
%Y-%m-%d %H:%i:%s |
DATETIME |
ISo |
%Y-%m-%d %H:%i:%s |
DATETIME |
EUR |
%Y-%m-%d %H.%i.%s |
DATETIME |
INTERNAL |
%Y%m%d%H%i%s |
流程控制函数
函数 | 用法 |
IF(value,value1,value2) |
如果value的值为TRUE,返回value1,否则返回value2 |
IFNULL(value1, value2) |
如果value1不为NULL,返回value1,否则返回value2 |
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END |
相当于Java的if…else if…else… |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 … [ELSE 值n] END |
相当于Java的switch…case… |
加密与解密函数
函数 | 用法 |
PASSWORD(str) |
返回字符串str的加密版本,41位长的字符串。加密结果 不可逆 ,常用于用户的密码加密 |
MD5(str) |
返回字符串str的md5加密后的值,也是一种加密方式。若参数为NULL,则会返回NULL |
SHA(str) |
从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。 SHA加密算法比MD5更加安全 。 |
ENCODE(value,password_seed) |
返回使用password_seed作为加密密码加密value |
DECODE(value,password_seed) |
返回使用password_seed作为加密密码解密value |
MySQL信息函数
函数 | 用法 |
VERSION() |
返回当前MySQL的版本号 |
CONNECTION_ID() |
返回当前MySQL服务器的连接数 |
DATABASE(),SCHEMA() |
返回MySQL命令行当前所在的数据库 |
USER(),CURRENT_USER()、SYSTEM_USER(),SESSION_USER() |
返回当前连接MySQL的用户名,返回结果格式为“主机名@用户名” |
CHARSET(value) |
返回字符串value自变量的字符集 |
COLLATION(value) |
返回字符串value的比较规则 |
其他函数
函数 | 用法 |
FORMAT(value,n) |
返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位 |
CONV(value,from,to) |
将value的值进行不同进制之间的转换 |
INET_ATON(ipvalue) |
将以点分隔的IP地址转化为一个数字 |
INET_NTOA(value) |
将数字形式的IP地址转化为以点分隔的IP地址 |
BENCHMARK(n,expr) |
将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 |
CONVERT(value USINGchar_code) |
将value所使用的字符编码修改为char_code |
聚合函数
聚合函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
- 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。
- AVG和SUM函数可以对数值型数据使用AVG 和 SUM 函数。
- MIN和MAX函数可以对任意数据类型的数据使用 MIN 和 MAX 函数。
- COUNT函数COUNT(*)返回表中记录总数,适用于任意数据类型。COUNT(expr) 返回expr不为空的记录总数。
- 问题:用count(),count(1),count(列名)谁好呢?
其实,对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb引擎的表用count(),count(1)直接读行数,复杂度是O(n),因为innodb真的要去数一遍。但好于具体的count(列名)。
- 问题:能不能使用count(列名)替换count()?
不要使用 count(列名)来替代 count() , count() 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。
说明:count()会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。
GROUP BY
- 可以使用GROUP BY子句将表中的数据分成若干组
- 在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中
- GROUP BY中使用WITH ROLLUP。使用 WITH ROLLUP 关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。
注意:当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的。
HAVING
- 过滤分组:HAVING子句
- 行已经被分组。
- 使用了聚合函数。
- 满足HAVING 子句中条件的分组将被显示。
- HAVING 不能单独使用,必须要跟 GROUP BY 一起使用。
- 非法使用聚合函数 : 不能在 WHERE 子句中使用聚合函数。
WHERE和HAVING的对比
区别1:WHERE 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING 必须要与 GROUP BY 配合使用,可以把分组计算的函数和分组字段作为筛选条件。
这决定了,在需要对数据进行分组统计的时候,HAVING 可以完成 WHERE 不能完成的任务。这是因为,在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选,这个功能是 WHERE 无法完成的。另外,WHERE排除的记录不再包括在分组中。
区别2:如果需要通过连接从关联表中获取需要的数据,WHERE 是先筛选后连接,而 HAVING 是先连接后筛选。
这一点,就决定了在关联查询中,WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选,用一个筛选后的较小数据集和关联表进行连接,这样占用的资源比较少,执行效率也比较高。HAVING 则需要先把结果集准备好,也就是用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用的资源就比较多,执行效率也较低。
小结:
cc | 优点 | 缺点 |
WHERE |
先筛选数据再关联,执行效率高 |
不能使用分组中的计算函数进行筛选 |
HAVING |
可以使用分组中的计算函数 |
在最后的结果集中进行筛选,执行效率较低 |
开发中的选择:
WHERE 和 HAVING 也不是互相排斥的,我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING,普通条件用 WHERE。这样,我们就既利用了 WHERE 条件的高效快速,又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候,运行效率会有很大的差别。
标签:返回,
函数,
date,
日期,
str,
MySQL,
字符串,
部分
From: https://www.cnblogs.com/ZDY-XJ/p/17874860.html