花了几天啃完了《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
。数值运算>逻辑运算。
/*检索由供应商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)
(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 操作要么完全执行,要么完全不执行,来维护数据库的完整性。
- 关于事务处理的术语:
- 事务处理用来管理 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 ;