SQL必知必会

了解SQL

---

数据库基础

数据库

保存有组织的数据的容器（通常是一个文件或一组文件）
注意混淆：数据库软件应称为数据库管理系统（DBMS）数据库是通过DBMS创建和操纵的容器

表

某种特定类型数据的结构化清单

主键

一列（或一组列），其值能够唯一标识表中的每一行
主键用来表示一个特定的行，应该总是定义主键
表中的任何列都可以作为主键，只要它满意以下条件：

• 任意两行都不具有相同的主键值
• 每一行都必须具有一个主键值（主键列不允许为Null 值）
• 主键列中的值不允许修改或更新
• 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

主键通常是定义在表的一列上，但并不是必需如此，可一起使用多个列作为主键，应用多个列时，所有列值的组合必须是唯一的

什么是SQL

SQL的优点

• 不是某个特定的数据库专有的语言，大部分DBMS都支持SQL
• 语句描述性强，单词数目不多
• 强力且灵活，可以进行非常复杂的高级的数据库操作

SELECT DISTINCT vend_id 
FROM Products;

SELECT DISTINCT vend_id 告诉 DBMS 只返回不同（具有唯一性）的vend_id 行，所以正如下面的输出，只有 3 行。如果使用 DISTINCT 关键字，它必须直接放在列名的前面。

数据搜索

限制结果

SQL Server 和 Access 中使用 SELECT 时，可以使用 TOP 关键字来限制最多返回多少行，如下所示：

SELECT TOP 5 prod_name 
FROM Products;

上面代码使用 SELECT TOP 5 语句，只检索前 5 行数据。
如果你使用 MySQL、MariaDB、PostgreSQL 或者 SQLite，需要使用 LIMIT子句，像这样：

SELECT prod_name 
FROM Products 
LIMIT 5;

上述代码使用SELECT语句来检索单独的一列数据。
LIMIT 5指示MySQL等 DBMS 返回不超过 5 行的数据。
这个语句的输出参见下面的代码。为了得到后面的 5 行数据，需要指定从哪儿开始以及检索的行数，像这样：

SELECT prod_name 
FROM Products 
LIMIT 5 OFFSET 5;

LIMIT 5 OFFSET 5 指示 MySQL 等 DBMS 返回从第 5 行起的 5 行数据。

第一个数字是指从哪儿开始，第二个数字是检索的行数。

注意：第 0 行

第一个被检索的行是第 0 行，而不是第 1 行。因此，LIMIT 1 OFFSET 1 会检索第 2 行，而不是第 1 行。

提示：MySQL 和 MariaDB 捷径

MySQL和MariaDB支持简化版的LIMIT 4 OFFSET 3语句，即LIMIT 3,4。使用这个语法，逗号之前的值对应 OFFSET，逗号之后的值对应 LIMIT。

注释

注释使用-- （两个连字符）嵌在行内。-- 之后的文本就是注释
例如，这用来描述 CREATE TABLE 语句中的列就很不错。

SELECT prod_name -- 这是一条注释 
FROM Products;

在一行的开始处使用#，这一整行都将作为注释。你在本书提供的脚本
create.sql 和 populate.sql 中可以看到这种形式的注释。
你也可以进行多行注释，注释可以在脚本的任何位置停止和开始。

# 这是一条注释
SELECT prod_name 
FROM Products;

注释从/开始，到/结束，/和/之间的任何内容都是注释。这种方式常用于给代码加注释，就如这个例子演示的，这里定义了两个 SELECT语句，但是第一个不会执行，因为它已经被注释掉了

/* SELECT prod_name, vend_id 
FROM Products; */ 
SELECT prod_name 
FROM Products;

排序检索数据

这一课讲授如何使用 SELECT 语句的 ORDER BY 子句，根据需要排序检索出的数据。
其实，检索出的数据并不是随机显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。但是，如果数据随后进行过更新或删除，那么这个顺序将会受到 DBMS 重用回收存储空间的方式的影响。因此，如果不明确控制的话，则最终的结果不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义

为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。ORDER BY 子句取一个或多个列的名字，据此对输出进行排序。请看下面的例子：

SELECT prod_name 
FROM Products 
ORDER BY prod_name;

注意：ORDER BY 子句的位置

在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。如果它不是最后的子句，将会出现错误消息。
一般默认升序，每列都直接ASC
如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。

过滤数据

使用where 语句

数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名（FROM 子句）之后给出，如下所示：

SELECT prod_name, prod_price 
FROM Products 
WHERE prod_price = 3.49;

这条语句从 products 表中检索两个列，但不返回所有行，只返回prod_price 值为 3.49 的行

提示：SQL 过滤与应用过滤
数据也可以在应用层过滤。为此，SQL 的 SELECT 语句为客户端应用检索出超过实际所需的数据，然后客户端代码对返回数据进行循环，提取出需要的行。通常，这种做法极其不妥。优化数据库后可以更快速有效地对数据进行过滤。而让客户端应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户端过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。

提示：何时使用引号 ？

如果仔细观察上述 WHERE 子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

范围值检查

SELECT prod_name, prod_price 
FROM Products 
WHERE prod_price BETWEEN 5 AND 10;

​ 要检查某个范围的值，可以使用 BETWEEN 操作符。其语法与其他 WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。
​ 例如，BETWEEN 操作符可用来检索价格在 5 美元和 10 美元之间的所有产品，或在指定的开始日期和结束日期之间的所有日期。
​ 从这个例子可以看到，在使用 BETWEEN 时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

空值检查

NULL
无值（no value），它与字段包含 0、空字符串或仅仅包含空格不同。

确定值是否为 NULL，不能简单地检查是否= NULL。
SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。
这个 WHERE 子句就是 IS NULL 子句。其语法如下

SELECT prod_name
FROM Products
WHERE prob_price IS NULL;

SELECT cust_name 
FROM CUSTOMERS 
WHERE cust_email IS NULL;

​ 这一课介绍了如何用 SELECT 语句的 WHERE 子句过滤返回的数据。我们学习了如何检验相等、不相等、大于、小于、值的范围以及 NULL值等。

高级数据过滤

组合 WHERE 子句 ，SQL 允许给出多个 WHERE 子句。
这些子句有两种使用方式，即以 AND 子句或 OR 子句的方式使用。

操作符（operator）

用来联结或改变 WHERE 子句中的子句的关键字，也称为逻辑操作符（logical operator）

使用 AND 操作符给 WHERE 子句附加条件。

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

这条 SELECT 语句中的 WHERE 子句包含两个条件，用 AND关键字联结在一起。AND 指示 DBMS 只返回满足所有给定条件的行。如果某个产品由供应商 DLL01 制造，但价格高于 4 美元，则不检索它。类似地，如果产品价格小于 4 美元，但不是由指定供应商制造的也不被检索。

AND

用在 WHERE 子句中的关键字，用来指示检索满足所有给定条件的行。

OR 操作符与 AND 操作符正好相反，它指示 DBMS 检索匹配任一条件的行。事实上，许多 DBMS 在 OR WHERE 子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

SELECT prod_name, prod_price 
FROM Products 
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

OR

WHERE 子句中使用的关键字，用来表示检索匹配任一给定条件的行。

WHERE 子句中使用圆括号

任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

IN 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN 取一组由逗号分隔、括在圆括号中的合法值。

为什么要使用 IN 操作符？其优点如下。

• 在有很多合法选项时，IN 操作符的语法更清楚，更直观。
• 在与其他 AND 和 OR 操作符组合使用 IN 时，求值顺序更容易管理。
• IN 操作符一般比一组 OR 操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。
• IN 的最大优点是可以包含其他 SELECT 语句，能够更动态地建立WHERE 子句。

IN

WHERE 子句中用来指定要匹配值的清单的关键字，功能与 OR 相当

NOT 操作符

NOT

WHERE 子句中用来否定其后条件的关键字

SELECT prod_name 
FROM Products 
WHERE NOT vend_id = 'DLL01' 
ORDER BY prod_name;

这里的 NOT 否定跟在其后的条件，因此，DBMS 不是匹配 vend_id 为DLL01，而是匹配非 DLL01 之外的所有东西。上面的例子也可以使用<>操作符来完成

为什么使用 NOT？对于这里的这种简单的 WHERE 子句，使用 NOT 确实没有什么优势。但在更复杂的子句中，NOT 是非常有用的。例如，在与 IN 操作符联合使用时，NOT 可以非常简单地找出与条件列表不匹配的行。

MariaDB 中的 NOT

MariaDB 支持使用 NOT 否定 IN、BETWEEN 和 EXISTS 子句。大多数DBMS 允许使用 NOT 否定任何条件。

用通配符进行过滤

如何使用通配符以及怎样使用 LIKE 操作符进行通配搜索，以便对数据进行复杂过滤。
LIKE 操作符

通配符（wildcard）
用来匹配值的一部分的特殊字符

搜索模式（search pattern）
由字面值、通配符或两者组合构成的搜索条件

通配符本身实际上是 SQL 的 WHERE 子句中有特殊含义的字符，SQL 支持几种通配符。为在搜索子句中使用通配符，必须使用 LIKE 操作符。LIKE指示 DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

百分号（%）通配符
最常使用的通配符是百分号（%）在搜索串中，%表示任何字符出现任意次数。
例如，为了找出所有以词 Fish 起头的产品，可发布以下 SELECT 语句：

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';

此例子使用了搜索模式’Fish%’。在执行这条子句时，将检索任意以 Fish 起头的词。%告诉 DBMS 接受 Fish 之后的任意字符，不管它有多少字符。

Access 通配符
如果使用的是 Microsoft Access，需要使用*而不是%

区分大小写

根据 DBMS 的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则’fish%'与 Fish bean bag toy 就不匹配

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。
下面的例子使用两个通配符，它们位于模式的两端：

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '%bean bag%';

搜索模式’%bean bag%'表示匹配任何位置上包含文本 bean bag 的值，不论它之前或之后出现什么

字符通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以 F 起头、以 y 结尾的所有产品：

SELECT prod_name 
FROM Products 
WHERE prod_name LIKE 'F%y';

根据部分信息搜索电子邮件地址

有一种情况下把通配符放在搜索模式中间是很有用的，就是根据邮件地址的一部分来查找电子邮件，例如 WHERE email LIKE ‘b%@forta.com’。

需要特别注意，除了能匹配一个或多个字符外，%还能匹配 0 个字符。%代表搜索模式中给定位置的 0 个、1 个或多个字符。请注意后面所跟的空格

包括 Access 在内的许多 DBMS 都用空格来填补字段的内容。
例如，如果某列有 50 个字符，而存储的文本为 Fish bean bag toy（17 个字符），则为填满该列需要在文本后附加 33 个空格。这样做一般对数据及其使用没有影响，但是可能对上述 SQL语句有负面影响。子句 WHERE prod_name LIKE 'F%y’只匹配以 F 开头、以 y 结尾的 prod_name。如果值后面跟空格，则不是以 y 结尾，所以 Fish bean bag toy 就不会检索出来。简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配 y 之后的字符（或空格）。更好的解决办法是用函数去掉空格

请注意 NULL

通配符%看起来像是可以匹配任何东西，但有个例外，这就是 NULL。子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。

_ 通配符 _总是刚好匹配一个字符，不能多也不能少