Jinvic's Blog
所有文章 标签 分类
Jinvic's Blog

目录

目录

《SQL必知必会》阅读笔记

Jinvic avatarJinvic  收录于  类别 笔记
     约 7885 字   预计阅读 35 分钟 
目录

《SQL必知必会》阅读笔记

这次看到是第五版，20年7月出版。 MySQL的示例数据库在这里 最近都是通过orm操作sql，补习一下基础知识好了。简单记一下，别变成抄书就行。

介绍数据库，表，列，数据类型，行，主键这些基本概念，略。

SQL：Structured Query Language 结构化查询语言 用了这么久sql还真不熟悉全称是啥。

标准SQL由ANSI标准委员会管理，称为 ANSI SQL。其他DBMS 即使有自己的扩展也都支持 ANSI SQL。

让你装一个sql。

简单介绍sql及基本数据库术语。

SELECT c_name 
FROM t_name;

提示：SQL不区分大小写。一种常用方法是关键词发现，列名表名等小写，这样有更好的可读性，但不是必须。

SELECT c_name1, c_name2 
FROM t_name;
SELECT * 
FROM t_name;

注意：检索不需要的列会降低速度和性能，如非必须最好不适用通配符。

SELECT DISTINCT c_name 
FROM t_name;

注意：不能部分使用DISTINCT。DISTINCT关键字作用于所有的列。

SELECT DISTINCT c_name 
FROM t_name 
LIMIT 5 
OFFSET 5;
  • 行内注释：-- text 或 # text
  • 多行注释： /* text */
  • SELECT子句
  • LIMIT和OFFSET子句
  • 注释

子句（clause）: SQL语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。 以前从来没有注意过子句的概念，直到gorm里被教做人。

SELECT c_name
FROM t_name 
ORDER BY c_name; 
SELECT c_name1, c_name2, _name3
FROM t_name 
ORDER BY c_name2, c_name3;

按ORDER BY子句中的列名顺序作为优先级排序。 提示：也可以使用非选择列排序。

SELECT c_name1, c_name2, _name3
FROM t_name 
ORDER BY 2, 3;

提示：如果有必要，可以混合使用实际列名和相对列位置。

SELECT c_name1, c_name2, c_name3
FROM t_name 
ORDER BY c_name2 DESC, c_name3 ASC;

sql默认使用升序排序，你也可以使用ASC关键字显示指定。要使用降序排序，使用DESC关键字。 和DISTINCT不同，ASC和DESC只会影响对应列。

  • 子句 概念
  • ORDER BY子句
  • ASC和DESC关键字
SELECT c_name1, c_name2
FROM t_name
WHERE c_name1 = 100;

注意在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。 这个我倒没注意过，用gorm时也没报过错，大概不同的DBMS或者ORM对这有优化吧。

不同的DBMS对操作符的支持不同，需要查看具体文档。 除了常规的大小等于，需要注意的操作符还有表区间的BETWEEN...AND...和判空的IS (NOT) NULL。

  • WHERE子句及其操作符

使用AND和OR逻辑操作符来组合WHERE子句。 AND的优先级大于OR，不过比起依赖默认顺序，更推荐使用括号消除歧义。

SELECT c_name1, c_name2, c_name3
FROM t_name 
WHERE (c_name1 > 30 OR c_name2 > 40) AND c_name3 < 50;
SELECT c_name1, c_name2, c_name3
FROM t_name 
WHERE c_name1 IN (3, 4, 5);

使用IN操作符的优点：

  • 语法更清楚只管
  • 和其他操作符组合使用时，求值顺序更易管理
  • 一般比一组OR执行更快
  • 可以包含其他SELECT语句，从而动态建立WHERE子句
SELECT c_name1, c_name2, c_name3
FROM t_name 
WHERE c_name1 NOT IN (3, 4, 5);

在复杂子句中NOT的易用性和可读性更高。

  • AND和OR操作符
  • IN操作符
  • NOT操作符

先记一些专有名词和概念： 通配符（wildcard）：用来匹配值的一部分的特殊字符。 搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。 谓词（predicate）：没讲清楚定义 ，但并不难理解。

以下是sql中的通配符。

  • %: 任何字符出现任意次数 直接’%‘不会匹配NULL
  • _:，任何字符匹配一个字符
  • []: 指定字符集指定位置匹配一个字符 部分DBMS不支持（如MySql）

最常见的关键词搜索示例如下：

SELECT c_name1, c_name2
FROM t_name 
WHERE c_name1 LIKE '%keyword%';
  • 不要过度使用通配符
  • 不要用在搜索模式开始处（速度慢）
  • 注意通配符位置
  • LIKE谓词
  • 通配符

有时需要从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据再在客户端应用程序重新格式化。 虽然很多操作都可以在客户端应用程序完成，但一般在数据库服务器上完成这些操作要快得多。

可以使用+或||拼接两个列。但MySql和MariaDB中则是特殊函数CONCAT()。 可以使用LTRIM()，RTRIM()或TRIM()函数去除左右空格。 可以使用AS关键字创建别名。这是可选的，但最佳实践推荐使用AS。

SELECT CONCAT(RTRIM(c_name1), c_name2) AS alias 
FROM t_name;

也可以使用加减乘除等操作符对数字类型的字段进行计算。

  • CONCAT()函数
  • LTRIM()，RTRIM()，TRIM函数
  • AS关键字
  • 算术运算

注意：不同的函数在不同DBMS中的支持不同。即使是相同的功能，在不同DBMS中的函数名也很可能不同。所以SQL函数是不可移植的。

大多是SQL实现支持以下类型的函数：

  • 处理文本字符串的文本函数。
  • 对数值数据进行算术操作的数值函数。
  • 数量日期和时间值并提取特定成分的日期和时间函数。
  • 生成美观易读的输出的格式化函数。
  • 返回DBMS相关特殊信息的系统函数。

由于不同DBMS对函数的支持不同，在这里不展开。有需要查表。

  • 数据处理函数

与数据处理函数不同，聚集函数在各种主要SQL实现中支持基本一致。 聚集函数：对某些行运行的函数，计算并返回一个值。

  • AVG() 返回某列的平均值
  • COUNT() 返回某列的行数
    • COUNT(*)对行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。
    • COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
  • MAX() 返回某列的最大值
  • MIN() 返回某列的最小值
  • SUM() 返回某列值之和
  • 对所有行执行计算，指定ALL参数或不指定（默认ALL）
  • 只包含不同的值，指定DISTINCT参数

有的DBMS还支持其他参数。

SELECT AVG(DISTINCT c_name) AS avg_res
FROM t_name;

在select中可以同时使用多个聚集参数。

  • 五种聚集函数
  • ALL和DISTINCT参数

举例需要对数据分组的需求。

使用GROUP BY子句创建分组。一些规定：

  • GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套。
  • GROUP BY子句中嵌套了分组，数据将在最后指定的分组上汇总。
  • GROUP BY子句中每一列必须是检索列或有效表达式（但不能是聚集函数）。如果SELECT中使用表达式，GROUP BY中也要使用相同的表达式，不能使用别名。
  • 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如TEXT）。
  • 除聚集语句外，SELECT中每一列都必须在GROUP BY中给出。
  • 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用HAVING子句过滤分组。 HAVING支持所有WHERE操作符，唯一的差别是WHERE过滤行，而HAVING过滤分组。 另一种理解方法是，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT c_name, COUNT(*) AS num
FROM t_name
GROUP BY c_name
HAVING COUNT(*) >= 2;

在特定DBMS中，GROUP BY分组时确实会按照子句的列排序。但还是推荐使用ORDER BY手动排序实现精细化操作和提高可读性。

列出目前为止学过的子句顺序

子句说明是否必须使用
SELECT要返回的列或表达式是
FROM从中检索数据的表仅在从表选择数据时使用
WHERE行级过滤否
GROUP BY分组说明仅在按组计算聚集时使用
HAVING组级过滤否
ORDER BY输出排序顺序否
  • GROUP BY子句
  • HAVING子句

子查询(subquery)：嵌套在其他查询中的查询。

举例，没有新内容。

示例给出的写法感觉用JOIN更方便和易读，仅作了解。

  • 子查询

简单介绍关系表概念和好处。

可伸缩性（scale）：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序 称为可伸缩性好（scale well）。

-- 等值连结
SELECT c1, c2, c3
FROM t1, t2
WHERE t1.c0 = t2.c0

-- 内连结
SELECT c1, c2, c3
FROM t1
INNER JOIN t2 ON t1.c0 = t2.c0

上述两种方法的结果是一致的。

  • 连结概念
  • 内连结（等值连结）
  • 缩短 SQL 语句；
  • 允许在一条 SELECT 语句中多次使用相同的表
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

  • 自联结（self-join）

      SELECT c1.cust_id, c1.cust_name, c1.cust_contact
  FROM Customers AS c1, Customers AS c2
  WHERE c1.cust_name = c2.cust_name
  AND c2.cust_contact = 'Jim Jones';

  • 自然联结（natural join）

    感觉没讲清楚。

  • 外联结（outer join）

    SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

    外联结还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表。

SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

讲了一堆没啥用的东西。

  • 别名
  • 自联结
  • 自然连结
  • 外联结

这章的UNION感觉没什么用，我好像都没用过，orm也用不上。

使用情况：

  • 在一个查询中从不同的表返回结构数据
  • 对一个表执行多个查询，按一个查询返回数据

使用UNION连接多条SELECT语句进行组合查询。

这里给的示例似乎有误，给了两个一样的sql语句。

UNION规则：

  • UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字UNION 分隔
  • NION 中的每个查询必须包含相同的列、表达式或聚集函数，但次序可以不同
  • 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型

UNION 从查询结果集中自动去除了重复的行。如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。 如果确实需要每个条件的匹配行全部出现（包括重复行），就必须使用 UNION ALL，而不是 WHERE。

在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后，排序所有结果。

利用 UNION，可以把多条查询的结果作为一条组合查询返回，不管结果中有无重复。 使用 UNION可极大地简化复杂的 WHERE 子句，简化从多个表中检索数据的工作。

INSERT INTO tablename
VALUES(v1,v2,v3);

INSERT INTO tablename(c1,c2,c3)
VALUES(v1,v2,v3);

INSERT INTO tablename(c1,c2,c3)
SELECT ...

若不指定列名，必须给按顺序每一个列提供值，如果没有值使用NULL。 省略的列必须允许为空或定义了默认值。 也可以使用INSERT SELECT直接插入查询结果。此时结果只看顺序无关列名。

CREATE TABLE table1 AS 
SELECT * FROM table2
  • 使用INSERT INTO插入数据
  • 使用INSERT SELECT插入表
  • 使用CREATE SELECT复制表
UPDATE table1
SET key1 = value1, key2 = value2
WHERE ...
DELETE FROM table1
WHERE ...
  • 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
  • 保证每个表都有主键，尽可能像 WHERE 子句那样使用它。
  • 在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
  • 使用强制实施引用完整性的数据库，这样 DBMS 将不允许删除其数据与其他表相关联的行。
  • 有的 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性，应该使用它。
  • UPDATE语句
  • DELETE语句
CREATE TABLE table1
(
    column1 TYPE NULL/NOT NULL,
    column2 TYPE NULL/NOT NULL,
    ...
)

不同DMBS对更新表的限制不一，需要具体查询对应文档。

ALTER TABLE table1
ADD column1 TYPE;

ALTER TABLE table1
DROP COLUMN column2;
DROP TABLE table1

不同DBMS对重命名表实现不同，需要具体查询对应文档。

  • 创建表
  • 更新表
  • 删除表

到这里终于来点有用的了。

  • 重用 SQL 语句。
  • 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
  • 使用表的一部分而不是整个表。
  • 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

尽管视图是个很重要的特性，但在orm中似乎基本没有用到，类似的功能在应用层通过保存构建查询语句的过程作为变量实现。

CREATE VIEW view1 AS
SELECT ...

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

过程的功能在orm中也被应用层取代，数据库操作基本只剩下crud。虽然sql本身也算一门比较完备的变成语言能实现各种功能，但将这些实现放到应用层而不是sql里能更好地与业务逻辑相结合。

简单来说，存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

  • 通过把处理封装在一个易用的单元中，可以简化复杂的操作。
  • 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。
  • 上一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
  • 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
  • 上一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
  • 因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令所需的工作量少，提高了性能。
  • 存在一些只能用在单个请求中的 SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

省流：简单、安全、高性能。

  • 不同 DBMS 中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的 DBMS，至少客户端应用代码不需要变动。
  • 一般来说，编写存储过程比编写基本 SQL 语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为安全措施（主要受上一条缺陷的影响）。
EXCUTE proc1(
    value1,
    value2,
    ...
)

语法略，各DBMS实现不一，有需要查文档。

  • 执行/创建/使用存储过程
  • 各种 DBMS 对存储过程的实现不一

除了crud，orm中最常用的就是事务了。比起手动begin和commit，我更喜欢直接transaction开各事务块，内部基本不用考虑事务本身。

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

  • 事务（transaction）指一组 SQL 语句；
  • 回退（rollback）指撤销指定 SQL 语句的过程；
  • 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
  • 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

老生常谈，不同 DBMS 用来实现事务处理的语法不同，需要参考具体文档。

ROLLBACK 命令用来回退（撤销）SQL 语句。

一般的 SQL 语句提交（写或保存）操作是自动进行的，这就是所谓的隐式提交（implicit commit）。而在在事务处理块中，提交不会隐式进行，需要手动提交使用 COMMIT 命令进行提交。

使用 SAVEPOINT 命令可以创建保留点（占位符），之后就可以通过 ROLLBACK 回退到特定保存点而非回退整个事务，从而实现精细化操作。

  • 创建事务
  • COMMIT
  • ROLLBACK
  • SAVEPOINT

略，不同DBMS语法混在一起讲的比较杂，大致就是查询的结果集。这些在orm里直接遍历就行，也不是必须在sql里实现的操作。

很多约束都可以通过代码而非约束实现，而且修改起来也方便。约束一般在建表时就得定义好，后续修改也麻烦。如果是使用纯数据库那约束确实有用，但如果是orm之类操作数据库其实没必要耗费心力。

主键：

  • 任意两行的主键值都不相同。
  • 每行都具有一个主键值（即列中不允许 NULL 值）。
  • 包含主键值的列从不修改或更新。
  • 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

外键：

外键用于表示引用。虽然很常用但我其实很少在定义外键时声明其为外键约束。毕竟有时这个外键约束影响到数据库操作很烦，级联删除也可以通过代码实现替代约束。

唯一约束:

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

  • 表可包含多个唯一约束，但每个表只允许一个主键。
  • 唯一约束列可包含 NULL 值。
  • 唯一约束列可修改或更新。
  • 唯一约束列的值可重复使用。
  • 与主键不一样，唯一约束不能用来定义外键。

检查约束:

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。

  • 检查最小或最大值。例如，防止 0 个物品的订单（即使 0 是合法的数）。
  • 指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
  • 只允许特定的值。例如，在性别字段中只允许 M 或 F。

同理，检查约束也可通过代码而非约束实现。

所以倒是主要在建表时实现，代码的话差不多也是通过orm建表时创建索引。

  • 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS 必须动态地更新索引。
  • 索引数据可能要占用大量的存储空间。
  • 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
  • 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
  • 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

触发器也可在orm中通过钩子或者手动实现。

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作（或组合）相关联。

  • 保证数据一致。例如，在 INSERT 或 UPDATE 操作中将所有州名转换为大写。
  • 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
  • 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
  • 计算计算列的值或更新时间戳。

一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。

需要保护的操作：

  • 对数据库管理功能（创建表、更改或删除已存在的表等）的访问；
  • 对特定数据库或表的访问；
  • 访问的类型（只读、对特定列的访问等）；
  • 仅通过视图或存储过程对表进行访问；
  • 创建多层次的安全措施，从而允许多种基于登录的访问和控制；
  • 限制管理用户账号的能力
  • 约束
  • 索引
  • 触发器
  • 数据库安全

200多页很快就读完了。虽然章节很多，但每一章内容都很少10分钟就能搞定，看完整本书也要不了几个小时。 因为很多内容不同DBMS的实现不同，所以这本书也没办法深入去讲只能提一嘴一笔带过。 除了crud和事务以外的操作，在orm中基本都不会用到。类似的逻辑可以大多可以通过代码而非sql实现，对非专业db工程师的程序员来说更友好。至于一些需要DB本身特性才能更快更好处理的需求，也可以通过rawsql实现。 确实是很适合初学者入门的书，各种基础知识点简明易懂，可以快速掌握sql基本用法。但对我就作用有限了，只有最后几章有点用。还是得找进阶一点的书来看。

更新于 2025-01-18
阅读原始文档 | 查看源代码 | 编辑此页 | 报告问题
 Sql
 | 主页