第4章 数据库的查询和视图

表4.7 A表
T1 10 5 20 T2 A1 A1 D2 T3 B1 C2 C2
4.1.3 连接（JOIN)
表4.8 B表
T1 1 T4 100 T5 A1 T6 D1 T1 20 T4 0 T5 A2 T6 D1
100
2
B2
C1
5
10
A2
C2
表4.9 A B
T1 5 20 T2 A1 D2 T3 C2 C2 T4 10 0 T5 A2 A2 T6 C2 D1
●
4.2.1 选择列
1. 选择指定的列 使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔。 【例4.5】 （查询前假设附录A中数据已经存入数据库）查询XSCJ数据库的XS表中 各个同学的姓名、专业名和总学分。
USE XSCJ SELECT 姓名,专业名,总学分 FROM XS;
说明：执行结果是XS表中全部学生的姓名、专业名和总学分列上的信息。 注意：当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。 2. 定义列别名 当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列 名之后使用AS子句来更改查询结果的列别名。语法格式为： SELECT column_name [AS] column_alias 【例4.6】 查询XS表中计算机系同学的学号、姓名和总学分，结果中各列的标题分 别指定为number、name和mark。
4.2 数据库的查询
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语句中包含的各个子句。
SELECT DISTINCT | DISTINCTROW column_name [ , column_name…]
其含义是对结果集中的重复行只选择一个，保证行的唯一性。 【例4.9】 对XSCJ数据库的XS表只选择专业名和总学分，消除结果集中的重复行。
SELECT DISTINCT 专业名，总学分 FROM XS;
4.2 数据库的查询
使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通 过SQL语句的查询可以从表或视图中迅速方便地检索数据。SQL的SELECT语句可以实现对 表的选择、投影及连接操作。 SELECT语句可以从一个或多个表中选取特定的行和列，结果通常是生成一个临时表。 在执行过程中系统根据用户的标准从数据库中选出匹配的行和列，并将结果放到临时的表中， 这就是实现选择和投影运算的一个形式。 下面介绍SELECT语句，它是SQL的核心。 语法格式：
/*FROM子句*/ /*WHERE子句*/ /* GROUP BY子句*/ /* HAVING 子句*/ /*ORDER BY子句*/ /*LIMIT子句*/
4.2 数据库的查询
说明： 从这个基本语法可以看出，最简单的SELECT语句是SELECT select_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提 前解开表锁定，在需要花费较长时间的情况下，也可以帮助把结果集合发送林 专 业 名 计算机 性 别 男 出生日期 1990-02-10 总 学 分 50 备 注
081102
081103
程明
王燕
计算机
计算机
男
女
1991-02-01
1989-10-06
50
50
若要在学生情况表中找出学生表中性别为女的行形成一个新表，则运算式为： F（学生） 上式中F：性别=“女”，该选择运算的结果如表4.2所示。
说明：不允许在WHERE子句中使用列别名。这是因为，执行WHERE代码时，可能 尚未确定列值。例如，下述查询是非法的：
SELECT 性别 AS SEX FROM XS WHERE SEX=0;
4.2.1 选择列
3. 替换查询结果中的数据 在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。 例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总 学分的具体数字。 要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：
CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 …… ELSE 表达式 END
【例4.7】 查询XS表中计算机系各同学的学号、姓名和总学分，对其总学分按以下 规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及 格”；若总学分在50～52之间，替换为“合格”；若总学分大于52，替换为“优秀”。 列标题更改为“等级”。
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] …] [WHERE where_definition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_definition] [ORDER BY {col_name | expr | position} [ASC | DESC] , ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}]
SELECT学号, 姓名, CASE WHEN 总学分 IS NULL THEN '尚未选课' WHEN 总学分 < 50 THEN '不及格' WHEN 总学分 >=50 and 总学分<=52 THEN '合格' ELSE '优秀' END AS 等级 FROM XS WHERE 专业名='计算机';
表4.4 A表
T1 1 6 2 T2 A F B T3 1 2
表4.5 B表
T4 3 0 T5 M N
表4.6A B
F
T1 1 2
T2 A B
T3 1 2
T4 3 0
T5 M N
4.1.3 连接（JOIN)
两个表连接最常用的条件是两个表的某些列值相等，这样的连接称为等值连接，上面 的例子就是等值连接。 数据库应用中最常用的是“自然连接”。进行自然连接运算要求两个表有共同属性 （列），自然连接运算的结果表是在参与操作两个表的共同属性上进行等值连接后再去除 重复的属性后所得的新表。自然连接运算记为：RS，其中R和S是参与运算的两个表。 【例4.4】 若A表和B表分别如表4.7和表4.8所示，则AB如表4.9所示。
表4.2 F（学生）
学 号 姓 名 专 业 名 性 别 出生日期 总 学 分 备 注
081103
王燕
计算机
女
1989-10-06
50
4.1.2 投影（Projection）
投影也是单目运算，该运算从表中选出指定的属性值组成一个新表，记为：A(R)。 其中A是属性名（即列名）表，R是表名。 【例4.2】 若在表4.1中对学号、姓名和总学分投影，运算式为： 学号，姓名，总学分（学生） 该运算得到如表4.3所示的新表。
表4.3 学号，姓名，总学分（学生）
学 号 081101 081102 081103 姓 名 王林 程明 王燕 总 学 分 50 50 50