【 MYSQL | 基礎篇 函數與約束 】

摘要：本文介紹數據庫中的函數與約束，函數含字符串、數值、日期、流程四類，可實現字符串處理、數值計算等需求。約束分六類，重點講外鍵約束的語法、刪除更新行為，保證數據正確完整。

思維導圖

1. 函數

函數是指一段可以直接被另一段程序調用的程序或代碼。這意味著，在 MySQL 中已經為我們提供了這一段程序或代碼，我們只需在合適的業務場景調用對應的函數，即可完成對應的業務需求。

MySQL 中的函數主要分為四類：字符串函數、數值函數、日期函數、流程函數? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

1.1 字符串函數

MySQL 中內置了諸多字符串函數，常用的幾個如下：

函數功能
CONCAT(S1,S2,...Sn)字符串拼接，將 S1、S2、...、Sn 拼接成一個字符串
LOWER(str)將字符串 str 全部轉為小寫
UPPER(str)將字符串 str 全部轉為大寫
LPAD(str,n,pad)左填充，用字符串 pad 對 str 的左邊進行填充，使總長度達到 n 個字符
RPAD(str,n,pad)右填充，用字符串 pad 對 str 的右邊進行填充，使總長度達到 n 個字符
TRIM(str)去掉字符串頭部和尾部的空格
SUBSTRING(str,start,len)返回從字符串 str 的 start 位置起，長度為 len 的子字符串

使用方法：select? 函數

演示如下：

A. concat：字符串拼接? ???

select concat('Hello' , ' MySQL');

B. lower：全部轉小寫

select lower('Hello');

C. upper：全部轉大寫

select upper('Hello');

D. lpad：左填充

select lpad('01', 5, '-');

E. rpad：右填充

select rpad('01', 5, '-');

F. trim：去除空格

select trim(' Hello MySQL ');

G. substring：截取子字符串??

select substring('Hello MySQL',1,5);

案例

由于業務需求變更，企業員工的工號統一改為 5 位數，目前不足 5 位數的需在前面補 0（例如：1 號員工的工號應改為 00001）。

update emp set workno = lpad(workno, 5, '0');

處理完畢后，具體的數據如下：

idworknonamegenderageidcardworkaddressentrydate
100001抖音號：1332402852女20123456789012345678北京2000-01-01
200002張無忌男18123456789012345670北京2005-09-01
300003韋一笑男38123456789712345670上海2005-08-01
400004趙敏女18123456757123845670北京2009-12-01
500005小昭女16123456769012345678上海2007-07-01
600006楊逍男2812345678931234567X北京2006-01-01
700007抖音號：1232402856男40123456789212345678北京2005-05-01
800008黛綺絲女38123456157123645670天津2015-05-01
900009范涼涼女45123156789012345678北京2010-04-01
1000010陳友諒男53123456789012345670上海2011-01-01
1100011張士誠男55123567897123465670江蘇2015-05-01
1200012常遇奇男32123446757152345678北京2004-02-01
1300013張三豐男88123656789012345678江蘇2020-11-01
1400014滅絕女65123456719012345670西安2019-05-01
1500015胡青牛男7012345674971234567X西安2018-04-01
1600016周芷若女18null北京2012-06-01

1.2 數值函數

常見的數值函數如下：

函數功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x,y)返回 x 除以 y 的余數（模）
RAND() ?返回 0~1 范圍內的隨機數
ROUND(x,y) ?對參數 x 進行四舍五入，保留 y 位小數

演示如下：

A. ceil：向上取整

select ceil(1.1);

B. floor：向下取整

select floor(1.9);

C. mod：取模

select mod(7,4);

D. rand：獲取隨機數

select rand();

E. round：四舍五入

select round(2.344,2);

案例?????

通過數據庫的函數，生成一個六位數的隨機驗證碼。

思路：通過 rand() 函數可獲取 0~1 之間的隨機數，將其乘以 1000000 后，舍棄小數部分，若結果長度不足 6 位，則在前面補 0。

select lpad(round(rand()*1000000 , 0), 6, '0');

1.3 日期函數

常見的日期函數如下：

函數功能
CURDATE()返回當前日期（格式：YYYY-MM-DD）
CURTIME()返回當前時間（格式：HH:MM:SS）
NOW()返回當前日期和時間（格式：YYYY-MM-DD HH:MM:SS）
YEAR(date)獲取指定 date 的年份
MONTH(date)獲取指定 date 的月份
DAY(date)獲取指定 date 的日期
DATE_ADD(date, INTERVAL expr type)返回一個日期 / 時間值，為指定 date 加上一個時間間隔 expr 后的結果
DATEDIFF(date1,date2)返回起始時間 date1 和結束時間 date2 之間的天數（date1 - date2）

演示如下：

A. curdate：當前日期

select curdate();

B. curtime：當前時間

select curtime();

C. now：當前日期和時間

select now();

D. YEAR、MONTH、DAY：獲取當前年、月、日

select YEAR(now());select MONTH(now());select DAY(now());

E. date_add：增加指定的時間間隔

select date_add(now(), INTERVAL 70 YEAR );

F. datediff：獲取兩個日期相差的天數

select datediff('2021-10-01', '2021-12-01');

案例

查詢所有員工的入職天數，并根據入職天數倒序排序。

思路：入職天數 = 當前日期 - 入職日期，可通過 datediff 函數實現。

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

1.4 流程函數

流程函數是一類常用函數，可在 SQL 語句中實現條件篩選，從而提升語句效率。

函數功能
IF(value, t, f)若 value 為 true，則返回 t；否則返回 f
IFNULL(value1, value2)若 value1 不為空，則返回 value1；否則返回 value2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END若 val1 為 true，則返回 res1；... 若所有條件都不滿足，則返回 default 默認值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END若 expr 的值等于 val1，則返回 res1；... 若所有條件都不滿足，則返回 default 默認值

演示如下：

A. if

select if(false, 'Ok', 'Error');

B. ifnull

select ifnull('Ok','Default');select ifnull('','Default');select ifnull(null,'Default');

C. case when then else end

需求：查詢 emp 表的員工姓名和工作地址（北京 / 上海 → 一線城市，其他 → 二線城市）

select
name,
( case workaddress when '北京' then '一線城市' when '上海' then '一線城市' else '二線城市' end ) as '工作地址'
from emp;

案例

先創建并插入數據到學員成績表 score：

create table score(id int comment 'ID',name varchar(20) comment '姓名',math int comment '數學',english int comment '英語',chinese int comment '語文') comment '學員成績表';insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

查詢學員成績，并顯示各科成績等級（≥85 為優秀，≥60 為及格，否則為不及格）：

select id,name,(case when math >= 85 then '優秀' when math >=60 then '及格' else '不及格' end ) '數學',(case when english >= 85 then '優秀' when english >=60 then '及格' else '不及格' end ) '英語',(case when chinese >= 85 then '優秀' when chinese >=60 then '及格' else '不及格' end ) '語文'from score;

函數場景回顧

  1. 數據庫中存儲入職日期（如 2000-01-01），如何快速計算出入職天數？

    答案：datediff 函數。

  2. 數據庫中存儲學生分數值（如 98、75），如何快速判定分數等級？

    答案：case ... when ... 流程函數。

2. 約束

2.1 概述

  • 概念：約束是作用于表中字段上的規則，用于限制存儲在表中的數據。

  • 目的：保證數據庫中數據的正確、有效性和完整性。

分類：

約束描述關鍵字
非空約束限制該字段的數據不能為 nullNOT NULL
唯一約束保證該字段的所有數據都是唯一、不重復的UNIQUE
主鍵約束?主鍵是一行數據的唯一標識，要求非空且唯一PRIMARY KEY
默認約束保存數據時，如果未指定該字段的值，則采用默認值DEFAULT
檢查約束保證字段值滿足某一個條件CHECK
外鍵約束?用來讓兩張表的數據之間建立連接，保證數據的一致性和完整性FOREIGN KEY

注意：約束是作用于表中字段上的，可以在創建表 / 修改表的時候添加約束。

2.2 外鍵約束

2.2.1 介紹

外鍵：用來讓兩張表的數據之間建立連接，從而保證數據的一致性和完整性。

2.2.2 語法

1). 添加外鍵

-- 方式1：創建表時添加
?
CREATE TABLE 表名(
?
字段名 數據類型,
?
[CONSTRAINT] [外鍵名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名)
?
);
?
-- 方式2：修改表時添加
?
ALTER TABLE 表名 ADD CONSTRAINT [外鍵名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名);

???????? ? ?多了一個藍色小鑰匙?

2). 刪除外鍵

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

案例：刪除 emp 表的外鍵 fk_emp_dept_id

alter table emp drop foreign key fk_emp_dept_id;

2.3.3 刪除 / 更新

添加外鍵后，刪除 / 更新父表數據時產生的約束行為，稱為刪除 / 更新行為，具體如下：

行為說明
NO ACTION當在父表中刪除 / 更新對應記錄時，先檢查該記錄是否有對應外鍵，若有則不允許刪除 / 更新（與 RESTRICT 一致），默認行為
RESTRICT當在父表中刪除 / 更新對應記錄時，先檢查該記錄是否有對應外鍵，若有則不允許刪除 / 更新（與 NO ACTION 一致），默認行為
CASCADE當在父表中刪除 / 更新對應記錄時，先檢查該記錄是否有對應外鍵，若有則同步刪除 / 更新子表中關聯的外鍵記錄
SET NULL當在父表中刪除對應記錄時，先檢查該記錄是否有對應外鍵，若有則將子表中該外鍵值設為 null（需外鍵允許取 null）
SET DEFAULT父表有變更時，子表將外鍵列設為默認值（Innodb 不支持）

具體語法

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

演示

NO ACTION 是默認行為，已通過前面語法測試，此處演示 CASCADE 和 SET NULL 兩種行為：

1). CASCADE

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

A. 修改父表數據：將 dept 表中 id 為 1 的記錄修改為 6。

結果：子表 emp 中 dept_id 值為 1 的記錄，同步變為 6，體現 CASCADE 級聯更新效果。

B. 刪除父表數據：刪除 dept 表中 id 為 6 的記錄。

結果：父表數據刪除成功，子表中關聯的記錄也被級聯刪除，體現 CASCADE 級聯刪除效果。
注意：一般業務系統中，不會修改表的主鍵值。

2). SET NULL

先刪除已建立的外鍵 fk_emp_dept_id，再恢復 emp、dept 表數據。

執行以下語句添加外鍵并設置 SET NULL 行為

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

大功告成！

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/pingmian/94859.shtml
繁體地址，請注明出處：http://hk.pswp.cn/pingmian/94859.shtml
英文地址，請注明出處：http://en.pswp.cn/pingmian/94859.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

Oracle 數據庫性能調優：從瓶頸診斷到精準優化之道

Oracle 數據庫性能調優：從瓶頸診斷到精準優化之道

引言：性能優化的本質在當今數據驅動的時代，數據庫性能直接關系到企業的運營效率和用戶體驗。Oracle 作為全球領先的關系型數據庫管理系統，承載著眾多企業的核心業務。然而，隨著數據量的增長和業務復雜度的提升，數據庫性…
閱讀更多...
楊校老師競賽課堂之C++語言GESP一級筆記

楊校老師競賽課堂之C++語言GESP一級筆記

考試大綱 GESP一級考試大綱 計算機基礎與編程環境 計算機歷史 變量的定義與使用 基本數據類型（整型、浮點型、字符型、布爾型） 輸入與輸出（cin與cout、scanf與printf） 基本運算（算術運算、關系運算、邏輯運算&am…
閱讀更多...
操作系統-管程

操作系統-管程

1. 為什么需要管程？—— 信號量 (Semaphore) 的困境在理解管程之前，你必須先知道它要解決什么問題。之前，我們使用信號量 (Semaphore) 來實現進程/線程間的同步與互斥。雖然信號量功能強大，但它存在兩個主要問題：編程復…
閱讀更多...
日志的實現

日志的實現

目錄 日志與策略模式 Log.hpp class LogStrategy基類 class ConsoleLogStrategy派生類 classFileLogStrategy派生類 日志等級 獲得時間戳 localtime_r函數詳解 函數原型 struct tm結構的指針 Logger類(重點) class LogMessage 日志信息類 std::stringstream 用法 重…
閱讀更多...
【論文閱讀】Sparse4D v2：Recurrent Temporal Fusion with Sparse Model

【論文閱讀】Sparse4D v2：Recurrent Temporal Fusion with Sparse Model

標題： Sparse4D v2：Recurrent Temporal Fusion with Sparse Model 作者： Xuewu Lin, Tianwei Lin, Zixiang Pei, Lichao Huang, Zhizhong Su motivation 在v1的基礎上，作者發現長時序有更好的效果，但v1的計算量太大&am…
閱讀更多...
構建免費的音視頻轉文字工具：支持多語言的語音識別項目

構建免費的音視頻轉文字工具：支持多語言的語音識別項目

在當今數字時代，音視頻內容越來越多，但如何快速將其轉換為文字一直是一個挑戰。本項目提供了一個免費的解決方案，支持將視頻和音頻文件轉換為文字，并且支持多語言識別。 一個支持中英文的音視頻轉文字工具，集成了 Vos…
閱讀更多...
【開題答辯全過程】以 基于SpringBootVue的智能敬老院管理系統為例，包含答辯的問題和答案

【開題答辯全過程】以 基于SpringBootVue的智能敬老院管理系統為例，包含答辯的問題和答案

個人簡介一名14年經驗的資深畢設內行人，語言擅長Java、php、微信小程序、Python、Golang、安卓Android等開發項目包括大數據、深度學習、網站、小程序、安卓、算法。平常會做一些項目定制化開發、代碼講解、答辯教學、文檔編寫、也懂一些降重方面的技巧。感謝大家的…
閱讀更多...
Linux 830 shell：expect,ss -ant ,while IFS=read -r line,

Linux 830 shell：expect,ss -ant ,while IFS=read -r line,

[rootsamba caozx26]# scp /home/caozx26/pub root192.168.235.3:~/ root192.168.235.3s password: /home/caozx26/pub: not a regular file [rootsamba caozx26]# ls app km nntp.sh ntp.sh until1.sh 公共 圖片 音樂 find.sh l2 ntp1.sh pub u…
閱讀更多...
???????GPT-5發布引爆爭議，奧特曼連夜回應!付費充值的Plus用戶成最大贏家？

???????GPT-5發布引爆爭議，奧特曼連夜回應!付費充值的Plus用戶成最大贏家？

摘要： GPT-5發布后，社區口碑兩極分化，從“強無敵”到“還我4o”的呼聲并存。面對技術故障和用戶質疑，OpenAI CEO薩姆奧爾特曼及團隊火速回應，公布了一系列補救措施和未來計劃。本文將帶你速覽這場風波始末，…
閱讀更多...
Python 操作 Redis 的客戶端 - Redis Stream

Python 操作 Redis 的客戶端 - Redis Stream

Python 操作 Redis 的客戶端 - Redis Stream1. Redis Stream2. Redis Commands2.1. CoreCommands.xadd() (生產端)2.2. CoreCommands.xlen() (生產端)2.3. CoreCommands.xdel() (生產端)2.4. CoreCommands.xrange() (生產端)2.5. RedisClusterCommands.delete()3. Redis Stream…
閱讀更多...
【Qt開發】按鈕類控件（一）-＞ QPushButton

【Qt開發】按鈕類控件（一）-＞ QPushButton

目錄 1 -> 什么是 PushButton？ 2 -> 相關屬性 3 -> 代碼示例 3.1 -> 帶有圖標的按鈕 3.2 -> 帶有快捷鍵的按鈕 4 -> 總結 1 -> 什么是 PushButton？ 在 Qt 框架中，QPushButton 是最基礎且最常用的按鈕控件之一&am…
閱讀更多...
Citrix 零日漏洞自五月起遭積極利用

Citrix 零日漏洞自五月起遭積極利用

安全研究員 Kevin Beaumont 披露了有關 CVE-2025-6543 的驚人細節，這是一個嚴重的 Citrix NetScaler 漏洞，在該公司發布補丁之前的幾個月里，該漏洞被積極利用作為零日攻擊。 Citrix 最初將其輕描淡寫為簡單的“拒絕服務”漏洞，但…
閱讀更多...
【系列08】端側AI：構建與部署高效的本地化AI模型 第7章：架構設計與高效算子

【系列08】端側AI：構建與部署高效的本地化AI模型 第7章：架構設計與高效算子

第7章：架構設計與高效算子 要將AI模型成功部署到端側，除了對現有模型進行壓縮和優化，更根本的方法是在設計之初就考慮其在資源受限環境下的運行效率。本章將深入探討如何設計高效的網絡架構，以及如何理解并優化常用的核心算子。高…
閱讀更多...
42-Ansible-Inventory

42-Ansible-Inventory

文章目錄Ansible基本概述手動運維時代（原始社會）自動化運維時代自動化運維工具的優勢Ansible的功能及優點Ansible的架構Ansible的執行流程安裝AnsibleAnsible配置文件生效順序Ansible inventory主機清單Ansible基于免秘鑰方式管理客戶端小結Ansible-Adho…
閱讀更多...
Go語言runtime/trace工具全面解析

Go語言runtime/trace工具全面解析

基本概念與功能 Go語言的runtime/trace是Go標準庫中內置的性能分析工具，主要用于追蹤和可視化Go程序的運行時行為。它能夠記錄程序執行期間的各種事件，包括goroutine調度、系統調用、垃圾回收(GC)、網絡I/O、鎖等待等關鍵信息。 trace工具的核心功能包括： goroutine生命周期…
閱讀更多...
Docker(自寫)

Docker(自寫)

Docker程序是跑在操作系統上的，而操作系統上又裝了各種不同版本的依賴庫和配置程序依賴環境，環境不同，程序就可能跑不起來，如果我們能將環境和程序一起打包docker就是可以將程序和環境一起打包并運行的工具軟件基礎鏡像DockerFile…
閱讀更多...
深度拆解 OpenHarmony 位置服務子系統：從 GNSS 到分布式協同定位的全鏈路實戰

深度拆解 OpenHarmony 位置服務子系統：從 GNSS 到分布式協同定位的全鏈路實戰

1. 系統概述 OpenHarmony 的“定位子系統”就是硬件服務子系統集里的 “位置服務子系統”（Location SubSystem）。它向下對接 GNSS/GPS、基站、Wi-Fi 等定位模組，向上以 標準位置 API 形式為應用提供 實時位置、軌跡、地理圍欄 等能力，并可與分布式軟總線聯動，實現 跨設備…
閱讀更多...
React Native基本用法

React Native基本用法

1，index調用registerComponent,把appName注入到React Native的根節點。 2，package.json是全局大管家，package-lock.json鎖定版本，不會手動編輯，通過install安裝 3， bebal.config.json bebal.config.json是翻…
閱讀更多...
LoraConfig target modules加入embed_tokens(64)

LoraConfig target modules加入embed_tokens(64)

LoraConfig target modules加入embed_tokens 更好且成本更低的方法 嵌入層（embedding layer）的 lora_embedding_A 和 lora_embedding_B 頭部（head）是否需加入目標模塊列表 用戶警告 解除權重綁定 解綁以后是隨機權重，怎么辦 更好且成本更低的方法 “有沒有一種更好且成本…
閱讀更多...
筆記共享平臺|基于Java+vue的讀書筆記共享平臺系統(源碼+數據庫+文檔)

筆記共享平臺|基于Java+vue的讀書筆記共享平臺系統(源碼+數據庫+文檔)

筆記共享平臺|讀書筆記共享平臺系統 目錄 基于Javavue的讀書筆記共享平臺系統 一、前言 二、系統設計 三、系統功能設計 四、數據庫設計 五、核心代碼 六、論文參考 七、最新計算機畢設選題推薦 八、源碼獲取 博主介紹：??大廠碼農|畢設布道師&#xff…
閱讀更多...
最新文章

Copyright @ 2022~2023