SQL必知必会笔记

发表于 更新于 分类于

DISTINCT 关键字

DISTINCT 指示数据库只返回不同的值。如果是用 DISTINCT 关键字，它必须直接放在列名的前边。

例如：

1
2
SELECT DISTINCT vend_id
FROM Products;

DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。

限制查询返回的结果

  • 在 SQL Serve 和 Access 中使用 SELECT 时，可以使用 TOP 关键字来限制最多返回多少行。

例如：

1
2
SELECT TOP 5 prod_name
FROM Products;

  • 在 Orcale，需要机遇 ROWNUM（行计数器）来计算行。

    例如：

    1
    2
    3
    SELECT prod_name
    FROM Products
    WHERE ROWNUM <=5;

  • 在 MySQL、MariaDB、PostgreSQL 或者 SQLite 中，需要使用 LIMIT 字句。

    例如：

    1
    2
    3
    SELECT prod_name
    FROM Products
    LIMIT 5;

注释语句

有单行注释和多行注释之分，直接上例子。

例如：

1
2
3
4
5
6
7
8
9
10
11
SELECT prod_name -- 这是一条注释
FROM Products;

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

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

ORDER BY 语句排序检索数据

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

例如：

1
2
3
SELECT prod_name
FROM Products
ORDER BY prod_name;

在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中的最后一条子句。

通常，ORDER BY 子句中使用的列件事为显示而选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

按多个列排序

要按多个列排序，简单指定列名，列名之间用逗号分开即可。

按位置排序

除了按列名指出排列顺序外，ORDER BY 子句还支持按相对列位置进行排序。

例如：

1
2
3
SELECT prod_name
FROM Products
ORDER BY prod_name;

指定排序方向

数据排序不限于升序排序（从A到Z），这只是默认的排序顺序。还可以使用 ORDER BY 子句进行降序（从Z到A）排序。为了进行降序排序，必须要指定 DESC 关键字。

例如：

1
2
3
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

用多个列排序

例如：

1
2
3
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

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

WHERE 语句过滤数据

在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤，WHERE 子句在表明（FROM子句）之后给出。

例如：

1
2
3
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

关于 WHERE 子句的位置问题，在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后。

WHERE 子句操作符。

操作符说明
BETWEEN在指定的两个值之间
IS NULL为NULL值

组合 WHERE 子句

操作符：用来联结或改变 WHERE 子句中的子句的关键字，也成为逻辑操作符。分别是 AND 操作符和 OR 操作符。

求值顺序

SQL 在处理 OR 操作符前，优先处理 AND 操作符，AND 操作符的优先级更高。圆括号比 AND 操作符拥有更高的求值顺序。

IN 操作符

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

例如：

1
2
3
4
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

为什么要使用 OR 操作符？

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

NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，那及时否定其后所跟的任何条件。以为 NOT 从不单独使用（它总是与其他操作符一起使用，所以它的语法与其他操作符有所不同）。NOT 关键字可以用在要过滤的列前，而不仅是在其后。

例如：

1
2
3
4
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

为什么使用 NOT？在比较复杂的子句中，与 IN 操作符联合使用时，NOT 可以非常简单的找出与条件列表不匹配的行。

大多数情况下 NOT 操作符可以否定任何条件。

LIKE 操作符

首先来看两个概念。

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

搜索模式：由自勉之、通配符或两者组合构成的搜索条件。

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

谓词：操作符合适不是操作符？答案是，它作为谓词时。从技术上说，LIKE 时谓词而不是操作符，虽然最终的结果都是相同的。

百分号（%）通配符

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

例如：

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

Access 通配符，需要使用 * 而不是 %。

通配符看起来可以匹配任何东西，但是有个例外，就是 NULL，子句 WHERE xx LIKE ‘%’ 不会匹配值为 NULL 的行。

下划线（_）通配符

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

如果是 Access，需要使用 ？而不是 _

例如：

1
2
3
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

方括号（[]）通配符

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

并不是所有的数据库都支持用来创建集合的 []。

例如:

1
2
3
4
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

此语句的 WHERE 子句中的模式为 ‘[JM]%’，这一搜索模式使用了两个不同的通配符。[KM] 匹配任何一方括号中字母开头的联系人明，它也只能匹配单个字符。因此，任何多余一个字符的名字都不匹配，其后的 % 通配符匹配第一个字符之后的任意数目的字符，返回所需的结果。

此通配符可以用前缀字符 ^（脱字号）来否定。

例如：

1
2
3
4
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

此例子与上一个例子查询的是相反的内容。

Access 中，需要用 ！而不是 ^来否定一个集合，因此，使用的是 [!JM] 而不是 [^JM] 。

使用通配符的技巧

SQL 的通配符很有用，但是这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些在使用通配符时要记住的技巧。

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

字段的概念

基本上与列的意思相同，经常互换使用，不过数据库一般称为列，而术语字段通常与计算字段一起使用。

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

拼接字段

拼接：将值联结到一起（将一个值附件到另一个值）构成单个值。

在 SQL 中的 SELECT 语句中，可以用一个特殊的操作符来拼接两个列。

根据使用的数据库的不同，操作符也不相同。除了 MySQL 和 MariaDB 必须使用特殊的函数外，可以用加号（+）或两个竖杠（||）来表示。

具体的用法为： Access 和 SQL Server 使用 + 号，DB2、Oracle、PostgreSQL、SQLite、和 Open Office Base 使用 || 。

例如：

1
2
3
4
5
6
7
8
9
10
SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
-----------------------------------------------------------
Bear Emporium (USA )
Bears R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )

和

1
2
3
4
5
6
7
8
9
10
SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;
-----------------------------------------------------------
Bear Emporium (USA )
Bears R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )

上述两个 SELECT 语句拼接一下元素：

  • 存储在 vend_name 列中的名字
  • 包含一个空格和一个左圆括号的字符串
  • 存储在 vend_country 列中的国家
  • 包含一个右圆括号的字符串

为了去掉结果中多余的空格，可以使用 SQL 的 RTRIM 函数来完成。

例如：

1
2
3
4
5
6
7
8
9
10
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

TRIM 函数，去掉字符串左右两边的空格；RTRIM 函数，去掉字符串右边的空格；LTRIM 函数，去掉字符串左边的空格。

使用别名

SELECT 语句可以很好的拼接地址字段，但是实际上拼接之后的这个列是没有名字的，它只是一个值，如果只是在 SQL 查询工具中查看它并没有什么问题。但是如果要在客户端应用中引用它的话就不可以了，因为一个未命名的列客户端没有办法引用它。

为了解决这个问题，SQL 支持别名。别名是一个字段的值或值得替换值，别名用 AS 关键字赋予。

例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;
-----------------------------------------------------------
vend_title

Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

使用||语法的语句

1
2
3
4
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

MySQL 和 MariaDB 中使用的语句：

1
2
3
4
SELECT Concat(vend_name, ' (', vend_country, ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;

SELECT 语句这里的计算字段，后边跟了文本 AS vend_title，它只是 SQL 创建一个包含指定结算结果的名为 vend_title 的计算字段。从输出可以看到，计算结果与以前的相同，但现在列名为 vend_title ，任何客户端应用都可以按照这个名称引用这个列，就像它是一个世纪的表列一样。

AS 关键字是可选的，不过最好使用它。

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

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

执行算数运算

例如

1
2
3
4
5
6
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

从上述例子可以看出来，将数量和单价相乘作为一个新的字段来展示。

SQL 支持下表中列出的基本算数操作符，圆括号可以用来区分优先顺序。

操作符说明
+加
-减
*乘
/除

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

使用数据处理函数

函数

各个数据库的函数差异见下表

函数语法
提取字符串的组成部分Access 使用MID()；DB2、Oracle、PostgreSQL 和 SQLite 使用 SUBSTR()；MySQL 和 SQL Server 使用 SUBSTRING()
数据类型转换Access 和 Oracle 使用多个函数，每种类型的转换有一个函数；DB2 和 PostgreSQL 使用 CAST()；MariaDB、MySQL 和 SQL Server 使用 CONVERT()
取当前日期Access 使用 NOW()；DB2 和 PostgreSQL 使用 CURRENT_DATE；MariaDB 和 MySQL 使用CURDATE()；Oracle 使用 SYSDATE；SQL Server 使用 GETDATE()；SQLite 使用 DATE()
UPPER() 函数

例如：

1
2
3
4
5
6
7
8
9
10
11
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
--------------------------- ----------------------------
vend_name vend_name_upcase
Bear Emporium BEAR EMPORIUM
Bears R Us BEARS R US
Doll House Inc. DOLL HOUSE INC.
Fun and Games FUN AND GAMES
Furball Inc. FURBALL INC.
Jouets et ours JOUETS ET OURS
一些常用的文本处理函数
函数说明
LEFT()（或使用子字符串函数）返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）返回字符串的长度
LOWER()（Access使用LCASE()）将字符串转换为小写
LTRIM()去掉字符串左边的空格
RIGHT()（或使用子字符串函数）返回字符串右边的字符
RTRIM()去掉字符串右边的空格
SOUNDEX()返回字符串的SOUNDEX值
UPPER()（Access使用UCASE()）将字符串转换为大写

Microsoft Access 和 PostgreSQL 不支持 SOUNDEX()，因此以下的例子不适用于这些 DBMS。另外，如果在创建SQLite 时使用了 SQLITE_SOUNDEX 编译时选项，那么 SOUNDEX() 在 SQLite 中就可用。因为 SQLITE_SOUNDEX 不是默认的编译时选项，所以多数 SQLite 实现不支持 SOUNDEX() 。

日期和时间处理函数

各个数据库中日期和时间处理函数是最重要的，但是它们的可移植性是最差的。

例如：

1
2
3
SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

这个例子（SQL Server 和 Sybase 版本以及 Access 版本）使用了 DATEPART() 函数，顾名思义，此函数返回日期的某一部分。DATEPART() 函数有两个参数，它们分别是返回的成分和从中返回成分的日期。在此例子中，DATEPART() 只从 order_date 列中返回年份。通过与 2012 比较，WHERE 子句只过滤出此年份的订单。

下面是使用名为 DATE_PART() 的类似函数的 PostgreSQL 版本：

1
2
3
SELECT order_num
FROM Orders
WHERE DATE_PART('year', order_date) = 2012;

Oracle 没有 DATEPART() 函数，不过有几个可用来完成相同检索的日期处理函数。

1
2
3
SELECT order_num
FROM Orders
WHERE to_number(to_char(order_date, 'YYYY')) = 2012;

在这个例子中，to_char() 函数用来提取日期的成分，to_number() 用来将提取出的成分转换为数值，以便能与 2012 进行比较。

MySQL 和 MariaDB 具有各种日期处理函数，但没有 DATEPART() 。MySQL 和 MariaDB 用户可使用名为 YEAR() 的函数从日期中提取年份：

1
2
3
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;

在 SQLite 中有个小技巧：

1
2
3
SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = 2012;
数值处理函数

在所有数据库中， 数值函数是最一致，最统一个的函数。

函数说明
ABS()返回一个数的绝对值
COS()返回一个角度的余弦
EXP()返回一个数的指数值
PI()返回圆周率
SIN()返回一个角度的正弦
SQRT()返回一个数平方根
TAN()返回一个角度的正切

汇总数据

聚集函数

我们经常要汇总数据而不用把他们实际检索出来，为此 SQL 提供了专门的函数。

函数说明
AVG()返回某列的平均值
COUNT()返回某列的函数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和

COUNT() 函数右两种使用方式：

  • 使用 COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
  • 使用 COUNT(column) 对特定列中具有值得行进行计数，忽略空值。
聚集不同值
  • 对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行为）
  • 只包含不同的值，指定 DISTINCT 参数。

例如：

1
2
3
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格

分组数据

这里讲介绍两个新的 SELECT 子句：GROUP BY 子句和 HAVING 子句。

例如：

1
2
3
4
5
6
7
8
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
------- ---------
vend_id num_prods
BRS01 3
DLL01 4
FNG01 2

上面的 SELECT 语句指定了两个列：vend_id 包含产品供应商的 ID，num_prods 为计算字段（用COUNT(*) 函数建立）。GROUP BY 子句指示 DBMS 按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods 一次。从输出中可以看到，供应商 BRS01 有 3 个产品，供应商 DLL01 有 4 个产品，而供应商 FNG01 有 2 个产品。因为使用了 GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY 子句指示 DBMS 分组数据，然后对每个组而不是整个结果集进行聚集。

在使用 GROUP BY 子句时，需要知道一些重要的规定：

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

参考链接：

SQL 必知必会电子版

本文章首发于个人博客 LLLibra146’s blog
本文作者：LLLibra146
版权声明：本博客所有文章除特别声明外，均采用 © BY-NC-ND 许可协议。非商用转载请注明出处！严禁商业转载！
本文链接：https://blog.d77.xyz/archives/1da172c2.html