MySQL 內(nèi)置函數(shù)
mysql 內(nèi)置函數(shù)
mysql數(shù)據(jù)庫中提供了很豐富的函數(shù)，可以在 sql 語句中直接使用，非常方便。mysql函數(shù)包括數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)、格式化函數(shù)等。通過這些函數(shù)，可以簡化用戶的操作。以下列出了這些函數(shù)的說明。
1. mysql 字符串函數(shù)
| 函數(shù) | 描述 | 范例 |
|---|---|---|
| ascii(s) | 返回字符串 s 的第一個字符的 ascii 碼。 |
返回 customername 字段第一個字母的 ascii 碼： select ascii(customername) as numcodeoffirstchar from customers; |
| char_length(s) | 返回字符串 s 的字符數(shù) |
返回字符串 yapf 的字符數(shù)
select char_length("yapf") as lengthofstring;
|
| character_length(s) | 返回字符串 s 的字符數(shù) |
返回字符串 yapf 的字符數(shù)
select character_length("yapf") as lengthofstring;
|
| concat(s1,s2...sn) | 字符串 s1,s2 等多個字符串合并為一個字符串 |
合并多個字符串
select concat("sql ", "yapf ", "gooogle ", "facebook") as concatenatedstring;
|
| concat_ws(x, s1,s2...sn) | 同 concat(s1,s2,...) 函數(shù)，但是每個字符串之間要加上 x，x 可以是分隔符 |
合并多個字符串，并添加分隔符：
select concat_ws("-", "sql", "tutorial", "is", "fun!")as concatenatedstring;
|
| field(s,s1,s2...) | 返回第一個字符串 s 在字符串列表(s1,s2...)中的位置 |
返回字符串 c 在列表值中的位置：
select field("c", "a", "b", "c", "d", "e");
|
| find_in_set(s1,s2) | 返回在字符串s2中與s1匹配的字符串的位置 |
返回字符串 c 在指定字符串中的位置：
select find_in_set("c", "a,b,c,d,e");
|
| format(x,n) | 函數(shù)可以將數(shù)字 x 進行格式化 "#,###.##", 將 x 保留到小數(shù)點后 n 位，最后一位四舍五入。 |
格式化數(shù)字 "#,###.##" 形式： select format(250500.5634, 2); -- 輸出 250,500.56 |
| insert(s1,x,len,s2) | 字符串 s2 替換 s1 的 x 位置開始長度為 len 的字符串 |
從字符串第一個位置開始的 6 個字符替換為 yapf
select insert("google.com", 1, 6, "yapf"); -- yapf.com
|
| locate(s1,s) | 從字符串 s 中獲取 s1 的開始位置 |
獲取 b 在字符串 abc 中的位置：
select locate('st','myteststring'); -- 5
返回字符串 abc 中 b 的位置： select locate('b', 'abc') -- 2
|
| lcase(s) | 將字符串 s 的所有字母變成小寫字母 |
字符串 yapf 轉(zhuǎn)換為小寫：
select lcase('yapf') -- yapf
|
| left(s,n) | 返回字符串 s 的前 n 個字符 |
返回字符串 yapf 中的前兩個字符：
select left('yapf',2) -- co
|
| lower(s) | 將字符串 s 的所有字母變成小寫字母 |
字符串 yapf 轉(zhuǎn)換為小寫：
select lower('yapf') -- yapf
|
| lpad(s1,len,s2) | 在字符串 s1 的開始處填充字符串 s2，使字符串長度達到 len |
將字符串 xx 填充到 abc 字符串的開始處：
select lpad('abc',5,'xx') -- xxabc
|
| ltrim(s) | 去掉字符串 s 開始處的空格 |
去掉字符串 yapf 開始處的空格：
select ltrim(" yapf") as lefttrimmedstring;-- yapf
|
| mid(s,n,len) | 從字符串 s 的 n 位置截取長度為 len 的子字符串，同 substring(s,n,len) |
從字符串 yapf 中的第 2 個位置截取 3個 字符：
select mid("yapf", 2, 3) as extractstring; -- deb
|
| position(s1 in s) | 從字符串 s 中獲取 s1 的開始位置 |
返回字符串 abc 中 b 的位置：
select position('b' in 'abc') -- 2
|
| repeat(s,n) | 將字符串 s 重復 n 次 |
將字符串 yapf 重復三次：
select repeat('yapf',3) -- yapfyapfyapf
|
| replace(s,s1,s2) | 將字符串 s2 替代字符串 s 中的字符串 s1 |
將字符串 abc 中的字符 a 替換為字符 x：
select replace('abc','a','x') --xbc
|
| reverse(s) | 將字符串s的順序反過來 |
將字符串 abc 的順序反過來：
select reverse('abc') -- cba
|
| right(s,n) | 返回字符串 s 的后 n 個字符 |
返回字符串 yapf 的后兩個字符：
select right('yapf',2) -- ku
|
| rpad(s1,len,s2) | 在字符串 s1 的結(jié)尾處添加字符串 s2，使字符串的長度達到 len |
將字符串 xx 填充到 abc 字符串的結(jié)尾處：
select rpad('abc',5,'xx') -- abcxx
|
| rtrim(s) | 去掉字符串 s 結(jié)尾處的空格 |
去掉字符串 yapf 的末尾空格：
select rtrim("yapf ") as righttrimmedstring; -- yapf
|
| space(n) | 返回 n 個空格 |
返回 10 個空格： select space(10); |
| strcmp(s1,s2) | 比較字符串 s1 和 s2，如果 s1 與 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 |
比較字符串：
select strcmp("yapf", "yapf"); -- 0
|
| substr(s, start, length) | 從字符串 s 的 start 位置截取長度為 length 的子字符串 |
從字符串 yapf 中的第 2 個位置截取 3個 字符：
select substr("yapf", 2, 3) as extractstring; -- deb
|
| substring(s, start, length) | 從字符串 s 的 start 位置截取長度為 length 的子字符串 |
從字符串 yapf 中的第 2 個位置截取 3個 字符：
select substring("yapf", 2, 3) as extractstring; -- deb
|
| substring_index(s, delimiter, number) | 返回從字符串 s 的第 number 個出現(xiàn)的分隔符 delimiter 之后的子串。 如果 number 是正數(shù)，返回第 number 個字符左邊的字符串。 如果 number 是負數(shù)，返回第(number 的絕對值(從右邊數(shù)))個字符右邊的字符串。 |
select substring_index('a*b','*',1) -- a
select substring_index('a*b','*',-1)????-- b
select substring_index(substring_index('a*b*c*d*e','*',3),'*',-1)????-- c
|
| trim(s) | 去掉字符串 s 開始和結(jié)尾處的空格 |
去掉字符串 yapf 的首尾空格：
select trim(' yapf ') as trimmedstring;
|
| ucase(s) | 將字符串轉(zhuǎn)換為大寫 |
將字符串 yapf 轉(zhuǎn)換為大寫：
select ucase("yapf"); -- yapf
|
| upper(s) | 將字符串轉(zhuǎn)換為大寫 |
將字符串 yapf 轉(zhuǎn)換為大寫：
select upper("yapf"); -- yapf
|
2. mysql 數(shù)字函數(shù)
| 函數(shù)名 | 描述 | 范例 |
|---|---|---|
| abs(x) | 返回 x 的絕對值 |
返回 -1 的絕對值： select abs(-1) -- 返回1 |
| acos(x) | 求 x 的反余弦值(參數(shù)是弧度) |
select acos(0.25); |
| asin(x) | 求反正弦值(參數(shù)是弧度) |
select asin(0.25); |
| atan(x) | 求反正切值(參數(shù)是弧度) |
select atan(2.5); |
| atan2(n, m) | 求反正切值(參數(shù)是弧度) |
select atan2(-0.8, 2); |
| avg(expression) | 返回一個表達式的平均值，expression 是一個字段 |
返回 products 表中price 字段的平均值： select avg(price) as averageprice from products; |
| ceil(x) | 返回大于或等于 x 的最小整數(shù) |
select ceil(1.5) -- 返回2 |
| ceiling(x) | 返回大于或等于 x 的最小整數(shù) |
select ceiling(1.5); -- 返回2 |
| cos(x) | 求余弦值(參數(shù)是弧度) |
select cos(2); |
| cot(x) | 求余切值(參數(shù)是弧度) |
select cot(6); |
| count(expression) | 返回查詢的記錄總數(shù)，expression 參數(shù)是一個字段或者 * 號 |
返回 products 表中 products 字段總共有多少條記錄： select count(productid) as numberofproducts from products; |
| degrees(x) | 將弧度轉(zhuǎn)換為角度 |
select degrees(3.1415926535898) -- 180 |
| n div m | 整除，n 為被除數(shù)，m 為除數(shù) |
計算 10 除于 5： select 10 div 5; -- 2 |
| exp(x) | 返回 e 的 x 次方 |
計算 e 的三次方： select exp(3) -- 20.085536923188 |
| floor(x) | 返回小于或等于 x 的最大整數(shù) |
小于或等于 1.5 的整數(shù)： select floor(1.5) -- 返回1 |
| greatest(expr1, expr2, expr3, ...) | 返回列表中的最大值 |
返回以下數(shù)字列表中的最大值： select greatest(3, 12, 34, 8, 25); -- 34 返回以下字符串列表中的最大值：
select greatest("google", "yapf", "apple"); -- google
|
| least(expr1, expr2, expr3, ...) | 返回列表中的最小值 |
返回以下數(shù)字列表中的最小值： select least(3, 12, 34, 8, 25); -- 3 返回以下字符串列表中的最小值：
select least("google", "yapf", "apple"); -- apple
|
| ln | 返回數(shù)字的自然對數(shù)，以 e 為底。 |
返回 2 的自然對數(shù)： select ln(2); -- 0.6931471805599453 |
| log(x) 或 log(base, x) | 返回自然對數(shù)(以 e 為底的對數(shù))，如果帶有 base 參數(shù)，則 base 為指定帶底數(shù)?！ ?/td> |
select log(20.085536923188) -- 3 select log(2, 4); -- 2 |
| log10(x) | 返回以 10 為底的對數(shù) |
select log10(100) -- 2 |
| log2(x) | 返回以 2 為底的對數(shù) |
返回以 2 為底 6 的對數(shù)： select log2(6); -- 2.584962500721156 |
| max(expression) | 返回字段 expression 中的最大值 |
返回數(shù)據(jù)表 products 中字段 price 的最大值： select max(price) as largestprice from products; |
| min(expression) | 返回字段 expression 中的最小值 |
返回數(shù)據(jù)表 products 中字段 price 的最小值： select min(price) as minprice from products; |
| mod(x,y) | 返回 x 除以 y 以后的余數(shù) |
5 除于 2 的余數(shù)： select mod(5,2) -- 1 |
| pi() | 返回圓周率(3.141593） |
select pi() --3.141593 |
| pow(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： select pow(2,3) -- 8 |
| power(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： select power(2,3) -- 8 |
| radians(x) | 將角度轉(zhuǎn)換為弧度 |
180 度轉(zhuǎn)換為弧度： select radians(180) -- 3.1415926535898 |
| rand() | 返回 0 到 1 的隨機數(shù) |
select rand() --0.93099315644334 |
| round(x) | 返回離 x 最近的整數(shù) |
select round(1.23456) --1 |
| sign(x) | 返回 x 的符號，x 是負數(shù)、0、正數(shù)分別返回 -1、0 和 1 |
select sign(-10) -- (-1) |
| sin(x) | 求正弦值(參數(shù)是弧度) |
select sin(radians(30)) -- 0.5 |
| sqrt(x) | 返回x的平方根 |
25 的平方根： select sqrt(25) -- 5 |
| sum(expression) | 返回指定字段的總和 |
計算 orderdetails 表中字段 quantity 的總和： select sum(quantity) as totalitemsordered from orderdetails; |
| tan(x) | 求正切值(參數(shù)是弧度) |
select tan(1.75); -- -5.52037992250933 |
| truncate(x,y) | 返回數(shù)值 x 保留到小數(shù)點后 y 位的值（與 round 最大的區(qū)別是不會進行四舍五入） |
select truncate(1.23456,3) -- 1.234 |
3. mysql 日期函數(shù)
| 函數(shù)名 | 描述 | 范例 |
|---|---|---|
| adddate(d,n) | 計算起始日期 d 加上 n 天的日期 |
select adddate("2017-06-15", interval 10 day);
->2017-06-25
|
| addtime(t,n) | n 是一個時間表達式，時間 t 加上時間表達式 n |
加 5 秒：
select addtime('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:16 (秒)
添加 2 小時, 10 分鐘, 5 秒:
select addtime("2020-06-15 09:34:21", "2:10:5");
-> 2020-06-15 11:44:26
|
| curdate() | 返回當前日期 |
select curdate(); -> 2018-09-19 |
| current_date() | 返回當前日期 |
select current_date(); -> 2018-09-19 |
| current_time | 返回當前時間 |
select current_time(); -> 19:59:02 |
| current_timestamp() | 返回當前日期和時間 |
select current_timestamp() -> 2018-09-19 20:57:43 |
| curtime() | 返回當前時間 |
select curtime(); -> 19:59:02 |
| date() | 從日期或日期時間表達式中提取日期值 |
select date("2017-06-15");
-> 2017-06-15
|
| datediff(d1,d2) | 計算日期 d1->d2 之間相隔的天數(shù) |
select datediff('2001-01-01','2001-02-02')
-> -32
|
| date_add(d，interval expr type) | 計算起始日期 d 加上一個時間段后的日期 |
select adddate('2011-11-11 11:11:11',1)
-> 2011-11-12 11:11:11????(默認是天)
select adddate('2011-11-11 11:11:11', interval 5 minute)
-> 2011-11-11 11:16:11 (type的取值與上面那個列出來的函數(shù)類似)
|
| date_format(d,f) | 按表達式 f的要求顯示日期 d |
select date_format('2011-11-11 11:11:11','%y-%m-%d %r')
-> 2011-11-11 11:11:11 am
|
| date_sub(date,interval expr type) | 函數(shù)從日期減去指定的時間間隔。 |
orders 表中 orderdate 字段減去 2 天： select orderid,date_sub(orderdate,interval 2 day) as orderpaydate from orders |
| day(d) | 返回日期值 d 的日期部分 |
select day("2017-06-15");
-> 15
|
| dayname(d) | 返回日期 d 是星期幾，如 monday,tuesday |
select dayname('2011-11-11 11:11:11')
->friday
|
| dayofmonth(d) | 計算日期 d 是本月的第幾天 |
select dayofmonth('2011-11-11 11:11:11')
->11
|
| dayofweek(d) | 日期 d 今天是星期幾，1 星期日，2 星期一，以此類推 |
select dayofweek('2011-11-11 11:11:11')
->6
|
| dayofyear(d) | 計算日期 d 是本年的第幾天 |
select dayofyear('2011-11-11 11:11:11')
->315
|
| extract(type from d) | 從日期 d 中獲取指定的值，type 指定返回的值。
type可取值為：
|
select extract(minute from '2011-11-11 11:11:11') -> 11 |
| from_days(n) | 計算從 0000 年 1 月 1 日開始 n 天后的日期 |
select from_days(1111) -> 0003-01-16 |
| hour(t) | 返回 t 中的小時值 |
select hour('1:2:3')
-> 1
|
| last_day(d) | 返回給給定日期的那一月份的最后一天 |
select last_day("2017-06-20");
-> 2017-06-30
|
| localtime() | 返回當前日期和時間 |
select localtime() -> 2018-09-19 20:57:43 |
| localtimestamp() | 返回當前日期和時間 |
select localtimestamp() -> 2018-09-19 20:57:43 |
| makedate(year, day-of-year) | 基于給定參數(shù)年份 year 和所在年中的天數(shù)序號 day-of-year 返回一個日期 |
select makedate(2017, 3); -> 2017-01-03 |
| maketime(hour, minute, second) | 組合時間，參數(shù)分別為小時、分鐘、秒 |
select maketime(11, 35, 4); -> 11:35:04 |
| microsecond(date) | 返回日期參數(shù)所對應的微秒數(shù) |
select microsecond("2017-06-20 09:34:00.000023");
-> 23
|
| minute(t) | 返回 t 中的分鐘值 |
select minute('1:2:3')
-> 2
|
| monthname(d) | 返回日期當中的月份名稱，如 november |
select monthname('2011-11-11 11:11:11')
-> november
|
| month(d) | 返回日期d中的月份值，1 到 12 |
select month('2011-11-11 11:11:11')
->11
|
| now() | 返回當前日期和時間 |
select now() -> 2018-09-19 20:57:43 |
| period_add(period, number) | 為 年-月 組合日期添加一個時段 |
select period_add(201703, 5); -> 201708 |
| period_diff(period1, period2) | 返回兩個時段之間的月份差值 |
select period_diff(201710, 201703); -> 7 |
| quarter(d) | 返回日期d是第幾季節(jié)，返回 1 到 4 |
select quarter('2011-11-11 11:11:11')
-> 4
|
| second(t) | 返回 t 中的秒鐘值 |
select second('1:2:3')
-> 3
|
| sec_to_time(s) | 將以秒為單位的時間 s 轉(zhuǎn)換為時分秒的格式 |
select sec_to_time(4320) -> 01:12:00 |
| str_to_date(string, format_mask) | 將字符串轉(zhuǎn)變?yōu)槿掌?/td> |
select str_to_date("august 10 2017", "%m %d %y");
-> 2017-08-10
|
| subdate(d,n) | 日期 d 減去 n 天后的日期 |
select subdate('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默認是天)
|
| subtime(t,n) | 時間 t 減去 n 秒的時間 |
select subtime('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)
|
| sysdate() | 返回當前日期和時間 |
select sysdate() -> 2018-09-19 20:57:43 |
| time(expression) | 提取傳入表達式的時間部分 |
select time("19:30:10");
-> 19:30:10
|
| time_format(t,f) | 按表達式 f 的要求顯示時間 t |
select time_format('11:11:11','%r')
11:11:11 am
|
| time_to_sec(t) | 將時間 t 轉(zhuǎn)換為秒 |
select time_to_sec('1:12:00')
-> 4320
|
| timediff(time1, time2) | 計算時間差值 |
select timediff("13:10:11", "13:10:10");
-> 00:00:01
|
| timestamp(expression, interval) | 單個參數(shù)時，函數(shù)返回日期或日期時間表達式；有2個參數(shù)時，將參數(shù)加和 |
select timestamp("2017-07-23", "13:10:11");
-> 2017-07-23 13:10:11
|
| to_days(d) | 計算日期 d 距離 0000 年 1 月 1 日的天數(shù) |
select to_days('0001-01-01 01:01:01')
-> 366
|
| week(d) | 計算日期 d 是本年的第幾個星期，范圍是 0 到 53 |
select week('2011-11-11 11:11:11')
-> 45
|
| weekday(d) | 日期 d 是星期幾，0 表示星期一，1 表示星期二 |
select weekday("2017-06-15");
-> 3
|
| weekofyear(d) | 計算日期 d 是本年的第幾個星期，范圍是 0 到 53 |
select weekofyear('2011-11-11 11:11:11')
-> 45
|
| year(d) | 返回年份 |
select year("2017-06-15");
-> 2017
|
| yearweek(date, mode) | 返回年份及第幾周（0到53），mode 中 0 表示周天，1表示周一，以此類推 |
select yearweek("2017-06-15");
-> 201724
|
4. mysql 高級函數(shù)
| 函數(shù)名 | 描述 | 范例 |
|---|---|---|
| bin(x) | 返回 x 的二進制編碼 |
15 的 2 進制編碼: select bin(15); -- 1111 |
| binary(s) | 將字符串 s 轉(zhuǎn)換為二進制字符串 |
select binary "yapf"; -> yapf |
case expression
when condition1 then result1
when condition2 then result2
...
when conditionn then resultn
else result
end
|
case 表示函數(shù)開始，end 表示函數(shù)結(jié)束。如果 condition1 成立，則返回 result1, 如果 condition2 成立，則返回 result2，當全部不成立則返回 result，而當有一個成立之后，后面的就不執(zhí)行了。 |
select case when 1 > 0 then '1 > 0' when 2 > 0 then '2 > 0' else '3 > 0' end ->1 > 0 |
| cast(x as type) | 轉(zhuǎn)換數(shù)據(jù)類型 |
字符串日期轉(zhuǎn)換為日期：
select cast("2017-08-29" as date);
-> 2017-08-29
|
| coalesce(expr1, expr2, ...., expr_n) | 返回參數(shù)中的第一個非空表達式（從左向右） |
select coalesce(null, null, null, 'yapf.com', null, 'google.com'); -> yapf.com |
| connection_id() | 返回唯一的連接 id |
select connection_id(); -> 4292835 |
| conv(x,f1,f2) | 返回 f1 進制數(shù)變成 f2 進制數(shù) |
select conv(15, 10, 2); -> 1111 |
| convert(s using cs) | 函數(shù)將字符串 s 的字符集變成 cs |
select charset('abc')
->utf-8
select charset(convert('abc' using gbk))
->gbk
|
| current_user() | 返回當前用戶 |
select current_user(); -> guest@% |
| database() | 返回當前數(shù)據(jù)庫名 |
select database(); -> yapf |
| if(expr,v1,v2) | 如果表達式 expr 成立，返回結(jié)果 v1；否則，返回結(jié)果 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(); ->6 |
| nullif(expr1, expr2) | 比較兩個字符串，如果字符串 expr1 與 expr2 相等 返回 null，否則返回 expr1 |
select nullif(25, 25); -> |
| session_user() | 返回當前用戶 |
select session_user(); -> guest@% |
| system_user() | 返回當前用戶 |
select system_user(); -> guest@% |
| user() | 返回當前用戶 |
select user(); -> guest@% |
| version() | 返回數(shù)據(jù)庫的版本號 |
select version() -> 5.6.34 |
學習MySQL