MySQL常用函数

一、MySQL常用函数

  一）操作符

　　1、操作符优先级

　　 操作符的执行优先级，从高到低，同一行中的操作符优先级相同，相同优先级的情况下则从左到右执行

  • INTERVAL
  • BINARY、COLLATE
  • !
  • -(负号）、〜（位反转）
  • ^
  • *、/、DIV、%、MOD
  • -、+
  • <<、>>
  • &
  • |
  • =(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
  • BETWEEN、CASE、WHEN、THEN、ELSE
  • NOT
  • AND、&&
  • XOR
  • OR、||
  • =（赋值）、:=

　　2、对比操作符

  • 对比操作符的执行结果为true，false，null三种
  • between A and B代表检查值是否在A与B之间
  • coalesce()代表返回第一个非Null的值
  • =代表相等操作符
  • >代表大于操作符
  • >=代表大于等于操作符
  • greatest() 代表返回最大的值
  • In()代表检查值是否在一系列的值之中
  • Interval()代表返回比第一个参数小的参数的位置
  • is/is not代表检查值是否与布尔值相同/不同
  • Is not null代表检查值是否是非NULL
  • Is null代表检查值是否是NULL
  • Isnull()代表检查参数是NULL
  • Least()代表返回最小的参数
  • <代表小于操作符
  • <=代表小于等于操作符
  • Like代表字符匹配
  • Not between A and B代表检查值是否不在A和B的范围之内
  • !=/<>代表不等于操作符
  • Not in()代表检查值是否不在一系列值的当中
  • Not like代表检查值是否不匹配
  • Strcmp()对比两个字符串

　　3、逻辑操作符

  • NOT、！代表非操作
  • AND、&& 与
  • OR、||  或
  • XOR

　　4、分配操作符

　　赋值操作

  • =
  • :=

  二）流程控制函数

  • CASE 当第一个condition满足时，则返回result，否则返回else里的result，如果没有else子句时则返回nul 
topics:
   CASE OPERATOR
   CASE STATEMENT
  • IF(expr1,expr2,expr3)   当expr1为1/true时，则返回expr2，否则返回expr3 
topics:
   CREATE DATABASE
   CREATE EVENT
   DROP DATABASE
   DROP EVENT
   DROP SERVER
   DROP TABLE
   DROP VIEW
   IF FUNCTION
   IF STATEMENT
  • IFNULL 当expr1为非null时，则返回expr1，否则返回expr2 
Syntax:
IFNULL(expr1,expr2)

Examples:
mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNU
  • NULLIF 当expr1等于expr2时，则返回null，否则返回expr1 
Syntax:
NULLIF(expr1,expr2)


Examples:
mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1

  三）字符串函数

  • CHAR_LENGTH(str)  返回字符串的字符长度 
  • CONCAT(str1,str2,...)  返回括号里所有参数字符串连接在一起，当其中有参数为NULL时则返回 NULL
  • CONCAT_WS(separator,str1,str2,...)  返回以第一个参数为分隔符的连接后的一个字符串，当有参数为NULL时则NULL被忽略
  • NSERT(str,pos,len,newstr)  将str中从pos位置开始后的len个字符替换成newstr字符串
  • INSTR(str,substr)  返回str字符串中第一个出现substr字符串的位置
  • LEFT(str,len)  返回str字符串中从左边开始的len个长度的字符
  • LENGTH(str)   返回str字符串的byte字节长度
  • LOAD_FILE(file_name)  读取文件且返回文件内容为字符串
  • LOCATE(substr,str)  返回str字符串中第一次出现substr字符串的位置，如果没有则返回null 
  • LOCATE(substr,str,pos)  返回str字符串中从pos位置开始第一次出现substr字符串的位置，如果没有则返 null
  • LOWER(str)  返回将str字符串中所有字符变换成小写后的字符串，但对二进制文本无效
  • LPAD(str,len,padstr)  将str的左边补充为padstr，直到补充成len长度的字符串并返回；如果str的长度比len长，则返回str中最左边开始的len长度的字符
  • LTRIM(str)  将str最左边的空格去掉并返回
  • REPEAT(str,count)  将str重复count并组合成字符串返回，如果count<1，则返回空串
  • REPLACE(str,from_str,to_str)  将所有str字符串中匹配from_str子串的地方都替换成to_str子字符串
  • REVERSE(str)  将str字符串中的字符按照倒序组合并返回
  • RIGHT(str,len)  将str字符串中从右边开始的len个字符返回
  • RPAD(str,len,padstr)  将字符串str从右边开始补充为padstr直到整体长度为len，如果str的长度本身大于len，则返回str中len长度的字符串
  • RTRIM(str)  将字符串str右边的空格去掉并返回
  • SPACE(N)  返回N个长度的空格组成的空字符串
  • UPPER(str)  返回将str字符串中所有字符转换成大写的字符串

　　1、字符串对比函数

　　通配符%表示匹配0个或多个字符

　　通配符_表示匹配1个字符

　　当匹配字符中有特殊字符时，可以用 \或者escape来指定特殊字符为字符

  • like
  • not like
  • STRCMP(expr1,expr2)  当exp1等于expr2时等于0，当expr1小于expr2时为-1，反之为1

  四）数字函数

  • ABS(X)  绝对值函数
  • CEILING(X)/CEIL(X)   返回>=X值的最小整数
  • FLOOR(X)   返回<=X值的最大整数
  • MOD(N,M), N % M, N MOD M   取余数操作，返回两者相除之后剩下的余数
  • RAND([N])   获取0到1之间的随机小数，比如当想获取7~12之间的随机整数是可以使用
  • ROUND(X), ROUND(X,D)   四舍五入为D位个小数，当D参数不存在是，则默认为0
  • TRUNCATE(X,D)   数字X只保留D位的小数，其余均舍弃 

  五）日期和时间函数

  • ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)  
    • 当expr为正数时则为增加时间，为负数时则为减少时间
    • Unit参数可以是任意时间单位 
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
SELECT ADDDATE('2008-01-02', 31);
  • ADDTIME(expr1,expr2)   将expr2的时间增加到expr1上 
> select ADDTIME('01:00:00','10:22:10');
+--------------------------------+
| ADDTIME('01:00:00','10:22:10') |
+--------------------------------+
| 11:22:10                       |
+--------------------------------+
1 row in set (0.02 sec)

> select ADDTIME('2020-10-30 01:00:00','10:22:10');
+-------------------------------------------+
| ADDTIME('2020-10-30 01:00:00','10:22:10') |
+-------------------------------------------+
| 2020-10-30 11:22:10 |
+-------------------------------------------+
1 row in set (0.00 sec)
  • CONVERT_TZ(dt,from_tz,to_tz)  将时间dt从from_tz这个时区转换成to_tz这个时区并返回 
SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
  • CURDATE()， CURRENT_DATE, CURRENT_DATE()   返回以yyyy-mm-dd或者yyyymmdd格式的当前时间
  • CURTIME(),CURRENT_TIME,CURRENT_TIME()   按照hh:mm:ss或者hhmmss格式返回当前时间
  • Now(), CURRENT_TIMESTAMP,CURRENT_TIMESTAMP()   返回当前的日期和时间，格式为yyyy-mm-dd hh:mi:ss或者 yyyymmddhhmiss
  • DATE(expr)  获取expr中的日期
  • DATEDIFF(expr1,expr2)   返回expr1和expr2之间的天数差异，忽略时分秒
  • DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)    增加或者减少时间
  • DATE_FORMAT(date,format)  将date日期时间转换成format格式 （和Linux date格式一致）
  • DAY(date), DAYOFMONTH(date)   返回date中日期在当前月份中是第几天
  • DAYNAME(date)   返回date时间是星期几 
  • DAYOFWEEK(date)   返回date时间是星期“几”
  • DAYOFYEAR(date)   返回date是一年中的第几天，取值范围在1~366
  • EXTRACT(unit FROM date)   Unit单元和date_add/date_sub函数中的一样，是获取date日期的unit部分
    • unit ：YEAR 、YEAR_MONTH 、DAY_MINUTE 、MICROSECOND
  • LAST_DAY(date)   返回date日期所在月份的最后一天日期
  • SYSDATE()   返回当前日期和时间，格式为yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss
  • TIME(expr)   返回expr日期时间中的时间部分

  六）格式转换函数

　　Cast()和convert()两个函数都可以用来转换数据类型或者转换字符集

　　1、允许转换的数据类型包括

  • Binary[N]
  • char[N]
  • Date
  • Datetime
  • decimal[M,[D]]
  • Time
  • Signed [integer]
  • Unsigned [integer]

  七）聚合函数

　　用在存在group by子句的语句中

  • AVG([DISTINCT] expr)   计算expr的平均值， distinct关键词表示是否排除重复值
  • COUNT(expr)   计算expr中的个数，如果没有匹配则返回0，注意NULL的区别
  • COUNT(DISTINCT expr,[expr...])   计算有多少个不重复的expr值，注意是计算非NULL的个数 
  • MAX([DISTINCT] expr),MIN([DISTINCT] expr)   返回expr中最大或者最小的值
  • SUM([DISTINCT] expr)   返回expr的求和值

  八）子查询

　　子查询是一个嵌套在外层语句中的完整select语句，通常用（）括起来，子查询可以范围一个单一的值，一行值，一个表格等等

　　通常子查询的方式可以简化复杂join表链接查询和union结果合并查询，提高了语句的可读性

SELECT (SELECT s1 FROM t2) FROM t1;
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);
delete from t1 where column1 in (select column1 from t2);
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT column1,column2,column3 FROM t1 WHERE(column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2);
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 WHERE t2.column2 = t1.column2);
SELECT AVG(sum_column1) FROM (SELECT SUM(column1) ASsum_column1 FROM t1 GROUP BY column1) AS t1;
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

 

posted @ 2020-10-31 21:43  思维无界限  阅读(179)  评论(0)  编辑  收藏  举报