数据库的SQL实验一指导.docx
- 文档编号:11575619
- 上传时间:2023-03-19
- 格式:DOCX
- 页数:29
- 大小:2.83MB
数据库的SQL实验一指导.docx
《数据库的SQL实验一指导.docx》由会员分享,可在线阅读,更多相关《数据库的SQL实验一指导.docx(29页珍藏版)》请在冰豆网上搜索。
数据库的SQL实验一指导
实验一使用ManagementStudio来管理数据库和表
⏹实验要求:
1.熟悉SQLServer2005的集成管理器(ManagementStudio)的用户界面
2.用ManagementStudio创建数据库
3.用ManagementStudio或CREATE语句创建表,更改基本表的定义,增加列、删除列、修改列的类型
4.管理表的约束
5.创建和管理表的索引和关系图
6.掌握视图的创建和使用,加深对视图作用的理解
⏹实验内容:
1.熟悉SQLServer2005集成管理器的用户界面
2.用ManagementStudio或SQL语句创建数据库
1)创建数据库Students,Students数据库采用系统提供的默认设置。
2)删除Students数据库。
3)建立数据库bookdb。
数据库名称:
bookdb
数据库相关参数设置:
采用系统默认设置
4)查看数据库bookdb的相关属性,并填写下表:
项目
内容
数据库所有者
数据库名称
数据库逻辑文件名
数据文件物理存放位置
数据库初始大小
可用空间(数据文件最大值)
数据文件增长量
日志逻辑文件名
日志文件物理存放位置
日志文件初始大小
日志文件可用空间
日志文件增长量
3.分别在bookdb数据库中创建如下表:
⏹Book库存图书表
字段名
主键
允许空
字段类型
描述
Book_id
Y
N
Int
书号
Book_name
N
varchar(50)
书名
Price
N
float
价格
Publisher
N
varchar(26)
出版社
City
N
varchar(20)
城市
⏹Orderform客户订单表
字段名
主键
允许空
字段类型
自动编号列
描述
Order_id
Y
N
Int
IDENTITY(2009001,1)
订单号
Book_id
N
Int
书号
Book_number
N
Int
册数
Order_date
N
datetime
订购日期
Client_id
N
Int
客户号
⏹Authors图书作者表
字段名
主键
允许空
字段类型
描述
Author_id
Y
N
Int
作者号
Author_name
N
char(8)
作者名
Address
Y
varchar(50)
作者地址
Telphone
Y
varchar(15)
联系电话
⏹Clients客户信息表
字段名
主键
允许空
字段类型
描述
Client_id
Y
N
Int
客户号
Client_name
N
char(8)
客户名
Address
N
char(50)
客户地址
4.更新表的结构及表约束:
1)在Book库存图书表中添加如下字段(位置在Book_name和Price之间):
字段名:
Author_id
主键:
N
允许空:
N
字段类型:
int
描述:
作者号
2)在Book库存图书表中添加约束,要求Book_name取值不能重复,Price的取值在0~100之间,City默认值为“北京”。
3)在Book库存图书表中删除字段City
5.创建和管理表的索引
为Authors图书作者表创建按Author_name升序排列的聚簇索引ix_authors;为Orderform客户订单表创建按Order_id升序、Order_date降序排列的复合索引ix_Orderform,设填充因子为10。
6.创建关系图
在“book”、“orderform”、“authors”、“clients”表间的建立如下关联,并保存关系图名称为“book关系图”:
book表通过book_id字段和orderform表建立关联PK_orderform_book,其主键表为book,外键表为orderform;
book表通过author_id字段和authors表建立关联PK_book_authors,其主键表为authors,外键表为book;
clients表通过client_id字段和orderform表建立关联PK_orderform_clients,其主键表为clients,外键表为orderform;
“book关系图”如下图所示:
7.熟悉SQL查询分析器的用户界面,了解SQL模板功能
*以下操作数据库为SCMIS(可从磁盘上附加到SQLServer中)
8.创建基于多个基表的视图scView,这个视图由学生学号、姓名和他所选修的课程名及成绩构成。
9.将所有学生的学号和他的平均成绩定义为一个视图S_G。
10.创建信息系学生信息的视图IS_student,再修改为信息系所有女生的视图。
11.在视图S_G中查询平均成绩在80分以上的学生的学号和平均成绩。
⏹实验步骤:
一.启动SQLServer2005
1.通过菜单[MicrosoftSQLServer2005]→[SQLServerManagementStudio],打开集成管理器。
2.连接SQLServer服务。
系统弹出“连接到服务器”界面。
通过“服务器名称”栏,选择合适的SQL服务器;在“服务器类型”栏中,选择“数据库引擎”服务项。
完成上述选择后,点击“连接”按钮,启动SQL数据库引擎服务。
3.SQLServer引擎服务的启动可能需要一定的时间,当SQLServer服务启动后,就进入集成管理器界面:
逐层点击打开“对象资源管理器”窗口中的树状结构,一直到打开“数据库”层,并查看数据库下的对象。
二.查看样例数据库
1.从磁盘上附加样例数据库SCMIS(见步骤十二“数据库的分离”),查看SCMIS的属性
用鼠标选中数据库SCMIS,通过点击菜单[属性]项,选中“常规”选项卡。
2.查看数据库SCMIS的关系图及其属性。
3.查看SCMIS数据库中的Student表的结构和内容。
4.查看SCMIS数据库中的视图:
C_View和avg_grade。
5.查看SCMIS数据库中的存储过程:
Course_proc1。
三.创建数据库
利用企业管理器创建数据库Students和Bookdb:
操作如下:
选中已注册的服务器,用鼠标右键点击“数据库”,在弹出的快捷菜单中选取菜单[新建数据库…]。
弹出“新建数据库”页面,在“名称”文本框中输入数据库名Students,打开“数据文件”和“事务日志”选项卡进行参数设置,按[确定]完成。
注意观察,了解数据文件和日志文件的物理存放位置及文件名。
删除数据库:
用鼠标右键点击“数据库”,在弹出的快捷菜单中选取菜单[删除]。
四.创建表
1.在bookdb数据库中创建表:
操作如下:
打开左侧窗口中的数据库“bookdb”,用鼠标右键点击“表”,在弹出的快捷菜单中选取菜单[新建表],进入表设计器。
在弹出的窗口中依次输入表的列名、数据类型、长度、描述等。
选中要定义主键的列,然后单击“设置主键”(钥匙形状)按钮。
点击“钥匙”设置主键
2.修改表的结构:
在Book库存图书表中添加Author_id字段(位置在Book_name和Price之间):
操作如下:
打开左侧窗口中的数据库“bookdb”,用鼠标右键点击“Book表”,在弹出的快捷菜单中选取菜单[修改],弹出表设计器窗口。
用鼠标右键点击Price列,在弹出的快捷菜单中选取菜单[插入列],输入列的信息。
3.添加约束:
在Book库存图书表中添加约束,要求Price的取值在0~100之间。
操作如下:
用鼠标右键点击Price列,在弹出的快捷菜单中选取菜单[CHECK约束…];或直接单击工具栏上的“管理CHECK约束…”按钮。
点击“CHECK约束”选项卡,点击“添加”,在约束表达式中输入“price>0andprice<100”,点击“关闭”按钮退出。
五.创建和管理表的索引
为Authors图书作者表创建按Author_name升序排列的索引ix_authors:
操作如下:
打开左侧窗口中的数据库“bookdb”,用鼠标右键点击“Authors表”,在弹出的快捷菜单中选取[修改],打开表设计器。
选择工具栏上的[管理索引和键…],弹出管理索引对话框。
思考:
“索引/键”对话框中的PK_Authors是什么索引?
何时建立的?
单击“添加…”按钮,进入“编辑”对话框,输入索引名称ix_authors,再选择Author_name列,并设置索引的各种选项,按[确定]按钮完成。
为Orderform客户订单表创建按Order_id升序、Order_date降序排列的复合索引ix_Orderform,设填充因子为10。
思考:
能否添加聚集索引,为什么?
该如何修改?
六.创建关系图
在“book”、“orderform”、“authors”、“clients”表间的建立如下关联,并保存关系图名称为“book关系图”:
操作如下:
打开左侧窗口中的数据库“bookdb”,用鼠标右键点击“数据库关系图”,在弹出的快捷菜单中选取菜单[新建数据库关系图…],弹出“创建数据库关系图向导”,单击“下一步”。
在弹出的对话框中,选择要添加的表:
Book、Orderform、Authors、Clients表,单击“添加”按钮,进入“编辑关系图”窗口。
将Book的Book_id拖到order_form的Book_id列,在出现的对话框进行设置。
其余类似。
练习:
尝试删除book表,并观察此时book表能否删除?
七.表中记录的新增和修改
1.在“book”、“orderform”、“authors”、“clients”表中添加如下数据:
⏹Book库存图书表
book_id
book_name
author_id
price
publisher
1
3DSMAX3.0标准教程
1
38
人民邮电出版社
2
3DSMAX实例教程
1
45
人民邮电出版社
3
WindowsXP常见问题与技巧1000例详解
6
24
铁道出版社
4
OfficeXP入门与提高实用教程
6
33
铁道出版社
⏹Orderform客户订单表
order_id
book_id
book_number
order_date
client_id
1
1
10
2007-1-1
1
2
3
1
2007-2-28
2
3
4
100
2007-10-11
1
⏹Authors图书作者表
author_id
author_name
address
telphone
1
刘耀儒
2
王晓明
3
谭耀强
4
张星
⏹Clients客户信息表
client_id
client_name
address
1
SSPU
泉州
2
张三
厦门
3
李四
泉州
2.任意向book表添加图书的资料,使库存图书的种类达到10种。
操作如下:
打开左侧窗口中的数据库“bookdb”,用鼠标右键点击“book表”,在弹出的快捷菜单中选取菜单[打开表]子菜单中的[返回所有行,进入数据操作界面。
八.视图的创建和修改
1.视图的创建
1)创建基于多个基表的视图scView,这个视图由学生学号、姓名和他所选修的课程名及成绩构成。
方法一:
在ManagementStudio中,选择数据库SCMIS->选择“视图”->在其上单击右键->选择“新建视图…”,打开视图对话框。
单击工具栏上的“添加表”按钮或单击右键菜单中的“添加表”,打开添加表对话框选择student、course和sc表。
点击表的字段左边的方框,选择输出字段sno、sname、cname、score。
单击工具栏上的“运行”按钮,在数据结果区将显示视图中的数据。
“运行”按钮
“保存”按钮
单击工具栏上的“保存”按钮,在弹出的对话框中输入视图名scView,单击“保存”,完成视图的创建。
要显示的字段
方法二:
在SQLSERVER中查询分析器的编辑窗口中执行下列SQL语句:
CREATEVIEWsc_View(sno,sname,cname,score)
ASSELECTstudent.sno,student.sname,ame,sc.score
FROMstudent,course,sc
WHEREstudent.sno=sc.snoando=o
然后在工具栏上单击“执行查询”
按钮,或按F5键,即可获得相应的SQL语句的执行结果。
以下各小题仅以SQL命令方式来操作,注意在查询分析器工具栏的数据库列表中,选中数据库:
SCMIS。
2)创建信息系学生信息的视图:
CREATEVIEWIS_StudentAS
SELECTsno,sname,sageFROMstudentWHEREsdept='IS'
3)创建信息系选修了C1课程的学生的视图
CREATEVIEWIS_V1AS
SELECTstudent.sno,cno,score
FROMstudent,sc
WHEREstudent.sno=sc.snoandsdept='IS'andcno='C1'
思考:
如何在企业管理器中建立包含and,or条件的视图
4)建立信息系选修了C1课程且成绩在90分以上的学生的视图:
CREATEVIEWIS_V2AS
SELECT*FROMIS_V1WHEREscore>=90
5)创建一个反映学生出生年份的视图:
CREATEVIEWBT_S(sno,sname,出生年份)AS
SELECTsno,sname,year(getdate())-sageFROMstudent
6)将所有学生的学号和他的平均成绩定义为一个视图:
CREATEVIEWS_G(sno,avg_score)AS
SELECTsno,avg(score)FROMscGROUPBYsno
2.视图结构的修改:
将视图IS_student修改为信息系所有女生的视图:
ALTERVIEWIS_studentAS
SELECTsno,sname,sageFROMstudentWHEREssex='女'andsdept='IS'
比较:
ALTERVIEWIS_studentAS
SELECTsno,sname,sageFROMstudentWHEREssex='女'结果有何不同?
说明:
视图结构的修改的AS后的Select语句与创建视图的完全一致,引入结构修改的目的是为了避免与视图相关的数据库对象变化而产生的影响。
3.查询视图:
在视图S_G中查询平均成绩在80分以上的学生的学号和平均成绩:
SELECT*FROMS_GWHEREavg_score>=80
4.更新视图:
1)将信息系学生视图IS_Student中学号为“99002”的学生姓名改为“刘辰”:
UPDATEIS_StudentSETsname='刘辰'WHEREsno='99002'
比较:
UPDATEIS_StudentSETsname='刘诚'WHEREsno='99003'此语句会产生什么结果?
为什么?
试着创建包含withcheckoption子句的视图,并理解其作用。
2)向信息系学生视图IS_Student中插入一个新的学生记录,学号为99029,姓名为“赵新”,年龄为20岁:
INSERTINTOIS_StudentVALUES('99029','赵新',20)
试着查看student表的数据。
思考:
向视图sc_VIEW或视图S_G任意插入一个记录,会产生什么结果?
为什么?
九.数据库的分离
每次实验的内容将在后续的实验中使用,所以必须做好数据库的备份。
1.选择数据库分离功能:
用鼠标右键点击左侧窗口中的数据库“bookdb”标签,在弹出的快捷菜单中选取菜单[任务]→[分离…]。
2.系统弹出窗口“分离数据库-bookdb”,点击按钮“确定”。
此时,在窗口“分离数据库-bookdb”中,已经完成了相关的设定。
3.将分离的备份文件“bookdb_Data.MDF”和“bookdb_Data.LDF”拷贝到软盘上保存。
4.选择数据库附加功能:
用鼠标右键点击左侧窗口中的数据库标签,在弹出的快捷菜单中选取菜单[附加…]。
在出现的页面点击“添加”,选择要添加的数据文件,按确定完成。
课后练习:
(1)用SQL语句完成实验内容2-5的各种操作。
(2)创建所有学生的基本信息和选课信息的视图S_C_all。
(3)基于上述视图S_C_all查询选修C2课程的学生姓名及其所在系。
。
(4)创建一个视图CS,包含选课成绩及格的学生编号、所选课程号和该课程成绩。
(5)创建一个基于视图的视图S_CS,基于(4)中建立的视图CS,定义一个包含学生编号、学生所选课程数目和平均成绩的视图。
(6)创建带表达式的视图,由学生姓名、所选课程名、所有课程成绩再多5分这几个属性组成。
(7)插入元组('99004’,’C3’,59)到视图CS中。
(8)删除视图CS。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 SQL 实验 指导