SQL 语法基础
一、什么是 SQL ?
SQL 是 Structured Query Language(结构化查询语言)的缩写,是一种专门用来与数据库沟通的语言。与其他语言(如英语或 C、C++、Java 这样的编程语言)不一样,SQL 中只有很少的词,这是有意而为的。设计 SQL 的目的是很好地完成一项任务 一 提供一种从数据库中读写数据的简单有效的方法。
SQL 有如下的优点:
- SQL 不是某个特定数据库供应商专有的语言。几乎所有重要的 DBMS 都支持 SQL ,所以学习此语言使你几乎能与所有数据库打交道;
- SQL 简单易学。它的语句全都是由有很强描述性的英语单词组成,而且这些单词的数目不多;
- SQL 虽然看上去很简单,但实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作。
二、创建、更新和删除表
下面先讲创建、更改和删除表的 SQL 语法。
2.1 创建表
用程序创建表,可以使用 SQL 的 CREATE TABLE 语句。下面的 SQL 语句创建本文中所用的 Products 表:
CREATE TABLE Products(
prod id CHAR(10) NOT NULL,
vend id CHAR(10) NOT NULL,
prod name CHAR(254) NOT NULL,
prod price DECIMAL(8,2) NOT NULL DEFAULT 3,
proddesc VARCHAR(1000) NULL
);
从上面的例子可以看到,表名紧跟 CREATE TABLE 关键字。实际的表定义(所有列)括在圆括号之中,各列之间用逗号分隔。有一列的描述增加了 DEFAULT 3,指示 DBMS,如果不给出价格则使用价格 3。
2.2 更新表
更新表定义,可以使用 ALTER TABLE 语句。因为给已有表增加列可能是所有 DBMS 都支持的唯一操作,所以我们举个这样的例子:
ALTER TABLE Vendors ADD vend_phone CHAR(20);
这条语句给 Vendors 表增加一个名为 vend_phone 的列,其数据类型为 CHAR。删除列,就使用DROP COLUMN col_name
即可。
2.3 删除表
删除表(删除整个表而不是其内容)非常简单,如下所示:
DROP TABLE CustCopy;
这条语句删除 CustCopy 表。删除表没有确认,也不能撤销,执行这条语句将永久删除该表。
使用关系规则防止意外删除 许多 DBMS 允许强制实施有关规则,防止删除与其他表相关联的表。在实施这些规则时,如果对某个表发布一条 DROP TABLE 语句,且该表是某个关系的组成部分,则 DBMS 将阻止这条语句执行,直到该关系被删除为止。 |
三、插入数据
下面介绍如何利用 SQL 的 INSERT 语句将数据插入表中。
3.1 数据插入
把数据插入表中的最简单方法是使用基本的 INSERT 语法,它要求指定表名和插入到新行中的值。下面举一个例子:
INSERT INTO Customers VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
这个例子将一个新顾客插入到 Customers 表中。存储到表中每一列的数据在 VALUES 子句中给出,必须给每一列提供一个值。如果某列没有值,如上面的 cust_contact 和 cust_email 列,则应该使用 NULL 值(假定表允许对该列指定空值)。各列必须以它们在表定义中出现的次序填充。
虽然这种语法很简单,但并不安全,应该尽量避免使用。上面的 SQL 语句高度依赖于表中列的定义次序,还依赖于其容易获得的次序信息。即使可以得到这种次序信息,也不能保证各列在下一次表结构变动后保持完全相同的次序。因此,编写依赖于特定列次序的 SQL 语句是很不安全的,这样做迟早会出问题。
编写 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);
这个例子与前一个 INSERT 语句的工作完全相同,但在表名后的括号里明确给出了列名。在插入行时,DBMS 将用 VALUES 列表中的相应值填入列表中的对应项。因为提供了列名,VALUES 必须以其指定的次序匹配指定的列名,不一定按各列出现在表中的实际次序。其优点是,即使表的结构改变,这条 INSERT 语句仍然能正确工作。
3.2 从一个表复制到另一个表
要将一个表的内容复制到一个全新的表(运行中创建的表),可以使用 SELECT INTO 语句。MariaDB、MySQL、Oracle、PostgreSQL 和 SQLite 使用的语法如下:
CREATE TABLE CustCopy AS SELECT * FROM Customers;
这条 SELECT 语句创建一个名为 CustCopy 的新表,并把 Customers 表的整个内容复制到新表中。因为这里使用的是 SELECT *,所以将在 CustCopy 表中创建(并填充)与 Customers 表的每一列相同的列。要想只复制部分的列,可以明确给出列名,而不是使用 * 通配符。
四、更新和删除数据
下面介绍如何利用 UPDATE 和 DELETE 语句进一步操作表数据。
4.1 更新数据
更新(修改)表中的数据,可以使用 UPDATE 语句。例子如下:
UPDATE Customers SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.cxx' WHERE cust_id = '1000000006';
UPDATE 语句以 WHERE 子句结束,它告诉 DBMS 更新哪一行。没有 WHERE 子句,DBMS 将会用这个电子邮件地址更新 Customers 表中的所有行,这不是我们希望的。
4.2 删除数据
从一个表中删除数据,使用 DELETE 语句。下面的语句从 Customers 表中删除一行:
DELETE FROM Customers WHERE cust_id = '1000000006';
这条语句很容易理解。DELETE FROM 要求指定从中删除数据的表名,WHERE 子句过滤要删除的行。在这个例子中,只删除顾客 1000000006。如果省略 WHERE 子句,它将删除表中每个顾客。
五、检索数据
下面介绍如何使用 SELECT 语句从表中检索一个或多个数据列。
5.1 SELECT 语句
正如上面所述,SQL语句是由简单的英语单词构成的。这些单词称为关键字,每个 SQL 语句都是由一个或多个关键字构成的。最经常使用的 SQL 语句大概就是 SELECT 语句了。它的用途是从一个或多个表中检索信息。
关键字(keyword) 作为 SQL 组成部分的保留字。关键字不能用作表或列的名字。附录E列出了某些经常使用的保留字。 |
为了使用 SELECT 检索表数据,必须至少给出两条信息一想选择什么,以及从什么地方选择。
5.2 检索单个列
我们将从简单的 SELECT 语句讲起,语句如下所示:
SELECT prod_name FROM Products;
上述语句利用 SELECT 语句从 Products 表中检索一个名为 prod_name 的列。所需的列名写在 SELECT 关键字之后,FROM 关键字指出从哪个表中检索数据。
提示1: SQL 语句和大小写 请注意, SQL 语句不区分大小写,因此 SELECT 与 SELECT 是相同的。同样,写成 SELECT 也没有关系。许多 SQL 开发人员喜欢对 SQL 关键字使用大写,而对列名和表名使用小写,这样做使代码更易于阅读和调试。不过,一定要认识到虽然 SQL 是不区分大小写的,但是表名、列名和值可能有所不同(这有赖于具体的DBMS及其如何配置)。 提示2:使用空格 在处理 SQL 语句时,其中所有空格都被忽略。 SQL 语句可以写成长长的一行,也可以分写在多行。多数 SQL 开发人员认为,将 SQL 语句分成多行更容易阅读和调试。 |
5.3 检索多个行
要想从一个表中检索多个列,仍然使用相同的 SELECT 语句。唯一的不同是必须在 SELECT 关键字后给出多个列名,列名之间必须以逗号分隔。
SELECT prod_id, prod_name, prod_price FROM Products;
这条语句使用 SELECT 语句从表 Products 中选择多个数据。在这个例子中,指定了 3 个列名,列名之间用逗号分隔。
5.4 检索所有列
除了指定所需的列外(如上所述,一个或多个列),SELECT 语句还可以检索所有的列而不必逐个列出它们。在实际列名的位置使用星号(*)通配符可以做到这点,如下所示:
SELECT * FROM Products;
如果给定一个通配符(*),则返回表中所有列。列的顺序一般是列在表定义中出现的物理顺序,但并不总是如此。
5.5 检索不同的值
如前所述,SELECT 语句返回所有匹配的行。但是,如果你不希望每个值每次都出现,该怎么办呢?办法就是使用 DISTINCT 关键字,顾名思义,它指示数据库只返回不同的值。
SELECT DISTINCT vend_id FROM Products;
SELECT DISTINCT vend_id 告诉 DBMS 只返回不同(具有唯一性)的 vend_id 行。如果使用 DISTINCT 关键字,它必须直接放在列名的前面。
警告:不能部分使用 DISTINCT DISTINCT 关键字作用于所有的列,不仅仅是跟在其后的那一列。例如,你指定 SELECT DISTINCT vend_id,prod_price,除非指定的两列完全相同,否则所有的行都会被检索出来。 |
5.6 限制结果
SELECT 语句返回指定表中所有匹配的行,很可能是每一行。如果你只想返回第一行或者一定数量的行,该怎么办呢?这是可行的,然而遗憾的是,各种数据库中的 SQL 实现并不相同。
如果你使用 My SQL 、MariaDB、Postgre SQL 或者 SQL ite,需要使用 LIMIT子句,像这样:
SELECT prod_name FROM Products LIMIT 5;
上述代码使用 SELECT 语句来检索单独的一列数据。LIMIT 5 指示 My SQL 等 DBMS 返回不超过 5 行的数据。
为了得到后面的 5 行数据,需要指定从哪儿开始以及检索的行数,像这样:
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
LIMIT 5 OFFSET 5
指示 My SQL 等 DBMS 返回从第 5 行起的 5 行数据。第一个数字是指从哪儿开始,第二个数字是检索的行数。
警告:第 0 行 第一个被检索的行是第 0 行,而不是第 1 行。因此,LIMIT 1 OFFSET 1 会检索第 2 行,而不是第 1 行。 提示:My SQL 和MariaDB快捷键 My SQL 和MariaDB支持简化版的LIMIT 4 OFFSET 3语句,即LIMIT 3,4。使用这个语法,之前的值对应 LIMIT,之后的值对应 OFFSET。 |
5.7 使用注释
我们先来看行内注释:
SELECT prod_name FROM Products; --这是一条注释
注释使用--
(两个连字符)嵌在行内,之后的文本就是注释。
你也可以进行多行注释,注释可以在脚本的任何位置停止和开始。
/* SELECT prod_name, vend_id
FROM Products; */
SELECT prod_name FROM Products;
注释从/*
开始,到*/
结束,之间的任何内容都是注释。
六、排序检索数据
下面讲授如何使用 SELECT 语句的 ORDER BY 子句,根据需要排序检索出的数据。
6.1 排序数据
为了明确地排序用 SELECT 语句检索出的数据,可使用 ORDER BY 子句。ORDER BY 子句取一个或多个列的名字,据此对输出进行排序。请看下面的例子:
SELECT prod_name FROM Products ORDER BY prod_name;
ORDER BY 子句,指示 DBMS 软件对 prod_name 列以字母顺序排序数据。
注意:ORDER BY 子句的位置 在指定一条 ORDER BY 子句时,应该保证它是 SELECT 语句中最后一条子句。如果它不是最后的子句,将会出现错误消息。 |
6.2 按多个列排序
下面的代码检索 3 个列,并按其中两个列对结果进行排序 一 首先按价格,然后按名称排序。
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
对于上述例子中的输出,仅在多个行具有相同的 prod_price 值时才对产品按 prod_name 进行排序。如果 prod_price 列中所有的值都是唯一的,则不会按 prod_name 排序。(先对 prod_price 排序,再对 prod_name 排序)
6.3 按列位置排序
除了能用列名指出排序顺序外,ORDER BY 还支持按相对列位置进行排序。为理解这一内容,我们来看个例子:
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
SELECT 清单中指定的是选择列的相对位置而不是列名。ORDER BY 2 表示按 SELECT 清单中的第二个列 prod_name 进行排序。ORDER BY 2, 3 表示先按 prod_price,再按 prod_name 进行排序。
6.4 指定排序方向
数据排序不限于升序排序(从 A 到 Z),这只是默认的排序顺序。还可以使用 ORDER BY 子句进行降序(从 Z 到 A )排序。为了进行降序排序,必须 指定 DESC 关键字。例子如下:
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;
DESC 关键字只应用到直接位于其前面的列名。在上例中,只对 prod_price 列指定 DESC,对 prod_name 列不指定。因此,prod_price 列以降序排序,而 prod_name 列(在每个价格内)仍然按标准的升序排序。
警告:在多个列上降序排序 如果想在多个列上进行降序排序,必须对每一列指定 DESC 关键字。 |
七、过滤数据
下面将讲授如何使用 SELECT 语句的 WHERE 子句指定搜索条件。
7.1 使用 WHERE 子句
在 SELECT 语句中,数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名(FROM 子句)之后给出,如下所示:
SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;
这条语句从 products 表中检索两个列,但不返回所有行,只返回 prod_price 值为 3.49 的行。
7.2 WHERE 子句操作符
SQL 支持下表列出的所有条件操作符。
操作符 | 说明 |
---|---|
= | 等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
IS NULL | 为NULL值 |
1. 范围内检查
要检查某个范围的值,可以使用 BETWEEN 操作符。其语法与其他 WHERE 子句的操作符稍有不同,因为它需要两个值,即范围的开始值和结束值。下面的例子说明如何使用 BETWEEN 操作符,它检索价格在 5 美元和 10 美元之间的所有产品:
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
从这个例子可以看到,在使用 BETWEEN 时,必须指定两个值——所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。
2. 空值检查
确定值是否为 NULL,不能简单地检查是否 =NULL。SELECT 语句有一个特殊的 WHERE 子句,可用来检查具有 NULL 值的列。这个 WHERE 子句就是 IS NULL 子句。其语法如下:
SELECT prod_name FROM Products WHERE prod_price IS NULL;
这条语句返回所有没有价格(空 prod_price 字段,不是价格为 0)的产品。
八、高级数据过滤
下面讲授如何组合 WHERE 子句以建立功能更强、更高级的搜索条件。我们还将学习如何使用 NOT 和 IN 操作符。
8.1 组合 WHERE 子句
第 4 课介绍的所有 WHERE 子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制,SQL 允许给出多个 WHERE 子句。这些子句有两种使用方式,即以 AND 子句或 OR 子句的方式使用。
1. AND 操作符
要通过不止一个列进行过滤,可以使用 AND 操作符给 WHERE 子句附加条件。下面的代码给出了一个例子:
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;
此 SQL 语句检索由供应商 DLL01 制造且价格小于等于 4 美元的所有产品的名称和价格。
2. OR 操作符
OR 操作符与 AND 操作符正好相反,它指示 DBMS 检索匹配任一条件的行。事实上,许多 DBMS 在 OR WHERE 子句的第一个条件得到满足的情况下, 就不再计算第二个条件了。例子如下:
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
此 SQL 语句检索由任一个指定供应商制造的所有产品的产品名和价格。
8.2 IN操作符
IN 操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN 取一组由逗号分隔、括在圆括号中的合法值。下面的例子说明了这个操作符:
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01', 'BRS01' );
此 SELECT 语句检索由供应商 DLL01 和 BRS01 制造的所有产品。IN 操作符后跟由逗号分隔的合法值,这些值必须括在圆括号中。 你可能会猜测 IN 操作符完成了与 OR 相同的功能,恭喜你猜对了!
8.3 NOT操作符
WHERE 子句中的 NOT 操作符有且只有一个功能,那就是否定其后所跟的任何条件。NOT 关键字可以用在要过滤的列前,而不仅是在其后。下面的例子说明了这个操作符:
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01';
这里的 NOT 否定跟在其后的条件,因此,DBMS 不是匹配 vend_id 为 DLL01,而是匹配非 DLL01 之外的所有东西。
九、用通配符进行过滤
下面介绍什么是通配符、如何使用通配符以及怎样使用 LIKE 操作符进行通配搜索,以便对数据进行复杂过滤。
1. 百分号(%)通配符
最常使用的通配符是百分号(%)。在搜索串中,% 表示任何字符出现任意次数。例如,为了找出所有以词 Fish 起头的产品,可发布以下 SELECT 语句:
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';
此例子使用了搜索模式 'Fish%’。在执行这条子句时,将检索任意以 Fish 起头的词。% 告诉 DBMS 接受 Fish 之后的任意字符,不管它有多少字符。
2. 下划线(_)通配符
另一个有用的通配符是下划线(_)。下划线的用途与 % —样,但它只匹配单个字符,而不是多个字符。例子如下:
SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';
这个 WHERE 子句中的搜索模式给出了后面跟有文本的两个通配符。
3. 方括号([])通配符
方括号([])通配符用来指定一个字符集,它必须匹配指定位置(通配符的位置)的一个字符。例如,找出所有名字以 J 或 M 起头的联系人,可进行如下查询:
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%';
这一搜索模式使用了两个不同的通配符。[JM] 匹配任何以方括号中字母开头的联系人名,它也只能匹配单个字符。[JM] 之后的 % 通配符匹配第一个字符之后的任意数目的字符,返回所需结果。
SQL 语法进阶
一、创建计算字段
下面介绍什么是计算字段,如何创建计算字段,以及如何从应用程序中使用别名引用它们。
1.1 计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式,下面举几个例子。
-
需要显示公司名,同时还需要显示公司的地址,但这两个信息存储在不同的表列中。
-
物品订单表存储物品的价格和数量,不存储每个物品的总价格(用价格乘以数量即可)。但为打印发票,需要物品的总价格。
-
需要根据表数据进行诸如总数、平均数的计算。
存储在表中的数据可能不是应用程序所需要的,我们需要直接从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据,然后再在客户端应用程序中重新格式化。这就是计算字段可以派上用场的地方了。与前面介绍的列不同,计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。
1.2 拼接字段
为了说明如何使用计算字段,我们来举一个简单例子,创建由两列组成的标题。在 SQL 中的 SELECT 语句中,可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS,此操作符可用加号(+)或两个竖杠(II)表示。在 MySQL 和 MariaDB 中,必须使用特殊的函数。
下面是 MySQL 和 MariaDB 中使用的语句:
SELECT Concat(vend_name, ' ', vend_country) FROM Vendors;
使用 Concat 函数拼接 vend_name,一个空格 ' ' 和 vend_country。
使用别名
SELECT 语句可以很好地拼接地址字段,但这个新计算列的名字是什么呢?实际上它没有名字,它只是一个值。如 果仅在 SQL 查询工具中查看一下结果,这样没有什么不好。但是,一个未命名的列不能用于客户端应用中,因为客户端没有办法引用它。
为了解决这个问题,SQL 支持列别名。别名(alias)是一个字段或值的替换名。别名用 AS 关键字赋予。请看下面的 SELECT 语句:
SELECT Concat(vend_name, ' ', vend_country) AS vend_title FROM Vendors;
这里的计算字段之后跟了文本 AS vend_title,它指示 SQL 创建一个包含指定计算结果的名为 vend_title 的计算字段。现在列名为 vend_title,任何客户端应用都可以按名称引用这个列,就像它是一个实际的表列一样。
1.3 执行算数计算
计算字段的另一常见用途是对检索出的数据进行算术计算。例如,可以汇总 2008 年这一年订单物品的价格(单价乘以订购数量):
SELECT prod_id, quantity, item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
这里的计算字段之后跟了文本 AS vend_title,它指示 SQL 创建一个包含指定计算结果的名为 vend_title 的计算字段。现在列名为 vend_title,任何客户端应用都可以按名称引用这个列,就像它是一个实际的表列一样。
SQL 支持基本的加减乘除这4种算术操作符。
二、使用数据处理函数
下面介绍什么是函数,DBMS 支持何种函数,以及如何使用这些函数;还将讲解为什么 SQL 函数的使用可能会带来问题。
2.1 函数
与大多数其他计算机语言一样,SQL 也可以用函数来处理数据。函数一般是在数据上执行的,为数据的转换和处理提供了方便。
函数带来的问题
与几乎所有 DBMS 都等同地支持 SQL 语句(如 SELECT)不同,每一个 DBMS 都有特定的函数。事实上,只有少数几个函数被所有主要的 DBMS 等同地支持。虽然所有类型的函数一般都可以在每个 DBMS 中使用,但各个函数的名称和语法可能极其不同。这就表示为特定 SQL 实现编写的代码在其他实现中可能不正常。
2.2 使用函数
大多数 SQL 实现支持以下类型的函数。
- 用于处理文本字符串(如删除或填充值,转换值为大写或小写)的文本函数。
- 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
- 用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差,检查日期有效性)的日期和时间函数。
- 返回 DBMS 正使用的特殊信息(如返回用户登录信息)的系统函数。
1. 文本处理函数
下面例子使用的是 UPPER() 函数:
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors;
UPPER() 将文本转换为大写。
下表列出了一些常用的文本处理函数。
函 数 | 说 明 |
---|---|
LEFT()(或使用子字符串函数) | 返回字符串左边的字符 |
LENGTH() (也使用 DATALENGTH() 或 LEN()) | 返回字符串的长度 |
LOWER() (Access 使用 LCASE()) | 将字符串转换为小写 |
LTRIM() | 去掉字符串左边的空格 |
RIGHT() (或使用子字符串函数) | 返回字符串右边的字符 |
RTRIM() | 去掉字符串右边的空格 |
SOUNDEX() | 返回字符串的SOUNDEX值 |
UPPER() (Access 使用 UCASE()) | 将字符串转换为大写 |
2. 日期和时间处理函数
MySQL 和 MariaDB 用户可使用名为 YEAR() 的函数从日期中提取年份:
SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;
DBMS 提供的功能远不止简单的日期成分提取。大多数 DBMS 具有比较日期、执行基于日期的运算、选择日期格式等的函数。但是,可以看到,不同 DBMS 的日期-时间处理函数可能不同。关于具体 DBMS 支持的日期-时间处理函数,请参阅相应的文档。
3. 数值处理函数
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算,因此不像字符串或日期-时间处理函数使用那么频繁。具有讽刺意味的是,在主要 DBMS 的函数中,数值函数是最一致、最统一的函数。下表列出了一些常用的数值处理函数。
函 数 | 说 明 |
---|---|
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
EXP() | 返回一个数的指数值 |
PI() | 返回圆周率 |
SIN() | 返回一个角度的正弦 |
SQRT() | 返回一个数的平方根 |
TAN() | 返回一个角度的正切 |
三、汇总数据
下面介绍什么是 SQL 的聚集函数,如何利用它们汇总表的数据。
3.1 聚集函数
我们经常需要汇总数据而不用把它们实际检索出来,为此 SQL 提供了专门的函数。使用这些函数,SQL 查询可用于检索数据,以便分析和报表生成。这种类型的检索例子有:
- 确定表中行数(或者满足某个条件或包含某个特定值的行数);
- 获得表中某些行的和;
- 找出表列(或所有行或某些特定的行)的最大值、最小值、平均值。
为方便这种类型的检索,SQL 给出了 5 个聚集函数,见下表。这些函数能进行上述检索。与前一章介绍的数据处理函数不同,SQL 的聚集函数在各种主要 SQL 实现中得到了相当一致的支持。
函 数 | 说 明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
1. AVG() 函数
AVG() 通过对表中行数计数并计算其列值之和,求得该列的平均值。下面的例子使用 AVG() 返回 Products 表中所有产品的平均价格:
SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
此 SELECT 语句返回值 avg_price,它包含 Products 表中所有产品的平均价格。
2. COUNT() 函数
C0UNT() 函数进行计数。可利用 C0UNT() 确定表中行的数目或符合特定条件的行的数目。C0UNT() 函数有两种使用方式:
- 使用 C0UNT() 对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
- 使用 COUNT(column) 对特定列中具有值的行进行计数,忽略 NULL 值。
SELECT COUNT(*) AS num_cust FROM Customers;
在此例子中,利用 C0UNT(*) 对所有行计数,不管行中各列有什么值。计数值在 num_cust 中返回。
3.2 聚集不同值
以上 5 个聚集函数都可以如下使用:
- 对所有行执行计算,指定 ALL 参数或不指定参数(因为 ALL 是默认行为)。
- 只包含不同的值,指定 DISTINCT 参数。
下面的例子使用 AVG() 函数返回特定供应商提供的产品的平均价格。它与上面的 SELECT 语句相同,但使用了 DISTINCT 参数,因此平均值只考虑各个不同的价格:
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
使用了 DISTINCT 后,会排除相同的价格。
3.3 组合聚集函数
目前为止的所有聚集函数例子都只涉及单个函数。但实际上,SELECT 语句可根据需要包含多个聚集函数。请看下面的例子:
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 语句执行了 4 个聚集计算,返回 4 个值(Products 表中物品的数目,产品价格的最高值、最低值以及平均值)。
四、分组数据
下面介绍如何分组数据,以便汇总表内容的子集。这涉及两个新 SELECT 语句子句:GROUP BY 子句和 HAVING 子句。
4.1 数据分组
如果要返回每个供应商提供的产品数目,该怎么办?或者返回只提供一项产品的供应商的产品,或者返回提供 10 个以上产品的供应商的产品, 怎么办?这就是分组大显身手的时候了。使用分组可以将数据分为多个逻辑组,对每个组进行聚集计算。
4.2 创建分组
分组是使用 SELECT 语句的 GROUP BY 子句建立的。理解分组的最好办法是看一个例子:
SELECT vend_id,COUNT(*) AS num_prods FROM Products GROUP BY vend_id;
输出如下:
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01 | 3 |
| DLL01 | 4 |
| FNG01 | 2 |
+---------+-----------+
上面的 SELECT 语句指定了两个列:vend_id 包含产品供应商的 ID,num_prods 为计算字段,GROUP BY 子句指示 DBMS 按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods —次。
4.3 过滤分组
除了能用 GROUP BY 分组数据外,SQL 还允许过滤分组,规定包括哪些分组,排除哪些分组。例如,你可能想要列出至少有两个订单的所有顾客。
SQL 为此提供了另一个子句,就是 HAVING 子句。HAVING 非常类似于 WHERE。事实上,目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是,WHERE 过滤行,而 HAVING 过滤分组。
SELECT cust_id,COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;
这条 SELECT 语句的前三行类似于上面的语句。最后一行增加了 HAVING 子句,它过滤 COUNT(*) >= 2 (两个以上订单)的那些分组。
五、联结表
下面会介绍什么是联结,为什么使用联结,如何编写使用联结的 SELECT 语句。
5.1 联结
SQL 最强大的功能之一就是能在数据查询的执行中联结(join)表。联结是利用 SQL 的 SELECT 能执行的最重要的操作,很好地理解联结及其语法是学习 SQL 的极为重要的部分。
1. 关系表
有一个包含产品目录的数据库表,其中每类物品占一行。对于每一种物品,要存储的信息包括产品描述、价格,以及生产该产品的供应商。
现在有同一供应商生产的多种物品,那么在何处存储供应商名、地址、联系方法等供应商信息呢?将这些数据与产品信息分开存储的理由是:
- 同一供应商生产的每个产品,其供应商信息都是相同的,对每个产品重复此信息既浪费时间又浪费存储空间;
- 如果供应商信息发生变化,例如供应商迁址或电话号码变动,只需修改一次即可;
- 如果有重复数据(即每种产品都存储供应商信息),则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。
关系表的设计就是要把信息分解成多个表,一类数据一个表。各表通过某些共同的值互相关联(所以才叫关系数据库)。
在这个例子中可建立两个表:一个存储供应商信息,另一个存储产品信息。Vendors 表包含所有供应商信息,每个供应商占一行,具有唯一的标识。
Products 表只存储产品信息,除了存储供应商 ID (Vendors 表的主键)外,它不存储其他有关供应商的信息。Vendors 表的主键将 Vendors 表与 Products 表关联,利用供应商 ID 能从 Vendors 表中找出相应供应商的详细信息。
2. 为什么使用联结
如前所述,将数据分解为多个表能更有效地存储,更方便地处理,并且可伸缩性更好。但这些好处是有代价的。如果数据存储在多个表中,怎样用一条 SELECT 语句就检索出数据呢?
答案是使用联结。简单说,联结是一种机制,用来在一条 SELECT 语句中关联表,因此称为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
5.2 创建联结
创建联结非常简单,指定要联结的所有表以及关联它们的方式即可。请看下面的例子:
SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;
SELECT 语句与前面所有语句一样指定要检索的列。这里最大的差别是所指定的两列(prod_name 和 prod_price)在一个表中,而第三列(vend_name)在另一个表中。
这条语句的 FROM 子句列出了两个表:Vendors 和 Products。它们就是这条 SELECT 语句联结的两个表的名字。这两个表用 WHERE 子句正确地联结,WHERE 子句指示 DBMS 将 Vendors 表中的 vend_id 与 Products 表中的 vend_id 匹配起来。
可以看到,要匹配的两列指定为 Vendors.vend_id 和 Products.vend_id。这里需要这种完全限定列名,如果只给出 vend_id,DBMS 就不知道指的是哪一个(每个表中有一个)。
5.3 联结多个表
SQL 不限制一条 SELECT 语句中可以联结的表的数目。创建联结的基本规则也相同。首先列出所有表,然后定义表之间的关系。例如:
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;
这个例子显示订单 20007 中的物品。订单物品存储在 OrderItems 表中。每个产品按其产品 ID 存储,它引用 Products 表中的产品。这些产品通过供应商 ID 联结到 Vendors 表中相应的供应商,供应商 ID 存储在每个产品的记录中。这里的 FROM 子句列出三个表,WHERE 子句定义这两个联结条件, 而第三个联结条件用来过滤出订单 20007 中的物品。
六、创建高级联结
本课讲解另外一些联结(包括它们的含义和使用方法),介绍如何使用表别名,如何对被联结的表使用聚集函数。
6.1 使用表别名
请看下面的 SELECT 语句。它与前一课例子中所用的语句基本相同,但改成了使用表别名:
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';
可以看到,FROM 子句中的三个表全都有别名。Customers AS C 使用 C 作为 Customers 的别名,如此等等。这样,就可以使用省略的 C 而不用全名 Customers。在这个例子中,表别名只用于 WHERE 子句。其实它不仅能用于 WHERE 子句,还可以用于 SELECT 的列表、ORDER BY 子句以及其他语句部分。
6.2 使用不同类型的联结
迄今为止,我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结:自联结(self-join)、自然联结(natura join)和外联结(outer join)。由于篇幅原因,这里不再过多介绍。
七、使用子查询
下面介绍什么是子查询,如何使用它们。
7.1 子查询
SQL 还允许创建子查询(subquery),即嵌套在其他查询中的查询。为什么要这样做呢?理解这个概念的最好方法是考察几个例子。
7.2 利用子查询进行过滤
订单存储在两个表中。每个订单包含订单编号、客户 ID、订单日期,在 Orders 表中存储为一行。各订单的物品存储在相关的 Orderltems 表中。Orders 表不存储顾客信息,只存储顾客 ID。顾客的实际信息存储在 Customers 表中。
现在,假如需要列出订购物品 RGAN01 的所有顾客,应该怎样检索?下面列出具体的步骤。
-
检索包含物品 RGAN01 的所有订单的编号。
-
检索具有前一步骤列出的订单编号的所有顾客的 ID。
-
检索前一步骤返回的所有顾客 ID 的顾客信息。
上述每个步骤都可以单独作为一个查询来执行。可以把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。也可以使用子查询来把 3 个查询组合成一条语句。使用子查询的语句如下所示:
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'));
为了执行上述 SELECT 语句,DBMS 实际上必须执行三条 SELECT 语句。最里边的子查询返回订单号列表,此列表用于其外面的子查询的 WHERE 子句。外面的子查询返回顾客 ID 列表,此顾客 ID 列表用于最外层查询的 WHERE 子句。最外层查询返回所需的数据。
八、组合查询
本课讲述如何利用 UNION 操作符将多条 SELECT 语句组合成一个结果集。
8.1 组合查询
多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句。但是,SQL 也允许执行多个查询(多条 SELECT 语句),并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。
主要有两种情况需要使用组合查询:
-
在一个查询中从不同的表返回结构数据;
-
对一个表执行多个查询,按一个查询返回数据。
8.2 创建组合查询
可用 UNION 操作符来组合数条 SQL 查询。利用 UNION,可给出多条 SELECT 语句,将它们的结果组合成一个结果集。如下所示:
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';
这条语句由前面的两条 SELECT 语句组成,之间用 UNION 关键字分隔。UNION 指示 DBMS 执行这两条 SELECT 语句,并把输出组合成一个查询结果集。