《MySQL必知必會》知識彙總四

十七、插入數據本章將介紹如何利用sql的INSERT語句將數據插入表中數據插入插入分為以下幾種方式：插入完整的行、插入行的一部分、插入多行、插入某些查詢結果插入完整的行 INSERT INTO Customers VALUES(NULL, 'Pep E. LaPew', '100 Main ...

十七、插入數據

本章將介紹如何利用sql的INSERT語句將數據插入表中

數據插入

插入分為以下幾種方式：插入完整的行、插入行的一部分、插入多行、插入某些查詢結果

插入完整的行

INSERT INTO Customers
VALUES(NULL,
   'Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA',
   NULL,
   NULL);

上述sql語句將高度依賴於表中列的定義次序，某個列沒有值，應該設置為null

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);

此時該sql指定了插入具體列名，那麼values的順序就將與指定的插入列一一對應

註意：

一般建議使用這種指定插入列的INSERT語句

不論使用上述哪種INSERT語句，values必須為每個列提供值

使用指定列的INSERT語句並不用將所有列都指定出來，可以省略列，指定列的順序也不用與資料庫表中列的順序一致，但是省略的列必須允許為NULL值或者表定義中給出預設值

插入多個行

當前可以多次執行INSERT語句實現插入多個行，但是也可以使用一條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'
   );

建議使用這種單條INSERT語句來插入多行數據，會提高性能

插入檢索出的數據

需求：想從另一表中合併客戶列表到你的customers表。

INSERT INTO customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
FROM custnew;

註意：

custnew表與customers表具有相同的表結構

上述sql中，不需要select返回的列名與customers的列名一致，它使用的是列的位置，select的第一列（不管其列名）將填充表列中指定的第一列

當然，select語句還可以使用where子句進行過濾數據！

十八、更新和刪除數據

本章將介紹如何利用UPDATE和DELETE語句進一步操縱表數據

更新數據

可採用兩種方式使用update：更新表中特定行、更新表中所有行

update customers
set cust_emal = '[email protected]'
where cust_id = 10005;

註意：

不要省略where語句，否則將會更新所有行

update customers
set cust_emal = '[email protected]',
    cust_name = 'The Fudds'
where cust_id = 10005;

通過單個set命令，可以更新多個列

UPDATE IGNORE customers 
set cust_emal = '[email protected]',
    cust_name = 'The Fudds'
where cust_id = 10005;

如果在更新行時，出現一行或者多行錯誤，則整個UPDATE操作被取消，當然可以是使用IGNORE關鍵字進行繼續更新

刪除數據

同樣可以採用兩種方式使用DELETE：從表中刪除特定行、從表中刪除所有行

delete from customers
where cust_id = 10006;

註意：

如果忽略了where子句，將刪除所有行

更快的刪除

如果想從表中刪除所有行，可以使用TRUNCATE TABLE,速度更快，實際上底層是刪除表後再新建一個表

更新和刪除的指導原則

一定要使用帶where的update和delete語句

使用where子句前先用select進行查看測試，防止where過濾的數據不正確

MySQL沒有撤銷命令，一定要小心使用！

十九、創建和操縱表

本章講述表的創建、更改和刪除的基本知識

表的基本創建

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;

需要給出以下信息：

新表的名字，再create table後給出

表列的名字和定義，用逗號隔開

註意：

在創建新的表時，指定的表名必須不存在，可以在表名後使用IF NOT EXISTS做判斷

使用NULL值

null值就是沒有值或者缺少值，上述創建表的sql中，cust_id、cust_name就不能為空，其他可以為空

不要將null值與空串相混淆，空串是一個有效的值，它不是無值

主鍵再介紹

PRIMARY KEY (vend_id)

或者

PRIMARY KEY (order_num,order_id)

可以使用單個列作為主鍵，也可以使用組合列作為主鍵

主鍵不允許使用null值

使用AUTO_INCREMENT

cust_id  int  NOT NULL AUTO_INCREMENT

這樣MySQL在插入數據時，自動分配一個遞增的int值

使用SELECT last_insert_id()函數將能查看最後一個自增的值

指定預設值

quantity  int  NOT NULL DEFAULT 1

表示在未給出數量的情況下使用預設數量1

引擎類型

ENGINE=InnoDB

還可以使用其他引擎如MyISAM

更新表

通過alter table來改變表結構，必須給出要更改的表名，所作更改的列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

ALTER TABLE vendors
DROP COLUME vend_phone;

註意：

一定要小心使用alter table，刪除了不該刪除的列，會造成數據丟失

刪除表

DROP TABLE customers2;

重命名錶

RENAME TABLE customers2 TO customers;

二十、使用視圖

本章將介紹何為視圖，怎樣工作的，何時使用，利用視圖簡化sql

需求：從三個表中檢索數據

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num
  AND prod_id = 'TNT2';

如果三個表的數據都在一張表中，那麼sql將變為

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id ='TNT2';

這就是視圖的作用，productcustomers可以看作是一個視圖，它不包含表中應該有的數據，僅包含的是一個SQL查詢

為什麼使用視圖

重用SQL語句，簡化複雜的SQL操作，使用表的組成部分、保護數據、更改數據格式和表示

視圖本身不包含數據，返回的數據是從其他表中檢索出來的

性能問題

每次使用視圖之前都要處理查詢後返回給視圖，處理複雜多表聯查會降低性能

註意：

視圖不能有索引、必須有足夠的訪問許可權

使用視圖

使用SHOW CREATE VIEW viewname;來查看創建視圖的語句。

用DROP刪除視圖，其語法為DROP VIEW viewname;

更新視圖時，可以先用DROP 再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的視圖不存在，則第2條更新語句會創建一個視圖；如果要更新的視圖存在，則第2條更新語句會替換原有視圖

使用視圖簡化複雜聯結

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;

該sql創建了productcustomers視圖，聯結了三個表，返回訂購了任意商品的客戶信息

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id ='TNT2';

因此，檢索購買了TNT的客戶這條sql即可滿足

使用視圖重新格式話檢索的數據

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

用視圖過濾不想要的數據

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

使用視圖與計算欄位

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

更新視圖

其實，對視圖更新也就是對視圖表示的基表進行更新，通過視圖增加和刪除一行，也是對基表進行增加和刪除

但是如果視圖涉及到分組、聯結、子查詢、並、聚集函數、DISTINCT、計算列那將不能進行更新

一般將視圖用於檢索而不用於更新