SQL必知必会------笔记（更新中）

设计SQL 的目的是很好地完成一项任务——提供一种从数据库中读写数据的简单有效的方法。

一、数据库相关概念的通俗理解
1、数据库:是一个以某种有组织的方式存储的数据集，或保存有组织的数据的容器（通常是一个文件或一组文件）。

2、表:是一种结构化的文件，可用来存储某种特定类型的数据（某种特定类型数据的结构化清单）；可以保存顾客清单、产品 目录，或其他信息清单。
存储在表中的是同一种类型的数据或清单，不同类型的数据或清单应该创建不同的表。

3、表名:使表名成为唯一的，实际上是数据库名和表名等的组合。

4、数据库中每个列都有相应的数据类型。数据类型（datatype）定义了列可以存储哪些数据种类。

5、行：表中的一个记录，你可能听到用户在提到行时称其为数据库记录（record）。这两个术语多半是可以交替使用的，但从技术上说，行才是正确的术语。

6、主键：一列（或一组列），其值能够唯一标识表中每一行。主键用来表示一个特定的行。没有主键，更新或删除表中特定行就极为困难，因为你不能保证操作只涉及相关的行。

7、应该总是定义主键，虽然并不总是需要主键，但多数数据库设计者都会保证他们创建的每个表具有一个主键，以便于以后的数据操作和管理。

表中的任何列都可以作为主键，只要它满足以下条件：
q 任意两行都不具有相同的主键值；
q 每一行都必须具有一个主键值（主键列不允许NULL 值）；
q 主键列中的值不允许修改或更新；
q 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。

二、检索数据
关键字：作为SQL 组成部分的保留字。关键字不能用作表或列的名字，SQL 语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL 语句都是由一个或多个关键字构成的。

1、select 语句
使用SELECT 检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。
多条SQL 语句必须以分号（；）分隔，
SQL 语句不区分大小写，对SQL 关键字使用大写，而对列名和表名使用小写，这样做使代码更易于阅读和调试，
在处理SQL 语句时，其中所有空格都被忽略。将SQL 语句分成多行更容易阅读和调试。

2、从一个表中，在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。

3、SELECT 语句还可以检索所有的列而不必逐个列出它们。在实际列名的位置使用星号（）通配符可以做到这点。如果给定一个通配符（），则返回表中所有列。

4、使用DISTINCT 关键字，顾名思义，它指示数据库只返回不同的值（去重）。如果使用DISTINCT 关键字，它必须直接放在列名的前面。DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。

5、如果你使用SQL Server使用TOP 关键字来限制最多返回多少行，
SELECT TOP 5 prod_name
FROM Products;
如果你使用Oracle，需要基于ROWNUM（行计数器）来计算行：
SELECT prod_name
FROM Products
WHERE ROWNUM <=5;
如果你使用MySQL，SQLite，需要使用LIMIT子句，
SELECT prod_name
FROM Products
LIMIT 5;

6、注释
单行注释：使用 – （两个连字符）嵌在行内。 – 之后的文本就是注释，
另一种是，在一行的开始处使用#，这一整行都将作为注释
多行注释：从/开始，到/结束，/和/之间的任何内容都是注释。这种方式
常用于给代码加注释，

三、排序检索数据
使用SELECT 语句的 ORDER BY 子句，根据需要排序检索出的数据。

1、SQL 语句由子句构成，有些子句是必需的，有些则是可选的。一个子
句通常由一个关键字加上所提供的数据组成。

2、ORDER BY 子句取一个或多个列的名字，据此对输出进行排序。

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

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

5、ORDER BY 还支持按相对列位置进行排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

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

7、与DESC相对的是ASC（或ASCENDING），在升序排序时可以指定它。升序是默认的（如果既不指定ASC 也不指定DESC，则假定为ASC）。
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

四、过滤数据
使用SELECT 语句的WHERE 子句指定搜索条件

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

2、在同时使用ORDER BY 和WHERE 子句时，应该让ORDER BY 位于WHERE 之后

3、WHERE子句操作符

4、单引号
单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

5、范围值检查
要检查某个范围的值，可以使用BETWEEN 操作符，它需要两个值，即范围的开始值和结束值。
在使用BETWEEN 时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND 关键字分隔。BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

6、空值检查
1、在一个列不包含值时，称其包含空值NULL。
2、它与字段包含0、空字符串或仅仅包含空格不同。
3、SELECT 语句有一个特殊的WHERE 子句，可用来检查具有NULL 值的列。这个WHERE 子句就是IS NULL 子句。

五、高级数据过滤
通过组合WHERE 子句以建立功能更强、更高级的搜索条件。
操作符：用来联结或改变WHERE 子句中的子句的关键字，也称为逻辑操作符
1、为了进行更强的过滤控制，SQL 允许给出多个WHERE 子句。这些子句有两种使用方式，即以AND 子句或OR 子句的方式使用

2、AND操作符
用在WHERE 子句中的关键字，用来指示检索满足所有给定条件的行。
可以增加多个过滤条件，每个条件间都要使用AND 关键字。

3、OR操作符
OR 操作符与AND 操作符正好相反，它指示DBMS 检索匹配任一条件的行。

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

5、NOT 操作符
WHERE 子句中的NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。

六、用通配符进行过滤
利用通配符，可以创建比较特定数据的搜索模式，使用LIKE 操作符进行通配搜索，以便对数据进行复杂过滤。

在搜索子句中使用通配符，必须使用LIKE 操作符
通配符搜索只能用于字符串
通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符

1、百分号（%）通配符
% 表示任何字符出现任意次数。代表搜索模式中给定位置的0 个、1 个或多个字符。
'%'不会匹配为NULL 的行。

2、下划线（）通配符
只匹配单个字符，（）总是刚好匹配一个字符，不能多也不能少。

3、方括号（[ ]）通配符
用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配（包含方括号里任意一个字符，都可匹配）

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

七、创建计算字段
1、计算字段
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT 语句内
创建的。基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用。

2、拼接字段
将值联结到一起（将一个值附加到另一个值）构成单个值。
在SQL 中的SELECT 语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的DBMS，此操作符可用加号（+）或两个竖杠（||）表示。

为正确返回格式化的数据，必须去掉这些空格。这可以使用SQL 的RTRIM()函数来完成，
RTRIM()去掉字符串右边的空格
LTRIM()去掉字符串左边的空格
TRIM()去掉字符串左右两边的空格

SELECT RTRIM(vend_name) || ’ (’ || RTRIM(vend_country) || ‘)’
FROM Vendors
ORDER BY vend_name;

3、使用别名
别名（alias）是一个字段或值的替换名，AS 关键字是可选的，不过最好使用它。别名的名字既可以是一个单词，也可以是一个字符串，字符串应该括在引号（“双引号”）中，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。

4、执行算术计算

八、使用数据处理函数

1、文本处理函数

注：SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

2、日期和时间处理函数
1.字符和日期要写在单引号内；
2.字符大小写敏感，日期格式也敏感；
3.默认日期格式是 DD-MON-RR；
修改系统默认日期格式：只适用于当前会话
alter session set nls_date_format=‘YYYY-MM-DD’;

3、数值处理函数
数值处理函数仅处理数值数据，这些函数一般主要用于代数、三角或几何运算

九、汇总数据
1、聚集函数
对某些行运行的函数，计算并返回一个值。

1.AVG()函数
AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。
AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。
AVG()函数忽略列值为NULL 的行。

2.COUNT()函数
COUNT()函数进行计数，

COUNT()函数有两种使用方式：
q 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值
（NULL）还是非空值。
q 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL 值。
附：

例子：

3.MAX()函数
MAX()返回指定列中的最大值。MAX()要求指定列名。
MAX()函数忽略列值为NULL 的行。

4.MIN()函数
MIN()返回指定列的最小值。与MAX()一样，MIN()要求指定列名
MIN()函数忽略列值为NULL 的行。

5.SUM()函数
SUM()用来返回指定列值的和（总计）。
SUM()函数忽略列值为NULL 的行。

2、聚集不同值
以上5个聚集函数都可以如下使用。

q 对所有行执行计算，指定ALL 参数或不指定参数（因为ALL 是默认行
为）。
q 只包含不同的值，指定DISTINCT 参数。

DISTINCT 不能用于COUNT(*)

3、组合聚集函数
SELECT 语句可根据需要包含多个聚集函数

十、分组数据
两个新SELECT 语句子句：GROUP BY 子句和HAVING 子句

1、数据分组
使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

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

select语句，若只含有组函数，可以不用group by 子句；若含有列名与组函数，必须要分组过滤；

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

有的SQL 实现允许根据SELECT 列表中的位置指定GROUP BY 的列。
例如，GROUP BY 2, 1 可表示按选择的第二个列分组，然后再按第一个列分组

3、过滤分组
HAVING 非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE 子句都可以用HAVING 来替代。唯一的差别是，WHERE过滤行，而HAVING 过滤分组。另一种理解方法，WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。
使用HAVING 时应该结合GROUP BY 子句，而WHERE 子句用于标准的行级过滤。

4、分组和排序

一般在使用GROUP BY 子句时，应该也给出ORDER BY 子句

5、SELECT 子句顺序