华科数据库实验报告.doc

课 程 实 验 报 告课程名称： 数据库系统概论 专业班级： 学 号： 姓 名： 指导教师： 报告日期： 计算机科学与技术学院目录目录3一、实验目的5二、实验原理51.SQL Server 2008查询分析器52.DDL使用方法53.DML使用方法64.DCL 的使用方法75.数据库的备份和恢复8三、实验内容9实验1: 基本表的创建、数据插入9实验2：数据查询10实验3：数据修改、删除10实验4：视图的操作10实验5：库函数，授权控制10实验6：数据库的备份、恢复11四、实验过程11实验1: 基本表的创建、数据插入11实验2: 数据查询14实验3: 数据修改、删除16实验4：视图的操作17实验5：库函数，授权控制18实验6：数据库的备份、恢复19五、心得体会21一、实验目的掌握SQL Server 2008 的工具使用掌握DDL的使用方法 掌握DML的使用方法掌握SELECT命令使用方法掌握DCL的使用方法 掌握数据库的备份和恢复二、实验原理1.SQL Server 2008查询分析器查询分析器是一个重要工具，实验中的所有SQL语言命令均需在查询分析器中输入、编辑运行。2.DDL使用方法 1）数据库创建在查询分析器中执行下列语句即可在默认的设备上创建新的数据库ems。CREATE DATABASE database_name 2）基本表的建立创建基本表的命令为：CREATE TABLE table_name，在该命令中定义主码和外码时，可以使用列约束（Column Constraint）或表约束（Table Constraint）子句。创建基本表时，应先选择包含表的数据库。3）视图的建立视图是组成数据库体系结构三级模式两级映像结构中的外模式的基本单元，SQL-Server的视图定义命令为： CREATE VIEW view-name AS SELECT statement视图是用于定义终端用户数据来源的。在视图定义中可以使用复杂的SELECT命令。3.DML使用方法SQL的DML包括插入（INSERT）、删除(DELETE)、修改(UPDATE)等命令。DML命令的执行是可能造成数据库不一致的根源。因此，每一条语句在执行前，SQL-Server都要验证语句是否符合完整性要求，包括实体完整性、参照完整性、用户定义完整性。1）INSERT命令SQL语言的插入命令：INSERT INTO table-name (column-list) VALUES(values-list)，可以完成数据输入功能。2）DELETE命令SQL的删除命令：DELETE FROM table-name WHERE condition-expression;执行DELETE命令后，系统会删除满足命令中条件表达式的所有元组。这种删除只是逻辑的。因此，当再次插入一个与被删除的元组具有相同关键字的元组时，被认为违反了实体完整性。3）UPDATE命令 SQL的修改命令为：UPDATE table-name SET column-name = expression WHERE condition-expression;执行UPDATE命令后，系统会按照SET子句修改满足命令中条件表达式的所有元组。如果使用修改命令更新关键字的值，而该关键字在其他表中作为外码存在时，操作可能违反参照完整性。系统将拒绝执行。4）SELECT命令SELECT 命令是SQL语言中使用最频繁的命令，其变化形式繁多，灵活运用SELECT 命令可以完成任意复杂的查询要求。SELECT 命令的基本语法为:SELECT query_expression|column-list|* FROM table_name_list|view_name_listWHERE condition-expressionGROUP BY HAVING condition-expressionORDER BY order_expression| column ASC|DESC,n; 4.DCL 的使用方法1）SQL Server 登录认证新登录的创建新的登录者可以是WINDOWS的用户或用户组，也可以是使用SQL Server认证模式的登录帐号。设定WINDOWS的用户为SQL Server登录者时使用sp_grantlogin。创建新的使用SQL Server认证模式的登录帐号时使用sp_addlogins。登录的删除删除基于使用SQL Server认证模式的SQL Server的登录可以使用sp_droplogin。但是，如果与该登录匹配的数据库用户仍存在sysuser表中，则不能删除该登录账号。删除WINDOWS的用户或用户组时，使用sp_revokelogin，拒绝WINDOWS的用户或用户组连接到SQL Server时，使用sp_decnylogin。登陆的查看sp_helplogins可以用来显示SQL Server的所有登录者的信息。如图29是显示数据库ems 的所有登录者信息的代码。2）用户管理SQL Server利用下列系统过程管理数据库用户：sp_adduser sp_grangdbaccess sp_dropuser Sp_dropuser sp_revokedbaccess sp_helpuser 。其中sp_adduser Sp_dropuser 是为了保持与以前版本相兼容，因此不主张使用，在SQL Server 2000中，建议使用sp_grangdbaccess sp_revokedbaccess 。创建新数据库用户所有的数据库用户（除了guest）均必须与某一登录账号相匹配，因此在使用系统过程创建新数据库用户时，不但要指出新数据库用户的名称，还必须指出一个已经存在的登录账号。系统过程sp_grantdbaccess用于为SQL Server 登录用户或NT用户或用户组建立相匹配的数据库用户帐号。删除数据库用户系统过程sp_revokedbaccess用于将数据库用户从当前数据库中删除，删除后与其匹配的登录即无法再访问该数据库了。查看数据库用户信息sp_helpuser可用来显示当前数据库的指定用户信息。3）用户授权管理对于登录到SQL Server的合法数据库用户，必须获得对数据库操作的授权。在SQL Server中包括两种类型的权限，即对象权限和语句权限。对象权限总是针对表、视图、存储过程而言，它决定了能对这些对象执行哪些操作（如UPDATE DELETE INSERT SELECT EXECUTE）。不同类型的对象支持不同类型的操作。语句权限指数据库用户执行某种语句的操作权，如创建数据库、表、存储过程等。这些语句虽然（如CREATE 命令）包含有操作对象，但这些对象在操作前并不存在于数据库中。5.数据库的备份和恢复备份是指对SQL Server 数据库或事务日志进行拷贝，如果数据库因意外而损坏，备份文件可以用来恢复数据库。SQL Server 2000 中有四种备份类型，分别是：数据库备份、事务日志备份、差异备份、文件和文件组备份。在使用是可以根据实际情况选择一种或几种的混合应用。1)备份设备在进行备份以前必须创建备份设备。备份设备是用来存储数据库、事务日志、文件和文件组备份的存储介质。可以是磁盘、磁带或管道。SQL Server 2000 提供了两种创建备份的途径，即可视化方式和命令方式。用命令备份数据库使用BACKUP 命令进行备份操作。l备份完整数据库/* EXEC sp_addumpdevice DISK,backupdevice_name, d:backupdevems.bak目标路径*/BACKUP DATABASE database TO backupdevice_name2)恢复数据库使用命令恢复数据库数据库恢复命令为：RESTORE DATABASE/LOG。三、实验内容实验1: 基本表的创建、数据插入（1）建立教学管理中的三个基本表：Students（S#，SNAME，AGE，SEX） 学生（学号，姓名，年龄，性别）Courses（C#，CNAME，SCORE，PC）课程（课程号，课程名，学分，先行课号）SC（S#，C#，GRADE） 选修（学号，课程号，成绩）（2）用INSERT 命令输入数据。表 1基本表Students的数据S1LU20MS2YIN19MS3XU18FS4QU18FS6PAN14MS8DONG24M表 2基本表Courses的数据C1数学4MC2英语8MC3数据结构4FC4数据库3.5FC5网络4M表 3基本表SC的数据S1S2S3S4S6S8C1859089848887C273NULL86827585C3888090NULLC48985NULL9288C573NULL87实验2：数据查询（1）列出选修课程号为C2的学生学号与姓名。（2）检索选修课程名为“数学”的学生学号与姓名。（3）检索没有选修C2课程的学生姓名与年龄。（4）检索选修全部课程的学生姓名。实验3：数据修改、删除（1） 把C2课程的非空成绩提高10。（2） 在SC表中删除课程名为“物理”的成绩所对应的元组。（3） 在S和SC表中删除学号为S8的所有数据。实验4：视图的操作（1） 建立男生学生的视图，属性包括学号，姓名，选修课程名和成绩。（2） 在男生视图中查询平均成绩大于80分的学生学号和姓名.实验5：库函数，授权控制（1） 计算每个学生选修课程的门数、平均成绩。（2） 建立一个合法的用户，将SC表的查询权限授予该用户。（3） 使用GRANT语句，把对基本表students、Courses、SC的使用权限授予其他用户。实验6：数据库的备份、恢复（1） 使用完全备份将你的实验数据库备份到软盘。（2） 删除你所建立的数据库。（3） 恢复你的数据库。（4） 在恢复后的数据库上撤销你建立的基本表和视图。四、实验过程实验1: 基本表的创建、数据插入（1）创建基本表create database emsuse ems;gocreate table students(sno char(9) primary key,sname char(20) not null,age char(3),sex char(6);create table courses(cno char(9) primary key,cname char(20) not null,score int ,pc char(3);create table sc(sno char(9) foreign key references students(sno),cno char(9),grade int,foreign key(cno) references courses(cno);（2）用INSERT命令输入数据use ems; go insert into students values(S1,LU,20,M); insert into students values(S2,YIN,19,M); insert into students values(S3,XU,18,F); insert into students values(S4,QU,18,F); insert into students values(S6,PAN,14,M); insert into students values(S8,DONG,24,M); insert into Courses values(C1 ,数学 , 4 , M); insert into Courses values(C2 ,英语 , 8 , M); insert into Courses values(C3 ,数据结构 , 4 , F); insert into Courses values(C4 ,数据库 , 3.5 , F); insert into Courses values(C5 ,网络 , 4 , M); insert into SC values(S1 ,C1 , 85); insert into SC values(S2 ,C1 , 90); insert into SC values(S3 ,C1 , 89); insert into SC values(S4 ,C1 , 84); insert into SC values(S6 ,C1 , 88); insert into SC values(S8 ,C1 , 87); insert into SC values(S1 ,C2 , 73); insert into SC values(S2 ,C2 , NULL); insert into SC values(S3 ,C2 , 86); insert into SC values(S4 ,C2 , 82); insert into SC values(S6 ,C2 , 75); insert into SC values(S8 ,C2 , 85); insert into SC values(S1 ,C3 , 88); insert into SC values(S2 ,C3 , 80); insert into SC values(S6 ,C3 , 90);insert into SC values(S8 ,C3 , NULL); insert into SC values(S1 ,C4 , 89); insert into SC values(S2 ,C4 , 85); insert into SC values(S4 ,C4 , NULL); insert into SC values(S6 ,C4 , 92);insert into SC values(S8 ,C4 , 88); insert into SC values(S1 ,C5 , 73); insert into SC values(S2 ,C5 , NULL); insert into SC values(S8 ,C5 , 87);插入后的三个表格图 1学生表格图 2课程表格图 3cs表格实验2: 数据查询（1） 列出选修课程号为C2的学生学号与姓名use ems;goselect sc.sno,snamefrom students,scwhere o=C2 and sc.sno=students.sno图 4查询一（2）检索选修课程名为“数学”的学生学号与姓名select sc.sno,snamefrom students,sc,courseswhere ame=数学and o=oand students.sno=sc.sno图 5查询二(3)检索没有选修C2课程的学生姓名与年龄。select sname,age from students where not exists(select * from sc where o=c2 and sno=students.sno);图 6查询三(4)检索选修全部课程的学生姓名。select sname from students where not exists(select * from courses where not exists( select * from sc where sno=students.sno and cno=o);图 7查询四实验3: 数据修改、删除（1）把C2课程的非空成绩提高10。use ems;goupdate sc set grade=grade*1.1 where o=c2 and o is not null图 8修改（2）在SC表中删除课程名为“物理”的成绩所对应的元组。delete from SC where cno in (select cno from courses where cname=物理)图 9删除（3）在S和SC表中删除学号为S8的所有数据。delete from sc where sc.sno=s8 delete from students where students.sno=s8图 10删除二实验4：视图的操作（1）建立男生学生的视图，属性包括学号，姓名，选修课程名和成绩。use ems;gocreate view student_m(sno,sname,cname,grade) as select students.sno,students.sname,cname,grade from sc,students,courses where students.sno=sc.sno and o=o and sex=M图 11视图（2）在男生视图中查询平均成绩大于80分的学生学号和姓名。use ems;goselect distinct students.sno,students.sname from student_m,students where student_m.sno=students.sno and grade80图 12视图查询实验5：库函数，授权控制（1）计算每个学生选修课程的门数、平均成绩。use ems;goselect students.sno,students.sname,count(cno) 选修门数,avg(grade) 平均成绩 from students,sc where students.sno=sc.sno group by students.sno,sname图 13平均成绩（2）建立一个合法的用户，将SC表的查询权限授予该用户。use ems go exec sp_addlogin ems,ems use ems go exec sp_grantdbaccess ems,ems图 14授权（3）使用GRANT语句，把对基本表students、Courses、SC的使用权限授予其他用户。use ems go GRANT all privileges -授予权限 ON Courses TO guest; GRANT all privileges -授予权限 ON students TO guest; GRANT all privileges -授予权限 ON sc TO guest;图 15授权二实验6：数据库的备份、恢复（1）使用完全备份将你的实验数据库备份到软盘。use emsgo EXEC sp_addumpdevice DISK,backupdevice_ems, d:backupdevems.bak BACKUP DATABASE ems TO backupdevice_ems图 16备份（2）删除你所建立的数据库。在目录中右键删除即可。（3）恢复你的数据库。restore database ems from beifen_ems图 17恢复（4）在恢复后的数据库上撤销你建立的基本表和视图。use ems drop table sc drop table students drop table courses drop view student_m图 18撤销五、心得体会通过本次试验，我掌握了sqlserver3008的使用方法，如何进行创建数据库及在该数据库基础上完成相关的创建表视图等操作，以及进行数据库的查询删除等功能。同时对于数据库的备份还原操作也基本掌握了。实验一开始也基本上是通过参考实验题目及sql使用方法上一步步做下来，但是一开始就出现了重复创建的数据库的问题，其实原因也比较简单，在查询分析器中，新建了查询，但是当已经创建了database之后，之后的操作不能再该代码后面继续进行，而应该使用usedatabase-name指令在该数据库下进行其他的查询等操作。至于对于创建表等操作，必须清楚所创建的数据中那些是主键外键，通过这次实验其实也是对于我们课程内容的一种巩固。在之后的查询操作及删除操作中偶尔也会出现一些小的问题，但在错误提示栏中也基本写明了错误的原因，例如需要groupby来进行分组操作等的；同时对于查询操作中会出现一个常见的问题就是输出结果中产生的数据项常常是大部分重复的，因此在select操作中要写distinct，默认的操作是不删除重复的元组。在进行实验五的过程中遇到了较为麻烦的问题，及创建用户之后在登录时总是提示已连接但是产生错误，通过分析代码以及问同学发现可能是某处设置的问题，才发现要先登录在进行创建，即需要两步操作，同时实验过程中要时刻注意是否创建成功。这次实验中出现的问题都比较简单解决。这次实验的目的也主要是一个熟悉sql服务器的过程，同时通过这次实验进一步掌握数据库的各种操作以及对于课堂的内容进行进一步的巩固与理解。