MySQL基礎篇(03)：系統和自定義函數總結，觸發器使用詳解

来源:https://www.cnblogs.com/cicada-smile/archive/2019/12/31/12122604.html
-Advertisement-
Play Games

本文源碼： "GitHub·點這裡" || "GitEE·點這裡" 一、系統封裝函數 MySQL 有很多內置的函數，可以快速解決開發中的一些業務需求，大概包括流程式控制制函數，數值型函數、字元串型函數、日期時間函數、聚合函數等。以下列出了這些分類中常用的函數。 1、控制流程函數 case...when ...


本文源碼：GitHub·點這裡 || GitEE·點這裡

一、系統封裝函數

MySQL 有很多內置的函數，可以快速解決開發中的一些業務需求，大概包括流程式控制制函數，數值型函數、字元串型函數、日期時間函數、聚合函數等。以下列出了這些分類中常用的函數。

1、控制流程函數

  • case...when

根據值判斷返回值，類比編程中的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)

如果表達式 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)

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

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

2、常用字元串函數

  • CHAR_LENGTH()

返回值為字元串的長度 。

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

拼接串聯字元串。

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 ，以此類推，可以用來轉換返回頁面的狀態。

SELECT ELT(1,'提交','審核中','規則通過') ;
SELECT ELT(2,'提交','審核中','規則通過') ;
  • FORMAT(X,D)

格式化數字類型。

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、數值函數

  • FLOOR(X)

返回不大於X的最大整數值 。

SELECT FLOOR(1.23); -- 1
SELECT FLOOR(-1.23); -- -2
  • MOD(N,M)

模操作。返回N 被 M除後的餘數。

SELECT MOD(29,9); -- 2
SELECT 29 MOD 9; -- 2
  • RAND() RAND(N)

返回一個隨機浮點值，範圍在0到1之間。若已指定一個整數參數 N ，則它被用作種子值，用來產生重覆序列。

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

4、時間日期函數

  • ADDDATE(date,INTERVAL expr type)

給指定日期，以指定類型進行運算。

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

將當前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具體格式根據函數用在字元串或是數字語境中而定。

SELECT CURDATE(); -- '2019-12-29' 字元串
SELECT CURDATE() + 0; -- 20180725 數字
  • DATE(expr)

提取日期或時間日期表達式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)

根據format 字元串進行 date 值的格式化。

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

5、聚合函數

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

二、自定義函數

1、概念簡介

函數存儲著一系列sql語句，調用函數就是一次性執行這些語句。所以函數可以降低語句重覆。函數註重返回值，而觸發器註重執行過程，所以一些語句無法執行。所以函數並不是單純的sql語句集合。

2、使用方式

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

參數列表的格式是： 變數名 數據類型。

  • 無參案例
CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2;
SELECT mysum1 () ;
  • 有參函數

表結構

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 '用戶表';

函數用法

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、函數查看

show create function get_name ;

4、刪除函數

drop function get_name ;

5、函數註意事項

函數是事先經過編譯，才能在伺服器環境調用，所以MySQL集群環境需要同步編譯；MySQL是多線程環境，所以要保證函數也是線程安全 。

三、觸發器

1、觸發器簡介

觸發器是特殊的存儲過程，不同的是存儲過程要用CALL來調用，而觸發器不需要使用CALL。也不需要手工啟動，只要當一個預定義的事件發生的時候，就會被MYSQL自動觸發調用。

2、創建觸發器

觸發器語法

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON tbl_name FOR EACH ROW trigger_stmt
  • trigger_name：觸發器命名 ;
  • trigger_time: 觸發動作的時間 ;
  • trigger_event: 激活觸發器的語句類型 ;
  • tbl_name: 觸發器作用的表明，非臨時表 ;
  • trigger_stmt：觸發程式執行的語句 ;

表數據同步

當向用戶表 t01_user 寫入數據時，同時向 t02_back 表寫入一份備份數據。

-- 用戶備份表
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 '用戶備份';

-- 觸發器程式
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 ;

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

3、查看觸發器

查看觸發器是指資料庫中已存在的觸發器的定義、狀態、語法信息等。可以在TRIGGERS表中查看觸發器信息。

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

4、刪除觸發器

DROP TRIGGER語句可以刪除MYSQL中已經定義的觸發器，刪除觸發器的基本語法。

DROP TRIGGER [schema_name.]trigger_name

5、觸發器註意事項

  • 觸發事件

對於相同的表，相同的事件只能創建一個觸發器，比如對錶t01_user創建兩次AFTER INSERT觸發器，就會報錯。

  • 執行效率

觸發器可以減少應用端和資料庫的通信次數和業務邏輯，但是基於行觸發的邏輯，如果數據集非常大，效率會降低。

  • 事務問題

觸發器執行和原表的執行語句是否在同一個事務中，取決於觸發表的存儲引擎是否支持事務。

四、源代碼地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • .NETCore3.1中的Json互操作最全解讀-收藏級
    本文全面的介紹了 System.Text.Json 在各種場景下的用法，並比較和 Newtonsoft.Json 使用上的不同，也通過實例演示了具體的使用方法，進一步深入講解了 System.Text.Json 各種對象的原理，希望對大家在遷移到.NETCore-3.1 的時候有所幫助。 ...
  • Linux 批量安裝依賴
    1、依賴檢測失敗，xxx被xxxx需要。 當我安裝rpm 的時候，出現依賴檢測失敗。 我們可以到http://rpmfind.net/linux/rpm2html/search.php 這個網站上去搜索相關信息，查看缺少什麼依賴架包 當我們聯網的時候，或者做過架包鏡像伺服器，可以直接yum inst ...
  • Linux系統目錄結構和常用目錄主要存放內容的說明
    目錄結構圖 常用目錄 /: 根目錄 一般根目錄下只存放目錄，在 linux 下有且只有一個根目錄，所有的東西都是從這裡開始 當在終端里輸入 /home，其實是在告訴電腦，先從 /（根目錄）開始，再進入到 home 目錄 /bin目錄和/usr/bin目錄 主要放置可執行二進位文件的目錄，如常用的命令 ...
  • 隨機定時修改密碼change_passwd.sh
    change_passwd.sh 定時任務 crontab -e ...
  • elasticsearch學習（1）
    安裝 elasticsearch ，操作elasticsearch的工具kibana， (1)在kibana中輸入GET _cluster/health查看es的健康狀況(2)在kibana中輸入 GET /_cat/health?v 查看es具體信息 epoch timestamp cluster ...
  • 離線安裝PostgreSQL11.6
    因為客戶最近有一臺CentOS7的虛擬機，但是沒有聯網，需要安裝離線安裝PostgreSQL 1、首先去官網下載離線安裝包 https://www.postgresql.org/download/ 說明：可以點擊相應系統下載，也可以點擊左邊導航source下載tar.gz 選擇網頁下端的 我此次安裝 ...
  • MySQL入門(四)
    1. 集群搭建之主從複製 MySQL主從複製是一個非同步的複製過程，主庫發送更新事件到從庫，從庫讀取更新記錄，並執行更新記錄，使得從庫的內容與主庫保持一致。 1.1 主伺服器的配置 1.1.1 第一步：修改my.conf文件： 在[mysqld]段下添加： #啟用二進位日誌 log-bin=mysql ...
  • MySQL基礎入門使用和命令的使用
    資料庫瞭解 概念 資料庫就是一種特殊的文件，其中存儲著需要的數據 一個資料庫可以有多張表 MySQL是一種關係型資料庫 具有關聯性數據的就是關係型資料庫 MySQL是一種軟體可以用來創建mysql資料庫 MySQL也是C/S構架（底層TCP） MySQL客戶端 客戶端連接服務端使用TCP協議連接 使 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類