结构化查询语言(Structured Query Language)
完成条件
SQL语句结构
SQL由以SELECT、DELETE或CREATE等关键字开头，并以分号结束的语句组成。除了固定字符字符串值（如 'Mr. Brown'）外，其元素对大小写不敏感。
以下是包含多个语言元素的完整语句示例。
创建表
数据类型
更多详情
-- 常用数据类型和简单约束
CREATE TABLE t_standard (
-- 列名 数据类型 默认值 可为空/约束
id DECIMAL PRIMARY KEY, -- 有些人更喜欢名称：'sid'
col_1 VARCHAR(50) DEFAULT 'n/a' NOT NULL, -- 可变长度字符串。Oracle: 'VARCHAR2'
col_2 CHAR(10), -- 固定长度字符串
col_3 DECIMAL(10,2) DEFAULT 0.0, -- 小数点前8位，后2位。有符号。
col_4 NUMERIC(10,2) DEFAULT 0.0, -- 与col_3相同
col_5 INTEGER,
col_6 BIGINT -- Oracle: 使用 'NUMBER(n)', n最多38
);
-- 具有时间属性的数据类型
CREATE TABLE t_temporal (
-- 列名 数据类型 默认值 可为空/约束
id DECIMAL PRIMARY KEY,
col_1 DATE, -- Oracle: 包含日期和时间，秒无小数
col_2 TIME, -- Oracle: 使用 'DATE' 并选择时间部分
col_3 TIMESTAMP, -- 包含秒的小数部分
col_4 TIMESTAMP WITH TIME ZONE, -- MySQL: 不支持时区
col_5 INTERVAL YEAR TO MONTH,
col_6 INTERVAL DAY TO SECOND
);
CREATE TABLE t_misc (
-- 列名 数据类型 默认值 可为空/约束
id DECIMAL PRIMARY KEY,
col_1 CLOB, -- 非常长的字符串（MySQL: LONGTEXT）
col_2 BLOB, -- 二进制，如Word文档或mp3流
col_3 FLOAT(6), -- 示例：三分之二 (2/3)。
col_4 REAL,
col_5 DOUBLE PRECISION,
col_6 BOOLEAN, -- Oracle: 不支持
col_7 XML -- Oracle: 'XMLType'
);
约束
更多详情
-- 使用富有表现力的名称命名所有约束，例如：表名的缩写（在模式中的所有表中唯一）、列名、约束类型、序号。
--
CREATE TABLE myExampleTable (
id DECIMAL,
col_1 DECIMAL(1), -- 仅1位（有符号）数字
col_2 VARCHAR(50),
col_3 VARCHAR(90),
CONSTRAINT example_pk PRIMARY KEY (id),
CONSTRAINT example_uniq UNIQUE (col_2),
CONSTRAINT example_fk FOREIGN KEY (col_1) REFERENCES person(id),
CONSTRAINT example_col_1_nn CHECK (col_1 IS NOT NULL),
CONSTRAINT example_col_1_check CHECK (col_1 >=0 AND col_1 < 6),
CONSTRAINT example_col_2_nn CHECK (col_2 IS NOT NULL),
CONSTRAINT example_check_1 CHECK (LENGTH(col_2) > 3),
CONSTRAINT example_check_2 CHECK (LENGTH(col_2) < LENGTH(col_3))
);
外键
更多详情
-- 引用不同（或相同）表。这创建了1:m或n:m关系。
CREATE TABLE t_hierarchie (
id DECIMAL,
part_name VARCHAR(50),
super_part_id DECIMAL, -- 包含此部分的部件的ID
CONSTRAINT hier_pk PRIMARY KEY (id),
-- 在此特殊情况下，外键引用相同的表
CONSTRAINT hier_fk FOREIGN KEY (super_part_id) REFERENCES t_hierarchie(id)
);
-- -----------------------------------------------
-- n:m关系
-- -----------------------------------------------
CREATE TABLE t1 (
id DECIMAL,
name VARCHAR(50),
-- ...
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE TABLE t2 (
id DECIMAL,
name VARCHAR(50),
-- ...
CONSTRAINT t2_pk PRIMARY KEY (id)
);
CREATE TABLE t1_t2 (
id DECIMAL,
t1_id DECIMAL,
t2_id DECIMAL,
CONSTRAINT t1_t2_pk PRIMARY KEY (id), -- 此表也应有自己的主键
CONSTRAINT t1_t2_unique UNIQUE (t1_id, t2_id), -- 每个链接只能出现一次
CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id),
CONSTRAINT t1_t2_fk_2 FOREIGN KEY (t2_id) REFERENCES t2(id)
);
删除/更新行为 / 可延迟
更多详情
-- 子表的删除和更新行为（参见第一个示例）
-- Oracle: 仅支持 DELETE [CASCADE | SET NULL]。默认是 NO ACTION，但不能
-- 明确指定——只需省略该短语。
CONSTRAINT hier_fk FOREIGN KEY (super_part_id) REFERENCES t_hierarchie(id)
ON DELETE CASCADE -- 或：NO ACTION（默认）、RESTRICT、SET NULL、SET DEFAULT
ON UPDATE CASCADE -- 或：NO ACTION（默认）、RESTRICT、SET NULL、SET DEFAULT
-- 初始阶段：立即 vs. 延迟，[不可]延迟
-- MySQL: 不支持 DEFERABLE
CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
INITIALLY IMMEDIATE DEFERRABLE
-- 在后期更改约束特性
SET CONSTRAINT hier_fk DEFERRED; -- 或：IMMEDIATE
修改表
更多详情
关于列
-- 添加一列（以及一些列约束）。Oracle: 不允许使用关键字 'COLUMN'。
ALTER TABLE t1 ADD COLUMN col_1 VARCHAR(100) CHECK (LENGTH(col_1) > 5);
-- 更改列的特性。（某些实现使用不同的关键字，如 'MODIFY'。）
ALTER TABLE t1 ALTER COLUMN col_1 SET DATA TYPE NUMERIC;
ALTER TABLE t1 ALTER COLUMN col_1 SET DEFAULT -1;
ALTER TABLE t1 ALTER COLUMN col_1 SET NOT NULL;
ALTER TABLE t1 ALTER COLUMN col_1 DROP NOT NULL;
-- 删除一列。Oracle: 关键字 'COLUMN' 是强制性的。
ALTER TABLE t1 DROP COLUMN col_2;
关于整个表
--
ALTER TABLE t1 ADD CONSTRAINT t1_col_1_uniq UNIQUE (col_1);
ALTER TABLE t1 ADD CONSTRAINT t1_col_2_fk FOREIGN KEY (col_2) REFERENCES person (id);
-- 更改定义。某些实现使用不同的关键字，如 'MODIFY'。
ALTER TABLE t1 ALTER CONSTRAINT t1_col_1_unique UNIQUE (col_1);
-- 删除约束。需要知道其名称。MySQL不支持此操作，只有 'DROP FOREIGN KEY'。
ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;
-- 作为SQL标准的扩展，某些实现提供了 ENABLE / DISABLE 命令用于约束。
删除表
更多详情
--
-- 所有数据和完整的表结构（包括索引）将被删除。
-- 无需指定列名。无需WHERE子句。不会触发任何触发器。考虑外键。非常快。
DROP TABLE t1;
选择（Select）
基本语法
更多详情
--
-- 总体结构：SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY
-- 常量、列值、操作符、函数
SELECT 'ID: ', id, col_1 + col_2, sqrt(col_2)
FROM t1
-- WHERE中的优先级：函数、比较、NOT、AND、OR
WHERE col_1 > 100
AND NOT MOD(col_2, 10) = 0
OR col_3 < col_1
ORDER BY col_4 DESC, col_5; -- 升序（默认）或降序
-- 行数，非空值的数量
SELECT COUNT(*), COUNT(col_1) FROM t1;
-- 预定义函数
SELECT COUNT(col_1), MAX(col_1), MIN(col_1), AVG(col_1), SUM(col_1) FROM t1;
-- 仅唯一值
SELECT DISTINCT col_1 FROM t1;
-- 在下一个示例中，col_1可能有重复。只有col_1和col_2的组合是唯一的。
SELECT DISTINCT col_1, col_2 FROM t1;
您可以根据需要复制/粘贴这些示例，以帮助您学习和实践SQL。
案例表达式
更多详情
-- 针对恰好一个列的条件的 CASE 表达式
SELECT id,
CASE contact_type -- 一个列名
WHEN 'fixed line' THEN 'Phone'
WHEN 'mobile' THEN 'Phone'
ELSE 'Not a telephone number'
END,
contact_value
FROM contact;
-- 针对任意列的条件的 CASE 表达式
SELECT id,
CASE -- 无列名
WHEN contact_type IN ('fixed line', 'mobile') THEN 'Phone'
WHEN id = 4 THEN 'ICQ'
ELSE 'Something else'
END,
contact_value
FROM contact;
分组（Grouping）
更多详情
SELECT product_group, count(*) AS cnt
FROM sales
WHERE region = 'west' -- 可以有额外的限制，但不是必须的
GROUP BY product_group -- 'product_group' 是创建分组的标准
HAVING COUNT(*) > 1000 -- 限制每组销售数量超过1000
ORDER BY cnt;
-- 注意：在下一个示例中，col_2 不属于 GROUP BY 标准的一部分。因此它无法被显示。
SELECT col_1, col_2
FROM t1
GROUP BY col_1;
-- 我们必须将每组的所有 col_2 值累积为一个值，例如：
SELECT col_1, sum(col_2), min(col_2)
FROM t1
GROUP BY col_1;
连接（Join）
更多详情
-- 内连接：仅显示有联系的人员及其联系方式。
-- 忽略所有没有联系方式的人员和所有没有人员的联系方式
SELECT *
FROM person p
JOIN contact c ON p.id = c.person_id;
-- 左外连接：显示所有人员。忽略没有人员的联系方式
SELECT *
FROM person p
LEFT JOIN contact c ON p.id = c.person_id;
-- 右外连接：显示所有联系方式。忽略没有联系方式的人员
SELECT *
FROM person p
RIGHT JOIN contact c ON p.id = c.person_id;
-- 全外连接：显示所有人员和所有联系方式。
SELECT *
FROM person p
FULL JOIN contact c ON p.id = c.person_id;
-- 笛卡尔积（缺少 ON 关键字）：小心使用！
SELECT COUNT(*)
FROM person p
JOIN contact c;
子查询（Subquery）
更多详情
-- SELECT 子句中的子查询
SELECT id,
lastname,
weight,
(SELECT avg(weight) FROM person) -- 子查询
FROM person;
-- WHERE 子句中的子查询
SELECT id,
lastname,
weight
FROM person
WHERE weight < (SELECT avg(weight) FROM person) -- 子查询
;
-- SELECT 子句中的相关子查询
SELECT id,
(SELECT status_name FROM status st WHERE st.id = sa.state)
FROM sales sa;
-- 在每个 booking_number 中检索最高版本的相关子查询
SELECT *
FROM booking b
WHERE version =
(SELECT MAX(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
;
集合操作（Set operations）
更多详情
-- UNION
SELECT firstname -- 第一个 SELECT 命令
FROM person
UNION -- 将两个中间结果合并为一个结果
SELECT lastname -- 第二个 SELECT 命令
FROM person;
-- 默认行为是：'UNION DISTINCT'。如果要保留所有重复值，必须显式指定 'UNION ALL'。
-- INTERSECT：结果值必须同时存在于两个中间结果中
SELECT firstname FROM person
INTERSECT
SELECT lastname FROM person;
-- EXCEPT：结果值必须存在于第一个中间结果中，但不在第二个中间结果中
SELECT firstname FROM person
EXCEPT -- Oracle 使用 'MINUS'。MySQL 不支持 EXCEPT。
SELECT lastname FROM person;
Rollup/Cube
更多详情
-- 每个组和子组的额外汇总
SELECT SUM(col_x), ...
FROM ...
GROUP BY ROLLUP (producer, model); -- MySQL 语法为：GROUP BY producer, model WITH ROLLUP
-- 每组分组列的每种组合的额外汇总
SELECT SUM(col_x), ...
FROM ...
GROUP BY CUBE (producer, model); -- MySQL 不支持
窗口函数（Window functions）
更多详情
-- 框架边界
SELECT id,
emp_name,
dep_name,
FIRST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_first_row,
LAST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_last_row,
COUNT(*) OVER (PARTITION BY dep_name ORDER BY id) AS frame_count,
LAG(id) OVER (PARTITION BY dep_name ORDER BY id) AS prev_row,
LEAD(id) OVER (PARTITION BY dep_name ORDER BY id) AS next_row
FROM employee;
-- 移动平均值
SELECT id, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY salary
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_over_1or2or3_rows
FROM employee;
递归（Recursions）
更多详情
-- 'WITH' 子句由三部分组成：
-- 第一部分：中间表及其列的任意名称
WITH intermediate_table (id, firstname, lastname) AS
(
-- 第二部分：起始行（或多行）
SELECT id, firstname, lastname
FROM family_tree
WHERE firstname = 'Karl'
AND lastname = 'Miller'
UNION ALL
-- 第三部分：查询下一层级的规则定义。大多数情况下，这是通过连接操作完成的。
SELECT f.id, f.firstname, f.lastname
FROM intermediate_table i
JOIN family_tree f ON f.father_id = i.id
)
-- 'WITH' 子句之后：深度优先 / 广度优先
-- 按 firstname 广度优先搜索并设置 sequence_number（默认行为）
-- 按 firstname 深度优先搜索并设置 sequence_number
-- 最终的 SELECT
SELECT * FROM intermediate_table;
-- 提示：自 Oracle 11.2 版本以来，Oracle 支持 SQL 标准的语法。
-- MySQL 完全不支持递归，并推荐使用过程性解决方法。
您可以根据需要复制/粘贴这些示例，以帮助您学习和实践SQL。
插入（Insert）
更多详情
-- 固定值/行列表
INSERT INTO t1 (id, col_1, col_2) VALUES (6, 46, 'abc');
INSERT INTO t1 (id, col_1, col_2) VALUES (7, 47, 'abc7'),
(8, 48, 'abc8'),
(9, 49, 'abc9');
COMMIT;
-- 子选择：可生成0、1或多行新数据
INSERT INTO t1 (id, col_1, col_2)
SELECT id, col_x, col_y
FROM t2
WHERE col_y > 100;
COMMIT;
-- 动态值
INSERT INTO t1 (id, col_1, col_2) VALUES (16, CURRENT_DATE, 'abc');
COMMIT;
INSERT INTO t1 (id, col_1, col_2)
SELECT id,
CASE
WHEN col_x < 40 THEN col_x + 10
ELSE col_x + 5
END,
col_y
FROM t2
WHERE col_y > 100;
COMMIT;
更新（Update）
更多详情
-- 基本语法
UPDATE t1
SET col_1 = 'Jimmy Walker',
col_2 = 4711
WHERE id = 5;
-- 将 col_2 的值乘以2；无WHERE子句 ==> 所有行！
UPDATE t1 SET col_2 = col_2 * 2;
-- 非相关子查询导致子查询只被评估一次
UPDATE t1 SET col_2 = (SELECT max(id) FROM t1);
-- 相关子查询导致外部查询的每一行都评估一次子查询
UPDATE t1 SET col_2 = (SELECT col_2 FROM t2 WHERE t1.id = t2.id);
-- WHERE子句中的子查询
UPDATE article
SET col_1 = 'topseller'
WHERE id IN
(SELECT article_id
FROM sales
GROUP BY article_id
HAVING COUNT(*) > 1000
);
合并（Merge）
更多详情
-- 根据任意标准插入/更新，这里是两个列 'id'
MERGE INTO hobby_shadow t -- 目标表
USING (SELECT id, hobbyname, remark
FROM hobby
WHERE id < 8) s -- 源数据
ON (t.id = s.id) -- 匹配标准
WHEN MATCHED THEN
UPDATE SET remark = concat(s.remark, ' Merge / Update')
WHEN NOT MATCHED THEN
INSERT (id, hobbyname, remark) VALUES (s.id, s.hobbyname, concat(s.remark, ' Merge / Insert'))
;
-- 独立于受影响行数，客户端与DBMS之间只有一次往返
删除（Delete）
更多详情
-- 基本语法
DELETE FROM t1 WHERE id = 5; -- 在 'DELETE' 关键字后不需要列名，因为将删除整行
-- 无匹配结果也是可以的
DELETE FROM t1 WHERE id != id;
-- 子查询
DELETE FROM person_hobby
WHERE person_id IN
(SELECT id
FROM person
WHERE lastname = 'Goldstein'
);
截断（Truncate）
更多详情
-- TRUNCATE 删除所有行（无法使用WHERE子句）。表结构保留。
-- 不会触发任何触发器。考虑外键。比DELETE快得多。
TRUNCATE TABLE t1;
您可以根据需要复制/粘贴这些示例，以帮助您学习和实践SQL。
最后修改: 2025年01月28日 星期二 12:16