一. 函數(shù)
Mysql中的函數(shù)主要分為四類：字符串函數(shù)、數(shù)值函數(shù)、日期函數(shù)、流程函數(shù)
1. 字符串函數(shù)
常用函數(shù)如下：
| 函數(shù) | 功能 |
|---|
| CONCAT(S1, S2, ……Sn) | 字符串拼接，將S1，S2，…..Sn拼接成一個(gè)字符串 |
| LOWER(str) | 將字符串str全部轉(zhuǎn)為小寫 |
| UPPER(str) | 將字符串str全部轉(zhuǎn)為大寫 |
| LPAD(str, n, pad) | 左填充，用字符串pad對(duì)str的左邊進(jìn)行填充，達(dá)到n個(gè)字符串長(zhǎng)度 |
| RPAD(str, n, pad) | 右填充，用字符串pad對(duì)str的右邊進(jìn)行填充，達(dá)到n個(gè)字符串長(zhǎng)度 |
| TRIM(str) | 去掉字符串頭部和尾部的空格 |
| SUBSTRING(str, start, len) | 返回字符串str從start位置起的len個(gè)長(zhǎng)度的字符串 |
(1).案例1：將yun3k和com進(jìn)行拼接
1select concat('yun3k', 'com');
(2). 案例2：將YUN3k_com全部轉(zhuǎn)為小寫
1select lower('YUN3k_com');
(3). 案例3：將yun3k_com全部轉(zhuǎn)為大寫
1select upper('yun3k_com');
(4). 案例4：將yun3k左邊不滿足10位的全部填充'a'
1select lpad('yun3k', 10, 'a');
(5). 案例5：將yun3k右邊不滿足10位的全部填充'b'
1select rpad('yun3k', 10, 'b');
(6). 去除" yun3k com "的首尾空格
1select trim(' yun3k com ');
(7). 截取yun3k_com的前5位字符串
1select substring('yun3k_com', 1, 5);
(8). 將yun3k_emp表中的員工工號(hào)統(tǒng)一變?yōu)?位數(shù)，不足8位數(shù)的全部在前面補(bǔ)6
1update yun3k_emp set workno = lpad(workno, 8, '6');
2. 數(shù)值函數(shù)
常見(jiàn)函數(shù)如下：
| 函數(shù) | 功能 |
|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x, y) | 返回x/y的模 |
| RAND() | 返回0～1內(nèi)的隨機(jī)數(shù) |
| ROUND(x, y) | 求參數(shù)x的四舍五入的值，保留y位小數(shù) |
(1). 案例1：1.1向上取整
1select ceil(1.1);
(2). 案例2：1.9向下取整
1select floor(1.9);
(3). 案例3：7與4取模
1select mod(7,4);
(4).案例4：獲取隨機(jī)小數(shù)
1select rand();
(5). 案例5：2.334四舍五入保留2位小數(shù)
1select round(2.324,2);
(6). 案例6：生成一個(gè)六位數(shù)的隨機(jī)驗(yàn)證碼
思路：獲取隨機(jī)數(shù)可以通過(guò)rand()函數(shù)，但是獲取出來(lái)的隨機(jī)數(shù)是在0-1之間，所以可以在其基礎(chǔ)上乘以1000000，然后舍棄小數(shù)部分，如果長(zhǎng)度不足6位，補(bǔ)0
1select lpad(round(rand() * 1000000 , 0), 6, '0');
3. 日期函數(shù)
常見(jiàn)日期函數(shù)如下：
| 函數(shù) | 功能 |
|---|
| CURDATE() | 返回當(dāng)前日期 |
| CURTIME() | 返回當(dāng)前時(shí)間 |
| NOW() | 返回當(dāng)前日期和時(shí)間 |
| YEAR(date) | 獲取指定date的年份 |
| MONTH(date) | 獲取指定date的月份 |
| DAY(date) | 獲取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一個(gè)日期/時(shí)間值加上一個(gè)時(shí)間間隔expr后的時(shí)間值 |
| DATEDIFF(date1, date2) | 返回起始時(shí)間date1和結(jié)束時(shí)間date2之間的天數(shù) |
(1).案例1：獲取當(dāng)前日期
1select curdate();
(2). 案例2：獲取當(dāng)前時(shí)間
1select curtime();
(3).案例3：獲取當(dāng)前日期和時(shí)間
1select now();
(4). 案例4：獲取當(dāng)前年、月、日
1select YEAR(now()), MONTH(now()), DAY(now());
(5). 案例5：當(dāng)前時(shí)間分別增加50年，50月，50日
1select date_add(now(), INTERVAL 50 YEAR),
date_add(now(), INTERVAL 50 MONTH),
date_add(now(), INTERVAL 50 DAY);
(6). 案例6：獲取當(dāng)前時(shí)間與”2022-06-30”日期的差值
1select datediff(now(), '2022-06-30');
(7). 案例7：查詢yun3k_emp表中所有員工的入職天數(shù)，并根據(jù)入職天數(shù)倒序排序
1select name, datediff(curdate(), entrydate) as 'entrydays' from yun3k_emp
order by entrydays desc;
4. 流程函數(shù)
常見(jiàn)流程函數(shù)如下：
| 函數(shù) | 功能 |
|---|
| IF(value, t, f) | 如果value位true，則返回t，否則返回f |
| IFNULL(vales1, value2) | 如果value不為空，返回value1，否則返回value2 |
| CASE WHEN [val1] THEN [resl] … ELSE [default] END | 如果val1為true，返回res1，… 否則返回default默認(rèn)值 |
| CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END | 如果expr的值等于val1，返回res1，… 否則返回fault默認(rèn)值 |
(1). 案例1：if使用
1select if(false, 'Ok', 'Error');
(2). 案例2：ifnull使用
1select ifnull('Ok','Default');
2
3select ifnull('','Default');
4
5select ifnull(null,'Default');
(3). 案例3：查詢yun3k_emp表的員工姓名和工作地址(北京/上海 ---> 一線城市，其他 ---> 二線城市)
1select name, (
2 case workaddress when '北京'
3 then '一線城市' when '上海'
4 then '一線城市'
5 else '二線城市' end
6) as '工作地址' from yun3k_emp;
(4). 案例4：查詢yun3k_emp表中年齡為40以下的顯示為青年，年齡為40-60顯示為中年，年齡60歲以上顯示為老年
1select id, age, (case
2 when age < 40 then '青年'
3 when age >=40 and age <=60 then '中年'
4 else '老年' end
5) as "年齡" from yun3k_emp;
二. 約束
Mysql中的約束主要分類以下幾類：
| 約束 | 描述 | 關(guān)鍵字 |
|---|
| 非空約束 | 限制該字段的數(shù)據(jù)不能為null | NOT NULL |
| 唯一約束 | 保證該字段的所有數(shù)據(jù)都是唯一、不重復(fù)的 | UNIQUE |
| 主鍵約束 | 主鍵是一行數(shù)據(jù)的唯一標(biāo)識(shí)，要求非空且唯一 | PRIMARY KEY |
| 默認(rèn)約束 | 保存數(shù)據(jù)時(shí)，如果未指定該字段的值，則采用默認(rèn)值 | DEFAULT |
| 檢查約束(8.0.16版本之后) | 保證字段值滿足某一個(gè)條件 | CHECK |
| 外鍵約束 | 用來(lái)讓兩張表的數(shù)據(jù)之間建立連接，保證數(shù)據(jù)的一致性和完整性 | FOREIGN KEY |
注：約束是作用于表中字段上的，可以在創(chuàng)建表/修改表的時(shí)候添加約束
1. 非空約束、唯一約束、主鍵約束、默認(rèn)約束、檢查約束
(1). 案例1：根據(jù)需求，完成表結(jié)構(gòu)的創(chuàng)建
| 字段名 | 字段含義 | 字段類型 | 約束條件 | 約束關(guān)鍵字 |
|---|
| id | 唯一標(biāo)識(shí) | int | 主鍵并且自動(dòng)增長(zhǎng) | PRIMARY KEY,AUTO_INCREMENT |
| name | 姓名 | varchar(10) | 不為空并且唯一 | NOT NULL, UNIQUE |
| age | 年齡 | int | 大于0并且小于等于120 | CHECH |
| status | 狀態(tài) | char(1) | 如果沒(méi)有指定該值，默認(rèn)為1 | DEFAULT |
| gender | 性別 | char(1) | 無(wú) |
|
1CREATE TABLE yun3k_user(
2 id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一標(biāo)識(shí)',
3 name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
4 age int check (age > 0 && age <= 120) COMMENT '年齡' ,
5 status char(1) default '1' COMMENT '狀態(tài)',
6 gender char(1) COMMENT '性別'
7);
2. 外鍵約束
(1). 介紹
用來(lái)讓兩張表的數(shù)據(jù)之間建立連接，從而保證數(shù)據(jù)的一致性和完整性。
我們來(lái)看一個(gè)例子：
左側(cè)的emp表是員工表，里面存儲(chǔ)員工的基本信息，包含員工的ID、姓名、年齡、職位、薪資、入職日期、上級(jí)主管ID、部門ID，在員工的信息中存儲(chǔ)的是部門的ID dept_id，而這個(gè)部門的ID是關(guān)聯(lián)的部門表dept的主鍵id，那emp表的dept_id就是外鍵,關(guān)聯(lián)的是另一張表的主鍵。
準(zhǔn)備數(shù)據(jù)
1create table dept(
2 id int auto_increment comment 'ID' primary key,
3 name varchar(50) not null comment '部門名稱'
4)comment '部門表';
5
6INSERT INTO dept (id, name)
7VALUES (1, '研發(fā)部'), (2, '市場(chǎng)部'),(3, '財(cái)務(wù)部'),
8(4, '銷售部'), (5, '總經(jīng)辦');
9
10create table emp(
11 id int auto_increment comment 'ID' primary key,
12 name varchar(50) not null comment '姓名',
13 age int comment '年齡',
14 job varchar(20) comment '職位',
15 salary int comment '薪資',
16 entrydate date comment '入職時(shí)間',
17 managerid int comment '直屬領(lǐng)導(dǎo)ID',
18 dept_id int comment '部門ID'
19)comment '員工表';
20
21INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
22VALUES (1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),
23(2, '張無(wú)忌', 20, '項(xiàng)目經(jīng)理',12500, '2005-12-05', 1,1),
24(3, '楊逍', 33, '開(kāi)發(fā)', 8400,'2000-11-03', 2,1),
25(4, '韋一笑', 48, '開(kāi) 發(fā)',11000, '2002-02-05', 2,1),
26(5, '常遇春', 43, '開(kāi)發(fā)',10500, '2004-09-07', 3,1),
27(6, '小昭', 19, '程 序員鼓勵(lì)師',6600, '2004-10-12', 2,1);
接下來(lái)，我們可以做一個(gè)測(cè)試，刪除id為1的部門信息。
結(jié)果，我們看到刪除成功，而刪除成功之后，部門表不存在id為1的部門，而emp表中還有很多員工關(guān)聯(lián)的是id為1的部門，此時(shí)就出現(xiàn)了數(shù)據(jù)的不完整性。而要想解決這個(gè)問(wèn)題就得通過(guò)數(shù)據(jù)庫(kù)的外鍵約束。
(2). 語(yǔ)法
添加外鍵
1CREATE TABLE 表名(
2 字段名 數(shù)據(jù)類型,
3 ...
4 [CONSTRAINT] [外鍵名稱] FOREIGN KEY (外鍵字段名)
5 REFERENCES 主表 (主表列名)
6);
1ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名) ;
案例1：為emp表的dept_id字段添加外鍵約束，關(guān)聯(lián)dept表的主鍵id。
1alter table emp add constraint fk_emp_dept_id
foreign key (dept_id) references dept (id);
添加外建約束之后，我們?cè)俚絛ept表(父表)刪除id為1的記錄，此時(shí)將會(huì)報(bào)錯(cuò)，不能刪除或更新父表記錄。
刪除外建
1ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;
案例1：刪除emp表的外鍵fk_emp_dept_id。
1alter table emp drop foreign key fk_emp_dept_id;
刪除/更新行為
添加了外鍵之后，再刪除父表數(shù)據(jù)時(shí)產(chǎn)生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:
| 行為 | 說(shuō)明 |
|---|
| NO ACTION | 當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有則不允許刪除/更新。（與RESTRICT一致）默認(rèn)行為 |
| RESTRICT | 當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有則不允許刪除/更新。（與NO ACTION一致）默認(rèn)行為 |
| CASCADE | 當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有，則也刪除/更新外鍵在子表中的記錄。 |
| SET NULL | 當(dāng)在父表中刪除對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有則設(shè)置子表中該外建值為null（這就要求該外鍵允許取null）。 |
| SET DEFAULT | 父表有變更時(shí)，子表將外鍵列設(shè)置成一個(gè)默認(rèn)的值（Innodb不支持）。 |
語(yǔ)句如下：
1ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段)
REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
案例1：CASCAED使用
1alter table emp add constraint fk_emp_dept_id foreign key (dept_id)
2references dept(id) on update cascade on delete cascade;
修改父表(dept)id為1的記錄，將id改為6
1update dept set id = 6 where id = 1;
我們發(fā)現(xiàn)，原來(lái)在子表中dept_id值為1的記錄，現(xiàn)在也變成了6，這就是cascade級(jí)聯(lián)的效果。
刪除父表id為6的記錄
1delete from dept where id = 6;
我們發(fā)現(xiàn)，父表的數(shù)據(jù)刪除成功了，但是子表中關(guān)聯(lián)的記錄也被級(jí)聯(lián)刪除了。
案例2：SET NULL使用
在進(jìn)行測(cè)試之前，我們先需要?jiǎng)h除上面建立的外鍵 fk_emp_dept_id。然后再通過(guò)數(shù)據(jù)腳本，將emp、dept表的數(shù)據(jù)恢復(fù)了。
1alter table emp drop foreign key fk_emp_dept_id;
2
3alter table emp add constraint fk_emp_dept_id foreign key (dept_id)
4references dept(id) on update set null on delete set null;
接下來(lái)，我們刪除父表(dept)id為5的數(shù)據(jù)，發(fā)現(xiàn)父表(dept)的記錄是可以正常刪除的，父表(dept)的數(shù)據(jù)刪除之后，再打開(kāi)子表emp，我們發(fā)現(xiàn)子表emp的dept_id字段原來(lái)dept_id為5的數(shù)據(jù)，現(xiàn)在都置為NULL了。
1delete from dept where id = 5;
這就是SET NULL這種刪除/更新行為的效果
更多mysql學(xué)習(xí)請(qǐng)關(guān)注微信公眾號(hào)”云哥技術(shù)yun3k”，回復(fù)”mysql學(xué)習(xí)”，免費(fèi)領(lǐng)取mysql全套學(xué)習(xí)資料。
