SQL 快速參考手冊

SQL 快速參考手冊為您提供最常用的 SQL 語句，方便您參考。您可以透過以下方式下載 SQL 快速參考手冊

下載 3 頁 PDF 格式的 SQL 快速參考手冊

從資料表查詢資料

從資料表查詢欄位 c1, c2 的資料

SELECT c1, c2 FROM t;
Code language: SQL (Structured Query Language) (sql)

從資料表查詢所有列和欄

SELECT * FROM t;
Code language: SQL (Structured Query Language) (sql)

查詢資料並使用條件篩選列

SELECT c1, c2 FROM t
WHERE condition;Code language: SQL (Structured Query Language) (sql)

從資料表查詢不重複的列

SELECT DISTINCT c1 FROM t
WHERE condition;
Code language: SQL (Structured Query Language) (sql)

以遞增或遞減順序排序結果集

SELECT c1, c2 FROM t
ORDER BY c1 ASC [DESC];Code language: SQL (Structured Query Language) (sql)

跳過 offset 列並返回接下來的 n 列

SELECT c1, c2 FROM t
ORDER BY c1 
LIMIT n OFFSET offset;Code language: SQL (Structured Query Language) (sql)

使用彙總函數分組列

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1;
Code language: SQL (Structured Query Language) (sql)

使用 HAVING 子句篩選群組

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition;
Code language: SQL (Structured Query Language) (sql)

從多個資料表查詢

內部聯結 t1 和 t2

SELECT c1, c2 
FROM t1
INNER JOIN t2 ON condition;
Code language: SQL (Structured Query Language) (sql)

左聯結 t1 和 t1

SELECT c1, c2 
FROM t1
LEFT JOIN t2 ON condition;
Code language: SQL (Structured Query Language) (sql)

右聯結 t1 和 t2

SELECT c1, c2 
FROM t1
RIGHT JOIN t2 ON condition;
Code language: SQL (Structured Query Language) (sql)

執行完整外部聯結

SELECT c1, c2 
FROM t1
FULL OUTER JOIN t2 ON condition;
Code language: SQL (Structured Query Language) (sql)

產生資料表中列的笛卡爾積

SELECT c1, c2 
FROM t1
CROSS JOIN t2;
Code language: SQL (Structured Query Language) (sql)

執行交叉聯結的另一種方式

SELECT c1, c2 
FROM t1, t2;
Code language: SQL (Structured Query Language) (sql)

使用 INNER JOIN 子句將 t1 與自身聯結

SELECT c1, c2
FROM t1 A
INNER JOIN t1 B ON condition;
Code language: SQL (Structured Query Language) (sql)

使用 SQL 運算符

合併兩個查詢的列

SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2;
Code language: SQL (Structured Query Language) (sql)

返回兩個查詢的交集

SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;
Code language: SQL (Structured Query Language) (sql)

從另一個結果集中減去一個結果集

SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2;
Code language: SQL (Structured Query Language) (sql)

使用模式匹配 %, _ 查詢列

SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern;
Code language: SQL (Structured Query Language) (sql)

查詢列表中的列

SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list;
Code language: SQL (Structured Query Language) (sql)

查詢兩個值之間的列

SELECT c1, c2 FROM t
WHERE  c1 BETWEEN low AND high;
Code language: SQL (Structured Query Language) (sql)

檢查資料表中的值是否為 NULL

SELECT c1, c2 FROM t
WHERE  c1 IS [NOT] NULL;
Code language: SQL (Structured Query Language) (sql)

管理資料表

建立具有三個欄位的新資料表

CREATE TABLE t (
     id INT PRIMARY KEY,
     name VARCHAR NOT NULL,
     price INT DEFAULT 0
);
Code language: SQL (Structured Query Language) (sql)

從資料庫刪除資料表

DROP TABLE t ;
Code language: SQL (Structured Query Language) (sql)

向資料表新增新欄位

ALTER TABLE t ADD column;
Code language: SQL (Structured Query Language) (sql)

從資料表刪除欄位 c

ALTER TABLE t DROP COLUMN c ;
Code language: SQL (Structured Query Language) (sql)

新增約束

ALTER TABLE t ADD constraint;
Code language: SQL (Structured Query Language) (sql)

刪除約束

ALTER TABLE t DROP constraint;
Code language: SQL (Structured Query Language) (sql)

將資料表從 t1 重新命名為 t2

ALTER TABLE t1 RENAME TO t2;
Code language: SQL (Structured Query Language) (sql)

將欄位 c1 重新命名為 c2

ALTER TABLE t1 RENAME c1 TO c2 ;
Code language: SQL (Structured Query Language) (sql)

移除資料表中的所有資料

TRUNCATE TABLE t;
Code language: SQL (Structured Query Language) (sql)

使用SQL 約束

將 c1 和 c2 設定為主鍵

CREATE TABLE t(
    c1 INT, c2 INT, c3 VARCHAR,
    PRIMARY KEY (c1,c2)
);
Code language: SQL (Structured Query Language) (sql)

將 c2 欄位設定為外部鍵

CREATE TABLE t1(
    c1 INT PRIMARY KEY,  
    c2 INT,
    FOREIGN KEY (c2) REFERENCES t2(c2)
);
Code language: SQL (Structured Query Language) (sql)

使 c1 和 c2 中的值唯一

CREATE TABLE t(
    c1 INT, c1 INT,
    UNIQUE(c2,c3)
);
Code language: SQL (Structured Query Language) (sql)

確保 c1 > 0 且 c1 中的值 >= c2

CREATE TABLE t(
  c1 INT, c2 INT,
  CHECK(c1> 0 AND c1 >= c2)
);
Code language: SQL (Structured Query Language) (sql)

設定 c2 欄位中的值不能為 NULL

CREATE TABLE t(
     c1 INT PRIMARY KEY,
     c2 VARCHAR NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

修改資料

將一列插入資料表

INSERT INTO t(column_list)
VALUES(value_list);
Code language: SQL (Structured Query Language) (sql)

將多列插入資料表

INSERT INTO t(column_list)
VALUES (value_list), 
       (value_list), …;
Code language: SQL (Structured Query Language) (sql)

將 t2 中的列插入 t1

INSERT INTO t1(column_list)
SELECT column_list
FROM t2;
Code language: SQL (Structured Query Language) (sql)

更新所有列的 c1 欄位中的新值

UPDATE t
SET c1 = new_value;
Code language: SQL (Structured Query Language) (sql)

更新符合條件的 c1、c2 欄位中的值

UPDATE t
SET c1 = new_value, 
        c2 = new_value
WHERE condition;
Code language: SQL (Structured Query Language) (sql)

刪除資料表中的所有資料

DELETE FROM t;
Code language: SQL (Structured Query Language) (sql)

刪除資料表中的子集列

DELETE FROM t
WHERE condition;
Code language: SQL (Structured Query Language) (sql)

管理檢視表

建立一個包含 c1 和 c2 的新檢視表

CREATE VIEW v(c1,c2) 
AS
SELECT c1, c2
FROM t;
Code language: SQL (Structured Query Language) (sql)

建立具有檢查選項的新檢視表

CREATE VIEW v(c1,c2) 
AS
SELECT c1, c2
FROM t;
WITH [CASCADED | LOCAL] CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

建立遞迴檢視表

CREATE RECURSIVE VIEW v 
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part
Code language: SQL (Structured Query Language) (sql)

建立臨時檢視表

CREATE TEMPORARY VIEW v 
AS
SELECT c1, c2
FROM t;
Code language: SQL (Structured Query Language) (sql)

刪除檢視表

DROP VIEW view_name;
Code language: SQL (Structured Query Language) (sql)

管理索引

在 t 資料表的 c1 和 c2 上建立索引

CREATE INDEX idx_name 
ON t(c1,c2);
Code language: SQL (Structured Query Language) (sql)

在 t 資料表的 c3、c4 上建立唯一索引

CREATE UNIQUE INDEX idx_name
ON t(c3,c4)
Code language: SQL (Structured Query Language) (sql)

刪除索引

DROP INDEX idx_name;
Code language: SQL (Structured Query Language) (sql)

管理觸發器

建立或修改觸發器

CREATE OR MODIFY TRIGGER trigger_name
WHEN EVENT
ON table_name TRIGGER_TYPE
EXECUTE stored_procedure;
Code language: SQL (Structured Query Language) (sql)

WHEN

• BEFORE – 在事件發生之前調用
• AFTER – 在事件發生之後調用

EVENT

• INSERT – 為 INSERT 調用
• UPDATE – 為 UPDATE 調用
• DELETE – 為 DELETE 調用

TRIGGER_TYPE

• FOR EACH ROW
• FOR EACH STATEMENT

刪除特定觸發器

DROP TRIGGER trigger_name;
Code language: SQL (Structured Query Language) (sql)