前言

MySQL是一種關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)系數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。

創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括儀隴網(wǎng)站建設(shè)、儀隴網(wǎng)站制作、儀隴網(wǎng)頁制作以及儀隴網(wǎng)絡(luò)營(yíng)銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，儀隴網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到儀隴省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。

一、系統(tǒng)封裝函數(shù)

MySQL 有很多內(nèi)置的函數(shù)，可以快速解決開發(fā)中的一些業(yè)務(wù)需求，大概包括流程控制函數(shù)，數(shù)值型函數(shù)、字符串型函數(shù)、日期時(shí)間函數(shù)、聚合函數(shù)等。以下列出了這些分類中常用的函數(shù)。

1、控制流程函數(shù)

• case...when

根據(jù)值判斷返回值，類比編程中的IF-ELSE判斷。

-- DEMO 01
SELECT CASE DATE_FORMAT(NOW(),'%Y-%m-%d') 
    WHEN '2019-12-29' THEN 'today' 
    WHEN '2019-12-28' THEN 'yesterday' 
    WHEN '2019-12-30' THEN 'tommor' 
    ELSE 'Unknow' END;
-- DEMO 02
SELECT (CASE WHEN 1>0 THEN 'true' ELSE 'false' END) AS result;

• if(expr1,expr2,expr3)

如果表達(dá)式 expr1 是TRUE，則 IF()的返回值為expr2; 否則返回值則為 expr3。

SELECT IF(1>2,'1>2','1<2') AS result ; 
SELECT IF(1<2,'yes ','no') AS result ;
SELECT IF(STRCMP('test','test'),'no','yes');

• ifnull(expr1,expr2)

如果表達(dá)式 expr1不為NULL，則返回值為expr1;否則返回值為 expr2。

SELECT IFNULL(NULL,'cicada');
SELECT IFNULL(1/1,'no');

2、常用字符串函數(shù)

• CHAR_LENGTH()

返回值為字符串的長(zhǎng)度 。

SELECT CHAR_LENGTH(' c i c ') ;-- 包含空格
SELECT LENGTH(' S q l ') ;

• CONCAT(str1...)

拼接串聯(lián)字符串。

SELECT CONCAT('My', 'S', 'ql');
SELECT CONCAT('My', NULL, 'QL'); -- 包含Null 則返回Null
SELECT CONCAT("%", "Java", "%"); -- mybatis中拼接模糊查詢

• ELT(N,str1,str2,...)

若N = 1，則返回值為  str1 ，若N = 2，則返回值為 str2 ，以此類推，可以用來轉(zhuǎn)換返回頁面的狀態(tài)。

SELECT ELT(1,'提交','審核中','規(guī)則通過') ;
SELECT ELT(2,'提交','審核中','規(guī)則通過') ;

• FORMAT(X,D)

格式化數(shù)字類型。

SELECT FORMAT(3.1455,2) ; -- 四舍五入保留兩位
SELECT TRUNCATE(3.1455,2) ; -- 直接截取兩位

• TRIM(str)

清空字符串空格。

SELECT LTRIM('  hel l o ') ;-- 清空左邊
SELECT RTRIM('  hel l o ') ;-- 清空右邊
SELECT TRIM('  hel l o ') ; -- 清空兩邊
SELECT REPLACE('M y S Q L',' ','') ; -- 替換掉全部空格

3、數(shù)值函數(shù)

• FLOOR(X)

返回不大于X的最大整數(shù)值 。

SELECT FLOOR(1.23); -- 1
SELECT FLOOR(-1.23); -- -2

• MOD(N,M)

模操作。返回N 被 M除后的余數(shù)。

SELECT MOD(29,9); -- 2
SELECT 29 MOD 9; -- 2

• RAND() RAND(N)

返回一個(gè)隨機(jī)浮點(diǎn)值，范圍在0到1之間。若已指定一個(gè)整數(shù)參數(shù) N ，則它被用作種子值，用來產(chǎn)生重復(fù)序列。

SELECT RAND(); -- 0.923
SELECT RAND(20) = RAND(20) ; -- TRUE

4、時(shí)間日期函數(shù)

• ADDDATE(date,INTERVAL expr type)

給指定日期，以指定類型進(jìn)行運(yùn)算。

SELECT DATE_ADD('2019-12-29', INTERVAL 3 DAY); -- 2020-01-01

• CURDATE()

將當(dāng)前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具體格式根據(jù)函數(shù)用在字符串或是數(shù)字語境中而定。

SELECT CURDATE(); -- '2019-12-29' 字符串
SELECT CURDATE() + 0; -- 20180725 數(shù)字

• DATE(expr)

提取日期或時(shí)間日期表達(dá)式expr中的日期部分。

SELECT DATE('2019-12-31 01:02:03'); -- '2019-12-31'
SELECT DATE('2019-12-31 01:02:03')+0; -- 20191231

• DATE_FORMAT(date,format)

根據(jù)format 字符串進(jìn)行 date 值的格式化。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2019-12-29
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 2019年12月29日

5、聚合函數(shù)

AVG([distinct] expr)  求平均值
COUNT({*|[distinct] } expr)  統(tǒng)計(jì)行的數(shù)量
MAX([distinct] expr)  求最大值
MIN([distinct] expr)  求最小值
SUM([distinct] expr)  求累加和

二、自定義函數(shù)

1、概念簡(jiǎn)介

函數(shù)存儲(chǔ)著一系列sql語句，調(diào)用函數(shù)就是一次性執(zhí)行這些語句。所以函數(shù)可以降低語句重復(fù)。函數(shù)注重返回值，而觸發(fā)器注重執(zhí)行過程，所以一些語句無法執(zhí)行。所以函數(shù)并不是單純的sql語句集合。

2、使用方式

create function 函數(shù)名([參數(shù)列表]) returns 數(shù)據(jù)類型
begin
 sql語句;
 return 值;
end;

參數(shù)列表的格式是：  變量名 數(shù)據(jù)類型。

• 無參案例

CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2;
SELECT mysum1 () ;

• 有參函數(shù)

表結(jié)構(gòu)

CREATE TABLE t01_user (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY  COMMENT '主鍵ID',
  user_name varchar(20) DEFAULT NULL COMMENT '用戶名稱'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用戶表';

函數(shù)用法

create function get_name(p_id INT) returns VARCHAR(20)
begin 
    declare userName varchar(20);
    select user_name from t01_user where id=p_id into userName;
    return userName;
end;

SELECT get_name(1) ;

3、函數(shù)查看

show create function get_name ;

4、刪除函數(shù)

drop function get_name ;

5、函數(shù)注意事項(xiàng)

函數(shù)是事先經(jīng)過編譯，才能在服務(wù)器環(huán)境調(diào)用，所以MySQL集群環(huán)境需要同步編譯；MySQL是多線程環(huán)境，所以要保證函數(shù)也是線程安全 。

三、觸發(fā)器

1、觸發(fā)器簡(jiǎn)介

觸發(fā)器是特殊的存儲(chǔ)過程，不同的是存儲(chǔ)過程要用CALL來調(diào)用，而觸發(fā)器不需要使用CALL。也不需要手工啟動(dòng)，只要當(dāng)一個(gè)預(yù)定義的事件發(fā)生的時(shí)候，就會(huì)被MYSQL自動(dòng)觸發(fā)調(diào)用。

2、創(chuàng)建觸發(fā)器

觸發(fā)器語法

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON tbl_name FOR EACH ROW trigger_stmt

• trigger_name：觸發(fā)器命名 ;
• trigger_time: 觸發(fā)動(dòng)作的時(shí)間 ;
• trigger_event: 激活觸發(fā)器的語句類型 ;
• tbl_name: 觸發(fā)器作用的表明，非臨時(shí)表 ;
• trigger_stmt：觸發(fā)程序執(zhí)行的語句 ;

表數(shù)據(jù)同步

當(dāng)向用戶表 t01_user 寫入數(shù)據(jù)時(shí)，同時(shí)向 t02_back 表寫入一份備份數(shù)據(jù)。

-- 用戶備份表
CREATE TABLE t02_back (
    id int(11) NOT NULL PRIMARY KEY COMMENT '主鍵ID',
  user_name varchar(20) DEFAULT NULL COMMENT '用戶名稱'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用戶備份';

-- 觸發(fā)器程序
DROP TRIGGER IF EXISTS user_back_trigger ;
CREATE TRIGGER user_back_trigger AFTER INSERT ON t01_user FOR EACH ROW
BEGIN
    INSERT INTO t02_back (id,user_name)
VALUES (new.id,new.user_name);
END ;

-- 測(cè)試案例
INSERT INTO t01_user (user_name) VALUES ('smile'),('mysql') ;
SELECT * FROM t02_back ;

3、查看觸發(fā)器

查看觸發(fā)器是指數(shù)據(jù)庫(kù)中已存在的觸發(fā)器的定義、狀態(tài)、語法信息等。可以在TRIGGERS表中查看觸發(fā)器信息。

SELECT * FROM `information_schema`.`TRIGGERS` 
WHERE `TRIGGER_NAME`='user_back_trigger';

4、刪除觸發(fā)器

DROP TRIGGER語句可以刪除MYSQL中已經(jīng)定義的觸發(fā)器，刪除觸發(fā)器的基本語法。

DROP TRIGGER [schema_name.]trigger_name

5、觸發(fā)器注意事項(xiàng)

• 觸發(fā)事件

對(duì)于相同的表，相同的事件只能創(chuàng)建一個(gè)觸發(fā)器，比如對(duì)表t01_user創(chuàng)建兩次AFTER INSERT觸發(fā)器，就會(huì)報(bào)錯(cuò)。

• 執(zhí)行效率

觸發(fā)器可以減少應(yīng)用端和數(shù)據(jù)庫(kù)的通信次數(shù)和業(yè)務(wù)邏輯，但是基于行觸發(fā)的邏輯，如果數(shù)據(jù)集非常大，效率會(huì)降低。

• 事務(wù)問題

觸發(fā)器執(zhí)行和原表的執(zhí)行語句是否在同一個(gè)事務(wù)中，取決于觸發(fā)表的存儲(chǔ)引擎是否支持事務(wù)。

本文題目：MySQL的函數(shù)和觸發(fā)器的使用
鏈接URL：http://www.rwnh.cn/article10/jdgpgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、網(wǎng)站營(yíng)銷、全網(wǎng)營(yíng)銷推廣、移動(dòng)網(wǎng)站建設(shè)、企業(yè)建站、Google

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)