太原理工大学数据库实验

实 验 报 告课程名称： 数据库系统原理 实验项目：认识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.学生基本信息表学号姓名性别年龄所在系班级李勇男20CS1001刘晨女19CS1001王敏女18MA1002张立男19IS1003刘洋女1001赵斌男19IS1005张明明男19CS1002表5.课程信息表课程号课程名先修课程号学分1数据库系统原理5642高等数学23管理信息系统144操作系统原理635数据结构746数据处理27C语言4表6.学生选课信息表学号课程号成绩19228538819028013（1）插入到Student表（2）插入到Course表（3）插入到SC表2,修改数据（1）将学生的年龄改为22岁。UPDATE student SET Sage = 22 WHERE Sno=;（2）将所有学生的年龄增加一岁。UPDATE Student SET Sage = Sage +1（3）填写赵斌同学的管理信息系统课程的成绩（4）将计算机科学系全体学生的成绩加5分（5）请自己完成如下操作a.将刘晨同学的2号课程成绩修改为80b.将“”同学的学号修改为“”3,删除数据将选课信息复制到一个临时表tmpSC中：在tmpSC中执行删除操作：（1）删除学号为的学生记录（2）删除学号学生的1号课程选课记录（3）删除临时表中学生的全部选课记录请自己完成该操作。（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 使用触发器创建一个触发器，实现如下的完整性约束：.王玉民程明王林总学分姓名学号709080成绩310642063106学分课程号学号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=成绩0 AND Grade=0 AND Grade0 AND Grade100)，不在此范围内不满足check约束所以不可以正确执行。INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(,张盛,男,1008)可以正确执行。INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES(,张盛,1008)不可以正确执行，性别Ssex属性被约束值必须为男或者女，即CHECK( Ssex in (男,女)，而此条记录中没有性别的值，不满足性别属性的check约束，所以不可以正确执行。6检查外键约束（1）执行下面的语句检查外键约束的作用INSERT INTO SC VALUES(,1,95)不可以正确执行，SC表中定义Sno与Cno为外键，分别引用Course表中的Sno与Cno，因为Course表中不存在学号为的记录，所以不满足外键约束，不可以正确执行。INSERT INTO SC VALUES(,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=可以正确执行。DELETE Student WHERE Sno=不可以正确执行，因为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 inserted INSERT INTO LOG_TABLE VALUES(CURRENT_USER,getdate(),Sno,Cno);GO（3）执行插入操作，触发触发器：INSERT INTO SC VALUES(,6,95)（4）验证触发器是否触发select * from studentselect * from LOG_TABLE五、讨论、心得平时应该多多练习这方面的知识,熟能生巧,把知识的基础打好,不问及时问老师和同学,要学就学好这门课程.学院名称软件学院专业班级软件1334实验成绩学生姓名 学号 实验日期2015课程名称数据库系统管理实验题目用户鉴别与数据控制一、实验目的和要求（1）了解SQL Serer数据库系统中数据访问控制的基本方法（2）了解使用SSMS如何给用户授权（3）熟练掌握常用GRANT和REVOKE进行权限控制（4）验证数据库系统的访问控制二、主要仪器设备计算机：HP-6470b windows7 64 位 运行环境： SQL Server 2008R2三、实验内容和原理（1）创建新的数据库用户（2）对上一实验建立的表进行权限设置（3）检查数据库用户的权限（4）撤销用户权限四、操作方法与实验步骤A、使用SSMS的图形界面创建用户并授权创建一个数据库用户：U1，授予他可以查询Student表的权限。1 使用SSMS的图形界面创建登录名在快捷菜单中选择“新建登录名”：进入“登录名”窗口，输入登录信息：按“确定”创建登录名。使用该名称可以登录数据库服务器。2 使用SSMS的图形界面创建数据库用户选择“学习”数据库，在快捷菜单中选择“新建登录名”：进入“数据库用户”窗口，输入用户名和登录名：注意：登录名是用户登录数据库系统的名称，用户名是该登录用户在指定数据库中的名称，两者可以相同。按“确定”创数据库用户。3使用SSMS的图形界面进行授权，设置用户对对象的访问权限展开“学习”数据库的“安全性”下的“用户”，选择“U1”。（）打开用户属性窗口： 在“选择对象类型”窗口中选择“表” 。（）在属性窗口的“安全对象”页中进行设置权限选择要访问的表Student，在下面的权限列表中点击“选择”权限的“授予”检查框。按“确定”授予“U1”可以查询Student表的权限。B、使用语句创建用户并授权在“学习”数据库中，使用SQL语句创建两个数据库用户，对数据库中的Student、SC、和Course表，分别对其授予不同的访问权限，如下表。表3.1 授予用户权限用户数据库对象权限查询插入修改删除U2StudentCourseSCU3StudentCourseSC1创建登录名用户要访问数据库，必须首先登录到服务器上。因此，首先需要为用户创建登录名。以系统管理员账户登录，进入数据库服务器，执行以下的操作。（1）分别创建两个登录名U2和U3：CREATE LOGIN U2 WITH password=1CREATE LOGIN U3 WITH password=1其中：“U2”、“U3”是用于登录数据库器的登录名，Password用于指定用户登录密码。（2）展开服务器的“安全性”下的“登录名”，检查登录名是否创建成功。2创建数据库用户用户登录服务器后，要进入某一数据库访问，需要为登录用户在该数据库中建立一个数据库用户名。对创建的两个登录名，在“学习”数据库分别为其创建两个数据库用户：（1）切换到“学习”数据库：USE 学习（2）创建登录名在TEST数据库中的用户：CREATE USER U2 FOR LOGIN U2CREATE USER U3 FOR LOGIN U3其中：第二个“U2”是登录名，第一个“U2”是该登录名在“学习”数据库的用户名。这两个名称也可以同名。（3）展开“学习”数据库的“安全性”下的“用户”，检查数据库用户是否创建成功。3. 授权在数据库中添加新用户后，新用户没用访问数据库中数据的权限，需要数据库管理人员给其授予相关的权限后，才能执行相应的操作。按照表3.1，授权如下：（1）授予U2权限GRANT SELECT ON Student TO U2（2）授予U3权限GRANT SELECT ON Student TO U3GRANT INSERT,UPDATE,DELETE ON Student TO U3GRANT SELECT ON Course TO U33.3 检查权限控制 分别以两个登录名登录数据库，执行一些对数据库的操作，检查用户否允许执行相关的操作。1检查U2的权限以用户“U2”登录，执行如下的操作，检查能否正确的运行：SELECT * FROM StudentINSERT INTO Student(Sno,Sname,Sclass) VALUES(,高志,1008)UPDATE Student Set Sag