SQL強大的一個原因就是函數豐富,MYSQL為例,函數有以下幾種:
數學函數
字元串函數
日期和時間函數
條件判斷函數
系統資訊函數
加密函數
格式化函數
同時,MYSQL也支援自定義函數,這篇文章分為兩個部分,限于篇幅,第一個部分介紹常用函數,第二部分講解如何自定義函數。
原生函數
1. 數學函數:
ABS(x),絕對值
CEIL(x),FLOOR(x),大于等于的整數,小于等于的整數
SIGN(x),傳回x的符号,x是負數、0、正數分别傳回-1、0和1
TRUNCATE(x,y),傳回數值x保留到小數點後y位的值
ROUND(x,y),保留x小數點後y位的值,但截斷時要進行四舍五入
POW(x,y),SQRT(x),EXP(x) 指數相關
MOD(x,y),傳回x除以y以後的餘數
LOG(x),LOG10(x),對數
2. 字元串函數
CHAR_LENGTH(s),傳回字元串s的字元數
CONCAT(s1,s2,...) ,将字元串s1,s2等多個字元串合并為一個字元串
CONCAT_WS(x,s1,s2,...),同上,每個字元串直接要加上x
UPPER(s),LOWER(s),大小寫
LEFT(s,n),RIGHT(s,n),傳回字元串s的前,後n個字元
LTRIM(s),RTRIM(s) ,TRIM(s) 去掉字元串s開始,結尾處的空格
STRCMP(s1,s2) 比較字元串s1和s2
SUBSTRING(s,n,len) 擷取s中第n個位置開始長度為len的字元串
3. 日期和時間函數
CURDATE(),CURTIME(),NOW() 傳回日期,時間2017-10-12 10:12:22
UNIX_TIMESTAMP(),UNIX_TIMESTAMP(d),FROM_UNIXTIME(d)
UTC_DATE() ,UTC_TIME() UTC日期時間
MONTH(d), 傳回日期d中的月份值,1->12
MONTHNAME(d),傳回日期當中的月份名稱,如Janyary
DAYNAME(d) ,傳回日期d是星期幾,如Monday,Tuesday
WEEK(d),計算日期d是本年的第幾個星期,範圍是0->53
DAYOFYEAR(d) ,計算日期d是本年的第幾天
DAYOFMONTH(d) ,計算日期d是本月的第幾天
HOUR(t),MINUTE(t),SECOND(t) 取出時分秒
TIME_TO_SEC(t),SEC_TO_TIME(s) 時間和秒轉換
TO_DAYS(d) ,計算日期d距離0000年1月1日的天數
DATEDIFF(d1,d2) ,計算日期d1->d2之間相隔的天數
ADDDATE(d,n) ,計算日期d加上n天的日期
SUBDATE(d,n) ,日期d減去n天後的日期
ADDDATE(d,INTERVAL expr type) 計算起始日期d加上一個時間段後的日期
SUBDATE(d,INTERVAL expr type) ,日期d減去一個時間段後的日期
ADDTIME(t,n),SUBTIME(t,n) 時間t加/減上n秒的時間
type有:
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
4. 條件判斷函數
IF(expr,v1,v2), if(sex=,"男","女")
IFNULL(v1,v2)
CASE expr
WHEN e1 THEN v1
WHEN e1 THEN v1
...
ELSE vn
END
5.其他函數
格式化函數FORMAT(x,n),将數字x進行格式化,将x保留到小數點後n位
ASCII(s) 傳回字元串s的第一個字元的ASCII碼;
BIN(x) 傳回x的二進制編碼;
HEX(x) 傳回x的十六進制編碼;
OCT(x) 傳回x的八進制編碼;
CONV(x,f1,f2) 傳回f1進制數變成f2進制數;
轉換資料類型
CAST(x AS type)
CONVERT(x,type)
eg, CAST('3' AS UNSIGNED INTEGER)
類型有BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER
自定義函數
MYSQL支援自定義函數
- 建立UDF:
CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
- 删除UDF:
- 調用函數:
SELECT function_name(parameter_value,...)
UDF可以沒有參數,但UDF必須有且隻有一個傳回值
在函數體中,如果包含多條語句,我們需要把多條語句放到BEGIN…END語句塊中
- 定義局部變量:
DECLARE var_name[,varname]...date_type [DEFAULT VALUE];
定義局部變量語句必須在BEGIN…END的第一行定義
- 變量指派:
使用者變量定義文法:(可以了解成全局變量)
- LOOP語句
使某些特定的語句重複執行,LOOP語句本身沒有停止循環的語句,必須是遇到LEAVE語句等才能停止循環。begin_label參數和end_label參數分别表示循環開始和結束的标志
LOOP語句的文法:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
- LEAVE語句,ITERATE語句
LEAVE語句,ITERATE語句主要用于跳出循環控制。其文法形式如下:
LEAVE label
ITERATE label
其中LEAVE相當于break,ITERATE相當于continue
- REPEAT語句
REPEAT語句是有條件控制的循環語句。相當于do while,基本文法形式如下:
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
- WHILE語句
基本文法形式如下:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
最後舉個例子,leetcode的一道題,
https://leetcode.com/problems/nth-highest-salary/description/
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-;
RETURN (
# Write your MySQL query statement below.
select max(Salary) from
(
select Salary from
(
select distinct Salary from Employee A
UNION ALL
(select distinct Salary from Employee B order by Salary DESC limit N)
)D GROUP BY Salary HAVING COUNT(Salary) =
)E
);
END
或者利用limit
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-;
RETURN (
# Write your MySQL query statement below.
SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M ,), NULL)
);
END
這裡注意下limit的用法
LIMIT 2 OFFSET 1; 2條資料,從第1條開始讀取
LIMIT 2,1; 從第2條開始讀,讀取1條