高性能 SQL 計劃 Day 4

今天計劃繼續(xù)斯棒，主要內(nèi)容是《MySQL必知必會》19-22章的內(nèi)容。

Ch 19.插入數(shù)據(jù)

1.插入一行

#第一種方式
INSERT INTO Customers
VALUES(NULL,
   'Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA',
   NULL,
   NULL);
#VALUES中值的順序必須與表中列的順序一致
   
#第二種方式
INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country,
   cust_contact,
   cust_email)
VALUES('Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA',
   NULL,
   NULL);
#VALUES中值的順序與前面列的順序一致即可痹愚，無需與表中列定義的順序一致仅仆。

使用第二種方式時，如果表的定義允許谨垃，則可以在INSERT操作中省略某些列启搂。省略的列必須滿足以下條件之一：

該列定義為允許NULL值（無值或空值）。
在表定義中給出默認值刘陶。這表示如果不給出值胳赌，將使用默認值。

如果對表中不允許NULL值且沒有默認值的列不給出值匙隔，則MySQL將產(chǎn)生一條錯誤消息疑苫，并且相應(yīng)的行插入不成功。

關(guān)于主鍵的處理：表中 cust_id 為自增主鍵，第一種方式中需要給這一列賦值為NULL捍掺，第二種可以直接省略該列撼短。

注意：不管使用哪種INSERT語法，都必須給出VALUES的正確數(shù)目挺勿。如果不提供列名曲横，則必須給每個表列提供一個值。如果提供列名不瓶，則必須對每個列出的列給出一個值禾嫉。如果不這樣，將產(chǎn)生一條錯誤消息蚊丐，相應(yīng)的行插入不成功熙参。

提高整體性能： 數(shù)據(jù)庫經(jīng)常被多個客戶訪問，對處理什么請求以及用什么次序處理進行管理是MySQL的任務(wù)麦备。INSERT操作可能很耗時（特別是有很多索引需要更新時）孽椰，而且它可能降低等待處理的SELECT語句的性能。如果數(shù)據(jù)檢索是最重要的（通常是這樣）泥兰，則你可以通過在INSERT和INTO之間添加關(guān)鍵字LOW_PRIORITY弄屡，指示MySQL降低INSERT語句的優(yōu)先級，如下所示：

 INSERT LOW_PRIORITY INTO

這個操作同樣適用于UPDATE和DELETE語句鞋诗。

2.插入多行

如果想向表中插入多行數(shù)據(jù)膀捷，可以依次執(zhí)行多個 INSERT 語句，同樣也可以用以下方式插入：

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   );

MySQL用單條INSERT語句處理多個插入比使用多條INSERT語句快削彬，因此此方法可以提高性能全庸。

3.插入檢索出的數(shù)據(jù)

可以將INSERT語句和SELECT語句結(jié)合使用，將檢索出的數(shù)據(jù)插入新表中融痛。

INSERT INTO customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT id,
    contact,
    email,
    name,
    address,
    city,
    state,
    zip,
    country
FROM custnew;
#注意：本例中要求 custnew 表中的 id 與 customers 表中的 cust_id 不重復(fù)壶笼，否則會造成主鍵重復(fù)的錯誤。如果 customers 中定義的是自增主鍵雁刷，則可以直接省略覆劈。

注意：插入檢索出的數(shù)據(jù)要求對應(yīng)列的數(shù)據(jù)類型一致。

Ch 20.更新和刪除數(shù)據(jù)

1.更新數(shù)據(jù)

更新數(shù)據(jù)使用的是 UPDATE 語句沛励，主要包含三部分內(nèi)容：

要更新的表
要更新的列名及其更新后的值
要更新行的過濾條件

#更新某行的一列
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

#更新某行的多列
UPDATE customers
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

注意：使用UPDATE語句時一定要注意使用WHERE進行過濾责语，否則會更新全部行的內(nèi)容。

IGNORE關(guān)鍵字：如果用UPDATE語句更新多行目派，并且在更新這些行中的一行或多行時出一個現(xiàn)錯誤坤候，則整個UPDATE操作被取消（錯誤發(fā)生前更新的所有行被恢復(fù)到它們原來的值）。為即使是發(fā)生錯誤企蹭，也繼續(xù)進行更新白筹，可使用IGNORE關(guān)鍵字智末，如下所示：

UPDATE IGNORE customers ...

刪除某列的值：為了刪除某列的值，如果該列允許值為NULL徒河，可以直接將其設(shè)為NULL系馆。

2.刪除數(shù)據(jù)

刪除數(shù)據(jù)使用的是DELETE關(guān)鍵字，主要包含兩部分：

使用的表
刪除的過濾條件

DELETE FROM customers
WHERE cust_id = 10006;

注意：使用DELETE語句時一定要注意使用WHERE進行過濾虚青，否則會刪除全部的行它呀。

更快的刪除：如果想從表中刪除所有行，不要使用DELETE棒厘。可使用TRUNCATE TABLE語句下隧，它完成相同的工作奢人，但速度更快（TRUNCATE實際是刪除原來的表并重新創(chuàng)建一個表，而不是逐行刪除表中的數(shù)據(jù)）淆院。

3.更新和刪除的原則

除非確實打算更新和刪除每一行何乎，否則絕對不要使用不帶WHERE子句的UPDATE或DELETE語句。
保證每個表都有主鍵土辩，盡可能像WHERE子句那樣使用它（可以指定各主鍵支救、多個值或值的范圍）。
在對UPDATE或DELETE語句使用WHERE子句前拷淘，應(yīng)該先用SELECT進行測試各墨，保證它過濾的是正確的記錄，以防編寫的WHERE子句不正確启涯。
使用強制實施引用完整性的數(shù)據(jù)庫贬堵，這樣MySQL將不允許刪除具有與其他表相關(guān)聯(lián)的數(shù)據(jù)的行。

Ch 21.創(chuàng)建和操縱表

1.創(chuàng)建表

使用CREATE TABLE語句創(chuàng)建表结洼，主要包含以下兩部分：

新表的名字黎做，在關(guān)鍵字CREATE TABLE之后給出；
表列的名字和定義松忍，用逗號分隔蒸殿。

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  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 ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

如果你僅想在一個表不存在時創(chuàng)建它，應(yīng)該在表名后給出IF NOT EXISTS鸣峭。這樣做不檢查已有表的模式是否與你打算創(chuàng)建的表模式相匹配宏所。它只是查看表名是否存在，并且僅在表名不存在時創(chuàng)建它叽掘。

注意以下幾點：
- 主鍵值必須唯一
- 每個表只允許一個AUTO_INCREMENT列楣铁，而且它必須被索引（如成為主鍵）
- 如果對AUTO_INCREMENT列強行指定一個未使用過的值，則會覆蓋自增的值更扁，并且接下來插入的記錄該列將會從指定值開始自增
獲得最后一個AUTO_INCREMENT值：

SELECT last_insert_id();

指定默認值：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL  DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

數(shù)據(jù)庫引擎

MySQL數(shù)據(jù)庫主要有以下幾種引擎：
- InnoDB是一個可靠的事務(wù)處理引擎盖腕，它不支持全文本搜索赫冬；
- MEMORY在功能等同于MyISAM，但由于數(shù)據(jù)存儲在內(nèi)存（不是磁盤）中溃列，速度很快（特別適合于臨時表）劲厌；
- MyISAM是一個性能極高的引擎，它支持全文本搜索听隐，但不支持事務(wù)處理补鼻。
注意：
- 同一數(shù)據(jù)庫中不同的表可以使用不同的引擎；
- 外健不能跨引擎雅任，即使用一個引擎的表不能引用具有使用不同引擎的表的外鍵风范。

2.更新表

可使用ALTER TABLE語句更新表。但是沪么，理想狀態(tài)下硼婿，當(dāng)表中存儲數(shù)據(jù)以后，該表就不應(yīng)該再被更新禽车。在表的設(shè)計過程中需要花費大量時間來考慮寇漫，以便后期不對該表進行大的改動。

ALTER TABLE語句主要包含以下幾個部分：

在ALTER TABLE之后給出要更改的表名（該表必須存在殉摔，否則將出錯）州胳；
所做更改的列表。

#添加列
ALTER TABLE vendors
ADD vend_phone CHAR(20);

#刪除列
ALTER TABLE vendors
DROP COLUMN vend_phone;

#修改列屬性
ALTER TABLE vendors MODIFY COLUMN vend_phone VARCHAR(50);

#修改列
ALTER TABLE vendors CHANGE vend_phone phone VARCHAR(30);  

#定義外鍵
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)
REFERENCES products (prod_id);

如果要更改結(jié)構(gòu)復(fù)雜的表逸月，一般涉及到以下過程：

用新的列布局創(chuàng)建新表栓撞；
使用INSERT SELECT語句從舊表復(fù)制數(shù)據(jù)到新表。如果有必要彻采，可使用轉(zhuǎn)換函數(shù)和計算字段腐缤；
檢驗包含所需數(shù)據(jù)的新表；
重命名舊表（如果確定肛响，可以刪除它）岭粤；
用舊表原來的名字重命名新表；
根據(jù)需要特笋，重新創(chuàng)建觸發(fā)器剃浇、存儲過程、索引和外鍵猎物。

3.刪除和重命名表

刪除表：

DROP TABLE customers2;

重命名表：

RENAME TABLE customers2 TO customers;

Ch 22.使用視圖

視圖是一個虛擬的表虎囚，它不包含表中應(yīng)該有的任何列或數(shù)據(jù)，它包含的是一個SQL查詢結(jié)果蔫磨。

使用視圖可以帶來以下優(yōu)點：

重用SQL語句淘讥。
簡化復(fù)雜的SQL操作。在編寫查詢后堤如，可以方便地重用它而不必知道它的基本查詢細節(jié)蒲列。
使用表的組成部分而不是整個表窒朋。
保護數(shù)據(jù)』柔可以給用戶授予表的特定部分的訪問權(quán)限而不是整個表的訪問權(quán)限侥猩。
更改數(shù)據(jù)格式和表示。視圖可返回與底層表的表示和格式不同的數(shù)據(jù)抵赢。

1.使用視圖

視圖的基本使用：

視圖用CREATE VIEW語句來創(chuàng)建欺劳。
使用SHOW CREATE VIEW viewname;來查看創(chuàng)建視圖的語句。
用DROP刪除視圖铅鲤，其語法為DROP VIEW viewname划提。
更新視圖時，可以先用DROP再用CREATE彩匕，也可以直接用CREATE OR REPLACE VIEW腔剂，如果要更新的視圖不存在，則會創(chuàng)建一個視圖驼仪；如果要更新的視圖存在，則會替換原有視圖袜漩。

使用視圖簡化復(fù)雜的聯(lián)結(jié)

#通過聯(lián)結(jié)查詢結(jié)果創(chuàng)建視圖
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num;
  
#使用視圖查詢
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

用視圖重新格式化檢索出的數(shù)據(jù)

#創(chuàng)建視圖
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

#使用視圖
SELECT *
FROM vendorlocations;

用視圖過濾數(shù)據(jù)

#創(chuàng)建視圖
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

#使用視圖
SELECT *
FROM customeremaillist;

使用視圖與計算字段

#創(chuàng)建視圖
CREATE VIEW orderitemsexpanded AS
SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

#使用視圖
SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

2.更新視圖

通常而言绪爸，視圖是可以進行更新操作的（使用INSERT、UPDATE和DELETE）宙攻，更新視圖將更新基表奠货。因此，如果MySQL不能正確地確定被更新的基數(shù)據(jù)座掘，則不允許更新（包括插入和刪除）递惋。

如果視圖的定義中存在以下要素，則不允許更新視圖：

分組（使用GROUP BY和HAVING）溢陪；
聯(lián)結(jié)萍虽；
子查詢；
并形真；
聚集函數(shù)（Min()杉编、Count()、Sum()等）咆霜；
DISTINCT邓馒；
導(dǎo)出（計算）列。

一般而言蛾坯，應(yīng)該將視圖用于檢索而非更新光酣。

未完待續(xù)···