MySQL學習筆記（四）：內置函數(shù)和自定義函數(shù)

一蔚万、常見內置函數(shù)

從文檔列表可知贺待，MySQL內置函數(shù)涵蓋范圍非常廣：控制流函數(shù)徽曲、字符串函數(shù)、數(shù)值函數(shù)狠持、時間日期函數(shù)疟位、全文搜索函數(shù) 映射函數(shù)、XML函數(shù)喘垂、位函數(shù)甜刻、加密和壓縮函數(shù)、信息函數(shù)正勒、空間分析函數(shù)得院、Json函數(shù)、企業(yè)加密函數(shù)等等章贞。在此只列舉開發(fā)中常用的函數(shù)祥绞。

聚合函數(shù)

這些函數(shù)與其它函數(shù)有些不同：它們一般作用在多條記錄上，一般與GROUP BY等關鍵字組合使用鸭限。

AVG([DISTINCT] expr)：返回平均值

mysql> SELECT student_name, AVG(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

COUNT(expr)：返回記錄數(shù)

mysql> SELECT student.student_name,COUNT(*)
    ->        FROM student,course
    ->        WHERE student.student_id=course.student_id
    ->        GROUP BY student_name;
#
# COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, 
# whether or not they contain NULL values.`

COUNT(DISTINCT expr,[expr...])：Returns a count of the number of rows with different non-NULL expr values.

mysql> SELECT COUNT(DISTINCT results) FROM student;

MAX([DISTINCT] expr)：返回表達式中的最大值

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

MIN([DISTINCT] expr) ：返回表達式的最小值

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

SUM([DISTINCT] expr)
：返回表達式的和

字符串函數(shù)

CONCAT()
：返回連接字符串（如果其中任何一個參數(shù)為NULL蜕径，則返回NULL）

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

INSERT(str,pos,len,newstr) ：將str的從pos位置開始的len個字符替換為newstr（如果其中任何一個參數(shù)為NULL，則返回NULL）

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'

LOWER(str)和UPPER(str) ：將字符串str轉換成小寫/大寫
LEFT(str,len)和RIGHT(str,len) ：分別返回str最左邊/最右邊的n個字符（如果其中任何一個參數(shù)為NULL败京，則返回NULL）
TRIM(str)和RTRIM(str)以及LTRIM(str)：去除字符串str左右空格/右空格/左空格
REPLACE(str,from_str,to_str)：將字符串str中所有出現(xiàn)的from_str字符串替換為to_str

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

SUBSTRING(str,pos,len)：返回字符串str中從pos起兜喻，len個字符長度的字符串

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

數(shù)值函數(shù)

ABS(x)：返回x的絕對值

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32

CEILING(x)或者CEIL(x)：返回大于x的最小整數(shù)
FLOOR(x) ：返回小于x的最大整數(shù)

mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1
mysql> SELECT FLOOR(1.23), FLOOR(-1.23);
        -> 1, -2

MOD(N,M) 或者 N % M 或者 N MOD M：取模

mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2

RAND()
用法1：返回[0 ~1.0)之間的隨機數(shù)

//要得到i <= R < j的隨機數(shù)： FLOOR(i + RAND() * (j?i))
//比如，要得到5~10之間的隨機數(shù)：FLOOR(5 + RAND()*5)

用法2：SELECT * FROM tbl_name ORDER BY RAND();：retrieve rows in random order

ROUND(X)或者ROUND(X,D)：對X四舍五入赡麦，含有D位小數(shù)的值朴皆，D值默認為0

mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20

TRUNCATE(X,D)：返回數(shù)字X被截斷為D位小數(shù)的數(shù)值（與ROUND()類似帕识，只是不四舍五入）

mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
mysql> SELECT TRUNCATE(122,-2);
       -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1028

時間日期函數(shù)

CURDATE()
：返回當前日期，格式為'YYYY-MM-DD'或者YYYYMMDD遂铡，取決于該函數(shù)的使用場景

mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613

CURTIME([fsp])：返回當前時間肮疗，格式為'HH:MM:SS'或者HHMMSS

mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026.000000

NOW([fsp])：返回當前日期+時間

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)：返回當前時間/date的unix時間戳

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1496132631 |
+------------------+
1 row in set
mysql> SELECT UNIX_TIMESTAMP('2017-05-30 16:25:19');
+---------------------------------------+
| UNIX_TIMESTAMP('2017-05-30 16:25:19') |
+---------------------------------------+
|                            1496132719 |
+---------------------------------------+
1 row in set

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)：把unix時間戳轉換格式

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

DATE_FORMAT(date,format)：將date時間格式化

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

其他函數(shù)

database() ：當前數(shù)據(jù)庫
version()：當前數(shù)據(jù)庫版本
user() ：當前登錄用戶

mysql> select database(), version(), user();
+------------+------------+----------------+
| database() | version()  | user()         |
+------------+------------+----------------+
| cpgl       | 5.7.17-log | root@localhost |
+------------+------------+----------------+
1 row in set

二、自定義函數(shù)

參考：MySql------自定義函數(shù)詳解

基本語法

創(chuàng)建

CREATE FUNCTION [db_name.]fn_name(func_parameter[,...])
RETURNS type
[characteristic...]
routine_body
#解釋
db_name：數(shù)據(jù)庫名扒接，不指明則為當前數(shù)據(jù)庫
type: 任何mysql支持的類型
routine_body: 函數(shù)體
characteristic:
LANGUAGE SQL | [NOT]DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'String'

注意：函數(shù)中一定要有return返回值語句

刪除

DROP FUNCTION  [IF EXISTS]  fn_name;

更改

ALTER FUNCTION fn_name [characteristic...]

查看狀態(tài)或定義語句

SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW CREATE FUNCTION fn_name;

函數(shù)中的變量
定義變量
a.通過DECLARE可以定義一個局部變量伪货，變量的作用范圍BEGIN...END塊中；
b.全局變量不用聲明珠增，可以直接@XXX使用超歌。定義全局變量：set @var_name=value，如：

mysql> set @a=1;
Query OK, 0 rows affected

c.變量語句必須寫在復合語句開頭蒂教，并且在其他語句的前面;
d.一次性可以聲明多個變量;
DECLARE var_name[,...] type [DEFAULT value]

為變量賦值
a.直接賦值：SET var_name = expr[,var_name=expr]...
b.通過查詢語句賦值：SELECT...INTO...巍举，如：

SELECT 表中某字段 INTO 變量名 FROM 表名 WHERE條件;

這種方式下，SELECT語句的查詢結果只能有一個凝垛。

其他說明
運行包含DDL語句懊悯，允許提交或回滾
可以在函數(shù)中調用其它函數(shù)或者存儲過程
與存儲過程類似，可以一塊看

實例

例子1

#創(chuàng)建表
CREATE TABLE `t_user_main` (  
  `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用戶id,作為主鍵',  
  `f_userName` varchar(5) DEFAULT NULL COMMENT '用戶名',  
  `f_age` int(3) DEFAULT NULL COMMENT '年齡',  
  PRIMARY KEY (`f_userId`)  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;  
#插入數(shù)據(jù)
INSERT INTO t_user_main (f_userName, f_age)   
VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29);  

#創(chuàng)建一個函數(shù)  
DELIMITER $$ -- 定界符  
-- 開始創(chuàng)建函數(shù)  
CREATE FUNCTION user_main_fn(v_id INT)  
RETURNS VARCHAR(50)  
BEGIN  
  -- 定義變量  
  DECLARE v_userName VARCHAR(50);  
  -- 給定義的變量賦值  
  SELECT f_userName INTO v_userName FROM t_user_main   
  WHERE f_userId = v_id;  
  -- 返回函數(shù)處理結果  
  RETURN v_userName;  
END $$ -- 函數(shù)創(chuàng)建定界符  
DELIMITER ;

例子2

最后編輯于：2017.12.07 23:12:21

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者

人面猴
序言：七十年代末梦皮，一起剝皮案震驚了整個濱河市炭分，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌剑肯，老刑警劉巖捧毛，帶你破解...
沈念sama閱讀 222,183評論 6贊 516
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異让网，居然都是意外死亡呀忧，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 94,850評論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門溃睹，熙熙樓的掌柜王于貴愁眉苦臉地迎上來而账，“玉大人，你說我怎么就攤上這事因篇∨⒎” “怎么了？”我有些...
開封第一講書人閱讀 168,766評論 0贊 361
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵竞滓，是天一觀的道長咐吼。經常有香客問我，道長商佑，這世上最難降的妖魔是什么锯茄？我笑而不...
開封第一講書人閱讀 59,854評論 1贊 299
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮莉御，結果婚禮上撇吞，老公的妹妹穿的比我還像新娘。我一直安慰自己礁叔，他們只是感情好牍颈，可當我...
茶點故事閱讀 68,871評論 6贊 398
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布陵像。她就那樣靜靜地躺著诗眨，像睡著了一般。火紅的嫁衣襯著肌膚如雪荒揣。梳的紋絲不亂的頭發(fā)上涣易，一...
開封第一講書人閱讀 52,457評論 1贊 311
城市分裂傳說
那天画机，我揣著相機與錄音，去河邊找鬼新症。笑死步氏，一個胖子當著我的面吹牛，可吹牛的內容都是我干的徒爹。我是一名探鬼主播荚醒，決...
沈念sama閱讀 40,999評論 3贊 422
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼隆嗅！你這毒婦竟也來了界阁？” 一聲冷哼從身側響起，我...
開封第一講書人閱讀 39,914評論 0贊 277
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤胖喳，失蹤者是張志新（化名）和其女友劉穎泡躯，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體丽焊，經...
沈念sama閱讀 46,465評論 1贊 319
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡较剃，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內容為張勛視角年9月15日...
茶點故事閱讀 38,543評論 3贊 342
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了粹懒。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片重付。...
茶點故事閱讀 40,675評論 1贊 353
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖凫乖，靈堂內的尸體忽然破棺而出确垫，到底是詐尸還是另有隱情，我是刑警寧澤帽芽，帶...
沈念sama閱讀 36,354評論 5贊 351
?日本核電站爆炸內幕
正文年R本政府宣布删掀，位于F島的核電站，受9級特大地震影響导街，放射性物質發(fā)生泄漏披泪。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 42,029評論 3贊 335
男人毒藥：我在死后第九天來索命
文/蒙蒙一搬瑰、第九天我趴在偏房一處隱蔽的房頂上張望款票。院中可真熱鬧控硼，春花似錦、人聲如沸艾少。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,514評論 0贊 25
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽缚够。三九已至幔妨，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間谍椅，已是汗流浹背误堡。一陣腳步聲響...
開封第一講書人閱讀 33,616評論 1贊 274
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留雏吭，地道東北人锁施。一個月前我還...
沈念sama閱讀 49,091評論 3贊 378
代替公主和親
正文我出身青樓，卻偏偏與公主長得像思恐，于是被迫代替她去往敵國和親沾谜。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 45,685評論 2贊 360