SQL 语法基础

参考资料

• SQL 必知必会 - 太简单了, 不如看菜鸟教程, 而且许多环境相关的语句没写 - 3

数据库

• 数据库中有多个表
• 模式描述了表的结构
• 显然表由列 (字段) 与行 (记录) 组成
• 主键 (一个字段) 标识了行的唯一性
  • 不可为空
  • 不可重复 (不同时也不行)
  • 不可修改

检索数据

• sql 不区分大小写
• sql 可以不加;
• sql 对空格不敏感

SELECT 列名 FROM 表名; -- 检索一列
SELECT 列名1,列名2 FROM 表名; -- 检索多列
SELECT * FROM 表名; -- 检索所有列

SELECT DISTINCT 列名 FROM 表名; -- 检索不重复的列 (去重),修饰 SELECT 而非列名

SELECT 列名 FROM 表名 LIMIT N OFFSET M; -- 返回第 M 行(从 0 开始数)起前 N 行
SELECT 列名 FROM 表名 LIMIT M,N -- 等效

排序检索数据

• 字典序中 A 等于 a (MySQL 的默认情况), 可以修改

SELECT 列名1 FROM 表名 ORDER BY 列名2,列名3 -- 检索结果按 列2 排序 (相同按 列3 ...)
SELECT 列名1 FROM 表名 ORDER BY 2,3 -- 检索结果按第 2 列 (列存在顺序) 排序

SELECT 列名1 FROM 表名 ORDER BY 列名2 DESC,列名3 DESC -- 降序, 修饰列名而非 ORDER BY

过滤数据 / 高级数据过滤

• 使用 AND,OR,NOT 联结修饰真值
• AND 的优先级高于 OR
• 数值与 NULL 不存在!= 关系

SELECT 列名1 FROM 表名 WHERE 真值表达式 ORDER BY 列名2,列名3 -- 应在 ORDER BY 前面

/*
= != <> 等于 不等于 不等于
< <= !< 小于 小于等于 不小于 大于一样
BETWEEN low AND high 两数之间
IS NULL 为 NULL
IN (值1,...) 等于 () 内任何值 (可以是另一个子句)
*/

用通配符进行过滤

• % 匹配任意多个任意字符
• _匹配单个任意字符
• [字符范围] 匹配单个 [] 内任意字符
  • [^字符范围] 匹配单个 [] 外任意字符 (否定)
• 数据库中一些字段类型未使用字符位置可能用' ' 填充
  • 影响通配符的匹配

SELECT 列名1 FROM 表名 WHERE 列名2 LIKE '通配符字符串'; -- LIKE 相当于 =

创建计算字段

• 计算字段是表达式 / 函数的结果 (临时字段)

SELECT Concat(列名1,列名2,'666') FROM 表名 -- MySQL 的函数
SELECT 列名1*列名2 FROM 表名 -- 算术运算

SELECT Concat(列名1,列名2,'666') AS 别名 FROM 表名 -- 便于客户端引用

使用函数处理数据

• SQL 函数往往不可移植
• 甚至有寻找读音类似字符串的函数

-- 不列举了, 没意义
/*
返回字符串长度
去空格
大小写转换
类型转换
简单的数学计算
*/

汇总数据

• 使用聚集函数 (有统一的定义)
• MySQL 支持许多 "标准偏差" 聚集函数

AVG()
COUNT()
MAX()/MIN()
SUM() -- 字面意思, 都会忽略 NULL
-- 除 COUNT() 都可以用 DISTINCT 修饰

分组数据

• GROUP BY 应在 WHERE 后面,ORDER BY 前面
• NULL 值会被单独分组
• GROUP BY 不可跟随聚集函数, 除此之外的 SELECT 列都需要出现在 GROUP BY 中
• HAVING 几乎与 WHERE 相同, 但它过滤的对象是分组后的结果 (比如聚集函数)

SELECT 列名1,列名2 FROM 表名 GROUP BY 列名1,列名2; -- 分组后, 列名1,列名2 相同的行被分为一组

SELECT 列名1,列名2 FROM 表名 GROUP BY 列名1 HAVING 真值表达式; -- 对分组后的结果过滤

SELECT 列名1,列名2 FROM 表名 GROUP BY 列名1 HAVING 真值表达式 ORDER BY 列名3,列名4; -- 对分组后的结果过滤并排序

使用子查询

• 子查询利用 IN 关键字, 在 () 中使用另一个查询 (以另一个查询的结果作为约束的查询)
• 子查询仅可返回一列

SELECT 列名1 FROM 表名1 WHERE 列名2 IN (SELECT 列名3 FROM 表名2 WHERE 真值表达式); -- 子查询可以是任何仅返回一列的 SELECT 语句

SELECT 列名1 (SELECT 计算字段 FROM 表名2 WHERE 外层查询结果参与的真值表达式) FROM 表名1; -- 要求子查询不可升维

• 完全限定列名 (表名.列名)

联结表

• 联结可以在一个 SELECT 语句中联结多个表 (临时表)
• 可以使用复杂的联结条件联结任意多个表
  • SQL 会尝试所有可能的组合, 因此性能会很差

SELECT 列名1,列名2 FROM 表名1,表名2 WHERE 真值表达式; -- 满足真值表达式的行被联结即等值联结(内联结)
-- 没WHERE会返回笛卡尔积
-- 亦可
SELECT 列名1,列名2 FROM 表名1 INNER JOIN 表名2 ON 真值表达式; -- 内联结

创建高级联结

自联结

• 表也可以有别名, 但仅作临时使用, 不返回客户端
• 因此当自联结时, 可使用别名区分同一物理表的两个逻辑表
• 自联结往往比同类型的子查询更高效

SELECT 列名1,列名2 FROM 表名1 AS 别名1,表名1 AS 别名2 WHERE 真值表达式; -- 自联结

自然联结

• 自然联结是特殊的自联结, 它返回所有不重复的列 (取每个表中非公有的列)
• 显然 "非自然联结" 没什么意义

外部联结

• 外部联结返回内联结的结果与左表不满足联结条件的行

SELECT 列名1,列名2 FROM 表名1 LEFT OUTER JOIN 表名2 ON 真值表达式; -- 左外部联结, 右 / 全外部联结不具有可移植性

组合查询

• 使用 UNION 联结多个查询的结果 (并集)
  • 注意 UNION 的每个 SELECT 所查询的名字必须相同
  • UNION 默认会去重, 使用 UNION ALL 可以不去重
• UNION 等效于多子句 WHERE, 数据库也会尽量优化成一个查询语句
• 若想排序, 则应在 UNION 的最后一个 SELECT 子句使用 ORDER BY
• UNION 可以用于查询多个表, 使用别名让查询的对象一致 (类型也得可以转换)

插入数据

• 一次插入多行优于多条单行插入

INSERT INTO 表名 VALUES(值1,值2,...); -- 按次序插入, 跳过应指明 NULL

INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...); -- 更安全, 省略的列会被置为 NULL (若表定义, 为默认值)

INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),VALUES(值3,值4,...); -- 插入多行, MySQL 支持

INSERT INTO 表名(列名1,列名2,...) SELECT 列名1,列名2,... FROM 表名2; -- 从 表2 中选择行插入 表1
-- 注意被插入表必须存在

SELECT * INTO 表名1 FROM 表名2; -- 从 表2 中导出 表1 ,注意 表1 不存在

更新与删除数据

• UPDATE IGNORE 可以强制更新多行的 UPDATE 语句不保持原子性

UPDATE 表名 SET 列名1=值1,列名2=值2,... WHERE 真值表达式; -- 更新列, 不指定 WHERE 则更新所有行 (小心!)

DELETE FROM 表名 WHERE 真值表达式; -- 删除行, 不指定 WHERE 则删除所有行 (小心!)
-- 想删除某行某列, 则应 UPDATE 为 NULL

• 外键指表中的列关联了另一个表的列 (最好是主键), 数据库以此保证数据的引用完整性
  • 数据库会阻止删除外键在其它表中具有实例的行

创建和操纵表

CREATE TABLE 表名(列名1 数据类型 NOT NULL,列名2 数据类型,...); -- 创建表, NOT NULL 表示不可为空 (默认为 NULL,可为空)

列名 数据类型 NOT NULL DEFAULT 值; -- 指定默认值

ALTER TABLE 表名 ADD 列名 数据类型; -- 添加列, ALTER TABLE还支持许多操作, 但强依赖环境, 不建议使用

RENAME TABLE 旧表名 TO 新表名; -- 重命名表

DROP TABLE 表名; -- 删除表

使用视图

• 视图是虚拟的表, 其内容是查询的结果, 视图本身不包含数据
• 视图的功能 (如可否依靠视图写入) 由数据库决定
  • MySQL 支持基于视图的修改, 但要求视图有完整的表的基数据 (不含聚集函数 / 分组 /...SQL 语句)
• 视图会保存查询的顺序

CREATE VIEW 视图名 AS SELECT 列名1,列名2,... FROM 表名; -- 创建视图, 注意视图名不能重复

DROP VIEW 视图名; -- 删除视图

存储过程

• 存储过程是一组流程控制的 SQL 语句

EXECUTE 存储过程名(参数1,参数2,...); -- 执行存储过程

• 存储过程的创建与编写依赖数据库, 在 MySQL 必知必会中讨论吧

管理事务处理

• 事务处理确保了要么一组 SQL 语句 (事务) 要么完全执行, 要么完全不执行 (原子性)

START TRANSACTION; -- 开始事务

-- 一系列SQL语句

ROLLBACK; -- 回滚到事务开始前的状态

COMMIT; -- 提交事务,注意提交后不可回滚

SAVEPOINT 保存点名; -- 设置保存点
ROLLBACK TO 保存点名; -- 回滚到保存点
-- 可以部分回滚

-- 结合流程控制语句使用

使用游标

• 游标是用于可视化操作的数据库对象,WEB 应用中不常用
• MySQL 的游标只能用于存储过程

DECLARE 游标名 CURSOR FOR SELECT 列名1,列名2,... FROM 表名; -- 声明游标, 此时还未查询

OPEN CURSOR 游标名; -- 打开游标

FETCH CURSOR 游标名 INTO 变量1,变量2,...; -- 从游标中获取一行数据到变量中

CLOSE CURSOR 游标名; -- 关闭游标

高级 SQL 特性

约束

列名 数据类型 NOT NULL DEFAULT 值 PRIMARY KEY -- 声明主键

列名1 数据类型 NOT NULL DEFAULT 值 REFERENCES 表名(列名2) -- 声明外键

列名 数据类型 NOT NULL DEFAULT 值 UNIQUE -- 声明唯一键 (不可作外键)

列名 数据类型 NOT NULL DEFAULT 值 CHECK(真值表达式) -- 检查约束

ALTER TABLE 表名 ADD CONSTRAINT 约束; -- 添加约束

索引

• 索引是对表以其中一列值进行排序的缓存 (便于快速查找)

CREATE INDEX 索引名 ON 表名(列名); -- 创建索引

触发器

• 触发器是特殊的存储过程, 当它依托的表发生特定操作 (插入 / 更新) 时, 会自动执行