11级_Java_曹建波5.28 数据库的查询和视图

数据库的查询和视图

选择（Selection）

选择是单目运算，其运算对象是一个表。该运算按给定的条件，从表中选出满足条件的行形成一个新表作为运算结果。

选择运算的记号为sF（R）。

其中s是选择运算符，下标F是一个条件表达式，R是被操作的表。

若要在学生情况表中找出学生表中性别为女的行形成一个新表，则运算式为：

sF（学生）

上式中F：性别=“女”，该选择运算的结果如表4.2所示。

投影也是单目运算，该运算从表中选出指定的属性值组成一个新表，记为：ÕA(R)。

其中A是属性名（即列名）表，R是表名。

若在表4.1中对学号、姓名和总学分投影，运算式为：

Õ学号，姓名，总学分（学生）

该运算得到如表4.3所示的新表。

投影也是单目运算，该运算从表中选出指定的属性值组成一个新表，记为：ÕA(R)。

其中A是属性名（即列名）表，R是表名。

若在表4.1中对学号、姓名和总学分投影，运算式为：

Õ学号，姓名，总学分（学生）

该运算得到如表4.3所示的新表。

连接是把两个表中的行按照给定的条件进行拼接而形成新表，记为：RS。

其中，R、S是被操作的表，F是条件。

F

若表A和B分别如表4.4和表4.5所示，则A B如表4.6所示，其中

F为：T1=T3。

两个表连接最常用的条件是两个表的某些列值相等，这样的连接称为等值连接，上面的例子就是等值连接。

数据库应用中最常用的是“自然连接”。进行自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作两个表的共同属性上进行等值连接后再去除重复的属性后所得的新表。自然连接运算记为：RS，其中R和S是参与运算的两个表。

若A表和B表分别如表4.7和表4.8所示，则AB如表4.9所示。

数据库的查询

使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过SQL语句的查询可以从表或视图中迅速方便地检索数据。SQL的SELECT语句可以实现对表的选择、投影及连接操作。

SELECT语句可以从一个或多个表中选取特定的行和列，结果通常是生成一个临时表。在执行过程中系统根据用户的标准从数据库中选出匹配的行和列，并将结果放到临时的表中，这就是实现选择和投影运算的一个形式。

下面介绍SELECT语句，它是SQL的核心。

语法格式：

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...

[INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name']

[FROM table_reference [ ,table_reference] …] /*FROM子句*/

[WHERE where_definition] /*WHERE子句*/

[GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] /* GROUP BY子句*/

[HAVING where_definition] /*HAVING 子句*/

[ORDER BY {col_name | expr | position} [ASC| DESC] , ...] /*ORDERBY子句*/

[LIMIT {[offset,] row_count | row_count OFFSET offset}] /*LIMIT子句*/

说明：

从这个基本语法可以看出，最简单的SELECT语句是SELECTselect_expr，利用这个最简单的SELECT语句，可以进行MySQL所支持的任何运算，例如，SELECT 1+1，它将返回2。

SELECT关键词的后面可以使用很多的选项：

● ALL | DISTINCT | DISTINCTROW：这几个选项指定是否重复行应被返回。如果这些选项没有被给定，则默认值为ALL（所有的匹配行被返回）。DISTINCT 和 DISTINCTROW是同义词，用于消除结果集合中的重复行。

● HIGH_PRIORITY，STRAIGHT_JOIN和以SQL_为开头的选项都是MySQL相对于标准SQL的扩展，这些选项在多数情况下可以选择不使用。

HIGH_PRIORITY：给予SELECT更高的优先权，使查询立刻执行，加快查询速度。

● STRAIGHT_JOIN：用于促使MySQL优化器把表联合在一起，加快查询速度。

● SQL_SMALL_RESULT：可以与GROUP BY或DISTINCT同时使用，来告知MySQL优化器结果集合是较小的。在此情况下，MySQL使用快速临时表来储存生成的表，不使用分类。

● SQL_BUFFER_RESULT：促使结果被放入一个临时表中。这可以帮助MySQL提前解开表锁定，在需要花费较长时间的情况下，也可以帮助把结果集合发送到客户端中。

● SQL_BIG_RESULT：可以与GROUP BY或DISTINCT同时使用，来告知MySQL优化器结果集合有很多行。在这种情况下，MySQL会优先进行分类，不优先使用临时表。

● SQL_CACHE：告知MySQL把查询结果存储在查询缓存中。对于使用UNION的查询或子查询，本选项会影响查询中的所有SELECT。

● SQL_NO_CACHE：告知MySQL不要把查询结果存储在查询缓存中。

● SQL_CALC_FOUND_ROWS：告知MySQL计算有多少行应位于结果集合中，不考虑任何LIMIT子句。

● SELECT…INTO OUTFILE 'file_name'：这个语句可以将表中的行导出到一个文件中，这个文件被创建在服务器主机中，file_name为文件名。这个语句将会在第8章中具体介绍。

所有被使用的子句必须按语法说明中显示的顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。

下面具体介绍一下SELECT语句中包含的各个子句。

1. 选择指定的列

使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔。

【例4.5】 （查询前假设附录A中数据已经存入数据库）查询XSCJ数据库的XS表中各个同学的姓名、专业名和总学分。

USE XSCJ

SELECT 姓名,专业名,总学分

FROM XS;

说明：执行结果是XS表中全部学生的姓名、专业名和总学分列上的信息。

注意：当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。

2. 定义列别名

当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列别名。语法格式为：

SELECT column_name [AS] column_alias

查询XS表中计算机系同学的学号、姓名和总学分，结果中各列的标题分别指定为number、name和mark。

SELECT 学号 AS number, 姓名 AS name, 总学分 AS mark

FROM XS

WHERE 专业名= '计算机';

该语句的执行结果为：

注意，当自定义的列标题中含有空格时，必须使用引号将标题括起来。例如：

SELECT 学号 AS 'Student number', 姓名 AS 'Student name', 总学分 AS mark

FROM XS

WHERE 专业名= '计算机';

说明：不允许在WHERE子句中使用列别名。这是因为，执行WHERE代码时，可能尚未确定列值。例如，下述查询是非法的：

SELECT 性别 AS SEX FROM XS WHERE SEX=0;

3. 替换查询结果中的数据

在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。

要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：

CASE

WHEN 条件1 THEN 表达式1

WHEN 条件2 THEN 表达式2

……

ELSE 表达式

END

查询XS表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50～52之间，替换为“合格”；若总学分大于52，替换为“优秀”。列标题更改为“等级”。

SELECT学号, 姓名,

CASE

WHEN 总学分 IS NULL THEN '尚未选课'

WHEN 总学分 < 50 THEN '不及格'

WHEN 总学分 >=50 and 总学分<=52 THEN '合格'

ELSE '优秀'

ENDAS 等级

FROM XS

WHERE 专业名='计算机';

该语句的执行结果为：