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

晴天_雨天發表於2018-09-16

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 = `kim@thetoystore.com`
    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 資料庫名稱

MySQL 快速入門
2020-04-05
MySql
KingShard MySQL中介軟體快速入門
2021-09-09
MySql
Mysql系列一:SQL入門
2018-08-05
MySql
MySQL入門--MySQL安全
2019-06-11
MySql
MySQL入門--mysql命令
2019-05-27
MySql
MySQL入門
2024-04-13
MySql
入門入門入門 MySQL命名行
2020-12-15
MySql
MySQL入門系列：MySQL概述
2019-03-06
MySql
Mysql入門【Mysql約束】
2020-11-20
MySql
MySQL 入門指南
2024-03-09
MySql
Mysql入門【JDBC】
2020-11-22
MySqlJDBC
如何入門 MySQL
2020-05-15
MySql
mySql入門-（二）
2019-05-19
MySql
MySQL入門——排序
2019-07-03
MySql排序
Mysql - 使用入門
2021-04-28
MySql
MYSQL 入門全套
2021-01-31
MySql
MySQL入門---（一）SQL的DDL語句
2024-07-26
MySql
MySQL基礎和SQL入門【臨時】
2020-12-05
MySql
快應用快速入門教程
2019-03-04
MySQL 基礎入門
2020-05-05
MySql
MySQL入門--EVENT（事件）
2019-06-03
MySql事件
Mysql 簡單入門
2020-12-25
MySql
TS快速回顧
2024-03-08
快速排序快速入門
2018-08-05
排序
[MySQL光速入門]008 SQL強化練習
2019-04-18
MySql
MySQL入門系列：MySQL資料型別
2019-03-07
MySql資料型別
[MySQL光速入門]002 讓MySQL跑起來
2019-04-18
MySql
10.spark sql之快速入門
2021-09-09
SparkSQL
flink快速入門（部署+flink-sql）
2020-12-26
SQL
MySQL入門系列：檢視
2019-03-18
MySql
入門MySQL——架構篇
2020-05-29
MySql架構
MySQL 入門（5）：複製
2020-05-18
MySql
[MySQL光速入門]027 索引
2019-04-18
MySql索引
MySQL入門--儲存引擎
2019-06-28
MySql儲存引擎
MySQL入門--表維護
2019-05-29
MySql
node+express+mysql入門
2018-04-24
ExpressMySql
mysql新手入門隨筆
2018-03-28
MySql
《MySQL 入門教程》第 02 篇 MySQL 安裝
2022-01-25
MySql