太原理工数据库-实验报告

1、 本科实验报告课程名称： 数据库系统原理B 实验项目： 交互式SQL、数据完整性 实验地点： 专业班级： 学号： 学生姓名： 指导教师： 2015年 6 月 24 日实验二：交互式 SQL语句的使用一、实验目的（1）掌握数据库对象的操作过程,包括创建、修改、删除。（2）熟悉表的各种操作，包括插入、修改、删除、查询（3）熟练掌握常用 SQL语句的基本语法二、实验内容和要求选择如下一个应用背景之一：学生选课系统其它你熟悉的应用（1）建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。（2）要求认真进行实验，记录各实验用例及执行结果。（3）深入了解各个操作的功能。实验要

2、求包括如下方面的内容：3.1 数据定义1 基本表的创建、修改及删除2 索引的创建3 视图的创建3.2 数据操作完成各类更新操作包括：1 插入数据2 修改数据3.删除数据3.3 数据查询操作完成各类查询操作1 单表查询2 分组统计3. 连接查询4. 嵌套查询5. 集合查询3.4 数据操作1 创建视图2 视图查询三、主要仪器设备操作系统：Windows 7 Windows 2003 Server 数据库管理系统： SQL Server。四、操作方法与实验步骤1.创建，修改及删除基本表（1）创建 Student表CREATE TABLE Student( Sno CHAR(8) PRIMARY KE

3、Y, Sname CHAR(8) , Ssex CHAR(2) NOT NULL, Sage INT, Sdept CHAR(20);（2）创建 Course表CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL, CpnoC HAR(4), Ccredit SMALLINT,);（3）创建 SC表Create table SC(Sno char(8), Cno char(4), Grade smallint,Primary key(Sno,Cno),Foreign key(Sno)references St

4、udent(Sno),Foreign key(Cno)references Course(Cno); （4）创建员工表EmployeeCREATE TABLE Employee(编号 CHAR(8) PRIMARY KEY,姓名 VARCHAR(8) not null部门 CHR（40）,工资 numeric(8,2),生日 datetime，职称 char(20),);指出该语句中的错误并改正后执行。（5）检查表是否创建成SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SCSELECT * FROM Employee修改表结构及约

5、束增加班级列ALTER TABLE Student ADD Sclass char(4)修改年龄列ALTER TABLE Student ALTER COLUMN Sage smallint增加约束ALTER TABLE Course ADD UNIQUE(Cname)（7）删除表DROP TABLE Employee2创建索引（1）为 Course表按课程名称创建索引CREATE INDEX iCname On Course(Cname)（2）为 Student表按学生姓名创建唯一索引CREATE UNIQUE INDEX iSname ON Student(Sname)（3）为 SC表按学

6、号和课程号创建聚集索引CREATE CLUSTERED INDEX iSnoCno On SC(Sno,Cno desc)（4）为 Course表按课程号创建唯一索引CREATE UNIQUE INDEX iCno ON Course(Cno)3创建视图建立信息系学生的视图：CREATE VIEW IS_StudentASSELECT Sno,Sname,Sage FROM StudentWHERE Sdept= IS3.2数据操作插入数据(1)插入到Student表INSERT INTO Student VALUES(20100001,李勇,男,20,CS,1001)INSERT INTO

7、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

8、(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(Cno,Cname,Cpno,Ccredit)values(2,高等数学,null,2)insert into Course(Cno,Cname,Cpno,Ccredit)v

9、alues(3,管理信息系统,1,4)insert into Course(Cno,Cname,Cpno,Ccredit)values(4,操作系统原理,6,3)insert into Course(Cno,Cname,Cpno,Ccredit)values(5,数据结构,7,4)insert into Course(Cno,Cname,Cpno,Ccredit)values(6,数据处理,null,2)insert into Course(Cno,Cname,Cpno,Ccredit)values(7,C语言,null,4)插入到Sc表insert into Sc values(201000

10、01,1,92)insert into Sc values(20100001,2,85)insert into Sc(Sno,Cno)values(20100003,1)insert into Sc(Sno,Cno,Grade)values(20100010,3,null)insert into Sc values(20100001,3,88)insert into Sc values(20100002,1,90)insert into Sc values(20100002,2,80)多行插入到表中create table cs_Student( 学号 char(8), 姓名 char(8),

11、 年龄 smallint);insert into cs_Student select Sno,Sname,Sagefrom Student where Sdept=CS;检查插入到表中的数据SELECT * FROM StudentSELECT * FROM CourseSELECT * FROM SC修改数据（1）将学生 20100001的年龄改为22岁。UPDATE student SET Sage = 22 WHERE Sno=20100001;（2）将所有学生的年龄增一岁。UPDATE Student SET Sage = Sage +1（3）填写赵斌同学的管理信息系统课程的成绩UP

12、DATE SC SET Grade = 85WHERE Sno=20100010 AND Cno=3（4）将计算机科学系全体学生的成绩加5分UPDATE sc SET Grade=Grade + 5WHERE CS=(select Sdept from student where student.Sno=sc.Sno);（5）请自己完成如下操作将刘晨同学的2号课程成绩修改为80update SC set Grade=80 where Sno=20100002AND Cno=2将“20100021”同学的学号修改为“20100025”update Student set Sno=20100025

13、 where Sno=20100021;（6）检查数据是否修改3.删除数据（1）删除学号为 201000022的学生记录DELETE FROM Student WHERE Sno=20100022（2）删除学号 20100001学生的 1号课程选课记录将选课信息复制到一个临时表tmpSC中：SELECT * INTO tmpSC FROM SC在 tmpSC中执行删除操作：DELETE FROM tmpSC WHERE Sno=20100001 and Cno=1（3）删除临时表中 20100002学生的全部选课记录delete from tmpSC where Sno=20100002;（4

14、）删除计算机科学系所有学生的选课记录DELETE FROM tmpSC WHERE CS=(select Sdept from student where student.Sno=tmpSC.Sno );（5）删除全部选课记录DELETE FROM tmpSC检查数据是否删除3.3 数据查询操作完成如下查询操作：1单表查询（1）按指定目标列查询查询学生的详细记录：SELECT* FROM Student;查询学生的学号、姓名和年龄SELECT Sno,Sname,Sage FROM Student;（2）目标列包含表达式的查询查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。S

15、ELECT 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）简单条件

16、查询查询计算机科学系全体学生的名单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

17、FROM Student WHERE Sname LIKE 刘%（9）查询空值查询缺少成绩的学生的学号和相应的课程号SELECT Sno,Cno FROM sc WHERE Grade is null;（10）多重条件查询查询计算机科学系年龄在岁以下的学生姓名SELECT Sname FROM student WHERE Sdept=CS and Sage23; 查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系select Sno,Sname,Sage,Sdept from Student where Sage in(17,18,20,23);查询年龄不在2124岁之间的学生的

18、姓名、系别和年龄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）分组

19、统计统计各门课程的选课人数、均分和最高分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(Cno)选课数,AVG(Grade) 平均成绩,MAX(Grade) 最高成绩from SC group b

20、y Sno统计每个班的每门课的选课人数、平均成绩和最高成绩select Student.Sclass 班级, SC.Cno 课程号,COUNT(*) 选课人数,AVG(SC.Grade) 平均成绩,MAX(SC.Grade) 最高成绩from SC,Studentwhere SC.Sno=Student.Snogroup by Student.Sclass,SC.Cno3.连接查询（1）在 WHERE中指定连接条件查询每个参加选课的学生信息及其选修课程的情况SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WH

21、ERE Student.Sno = SC.Sno查询每一门课的间接先修课SELECT * FROM course first,course secondWHERE first.Cpno=second.Cno;SELECT first.Cno,second.Cpno FROM course first,course secondWHERE first.Cpno=second.Cno;（2）在 FROM中指定连接条件查询每个参加选课的学生信息及其选修课程的情况SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade From Student JOIN S

22、C ON (Student.Sno=SC.Sno)（3）使用外连接查询查询每个学生信息及其选修课程的情况SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)（4）复合条件连接查询查询选修号课程且成绩在分以上的所有学生SELECT Student.Sno, SnameFROM Student join SC ON (Student.Sno = SC.Sno) /*连接条件*/WHERE SC.Cno= 2 AND SC.Grade 90;

23、 /*过滤条件*/（5）多表查询查询每个学生的学号、姓名、选修的课程名及成绩SELECT Student.Sno,Sname,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;完成下列查询查询选修了 2号课程的同学的学号和姓名select Student.Sno,Snamefrom Student,SCwhere Student.Sno=SC.Sno and SC.Cno=2查询各门课程的课程号、课程名称以及选课学生的学号select SC.Cno 课程号,Course.Cna

24、me 课程名称,SC.Sno 学号from SC,Coursewhere SC.Cno=Course.Cno查询选修了数据库系统原理课程的同学的学号和姓名和成绩select Student.Sno,Sname,Gradefrom Student,Course,SCwhere Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname=数据库系统原理;4.嵌套查询（1）由 In引出的子查询查询与“刘晨”在同一个系学习的学生SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN ( SELECT Sdept FR

25、OM 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 ALL ( SELECT Sage FROM Student WHERE Sdept= CS)AND Sdept C

26、S ;（4）由 EXISTS引出的子查询查询所有选修了1号课程的学生姓名SELECT Sname FROM StudentWHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1)5.集合查询（1）集合并查询计算机科学系的学生及年龄不大于19岁的学生SELECT * FROM Student WHERE Sdept= CSUNIONSELECT * FROM Student WHERE Sage=19（2）集合交查询计算机科学系且年龄不大于 19岁的学生SELECT * FROM Student WHERE Sdept=CS

27、INTERSECTSELECT * FROM Student WHERE Sage=19（3）集合差查询计算机科学系且年龄大于19岁的学生SELECT * FROM Student WHERE Sdept=CSEXCEPTSELECT * FROM Student WHERE Sage = S_G.Gavg（3）查询每个学生的学号、姓名、选修的课程名及成绩SELECT Sno,Sname,Cname,GradeFROM XK_VIEW（4）比较使用视图查询和直接从基表查询的优点五、实验数据记录和处理检查数据是否被修改：检查数据是否删除：单表查询：查询学生基本信息，结果集属性名使用 查询信息系且

28、年龄大于23岁同学的学号汉字 无结果查询年龄是17、18、20、23岁同学的 查询年龄不在2124岁之间的学生的姓名、学号、姓名、年龄和所在系 系别和年龄 分组统计：统计每个同学的学号、选课数、平均成绩 统计每个班的每门课的选课人数、平均成绩 连接查询：查询选修了2号课程的同学的学号和姓名 查询各门课程的课程号、课程名称以及选课学 生的学号 查询选修了数据库系统原理课程的同学的学号六、实验结果与分析StudentCourse Sc 比较使用视图查询和直接从基表查询的优点视图可以简化用户的操作 视图机制使用户可以将注意力集中在所关心的数据上，而从基本表直接得来则显得复杂，定义了视图则可以简化查询

29、操作。视图使用户能以多种角度看待同一数据 视图可以让用户从不同的方式看待同一数据，适当利用视图可以比基本表更清晰地表达。视图对重构数据库提供了一定程度的逻辑独立性 逻辑结构改变，不影响程序使用。视图能够对机密数据提供安全保护 视图可以让特定的用户查询特定的内容，把用户限制在数据不同的子集，保证安全性。七、讨论、心得 这个实验是对数据库基本操作的熟悉，花费了大量的时间去进行测试，有时候是出错不是因为代码错误，而是因为插入数据是不能一一对应，或是在连接时出现问题，与表的定义也有一定的关系。总之编写的时候遇到不少问题，通过解决问题来熟悉了操作，更是要耐心的解决问题，有时候出错的问题非常简单，只要认真

30、的检查一下代码就能发现，希望自己可以在编写代码时更加认真。实验三：数据完整性实验目的（1）了解 SQL Serer数据库系统中数据完整性控制的基本方法（2）熟练掌握常用 CREATE 或 ALTER 在创建或修改表时设置约束（3）了解触发器的机制和使用（4）验证数据库系统数据完整性控制二、实验内容和要求结合 ST数据库中的各个表，设置相关的约束，要求包括主键约束、外键约束、唯一约束、检查约束、非空约束等，掌握各约束的定义方法。设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制。设计一些示例数据，验证完整性检查机制。要求包括如下方面的内容：3.1 使用 SQL语句设置约束使用

31、CREATE或 ALTER语句完成如下的操作，包括：1设置各表的主键约束2设置相关表的外键3. 设置相关属性的非空约束、默认值约束、唯一约束4. 设置相关属性的 CHECK约束3.2 使用触发器创建一个触发器，实现如下的完整性约束：当向Sc表中插入一行数据时，自动将学分累加到总学分中。记录修改学分的操作。3.4 检查约束分别向相关表插入若干条记录，检查你设置的完整性约束是否有效：1插入若干条包含正确数据的记录，检查插入情况。2分别针对设置的各个约束，插入违反约束的数据，检查操作能否进行。3向 SC表插入若干行数据，检查触发器能否实现其数据一致性功能。三、主要仪器设备操作系统：Windows 7

32、 Windows 2003 Server 数据库管理系统： SQL Server。四、操作方法与实验步骤1创建基本表及约束（1）创建 Student表CREATE TABLE Student( Sno CHAR(8) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK( Ssex in (男,女), Sage SMALLINT, Sdept CHAR(20), Sclass CHAR(4) NOT NULL, Stotal smallint DEFAULT 0);掌握主键约束、非空约束、CHECK约束、默认值的定义格式。（2）创建 C

33、ourse表CREATE TABLE Course( Cno CHAR(4) CONSTRAINT PK_Course PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT);（3）创建 SC表CREATE TABLE SC( Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno), Cno CHAR(4), Grade SMALLINT CONSTRAINT SC_CHECK CHECK(Grade 0 AND Grade=0 AND Grade=100) 掌握如何修改约束

34、理解命名约束的优点二、检查完整性约束通过修改数据库中的数据检查完整性约束条件的作用。1检查主键约束2检查唯一约束3检查默认值、允许空值列4检查非空约束5检查 CHECK约束6检查外键约束 具体内容在第五部分三、触发器的定义及使用1定义触发器（1）定义一个触发器，实现有关学分的完整性约束：当向SC表插入一行选课记录时，自将该课程的学分累CREATE TRIGGER tr_INSERT ON SCFOR INSERTAS-声明变量DECLARE sno char(8)DECLARE cridit intDECLARE cno char(4)-提取插入的数据SELECT sno=Sno,cno=Cn

35、o FROM inserted-提取学生的总学分SELECT cridit = CcreditFROM SC join Course ON (SC.Cno = Course.Cno)WHERE SC.Cno = cno-更新总学分UPDATE Student SET Stotal = Stotal + criditWHERE Sno = snoGO（2）定义一个触发器，实现对SC表的操作登记：当用户向SC表插入或修改时，记录该操作到数据库中。创建日志登记表:CREATE TABLE LOG_TABLE( username char(10), -操作人员 date datetime, -修改时间

36、 Sno char(8) , -学生学号 Cno char(4) -课程号 )创建日志登记触发器:CREATE TRIGGER tr_UPDATE ON SCFOR INSERT,UPDATEASDECLARE sno char(8)DECLARE cno char(4)DECLARE new smallintSELECT sno=Sno,cno=Cno FROM insertedINSERT INTO LOG_TABLE VALUES(CURRENT_USER,getdate(),Sno,Cno);GO（3）执行插入操作，触发触发器：INSERT INTO SC VALUES(2010000

37、1,6,95)（4）验证触发器是否触发select * from studentselect * from LOG_TABLE五、实验数据记录和处理1检查主键约束（1）执行下面的语句修改Student表，观察语句能否正确运行，解释为什么？INSERT INTO Student VALUES(20100101,李斌,男,20,CS,1001,0)INSERT INTO Student VALUES(20100001,李斌,男,20,CS,1001,0)该句无法执行，因为Sno主键不能有重复。UPDATE Student SET Sno=20100021 WHERE Sname = 张立该句无法执行，因为张立要修改的学号和王敏的学号重复了，Sno是主键，不能重复（2）执行下面的语句修改SC表，观察语句能否正确运行，解释为什么？INSERT INTO SC VALUES(20100001,1,78)该句无法执行，主键重复。INSERT INTO SC VALUES(20100001,null,78)该句无法执行，第二个值不能为空。2检查唯一约束执行下面的语句修改Course表，观察语句能否正确运行，解释为什么？INSERT INTO Course VALUES(8,JAVA,7, 3)INSERT I