首页 » SQL 速查表

SQL 速查表

SQL 速查表为您提供最常用的 SQL 语句，供您参考。您可以按照以下步骤下载 SQL 速查表

下载 PDF 格式的三页 SQL 速查表

SQL Cheet Sheet 1

SQL Cheat Sheet 2 SQL Cheat Sheet 3

查询表中的数据

查询表中 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)

跳过偏移行，返回接下来的 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 和 t2

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 列中的值设置为 NOT 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)
SQL List All Tables
上一个
SQL 列出所有表
下一个
SQL 参考
SQL Reference