SQL 必知必会总结（一）

SQL必知必会总结（一）

第 1 课　了解SQL

1、数据库(database)： 保存有组织的数据容器（通常是一个文件或一组文件）。

2、数据库管理系统(DBMS)： 数据库软件，数据库是通过 DBMS 创建和操纵的容器。

3、表(table): 某种特定类型数据的结构化清单。

4、表名： 使表名成为唯一的，实际上是数据库名和表名等的组合。也就是说，虽然在相同的数据库中不能两次使用相同的表名，但在不同的数据库中完全可以使用相同的表名。

5、列(column): 表中的一个字段。所有表都是由一个或多个列组成的。

6、行(row)： 表中的一个记录。

7、主键(primary key)： 一列（或一组列），其值能够唯一标识表中每一行。

第 2 课　检索数据

1、检索单个列

SELECT prod_name 
FROM Products;

PS:

如果没有明确排序查询结果，则返回的数据没有特定的顺序。返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同数目的行，就是正常的。
多条 SQL 语句必须以分号(;)分隔（在data studio中，不需要分号，需要以go分隔）。
SQL 语句不区分大小写，一般默认的规则是对 SQL 关键字使用大写，而对列名和表名使用小写。
在处理 SQL 语句时，其中所有的空格都被忽略。SQL 语句可以写成长长的一行，也可以分写在多行。

2、检索多个列

SELECT prod_id,prod_name,prod_price 
FROM Products;

3、检索所有列

SELECT * 
FROM Products;

　　PS：如果给定一个通配符(*)，则返回表中所有列。一般而言，除非你确实需要表中的每一列，否则最好别使用通配符(*)。因为检索不需要的列通常会降低检索和应用程序的性能。

4、检索不同的值

SELECT DISTINCT vend_id 
FROM Products;

　　PS: 当 DISTINCT 关键字定义的两个字段时，以去重后剩余行数最多的字段为基准。如：SELECT DISTINCT vend_id,prod_price FROM Products。

5、限制结果

　　不同的数据库管理系统(DBMS)实现本功能有区别

在 SQL Server 和 Access 中使用 SELECT 时，可以使用 TOP 关键字限制最多返回多少行，如下所示：

SELECT TOP 5 prod_name 
FROM Products;

在DB2中：

SELECT prod_name 
FROM Products 
FETCH FIRST 5 ROWS ONLY;

在Oracle中：

SELECT prod_name 
FROM Products 
WHERE ROWNUM <= 5;

在MySQL、MariaDB、PostgreSQL或者SQLite中：

SELECT prod_name 
FROM Products 
LIMIT 5;

PS: 通过子语句 LIMIT 5 OFFENT 5 可以返回从第5行起的5行数据（第一个数字是检索的行数，第二个数字是指从哪儿开始）。该子语句也可以简写为 LIMIT 3,4。

6、使用注释

-- 用于单行注释，通常用于嵌入行内的注释。
# 用于单行注释，通常用于 .sql 文件开头。
/*　...　*/ 用于多行注释。

第 3 课　排序检索数据

1、排序数据

子句(clause): 一个子句通常由一个关键字加上所提供的数据组成。SQL语句由子句构成，有些是必须的，有些则是可选的。
使用 ORDER BY 子句对检索结果进行排序，如下所示：

SELECT prod_name 
FROM Products 
ORDER BY prod_name;

PS：在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。如果不是，将会出现错误信息。

2、按多个列排序

　　要按多个列排序，简单指定列名，列名之间用逗号分开即可，如下所示：

SELECT prod_id,prod_price,prod_name 
FROM Products 
ORDER BY prod_price,prod_name;

　　PS: 在按多个列排序时，排序的顺序往前按规定进行。即对上述例子，仅在多个行具有相同的 prod_price 值时才会按照列 prod_name 进行排序。

3、按列位置排序

　　在 ORDER BY 子句中，使用列名在 SELECT 子句中出现的顺序代替实际的列名。这种方法的++优点++在于：不用重新输入列名；++缺点++在于：

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

4、指定排序方向

　　数据排序的默认顺序是升序顺序，如果需要进行降序排序，必须指定 DESC 关键字，如下所示：

SELECT prod_id,prod_price,prod_name 
FROM Products 
ORDER BY prod_price DESC;

　　PS:

DESC 只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。
DESC 是 DESCENDING 的缩写，这两个关键字都可以使用。
ASC 是 ASCENDING 的缩写，在升序排序是可以指定它。
实际上，ASC 没有多大用处，因为升序是默认的。

5、区分大小写和排序顺序

　　在对文本性数据进行排序时，A 与 a 的区别、a 与 B 的顺序取决于数据库的设置方式。在字典(dictionary)排序顺序中，A 与 a 是相同的，这是大多数数据库管理系统的默认行为。如果你需要改变这种排序方式，需要修改数据库设置，无法通过 ORDER BY 子句完成。

第 4 课　过滤数据

1、使用 WHERE 子句

只检索所需数据需要指定++搜索条件++(search criteria)，搜索条件也称为++过滤条件++(filter condition)。
在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名（FROM子句）之后给出，如下所示：

SELECT prod_name,prod_price 
FROM Products 
WHERE prod_price = 3.49;

2、WHERE 子句操作符

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

PS: 上表中列出的某些操作符是冗余的，并非所有DBMS都支持这些操作符。如果想确定你的数据库管理系统支持哪些操作符，请百度、google或者参阅说明文档。

检查单个值

SELECT prod_name,prod_price 
FROM Products 
WHERE prod_price < 10;

不匹配检查

SELECT vend_id,prod_name 
FROM Products 
WHERE vend_id <> 'DLL01';

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

范围值检查

SELECT prod_name,prod_price 
FROM Products 
WHERE prod_price BETWEEN 5 AND 10;

空值检查

SELECT cust_name 
FROM Customers 
WHERE cust_email IS NULL;

PS： NULL 和非匹配

　　通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL 值的行，但是这不可行。因为未知(unknown)有特殊的含义，数据库不知道他们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。过滤数据时，一定要验证被过滤列中含 NULL 的行确实出现在返回的数据中。

第 5 课　高级过滤数据

1、组合WHERE子句

　　操作符(operate): 用来联结或改变 WHERE 子句中的子句的关键字，也称为逻辑操作符(logical operator)。

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

SELECT prod_id,prod_price,prod_name 
FROM Products 
WHERE vend_id = 'DLL01' AND prod_price <=4;

　　PS：本实例只包含一个 AND 子句，因此最多有两个过滤条件。如果想要增加多个过滤条件，每个条件间都要使用 AND 关键字进行连接。

OR 操作符
　　（OR： WHERE 子句中使用的关键字，用来表示检索匹配任一给定条件的行。）
　　在大部分DBMS默认中，遇到OR WHERE子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

SELECT prod_name,prod_price 
FROM Products 
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

求值顺序
　　SQL 在处理 OR 操作符前，优先处理 AND 操作符。即，由于 AND 在求值过程中优先级更高，操作符被错误的组合了。对于这个问题，解决方法是使用圆括号对操作符进行明确分组。

SELECT prod_name,prod_price 
FROM Products 
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') 
	AND prod_price >= 10;

2、IN 操作符

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

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

　　PS: IN 操作符与 OR 操作符的功能相同，只是使用的形式有区别。

使用 IN 操作符的优点：

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

3、NOT 操作符

　　WHERE 子句中用来否定其后条件的关键字。

SELECT prod_name 
FROM Products 
WHERE NOT vend_id = 'DLL01' 
ORDER BY prod_name;

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

第 6 课　用通配符进行过滤

1、通配符(wildcard)：用来匹配值的一部分的特殊字符。

2、搜索模式(search pattern)：由字面值、通配符或两者组合构成的搜索条件。

3、谓词(predictate)：操作符何时不是操作符？答案是，它作谓词时。从技术上说，LIKE是谓词而不是操作符。

4、LIKE 操作符：用于创建比较特定数据的搜索模式。

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

SELECT prod_id，prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';

　　通配符可以在搜索模式中的任意位置使用，并且可以使用多个通配符。如下实例是使用两个通配符：

SELECT prod_id，prod_name 
FROM Products 
WHERE prod_name LIKE '%bean bag%';

PS 1：百分号(%)通配符除了能匹配一个或多个字符外，还可以匹配 0 个字符。% 代表搜索模式中给定位置的 0 个、1 个或多个字符。
PS 2：百分号(%)通配符不会匹配 NULL。
PS 3: Access需要使用问号(*)。

下划线(_)通配符
　　在搜索串中，_ 只匹配单个字符。

SELECT prod_id，prod_name 
FROM Products 
WHERE prod_name LIKE '__ inch teddy bear';

PS 1: 上边的实例是两个下划线。
PS 2: DB2不支持下划线通配符。
PS 3: Access需要使用问号(?)。

方括号([])通配符
　　在搜索串中，[] 用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。只有微软的 Access 和 SQL Servier 支持集合。

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact;

PS1: 上述实例用于找出所有名字以 J 或 M 开头的联系人。
PS2：在 Accress 中，使用！表示否定一个集合；在 SQL Server 中，使用 ^ 表示否定一个集合。

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[^JM]%' 
ORDER BY cust_contact;

PS: 上述实例用于找出所有名字不以 J 或 M 开头的联系人，与上一个实例相反。

5、使用通配符的技巧

　　SQL 的通配符在搜索记录的时候有很大作用，但是这种功能是以效率为代价的。即，通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。下面给出了使用通配符需要注意的地方：

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

第 7 课　创建计算字段

**1、字段(field): **基本上与列(column)的意思相同，基础互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用。

**2、转换与格式化工作（服务器端与客户端）: **在SQL语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。

**3、拼接(concatenate): **将值联结到一起（将一个值附加到另一个值）构成单个值。
PS：是 + 还是 || ----Access 和 SQL Server 使用 + 号。DB2、Oracle、PostgreSQL、SQLite 和 Open Office Base 使用 ||。

SELECT vend_name || '(' || vend_country || ')' 
FROM Vendors 
ORDER BY vend_name;

PS：拼接字段并去掉空格

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

**4、TRIM函数: **去掉字符左右两边的空格；RTRIM()，去掉字符串右边的空格；LTRIM()，去掉字符串左边的空格。

**5、别名(alais): **一个字段或值的替换名。

拼接后的新列没有名字，只是一个值，客户端如果需要使用这个新值，需要用 AS 为这个新列赋别名。

SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')' 
    AS vend_title 
FROM Vendors 
ORDER BY vend_name;

别名有时也称为导出列(derived column)，不管这么叫，它们说代表的是相同的东西。
别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。
别名的名字既可以是一个单词，也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此别名最常见的使用是将多个单词的列名重命名为一个单词的名字。

**6、SQL 进行算数计算 **

SELECT prod_id, 
       quantity, 
       item_price, 
       quantity * item_price AS expanded_price 
FROM OrderItems 
WHERE order_num = 20008;

7、SQL 算术操作符优先级

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

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

第 8 课　使用函数处理数据

**1、可移植(portable): **所编写的代码可以在多个系统上运行。

2、函数的分类

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

3、文本处理函数

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

PS： SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。

SELECT cust_name, cust_contact 
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

4、日期和时间处理函数
　　oracle 中，检索 2012 年的订单：

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

或者：

SELECT order_num 
FROM Orders 
WHERE order_date 
	BETWEEN to_date('01-01-2012', 'mm-dd-yyyy') 
    AND to_date('12-31-2012', 'mm-dd-yyyy');

5、数值处理函数

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

第 9 课　汇总数据

**1、聚集函数(aggregate function)： **对某些行运行的函数，计算并返回一个值。

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

AVG() 函数：通过对表中行数计数并计算其列值之和，求得该列的平均值。可以用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

SELECT AVG(prod_price) AS avg_price 
FROM Products;

SELECT AVG(prod_price) AS avg_price 
FROM Products 
WHERE vend_id = 'DLL01';

PS1：只用于单个列，AVG() 只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个 AVG() 函数。
PS2：AVG() 函数忽略列值为 NULL 的行。

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

SELECT COUNT(*) AS num_cust 
FROM Customers;

SELECT COUNT(cust_email) AS num_cust 
FROM Customers;

MAX() 函数：获取指定列中的最大值。

SELECT MAX(prod_price) AS max_price 
FROM Products;

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

MIN() 函数：获取指定列中的最小值。

SELECT MIN(prod_price) AS min_price 
FROM Products;

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

SUM() 函数：用来返回指定列值的和（总计）。

SELECT SUM(quantity) AS items_ordered 
FROM OrderItems 
WHERE order_num = 20005;

SELECT SUM(item_price * quantity) AS total_price 
FROM OrderItems 
WHERE order_num = 20005;

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

**2、聚集不同值 **（MicroSoft Access 在聚集函数中不支持 DISTINCT）

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

PS: DISTINCT 必须使用列名，不能用于计算或者表达式。在 COUNT() 函数中，DISTINVT 只能用于 COUNT(column) 形式。

**3、组合聚集函数 **

SELECT COUNT(*) AS num_items, 
	   MIN(prod_price) AS price_min, 
       MAX(prod_price) AS price_max, 
       AVG(prod_price) AS price_avg 
FROM Products;

PS: 在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做也算合法，但许多SQL实现不支持，可能会产生模糊的错误信息。

第 10 课　分组数据

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

2、创建分组

SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
GROUP BY vend_id;

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

3、过滤分组： 使用 HAVING 子句来过滤分组

SELECT cust_id, COUNT(*) AS orders 
FROM Orders 
GROUP BY cust_id 
HAVING COUNT(*)=2;

PS: HAVING 和 WHERE 的差别
　　WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。WHERE 排除的行不包括在分组中，这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。使用 HAVING 时应该结合 GROUP BY 子句，而 WHERE 子句用于标准的行级过滤。
同时使用 WHERE 子句与 HAVING 子句的情况为：列出具有两个以上产品且其价格大于等于4的供应商。

SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id 
HAVING COUNT(*) >= 2;

4、分组和排序

ORDER BY	GROUP BY
对查询的结果进行排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

PS：在上述例子中，使用 GROUP BY 子句按订单号（order_num列）分组数据，以便 COUNT(*) 函数能够返回每个订单中的物品数目。HAVING 子句过滤数据，使得只返回包含三个或更多物品的订单，最后，用 ORDER BY 子句排序输出。

5、回顾 SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

posted @ 2017-11-07 19:55 奋斗小菜鸟阅读(538) 评论(0) 编辑收藏举报

刷新页面返回顶部

奋斗小菜鸟

SQL 必知必会 总结（一）

SQL必知必会 总结（一）

第 1 课 了解SQL

第 2 课 检索数据

第 3 课 排序检索数据

第 4 课 过滤数据

第 5 课 高级过滤数据

第 6 课 用通配符进行过滤

第 7 课 创建计算字段

第 8 课 使用函数处理数据

第 9 课 汇总数据

第 10 课 分组数据

公告