---
tags: RDBMS
---
# MySQL
## 資料庫相關概念

DB: 資料庫，保存一組有組織過的數據的容器。
DBMS: 資料庫管理系統，又稱為資料庫軟體（產品），用於管理 DB 內的資料
SQL: Structured query language -  結構化查詢語言，為和 DBMS 溝通的語言
資料存取方式：資料存到表格，表格再存到資料庫（表格名稱具有唯一性）

## 登入資料庫

如何登入: mysql (--h=hostname -P=port num) - u帳號 -p密碼
如何退出: exit, quit, ctrl + c
預設 Database (1 - 3 不能刪掉):
   1. information_schema: 保存 metadata
   2. mysql: 保存用戶訊息
   3. performance_schema: 保存性能資料及參數
   4. test: 空的，可以直接建立表，或是刪除

## 查詢基本資訊

1. select database(); 查看當前所在的庫
2. desc table ; 查看該表結構和訊息
3. select version(); 查看版本

## DQL
### 語句執行順序

FROM+JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

### select 語句
查詢對象

表格欄位的字段
常數ex. 數字, 字串（要加單引號）
表達式 ex. 100*98
函數 ex. select version()

別名 as

where 子句不支援別名
group by/having/order by 支援

select last_name as 姓, firt_name as 名 from employees; (別名裡打中文不用加符號)
select last_name 姓, first_name 名 from employees;
select salary as "out put" from empolyee; (別名中間有空格才要加)

SQL 中 + 號的用意

兩個操作數都為數值時，做加法運算
其中一方為字符時，試圖將字符轉換成數值，如果轉換成功則繼續做加法運算，如果轉換失敗，則將字符轉換成 0
如果其中一方為 null，則結果必定為 null

字符連接：concat()

接在 select 後面，括弧內可以填入多個字串，用逗號分隔，後面可以用 as 取別名
如果其中一個的值為 null 結果就會是 null, 要解決這個問題，可以透過 ifnull(欄位名稱, 如果為null顯示出來的值)

### 條件查詢
語法

select 
    查詢欄位
from 
    表格名稱
where 
    篩選條件;

篩選方式

簡單條件運算子：>, <, =, !=, <>, <=, >=
邏輯運算子： &&(and), ||(or), !(not)
範例：
select * from employees where department_id < 90 or department_id > 110 or salary >15000; 
也可以寫成
select * from employees where not (department_id >= 90 and department_id <= 110) or salary > 15000;

模糊查詢

like => 

範例一：查詢員工名字內包含 a 的員工訊息
    select * from employees where last_name like '%a%';
◊ 特點：一般和通配符搭配使用(如果值包含 null, 使用 '%%' 不等於全選)
     % => 任意多個字符，包含 0 個字符
     _ => 任意單個字符
範例二：查詢員工名中第三個字符為 n, 第五個字符為 l 的員工名和工資
    select last_name, salary from employees where last_name like '__n_l%';
範例三：查詢員工名中第二個字符為_的員工名
    select last_name from employees where last_name like '_\_%';  
    (\ 為跳脫字元，也可以用 escape 自定義跳脫字元)
    select last_name from employees where last_name like '_$_%' escape '$';

between and =>

範例一：
    select  * from employees where  employee_id >= 100 and employee_id <= 120; 
    等價於  
    select * from  employees where  employee_id between 100 and 120; 
範例二：選擇工資不在 8000 到 17000 的員工姓名和工資，按工資降序
    select last_name, salary from employees where salary not between 8000 and 17000 order by salary desc;
◊ 特點：
    1. 使用 between and 可以提高語句的簡潔度
    2. 包含臨界值
    3. 兩個臨界值不能互換

in =>

◊ 含義：
    1. 判斷某字段的值是否屬於 in 列表中的某一項
    2. in 列表值的類型必須一致或兼容（值類型可以互相轉換）
    3. in 不支持通配符，意義上是用完全相等於列表中的其中一個值 (重要！)					
範例一：
    select last_name, job_id from employees where job_id in ('IT_PROT','AD_VP','AD_PRES');

is null / is not null =>

◊ 範例：查詢沒有獎金的員工名和獎金率
    select last_name, commission_pct from employees where commission_pct is null;
◊ = 不能用於判斷 null 值
◊ <=> 安全等於, 可用來判斷 null 還有一般值, 但是可讀性較差

### 排序查詢
語法：

select 查詢列表
from 表格
(where 篩選條件)
order by  排序列表  (asc / desc)

特點

asc 從低到高, desc 從高到低, 預設是 asc
order by 子句可以接單個字段, 多個字段, 表達式, 函數, 別名
一般放在查詢語句的最後面, limit 子句除外

範例

按年新高低顯示員工訊息和年薪
    select *, salary*12*(1+ifnull(commission_pct,0)) as annual_salary from employees order by annual_salary desc;

按姓名長度顯示員工訊息和工資
	select length(first_name) as 名長, first_name, salary from employees order by 名長;

查詢員工訊息，要求先按工資排序，再按員工編號排序（按照多個字段排序）
	select * from employees order by salary asc, employee_id desc;(這個情況是工資一樣的時候要再用員工編號排序)

### DQL 單行函數

#### 字符函數
* concat 拼接字符串
* upper 轉大寫
* lower 轉小寫
* length 換取參數值的位元組數(Byte)

一個英文字為一個 Byte (8 bits), 中文的話則取決於編碼
一般 utf-8 編碼下，一個中文字元佔用 3 個 位元組；
一般 gbk 編碼下，一個中文字元佔用 2 個 位元組；
詳解：
https://www.itread01.com/content/1550081539.html

* substr(str,index,range)

擷取字串
index 從 1 開始算，print 出含該 index 本身字元之後的所有東西, 有 range 的話則往後數該 range。

* instr(whole str, substr)

返回子字串第一次出現的索引，如果找不到返回 0
ex. select instr('I am Aaron','Aaron'); out 為 6 

* trim()

去除前後字元
ex. select trim('aa' from 'aaaaaaaaa張aaa國aaaaa榮aaaaaaaaaaa') as out_put; output 為   a張aaa國aaaaa榮a 

* lpad()

用指定的字元實現左填充指定字元長度
ex. select lpad('張國榮',5,'*'); output 為*****張國榮

* rpad()

用指定的字元實現右填充指定字元長度
ex. select rpad('張國榮',7,'*'); output 為張國榮*******

* replace

替換
select replace('Aaron likes Sabrina','Sabrina','Vanessa'); output為Aaron loves Vanessa 

#### 數學函數
* round

四捨五入
select round(-1.55); => -2
select round(1.567,2); =>1.57 （取到小數點後兩位）

* ceil

向上取整
返回 >= 該參數的最小整數
select ceil(-1.3); => -1

* floor

向下取整
返回 <= 該參數的最大整數
select floor(-9.99) => -10

* truncate

截斷，把指定小數點位數之後的數值全部拿掉
select truncate(1.699999,1); => 1.6

* mod

取餘
select mod(10,-3) ; => 1
等價於 select 10%3
餘數若理解有困難，可以直接帶公式：mod(a,b) : a - a/b*b * 注意：a / b 的部分，若 a,b 均為整數，結果取整 ex. mod (-10,-3) => -10 - (-10/-3) * -3 => -1

#### 日期函數
* now()
* curdate()
* curtime()
* year()
* month()
* monthname()
* str_to_date()

將字元通過指定的格式轉換成日期
select * from employees where hiredate = str_to_date('4-3 1992', '%c-%d %Y' );

* date_format()

將日期轉換成字元
select date_format(now(),'%y年%m月%d日'); 

#### 流程控制函數

if

select last_name, commission_pct, if(commission_pct is null,'沒獎金QQ','有獎金！') as 備註 from employees;
判別第一個參數，如果為真則顯示第二個參數，為假顯示第三個參數

case

用法一: 類似於 switch case, 進行等值比較 （搭配 select 時作陳述式用, then 只能接值 ; 單獨使用時 then 則接語句）

case 要判斷的字段或表達式
when 常數 1 then 要顯示的值 1 或語句 1 (語句要加上 ;)
when 常數 2 then 要顯示的值 2 或語句 2 
…
else 要顯示的值 n 或語句 n 
end  (as 別名)

select salary 原始工資, department_id,  case department_id when 30 then salary * 1.1 when 40 then salary * 1.2 when 50 then salary * 1.3 else salary end as 新工資 from employees;

用法二: 類似於多重 if 判斷 (case 後面不接東西, 用於判斷區間)
case 
when 條件1 then 要顯示的值或語句1
when 條件2 then 要顯示的值或語句2
…
else 要顯示的值n或語句n
end 
select salary, case when salary > 20000 then 'A' when salary > 15000 then 'B' when salary > 10000 then 'C' else 'D' end as 工資級別 from employees;

### DQL 分組函數

* 功能：用作統計使用，又稱為聚合函數或組函數，亦即把所有資料列整合在一起
* 分類：sum 總和, avg 平均值, max 最大值, min 最小值, count 計算個數
* 特點：
1. sum / avg 處理數值 ; max / min / count 處理任何類型的資料
2. 均忽略 null 值 => 和 null 值進行數學運算的話結果必為 null
3. 可以和 distinct 搭配進行去重運算
4. 一般使用 count(*) 來統計行數
5. 和分組函數一同查詢的字段要求是 group by後的字段（重要!!!）
6. 把聚合函數的結果依照某一個欄位去分組

簡單的使用

select sum(salary) 總和, round(avg(salary),2) 平均, max(salary) 最高, min(salary) 最低, count(salary) 個數 from employees;
select sum(distinct salary), sum(salary) from employees;
select count(distinct salary), count(salary) from employees;

> 一般 sum, avg 處理數值 / max, min 處理可排序, 有大小之分的資料

### DQL 分組查詢

語法

select 分組函數, 欄位
from 表
(where 篩選條件 -> 分組前篩選, 這裡指的是欄位) 
group by 欄位 (必須出現在上面的 select)
(order by 子句)
(having 篩選條件 -> 分組後篩選)

特點

分組查詢中的篩選條件分為兩種資料源：

1. 分組前篩選: 原始表
2. 分組後篩選: 分組後的子集

分組函式作為條件時一定放在 having 後之後，
原因是因為分組函式的結果不會在原始表內。
另外，如果能先用分組前篩選優先考慮分組前篩選（與性能有關）

group by 子句支援多欄位分組，用逗號分隔，且沒有先後順序

範例

select round(avg(salary),2) 平均薪資, department_id, job_id
from employees
where department_id is not null
group by job_id, department_id
having 平均薪資 > 10000
order by 平均薪資 desc;

### DQL 多表連接查詢 (SQL92)

#### 等值連接
常見錯誤

資料膨脹（又稱笛卡爾乘積現象）
ex. 表 1 有 m 行， 表 2 有 n 行，結果為 m*n 行
原因: 沒有添加兩張表都符合的連接條件

特點

1. 多表等值連接的結果為多表的交集部分
2. n 表連接，至少需要 n-1 個連接條件
3. 多表的順序沒有要求
4. 一般需要幫表取別名
5. 可以搭配所有子句使用

範例：三表連接

select last_name, department_name, city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
order by department_name desc;

#### 非等值連接
範例: 查詢員工薪資和薪資等級

select salary, grade_level
from employees e, job_grades g
where salary between g.`lowest_sal` and g.`highest_sal`;

#### 自連接
範例查詢每個員工的直屬主管名稱

同一張表每列資料都有對應的主管員工編號，因此用自連接的方式，假設一張表為員工表，另外一張表為主管表，透過員工編號進行等值連接。

select e.employee_id, e.last_name, m.employee_id, m.last_name
from employees e, employees m
where e.`manager_id` = m.`employee_id`;

### DQL 多表連接查詢 (SQL99)
語法

select 查詢欄位
from 表1 別名
join 表2 別名
on 連接條件
group by 分組
having 篩選條件
order by 排序列表

分類

內連接: inner (inner 可以省略，直接寫 join)
外連接:
    左外 -> left (outer)
    右外 -> right (outer)
    全外 -> full (outer)
交叉連接: cross

#### inner 之等值連接
範例：查詢部門數 > 3 之城市名及部門數量

select city, count(*)
from departments d
inner join locations l
on d.`location_id` = l.`location_id`
group by city
having count(*) > 3;

範例： 查詢員工名、部門名、職務名，並按部門名降序

select last_name, department_name, job_title from employees e
inner join departments d on e.`department_id` = d.`department_id`
inner join jobs j on e.`job_id` = j.`job_id`
order by department_name desc;

#### inner 之非等值連接
範例：查詢員工的工資級別

select salary, grade_level
from employees e 
join job_grades g 
on e.`salary` between g.`lowest_sal` and g.`highest_sal`;

#### inner 之自連接
範例：查詢員工名字和其主管名字

select e.last_name, m.last_name
from employees e
join employees m
on e.manager_id = m.employee_id;

#### 外連接
應用場景：用於查詢一個表有，另一個表沒有的紀錄
特點

1. 外連接的查詢結果為主表中的所有紀錄，如果從表有和主表匹配的紀錄，則顯示匹配的值，如果從表中沒有和主表匹配的，則顯示 null
2. 外連接查詢結果 = 內連接結果 + 主表中有而從表沒有的紀錄
3. 左外和右外交換兩個表的順序，出來的結果一樣
4. 全外連接 = 內連接結果 + 表 1 有但表 2 沒有 + 表 2 有但表 1 沒有

範例：查詢男友不在男孩表的女孩

select b.name, bo.* 
from beauty b 
left join boys bo 
on b.boyfriend_id = bo.id 
where bo.id is null;

範例：查詢沒有員工的部門

select d.department_id 
from departments d 
left outer join employees e 
on d.department_id = e.department_id 
where e.employee_id is null;

範例：查詢沒有部門的城市

select city, d.* 
from departments d 
right outer join locations l 
on d.`location_id` = l.`location_id` 
where d.`department_id` is null;

##### 交叉連接
SQL99語法中表示笛卡爾乘積的語法

### 子查詢
概念：出現在其他語句內部的 select 語句，稱為子查詢或內查詢。反之為主查詢或外查詢
分類：
1. 按子查詢出現的位置

   select 後面（支援標量子查詢）
   from 後面 （支援表子查詢）
   where 或 having 後面 （支援*標量、*行、列子查詢）
   exists 後面（相關子查詢，支援表子查詢）

2. 按結果集的行列數出不同

   標量子查詢（結果集只有 1 row 1 column）
   行子查詢 (結果有 1 column 多 rows)
   列子查詢（結果集有 1 row 多 columns)
   表子查詢（結果集一般為多行多列）

#### where / having 子查詢
特點：
1. 標量子查詢一般搭配單行操作符使用 > < >= <= < >
2. 行子查詢一般搭配多行操作符使用 in, any/some, all
3. 子查詢的執行優先於主查詢的執行，主查詢的條件用到了子查詢的結果

##### 標量子查詢
範例一：查詢薪水比 Abel 還要高的員工薪水

select *
from employees
where salary > (
select salary
from employees
where last_name = 'Abel'
);

範例二：返回 job_id 與 141 號員工相同，salary 比 143 號員工多的員工、姓名、job_id 和薪水

1. 查詢 141 號員工的 job_id
select job_id
from employees
where employee_id = 141;

2. 查詢 143 號員工的 salary
select salary
from employees
where employee_id = 143;

3. 查詢員工的姓名、job_id 和工資，要求 job_id = 1 而且 salary > 2
select last_name, job_id, salary
from employees
where job_id = (
    select job_id
    from employees
    where employee_id = 141
) and salary > (
    select salary
    from employees
    where employee_id = 143
);

範例三：查詢最低工資大於 50 號部門最低工資的部門 id 以及其最低工資

1.
select min(salary) 
from employees
where department_id = 50;

2.
select min(salary)
from employees
group by department_id;


3. 2 用 1 篩選
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (
	select min(salary) 
	from employees
	where department_id = 50
);

##### 行子查詢（1 column 多 rows）
常用操作符：in/not in、any|some, all
範例一：返回 location_id 為 1400 或 1700 的部門，其所有員工的姓名

select last_name
from employees
where department_id in (
	select department_id
	from departments
	where location_id in (1400,1700)
);

範例二： 查詢比職務為 'IT_PROG' 之任一員工薪資還要低之員工訊息

select last_name, employee_id, job_id, salary
from employees
where salary < any (
	select distinct salary
	from employees
	where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';

##### 列子查詢 (1 row 多 columns)
範例一：查詢員工編號最小且工資最高之員工訊息

select *
from employees
where (employee_id,salary) = (
    select min(employee_id), max(salary)
    from employees
);

#### select 子查詢
範例一：查詢每個部門的員工個數

select d.*, (
    select count(*)
    from employees e
    where e.department_id = d.department_id
) 個數
from departments d;

範例二：查詢每個主修之男生人數以及女生人數分別為多少

select majorid,
(select count(*) from student where sex = "男" and majorid = s.majorid) 男,
(select count(*) from student where sex = "女" and majorid = s.majorid) 女
from student s 
group by s.majorid;

#### from 子查詢
將子查詢的結果充當一張表（且必須取別名）
範例一：查詢每個部門的平均工資，其工資等級

select dep.*, g.grade_level
from (
	select department_id, round(avg(salary),2) avg_sal
	from employees
	group by department_id
) dep
inner join job_grades g
on dep.avg_sal between g.lowest_sal and g.highest_sal;

範例二：查詢每個部門其薪資大於該部門平均薪資之員工姓名、編號、薪水

select employee_id, last_name, salary, e.department_id
from employees e
inner join (
	select department_id, avg(salary) avg
	from employees
	group by department_id
) dep_avg
on e.department_id = dep_avg.department_id
where salary > dep_avg.avg;

#### exists 子查詢 (相關子查詢)
語法

exists（完整的查詢語句）
結果：1 或是 0

意義

先執行主查詢，然後再根據主查詢的結果去比對 exists 內的子查詢，子查詢涉及到主查詢

範例一：查詢有員工的部門名 (跟前面的 where in 子查詢一樣)

select department_name
from departments d
where exists (
	select *
    from employees e
    where d.`department_id` = e.`department_id`
);

### 分頁查詢
應用場景

要顯示的數據一頁顯示不全時，需要分頁提交 sql 請求

語法

select 查詢列表
from 表
join type + 表 
on 連接條件
where 篩選條件
group by 分組欄位
having 分組後篩選
order by 排序欄位
limit offset, size;

* offset 為起始索引（從 0 開始）
* size 為要顯示的資料筆數

### 聯合查詢
語法

將多條查詢語句的結果合併成一個結果

查詢語句 1
union (all)
查詢語句 2
union
...

應用場景

要查詢的結果來自於多個表，且多個表沒有直接的連接關係，但查詢的訊息一致

特點

1. 要求多條查詢語句的查詢欄位數是一致的
2. 多條查詢語句之查詢欄位，其每一欄的類型和順序最好是一致的
3. 使用 union 時預設是去重的，使用 union all 時可以包含重複項

## DML

數據操作語言：
插入：insert
修改：update
刪除：delete

### 插入語句
語法一

insert into 表名（欄位1,...）
values (值1,...)

特點

1. 插入值的類型要與欄位類型一致或兼容（亦即可以轉換）
2. 不可以為 NULL 的欄位必須插入值，可以為 NULL 的欄位則有以下兩種方法
- 方法一：欄位不省略，給值或 NULL
    insert into beauty(id,Name.sex,borndate,phone,photo,boyfriend_id)
    values(13,'Tina','female','1992-12-04','11123',NULL,2);
- 方法二：欄位跟值都省略
    insert into beauty(id,Name,sex,phone)
    values(15,'Alex','female','152421')
3. 欄位順序可以調換
4. 欄位和值的個數必須一致
5. 可以省略欄位名，預設為所有欄位，而且值的順序要和表中欄位的順序一致

語法二

insert into 表名
set 列名 = 值, 列名 = 值,...

語法一及語法二比較

1. 語法一支持多行插入
   寫法：
       insert into beauty (id,Name.sex,borndate,phone,photo,boyfriend_id)
       values (13,'Tina','female','1992-12-01','11123',NULL,2),
       (14,'Peggy','female','1992-10-04','11123',NULL,3),
       (15,'Sophia','female','1992-12-21','11123',NULL,4);
3. 語法一支持子查詢，語法二不支持
   寫法：
       ex1. 
       insert into beauty (id, NAME, phone)
       select 26, 'Victoria', '11809866'; 
       
       ex2.
       insert into beaty
       select 23, 'Ann', '1234567' union
       select 24, 'Grace', '12244567' union
       select 25, 'Lara', '1233452' union
       
       ex3. 
       insert into beauty (id,name,phone)
       select id, boyName, '123456'
       from boys where id < 3;

### 修改語句

修改單表的資料
語法

update 表明
set 欄位 ＝ 新值, 欄位 = 新值,...
where 篩選條件

修改多表的資料
語法

sql92 語法
update 表1 別名, 表2 別名
set 列=值,...
where 連接條件
and 篩選條件

sql99 語法
update 表1 別名
inner/left/right join 表2 別名
on 連接條件
set 欄位 = 值,...
where 篩選條件

範例一：修改某 A 的女朋友的手機為 114

update boys bo
inner join beauty b
on bo.id = b.boyfriend_id
set b.phone = '114'
where bo.boyName = "某A";

範例二：把沒有男友的女神，其男友編號都改為 2 號

update boys bo
right join beauty b
on bo.id = b.boyfriend_id
set b.boyfriend_id = 2
where b.boyfriend_id is null;

### 刪除語句(清空表的內容)
方法一：delete
單表刪除語法

delete from 表名
where 篩選條件;

多表刪除語法

sql92 語法
delete 表1別名, 表2別名
from 表1 別名, 表2 別名
where 連接條件
and 篩選條件;

sql99 語法
delete 表1別名, 表2別名
from 表1 別名
inner|left|right join 表2 別名
on 連接條件
where 篩選條件;

方法二：truncate
語法

truncate 表名;

delete 和 truncate 的差別

1. truncate 無法加篩選條件
2. 假如表內有欄位的設定為 auto increment，用 delete 刪除後再插入資料，該欄位的值會從斷點開始；用 truncate 刪除後在插入資料，該欄位的值會從 1 開始
3. truncate 沒有返回值，delete 有返回值
4. truncate 不能回滾，delete 可以回滾

## DDL
數據定義語言

庫和表的管理
1. 創建：create
2. 修改：alter
3. 刪除：drop

### 庫的管理
創建

create database (if not exists) 庫名;

修改

* 更改庫的字符集
alter database 庫名 character set gbk;

刪除

DROP DATABASE (IF EXISTS) 庫名;

### 表的管理
創建

CREATE TABLE IF NOT EXISTS 表名 (
    欄位名 類型(長度) 條件約束,
    欄位名 類型(長度) 條件約束,
    欄位名 類型(長度) 條件約束,
    ...
);

修改

1. 修改欄位名稱
   ALTER TABLE book
   CHANGE (COLUMN) publishdate pubDate DATETIME;

2. 修改欄位類型或條件約束
   ALTER TABLE book
   MODIFY COLUMN pubDate TIMESTAMP;
   
3. 新增欄位
   ALTER TABLE author
   ADD COLUMN annual DOUBLE;
   
4. 刪除欄位
   ALTER TABLE author
   DROP COLUMN annual;

5. 修改表名
   ALTER TABLE author
   RENAME TO book_author;

刪除

DROP TABLE IF EXISTS book_author;

複製

* 可以跨資料庫複製
 
1. 只複製表的結構
   CREATE TABLE copy LIKE author;

2. 複製表的結構+資料
   CREATE TABLE copy2
   SELECT * FROM author;

3. 只複製部分資料
   CREATE TABLE copy3
   SELECT id, name FROM author
   WHERE sex = "male";

4. 只複製部分欄位，不包含資料
   CREATE TABLE copy4
   SELECT id, name FROM author
   WHERE 0;

## 數據類型
* 數值型

整數
小數：定點數、浮點數

* 字符型

較短的文本：char, varchar
較長的文本：text, blob(較長的二進位數據)

* 日期型

### 整數

分類
| 型別 | 位元組 |
| ----------- | ---- |
| tinyint | 1 |
| smallint | 2 |
| mediumint | 3 |
| int/integer | 4 |
| bigint | 8 |

特點

1. 如果不設置為有符號 or 無符號，預設是有符號；如何想要設置無符號，需要加上 unsigned 關鍵字
2. 如果插入的整數超出範圍時會變成插入臨界值
3. 如果不設置長度，會有預設的長度；長度表示顯示的最大寬度，有加上 zerofill 這個條件約束的話會在左邊添加 0 (ex. 0000123)

如何設置有符號 or 無符號之整數

create table int_test (
	t1 int,
    t2 int unsigned
);

### 小數

分類

1. 浮點型
   float(M,D)
   double(M,D)
2. 定點型
   dec(M,D)
   decimal(M,D)

特點

1.
M: 整數部位 + 小數部位
D: 小數部位
如果超過範圍，則插入臨界值

2.
M和D都可以省略
如果是decimal, 則M默認為10, D默認為0
如果是float和double, 則會根據插入數值之精度來決定精度

3. 
定點型的精確度較高，如果要求插入數值的精度較高（如金融運算）則考慮使用，其他情況用float(空間小,因為最大字節小)或double(空間大，因為最大字節大)
decimal -> 範圍小，位數高


數值型資料型態原則

所選擇的類型越簡單越好，能保存的數值越小越好（節省空間）

### 字符型
分類

1. 較短文本
   char
   varchar
2. 較長文本
   text
   blob（較大的二進位制資料）
3. 其他
   binary 和 varbinary 用於保存較短的二進位制資料
   enum 用於保存枚舉，把允許插入的值寫在 () 內，一次只能插入一個值（值前後要加引號 ex. 'a'）
   set 用於保存集合，和 enum 類似，只能一次可以插入多個值 (ex. 'a,b,c,d')

特點

| 寫法 | M 的意思 | 特點 | 耗費空間 | 效率 |
| ---------- | ------------------------------ | -------------- | -------- | ---- |
| char(M) | 最大的字符數（可省略，預設為 1） | 固定長度的字符 | 比較多 | 高 |
| varchar(M) | 最大的字符數（不可省略） | 可變長度的字符 | 比較少 | 低 |

### 日期型
分類

date 只保存日期
time 只保存時間
year 只保存年份

datetime 保存日期 + 時間
timestamp 保存日期 + 時間

特點

| | 位元組 | 範圍 | 受時區影響 |
| --------- | ------ | --------- | ---------- |
| datetime | 8 | 1000-9999 | 不受 |
| timestamp | 4 | 1970-2038 | 受 |

* 受時區影響的意思是若更改系統時區，資料庫裡面已存在的時間資料也會跟著變

## 常見條件約束(Constraints)

含義：一種限制，用於限制表內的數據，保證該欄位數據的準確性和可靠性
分類：六大條件約束

1. NOT NULL: 非空，用於保證該欄位的值不能為空
   ex. 姓名、學號
2. DEFAULT: 預設，用於保證該欄位的值有預設值
   ex. 性別
3. PRIMARY KEY: 主鍵，用於保證欄位的值具有唯一性，並且非空
   ex. 學號、員工編號
4. UNIQUE: 唯一，用於保證該自斷的值具有唯一性，可以為空
   ex. 座位編號
5. CHECK: 檢查約束（MySQL不支援，類似於enum）
6. FOREIGN KEY: 外鍵，用於限制兩個表的關係，保證該欄位值必須來自於主表關聯列之值
                -> 在從表添加外鍵約束，用於引用主表中某欄位的值

添加條件約束的時機

1. 創建表時
2. 修改表時
3. 插入資料之前

條件約束的添加分類

行級約束：
    1. 寫在欄位名 / 欄位類型之後
    2. 六大約束語法都支持，但 FOREIGN KEY 沒有效果
表級約束：
    1. 寫在創建表的最後
    2. 除了 NOT NULL / DEFAULT 之外都支持

### 添加行級約束

語法

1. 直接在欄位名稱和類型後面追加約束類型即可
2. 可以添加多個約束條件

範例

CREATE TABLE stuinfo (
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1) CHECK (gender='男' or gender='女'), # MySQL 不支援
    seat INT UNIQUE,
    AGE INT DEFAULT 18
);

* 補充：stuinfo 表中所有的索引，包含主鍵、外鍵和唯一
SHOW INDEX FROM stuinfo;

### 添加表級約束
語法

在各個欄位設定之後
【CONSTRAINT 約束名（索引名）】+ 約束類型（欄位1,欄位2,...）

範例

CREATE TABLE stuinfo (
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    
    CONSTRAINT pk PRIMARY KEY(id),
    CONSTRAINT uq UNIQUE (seat,name),
    CONSTRAINT ck CHECK(gender='男' or gender = '女'), # MySQL 不支援
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES beauty(id)
);

### 通用的寫法

CREATE TABLE IF NOT EXISTS stuinfo (
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    gender CHAR(1),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

### PRIMARY KEY 和 UNIQUE 的對比

| | 唯一性 | 允許為空 | 一個表中可以有幾個 | 允許組合 |
| ----------- | ------ | -------- | ------------------ | -------------------------- |
| PRIMARY KEY | v | x | 最多一個 | v (但不推薦，因為不夠穩定) |
| UNIQUE | v | v | 可以有多個 | v (但不推薦，因為不夠穩定) |

** UNIQUE 可以允許多個 NULL 值

### 組合 PRIMARY KEY / UNIQUE

PRIMARY KEY (欄位一,欄位二)
UNIQUE (欄位一,欄位二)

* 可以其中一個相同，兩個欄位值都相同就會報錯

### 外鍵注意事項

1. 要求在從表設置外鍵關係
2. 從表外鍵的欄位類型和主表關聯欄位的類型要是一致或是兼容，名稱無要求
3. 主表的關聯欄位必須是一個 key (一般是 PRIMARY KEY 或 UNIQUE)
4. 插入數據時，必須先插入主表，再插入從表 ; 刪除數據時，先刪除從表，再刪除主表

### 修改表時添加約束
語法

1. 添加行級約束
   ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位類型 新增的約束
2. 添加表級約束
   ALTER TABLE 表名 ADD【CONSTRAINTS 約束名】 約束類型（欄位名）【引用外鍵】;

1. 添加非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

2. 添加預設約束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3. 添加主鍵
a. 行級約束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
b. 表級約束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4. 添加唯一
a. 行級約束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
b. 表級約束
ALTER TABLE stuinfo ADD UNIQUE(seat);

5. 添加外鍵
ALTER TABLE stuingo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCE major(id);

### 修改表時刪除約束
語法

1. 刪除非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2. 刪除預設約束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

3. 刪除主鍵
ALTER TABLE stuinfo DROP PRIMARY KEY;

4. 刪除唯一
ALTER TABLE stuinfo DROP INDEX seat;

5. 刪除外鍵
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

## 自增長 Auto Increment

特點

1. 只能和 PRIMARY KEY 搭配嗎？ 不一定，但要求要是一個 key
2. 一個表能有幾個自增長？ 最多只能有一個
3. 自增長的類型只能是數值型
4. 自增長可以透過 
auto_increment_increment = 3 進行每次間隔長度 ; 另外可以透過手動插入值來設置起始值。

一、創建表時設置自增長

CREATE TABLE tab_identy IF NOT EXISTS (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

# 寫法一
INSERT INTO tab_identity (id,NAME) VALUES (NULL,'John'); # 因為id欄位設置了自增長，所以不需要插入值，它會直接從 1 開始算起

# 寫法二
INSERT INTO tab_identity (NAME) VALUES ('John')

二、修改表時設置自增長

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_CREMENT;

三、修改表時刪除自增長

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;

四、查看變數

SHOW VARIABLES LIKE '%auto_increment%';

變數類型:
1. auto_increment_increment #每次增長的間隔
2. auto_increment_offset #起始值, MySQL不支援改變此設定

# 設置每次增長的間隔
SET auto_increment_increment = 3;

## TCL

全名：Transaction Control Language 事務控制語言

1. 何謂事務？

一個或一組 sql 組成一個執行單元，這個執行單元要馬全部執行，要馬全部不執行。整個執行單元作為一個不可分割的整體，如果某條 SQL 語句執行失敗，整個單元將回滾，所有受到影響的數據將返回到事務開始前的狀態，如果單元中的所有 SQL 語句均執行成功，則事務備順利執行

案例一：轉帳

Aaron 持有 NT1000
Tom 持有 NT1000

UPDATE 表 SET Aaron的餘額 = 500 WHERE NAME = 'Aaron'
發生意外
UPDATE 表 SET Tom的餘額 = 1500 WHERE NAME = 'Tom'

如果逐條執行的話，若兩個語句中間發生異常，則 Aaron 的金額變了，但 Tom 的金額不變，因此需使用 TCL 來處理此業務

### 存儲引擎
1. 資料庫當中的數據可以用各種不同的技術存儲在文件或記憶體當中。
2. 在 MySQL 中用的最多的存儲引擎有: innodb, myisam, memory 等。其中 innodb 支持事務，而 myisam、memory 等不支持事務。
3. 事務的 ACID 屬性

a. 原子性(Atomicity)
   遠子性是指事務是一個不可分割的工作單位，事務中的操作要馬都發生，要馬都不發生。
b. 一致性(Consistency)
   事務必須使數據庫從一個一致性狀態變換到另外一個一致性狀態
c. 隔離性(Isolation)
   事務的隔離性是指一個事務的執行不能被其他事務干擾，即一個事務內部的操作以及使用的數據對併發的其他事務而言是分隔開來的，併發執行的各個事務之間不能互相干擾。
d. 持久性(Durability)
   持久性是指一個事務一旦被提交，它對數據庫的改變就是永久性的，接下來的其他操作和數據庫故障不應該對其有任何影響

### 事務的建立
1. 隱式事務：事務沒有明顯的開啟和結束的標記
比如：insert、update、delete語句

> 想要確認的話可以輸入
> SHOW VARIABLES LIKE 'autocommit'


2. 顯式事務：事務具有明顯的開啟和結束的標記
前提：必須先設置自動提交功能為禁用
set auto commit = 0;

> 注意：這個關閉的動作只對當前的會話(session)有效，每次開啟顯式事務都要手動關閉自動提交功能

步驟一：開啟事務

SET AUTOCOMMIT = 0;
START TRANSACTION; #optional

步驟二：編寫事務中的 SQL 語句(SELECT, INSERT, UPDATE, DELETE。即 DQL+DML)

語句1;
語句2;
...

步驟三：結束事務

commit; #提交事務
rollback; #回滾

ex.

# 開啟事務
SET autocommoit = 0;
START TRANSACTION;

# 編寫一組事務語句
UPDATE account SET balance = 500 WHERE username = 'Aaron';
UPDATE account SET balance = 1500 WHERE username = 'Tom';

# 結束事務
ROLLBACK; #把上述的事務全數撤銷
COMMIT; #提交

### 事務併發
對於同時運行的多個事務，當這些事務訪問數據庫中相同的數據時，如果沒有採取必要的隔離機制，就會導致各種併發問題。

1. 髒讀
對於兩個事務 T1 和 T2, T1 讀取了已經被 T2 更新但**還沒有被提交**的字段，若 T2 回滾，T1 讀取的內容就是臨時且無效的。

2. 不可重複讀
對於兩個事務 T1 和 T2, T1 讀取了一個字段，然後 T2 更新並提交該字段之後，T1 再次讀取同一個字段時值就不同了。

3. 幻讀
對於兩個事務 T1 和 T2,T1 從一個表中讀取了一個字段，然後 T2 在該表中插入了一些新的行之後，如果 T1 再次讀取同一個表就會多出 T2 插入的那幾行。

#### 有哪些隔離級制？

| 事務的隔離級別 | 髒讀 | 不可重複讀 | 幻讀 |
| --------------- | ---- | ---------- | -------- |
| read uncommited | v | v | v |
| read commited | x | v | v |
| repeatable read | x | x | v |
| serializable | x | x | x |

MySQL 中預設是 repeatable read
Oracle 中預設是 read committed

#### 查看隔離級別

SELECT @@TRANSACTION_ISOLATION;

#### 設置隔離級別

#### Truncate 和 Delete 在事務中的演示
1. Delete 支援 Rollback
2. Truncate 不支援 Rollback

#### savepoint 的使用

SET autocommit = 0;
START TRANSACTION;
DELETE FROM ACCOUNT WHERE id = 25;
SAVEPOINT a # 設置保存點
DELETE FROM WHERE id = 28;
ROLLBACK TO a;

## 視圖(View)

即虛擬表，和普通表一樣使用
mysql5.1 版本之後出現的新特性，通過表動態生成的數據

好處

1. 重用 SQL 語句
2. 簡化複雜的 SQL 操作，不必知道它的查詢細節
3. 保護數據，提高安全性

### 視圖的建立
語法

CREATE VIEW 視圖名
AS 
查詢語句

ex. 查詢D開頭的員工跟所屬部門

CREATE VIEW V1
AS 
SELECT e.first_name, d.department_id
FROM employees e
INNER JOIN departments d on e.`department_id` = d.`department_id`;

SELECT * FROM V1 WHERE first_name LIKE 'D%';

ex. 查詢各部門的平均工資級別

CREATE VIEW myv2 
AS
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id;

SELECT myv2.`ag`, g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

ex. 查詢平均工資最低的部門名和工資

CREATE VIEW myv3
AS 
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*, m.ag
FROM myv3 m 
JOIN departments d
ON m.`department_id` = d.`department_id`;

### 修改視圖

方式一

CREATE OR REPLACE VIEW 視圖名
AS
查詢語句;

方式二

ALTER VIEW 視圖名
AS 
查詢語句

### 刪除視圖
語法

DROP VIEW 視圖名, 視圖名, 視圖名;

### 查看視圖
語法一

DESC 視圖名; 

語法二

SHOW CREATE VIEW 視圖名;

### 視圖的更新（增刪改）
> 除非有設置視圖權限，否則均會影響原始表
1. 插入

INSERT INTO myv1 VALUES('Aaron','hey,its me');

2. 修改

UPDATE myv1 SET last_name = 'Allen' WHERE last_name = 'Aaron';

3. 刪除

DELETE FROM myv1 WHERE last_name = 'Allen';

#### 具備以下特點的視圖不允許更新

1. 含以下關鍵字的 SQL 語句：分組函數、DISTINCT、GROUP BY、HAVING、UNION ALL

2. 常量視圖
- 範例

CREATE OR REPLACE VIEW myv2
AS
SELECT 'John' NAME;

UPDATE myv2 SET NAME = 'lucy';

3. SELECT中包含子查詢
- 範例

CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工資;

UPDATE myv3 SET 最高工資 = 100000;

4. join
- 範例

CREATE OR REPLACE VIEW myv4
AS
SELECT last_name, department_name 
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

UPDATE myv4 SET last_name = 'Pat' WHERE last_name = 'Aaron'; #可以更新
INSERT INTO myv4 VALUES('Jack','Finance'); #不能更新 

5. FROM 一個不能更新的視圖

CREATE OR REPLACE VIEW myv5
AS 
SELECT * FROM myv3;

UPDATE myv5 SET 最高工資 = 10000 WHERE department_id = 60;

6. WHERE子句的子查詢引用了FROM子句中的表

CREATE OR REPLACE VIEW myv6
AS
SELECT last_name, email, salary 
FROM employees
WHERE employee_id IN (
    SELECT manager_id 
    FROM employees
    WHERE manager_id IS NOT NULL'
)

UPDATE myv6 SET salary = 10000 WHERE last_name = 'k_ing';

### 視圖和表的對比

| | 創建語法的關鍵字 | 使用 | 是否實際占用物理空間 |
| ---- | ---------------- | --- | -------------------- |
| 表 | CREATE VIEW | 增刪改查 | 保存數據 |
| 視圖 | CREATE TABLE | 增刪改查，只是一般不能增刪改 | 僅保存SQL邏輯 |

## 變數

### 系統變數

說明：由系統提供之變數，非使用者定義，屬於伺服器層面
分成：全局變數／會話變數（作用域：僅僅針對於當前會話（連接）有效）

－查看所有系統變數

SHOW VARIABLES;
SHOW GLOBAL(SESSION) VARAIBLES;

－查看滿足條件的部分系統變數

SHOW GLOBAL(SESSION) VARAIBLES LIKE '%CHAR%';

－查看指定的某個系統變數的值

SELECT @@GLOBAL(SESSION).系統變數名;

－為某個系統變數賦值

SET GLOBAL(SESSION) 系統變數名 = 值;
SET @@GLOBAL(SESSION).系統變數名 = 值;

備註：什麼級別都不寫，預設是SESSION

### 自定義變數

說明：變數是用戶自定義的，不是系統定義的
使用步驟：宣告 -> 賦值 -> 使用（查看、比較、運算）
分成：用戶變數、局部變數

#### 用戶變數

作用域：針對當前會話（連接有效），相當於系統變數中會話變數的作用域。
可以應用在任何地方，也就是BEGIN END裡面或BEGIN END外面

1. 宣告並初始化

SET @用戶變數名 = 值;
SET @用戶變數名:=值;
SELECT @用戶變數名:=值;

2. 賦值(更新用戶變數的值)
方式一：通過 SET 或 SELECT

SET @用戶變數名= 值;
SET @用戶變數名:= 值;
SELECT @用戶變數名:= 值;

方式二：通過 SELECT INTO

SELECT 字段 INTO 變數名
FROM 表;

ex.

SELECT COUNT(*) INTO @COUNT
FROM EMPLOYEES;

3. 使用 (查看用戶變數的值)

SELECT @用戶變數名

#### 局部變數

作用域：僅僅在定義它的BEGIN END中有效

1. 宣告

DECLARE 變數名 類型;
DECLARE 變數名 類型 DEFAULT 值;

2. 賦值
方式一：透過 SET 或 SELECT

SET 局部變數名= 值;
SET 局部變數名:= 值;
SELECT @局部變數名:=值;

方式二：透過 SELECT INTO

SELECT 字段 INTO 局部變數名
FROM 表;

3. 使用

SELECT 局部變數名;

| | 作用域 | 定義和使用的位置 | 語法|
| --------------- | ---- | ---------- | -------- |
| 用戶變數 | 當前會話 | 會話中的任何地方 | 必須加上@符號，不用限定類型 |
| 局部變數 | BEGIN END 中 | 只能在BEGIN END中，且為第一句話 | 一般不用加@符號，需要限定類型 |

## 存儲過程和函數
類似於 Java 中的方法
好處

1. 提高程式碼的重用性
2. 簡化操作

定義

一組預先編譯好的SQL語句之集合，可以理解成把語句批次處理
1. 提高了程式碼的重用性
2. 簡化操作
3. 減少編譯次數以及減少和資料庫SERVER的連線次數,提高了效率

### 存儲過程
創建

CREATE PROCEDURE 存儲過程名(參數列表)
BEGIN
    方法內容(一組合法的SQL語句)
END

注意:
1. 參數列表包含三個部分
參數模式 參數名 參數類型
舉例 IN stuname VARCHAR(20)

參數模式

IN -> 該參數可以作為輸入, 也就是該參數需要調用方傳入值
OUT-> 該參數可以作為輸出, 也就是該參數可以作為回傳值
INOUT -> 該參數既可以作為輸入, 也可以作為輸出

2. 如果存儲過程內容只有一句話, BEGIN/END 則可以省略; 存儲過程中的每條SQL語句的結尾要求必須加分號;存儲過程結尾的結束標記可以使用DELIMITER重新設置, 但是用完之後記得要把結束標記改回 ;
語法

DELIMITER 結束標記
例:
DELIMITER $ (也就是用$取代;)

調用

CALL 存儲過程名(參數列表)

空參
例1.插入到 admin 表中五條紀錄

SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
	INSERT INTO admin(username, `PASSWORD`)
    VALUES ('john1','0000'), ('lily','0000'), ('rose','0000'), ('jack','0000'), ('john1','0000'), ('tom','0000');
END $
DELIMITER ;
#調用
CALL myp1()$

IN模式參數
例題1. 輸入女生姓名，查找對應的男友

DELIMITER $
CREATE PROCEDURE myp2(IN beautyname VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name = beautyName; #指定參數對應的欄位名稱
END $
DELIMITER ;
CALL myp2('王语嫣'); 

例2. 創建存儲過程以實現用戶是否登入成功

DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT default 0;
    SELECT COUNT(*) INTO result # 賦予值
    FROM admin
    WHERE admin.username = username #這裡如果沒有指定表名, 會因為變數名稱都是 username 而恆成立(就近原則)
    AND admin.PASSWORD = PASSWORD;
    SELECT IF (result>0, '成功', '失敗');
END $
DELIMITER ;

CALL myp3('john', '8888');

帶out模式的存儲過程
例1: 根據女生名，返回對應的男朋友名字

DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyName
    FROM boys bo
    INNER JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name = beautyName;
END $
DELIMITER ;

CALL myp5('Angelababy', @bName); # 回傳值會自動塞給 @bName
SELECT @bName;

例2: 根據女生名，返回對應的男朋友名和男友魅力值

DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
    SELECT bo.boyName, bo.userCP INTO boyName, userCP
    FROM boys bo
    INNER JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name = beautyName;
END $
DELIMITER ;

CALL myp4('小昭', @bname, @usercp); #這樣就表示宣告了用戶變數了
SELECT @bname, @usercp;

帶inout模式的存儲過程
例題1: 傳入a,b兩值, 最終a和b都翻倍並返回

DELIMITER $
CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
BEGIN
    SET a=a*2; #局部變數不需要加上@
    SET b=b*2;
END $
DELIMITER ;

#先定義用戶變數再調用傳參
SET @m=10;
set @n=20;

CALL myp6(@m, @n);
SELECT @m, @n;

刪除存儲過程

DROP PROCEDURE p1
DROP PROCEDURE p1, p2;

查看存儲過程的資訊

SHOW CREATE PROCEDURE 名稱;

### 函式
和存儲過程的差別

存儲過程: 可以有0個返回，也可以有多個返回，適合做批量插入、批量更新
函式: 有且僅有一個返回，適合用來處理數據並返回一個結果

創建語法

CREATE FUNCTION 函數名(參數列表) RETURNS 返回類型
BEGIN
    函式體
END

注意

1. 參數列表包含: 參數名、參數類型
2. 函式體: 肯定會有 return 語句，如果沒有會報錯，如果 return 語句沒有放在函式體的最後也不會報錯，但不建議。
3. 函式體中僅有一句話，則可以省略 BEGIN END
4. 使用DELIMITER語句設置結束標記

調用語法

SELECT 函數名(參數列表)

無參有返回
例1: 返回公司的員工個數

DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;
    SELECT COUNT(*) INTO c
    FROM employees;
    RETURN c;
END $
DELIMITER ;

SELECT myf1(); # 

有參有返回
例1: 根據員工名，返回他的工資

DELIMITER $
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @sal = 0;
    SELECT salary INTO @sal
    FROM EMPLOYEES
    WHERE last_name = empName;
    RETURN @sal;
END $
DELIMITER ;

SELECT myf2('Kochhar');

查看函數

SHOW CREATE FUNCTION 名稱;

刪除函數

DROP FUNCTION 名稱;

## 流程控制

1. if 函數
2. case 結構
a. 類似於Java中的Switch語句，一般用於實現等值判斷
語法:

CASE 變數|表達式|字段
WHEN 要判斷的值 THEN 返回的值1或語句1;
WHEN 要判斷的值 THEN 返回的值2或語句2;
...
ELSE 要返回的值n或語句n;
END CASE;

b. 類似於JAVA中的多重IF語句，一般用於實現區間判斷
語法

CASE 
WHEN 要判斷的條件1 THEN 返回的值1或語句1;
WHEN 要判斷的條件2 THEN 返回的值2或語句2;
...
ELSE 要返回的值n或語句n;
END CASE;

三大寫法


創建存儲過程，根據傳入的成績來顯示等級，比如傳入的成績: 90-100, 顯示A; 80-90, 顯示B; 60-80, 顯示C, 否則顯示D(作為獨立語句且為情況二)

CREATE PROCEDURE test_case(IN score INT)
BEGIN
    CASE
    WHEN score>=90 AND score <=100 THEN SELECT 'A';
    WHEN score >=80 THEN SELECT 'B';
    WHEN score >= 60 THEN SELECT 'C'
    ELSE SELECT 'D'
END CASE;
END $

3. if結構
應用在BEGIN END中
語法

IF 條件1 THEN 語句;
ELSEIF 條件2 THEN 語句;
...
[ELSE 語句n;]
END IF;

例1: 根據傳入的成績來顯示等級，比如傳入的成績: 90-100, 返回 A;80-90,返回B;60-80返回C,否則返回D

CREATE FUNCTION test_if(SCORE INT) RETURNS CHAR
BEGIN
    IF score>=90 AND score<=100 THEN RETURN 'A';
    ELSEIF score>=80 THEN RETURN 'B'
    ELSEIF score>=60 THEN RETURN 'C';
    ELSE RETURN 'D';
    END IF;
END $

## 循環結構
分類

while, loop, repeat

循環控制

iterate 類似於 continue
leave 類似於 break

1. while

while 循環條件 do 
    循環體;
end while;

2. loop

loop
    循環體;
end loop;

3. repeat

repeat 
    循環體;
until 結束循環的條件
end repeat

案例1. 批次插入，根據次數插入到 admin 表中多條紀錄

CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=inserCount DO
        INSERT INTO admin(username, `password`) VALUES('Rose'+i, '666');
        SET i=i+1;
    END WHILE;
END $

案例2. 批量插入，根據次數插入到admin表中多條紀錄，如果次數>20則停止

delimiter $
create procedure test_while(in insertcount int)
begin
	declare i int default 1;
    a:while i<=insertCount do
		insert into admin(username, `password`) values(concat('xiaohua', i), '0000');
        if i>=20 then leave a;
        end if;
        set i=i+1;
	end while a;
end $

call test_while(200);

案例3. 批量插入，根據次數插入到admin表中多條紀錄，只插入偶數次

delimiter $
create procedure test_while2(int insertCount INT)
begin
    declare i int default 0;
    a:while i<=insertCount do #a是標籤
        set i=i+1
        if mod(i,2)!=0 then iterate a; #interate=continue
        end if;
        insert into admin(username, `password`) values(concat('xiaohua', i), '0000');
    end while a;
end $


例題1: 向stringcontent表插入指定個數且隨機的字符串

drop rable if exists stringcontent;
create table stringcontent(
    id int primary key auto_increment,
    content varchar(20)
);

delimiter $
create procedure test_random_insert(IN insertCount INT)
begin
    declare i int default 1; #定義一個循環變數表示插入次數
    declare str varchar(26) default 'abcdefghijklmnopqrstuvwxyz';
    declare startIndex int default 1; #代表起始索引
    declare len int default 1; #代表擷取的字符長度
    while i<=insertCount do
        set len=floor(rand()*(20-startIndex+1)+1); #產生一個隨機的整數，代表擷取長度
        set startIndex=floor(rand()*26+1); #產生一個隨機的整數代表起始索引1-26
        insert into stringcontent(content) values(substr(str,startIndex,len));
        set i=i+1;
    end while;
end $

delimiter ;
call test_random_insert(20);

## MySQL 邏輯架構

SQL Interface(語句分類)->parser(解析sql語句)->optimizer(優化器)

4. 數據存儲層
主要是將數據存儲在運行於裸設備的文件系統之上，並完成與存儲引擎的交互

最常用引擎之差別