数据库实验指导书

数据库原理 实验指导书 2008 年 1 月 实验一 数据库的创建与管理实验一 数据库的创建与管理 一 一 实验目的实验目的 掌握运用 sqlserver2000 中企业管理器和 Transact SQL 语句创建 删除 修改数据 库 二 二 实验要求实验要求 1 使用企业管理器创建 修改 删除一个学生数据库或物资数据库 2 编写 SQL 程序创建 修改 删除一个学生数据库或物资数据库 并给出相应的程 序注释 三 三 实验步骤实验步骤 1 确保安装了 SQLServer2000 2 确保数据库服务器启动 3 使用企业管理器创建 修改和删除数据库 4 编写 Transact SQL 语句创建 修改和删除数据库 四 四 参考操作和源代码参考操作和源代码 1 使用企业管理器直接创建数据库 1 打开企业管理器 在左窗格中双击要建立数据库的服务器节点 将该节点展 开 双击 数据库 节点 然后在右窗格中击空白区域 从弹出的快捷菜单中选择 新 建数据库 命令 会弹出如图 1 1 所示的对话框 图图 1 1 新建数据库新建数据库 2 在 名称 文本框中输入正确的数据库名称 然后单击 数据库文件 选项 卡 如图 1 2 所示 在 文件名 栏中可以输入文件名 SQL Server2000 在默认情况下 自动在用户输入的文件名后增加上 Data 字样 图图 1 2 数据文件数据文件 选项卡选项卡 3 在 文件组 栏中 输入文件所属的文件组名称 4 在 文件属性 选项组里 选中 文件自动增长 复选框 当数据文件的空 间不够用时 SQL Server2000 可以自动增加容量 SQL Server2000 提供了两种方式来实 现数据文件的自动增加 一种是以 按兆字节 的方式递增 一次增加 1MB 另外一 种方式是以 按百分比 的方式递增 一次递增原数据库文件容量的 10 5 单击 位置 栏中的按钮 弹出图 1 3 所示对话框 可以选择存放数据文件 的位置 图图 1 3 查找数据库文件查找数据库文件 对话对话 框框 6 单击 确定 按钮 返回 数据库属性 对话框 单击 事务日志 选项卡 输入日志文件的名称 位置 大小 在日志需要更多空间时可以在 文件属性 选项组 中设置日志文件自动增长和最大值选项 方法与设置数据文件类似 如图 1 4 所示 图图 1 4 事务日志事务日志 选项卡选项卡 7 双击数据库 即可查看 修改其属性 2 使用 Transact SQL 语句创建 查看 修改 删除数据库 1 打开查询分析器 2 在查询分析器里编写创建 查看 修改 删除数据库的 Transact SQL 语句 参考源代码 创建学生数据库 CREATE DATABASE student ON PRIMARY NAME student1 dat FILENAME e 学习 student1 dat mdf SIZE 10 MAXSIZE 50 FILEGROWTH 15 NAME student2 dat FILENAME e 学习 student2 dat Ndf SIZE 10 MAXSIZE 50 FILEGROWTH 15 FILEGROUP studentGroup1 NAME student1Fi1 dat FILENAME e 学习 student1Fi1 dat ndf SIZE 10 MAXSIZE 50 FILEGROWTH 5 NAME student1Fi2 dat FILENAME e 学习 student1Fi2 dat ndf SIZE 10 MAXSIZE 50 FILEGROWTH 5 FILEGROUP studentGroup2 NAME student2Fi1 dat FILENAME e 学习 student2Fi1 dat ndf SIZE 10 MAXSIZE 50 FILEGROWTH 5 NAME student2Fi2 dat FILENAME e 学习 student2Fi2 dat ndf SIZE 10 MAXSIZE 50 FILEGROWTH 5 LOG ON NAME student log FILENAME e 学习 studentlog ldf SIZE 5 MAXSIZE 25 FILEGROWTH 5 查看数据库属性 主要通过 sp dboption 来完成查看操作 语法如下 sp dboption dbname database optname option name optvalue option value 修改日志文件的最大值为 150MB USE student GO ALTER DATABASE student MODIFY FILE NAME student1 log MAXSIZE 150MB GO 修改日志文件的初始值为 20MB USE student GO ALTER DATABASE student MODIFY FILE NAME student1 log SIZE 20MB GO 查看数据库信息 验证数据库是否已经修改成功 sp helpdb student 更改数据库 语法 ALTER DATABASE 向指定的文件组里增加新的数据文件 ADD FILE 1 n TO FILEGROUP filegroup name 增加新的日志文件 ADD LOG FILE 1 n 删除某一个操作系统文件 REMOVE FILE logical file name 增加一个文件组 ADD FILEGROUP filegroup name 删除某一个文件组 REMOVE FILEGROUP FILEGROUP filegroup name 修改某操作系统文件属性 MODIFY FILE 修改某文件组的属性 其中包括 READONLY 只读 READWRITE 可读 可写 DEFAULT 设置该文件组为默认文件组 MODIFY FILEGROUP filegroup name filegroup property NAME logical file name FILENAME os file name SIZE size MAXSIZE max size UNLIMITED FILEGROWTH growth increment 删除数据库 USE student DROP DATABASE student 缩小数据库 语法 BDCC SHRINKDATABASE database name target percent arget percent 当数据库被缩小后还剩下的自由空间 NOTRUNCATE TRUNCATEONLY NOTRUNCATE 被释放的空间将不被操作系统回收 仍保持在数据库文件内 TRUNCATEONLY 将所有未使用的数据库空间释放并让操作系统回收 将 忽 略 arget percent 缩小某一个操作系统文件的长度 语法 DBCC SHRINKFILE file name target size 将文件缩小到指定长度 EMPTYFILE NOTRUNCATE TRUNCATEONLY EMPTYFILE 将指定文件上的数据全部转移到本文件组内其他文件上 以 后 的操作将不会再在该文件上增加数据 实验二 实验二 SQL 定义 删除 修改表实验定义 删除 修改表实验 一 实验目的一 实验目的 掌握运用 sqlserver 中 Transact SQL 语句创建 删除 修改表 二 实验要求二 实验要求 1 创建学生或物资数据库的表 2 给出相应的程序注释 三 实验步骤三 实验步骤 1 确保安装了 SQLServer2000 2 确保数据库服务器启动 3 使用 Transact SQL 语句创建 修改和删除表 4 编译并执行 Transact SQL 语句 四 参考源代码四 参考源代码 1 创建学生表 创建名为 Student 的学生表 CREATE TABLE Student 学号列 类型 固定长度为 5 的字符串 Sno CHAR 5 姓名 最大长度为 20 的可变字符串 Sname VARCHAR 20 unique 性别 固定长度为 2 的字符串 Ssex CHAR 2 年龄 整型 Sage INT 所在系别 最大长度为 15 的可变字符串 Sdept CHAR 15 设置学号列为主码 PRIMARY KEY Sno 2 创建课程表 创建名为 Course 的课程表 CREATE TABLE Course 课程号列 类型 固定长度为 3 的字符串 Cno CHAR 3 课程名 最大长度为 20 的可变字符串 Cname VARCHAR 20 先行课号 类型 固定长度为 3 的字符串 Cpno CHAR 3 学分 整型 Ccredit INT 设置课程号列为主码 Primary key Cno 设置先行课号为外码 参照本表 Foreign key Cpno REFERENCES Course 3 创建选课表 创建名为 SC 的选课表 CREATE TABLE SC 学号列 类型 固定长度为 5 的字符串 Sno CHAR 5 课程号列 类型 固定长度为 3 的字符串 Cno CHAR 3 成绩列 整型 Grade int 用户自定义完整性 成绩列必须 0 100 CHECK Grade 0 and Grade 2 AND Ccredit 3 ORDER BY Ccredit ASC 7 查询选修了课程的学生及其对应的平均成绩 SELECT Sno AVG Grade FROM SC GROUP BY Sno 实验五 多表连接查询实验五 多表连接查询 一 实验目的一 实验目的 1 了解多表连接查询的概念和方法 2 掌握复杂查询的使用方法 3 掌握多表连结的方法 4 掌握 SELECT 语句在多表连接查询中的应用 二 实验要求二 实验要求 1 编写 Transact SQL 语句实现多表连接查询 2 给出相应的程序注释 三 实验步骤三 实验步骤 1 确保安装了 SQLServer2000 2 确保数据库服务器启动 3 编写 Transact SQL 语句针对学生或物资数据库中的表进行各种连接查询 4 编译并执行 Transact SQL 语句 四 参考源代码四 参考源代码 1 查询每个学生及其选修课情况 SELECT Student SC FROM Student SC WHERE Student Sno SC Sno 消除上例结果的重复列 SELECT Student Sno Sname Ssex Sage Sdept Cno Grade FROM Student SC WHERE Student Sno SC Sno 2 以 Student 表为主体列出每个学生的基本情况及其选课情况 如果学生没有选课 只输 出 其基本情况 SELECT Student Sno Sname Ssex Sage Sdept Cno Grade FROM Student SC WHERE Student sno SC Sno 3 查询每门课的间接先行课 SELECT First Cno Second Cpno FROM Course First Course Second WHERE First Cpno Second Cno 4 查询选修 2 号课程且成绩在 80 分以上的所有学生信息 SELECT Student Sno Sname Grade FROM Student SC WHERE Student Sno SC Sno AND SC Grade 80 5 查询每个学生的学号 姓名 选修的课程名及成绩 SELECT Student Sno Sname Cname Grade FROM Student SC Course WHERE Student Sno SC Sno AND SC Cno Course Cno 6 查询姓陈的 全名为 3 个字的学生选课信息 SELECT SC FROM SC Student WHERE SC Sno Student Sno AND Sname LIKE 陈 实验六 嵌套和集合查询实验六 嵌套和集合查询 一 实验目的一 实验目的 1 了解嵌套查询及集合查询的概念和方法 2 掌握复杂嵌套查询的使用方法 3 掌握嵌套查询及集合查询的方法 4 掌握 SELECT 语句在嵌套查询及集合查询中的应用 二 实验要求二 实验要求 1 编写 Transact SQL 语句实现嵌套查询 集合查询 2 给出相应的程序注释 三 实验步骤三 实验步骤 1 确保安装了 SQLServer2000 2 确保数据库服务器启动 3 编写 Transact SQL 语句针对学生或物资数据库中的表进行嵌套查询和集合查 询 4 编译并执行 Transact SQL 语句 四 参考源代码四 参考源代码 1 查询所有软件学院 SW 学生的学号 选修课程号以及成绩 SELECT Sno Cno Grade FROM SC WHERE Sno IN SELECT Sno FROM Student WHERE Sdept SW 或 SELECT Sno Cno Grade FROM SC WHERE EXISTS SELECT Sno FROM Student WHERE Sdept SW 2 查询其他系中比信息系任意一个 其中某一个 学生年龄小的学生姓名和年龄 SELECT Sname Sage FROM Student WHERE Sage ANY SELECT Sage FROM Student WHERE Sdept IS AND Sdept IS 用集函数实现上查询 SELECT Sname Sage FROM Student WHERE Sage SELECT MAX Sage FROM Student WHERE Sdept IS AND Sdept IS 3 查询没有选修 1 号课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS SELECT FROM SC WHERE Sno Student Sno AND Cno 1 4 将软件学院和计算机系的学生学号和姓名合并 并按学号降序排列 SELECT Sno Sname FROM Student WHERE Sdept SW UNION SELECT State FROM Student WHERE Sdept CS ORDER BY Sdept 实验七 实验七 SQL 更新数据实验更新数据实验 一 实验目的一 实验目的 掌握运用 sqlserver 中 Transact SQL 语句更新数据 二 实验要求二 实验要求 1 向学生数据库或物资数据库的表中添加 更改 删除数据 2 给出相应的程序注释 三 实验步骤三 实验步骤 1 确保安装了 SQLServer2000 2 确保数据库服务器启动 3 使用 Transact SQL 语句添加表中数据 4 编译并执行 Transact SQL 语句 四 参考源代码四 参考源代码 1 插入三条新学生数据 INSERT INTO Student sno sname ssex sage sdept VALUES 03020 陈冬 男 18 SW INSERT INTO Student sno sname ssex sage sdept VALUES 03021 刘丽 女 20 SW INSERT INTO Student sno sname ssex sage sdept VALUES 03022 陈勇 男 19 SW INSERT INTO Student sno sname ssex sage sdept VALUES 03031 李铃 女 21 CS INSERT INTO Student sno sname ssex sage sdept VALUES 03032 吴晨 男 19 CS 2 插入三条新课程数据 INSERT INTO Course Cno Cname Ccredit VALUES 1 计算机导论 2 INSERT INTO Course VALUES 2 pascal 1 3 INSERT INTO Course VALUES 3 数据库原理 1 3 3 插入三条新选课记录 INSERT INTO SC VALUES 03020 1 88 INSERT INTO SC Sno Cno VALUES 03020 2 INSERT INTO SC Sno Cno VALUES 03021 1 4 所有计算机系 CS 的学生都选了 1 号课程 插入其选课信息 INSERT INTO SC Sno Cno SELECT Sno 1 FROM Student WHERE Sdept CS 5 修改 cno 为 3 的记录的课程名称为 数据库原理及应用 UPDATE course SET cname 数据库原理及应用 WHERE cno 3 6 从 course 表中删除所有学分 Ccredit 为 2 的记录 DELETE FROM course WHERE Ccredit 2 7 删除计算机科学系所有学生的选课记录 DELETE FROM SC WHERE Sno in SELECT Sno FROM Student WHERE Sdept CS 实验八 实验八 SQL 定义 删除 修改视图实验定义 删除 修改视图实验 一 实验目的一 实验目的 掌握运用 sqlserver 中 Transact SQL 语句创建 删除 修改视图 二 实验要求二 实验要求 1 按要求创建一些视图 通过视图更新数据 2 给出相应的程序注释 三 实验步骤三 实验步骤 1 确保安装了 SQLServer2000 2 确保数据库服务器启动 3 使用 Transact SQL 语句创建视图 通过视图更新数据 4 编译并执行 Transact SQL 语句 四 参考源代码四 参考源代码 1 创建计算机系学生的视图 并要求进行修改和插入操作时仍需保证该视图只有计算机系 的学生 CREATE VIEW CS Student AS SELECT Sno Sname Sage Sdept FROM Student WHERE Sdept CS 在修改和插入操作时 保证该视图只有计算机系的学生 WIT