SQL快速入門 ( MySQL快速入門, MySQL參考, MySQL快速回顧 )

SQL

先說點廢話，很久沒發文了，整理了下自己當時入門 SQL 的筆記，無論用於入門，回顧，參考查詢，應該都是有一定價值的，可以按照目錄各取所需。SQL資料庫有很多，MySQL是一種，本文基本都是SQL通用標準，有些標準很不統一的地方就用MySQL的寫法了。希望本文幫你快速瞭解SQL的基本操作和概念。
文章格式上有些問題，可以點擊這裡獲得更加的閱讀體驗

目錄

• 檢索
• 過濾檢索結果
• 數據彙總處理
• 分組
• 給檢索結果排序
• 表操作
• 插入數據
• 更新刪除數據
• 子查詢-迭代查詢
• 聯結-關聯多個表
• 組合查詢
• 視圖
• 其它

檢索

• 檢索某表中單個列：
  SELECT 列名
  FROM 表名;
• 檢索某表中多個列：
  SELECT 列名,列名,列名
  FROM 表名;
  
• 檢索某表中所有列：（儘量不用）
  SELECT *
  FROM 表名;
  
• 只檢索某表中某列里不重覆的項：
  SELECT DISTINCT 列名 (如果有兩列或以上，需要這些列組合起來是不重覆的)
  FROM 表名;
  
• 檢索指定行數：
  SELECT 列名
  FROM 表名
  LIMIT 5 OFFSET n; （mySQL中，選第n行後的五行。 OFFSET n 可不填寫預設為0，其它 SQL 資料庫中有不同寫法）

過濾檢索結果

• 尋找指定行：（舉例）
  

    SELECT prod_name, prod_price  
    FROM Products  
    WHERE prod_price = 3.49;（和字元串比較加單引號，數值不用）

查找列名為prod_name和列名為prod_price的兩列，檢索其中prod_price = 3.49; 的所有行。  
= 可以替換為其它操作符，如下表

| 操作符 | 描述 |
| --- | --- |
| = | 等於 |
| <> | 不等於 |
| > | 大於 |
| < | 小於 |
| >= | 大於等於 |
| <= | 小於等於 |
| BETWEEN | 在某個範圍內 |
| LIKE | 搜索某種模式 |

• 組合WHERE子句：

    SELECT prod_id, prod_price, prod_name
    FROM Products
    WHERE vend_id = 'DLL01' AND prod_price <= 4;

AND 連接同時需要滿足的兩個條件，OR即滿足一個條件即可，NOT 找到與後邊條件不匹配的行。  
且not，and和or可以組合使用，用小括弧聲明邏輯循序。  
`WHERE vend_id IN ( 'DLL01', 'BRS01' ) `  
IN 起到作用類似於or，速度更快，邏輯更清晰。

• 通配符搜索：

    SELECT prod_id, prod_name
    FROM Products
    WHERE prod_name LIKE '%bean bag%';

％表示任意字元出現任意次數。也可以出現在中間位置。  
_ 表示一個字元。  
[charlist] 表示包含在裡面的任意字元，[^charlist]不包含在裡面的任意字元。
少使用通配符，搜索速度較慢。  

數據彙總處理

• 算術計算：

    SELECT prod_id,
            quantity,
            item_price,
            quantity * item_price AS expanded_price
    FROM OrderItems
    WHERE order_num = 20008;

`expanded_price`成為計算出來的新列。

• 字元串拼接：
  不同資料庫有差異，MySQL中：

    SELECT concat(vend_name , vend_country)
            AS vend_title
    FROM Vendors
    ORDER BY vend_name;

concat_ws( ':' , vend_name , vend_country) 形式第一個參數為分隔符。  
其他資料庫用＋或者||拼接字元串。

• 日期時間處理不同資料庫差異較大。
• 內置方法：
  • 求平均值：
    SELECT AVG(prod_price) AS avg_price
    FROM Products;
    表Products中prod_price的平均值。返回給 avg_price。
    可以配合WHERE語句計算指定行的平均值。
  • 求最大值：MAX(prod_price)
  • 求最小值：MIN(prod_price)
  • 求和：SUM(prod_price)
  • 近似的小數點後幾位：ROUND(column_name,decimals)
  • 當前日期： Now()
  • 求行數：
    SELECT COUNT(＊) AS num_cust
    FROM Customers;
    求表Customers有幾行。返回給num_cust。
    ＊可以換成指定列如：cust_email。計算所得行數不包括該列值為null的行。
    DISTINCT 列名，求不重覆的列。
• 組合：
  

    SELECT COUNT(＊) AS num_items,
           MIN(prod_price) AS price_min,
           MAX(prod_price) AS price_max,
           AVG(prod_price) AS price_avg
    FROM Products;

分組

• 創建分組：
  

    SELECT vend_id
    FROM Products
    GROUP BY vend_id;

根據 vend_id列中內容對 vend_id分組，  
第一行換成 `SELECT vend_id, COUNT(＊) AS num_prods` 即對每一個組計算行數。  
註意：多行NULL會分為一組，GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。  
可以對一個以上的列進行 GROUP BY

• 過濾分組：
  HAVING：類似於WHERE。唯一的差別是，WHERE過濾行，而HAVING過濾分組。
  

    SELECT vend_id, COUNT(＊) AS num_prods
    FROM Products
    WHERE prod_price >= 4
    GROUP BY vend_id
    HAVING num_prods >= 2;

過濾出有（兩個價格大與4的產品）的供應商

給檢索結果排序

SELECT Company, OrderNumber 
FROM Orders 
ORDER BY Company DESC, OrderNumber ASC  

可以 ORDER BY 列名1,列名2; 先按列名1內容排序，排序結果相同的按列名2內容排序。
列名後接 DESC 按該列內容倒序排列，ASC 正序(預設)。
ORDER BY 命令放在查詢、分組等語句的最後。

表操作

• 創建表:

    CREATE  TABLE  newProducts
    (
        prod_id         CHAR(10)           NOT NULL,
        vend_id         CHAR(10)           NOT NULL,
        prod_name       CHAR(254)          NOT NULL,
        prod_price      DECIMAL(8,2)       NOT NULL,
        prod_desc       VARCHAR(1000)      NULL
    );

NOT NULL 非空約束，不允許列中有NULL值下麵介紹其他約束。  
列的設置可以加上預設值，如NOT NULL後邊接 DEFAULT  CURRENT_DATE() ，預設值為當前日期。（每個資料庫獲取當前日期語句不同。）
後面接 PRIMARY KEY 即設置改列為主鍵。
後面接  AUTO_INCREMENT 即設置為自增，只有int型可以設置。

• 約束：
  每個列可以有一種或幾種約束。
  • NOT NULL 非空約束.
  • UNIQUE 唯一約束，可唯一標識資料庫表中的每條記錄。
  • PRIMARY KEY 主鍵約束，唯一標識資料庫表中的每條記錄，唯一且非空。
  • FOREIGN KEY 外鍵約束，一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY。
  • CHECK 檢查約束，用於限制列中的值的範圍。
  • DEFAULT 預設約束，用於向列中插入預設值
    每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。
    每種約束可以創建表時設置好，也可以後期增刪.
• 索引：
  在不讀取整個表的情況下，索引使資料庫應用程式可以更快地查找數據。

    CREATE INDEX 索引名
    ON Person (列名[，列名])  

• 複製表或表中部分列:

    CREATE  TABLE CustCopy AS
    SELECT * FROM Customers;

創建Customers表的複製，CustCopy。

• 修改表:

    ALTER TABLE Vendors
    ADD vend_phone CHAR(20);
    ALTER TABLE Vendors
    DROP COLUMN vend_phone;

各資料庫有不相容現象，複雜表操作列可能要新建表刪除舊表。  
ALTER 還可以用來添加刪除約束，刪除索引等。

• 刪除表：
  DROP TABLE CustCopy;
• 重命名錶：
  RENAME Table oldTable TO newTable;

插入數據

• 插入整行或部分行：

    INSERT INTO Customers(cust_id,
                            cust_name,
                            cust_address,
                            cust_city,
                            cust_state,
                            cust_zip,
                            cust_country,
                            cust_contact,
                            cust_email)
    VALUES('1000000007',
            'Toy Land',
            '123 Any Street',
            'New York',
            'NY',
            '11111',
            'USA',
            NULL,
            NULL);

插入整行時，可省略 Customers 括弧內的內容，即按照列的順序，分別插入數據（不推薦）。省略 Customers 括弧內的內容時，無內容的列必須用NULL占位。  
插入部分行時，把要插入的列填入 Customers 括弧內，與VALUES內容一一對應，沒有提到的列預設NULL或其他預設值。

• 插入查詢到的值:

    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_city,
            cust_state,
            cust_zip,
            cust_country
    FROM CustNew;

把從CustNew表中查到的內容插入 Customers表中。一次插入多行的方式。

更新和刪除數據:

• 更新數據：

    UPDATE Customers
    SET cust_email = '[email protected]'
    WHERE cust_id = '1000000005';

步驟為，要更新的表，要更新的列，要更新的行。一個SET可以跟多個列用逗號隔開。  
刪除某個值，即設置他為NULL。

• 刪除數據：

    DELETE FROM Customers
    WHERE cust_id = '1000000008';

刪除表中指定整行，刪除部分列用UPDATE  
在UPDATE或DELETE語句使用WHERE子句前，應該先用SELECT進行測試，保證它過濾的是正確的記錄，以防編寫的WHERE子句不正確。如果不寫WHERE會更新或刪除所有行內容。

子查詢-迭代查詢

• 一種形式：

    SELECT cust_name, cust_contact
    FROM Customers
    WHERE cust_id IN (SELECT cust_id
        FROM Orders
        WHERE order_num IN (SELECT order_num
            FROM OrderItems
            WHERE prod_id = 'RGAN01'));

先從第二個括弧選擇符合條件的order_num，成為第二個括弧內容，再向上找到第一個括弧，查到符合條件的cust_id返回給第一個括弧，最後根據第一個括弧內容執行主查詢語句。性能問題不要嵌套太多層。  
也就是對Customers表的查詢要用到Orders表查詢後返回的內容，對Orders表的查詢要用到OrderItems表查詢後返回的內容。

• 另一種形式：

    SELECT cust_name,
        cust_state,
        (SELECT COUNT(＊)
            FROM Orders
            WHERE Orders.cust_id = Customers.cust_id) AS orders
    FROM Customers

根據Customers 表中的cust_id，去Orders表中取得計算後的數據。  

• 同一個表迭代查詢：

    SELECT cust_id, cust_name, cust_contact
    FROM Customers
    WHERE cust_name = (SELECT cust_name
                    FROM Customers
                    WHERE cust_contact = 'Jim Jones');

聯結-關聯多個表

• 兩個表：
  • 內聯結

        SELECT vend_name, prod_name, prod_price
        FROM Vendors, Products
        WHERE Vendors.vend_id = Products.vend_id;

    根據兩個表共同的列vend_id把Vendors, Products關聯起來。
    與

        SELECT vend_name, prod_name, prod_price
        FROM Vendors INNER JOIN Products
        ON Vendors.vend_id = Products.vend_id;

    結果相同。都是內聯結，前一種是後一種的簡寫。  
    INNER 可省略。

  • 外聯結：

        SELECT Customers.cust_id, Orders.order_num
        FROM Customers LEFT OUTER JOIN Orders
        ON Orders.cust_id = Customers.cust_id;

    LEFT OUTER JOIN 把Customers表中沒有被匹配到的 cust_id 也聯結進去（會顯示在結果里）。  
    RIGHT OUTER JOIN 是把Orders表中沒有被匹配到的 cust_id 也聯結進去（會顯示在結果里）。  
    FULL OUTER JOIN 會把兩張表中沒有匹配到的列也顯示出來(mysql 不支持，可通過 UNION 實現)
    OUTER 可省略。

• 多個表：

    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 = 'RGAN01';

作用同子查詢中a。同樣不要關聯太多，有性能問題。其中表名可以使用別名，如：  

    SELECT cust_name, cust_contact
    FROM Customers AS C, Orders AS O, OrderItems AS OI
    WHERE C.cust_id = O.cust_id
    AND OI.order_num = O.order_num
    AND prod_id = 'RGAN01';

組合查詢

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state IN ('IL','IN','MI') 
UNION ALL
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_name = 'Fun4All';

UNION ALL 鏈接兩句查詢語句，統一返回結果，包含重覆結果。
去掉ALL以後，去掉重覆結果。
此處（從同一個表中查詢）可以用WHERE ， OR代替。
常用作從不同表中查詢時，只要列數相同就可以拼接到一起，列名按照第一句中查詢的列名。

視圖

對已存在的表，進行篩選，數據處理，聯結等操作後返回的數據，創建的虛擬表。視圖是為了重用和簡化常用的查詢。對視圖的查詢同表。
視圖總是顯示最近的數據。每當用戶查詢視圖時，資料庫引擎通過使用 SQL 語句來重建數據。

• 創建視圖:

    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;

對OrderItems， Orders和Customers三個表進行聯結，聯結後結果形成 ProductCustomers 視圖，可以把它當一張表來查詢。

• 刪除視圖：
  DROP VIEW ProductCustomers;

  其它

• 存儲過程：為以後的使用保存一條或多條SQL語句，用於簡化操作。每個資料庫不同，見資料庫具體介紹。
• 事務處理：事務處理模塊中的語句，或者全部執行，或者全部不執行。可以設立保留點，執行失敗時回到保留點。
• 創建資料庫: CREATE DATABASE database_name

• 刪除資料庫：DROP DATABASE 資料庫名稱