MySQL基礎知識（全）

WngShhng發表於2017-11-21

原文網址 : https://juejin.im/post/5a12d62bf265da431d3c4a01

MySql

MySQL Tutorials

1、概述

目前屬於Oracle，分成社群版和企業版，關係型資料庫。

1.1 目錄結構

bin：儲存可執行檔案
data：儲存資料檔案
doc：文件
include：儲存包含標頭檔案
lib：儲存庫檔案
share：錯誤訊息和字符集

配置檔案：my.ini

1.2 啟動和停止

在win cmd中使用net start/stop mysql來啟動和停止mysql服務

1.3 登入和登出

1.3.1 登入

mysql -uuer_name -ppassword -Pport -hhost
複製程式碼

可以只使用mysql -uuser_name -ppasswrod來開啟，後面的可以使用預設的值。-P是指埠號，預設3306。-h是指地址，預設127.0.0.1。

1.3.2 登出

在MySQL命令視窗輸入：exit, quit和\q之中任意一個即可。

1.4 修改輸入提示符

在登入時，通過-prompt來指定提示符，或者在處於命令列時使用prompt來指定。

在指定提示符的時候可以使用\D, \d, \h和\u來指示提示符顯示當前的日期、資料庫、伺服器和使用者。

1.5 查詢資料庫資訊

使用SELECT VERSION(), SELECT NOW(), SELECT USER(), SELECT DATABASE()可以分別用來顯示當前的資料庫版本、時間、操作使用者和資料庫。

1.6 註釋

行註釋：--
多行註釋：/**/

1.7 約定

資料庫操作指令大寫；資料庫相關的名稱小寫，且單詞之間用下劃線分開。

1.8 關於字元編碼

檢視mysql中當前編碼

show variables like 'char%
複製程式碼

檢視資料表的編碼格式

mysql> show create table <表名>;
複製程式碼

建立資料庫時指定資料庫的字符集

mysql>create database <資料庫名> character set utf8;
複製程式碼

建立資料表時指定資料表的編碼格式

create table tb_books (
    name varchar(45) not null,
    price double not null,
    bookCount int not null,
    author varchar(45) not null ) default charset = utf8;
複製程式碼

修改資料庫的編碼格式

mysql>alter database <資料庫名> character set utf8;
複製程式碼

修改資料表格編碼格式

mysql>alter table <表名> character set utf8;
複製程式碼

修改欄位編碼格式

mysql>alter table <表名> change <欄位名> <欄位名> <型別> character set utf8;
複製程式碼

window命令列中中文亂碼問題，連線之後使用

set names gbk;
複製程式碼

2、資料庫操作

2.1 建立

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 
[DEFAULT] CHARACTER SET [=] character_name
複製程式碼

DEFAULT用來說明使用預設編碼方式。預設編碼方式通過配置檔案my.ini 中的default-character-set中指定。
CHARACTER SET [=] character_name用來指定資料庫的字元編碼方式。
有了IF NOT EXISTS當指定資料庫已經存在的時候，就不會報錯了，否則會報錯，但是報的錯還是可以查詢到的。

建立表的時候使用備註：

CREATE TABLE test_table (
    test_grade int DEFAULT 1 COMMENT '等級'
) COMMENT = '測試表';
複製程式碼

2.2 顯示建立資料庫的SQL語句

SHOW CREATE DATABASE db_name;
複製程式碼

類似的，還有顯示錶的建立的SQL語句：SHOW CREATE TABLE tbl_name;

2.3 修改

修改資料庫的字元編碼方式的語句：

ALTER {DATABASE|SCHEMA} [DEFAULT] CHARACTER SET [=] charset_name;
複製程式碼

2.4 刪除

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name
複製程式碼

2.5 顯示當前所有資料庫

SHOW DATABASES;
複製程式碼

類似的，有SHOW TABLES [FROM db_name]用於顯示當前資料庫（或指定資料庫）下面的所有表。

3、資料型別

3.1 整型

整數分為有符號和無符號的，通過UNSIGNED指定。（取值範圍和C語言中的型別範圍一樣）

TiNYINT：1位元組
SMALLINT：2位元組
MEDIUMINT：3位元組
INT：4位元組
BIGINT：8位元組

3.2 浮點型

浮點數也分為有符號的和無符號的。如下所示，前面的m指定了總的位數，後面的n指定了小數的位數，所以整數位數為m-n. 和C語言中的float和double範圍一樣。

FLOAT[(m,n)]：
DOUBLE[(m,n)]：
DECIMAL[(m,n)]：適用於高精度要求的場景

3.3 時間型別

YEAR：1位元組，格式YYYY，範圍1901-2155，零值0000
TIME：3位元組，格式HH:MM:SS，範圍-838:59:59-838:59:59，零值00:00:00
DATE：4位元組，格式YYYY-MM-DD，範圍1000-01-01-9999-12-31，零值0000-00-00
DATETIME：8位元組，格式YYYY-MM-DD HH:MM:SS，範圍1000-01-01 00:00:00-9999-12-31 23:59:59，零值0000-00-00 00:00:00
TIMESTAMP：4位元組，格式YYYY-MM-DD HH:MM:SS，範圍19700101080001-2038年的某個時刻，零值00000000000000

或者使用時間戳，用BIGINT儲存指定時間的毫秒值。

3.4 字元型

CHAR(m)：m位元組，0<=m<=255
VARCHAR(m)：L+1位元組，L<=m<=65535
TINYTEXT：L+1位元組，L<2⁸
TEXT：L+2位元組，L<2¹⁶
MEDIUMEXT：L+3位元組，L<2²⁴
LONGTEXT：L+4位元組，L<2³²
ENUM('val1', 'val2', ...)：列舉型別，取決於列舉個數，最多65535
SET('val1', 'val2', ...)：主要用於對列舉進行組合選擇

4、資料表的操作

4.1 建立資料表

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type, 
    ...
);
複製程式碼

4.2 檢視資料庫的表

檢視指定資料庫的所有的表

SHOW TABLES [FROM db_name][LIKE 'pattern'|WHERE expr];
複製程式碼

4.3 檢視列定義

顯示指定表的各個列的定義：

SHOW COLUMNS FROM tbl_name;
複製程式碼

4.4 約束

4.4.1 表的空和非空

在建立表的時候，在定義列的後面加上NOT NULL來指定指定的列不可為空。這樣的列在插入記錄的時候必須對其進行賦值。

4.4.2 自增

自動編號，必須與主鍵組合使用。通過在建立表的時候在列定義後加入AUTO_INCREMENT來實現。此外，可以通過ALTER TABLE users AUTO_INCREMENT = 10000;來指定自增的開始值。

4.4.3 主鍵

必須保證唯一性，一個表只能有一個，非NULL。建立表的時候使用PRIMARY KEY來指定列是主鍵。

4.4.4 唯一約束

必須保證唯一性，一個表可以有多個，可以是NULL的。建立表的時候使用UNIQUE KEY來指定列是唯一的。

4.4.5 預設約束

當指定的列沒有指定值的時候就使用預設的值，在建立表的時候，通過使用DEFAULT關鍵字來指定列的預設值。

4.4.6 外來鍵約束

在實際開發過程中更多地使用邏輯外來鍵而不是物理外來鍵，就是隻要保證表之間的關聯關係就好，而不為表設定外來鍵。因為外來鍵要求建立表的時候，父表和子表都必須使用INNODB引擎。

4.5 修改表

4.5.1 新增單列

ALTER TABLE tbl_name ADD [COLUMN] col_name col_def [FIRST|AFTER col_name]
複製程式碼

向指定的表中加入一列。通過FIRST指定新加入的列處於所有列的最前方，通過AFTER col_name指定新加入的列相對於某個列的位置。

4.5.2 新增多個列

ALTER TABLE tbl_name ADD [COLUMN] (col_name col_def[, col_name col_def, ...])
複製程式碼

當向表中新增多個列的時候，只能將新加入列放在表的最後面的位置。

4.5.3 刪除列

ALTER TABLE tbl_name DROP [COLUMN] col_name[, col_name, ...]
複製程式碼

可以指定並刪除多個列。

4.5.4 新增約束

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](index_col_name)

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] UNIQUE [index_type](index_col_name)
複製程式碼

上面的兩條語句分別用來向指定的表中的列加入主鍵和唯一性約束。示例ALTER TABLE temp ADD UNIQUE(name)（列名上要加括號）。加入了主鍵和唯一性約束的同時會為指定的列加上索引，所以可以使用index_type來指定索引的型別。要為MySQL設定預設的索引，到my.ini中的default-storage-engine中進行設定即可。另外，可以使用SHOW INDEXES FROM tbl_name檢視指定表中存在的索引。

4.5.5 刪除約束

ALTER TABLE tbl_name DROP PRIMARY KEY;

ALTER TABLE DROP {INDEX|KEY} col_name;
複製程式碼

4.5.6 修改表定義

ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_def [FIRST|AFTER col_name]
複製程式碼

上面這種方式可以修改列的定義語句，它適用的範圍比較廣——可以通過在col_def中指定約束和資料型別來對列進行更多的修改。

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_def [FIRST|AFTER col_name]
複製程式碼

上面的修改語句的適用範圍更廣，它可以修改的範圍包括：列的資料型別、約束、位置和名稱。

4.5.7 修改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name 

RENAME TABLE tbl_anem TO new_tbl_name [, tbl_name TO new_tbl_name...]
複製程式碼

4.5.8 增加索引

主鍵索引, 新增PRIMARY KEY：ALTER TABLE tbl_name ADD PRIMARY KEY (col_name)
唯一索引, 新增UNIQUE：ALTER TABLE tbl_name ADD UNIQUE (col_name)
普通索引, 新增INDEX： ALTER TABLE ADD key(col_name)以及ALTER TABLE tbl_name ADD INDEX index_name (col_name)
全文索引, 新增FULLTEXT: ALTER TABLE tbl_name ADD FULLTEXT (col_name)
多列索引: ALTER TABLE tbl_name ADD INDEX index_name (col_name1, col_name2, ..)

5、插入操作

向表中插入記錄

INSERT [INTO] tbl_name [(col_name, ...)] {VALUES|VALUE} ({expr|DEFAULT}, ...), (...), ...
複製程式碼

以上用來向指定資料庫的指定列中插入資料，如果不指定了列的名稱，就必須對錶的所有的列進行賦值。可以為指定的列指定值，可以使用表示式，也可以使用預設值。

INSERT [INTO] tbl_name SET col_name = {expr|DEFAULT} [, col_name = {expr|DEFAULT}]

INSERT [INTO] tbl_name [(col_name, ...)] SELECT ...
複製程式碼

將查詢結果插入到指定的表中。比如INSERT INTO temp (name) SELECT name FROM assignment

6、更新操作

UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
SET col_name1 = {expr|DEFAULT} [, col_name2 = {expr|DEFAULT}] ... 
[WHERE where_condition]
複製程式碼

沒有指定WHERE語句，就對整個表的全部記錄進行更新。

7、刪除操作

DELETE FROM tbl_name [WHERE where_condition]
複製程式碼

8、查詢

SELECT select_expr [, select_expr ...] 
[
	FROM table_references
	[WHERE where_condition]
	[GROUP BY {col_name|position} [ASC|DESC], ... ]
	[HAVING having_condition]
	[ORDER BY {col_name|expr|position} [ASC|DESC], ...]
	[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
複製程式碼

8.1 查詢不同值

將DISTINCT關鍵字放在指定的列前面，用於檢索指定的列的不同的值。比如，select count(distinct cust_id) from orders;。

DISTINCT作用於所有的列，而不是其後的那一列。比如，select count(distinct cust_id, order_num) from orders;將搜尋出cust_id不同並且order_num不同的列。

8.2 限制結果集、分頁

select * from orders limit 2 offerset 2;  
select * from orders limit 2 , 2;
複製程式碼

類似於上面這樣，前面的數字2表示查詢的數目條數，後面的2表示從哪個位置開始。所以，上面的效果是查詢第2條和第3條資料。但是，要注意的是資料庫中條的編號的起始位置是0。如果不指定第二個2將查出所有資料中前兩個記錄。

8.3 排序結果集

使用ORDER BY語句。可以指定多個列進行排序，可以為要排序的列指定排序的方向，使用ASC表示增序排列，使用DESC表示降序排列。預設，升序排列。除了使用列名來指定要排序的列，害可以使用列的在查詢的所有列中的位置來指定排序的列。

DESC只應用到直接位於其前面的列，如果要為多個列進行降序，就應該每個列後面加上DESC。

8.4 過濾結果集

8.4.1 WEHER子句操作符

等於 =
不等於 <>和!=
小於和小於等於 <和<=
大於和大於等於 >和>=
為NULL值IS NULL
處於兩者之間BETWEEN

BETWEEN例項：

SELECT * FROM orders WHERE order_num BETWEEN 20006 AND 30000;
複製程式碼

8.4.2 AND和OR操作符

使用AND和OR操作符的時候要注意優先順序是AND>OR。比如，

select * from orders 
where order_num = 20006 or order_num = 200005 and cust_id = 1000000001;
複製程式碼

會被當成

select * from orders 
where order_num = 20006 or (order_num = 20005 and cust_id = 1000000001);
複製程式碼

而如果我們想要表達的意思是：

select * from orders 
where (order_num = 20006 or order_num = 200005) and cust_id = 1000000001;
複製程式碼

因此就要在OR操作符上面增加圓括號。

8.4.3 IN操作符

select * from orders where order_num in (20006, 20005);  
複製程式碼

IN操作符其實完成的操作和OR一樣。

8.4.4 NOT操作

select * from orders where not order_num = 20005;
複製程式碼

如上所示將not操作加在where條件的前面可以表示對條件的反。上面的效果就相當於where order_num != 20005

8.4.5 exists

select * from orders t 
where 1 = 1 
and exists(select * from customers where cust_id = t.cust_id);
複製程式碼

8.4.6 like

select * from products where prod_name like '%doll';
複製程式碼

上面的是查詢所有prod_name以doll結尾的記錄。還可以，比如Fish%用來匹配所有以Fish開頭的，F%y用來匹配以F開頭並且以y結尾的記錄。

萬用字元%可以用來匹配除NULL意外的記錄，即WHERE prod_name like '%'不會找出prod_name為NULL的記錄。

8.4.7 下劃線

下劃線用來匹配指定數量的字元。一個下劃線匹配一個字元。

8.5 建立計算欄位

8.5.1 拼接字串

select concat(cust_name,':',cust_city) from customers;
複製程式碼

如上所示，使用concat函式可以將查詢結果拼接起來。

8.5.2 使用別名

select concat(cust_name,':',cust_city) as cust_city from customers;
複製程式碼

如上所示，使用AS操作符來將拼接的字串命名為cust_city.

8.5.3 使用正規表示式

下面的是使用正規表示式對資料進行過濾的例子，即可以將REGEXP像like一樣使用，並在REGEXP後面加上正規表示式即可。下面的表示式檢索出的結果是，customers中所有cust_name中包含'Fun'的記錄。

select * from customers where cust_name REGEXP 'Fun';
複製程式碼

下面的SQL語句用來搜尋出所有prod_name開頭字元包含在1,2,8之內，並且字元後面是' inch'的記錄：

select * from products where prod_name regexp '[812] inch'
複製程式碼

8.6 分組資料

8.6.1 分組過濾GROUP BY

select order_num, count(order_num) from orderitems group by order_num;
複製程式碼

使用GROUP BY可以對資料進行分組，上面的效果是顯示出指定order_num的記錄的條數。

select t.*, sum(quantity*item_price) as total 
from orders as t, orderitems 
where t.order_num = orderitems.order_num 
group by order_num;
複製程式碼

上面的語句的執行的結果是，得到了orders表的全部資料，以及每條訂單對應的總價（根據orderitems表計算得出）。但是如果沒有加入Group BY語句就無法得到上面的結果。

可以使用下面的兩個SQL語句的執行結果來理解GROUP BY語句的效果：

select sum(quantity*item_price) from orderitems;
select sum(quantity*item_price) from orderitems group by order_num;
複製程式碼

如果沒加Group BY得到的是兩個列的每條記錄的兩個欄位相乘之後的總和，使用之後得到的是針對每個order_num的兩個欄位相乘之後的總和。前面的只得到了一條記錄，後面的得到了針對每個order_num的記錄。

下面的例子也使用了GROUP語句，但是這裡在分組的時候的依據不是order_num而是cust_id。這樣也是可以的，它得到的結果是：各個cust_id所應該支付的賬單總額。

select sum(i.quantity*i.item_price) as total
from orders as o, orderitems as i
where o.order_num = i.order_num
group by o.cust_id;
複製程式碼

如果我們再關聯一張customers表，來得到使用者的名稱，那麼我們可以寫成下面的樣子：

select c.cust_name as name, sum(i.quantity*i.item_price) as total
from orderitems as i, orders as o, customers as c
where i.order_num = o.order_num and c.cust_id = o.cust_id
group by c.cust_id;
複製程式碼

8.6.2 過濾分組HAVING

select sum(quantity * item_price) as total 
from orderitems 
group by order_num 
having total > 1000;
複製程式碼

如上所示，我們使用HAVING語句來對分組之後的結果進行過濾。上面是找出總價值在1000以上的分組。

8.7 子查詢

下面的使用了子查詢的語句執行的結果與上面的一樣：指定名稱使用者的總額。

select c.cust_name, (select sum(i.quantity*i.item_price) 
                     from orderitems as i, orders as o
                     where i.order_num = o.order_num and c.cust_id = o.cust_id 
                     group by o.cust_id) as total 
from customers as c;
複製程式碼

上面的這種方式是將子查詢用作計算欄位。

子查詢的另一種是用方式是將子查詢的結果放在一個IN中作為取值範圍。

8.8 聯結表

所謂的聯結可以理解成按照指定的方式將兩個或者多個表組合起來，形成一個類似於新的表。因為MySQL是關係型資料庫，所以經常會在多個表之間存在一對多或者多對多關係，而聯結的效果就是將這樣的表聯結起來。比如，如果兩個資料庫之間是一對多關係，那麼肯定有些欄位只存在於父表中，在查詢的時候我們要將這些存在於父表中的欄位與存在於子表中的欄位組合起來，形成一個完整的“資料”，就應該使用聯結來實現。

假如有這樣的資料：

在表1中儲存著：A₀B₀, A₀B₁ 在表2中儲存著：A₀C₀, A₀C₁, A₀C₂

這裡的A, B, C表示的是資料庫的列中儲存的記錄。A表示一個列中的資料，B也表示列中的資料，AB構成一個記錄。

那麼，如果我們對錶1和表2在A上面使用聯結將得到的記錄如下：

B₀C₀, B₀C₁, B₀C₂, B₁C₀, B₁C₁, B₁C₂

這就相當於我們將B中的與A相關的欄位補充了起來。

8.8.1 等值聯結

聯結表的最常見的形式是使用=將兩表的對應的欄位連線起來，這叫做等值聯結。

8.8.2 內聯結

下面是使用內聯結的一個實現：

select vend_name, prod_name
from vendors as v inner join products as p
on v.vend_id = p.vend_id;
複製程式碼

內聯結相當於對兩個表在指定的列上面取交集，即只有兩個表中都存在的欄位才會進行聯結。

8.8.3 自聯結

select *
from customers as c1, customers as c2
where c1.cust_name = c2.cust_name
and c2.cust_contact = 'Jim Jones';
複製程式碼

上面的SQL語句的是自聯結的，需要為兩個相同的表分別指定一個別名。

8.8.4 外聯結

所謂的外聯結，就是指左聯結和右聯結。它跟內聯結不同的地方只在於，以左聯結為例，如果左表中被用來指定聯結的值在右表中不存在，那麼也一樣將左表中的記錄檢索出來，只是右表中指定的欄位為NULL。

select vend_name, prod_name
from vendors as v left join products as p
on v.vend_id = p.vend_id;
複製程式碼

比如上面的SQL中，如果vendors中的vend_id在右表中不存在，那麼prod_name作為NULL。同理，可得右聯結。

聯結的語法：

LEFT [OUTER] JOIN，左外連線。
RIGHT [OUTER] JOIN，右外連線。
複製程式碼

這裡的OUTER是可選的，有沒有都行。

8.8.5 組合查詢UNION

select prod_name from products
union
select vend_name from vendors;
複製程式碼

使用UNION可以將兩個分別獨立的SQL查詢的結果合併起來。（但是要求兩個SQL檢索出的列的數目要相等，其實它的效果和OR差不多）

如果查詢出來的兩條記錄一樣，重複的行會被取消，可以使用UNION ALL關鍵字來替代UNION，這樣重複的行就不會被取消。

使用UNION組合查詢的時候，只能有一條ORDER BY子句，且必須位於最後一條SELECT語句的後面。

9、檢視

檢視可以用來簡化SQL操作，你可以將一次的查詢結果作為一個檢視，併為其新增一個名稱，比如A。然後，我們可以像使用一個表一樣從A中檢索出資料。

檢視的建立語句如下：

create view orderdetail as
select o.*, i.order_item, i.prod_id, i.quantity, i.item_price
from orders as o left join orderitems as i
on o.order_num = i.order_num;
複製程式碼

即在一個標準的查詢語句上面增加一個create view orderdetail as語句來建立檢視。建立完畢檢視之後，我們就可以像使用表一樣從檢視中檢索資料。比如：

select * from orderdetail order by cust_id;
複製程式碼

10、儲存過程

儲存過程和檢視相似，也是提供一種SQL複用的機制。因為本身檢視只能用來將指定的資料檢索出來作為資料表一樣使用，它本身只能用於“查詢”操作，具有一定的侷限性。因此，這裡有儲存過程來將指定功能的SQL封裝起來，使其像一個函式一樣可以被呼叫。

儲存過程的可移植性比較差，而且需要更高的技能和經驗，因此通過被限制建立。

10.1 建立儲存過程

10.1.1 建立無參儲存過程

在MySQL命令列中建立儲存過程的示例：

mysql> delimiter //
mysql> create procedure mypp()
    -> begin
    -> select * from orders;
    -> end//
複製程式碼

在這裡delimiter //的作用是使用//而不是';'作為語句分隔符。

10.1.2 為儲存過程新增引數

MySQL支援IN（傳遞給儲存過程）、 OUT（從儲存過程傳出，如這裡所用）和INOUT（對儲存過程傳入和傳出）型別的引數。

mysql> create procedure avgprice(in num int, out price decimal(8,2))
    -> begin
    ->     select avg(item_price)
    ->     from orderitems
    ->     where order_num = num
    ->     into price;
    -> end//
複製程式碼

上面的語句中建立了一個名為avgprice的儲存過程。它接受兩個引數，num是傳入的引數；price是傳出的引數。在儲存過程內部，我們為orderitems的指定order_num為num的所有記錄的平均item_price賦值非price.

建立完畢了儲存過程之後，我們可以像下面這樣呼叫以上儲存過程：

call avgprice(20009, @price);
複製程式碼

然後，我們可以使用SELECT語句得到price的計算結果：

select @price
複製程式碼

10.2 使用儲存過程

沒有引數的儲存過程的使用示例：

call mypp();
複製程式碼

10.3 刪除儲存過程

drop procedure mypp;
複製程式碼

10.4 檢查儲存過程

show create procedure avgprice;
複製程式碼

11、遊標

MySQL遊標只能用於儲存過程（和函式）

mysql> create procedure copyprodname()
    -> begin
    ->     --定義區域性變數，結束條件
    ->     declare done boolean default 0;
    ->     --定義區域性變數，產品名稱
    ->     declare name varchar(255);
    ->
    ->     --定義遊標
    ->     declare names cursor
    ->     for
    ->     select prod_name from products;
    ->
    ->     --定義迴圈結束條件
    ->     declare continue handler for sqlstate '02000' set done = 1;
    ->
    ->     --建立表
    ->     create table if not exists prod_name(name varchar(255));
    ->
    ->     --開啟遊標
    ->     open names;
    ->
    ->     --定義迴圈
    ->     repeat
    ->     --獲取遊標的資料，將其填充到name中
    ->     fetch names into name;
    ->     --將遊標中的資料插入到prod_name表中
    ->     insert into prod_name values(name);
    ->     --定義迴圈的結束條件
    ->     until done end repeat;
    ->
    ->     --結束遊標
    ->     close names;
    -> end//
複製程式碼

在上面的程式碼中，使用

declare done boolean default 0;
複製程式碼

定義了迴圈的條件為布林型別，並將其預設值設定為0. 然後，使用定義了迴圈結束的條件

declare continue handler for sqlstate '02000' set done = 1;
複製程式碼

這裡的'02000'是一個未找到條件，當REPEAT由於沒有更多的行供迴圈而不能繼續時，出現這個條件。在使用遊標之前，需要先開啟遊標。這通過SQL語句：

open names;
複製程式碼

來完成。當我們執行完業務邏輯之後，需要關閉遊標。這通過SQL語句：

close names;
複製程式碼

來完成。在遊標的開啟和關閉的作用域內，我們通過

fetch names into name;
複製程式碼

將從遊標中得到的資料填充到name中。

12、觸發器

觸發器用來在某事件發生時自動執行，有些類似於監聽器的作用。它只能為delete, insert, update三種操作設定觸發器。觸發器可以設定在指定的操作執行之前或者之後觸發。

只有表才支觸發器，檢視和臨時表都不支援。

每個表最多設定6個觸發器，且同一觸發器不能與多個事件或多個表關聯。

12.1 建立觸發器

觸發器建立的基本語法格式：

CREATE TRIGGER <觸發器名稱>  --觸發器必須有名字，最多64個字元
{ BEFORE | AFTER }  --觸發器有執行的時間設定：可以設定為事件發生前或後。
{ INSERT | UPDATE | DELETE }  --觸發的事件：insert、update或delete的過程中觸發。
ON <表名稱>  --觸發器是屬於某一個表的:當在這個表上執行插入、 更新或刪除操作的時候就觸發。
FOR EACH ROW  --觸發器的執行間隔：FOR EACH ROW子句通知觸發器 每隔一行執行一次動作，而不是對整個表執行一次。
<觸發器SQL語句>  --觸發器包含所要觸發的SQL語句：這裡的語句可以是任何合法的語句， 包括複合語句，但是這裡的語句受的限制和函式的一樣。
複製程式碼

觸發器的使用示例：

mysql> create trigger watcher after insert on prod_name
    -> for each row
    -> begin
    ->     insert into prod_name_copy values(new.name);
    -> end//
複製程式碼

上面的觸發器監聽prod_name表的插入操作，每當向表prod_name中插入一條記錄的同時向prod_name_copy也插入一條記錄。

insert觸發器程式碼內，可以引用一個名為new的虛擬表，訪問被插入的行。

delete觸發器程式碼內，可以引用一個名為old的虛擬表，訪問被刪除的行。

update觸發器程式碼內，可以使用一個名為old的虛擬表，訪問更新之前的資料，使用名為new的虛擬表，訪問更新的值。

12.2 刪除觸發器

DROP TRIGGER 觸發器名稱：
複製程式碼

13、事務管理

事務保證了一系列操作的原子性，只有當執行過程中沒有產生錯誤，並且提交了之後才會將執行的結果反應到資料庫上。如果使用了回滾操作將回復到開始事務之前的狀態。

MySQL中的資料庫引擎分別支援不同型別的事務。

13.1 基本的事務操作

開始事務可以使用下面的語句：

START TRANSACTION;
複製程式碼

回滾事務使用：

ROLLBACK;
複製程式碼

提交事務使用：

COMMIT;
複製程式碼

13.2 建立保留點

可以使用語句

SAVEPOINT 名稱;
複製程式碼

來建立保留點。在準備回滾的時候，我們可以使用

ROLLBACK TO 名稱;
複製程式碼

來回滾到指定的保留點。這樣指定保留點之前的操作不會回滾，而保留點之後的操作將全部被會滾。

通常，保留點越多越好。當提交或者回滾之後，保留點會被自動釋放。此外，也可以使用RELEASE來主動釋放保留點。

13.3 修改預設提交行為

使用

SET autocommit = 0;
複製程式碼

來設定不自動提交。

14、安全管理

在MySQL的資料庫中存在一個mysql庫，那裡面的user表用於儲存MySQL的使用者。

create user shouheng identified by 'psd';
複製程式碼

上面的SQL用來MySQL中新增一個使用者。如果要對使用者進行重新命名，可以使用：

rename user little_boy to shouheng;
複製程式碼

如果刪除使用者可以使用：

drop user username;
複製程式碼

檢視授予使用者的許可權：

show grants for shouheng;
複製程式碼

授予許可權：

grant select on sql_test_db.* to shouheng;
複製程式碼

以上操作用來將sql_test_db的所有資料的只讀許可權授予指定使用者。如果要回收指定使用者的許可權，可以使用

 revoke select on sql_test_db.* from shouheng;
複製程式碼

它表示回收指定使用者對sql_test_db的只讀許可權。

除了上面示例的只讀許可權以外，MySQL還允許為新加入的使用者指定其他許可權。

為指定的使用者修改密碼：

 set password for shouheng = Password('psd');
複製程式碼