1、第4章 数据库的查询和视图,4.1 数 据 库 的 查 询,SELECT语句的基本语法：,SELECT语句很复杂，其基本的语法如下：,4.1.1 单 表 查 询,1. 选择列,4.1.1 单 表 查 询,1) 选择一个表中指定的列,4.1.1 单 表 查 询,USE XSBOOK SELECT 姓名,专业名, 借书数 FROM XS,4.1.1 单 表 查 询,查询XS表中的所有列。,2) 查询全部列,3) 修改查询结果中的列标题,4.1.1 单 表 查 询,SELECT 借书证号 AS cardno，姓名 AS name，借书数 AS cnt FROM XS WHERE 专业名= 计算机,S
2、ELECT Card no = 借书证号, 姓名 AS Student name, cnt = 借书数 FROM XS WHERE 专业名= 计算机,4.1.1 单 表 查 询,4) 替换查询结果中的数据,SELECT 借书证号, 姓名, 性别= CASE WHEN 性别= 0 THEN 男WHEN 性别= 1 THEN 女 END, 借书数 FROM XS,SELECT 借书证号, 姓名, 评价= CASE WHEN 借书数 = 0 THEN 不爱学习 WHEN 借书数0 and 借书数=5 and 借书数 =10 THEN 勤奋 ELSE 勤奋 END FROM XS WHERE 专业名=
3、计算机,4.1.1 单 表 查 询,5) 查询经过计算的值,要查询经过计算的值，在SELECT之后的目标列的格式为表达式： SELECT expression , expression ,4.1.1 单 表 查 询,4.1.1 单 表 查 询,SELECT CPMC AS 产品名称，产品总值 = JG * KCL FROM CP,4.1.1 单 表 查 询,2. 选择行,1) 消除结果集中的重复行,4.1.1 单 表 查 询,2) 限制结果集的返回行数,4.1.1 单 表 查 询,WHERE子句给出查询条件，该子句必须紧跟FROM子句之后，其基本格式为：,3) 查询满足条件的行,4.1.1 单
4、 表 查 询,4.1.1 单 表 查 询,(1) 表达式比较,expression = | | = | | != | ! expression,查询XSBOOK数据库XS表中借书数在3本以上的学生情况。 SELECT *FROM XSWHERE 借书数 != 3,4.1.1 单 表 查 询,(2) 指定范围,用于范围比较的关键字有两个：BETWEEN和NOT BETWEEN，用于查找字段值在（或不在）指定范围的行。BETWEEN(NOT BETWEEN)关键字格式为：expression NOT BETWEEN expression1 AND expression2,查询XS表中出生时间在“1
5、980-1-1”与“1982-12-31”之间的学生情况。 SELECT *FROM XSWHERE 出生时间 BETWEEN 1980-1-1 AND 1982-12-31 查询XS表中不在1982年出生的学生情况。 SELECT *FROM XSWHERE 出生时间 NOT BETWEEN 1982-1-1 and 1982-12-31,(3) 确定集合,使用IN关键字可以指定一个值表集合，值表中列出所有可能的值，当表达式与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表集合的格式为： expression IN ( expression ,n),查询XS表
6、中专业名为“计算机”、“信息工程”、“英语”或“自动化”的学生的情况。 SELECT *FROM XSWHERE 专业名 IN (计算机，信息工程，英语，自动化),4.1.1 单 表 查 询,(4) 字符匹配,SELECT *FROM XSWHERE 专业名 LIKE 计算机,SELECT *FROM XSWHERE 姓名 LIKE 王_,4.1.1 单 表 查 询,(5) 空值比较,当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为： expression IS NOT NULL,查询XS表中专业名尚不定的学生情况。,4.1.1 单 表 查 询,(6) 多重条件查询,查询
7、计算机专业、借书数在3本以下的学生姓名和借书证号。,查询计算机专业或者英语专业的学生姓名和借书证号。,4.1.1 单 表 查 询,(7) CONTAINS谓词,全文索引表操作过程为：, 建立全文目录。展开数据库，在数据库目录树的“全文目录”上按下鼠标右键，在弹出的快捷菜单上选择“新建全文目录”，如图4.10所示。 上一步操作结束后，出现如图4.11所示的“全文目录属性”对话框，输入文件名(如BOOK1)，单击“确定”，即创建好全文目录。,4.1.1 单 表 查 询,4.1.1 单 表 查 询, 建立全文索引。展开数据库，在需建全文索引的表上按下鼠标右键，在弹出的快捷菜单上选择“全文索引表”“在
8、表上定义全文索引”，将出现如图4.12所示的“全文索引向导”界面，单击“下一步”。 在所出现的图4.13所示的全文索引向导界面中为表选择唯一索引，单击“下一步”。,4.1.1 单 表 查 询,4.1.1 单 表 查 询, 在所出现的如图4.14所示的全文索引向导界面中选择作为全文索引的列及语言种类，单击“下一步”。, 选择全文目录，如图4.15所示，单击“下一步”。, 在如图4.16所示的全文索引向导界面中单击“完成”，即完成全文索引的创建工作。此后，还需启动对全文目录文件的增量填充，如图4.17所示。讨论CONTAINS谓词的使用，CONTAINS谓词的格式为：CONTAINS ( colu
9、mn | * , ),4.1.1 单 表 查 询,4.1.1 单 表 查 询,4.1.1 单 表 查 询,4.1.1 单 表 查 询,SELECT语句的ORDER BY子句可用于对查询结果按照一个或多个字段的值进行升序(ASC)或降序(DESC)排列，缺省值为升序(ASC)。ORDER BY子句的格式为： ORDER BY order_by_expression ASC | DESC ,n ,3) 对查询结果排序,(1) SUM和AVGSUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM / AVG ( ALL | DISTINCT expression ),4) 使用聚
10、合函数,(2) MAX和MIN MAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为： MAX / MIN ( ALL | DISTINCT expression ),4.1.1 单 表 查 询,表4.4 常用聚合函数表,4.1.1 单 表 查 询,(3) COUNT COUNT用于统计组中满足条件的行数或总行数，格式为： COUNT ( ALL | DISTINCT expression | * ),(4) GROUPIN GROUPING函数为输出的结果表产生一个附加列，该列的值为1或0，格式为： GROUPING(column_name),4.1.1 单 表 查 询,5)
11、 对查询结果分组,将XS表中的学生按专业名分组。,4.1.1 单 表 查 询,例如：查询各专业的学生数,4.1.1 单 表 查 询,例如：查询每个专业的男生、女生人数、总人数及学生总人数。,4.1.1 单 表 查 询,6) HAVING子句,查找男生数或女生数不少于2的专业名以及学生人数。,4.1.1 单 表 查 询,例如：查找男生人数超过2的专业名。,4.1.1 单 表 查 询,COMPUTE子句用于分类汇总，格式为： COMPUTE 聚合函数名（expression） ,n BY expression ,n ,查找计算机专业学生的借书证号、姓名、出生时间，并统计计算机专业的学生总人数。,7
12、) COMPUTE子句,4.1.1 单 表 查 询,4.1.2 连 接 查 询, 其中比较运算符主要有：、=、!=、!，当比较符为“=”时，就是等值连接。,连接谓词又称为连接条件，其一般格式为：,1. 连接谓词,查找XSBOOK数据库每个学生的情况以及学生的借书情况。,4.1.2 连 接 查 询,4.1.2 连 接 查 询,例如：查找借阅了“Web站点安全”一书的学生的借书证号、姓名、专业名和借书时间。,4.1.2 连 接 查 询,该语句的执行结果如：,4.1.2 连 接 查 询,2. 以JOIN关键字指定的连接,4.1.2 连 接 查 询,(1) 内连接,例如：用FROM的JOIN关键字表达
13、下列查询：查询借阅了ISBN为“7-111-06359-7”的学生姓名及专业名。,4.1.2 连 接 查 询,例如：用FROM的JOIN关键字表达下列查询：查找借阅了“Web站点安全”一书的学生的借书证号、姓名、专业名和借书时间。,(2) 外连接 外连接包括三种：,4.1.2 连 接 查 询,图4.20 外连接查询,例如：查找所有学生情况，及他们借阅图书的索书号，若学生未借阅任何图书，也要包括其情况。,4.1.2 连 接 查 询,例如：查找被借阅了的图书的借阅情况和所有的书名。,(3) 交叉连接,例如：列出借阅了书名中含有“计算机”的图书的学生的借书证号、姓名、专业名、所借图书的ISBN、书名
14、、索书号和借书时间。,4.1.2 连 接 查 询,图4.21 例4.58的执行结果,执行结果如图所示。,4.1.3 嵌 套 查 询,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。例如：,4.1.3 嵌 套 查 询,1. IN子查询,例如：查找与“李宏”在同一个专业的学生情况。,4.1.3 嵌 套 查 询,4.1.3 嵌 套 查 询,例如:查找未借阅“计算机网络”一书的学生情况,4.1.3 嵌 套 查 询,该语句的执行结果如下：,4.1.3 嵌 套 查 询,例如：查找其他专业比所有计算机专业的学生年龄都小的学生。,2. 比较子查询,4.1.3 嵌 套 查 询,例如：查
15、找其他专业比所有计算机专业某个学生年龄小的学生。,SELECT *,4.1.3 嵌 套 查 询,3. EXISTS子查询,例如：查找借阅了全部图书的同学的姓名。,4.1.3 嵌 套 查 询,例如：查询至少借阅了借书证号为“20000001”的同学所借阅的全部图书的学生的借书证号。,4.1.4 SELECT查询的其他子句,1. FROM子句,table_source指出了要查询的表或视图，格式为：,4.1.4 SELECT查询的其他子句,例如：在XS表中查找1983年1月1日以前出生的学生的姓名和专业名，分别使用别名stu_name和speciality表示。,4.1.4 SELECT查询的其他
16、子句,2. INTO子句,INTO子句的格式为：, INTO new_table ,例如：由XS表创建“计算机系学生借书证”表，包括借书证号和姓名。,4.1.4 SELECT查询的其他子句,3. UNION子句,使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其格式为：,4.1.4 SELECT查询的其他子句,例如：查询借阅了ISBN为“7-111-06359-7”或“7-115-10162-0”图书的学生的借书证号。,4.2 视 图,使用视图有下列优点：,4.2.1 创 建 视 图,1. 在SQL Server Enterprise Manager中创建视图(1)
17、打开SQL Server Enterprise Manager，在数据库XSCJ上单击鼠标右键，在弹出的快捷菜单上选择“新建”“视图”。,(2) 在所出现的窗口的第一个子窗口中单击鼠标右键，将弹出一个“增加表”快 捷菜单，如图4.22所示。,(3) 在“增加表”快捷菜单中选择“添加表”功能项，将出现“添加表”对话框。,(4) 在图4.22所示的窗口的第2个子窗口中选择创建视图所需的字段，可以指定列的别名、排序方式和规则（本例指定“专业名”字段的规则为“计算机”）等,4.2.1 创 建 视 图,4.2.1 创 建 视 图,(5) 上一步完成后，单击保存按钮，出现如图4.25所示的保存视图对话框，
18、在其中输入视图名，并单击“确定“按钮，便完成了视图的创建。,4.2.1 创 建 视 图,2. 通过视图向导创建视图,(1) 打开SQL Server Enterprise Manager,4.2.1 创 建 视 图,(2) 在欢迎使用向导界面中单击“下一步”，出现如图4.28所示的选择数据库对话框。,4.2.1 创 建 视 图,(4) 在图4.30所示的选择列对话框中选择视图所需要的列，单击“下一步”,(3) 在图4.29所示的选择表对话框中选择与视图相关联的表，单击“下一步”,4.2.1 创 建 视 图,图4.32 输入视图名 图4.33视图创建完成,(5) 在图4.31所示的视图限制对话框
19、中输入对视图的限制条件,(6) 在图4.32所示的输入视图名对话框中输入视图名，单击“下一步”,4.2.1 创 建 视 图,3. 使用CREATE VIEW语句创建视图,用CREATE VIEW语句创建视图CS_XS，其表示形式为：,4.2.1 创 建 视 图,例如： 创建CS_JY视图，包括计算机专业各学生的借书证号、其借阅图书的索书号及借书时间。,4.2.1 创 建 视 图,例如：定义学生所借图书总价值的视图,4.2.2 查 询 视 图,对LENDNUM视图进行查询：,例如：查找被借出数在3本或3本以上的图书的ISBN和借出数。,4.2.2 查 询 视 图,对TOTPRICE视图进行查询：
20、,例如：查找所借图书价值在100元以上的学生的借书证号和所借图书价值。,4.2.3 更 新 视 图,一个可更新视图可以是以下情形之一：,4.2.3 更 新 视 图,例如： 向计算机专业学生视图CS_XS中插入一个新的学生记录，借书证号为10000005，姓名为赵红平，性别为男，出生时间为1983-4-29。,1. 插入数据,4.2.3 更 新 视 图,例如：将计算机专业学生视图CS_XS中借书证号为“10000001”的学生姓名改为“李惠娟”。,2. 修改数据,3. 删除数据,例如：通过企业管理器的界面对视图XS12_VIEW进行如下操作：,4.2.3 更 新 视 图,4.2.3 更 新 视
21、图,4.2.4 修改视图的定义,1. 通过企业管理器修改视图,4.2.4 修改视图的定义,2. 使用ALTER VIEW语句修改视图,4.2.4 修改视图的定义,例如：将CS_XS视图修改为只包含计算机专业学生的借书证号、姓名和借书数。,4.2.4 修改视图的定义,例如：视图CS_JY是加密存储视图，修改其定义：包括借书证号、姓名、索书号、和借书时间。,4.2.5 删 除 视 图,删除视图的T-SQL语句是DROP VIEW，格式为： DROP VIEW view , n ,4.3 游 标,4.3.1 游 标 概 念,一个对表进行操作的T-SQL语句通常都可产生或处理一组记录，但是许多应用程序
22、，尤其是T-SQL嵌入到的主语言（如C、VB、PowerBuilder或其它开发工具）通常不能把整个结果集作为一个单元来处理，这些应用程序就需要一种机制来保证每次处理结果集中的一行或几行，游标（cursor）就提供了这种机制。,4.3.2 声 明 游 标,1. SQL-92语法,4.3.2 声 明 游 标,2. T-SQL扩展,4.3.2 声 明 游 标,4.3.3 打 开 游 标,例如：定义游标XS_CUR3，然后打开该游标，输出其行数。,4.3.4 读 取 数 据,FETCH语句的格式为：,4.3.4 读 取 数 据,例如：从游标XS_CUR1中提取数据。,4.3.4 读 取 数 据,例如：从游标XS_CUR2中提取数据。,4.3.4 读 取 数 据,4.3.5 关闭游标,4.3.6 删 除 游 标,4.4 综合应用训练,
