21．創建和操作表
21.1.創建表
CREATE TABLE創建表，必須給出下列信息：
1） 新表的名字，在關鍵字CREATETABLE之後
2） 表列的名字和定義，用逗號分隔
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOTNULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
primaryKEY (cust_id)
) ENGINE = InnoDB;
有以下幾點需要注意：
1）其中主鍵也可以用多個列組成，如orderitems表中在主鍵表示如下：
PRIMARY KEY(order_num,order_item)
主鍵只能使用不允許NULL值的列。
2）每個表只允許一個AUTO_INCREMENT列。可用SELECTlast_insert_id()獲取最後一個AUTO_INCREMENT值。
3）用DEFAULT指定默認值
4)引擎類型
InnoDB是一個可靠的事物處理引擎，它不支持全文本搜索
MEMORY在功能上等同於MyISAM,但由於數據存儲在內存中，速度很快（特適合於臨時表）
MyISAM是一個性能極高的引擎，它支持全文本搜索，但不支持事物處理
引擎可以混用，但外鍵不能誇引擎。
21.2更新表
必須提供以下信息：
1）在ALTER TABLE之後給出要更改的表名
2）所做更改的列表
ALTER TABLE vendors ADD vend_phoneCHAR(20);//增加一個新列
ALTER TABLE vendors DROP vend_phone;//刪除一個列
定義外鍵：
ALTER TABLE orderitems ADD CONSTRAINTfk_orderitems_orders FOREIGN KEY (order_num) REFERENCE orders(order_num);
21.3刪除表
DROP TABLE customers;
21.4重命名表
RENAME TABLE backup_customers TO customers;
22.使用視圖
視圖是虛擬的表。與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢。
視圖不包含表中應該有的任何列或數據，它包含的是一個SQL查詢。視圖僅僅用來查看存儲在別處數據的一種設施。視圖本身不包含數據，因此它們返回的數據是從其他表中檢索出來的。在添加或更改這些彪中國的數據時，視圖將返回改變過的數據。每次使用視圖時，都必須處理查詢執行時所需要的任一檢索。
視圖不能索引，也不能有關聯的觸發器或默認值。
使用視圖：
1）視圖用CREATE VIEW來創建
2）使用SHOW CREATE VIEWviewname;來查看創建視圖的語句
3）用DROP刪除視圖，其語法為DROPVIEW viewname;
4) 更新視圖時，可以先用DROP再用CREATE,也可以直接用CREATEOR REPLACE VIEW。
22.1利用視圖簡化復雜的聯結
mysql> CREATE VIEW productcustomers ASSELECT cust_name,cust_contact,prod_id FRO
M customers,orders,orderitems WHEREcustomers.cust_id = orders.cust_id AND order
items.order_num = orders.order_num;
Query OK, 0 rows affected (0.13 sec)
為檢索訂購產品TNT2的客戶，可如下執行：
mysql> SELECT cust_name,cust_contactFROM productcustomers WHERE prod_id = 'TNT2
';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | YLee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.00 sec)
22.2用視圖重新格式化檢索出的數據
SELECT * FROM productcustomers;
22.2視圖的更新
有時，視圖是可更新的（即可以對它們使用INSERT、UPDATE和DELETE），對視圖增加或刪除行，實際上是對其基表增加或刪除行。
但是，如果視圖定義中有以下操作，則不能進行更新：
1） 分組（使用GROUP BY和HAVING）
2） 聯結
3） 子查詢
4） 並
5） 聚集函數
6） DISTINCT
7） 導出（計算）列
23.使用存儲過程
存儲過程簡單來說，就是為以後的使用而保存的一條或多條MySQL語句的集合。可將其視為批文件，雖然它們的作用不僅限於批處理。
存儲過程有3個主要的好處：簡單、安全、高性能。
23.1執行存儲過程
MySQL稱存儲過程的執行為調用，因此MySQL執行存儲過程的語句為CALL。CALL接受存儲過程的名字以及需要傳遞給它的任意參數。
CALL producpricing(@pricelow,@pricehigh,priceaverage);
其中，執行名為productpricing的存儲過程，它計算並返回產品的最低、最高和平均價格。存儲過程可以顯示結果，也可以不顯示結果。
23.2創建過程
CREATE PROCEDURE productpricing()
BEGIN
SELECTAve(prod_price) AS priceaverage
FROMproducts;
END;
此存儲過程名為productricing,用CREATE PROCEDURE productpricing()語句定義。如果存儲過程接受參數，它們將在()中列舉出來。此存儲過程沒有參數，但後跟的()仍然需要。BEGIN和END語句用來限定存儲過程體，過程體本事僅是一個簡單的SELECT語句。
在MySQL處理這段代碼時，它創建一個新的存儲過程productpricing。沒有返回數據，因為這段代碼並未調用存儲過程，這裡只是為以後使用而創建它。
如果使用MySQL命令行使用程序，由於默認的MySQL語句結束符為；，則存儲體內的；字符將不會最終成為儲存過程的成分，這會導致語法錯誤。解決辦法是使用新語句分隔符。DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECTAvg(prod_price) AS priceaverage
FROMproducts;
END//
DELIMITER;
其中DELIMITER//告訴命令行實用程序使用//作為新的語句結束符，可以看到表示存儲過程結束的END定義END//而不是END;作為語句結束。最後使用DELIMITER ；恢復原來的語句結束符。然後調用結果如下：
mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
1 row in set (0.30 sec)
Query OK, 0 rows affected (0.30 sec)
23.2刪除存儲過程
DROP PROCEDURE productpricing;//當不存在時報錯
DROP PROCEDURE productpricing IF EXISTS;//僅當存在時刪除，不存在時不報錯
23.3使用參數
一般，存儲過程並不顯示結果，而是把結果返回給你指定的變量。
變量：內存中一個特定的位置，用來臨時存儲數據。所有MySQL變量必須以@開始。
CREATE PROCEDURE order（IN onumberINT, OUT ototal DECIMAL(8,2)）
BEGIN
SELECTSum(item_price*quantity)
FROMorderitems
WHEREorder_num = onumber
INTOototal;
END;
此存儲過程接受兩個參數，每個參數必須指定參數類型。關鍵字IN指出相應參數用來傳遞給存儲過程，OUT（從存儲過程傳出），INOUT（對存儲過程傳入和傳出）。存儲過程的代碼位於BEGIN和END語句內。
為調用這個存儲過程，可使用如下語句：
CALL ordertotal(2005,@total);
為顯示此合集，如下
SELECT @total;
為得到另一個訂單的合計信息，需要再次調用存儲過程
CALL ordertotal(2009,@total);
SELECT @total;
23.4 建立智能存儲過程
只有在存儲過程內包含業務規則和智能處理時，他們的威力彩真正顯現出來。
CREATE PROCEDURE ordertotal(
INonumber INT,
INtaxable BOOLEAN,
OUTototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionallyadding tax'
BEGIN
--Declare variable for total
DECLARE total DECIMAL(8,2);
--DECLARE tax percentage
DECLARE taxrate INT DEFAULT 6;
--Getthe order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
--Is this taxable?
IFtaxable THEN
--Yes, so add taxrate to total
SELECT total+(total/100*taxrate) INTO total;
ENDIF;
--And finally,save to out variable
SELECT total INTO ototal;
END；
這裡COMMERNT關鍵字不是必須的，但如果給出，將在SHOW PROCEDURE STATU的結果中顯示。
MySQL除了IF語句,還指出ELSEIF和ELSE子句（ELSEIF還須用THEN, ELSE不使用）
調用並顯示如下結果1:如下
mysql> CALL ordertotal(20005,0,@total);
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
調用並顯示如下結果2如下
mysql> CALL ordertotal(20005,1,@total);
Query OK, 0 rows affected, 1 warning (0.00sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)
23.5檢查存儲過程
為了顯示用來創建一個存儲過程的CREATE語句，使用SHOW CREATE PROCEDURE語句：
SHOW CREATE PROCEDURE ordertotal;
為了獲得包括何時，由誰創建等詳細信息的存儲過程列表，使用SHOW PROCEDURE STATUS,並且可用LIKE指定一個過濾模式，例如
SHOW PROCEDURE STATUS LIKE ‘ordertotal’;
24使用游標
有時，需要在檢索出來的行中前進或後退一行或多行。這就是使用游標的原因。游標是一個存儲在MySQL服務器上的數據庫查詢，它不是一條SELECT語句，而是被該語句檢索出來的結構集。在存儲了游標之後，應用程序可以根據需要滾動或浏覽或更改其中的數據。
24.1使用游標
步驟：
1) 在使用有游標前，必須聲明它。這個過程實際上沒有檢索數據，它只是ing醫藥使用的SELECT語句
2) 一旦聲明後，必須打開游標供使用。這個過程用前面定義的SELECT語句把數據實際檢索出來。
3) 對於填有數據的游標，根據需要取出各行
4) 在結束游標使用時，必須關閉游標
24.2創建游標和使用游標數據
在一個游標被打開後，可以使用FETCH語句分別訪問它的每一行，FETCH指定檢索什麼數據，檢索出來的數據存儲在什麼地方。它還向前移動游標中的內部行指針，使下一條FETCH語句檢索下一行。
舉例如下：
CREATE PROCEDURE processorders()
BEGIN
--Declarelocal variables
DECLAREdone BOOLEAN DEFAULT 0;
DECLAREo INT;
DECLAREt DECIMAL(8,2);
--Declarethe cursor
DECLAREordernumbers CURSOR
FOR
SELECTorder_num FROM orders;
--Declarecontinue handler
DECLARECONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;
--Createa table to store the results
CREATETABLE IF NOT EXISTS ordertotals
(order_numINT, total DECIMAL(8,2));
--Openthe cursor
OPENordernumbers;
--Loopthrough all rows
REPEAT
--Getorder number
FETCH ordernumbers INTO o;
--Getthe total for this order
CALL ordertotal(o,1,t);
--Insertorder and total into ordertotals
INSERTINTO ordertotals(order_num,total)
VALUES(o,t);
--ENDOF LOOP
UNITLdone END REPEAT;
--Closethe cursor;
CLOSEordernumbers;
END;
在這個例子中，FETCH在REPEAT內，因此它反復執行直到done為真（由UNTIL done END REPEAT;規定）。為使它起作用，用一個DEFAULT 0定義變量done。然後用DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done =1;這條語句定義一個CONTINUEHANDLER,它是在條件出現時被執行的代碼。這裡，它指出當SQLSTATE ‘02000’出現時，SET done=1。SQLSTATE ‘02000’是一個未找到條件，當REPEAT由於沒有更多的行工循環而不能繼續時，出現這個條件。這裡還調用了另外一個存儲過程CALL ordertotal(o,1,t);這是在前一章創建的存儲過程，實現計算每個訂單帶稅額合計。此存儲過程不返回數據，但它能夠創建和填充另一個表。
可以用以下語句執行存儲過程和查看存儲結果：
mysql> CALL processorders();
mysql> SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20009 | 40.78 |
+-----------+---------+
6 rows in set (0.00 sec)
這樣我們就得到了存儲過程，游標，逐行處理以及存儲過程調用其他存儲工程的一個完整的工作樣例。
25.使用觸發器
25.1觸發器
觸發器是MySQL響應一下任意語句而自動執行的一條MySQL語句（或位於BEGIN和END語句之間的一組語句）：
DELETE
INSERT
UPDATE
25.2創建觸發器
創建觸發器時，需要給出4條信息：
1) 唯一的觸發器名；
2) 觸發器關聯的表
3) 觸發器應該響應的活動（DELETE、INSERT或UPDATE）
4) 觸發器何時執行(處理之前或之後)
只有表可以支持觸發器，視圖不支持（臨時表也不支持）。
CREATE TRIGGER newproduct AFTER INSERT ONproducts FOR EACH ROW SELECT 'd';
這裡用CREATE TRIGGER創建一個名為newproduct的新觸發器。觸發器可在一個操作發生之前或之後執行，這裡給出了AFTER INSERT，所以觸發器將在INSERT語句成功後執行。這個觸發器還制定了FOR EACH ROW，因此代碼對每個插入行執行。在這個例子中，文本Product added將對每個插入的行顯示一次。PS.在我的MySQL版本中，不能成功實現，會提示不能返回結果集Not allowed to return a result set from a trigger錯誤。不知道是MySQL版本問題還是MySQL必知必會中的錯誤。
觸發器按每個表每個時間每次的定義，每個表每個時間每次只能定義一個觸發器，因此一個表最多支持6個觸發器（INSERT,UPDATE和DELETE之前或之後）。
25.3刪除觸發器：
DROP TRIGGER newproduct;
25.4使用觸發器
25.4.1 INSERT觸發器
1)在INSERT觸發器代碼內，可引用一個名為NEW的虛擬表，訪問被插入的行；
2)在BEFORE INSERT觸發器中，NEW中的值可以被更新（允許更改被插入的值）
3）對於AUTO_INCREMENT列，NEW在INSERRT執行之前包含0，在執行之後包含新的自動生成的值。
CREATE TRIGGER neworder AFTER INSERT ONorders FOR EACH ROW SELECT NEW.ORDER_num;
25.4.2DELETE觸發器
1）在DELEYE觸發器代碼內，你可以引用一個名為OLD的虛擬表，訪問被刪除的行
2）OLD中的值全部都是只讀的，不能更新。
CREATE TRIGGER deleteorder BEFORE DELETE ONorders
FOR EACH ROW
BEGIN
INSERT INTO
archive_orderss(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;
25.5 UPDATE觸發器
1)可以用OLD的虛擬表訪問以前的值，也可以用名為NEW的虛擬表訪問新更新的值
2）在BEFFORE UPDATE觸發器中，NEW中的值可能也被更新
3）OLD中的值全部都是只讀的，不能更新
26.管理事務處理
事務處理可以用來維護數據庫的完整性，它保證成批的MySQL操作要麼完全執行，要麼完全不執行。
術語：
事務：指一組SQL語句
回退：指撤銷指定SQL語句的過程
提交：指將未存儲的SQL語句結果寫入數據庫表
保留點：值事務處理中設置的臨時占位符，你可以對它發布回退。
26.1控制事務處理
管理事務處理的關鍵在於將SQL語句組分解為邏輯塊，並明確規定數據何時應該回退，何時不應該回退。
START TRANSACTION//標記是事務開始
26.2使用ROLLBACK
ROLLBACK用來回退MySQL語句
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
依次執行上面語句，可以看到刪除的表格內容又被回退了。
ROLLBACK只能在一個事務處理內使用（在執行一條STARTTRANSACTION命令之後）。
26.3使用COMMIT
一般的MySQL都是直接對數據庫表執行和編寫的，這是隱含提交。但在事務處理塊中，提交不會隱含地進行。為進行明確的提交，使用COMMIT語句，如下所示：
START TRANACTION;
DELETE FROM orderitems WHERE order_num =20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
如果第一條起作用，第二條失敗，則事務不處理，也就是兩條DELETE不會被提交。
26.4使用保留點
簡單的ROLLBACK和COMMIT語句就可以寫入或撤銷整個事務處理。但是，只是對簡單的事務處理才嫩這樣做，更復雜的事務處理可能需要部分提交或回退。
為了支持回退部分事務處理，必須使用保留點，可如下使用SAVEPOINR語句：
SAVEPOINT delete1;
每個保留點都表示它的唯一名字，以便回退時，MySQL知道要回退到何處。
ROLLBACK TO delete1;
26.5 更改默認的提交行為
為指示MySQL不自動提交更改，需要使用以下語句:
SET autocommit = 0;
27.全球化和本地化
CREATETABLE mytable
(
Column1 INT,
Column2 VARCHAR(0)
)DEAULT CHARACTER SET hebrew CLLATEhebrew_general_ci;
這條語句創建一個表，並且制定一個字符集和一個校對順序
SHOW CHARACTER SET;//可以查看所支持的字符集完整列表
SHOW COLLATION; //查看所支持的校對完整列表
28．安全管理
管理用戶
USE mysql;
SELECT user FROM user;
Mysql數據庫有一個名為user的表，它包含所有用戶賬號。User表有一個user列。
28.1創建用戶賬戶
為創建新用戶賬戶，使用CREATE USER語句
CREATE USER ben IDENTIFIED BY ‘P@$$w0rd’;
創建一個用戶名為ben的用戶，口令為P@$$w0rd。
RENAME USER ben TO bforta;
28.2刪除用戶賬號
DROP USER bforta;
28.3設置訪問權限
mysql> SHOW GRANTS FOR bforta;
+-------------------------------------------------------------------------------
------------------------+
| Grants for bforta@%
|
+-------------------------------------------------------------------------------
------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'IDENTIFIED BY PASSWORD '*A6210E6C376AADB5A6
9274F8C3D15B788433A9EB' |
+-------------------------------------------------------------------------------
------------------------+
1 row in set (0.00 sec)
輸出結果顯示用戶bforta有一個權限USAGE ON *.*。USAGE表示沒有權限，所以USAGE ON *.*表示在任意數據庫和人意表上對任何東西都沒有權限。
使用GRANT設置權限，需要給出以下信息：
1）要授予的權限
2）被授予訪問權限的數據庫或表
3） 用戶名
GRANT SELECT ON crashcourse.* TO bforta;
賦予bforta在crashcourse.*擁有SELECT權限。
在此顯示權限如下：
SHOW GRANTS FOR bforta;
+-------------------------------------------------------------------------------
------------------------+
| Grants for bforta@%
|
+-------------------------------------------------------------------------------
------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'IDENTIFIED BY PASSWORD '*A6210E6C376AADB5A6
9274F8C3D15B788433A9EB' |
| GRANT SELECT ON `crashcourse`.* TO'bforta'@'%'
|
+-------------------------------------------------------------------------------
------------------------+
2 rows in set (0.00 sec)
可以用REVOKE取消權限。
REVOKE SELECT ON crashcourse.* FROM bforta;
28.4更改口令
更新用戶口令
SET PASSWORD FOR bforta = Password(‘n3wp@$$w0rd’);
設置自己的口令
SET PASSWOR = Password(‘n3w p@$$w0rd’);
第29章數據庫維護
ANALYZE TABLE：用來檢查表鍵是否正確
CHECK TABLE:用來針對許多問題對表進行檢查
