SQL(上)：

数据库基础：

• 从SQL的角度来看，数据库是以某种有组织的方式存储的数据集合。而数据库软件应该叫做数据库管理系统(DBMS)，数据库是通过DBMS创建和操纵的容器。
• 表是一种结构化的文件，可以用来存储某种特定类型的数据。这里关键的一点是，存储在表中的数据是同一种类型的数据，否则后期检索和访问会很困难。数据库中每一个表都有一个名字来标识自己，这个名字是唯一的，即数据库中没有其他表具有相同的名字。
• 表具有一些特性，这些特性定义了数据在表中应该如何存储，包括存储什么样的数据，数据如何分解，各个部分信息如何命名等信息。描述表的这组信息就叫做模式。模式可以用来描述数据库中特定的表，也可以用来描述整个数据库。
• 列：表中的一个字段，所有表都是由一个或者多个列组成的。正确的将数据分解为多个列极为重要，列就像是电子表格的网格中每一列存储着某种特定的信息。
• 数据库中的每个列都有相应的数据类型，数据类型定义了列可以存储哪些数据种类。数据类型还帮助正确的分类数据，并且在优化磁盘使用方面上起到了重要的作用。因此在创建表时必须关注所使用的数据类型。
• 行：表中的数据是按行进行存储的，所保存的每个记录存储在自己的行内，也可以叫做数据库记录。
• 主键：表中每一行都应该有一列或者几列可以唯一的标识自己，而这个一列或者几列称为主键。没有主键，更新或者删除表中特定行就极为困难，因为有可能伤及无辜。
• 表中的任何列都可以作为主键，只要它满足以下条件：1.任意两行都不具有相同的主键值；2.每一行都必须具有一个主键值(主键列不允许空值null)；3.主键列中的值不允许修改或者更新；4.主键值不能重用(如果某行从表中删除，那么它的主键不能赋值给以后的新行)；5.主键通常定义在表的一列上，但是并不是必须这样做，也可以使用多个列作为主键，但是在使用多个列时，上述条件必须满足且列值组合是唯一的。

什么是SQL：

SQL是structured query language(结构化查询语言)的缩写。SQL是一种专门用来与数据库沟通的语言，SQL是一种强有力的语言。

检索数据：

SELECT语句：用于从一个表或者多个表中检索信息。

检索单个列：

​
SELECT 列 --从该表中检索什么列
FROM 表名;--FROM关键字表示从哪个表中检索数据

​

• 未排序数据：如果没有明确的排序查询结果，则返回的数据没有特定的顺序，只要返回的数目没有问题即可。
• 结束SQL语句：多条SQL语句必须以分号(；)分隔。多数的DBMS不需要在单条SQL语句后面加上分号，但是也有DBMS可能必须在单条SQL语句后面加上分号。
• SQL语句不区分大小写，但是习惯是关键字大写，列和表小写，有利于阅读。
• 空格：处理SQL语句时，其中的所有的空格都会被忽略，也就是一句SQL可以分写在多行，也可以一行。
• 数据表示：SQL语句一般返回的是原始的，无格式的数据，不同的DBMS和客户端显示数据的方式略有不同。不过，SQL数据一般很少直接被显示出来，通常情况下，它会返回到应用程序中，应用程序中会规定怎么进行格式化，怎么把数据表示出来。
• 第0行：第一个被检索的是第0行，而不是第1行，9个数据也只有0-8行。

检索多个列：

不同的是，检索多个列必须在SELECT关键字后面给出多个列名，列名之间必须以逗号分隔。注意：最后一个列名不需要加上逗号，否则报错。

​SELECT 列1，列2，列3--多个列，最后一个列不能有逗号
FROM 表名;--FROM关键字表示从哪个表中检索数据

检索所有列：

使用通配符(*)加上SELECT语句，一般而言，除非确实需要表的每一列，否则不要使用*通配符。虽然省事，但是会降低检索速度和应用程序的性能。注意：使用通配符有一个优点：由于检索每一列，所以可以检索出名字未知的列。

SELECT *
FROM 表名;
​

检索不同的值：

因为SELECT语句返回所有的匹配值，如果不希望匹配值重复出现，则可以使用SELECT和DISTINCT组合。它告诉DBMS只返回不同的匹配值，如果有多列，那么会返回不重复的列的组合。

SELECT DISTINCT 列--表中该列不重复的匹配值
FROM 表名；

SELECT DISTINCT 列1，列2--表中这些列不重复的组合匹配值
FROM 表名；

限制结果：

不同的DBMS有不同的方式：

在SQL Server中使用SELECT和TOP组合，TOP来限制最多返回多少行。

SELECT TOP 5 列
FROM 表名；

在DB2中：

​SELECT 列
FROM 表名
FETCH FIRST 5 ROWS ONLY;--仅仅返回5行

在Oracle中，需要使用行计数器ROWNUM来计算行：

​SELECT 列
FROM 表名
WHERE ROWNUM <=5;
​

在MySQL，MariaDB，PostgreSQL或者SQLlite，需要使用LIMIT子句：

​SELECT 列
FROM 表名
LIMIT 5;

表示返回不超过5行；也可以指定从哪里开始以及检索的行数：

​SELECT 列
FROM 表名
LIMIT 5 OFFSET 5;

表示从DBMS返回的第5行开始的5行数据，第一个数字是检索的行数，第二个数字是从哪里开始。

注释：

• 两个连字符(--)：嵌入在行内，--之后的文本就是注释。
• #：在一行的开始处使用，将这一整行都作为注释。
• /*注释*/：多行注释，注释可以在任何位置开始和停止。

排序检索数据：

• 其实，检索出的数据并不是随机显示的。如果不排序，数据一般将以在表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。但是如果数据随后进行过更新或删除，那么这个顺序将会受到DBMS重用回收存储空间的方式的影响。因此，如果不明确控制的话，则最终的结果 不能(也不应该)依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。
• SQL语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。

排序数据：

ORDER BY子句可以取一个列或者多个列，据此对输出进行字母顺序排序。

• 注意：在指定一条ORDER BY子句时，应该保证它是SELECT语句中的最后一句子句。如果不是最后一句的子句，那么就会报错。
• 通常，ORDER BY子句中使用的列将是为显示而选择的列。但是，实际上并不一定要这样，用非检索的列进行排序数据也是完全合法的。

SELECT 列 
FROM 表名
ORDER BY 列;--对该列按字母顺序排序

按多个列排序：

经常需要按不止一个列进行数据排序，要按多个列排序的话，只需要指定这些列，列与列之间用逗号分开即可。

SELECT 列1，列2，列3
FROM 表名
ORDER BY 列1，列2;--先对列1进行排序，当列1的值相同的时候，才会对列1相同值的行按照列2再进行排序。

按列位置排序：

ORDER BY子句还支持按照相对列位置进行排序：

SELECT 列1，列2，列3
FROM 表名
ORDER BY 1，2;--选择相对位置，先按SELECT清单的第一个列排序，再按第二个列排序。

注意：

• 这个技术的好处在于不用输入列名，但是也有缺点。首先，不明确输入列名有可能造成用错列名；其次，在对SELECT进行更改时容易错误的对数据进行排序；最后如果进行排序的列不在SELECT清单中，显然不能使用这个技术。
• 有必要可以使用实际列名和相对列位置组合使用。

指定排序方向：

数据排序不限于(从A到Z)升序排序，这是默认的排序顺序。还可以使用ORDER BY子句配合DESC关键字进行降序排序。

​SELECT 列1，列2，列3
FROM 表名
ORDER BY 列1 DESC;--对列1降序排序

多个列排序或者降序排序：（DESC关键字只应用到直接位于其前面的列名，如果想要在多个列进行降序排序，必须在每一个列都指定DESC关键字，没有指定还是默认的升序。其实升序的关键字是ASC，也可以指定ASC，但是实际上，ASC没有什么用，因为它是默认的）

​
​SELECT 列1，列2，列3
FROM 表名
ORDER BY 列1 DESC，列2 DESC，列3;--对列1，列2降序排序，列3仍然升序

在对文本性数据进行排序时，大小写的顺序是否相同？这取决于数据库的设置方式。大多数数据库管理系统的默认做法是相同的，但是许多DBMS允许数据库管理员在需要的时候改变这种做法。

过滤数据：

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

提示：

1. 有多少个0？显示的时候有可能出现数字末尾有0，比如：3和3.0；这样的情况，往往是因为DBMS指定了所使用的数据类型及其的默认行为。数学角度来看，两者是一样的。
2. SQL过滤和应用过滤：数据也可以在应用层进行过滤，为此，SQL的SELECT语句为客户端应用检索出超过实际所需要的数据，然后客户端代码对返回数据进行循环，提取出所需要的行。通常这种做法极其不妥。
3. 同时使用WHERE子句和ORDER BY子句时，应该让ORDER BY子句在WHERE子句后面，避免错误。

WHERE子句的操作符：（表中的某些操作符是重复的，如<>和！=，并非所有的DBMS都支持这些操作符）

操作符	说   明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!>	不大于
>	大于
>=	大于等于
!<	不小于
BETWEEN	在指定的两个值之间
IS NULL	为NULL值

示例：

#检查相等
SELECT 列
FROM 表
WHERE 列=3；--返回列等于3的行

#检查单个值
SELECT 列
FROM 表
WHERE 列<3；--返回列小于3的行

#不匹配检查
SELECT 列
FROM 表
WHERE 列<>'D'；--返回列不等于D的行

/*对于什么时候加上单引号的情况，单引号用来限定字符串。
也就是说，如果将值与字符串类型的列进行比较的话，那么
该值就要加上单引号，如果将值与数值列进行比较的话，那
么就不用加上单引号*/

#范围值检查
SELECT 列
FROM 表
WHERE 列 BETWEEN 5 AND 10；--返回列范围在5到10之间的行，包括5和10

#空值检查
SELECT 列
FROM 表
WHERE 列 IS NULL；--返回列等于NULL的行

/*在一个列不包含值时，我们称其包含空值NULL。它与字段包含
0，空字符串或者仅仅包含空格不同。*/

/*通过过滤选择不包含指定值的所有行时，你可能希望返回含NULL
值的行。但是这做不到，因为NULL比较特殊，所以在进行匹配过滤
或者非匹配过滤时，不会返回这些结果。*/

高级数据过滤：

组合使用WHERE子句：为了进行更强的过滤控制，SQL允许给出多个WHERE子句，这些子句有两种使用方式，即以AND子句或者OR子句的方式使用。

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

SELECT 列1，列2
FROM 表
WHERE 列1=3 AND 列2=5；--返回列1等于3并且列2等于5的行

OR操作符：用在WHERE子句中的关键字，用来指示检索满足任一指定条件的行。当第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来。

​SELECT 列1，列2
FROM 表
WHERE 列1=3 OR 列2=5；--返回列1等于3或者列2等于5的行

求值顺序：

WHERE子句可以包含任意数目的AND和OR操作符。允许两者结合以进行复杂，高级的过滤。但是两者有优先级，在处理OR操作符前，优先处理AND操作符。为了避免操作符被错误组合，可以使用圆括号（）对操作符进行明确的分组。因为它优先级更高。

SELECT 列1，列2,列3
FROM 表
WHERE 列1=3 OR 列2=5 AND 列3>=10；

SELECT 列1，列2,列3
FROM 表
WHERE (列1=3 OR 列2=5) AND 列3>=10；

/*两者是有区别的，前者是返回满足列2=5并且列3大于等于10的或者
列1=3的；后者是返回列1=3或者列2=5并且它们的列3大于等于10的*/

IN操作符：

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，IN操作符取一组由逗号分隔，括在圆括号中的合法值。其实和OR操作符有相同的功能，但是它的优点还有：IN操作符语法更加直观；求值顺序更容易管理；IN操作符比OR操作符一般执行的更快；最大优点为可以包含其他SELECT语句，从而动态建立WHERE子句。

SELECT 列1，列2,列3
FROM 表
WHERE 列1 IN(3，5)；--返回列1满足3或者5的行

NOT操作符：

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后面所跟着的任何条件。NOT操作符从来不单独使用。使用NOT操作符可以非常简单的找出与条件列表不匹配的行。

SELECT 列1，列2,列3
FROM 表
WHERE NOT 列1=3；--返回列1！=3的行，也就是非列1=3的行

用通配符进行过滤：

前面的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某一个范围的值，共同点是过滤中使用的值都是已知的。而利用通配符，可以创建比较特定数据的搜索模式。通配符本身是SQL的WHERE子句中具有特殊含义的字符，SQL支持几种通配符，为了在搜索子句中使用通配符，必须使用LIKE操作符，用于告诉DBMS利用通配符匹配。通配符搜索只能用于文本字段，非文本字段不能使用通配符搜索。

百分号%通配符：

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

WHERE 列 LIKE 'F%'--以F为开头的
WHERE 列 LIKE '%F'--以F为结尾的
WHERE 列 LIKE '%F%'--包含F的，不管之前之后是什么字符

注意：

• 根据DBMS的不同及其配置，搜索是可以区分大小写的。如果区分大小写，则'F%'与'f a'是不匹配的。
• 注意空格：有些DBMS用空格来填补字段的内容，例如：一列有50个字符，其中存储的文本为17个，那么文本后面就要填满33个空格。这样做一般对数据没有影响，但是对'%y'这种情况有可能影响，因为匹配的是y结尾的，但是文本后面跟着空格，就不是以y结尾了。解决方法：1.再加一个%，'%y%',表示还匹配y之后的字符或者空格；2.用函数去掉空格。
• NULL：%不会匹配NULL的行。

下划线_通配符：

下划线通配符只匹配单个字符，而不是多个字符。当然下划线可以组合使用，比如：_,__,___。

DB2不支持下划线通配符。

WHERE 列 LIKE '__ for'--for前面有两个字符
WHERE 列 LIKE '_ for'--for前面有一个字符
WHERE 列 LIKE 'f_r'

方括号[]通配符：

方括号通配符可以用来指定一个字符集，它必须匹配通配符位置的一个字符。但是MySQL，Oracle，DB2，SQLite，不支持方括号，而SQL server支持。

WHERE 列 LIKE '[JM]%'--表示返回列中以J或者M开头的列

通配符使用技巧：

• SQL的通配符很有用，但是通配符搜索比一般搜索耗费时间更长。
• 不要过度使用通配符，如果其他操作符能够达成目的，应该使用其他操作符。
• 在确实使用通配符时，也尽量不要把它们放在搜索模式的开始处。因为效率慢。
• 注意通配符放置的位置。

创建计算字段：

计算字段：存储在数据库表中的数据一般不是应用程序所需要的格式，应用程序可能需要的是两个字段的结合，或者是两个字段的计算结果。所以我们需要从数据库表中检索出转换，计算或者格式化后的数据，而不是检索出数据，再到客户端重新格式化。因为数据库服务端比客户端要执行的快，而且只有数据库知道哪些是实际的字段和计算字段，从客户端来看，两者返回方式相同。

拼接字段：

将两个值拼接到一起形成一个新的值，也可以是把两个列拼接成一个列。根据DBMS的不同，拼接操作符有所不同：在SQL server中使用+，在DB2，Oracle，postgerSQL和SQLite中使用||,在MySQL和MarialDB中使用Concat函数。

SELECT 列1+列2--或者SELECT 列1+'('+列2+')'
FROM 表
ORDER BY 列1；

SELECT 列1||列2--或者SELECT 列1||'('||列2||')'
FROM 表
ORDER BY 列1；

SELECT Concat(列1,列2)--或者SELECT Concat(列1,'(',列2,')')
FROM 表
ORDER BY 列1；

结合成一个计算字段的两个列用空格填充，许多数据库保存填充为列宽的文本值，如果不需要这些空格，可以使用SQL的RTRIM（）函数来完成。（大多数DBMS都支持RTRIM函数，它去掉字符串右边的空格，LIRIM函数，它去掉字符串左边的空格，TRIM函数，它去掉字符串左右两边的空格）

​
SELECT RTRIM(列1)+RTRIM(列2)--或者SELECT RTRIM(列1)+'('+RTRIM(列2)+')'
FROM 表
ORDER BY 列1；

SELECT RTRIM(列1)||RTRIM(列2)--或者SELECT RTRIM(列1)||'('||RTRIM(列2)||')'
FROM 表
ORDER BY 列1；

SELECT Concat(RTRIM(列1),RTRIM(列2))--或者SELECT Concat(RTRIM(列1),'(',RTRIM(列2),')')
FROM 表
ORDER BY 列1；

使用别名：

SELECT语句可以很好的拼接字段，但是这个新的字段实际上它没有名字，它只是两个字段拼接出来的一个新值。但是这样的一个后果就是一个没有命名的字段不能用于客户端应用中，因为客户端没有办法进行引用它。为了解决这个问题，SQL支持别名，别名是一个字段或者一个值的替换名，别名使用AS关键字。（别名也叫做导出列，AS通常是可选的，但是最好使用它，别名还可以重新命名实际表的列名）

​SELECT RTRIM(列1)+RTRIM(列2)--或者SELECT RTRIM(列1)+'('+RTRIM(列2)+')'
AS 别名--给这个新的值起一个替换名
FROM 表
ORDER BY 列1；

SELECT RTRIM(列1)||RTRIM(列2)--或者SELECT RTRIM(列1)||'('||RTRIM(列2)||')'
AS 别名
FROM 表
ORDER BY 列1；

SELECT Concat(RTRIM(列1),RTRIM(列2))--或者SELECT Concat(RTRIM(列1),'(',RTRIM(列2),')')
AS 别名
FROM 表
ORDER BY 列1；

执行算术计算：

计算字段的另一个常见用途就是对检索的数据进行算术运算，SQL的基本算术运算符有：+，-,*,/(加，减，乘，除)四种，也可以配合圆括号区分优先级。

SELECT 列1，列2，列1*列2 AS 别名
FROM 表；

补充：SELECT语句为测试，检验函数和计算提供了很好的方法，虽然SELECT语句经常用于从表中检索数据，但是在省略了FROM子句之后它就是简单的访问和处理表达式。例如：SELECT 3*2返回6，SELECT Trim（'abc'）返回abc，SELECT Curdate（）返回当前日期和时间。

函数：

与大多数计算机语言一样，SQL也可以使用函数来处理函数，函数一般是在数据上执行的，为数据的转换和处理提供了方便。

函数带来的问题：与几乎所有DBMS都支持SQL语句不同，每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同的支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但是各个函数的名称和语法可能极其不同。这意味着为特定SQL实现编写的代码在其他实现中可能不能用(可移植性差)。所以，如果决定使用函数，应该保证做好代码注释，以便以后你自己能够知道含义。

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

1. 用于处理文本字符串(如删除或填充值，转换值为大写或小写)的文 本函数。 
2. 用于在数值数据上进行算术操作(如返回绝对值，进行代数运算)的 数值函数。
3. 用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差，检查日期有效性)的日期和时间函数。
4. 用于生成美观好懂的输出内容的格式化函数(如用语言形式表达出日期，用货币符号和千分位表示金额)。
5. 返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数。

文本处理函数：

SELECT UPPER(列1)
FROM 表
ORDER BY 列1；

注意：因为SQL函数不区分大小写，不过也要注意保持风格一致，不要变来变去。

常用的文本处理函数：

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH()(也使用DATALENGTH()或LEN())	返回字符串的长度
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT（）（或使用子字符串函数）	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SUBSTR()或SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	将字符串转换为大写

补充：

1. SUBSTR()函数：作用提取字符串的组成部分，也就是用来截取数据库某个字段中的一部分。语法为substr(string,start,length)，1参：必选，数据库中需要截取的字段。2参：必选，正数，从字符串指定位开始截；负数，从字符串结尾指定位子开始截取；0，在字符串中第一个位子开始截取。3参：可选，需要截取的长度。缺省，即截取到结束位置。
2. SOUNDEX()函数：是一个将任何文本字符串转换为其语音表示的字母数字模式的算法。

日期和时间处理函数：

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊形式。日期和时间以特殊形式存储，以便于快速有效的排序和过滤，并且节省物理存储空间。应用程序一般不使用时间和日期的存储形式，因此日期和时间函数总是用来读取，统计和处理这些值。遗憾的是，它们的可移植性最差。

#在SQL server中检索
SELECT 列1
FROM 表
WHERE DATEPART(yy,列1)=2020；

#在PostgreSQL中检索
SELECT 列1
FROM 表
WHERE DATEPART('year',列1)=2020；

#在Oracle中检索
SELECT 列1
FROM 表
WHERE EXTRACT(year FROM 列1)=2020；

DATEPART(1参，2参)函数：返回日期的某一部分。1参是日期的哪一部分；2参是日期列。具体1参有哪些，如下表：

1参	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微秒	mcs

完成相同工作还可以使用BETWEEN操作符：

​SELECT 列
FROM 表
WHERE 列 BETWEEN to_date('2020-12-12','yyyy-mm-dd') 
AND to_date('2020-12-22','yyyy-mm-dd');

• Oracle中的to_date函数用于将字符串1参转换为日期，而BETWEEN操作符用于找出2020-12-12到2020-12-22之间的所有列。两个日期之间的列。当然也可以用DATEPART()函数替换to_date()函数。
• 而DB2，MySQL和MariaDB可以使用YEAR（）

数值处理函数：

数值处理函数仅仅用于处理数值数据。这些函数一般用于代数，三角函数，几何运算，因此不是使用很频繁。在主要的DBMS函数中，数值处理函数是最一致的函数。

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

汇总数据：

聚焦数据：我们经常需要汇总数据而不用把它们实际的检索出来，为此SQL提供了专门的函数，以便于分析和报表的生成。这种类型的例子有：1.确定表中的行数(或者满足某个条件或者包含某个特定值的行数)；2.获取表中某些行的和；3.找出表列(或者所有行或者某些行)的最大值，最小值，平均值。这些情况都是要汇总表中的数据，而不需要查出数据的本身。为了方便这些情况的检索，SQL提供了5个聚集函数(对某些行运行的函数，计算并且返回一个值)。聚集函数还可以用来执行多个列的计算或者同时使用多个聚集函数。如下：

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

/*AVG()只用于单个列，用来确定数值列的平均值，而且
列名必须是函数参数。而多个列的平均值，必须使用多个
AVG(),除非要从多个列计算出一个值时。AVG()忽略列值
为NULL的行*/

SELECT AVG(列) AS 别名
FROM 表；

#特定行
SELECT AVG(列) AS 别名
FROM 表
WHERE 列1='q';--过滤出列1等于q的行，再平均值

SELECT COUNT(*) AS 别名--对所有行进行计数，不管表列包含是NULL还是非NULL
FROM 表；

SELECT COUNT(列) AS 别名--对列中有值的行进行计数，忽略NULL值
FROM 表；

SELECT MAX(列) AS 别名--返回列中最大值
FROM 表；

SELECT MIN(列) AS 别名--返回列中最小值
FROM 表；

SELECT SUM(列) AS 别名--返回列值的总和
FROM 表；

SELECT SUM(列1*列2) AS 别名--返回每行列1*列2之和
FROM 表；

/*MAX,MIN,SUM忽略值为NULL的行，对非数值数据使用MAX和MIN时，例如：
文本数据时，MAX会返回该列排序的最后一行，MIN会返回该列排序的最前面
行，且所有的聚集函数都可以执行多个列的计算*/

聚焦不同的值：

以上的5个聚焦函数都是对所有行或者特定行执行计算，有重复值，因为它们指定的是ALL参数(不指定时默认也是ALL)。如果想只要不同的值的行参与，指定DISTINCT参数。

SELECT AVG(DISTINCT 列)AS 别名--不同值的列的平均值，也就是没有重复
FROM 表；

注意事项：

• DISTINCT不能用于COUNT(*),因为DISTINCT要指定列名，所以也只能用于COUNT()上面。类似的，DISTINCT因为要指定列名，所以不能用于计算和表达式。
• 将DISTINCT用于MAX和MIN是可以的，但是没有价值。

分组数据：

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

SELECT 列 ,COUNT(*) AS 别名--再对每组进行聚集计算
FROM 表
GROUP BY 列；--先对表按照该列分组，列值相同的一组

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY 子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。

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

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

过滤分组：

除了CROUP BY分组数据外，还允许过滤分组，规定包括哪些分组，排除哪些分组。但是对于分组，WHERE并不能完成任务，因为WHERE过滤指定的是行而不是分组。这个时候就要使用HAVING来替代了，HAVING与WHERE很相似，目前所有类型的WHERE都可以使用HAVING来替代。两者区别就是，WHERE过滤行，HAVING过滤分组。并且WHERE在分组之前过滤，HAVING在分组之后过滤。

​
SELECT 列 ,COUNT(*) AS 别名--再对每组进行聚集计算
FROM 表
GROUP BY 列--先对表按照该列分组，列值相同的一组
HAVING COUNT(*)>=2;--返回组中行大于等于2的

如果加上WHERE子句，那么是先使用WHERE过滤，再进行分组过滤的。

分组和排序：

GROUP BY和ORDER BY经常完成相同的工作，但是两者还是有差别：

• ORDER BY对产生的输出就行排序，GROUP BY对行分组，但是输出不一定是分组的顺序，也并不代表你需要以相同的方式排序输出。所以一般使用GROUP BY时，应该也加上ORDER BY子句。
• ORDER BY对任意列都可以使用甚至是非选择的列也可以使用，只可能使用选择列或者表达式列，而且必须使用每个选择列，表达式。

SELECT子句顺序：

1. SELECT(要返回的列或者表达式)，必须使用；
2. FROM(从中检索的表)，从表中选择数据的时候使用；
3. WHERE(行级过滤)，可选；
4. GROUP BY(分组说明)，仅仅在分组计算聚集的时候使用；
5. HAVING(组级过滤)，可选；
6. ORDER BY（输出排序顺序），可选。

使用子查询：

SELECT语句是SQL的查询，上述都是简单的查询，即从单个数据库表中检索数据的单条语句。SQL还支持创建子查询，即嵌套在其他查询中的查询。

利用子查询进行过滤：

举例：

订单存储在三个表中。在Orders 表中存储每个订单的订单编号、客户ID、订单日期，三个列为一行。而每个订单的物品信息存储在OrderItems表中。还有顾客的实际信息存储在Customers表中。现在，假如需要列出订购物品为RCANO1的所有顾客的信息，应该怎样检索？

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

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。也可以使用子查询来把3个查询组合成一条语句。如：

#第一种分为三个查询
SELECT order_num
FROM OrderItems
WHERE prod_id='RGAN01';--检索出prod_id='RGAN01'的order_num（订单编号）

SELECT cust_id
FROM Orders
WHERE order_num IN(上面返回的order_num)--检索出order_num满足的客户ID

SELECT cust_name,cust_contact
FROM Customers
WHERE cust_id IN(上面返回的cust_id)--检索出cust_id满足的客户信息

#三者结一，子查询
​SELECT cust_name,cust_contact
FROM Customers
WHERE cust_id IN(SELECT cust_id
                 FROM Orders
                 WHERE order_num IN(SELECT order_num
                                    FROM OrderItems
                                    WHERE prod_id='RGAN01'));

​

注意：

1. 子查询总是从内到外处理的，上面的例子，先是最里面的SELECT语句，把它返回的值作为参数传递给外部，如果是多个值那么就是多个参数；外部再把返回的值传递给最外面；最后返回的值就是我们需要的数据。
2. 包含子查询的SELECT语句难以阅读和调试，可以适当缩减+分行加强阅读性。
3. 在WHERE子句中使用子查询可以编写出功能很强而且灵活的SQL语句。对于嵌套的子查询数目没有限制，不过实际应用中，不能嵌套太多的子查询。
4. 作为子查询的SELECT语句只能查询单个列，企图检索多个列是错误的。

作为计算字段使用子查询：

使用子查询的另一方法加上创建计算字段。

SELECT cust_name, cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust.id=Customers.cust.id)AS orders
FROM Customers
ORDER BY cust_name;