数据库实验指南.doc

实验一 熟悉SQL Server 2000的运行环境和系统配置一、实验目的1.熟悉SQL Server 2000上机环境以及相应的配置；2.掌握SQL Server 2000的启动和管理工作；3.熟练掌握SQL Server 2000的企业管理器。4.熟练掌握SQL Server 2000的查询分析器。本实验需2学时。二、实验内容1.熟悉数据库SQL Server 2000的运行环境。2.熟练掌握“企业管理器”的使用。3.熟练掌握“查询分析器”的使用。4.学会使用“联机从书”。三、实验任务1.启动SQL Server 2000的服务管理器在程序菜单中选择“Microsoft SQL Server”，再选中“服务管理器”，如图1-1所示。图1-1 选择服务管理器点击“服务管理器”后，出现“SQL Server服务管理器”，如图1-2所示。图1-2 服务管理器点击“开始/继续”按钮，启动SQL Server 2000数据库服务。图1-3 启动后的服务管理器启动成功后，在Windows的系统工具栏的时钟旁边出现一个符号。2.启动SQL Server 2000的企业管理器在程序菜单中选择“Microsoft SQL Server”，再选中“企业管理器”，如图1-4所示：图1-4 选择企业管理器点击“企业管理器”后，出现“企业管理器”的主界面，如图1-5所示。图1-5 企业管理器3.注册SQL 服务器第一步：在“企业管理器”中，展开树形列表中的“Microsoft SQL Servers”项目，可以看到下面有一个子项目“SQL Server组”。用鼠标右键点击该项目图标，在弹出的菜单中选“新建 SQL Server注册”菜单项，如图1-6所示。图1-6 注册SQL 快捷菜单第二步：“企业管理器”启动向导帮助下进行注册。选择“可用的服务器”，如图1-7和1-8所示。然后点击“下一步”按钮，出现如图1-9所示的对话框，选择“SQL Server身份验证”的方式。又点击“下一步”按钮，出现如图1-10所示的对话框，填入登录名和密码，本次实验使用的登录名为“sa”,密码为空。然后点击“下一步”按钮，按向导默认方式如图1-11、1-12和1-13完成SQL服务器注册。 图1-7 图1-8图1-9图1-10图1-11图1-12图1-134.启动SQL Server 2000的查询分析器在程序菜单中选择“Microsoft SQL Server”，然后再选中“查询分析器”，如图1-14所示。图1-14点击“查询分析器”后，出现连接到SQL Server对话框，如图1-15所示。图1-15再点击“确定”按钮。出现SQL的“查询分析器”主界面，如图1-16所示。图1-16实验二 建立数据库、表和数据完整性一、实验目的1.了解SQL Server 2000数据库的逻辑结构和物理结构；2.掌握使用向导创建和删除数据库的方法；3.掌握使用SQL 语句创建和删除数据库；4.掌握数据完整性的基础知识；5.熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引。本实验需10学时。二、实验内容1.使用向导创建和删除数据库2.使用命令创建和删除数据库3.利用向导和SQL语句创建和删除表4.创建和删除索引三、实验任务1.使用向导创建和删除数据库设有一个学生信息管理系统，其数据库名为“student”，初始大小为5MB，最大为100MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到10MB，按1MB增长。数据库的逻辑文件名为“student_data”, 物理文件名为“student_data.mdf，存放路径为“D:student_data”。日志文件的逻辑文件名为“student_log”, 物理文件名为“student_log.ldf”，存放路径为“D:student_data”。(1)使用向导创建上述描述的数据库。启动SQL Server 2000的“企业管理器”，点击“企业管理器”的左边树状控制栏的“+/”号可以打开和关闭SQL Server组中的本地服务项目，在选中数据库服务项目后，单击鼠标右键，出现数据库的快捷菜单，如图2-1所示。图2-1点击“新建数据库”命令，出现新建数据库对话框，如图2-2所示。图2-2在该对话框中输入数据库名称：student，点击“数据文件”选项卡，如图2-3所示。图2-3在数据文件中，设置数据文件的名称、所在目录以及文件的大小等信息。完成后点击“事务日志”选项卡，如图2-4所示。图2-4事务日志中，设定日志文件的名称、所在目录以及文件的大小等信息。接受系统的缺省值，点击确定按钮，完成数据库的新建，如图2-5所示。图2-5(2)使用向导删除上面建立的数据库。右击数据库“student”，出现如图2-6所示快捷菜单，点击“删除”命令，弹出如图2-7所示对话框，点击“是”按钮就会完成删除已建立的数据库。 图2-6 图2-72.使用命令创建和删除数据库(1)命令创建数据库首先启动SQL Server2000的“查询分析器”，然后在“查询分析器”的命令窗口输入下列命令：create database studenton(name=student_data, /*数据文件的逻辑名称,注意不能与日志逻辑同名*/filename=d:student_datastudent_data.mdf ,/*物理名称，注意路径必须存在*/size=5, /*数据初始长度为M*/maxsize=100, /*最大长度为M*/filegrowth=5%) /*数据文件每次增长%*/log on( name=student_log, filename=d:student_datastudent_log.ldf , size=2,maxsize=10, filegrowth=1)go /*运行上诉语句建立数据库student*/(2)删除数据库student。在“查询分析器”的命令窗口输入下列命令：drop database student /*删除数据库的命令*/go3.利用向导和SQL语句创建和删除表(1) 向导创建表假定已建立的数据库student中，根据分析需要如下几个表：表2.1 student表（学生信息表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号snamechar8NOT NULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系spnochar8NULL专业代码（外键）classnochar4NULL班级号entimesmalldate4NULL学生入校时间homevarchar40NULL学生家庭住址telvarchar40NULL学生联系电话表2.2 course表（课程信息表）字段名称类 型宽 度允许空值主 键说 明cnochar10NOT NULL是课程编号spnochar8NULL专业代码（外键）cnamechar20NOT NULL课程名称ctnotinyint1NULL课程类型编号（外键）experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分表2.3 student_course表（学生选课成绩表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号tcidsmallint2NOT NULL是上课编号scoretinyint1NULL学生成绩表2.4 teacher表（教师信息表）字段名称类 型宽 度允许空值主 键说 明tnochar8NOT NULL是教师编号tnamechar8NOT NULL教师姓名sexchar2NULL教师性别birthdaysmalldate4NULL教师出生日期dnochar6NULL教师所在院系pnotinyint1NULL教师职务或职称编号homevarchar40NULL教师家庭住址zipcodechar6NULL邮政编码telvarchar40NULL联系电话emailvarchar40NULL电子邮件表2.5 teacher_course表（教师上课课表）字段名称类 型宽 度允许空值主 键说 明tcidsmallint2NOT NULL是上课编号tnochar8NULL教师编号（外键）spnochar8NULL专业代码（外键）classnochar4NULL班级号cnochar10NOT NULL课程编号（外键）semesterchar6NULL学期schoolyearchar10NULL学年classtimevarchar40NULL上课时间classroomvarchar40NULL上课地点weektimetinyint1NULL每周课时数步骤：右键单击student的子目录“表”，如图2-8所示，选择“新建表”命令，弹出如图2-9所示的对话框，在新表中输入“列名”，选择“类型”与“宽度”，并设置“sno“为主键，右键单击“sno”,单击“设置主键”，按照表2.1的内容输入各字段及属性，关闭，保存表名为“student”。图2-9图2-9同理可依次建立表student_course，teacher，teacher_course。(2)用企业管理器删除所建立的表步骤：右键单击student的子目录“表”中的表“student”，如图2-10所示，选择“删除”命令，弹出如图2-11所示的对话框，点击“全部除去”按钮后删除表“student”。图2-10图2-11(3)用SQL语句创建表在查询分析器中分别输入下列命令：创建student表代码如下：create table student(sno char(8) NOT NULL,sname char(8) NOT NULL,sex char(2) NULL,birthday smalldatetime NULL,dno char(6) NULL,spno char(4) NULL,clssno char(4) NULL,entime smalldatetime NULL,home varchar(40) NULL,tel varchar(40) NULL,constraint pk_student primary key (sno)Go创建course表代码如下：create table course( cno char(10) NOT NULL primary key, spno char(8) NULL, cname char(20) NOT NULL, ctno tinyint NULL, experiment tinyint NULL, lecture tinyint NULL, semester tinyint NULL, credit tinyint NULL,)Go创建teacher表代码如下：create table teacher( tno char(8) NOT NULL primary key, tname char(8) NOT NULL, sex char(2) NOT NULL, birthday smalldatetime NULL, dno char(6) NULL, pno tinyint NULL, home varchar(40) NULL, zipcode char(6) NULL, tel varchar(40) NULL, email varchar(40) NULL)go创建teacher_course表代码如下：create table teacher_course( tcid smallint NOT NULL primary key, tno char(8) NULL , spno char(8) NOT NULL, classno char(4) NULL, cno char(10) NOT NULL, semester char(6) NULL, schoolyear char(10) NULL, classtime varchar(40) NULL, classroom varchar(40) NULL, weektime tinyint NULL,foreign key(tno) references teacher(tno)go(4)查询分析器中用sql语句删除表Tearch_course和表Teacher代码：drop table student.dbo.teacher_course或 drop table teacher_coursedrop table student.dbo.teacher或 drop table teacher4.创建和删除索引（1）利用向导建立索引在student表的sname列上建立普通降序索引。步骤1：右击表student弹出快捷菜单，选择“管理索引”命令，如图2-12所示，就会弹出如图2-13所示的“管理索引”对话框。图2-12图2-13步骤2：选择“管理索引”对话框中“新建”按钮，就会弹出如图2-14所示“新建索引”对话框。图2-14步骤3：输入“索引名称”为sname_index，选择sname列并选中“排列次序”为“降序”，单击“确定”后就完成在student表的sname列上建立普通降序索引(2)利用SQL建立索引对course表的cname列建立索引，在查询分析器编辑窗口中输入下列命令并执行：use student -切换到指定的数据库if exists(select name from sysindexs where name=cname drop index ame -删除索引create index cname on course(cname) -建立索引go(3)删除索引将student_course表的sno列上的聚集索引删掉。四、思考练习1利用企业管理器建立xuanke数据库2在实验任务创建的数据库，利用查询分析器完成课本P82三个表的创建。3.向Student 表增加“入学时间”列（列名为Scome,日期型）4.将年龄的数据类型改为半字长整数。5.为Student中sname添加列级完整性约束，不能为空。为Student中sno添加列级完整性约束，取值唯一，不能为空。6.为Score表按学号升序和课程号降序分别建立索引,索引名分别为SC_NDXSNO和SC_NDXCNO。为Score表按分数降序建立索引，索引名为GRADE_NDX。7删除索引SC_NDX_SNO。8删除Student中sname列级完整性约束9为SC建立按学号升序和课程号降序建立唯一索引。10建立Student、Course及SC的主码约束11建立Student与SC、Course与SC之间的外码约束12实现学生性别只能是“男”或“女”的Check(检查)约束。13实现学生年龄只能小于等于30大于17的语义约束14用Create Index对表student的sname字段建立一个升序索引，索引名Indexs。15用Drop Index删除索引Indexs。实验三 数据查询 一、实验目的学会使用企业管理器查询数据，掌握使用SQL语句查询数据本实验需6学时。二、实验内容1. 企业管理器查询数据2.命令方式单表查询、多表查询、复合条件查询三、实验任务1.用企业管理器进行数据查询 1）启动企业管理器2）右键单击要查询的表,在弹出的快捷菜单中选择打开表,如图3-1所示，其下级菜单中的查询命令含义如下 ： 返回所有行(A) -该命令以网格方式显示指定表的所有记录 返回首行(T). -该命令以交互方式输入要返回的行数 查询(Q) -打开查询窗口,以图形方式设计查询 图3-13）点击“查询”，出现查询设计器窗口，选择需要字段，并运行查询，如图3-2所示。4)多表查询，在图3-2中，右键点击数据表关系窗格，在弹出菜单中点击“添加表”，出现添加表窗口，如图3-3所示。5）在图3-3中点击SC表，然后点击“添加”，将SC表添加到查询窗口，然后选择课程号和成绩列，如图3-4所示。对查询字段重新命名SQL查询语句窗格查询条件列查询结果数据表关系窗格图3-2运行查询图3-3图3-43.命令方式进行数据查询打开查询分析器。练习教材3.4节中例1-例52实验四 数据更新一、实验目的掌握使用SQL语言进行数据更新的方法。本实验需4学时。二、实验内容命令方式添加、修改、删除数据三、实验任务打开查询分析器。1.用命令方式向student表添加一条记录(20021517,金胜男，女，19，IS)在查询分析器中输入下面代码，并运行。insert into student values (20021517,金胜男,女,19,IS)2. 用命令方式将student表学号为20021517的学生的性别改为男，年龄改为20.在查询分析器中输入下面代码，并运行。update student set Ssex=男,Sage=20 where Sno=200215173.用命令方式删除student表学号为20021517的学生。在查询分析器中输入下面代码，并运行。Delete from student where Sno=200215174.练习教材3.5节中例1-例10实验五 视图一、实验目的掌握企业管理器和命令方式建立视图的方式。本实验需2学时。二、实验内容1.企业管理器建立视图2.命令方式建立视图三、实验任务1. 用企业管理器建立一个基于学生表、课程表、成绩表的视图，要求该视图显示学号、姓名、课程、成绩 。右键点击图5-11）展开student数据库，右键点击“视图”，然后点击“新建视图”，出现“新视图”窗口，如图5-1所示，在关系窗格右键点击，在出现的弹出式菜单中点击“添加表”，出现添加表窗口，如图5-2所示。图5-22）在图5-2中分别选择student、course和SC表，然后点击“添加”，将三个表依次添加到关系窗格。3）在图5-3中，在三个表中选中相应字段，观察窗口中其它窗格的变化。图5-34）关闭窗口，并为视图命名为sinfo。5)回到管理器窗口观察视图结果，如图5-4所示图5-42. 命令方式建立一个基于学生表、课程表、成绩表的视图，要求该视图显示姓名、课程、成绩 。1）打开查询分析器，在命令窗口输入下面代码，并运行。CREATE VIEW dbo.sinfo2ASSELECT dbo.student.Sname, dbo.Course.Cname, dbo.SC.GradeFROM dbo.student INNER JOIN dbo.SC ON dbo.student.Sno = dbo.SC.Sno INNER JOIN dbo.Course ON dbo.SC.Cno = dbo.Course.Cno实验六 触发器一、实验目的了解触发器的概念，掌握触发器的作用及其建立方式。本实验需4学时。二、实验内容分别创建instead of 触发器和after触发器，并体验触发器的作用三、实验任务右键点击要建立触发器的表，然后在“所有任务”中点击“管理触发器”，出现触发器管理窗口，如图6-2所示。图6-11.在学生数据库中，新建两个表，表的定义如下：CREATE TABLE salarys( id int identity(1,1) NOT NULL,姓名 varchar(10),基本工资 money,奖金 money,) CREATE TABLE cash( id int identity(1,1) NOT NULL,姓名 varchar(10), 应发工资 money,)2. 对salary表建立触发器，当向salary表插入信息后，自动将用户名和用户应发工资添加到cash表，应发工资为： 基本工资 + 奖金在图6-2编辑窗口，输入下面代码，并点击确定，完成触发器的建立。create trigger salaryinserton salarysafter insertasbegindeclare name char(10), mycash moneyselect name=inserted.姓名,mycash=inserted .基本工资+奖金from insertedinsert into cash (姓名, 应发工资) values ( name,mycash)end3. 对salary表建立触发器，当要删除salary表中某个记录时，实际删除的为cash表中与此记录姓名相同的记录。在图6-2编辑窗口，输入下面代码，并点击确定，完成触发器的建立。create trigger salarydelete2on salarysinstead of deleteasbegindeclare name char(10)select name=deleted.姓名from deleteddelete from cash where cash.姓名=nameend4.对salary表进行添加和删除记录，实验触发器的作用。实验七 存储过程一、实验目的了解存储过程的概念，掌握存储过程的建立方式，存储过程中参数、变量的定义、使用。本实验需2学时。二、实验内容1.利用向导创建存储过程，并运行存储过程2.利用存储过程编辑器创建存储过程，并运行存储过程三、实验任务1. 用向导创建一个存储过程：该存储过程用于向学生表添加记录。 图7-11）打开企业管理器，然后展开数据库student，单击“存储过程”，然后在“工具”菜单中单击“向导(W).”,在向导对话框中展开“数据库”，选择“创建存储过程向导”，如图7-1所示。图7-2图7-32）在图7-1中单击“确定”，然后在出现的欢迎界面点击“下一步”，出现选择数据库界面，如图7-2所示，选择数据库student，单击“下一步”，出现“选择存储过程”界面，如图7-3所示 。图7-44）在图7-3中选择course表的插入项，单击“下一步”，出现“完成”界面，如图7-4所示 。图7-55）在图7-4中，点击“编辑”按钮，出现图7-5，在这里可以修改存储过程名称，也可以点击“编辑”查看存储过程代码，如图7-5所示。6）在SQL查询分析器中输入下面代码，运行存储过程。use studentexec insert_course_1 8,C语言,6,42.利用存储过程编辑器，创建一个存储过程,统计“CS”系的人数。1）打开企业管理器，然后展开数据库student，右键点击“存储过程”，然后点击“新建存储过程”出现存储过程编辑窗口，如图7-6所示。2）在编辑窗口输入下面代码，点击确定完成存储过程的建立。CREATE PROCEDURE countstudent ASselect count(*) from student where Sdept=CSGO图7-63）在SQL查询分析器中输入下面代码，运行存储过程。use studentexec countstuden实验八 用户权限管理一、实验目的了解SQL Server 2000中的两种用户登录模式；熟悉登录用户的添加、删除，角色、权限的分配。本实验需4学时。二、实验内容1.使用SQL Server 2000中的两种用户登录模式连接数据库服务器；2.创建帐户，并对帐户分配权限3.固定角色的分配和收回三、实验任务1.运行SQL Server本地客户端的查询分析器，如图8-1所示，选择或指定要连接的服务名称(这里为6DEDF270BA074C4或3)1）选择“windows身份验证”，点击确定直接登录本机数据库服务器。2)输入帐户以及密码，登录数据库服务器。 图8-12.创建登录帐户用户可以通过企业管理器或系统提供的存储过程来进行登录帐户的创建。1）用系统存储过程来创建运行企业管理器，在SQL 查询分析器中，输入以下语句并运行sp_addlogin ma,123456,northwind这个例子创建了一个名为ma, 密码是123456，默认数据库为northwind的帐户。在建立用户的登录帐号信息时，用户应该选择默认的数据库，以后每次连接上服务器后，系统都会自动转到默认的数据库上。这里也可以不指定数据库，系统默认为master库。2）企业管理器创建登录帐户3）右键点击图8-2中的“登录”，然后点击“新建登录”，出现“新建登录窗口”如图8-3所示，在名称中输入用户名“pa”，选择“SQL Server 身份验证”，并输入密码，制定默认数据库及默认语言，点击“确定”完成帐户添加。登录用户列表图8-2图8-33.修改和删除帐户建立完成的帐户信息还可以进行修改。1） 使用系统存储过程sp_defaultdb修改默认数据库。sp_defaultdb ma,master将帐户ma的默认数据库改为master。2）使用系统存储过程sp_defaultlanguage修改默认语言sp_defaultlanguage ma,english将帐户ma的默认语言改为 English。3）使用系统存储过程sp_password修改帐户密码sp_password 123456,654321,ma将帐户ma的密码123456改为 654321。4） 使用系统存储过程sp_droplogin删除登录帐户sp_droplogin ma将帐户ma删除5） 用企业管理器来修改帐户信息在图8-2中右边的帐户列表中右键点击要操作的帐户，如图8-4所示。图8-4如果要删除用户，在图8-4中选择“删除”即可，如果修改帐户，在图8-4选择“属性”，出现登录属性窗口，如图8-5所示，在这里可以修改帐户基本信息。图8-5在SQL Server 2000中删除帐户信息时，有很多限制。主要有以下几种情况：l 系统帐户sa不能被删除；l 已经映射到数据库用户上的帐户不能被删除；l 正在使用或连接的帐户不能被删除；l 拥有数据库的帐户不能被删除4.添加、删除数据库用户以上的操作只是建立了登录SQL Server的帐户，用户登录后还不能对数据库进行访问，将登录帐户添加为数据库用户后，使用登录帐户登录的SQL Server的用户才可以实现对数据库的访问。1）通过存储过程sp_grantdbaccess添加数据库用户use northwindgosp_grantdbaccess ma将northwind数据库的访问权授予帐户ma。2）通过存储过程sp_revokedbaccess删除数据库用户use northwindgosp_revokedbaccess ma删除帐户ma对northwind数据库的访问权 。3）通过管理器添加，删除数据库用户在图8-5中，点击“数据库访问”选项卡，如图8-6所示，在这里将要授予帐户ma的数据库选中即可，如果删除数据库用户，只要将数据库选中状态去掉就可以。图8-65角色角色是SQL Server2000引进的用来集中管理数据库或服务器权限的概念。数据库管理员将数据库的权限赋予角色，然后将角色再赋予给数据库用户或登录帐户，从而使数据库用户或登录帐户拥有相应的权限。SQL Server提供了固定服务器角色和数据库角色，用户可以修改固定数据库角色的权限，也可以自己创建新的数据库角色，再分配权限给新的角色。具体固定服务器角色和数据库角色及其含义可以参照SQL Server 帮助信息。1）使用存储过程sp_addsrvrolemember固定服务器角色分配给指定的登录帐户sp_addsrvrolemember ma,dbcreator将登录帐户ma添加为服务器角色dbcreatord的成员，使其具有创建和修改数据库的权利2）使用存储过程sp_dropsrvrolemember收回分配给指定的登录帐户的指定固定服务器角色sp_dropsrvrolemember ma,dbcreator收回分配给登录帐户ma的固定服务器角色dbcreator。3）通过管理器分配、回收指定帐户的服务器角色在图8-5中，点击“服务器角色”选项卡，如图8-7所示，在这里将要授予帐户ma的服务器角色选中即可，如果删除相应角色，只要将相应角色选中状态去掉就可以。图8-74）使用存储过程sp_addrolemember将指定登录帐户增加到某个固定数据库角色中use northwindgosp_addrolemember db_datareader,ma授予数据库用户ma只读数据库属性。5）使用sp_droprolemember从某个固定数据库角色中删除指定的登录帐户use n