web应用程序开发教程第08章课件.ppt

1、 第第8章章 SQL Server 2000数据库管理数据库管理本章知识点本章知识点p 常用数据库管理工具常用数据库管理工具p 创建和维护数据库创建和维护数据库p 表管理表管理p 表约束表约束p 索引操作索引操作p 视图管理视图管理8.1常用数据库管理工具常用数据库管理工具p SQL Server 2000SQL Server 2000的版本和组件的版本和组件p 服务管理器服务管理器p 企业管理器企业管理器p osqlosql实用工具实用工具p SQLSQL查询分析器查询分析器SQL Server 2000的版本和组件的版本和组件pSQL Server 2000SQL Server 2000包

2、括企业版、标准版、包括企业版、标准版、个人版、开发版和评估版等版本。个人版、开发版和评估版等版本。服务管理器服务管理器p SQL ServerSQL Server服务管理器的功能是启动、服务管理器的功能是启动、停止和暂停服务器上的停止和暂停服务器上的SQL ServerSQL Server组组件。件。企业管理器企业管理器企业管理器企业管理器企业管理器企业管理器企业管理器企业管理器osql实用工具实用工具p 在在WindowsWindows命令提示符中输入下面的命令可以进入命令提示符中输入下面的命令可以进入osqlosql实用工具，这实用工具，这里假定帐户里假定帐户sasa的密码也是的密码也是s

3、asa。osql/S Ntserver/Usa/P sap【例例】在在osqlosql实用工具中输入以下命令，可以返回当前数据库服务器实用工具中输入以下命令，可以返回当前数据库服务器的名称。的名称。SELECT SERVERNAMEGOSQL查询分析器查询分析器8.2 创建和维护数据库创建和维护数据库p数据库的概念及组成数据库的概念及组成p创建数据库创建数据库p删除数据库删除数据库p备份数据库备份数据库p还原数据库还原数据库p分离数据库分离数据库p附加数据库附加数据库数据库的概念及组成数据库的概念及组成p数据库对象的基本概念数据库对象的基本概念表是保存基本数据的逻辑单位，由行和列组成。每行代表

4、唯一的一条记录，而每列代表记录中的一个域，通常被称为字段。视图类似于一种虚拟的表，它在物理上并不真实存在，只是从一个或多个表中按照一定的规则读取若干列，组成新的结果集。视图是由查询数据库表产生的。数据库的索引和书籍中的目录非常相似。p3 3种文件类型来存储数据种文件类型来存储数据主文件次要文件事务日志p4 4种系统数据库种系统数据库master数据库 tempdb数据库model数据库 msdb数据库创建数据库创建数据库创建数据库创建数据库p CREATE DATABASECREATE DATABASE语句创建数据库。语句创建数据库。CREATE DATABASE TestDBGO 【例例】创

5、建数据库创建数据库TestDB1TestDB1，同时自定义数据文件的参数。，同时自定义数据文件的参数。CREATE DATABASE TestDB1ONPRIMARY(NAME=test1,FILENAME=c:program filesmicrosoft sql servermssqldatatestdat1.mdf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=test2,FILENAME=c:program filesmicrosoft sql servermssqldatatestdat2.ndf,SIZE=100MB,MAXSIZE=200,

6、FILEGROWTH=20),(NAME=test3,FILENAME=c:program filesmicrosoft sql servermssqldatatestdat3.ndf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20)GO创建数据库创建数据库【例例】创建数据库创建数据库TestDB2TestDB2，同时自定义数据文件和日志文件的参数：，同时自定义数据文件和日志文件的参数：CREATE DATABASE TestDB2ONPRIMARY(NAME=1,FILENAME=c:program filesmicrosoft sql servermssqldat

7、atest2dat1.mdf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Arch2,FILENAME=c:program filesmicrosoft sql servermssqldatatest2dat2.ndf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Arch3,FILENAME=c:program filesmicrosoft sql servermssqldatatest2dat3.ndf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20)LOG ON(NAME=Ar

8、chlog1,FILENAME=c:program filesmicrosoft sql servermssqldatatest2log1.ldf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Archlog2,FILENAME=c:program filesmicrosoft sql servermssqldatatest2log2.ldf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20)GO删除数据库删除数据库p DROP DATABASEDROP DATABASE命令删除数据库，语法如下：命令删除数据库，语法如下：DRO

9、P DATABASE【例例】要删除数据库要删除数据库TestDB1TestDB1和和TestDB2TestDB2，可以使用下面的命令：，可以使用下面的命令：DROP DATABASE TestDB1DROP DATABASE TestDB2GO备份数据库备份数据库p 备份模式：备份模式：完全备份，备份所有的数据库数据；差异备份，只备份与上次备份的差异部分；事务日志，只备份事务日志中的数据；文件和文件组，只备份指定的文件或文件组，需要单击后面的“”按钮选择文件或文件组。备份数据库备份数据库备份数据库备份数据库pBACKUPBACKUP语句可以备份语句可以备份SQL ServerSQL Serve

10、r数据库。数据库。BACKUP DATABASE database_name TO 【例例】将数据库将数据库TestDBTestDB备份到备份到C:TestDB.bakC:TestDB.bak：BACKUP DATABASE TestDBTO DISK=C:TestDB.bak 在查询分析器中执行上面的语句，返回的结果如下：在查询分析器中执行上面的语句，返回的结果如下：已处理 96 页，这些页属于数据库 TestDB 的文件 TestDB_Data（位于文件 1 上）。已处理 1 页，这些页属于数据库 TestDB 的文件 TestDB_Log（位于文件 1 上）。BACKUP DATABAS

11、E 操作成功地处理了 97 页，花费了 0.882 秒（0.893 MB/秒）。还原数据库还原数据库还原数据库还原数据库还原数据库还原数据库p RESTORE DATABASERESTORE DATABASE语句还原数据库。语句还原数据库。RESTORE DATABASE database_name FROM 【例例】要从备份文件要从备份文件TestDB.bakTestDB.bak中还原数据库中还原数据库TestDBTestDB，可以使用下面的语句：，可以使用下面的语句：RESTORE DATABASE TestDB FROM DISK=C:TestDB.bak WITH REPLACEGOp

12、 在还原数据库时，不能存在到该数据库的连接。否则将提示下面的错在还原数据库时，不能存在到该数据库的连接。否则将提示下面的错误信息。误信息。服务器:消息 3101，级别 16，状态 2，行 1因为数据库正在使用，所以未能获得对数据库的排它访问权。服务器:消息 3013，级别 16，状态 1，行 1RESTORE DATABASE 操作异常终止。p 如果还原数据库成功，则显示如下提示信息。如果还原数据库成功，则显示如下提示信息。已处理 96 页，这些页属于数据库 TestDB 的文件 TestDB_Data（位于文件 1 上）。已处理 1 页，这些页属于数据库 TestDB 的文件 TestDB_

13、Log（位于文件 1 上）。RESTORE DATABASE 操作成功地处理了 97 页，花费了 0.047 秒（16.776 MB/秒）。分离数据库分离数据库p 存储过程存储过程sp_detach_dbsp_detach_db分离数据库：分离数据库：sp_detach_db dbname=dbname ,skipchecks=skipchecks p【例例】要分离数据库要分离数据库TestDBTestDB：Exec sp_detach_db TestDB附加数据库附加数据库附加数据库附加数据库p使用存储过程使用存储过程sp_attach_dbsp_attach_db附加数据库，语法附加数据库

14、，语法结构如下：结构如下：sp_attach_db dbname=dbname ,filename1=filename_n ,.16 【例例】要附加数据库要附加数据库TestDBTestDB，可以使用如下语，可以使用如下语句：句：Exec sp_attach_db TestDB,C:Program FilesMicrosoft SQL ServerMSSQLDataTestDB_Data.MDFGO 8.3 表管理表管理p 表的概念表的概念p 创建表创建表p 修改表修改表p 删除表删除表p 查看和编辑表数据查看和编辑表数据表的概念表的概念p表演示图表演示图p表分为永久表和临时表两种。表分为永久

15、表和临时表两种。p临时表存储在临时表存储在tempdbtempdb数据库中，当不再使用时数据库中，当不再使用时系统会自动删除临时表。系统会自动删除临时表。p临时表可以分为本地临时表和全局临时表。本临时表可以分为本地临时表和全局临时表。本地临时表以地临时表以#符号开头，例如符号开头，例如#tmptable1#tmptable1。全局暧。全局暧昧表以昧表以#符号开头，例如符号开头，例如#tmptable2#tmptable2。表的概念表的概念p查看数据库中的表查看数据库中的表创建表创建表 p二进制数据二进制数据数据类型描述Binary固定长度的二进制数据类型，binary (n)表示固定长度的n个

16、字节二进制数据。n必须从18 000，存储空间大小为n+4字节Varbinary变长的二进制数据类型，varbinary (n)表示n个字节变长二进制数据。n必须从18 000。存储空间大小为实际输入数据长度+4个字节，而不是n个字节。输入的数据长度可能为 0 字节Image可以用来存储超过8 KB的可变长度的二进制数据，如Microsoft Word文档、Microsoft Excel电子表格、包含位图的图像、GIF文件和JPEG文件创建表创建表p字符数据字符数据数据类型描述char固定长度的字符数据类型。char(n)表示长度为n个字节的固定长度且非Unicode 的字符数据。n必须是一个

17、介于18 000之间的数值。存储大小为n个字节varchar是非固定长度的字符数据类型。varchar(n)表示长度为n个字节的可变长度且非Unicode的字符数据。n必须是一个介于18 000之间的数值。存储大小为输入数据的字节的实际长度，而不是n个字节。所输入的数据字符长度可以为0text数据类型的列可用于存储大于8KB的ASCII字符。例如，由于HTML文档均由ASCII字符组成且一般长于8KB，所以用浏览器查看之前应在SQL Server中存储在text列中创建表创建表pUnicode Unicode 数据数据p日期和时间数据日期和时间数据数据类型描述Ncharnchar是固定长度Un

18、icode数据的数据类型，nchar(n)表示包含n个字符的固定长度Unicode字符数据。n的值必须介于14 000之间。存储大小为n字节的两倍nvarcharnvarchar是可变长度 Unicode 数据的数据类型，nvarchar(n)表示包含n个字符的可变长度Unicode字符数据。n的值必须介于14 000之间。字节的存储大小是所输入字符个数的两倍。所输入的数据字符长度可以为零Ntext可变长度Unicode数据的最大长度为230-1(1 073 741 823)个字符。存储大小是所输入字符个数的两倍（以字节为单位）数据类型描述datetime从 1753 年 1 月 1 日到 9

19、999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）smalldatetime从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据精确到分钟。29.998 秒或更低的 smalldatetime 值向下舍入为最接近的分钟，29.999 秒或更高的 smalldatetime 值向上舍入为最接近的分钟创建表创建表p数字数据数字数据数据类型描述整型数据bigint从-263（-9 223 372 036 854 775 808）263-1（9 223 372 036 854 775 807）的整型数据（所有数字

20、）。存储大小为 8 个字节int从-231（-2 147 483 648）231-1（2,147,483,647）的整型数据（所有数字）。存储大小为 4 个字节smallint从-215（-32 768）215-1（32 767）的整型数据。存储大小为 2 个字节。tinyint从 0 255 的整型数据。存储大小为 1 字节小数数据D e c i m a l 和numeric带定点精度和小数位数的numeric数据类型。decimal(p,s)和 numeric(p,s)表示定点精度和小数位数。使用最大精度时，有效值从-1038+1 1038-1。p表示精度，指定小数点左边和右边可以存储的十

21、进制数字的最大个数。精度必须是从 1 到最大精度之间的值。最大精度为38。s表示小数位数，指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从0 p之间的值。默认小数位数是0，因而 0 s p。最大存储大小基于精度而变化。近似数字数据float (n)从-1.79E+308 1.79E+308 之间的浮点数字数据。n 为用于存储科学记数法float数尾数的位数，同时指示其精度和存储大小。n 必须为从 1 53 之间的值。real从-3.40E+38 3.40E+38 之间的浮点数字数据。存储大小为 4 字节创建表创建表p货币数据货币数据p特殊数据特殊数据数据类型描述Money货币数据

22、值介于-263（-922 337 203 685 477.5808）263 1（+922 337 203 685 477.5807）之间，精确到货币单位的千分之十。存储大小为 8 个字节smallmoney货币数据值介于-214 748.3648 +214 748.3647 之间，精确到货币单位的千分之十。存储大小为 4 个字节数据类型描述table一种特殊的数据类型，存储供以后处理的结果集。table数据类型只能用于定义table类型的局部变量或用户定义函数的返回值bitbit 数据类型只能包括 0 或 1。可以用bit数据类型代表TRUE或FALSE、YES或NO。例如，询问客户是否为初次

23、访问的问题可存储在bit列中，类似于其他数据类型中的布尔型timestamp用于表示SQL Server在一行上的活动顺序，按二进制格式以递增的数字来表示。当表中的行发生变动时，用从DBTS函数获得的当前数据库的时间戮值来更新时间戮。timestamp数据与插入或修改数据的日期和时间无关。若要自动记录表中数据更改的时间，使用datetime或smalldatetime数据类型记录事件或触发器uniqueidentifier以一个16位的十六进制数表示全局唯一标识符（GUID）。当需要在多行中唯一标识某一行时可使用GUID。例如，可使用uniqueidentifier 数据类型定义一个客户标识代

24、码列，以编辑公司来自多个国家/地区的总的客户名录sql_variant一种存储SQL Server所支持的各种数据类型（text、ntext、timestamp和sql_variant除外）值的数据类型创建表创建表p用户定义的数据类型用户定义的数据类型p 创建用户定义的数据类型时必须提供以下三个参数：创建用户定义的数据类型时必须提供以下三个参数：（1）名称。（2）新数据类型所依据的系统数据类型。（3）为空性（数据类型是否允许空值）。【例例】创建用户自定义数据类型创建用户自定义数据类型postcodepostcode，用于表示邮政编码。，用于表示邮政编码。创建表创建表p在数据库在数据库HrDBH

25、rDB中要创建数据库中要创建数据库DepInfoDepInfo，用于保存部门信息，用于保存部门信息创建表创建表p数据库关系数据库关系创建表创建表创建表创建表p数据库设计器数据库设计器创建表创建表p CREATE TABLECREATE TABLE语句创建表：语句创建表：CREATE TABLE 表名(列名1 数据类型和长度1 列属性1,列名2 数据类型和长度2 列属性2,.列名n 数据类型和长度n 列属性n,)【例例】使用使用CREATE TABLECREATE TABLE语句创建表语句创建表EmpInfoEmpInfo。CREATE TABLE EmpInfo(Emp_idint IDENT

26、ITY PRIMARY KEY,Emp_namevarchar(50),Sexchar(2),Titlevarchar(50),Wagefloat,IdCardvarchar(20),Dep_idtinyint)修改表修改表p重命名表重命名表（1 1）向表中添加列）向表中添加列ALTER TABLE 表名 ADD 列名 数据类型和长度 列属性【例例】使用使用ALTER TABLEALTER TABLE语句在表语句在表DepInfoDepInfo中增加一列，列名为中增加一列，列名为“NewColumnNewColumn”，数据类型为，数据类型为varcharvarchar，长，长度为度为5050

27、，列属性为允许空。，列属性为允许空。ALTER TABLE DepInfo ADD NewColumn varchar(50)NULL（2 2）修改列属性）修改列属性ALTER TABLE 表名 ALTER COLUMN 列名 新数据类型和长度 新列属性【例例】使用使用ALTER TABLEALTER TABLE语句在表语句在表DepInfoDepInfo中修改中修改NewColumnNewColumn列的属性，将数据类型为列的属性，将数据类型为charchar，长度为，长度为3030，列属性为允许空，默认值为列属性为允许空，默认值为“新建列新建列”。ALTER TABLE DepInfo A

28、LTER COLUMN NewColumn char(30)NOT NULL（3 3）删除列）删除列ALTER TABLE 表名 DROP COLUMN 列名修改表修改表【例例】使用使用ALTER TABLEALTER TABLE语句在表语句在表DepInfoDepInfo中删除中删除NewColumnNewColumn列。列。ALTER TABLE DepInfo DROP COLUMN NewColumnp 使用使用sp_renamesp_rename存储过程修改表或列的名称。存储过程修改表或列的名称。sp_rename 原对象名,新对象名,对象类型【例例】使用存储过程使用存储过程sp_r

29、enamesp_rename将表将表DepInfoDepInfo重命名为重命名为DepartmentsDepartments。sp_rename DepInfo,Departments 运行结果如下：运行结果如下：注意:更改对象名的任一部分都可能破坏脚本和存储过程。object 已重命名为 Departments。删除表删除表p DROP TABLEDROP TABLE语句删除表：语句删除表：DROP TABLE 表名【例例】使用使用DROP TABLEDROP TABLE语句删除表语句删除表DepInfoDepInfo：DROP TABLE DepInfo查看和编辑表数据查看和编辑表数据p查

30、看表的属性查看表的属性p编辑表的内容编辑表的内容查看和编辑表数据查看和编辑表数据p验证一下表验证一下表DepInfoDepInfo中中Dep_idDep_id列的自动增列的自动增1 1属性。属性。查看和编辑表数据查看和编辑表数据p“查询设计器查询设计器”窗口窗口关系图窗格SQL窗格网格窗格结果窗格8.4 表约束表约束p 主键约束主键约束p 唯一性约束唯一性约束p 检查约束检查约束p 默认约束默认约束p 外键约束外键约束主键约束主键约束p创建、修改和删除键创建、修改和删除键唯一性约束唯一性约束p定义和管理唯一性约束定义和管理唯一性约束检查约束检查约束p定义和管理检查约束定义和管理检查约束默认约束

31、默认约束p定义默认约束定义默认约束外键约束外键约束p创建和管理外键创建和管理外键外键约束外键约束p创建外键约束创建外键约束p删除外键约束删除外键约束8.5 索引操作索引操作p 设计索引设计索引p 创建索引创建索引p 查看、修改和删除索引查看、修改和删除索引设计索引设计索引pEmp_nameEmp_name索引的存储和工作情况索引的存储和工作情况p SQL Server 2000SQL Server 2000中，唯一索引和聚集索引是两种特殊索引。中，唯一索引和聚集索引是两种特殊索引。唯一索引。在SQL Server中，当唯一性是数据本身的特点时，可创建唯一索引，但索引列的组合不同于表的主键。聚集

32、索引。在聚集索引中，表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。Emp_name Sex Title 李三 男 部门经理 赵五 女 部门经理 高十 李三 赵五 高十 男 职员 EmpInfo 表表 Emp_id 索引索引 创建索引创建索引p“索引索引/键键”选项卡创建索引选项卡创建索引p CREATE INDEXCREATE INDEX语句创建索引：语句创建索引：CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名|视图名 (列名 ASC|DESC ,.n )创建索引创建索引p 参数说明如下：参数说明如下：唯一

33、索引。使用UNIQUE参数创建唯一索引。【例例】为表为表DepInfoDepInfo创建基于列创建基于列Dep_nameDep_name的唯一索引的唯一索引IX_DepInfoIX_DepInfo：CREATE UNIQUE INDEX IX_DepInfo ON DepInfo(Dep_name)聚集/非聚集索引。使用CLUSTERED和NONCLUSTERED参数创建聚集和非聚集索引。CLUSTERED/NOCLUSTERED可以和UNIQUE同时出现。【例例】为表为表DepInfoDepInfo创建基于创建基于Dep_nameDep_name列的唯一、聚集索引列的唯一、聚集索引IX_Na

34、meIX_Name：CREATE UNIQUE CLUSTERED INDEX IX_Name ON DepInfo(Dep_name)升序和降序。使用ASC和DESC参数来确定具体某个索引列的升序或降序排序方向。默认设置为 ASC。【例例】对表对表EmpInfoEmpInfo的列的列WageWage按照降序创建索引：按照降序创建索引：CREATE INDEX IX_Wage ON EmpInfo(Wage DESC)查看、修改和删除索引查看、修改和删除索引p DROP INDEXDROP INDEX语句删除索引，基本语法如下：语句删除索引，基本语法如下：DROP INDEX 表名.索引名|视

35、图名.索引名 ,.n 【例例】删除表删除表EmpInfoEmpInfo的索引的索引IX_WageIX_WageDROP INDEX EmpInfo.IX_Wage 8.6 视图管理视图管理p 创建视图创建视图p 修改视图修改视图p 删除视图删除视图创建视图创建视图p视图设计器视图设计器关系图窗格SQL窗格网格窗格结果窗格创建视图创建视图p添加表添加表创建视图创建视图p创建新视图创建新视图创建视图创建视图p创建视图向导创建视图向导创建视图创建视图p创建视图向导创建视图向导创建视图创建视图p CREATE VIEWCREATE VIEW语句创建视图。语句创建视图。CREATE VIEW WITH

36、视图参数 AS 【例例】使用使用CREATE VIEWCREATE VIEW命令创建视图命令创建视图EmpViewEmpView。CREATE VIEW dbo.EmpViewASSELECT dbo.EmpInfo.Emp_name,dbo.EmpInfo.Sex,dbo.EmpInfo.Wage,dbo.EmpInfo.Title,dbo.DepInfo.Dep_nameFROM dbo.EmpInfo INNER JOIN dbo.DepInfo ON dbo.DepInfo.Dep_id=dbo.EmpInfo.Dep_id创建视图创建视图pWITHWITH子句支持的视图属性子句支持的

37、视图属性 【例例】使用使用CREATE VIEWCREATE VIEW命令创建加密视图命令创建加密视图EmpView1EmpView1：CREATE VIEW dbo.EmpViewWITH ENCRYPTIONASSELECT dbo.EmpInfo.Emp_name,dbo.EmpInfo.Sex,dbo.EmpInfo.Wage,dbo.EmpInfo.Title,dbo.DepInfo.Dep_nameFROM dbo.EmpInfo INNER JOIN dbo.DepInfo ON dbo.DepInfo.Dep_id=dbo.EmpInfo.Dep_id属性描述ENCRYPTIO

38、N表示 SQL Server 加密包含 CREATE VIEW 语句文本的系统表列。使用 WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布SCHEMABINDING将视图绑定到架构上。指定 SCHEMABINDING 时，select_statement 必须包含所引用的表、视图或用户定义函数的两部分名称(owner.object)VIEW_METADATA指定为引用视图的查询请求浏览模式的元数据时，SQL Server 将向 DBLIB、ODBC 和 OLE DB API 返回有关视图的元数据信息，而不是返回基表或表修改视图修改视图p对视图加密对视图加

39、密修改视图修改视图p ALTER VIEWALTER VIEW语句修改视图。语句修改视图。ALTER VIEW WITH 视图参数 AS【例例】使用使用ALTER VIEWALTER VIEW命令修改视图命令修改视图EmpViewEmpView，查询部门编号为，查询部门编号为1 1的员的员工记录：工记录：ALTER VIEW dbo.EmpViewASSELECT dbo.EmpInfo.Emp_name,dbo.EmpInfo.Sex,dbo.EmpInfo.Wage,dbo.EmpInfo.Title,dbo.DepInfo.Dep_nameFROM dbo.EmpInfo INNER JOIN dbo.DepInfo ON dbo.DepInfo.Dep_id=dbo.EmpInfo.Dep_idWHERE dbo.DepInfo.Dep_name=人事部删除视图删除视图p删除视图删除视图p DROP VIEWDROP VIEW语句删除视图，其基本语法如下：语句删除视图，其基本语法如下：DROP VIEW 视图名 【例例】使用使用DROP VIEWDROP VIEW命令删除视图命令删除视图EmpViewEmpView：DROP VIEW EmpView