2数据库实验指导书.docx

1、2数据库实验指导书数据库系统原理与应用实验指导书浙江农林大学环境与资源学院2015.3 目录实验一 数据库定义 3实验二 数据插入、修改、删除与简单查询 4实验三 数据库的连接查询和嵌套查询 12实验四 数据库的组合查询和统计查询 14实验五 数据完整性和数据安全性 17实验六 数据库备份和恢复 34实验七 数据库前端开发工具基础 41学生选课管理系统开发（课外练习） 57实验一 数据库定义实验目的1 熟悉SQL Server 2008 中SQL Server Management Studio的环境2 了解SQL Server 2008数据库的逻辑结构和物理结构3 掌握使用向导方式和SQL方

2、式来创建和删除数据库及数据表的方法4 学会数据操纵语言Insert、Update、Delete最基本的使用方法。实验准备 1装有SQL Server 2008的PC机。2. 明确能够创建数据库的用户必须是系统管理员。实验要求1.熟练使用SQL Server Management Studio（简称SSMS）中的进行数据库的创建和删除操作，以及数据表的创建和删除操作。2.熟练使用SQL语句创建和删除数据库数据表。3.学会分离和附加数据库。4.学会使用Insert、Update、Delete语句。5.完成相应的实验报告。实验内容 设有一学籍管理系统，其数据库名为“STUDENT”，初始大小为 10

3、MB，最大为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”（如不修改则默认为数据库名STUDENT_data）,存放路径为“E:sql_data”（可自己选择存放路径）, 物理文件名为“student_data.mdf（自动生成，无需设置），创建完成后可在存放路径下查看物理文件。日志文件的逻辑文件名为“student_log”,存放路径为“E:sql_data” （可自己选择存放路径），物理文件名为“student_log.ldf”，（自动生成，无需设置），创建完成后可在存放路径下查看物

4、理文件。 (1) 分别使用向导和SQL语句两种方式创建上述描述的数据库STUDENT。 使用SQL Server Management Studio（简称SSMS）创建数据库。 （2）创建STUDENT数据库中的三个基本表（见教材教学管理数据库 三个表结构），并录入表中数据。 （3）删除上面建立的SC表。 （4）删除上面建立的数据库STUDENT。操作指导使用向导创建和删除数据库数据表(1) 使用向导创建上述描述的数据库。 使用SQL Server Management Studio（简称SSMS）创建数据库。 1）启动SSMS 在开始菜单中：所有程序SQL Server 2008 SQL S

5、erver Management Studio单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。2）建立数据库 在“对象资源管理器”窗口，建立上述数据库STUDENT。在数据库节点上右击选择新建。在下图“新建数据库”窗口中，完成实验内容中STUDENT数据库的各项要求。以上设置完成后，点击“确定”。完成数据库创建。即可在“对象资源管理器”中查看到STUDENT。如下图所示。（2）使用向导创建STUDENT数据库中的三个基本表，并录入数据。以student表为例，表结构如下：student表（学生信

6、息表）字段名称类 型宽 度允许空值主 键说 明snochar9NOT NULL是学生学号snamechar20NOT NULL学生姓名ssexchar2NULL学生性别sagesmallintNULL学生年龄sdeptchar20NULL学生所在院系1）打开STUDENT前的“+”，右击下层的“表”选项，右侧弹出表设计窗口，先完成student的创建，右击sno设置主键，然后点击保存，对话框中输入student（表名），点击“确定”，完成student表的创建。目前，该表还只是一张空白表，里面没有任何数据。可点击STUDENT下“表”前“+”，查看已创建的student表， 右击“dbo.st

7、udent”-打开表，如下图按行录入表中数据，数据参照教材（P82）。录入完成后，关闭窗口，系统自动保存数据。2）使用同样方法继续在STUDENT数据库中创建另外两个表course和sc表。表结构如下：注意：sc表设置主键时，需要把sno和cno两行一起选中，再设置。course表（课程信息表）字段名称类 型宽 度允许空值主 键说 明cnochar4NOT NULL是课程编号cnamechar40NOT NULL课程名称cpnochar4NULL先修课课程编号（外键）ccreditsmallintNULL课程学分sc表（学生选课成绩表）字段名称类 型宽 度允许空值主 键说 明snochar9N

8、OT NULL是学生学号cnochar4NOT NULL课程编号gradesmallintNULL学生成绩（3）使用向导删除上面建立的SC表。右击“dbo.sc”选择“删除”，确认删除此表。（4）使用向导删除上面建立的数据库STUDENT。（建议下课前再做此项，也可简单另外创建一个新数据库，再对其删除。）右击“STUDENT”选择“删除”，确认删除此数据库。操作指导使用SQL语句创建和删除数据库数据表1.使用SQL创建数据库STUDENT。打开SQL Server Management Studio点击左上角“新建查询”连接数据库成功后，右侧即可出现查询分析器空白窗口，输入SQL语句，点击分析

9、有无语法错误，如有错误，根据提示修改，直到“命令成功完成”再点击运行该SQL语句，“命令成功完成”后，刷新左侧数据库选项，即可看到新创建的数据库。1）以下是创建数据库userdb1的SQL语句，create database userdb1on(name=userdb1_data,-数据文件的逻辑名称,注意不能与日志逻辑同名filename=d:SQL_datauserdb1.mdf ,-物理名称，注意路径必须存在size=5,-数据初始长度为Mmaxsize=10,-最大长度为Mfilegrowth=1)-数据文件每次增长Mlog on(name=userdb1_log, filename=

10、d:SQL_datauserdb1.ldf , size=2 , maxsize=5 , filegrowth=1)运行上述语句建立数据库userdb1。2）根据步骤1）的SQL语句，写出创建数据库STUDENT的SQL语句，数据库要求见实验一，并建立数据库STUDENT。2. 使用SQL创建数据库STUDENT中的三个基本表student，course和SC。在SQL Server 2008 的查询分析器中，用sql语句创建student表。点击“新建查询”，打开新的窗口，输入以下创建语句，注意选择自己的数据库，否则默认为系统数据库master.CREATE TABLE student(sn

11、o char(9) PRIMARY KEY, Sname char(20) UNIQUE, Ssex char(10), Sage smallint, sdept char(20);以上方法用SQL语句创建其他表(建表SQL语句参考课本上的例子)。数据录入方法同实验一。3. 练习使用分离附加数据库。当数据库数据表创建好后，如想把数据库拷贝出来，必须先从SQL Server Management Studio中分离数据库，然后可到物理文件存储路径下，复制数据文件和日志文件。下次再需要使用该数据库数据时，再把物理文件附加进SQL Server Management Studio。具体操作如下：1）

12、首先关闭数据库中的所有表的窗口。右击对象资源管理器中要分离的数据库名，选择“任务分离”点击“确认”即可，分离成功后，对象资源管理器中，该数据库立刻消失了。2）打开数据库的物理存储路径，如“d:SQL_data”（以自己数据库存储路径为准），把相应数据库的数据文件和日志文件，一并拷贝，转入U盘保存。3）“附加”操作。打开对象资源管理器，右击“数据库”选择“附加”，弹出附加数据库窗口，点击“添加”，选择数据库物理文件存储路径，“确定”，附加成功后，对象资源管理器中，出现新附加的数据库名。4.使用SQL删除SC表。 在查询分析器中输入SQL语句Drop table sc; 执行该语句完成删除数据表操

13、作。5.使用SQL删除STUDENT数据库。在查询分析器中输入SQL语句Drop database STUDENT; 执行该语句完成删除数据库操作。操作指导使用SQL语句修改数据表和对数据操作student表（学生信息表）字段名称类 型宽 度允许空值主 键说 明snochar9NOT NULL是学生学号snamechar20NOT NULL学生姓名ssexchar2NULL学生性别sagesmallintNULL学生年龄sdeptchar20NULL学生所在院系course表（课程信息表）字段名称类 型宽 度允许空值主 键说 明cnochar4NOT NULL是课程编号cnamechar40N

14、OT NULL课程名称cpnochar4NULL先修课课程编号（外键）ccreditsmallintNULL课程学分sc表（学生选课成绩表）字段名称类 型宽 度允许空值主 键说 明snochar9NOT NULL是学生学号cnochar4NOT NULL课程编号gradesmallintNULL学生成绩（3）使用向导删除上面建立的SC表。1、Create Table-建立学生表CREATE TABLE student(sno char(9) PRIMARY KEY, Sname char(20) NOT NULL, Ssex char(10), Sage smallint, sdept cha

15、r(20);-建立课程表CREATE TABLE course(cno char(4) PRIMARY KEY, cname char(40) NOT NULL, cpno char(4), ccredit smallint);-建立选课表CREATE TABLE SC(sno char(9) NOT NULL, cno char(4) NOT NULL, grade dec(4,1),Constraint c1 Primary Key (sno,cno),Constraint c2 Foreign Key (sno) references student(sno),Constraint c3

16、 Foreign Key (cno) references course(cno);、Alter Table- 添加一列cphoneAlter Table student add cphone char(11);-修改列属性Alter Table studentAlter column cphone char(12);-删除一列Alter Table studentDrop column cphone;、Insert Into -插入数据Insert Into studentValues (100000001,李晓峰,男,22,环境与资源学院); 、Delete-删除数据Delete From

17、 student;实验二 数据插入、修改、删除与简单查询一、实验目的要求学生熟练掌握和使用SQL、T-SQL、SQL Server企业管理器创建数据库、表和索引,修改表结构,学会使用查询分析器接受T-SQL语句,进行结果分析。掌握SQL Server查询分析器的使用方法，加深对SQL和TransactSQL语言的查询语句的理解。掌握简单表的数据查询、数据排序和数据联结查询的操作方法。二、实验内容1. 建立数据库: 学生选课学生选课数据包含4个表，内容如下：学生(学号，姓名，年龄，性别，学院号)； 课程(课程号，课程名，先行课)； 选课(学号，课程号，成绩) 学院(学院号,学院名) 2.基本查询

18、操作（1）熟悉SQL Server企业管理器和查询分析器的基本使用方法（2）简单查询操作 该实验包括投影、选择条件表达、数据排序等。（3）连接查询操作 该实验包括求笛卡儿积、内连接、左外连接、右外连接、等值连接和自连接。三、实验步骤1用企业管理器建立学生选课数据库(名称：学生选课, 初始大小10MB,增长10%)2在企业管理器中察看学生选课数据库属性,并修改3用查询分析器建学生、课程、学院和选课4个表，同时建立约束：学生(学号 smallint, 姓名 char(10),年龄 smallint,性别 char(2),学院号 tinyint); - 主码，外码课程(课程号 smallint,课程

19、名 char(20),先行课 smallint); - 主码选课(学号 smallint,课程号 smallint,成绩 decimal(4,1); - 主码，外码 学院(学院号 tinyint, 学院名 char(30) ; - 主码 选择合适的数据类型,定义每个表的主码,是否允许空和默认值等列级数据约束。4在企业管理器中浏览学生、课程、学院和选课4个表级数据约束：每个表的主码约束、外码约束。也可以尝试建立表和修改约束。让大家明白查询分析器和企业管理器都可以进行数据定义，建议在一般情况下使用查询分析器。 5 用T-SQL 进行简单查询： 求环境与资源学院学生的学号和姓名； 求选修了课程的学生

20、学号。 求选修了课程的学生学号、课程号和成绩。 求选修课程”数据库原理”且成绩在8090分之间的学生学号和成绩。 求环境与资源学院或计算机学院姓张的学生的信息。 求缺少了成绩的学生的学号和课程号。四、实验方法（一）创建数据库和表1、创建数据库(1)用企业管理器创建数据库1)开始-程序-SQL Server-企业管理器2)选中要在其上建立数据库的服务器,单击前面的”+”号,展开树形目录3)选中“数据库”文件夹,点击鼠标右键,在弹出菜单上选“新建数据库”,随后在数据库属性中输入数据库名、数据文件名、日志文件名,并设置各自参数(参考上面的要求)；2、查看数据库属性选中数据库文件夹,点击鼠标右键,选择

21、“属性”项,弹出数据库属性对话框,可以查看可修改数据库属性(参考课本)3、删除数据库选中要删除的数据库,点击鼠标右键,选择“删除”项,确认4、新建表用查询分析器T-SQLCreate table 学生（）Create table 课程（）Create table 选课（）Create table 学院 ( )*注意几个表的参考和被参考关系，以及各自的主码和外码关系：学生与学院表中的学院号：外码与主码；学生与选课表中的学号：主码与外吗；课程与选课表中的课程号：主码与外吗；*注意约束的先后顺序：先建主码，后建外码。删除的顺序相反。详见后面的参考部分。5、定义完整性约束 前3个表的check约束(随

22、意建)6、修改表结构在学生表中添加”身份证号码”字段。(1)在企业管理其中修改用鼠标右击表名，在弹出菜单中选择设计表，弹出表属性窗口，可在此修改结构。(2)用alter table修改 alter table 学生 add column身份证号码 char(16)go（7）修改身份证号码长度alter table 学生 alter column身份证号码 char(18)go8、删除列alter table 学生 drop column身份证号码go（二）插入、删除数据1、直接插入Insert into 学生(学号,姓名,性别,年龄,学院号) values(201218080102,张凯烨,女

23、,21,11)或Insert into 学生 values(201218080102,张凯烨,女,21,11)或Insert into 学生(学号,姓名) values(201218080102,张凯烨)2、从查询结果插入create table 地信专业学生(学号 dec(12,0) primary key,姓名 char(10),年龄 smallint,性别 char(2),学院号 tinyint)goInsert into 地信专业学生(学号,姓名,性别,年龄,学院号) Select学号,姓名,性别,年龄,学院号 from 学生Go3、删除数据（1）删除部分数据Delete from 学

24、生 where 学院 = 环境与资源学院Go（2）删除全部数据Delete from 学生Go（三）简单查询 将查询需求用Transact SQL语言表示。 在SQL Server查询分析器的输入区中Transact SQL查询语句。 设置查询分析器结果区为Standard Execute(标准执行)或Execute to Grid方式。 发布执行命令，查看查询结果；如果结果不正确，进行修改，直到正确为止。 查询分析器及使用方法。 查询分析器是在开发数据库应用系统时使用最多的工具。查询分析器的主要作用是编辑Transact SQL，将其发送到服务器，并将执行结果及分析显示出来(或进行存储)。查

25、询分析功能主要通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能。查询分析器的界面如图21图所示。图21 SQL Server 2008查询分析器 Use student表示将student数据库作为当前操作的数据库，以后的SQL操作均在此库进行。查询分析器启动时，默认当前数据库为Master数据库。在查询生成器中的左边窗口是对象浏览器，其中按树结构列出了数据库对象；右上方是SQL代码区域，用于输入SQL的查询语句；右下方为结果区，用于显示查询结果和分析结果。对于TSQL语句的执行结果，在结果区中可以有4种不同的输出形式：标准执行将结果直接显示在结果区；网

26、格执行将结果以表格形式显示在结果区；计划执行显示执行计划：索引分析为在结果区中显示查询的索引情况。上述输出形式，可以通过菜单或按钮选择。1简单查询数据准备将教材中用SQL描述的建立学生_课程库操作，在SQL Server企业管理器中实现。建立数据库学生选课，其库中的学生、课程、选课、学院表的数据为：学生(1,刘纯,20,女,11)(2,张浩,21,男,11)(3,李丽,19,女,21)(4,刘安元,19,男,21)(5,岳不群,25,男,null)(6,李东,21,男,21)(7,张名,20,男,31)(8,岳群,22,男,41)课程 (1,计算机原理,高等数学 ) (2,计算机应用,计算机原

27、理 )(3,数据库原理,C语言)(4,信息工程,计算机应用)(5,C语言,高等数学)(6,数据结构,C语言)(7,遥感概论,高等数学)(7,高等数学,)选课（1,1,80）（1,3,70）（1,4,90）（3,1,78）（3,2,92）（3,5,85）（4,1,95）（4,6,83）（5,4,79）（5,6,89）（6,6,null）（6,2,81）学院(11, 林业生物技术)(21, 环境科技)(31, 园林)(41, 信息技术)(51, 工程)(61, 国际教育)2基本操作实验 (1)简单查询实验 用TransactSQL语句表示下列操作，在学生选课库中实现其数据查询操作： 求环境与资源学

28、院学生的学号和姓名。 求选修了课程的学生学号。 求选修了课程的学生学号、课程号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。 求选修课程”数据库原理”且成绩在8090分之间的学生学号和成绩，并将成绩乘以系数0.7输出。 求环境与资源学院或计算机学院姓张的学生的信息。 求缺少了成绩的学生的学号和课程号。 (2)连接查询实验 （扩展） 用TransactSQL语句表示，并在学生选课库中实现下列数据连接查询操作： 查询每个学生的情况以及他(她)所选修的课程。 求学生的学号、姓名、选修的课程名及成绩。 求选修”数据库原理”且成绩为90分以上的学生学号、姓名及成绩。 查询每一门课的间接先行课(即先行课的先行课)。 (3)TransactSQL语句表示学生和学院之间的内连接、左外部连接和右外部连接，在学生、学院中实现其数据内连接和各种外查询操作。（扩展）五、注意事项 1查询结果的几种处理方式。 2内连接、左外部连接和右外部连接的含义及表达方法。3输入SQL语句时应注意，语句中均使用西文操作符号。七、SQL参考-1、建表和约束use studentdrop table 学生drop table 课程drop table 选课create t