一、 DML 操作【 重點
重點
1.1 新增（INSERT）
INSERT INTO 表名 (列 1，列 2，列 3…) VALUES(值 1，值 2，值 3…);
1.1.1 添加一條資訊
#添加一條工作崗位資訊
INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES('JAVA_Le','JAVA_Lecturer',2500,9000); #添加一條員工資訊
INSERT INTO `t_employees`
（EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID）
VALUES
('194','Samuel','McCain','SMCCAIN', '650.501.3876', '1998-07-01', 'SH_CLERK', '3200', NULL, '123', '50'); 1.2 修改（UPDATE）
UPDATE 表名 SET 列 1=新值 1 ,列 2 = 新值 2,…WHERE 條件;
1.2.1 修改一條資訊
#修改編号為100 的員工的工資為 25000
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100'; #修改編号為135 的員工資訊崗位編号為 ST_MAN，工資為3500
UPDATE t_employees SET JOB_ID=ST_MAN,SALARY = 3500 WHERE EMPLOYEE_ID = '135'; 1.3 删除（DELETE）
DELETE FROM 表名 WHERE 條件；
1.3.1 删除一條資訊
#删除編号為135 的員工
DELETE FROM t_employees WHERE EMPLOYEE_ID='135'; #删除姓Peter，并且名為 Hall 的員工
DELETE FROM t_employees WHERE FIRST_NAME = 'Peter' AND LAST_NAME='Hall'; 1.4 清空整表資料（TRUNCATE）
TRUNCATE TABLE 表名; 該文法不屬于DML語句.
1.4.1 清空整張表
#清空t_countries整張表
TRUNCATE TABLE t_countries; 2、資料查詢【 重點
重點
2.1 資料庫表的基本結構
關系結構資料庫是以表格（Table）進行資料存儲，表格由“行”和“列”組成
-- 員工表 employee
employee_id --編号
first_name --名字
email --郵箱
salary --月薪
commission_pct --提成
manager_id --所屬經理編号
department_id --部門id
job_id --工作編号 -- 部門表 department
department_id --部門編号
department_name --部門名稱
-- 工作表 job
job_id --工作id
job_name --工作名稱
job_desc --工作内容
--經理表 manager
manager_id --經理編号
manager_name --經理名字 2.2 基本查詢
文法： SELECT 列名 FROM 表名
| 關鍵字 | 描述 |
| SELECT | 指定要查詢的列 |
| FROM | 指定要查詢的表 |
2.2.1 查詢部分列
#查詢員工表中所有員工的編号、名字、郵箱
SELECT employee_id,first_name,email
FROM t_employees; 2.2.2 查詢所有列
#查詢員工表中所有員工的所有資訊（所有列）
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees; 2.2.3 對列中的資料進行運算
#查詢員工表中所有員工的編号、名字、年薪
SELECT employee_id , first_name , salary*12
FROM t_employees; | 算數運算符 | |
| + | 兩列做加法運算 |
| - | 兩列做減法運算 |
| * | 兩列做乘法運算 |
| / | 兩列做除法運算 |
2.2.4 列的别名
列 as ‘列名’
#查詢員工表中所有員工的編号、名字、年薪（列名均為中文）
SELECT employee_id as "編号" , first_name as "名字" , salary*12 as "年薪"
FROM t_employees; 2.2.5 查詢結果去重
DISTINCT 列名
#查詢員工表中所有經理的ID。
SELECT DISTINCT manager_id
FROM t_employees; 2.3排序查詢
文法： SELECT 列名 FROM 表名 ORDER BY 排序列 [排序規則]
| 排序規則 | |
| ASC | 對前面排序列做升序排序 |
| DESC | 對前面排序列做降序排序 |
2.3.1 依據單列排序
#查詢員工的編号，名字，薪資。按照工資高低進行降序排序。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC; 2.3.2 依據多列排序
#查詢員工的編号，名字，薪資。按照工資高低進行升序排序（薪資相同時，按照編号進行升序排序）。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC; 2.4 條件查詢
文法：SELECT 列名 FROM 表名 WHERE 條件
| 在查詢結果中，篩選符合條件的查詢結果，條件為布爾表達式 |
2.4.1 等值判斷（=）
#查詢薪資是11000的員工資訊（編号、名字、薪資）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000; 2.4.2 邏輯判斷（and、or、not）
#查詢薪資是11000并且提成是0.30的員工資訊（編号、名字、薪資）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000 AND commission_pct = 0.30; 2.4.3 不等值判斷（> 、< 、>= 、<= 、!= 、<>）
#查詢員工的薪資在6000~10000之間的員工資訊（編号，名字，薪資）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary >= 6000 AND salary <= 10000; 2.4.4 區間判斷（between and）
#查詢員工的薪資在6000~10000之間的員工資訊（編号，名字，薪資）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000; #閉區間，包含區間邊界的兩個值 2.4.5 NULL 值判斷（IS NULL、IS NOT NULL）
IS NULL
列名 IS NULL
IS NOT NULL
列名 IS NOT NULL
#查詢沒有提成的員工資訊（編号，名字，薪資 , 提成）
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL; 2.4.6 枚舉查詢（ IN (值 1，值 2，值 3 ) ）
#查詢部門編号為70、80、90的員工資訊（編号，名字，薪資 , 部門編号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN(70,80,90);
注：in的查詢效率較低，可通過多條件拼接。 2.4.7 模糊查詢
LIKE _ （單個任意字元）
列名 LIKE ‘張_’
LIKE %（任意長度的任意字元）
列名 LIKE ‘張%’
#查詢名字以"L"開頭的員工資訊（編号，名字，薪資 , 部門編号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L%';
#查詢名字以"L"開頭并且長度為4的員工資訊（編号，名字，薪資 , 部門編号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L___'; 2.4.8 分支結構查詢
CASE
WHEN 條件1 THEN 結果1
WHEN 條件2 THEN 結果2
WHEN 條件3 THEN 結果3
ELSE 結果
END #查詢員工資訊（編号，名字，薪資 , 薪資級别<對應條件表達式生成>）
SELECT employee_id , first_name , salary , department_id ,
CASE
WHEN salary>=10000 THEN 'A'
WHEN salary>=8000 AND salary<10000 THEN 'B'
WHEN salary>=6000 AND salary<8000 THEN 'C'
WHEN salary>=4000 AND salary<6000 THEN 'D'
ELSE 'E'
END as "LEVEL"
FROM t_employees; 2.5 時間查詢
文法：SELECT 時間函數([參數清單])
| 時間函數 | |
| SYSDATE() | 目前系統時間（日、月、年、時、分、秒） |
| CURDATE() | 擷取目前日期 |
| CURTIME() | 擷取目前時間 |
| WEEK(DATE) | 擷取指定日期為一年中的第幾周 |
| YEAR(DATE) | 擷取指定日期的年份 |
| HOUR(TIME) | 擷取指定時間的小時值 |
| MINUTE(TIME) | 擷取時間的分鐘值 |
| DATEDIFF(DATE1,DATE2) | 擷取DATE1 和 DATE2 之間相隔的天數 |
| ADDDATE(DATE,N) | 計算DATE 加上 N 天後的日期 |
2.5.1 獲得目前系統時間
#查詢目前時間
SELECT SYSDATE(); #查詢目前時間
SELECT NOW(); #擷取目前日期
SELECT CURDATE(); #擷取目前時間
SELECT CURTIME(); 2.6 字元串查詢
文法： SELECT 字元串函數 ([參數清單])
| 字元串函數 | 說明 |
| CONCAT(str1,str2,str…) | 将 多個字元串連接配接 |
| INSERT(str,pos,len,newStr) | 将str 中指定 pos 位置開始 len 長度的内容替換為 newStr |
| LOWER(str) | 将指定字元串轉換為小寫 |
| UPPER(str) | 将指定字元串轉換為大寫 |
| SUBSTRING(str,num,len) | 将str 字元串指定num位置開始截取 len 個内容 |
2.6.1 字元串應用
#拼接内容
SELECT CONCAT('My','S','QL'); #字元串替換
SELECT INSERT('這是一個資料庫',3,2,'MySql');#結果為這是 MySql 資料庫 #指定内容轉換為小寫
SELECT LOWER('MYSQL');#mysql #指定内容轉換為大寫
SELECT UPPER('mysql');#MYSQL #指定内容截取
SELECT SUBSTRING('JavaMySQLOracle',5,5);#MySQL 2.7 聚合函數
聚合函數(列名) FROM 表名;
| 聚合函數 | |
| SUM() | 求所有行中單列結果的總和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| COUNT() | 求總行數 |
2.7.1 單列總和
#統計所有員工每月的工資總和
SELECT sum(salary)
FROM t_employees; 2.7.2 單列平均值
#統計所有員工每月的平均工資
SELECT AVG(salary)
FROM t_employees; 2.7.3 單列最大值
#統計所有員工中月薪最高的工資
SELECT MAX(salary)
FROM t_employees; 2.7.4 單列最小值
#統計所有員工中月薪最低的工資
SELECT MIN(salary)
FROM t_employees; 2.7.5 總行數
#統計員工總數
SELECT COUNT(*)
FROM t_employees; #統計有提成的員勞工數
SELECT COUNT(commission_pct)
FROM t_employees; 2.8 分組查詢
文法：SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 分組依據（列）;
| GROUP BY | 分組依據，必須在 WHERE 之後生效 |
2.8.1 查詢各部門的總人數
#思路：
#1.按照部門編号進行分組（分組依據是 department_id）
#2.再針對各部門的人數進行統計（count）
SELECT department_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id; 2.8.2 查詢各部門的平均工資
#思路：
#1.按照部門編号進行分組（分組依據department_id）。
#2.針對每個部門進行平均工資統計（avg）。
SELECT department_id , AVG(salary)
FROM t_employees
GROUP BY department_id 2.8.3 查詢各個部門、各個崗位的人數
#思路：
#1.按照部門編号進行分組（分組依據 department_id）。
#2.按照崗位名稱進行分組（分組依據 job_id）。
#3.針對每個部門中的各個崗位進行人數統計（count）。
SELECT department_id , job_id , COUNT(employee_id)
FROM t_employees
GROUP BY department_id , job_id; 2.8.4 常見問題
#查詢各個部門id、總人數、first_name
SELECT department_id , COUNT(*) , first_name
FROM t_employees
GROUP BY department_id; #error 2.9 分組過濾查詢
文法：SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 分組列 HAVING 過濾規則
| 過濾規則定義對分組後的資料進行過濾 |
2.9.1 統計部門的最高工資
#統計60、70、90号部門的最高工資
#思路：
#1). 确定分組依據（department_id）
#2). 對分組後的資料，過濾出部門編号是60、70、90資訊
#3). max()函數處理
SELECT department_id , MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in (60,70,90)
# group确定分組依據department_id
#having過濾出60 70 90部門
#select檢視部門編号和max函數。 2.10 限定查詢
LIMIT 起始行，查詢行數
| LIMIT offset_start，row_count | 限定查詢結果的起始行和總行數 |
2.10.1 查詢前 5 行記錄
#查詢表中前五名員工的所有資訊
SELECT * FROM t_employees LIMIT 0,5; 2.10.2 查詢範圍記錄
#查詢表中從第四條開始，查詢 10 行
SELECT * FROM t_employees LIMIT 3,10; 2.10.3 LIMIT典型應用
分頁查詢：一頁顯示 10 條，一共查詢三頁
#思路：第一頁是從 0開始，顯示 10 條
SELECT * FROM LIMIT 0,10;
#第二頁是從第 10 條開始，顯示 10 條
SELECT * FROM LIMIT 10,10;
#第三頁是從 20 條開始，顯示 10 條
SELECT * FROM LIMIT 20,10; 2.11 查詢總結
2.11.1 SQL 語句編寫順序
SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 分組 HAVING 過濾條件 ORDER BY 排序列（asc|desc）LIMIT 起始行，總條數
2.11.2 SQL 語句執行順序
1.FROM ：指定資料來源表
2.WHERE : 對查詢資料做第一次過濾
3.GROUP BY ： 分組
4.HAVING : 對分組後的資料第二次過濾
5.SELECT : 查詢各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查詢結果 2.12 子查詢（作為條件判斷）
Where 條件 (子查詢結果)
2.12.1 查詢工資大于Bruce 的員工資訊
#1.先查詢到 Bruce 的工資（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';#工資是 6000
#2.查詢工資大于 Bruce 的員工資訊
SELECT * FROM t_employees WHERE SALARY > 6000;
#3.将 1、2 兩條語句整合
SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce' ）; 2.13 子查詢（作為枚舉查詢條件）
SELECT 列名 FROM 表名 Where 列名 in(子查詢結果);
2.13.1 查詢與名為’King’同一部門的員工資訊
#思路：
#1. 先查詢 'King' 所在的部門編号(多行單列)
SELECT department_id
FROM t_employees
WHERE last_name = 'King'; //部門編号：80、90
#2. 再查詢80、90号部門的員工資訊
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (80,90);
#3.SQL：合并
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列 2.13.2 工資高于60部門所有人的資訊
#1.查詢 60 部門所有人的工資（多行多列）
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;
#2.查詢高于 60 部門所有人的工資的員工資訊（高于所有）
select * from t_employees where SALARY > ALL(select SALARY from t_employees WHERE DEPARTMENT_ID=60);
#。查詢高于 60 部門的工資的員工資訊（高于部分）
select * from t_employees where SALARY > ANY(select SALARY from t_employees WHERE DEPARTMENT_ID=60); 2.14 子查詢（作為一張表）
SELECT 列名 FROM （子查詢的結果集） WHERE 條件;
2.14.1 查詢員工表中工資排名前 5 名的員工資訊
#思路：
#1. 先對所有員工的薪資進行排序（排序後的臨時表）
select employee_id , first_name , salary
from t_employees
order by salary desc
#2. 再查詢臨時表中前5行員工資訊
select employee_id , first_name , salary
from (臨時表)
limit 0,5;
#SQL：合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
limit 0,5; 2.15 合并查詢（了解）
2.15.1 合并兩張表的結果（去除重複記錄）
#合并兩張表的結果，去除重複記錄
SELECT * FROM t1 UNION SELECT * FROM t2; 2.15.2 合并兩張表的結果（保留重複記錄）
#合并兩張表的結果，不去除重複記錄（顯示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2; 2.16 表連接配接查詢
SELECT 列名 FROM 表 1 連接配接方式 表 2 ON 連接配接條件
2.16.1 内連接配接查詢（INNER JOIN ON）
#1.查詢所有有部門的員工資訊（不包括沒有部門的員工） SQL 标準
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID
#2.查詢所有有部門的員工資訊（不包括沒有部門的員工） MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID 2.16.2 三表連接配接查詢
#查詢所有員工工号、名字、部門名稱、部門所在國家ID
SELECT * FROM t_employees e
INNER JOIN t_departments d
on e.department_id = d.department_id
INNER JOIN t_locations l
ON d.location_id = l.location_id 2.16.3 左外連接配接（LEFT JOIN ON）
#查詢所有員工資訊，以及所對應的部門名稱（沒有部門的員工，也在查詢結果中,部門名稱以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
LEFT JOIN t_departments d
ON e.department_id = d.department_id; 2.16.4 右外連接配接（RIGHT JOIN ON）
#查詢所有部門資訊，以及此部門中的所有員工資訊（沒有員工的部門，也在查詢結果中，員工資訊以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
RIGHT JOIN t_departments d
ON e.department_id = d.department_id; 三、資料庫進階
3.1 存儲過程
MySQL 5.0 版本開始支援存儲過程。存儲過程思想上很簡單，就是資料庫 SQL 語言層面的代碼封裝與重用。
存儲過程（Stored Procedure）是一種在資料庫中存儲複雜程式，以便外部程式調用的一種資料庫對象。存儲過程是為了完成特定功能的SQL語句集，經編譯建立并儲存在資料庫中，使用者可通過指定存儲過程的名字并給定參數(需要時)來調用執行。
DELIMITER $$ 或 DELIMITER //
這個代表目前sql語句的結尾符号不是 ; 而是變成了指定的 $$ 或者 //
因為在存儲過程的建立過程中,我們會有;代表一行語句的結尾
• 1
• 2
• 3 3.1.1 建立存儲過程
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
[begin_label:] BEGIN
[statement_list]
……
END [end_label] DELIMITER $$
CREATE PROCEDURE pro_test1()
BEGIN
INSERT INTO t2(tname) VALUES('haha');
UPDATE t1 SET tname='jack1' WHERE tid=1;
SELECT * FROM t1;
END $$
call pro_test1() 3.1.2 存儲過程參數
IN | OUT | INOUT 類型 名稱 類型
in參數的使用
DELIMITER $$
CREATE PROCEDURE pro_test2(IN t2_name VARCHAR(20) , IN t1_id INT)
BEGIN
INSERT INTO t2(tname) VALUES(t2_name);
UPDATE t1 SET tname='jack1' WHERE tid=t1_id;
SELECT * FROM t1;
END $$
CALL pro_test2('hehe',2) out參數的使用
DELIMITER $$
CREATE PROCEDURE pro_test3(OUT tname VARCHAR(20))
BEGIN
SET tname = 'xixi';
END $$
CALL pro_test3(@tname);
SELECT @tname; inout參數的使用;盡量分開使用in和out,不使用inout
DELIMITER $$
CREATE PROCEDURE pro_test4(INOUT tname VARCHAR(20))
BEGIN
SELECT tname;
SELECT CONCAT(tname,"hello") INTO tname;
END $$
SET @tname='jack';
CALL pro_test4(@tname);
SELECT @tname; 3.1.3 存儲過程變量
局部變量 DECLARE var_name[, var_name] … type [DEFAULT value];
set和預設值
declare num2 int default 100;
select num2;
DELIMITER $$
CREATE PROCEDURE pro_test5()
BEGIN
DECLARE num INT DEFAULT 100;
SELECT num;
set num = 200;
select num;
END $$
CALL pro_test5 使用 into 進行指派
DELIMITER $$
CREATE PROCEDURE pro_test6()
BEGIN
DECLARE num INT DEFAULT 100;
SELECT num;
SELECT tid INTO num FROM t1 WHERE tname='jack';
SELECT num;
END $$
CALL pro_test6 使用者變量 @變量名 不需要申明可直接使用;類似于Java全局變量
DELIMITER $$
CREATE PROCEDURE pro_test7()
BEGIN
SET @param_t1 = 300;
SELECT @param_t1;
SELECT tid INTO @param_t1 FROM t1 WHERE tname='jack';
SELECT @param_t1;
END $$
CALL pro_test7 系統變量 @@變量名
根據系統變量的作用域分為：全局變量與會話變量（兩個@符号）
全局變量(@@global.)
在MySQL啟動的時候由伺服器自動将全局變量初始化為預設值；
全局變量的預設值可以通過更改MySQL配置檔案(my.ini、my.cnf)來更改。
會話變量(@@session.)
在每次建立一個新的連接配接的時候，由MySQL來初始化；
MYSQL會将目前所有全局變量的值複制一份來做為會話變量（也就是說，如果在建立會話以後，沒有手動更改過會話變量與全局變量的值，那所有這些變量的值都是一樣的）。
全局變量與會話變量的差別：對全局變量的修改會影響到整個伺服器，但是對會話變量的修改，隻會影響到目前的會話。 3.1.4 條件語句
if-then-else 語句
IF expression THEN
statements;
END IF;
----------------------------------------
IF expression THEN
statements;
ELSE
else-statements;
END IF;
----------------------------------------
IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
...
ELSE
else-statements;
END IF; DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)
BEGIN
IF tid = 1 THEN
INSERT INTO t1(tname) VALUES('tom');
END IF;
END $$
CALL pro_test8(1);
-----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)
BEGIN
IF tid = 1 THEN
INSERT INTO t1(tname) VALUES('tom');
ELSE
SELECT CONCAT(tid,'xixi');
END IF;
END $$
CALL pro_test8(2);
---------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)
BEGIN
IF tid = 1 THEN
INSERT INTO t1(tname) VALUES('tom');
ELSEIF tid = 2 THEN
SELECT CONCAT(tid,'xixi');
ELSEIF tid = 3 THEN
SELECT CONCAT(tid,'xxxxxx');
END IF;
END $$
CALL pro_test8(3); 3.1.5 循環語句
WHILE……DO……END WHILE
REPEAT……UNTIL END REPEAT
LOOP……END LOOP
GOTO 不建議用
DELIMITER $$
CREATE PROCEDURE pro_test9(IN number INT)
BEGIN
-- 滿足什麼條件繼續循環
WHILE number > 0 DO
SELECT number;
SET number=number-1;
END WHILE;
END $$
CALL pro_test9(6); REPEAT……UNTIL END REPEAT
DELIMITER $$
CREATE PROCEDURE pro_test10(IN number INT)
BEGIN
REPEAT
INSERT INTO t1(tname) VALUES(CONCAT('aa',number));
SET number=number-1;
UNTIL number < 0 -- 這裡不需要加分号 這個是滿足什麼條件退出循環
END REPEAT;
END $$
CALL pro_test10(6); LOOP…LEAVE…END LOOP
DELIMITER $$
CREATE PROCEDURE pro_test11()
BEGIN
BEGIN
DECLARE i INT DEFAULT 0;
loop_x : LOOP
INSERT INTO t1(tname) VALUES(CONCAT('bb',i));
SET i=i+1;
IF i > 5 THEN
LEAVE loop_x;
END IF;
END LOOP;
END;
END $$
CALL pro_test11 3.1.6 分支語句
DELIMITER $$
CREATE PROCEDURE pro_test12(IN var INT)
BEGIN
CASE var
WHEN 1 THEN
SELECT 'a';
WHEN 2 THEN
SELECT 'b';
ELSE
SELECT 'c';
END CASE;
END $$
CALL pro_test12(2); 3.2 函數（了解）
自定義函數,和concat等内置函數意義一緻
CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
[characteristic ...]
BEGIN
routine_body
END;
（1）func_name ：存儲函數的名稱。
（2）param_name type：可選項，指定存儲函數的參數。
（3）RETURNS type：指定傳回值的類型。
（4）characteristic：可選項，指定存儲函數的特性。
（5）routine_body：SQL代碼内容。
調用函數
SELECT func_name([parameter[,…]]); DELIMITER //
CREATE FUNCTION fc_test(id INT)
RETURNS VARCHAR(20)
BEGIN
SELECT tname INTO @name FROM t1 WHERE tid=id;
RETURN @name;
END //
SELECT fc_test(2); 3.3 觸發器Trigger（了解）
3.3.1 主外鍵級聯操作
Cascade 在主表上update/delete記錄時，同步update/delete掉子表的比對記錄
No Action 如果子表中有比對的記錄,則不允許對父表對應候選鍵進行update/delete操作
Restrict 同no action, 都是立即檢查外鍵限制
Set null 在主表上update/delete記錄時，将子表上比對記錄的列設為null
注: trigger不會受外鍵cascade行為的影響,即不會觸發trigger
NULL、RESTRICT、NO ACTION
删除：從表記錄不存在時，主表才可以删除。删除從表，主表不變
更新：從表記錄不存在時，主表才可以更新。更新從表，主表不變
CASCADE
删除：删除主表時自動删除從表。删除從表，主表不變
更新：更新主表時自動更新從表。更新從表，主表不變
SET NULL
删除：删除主表時自動更新從表值為NULL。删除從表，主表不變
更新：更新主表時自動更新從表值為NULL。更新從表，主表不變 3.3.2 Trigger
觸發器是與表有關的資料庫對象，在滿足定義條件時觸發，并執行觸發器中定義的語句集合。觸發器的這種特性可以協助應用在資料庫端確定資料的完整性。
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：觸發器的名稱
tirgger_time：觸發時機，為BEFORE或者AFTER
trigger_event：觸發事件，為INSERT、DELETE或者UPDATE
tb_name：表示建立觸發器的表明，就是在哪張表上建立觸發器
trigger_stmt：觸發器的程式體，可以是一條SQL語句或者是用BEGIN和END包含的多條語句
是以可以說MySQL建立以下六種觸發器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE
BEFORE和AFTER參數指定了觸發執行的時間，在事件之前或是之後
FOR EACH ROW表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器
CREATE TRIGGER 觸發器名 BEFORE|AFTER 觸發事件
ON 表名 FOR EACH ROW
BEGIN
執行語句清單
END DELIMITER $$
CREATE TRIGGER tri_test1 AFTER DELETE
ON t1 FOR EACH ROW
BEGIN
INSERT INTO t2(tname) VALUES('ssss');
END$$
SELECT * FROM t1;
SELECT * FROM t2;
UPDATE t1 SET tname = 'aaa' WHERE tid = 1;
DELETE FROM t1 WHERE tid=1; 3.4 視圖（了解）
視圖是一個虛拟表，是sql的查詢結果，其内容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行資料，在使用視圖時動态生成。視圖的資料變化會影響到基表，基表的資料變化也會影響到視圖
1）簡單：使用視圖的使用者完全不需要關心後面對應的表的結構、關聯條件和篩選條件，對使用者來說已經是過濾好的複合條件的結果集。
2）安全：使用視圖的使用者隻能通路他們被允許查詢的結果集，對表的權限管理并不能限制到某個行某個列，但是通過視圖就可以簡單的實作。
3）資料獨立：一旦視圖的結構确定了，可以屏蔽表結構變化對使用者的影響，源表增加列對視圖沒有影響；源表修改列名，則可以通過修改視圖來解決，不會造成對通路者的影響。
總而言之，使用視圖的大部分情況是為了保障資料安全性，提高查詢效率。
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION] CREATE VIEW view_test1
AS
SELECT * FROM t1;
CREATE VIEW view_test2
AS
SELECT employee_id,first_name,manager_name FROM employee LEFT JOIN manager ON employee.`manager_id` = manager.`manager_id`; 3.5 索引和限制
3.5.1 限制
作用：是為了保證資料的完整性而實作的摘自一套機制，它具體的根據各個不同的資料庫的實作而有不同的工具（限制）；
1、非空限制：not null; 訓示某列不能存儲 NULL 值
2、唯一限制：unique(); unique限制的字段，要求必須是唯一的，但null除外；
3、主鍵限制：primary key(); 主鍵限制=not null + unique，確定某列（或兩個列多個列的結合）有唯一辨別，有助于更容易更快速地找到表中的一個特定的記錄。
4、外鍵限制：foreign key ;保證一個表中的資料比對另一個表中的值的參照完整性。
5、自增限制：auto_increment
6、預設限制：default 給定預設的值
7、檢查性限制：check 保證列中的值符合指定的條件。 3.5.2 索引
作用: **快速定位特定資料，提高查詢效率，確定資料的唯一性，快速定位特定資料；**可以加速表和表之間的連接配接，實作表與表之間的參照完整性，使用分組和排序語句進行資料檢索時，可以顯著減少分組和排序的時間全文檢索字段進行搜尋優化；
1、主鍵索引（primary key）;
2、唯一索引（unique）;
3、正常索引（index）;
4、全文索引（full text）;
全文索引是MyISAM的一個特殊索引類型，它查找的是文本中的關鍵詞，主要用于全文檢索。
MySQL InnoDB從5.6開始已經支援全文索引，但InnoDB内部并不支援中文、日文等，因為這些語言沒有分隔符。可以使用插件輔助實作中文、日文等的全文索引。
SHOW INDEX FROM table_name;
索引字段盡量使用數字型（簡單的資料類型）
盡量不要讓字段的預設值為NULL
使用唯一索引
使用組合索引代替多個列索引
注意重複/備援的索引、不使用的索引 不使用索引
1.查詢中很少使用到的列 不應該建立索引,如果建立了索引然而還會降低mysql的性能和增大了空間需求.
2.很少資料的列也不應該建立索引,比如 一個性别字段 0或者1,在查詢中,結果集的資料占了表中資料行的比例比較大,mysql需要掃描的行數很多,增加索引,并不能提高效率
3.定義為text和image和bit資料類型的列不應該增加索引,
4.當表的修改(UPDATE,INSERT,DELETE)操作遠遠大于檢索(SELECT)操作時不應該建立索引,這兩個操作是互斥的關系 搞定~