MySQL編程
MySQL 使用標準 SQL 檢索和處理數據，體積小、開源、免費，易於快速部署。正是因為這些特點，使得其在互聯網行業，特別是 Web 應用方面使用相當廣泛。至今最新的版本已到 8.0。
一 基本操作
MySQL 和 SQL Server 或 Oracle 不同，它沒有官方的圖形化操作管理軟體，它主要通過命令行的方式操作。
本文主要介紹 MySQL 編程相關的知識，安裝和部署不在本文講解範圍，對於 MySQL 的安裝和部署，請自行查閱官方文檔或其他資料。
1，基本命令
1 net start mysql80(服務名) 2 net stop mysql80 3 /* 啟動 mysql 服務，不同版本的 MySQL 服務名默認不一致，但安裝的時候可以自己指定 */ 4 mysql -h 主機地址 -u 用戶名 －p 用戶密碼 5 /* 登錄 MySQL 伺服器，如果是在本地登錄可以省略主機地址 */ 6 exit 7 /* 退出登錄 */
2，基礎語法
MySQL 採用標準 SQL 語法，他們同樣分為 DDL、DQL、DML、DCL。
詳情可以移步我的《SQL入門，就這麼簡單》。
3，注釋
MySQL 使用 # 標記單行注釋，/* */ 標記多行注釋。
1 #這是單行注釋 2 /* 3 這是多行注釋 4 */ 5 -- 這也是單行注釋
使用雙橫線注釋時，雙橫線後面至少需要一個空格。
4，語句結束
由於 MySQL 使用命令行的方式操作資料庫，所以對於每一條命令，我們必須明確的指定結束符。MySQL 默認的語句結束符是分號”;”。
1 1 語句 1; 2 2 語句 2; 3 3 delimiter $ 4 4 /* 通過 delimiter 指定新的結束標識 */ 5 5 語句3$ 6 6 語句4$
比如，我們在創建存儲過程或函數時，函數或存儲過程內部有多條語句，但創建過程本身也是一條語句，為了避免衝突，所以在創建存儲過程和函數時一般會臨時修改語句結束符。不要忘記在創建完後再把結束符改為默認的分號哦。
5，約束
MySQL 中對約束進行了分類：表約束和列約束，語法稍有差異。
1 create table myTable( 2 col1 type[AUTO_INCREMENT], 3 col2 type, 4 col3 type, 5 col4 type, 6 col5 type NOT NULL, 7 col6 type DEFAULT 'something', 8 PRIMARY KEY (col1), 9 FOREIGN KEY (col2) REFERENCES mytable1(somecol), 10 UNIQUE(col3), 11 CHECK(col4<200) 12 ); 13 /*AUTO_INCREMENT 設置主鍵自增，相似的，MsSQL 使用IDENTITY(1,1) 設置自增*/
列約束只有 NOT NULL 和 DEFAULT，其他的均屬於表約束。
6，查詢相關
HAVING 關鍵字對聚合後的數據進行再篩選。WHERE 關鍵字是對原始數據的篩選。
1 select a.dep,avg(a.sal) as 平均工資 from salary a 2 left join department b 3 on a.dep=b.dep 4 where b.sta_num>10 5 having avg(sal)>10000; 6 /* 連接查詢部門和部門平均工資，部門人數在10以上，且平均工資大於10000 */
LIMIT 關鍵字，可以用來返回指定條數的記錄，常用來做分頁查詢（MsSQL 使用 top 關鍵字）。limit 需要指定兩個 int 類型的參數，分別表示從哪條記錄開始（第一條數據索引為0），提取多少條記錄。當從第一條數據開始時，第一個參數可以省略。
1 /* 基礎用法：返回指定數量記錄 */ 2 select * from department order by sta_num limit 10; 3 /* 查詢人數最少的10各部門情況 */
1 /* 分頁查詢 */ 2 SET @num:=10;#定義用戶變數，稍後會詳細講解；每頁需要查詢的記錄數 3 SET @page:=2;#需要查詢第幾頁 4 SET@index=(@page-1)*@num;#開始索引 5 PREPARE mysql FROM "select * from country limit ?,?";#由於 limit 支援使用變數，所以必須先使用 prepare 預編譯 SQL 語句，？ 標識變數 6 EXECUTE mysql USING @index,@num;#使用 execute 執行預編譯的語句，並使用 using 傳遞參數，順序必須和預編譯時一致
子查詢又稱嵌套查詢，是指一個 SELECT 語句的查詢結果被嵌套在另一個語句之中，子查詢既可以放在 FROM 關鍵字後面，也可以放在 WHERE 後面。外部的語句可以是 SELECT、INSERT、DELETE、UPDATE。
1 select * from ( 2 selec * from tableA where col1>( 3 select min(col1) from tableB 4 ) 5 )a;
二 函數
1，字元函數
| 函數 | 功能 | 例子 | 說明 |
| concat() | 連接多個字元串 | concat(‘a’,’b’) | 連接『a』和『b』，返回新的字元串『ab』 |
| substr() | 截取字元串 | substr(‘abcde’,1,3) | 在字元串『abcde』中從第1個字元開始截取3個字元，並返回 |
| lower()/upper() | 字元串大小寫轉換 | lower(‘abD’) | 把字元串『abD』都轉換成小寫，並返回 |
| replace() | 替換指定字元 | replace(‘abcda’,’a’,’x’) | 把字元串『abcda』中的『a』都替換成『x』，並返回新的字元串 |
| length() | 計算字元串長度 | length(‘abcde’) | 返回字元串『abcde』的長度 |
| trim() | 去掉前後空格 | trim(‘ abc ‘) | 返回去掉了前後空格的新字元串 |
| lpad()/rpad() | 左右填充指定字元 | lpad(‘abc’,10,’-‘) | 用『-』填充字元串『abc』至長度為10 |
| instr() | 子串第一次出現的索引 | instr(‘abcde’,’c’) | 返回在『abcde』中第一次出現『c』的索引 |
2，數學函數
| 函數 | 功能 | 例子 | 說明 |
| ceil() | 向上取整 | ceil(1.1) | 2 |
| floor() | 向下取整 | ceil(1.9) | 1 |
| round() | 四捨五入 | round(2.5) | 3 |
| mod() | 取模 | mod(21,4) | 1 |
| truncate() | 截斷小數 | truncate(1.258,2) | 1.25（多餘的小數位直接截斷） |
| rand() | 返回隨機數 | rand() | 隨機數[0,1) |
| format() | 截斷位數 | format(1.258,2) | 1.26（最後一位四捨五入得來) |
3，日期函數
| 函數 | 功能 | 例子 | 說明 |
| now() | 返回當前日期時間 | now() | 2020-05-15 16:27:59 |
| year()/month()/day() | 返回年/月/日 | year(now()) | 2020 |
| hour()/minute()/secend() | 返回時/分/秒 | hour(now()) | 16 |
| curtime() | 返回時間部分 | curtime(now()) | 16:27:59 |
| curdate() | 返回日期部分 | curdate(now()) | 2020-05-15 |
| date_format() | 格式化日期 | date_format(now(),’%Y/%m/%d’) | 2020/05/15 |
| str_to_date() | 把字元串表示的日期轉換成日期格式 | str_to_date(‘2020-05-15 00:00:00’,‘%Y%m%d’) | 2020/05/15 |
| datediff() | 返回兩個日期之間的天數差 | datediff(now(),’2020-01-01′) | 135 |
4，聚合函數
| 函數 | 功能 | 例子 | 說明 |
| max() | 返回最大值 | max(列名) | 返回一列的最大值 |
| min() | 返回最小值 | min(列名) | 返回一列的最小值 |
| avg() | 返回平均值 | avg(列名) | 返回一列的平均值 |
| sum() | 返回和 | sum(列名) | 返回一列的和 |
| count() | 返回數量 | count(列名) | 返回一列的數量（不計算NULL） |
5，其他函數
A：IFNULL()
1 /*MySQL*/ 2 select IFNULL(NULL,1,2) 3 /*如果第一個參數是 NULL ，則返回第三個參數，否則返回第二個*/ 4 /*MsSQL*/ 5 select ISNULL(NULL,1) 6 /*如果第一個參數是 NULL，則返回第二個參數，否則原樣返回*/
B：IF()
1 IF(expr1,expr2,expr3) 2 /*如果 expr1 的值為 true，則返回 expr2 的值，否則返回 expr3 的值。*/
C：其他
1 SELECT DATABASE() 2 SELECT VERSION() 3 SELECT USER() 4 /* 分別返回當前連接資料庫，當前資料庫版本，當前用戶 */
三 編程對象
1，視圖
A：創建
1 use world; 2 create view v_1 3 as 4 select * from country limit 10; 5 /* mysql8.0 自帶一個 world 庫*/
B：刪除
1 drop view v_name;
C：修改
1 /*方式一：*/ 2 alter view v_name 3 as 4 語句; 5 /*方式二：*/ 6 create or replace view v_name 7 as 8 語句;
2，變數
MySQL 中變數分為 3 種：系統變數，回話變數，用戶變數，局部變數。
A：系統變數
1 SHOW GLOBAL VARIABLES; 2 /* 查看系統變數。當服務啟動時，它將所有系統變數初始化為默認值。要想更改全局變數，必須具有super許可權。 */
B：會話變數
1 SHOW SESSION VARIABLES; 2 /* 查看會話變數，只作用於當前連接，查看指定系統或會話變數使用：select @@+變數名 */
C：用戶變數
1 set @name='張三'; 2 set @age:=12; 3 select @sex='男'; 4 select @var:=max(col) from table 5 /* 用戶變數直接賦值即可，使用 select 賦值時只能使用 :=，這是為了讓系統避免混淆賦值和等於操作，推薦都使用 :=*/
D：局部變數
1 1 declare age int default 0; 2 2 set age:=12; 3 3 select @age:=18; 4 4 select max(col) into age from table; 5 5 /* declare 語句專門用於定義局部變數，可以使用 default 來說明默認值，直接賦值時，使用 set，則不需要使用 @ 標記變數，使用 select 則需要。如果使用查詢賦值，則需要使用 select into.
局部變數只能在 BENGIN…END 語句塊中使用，比如下面將要講到的存儲過程或函數中。如果如上例中那樣直接運行將報錯。
3，存儲過程
A：創建
1 create procedure proc_name(參數列表) 2 begin 3 語句塊; 4 end
參數包含三個部分：參數模式，參數名，參數類型。
參數模式也分為三種：IN（僅用來像存儲過程輸入），OUT（僅用來像存儲過程外部輸出），INOUT（即可以用來輸入也可以用來輸出）。
存儲過程也可以沒有參數，括弧內留空即可。
B：調用
1 call proc_name(實參列表);
1 DELIMITER $ 2 CREATE PROCEDURE p_1(IN cname VARCHAR(20),OUT cregion VARCHAR(20)) 3 BEGIN 4 SELECT region INTO cregion FROM country 5 WHERE country.name=cname; 6 END$ 7 /*修改默認結束符後，這種語法只能在命令行模式使用，圖形化介面會報錯（SQLyog，navicat等） 8 這裡使用的依然是 MySQL8.0 自帶的 world 庫*/
9 CALL('Aruba',@cregion);
10 SELECT @cregion;
C：刪除
1 drop procedure proc_name;
存儲過程是一組預編譯的 SQL 語句，使用存儲過程，可以提高程式碼的重用性，簡化操作，因為已經預編譯好了 SQL 程式碼，執行時減少了預編譯環節，可以提高執行效率，一般用來對錶數據進行增刪改。
4，自定義函數
A：創建
1 DELIMITER $ 2 CREATE FUNCTION func_name(參數列表) 3 RETURNS 返回類型 4 BEGIN 5 函數體 6 END$
函數的參數和存儲過程不同，只需要指定參數名和參數類型。並且單獨使用關鍵字 RETURNS 指定返回值和其類型。由於函數必須返回值，所以函數內部必須使用 RETURN 關鍵字。RETURN 建議放在函數末尾，因為它會阻斷後面語句的執行。
1 delimiter $ 2 create function f_1(num1 float,num2 float) 3 returns float 4 begin 5 declare sum float default 0; 6 set sum=num1+num2; 7 return sum; 8 end$ 9 select f_1(1.1,2.2)$
MySQL 不支援存儲過程和函數的修改，如果需要修改，必須刪除原來的，並新建同名的過程名或函數名。
1 create percedure|function if not exists name 2 ... 3 /* 創建之前先判斷是否已存在，免得報錯，刪除時也應該檢測是否不存在 */
5，事務
事務是由一組 SQL 語句組成的執行單元，該執行單元被視為一個不可分割的整體，單元內的語句要麼全部執行成功，要麼全部失敗，不允許某些成功，而另外一些執行失敗。如果單元中某一條語句執行失敗，則前面所有被成功執行語句將回滾（使其失效），即數據回到那些語句還沒執行時的狀態。如果所有語句被全部成功執行，那麼我們就說該事務被順利執行了。
總結起來，事務具備以下 ACID 特性：
A（Atomicity 原子性）：原子性是指事務是一個不可分割的執行單元。裡面的操作要麼都成功，要麼都失敗。
C（Consistency 一致性）：一致性是指事務必須是數據從一個一致性狀態過度到另一個一致性狀態。關於一致性，舉個例子：兩個人各有1000元錢，資料庫中存儲的錢總和是2000元，如果一個事務的操作是他們兩之前轉一次賬，那麼執行完事務之後資料庫中存儲的錢總和還應該是2000元。
I（Isolation 隔離性）：隔離性是指在被一個事務操作的數據，不應該再被另一個事務所干擾。迸發執行的各個事務之間不能相互干擾。
D（Durability 持久性）：持續性是指一個事務被提交以後不可恢復，它對數據的影響是永久性的，如果需要在該事務提交後再恢復到原始狀態，只能通過另一個事務。
MySQL 中事務分為兩類：顯示事務和隱式事務。
隱式事務不用顯式的指定開始和結束標記。比如 INSERT、UPDATE、DELETE語句都數據隱式事務，他們不需要指定開始和結束，執行就開啟並自動提交。
顯式事務是指有顯式指定開始和結束標記的事務，這類事務一般由用戶創建。由於系統默認是開啟了事務自動提交功能的，所以在創建顯式事務時我們必須先手動的關閉自動提交功能。
1 set autocommit=0; 2 select @@autocommit; 3 /*0為關閉，1為開啟，該設置只在本次會話生效*/
語法：
1 start transaction; 2 /*開啟顯式事務*/ 3 語句1； 4 savepoint point_name; 5 /*設置事務斷點，不是必須的*/ 6 語句2； 7 ... 8 rollback to point_name; 9 /*回滾到指定斷點，該斷點之前的將不會被回滾*/ 10 commit; 11 /*提交事務，所有的操作被提交到資料庫，源數據被修改*/
如果沒有設置事務斷點，那麼回滾將對所有事務中的語句生效，當然 rollback 後也不需要指定斷點名稱了。提交和回滾不一定會同時存在。
事務一般是結合應用程式來使用的，直接在 MySQL 中無法測試。
比如，在應用程式中，用戶輸入的資訊要被提交到資料庫，當用戶輸入完畢後，點擊提交，我們可以設置等待時間，並提示用戶是否確認提交。如果在等待時間結束之後用戶無任何操作，或用戶點擊確認，那麼我們直接提交事務，如果用戶點擊了取消，那麼我們可以回滾事務。
四 流程式控制制
1，選擇
A：CASE 結構
和其他程式語言一樣，MySQL 中的 case 語句也是用來實現多分支結構的。不同的是，MySQL 中的 case 不僅可以用來做等值判斷，還可以用來實現類似多重 IF 的邏輯。
等值判斷：
1 case 表達式或欄位 2 when 用來比較的值1 then 返回的值1或語句1; 3 when 用來比較的值2 then 返回的值2或語句2; 4 ... 5 else 返回的值n或語句n; 6 end case; 7 /* case 後面直接跟需要被做等值判斷的表達式或表欄位 */
區間判斷：
1 case 2 when 判斷條件1 then 返回的值1或語句1; 3 when 判斷條件2 then 返回的值2或語句2; 4 ... 5 else 返回的值n或語句n; 6 end case; 7 /* case 後面不跟任何值或表達式，在 when 後通過條件表達式來判斷所屬情況 */
如果把 case 結構作為表達式，那麼它可以被嵌套在其他語句中（then 後不需要分號），用在任何地方。如果作為獨立的語句使用，那麼必須在 begin…end 中。
B：IF 結構
IF 結構主要用來實現多重分支的結構，IF 結構只能包含在 BEGIN…END 中使用，不能作為獨表達式使用，如果想作為表達式，可以使用 IF() 函數，但它只能實現兩個分支。
1 if 判斷條件1 2 then 語句1; 3 elseif 判斷條件2 4 then 語句2; 5 ... 6 else 判斷條件n 7 then 語句n; 8 end if; 9 /* 與其他程式語言不同，這裡的 elseif 中間沒有空格 */
2，循環
MySQL 中的循環控制語句有兩個：iterate（結束本次循環，執行下一次循環，類似其他語言中的 continue），leave（結束整個循環，類似 break）。
另外，循環控制語句必須指明當前退出的是哪個循環。所以，如果你要使用它們精準控制循環執行，那麼你需要在循環語句最開始指明當前循環的別稱。當然，這不是必須的。
A：while 循環
1 [循環名稱:]while 循環條件 2 do 3 循環體; 4 end while [循環名稱]; 5 /* 通過判斷循環條件來決定是否執行循環體 */
B：repeat 循環
1 [循環名稱:]repeat 2 循環體; 3 until 循環條件 4 end repeat [循環名稱]; 5 /* 先執行一次循環，再檢查循環條件 */
C：loop 循環
1 [循環名稱:]loop 2 循環條件; 3 end loop [循環名稱]; 4 /* 沒有循環條件，只能通過循環控制語句結束 */
MySQL 中的循環語句都只能包含在 begin…end 中使用，所以一般在存儲過程和函數中使用。
