主頁 » SQL 速查表

SQL 速查表

SQL 速查表為您提供了最常用的 SQL 語句以供參考。您可以按如下方式下載 SQL 速查表：

下載 3 頁 PDF 格式的 SQL 速查表

SQL Cheet Sheet 1

SQL Cheat Sheet 2 SQL Cheat Sheet 3 #

從表中查詢資料 #

從表中查詢 column1 和 column2 的資料

SELECT
  column1,
  column2
FROM
  table_name;Code language: SQL (Structured Query Language) (sql)

查詢表中的所有資料

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

根據條件檢索特定行

SELECT
  column1,
  column2
FROM
  table_name
WHERE
  condition;Code language: SQL (Structured Query Language) (sql)

從表中查詢 column1 和 column2 的唯一行

SELECT DISTINCT
  column1,
  column2
FROM
  table_name;Code language: SQL (Structured Query Language) (sql)

按升序對結果集進行排序

SELECT
  column1,
  column2
FROM
  table_name
ORDER BY
  column1;Code language: SQL (Structured Query Language) (sql)

按降序對結果集進行排序

SELECT
  column1,
  column2
FROM
  table_name
ORDER BY
  column2 DESC;

從表中跳過 m 行後返回接下來的 n 行

SELECT
  column1,
  column2
FROM
  table_name
ORDER BY
  column1
LIMIT
  n
OFFSET
  m;Code language: SQL (Structured Query Language) (sql)

對行進行分組並對每個組應用聚合函式

SELECT
  column1,
  aggregate_fn (column2)
FROM
  table_name
GROUP BY
  column1;Code language: SQL (Structured Query Language) (sql)

使用 HAVING 子句按條件篩選分組

SELECT
  column1,
  aggregate_fn (column2)
FROM
  table_name
GROUP BY
  column1
HAVING
  condition;Code language: SQL (Structured Query Language) (sql)

從多個表中查詢資料 #

對兩個表執行內連線

SELECT
  column1,
  column2
FROM
  table1
  INNER JOIN table2 ON condition;Code language: SQL (Structured Query Language) (sql)

對兩個表執行左連線

SELECT
  column1,
  column2
FROM
  table1
  LEFT JOIN table2 ON condition;Code language: SQL (Structured Query Language) (sql)

對兩個表執行右連線

SELECT
  column1,
  column2
FROM
  table1
  RIGHT JOIN table2 ON condition;Code language: SQL (Structured Query Language) (sql)

執行全外連線

SELECT
  column1,
  column2
FROM
  table1
  FULL OUTER JOIN table2 ON condition;Code language: SQL (Structured Query Language) (sql)

執行交叉連線

SELECT
  column1,
  column2
FROM
  table1
  CROSS JOIN table2;Code language: SQL (Structured Query Language) (sql)

使用內連線執行自連線

SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table1 t2 ON condition;Code language: SQL (Structured Query Language) (sql)

集合運算 #

返回兩個結果集的並集

SELECT column1, column2 
FROM table1
UNION [ALL]
SELECT column1, column2 
FROM table2;Code language: SQL (Structured Query Language) (sql)

返回兩個結果集的交集

SELECT column1, column2 
FROM table1
INTERSECT [ALL]
SELECT column1, column2 
FROM table2;Code language: SQL (Structured Query Language) (sql)

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

SELECT column1, column2 
FROM table1
MINUS
SELECT column1, column2 
FROM table2;Code language: SQL (Structured Query Language) (sql)

邏輯運算子 #

使用模式匹配查詢行

SELECT column1, column2 
FROM table_name
WHERE column1 [NOT] LIKE pattern;Code language: SQL (Structured Query Language) (sql)

檢查一個值是否在一組值中

SELECT column1, column2 
FROM table_name
WHERE column1 [NOT] IN (v1, v2, v3);Code language: SQL (Structured Query Language) (sql)

檢查一個值是否在一個值範圍內

SELECT column1, column2 
FROM table_name
WHERE column1 [NOT] BETWEEN low AND high;Code language: SQL (Structured Query Language) (sql)

檢查一個值是否為 NULL

SELECT column1, column2 
FROM table_name
WHERE column1 IS [NOT] NULL;Code language: SQL (Structured Query Language) (sql)

管理表 #

建立一個新表

CREATE TABLE IF NOT EXISTS table_name (
     column1 datatype PRIMARY KEY,
     column2 datatype constraint,
     table_constraint
);Code language: SQL (Structured Query Language) (sql)

從資料庫中移除一個表

DROP IF EXISTS TABLE table_name ;Code language: SQL (Structured Query Language) (sql)

向表中新增一個新列

ALTER TABLE table_name 
ADD column1 datatype constraint;Code language: SQL (Structured Query Language) (sql)

從表中刪除一個列

ALTER TABLE table_name 
DROP COLUMN column1;Code language: SQL (Structured Query Language) (sql)

新增一個約束

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

刪除一個約束

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

重新命名一個表

ALTER TABLE table1 
RENAME TO table2;Code language: SQL (Structured Query Language) (sql)

重新命名一個列

ALTER TABLE table1 
RENAME column1 TO column2;Code language: SQL (Structured Query Language) (sql)

快速移除表中的所有資料

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

使用 SQL 約束 #

建立一個複合主鍵

CREATE TABLE table_name (
    column1 datatype, 
    column2 datatype, 
    column3 datatype,
    PRIMARY KEY (column1, column2)
);Code language: SQL (Structured Query Language) (sql)

建立一個外部索引鍵約束

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,  
    column2 datatype,
    FOREIGN KEY (column2) REFERENCES table2(column2) ON DELETE CASCADE ON UPDATE CASCADE
);Code language: SQL (Structured Query Language) (sql)

建立一個包含兩列的唯一約束

CREATE TABLE table_name (
    column1 data_type, 
    column2 data_type,
    column3 data_type,
    UNIQUE(column2,column3)
);Code language: SQL (Structured Query Language) (sql)

建立一個 CHECK 約束

CREATE TABLE table_name (
   column1 datatype, 
   column2 datatype,
   CHECK(expression)
);Code language: SQL (Structured Query Language) (sql)

設定一個 NOT NULL 約束

CREATE TABLE table_name (
     column1 datatype,
     column2 datatype NOT NULL
);Code language: SQL (Structured Query Language) (sql)

修改資料 #

向表中插入一行

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

向表中插入多行

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

將從 table2 中選定的資料插入到 table1

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

更新所有行中列的新值

UPDATE table_name
SET column1 = new_value;Code language: SQL (Structured Query Language) (sql)

更新符合條件的行中兩列的值

UPDATE table_name
SET column1 = new_value, 
    column2 = new_value
WHERE condition;Code language: SQL (Structured Query Language) (sql)

刪除表中的所有資料

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

刪除表中的部分行

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

管理檢視 #

基於查詢建立一個新檢視

CREATE VIEW view_name(column1,column2) 
AS
query;Code language: SQL (Structured Query Language) (sql)

使用 WITH CHECK OPTION 建立一個新檢視

CREATE VIEW view_name(column1, column2) 
AS
query
WITH [CASCADED | LOCAL] CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

建立一個遞迴檢視

CREATE RECURSIVE VIEW view_name 
AS
query  -- anchor part
UNION [ALL]
query; -- recursive partCode language: SQL (Structured Query Language) (sql)

建立一個臨時檢視

CREATE TEMPORARY VIEW view_name 
AS
query;Code language: SQL (Structured Query Language) (sql)

刪除一個檢視

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

管理索引 #

在表 table_name 的 column1 和 column2 上建立索引

CREATE INDEX index_name 
ON table_name(column1,column2);Code language: SQL (Structured Query Language) (sql)

在表 table_name 的 column3 和 column4 上建立唯一索引

CREATE UNIQUE INDEX index_name
ON table_name(column3, column4)Code language: SQL (Structured Query Language) (sql)

刪除一個索引

DROP INDEX index_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)

刪除一個特定的觸發器

DROP TRIGGER trigger_name;Code language: SQL (Structured Query Language) (sql)
SQL List All Tables
上一篇
SQL 列出所有表
下一篇
SQL 函式
SQL Functions
© .