数据库系统原理实验报告.doc

本科实验报告课程名称： 数据库系统原理 实验项目： 实验二、实验三、实验四 实验地点： 逸夫楼502 专业班级： 学号： 学生姓名： 指导教师： 2013年5月20日- 43 -数据库系统概论实验报告学院名称软件学院专业班级软件1112实验成绩学生姓名苏泽楠学号2011005416实验日期5月课程名称实验二实验题目交互式 SQL 语句的使用一、实验目的和要求（1） 掌握数据库对象的操作过程，包括创建、修改、删除（2） 熟悉表的各种操作，包括插入、修改、删除、查询（3） 熟练掌握常用 SQL 语句的基本语法二、实验内容和原理选择如下一个应用背景之一：l 学生选课系统l 习题 3、4、和 5 中使用的数据库l 其它你熟悉的应用（1） 建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。（2） 要求认真进行实验，记录各实验用例及执行结果。（3） 深入了解各个操作的功能。实验要求包括如下方面的内容：1. 数据定义(1) 基本表的创建、修改及删除(2) 索引的创建(3) 视图的创建2. 数据操作完成各类更新操作包括：(1) 插入数据(2) 修改数据(3) 删除数据3. 数据查询操作完成各类查询操作(1) 单表查询(2) 分组统计(3) 连接查询(4) 嵌套查询(5) 集合查询4. 数据操作(1) 创建视图(2) 视图查询三、主要仪器设备硬件配置：HP-6460b软件环境：SQL Server 四、实验程序代码及实验结果-1. 数据定义/*创建ST数据库*/CREATE DATABASE ST/*创建Student表*/CREATE TABLE Student(SnoCHAR(8)PRIMARY KEY,SnameCHAR(8),SsexChar(2) NOT NULL,SageINT,SdeptCHAR(20);/*创建Course表*/CREATE TABLE Course(CnoCHAR(4)PRIMARY KEY,CnameCHAR(40) NOT NULL,CpnoCHAR(4),CcreditSMALLINT,);/*创建SC表*/CREATE TABLE SC(Sno CHAR(8)FOREIGN KEY (Sno)REFERENCES Student(Sno),CnoCHAR(4),Grade SMALLINT,);/*创建员工表Employee*/CREATE TABLE Employee(编号 CHAR(8) PRIMARY KEY,姓名 VARCHAR(8) NOT NULL,部门 CHAR(40),工资 numeric(8,2),生日 TIME,职称 CHAR(20),);/*检查表是否创建成功*/SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SCSELECT * FROM Employee/*增加班级列*/ALTER TABLE Student ADD Sclass char(4)/*修改年龄列*/ALTER TABLE Student ALTER COLUMN Sage SMALLINT/*增加约束*/ALTER TABLE Course ADD UNIQUE(Cname)/*删除表*/DROP TABLE Employee/*为Course表按课程名称创建索引*/CREATE INDEX iCname On Course(Cname)/*为Student表按学生姓名创建唯一索引*/CREATE UNIQUE INDEX iSname ON Student(Sname)/*为SC表按学号和课程号创建聚集索引*/CREATE CLUSTERED INDEX iSnoCno ON SC(Sno,Cno desc)/*为Course表按课程号创建唯一索引*/CREATE UNIQUE INDEX iCno ON Course(Cno)/*建立信息系学生的视图*/CREATE VIEW IS_StudentASSELECT Sno,Sname,Sage FROM StudentWHERE Sdept=IS;-2. 数据操作/*插入数据：*/*插入到Student表*/INSERT INTO Student VALUES(20100001,李勇,男,20,CS,1001)INSERT INTO Student VALUES(20100002,刘晨,女,19,CS,1001)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100021,王敏,女,18,MA,1002)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100031,张立,男,19,IS,1003)INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(20100003,刘洋,女,1001)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100010,赵斌,男,19,IS,1005)INSERT INTO Student VALUES(20100022,张明明,男,19,CS,1002)/*插入到Course表*/INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES(1,数据库系统原理,5,4)INSERT INTO Course VALUES(2,高等数学,NULL,2)INSERT INTO Course VALUES(3,管理信息系统,1,4)INSERT INTO Course VALUES(4,操作系统原理,6,3)INSERT INTO Course VALUES(5,数据结构,7,4)INSERT INTO Course VALUES(6,数据处理,NULL,2)INSERT INTO Course VALUES(7,C语言,NULL,4)/*插入到SC表*/INSERT INTO SC VALUES(20100001,1,92)INSERT INTO SC VALUES(20100002,2,80)INSERT INTO SC(Sno,Cno) VALUES(20100003,1)INSERT INTO SC(Sno,Cno,Grade) VALUES(20100010,3,NULL)INSERT INTO SC VALUES(20100001,2,85)INSERT INTO SC VALUES(20100001,3,88)INSERT INTO SC VALUES(20100002,1,90)/*多行插入到表中：*/*创建存一个表，保存学生的学号、姓名和年龄*/CREATE TABLE cs_Student(学号 CHAR(8),姓名 CHAR(8),年龄 SMALLINT);/*插入数据行*/INSERT INTO cs_StudentSELECT Sno,Sname,SageFROM Student WHERE Sdept=CS/*检查插入到表中的数据*/SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SC/*修改数据*/*将学生20100001的年龄改为22岁*/UPDATE Student SET Sage=22 WHERE Sno=20100001;/*将所有学生的年龄增加一岁*/UPDATE Student SET Sage=Sage+1/*填写赵斌同学的管理信息系统课程的成绩*/UPDATE SC SET Grade=85WHERE Sno=20100010 AND Cno=3/*将计算机科学系全体学生的成绩加5分*/UPDATE SC SET Grade=Grade+5WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);/*将刘晨同学的2号课程成绩修改为80*/UPDATE SC SET Grade=80WHERE Sno=20100002 AND Cno=2/*将20100021同学的学号修改为20100025*/UPDATE Student SET Sno=20100025 WHERE Sno=20100021;/*检查数据是否修改*/SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SC/*删除数据*/*删除学号为20100022的学生记录*/DELETE FROM Student WHERE Sno=20100022/*删除学号为20100001学生的1号课程选课记录*/*将选课信息复制到一个临时表tmpSC中*/SELECT * INTO tmpSC FROM SC/*在tmpSC中执行删除操作*/DELETE FROM tmpSC WHERE Sno=20100001 AND Cno=1/*删除临时表中20100002学生的全部选课记录*/DELETE FROM tmpSC WHERE Sno=20100002/*删除计算机科学系所有学生的选课记录*/DELETE FROM tmpSC WHERE CS=(select Sdept from student where student.Sno=tmpSC.Sno)/*删除全部选课记录*/DELETE FROM tmpSC/*检查数据是否删除*/SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SCSELECT * FROM tmpSC-3. 数据查询操作-1.单表查询 -1.按指定目标列查询 -查询学生的详细记录 SELECT *FROM Student -查询学生的学号、姓名和年龄 SELECT Sno,Sname,Sage FROM Student -2.目标列包含表达式的查询 -查询全体学生的姓名、出生年份和所有系，要求 用小写字母表示所有系名 SELECT Sname,Year of Birth:,2004-Sage,LOWER(Sdept) FROM Student -3.查询结果集中修改列名称 -查询全体学生的姓名、出生年份和所有系，要求 用小写字母表示所有系名 SELECT Sname,Year of Birth: as BIRTH,2000-Sage BIRTHDAY,DEPARTMENT=LOWER(Sdept) FROM Student -4.取消重复行 -查询选修了课程的学生学号：比较ALL和DISTINCT的区别 SELECT Sno FROM SC; SELECT DISTINCT Sno FROM SC; -5.简单条件查询 -查询计算机科学系全体学生的名单 SELECT Sname FROM Student WHERE Sdept=CS -6.按范围查询 -查询年龄在2023岁之间的学生的姓名、系别和年龄 SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23 -7.查询属性值属于指定集合的行 -查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别SELECT Sname,Ssex FROM Student WHERE Sdept IN(IS,MA,CS) -8.模糊查询 -查询所有姓刘学生的姓名、学号和性别SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘% -9.查询空值 -查询缺少成绩的学生的学号和相应的课程号SELECT Sno,Cno FROM SC WHERE Grade is NULL -10.多重条件查询 -查询计算机科学系年龄在20岁以下的学生姓名SELECT Sname FROM Student WHERE Sdept=CS AND Sage23 -查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系 select Sno,Sname,Sage,Sdept from Studentwhere Sage in(17,18,20,23) -查询年龄不在2124岁之间的学生的姓名、系别和年龄SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 21 AND 24-2.分组统计 -1.聚集函数的使用 -查询学生总人数SELECT COUNT(*) FROM Student -查询选修了课程的学生人数SELECT COUNT(DISTINCT Sno) FROM SC -查询最高分SELECT MAX(Grade) FROM SC -2.聚集函数作用于部分行 -统计2号课程的总分、均分和最高分 SELECT SUM(Grade) 总分,AVG(Grade) 均分,MAX(Grade) 最高分FROM SC WHERE Cno=2 -3.分组统计 -统计各门课程的选课人数、均分和最高分 SELECT Cno 课程号,COUNT(*) 人数,AVG(Grade) 均分,MAX(Grade) 最高分FROM SC GROUP BY Cno -统计均分大于90的课程 SELECT Cno 课程号,COUNT(*) 人数,AVG(Grade) 均分,MAX(Grade) 最高分 FROM SC GROUP BY CnoHAVING AVG(Grade)90 -4.完成下面的查询 -统计每个同学的学号、选课数、平均成绩和最高成绩 SELECT Sno 学号,COUNT(*) 选课数,AVG(Grade) 平均成绩,MAX(Grade) 最高成绩FROM SC GROUP BY Sno -统计每个班的每门课的选课人数、平均成绩和最高成绩 select Cno 班号,count(*) 选课人数,AVG(Grade) 平均成绩,MAX(Grade) 最高成绩from SC group by Cno-3.连接查询 -1.在WHERE中指定连接条件 -查询每个参加选课的学生信息及其选修课程的情况 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SCWHERE Student.Sno=SC.Sno -查询每一门课的间接选修课 SELECT * FROM Course FIRST,Course SENCOND WHERE FIRST.Cpno=SECOND.Cno select o,second.cpno from course first,course secondwhere first.cpno=o -2.在FROM中指定连接条件 -查询每个参加选课的学生信息及其选修课程的情况 select student.sno,sname,ssex,sage,sdept,cno,gradefrom student join sc on (student.sno=sc.sno) -3.使用外连接查询 -查询每个学生信息及其选修课程的情况 select student.sno,sname,ssex,sage,sdept,cno,gradefrom student left outer join sc on(student.sno=sc.sno) -4.复合条件连接查询 -查询选修2号课程且成绩在90分以上的所有学生 select student.sno,snamefrom student join sc on (student.sno=sc.sno)where o=2 and sc.Grade90 -5.多表查询 -查询每个学生的学号、姓名、选修的课程名及成绩select Student.sno,sname,cname,gradefrom student,sc,coursewhere Student.sno=sc.sno and o=o -6.完成下列查询 -查询选修了2号课程的同学的学号和姓名select Student.Sno,Sname from SC,Studentwhere Cno=2 and SC.Sno=Student.Sno -查询各门课程的课程号、课程名称以及选课学生的学号select SC.Cno,Course.Cname,SC.Sno from SC,Coursewhere SC.Cno=Course.Cno -查询选修了数据库系统原理课程的同学的学号、姓名和成绩select Student.Sno,Sname,Grade from SC,Student,Coursewhere Cname=数据库系统原理 and Course.Cno=SC.Cno and SC.Sno=Student.Sno-4.嵌套查询 -1.由IN引出的子查询 -查询与“刘晨”在同一个系学习的学生select sno,sname,sdept from studentwhere sdept in (select sdept from student where sname=刘晨) -2.由比较运算符引出的子查询 -找出每个学生超过他选修课程平均成绩的课程号select sno,cno from sc xwhere grade =(select avg(grade) from sc ywhere y.sno=x.sno) -3.带修饰符的比较运算符引出的子查询-查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄select Sname,Sage from Studentwhere Sage =S_G.Gavg -3.查询每个学生的学号、姓名、选修的课程名及成绩 SELECT Sno,Sname,Cname,Grade FROM XK_VIEW -4.比较使用视图查询和直接从基表查询的优点五、讨论、心得通过本次实验，我熟悉了SQL语句的基本语法，掌握了数据库对象的操作过程，及表的各种操作，如：插入、修改、删除、查询。数据库系统概论实验报告学院名称软件学院专业班级软件1112实验成绩学生姓名苏泽楠学号2011005416实验日期5月课程名称实验三实验题目用户鉴别与数据控制一、实验目的和要求（4） 了解 SQL Serer 数据库系统中数据访问控制的基本方法（5） 了解使用 SSMS 如何给用户授权（6） 熟练掌握常用 GRANT 和 REVOKE 进行权限控制（7） 验证数据库系统的访问控制二、实验内容和原理实验应包括如下方面的内容：l 创建新的数据库用户l 对上一实验建立的表进行权限设置l 检查数据库用户的权限l 撤销用户权限三、主要仪器设备硬件配置：HP-6460b软件环境：SQL Server四、实验程序代码及实验结果-3.2使用SQL语句创建用户并授权 -1.创建登录名 CREATE LOGIN 王明 WITH password=123456 CREATE LOGIN 李刚 WITH password=123456 -2.创建数据库用户 -1.切换到TEST数据 USE TEST -2.创建登录名在TEST数据库中的用户 CREATE USER 王明db FOR LOGIN 王明 CREATE USER 李刚 FOR LOGIN 李刚 -3.授权 -1.授予王明权限 GRANT SELECT ON Student TO 王明db -2.授予李刚权限 GRANT SELECT ON Student TO 李刚 GRANT INSERT,UPDATE,DELETE ON Student TO 李刚 GRANT SELECT,UPDATE ON Course TO 李刚-3.3检查权限控制 -1.检查王明的权限 SELECT * FROM Student INSERT INTO Student(Sno,Sname,Sclass) VALUES(20101101,高志,1008) UPDATE Student Set Sage=Sage+1 DELETE FROM Student WHERE Sno=20101010 SELECT * FROM Course SELECT * FROM SC -2.检查李刚的权限 SELECT * FROM Student INSERT INTO Student(Sno,Sname,Sclass) VALUES(20101201,钱红,1008) UPDATE Student Set Sage=Sage+1 DELETE FROM Student WHERE Sno=20101210 SELECT * FROM Course INSERT INTO Course VALUES(20,FORTRAN语言,NULL,2) UPDATE Course Set Ccredit=3 WHERE Cno=2 DELETE FROM Course WHERE Cno=6 -3.增加李刚的权限 -1.检查李刚能否对SC表执行查询、插入、修改、删除操作SELECT * FROM SCINSERT INTO SC VALUES(20100010,1,99)UPDATE SC Set Grade=100 WHERE Sno=20100010DELETE FROM SC WHERE Sno=20100010-2.使用管理员账号给李刚授予对SC表的查询、插入、修改、删除权限GRANT SELECT,INSERT,UPDATE,DELETE ON SC TO 李刚-3.再次检查李刚能否对SC表执行查询、插入、修改、删除操作SELECT * FROM SCINSERT INTO SC VALUES(20100010,1,99)UPDATE SC Set Grade=100 WHERE Sno=20100010DELETE FROM SC WHERE Sno=20100010-3.4撤销权限 -1.撤销权限 REVOKE SELECT ON Student FROM 李刚 REVOKE INSERT,UPDATE,DELETE ON Student FROM 李刚 -2.检查权限 SELECT * FROM Student INSERT INTO Student(Sno,Sname,Sclass) VALUES(20101501,赵光,1008) UPDATE Student Set Sage=Sage+1 DELETE FROM Student WHERE Sno=20101510 五、讨论、心得通过本次实验，我了解了SQL Server数据库系统中数据访问控制的基本方法，掌握了使用GRANT和REVOKE进行权限控制，并验证了数据库系统的访问控制，加深了我对数据库系统安全性的理解。数据库系统概论实验报告学院名称软件学院专业班级软件1112实验成绩学生姓名苏泽楠学号2011005416实验日期5月课程名称实验四实验题目数据库完整性一、实验目的和要求（8） 了解 SQL Serer 数据库系统中数据完整性控制的基本方法（9） 熟练掌握常用 CREATE 或 ALTER 在创建或修改表时设置约束（10） 了解触发器的机制和使用（11） 验证数据库系统数据完整性控制二、实验内容和原理结合 ST 数据库中的各个表，设置相关的约束，要求包括主键约束、外键约束、唯一约束、 检查约束、非空约束等，掌握各约束的定义方法。设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制。设计一些示例数据，验证完整性检查机制。要求包括如下方面的内容：1. 使用 SQL 语句设置约束使用 CREATE 或 ALTER 语句完成如下的操作，包括：(1) 设置各表的主键约束(2) 设置相关表的外键(3) 设置相关属性的非空约束、默认值约束、唯一约束(4) 设置相关属性的 CHECK 约束2. 使用触发器 创建一个触发器，实现如下的完整性约束：l 当向 SC 表中插入一行数据时，自动将学分累加到总学分中。 l 记录修改学分的操作。 3. 检查约束和触发器 分别向相关表插入若干条记录，检查你设置的完整性约束是否有效：(1) 插入若干条包含正确数据的记录，检查插入情况(2) 分别针对设置的各个约束，插入违反约束的数据，检查操作能否进行(3) 向 SC 表插入若干行数据，检查触发器能否实现其数据一致性功能。三、主要仪器设备硬件配置：HP-6460b软件环境：SQL Server 四、实验程序代码及实验结果-一、声明完整性约束create database TEST-1.创建基本表及约束-1.创建student表create table Student(Snochar(8) primary key,Snamechar(8) not null,Ssexchar(2) check(ssex in(男,女),Sagesmallint,Sdeptchar(20),Sclasschar(4) not null,Stotalsmallint default 0);-2.创建course表create table Course(Cnochar(4)constraint fk_course primary key,Cnamechar(40),Cpnochar(4),Ccreditsmallint);-3.创建SC表create table SC(Snochar(8)foreign key (Sno) references Student(Sno),Cnochar(4),Gradesmallint constraint sc_check check(grade0 and grade=0 and Grade = 100)-二、检查完整性约束-1.检查主键约束-1.执行下面的语句修改Student表，观察语句能否正确运行，解释为什么insert into Student values(20100101,李斌,男,20,CS,1001,0)insert into Student values(20100001,李斌,男,20,CS,1001,0) -不能运行update Student set sno=20100021 where Sname=张立 -ERROR-2.执行下面的语句修改SC表，观察语句能否正确运行，解释为什么insert into SC values(20100001,1,78) -ERRORinsert into SC values(20100001,null,78) -ERROR-2.检查唯一约束-执行下面的语句修改Course表，观察语句能否正确运行，解释为什么insert into Course values(8,JAVA,7,3)insert into Course values(9,数据结构,7,3)-3.检查默认值、允许空值列-运行如下的语句insert into Student(Sno,Sname,Ssex,Sclass) values(20100102,张盛,男,1008)-观察插入数据行的数值select * from Student where Sno=20100102-4.检查非空约束-下面的语句包含空值，检查运行结果，解释为什么insert into Student(Sno,Sname,Ssex,Sclass) values(20100103,张盛,男,null)insert into Student(Sno,Sname,Ssex) values(20100104,张盛,男)-5.检查CHECK约束-执行下面的语句，解释其运行结果insert into SC values(20100001,4,95)insert into SC values(20100001,4,102)insert into Student(Sno,Sname,Ssex,Sclass) values(2010