一. 函式

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全套學習資料，

標籤：其他

上一篇：Mysql基礎篇（一）之DDL、DML、DQL、DCL

下一篇：返回列表