《sql必知必会》第5版 知识点摘要梳理（前五章）

前言

	鉴于学习sql依据书《sql必知必会》，此学习mysql路径依照此书目录顺序来记录知识点，有的老师会将sql'语句分为DDL、DML、DQL、DCL四个方面来入
手梳理，我这边还是依照书籍目录顺序来学习。学习路径主要有二十一个大章节，分别是：检索数据、排序检索数据、过滤数据、高级数据过滤、用通配符进行
过滤、创建计算字段、使用函数处理数据、汇总数据、分组数据、使用子查询、联结表、创建高级联结、组合查询、插入数据、更新和删除数据、创建和操纵
表、使用视图、使用存储过程、管理事务处理、管理事务过程、使用游标、高级SQL特性。

每日不同事情安排与章节难易不同每日进度有缓有慢，毕业以来均是被事情一件件赶着走到现在七年，浮躁的社会风气下我更应该塌下心来一步步走好当下该做的。王阳明爬山的故事讲述在爬山过程中一直盯着前方山头有多远那就会累的气喘吁吁，只有盯着脚下看着自己走过结结实实的一个个脚印到山顶才能像阳明先生一样气定神闲。让脚步慢下来心才能静下来。那就让我们开始把这一本书的东西装进脑子吧。

---

一、数据的准备

根据书《sql必知必会》附录A 样例表脚本可以取到本书所用练习数据集，取用地址为HTTP：//forta.com/books/0135182794.其中包含create.txt（建表语句） populate.txt（插入数据语句）两个文件。下面是导入操作示意图。

---

打开navicat连接数据库，选择已在命令行创建的数据库projecta，点击工具栏按钮“新建查询”，将create.txt（建表语句）文件中的代码拷贝进入编写区域，点击编写区域上方按钮“运行”键，查看下方摘要信息有没有错误，成功则点击左边项目栏中projecta数据库点击刷新，次数中用到的五个表创建完成

---

上述方式只得到了五张没有数据的空表，但主键、外键与表头均已生成完。同样方式拷贝populate.txt（插入数据语句）文件中的代码到新的查询编写界面运行，则此书所需练习数据已添加进五张对应的表中。
customers表

orderitems表

orders表

products表

vendors表

---

至此此书课程五张所用数据表已准备完毕，建表与导入数据知识书后章节有具体讲解，此处不再过多说明，我们跟着此书章节顺序进行学习。接下来正式进入此书学习阶段

二、检索数据

2.1 select语句

“select”是一个关键字，作为sql组成部分的保留字。关键字不能用作表或列的名字。保留字有许多个，在本书附录D上列有三页如有兴趣可以查询。

使用SELECT检索表数据，必须至少给出两条信息：1、想选择什么；2、从什么地方选择

---

2.2 检索单个列

本书先从最简单的SQL SELECT语句学起，下面是我们需要在navicat新建查询中写的代码，我们将其运行并得到结果，代码与结果为下图。

SELECT `prod_nam`
FROM `Products`;   ##从Products表中检索一个名为prod_name的列。符合想选择什么从什么地方选择两条信息。

输出结果以列表形式在navicat上给出回馈

在我们的结果块内有消息、摘要、结果三个信息块，给出了表运行时长、查询出了几条数据、时间戳等重要信息。多次运行发现列表结果返回的顺序各不相同，如果没有明确的排序查询结果，返回的数据也没有特定的顺序，此时我们查看数据查询是否正确只需查看是否有相同的行数。上述查询返回的所有行数据没有过滤和排序，之后讲到具体过滤排序方式会深入讲解。

注意：1、结束SQL语句
		多条SQL语句必须以分号（;）分隔（必须英文分号，代码编写养成好习惯用英文）。有的DBMS不需要在单条SQL语句后加分号但有的需要，
		为了养成好习惯此处直接定性不管但条多条都需要加分号。
	2、SQL语句和大小写
		在SQL语句中不分大小写，因此SELECT与select是相同的。一般在SQL语句中开发人员对关键字使用大写是为了方便阅读与调试。但是表名
		列名和值可能有所不同，在这类数据上仍要对其区分大小写。
	3、使用换行
		在处理SQL语句时，所有空格换行都被需略，可以写成一行或多行。

下面是一种SQL语句三种不同的书写方式，其作用是一样的。

SELECT prod_name FROM products;


SELECT prod_name
FROM products;


SELECT 
prod_name
FROM 
products;

---

2.3 检索多个列

与检索单列原理一致，仍然需要使用select语句。不同的是在select后面需要给出需要检索的多个列名，并将各个列名用逗号隔开。
注意最后一列名后面不用加逗号。查询与结果如下图

不同的DBMS和客户端的显示数据的方式不同，数据的格式化是表示问题，而不是检索问题。通常很少使用实际检索出的数据（没有应用程序提供的格式）

---

2.4 检索所有列

此节主要用到通配符 * ，表示所有列的集合。星号虽然会省事检出所有列，但也也会影响检索的速度和应用程序的性能。优点是能检索出名字未知的列。

---

2.5 检索不同的值

当我想检索Products表中所有产品供应商的ID时，由于Products表中有9种产品，所以索引出三种九条供应商ID下图所示：

---

如果想要只返回不同的值，那我们就需要用到一个关键字DISTINCT。在选择的列名前加上DISTINCT就会返回不同值的列数据，如下图所示：

---

注意：DISTINCT作用于所有列，不仅仅是跟在其后面的那一列。如果是vend_idhe prod_price两个列，则单个列中也会出现重复的数值，因为
DISTINCT的不重复指后面两列数值组合为一组数的不重复。下图为索引两列使用DISTINCT的结果：

---

2.6 限制结果

如果想要结果只返回第一行或者一定数量的行，SQL语句可以做到，然而遗憾的是不同数据库实现的SQL语句并不相同，下面我尽量罗列较多数据库实现方式

SQL Sever
SQL Sever中使用TOP加数值来限定提取出前几行数据

SELECT 
TOP 5
prod_name
FROM
products;

---

DB2
DB2中使用FETCH FIRST 5 ROWS ONLY只取前五行

SELECT 
prod_name
FROM
products
FETCH FIRST 5 ROWS ONLY；

---

Oracle
Oracle中使用**ROWNUM（行计数器）**来计算行返回前五行数据

SELECT 
prod_name  
FROM
products
WHERE ROWNUM <=5;

---

MySQL、MariaDB、PostgreSQL、SQLLite
这四种数据库均使用LIMIT子句，SELECT语句检索单独的一列数据，LIMIT 5指示MySQL返回不超过5行的数据。我们使用的是MySQL，输出结果如下图

SELECT
prod_name
FROM products
LIMIT 5;

注意：当我们需要检索从第几行起的几行数据时，我们用到的是LIMIT+OFFSET组合，具体操作如下：

LIMIT指定返回的行数，LIMIT带的OFFSET指定从哪儿开始。在我们的例子中products表只有9行数据，所以LIMIT 5 OFFSET 5只返回了4行数据。

注意第一个被检索的行是第0行，不是第1行。LIMIT 1 OFFSET 1 会检索第2行而不是第1行。

---

如要检索第一行可以直接用LIMIT 1 或者 LIMIT 1 OFFSET 1，后者比前者多但是更能使开发者容易记住OFFSET是第几行开始 LIMIT是返回几行数据。

MySQL、MariaDB和SQLLite数据库可以将LIMIT 4 OFFSET 3 简化为LIMIT 3，4.
逗号之前对应OFFSET，逗号之后对应LIMIT。和语句是反着的，如有缩写请注意。

---

2.7 使用注释

 	注释的作用是重要的，可以让开发者为代码添加描述性语句或者其他开发者想写的语句在某一行代码的后面 上面开头和结尾，同时注释也可以用作代码测试，
 在不删除原语句代码的情况下测试新的代码语句。

注释有多种方式实现，此处列举三种实现方式

使用连字符**–**
使用”–“两个连字符符放于代码行内，连字符之后的文本就是注释，例如：

SELECT 
prod_id                --  列名
FROM 
products				-- 表名
LIMIT 1 OFFSET 0;       --  OFFSET 从第几行开始   LIMIT 检索几行

---

使用”#“
在一行开始处使用#，那这一行均为注释，在一行结束后使用#，那#后为注释文本，例如

# 限制结果检索
SELECT 
prod_id                # 列名
FROM 
products				# 表名
LIMIT 1 OFFSET 0;       # OFFSET 从第几行开始   LIMIT 检索几行

本书提供的脚本多以这种方式为注释

---

多行注释使用”/* */“组合

注释从/*开始，从*/结束，中间的内容均为注释，在注释内的内容不会被执行。例如：

/*
SELECT 
prod_id             
FROM 
products			
LIMIT 1 OFFSET 0; 
*/   
SELECT prod_name
FROM products;

下面是代码演示：

---

2.8 此章小结

知识点总结：
SELECT检索单个表列、多个表列、所有表列
如何返回不重复的值
如何注释代码
如何限制结果在第几行开始的几行数据
不同的数据库在上述语句中有什么差异

2.9 章节练习题

挑战题答案网址

本书挑战题答案在http://forta.com/books/0135182794或至图灵社区本书主页www.ituring.com.cn/book/2649下载。

---

1、编写SQL语句，从customers表中检索所有的DI（cust_id）

---

2、OrderItems表包含了所有已订购的产品（有些已被订购多次）。编写SQL语句，检索并列出已订购产品（prod_id）的清单（不用列每个订单，只列出不同产品的清单）。提示：最终应该显示7行。

---

3、编写SQL语句，检索Customers表中所有的列，再编写另外的SELECT语句，仅检索顾客的ID。使用注释，注释掉一条SELECT语句，以便运行另一条SELECT语句。（当然，要测试这两个语句。）

简单的可以直接注释SELECT语句不用一同注释掉，后面FROM表都相同。注意结尾分号一定要是英文分号不然报错。

---

三、排序检索数据

这一节是对**ORDER BY **子句的使用，根据需求排序检索出数据

3.1 排序数据

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

使用ORDER BY子句对数据排序，子句取一个或多个列的名字，默认以首字母/数字顺序排序。下面是其在navicat中的直观表现。

注意：1、ORDER BY子句的位置要在SELECT语句中最后一条子句的位置，如果不是则程序报错。
2、ORDER BY子句中使用的列一般为显示选择的列，不过用非检测的列排序数据是完全合规则的。

---

3.2 按多个列排序

在生产中经常需要按多个列进行数据排序，例如，如果要显示雇员名单，如果多个雇员有相同的姓，可能希望按姓和名排序（首先按姓排序，然后再按名排序）。要按多个列排序只须指定这些列名，列名之间用逗号分开即可。下面操作与结果是检索3个列，并按其中两个列对结果惊醒排序（先按价格排序，再按名称排序）

ORDER BY 前后的列名就是先按前面列名排序，如果前面列名值一致时会对依次第二个列名排序，如第一列名不一致则只。按第一列名排序

---

3.3 按列位置排序

除了按列名进行排序，ORDER BY还支持按相对列位置进行排序（直接使用SELECT清单中对应的数字的列对列名替代）。
下图为实操演示图：

优点：不用重新输入列名
缺点：1、不明确写出列名有可能错用列名排序；2、在更改SELECT语句时容易忽视列名改动导致排序列错误；3、进行排序的列不在SELECT清单中不能用。

注意：当对不出现在SELECT语句清单中的列进行排序时，不能采用位置排序，必要时可以使用列名和相对列位置进行排序。

---

3.4 指定排序方向

数据排序不限于升序排序（从A到Z），升序排序为默认排序顺序。还可以使用ORDER BY 子句进行降序（从Z到A）排序。当进行降序排序时，必须指定DESC关键字。下面例子以价格降序来排序产品（最贵的排在最前面）：

---

当用多个列排序时，DESC可以跟在需要降序的列名后，不需要降序的列名为默认升序排序：

	上图可以看到价格排序由高到低，乍一看产品名没有排序，实则时在相同价格时才有升序排序。DESC关键词只应用到直接位于其前面的列名，如果想
在多个列上进行降序排序，必须对每一列指定DESC关键词。此处DESC是DESCENDING的缩写，所以这两个关键字作用作用相同。与DESC相对的是ASC
或者ASCENDING，使用升序排序可以用ASC。一般用不到ASC，默认排序是升序。

	区分大小写和排序顺序，在字典（dictionary）排序顺序中，A被视为与a相同，大多数数据库管理系统默认如此。但是许多DBMS允许数据库管理员
在需要时改变这种行为（当数据库包含大量外语字符，可能会有这必需排序需求）。如果要对大小写进行排序，ORDER BY 子句做不到，需要请求数据库
管理员的帮助。

---

3.5 此章小节

知识点总结：
ORDER BY 子句对SELECT进行排序，默认排序升序
ORDER BY 排序多列，先以第一列排序后才对相同第一列数据的第二列数据进行排序
ORDER BY 可以排序没有被SELECT筛选出的列
位置排序只对被SELECT筛选的列排序，可以和不被筛选的列名共同使用
ORDER BY 必须在SELECT语句最后
DESC降序排列用在列名后，多个列降序排序需要DESC在降序排列的列名后，列名后没有DESC默认升序排序
默认升序，实际有ASC关键词表示升序排序

---

3.6 章节练习题

1、编写SQL语句，从Customers中检索所有的顾客名称（cust_name），并按从Z到A的顺序显示结果。

分析：从Z到A意思是字母降序排序，使用ORDER BY 子句 并用列名加上DESC关键词逆序排序

---

2、编写SQL语句，从Orders表中检索顾客ID（cust_id）和订单号（order_num），并先按顾客ID对结果进行排序，再按订单日期倒序排列。

分析：此处需要对标进行多列排序使用ORDER BY 子句，并有倒序排列用到关键字DESC，其中订单日期是没有被SELECT筛选出的不显示列。

---

3、显然，我们虚拟商店更喜欢出售比较贵的物品，而且这类物品有很多。编写SQL语句，显示OrderItems表中的数量和价格（item_price），并按数量由多到少、价格由高到低排序。

分析：首先我们要看下表OrderItems中有哪些列和数据的样式，使用通配符*。数量多少是列quantity的数据。数量由多到少和价格由高到低排序均是降序
排序，用到多列排序和DESC关键字。

---

4、 下面的SQL语句有问题吗？（尝试在不运行的情况下指出）

SELECT vend_name,
FROM Vendors
ORDER vend_name DESC;

问题：1、在筛选列名一列不需要在列名后加逗号，多列需要在两列之间用逗号隔开；
2、排序子句为ORDER BY 而不是ORDER。

---

四、过滤数据

这一章节讲述如何使用SELECT语句的WHERE子句指定搜索条件。

4.1 使用WHERE子句

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

	这条语句从products表中检索两个列，但不返回所有行，只返回prod_price值为3.49的行。这个示例使用了简单的相等检验：检察某一列的值是否为指定值，
根据此过滤数据。

注意：1、显示结果在表示数值时小数点后可能会有不同位数的0，例如3.490、3，4900、3.49等，这是因为DBMS指定了所使用的数据类型及其默认行为。所以其小数末尾后几位零数值结果是一样的。
2、SQL过滤与应用过滤： 数据可以在应用层过滤。SQL的SELECT语句为客户端应用检索出超过实际所需的数据，然后客户端代码对返回数据进行循环，提取出需要的行。上述做法通常非常不妥。优化数据库后可以更快速有效的对数据进行过滤。让客户端应用（或开发语言）处理数据库的工作将会极大的影响应用的性能，并且时所创建的应用完全不具备可伸缩性。如果在客户端过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。
3、在同时使用ORDER BY 和WHERE子句时，应该让ORDER BY子句位于WHERE子句之后，否则将会产生错误。

---

4.2 WHERE 子句操作符

注意：上述表中所列出操作符是冗余的，例如<>与!=相同都是不等于，!<相当于>=等逻辑意义相等。而且不是所有DBMS均支持所有上述操作符，具体以使用的DBMS使用文档查看支持哪些操作符。

---

4.2.1 检查单个值

检索条件是一列的数值符合指定搜索条件，例如价格小于9.49美元或者价格小于等于9.49美元等等。(为了更好看此例用了升序排序)：

---

4.2.2 不匹配检查

不匹配检查使用不等于操作符，不等于操作符有两种，一种是！= 一种是<>，我们不同情况下该用哪种呢？下面例子来分析：


由上述两个例子可以看出！=和<>是可以通用的，都是不等于操作符。但是并非所有的DBMS都支持这两种不等于操作符，如果有提示不支持其中一种操作符报错时可以试试另外一种不等于操作符。
注意：上述两个例子操作符后面的值在单引号内，而有的值没有，因为单引号是来限定字符串为一个整体供服务器识别。用数值列进行比较不需要单引号，用字符串类型列则需要单引号。

4.2.3 范围值检查

范围值检查使用BETWEEN操作符。其与WHERE子句的操作符不同，BETWEEN需要两个值，即范围开始值和结束值。下面例子为检索间隔在4.99美元和9.49美元之间的所有产品。

由上图可知BETWEEN操作符的筛选包含开始值和结束值的条件。

---

4.2.4 空值检查

在创建表时，表设计人员可以指定其中的列能否不包含值。在一个列不包含值时，称其包含空值NULL（无值（no value），它与字段包含0、空字符或仅仅包含空格不同）。确定值是否为NULL，不能简单地检查是否等于NULL。SELECE语句有一个特殊的WHERE子句，可以用来检查具有NULL的值，这个WHERE子句就是IS NULL子句。下面例子为Customers表中检索没有电子邮件地址的数据：

下面是检索电子邮件地址不是NUll的数据，用到的操作符时IS NOT NULL：

---

4.3 此章小节

知识点总结：
1、此章学习了通过WHERE子句过滤返回的数据。
2、WHERE子句中操作符的运用，如大于、小于、不等于、在什么范围内、是否为空值等操作符。

---

4.4 章节练习题

1、编写SQL语句。从Products表中检索产品ID（prod_id）和产品名称（prod_name），只返回价格为9.49美元的产品。

---

2、编写SQL语句，从Products表中检索产品ID（prod_id）和产品名称（prod_name），只返回价格为9.49美元或更高的产品。

---

3、结合第三课和第四课编写SQL语句，从OrderItems表中检索出所有不同订单号（order_num），其中包含100个或更多的产品。

---

4、编写SQL语句，返回Products表中所有价格在3美元到6美元之间的产品名称（prod_name）和价格（prod_price），然后按价格对结果进行排序。（本题有多种解决方案，此处仅使用前四章节学习过的知识）

---

五、高级数据过滤

此节主要讲如何组合WHERE子句以建立功能更强、更高级的搜索条件。

---

5.1 组合WHERE子句

第四章介绍的所有WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND子句或者OR子句的方式使用。（操作符（operator）：用来联结或改变WHERE子句中的子句的关键字，也成为逻辑操作符（logical operator））。

---

5.1.1 AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。下面的代码给出了一个例子：

AND关键字用在WHERE子句中，用来指示检索满足所有给定条件的行。这条例子只包含一个AND子句，因此只有两个过滤条件。可以增加多个过滤条件，每个条
件间都用AND关键字连接。

5.1.2 OR操作符

OR操作符与AND操作符正好相反，它指示DBMS检索匹配任一条件的行。事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。例如下：

OR是WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。

---

5.1.3 求值顺序

WHERE子句可以包含任意数目的AND和OR操作符。允许两者结合以进行复杂、高级的过滤。但是组合AND和OR会带来一个问题，那就是求值的顺序。SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。如下例要列出价格在10美元以上，且由DLL01和BRS01制造的所有产品。

上述返回的结果行中4行价格小于10美元，返回结果未达到预期。因为AND操作符的优先处理使得服务器理解为BRS01 生产的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，而不管其价格是多少了。AND在求值过程中优先级更高，操作符被错误组合了。这种问题的解决办法是使用圆括号对操作符进行明确分组，如下图：

在WHERE子句中使用圆括号：在任何时候使用具有AND和OR操作符的WHERE子句时，都应该使用圆括号明确的分组操作符。不要过分依赖默认求值顺序。

---

5.2 IN操作符

IN操作符是用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。下例子为用SELECT语句检索由供应商DLL01 和BRS01制造的所有产品。：

IN操作符完成了与OR相同的功能。

下面的SQL语句完成于上面例子相同的工作：

IN时WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。
为什么要用IN操作符？其优点有哪些？：
1、在很多合法选项时，IN操作符的语法更清楚和直观。
2、在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
3、IN操作符一般比一组OR操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）
4、IN的最大优点是可以包含其他SELECT语句，能够更动态的建立WHERE子句。（第11章会对此详细介绍）

---

5.3 NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为NOT从不单独使用（它总和其他操作符一起使用），所以它的语法与其他操作符有所不同。NOT关键字可以用在要过滤的列前。而不仅是在其后。下例子为了列出除了DLL01之外的所有供应商制造的产品：

上例DBMS不是匹配vend_id为DLL01，而是匹配非DLL01之外的所有东西。上述例子同样也可以使用<>操作符来完成，如下：

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

在MariaDB中的NOT 支持否定IN、BETWEEN和WXISTS子句。大多数DBMS允许使用NOT否定任何条件。

---

5.4 此章小结

知识点汇总：
1、AND操作符表示筛选同时满足两边条件的数据。
2、OR操作符表示筛选出满足任一条件的数据。
3、AND和OR联结使用下要使用（）来保障求值顺序。
4、IN操作符和OR作用相同，但IN的优势更明显，列有四条：直观，求值顺序显然、执行快、可以包含SELECT语句
5、NOT操作符可以否定任何条件，可与IN、BETWEEN、WXISTS联结使用。

---

5.5 章节练习题

1、编写SQL语句，从Vendors表中检索供应商名称（vend_name），仅返回加利福尼亚州供应商（这需要按国家【USA】和州【CA】进行过滤，没准其他国家也存在一个加利福尼亚州）。提示：过滤器需要匹配字符串。

---

2、编写SQL语句，查找所有至少订购了总量100个的BR01、BR02或BR03的订单。你需要返回OrderItems表中的订单号（order_num）、产品ID（prod_id）和数量，并按产品ID和数量进行过滤。提示：根据编写过滤器的方式，可能需要特别注意求值顺序。

---

3、现在，我们回顾上一课的挑战题。编写SQL语句，返回所有价格在3美元到6美元之间的产品的名称（prod_name）和价格（prod_price）。使用AND,然后按价格对结果进行排序。

4、下面SQL语句有问题吗？（尝试在不运行的情况下指出）

SELECT vend_name
FROM Vendors
ORDER BY vend_name
WHERE vend_country = 'USA' AND vend_state = 'CA';

问题1：ORDER  BY要在SELECT语句最后

---