《数据库 第六章.ppt》由会员分享，可在线阅读，更多相关《数据库 第六章.ppt（56页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库原理与应用技术电子工业出版社电子工业出版社教学资源到华信教育资源网站教学资源到华信教育资源网站http:/或或http:/下载。下载。1/31/2023本章教学目标、重点和难点本章教学目标、重点和难点教学目标：使学生了解SQL Serve DBMS功能及特点，了解数据库对象，掌握SQL Server 2000的10种管理工具的功能和操作方法，熟练掌握使用Transact-SQL进行数据定义、数据查找、数据维护的方法。教学重点：SQL Serve功能及特点，数据库对象，SQL Server 2000的10种管理工具，Transact-SQL的数据定义、查找和维护的功能和表达方法。教学难点：
2、触发器的概念和定义方法，Transact-SQL的数据查找和数据维护的功能和表达方法。6.1 SQL Server DBS系统结构系统结构6.2 SQL Server DBMS管理功能管理功能6.3 SQL Server DBMS数据库操作数据库操作6.4 Transact-SQL语言语言6.5 SQL Server 2005新增功能新增功能 第第6章章 SQL Server数据库管理系统数据库管理系统6.1 SQL Server DBS系系统结统结构构 6.1.1 N-Tier6.1.1 N-Tier客户机客户机客户机客户机 服务器结构服务器结构服务器结构服务器结构1.桌面型数据库系统和客户
3、机桌面型数据库系统和客户机/服务器型数据库系统服务器型数据库系统桌桌面面型型数数据据库库系系统统：SQL Server和和数数据据库库都都安安装装在在客客户户端端计计算算机机中中。客客户户机机/服服务务器器型型数数据据库库系系统统：系系统统安安装装在在网网络络服服务务器器中，数据库为网络中的客户机应用程序共享。中，数据库为网络中的客户机应用程序共享。2.客户机客户机/服务器的结构和功能服务器的结构和功能通通过过网网络络连连接接产产品品将将多多台台计计算算机机连连接接为为企企业业内内部部网网，能能够够与与Internet相连并发布网页。相连并发布网页。3.N-Tier客户机客户机服务器结构服务器
4、结构客客户户机机应应用用程程序序负负责责建建立立用用户户界界面面，让让用用户户向向数数据据库库添添加加、修修改改、删删除除、查查询询数数据据；中中间间端端服服务务器器可可以以是是多多层层的的，管管理理一一个个或或多多个个独独立立的的数数据据库库，为为客客户户机机或或前前级级服服务务器器提提供供事事务务服服务务和和数数据据预预处处理理；后后端端服服务务器器是是中中间间服服务务器器的的后后台台，管管理理多多个个中中间间服务器，提供整个数据库系统的事务管理功能。服务器，提供整个数据库系统的事务管理功能。6.1.2.与与Internet高度集成功能高度集成功能 SQL Server 可以作为可以作为I
5、nternet或内部网服务或内部网服务器的后端数据库，它与器的后端数据库，它与Web服务器协作，为服务器协作，为Web服务器和浏览器提供信息。服务器和浏览器提供信息。SQL Server 通过通过Web站点共享数据，它可以发布站点共享数据，它可以发布Web网网页和接收电子邮件，使用页和接收电子邮件，使用Web浏览器可以直浏览器可以直接访问接访问SQL Server 数据库的数据，且易于学数据库的数据，且易于学习和使用。习和使用。6.1.4 数据数据库对库对象象1.数据库的类别数据库的类别(1)物理数据库和逻辑数据库物理数据库和逻辑数据库 物理数据库物理数据库由构成数据库的物理文件构成。一个物理
6、数据由构成数据库的物理文件构成。一个物理数据库中至少有一个数据库信息文件和一个数据库事务日志文库中至少有一个数据库信息文件和一个数据库事务日志文件。物理数据库由件。物理数据库由DBA负责创建和管理。负责创建和管理。逻辑数据库逻辑数据库是数据库中用户可视的表或视图，用户利用逻是数据库中用户可视的表或视图，用户利用逻辑数据库的数据库对象，存储或读取数据库中的数据。辑数据库的数据库对象，存储或读取数据库中的数据。(2)(2)系统数据库和用户数据库系统数据库和用户数据库系统数据库系统数据库是由系统创建和维护的数据库。系统数据库中是由系统创建和维护的数据库。系统数据库中记录着配置情况、任务情况和用户数据
7、库的情况等系统管记录着配置情况、任务情况和用户数据库的情况等系统管理的信息，它实际上就是我们常说的数据字典。理的信息，它实际上就是我们常说的数据字典。用户数据库用户数据库是根据管理对象要求创建的数据库，用户数据是根据管理对象要求创建的数据库，用户数据库中保存着用户直接需要的数据信息。库中保存着用户直接需要的数据信息。2.数据库对象数据库对象(1)表和视图：表和视图：表是在数据库中存放的实际关系。视图是为了用户查表是在数据库中存放的实际关系。视图是为了用户查询方便或根据数据安全的需要而建立的虚表。询方便或根据数据安全的需要而建立的虚表。(2)角色：角色：由一个或多个用户组成的单元，也称职能组。一
8、个用户可由一个或多个用户组成的单元，也称职能组。一个用户可以成为多个角色中的成员。以成为多个角色中的成员。(3)索引：索引：来加速数据访问和保证表的实体完整性。索引有群聚和非来加速数据访问和保证表的实体完整性。索引有群聚和非群聚索引群聚索引:群聚索引群聚索引会使表的物理顺序与索引顺序一致，一个表只会使表的物理顺序与索引顺序一致，一个表只能有一个群聚索引；能有一个群聚索引；非群聚索引非群聚索引与表的物理顺序无关，一个表可与表的物理顺序无关，一个表可以建立多个非群聚索引。以建立多个非群聚索引。(4)存存储过储过程程：通过：通过T-SQL编写的程序。包括：编写的程序。包括：系统存储过程系统存储过程是
9、由是由SQL Server 2000提供的，其过程名均以提供的，其过程名均以SP开头；开头；用户过程用户过程是由用是由用户编写的，它可以自动执行过程中安排的任务。户编写的，它可以自动执行过程中安排的任务。(5)触触发发器：器：一种特殊类型的存储过程，当表中发生特殊事件时执行。一种特殊类型的存储过程，当表中发生特殊事件时执行。触发器主要用于保证数据的完整性。触发器主要用于保证数据的完整性。(6)约约束：束：约束规则用于加强数据完整性。约束规则用于加强数据完整性。3.数据库结构(1)三种物理文件1)基本数据文件：用于容纳数据库对象，文件扩展名为.mdf。2)辅助数据文件：当数据库中的数据较多时需要
10、建立辅助数据文件。一个数据库中可以没有、也可以有一个或多个辅助数据文件。文件扩展名为.ndf。3)日志文件：用于存放数据库日志信息的文件。一个数据库可以有一个或多个日志文件。文件扩展名为.ldf。(2)数据库文件的两种组件1)页：使用的最小数据单元，一页可以容纳8k的数据。共有8种页：数据页、索引页、文本/图像页、全局分配映射表页、页空闲空间、索引分配映射表页、大容量更改映射表页和差异更改映射表页。2)扩展盘区：扩建表和索引的基本单位，一个扩展盘区由8个相邻页的构成。(3)文件组：多个文件可以归纳成为一个文件组。6.1.5 6.1.5 系统数据库系统数据库1.Master数据库数据库主文件名为
11、主文件名为Master.mdf，日志文件为日志文件为Masterlog.ldf。库中。库中内含许多系统表，跟踪和记录内含许多系统表，跟踪和记录SQL Server相关信息。相关信息。2.Msdb数据数据库库主文件名为主文件名为Msdb.dbf，日志文件名为日志文件名为Msdb.ldf。Msdb由企由企业管理器和代理服务器使用业管理器和代理服务器使用,记录着任务计划信息、事件处记录着任务计划信息、事件处理信息、数据备份及恢复信息和警告及异常信息。理信息、数据备份及恢复信息和警告及异常信息。3.Model数据数据库库主文件是主文件是model.mdf，日志文件为日志文件为model.ldf。Mod
12、el数据库是数据库是为用户数据库提供的样板，新的用户数据库都以为用户数据库提供的样板，新的用户数据库都以model数据数据库为基础。库为基础。4.tempdb数据数据库库主文件名和日志文件名分别为主文件名和日志文件名分别为tempdb.dbf和和tempdb.ldf。tempdb是一个共享的工作空间，所有数据库都可以使用它，是一个共享的工作空间，所有数据库都可以使用它，为临时表提供了一个存储区。为临时表提供了一个存储区。6.2 SQL Server DBMS的管理工具1.查询分析器查询分析器编辑编辑Transact-SQL，将其发送到服务器，并将执行结果及分析显示出将其发送到服务器，并将执行结
13、果及分析显示出来。通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，来。通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能。并可以实现自动建立索引的功能。2.导入和导出数据导入和导出数据通过一个向导程序通过一个向导程序“数据转换服务（简称数据转换服务（简称DTS）”实现，其作用是使实现，其作用是使SQL Server 与任何与任何OLE DB、ODBC、JDBC或文本文件等多种不同类型的数或文本文件等多种不同类型的数据库之间实现数据传递。据库之间实现数据传递。3.服务管理器服务管理器 服务管理器（服务管理器（Service Manger）用于开启
14、、暂停和停止用于开启、暂停和停止SQL服务服务工作，同时，也可以通过它设置服务是否自动启动。工作，同时，也可以通过它设置服务是否自动启动。4.客户机网络连接工具和服务器网络连接工具客户机网络连接工具和服务器网络连接工具客户机网络连接工具用于设置本机作为服务器或客户机所使客户机网络连接工具用于设置本机作为服务器或客户机所使用的缺省网络链接库用的缺省网络链接库，并可为不支持缺省网络库的并可为不支持缺省网络库的SQL服务服务器设置连接方式。服务器网络连接工具用于设置本机作为服器设置连接方式。服务器网络连接工具用于设置本机作为服务器时允许的连接协议，以支持不同配置的客户端。务器时允许的连接协议，以支持
15、不同配置的客户端。5.联机丛书联机丛书为用户提供的在线帮助功能。在联机丛书中，全面而详细地为用户提供的在线帮助功能。在联机丛书中，全面而详细地介绍了有关介绍了有关SQL Server2000的功能、管理及开发方法、的功能、管理及开发方法、T-SQL语法等。语法等。6.企业管理器企业管理器企业管理器可以实现对管理对象的定义、改动、删除等各种操企业管理器可以实现对管理对象的定义、改动、删除等各种操作。窗口的左边是以树形结构组织的管理对象，右边称为明细作。窗口的左边是以树形结构组织的管理对象，右边称为明细窗口，其内容是是左边选中对象的详细内容。窗口，其内容是是左边选中对象的详细内容。7.事件探查器事
16、件探查器把一个操作序列保存为一个把一个操作序列保存为一个.trc文件。用于监视与分析文件。用于监视与分析SQL Server活动、服务器的网络进出流量或发出在活动、服务器的网络进出流量或发出在SQL Server上上的事件。的事件。8.在在IIS中配置中配置SQL XML支持支持含有含有SQL Server的的IIS虚拟目录管理器，通过它在虚拟目录管理器，通过它在IIS定义一个定义一个与与SQL Server 2000实例相关的虚拟目录。实例相关的虚拟目录。Internet应用程序便应用程序便可以编写出能够引用虚拟目录。可以编写出能够引用虚拟目录。9.分析管理器分析管理器访问分析服务器及元数据
17、知识库的控制台，实现：管理分析服访问分析服务器及元数据知识库的控制台，实现：管理分析服务器；创建数据库和数据源；生成并处理多维数据集；创建并务器；创建数据库和数据源；生成并处理多维数据集；创建并处理数据挖掘模型；指定存储选项并优化查询性能；管理安全处理数据挖掘模型；指定存储选项并优化查询性能；管理安全性；浏览数据源、共享维度、安全角色和其它对象。性；浏览数据源、共享维度、安全角色和其它对象。6.3 SQL Server DBMS数据库操作工具6.3.1 用企业管理器创建数据库用企业管理器创建数据库1)选择企业管理器。选择企业管理器。2)选中需要在其上创建数据库的服务器选中需要在其上创建数据库的
18、服务器3)选中选中“数据库数据库”文件夹，单击右键，在弹出上选择文件夹，单击右键，在弹出上选择“新建数新建数据库据库”。在常规页面中，输入数据库名，选择。在常规页面中，输入数据库名，选择SQL 服务器。服务器。4)选择数据文件页面，输入数据文件属性；再选择事物日志选择数据文件页面，输入数据文件属性；再选择事物日志页面，输入数据库的日志文件属性。页面，输入数据库的日志文件属性。5)单击单击“确定确定”按钮，关闭对话框。按钮，关闭对话框。6.3.2 在企业管理器中定义和管理表在企业管理器中定义和管理表 1.新建表新建表选中数据库中的表文件夹，击鼠标右健。在弹出的选中数据库中的表文件夹，击鼠标右健。
19、在弹出的菜单中选择新建表。随后输入表结构对话框。菜单中选择新建表。随后输入表结构对话框。在对话框中单击右键，出现一个弹出框在对话框中单击右键，出现一个弹出框。字段输入完后，关闭建表对话框。在对话框中输入表名，单击字段输入完后，关闭建表对话框。在对话框中输入表名，单击“确定确定”按钮。按钮。2.定义表的完整性约束和索引(1)定义索引和键定义索引和键(2)定义表间关联定义表间关联选择选择“索引索引/键键”页面，选择表页面页面，选择表页面 6.4 Transact-SQL语言 6.4.1 数据定义语言定义语言1.创建和管理数据库创建和管理数据库 CREATE DATABASE数据库名数据库名 ON
20、PRIMARY(NAME=逻辑数据文件名逻辑数据文件名，FILENAME=操作数据文件路径和文件名操作数据文件路径和文件名 ，SIZE=文件长度文件长度 ，MAXSIZE=最大长度最大长度 ，FILEROWTH=文件增长率文件增长率)，n LOG ON(NAME=逻辑日志文件名逻辑日志文件名，FILENAME=操作日志文件路径和文件名操作日志文件路径和文件名 ，SIZE=文件长度文件长度)，n FOR RESTORE 句中：句中：PRIMARY指明主文件；指明主文件；SIZE说明文件的大小，默认为说明文件的大小，默认为3MB；FILEROWTH说明文件增长率，默认为说明文件增长率，默认为10%
21、。FOR RESTORE子句说子句说明重建数据库（用于数据恢复）。明重建数据库（用于数据恢复）。2.定义表定义表CREATE TABLE 库名库名 表名表名(列名列名数据类型数据类型|AS表达式表达式 字段约束字段约束，记录约束记录约束)，标准标准 SQL 格式为：格式为：CREATE TABLE 库名库名表名表名(列名列名数据类型数据类型列级约束条件列级约束条件 ，n ，表级约束条件表级约束条件 ，n)；(2)(2)记录约束：CONSTRAINT 1)PRIMARY KEY CLUSTERED|NON CLUSTERED(列列名组名组)2)FOREIGN KEY()REFERENCES()3
22、)CHECK(条件表达式条件表达式)：定义记录应满足的条件。：定义记录应满足的条件。4)UNIQUE(列组列组)：定义不允许重复值的字段组。：定义不允许重复值的字段组。(1)字段约束字段约束1)NOT NULL|NULL：不允许或允许字段值为空。不允许或允许字段值为空。2)PRIMARY KEY CLUSTERED|NON CLUSTERED3)REFERENCE()：定义参照完整条件。：定义参照完整条件。4)DEFAULT缺省值缺省值：定义字段的缺省值。：定义字段的缺省值。5)CHECK(条件条件)：定义字段应满足的条件表达式。：定义字段应满足的条件表达式。6)IDENTITY(，)：定义字
23、段初始值和步长值。：定义字段初始值和步长值。3.基本表的维护(1)修改基本表修改基本表1)修改字段的定义。修改字段的定义。ALTER TABLE表名表名 ALTER COLUMN列名列名新类型新类型 NULL|NOT NULL约束定义约束定义2)增加字段和表约束规则。增加字段和表约束规则。ALTER TABLE表名表名ADD列定义列定义|表约束定表约束定义义 3)删除字段或约束规则。删除字段或约束规则。ALTER TABLE表名表名DROPCONSTRAINT约束约束名名|COLUMN列名列名4)使约束有效或无效。使约束有效或无效。ALTER TABLE表名表名CHECK|NOCHECK CO
24、NSTRAINT ALL|约束名组约束名组(2)删除基本表删除基本表:DROP TABLE表名表名 修改基本表为：修改基本表为：ALTER TABLE表名表名 ADD(新列名新列名数据类型数据类型完整性约束完整性约束，n)DROP完整性约束名完整性约束名 MODIFY(列名列名数据类型数据类型，n)；删除基本表为：删除基本表为：DROP TABLE表名表名；标准标准 SQL 格式为：格式为：4.创建和管理索引 (1)创建索引创建索引 CTEATE UNIQUE CLUSTERED|NON CLUSTERED INDEX索引名索引名ON表表名名(索引列组索引列组)(2)删除索引删除索引 DROP
25、 INDEX表名表名.索引名索引名 5.创建和管理视图 (1)创建视图创建视图创建视图的语法为：创建视图的语法为：CREATE VIEW视图名视图名(列名组列名组)WITH ENCRYPTIOPN AS子查询子查询 WITH CHECK OPTION(2)删除视图删除视图 DROP VIEW 视图名组视图名组 6.创建和管理缺省(1)创建缺省创建缺省 CREATE DEFAULT缺省名缺省名(2)绑定缺省绑定缺省 EXEC sp_bindfault 缺省名缺省名，表名表名.列名列名系统存储过程执行时的格式为：系统存储过程执行时的格式为：EXEC存储过程名存储过程名参数组参数组 如果参数是常量，
26、则要加定界符如果参数是常量，则要加定界符 。(3)解除缺省解除缺省EXEC sp_unbinddefault ，.(4)删除缺省删除缺省 DROP DEFAULT 缺省名组缺省名组7.创建和管理规则(1)创建规则创建规则 CREATE RULE规则名规则名AS规则表达式规则表达式规则表达式是规则表达式是WHERE子句中的有效表达式。子句中的有效表达式。(2)绑定规则绑定规则 EXEC sp_bindrule 规则名规则名，对象对象名名(3)解除规则解除规则EXEC sp_unbindrule ，(4)删除规则删除规则 DROP RULE规则名组规则名组 8.创建和管理存储过程(1)创建存储过程
27、和调用存储过程 CREATE PROCEDURE过程名过程名;版本号版本号 =OUTPUTWITH RECOMPILE|ENCRYPTION|RECOMPILE ASSQL语句组语句组说明：说明：版本号是整数版本号是整数;OUTPUT为返回值；为返回值；RECOMPILE为重编译；为重编译；ENCYPTION为加密选项；参数前加为加密选项；参数前加“”为局部变量，加为局部变量，加“”则说明则说明为全局变量。为全局变量。(2)过程的调用语句 EXE=参数参数(3)删除存储过程 DROP PROCEDURE存储过程名组存储过程名组 9.创建和管理触发器CREATE TRIGGER ON FOR D
28、ELETE，INSERT，UPDATE ASSQL语句组语句组1)DELETE触发器是当对表执行触发器是当对表执行DELETE操作时，将删除的元操作时，将删除的元组放入组放入deleted表中，检查表中，检查delete表中的数据，确定该如何处理。表中的数据，确定该如何处理。2)INSERT触发器在对表执行触发器在对表执行INSERT操作时操作时,将插入数据送入将插入数据送入inserted表中，根据表中，根据inserted表中的值决定如何处理。表中的值决定如何处理。3)UPDATE触发器仅在更新数据操作时，原数据移入触发器仅在更新数据操作时，原数据移入deleted表表中，将新数据备份送入
29、中，将新数据备份送入inserted表中，对表中，对deleted和和inserted表进表进行检查，并决定如何处理。行检查，并决定如何处理。【例例】设有成员、借书和预定书表。通过触发器定义未还图书设有成员、借书和预定书表。通过触发器定义未还图书的成员不能从成员表中删除，当删除成员时，该成员在的预定的成员不能从成员表中删除，当删除成员时，该成员在的预定书表中的记录也将全部被删除。书表中的记录也将全部被删除。CREATE TRIGGER成员成员_删除 ON 成员成员 FOR DELETE AS IF(SELECT COUNT(*)FROM借书借书，deleted WHERE 借书借书.成员编号成
30、员编号=deleted.成员编号成员编号)0 ROLLBACK TRANSACTION ELSE DELETE预定书预定书 FROM预定书预定书，deleted WHERE预定书预定书.成员编号成员编号=deleted.成员编号成员编号6.4.2 数据操纵语言数据操纵语言1.数据检索语句的语句格式数据检索语句的语句格式 SELECT查询列查询列 INTO新表名新表名 FROM数据源数据源 WHERE元组条件表达式元组条件表达式 GROUP BY HAVING ORDER BY COMPUTE统计列组统计列组BY表达式表达式标准标准SQL语句语句 SELECT目标列组目标列组 FROM数据源数据
31、源 WHERE元组选择条件元组选择条件 GROUP BY HAVING ORDER BY ，n；（1）SELECT子句SELECT ALL|DISTINCT TOP PERCENT查询列为：查询列为：查询列查询列:=*|表或视图表或视图.*|列名或表达式列名或表达式AS列别名列别名|列别名列别名=表达式表达式1)ALL|DISTINCT：ALL为所有行为所有行,DISTINCT为结果中仅有唯一行。为结果中仅有唯一行。ALL是缺省值。是缺省值。2)TOP数值数值：结果中的前：结果中的前数值数值行。行。有有PENCENT，则返回则返回结果中的百分之结果中的百分之数值数值行记录。行记录。3)“*”：
32、指全部列。：指全部列。4)表或视图表或视图.*：指定表或视图的全部列。：指定表或视图的全部列。5)列别名列别名：用来代替出现在结果集中的列名或表达式，别名可：用来代替出现在结果集中的列名或表达式，别名可以在以在ORDER BY子句中出现。子句中出现。(2)INTO子句和FROM子句 INTO子句将查询结果加到创建表中。创建表是临时表时表名前子句将查询结果加到创建表中。创建表是临时表时表名前加加“#”字。字。FROM语法格式为：语法格式为：FROM数据源组数据源组数据源的语法为：数据源的语法为：数据源数据源:=表名表名AS表别名表别名|视图名视图名AS视图别名视图别名|嵌套的嵌套的SELECT语
33、句语句AS别名别名|连连接表接表连接表的语法为：连接表的语法为：连接表连接表:=ON|CROSSJOIN|:=INNER|LEFT|RIGHT|FULLOUTER JOIN笛卡儿积运算笛卡儿积运算内连接内连接左外连接左外连接全外连接全外连接右外连接右外连接(4)WHERE子句：子句：WHERE|(5)GROUP BY子句：子句：GROUP BY ALL分组表达式组分组表达式组(6)HAVING子句：子句：HAVING分组或汇总筛选条件分组或汇总筛选条件(7)ORDER BY：ORDER BY排序项排序项ASC|DESC，n(8)COMPUTE子句。子句。产生汇总值产生汇总值,在结果集中后将汇总
34、值放入摘要列，在结果集中后将汇总值放入摘要列，COMPUTE与与BY配合，起到换行控制和分段小计的作用。配合，起到换行控制和分段小计的作用。COMPUTE统计函数组统计函数组BY分组项分组项 其中：其中：BY分组项分组项表示在结果集中产生换行控制及分段小表示在结果集中产生换行控制及分段小计。计。COMPUTE BY必须和必须和ORDER BY配合使用。配合使用。2 Transact-SQL查询实例设图书借阅数据库中包括：设图书借阅数据库中包括：图书图书(书号，类别，出版社，作者，书名，定价书号，类别，出版社，作者，书名，定价)；读者读者(书证号，姓名，单位，性别，电话书证号，姓名，单位，性别，
35、电话)；借阅借阅(书号，读者书证号，借阅日期书号，读者书证号，借阅日期).【例例】将计算机类的书存入永久的计算机图书表，将借书将计算机类的书存入永久的计算机图书表，将借书日期在日期在99年以前的借阅记录存入临时的超期借阅表。年以前的借阅记录存入临时的超期借阅表。SELECT*INTO 计算机图书计算机图书 FROM 图书图书 WHERE 类别类别=计算机计算机 GO SELECT*INTO#超期借阅超期借阅 FROM 借阅借阅 WHERE 借阅日期借阅日期1999-01-01 GO查询例子【例例】查找类别中最高的图书定价不低于全部按类别分组的图查找类别中最高的图书定价不低于全部按类别分组的图书
36、平均定价的书平均定价的2倍的图书。倍的图书。SELECT A.*FROM 图书图书 A GROUP BY A.类别类别 HAVING MAX(A.定价定价)=ALL(SELECT 2*AVG(B.定价定价)FROM 图书图书 B GROUP BY B.类别类别)【例例】列出计算机类图书的书号、名称及价格，并求出各出版列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。社这类书的总价格，最后求出全部册数和总价格。SELECT书号，名称，定价书号，名称，定价 FROM 图书图书 WHERE 类别类别=计算机类计算机类 ORDER BY 书号书号 ASC CO
37、MPUTE COUNT(*)，SUM(定价定价)BY 出版社出版社 COMPUTE COUNT(*)，SUM(定价定价)6.4.3 6.4.3 数据控制机制和语句（书中数据控制机制和语句（书中5.65.6）1 数据控制机制（1）授权定义 具有授权资格的用户，如DBA或DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。（2）存权处理 DBMS把授权的结果编译后存入数据字典中。（3）查权操作 当提出操作请求时，系统在数据字典中查找该用户的数据操作权限，当拥有该操作权时执行其操作，否则系统将拒绝其操作。2 2 数据控制语句数据控制语句（1）授权语句）授权语句 GRANT系统特权组系统特权
38、组To用户组用户组|PUBLIC WITH GRANT OPTION；1)PUBLIC指数据库的所有用户。指数据库的所有用户。2)WITH GRANT OPTION:获得权限的用户可以把该权限再获得权限的用户可以把该权限再授予别的用户。授予别的用户。GRANT ALL PRIVILIGES|ON TO用户组用户组|PUBLIC WITH GRANT OPTION；1)ALL PRIVILIGES指所有的对象特权。指所有的对象特权。2)对象名指操作的对象标识，如表名、视图名和过程名等。对象名指操作的对象标识，如表名、视图名和过程名等。例子例子【例例】把修改学生学号和查询学生表的权力授予用户王平。
39、把修改学生学号和查询学生表的权力授予用户王平。GRANT UPDATE(学号学号)，SELECT ON 学生学生 TO 王平；王平；【例例】把建立数据库和备份数据库的权力赋给用户王平。把建立数据库和备份数据库的权力赋给用户王平。GRANT CREATE DATABASE，BACKUP DATABASE TO 王平；王平；(2)收权语句REVOKE语句的一般格式为：语句的一般格式为：REVOKE权限组权限组|ALL PRIVILIGES ON对象名对象名 FROM用户名组用户名组|PUBLIC；其中：其中：ON子句用于指定被收回特权的对象；子句用于指定被收回特权的对象；ALL PRIVILIGE
40、S指收回所有特权；指收回所有特权；PUBLIC指所有用户指所有用户【例例】将用户王平的将用户王平的“在学生表中修改学生学号在学生表中修改学生学号”的权利收回。的权利收回。REVOKE UPDATE(学号学号)ON 学生学生 FROM 王平；王平；（3）拒绝访问语句拒绝访问语句的一般格式为：拒绝访问语句的一般格式为：DENY ALL PRIVILIGES|ON TO 用户组用户组|PUBLIC；其中：其中：ON子句用于说明对象特权的对象名；对象名指的子句用于说明对象特权的对象名；对象名指的是表名、视图名、视图和表的字段名或者过程名。是表名、视图名、视图和表的字段名或者过程名。6.4.4 嵌入式S
41、QL(书中 5.5）1 嵌入式嵌入式SQL的特点的特点（1）嵌入式嵌入式SQL应注意的问题应注意的问题SQL和主语言的配合问题；和主语言的配合问题；合理选择主语言。合理选择主语言。（2）SQL嵌入主语言时必须解决的三个问题嵌入主语言时必须解决的三个问题1)如何区别如何区别SQL和主语言和主语言2)使数据库的工作单元与程序工作单元之间能够通信使数据库的工作单元与程序工作单元之间能够通信主语言通过主变量向主语言通过主变量向SQL语句提供参数；语句提供参数；SQL语句的当前语句的当前工作状态和运行环境数据要返馈给应用程序。工作状态和运行环境数据要返馈给应用程序。3)使用游标解决使用游标解决SQL一次
42、一集合的操作与主语言一次一记一次一集合的操作与主语言一次一记录操作的矛盾录操作的矛盾2 2 不用游标的不用游标的SQLSQL语句语句（1）几种不需要使用游标的几种不需要使用游标的SQL语句语句1)用于说明主变量的说明性语句用于说明主变量的说明性语句 SQL的说明性语句主要有两条：的说明性语句主要有两条：EXEC SQL BEGIN DECLARE SECTION；EXEC SQL END DECLARE SECTION；2)数据定义和数据控制语句数据定义和数据控制语句3)查询结果为单记录的查询语句查询结果为单记录的查询语句4)数据的插入语句和某些数据删除、修改语句数据的插入语句和某些数据删除、
43、修改语句独立的数据删除和修改语句不需要使用游标；与查询语句配独立的数据删除和修改语句不需要使用游标；与查询语句配合，删除或修改查询到的当前记录的操作，与游标有关。合，删除或修改查询到的当前记录的操作，与游标有关。（2 2）不用游标的查询语句）不用游标的查询语句EXEC SQL SELECT ALL|DISTINCT目标列表达式目标列表达式，n INTO主变量主变量指示变量指示变量，n FROM表名或视图名表名或视图名，n WHERE条件表达式条件表达式；1)在语句开始前要加在语句开始前要加EXEC SQL前缀。前缀。2)该查询语句中又扩充了该查询语句中又扩充了INTO子句。子句。3)在在WHE
44、RE子句的条件表达式中可以使用主变量。子句的条件表达式中可以使用主变量。4)由于查询结果只有一条记录，不必有排序和分组子句。由于查询结果只有一条记录，不必有排序和分组子句。5)INTO子句中的主变量后面跟有指示变量时：值为子句中的主变量后面跟有指示变量时：值为NULL，指指示变量为负值，结果不向主变量赋值；传递正常，指示变量为示变量为负值，结果不向主变量赋值；传递正常，指示变量为0；主变量宽度不够，指示变量的值为数据截断前的宽度。；主变量宽度不够，指示变量的值为数据截断前的宽度。【例例】查询学号为主变量查询学号为主变量givesno、课号为主变量课号为主变量givecno的值的值的学生选课记录
45、。的学生选课记录。EXEC SQL SELECT 学号，课程号，成绩学号，课程号，成绩 INTO:Sno，:Cno，:grade:gradeid FROM 选课选课 WHERE 学号学号=:givesno AND 课程号课程号=:givecno；（3）不用游标的数据维护语句不用游标的数据维护语句【例例】删除学号由主变量删除学号由主变量Sno决定的学生记录。决定的学生记录。EXEC SQL DELETE FROM 学生学生 WHERE 学号学号=:Sno；【例例】将计算机系所有学生的年龄都加上主变量将计算机系所有学生的年龄都加上主变量Raise。EXEC SQL UPDATE 学生学生 SET
46、年龄年龄=年龄年龄+:Raise WHERE 所在系所在系=计算机系计算机系；【例例】将计算机系学生的年龄置空。将计算机系学生的年龄置空。Raiseid=-1 EXEC SQL UPDATE 学生学生 SET 年龄年龄=年龄年龄+:Raise:Raiseid；【例例】将将学学号号为为主主变变量量Sno、课课程程号号为为Cno的的选选课课记记录录，插插到到库库中。中。EXEC SQL INSERT INTO 选课选课 VALUES(:Sno，:Cno)；例子例子3 3 使用游标的使用游标的SQLSQL(1)定义游标命令定义游标命令 EXEC SQL DECLARE游标名游标名CURSOR FOR
47、子查询子查询 FOR UPDATE OF字段名字段名1，n；删除和修改数据的语句中，删除和修改数据的语句中，WHERE为：为：WHERE CURRENT OF游标名游标名【例例】定义按主变量定义按主变量DEPT查询系里学生的游标。查询系里学生的游标。EXEC SQL DECLARE SX CURSOR FOR SELECT*FROM 学生学生 WHERE 所在系所在系=:DEPT；修改和删除时修改和删除时用游标用游标(2)(2)打开、推进和关闭游标命令打开、推进和关闭游标命令EXEC SQL OPEN 游标名游标名；执行对应的查询语句，并将游标指向结果集的第一条记录前。执行对应的查询语句，并将
48、游标指向结果集的第一条记录前。打开的游标处于活动状态，可以被推进。打开的游标处于活动状态，可以被推进。EXEC SQL FETCH游标名游标名INTO主变量组主变量组；将游标下移一行，读出当前的记录，将当前记录的各数据项将游标下移一行，读出当前的记录，将当前记录的各数据项值放到值放到INTO后的主变量组中。后的主变量组中。EXEC SQL CLOSE 游标名游标名；【例例】打开打开SX游标。游标。DEPT=计算机系计算机系 EXEC SQL OPEN SX；【例例】将打开的指向系的游标向前推进。将打开的指向系的游标向前推进。EXEC SQL FETCH SX INTO:Sno，:Sname，:Sage，:Sdept；例子