《MySQL必知必會》知識匯總四-有解無憂

十七、插入資料

本章將介紹如何利用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、計算列那將不能進行更新

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/539397.html

標籤：其他

上一篇：1.5 HDFS分布式檔案系統-hadoop-最全最完整的保姆級的java大資料學習資料

下一篇：1.5.4 HDFS 客戶端操作-hadoop-最全最完整的保姆級的java大資料學習資料