一. 函數

Mysql中的函數主要分為四類：字元串函數、數值函數、日期函數、流程函數

1. 字元串函數

常用函數如下：

(1).案例1：將yun3k和com進行拼接

select concat('yun3k', 'com');

(2). 案例2：將YUN3k_com全部轉為小寫

select lower('YUN3k_com');

(3). 案例3：將yun3k_com全部轉為大寫

select upper('yun3k_com');

(4). 案例4：將yun3k左邊不滿足10位的全部填充'a'

select lpad('yun3k', 10, 'a');

(5). 案例5：將yun3k右邊不滿足10位的全部填充'b'

select rpad('yun3k', 10, 'b');

(6). 去除" yun3k com "的首尾空格

select trim(' yun3k com  ');

(7). 截取yun3k_com的前5位字元串

select substring('yun3k_com', 1, 5);

(8). 將yun3k_emp表中的員工工號統一變為8位數，不足8位數的全部在前面補6

update yun3k_emp set workno = lpad(workno, 8, '6');

2. 數值函數

常見函數如下：

(1). 案例1：1.1向上取整

select ceil(1.1);

(2). 案例2：1.9向下取整

select floor(1.9);

(3). 案例3：7與4取模

select mod(7,4);

(4).案例4：獲取隨機小數

select rand();

(5). 案例5：2.334四捨五入保留2位小數

select round(2.324,2);

(6). 案例6：生成一個六位數的隨機驗證碼

思路：獲取隨機數可以通過rand()函數，但是獲取出來的隨機數是在0-1之間，所以可以在其基礎上乘以1000000，然後捨棄小數部分，如果長度不足6位，補0

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

3. 日期函數

常見日期函數如下：

(1).案例1：獲取當前日期

select curdate();

(2). 案例2：獲取當前時間

select curtime();

(3).案例3：獲取當前日期和時間

select now();

(4). 案例4：獲取當前年、月、日

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

(5). 案例5：當前時間分別增加50年，50月，50日

select date_add(now(), INTERVAL 50 YEAR), date_add(now(), INTERVAL 50 MONTH), date_add(now(), INTERVAL 50 DAY);

(6). 案例6：獲取當前時間與”2022-06-30”日期的差值

select datediff(now(), '2022-06-30');

(7). 案例7：查詢yun3k_emp表中所有員工的入職天數，並根據入職天數倒序排序

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

4. 流程函數

常見流程函數如下：

(1). 案例1：if使用

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

(2). 案例2：ifnull使用

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

select ifnull('','Default');

select ifnull(null,'Default');

(3). 案例3：查詢yun3k_emp表的員工姓名和工作地址(北京/上海 ---> 一線城市，其他 ---> 二線城市)

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

(4). 案例4：查詢yun3k_emp表中年齡為40以下的顯示為青年，年齡為40-60顯示為中年，年齡60歲以上顯示為老年

select id, age, (case 
	when age < 40 then '青年' 
	when age >=40 and age <=60 then '中年' 
	else '老年' end
) as "年齡" from yun3k_emp;

二. 約束

Mysql中的約束主要分類以下幾類：

註：約束是作用於表中欄位上的，可以在創建表/修改表的時候添加約束

1. 非空約束、唯一約束、主鍵約束、預設約束、檢查約束

(1). 案例1：根據需求，完成表結構的創建

CREATE TABLE yun3k_user( 
	id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一標識', 
	name varchar(10) NOT NULL UNIQUE COMMENT '姓名' , 
	age int check (age > 0 && age <= 120) COMMENT '年齡' , 
	status char(1) default '1' COMMENT '狀態', 
	gender char(1) COMMENT '性別' 
);

2. 外鍵約束

(1). 介紹

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

我們來看一個例子：

左側的emp表是員工表，裡面存儲員工的基本信息，包含員工的ID、姓名、年齡、職位、薪資、入職日期、上級主管ID、部門ID，在員工的信息中存儲的是部門的ID dept_id，而這個部門的ID是關聯的部門表dept的主鍵id，那emp表的dept_id就是外鍵,關聯的是另一張表的主鍵。

準備數據

create table dept( 
	id int auto_increment comment 'ID' primary key, 
	name varchar(50) not null comment '部門名稱' 
)comment '部門表'; 

INSERT INTO dept (id, name) 
VALUES (1, '研發部'), (2, '市場部'),(3, '財務部'), 
(4, '銷售部'), (5, '總經辦'); 

create table emp( 
	id int auto_increment comment 'ID' primary key, 
	name varchar(50) not null comment '姓名', 
	age int comment '年齡', 
	job varchar(20) comment '職位', 
	salary int comment '薪資', 
	entrydate date comment '入職時間', 
	managerid int comment '直屬領導ID', 
	dept_id int comment '部門ID' 
)comment '員工表'; 

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) 
VALUES (1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),
(2, '張無忌', 20, '項目經理',12500, '2005-12-05', 1,1), 
(3, '楊逍', 33, '開發', 8400,'2000-11-03', 2,1),
(4, '韋一笑', 48, '開 發',11000, '2002-02-05', 2,1), 
(5, '常遇春', 43, '開發',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程 序員鼓勵師',6600, '2004-10-12', 2,1);

接下來，我們可以做一個測試，刪除id為1的部門信息。

結果，我們看到刪除成功，而刪除成功之後，部門表不存在id為1的部門，而emp表中還有很多員工關聯的是id為1的部門，此時就出現了數據的不完整性。而要想解決這個問題就得通過資料庫的外鍵約束。

(2). 語法

1. 添加外鍵

CREATE TABLE 表名( 
	欄位名 數據類型, 
	... 
	[CONSTRAINT] [外鍵名稱] FOREIGN KEY (外鍵欄位名) 
	REFERENCES 主表 (主表列名) 
);

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位名) REFERENCES 主表 (主表列名) ;

案例1：為emp表的dept_id欄位添加外鍵約束，關聯dept表的主鍵id。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

添加外建約束之後，我們再到dept表(父表)刪除id為1的記錄，此時將會報錯，不能刪除或更新父表記錄。

2. 刪除外建

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

案例1：刪除emp表的外鍵fk_emp_dept_id。

alter table emp drop foreign key fk_emp_dept_id;

3. 刪除/更新行為

添加了外鍵之後，再刪除父表數據時產生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:

語句如下：

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

案例1：CASCAED使用

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

修改父表(dept)id為1的記錄，將id改為6

update dept set id = 6 where id = 1;

我們發現，原來在子表中dept_id值為1的記錄，現在也變成了6，這就是cascade級聯的效果。

刪除父表id為6的記錄

delete from dept where id = 6;

我們發現，父表的數據刪除成功了，但是子表中關聯的記錄也被級聯刪除了。

案例2：SET NULL使用

在進行測試之前，我們先需要刪除上面建立的外鍵 fk_emp_dept_id。然後再通過數據腳本，將emp、dept表的數據恢復了。

alter table emp drop foreign key fk_emp_dept_id;

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

接下來，我們刪除父表(dept)id為5的數據，發現父表(dept)的記錄是可以正常刪除的，父表(dept)的數據刪除之後，再打開子表emp，我們發現子表emp的dept_id欄位原來dept_id為5的數據，現在都置為NULL了。

delete from dept where id = 5;

這就是SET NULL這種刪除/更新行為的效果

更多mysql學習請關註微信公眾號”雲哥技術yun3k”，回覆”mysql學習”，免費領取mysql全套學習資料。