SQL必知必会笔记
DISTINCT 关键字
DISTINCT 指示数据库只返回不同的值。如果是用 DISTINCT 关键字,它必须直接放在列名的前边。
例如:
1 | SELECT DISTINCT vend_id |
DISTINCT 关键字作用于所有的列,不仅仅是跟在其后的那一列。
限制查询返回的结果
- 在 SQL Serve 和 Access 中使用 SELECT 时,可以使用 TOP 关键字来限制最多返回多少行。
例如:
1 | SELECT TOP 5 prod_name |
在 Orcale,需要机遇 ROWNUM(行计数器)来计算行。
例如:
1
2
3SELECT prod_name
FROM Products
WHERE ROWNUM <=5;在 MySQL、MariaDB、PostgreSQL 或者 SQLite 中,需要使用 LIMIT 字句。
例如:
1
2
3SELECT prod_name
FROM Products
LIMIT 5;
注释语句
有单行注释和多行注释之分,直接上例子。
例如:
1 | SELECT prod_name -- 这是一条注释 |
ORDER BY 语句排序检索数据
为了明确地排序用 SELECT 语句检索出的数据,可以使用 OEDER BY 子句。ORDER BY 子句取一个或多个列的名字,据此对输出进行排序。
例如:
1 | SELECT prod_name |
在指定一条 ORDER BY 子句时,应该保证它是 SELECT 语句中的最后一条子句。
通常,ORDER BY 子句中使用的列件事为显示而选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的。
按多个列排序
要按多个列排序,简单指定列名,列名之间用逗号分开即可。
按位置排序
除了按列名指出排列顺序外,ORDER BY 子句还支持按相对列位置进行排序。
例如:
1 | SELECT prod_name |
指定排序方向
数据排序不限于升序排序(从A到Z),这只是默认的排序顺序。还可以使用 ORDER BY 子句进行降序(从Z到A)排序。为了进行降序排序,必须要指定 DESC 关键字。
例如:
1 | SELECT prod_id, prod_price, prod_name |
用多个列排序
例如:
1 | SELECT prod_id, prod_price, prod_name |
简单来说,DESC 关键字只应用到直接位于其前面的列名。对于想要在多个列上进行降序排序,必须对每一个列指定 DESC 关键字。
WHERE 语句过滤数据
在 SELECT 语句中,数据根据 WHERE 子句中指定的搜索条件进行过滤,WHERE 子句在表明(FROM子句)之后给出。
例如:
1 | SELECT prod_name, prod_price |
关于 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 | SELECT prod_name, prod_price |
为什么要使用 OR 操作符?
- 在有很多合法选项时,IN 操作符的语法更清楚,更直观。
- 在与其他 AND 和 OR 操作符组合使用 IN 时,求值顺序更容易管理。
- IN 操作符一般比一组 OR 操作符执行的更快。
- IN 最大的优点是可以包含其他 SELECT 语句,能够动态的建立 WHERE 子句。
NOT 操作符
WHERE 子句中的 NOT 操作符有且只有一个功能,那及时否定其后所跟的任何条件。以为 NOT 从不单独使用(它总是与其他操作符一起使用,所以它的语法与其他操作符有所不同)。NOT 关键字可以用在要过滤的列前,而不仅是在其后。
例如:
1 | SELECT prod_name |
为什么使用 NOT?在比较复杂的子句中,与 IN 操作符联合使用时,NOT 可以非常简单的找出与条件列表不匹配的行。
大多数情况下 NOT 操作符可以否定任何条件。
LIKE 操作符
首先来看两个概念。
通配符:用来匹配值的一部分的特殊字符。
搜索模式:由自勉之、通配符或两者组合构成的搜索条件。
为在搜索子句中使用通配符,必须使用 LIKE 操作符,指示数据库,后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。
谓词:操作符合适不是操作符?答案是,它作为谓词时。从技术上说,LIKE 时谓词而不是操作符,虽然最终的结果都是相同的。
百分号(%)通配符
最常使用的通配符时百分号(%)。在搜索串中,% 表示任何字符串出现任意次数。
例如:
1 | SELECT prod_id, prod_name |
Access 通配符,需要使用 * 而不是 %。
通配符看起来可以匹配任何东西,但是有个例外,就是 NULL,子句 WHERE xx LIKE ‘%’ 不会匹配值为 NULL 的行。
下划线(_)通配符
下划线通配符的用途与 % 一样,但是它值匹配单个的字符,而不是多个字符。
如果是 Access,需要使用 ?而不是 _
例如:
1 | SELECT prod_id, prod_name |
方括号([])通配符
方括号通配符用来指定一个字符集,它必须匹配指定位置(通配符位置)的一个字符。
并不是所有的数据库都支持用来创建集合的 []。
例如:
1 | SELECT cust_contact |
此语句的 WHERE 子句中的模式为 ‘[JM]%’,这一搜索模式使用了两个不同的通配符。[KM] 匹配任何一方括号中字母开头的联系人明,它也只能匹配单个字符。因此,任何多余一个字符的名字都不匹配,其后的 % 通配符匹配第一个字符之后的任意数目的字符,返回所需的结果。
此通配符可以用前缀字符 ^(脱字号)来否定。
例如:
1 | SELECT cust_contact |
此例子与上一个例子查询的是相反的内容。
Access 中,需要用 !而不是 ^来否定一个集合,因此,使用的是 [!JM] 而不是 [^JM] 。
使用通配符的技巧
SQL 的通配符很有用,但是这种功能是有代价的,即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些在使用通配符时要记住的技巧。
- 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
- 在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处,把通配符置于开始处,搜索起来时最慢的。
- 仔细注意通配符的位置,如果放错地方,可能不会返回想要的数据。
字段的概念
基本上与列的意思相同,经常互换使用,不过数据库一般称为列,而术语字段通常与计算字段一起使用。
需要特别注意,只有数据库知道 SELECT 语句中哪些列是实际的表列,哪些列是计算字段。从客户端(如应用程序)来看,计算字段的数据与其他列的数据的返回方式相同
拼接字段
拼接:将值联结到一起(将一个值附件到另一个值)构成单个值。
在 SQL 中的 SELECT 语句中,可以用一个特殊的操作符来拼接两个列。
根据使用的数据库的不同,操作符也不相同。除了 MySQL 和 MariaDB 必须使用特殊的函数外,可以用加号(+)或两个竖杠(||)来表示。
具体的用法为: Access 和 SQL Server 使用 + 号,DB2、Oracle、PostgreSQL、SQLite、和 Open Office Base 使用 || 。
例如:
1 | SELECT vend_name + ' (' + vend_country + ')' |
和
1 | SELECT vend_name || ' (' || vend_country || ')' |
上述两个 SELECT 语句拼接一下元素:
- 存储在 vend_name 列中的名字
- 包含一个空格和一个左圆括号的字符串
- 存储在 vend_country 列中的国家
- 包含一个右圆括号的字符串
为了去掉结果中多余的空格,可以使用 SQL 的 RTRIM 函数来完成。
例如:
1 | SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' |
TRIM 函数,去掉字符串左右两边的空格;RTRIM 函数,去掉字符串右边的空格;LTRIM 函数,去掉字符串左边的空格。
使用别名
SELECT 语句可以很好的拼接地址字段,但是实际上拼接之后的这个列是没有名字的,它只是一个值,如果只是在 SQL 查询工具中查看它并没有什么问题。但是如果要在客户端应用中引用它的话就不可以了,因为一个未命名的列客户端没有办法引用它。
为了解决这个问题,SQL 支持别名。别名是一个字段的值或值得替换值,别名用 AS 关键字赋予。
例如:
1 | SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' |
使用||语法的语句
1 | SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' |
MySQL 和 MariaDB 中使用的语句:
1 | SELECT Concat(vend_name, ' (', vend_country, ')') |
SELECT 语句这里的计算字段,后边跟了文本 AS vend_title,它只是 SQL 创建一个包含指定结算结果的名为 vend_title 的计算字段。从输出可以看到,计算结果与以前的相同,但现在列名为 vend_title ,任何客户端应用都可以按照这个名称引用这个列,就像它是一个世纪的表列一样。
AS 关键字是可选的,不过最好使用它。
别名还有其他的用途,常见的用途包括在实际的表列明包含不合法的字符(如空格)时重新命名它, 在原来的名字含混或容易误解时扩充它。
别名既可以是一个单词也可以是一个字符串。如果是后者,字符串应该在引号中。虽然这种做法是合法的,但不建议这么做,多单词的可读性高,不过会给客户端应用带来各种问题。因此,别名最常见的使用时将多个单词的列名重命名为一个单词的名字。
执行算数运算
例如
1 | SELECT prod_id, |
从上述例子可以看出来,将数量和单价相乘作为一个新的字段来展示。
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 | SELECT vend_name, UPPER(vend_name) AS vend_name_upcase |
一些常用的文本处理函数
函数 | 说明 |
---|---|
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 | SELECT order_num |
这个例子(SQL Server 和 Sybase 版本以及 Access 版本)使用了 DATEPART() 函数,顾名思义,此函数返回日期的某一部分。DATEPART() 函数有两个参数,它们分别是返回的成分和从中返回成分的日期。在此例子中,DATEPART() 只从 order_date 列中返回年份。通过与 2012 比较,WHERE 子句只过滤出此年份的订单。
下面是使用名为 DATE_PART() 的类似函数的 PostgreSQL 版本:
1 | SELECT order_num |
Oracle 没有 DATEPART() 函数,不过有几个可用来完成相同检索的日期处理函数。
1 | SELECT order_num |
在这个例子中,to_char() 函数用来提取日期的成分,to_number() 用来将提取出的成分转换为数值,以便能与 2012 进行比较。
MySQL 和 MariaDB 具有各种日期处理函数,但没有 DATEPART() 。MySQL 和 MariaDB 用户可使用名为 YEAR() 的函数从日期中提取年份:
1 | SELECT order_num |
在 SQLite 中有个小技巧:
1 | SELECT order_num |
数值处理函数
在所有数据库中, 数值函数是最一致,最统一个的函数。
函数 | 说明 |
---|---|
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
EXP() | 返回一个数的指数值 |
PI() | 返回圆周率 |
SIN() | 返回一个角度的正弦 |
SQRT() | 返回一个数平方根 |
TAN() | 返回一个角度的正切 |
汇总数据
聚集函数
我们经常要汇总数据而不用把他们实际检索出来,为此 SQL 提供了专门的函数。
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的函数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
COUNT() 函数右两种使用方式:
- 使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
- 使用 COUNT(column) 对特定列中具有值得行进行计数,忽略空值。
聚集不同值
- 对所有行执行计算,指定 ALL 参数或不指定参数(因为 ALL 是默认行为)
- 只包含不同的值,指定 DISTINCT 参数。
例如:
1 | SELECT AVG(DISTINCT prod_price) AS avg_price |
使用了 DISTINCT 参数,因此平均值只考虑各个不同的价格
分组数据
这里讲介绍两个新的 SELECT 子句:GROUP BY 子句和 HAVING 子句。
例如:
1 | SELECT vend_id, COUNT(*) AS num_prods |
上面的 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