SQL 語法速成手冊

本文針對關係型數據庫的一般語法。限於篇幅，本文側重說明用法，不會展開講解特性、原理。篇幅較長，但內容基本涵蓋了 SQL 語法的大部分內容。

一、基本概念

數據庫術語

SQL 語法

SQL（Structured Query Language)，標準 SQL 由 ANSI 標準委員會管理，從而稱爲 ANSI SQL。各個 DBMS 都有自己的實現，如 PL/SQL、Transact-SQL 等。

SQL 語法結構

SQL 語法結構包括：

SQL 語法要點

例如：SELECT 與 select 、Select 是相同的。

-- 一行 SQL 語句
UPDATE user SET username='robot', password='robot' WHERE username = 'root';

-- 多行 SQL 語句
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
## 註釋1
-- 註釋2
/* 註釋3 */

SQL 分類

數據定義語言（DDL）

數據定義語言（Data Definition Language，DDL）是 SQL 語言集中負責數據結構定義與數據庫對象定義的語言。

DDL 的主要功能是定義數據庫對象。

DDL 的核心指令是 CREATE、ALTER、DROP。

數據操縱語言（DML）

數據操縱語言（Data Manipulation Language, DML）是用於數據庫操作，對數據庫其中的對象和數據運行訪問工作的編程語句。

DML 的主要功能是 訪問數據，因此其語法都是以讀寫數據庫爲主。

DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。這四個指令合稱 CRUD(Create, Read, Update, Delete)，即增刪改查。

事務控制語言（TCL）

事務控制語言 (Transaction Control Language, TCL) 用於管理數據庫中的事務。這些用於管理由 DML 語句所做的更改。它還允許將語句分組爲邏輯事務。

TCL 的核心指令是 COMMIT、ROLLBACK。

數據控制語言（DCL）

數據控制語言 (Data Control Language, DCL) 是一種可對數據訪問權進行控制的指令，它可以控制特定用戶賬戶對數據表、查看錶、預存程序、用戶自定義函數等數據庫對象的控制權。

DCL 的核心指令是 GRANT、REVOKE。

DCL 以控制用戶的訪問權限爲主，因此其指令作法並不複雜，可利用 DCL 控制的權限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。

根據不同的 DBMS 以及不同的安全性實體，其支持的權限控制也有所不同。

（以下爲 DML 語句用法）

二、增刪改查

增刪改查，又稱爲 CRUD，數據庫基本操作中的基本操作。

插入數據

  • INSERT INTO 語句用於向表中插入新記錄。

插入完整的行

INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');

插入行的一部分

INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');

插入查詢出來的數據

INSERT INTO user(username)
SELECT name
FROM account;

更新數據

  • UPDATE 語句用於更新表中的記錄。
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

刪除數據

  • DELETE 語句用於刪除表中的記錄。

  • TRUNCATE TABLE 可以清空表，也就是刪除所有行。

刪除表中的指定數據

DELETE FROM user
WHERE username = 'robot';

清空表中的數據

TRUNCATE TABLE user;

查詢數據

  • SELECT 語句用於從數據庫中查詢數據。

  • DISTINCT 用於返回唯一不同的值。它作用於所有列，也就是說所有列的值都相同纔算相同。

  • LIMIT 限制返回的行數。可以有兩個參數，第一個參數爲起始行，從 0 開始；第二個參數爲返回的總行數。

  • ASC ：升序（默認）

  • DESC ：降序

查詢單列

SELECT prod_name
FROM products;

查詢多列

SELECT prod_id, prod_name, prod_price
FROM products;

查詢所有列

ELECT *
FROM products;

查詢不同的值

SELECT DISTINCT
vend_id FROM products;

限制查詢結果

-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;

三、子查詢

子查詢是嵌套在較大查詢中的 SQL 查詢。子查詢也稱爲內部查詢或內部選擇，而包含子查詢的語句也稱爲外部查詢或外部選擇。

子查詢的子查詢

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

WHERE

FiXDgP

SELECT 語句中的 WHERE 子句

SELECT * FROM Customers
WHERE cust_name = 'Kids Place';

UPDATE 語句中的 WHERE 子句

UPDATE Customers
SET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';

DELETE 語句中的 WHERE 子句

DELETE FROM Customers
WHERE cust_name = 'Kids Place';

IN 和 BETWEEN

IN 示例

SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

BETWEEN 示例

SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;

AND、OR、NOT

AND 示例

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

OR 示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

NOT 示例

SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;

LIKE

% 示例

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

**_ 示例**

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

四、連接和組合

連接（JOIN）

  • 如果一個 JOIN 至少有一個公共字段並且它們之間存在關係，則該 JOIN 可以在兩個或多個表上工作。

  • 連接用於連接多個表，使用 JOIN 關鍵字，並且條件語句使用 ON 而不是 WHERE。

  • JOIN 保持基表（結構和數據）不變。

  • JOIN 有兩種連接類型：內連接和外連接。

  • 內連接又稱等值連接，使用 INNER JOIN 關鍵字。在沒有條件語句的情況下返回笛卡爾積。

  • 自連接可以看成內連接的一種，只是連接的表是自身而已。

  • 自然連接是把同名列通過 = 測試連接起來的，同名列可以有多個。

  • 內連接 vs 自然連接

  • 內連接提供連接的列，而自然連接自動連接所有同名列。

  • 外連接返回一個表中的所有行，並且僅返回來自次表中滿足連接條件的那些行，即兩個表中的列是相等的。外連接分爲左外連接、右外連接、全外連接（Mysql 不支持）。

  • 左外連接就是保留左表沒有關聯的行。

  • 右外連接就是保留右表沒有關聯的行。

  • 連接 vs 子查詢

  • 連接可以替換子查詢，並且比子查詢的效率一般會更快。

內連接（INNER JOIN）

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

自連接

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

自然連接（NATURAL JOIN）

SELECT *
FROM Products
NATURAL JOIN Customers;

左連接（LEFT JOIN）

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

右連接（RIGHT JOIN）

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

組合（UNION）

組合查詢

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

JOIN vs UNION

五、函數

🔔 注意：不同數據庫的函數往往各不相同，因此不可移植。本節主要以 Mysql 的函數爲例。

文本處理

7mbBhO

其中， SOUNDEX() 可以將一個字符串轉換爲描述其語音表示的字母數字模式。

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和時間處理

HgUPC5

mysql> SELECT NOW();
2018-4-14 20:25:11

數值處理

cPXNPV

彙總

3pAfyT

AVG() 會忽略 NULL 行。

使用 DISTINCT 可以讓彙總函數值彙總不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable

六、排序和分組

ORDER BY

指定多個列的排序方向

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

GROUP BY

分組

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;

分組後排序

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

HAVING

使用 WHERE 和 HAVING 過濾數據

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;

（以下爲 DDL 語句用法）

七、數據定義

DDL 的主要功能是定義數據庫對象（如：數據庫、數據表、視圖、索引等）。

數據庫（DATABASE）

創建數據庫

CREATE DATABASE test;

刪除數據庫

DROP DATABASE test;

選擇數據庫

USE test;

數據表（TABLE）

創建數據表

普通創建

CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用戶名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密碼',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '郵箱'
) COMMENT='用戶表';

根據已有的表創建新表

CREATE TABLE vip_user AS
SELECT * FROM user;

刪除數據表

DROP TABLE user;

修改數據表

添加列

ALTER TABLE user
ADD age int(3);

刪除列

ALTER TABLE user
DROP COLUMN age;

修改列

ALTER TABLE `user`
MODIFY COLUMN age tinyint;

添加主鍵

ALTER TABLE user
ADD PRIMARY KEY (id);

刪除主鍵

ALTER TABLE user
DROP PRIMARY KEY;

視圖（VIEW）

  • 定義

  • 視圖是基於 SQL 語句的結果集的可視化的表。

  • 視圖是虛擬的表，本身不包含數據，也就不能對其進行索引操作。對視圖的操作和對普通表的操作一樣。

  • 作用

  • 簡化複雜的 SQL 操作，比如複雜的聯結；

  • 只使用實際表的一部分數據；

  • 通過只給用戶訪問視圖的權限，保證數據的安全性；

  • 更改數據格式和表示。

創建視圖

CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;

刪除視圖

DROP VIEW top_10_user_view;

索引（INDEX）

  • 作用

  • 通過索引可以更加快速高效地查詢數據。

  • 用戶無法看到索引，它們只能被用來加速查詢。

  • 注意

  • 更新一個包含索引的表需要比更新一個沒有索引的表花費更多的時間，這是由於索引本身也需要更新。因此，理想的做法是僅僅在常常被搜索的列（以及表）上面創建索引。

  • 唯一索引

  • 唯一索引表明此索引的每一個索引值只對應唯一的數據記錄。

創建索引

CREATE INDEX user_index
ON user (id);

創建唯一索引

CREATE UNIQUE INDEX user_index
ON user (id);

刪除索引

ALTER TABLE user
DROP INDEX user_index;

約束

SQL 約束用於規定表中的數據規則。

創建表時使用約束條件：

CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用戶名',
  Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密碼',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '郵箱地址',
  Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',
  PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

（以下爲 TCL 語句用法）

八、事務處理

  • 不能回退 SELECT 語句，回退 SELECT 語句也沒意義；也不能回退 CREATE 和 DROP 語句。

  • MySQL 默認是隱式提交，每執行一條語句就把這條語句當成一個事務然後進行提交。當出現 START TRANSACTION 語句時，會關閉隱式提交；當 COMMIT 或 ROLLBACK 語句執行後，事務會自動關閉，重新恢復隱式提交。

  • 通過 set autocommit=0 可以取消自動提交，直到 set autocommit=1 纔會提交；autocommit 標記是針對每個連接而不是針對服務器的。

  • 指令

  • START TRANSACTION - 指令用於標記事務的起始點。

  • SAVEPOINT - 指令用於創建保留點。

  • ROLLBACK TO - 指令用於回滾到指定的保留點；如果沒有設置保留點，則回退到 START TRANSACTION 語句處。

  • COMMIT - 提交事務。

-- 開始事務
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 創建保留點 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滾到保留點 updateA
ROLLBACK TO updateA;

-- 提交事務，只有操作 A 生效
COMMIT;

（以下爲 DCL 語句用法）

九、權限控制

  • GRANT 和 REVOKE 可在幾個層次上控制訪問權限：

  • 整個服務器，使用 GRANT ALL 和 REVOKE ALL；

  • 整個數據庫，使用 ON database.*；

  • 特定的表，使用 ON database.table；

  • 特定的列；

  • 特定的存儲過程。

  • 新創建的賬戶沒有任何權限。

  • 賬戶用 username@host 的形式定義，username@% 使用的是默認主機名。

  • MySQL 的賬戶信息保存在 mysql 這個數據庫中。

    USE mysql;
SELECT user FROM user;
複製代碼

創建賬戶

CREATE USER myuser IDENTIFIED BY 'mypassword';

修改賬戶名

UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;

刪除賬戶

DROP USER myuser;

查看權限

SHOW GRANTS FOR myuser;

授予權限

GRANT SELECT, INSERT ON *.* TO myuser;

刪除權限

REVOKE SELECT, INSERT ON *.* FROM myuser;

更改密碼

SET PASSWORD FOR myuser = 'mypass';

十、存儲過程

  • 存儲過程可以看成是對一系列 SQL 操作的批處理；

  • 使用存儲過程的好處

  • 代碼封裝，保證了一定的安全性；

  • 代碼複用；

  • 由於是預先編譯，因此具有很高的性能。

  • 創建存儲過程

  • 命令行中創建存儲過程需要自定義分隔符，因爲命令行是以 ; 爲結束符，而存儲過程中也包含了分號，因此會錯誤把這部分分號當成是結束符，造成語法錯誤。

  • 包含 in、out 和 inout 三種參數。

  • 給變量賦值都需要用 select into 語句。

  • 每次只能給一個變量賦值，不支持集合的操作。

創建存儲過程

DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;

    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
;;
DELIMITER ;

使用存儲過程

set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

十一、遊標

  • 遊標（cursor）是一個存儲在 DBMS 服務器上的數據庫查詢，它不是一條 SELECT 語句，而是被該語句檢索出來的結果集。

  • 在存儲過程中使用遊標可以對一個結果集進行移動遍歷。

  • 遊標主要用於交互式應用，其中用戶需要對數據集中的任意行進行瀏覽和修改。

  • 使用遊標的四個步驟：

  • 聲明遊標，這個過程沒有實際檢索出數據；

  • 打開遊標；

  • 取出數據；

  • 關閉遊標；

DELIMITER $
CREATE  PROCEDURE getTotal()
BEGIN
    DECLARE total INT;
    -- 創建接收遊標數據的變量
    DECLARE sid INT;
    DECLARE sname VARCHAR(10);
    -- 創建總數變量
    DECLARE sage INT;
    -- 創建結束標誌變量
    DECLARE done INT DEFAULT false;
    -- 創建遊標
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
    -- 指定遊標循環結束時的返回值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    SET total = 0;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE(NOT done)
    DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;

    CLOSE cur;
    SELECT total;
END $
DELIMITER ;

-- 調用存儲過程
call getTotal();

十二、觸發器

觸發器是一種與表操作有關的數據庫對象，當觸發器所在表上出現指定事件時，將調用該對象，即表的操作事件觸發表上的觸發器的執行。

可以使用觸發器來進行審計跟蹤，把修改記錄到另外一張表中。

MySQL 不允許在觸發器中使用 CALL 語句 ，也就是不能調用存儲過程。

BEGIN 和 END

當觸發器的觸發條件滿足時，將會執行 BEGIN 和 END 之間的觸發器執行動作。

🔔 注意：在 MySQL 中，分號 ; 是語句結束的標識符，遇到分號表示該段語句已經結束，MySQL 可以開始執行了。因此，解釋器遇到觸發器執行動作中的分號後就開始執行，然後會報錯，因爲沒有找到和 BEGIN 匹配的 END。

這時就會用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思）。它是一條命令，不需要語句結束標識，語法爲：DELIMITER new_delemiter。new_delemiter 可以設爲 1 個或多個長度的符號，默認的是分號 ;，我們可以把它修改爲其他符號，如 $ - DELIMITER $ 。在這之後的語句，以分號結束，解釋器不會有什麼反應，只有遇到了 $，才認爲是語句結束。注意，使用完之後，我們還應該記得把它給修改回來。

NEW 和 OLD

創建觸發器

提示：爲了理解觸發器的要點，有必要先了解一下創建觸發器的指令。

CREATE TRIGGER 指令用於創建觸發器。

語法：

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

說明：

示例：

DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user',  now());
END $
DELIMITER ;

查看觸發器

SHOW TRIGGERS;

刪除觸發器

DROP TRIGGER IF EXISTS trigger_insert_user;

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/qpZtL-TTIIvDv4mf5Szqyw

猜你喜歡