太原理工大学数据库实验.doc

Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-date太原理工大学数据库实验太原理工大学数据库实验实 验 报 告课程名称： 数据库系统原理 实验项目：认识DBMS系统、交互式SQL、数据完整性、用户鉴别与数据控制实验地点： 实验室210 专业班级： 软件1334 学号： 学生姓名： 指导教师： 宋晓涛 2015年5月 8日学院名称软件学院专业班级1334实验成绩学生姓名学号实验日期2015.0课程名称数据管理库系统概论实验题目认识DBMS系统一、实验目的和要求 （1）通过对SQL Server 2005/2008数据库管理系统的使用，了解DBMS的工作原理和系统构架。    （2）熟悉SQL Server提供的管理工具    （3）熟悉使用SQL Server Management Studio创建数据库对象二、主要仪器设备计算机：HP-6470b windows7 64 位 运行环境： SQL Server 2008R2三、实验内容及要求3.1  安装SQL Server    1在安装过程中记录安装的选择，并且对所作的选择进行思考，为何要进行这样的配置，对今后运行数据库管理系统会有什么影响。    2理解默认实例、命名实例的含义3了解SQL Server的身份认证模式，初步了解SQL Server的安全性。4了解SQL Server提供的服务。5检查SQL Server安装是否成功。3.2  管理和使用SQL Server了解SQL Server如何通过它提供的工具对数据库服务器进行管理和使用的。1、启动、暂停和停止SQL Server学会运用SQL Server配置管理或SQL Server Management Studio启动和停止SQL Server的各种服务。2、了解SQL Server的管理工具初步了解SQL Server的提供了哪些主要管理工具和它们的功能。3、学会使用SQL Server联机丛书学会SQL Server联机丛书查询SQL命令语法格式、SQL Server数据库的概念、术语等内容。3.3  熟悉使用SQL Server Management Studio了解SQL Server Management Studio的基本用法，能熟练使用它管理数据库服务器和数据库对象。1、可视化图形界面的操作熟悉使用SQL Server Management Studio中的对象资源管理器，熟悉其中列出的各类信息的含义，学会查看各种信息。2、查询编辑器的使用熟悉使用查询编辑器，了解如何编写SQL脚本，如何运行SQL脚本。3.4 创建SQL Server数据库通过创建数据库，掌握使用SQL Server Management Studio创建数据库的方法，了解SQL Server数据库的构成。3.1  创建数据库 1使用图形界面创建数据库系 创建一个数据库，掌握使用图形界面创建数据库的步骤，了解其中各参数的含义。 2使用SQL语句创建数据库使用查询编辑器编写并运行创建数据库的SQL语句，掌握SQL语句的语法格式和主要参数的含义。3.2  了解数据库系统的构架 1了解数据库系统的逻辑组件： 它们主要是数据库对象，包括基本表、视图、触发器、存储过程、约束等。今后将学习如何操作这些数据库对象。 2了解数据库的物理组件： 思考数据库物理存储的存储单位是什么，数据主要以什么方式存放，如何确定数据的存放位置。3.5 创建SQL Server数据库中的表通过创建表，掌握使用SQL Server Management Studio创建表方法，了解SQL Server数据库的数据类型，以及表中数据的维护。3.1  创建数据库表1使用图形界面创建表在数据库中创建Student、Course和SC表，掌握使用图形界面创建表的方法。2掌握如何查询表的各种信息。3了解SQL Server提供的数据类型通过查询联机手册，了解SQL Server提供了哪些数据类型，以及他们的用法。3.2  维护表中的数据使用图形界面在Student、Course和SC输入分别输入若干行数据，熟练掌握数据输入、修改和删除的方法。四、操作方法与实验步骤1、创建数据库 选择“新建数据库”，进入“新建数据库”界面：在“数据库名称”输入框中输入数据库名称：test，点击“确定”，即可创建数据库。展开“数据库”，创建的数据库将显示在列表中，如下图中的“test”。2、创建表创建学生-课程数据库中的三个表student、course和sc。3、输入、修改和删除数据行4、查看数据鼠标右击要查看的表，在快捷菜单中选择“选择前1000行”五、讨论、心得 遇到问题应该及时的问老师,问同学,并且结合课本,做到解决,深化所学到的知识，从而使自己的知识得到深化，能力得到进步。教师签字：学院名称软件学院专业班级软件1334实验成绩学生姓名学号实验日期2015.课程名称数据库系统管理实验题目交互式SQL一、实验目的和要求1，掌握 SELECT 语句基本语法，加深对查询语句基本概念要求。2，掌握简单表的查询。3，掌握连接查询。4，熟悉通过 SQL 对数据库进行操作。5, 掌握视图的操作。二、主要仪器设备计算机：HP-6470b windows7 64 位 运行环境： SQL Server 2008R2三、实验内容和原理1在RDBMS 中建立一个学生-课程数据库，进行实验所要求的各种操作，所有的SQL 操作均在此建立的新库里进行。2根据以下要求认真进行实验，记录所有的实验用例及执行结果。数据定义：基本表的创建、修改及删除；索引的创建和删除。数据操作：完成各类查询操作(单表查询，连接查询，嵌套查询，集合查询)；完成各类更新操作(插入数据，修改数据，删除数据)。视图的操作：视图的定义(创建和删除)，查询，更新(注意更新的条件)。特别说明：实验中注意特定数据库系统（如SQL SERVER）的SQL 语句格式与SQL-3 标准的区别。四、操作方法与实验步骤1. 数据定义创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：表1. Student表结构列名称用途类型长度约束备注Sno学号字符8主键Sname姓名字符8Ssex性别字符2Sage年龄整型Sdept所在系字符20Sclass班级字符4表2. Course表结构列名称用途类型长度约束备注Cno课程号字符4主键Cname课程名字符40Cpno先修课程号 字符4Ccredit学分整型表3. SC表结构列名称用途类型长度约束备注Sno学号字符8外键Cno课程号字符4Grade成绩整型2. 建立基本表建立一个学生表Student：建立一个课程表Course：建立一个学生选课信息表SC：3. 数据操作一更新操作1,插入数据将如下表格中的数据分别插入到数据库相应的表中：表4.学生基本信息表学号姓名性别年龄所在系班级20100001李勇男20CS100120100002刘晨女19CS100120100021王敏女18MA100220100031张立男19IS100320100003刘洋女100120100010赵斌男19IS100520100022张明明男19CS1002表5.课程信息表课程号课程名先修课程号学分1数据库系统原理5642高等数学23管理信息系统144操作系统原理635数据结构746数据处理27C语言4表6.学生选课信息表学号课程号成绩2010000119220100001285201000013882010000219020100002280201000031201000103（1）插入到Student表（2）插入到Course表（3）插入到SC表2,修改数据（1）将学生20100001的年龄改为22岁。UPDATE student SET Sage = 22 WHERE Sno='20100001'（2）将所有学生的年龄增加一岁。UPDATE Student SET Sage = Sage +1（3）填写赵斌同学的管理信息系统课程的成绩（4）将计算机科学系全体学生的成绩加5分（5）请自己完成如下操作a.将刘晨同学的2号课程成绩修改为80b.将“20100021”同学的学号修改为“20100025”3,删除数据将选课信息复制到一个临时表tmpSC中：在tmpSC中执行删除操作：（1）删除学号为201000022的学生记录（2）删除学号20100001学生的1号课程选课记录（3）删除临时表中20100002学生的全部选课记录请自己完成该操作。（4）删除计算机科学系所有学生的选课记录（5）删除全部选课记录（6）检查数据是否删除 二数据查询操作 完成如下查询操作：1 单表查询（1）按指定目标列查询l查询学生的详细记录： l查询学生的学号、姓名和年龄 （2）目标列包含表达式的查询l查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。 （3）查询结果集中修改列名称l查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。 （4）取消重复行l查询选修了计算机课程的学生学号：比较ALL和DISTINCT的区别 （5）简单条件查询l查询计算机科学系全体学生的名单 （6）按范围查询l查询年龄在2023岁之间的学生的姓名、系别和年龄 （7）查询属性值属于指定集合的行l查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别 （8）模糊查询l查询所有姓刘学生的姓名、学号和性别 （9）查询空值l查询缺少成绩的学生的学号和相应的课程号 （10）多重条件查询l查询计算机科学系年龄在20岁以下的学生姓名 （11）结果集排序l查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列 （12）完成下列查询l查询学生基本信息，结果集属性名使用汉字 l查询信息系且年龄大于23岁同学的学号和姓名 l查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系 l查询年龄不在2124岁之间的学生的姓名、系别和年龄 2 分组统计（1）聚集函数的使用l查询学生总人数 l查询选修了课程的学生人数 l查询最高分 （2）聚集函数作用于部分行l统计2号课程的总分、均分和最高分 （3）分组统计l统计各门课程的选课人数、均分和最高分 l统计均分大于90的课程 （4）完成下面的查询l统计每个同学的学号、选课数、平均成绩和最高成绩 l统计每个班的每门课的选课人数、平均成绩和最高成绩3. 连接查询（1）在WHERE中指定连接条件l查询每个参加选课的学生信息及其选修课程的情况 l查询每一门课的间接先修课 （2）在FROM中指定连接条件l查询每个参加选课的学生信息及其选修课程的情况（3）使用外连接查询l查询每个学生信息及其选修课程的情况（4）复合条件连接查询l查询选修2号课程且成绩在90分以上的所有学生 （5）多表查询l查询每个学生的学号、姓名、选修的课程名及成绩 （6）完成下列查询l查询选修了2号课程的同学的学号和姓名 l查询各门课程的课程号、课程名称以及选课学生的学号 l查询选修了数据库系统原理课程的同学的学号和姓名和成绩 4. 嵌套查询（1）由In引出的子查询l查询与“刘晨”在同一个系学习的学生 （2）由比较运算符引出的子查询l找出每个学生超过他选修课程平均成绩的课程号。 （3）带修饰符的比较运算符引出的子查询l查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。 （4）由EXISTS引出的子查询l查询所有选修了1号课程的学生姓名 5. 集合查询（1）集合并l查询计算机科学系的学生及年龄不大于19岁的学生 （2）集合交l查询计算机科学系且年龄不大于19岁的学生 （3）集合差l查询计算机科学系且年龄大于19岁的学生 6视图操作建立视图并基于视图进行查询：1. 创建视图（1）建立学生基本信息视图（2）建立学生均分视图 （3）建立选课信息视图 2. 视图查询（1）查询学生基本信息 （2）找出每个学生超过他选修课程平均成绩的课程号 （3）查询每个学生的学号、姓名、选修的课程名及成绩 （4）比较使用视图查询和直接从基表查询的优点1，能分割数据,简化观点；2,，为数据提供一定的逻辑独立性；3，提供自动的安全保护功能；4，视图可以间接对表进行更新,因此视图的更新就是表的更新。五、讨论、心得 平时要多思考，多实践，这样子才能进步，才能有所收获，才能受益匪浅。学院名称软件学院专业班级软件1334实验成绩学生姓名 学号 实验日期2014.6课程名称数据库系统管理实验题目数据完整性一、实验目的和要求（1）了解SQL Serer数据库系统中数据完整性控制的基本方法（2）熟练掌握常用CREATE 或ALTER 在创建或修改表时设置约束（3）了解触发器的机制和使用（4）验证数据库系统数据完整性控制二、主要仪器设备计算机：HP-6470b windows7 64 位 运行环境： SQL Server 2008R2三、实验内容和原理结合ST数据库中的各个表，设置相关的约束，要求包括主键约束、外键约束、唯一约束、检查约束、非空约束等，掌握各约束的定义方法。设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制。设计一些示例数据，验证完整性检查机制。要求包括如下方面的内容：3.1  使用SQL语句设置约束使用CREATE或ALTER语句完成如下的操作，包括：1 设置各表的主键约束2 设置相关表的外键3. 设置相关属性的非空约束、默认值约束、唯一约束4. 设置相关属性的CHECK约束3.2  使用触发器创建一个触发器，实现如下的完整性约束：.王玉民010003程明010002王林010001总学分姓名学号709080成绩310601000242060100013106010001学分课程号学号SCStudentl 当向SC表中插入一行数据时，自动将学分累加到总学分中。l 记录修改学分的操作。3.4  检查约束和触发器    分别向相关表插入若干条记录，检查你设置的完整性约束是否有效：1 插入若干条包含正确数据的记录，检查插入情况2 分别针对设置的各个约束，插入违反约束的数据，检查操作能否进行3 向SC表插入若干行数据，检查触发器能否实现其数据一致性功能。四、操作方法与实验步骤一、声明完整性约束创建学生选课数据库“学生选课”，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：表1. Student表结构列名称用途类型长度约束备注Sno学号字符8主键Sname姓名字符8非空Ssex性别字符2男，女Sage年龄整型Sdept所在系字符20Sclass班级字符4非空Stotal总学分整型默认值0表2. Course表结构列名称用途类型长度约束备注Cno课程号字符4主键Cname课程名字符40非空，唯一Cpno先修课程号 字符4外键引用本表的CnoCcredit学分整型表3. SC表结构列名称用途类型长度约束备注Sno学号字符8主码属性，外键引用Student的SnoCno课程号字符4主码属性，外键引用Course的CnoGrade成绩整型0<=成绩<=1001创建基本表及约束（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 );l 掌握主键约束、非空约束、CHECK约束、默认值的定义格式。（2）创建Course表CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) not null unique, Cpno CHAR(4),Ccredit SMALLINT,foreign key(Cpno) references Course(Cno);（3）创建SC表CREATE TABLE SC( Sno CHAR(8),Cno CHAR(4), Grade INT CONSTRAINT SC_CHECK CHECK(Grade >0 AND Grade<100), PRIMARY KEY (Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY (Cno) REFERENCES Course(Cno);l 掌握多个属性的主键约束、外键约束、CHECK约束的定义格式。l 掌握约束的命名方式。（constraint 约束名 约束条件）（4）检查表是否创建成功。2 插入数据将如下表格中的数据分别插入到数据库相应的表中：表4.学生基本信息表学号姓名性别年龄所在系班级总学分20100001李勇男20CS1001020100002刘晨女19CS1001020100021王敏女18MA1002020100031张立男19IS1003020100003刘洋女1001020100010赵斌男19IS1005020100022张明明男19CS10020表5.课程信息表课程号课程名先修课程号学分1数据库系统原理542高等数学23管理信息系统144操作系统原理635数据结构746数据处理27C语言4表6.学生选课信息表学号课程号成绩2010000119220100001285201000013882010000219020100002280201000031201000103（1）插入学生信息到Student表INSERT INTO StudentVALUES('20100001','李勇','男',20,'CS','1001',0)INSERT INTO StudentVALUES('20100002','刘晨','女',19,'CS','1001',0)INSERT INTO StudentVALUES('20100021','王敏','女',18,'MA','1002',0)INSERT INTO StudentVALUES('20100031','张立','男',19,'IS','1003',0)INSERT INTO Student(Sno,Sname,Ssex,Sclass,Stotal)VALUES('20100003','刘洋','女','1001',0)INSERT INTO StudentVALUES('20100010','赵斌','男',19,'IS','1005',0)INSERT INTO StudentVALUES('20100022','张明明','男',19,'CS','1002',0)（2）插入到课程信息到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)VALUES('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) （3）插入到SC表INSERT INTO SCVALUES('20100001','1',92)INSERT INTO SCVALUES('20100001','2',85)INSERT INTO SCVALUES('20100001','3',88)INSERT INTO SCVALUES('20100002','1',90)INSERT INTO SCVALUES('20100002','2',80)INSERT INTO SC(Sno,Cno)VALUES('20100003','2')INSERT INTO SC(Sno,Cno)VALUES('20100010','3')（4）检查插入到表中的数据3 修改约束对数据库中已经存在的表，可对其增加约束或修改已存在的约束：（1）添加约束ALTER TABLE Course ADD UNIQUE(Cname)ALTER TABLE Course ADD FOREIGN KEY (Cpno) REFERENCES Course(Cno)l 掌握如何添加约束l 掌握如何声明唯一约束（2）修改约束ALTER TABLE SC DROP CONSTRAINT SC_CHECKALTER TABLE SC ADD CONSTRAINT SC_CHECK CHECK(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)不可以正确执行，因为学号Sno已经被定义为主码primary key约束，即表中学号一列内容不可以重复，在表中已经存在学号为20100001的记录，所以不可以正确执行。UPDATE Student SET Sno='20100021' WHERE Sname = '张立'不可以正确执行，理由同上一条。（2）执行下面的语句修改SC表，观察语句能否正确运行，解释为什么？INSERT INTO SC VALUES('20100001','1',78)不可以正确执行，因为在SC表中学号Sno和课程号Cno被定义为联合主码，即两者不可以与其他记录完全相同，如果学号与其他记录相同时，课程号与此学号对应的课程号不发生重复即可正常执行，反之如果课程号与其他记录相同时也是如此。因为表中已经存在了学号为20100001，课程号为1的成绩记录，所以不可以再次插入。INSERT INTO SC VALUES('20100001',null,78)不可以正确执行，因为Cno被定义为主码，主码不允许出现null值。2检查唯一约束执行下面的语句修改Course表，观察语句能否正确运行，解释为什么？INSERT INTO Course VALUES('8','JAVA',7, 3)可以正确执行。INSERT INTO Course VALUES('9','数据结构',7, 3)不可以正确执行，因为课程名Cname被定义为唯一值（UNIQUE），即在此列中不允许出现相同值，表中已经有课程名为数据结构的记录，所以不可以正确执行。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)不可以正确执行，因为Sclass被定义为非空（not null），不可以插入Sclass值为空值的记录。INSERT INTO Student(Sno,Sname,Ssex) VALUES('20100104','张盛','男')不可以正确执行，因为Sclass被定义为非空（not null），在插入数据时必须插入不允许为空的属性值。5检查CHECK约束执行下面的语句，解释其运行结果。INSERT INTO SC VALUES('20100001','4',95)可以正确执行。INSERT INTO SC VALUES('20100001','4',102)不可以正确执行，首先Sno与Cno被定义为联合主码不可重复，而此条记录与上一条插入的发生重复；其次因为成绩为102，而表定义时规定成绩须为0到100之间，即CHECK(Grade >0 AND Grade<100)，不在此范围内不满足check约束所以不可以正确执行。INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100103','张盛','男','1008')可以正确执行。INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100104','张盛','','1008')不可以正确执行，性别Ssex属性被约束值必须为男或者女，即CHECK( Ssex in ('男','女')，而此条记录中没有性别的值，不满足性别属性的check约束，所以不可以正确执行。6检查外键约束（1）执行下面的语句检查外键约束的作用INSERT INTO SC VALUES('20100301','1',95)不可以正确执行，SC表中定义Sno与Cno为外键，分别引用Course表中的Sno与Cno，因为Course表中不存在学号为20100301的记录，所以不满足外键约束，不可以正确执行。INSERT INTO SC VALUES('20100001','10',95)不可以正确执行，理由同上，Course表中不存在课程号为10的记录。UPDATE SC SET Cno = '10' Where Cno='1'不可以正确执行，理由同上。UPDATE Course SET Cno = '10' Where Cno='3'不可以正确执行，理由同上。外键约束为子表（SC）中的记录必须从父表（Course）中来，子表中需引用父表的属性的值不可以出现父表中被引用属性中没有的值的记录。（2）执行下面的语句检查对被引用表的约束DELETE Student WHERE Sno='20100021'可以正确执行。DELETE Student WHERE Sno='20100001'不可以正确执行，因为SC表中的Sno属性值引用Student中的Sno属性值，子表还存在，且对应引用的记录也存在，所以父表中此记录不可删除。可以在删除子表引用记录后父表的此记录即可删除。UPDATE Course SET Cno = '10' WHERE Cname = '数据库系统原理'不可以正确执行，因为SC表中的Cno属性引用Course表中的Cno属性，Course表中的“数据库系统原理”对应记录的Cno已经被SC表引用，在SC表中此属性值存在的情况下，Course表的此Cno属性值不可更改不可删除。三、触发器的定义及使用1定义触发器（1）定义一个触发器，实现有关学分的完整性约束：当向SC表插入一行选课记录时，自动将该课程的学分累加到该学生的总学分中。CREATE TRIGGER tr_INSERT ON SC FOR INSERT AS-声明变量DECLARE sno char(8) DECLARE cridit intDECLARE cno char(4)-提取插入的数据SELECT sno=Sno,cno=Cno FROM inserted-提取学生的总学分SELECT cridit = Ccredit FROM SC join Course ON (SC.Cno = Course.Cno)WHERE SC.Cno = cno-更新总学分UPDATE Student SET Stotal = Stotal + criditWHERE Sno = snoGO（2）定义一个触发器，实现对SC表的操作登记：当用户向SC表插入或修改时，记录该操作到数据库中。l 创建日志登记表:CREATE TABLE LOG_TABLE(username char(10), -操作人员date datetime, -修改时间Sno char(8) , -学生学号Cno char(4) -课程号)l 创建日志登记触发器:CREATE TRIGGER tr_UPDATE ON SC FOR INSERT,UPDATE ASDECLARE sno char(8) DECLARE cno char(4)DECLARE new smallintSELECT sno=Sno,cno=Cno FROM insert