SQL 相关知识

检索

检索去重数据关键字——DISTINCT

DISTINCT关键字指示数据库只返回不同值，若使用DISTINCT关键字，必须放在列名前面。DISTINCT关键字作用于所有列。

检索从第N行开始检索前M行数据

SQL SERVER：

ORACLE：

MySQL,MariaDB,PostgreSQL或者SQLite:

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

数据库检索从第0行开始，因此LIMIT 1 OFFSET 1会检索第2行。

三种注释方式

排序相关注意点

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。

为了明确地为数据进行排序，可以使用ORDER BY子句。ORDER BY子句取一个或多个列的名字。

ORDER BY 需要保证为SELECT语句的最后一条子句，即放在最后。

通常，ORDER BY子句使用的列可以为显示选择的列，也可以是非检索的列数据。

ORDER BY 后可以跟多个列，排序顺序为第一个列，当第一个列中有多行相同时，采用第二列进行排序，以此类推。

ORDER BY可以按相对列的位置进行排序。

这样表示好处在于不用重新输入列名，但缺点也很明显：1.不明确给出列名有可能造成错用列名排序；2.若修改索引列的数量和顺序，则ORDER BY也需要更改，若遗漏将可能导致数据顺序出现异常；3.若进行排序的列不再索引列中，无法使用此种写法。

可以混合使用列名排序和位置排序。

排序方向

默认排序顺序为升序(从A到Z)，还可以使用降序排列，指定DESC关键字。

DESC关键字只应用到直接位于其前面的列名,如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

默认按字典序排序，若需要改变，需要DBMS Mananger进行修改。

过滤数据

只检索满足特定条件的数据，需要指定搜索条件，搜索条件也称过滤条件，在SELECT语句中们加入WHERE关键字加搜索条件进行过滤。

WHERE子句操作符

单引号用来限定字符串。如果将值与字符串类型

的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS

NULL子句。

组合WHERE子句

AND、OR逻辑操作符

SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND子句或OR子句等逻辑操作符的方式使用。

>

AND指示DBMS只返回满足所有给定条件的行。

可以增加多个过滤条件，每个条件间都要使用AND关键字。

>

OR操作符指示DBMS检索匹配任一条件的行

AND在求值过程中优先级比OR更高,使用圆括号进行分组可以避免查询条件混乱,消除歧义。

IN操作符

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

优点:

• 在有很多合法选项时(一般超过三个)，IN操作符的语法更清楚，更直观。
• 在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
• IN操作符一般比一组OR操作符执行得更快。
• IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。NOT关键字可以用在要过滤的列前，而不仅是在其后。

也可以使用<>或!=操作符来完成。

在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

用通配符进行过滤

前面的过滤条件都是使用已知的值。利用通配符，可以创建比较特定数据的搜索模式。

>

通配符（wildcard）

用来匹配值的一部分的特殊字符。

搜索模式（search pattern）

由字面值、通配符或两者组合构成的搜索条件。

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。

百分号通配符(%)

最常用的通配符是百分号(%)。在搜索串中，%表示任何字符出现任意次数。

%告诉DBMS接受Fish之后的任意字符，不管它有多少字符。

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。

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

>

需要特别注意，%代表搜索模式中给定位置的0个、1个或多个字符。通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。

下划线（_）通配符

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

与%能匹配0个字符不同，_总是刚好匹配一个字符，不能多也不能少。

方括号（[ ]）通配符

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

[JM]匹配任何以方括号中字母开头的联系人名，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。此通配符可以用前缀字符^（脱字号）来否定。

当然，也可以使用NOT操作符得出相同的结果。^的唯一优点是在使用多个WHERE子句时可以简化语法。

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

需要特别注意，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，计算字段的数据与其

他列的数据的返回方式相同。

拼接字段

Access和SQL Server使用+号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用||。在MySQL和MariaDB中，必须使用特殊的函数。

"||"

为正确返回格式化的数据，必须去掉空格。这可以使用SQL的RTRIM()函数来完成。

MySQL ↓

SELECT语句本身与以前使用的相同，只不过这里的计算字段之后跟了文本AS vend_title。它指示SQL创建一个包含指定计算结果的名为vend_title的计算字段。任何客户端应用都可以按名称引用这个列，就像它是一个实际的表列一样。

>

说明：AS通常可选

在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。

提示：别名的其他用途

别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。

警告：别名

别名既可以是一个单词也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。

算术运算

计算字段的另一常见用途是对检索出的数据进行算术计算。

| 操作符 | 说明 |

|--| --|

| + | 加 |

| - | 减 |

| * | 乘 |

| / | 除 |

>

SELECT语句为测试、检验函数和计算提供了很好的方法。虽然SELECT通常用于从表中检索数据，但是省略了FROM子句后就是简单地访问和处理表达式，例如SELECT
3 * 2;将返回6，SELECT Trim(' abc ');将返回abc，SELECT Now();使用Now()函数返回当前日期和时间。

使用函数

大多数SQL支持以下类型函数：

• 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
• 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

函数可以作为SELECT语句的WHERE子句中使用，在其他SQL语句中使用等。

文本处理函数

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但多数DBMS都提供对SOUNDEX的支持。

MySQL可以使用YEAR()的函数中提取年份。

数值处理函数

数值处理函数：

| 函数 | 说明 |

| --- | --- |

| ABS() | 返回一个数的绝对值 |

| COS() | 返回一个角度的余弦 |

| EXP() | 返回一个数的指数值 |

| PI() | 返回圆周率 |

| SIN() | 返回一个角度的正弦 |

| SQRT() | 返回一个数的平方根 |

| TAN() | 返回一个角度的正切 |

聚集函数

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）；
• 获得表中某些行的和；
• 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。

| 函数 | 说明 |

| -- | -- |

| AVG() | 返回某列的平均值 |

| COUNT() | 返回某列的行数 |

| MAX() | 返回某列的最大值 |

| MIN() | 返回某列的最小值 |

| SUM() | 返回某列值之和 |

>

VG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

AVG()函数忽略列值为NULL的行。

COUNT()

利用COUNT()函数确定表中行数或符合特定条件的行数。

• 使用COUNT(*)对表中行的数目进行技术，不管表列中包含的是空值(NULL)还是非空值
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

MAX()

MAX()返回指定列中的最大值。

>

虽然MAX()一般用来找出最大的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。MAX()函数忽略列值为NULL的行。

MIN()

和MAX()使用方法相反。

SUM()

SUM()用来返回指定列值得和，也可以用来合计计算值。

>

利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。SUM()函数忽略列值为NULL的行。

聚集不同值

以上五个聚集函数都可以如下使用:

• 对所有行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）。

• 只包含不同的值，指定DISTINCT参数。

• ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。

• DISTINCT不能用于COUNT(*)

  如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

• 将DISTINCT用于MIN()和MAX()

  虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否只考虑不同值，结果都是相同的。

• 除了这里介绍的DISTINCT和ALL参数,支持对查询结果的子集进行计算的TOP和TOP PERCENT。

组合聚集函数

SELECT语句可根据需要包含多个聚集函数。

在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。

数据分组

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。分组是使用SELECT语句的GROUP BY子句建立的。

GROUP BY子句指示DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。

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

过滤分组

HERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。

SQL为此提供了另一个子句，就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组。WHERE和HAVING可以一起使用。

分组和排序

>

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

SELECT子句顺序

| 子句 | 说明 | 是否必须使用 |

| -- | -- |

| --- | ---|

| SELECT | 要返回的列或表达式 | 是 |

| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |

| WHERE | 行级过滤 | 否 |

| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |

| 组级过滤 | 否 |

| ORDER BY | 输出排序顺序 | 否 |

子查询

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

假如需要列出订购物品RGAN01的所有顾客：

1. 检索包含物品RGAN01的所有订单的编号。
2. 检索具有前一步骤列出的订单编号的所有顾客的ID。
3. 检索前一步骤返回的所有顾客ID的顾客信息。

子查询总是从内向外处理。包含子查询的SELECT语句难以阅读和调试，它们在较为复杂时更是如此。把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用。

在WHERE子句中使用子查询能够编写出功能很强且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

>

警告：只能是单列

作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

作为计算阻断使用字符串

用一个句点分隔表名和列名，这种语法必须在有可能混淆列名时使用。如果在SELECT语句中操作多个表，就应使用完全限定列名来避免歧义。

联结

联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。要理解联结不是物理实体。换句话说，它在实际的数据库表中并不存在。DBMS会根据需要建立联结，它在查询执行期间一直存在。

WHERE子句指示DBMS将Vendors表中的vend_id与Products表中的vend_id匹配起来。要匹配的两列指定为Vendors.vend_id和Products.vend_id。这里需要这种完全限定列名，如果只给出vend_id，DBMS就不知道指的是哪一个（每个表中有一个）。

>

笛卡儿积（cartesian product）

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

>

不要忘了WHERE子句

要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。同理，要保证WHERE子句的正确性。不正确的过滤条件会导

致DBMS返回不正确的数据。

使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。其实，可以对这种联结使用稍微不同的语法，明确指定联结的类型。

联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

SQL不限制一条SELECT语句中可以联结的表的数目。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

>

DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。

>

联结中表的最大数目

虽然SQL本身不限制每个联结约束中表的数目，但实际上许多DBMS都有限制。

使用表别名

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：

• 缩短SQL语句；
• 允许在一条SELECT语句中多次使用相同的表。

>

警告：Oracle中没有AS

Oracle不支持AS关键字。要在Oracle中使用别名，可以不用AS，简单地指定列名即可（因此，应该是Customers C，而不是Customers ASC）。需要注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

不同类型的联结

联结类型：

• 内联结或等值联结
• 自联结（self-join）
• 自然联结（natural join）
• 外联结（outer join）

提示：用自联结而不用子查询

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子

查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结

*自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT ），而对其他表的列使用明确的子集来完成。

外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：

• 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
• 列出所有产品以及订购数量，包括没有人订购的产品；
• 计算平均销售规模，包括那些至今尚未下订单的顾客。

内联结:检索所有顾客及其订单

外联结:检索包括没有订单顾客在内的所有顾客

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

还存在另一种外联结，就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。全外联结的语法如下：

Mysql不支持FULL OUTER JOIN

使用带聚集函数的联结

所有顾客及每个顾客所下的订单数

使用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。

组合查询

SQL允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

主要有两种情况需要使用组合查询：

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

>

提示：组合查询和多个WHERE条件

多数情况下，组合相同表的两个查询所完成的工作与具有多个WHERE子句条件的一个查询所完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询。

UNION关键字

利用UNION，可给出多条SELECT语句，将它们的结果组合成一个结果集。

部分情况下，使用UNION可能比使用WHERE子句更为复杂。但对于较复杂的过滤条件，或者从多个表（而不是一个表）中检索数据的情形，使用UNION可能会使处理更简单。

UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

使用UNION时，重复的行会被自动取消。如果想返回所有的匹配行，可使用UNION ALL而不是UNION。

UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），就必须使用UNION ALL，而不是WHERE。

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

UNION在需要组合多个表的数据时也很有用，即使是有不匹配列名的表，在这种情况下，可以将UNION与别名组合，检索一个结果集。

数据插入

• 插入完整的行；
• 插入行的一部分；
• 插入某些查询的结果。

事物处理

术语：

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

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

使用简单的ROLLBACK和COMMIT语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。

在MariaDB、MySQL和Oracle中创建占位符，可使用SAVEPOINT语句：

每个保留点都要取能够标识它的唯一名字，以便在回退时，DBMS知道回退到何处。

可以在SQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能灵活地进行回退。

游标

游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

• 能够标记游标为只读，使数据能读取，但不能更新和删除。

• 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）。

  能标记某些列为可编辑的，某些列为不可编辑的。

• 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。

• 指示DBMS对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。

• 游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

索引

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