《SQL必知必会第五版》自留笔记

不蒸馒头

已于 2024-03-27 17:20:49 修改

阅读量687

点赞数 20

分类专栏： SQL 文章标签： sql 笔记数据库

于 2024-03-27 17:17:35 首次发布

本文链接：https://blog.csdn.net/2301_80435663/article/details/136644761

版权

SQL 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

花了几天啃完了《SQL必知必会》，打算写篇文章整理下笔记留个档。本文是对《SQL必知必会》的复习，主要归纳相关知识点，同时参考菜鸟教程来总结SQL相关学习内容，方便后续查阅。《SQL必知必会第五版》pdf可私信，课后挑战题见本专栏
注：本书介绍的SQL主要适用于以下系统：IBM DB2,Microsoft SQL Server,MariaDB,MySQL,
Oracle,PostgreSQL,SQLite.

一、了解SQL

数据库(database):以某种有组织的方式存储的数据集合。
表的一些特性定义了数据在表中如何存储，包括存储什么样的数据，数据如何分解，各部分信息如何命名等信息。
模式（schema):关于数据库和表的布局及特性的信息，模式可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系）。
列（column)：表中的一个字段。表由列组成，列存储表中某部分的信息。
数据类型（datatype):定了可存储在列中的数据种类，数据库中每个列都有相应的数据类型。
行（row）：表中的一个记录。
主键(primary key):一列（或几列），其值能够唯一标识表中每一行。
表中的任何列都可以作为主键，只要它满足以下条件：
 任意两行都不具有相同的主键值；
 每一行都必须具有一个主键值（主键列不允许空值 NULL）；
 主键列中的值不允许修改或更新；
 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。
SQL（Structured Query Language）是一种专门用来与数据库沟通的语言。
表（table）：一种结构化的文件，可用来存储某种特定类型的数据，存储在表中的数据是同一种类型的数据或清单。数据库中的每个表都有唯一的一个名字来标识自己。
关键字（keyword）:作为 SQL 组成部分的保留字,关键字不能用作表或列的名字。每个 SQL 语句都是由一个或多个关键字构成的。
子句（clause):一个子句通常由一个关键字加上所提供的数据组成。例如 SELECT 语句的 FROM 子句。
NULL：无值（no value），它与字段包含 0、空字符串或仅仅包含空格不同。
操作符（operator）：用来联结或改变 WHERE 子句中的子句的关键字，也称为逻辑操作(logical operator）。
AND:逻辑与。用在 WHERE 子句中的关键字，用来指示检索满足所有给定条件的行。
OR:逻辑或。WHERE 子句中使用的关键字，用来表示检索匹配任一给定条件的行。
IN：WHERE 子句中用来指定要匹配值的清单的关键字，功能与 OR 相当。
NOT：逻辑非。WHERE 子句中用来否定其后条件的关键字。
通配符（wildcard):用来匹配值的一部分的特殊字符。通配符搜索只能用于文本字段
搜索模式（search pattern):由字面值、通配符或两者组合构成的搜索条件。
字段（field）：基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而字段这个术语通常在计算字段这种场合下使用。
拼接（concatenate）：将值联结到一起（将一个值附加到另一个值）构成单个值。
别名（alias）：一个字段或值的替换名。别名用 AS 关键字赋予。有时也被称为导出列（derived column）。注:在指定别名以包含某个聚类函数的结果时，不应该使用表中实际的列名。
可移植（portable）：所编写的代码可以在多个系统上运行。SQL 函数不是可移植的。
聚集函数（aggregate function)：对某些行运行的函数，计算并返回一个值。
查询（query)：任何 SQL 语句都是查询。但此术语一般指 SELECT 语句。
子查询（subquery）：嵌套在其他查询中的查询。
可伸缩（scale)：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好（scale well）。
笛卡儿积（cartesian product） ：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
叉联结（cross join）：返回笛卡儿积的联结。

二、检索数据

本节内容：使用SELECT语句来检索单个列、多个表列以及所有表列，如何返回不同的值，如何注释代码。

1.检索列

--SQL语句结尾以；结束
/*SELECT语句检索单个指定表列
所需的列名写在SELECT关键字后，FROM关键字指出从哪个表中检索数据*/
SELECT prod_name
FROM Products;
--SELECT语句检索多个列，列名之间以逗号分隔
SELECT prod_id,prod_name,prod_price
FROM Products;
--SELECT语句检索所有列，使用通配符（*）
SELECT *
FROM Products;

2.检索不同的值

/*使用DISTINCT关键字去重，指示数据库返回不同值*/
SELECT DISTINCT vend_id
FROM Products;

DISTINCT关键字用于返回唯一不同的值，必须放在第一列名的前面
DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列

3.限制结果

不同DBMS实现返回指定行或一定数量行的方法不一致，如下：

--MYSQL,MariaDB,PostgreSQL，SQLite使用LIMIT子句
SELECT prod_name
FROM Products
LIMIT 5;
--SQL Server 用TOP关键字限制最多返回多少行
SELECT TOP 5 prod_name
FROM Products;
--Oracle基于ROWNUM（行计数器）来计算行
SELECT prod_name
FROM Products
WHERE ROWNUM <=5;

MYSQL,MariaDB,PostgreSQL，SQLite使用LIMIT子句限制输出结果：

--指示DBMS返回从第5行起的4行数据
SELECT prod_name
FROM Products
LIMIT 4 OFFSET 5;

/*由于第一个被检索的行是第0行，而不是第1行，
因此LIMIT 1 OFFSET 1会检索第2行，而不是第1行*/

LIMIT m OFFSET n; ---> offset指从哪儿开始，LIMIT是检索行数。从第n+1行开始，取m行
LIMIT n,m; ---> 从第n+1行开始，取m行

4.注释

SELECT prod_name --行内注释，两个连字符之后的文本就是注释
FROM Products;
#整行作为注释
SELECT prod_name
FROM Products;
/*多行注释
SELECT prod_name,vned_id
FROM Products;*/
SELECT prod_name
FROM Products;

三、排序检索数据

本节内容：用 SELECT 语句的 ORDER BY 子句对检索出的数据进行排序。这个子句必须是 SELECT 语句中的最后一条子句。根据需要，可以利用它在一个或多个列上对数据进行排序。

1.排序数据

ORDER BY关键字用于对结果集按照一个列或者多个列进行排序，默认按升序对记录排序。
在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。

/*按单列排序*/
SELECT prod_name
FROM Products
ORDER BY prod_name;
/*按多个列排序：指定列名之间用逗号分开，排序的顺序完全按规定进行*/
SELECT prod_id,prod_name,prod_price
FROM Products
ORDER BY prod_price,prod_name;--检索三列，并按其中两列对结果排序，先按价格再按名称排序
/*用列名指出排序顺序外，ORDER BY还支持按相对位置进行排序*/
SELECT prod_id,prod_name,prod_price
FROM Products
ORDER BY 2,3;

2.指定排序方向

ORDER BY 子句默认升序排序（ASC或ASCENDING），降序排列指定DESC关键字(或DESCENDING)。
DESC关键字只应用到直接位于其前面的列名。若想在多个列上进行降序排序，必须对每一列指定DESC关键字。

/*单列降序排序*/
SELECT prod_id,prod_name,prod_price
FROM Products
ORDER BY prod_price DESC;
/*多列降序排序:先按照第一个列排序，如果第一个列相同，再按照第二个列排序*/
SELECT prod_id,prod_name,prod_price
FROM Products
ORDER BY prod_price DESC,prod_name;
--按照prod_price列降序，prod_name列默认升序排序。
SELECT prod_id,prod_name,prod_price
FROM Products
ORDER BY prod_price DESC,prod_name DESC;
--按照prod_price列降序，prod_name列降序排序。

四、过滤数据

本节内容：用 SELECT 语句的 WHERE 子句指定搜索内容，过滤返回的数据。如何检验相等、不相等、大于、小于、值的范围以及 NULL值等

1.WHERE子句操作符

在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件(filter condition)。
SQL使用单引号来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。
要检查某个范围的值用BETWEEN操作符，必须用 AND 关键字分隔。BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。
通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL 值的行。但是这做不到。因为 NULL 比较特殊，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。

操作符	说明	操作符	说明
=	等于	<>	不等于
!=	不等于	<	小于
<=	小于等于	>	大于
>=	大于等于	between	在指定的两个值之间
in	在某个集合内	exists	存在
is null	为空	is not null	不为空
!<	不小于	!>	不大于

--检查单个值-列出所有价格小于10美元的产品
SELECT prod_name,prod_price
FROM Products
WHERE prod_price <10;
--不匹配检查-列出所有供应商不是DLL01制造的产品
SELECT vend_id,prod_name
FROM Products
WHERE vend_id !='DLL01';
--范围值检查-检索价格在5美元和10美元之间的所有产品
SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
--空值检查-IS NULL子句检查具有NULL的列
SELECT cust_name
FROM Customers
WHERE cust_email IS NULL;

五、高级数据过滤

本节内容：用 AND 和 OR 操作符组合成 WHERE 子句以建立功能更强、更高级的搜索条件，如何明确地管理求值顺序，如何使用 IN 和 NOT 操作符

1.组合WHERE子句

WHERE 子句可以包含任意数目的AND 和OR操作符，允许两者结合进行复杂、高级的过滤。
任何时候使用具有 AND和 OR 操作符的 WHERE 子句，都应使用圆括号明确地分组操作符，避免逻辑关系不明确。
AND&OR运算符用于基于一个以上的条件对记录进行过滤。简单来说，如果第一个条件和第二个条件都成立，则AND运算符显示一条记录，如果第一个条件和第二个条件只要有一个成立，则OR运算符显示一条记录。
SQL在处理OR操作符前，优先处理AND操作符。
求值顺序：NOT、AND、OR。数值运算>逻辑运算。

（1）AND 操作符给 WHERE 子句附加条件，用来指示检索满足所有给定条件的行。

/*检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格
该句只有两个过滤条件，增加多个过滤条件需每个条件间使用AND关键字*/
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01'AND prod_price <= 4;

（2）OR操作符指示DBMS检索匹配任一条件的行而不是同时匹配两个条件。

--检索由任一个指定供应商制造的产品名和价格
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

（3）SELECT语句使用组合的AND和OR——明确求值顺序：用圆括号来组成复杂的表达式

解析：该返回行有4行价格小于10美元，原因在于求值的顺序，由于AND在求值过程中优先级更高，操作符被错误地组合了。SQL理解为：由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，不管其价格。

解决办法：使用圆括号对操作符进行明确分组

解析：圆括号具有比AND或OR操作符更高的优先级，DBMS首先过滤圆括号内的OR条件。SQL理解为：选择由供应商DLL01或BRS01制造的且价格在10美元及以上的所有产品

2.IN操作符

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

/*检索由供应商DLL01和BRS01制造的所有产品。*/
/*IN操作符跟由逗号分隔的合法值，这些值必须括在圆括号内*/
SELECT prod_price,prod_name
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;
/*OR操作符完成上述IN操作符相同功能*/
SELECT prod_price,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
ORDER BY prod_name;

注：IN操作符优点——1.在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理 2.IN操作符一般比OR操作符执行得更快 3.IN的最大优点是可以包含其他SELECT语句，能够更让动态地建立WHERE子句

3.NOT操作符

WHERE 子句中的NOT操作符有且只有一个功能——否定其后所跟的任何条件
NOT关键字可以用在要过滤的列前，而不仅是在其后。

/*列出除DLL01之外的所有供应商制造的产品,NOT否定跟在其后的条件
在与IN操作符联合使用时，NOT可以非常简单找出与条件列表不匹配的行*/
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

六、用通配符进行过滤

本节内容：什么是通配符，如何在WHERE子句中使用通配符，怎样使用LIKE操作符进行通配搜索，以便对数据进行复杂过滤

前面介绍的所有操作符都是针对已知值进行过滤的。利用通配符，可以创建比较特定数据的搜索模式。通配符实际上是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。
为在搜索子句中使用通配符，必须使用LIKE操作符，LIKE关键字用来进行模糊匹配。LIKE指示DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。
通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。
通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符
注：搜索可以是区分大小写的

通配符	说明	sql语句	阐述
%	任意多个字符	like 'A%'	以A开头
_	任意单个字符	like '_r%'	第二个字符是r
[charlist]	字符列中的任意单个字符	like '[AB]%'	以A或者B开头
[^charlist]	不在字符列中的任意单个字符	like '[^AB]%'	不以A或者B开头

1.百分号（%）通配符

搜索串中，%表示任何字符出现任意次数，% 代表搜索模式中给定位置的 0 个、1 个或多个字符。
通配符%看起来好像可以匹配任何东西，NULL例外，子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。

/*检索任意以Fish起头的产品，
'Fish%'告诉DBMS接受Fish之后的任意字符，不管它有多少字符*/
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
#通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符
/*'%bean bag%'表示匹配任何位置上包含文本bean bag的值，
不论它之前或之后出现什么字符*/
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';
/*检索以F起头、以y结尾的所有产品
通配符也可以出现在搜索模式的中间*/
SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

说明：请注意后面所跟的空格

有些 DBMS 用空格来填补字段的内容。例如，如果某列有 50 个字符，而存储的文本为 Fish bean bag toy（17 个字符），则为填满该列需要在文本后附加 33 个空格。这样做一般对数据及其使没有影响，但是可能对上述 SQL 语句有负面影响。子句 WHERE prod_name LIKE'F%y'只匹配以 F 开头、以 y 结尾的 prod_name。如果值后面跟空格，则不是以 y 结尾，所以 Fish bean bag toy 就不会检索出来。简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配 y 之后的字符（或空格）。更好的解决办法是用函数去掉空格。请参阅第八课。

2.下划线（_）通配符

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

--WHERE子句中的搜索模式给出后面跟有文本的两个通配符
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
--WHERE子句中的搜索模式给出后面跟有文本的一个通配符
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '_ inch teddy bear';

3.方括号（[ ]）通配符

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
此通配符可以用前缀字符^ （脱字号）来否定。

--找出所有名字以J或M起头的联系人
SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact;
/*此语句的 WHERE 子句中的模式为'[JM]%'。这一搜索模式使用了两个不同的通配符。
[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。
因此，任何多于一个字符的名字都不匹配。
[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。*/

--匹配以J 和 M 之外的任意字符起头的任意联系人名
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

4.使用通配符技巧

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

七、创建计算字段

本节内容：什么是计算字段，如何创建计算字段，举例说明计算字段在字符串拼接和算术计算中的用途。以及如何创建和使用别名，以便应用程序能引用计算字段。

1.计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，我们需要利用计算字段直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。
只有数据库知道 SELECT语句中哪些列是实际的表列,哪些列是计算字段。从客户端（如应用程序）来看，计算字段的数据与其他列的数据的返回方式相同。
计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。

2.拼接字段

在SQL 中的 SELECT 语句中，可使用加号（+）或两个竖杠（||）来拼接两个列。<不同的DBMS有差异，在 MySQL 和 MariaDB 中，必须使用特殊的函数。SQL Server 使用+号。DB2、Oracle、PostgreSQL 和 SQLite 使用||。>

--多数DBMS语法
SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
--MYSQL语句
SELECT Concat(vend_name, ' (', vend_country, ')') 
FROM Vendors
ORDER BY vend_name;
/*上面两个 SELECT 语句拼接以下元素：存储在 vend_name 列中的名字；
包含一个空格和一个左圆括号的字符串；存储在 vend_country 列中的国家；包含一个右圆括号的字符串。*/

由SELECT 语句返回的输出可看出结合成一个计算字段的两个列用空格填充。许多数据库(不是所有)保存填充为列宽的文本值，为正确返回格式化的数据，使用 SQL 的 RTRIM()函数来去掉这些空格。
RTRIM()（去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及 TRIM()（去掉字符串左右两边的空格)。

--RTRIM()函数去掉右边的所有空格
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

3.使用别名

SQL 支持列别名。别名（alias）是一个字段或值的替换名。别名用 AS 关键字赋予。

/*AS vend_title-创建一个包含指定计算结果的名为vend_title的计算字段*/
--多数DBMS语法
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' 
AS vend_title
FROM Vendors
ORDER BY vend_name;
--MYSQL语法
SELECT Concat(RTrim(vend_name), ' (',
 RTrim(vend_country), ')') AS vend_title
FROM Vendors
ORDER BY vend_name;

4.执行算术计算

/*汇总物品的价格（单价乘以订购数量）,输出的 expanded_price列是一个计算字段，客户端应用可使用该新计算列*/
SELECT prod_id,
       quantity,
	   item_price,
	   quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

SQL基本算术操作符如下，圆括号可用来区分优先顺序。

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

/*SELECT 语句为测试、检验函数和计算提供了很好的方法。虽然 SELECT通常用于从表中检索数据，
但是省略FROM 子句后就是简单地访问和处理表达式，例如 SELECT 3 * 2;将返回 6,
SELECT Curdate();使用 Curdate()函数返回当前日期和时间。*/

八、使用函数处理数据

本节内容：什么是函数，DBMS 支持何种函数，以及如何使用这些函数；还将讲解为什么SQL 函数的使用可能会带来问题。(虽然这些函数在格式化、处理和过滤数据中非常有用，但它们在各 SQL 实现中很不一致。）

3 个常用的函数及其在各个 DBMS 中的语法:

1.文本函数

——处理文本字符串（如删除或填充值，转换值为大写或小写）

函数	说明	函数	说明
left	返回字符串左边的字符	right	返回字符串右边的字符
lower	返回小写字符串	upper	返回大写字符串
ltrim	返回去除字符串左边的空格	rtrim	返回去除字符串右边的空格
length/len/datalength	返回字符串长度	mid	返回字符串中间的字符
soundex	返回字符串的SOUNDEX值	substr/substring	提取字符串的组成部

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

--UPPER()将文本转换成大写
SELECT vend_name,UPPER(vend_name) AS vend_name——upcase
FROM Vendors
ORDER BY vend_name;
/*Customers 表中有一个顾客Kids Place，其联系名为 Michelle Green。
但这是错误的输入，此联系名实际上应该是 Michael Green。
显然，按正确的联系名搜索不会返回数据，
使用SOUNDEX()搜索匹配所有发音类似于Michael Green的联系*/
SELECT cust_name,cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) =SOUNDEX('Michael Green');

2.日期和时间函数

——处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）

函数	说明	sql语句	阐述
datepart	返回日期的部分	datepart(‘'yyyy', '2020-01-01')	2020
curdate	返回当前日期	curdate()
curtime	返回当前时间	curtime()

/*SQL Server中检索2020年的所有订单，
DATEPART()函数返回日期的某一部分，两个参数分别是返回的成和从中返回成分的日期
Oracle，MySQL 和 MariaDB没有 DATEPART()函数，*/
SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2020;
--DB2，MySQL 和 MariaDB 可使用名为 YEAR()的函数从日期中提取年份
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2020;

注：日期和时间采用相应的数据类型存储在表中，每种 DBMS 都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

3.数值函数

——在数值数据上进行算术操作（如返回绝对值，进行代数运算）

函数	说明	函数	说明
abs	返回绝对值	cos	返回余弦值
exp	返回一个数的指数值	pi	返回圆周率π 的值
sin	返回正弦值	sqrt	返回平方根
tan	返回正切值	round	四舍五入

九、汇总数据

本节内容：SQL 的聚集函数用来汇总数据，可以用多种方法使用它们，返回所需的结果。

1.聚集函数

聚集函数（aggregate function)——对某些行运行的函数，计算并返回一个值。
AVG(),MAX(),MIN(),SUM()，COUNT(column)会忽略列的值为NULL的行，而count(*)对表中行的数目进行计数，不管表列中包含的是NULL还是非空值。
AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个 AVG()函数。
对非数值数据使用 MAX()——MAX()一般用来找出最大的数值或日期值，但许多（并非所有） DBMS 允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。
对非数值数据使用 MIN()——MIN()一般用来找出最小的数值或日期值，但许多（并非所有） DBMS 允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，MAX()返回按该列排序后最前面的行。

函数	说明	函数	说明
avg	返回某列的平均值	count	返回某列的行数
max	返回某列的最大值	min	返回某列的最小值
sum	返回某列值的总和	distinct	返回唯一值

/*AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值
分析：别名avg_price返回Products表中供应商为DLL01的产品的平均价格*/
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';
/*COUNT()函数进行计数，确定表中行的数目或符合特定条件的行的数目
分析：只对具有电子邮件地址的客户计数*/
SELECT COUNT(*) AS num_cust
FROM Customers;
/*MAX()返回指定列中的最大值，MAX()要求指定列名
分析：返回Products表中最贵物品的价格*/
SELECT MAX(prod_price) AS max_price
FROM Products;
/*MIN()返回指定列中的最大小值，MIN()要求指定列名
分析：返回Products表中最便宜物品的价格*/
SELECT MIN(prod_price) AS min_price
FROM Products;
/*SUM()返回指定列值的和(总计)
分析：返回订单中所有物品价钱之和*/
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

2.聚集不同值

上述五个聚集函数有如下操作：

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

如果指定列名，则 DISTINCT 只能用于 COUNT()。DISTINCT 不能用于 COUNT(*)。类似地，DISTINCT 必须使用列名，不能用于计算或表达式。

--AVG()返回Products表中供应商为DLL01的产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products 
WHERE vend_id = 'DLL01';
--平均值只考虑各个不同的价格
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

3.组合聚集函数

--SELECT语句可根据需要包含多个聚类函数
--单条SELECT语句执行4个聚类计算返回4个值
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;
--在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。

十、分组数据

本节内容：如何分组数据，以便汇总表内容的子集。这涉及两个新SELECT 语句子句：GROUP BY 子句和 HAVING 子句。以及ORDER BY和 GROUP BY 之间以及 WHERE 和 HAVING 之间的差异。

1.创建分组

分组是使用SELECT语句的GROUP BY子句建立的，GROUP BY子句可以包含任意数目的列。GROUP BY关键字用来对数据进行分组，HAVING关键字用来对分组后的数据进行筛选。
如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

--GROUP BY子句指示DBMS按vend_id排序并分组数据
--对每个组而不是整个结果集进行聚集
SELECT vend_id,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

2.过滤分组

WHERE 过滤行，而 HAVING 过滤分组。过滤是基于分组聚集值，而不是特定行的值。
另一种理解：WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。这是一个重要的区别，WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。
使用 HAVING 时应该结合 GROUP BY 子句，而 WHERE 子句用于标准的行级过滤。

--列出至少有两个订单的所有顾客
SELECT cust_id,COUNT(*)AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >=2;
/*列出具有两个以上产品且其价格大于等于4的供应商
WHERE子句过滤所有prod_price 至少为 4 的行，
然后按vend_id分组数据，HAVING子句过滤计数为 2 或 2 以上的分组*/
SELECT vend_id,COUNT(*)AS num_prods
FROM Products
WHERE prod_price >=4
GROUP BY vend_id
HAVING COUNT(*) >=2;

3.分组和排序

--检索包含三个或更多物品的订单号和订购物品的数目,并按订购物品的数目排序输出
SELECT order_num,COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*)>=3
ORDER BY items, order_num;
/*使用 GROUP BY 子句按订单号（order_num 列）分组数据，
以便 COUNT(*)函数能够返回每个订单中的物品数目。
HAVING 子句过滤数据，使得只返回包含三个或更多物品的订单。
最后，用 ORDER BY子句排序输出。*/

4.SELECT子句顺序

十一、使用子查询

本节内容：什么是子查询，如何使用它们。子查询常用于 WHERE 子句的 IN 操作符中，以及用来填充计算列。

1.利用子查询进行过滤

SQL 允许创建子查询（subquery），即嵌套在其他查询中的查询。在SELECT 语句中，子查询总是从内向外处理。子查询可以用来作为WHERE、FROM、SELECT、HAVING、IN、EXISTS等子句的一部分。
子查询格式：把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用。
作为子查询的SELECT语句只能查询单个列，企图检索多个列将返回错误。

/*检索出订购物品RGAN01的所有顾客分为以下三步：
检索包含物品RGAN01的所有订单的编号
检索具有前一步骤列出的订单编号的所有顾客的ID
检索前一步骤返回的所有顾客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'));

2.作为计算字段使用子查询

/*检索出Customers表中每个顾客的订单总数分为以下两步：
1.从Customers表中检索顾客列表
2.对于检索出的每个顾客，统计其在Orders表中的订单数目
注：orders是一个计算字段，它由圆括号中的子查询建立的，
该子查询对检索出的每个顾客执行一次*/
SELECT cust_name,
			 cust_state,
			 (SELECT COUNT(*)
			  FROM Orders
			  WHERE Orders.cust_id = Customers.cust_id) AS orders
--完全限定列名：用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法。
FROM Customers
ORDER BY cust_name;

十二、联结表

本节内容：什么是联结，为什么使用联结，如何编写使用联结的SELECT 语句。

1.联结

关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）
联结是一种机制，用来在一条SELECT语句中关联表，因此成为联结。联结表就是将两个表中的数据进行合并。联结表的类型有很多，比如内联结、外联结、自联结等。

2.创建联结

创建联结非常简单，指定要联结的所有表以及关联它们的方式即可。
要保证所有联结都有WHERE子句
WHERE子句建立联结条件——WHERE子句作为过滤条件，只包含那些匹配给定条件（联结条件）的行。应该总提供联结条件，否则将出现笛卡尔积。

--WHERE子句联结Vendors和Products，使用完全限定列名
SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Products.vend_id;
--笛卡尔积
SELECT vend_name,prod_name,prod_price
FROM Vendors,Products;

内联结（inner join）：即等值联结（equijoin），它基于两个表之间的相等测试。

/*同上例，WHERE使用简单的等值语法
只是两个表之间的关系是以INNER JOIN指定的部分FROM子句，
联结条件用特定的ON子句给出*/
SELECT vend_name,prod_name,prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

联结多个表：SQL不限制一条SELECT语句中可以联结的表的数目。

/*显示订单20007中的物品，WHERE子句定义这两个联结条件，
第三个联结条件用来过滤出订单20007中的物品*/
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
--子查询：返回订购产品RGAN01的顾客列表
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'));
--使用联结的相同查询-三个WHERE子句
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

十三、创建高级联结

本节内容：如何以及为什么使用别名，然后讨论不同的联结类型以及每类联结所使用的语法。之后介绍如何对被联结的表使用聚集函数，以及在使用联结时应该注意的问题。

1.使用表别名

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。
表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。
注:ORACLE不支持AS关键字，要在oracle中使用别名，简单指定列名即可

--对计算字段使用别名
SELECT RTRIM(vend_name) + '(' +RTRIM(vend_country) + ')'
	   AS vend_title
FROM Vendors
ORDER BY vend_name;
--给表名起别名,此例中表别名只用于WHERE子句，还可用于SELECT的列表、ORDER BY子句及其他语句部分
SELECT cust_name,cust_contact
FROM Customers AS C,Orders AS O,OrderItems AS OI
WHERE C.cust_id = o.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

2.使用不同类型的联结

2.1 自联结(self-join）
（1）自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。

/*使用子查询检索出Jim Jones同一公司的所有顾客，
先找出Jim Jones工作的公司，再找出在该公司工作的顾客*/
SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name in (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');
/*使用自联结检索出Jim Jones同一公司的所有顾客，
对Customers的引用具有歧义，故使用表别名*/
SELECT c1.cust_id,c1.cust_name,c1.cust_contact	
FROM Customers AS c1,Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

2.2 自然联结(natural join）
（1）对表进行联结时，至少有一列不止出现在一个表中（被联结的列）。内联结返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。
（2）自然联结要求只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。

SELECT C.*,O.order_num,O.order_date,
       OI.prod_id,OI.quantity,OI.item_price
FROM Customers AS C,Orders AS O,
		 OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id ='RGAN01';

2.3 外联结(outer join）

（1）外联结——联结包含了那些在相关表中没有关联行的行。
（2） 使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指的是 OUTER JOIN 右边的表， LEFT 指的是 OUTER JOIN左边表）
（3）总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联表的顺序。换句话说，调整 FROM 或 WHERE子句中表的顺序，左外联结可转换为右外联结。
（4）全外联结（full outer join）——检索两个表中所有行并关联那些可以关联的行。
（5）与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。
（6）MariaDB、MySQL 和 SQLite 不支持 FULL OUTER JOIN 语法。

/*对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
使用关键字OUTER JOIN来指定联结类型
与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行*/
SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_，id;
--全外联结
SELECT Customers.cust_id, Orders.order_num
FROM Customers
FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

3.使用带聚类函数的联结

/*检索所有顾客及每个顾客所下的订单数
使用INNER JOIN 将Customers和Orders表互相关联，
函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord返回*/
SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
--使用左外部联结来包含所有顾客，甚至那些没有任何订单的顾客
SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

联结类型	说明
自联结	表与自己联结
left outer join	左联结
right outer join	右联结
full outer join	全联结
cross join	笛卡尔积

十四、组合查询

本节内容：如何用 UNION 操作符来组合 SELECT 语句。利用 UNION，可以把多条查询的结果作为一条组合查询返回，不管结果中有无重复。从而极大地简化复杂的 WHERE 子句，简化从多个表中检索数据的工作。

并（union）或复合查询（compound query）：多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句。但是，SQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果集返回。
组合查询的类型有很多：比如UNION、UNION ALL、INTERSECT、EXCEPT等等。

利用 UNION ，可给出多条SELECT 语句，将它们的结果组合成一个结果集。
UNION规则：（1）UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字UNION 分隔（2）UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同次序列出）（3）列数据类型必须兼容。

若结合 UNION 使用的 SELECT 语句遇到不同的列名，查询结果会返回第一个名字，由于只返回第一个名字，那么想要排序也只能用这个名字。
UNION 从查询结果集中自动去除了重复的行；若想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。
SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。

--使用UNION操作符来组合数条SQL查询，UNION几乎总是完成与多个WHERE条件相同的工作。
SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name,cust_contact;

组合查询类型	说明
union	合并两个查询的结果，去重
union all	合并两个查询的结果，不去重
intersect	返回两个查询的交集
except/minus	返回两个查询的差集

十五、插入数据

本节内容：介绍如何将行插入到数据库表中。学习 INSERT 的几种方法，为什么要明确使用列名，如何用 INSERT SELECT 从其他表中导入行，如何用 SELECT INTO 将行导出到一个新表。

INSERT 用来将行插入（或添加）到数据库表。
INSERT 通常只插入一行。要插入多行，必须执行多个 INSERT 语句。INSERT SELECT是个例外，它可用一条INSERT插入多行，不管SELECT语句返回多少行，都将被 INSERT 插入。
如果不指定列名，那么就是插入所有列的值，按照列的顺序。如果不指定值，那么就是插入NULL值。如果不能为NULL，就会报错。有默认值的话，就插入默认值。自增的话，就插入自增的值。

INSERT插入有几种方式：
(1)插入完整的行；
(2)插入行的一部分；
(3)插入某些查询的结果。

/*(1)插入完整的行：
存储到表中每一列的数据在 VALUES 子句中给出，必须给每一列提供一个值。
各列必须以它们在表定义中出现的次序填充。*/
INSERT INTO Customers
VALUES(1000000006, 
 'Toy Land',
 '123 Any Street',
 'New York',
 'NY',
 '11111',
 'USA',
 NULL,
 NULL);
/*同上句INSERT的工作，但表后的括号给出列名，VALUES 必须以其指定的次序匹配指定的列名，
不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条INSERT语句仍然能正确工作*/
 INSERT INTO Customers(cust_id,
 cust_name,
 cust_address,
 cust_city,
 cust_state,
 cust_zip,
 cust_country,
 cust_contact,
 cust_email)
VALUES(1000000006,
 'Toy Land',
 '123 Any Street',
 'New York',
 'NY',
 '11111',
 'USA',
 NULL,
 NULL);

/*(2)插入部分行：使用 INSERT 的推荐方法是明确给出表的列名。使用这种语
法，还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。*/
/*省略的列必须满足以下某个条件:
1.该列定义为允许 NULL 值（无值或空值）。
2.在表定义中给出默认值。这表示如果不给出值，将使用默认值。*/
INSERT INTO Customers(cust_id,
 cust_name,
 cust_address,
 cust_city,
 cust_state,
 cust_zip,
 cust_country)
VALUES(1000000006,
 'Toy Land',
 '123 Any Street',
 'New York',
 'NY',
 '11111',
 'USA');

/*(3)插入检索出的数据：利用INSERT将 SELECT 语句的结果插入表中。*/
--把另一表中的顾客列合并到Customers表中，不需要每次读取一行再插入
INSERT INTO Customers(cust_id,
 cust_contact,
 cust_email,
 cust_name,
 cust_address,
 cust_city,
 cust_state,
 cust_zip,
 cust_country)
SELECT cust_id,
 cust_contact,
 cust_email,
 cust_name,
 cust_address,
 cust_city,
 cust_state,
 cust_zip,
 cust_country
FROM CustNew;

CREATE SELECT将一个表的内容复制到一个全新的表（运行中创建的表）

/*创建名为CustCopy的新表，并把Customers表的整个内容复制到新表中
要想只复制部分的列，可以明确给出列名，而不是使用*通配符。*/
CREATE TABLE CustCopy AS
SELECT * 
FROM customers;

十六、更新和删除数据

本节内容：如何使用 UPDATE 和 DELETE 语句处理表中的数据。以及为保证数据安全而应该遵循的一些指导原则。

1.更新数据

更新（修改）表中的数据，可以使用 UPDATE 语句。有两种使用 UPDATE 的方式：（1）更新表中的特定行；(2)更新表中的所有行。

UPDATE语句用来更新数据，SET关键字用来指定列名及更新的值，
WHERE关键字确定要更新哪些行的过滤条件。
==不要忘记where关键字，否则会更新所有的数据。

--更新客户1000000005 的多个列，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）
UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = 1000000006;

2.删除数据

从一个表中删除（去掉）数据，使用 DELETE 语句。有两种使用 DELETE 的方式:(1)从表中删除特定的行；(2)从表中删除所有行。
如果想从表中删除所有行，不要使用 DELETE。可使用 TRUNCATE TABLE语句,比delete快

十七、创建和操纵表

本节内容：讲授创建、更改和删除表的基本知识。CREATE TABLE 用来创建新表，ALTER TABLE 用来更改表列（或其他诸如约束或索引等对象），而 DROP TABLE 用来完整地删除一个表。这些语句必须小心使用，并且应该在备份后使用。

1.创建表

#理解NULL：
不要把 NULL 值与空字符串相混淆。NULL 值是没有值，不是空字符串。
如果指定''（两个单引号，其间没有字符），这在 NOT NULL 列中是允许的。
空字符串是一个有效的值，它不是无值。NULL 值用关键字 NULL而不是空字符串指定。
#指定默认值：
SQL 允许指定默认值，在插入行时如果不给出值，DBMS 将自动采用默认值。
默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。
默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。

--每个表列要么是 NULL列(没有值或缺值)，要么是 NOT NULL列，这种状态在创建时由表的定义规定。
--语法
create table table_name
(
    column_name1 data_type [not null] [default value],
    column_name2 data_type [not null] [default value],
    ...
    column_name3 data_type [not null] [default value]
    primary key (column_name1)
);
--例：创建 OrderItems 表，包含构成订单的各项
CREATE TABLE OrderItems
( 
 order_num INTEGER NOT NULL,
 order_item INTEGER NOT NULL,
 prod_id CHAR(10) NOT NULL,
 quantity INTEGER NOT NULL DEFAULT 1,
 item_price DECIMAL(8,2) NOT NULL
);

数据类型	说明
int	整数
decimal	小数
char	定长字符串
varchar	变长字符串
date	日期
time	时间

约束	说明	使用
not null	非空	column_name data_type not null
default value	默认值	salary decimal(10, 2) default 0.00
primary key	主键	primary key (column_name1)
foreign key	外键	foreign key (column_name1) references table_name(column_name2)
unique	唯一	unique (column_name1)
check	检查	check (sex in ('M', 'F'))
auto_increment	自增	id int auto_increment

2.更新表

alter table table_name
add column_name data_type [not null] [default value];
-- 添加列

alter table table_name
drop column column_name;
-- 删除列

alter table table_name
modify column_name data_type [not null] [default value];
-- 修改列

3.删除表

drop table table_name;
-- 删除表
-- 删除表没有确认步骤也不能撤销，执行这条语句将永久删除该表。

4.重命名表

每个 DBMS 对表重命名的支持有所不同。对于这个操作，不存在严格的标准。DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 用户使用 RENAME语句，SQL Server 用户使用 sp_rename 存储过程，SQLite 用户使用 ALTER TABLE 语句。

alter table table_name
rename to new_table_name; 
-- 重命名表

十八、使用视图

本节内容：什么是视图，它们怎样工作，何时使用它们；如何利用视图简化前几课中执行的某些 SQL 操作。

1.视图

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。
视图的规则和限制：（其他规则查看具体DBMS文档）
与表一样，视图必须唯一命名；对于可以创建的视图数目没有限制；创建视图，必须具有足够的访问权限；视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。

2.创建视图

视图用 CREATE VIEW 语句来创建，CREATE VIEW只能用于创建不存在的视图

（1）利用视图简化复杂的联结

/*创建一个名为 ProductCustomers 的视图，它联结三个表，返
回已订购了任意产品的所有顾客的列表。*/
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

--检索订购了产品RGAN01的顾客,通过WHERE子句从视图中检索特定数据
SELECT cust_name,cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

--注：若想创建可重用的视图，初始注意创建不绑定数据的视图

（2）利用视图重新格式化检索出的数据

/*假设经常需要拼接格式的结果。不必在每次需要时执行这种拼接，而是创建一个视图，使用它即可。*/
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
 AS vend_title
FROM Vendors;
--检索所有供应商名和位置
SELECT * FROM VendorLocations;

（3）利用视图过滤不想要的数据

--定义 CustomerEMailList 视图，过滤没有电子邮件地址的顾客。
CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

--使用视图CustomerEMailLis
SELECT *
FROM CustomerEMailList;

（4）使用视图与计算字段

--检索订单中的物品
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
 prod_id,
 quantity,
 item_price,
 quantity*item_price AS expanded_price
FROM OrderItems;
--检索订单20008的详细内容
SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

十九、使用存储过程

本节内容：什么是存储过程，为什么使用存储过程，如何使用存储过程，以及创建和使用存储过程的基本语法。

1.执行存储过程

存储过程就是一组预先编译好的SQL语句。存储过程可以简化复杂的操作，可以提高安全性，可以提高性能。可以理解为一种批处理，其他编程语言中的函数。为什么使用存储过程？存储过程可以简化复杂的操作，可以提高安全性，可以提高性能。封装、复用、安全、性能。

--EXECUTE接受存储过程名和需要传递给它的任何参数
--执行名为AddNewProduct 这个存储过程
EXECUTE AddNewProduct('JTS01', 
 'Stuffed Eiffel Tower', 
 6.49,
 'Plush stuffed toy with 
the text La Tour Eiffel in red white and blue');

2.创建存储过程

-- 创建存储过程
create procedure mysum (in a int, in b int, out c int)
begin
    set c = a * b + a / b;
end;
-- 使用存储过程
call mysum(1, 2, @c);
select @c;

create procedure关键字用来创建存储过程。
in表示输入参数，out表示输出参数。mysum是存储过程的名字，a、b、c是参数的名字。
begin和end之间是存储过程的内容。
call关键字用来调用存储过程。@c用于接收存储过程的返回值。
定义变量
- declare关键字用来定义变量。
- declare var_name data_type [default value]。
- var_name是变量的名字，data_type是变量的类型，default value是变量的默认值。
- 如declare a int default 0。
变量赋值
- set关键字用来给变量赋值。
- set var_name = value。
- 如set a = 1
条件判断
- if关键字用来进行条件判断。
- if condition then statement1 else statement2 end if。
- 如if a > b then set c = a; else set c = b; end if。

case关键字用来进行多条件判断

case case_value when value1 then statement1 when value2 then statement2 else statement3 end case。

declare grade char(1);
case
when score >= 90 then set grade = 'A';
when score >= 80 then set grade = 'B';
when score >= 70 then set grade = 'C';
else set grade = 'D';
end case;

while循环
- while关键字用来进行循环。
- while condition do statement end while。
```
declare i int default 0;
while i < 10 do
set i = i + 1;
end while;
```

/*复杂存储过程实例*/
create procedure isprime (in n int, out result int)
begin
    declare i int default 2;
    declare is_prime int default 1;
    while i < n do
        if n % i = 0 then
            set is_prime = 0;
        end if;
        set i = i + 1;
    end while;
    set result = is_prime;
end;

SHOW PROCEDURE STATUS; -- 查看存储过程
SHOW CREATE PROCEDURE isprime; -- 查看存储过程的创建语句（源码）
SHOW PROCEDURE STATUS WHERE db = 'test'; -- 查看指定数据库的存储过程
SHOW PROCEDURE STATUS LIKE 'isprime'; -- 查看指定存储过程

二十、管理事务处理

本节内容：什么是事务处理，如何使用 COMMIT 和 ROLLBACK 语句对何时写数据、何时撤销进行明确的管理；如何使用保留点，更好地控制回退操作。

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

关于事务处理的术语：

事务（transaction ）指一组 SQL 语句；

回退（rollback ）指撤销指定 SQL 语句的过程；

提交（commit ）指将未存储的 SQL 语句结果写入数据库表；

保留点（savepoint ）指事务处理中设置的临时占位符（ placeholder），可以对它发布回退 (与回退整个事务处理不同）。

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT语句、CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。
事务特性ACID：原子性、一致性、隔离性、持久性

/*管理事务的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
通常，COMMIT 用于保存更改，ROLLBACK 用于撤销。*/
--MariaDB和MYSQL
START TRANSACTION
...
--SQL Server
BEGIN TRANSACTION
...
COMMIT TRANSACTION
--Oracle
SET TRANSACTION
...

术语	说明
transaction	事务，一组sql语句
commit	提交事务，将未存储的SQL语句结果写入数据库
rollback	回滚事务，撤销指定的SQL语句
savepoint	保存点，事务中的一个标记，可以回滚到这个标记

用一个例子来说明事务处理。
A账户有1000元，B账户有500元。发生两个事件，
1、A账户要给B账户转200元。A账户减少200元，B账户增加200元。成功，提交事务。
2、A账户要给B账户转300元。A账户减少300元，B账户增加300元。失败，需要回归到第一个事件。提交事务。

create table account
(
    id int primary key auto_increment,
    name varchar(20),
    money decimal(10, 2)
);
insert into account (name, money) values ('A', 1000), ('B', 500);
select * from account;

-- 1、A账户要给B账户转200元
start transaction; -- 开启事务
update account set money = money - 200 where name = 'A'; -- A账户减少200元
update account set money = money + 200 where name = 'B'; -- B账户增加200元
commit; -- 提交事务
select * from account;

-- 2、A账户要给B账户转300元
start transaction; -- 开启事务
savepoint before_change; -- 保存点
update account set money = money - 300 where name = 'A'; -- A账户减少300元
update account set money = money + 300 where name = 'B'; -- B账户增加300元

-- 由于某种原因，比如此时B账户已经卷钱跑路销卡了，所以需要回滚到第一个事件
rollback to before_change; -- 回滚到保存点
select * from account;
commit; -- 提交事务

start transaction关键字用来开启事务， commit关键字用来提交事务， rollback关键字用来回滚事务， savepoint关键字用来保存点。
在事务处理中要经常使用条件判断和循环，设定保存点，设定什么提交下回滚到哪个保存点，这样可以保证事务的完整性和一致性。
保存点是事务中的一个标记，可以回滚到这个标记。

二十一、使用游标

本节内容：什么是游标，如何使用游标。

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

/*创建一个游标来检索没有电子邮件地址的所有顾客，
作为应用程序的组成部分，帮助操作人员找出空缺的电子邮件地址*/
/*DECLARE 语句用来定义和命名游标，这里为CustCursor。
SELECT 语句定义一个包含没有电子邮件地址（NULL值）的所有顾客的游标*/
--DB2、MariaDB、MySQL和SQL Server 版本
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;
--Oracle和PostgreSQL 版本
DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL;

/*使用游标*/

--执行查询，存储检索出的数据以供浏览和滚动
OPEN CURSOR CustCursor
--FETCH语句访问游标数据，指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。
DECLARE TYPE CustCursor IS REF CURSOR
 RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
 OPEN CustCursor; 
 FETCH CustCursor INTO CustRecord;
 CLOSE CustCursor;
END;

--游标在使用完毕时需要关闭，不同DBMS语法不同，不过多阐述，请参阅具体文档
CLOSE CustCursor

二十二、高级SQL特性

1.约束

约束	说明	SQL语句	阐述
主键约束	唯一标识	primary key (column_name)
外键约束	表与表之间的关联	foreign key (column_name) references table_name(column_name)
唯一约束	字段中的值唯一	unique (column_name)
检查约束	检查	check ( column_name in ('M', 'F'))	是否在指定的范围内
NOT NULL	非空	column_name data_type not null

唯一约束类似于主键约束，但是唯一约束允许NULL值，主键约束不允许NULL值。表可以有多个唯一约束，但是只能有一个主键约束。唯一约束可修改或更新，主键约束不可修改或更新。唯一约束可以复用，主键约束不可复用。唯一约束不可以用于外键约束，主键约束可以用于外键约束。
一个表的外键一定是另一个表的主键。

2.索引

简单来说，索引是一种数据结构，用于快速查找数据库表中的特定数据。MySQL索引可以大大提高查询速度，因为它们允许数据库引擎更快地找到所需的行，而无需扫描整个表。

优点：

加速查询：索引可以使数据库引擎更快地定位到所需的数据，从而加速查询操作。
减少IO成本：索引可以减少数据库引擎需要读取的数据量，从而降低IO成本。
提高性能：通过减少数据检索时间，索引可以大大提高数据库的性能和响应速度。

缺点：

占用空间：索引会占用额外的存储空间，特别是在大型表中。
降低写操作速度：对表进行插入、更新和删除操作时，索引需要维护，可能会影响写操作的速度。
不适合所有情况：不是所有的查询都适合使用索引，有时索引可能会导致性能下降。

类型	说明
单列索引	一个索引只包含单个列
复合索引	一个索引包含多个列
唯一索引	索引列的值必唯一
主键索引	一种特殊的唯一索引，用于唯一标识表中的每一行
全文索引	用于全文搜索，可以在文本列上执行高效的全文搜索。

create index index_name on table_name (column_name);
-- 在table_name表的column_name列上创建索引

3.触发器

MySQL触发器（Triggers）是一种在特定表发生特定事件时自动执行的数据库对象。这些事件可以是INSERT、UPDATE或DELETE操作，触发器允许您在数据变化时执行自定义的SQL逻辑。MySQL触发器通常用于实现数据完整性约束、审计跟踪、自动化任务等功能。

自动执行：触发器是自动执行的，无需手动调用，它们在相关事件发生时被触发。
绑定到特定表：每个触发器都与特定表相关联，当该表上的特定事件发生时，触发器被触发。
支持多个事件：触发器可以与INSERT、UPDATE和DELETE事件相关联，您可以为每种事件定义不同的触发器。
执行逻辑：触发器可以包含自定义的SQL逻辑，例如更新其他表、记录审计日志等。

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
trigger_body

CREATE TRIGGER关键字用来创建触发器。 trigger_name是触发器的名字， before| after表示触发器是在事件之前还是之后触发， insert| update| delete表示触发器是在插入、更新还是删除时触发， table_name是触发器所在的表， trigger_body是触发器的内容。

假设我们有一个银行系统，其中包含两个表：accounts（账户信息）和transactions（交易记录）。我们可以使用触发器来实现以下功能：

-- 账户信息表
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_number VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

-- 交易记录表
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_account VARCHAR(50) NOT NULL,
    to_account VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

在每次转账后更新账户余额。

DELIMITER //
CREATE TRIGGER update_balance_after_transfer AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
    UPDATE accounts SET balance = balance - NEW.amount WHERE account_number = NEW.from_account;
    UPDATE accounts SET balance = balance + NEW.amount WHERE account_number = NEW.to_account;
END;
//
DELIMITER ;

记录每笔交易到交易记录表中。

DELIMITER // -- 暂时修改结束符
CREATE TRIGGER log_transaction AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
    INSERT INTO transactions (from_account, to_account, amount) VALUES (NEW.from_account, NEW.to_account, NEW.amount);
END;
//
DELIMITER ;