《SQLServer关系数据库管理系统.ppt》由会员分享，可在线阅读，更多相关《SQLServer关系数据库管理系统.ppt（71页珍藏版）》请在三一办公上搜索。
1、第6章 SQL Server 关系数据库管理系统,SQL Server 系统结构SQL Server 的管理功能SQL Server 数据库操作工具Transact-SQL功能及实例,SQL Server是由Microsoft开发和推广的关系数据库管理系统（RDBMS）它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。1996年，SQL Server 6.5版本；1998年，SQL Server 7.0版本；2000年，SQL Server 2000。SQL Server2005,SQL Server2008,SQL
2、Server2010,（1）企业版（Enterprise Edition）支持所有的SQL Server 特性，可作为大型Web 站点、企业OLTP（联机事务处理）以及数据仓库系统等的产品数据库服务器。（2）标准版（Standard Edition）用于小型的工作组或部门。（3）个人版（Personal Edition）用于单机系统或客户机。（4）开发者版（Developer Edition）用于程序员开发应用程序，这些程序需要SQL Server 2000 作为数据存储设备。此外，SQL Server 还有Desktop Engine（桌面引擎）和Windows CE 版，用户可以根据实际情
3、况选择所要安装的SQL Server 版本。,6.1 SQL Server 系统结构,1.桌面型数据库系统和客户机/服务器型数据库系统桌面型数据库系统：SQL Server 安装在客户端计算机中，数据库存储在客户端计算机中。客户机/服务器型数据库系统：系统安装在网络服务器中，数据库为网络中的客户机应用程序共享。2.客户机/服务器的结构和功能 可以通过网络连接产品将多台计算机连接为企业内部网，能够与Internet相连并发布网页。,3.N-Tier客户机服务器结构 后端服务器、中间端（应用）服务器和客户机：客户机应用程序负责建立用户界面，通过用户界面让用户向数据库添加、修改、删除、查询数据；数据
4、库服务器执行数据库的存储、检索、管理、安全性及数据备份工作；中间端服务器可以是多层的，管理一个或多个独立的数据库，为客户机或前级服务器提供事务服务和数据预处理。后端服务器是中间服务器的后台，管理多个中间服务器，提供整个数据库系统的事务管理功能。,与Internet集成功能,SQL Server 可以作为Internet或内部网服务器的后端数据库，它与Web服务器协作，为Web服务器和浏览器提供信息。SQL Server 通过Web站点共享数据，它可以发布Web网页和接收电子邮件，使用Web浏览器可以直接访问SQL Server 数据库的数据，且易于学习和使用。,客户机/服务器软件结构,Rela
5、tional DatabaseManagement System,SQL Server,Client,Results,Client Application,OLAP,OLTP,Query,SQL SERVER构成,Client Application,Client Net-Library,Client,SQL Server,Relational Engine,Storage Engine,Server,LocalDatabase,Database API(OLE DB,ODBC,DB-Library),Processor,Memory,Open Data Services,Server Net
6、-Libraries,Client-Server Communication Process,Query,Result Set,Result Set,1,2,3,4,5,客户机软件,SQL客户机用于访问SQL服务器及数据库。客户端软件主要包括：应用程序服务器的数据库应用程序编程接口（API）包括OLE DB,ODBC,DB-Library,Embedded SQL网络资源库（Net-Library)动态链接库，同时为客户端和服务器端加载，使得客户端和服务器端以一个通用的方式进行通信。,服务器端软件,服务器端软件包括：网络链接库（Net-Library)开放式数据服务（ODS）SQL Serve
7、r代理服务程序（SQL Agent Service)SQL Server服务器服务（SQL Server Service）分布任务管理服务,6.1.4 数据库对象,1.数据库的类别(1)物理数据库和逻辑数据库 物理数据库由构成数据库的物理文件构成。SQL Server 的一个物理数据库中至少有一个数据库信息文件和一个数据库事务日志文件。物理数据库由DBA（数据库管理员）负责创建和管理。逻辑数据库是数据库中用户可视的表或视图，用户利用逻辑数据库的数据库对象，存储或读取数据库中的数据。,(2)系统数据库和用户数据库 系统数据库是由系统创建和维护的数据库。系统数据库中记录着SQL Server 的配
8、置情况、任务情况和用户数据库的情况等系统管理的信息，它实际上就是我们常说的数据字典。用户数据库是根据管理对象要求创建的数据库，用户数据库中保存着用户直接需要的数据信息。,数据库的逻辑组件(数据库对象),SQL Server中的数据库,Sales数据库,SQL Server,表,视图,数据库的物理实现(数据库文件),SalesDat1.mdf,SalesDat2.ndf,SalesLog1.ldf,主数据文件,次数据文件,日志文件,索引,存储过程,用户视图,物理视图,数据库的用户视图和物理视图,2.SQL Server 的数据库对象,(1)表和视图：表是在数据库中存放的实际关系。视图是为了用户查
9、询方便或根据数据安全的需要而建立的虚表。(2)角色：由一个或多个用户组成的单元，也称职能组。一个用户可以成为多个角色中的成员。(3)索引：来加速数据访问和保证表的实体完整性的数据库对象。(4)存储过程：通过Transact-SQL编写的程序。包括系统存储过程和用户存储过程：系统存储过程是由SQL Server 2000提供的，其过程名均以SP开头；用户过程是由用户编写的，它可以自动执行过程中安排的任务。(5)触发器：一种特殊类型的存储过程，当表中发生特殊事件时执行。触发器主要用于保证数据的完整性。(6)约束：约束规则用于加强数据完整性。,3.数据库结构,(1)SQL Server 的三种物理文
10、件1)基本数据文件：基本数据文件用于容纳数据库对象，它使用.mdf作为文件扩展名。2)辅助数据文件：当数据库中的数据较多时需要建立辅助数据文件。一个数据库中可以没有、也可以有一个或多个辅助数据文件。辅助数据文件的扩展名为.ndf。3)日志文件：用于存放数据库日志信息的文件。一个数据库可以有一个或多个日志文件。日志文件的扩展名为.ldf。,(2)数据库文件的两种组件1)页：使用的最小数据单元，一页可以容纳8k的数据。共有8种页：数据页、索引页、文本/图像页、全局分配映射表页、页空闲空间、索引分配映射表页、大容量更改映射表页和差异更改映射表页。2)扩展盘区：扩建表和索引的基本单位，一个扩展盘区由8
11、个相邻页构成。(3)文件组：多个文件可以归纳成为一个文件组。,文件组 SQL Server允许对文件进行分组，以便于管理和数据的分配放置。所有数据库都至少包含一个主文件组，所有系统表都分配在主文件组中。用户可以定义额外的文件组。数据库首次创建时，主文件组是默认文件组；可以使用 ALTER DATABASE语句将用户定义的文件组指定为默认文件组。创建时没有指定文件组的用户对象的页将从默认文件组分配。在使用文件组时，应当注意以下几个准则：文件或文件组不能由一个以上的数据库使用。文件只能是一个文件组的成员。数据和事务日志信息不能属于同一文件或文件组。事务日志文件不能属于任何文件组。,SQL Serv
12、er中的数据库文件,主数据文件*.mdf仅有一个,事务日志文件*.ldf一到多个,次数据文件*.ndf零到多个,次数据文件*.ndf零到多个,(仅有一个)主文件组,(零到多个)次文件组,事务日志,一个数据库的文件集,SQL Server的系统数据库 数据库服务器上自动建立了6个数据库，其中有4个系统数据库、两个示例数据库。系统数据库:master数据库tempdb数据库model数据库msdb 数据库示例数据库:northwind数据库pubs数据库,1.Master数据库Master数据库的主文件名为Master.mdf，日志文件为Masterlog.ldf。Master中内含许多系统表，用
13、来跟踪和记录SQL Server相关信息。2.Msdb数据库Msdb的主文件名为Msdb.dbf，日志文件名为Msdb.ldf。Msdb由SQL Server企业管理器和代理服务器使用。Msdb中记录着任务计划信息、事件处理信息、数据备份及恢复信息和警告及异常信息。3.Model数据库Model数据库的主文件是model.mdf，日志文件为model.ldf。为用户数据库提供的样板，新的用户数据库都以model数据库为基础。4.tempdb数据库tempdb的主文件名和日志文件名分别为tempdb.dbf和tempdb.ldf。tempdb是一个共享的工作空间，所有数据库都可以使用它，它为临时
14、表和其他临时工作提供了一个存储区。,SQL Server的管理功能,服务器的安装、配置和管理服务管理数据库访问服务、服务代理、分布式事务处理协调服务数据库管理数据库的创建、修改、删除、备份和恢复数据库对象的管理各种对象的创建、修改和删除数据管理数据的增删改查，数据的传递和复制安全性管理用户和权限管理作业管理集成管理,SQL Server 的管理工具,1.查询分析器编辑Transact-SQL，将其发送到服务器，并将执行结果及分析显示出来。通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能。,2.导入和导出数据,导入和导出数据通过一个向导程序“数据转换服务（
15、简称DTS）”实现，其作用是使SQL Server 与任何OLE DB、ODBC、JDBC或文本文件等多种不同类型的数据库之间实现数据传递。,3.服务管理器,服务管理器（Service Manger）用于开启、暂停和停止SQL服务工作，同时，也可以通过它设置服务是否自动启动。,4.客户机网络连接工具和服务器网络连接工具,客户机网络连接工具用于设置本机作为服务器或客户机所使用的缺省网络链接库，并可为不支持缺省网络库的SQL服务器设置连接方式。服务器网络连接工具用于设置本机作为服务器时允许的连接协议，以支持不同配置的客户端。,5.联机丛书,联机丛书是SQL Server 2000为用户提供的在线帮
16、助功能。在联机丛书中，全面而详细地介绍了有关SQL Server2000的功能、管理及开发方法、TSQL语法等。,6.企业管理器,企业管理器窗口的左边是以树形结构组织的管理对象，右边称为明细窗口，其内容是左边选中对象的详细内容。企业管理器可以实现对管理对象的定义、改动、删除等各种操作。,7.事件探查器,用于监视与分析SQL Server活动、服务器的网络进出流量或发出在SQL Server上的事件。事件探查器把一个操作序列保存为一个.trc文件，然后在本机或其他机器上按原来的次序重新执行一遍。,8.在IIS中配置SQL XML支持,SQLServer 2000含有SQL Server的IIS虚
17、拟目录管理器，通过它在IIS定义一个与SQL Server 2000实例相关的虚拟目录。Internet应用程序便可以编写出能够引用虚拟目录。,9.分析管理器,提供用户界面以访问分析服务器及其元数据知识库的控制台应用程序。实现：管理分析服务器，锁定被编辑的对象和它们的从属对象；创建数据库和指定数据源；生成并处理多维数据集；创建并处理数据挖掘模型；指定存储选项并优化查询性能；管理安全性；浏览数据源、共享维度、安全角色和其它对象。,SQL Server 数据库操作工具 6.3.1 用企业管理器创建数据库,1)选择企业管理器。2)选中需要在其上创建数据库的服务器 3)选中“数据库”文件夹，单击右键，
18、在弹出上选择“新建数据库”。在常规页面中，输入数据库名，选择SQL 服务器。,4)选择数据文件页面，输入数据文件属性；再选择事物日志页面，输入数据库的日志文件属性。5)单击“确定”按钮，关闭对话框。,6.3.2 在企业管理器中定义和管理表,1.新建表选中数据库中的表文件夹，击鼠标右健。在弹出的菜单中选择新建表。随后输入表结构对话框。,在对话框中单击右键，出现一个弹出框。字段输入完后，关闭建表对话框。在对话框中输入表名，单击“确定”按钮。,2.定义表的完整性约束和索引,(1)定义索引和键(2)定义表间关联选择“索引/键”页面，选择表页面,6.4 Transact-SQL语言 6.4.1 数据定义
19、语言,1.创建和管理数据库 CREATE DATABASE数据库名 ON PRIMARY(NAME=逻辑数据文件名，FILENAME=操作数据文件路径和文件名，SIZE=文件长度，MAXSIZE=最大长度，FILEROWTH=文件增长率)，n LOG ON(NAME=逻辑日志文件名，FILENAME=操作日志文件路径和文件名，SIZE=文件长度)，n FOR RESTORE 子句中：PRIMARY指明主文件名；SIZE说明文件的大小，数据库文件最小为1MB，默认值为3MB；FILEROWTH说明文件的增长率，默认值为10%。FOR RESTORE子句说明重建一个数据库，该重建的数据库用于数据恢
20、复操作。,2.定义表：CREATE TABLE表名(列名类型|AS表达式 字段约束，记录约束),(1)字段约束1)NOT NULL|NULL：不允许或允许字段值为空。2)PRIMARY KEY CLUSTERED|NON CLUSTERED：字段为主码并建立聚集或非聚集索引。3)REFERENCE参照表(对应字段)：定义被参照表及字段。4)DEFAULT缺省值：定义字段的缺省值。5)CHECK(条件)：定义字段应满足的条件表达式。6)IDENTITY(初始值，步长)：定义字段为数值型数据，并指出它的初始值和逐步增加的步长值。,(2)记录约束 CONSTRAINT约束名约束式1)PRIMARY
21、KEY CLUSTERED|NON CLUSTERED(列名组)2)FOREIGN KEY(外码)REFERENCES参照表(对应列)：3)CHECK(条件表达式)：定义记录应满足的条件。4)UNIQUE(列组)：定义不允许重复值的字段组。,3.基本表的维护,(1)修改基本表1)修改字段的定义。ALTER TABLE表名 ALTER COLUMN列名新类型 NULL|NOT NULL约束定义2)增加字段和表约束规则。ALTER TABLE表名ADD列定义|表约束定义,3)删除字段或约束规则。ALTER TABLE表名DROPCONSTRAINT约束名|COLUMN列名4)使约束有效或无效。AL
22、TER TABLE表名CHECK|NOCHECK CONSTRAINT ALL|约束名组(2)删除基本表:DROP TABLE表名,4.创建和管理索引,(1)创建索引 CTEATE UNIQUE CLUSTERED|NON CLUSTERED INDEX索引名ON表名(索引列组)(2)删除索引 DROP INDEX表名.索引名,5.创建和管理视图,(1)创建视图创建视图的语法为：CREATE VIEW视图名(列名组)WITH ENCRYPTIOPN AS子查询 WITH CHECK OPTION(2)删除视图 DROP VIEW 视图名组,6.创建和管理缺省,(1)创建缺省 CREATE DE
23、FAULT缺省名(2)绑定缺省 EXEC sp_bindfault 缺省名，表名.列名系统存储过程执行时的格式为：EXEC存储过程名参数组 如果参数是常量，则要加定界符。(3)解除缺省EXEC sp_unbinddefault 缺省名，表名.列名(4)删除缺省 DROP DEFAULT 缺省名组,创建当前日期默认值today_defa。CREATE DEFAULT today_defa AS getdate()EXEC sp_bindefault today_defa,employee.hire_date解除默认值today_defa与表employee 的hire_date 列的绑定:EXE
24、C sp_unbindefault employee.hire_date,7.创建和管理规则,(1)创建规则 CREATE RULE规则名AS规则表达式规则表达式是WHERE子句中的有效表达式。(2)绑定规则 EXEC sp_bindrule 规则名，对象名(3)解除规则EXEC sp_unbindrule 规则名，对象名(4)删除规则 DROP RULE规则名组,CREATE RULE hire_date_rule AS hire_date=1980-01-01 and hire_date=getdate()创建性别规则sex_rule。CREATE RULE sex_rule AS sex
25、 in(男,女)绑定例8-2创建的规则sex_rule 到employee 表的字段sex。EXEC sp_bindrule sex_rule,employee.sex,创建雇佣日期规则 hire_date_rule。,8.创建和管理存储过程,(1)创建存储过程和调用存储过程 CREATE PROCEDURE过程名;版本号 参数名参数类型=缺省值OUTPUTWITH RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION ASSQL语句组1)版本号是整数，它用于将有相同名字的存储过程编为不同的组。2)OUTPUT用于给调用者返回值。3)RECOMPILE为重编译。4
26、)ENCYPTION为加密选项。5)参数前加“”为局部变量，加“”则说明为全局变量。,过程的调用语句为：EXEUTE过程名参数名=参数版本号(2)删除存储过程 DROP PROCEDURE存储过程名组,9.创建和管理触发器,CREATE TRIGGER触发器名ON表名 WITH ENCRYPTION FORDELETE，INSERT，UPDATE WITH APPEND NOT FOR REPLICATION ASSQL语句组,1)WITH ENCRYPTION为加密。2)DELETE触发器是当对表执行DELETE操作时删除元组，将删除的元组放入deleted表中。检查delete表中的数据，
27、确定该如何处理。3)INSERT触发器在对执行插入数据操作时,将插入表中的数据拷贝并送入inserted表中，根据inserted表中的值决定如何处理。4)UPDATE触发器仅在更新数据操作时将要被更新的原数据移入deleted表中，将更新后的数据备份送入inserted表中，对deleted和inserted表进行检查，并决定如何处理。5)NOT FOR REPLICATION项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。,【例】设有member表（成员表）、loan表（借书表）和reservation表（预定书表）。通过触发器定义未还图书的成员不能从成员表中删除
28、，当删除成员时，该成员在的预定书表（reservation表）中的记录也将全部被删除。,CREATE TRIGGER member_delete ON member FOR DELETE AS IF(SELECT COUNT(*)FROM loan，deleted WHERE loan.member_no=deleted.member_no)0 ROLLBACK TRANSACTION ELSE DELETE reservation FROM reservation，deleted WHERE reservation.member_no=deleted.member_no,6.4.2 数据操纵
29、语言,1.数据检索语句的语句格式 SELECT查询列 INTO新表名 FROM数据源 WHERE元组条件表达式 GROUP BY分组条件HAVING组选择条件 ORDER BY排序条件 COMPUTER统计列组BY表达式,（1）SELECT子句,SELECT ALL|DISTINCT TOP数值PERCENT查询列组查询列为：查询列:=*|表或视图.*|列名或表达式AS列别名|列别名=表达式,ALL|DISTINCT：ALL为返回所有行,DISTINCT为仅显示结果集中的惟一行。该项不选时，ALL是缺省值。TOP数值：仅返回结果集中的前数值行。如果有PENCENT，则返回结果集中的百分之数值行
30、记录。“*”：指明返回表和视图的全部列。表或视图.*：指明返回指定表或视图的全部列。列别名：用来代替出现在结果集中的列名或表达式，别名可以在ORDER BY子句中出现，但不能在WHERE、GROUP BY或HAVING子句中出现。,(2)INTO子句和FROM子句,INTO子句将查询结果添加到创建表中。INTO不能与COMPUTE 子句同时使用。如果创建表是临时表，在表名前加“#”字符。FROM语法格式为：FROM数据源组数据源的语法为：数据源:=表名AS表别名WITH(表线索组)|视图名AS视图别名|行集合函数AS别名|嵌套的SELECT语句AS别名|连接表,连接表的语法为：连接表:=数据源
31、连接类型数据源ON连接条件|数据源CROSSJOIN数据源|连接表连接类型:=INNER|LEFT|RIGHT|FULLOUTER JOIN1)INNER为内连接，它返回所有连接匹配的行。LEFT OUTER为左外连接。RIGHT OUTER为右外连接。FULL OUTER为完全外连接。2)ON连接条件指定连接条件：列名比较符列名3)CROSS JOIN为交叉连接，即对两个表进行笛卡儿积运算。,(4)WHERE子句 WHERE查询条件|旧格式外连接理条件 旧格式外连接条件:=列名*=|=*列名(5)GROUP BY子句 GROUP BY ALL分组表达式组(6)HAVING子句。HAVING分
32、组或汇总筛选条件,(7)ORDER BY ORDER BY排序项ASC|DESC，n(8)COMPUTE子句。产生汇总值,在结果集中后将汇总值放入摘要列，COMPUTE与BY配合，起到换行控制和分段小计的作用。COMPUTE统计函数组BY分组项 其中：BY分组项表示在结果集中产生换行控制及分段小计。COMPUTE BY必须和ORDER BY配合使用。,2 Transact-SQL查询实例,设图书借阅数据库中包括：图书(书号，类别，出版社，作者，书名，定价)；读者(书证号，姓名，单位，性别，电话)；借阅(书号，读者书证号，借阅日期).【例】将计算机类的书存入永久的计算机图书表，将借书日期在99年
33、以前的借阅记录存入临时的超期借阅表。SELECT*INTO 计算机图书 FROM 图书 WHERE 类别=计算机 GO SELECT*INTO#超期借阅 FROM 借阅 WHERE 借阅日期1999-01-01 GO,【例】查找类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍的图书。SELECT A.*FROM 图书 A GROUP BY A.类别 HAVING MAX(A.定价)=ALL(SELECT 2*AVG(B.定价)FROM 图书 B GROUP BY B.类别)【例】列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。SELECT
34、书号，名称，定价 FROM 图书 WHERE 类别=计算机类 ORDER BY 书号 ASC COMPUTE COUNT(*)，SUM(定价)BY 出版社 COMPUTE COUNT(*)，SUM(定价),插入数据 语法格式如下：INSERT INTO table_name|view_name(column_list)VALUES(value_list)|derived_table|execute_statement|Select 语句|DEFAULT VALUES,数据更新操作,USE SalesGOINSERT Supplier VALUES(S001,华科电子有限公司,施宾彬,朝阳路56
35、号,2636565)显式指定列列表。INSERT(supplier_id,supplier_name,linkman_name,address,telephone)VALUES(S001,华科电子有限公司,施宾彬,朝阳路56号,2636565),修改数据语法格式：UPDATE table_name|view_name SET column_name|variable=expression,.n FROM,.n WHERE,例：使用简单的UPDATE语句。UPDATE Customer2 SET linkman_name=佚名,address=NULL,telephone=例：在UPDATE语句
36、中使用WHERE子句。UPDATE Customer2 SET telephone=0731-+telephone WHERE LEN(telephone)=7,例 在UPDATE 语句中使用SELECTTOP语句。UPDATE Goods SET Unit_Price=Unit_Price*0.9 FROM Goods,(SELECT TOP 10 Goods_ID,SUM(Order_Num)AS Total_Num FROM Sell_Order GROUP BY Goods_ID ORDER BY Total_Num DESC)AS Total_Sum WHERE Goods.Good
37、s_id=Total_Sum.Goods_id,删除数据 1DELETE 语句DELETE 语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。DELETE语句的语法格式如下：DELETE FROM table_name|view_name FROM,.n WHERE,例 不带参数使用DELETE命令删除所有行。USE SalesGODELETE Customer2本例从Customer2表中删除所有行。注意：将DELETE语句与DROP TABLE语句的功能区分开来。例 带WHERE子句的DELETE语句，有条件地删除行。DELETE FROM Sell_Order WHERE C
38、ustom_ID=C0003,例 在DELETE中使用联接或子查询。-基于Transact-SQL，使用表的联接DELETE Sell_Order FROM Sell_Order SO INNER JOIN customer2 C ON SO.customer_id=C.customer_id WHERE C.address LIKE 东方市%-等同于下列命令DELETE Sell_Order FROM Sell_Order SO,customer2 C WHERE SO.customer_id=C.customer_id AND C.address LIKE 东方市%-基于SQL-92标准，须使用子查询DELETE FROM Sell_Order WHERE customer_id IN(SELECT customer_id FROM customer2 WHERE address LIKE 东方市%),2TRUNCATE TABLE语句语法格式：TRUNCATE TABLE table_name其中table_name是要清空的表的名称。例 使用TRUNCATE TABLE语句清空表。TRUNCATE TABLE customer2,
