数据库实验指导书

数据库实验指导书 数据库实验指导书 （试用版） 目录 工贸企业有限空间作业目录特种设备作业人员作业种类与目录特种设备作业人员目录1类医疗器械目录高值医用耗材参考目录 TOC \o "1-3" \h \z 引言 1 一、课程实验目的和基本要求 1 二、主要实验环境 1 三、实验内容 1 实验1 数据库模式 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 和数据库的建立 2 一、教学目的和要求 2 二、实验内容 2 三、实验步骤 2 四、思考与总结 3 实验2 数据库的简单查询和连接查询 4 一、教学目的和要求 4 二、实验内容 4 三、实验步骤 4 四、思考与总结 5 实验3 数据库的嵌套查询和组合统计查询 6 一、教学目的和要求 6 二、实验内容 6 三、实验步骤 6 四、思考与总结 7 实验4 视图与图表的定义及数据完整性和安全性 8 一、教学目的和要求 8 二、实验内容 8 三、实验步骤 8 四、思考与总结 9 实验5 简单应用系统的实现 10 一、教学目的和要求 10 二、实验内容 10 三、实验步骤 10 四、思考与总结 10 附录1：数据库实验报告格式 11 附录2：SQL Server 2000使用指南 12 1 SQL Server 2000简介 12 2 SQL Server 2000的版本 12 3 SQL Server 2000实用工具 12 4 创建数据库 15 5 创建和修改数据表 17 6 创建索引 22 7 存储过程 23 8 触发器 25 9 备份和恢复 27 10 用户和安全性管理 28 引言 数据库技术是一个理论和实际紧密相连的技术，上机实验是数据库课程的重要环节，它贯穿于整个“数据库阶段”课程教学过程中。 一、课程实验目的和基本要求 上机实验是本课程必不可少的实践环节。学生应在基本掌握各知识点内容的基础上同步进行相关实验，以加深对知识的理解和掌握，达到理论指导实践，实践加深理论的理解与巩固的效果。 数据库课程上机实验的主要目标是： （1）​ 通过上机操作，加深对数据库系统理论知识的理解。 （2）​ 通过使用具体的DBMS，了解一种实际的数据库管理系统，并掌握操作技术。 （3）​ 通过实际题目的上机实验，提高动手能力，提高分析问题和解决问题的能力。 实验在单人单机的环境下，在规定的时间内，由学生独立完成。出现问题时，教师要引导学生独立分析、解决，不得包办代替。 上机总学时不少于12学时。 二、主要实验环境 操作系统为Microsoft Windows 2000/XP。 数据库管理系统可以选择：（1）Microsoft SQL Server 2000标准版或企业版 （2）Microsoft Access2000 （3）金仓数据库KingbaseES。 三、实验内容 本课程实验主要包括数据库的模式设计，数据库、表、视图、索引的建立与数据的更新；关系数据库的查询，包括连接查询、嵌套查询、组合查询等；数据库系统的实现技术，包括事务的概念及并发控制、完整性和安全性实现机制；简单数据库应用系统的设计与实现。 实验1 数据库模式设计和数据库的建立 一、教学目的和要求 (1)​ 根据一个具体应用，独自完成数据库模式的设计。 (2)​ 熟练使用SQL语句创建数据库、表、索引和修改表结构。 (3)​ 熟练使用SQL语句向数据库输入数据、修改数据和删除数据的操作。 二、实验内容 (1)​ 对实际应用进行数据库模式设计（至少三个基本表）。 (2)​ 创建数据库、表、确定表的主码和约束条件，为主码建索引。 (3)​ 查看数据库属性，查看和修改表结构。 三、实验步骤 1、基本操作实验： (1)​ 通过具体一个DBMS中建立学生选课数据库。 (2)​ 在DBMS建好的学生选课数据库中建立学生、课程和选课三个表，其结构为： 学生表：Student(Sno,Sname,Ssex,Sage,Sdept)，其主码为Sno； 课程表：Course(Cno,Cname,Cpno,Ccredit)，其主码为Cno； 选课表：SC(Sno,Cno,Grade)，其主码为（Sno,Cno）。 (3)​ 要求为属性选择合适的数据类型，定义每个表的主码，是否允许空值和默认值等列级数据约束。 (4)​ 建立学生、课程和选课3个表的主码约束；选课表与学生表、选课表和课程表之间的外码约束，通过操作予以实现。 (5)​ 在学生选课数据库的学生、课程和选课3个表中各输入10条记录。要求记录不仅满足数据约束要求，还要有表间关联的记录。 (6)​ 实现对学生选课数据库的学生、课程和选课3个表中数据的插入、删除和修改操作。 2、提高操作实验： (1)​ 将学生选课数据库、库中的表、索引和约束用SQL表达，实现建库、建表、建立表间联系和建立必要的索引、约束的操作。 四、思考与总结 （1）指出学生选课数据库的主码、外码和数据约束。 （2）学生选课数据库的选课表中，属性学号、课程号采用数值型，还是采用字符型的，采用哪种类型最好。 （3）为什么要建立索引，建立多少索引合适。 （4）为什么不能随意删除被参照表中的主码。 实验2 数据库的简单查询和连接查询 一、教学目的和要求 （1）加深对标准SQL查询语句的理解。 （2）熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。 二、实验内容 （1）简单查询操作，该实验包括投影、选择条件表达，数据排序等。 （2）连接查询操作，该实验包括等值连接、自然连接、求笛卡尔积、一般连接、外连接、内连接、左连接、右连接和自身连接等。 三、实验步骤 1、基本操作实验： (1)​ 查询计算机系学生的学号和姓名。 (2)​ 查询选修了数学课的所有学生名单。 (3)​ 查询年龄小于23岁的女同学的学号和姓名。 (4)​ 查询所有姓王的学生。 (5)​ 查询全体学生的姓名和出生年份。 (6)​ 查询选修了课程的学生学号。 (7)​ 查询每个学生的情况以及他（她）所选修的课程号。 2、提高操作实验： (1)​ 查询学生的学号、姓名、选修的课程名及成绩，并按成绩的降序排列。 (2)​ 查询选修1号课程且成绩在80-90分之间的学生学号和成绩。 (3)​ 查询选修1号课程且成绩在90分以上的学生学号、姓名及成绩。 (4)​ 查询每一门课（名称）的间接先行课名称（即先行课的先行课）。 (5)​ 查询至少选修了1、2号课程的学生姓名。 四、思考与总结 (1)​ 输入SQL语句时应注意，语句中均使用西文操作符号。 (2)​ 学会查询结果的几种处理方式。 (3)​ 如何提高数据查询和连接速度。 (4)​ 对于常用的查询形式或者查询结果，怎样处理好。 实验3 数据库的嵌套查询和组合统计查询 一、教学目的和要求 (1)​ 加深对SQL语言的嵌套查询语句的理解。 (2)​ 熟练掌握数据查询中分组、统计、计算和组合的操作方法。 二、实验内容 （1）使用IN、比较符、ANY或ALL和EXITS操作符进行嵌套查询操作。 （2）分组查询实验。该实验包括分组条件表达、选择组条件表达的方法。 （3）使用函数查询实验。该实验包括统计函数和分组统计函数的 使用方法 消防栓的使用方法指针万用表的使用方法84消毒液使用方法消防灭火器使用方法铁材计算器使用方法 。 （4）组合查询，计算和分组计算实验。 三、实验步骤 1、基本操作实验： (1)​ 查询选修了数据库的学生学号和姓名。 (2)​ 查询1号课程的成绩高于张三的学生学号和成绩。 (3)​ 查询其他系中年龄小于计算机系年龄最大者的学生。 (4)​ 查询没有选修2号课程的学生姓名。 (5)​ 查询选修1号课程的学生最低分。 (6)​ 统计男女同学的人数。 (7)​ 求各个课程名称及相应的选课人数。 2、提高操作实验： (1)​ 检索选修2号课程的学生中成绩最高的学生的学号。 (2)​ 查询选修了全部课程的学生的姓名。 (3)​ 查询平均成绩最高的学生学号和姓名。 (4)​ 查询1号课程成绩高于所有课程总平均成绩的学生姓名。 (5)​ 查询有3门以上课程是80分以上的学生的学号及（80分以上）的课程数。 四、思考与总结 (1)​ 输入SQL语句时应注意，语句中均使用西文操作符号。 (2)​ 在使用SQL语句时，注意语句的层次嵌套关系和括号的配对使用问题。 (3)​ 子句WHERE<条件>表示元组筛选条件，子句HAVING<条件>表示组选择条件。 (4)​ 组合查询的字句间不能有语句结束符。 (5)​ 子句HAVING<条件>必须和GROUP BY<分段字段>子句配合使用。 (6)​ 试用多种形式表示实验中的查询语句，并进行比较。 (7)​ 组合查询语句是否可以用其他语句代替，有什么不同。 (8)​ 使用GROUP BY<分段条件>子句后，语句中的统计函数的运行结果有何不同。 实验4 视图的定义及数据完整性 一、教学目的和要求 (1)​ 使学生掌握视图创建方法，加深对视图的理解。 (2)​ 加深对数据的完整性的理解。 (3)​ 学会创建和使用规则、缺省和触发器。 二、实验内容 (1)​ 创建、查看、修改和删除视图。 (2)​ 创建触发器，并使用触发器。 (3)​ 创建存储过程并执行。 三、实验步骤 1、基本操作实验： (1)​ 按下列SQL描述的视图定义，创建IS_S1视图。 CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept=’IS’； (2)​ 创建触发器，并在查询分析器中执行一个可以引起触发器执行的语句来使触发器执行，并观察结果。例如，用CREATE TRIGGER T_S ON Student FOR DELETE As 定义了触发器，则执行DELETE Student WHERE<条件>语句。用SQL写出实验操作语句。 2、提高操作实验： (1)​ 针对IS_S1视图完成下列查询： a) 在信息系学生的视图中找出年龄不小于21岁的学生。 b) 查询信息系选修了1号课程的学生。 (2)​ 建立一个学生与选课表间，为维护参照完整性而使用的级联删除触发器、级联修改触发器和受限插入触发器。 (3)​ 建立并调用一个带参数的存储过程并在查询分析器中执行，观察结果。 四、思考与总结 (1)​ 参照表和被参照表之间的关系，主码和外码之间的关系。 (2)​ 各种触发器的含义，主要功能是什么。 实验5 简单应用系统的实现 一、教学目的和要求 (1)​ 综合运用前面掌握的内容并进行数据库综合应用。 (2)​ 选定任一种开发工具（PB、Delphi、VB、VC等），设计并实现一个简单的数据库应用系统，包括数据的插入、删除、修改、查询、统计等功能，做到界面友好、使用方便。 (3)​ 通过实践设计使学生加深理论的理解，从而掌握数据库设计的方法和步骤，并通过上机操作锻炼学生的动手能力。 二、实验内容 (1)​ 进行需求分析画出数据流图； (2)​ 进行概念结构设计画出实体联系模型； (3)​ 进行逻辑结构设计写出关系模式； (4)​ 进行物理结构设计并写出评价，写出评价结果； (5)​ 写出功能模块图； (6)​ 上机实施并调试运行。 三、实验步骤 参考有关关系数据库系统和系统开发工具方面的有关技术书籍。 四、思考与总结 (1)​ 在数据库开发的过程中的体会与提高。 附录：数据库实验报告格式 专业(类)班级 姓名 学号 日期 1、​ 实验名称 ： 2、​ 实验目的和要求： 3、​ 实验程序源代码： 4、​ 总结与体会： 附录2：SQL Server 2000使用指南 1 SQL Server 2000简介 SQL Server 2000 是Microsoft 公司推出的SQL Server 数据库管理系统的最新版本。该版本继承了SQL Server 7.0 版本的优点，同时又比它增加了许多更先进的功能，具有使用方便、可伸缩性好、与相关软件集成程度高等。优点可跨越从运行Microsoft Windows 98 的膝上型电脑到运行Microsoft Windows 2000 的大型多处理器的服务器等多种平台使用。 2 SQL Server 2000的版本 SQL Server 2000 的常见版本如下： ​ 企业版(Enterprise Edition)，支持所有的SQL Server 2000 特性，可作为大型Web 站点、OLTP 联机事务处理以及数据仓库系统等的产品数据库服务器。 ​ 标准版(Standard Edition)，用于小型的工作组或部门。 ​ 个人版(Personal Edition)，用于单机系统或客户机。 ​ 开发者版(Developer Edition)，用于程序员开发应用程序，这些程序需要SQL Server 2000 作为数据存储设备。此外，SQL Server 2000 还有桌面引擎Desktop Engine 和Windows CE 版用户，可以根据实际情况选择所要安装的SQL Server 2000 版本。 3 SQL Server 2000实用工具 SQL Server 2000 包含一系列的管理开发工具使其安装调用管理及使用都十分便捷。 3.1 企业管理器 企业管理器（Enterprise Manager）是SQL Server 中最重要的管理工具。在使用SQL Server的过程中，大部分的时间都是和它打交道。通过企业管理器可以管理所有的数据库系统工作和服务器工作，也可以调用其它的管理开发工具。 用户可以通过开始菜单或快捷方式启动企业管理器。启动后，界面如图1 所示。企业管理器的界面是一个标准的Windows界面 ，由标题栏、菜单栏、工具条树窗口和任务对象窗口（又称任务板）组成。 图1 Enterprise Manager 启动界面 企业管理器的菜单栏分为两层，上一层是主菜单栏，下一层是控制台菜单栏。主菜单在程序运行的过程中是基本保持不变的，控制台菜单栏中的菜单则是动态的，随着所进行操作的不同而显示不同的菜单内容。其中，操作（Action）和查看（View）两个菜单是动态化最明显，随着所进行操作的不同而显示不同的菜单项。尤其是“操作(Action) ”菜单，它的菜单项与当时的快捷菜单的内容相同。而工具Tools菜单的菜单项在使用企业管理器的过程中是保持不变的，只是应操作的不同而启用或禁用。 3.2 服务管理器 服务管理器（Service Manager）用于启动、暂停或停止数据库服务器——SQL Server SQL、Server Agent、MSDTC（Microsoft Distributed Transaction Coordinator，微软分布式事务协调器）。 3.2.1 启动服务管理器的方法 ​ 用Enterprise Manager 启动 在Enterprise Manager 的SQL Server Group 中，用左键单击所要启动的服务器或在所 要启动的服务器上，单击右键后从快捷菜单中选择Start 项，即可启动。 ​ 用SQL Server Service Manager 启动 启动方法如下： 1．从菜单中选择Service Manager 选项启动服务管理器，服务管理器启动后，如图2所示。 2．在Service Manager 中选择要启动的服务器Server 和服务Service 选项。 3．在Service Manager 中点击 按钮，即Start/Continue 选项启动服务器。启动后画面如图2 所示。 图2 Service Manager ​ 自动启动服务管理器 可以在操作系统启动时启动服务器，可以在Service Manager 中选择自动启动选项，如图2 所示。也可以在操作系统的计划任务中设置自动启动或在服务器属性配置中指定。 3.2.2 注册服务管理器 在SQL Server 中可以使用注册向导方式来注册服务管理器，步骤如下： 1.​ 在“程序”菜单中寻找“Microsoft SQL Server”菜单项。 2.​ 如果“Microsoft SQL Server”菜单项下有“服务管理器”，则启动服务管理器，在其界面中启动SQL SERVER服务。 3.​ 在“Microsoft SQL Server”菜单项下打开“企业管理器”。 4.​ 点击工具栏上的 图标，注册一个SQL SERVER服务器。 5.​ 在注册向导的“可用服务器”中选择一个服务器添加到“添加的服务器”中，点击下一步。 6.​ 选择“系统管理员分配的SQL Server登录信息”。 7.​ 填写登录名和密码。 8.​ 不改变默认选项，添加到现有组中。 9.​ 完成SQL Server注册。 3.2.3 连接与断开服务管理器 在Enterprise Manager 的SQL Server Group 中用左键单击所要连接的服务器或在所要启动的服务器上单击右键后，从快捷菜单中选择Connect 项，即可启动。如果在注册服务器时选择了，在SQL Server 启动时输入账号和口令选项则会 提示 春节期间物业温馨提示小区春节期间温馨提示物业小区春节温馨提示春节物业温馨提示物业春节期间温馨提示 输入它们。 在所要断开的服务器上单击右键后，从快捷菜单中选择Disconnect 选项，就可以断开服务器，在关闭Enterprise Manager 时也会自动断开服务器。 3.3 查询分析器 查询分析器（Query Analyzer）用于执行Transaction-SQL 命令等（SQL Scripts SQL 脚本），程序以查询分析或处理数据库中的数据。这是一个非常实用的工具，对掌握SQL 语言，理解SQL Server 的工作有很大帮助。SQL Query Analyzer窗口如图3所示。可以通过在编辑器窗格中创建或打开脚本并按F5键来执行完整的脚本。 图3 SQL查询分析器 4 创建数据库 4.1 文件与文件组 在SQL Server 中，数据库是由数据库文件和事务日志文件组成的。一个数据库至少应包含一个数据库文件和一个事物日志文件。 ​ 数据库文件（Database File） 数据库文件是存放数据库数据和数据库对象的文件。一个数据库可以有一个或多个数据库文件。一个数据库文件只属于一个数据库，当有多个数据库文件时，有一个文件被定义为主数据库文件（Primary Database File），扩展名为mdf，它用来存储数据库的启动信息和部分或全部数据。一个数据库只能有一个主数据库文件，其它数据库文件被称为次数据库文件（Secondary Database File），扩展名为ndf，用来存储主文件没存储的其它数据。 ​ 事务日志文件（Transaction Log File） 事务日志文件是用来记录数据库更新情况的文件，扩展名为ldf。例如使用INSERT、UPDATE、DELETE 等对数据库进行更改的操作，都会记录在此文件中。而如SELECT等对数据库内容不会有影响的操作，则不会记录在案。一个数据库可以有一个或多个事务日志文件，SQL Server 中采用“Write-Ahead（提前写）”方式的事务，即对数据库的修改先写入事务日志中再写入数据库。 ​ 文件组（File Group） 文件组是将多个数据库文件集合起来形成的一个整体，每个文件组有一个组名。与数据库文件一样，文件组也分为主文件组（Primary File Group）和次文件组（Secondary File Group），一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用。主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其它文件，在次文件组中可以指定一个缺省文件组，那么在创建数据库对象时，如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。 4.2 系统数据库 SQL Server 2000有四个系统数据库（System Databases）：Master、Model、Msdb、Tempdb， 这些系统数据库的文件存储在Microsoft SQL Server 默认安装目录的MSSQL子目录的Data 文件夹中。 4.2.1 Master 数据库 Master 数据库记录了一个SQL Server 系统的所有系统信息。这些系统信息主要有：所有的登录信息；系统设置信息；SQL Server 初始化信息；系统中其它系统数据库和用户数据库的相关信息包括其主文件的存放位置等。 4.2.2 Model 数据库 Model 数据库是所有用户数据库和Tempdb 数据库的创建模板。当创建数据库时，系统会将Model 数据库中的内容复制到新建的数据库中去。 4.2.3 Msdb 数据库 SQL Server SQL Server Enterprise Manager 和SQL Server Agent 使用Msdb 数据库来存储计划信息以及与备份和恢复相关的信息。尤其是SQL Server Agent 需要使用它来执行安排工作和警报、记录操作者等操作。 4.2.4 Tempdb 数据库 Tempdb 数据库用作系统的临时存储空间，其主要作用有：存储用户建立的临时表和临时存储过程；存储用户 说明 关于失联党员情况说明岗位说明总经理岗位说明书会计岗位说明书行政主管岗位说明书 的全局变量值；为数据排序创建临时表；存储用户利用游标说明所筛选出来的数据。在Tempdb 数据库中所做的操作不会被记录，因而在Tempdb 数据库中的表上进行数据操作比在其它数据库中要快得多。 4.3 用企业管理器创建数据库 在企业管理器(Enterprise Manager) 中，可以按下列步骤来创建数据库： 1. 单击工具栏中的 图标，或在服务器的Databases 文件夹或其下属数据库图标上单击右键，选择New Database…选项，即会出现如图4 所示的对话框。 2. 指定数据库的名称和编码方式如图4 所示。 3. 点击Transaction Log 页框指定数据库文件的名称、存储位置、初始容量大小和所属文件组，如图4所示。 4. 进行数据库文件大小扩充方式和容量限制设置。 5. 点击Transaction Log页框，指定事务日志文件的名称、存储位置和初始容量大小。 6. 进行事务日志文件大小、扩充方式和容量限制设置。 7．单击确定按钮，则建新数据库。 5 创建和修改数据表 5.1 创建表 分别使用企业管理器和Transact-SQL语句按下列要求创建两个数据表: 表B1项目数据表，表B2员工数据表，并按照步骤完成对表的相关修改和约束设置。后面的一些操作实例都是针对这两个表进行的。 表一 项目数据表（Project） 字段字段名 数据类型 字段长度 注释 项目编号 int 主键 名称 varchar 长度为50 负责人 int 客户 int 开始日期 datetime 结束日期 datetime 表二 员工数据表(Employee) 字段名 数据类型 字段长度 注释 编号 int 主键 姓名 varchar 长度为50 性别 varchar 长度为50 部门 varchar 长度为50 工资 money 长度为8 ​ 使用企业管理器创建数据表并添加约束 1.​ 打开企业管理器，在树状目录窗口中找到数据库节点TestDB，并选中下一级节点“表”。 2.​ 单击鼠标右键，从弹出菜单中选择命令“新建表”，打开表设计窗口，在窗口中按照“上机任务”中所列的“项目数据表”的字段及要求键入列名、数据类型、长度等属性，并将“项目编号”设置为主键。如图5所示。 3.​ 单击“保存”按钮，在弹出的对话框中输入表名称“项目数据表”，关闭表设计窗口，完成表“项目数据表”的创建。 4.​ 重复1-3步，创建另一个数据表“员工数据表”，表设计窗口如图6所示。 5.​ 添加外键约束：在“项目数据表”的“负责人”字段上添加外键约束，参照字段为“员工数据表”中的字段“编号”，约束名为FK_pm。实现方法为： 首先在企业管理器中选中“项目数据表”并单击鼠标右键，在弹出菜单中选择命令“设计表”打开设计表窗口。然后在该窗口中单击按钮“表和索引属性”按钮，打开表的“属性”对话框。最后选择“关系”选项卡，在该选项卡中做如下的操作。 单击“新建”按钮；从“主键表”中选择“员工数据表”，并选择字段“编号”；从“外键表”中选择“项目数据表”，并选择字段“负责人”；在“关系名”中输入FK_pm。单击“关闭”按钮，完成外键约束的设置。 6.​ 添加检查约束：将“员工数据表”中的字段“工资”的值限定在1000-10000，约束名CK_Salary。 操作方法为： 首先在企业管理器中选中"“员工数据表”并单击鼠标右键，在弹出菜单中选择命令“设计表”打开设计表窗口。然后在该窗口中单击按钮“表和索引属性”按钮，打开表的“属性”对话框。最后选择“CHECK约束”选项卡，在该选项卡中做如下的操作： 单击标签中“新建”按钮；在“约束表达式”文本框中输入一个条件表达式(工资>=1000 AND 工资<=10000)，在“约束名”对话框中输入所建核查约束名“CK_Salary” 。如图7所示。 ​ 使用Transact-SQL语句创建数据表并添加约束 1 创建项目数据表 2 创建员工数据表 3 添加外键约束 4 添加检查约束 ALTER TABLE 项目数据表 ALTER TABLE 员工数据表 ADD CONSTRAINT PK_pm ADD CONSTRAINT CK_pm FOREIGN KEY(负责人) CHECK(工资>=1000 AND 工资<=10000) REFERENCES 员工数据表（编号） 5.2 修改表 当表创建好后，可能根据需要对表的列约束等属性进行添加、删除或修改，这就需要修改表结构。 ​ 用企业管理器修改 在企业管理器Enterprise Manager 中选择要进行改动的表，单击右键，从快捷菜单中选择Design Table 选项，则会出现如图8 所示的修改表结构对话框，可以在图8所示的对话框中修改列的数据类型、名称等属性或添加、删除列，也可以指定表的主关键字约束，单击工具栏中的图标 ，出现如上图7 所示的编辑表和约束的属性的对话框，可以在其中编辑各种约束和一些表的属性。 ​ 用ALTER TABLE 命令修改 ALTER TABLE命令可以添加或删除表的列约束，也可以禁用或启用已存在的约束或触发器。通过例题讲解。 例：创建一个定货商信息表，然后修改简介列的数据类型。 create table order_firm (order_firm_id char (8) primary key, firm_name varchar (50) not null firm_introduce char(50) null ) on [primary] alter table order_firm alter column firm_introduce varchar(250) null 5.3 删除表 ​ 用企业管理器删除 在企业管理器Enterprise Manager 中，用右键单击要删除的表，从快捷菜单中选择删除Delete选项，则会出现如图9 所示的删除对象对话框，单击Drop All 按钮，即可以删除表。单击Show Dependencies 按钮，即会出现一个对话框，列出了表所依靠的对象和依赖于表的对象，当有对象依赖于表时，就不能删除表。 图9 删除对象对话框 ​ 用DROP TABLE 命令删除 DROP TABLE命令可以删除一个表和表中的数据及其与表有关的所有索引触发器、约束、许可对象与表相关的视图和存储过程。需要用DROP VIEW 和DROP PROCEDURE命令来删除。 DROP TABLE命令的语法如下： DROP TABLE table_name 要删除的表如果不在当前数据库中，则应在table_name 中指明其所属数据库和用户名。在删除一个表之前，要先删除与此表相关联的表中的外关键字、约束。当删除表后，绑定的规则或缺省值会自动松绑。 6 创建索引 6.1 用CREATE INDEX 命令创建索引 CREATE INDEX既可以创建一个可改变表的物理顺序的簇索引，也可以创建提高查询性能的非簇索引。举例：使用T-SQL语句创建索引： USE TestDB GO CREATE INDEX IDX_Name ON 员工数据表(姓名) GO 6.2 使用企业管理器创建索引 (1)​ 打开企业管理器，在树状目录中展开数据表“员工数据表”所在的数据库节点TestDB，并选择其下一级节点“表”。 (2)​ 选择表“员工数据表”，并单击鼠标右键。在弹出菜单中选择命令“所有任务〉管理索引”，打开“管理索引”对话框。 (4)​ 在“管理索引”对话框中的“数据库”下拉菜单中选择数据库表。 (5)​ 选择“新建”按钮，弹出“新建索引”对话框。 (6)​ 在“新建索引”对话框的“索引名”文本框中输入索引名称“IDX_Name”，然后选择字段“姓名”并设置索引属性，点击确定按钮后，如图10所示。 7 存储过程 存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 在SQL Server 的系列版本中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中，并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。用户自定义存储过程是由用户创建，并能完成某一特定功能，如查询用户所需数据信息的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。 7.1 存储过程的优点 当利用MS SQL Server 创建一个应用程序时，Transaction-SQL 是一种主要的编程语言。若运用Transaction-SQL 来进行编程，有两种方法。其一是，在本地存储Transaction-SQL 程序并创建应用程序，向SQL Server 发送命令来对结果进行处理。其二是，可以把部分用Transaction-SQL 编写的程序作为存储过程存储，在SQL Server 中并创建应用程序来调用存储过程对数据结果进行处理。存储过程能够通过接收参数，向调用者返回结果集。结果集的格式由调用者确定，返回状态值给调用者指明调用是成功或是失败，包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。 我们通常更偏爱于使用第二种方法，即在SQL Server 中使用存储过程而不是在客户计算机上调用Transaction-SQL 编写的一段程序。 7.2 创建存储过程 在MS SQL Server 2000 中，创建存储过程有两种方法。一种是使用Transaction-SQL 命令Create Procedure ，另一种是使用图形化管理工具Enterprise Manager。用Transaction-SQL 创建存储过程是一种较为快速的方法，但对于初学者，使用Enterprise Manager 更易理解，更为简单。 ​ 使用企业管理器创建存储过程 按照下述步骤用企业管理器Enterprise Manager 创建一个存储过程： 1．启动Enterprise Manager 登录到要使用的服务器。 2．选择要创建存储过程的数据库，在左窗格中单击Stored Procedure 文件夹。此时在右窗格中显示该数据库的所有存储过程。 3．右击Stored Procedure 文件夹，在弹出菜单中选择New Stored Procedure, 此时打开创建存储过程对话框。 4．输入存储过程正文。 5．单击Check Syntax，检查语法是否正确。 6．单击OK 保存。 7．在右窗格中右击该存储过程，在弹出菜单中选择All task, 选择Manage Permissions，设置权限。 ​ 用CREATE PROCEDURE 命令创建存储过程 通过运用Create Procedure 命令创建存储过程。 例：在数据库Company_Data中创建存储过程Procedure_SalaryByDept，要求返回某一特定部门所有员工的工资总和，其中特定部门的名称以存储过程的输入参数进行传递。 创建步骤如下所述： （1）打开查询分析器。 （2）在查询窗口书写 CREATE PROCEDURE Transact-SQL语句创建存储过程。Procedure_SalaryByDept，并带有一个输入参数@Department用于传递部门名称，一个输出参数@TotalSalary用于传递输出结果。 CREATE PROCEDURE Procedure_SalaryByDept @Department varchar(40), @TotalSalary money OUTPUT AS （3）选择所属部门为该参数传递的部门名称的所有记录，并对其工资字段求和。 SELECT @Tota1Sa1ary=sum(工资) FROM员工数据表 WHERE所属部门=@Department GO （4）报告该部门的工资总额。 PRINT'该部门的工资总额’+CONVERT(varchar,@TotalSalary) GO （5）执行这个创建存储过程的语句。 （6）执行系统存储过程sp_help查看该存储过程的一般信息。 （7）执行该存储过程，分别计算项目部、录入部和检验部的工资总额。 （8）用ALTTER PROCEDURE语句将存储过程加密。 8 触发器 触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程，触发器主要是通过事件进行触发而被执行的。而存储过程可以通过存储过程名字而被直接调用，当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。 8.1 触发器的种类 SQL Server 2000 支持两种类型的触发器：AFTER 触发器和INSTEAD OF触发器。其 中AFTER 触发器即为SQL Server 2000 版本以前所介绍的触发器，该类型触发器要求只有执行某一操作INSERT、UPDATE、DELETE之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器，对于AFTER 触发器可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder 来完成此任务。 INSTEAD OF触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE）而仅是执行触发器本身。既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF 触发器。 8.2 创建触发器 ​ 用企业管理器Enterprise Manger 创建触发器 其操作步骤如下： 1 启动Enterprise Manger 登录到指定的服务器上。 2 展开数据库，然后展开要在其上创建触发器的表所在的数据库，然后单击该表。 3 右击鼠标，在弹出菜单中选择ALL Tasks, 然后单击Manage Triggers…。 4 在名字框中选择new，在文本框中输入触发器文本，如图11 所示。 5 单击Check Syntax，检查语句是否正确。 6 单击Apply，在Name下拉列表中会有新创建的触发器名字。 7 单击OK，关闭窗口，创建成功。 ​ 用CREATE TRIGGER 命令创建触发器 下面以一个例子为例，介绍用CREATE TRIGGER 命令创建触发器的方法。 例：在数据库 Company_Data的表“项目数据表”和“员工数据表”中分别创建触发器。 (1) 打开查询分析器。 (2) 在查询窗口书写 CREATE TRIGGER语句，基于表“员工数据表”创建AFTER INSERT 触发器 Tigger_NewEmployeeSalary，将插入员工的工资额限制在5000以内。 USETestDB. GO CREATE TRIGGERTrigger_NewEmployeeSalary ON 员工数据表 AFTER INSERT AS IF (SELECT 工资 FROM inserted) > 5000 BEGIN PRINT '新员工工资不能超过5000' ROLLBACK END GO (3)在查询窗口书写CREATE TRIGGER语句，基于表“员工数据表”创建AFTER UPDATE触发器Trigger_SalaryChange，将员工工资变动额限制在2000以内。 CREATE TRIGGER Trigger_SalaryChange ON员工数据表 AFTER UPDATE AS IF UPDATE(工资) BEGIN IF (SELECT MAX(ABS(inserted.工资一de1eted.工资)) FROM inserted JOIN deleted ON inserted.编号 = deleted.编号) > 2000 BEGIN PRINT ‘工资变动不能超过 2000' ROLLBACKTRANSACTION END END 9 备份和恢复 备份和恢复组件是SQL Server 的重要组成部分。备份就是指对SQL Server 数据库或事务日志进行拷贝数据库，备份记录了在进行备份这一操作时数据库中所有数据的状态。如果数据库因意外而损坏，这些备份文件将在数据库恢复时被用来恢复数据库。 下面使用SQLServer2000企业管理器备份和恢复数据库TestDB，步骤如下所述： (1)​ 打开企业管理器，在控制面板目录中选择数据库TestDB。 (2)​ 单击鼠标右键，从弹出菜单中选择命令“所有任务〉备份数据库”。 (3)​ 在出现的“SQLServer备份”对话框中选择"常规"选项卡。 (4)​ 在该选项卡中的“名称”文本框中键入“TestDB数据库备份”。 (3)​ 在“描述”文本框中键入“备份集1”。 (4)​ 在“备份”对话框中选中“数据库-完全”选项，如图12所示。 (5)​ 单击“添加”按钮，在弹出的对话框中选择“备份设备”，然后从下拉列表中选择备份使用的备份设备(注意：如果没有现成的备份设备，应在备份以前创建一个)。 (6)​ 单击“重写现有媒体”选项按钮。 (7)​ 单击“选项”选项卡，在该选项卡中选中“完成后验证备份”复选框。 (8)​ 单击“确定”按钮，出现“备份进度”对话框，备份完成后，将弹出消息框“备份操作及其验证已成功完成”。 (9)​ 单击“确定”完成数据库的备份。 (10)​ 完成数据库的还原，方法如下： 从企业管理器的树状目录窗口中选择需要还原的数据库TestDB，单击鼠标右键，从弹出菜单中选取命令“所有任务/还原数据库”，打开“还原数据库”对话框，如图13所示。然后在该对话框中选择“还原--从设备”，单击“选择设备”按钮，并从弹出的对话框中选择备份设备TestDB_backup。最后选择“还原备份集”下的“数据库---完全”，单击“确定”按钮执行数据库的还原。 10 用户和安全性管理 SQL Server 2000 的安全性管理是建立在认证authentication 和访问许可permission 两者机制上的。认证是指来确定登录SQL Server 的用户的登录账号和密码，是否正确以此来验证其是否具有连接SQL Server 的权限。 10.1 SQL Server 的登录认证 ​ WINDOWS 认证模式 SQL Server数据库系统通常运行在NT服务器平台或基于NT构架的WINDOWS 2000上，而NT作为网络操作系统，本身就具备管理登录、验证用户合法性的能力。所以WIN- DOWS 认证模式正是利用这一用户安全性和账号管理的机制，允许SQL Server 也可以使用NT 的用户名和口令。 ​ SQL Server 认证 在该认证模式下，用户在连接SQL Server 时，必须提供登录名和登录密码，这些登录信息存储在系统表syslogins 中，与NT的登录账号无关。SQL Server自己执行认证处理，如果输入的登录信息与系统表syslogins中的某条记录相匹配，则表明登录成功。 ​ 混合认证模式 在混合认证模式下，WINDOWS 认证和SQL Server 认证这两种认证模式都是可用的。NT的用户既可以使用NT认证，也可以使用SQL Server 认证。 10.2 数据库用户的管理 数据库用户用来指出哪一个人可以访问哪一个数据库。在一个数据库中，用户ID唯一标识一个用户。用户对数据的访问权限以及对数据库对象的所有关系，都是通过用户账号来控制的。 ​ 利用SQL Server Enterprise Manager管理数据库用户 利用SQL Server Enterprise Manager创建一个新数据库用户要执行以下步骤： 1. 启动SQL Server Enterprise Manager，单击登录服务器旁边的+标志。 2. 打开Database 文件夹，打开要创建用户的数据库。 3. 右击User 图标，在弹出菜单中选择New Database User，弹出New Database User – New User 对话框。 4. 在Login选择框内选择已经创建的登录账号，在User选择框内输入数据库用户名称。 5. 在Database role membership 下的选项框中为该用户选择数据库角色。 6. 单击确定按钮。 ​ 查看删除数据库用户 在企业管理器中选中User 图标，创建数据库用户的第三步，则在右面的窗格中显示当前数据库的所有用户。选中User 图标后，在右面窗格中右击想要删除的数据库用户，则会弹出选项菜单，然后选择Delete，则会从当前数据库中删除该数据库用户。 10.3 角色管理 在SQL Server 中，主要有两种角色类型：服务器角色与数据库角色。 服务器角色是指根据SQL Server的管理任务以及这些任务相对的重要性等级来，把具有SQL Server 管理职能的用户划分成不同的用户组，每一组所具有管理SQL Server的权限已被预定义服务器角色，适用在服务器范围内，并且其权限不能被修改。 数据库角色能为某一用户或一组用户授予不同级别的管理或访问数据库或数据库对象的权限。这些权限是数据库专有的，而且还可以使一个用户具有属于同一数据库的多个角色。SQL Server 提供了两种角色类型：预定义的数据库角色，用户自定义的数据库角色。 使用SQL Server Enterprise Manager 创建数据库角色。虽然我们不能创建自己的服务器角色，但可以创建自定义的数据库角色。可以按以下步骤执行： 1. 启动SQL Server Enterprise Manager 登录到指定的服务器。 2. 展开指定的数据库选中Roles 图标。 3. 右击图标，在弹出菜单中选择New Database Row，弹出Database Role Properties – New Role 对话框。 4. 在Name 框中输入该数据库角色的名称。 5. 在Database Role Type 选项栏中，选择数据库角色类型：标准角色或应用角色。如果选择 Standard Role，可单击Add 按钮，将数据库用户增加到新建的数据库角色当中。如果选择了 Application Role，则在Password 框中输入口令。 6. 按确定按钮。