数据库原理与应用课程实验指导书SQL.docx
《数据库原理与应用课程实验指导书SQL.docx》由会员分享，可在线阅读，更多相关《数据库原理与应用课程实验指导书SQL.docx（54页珍藏版）》请在冰豆网上搜索。
数据库原理与应用课程实验指导书SQL
《数据库原理与应用》
课程实验指导书
前言
数据库技术是计算机学科中的一个重要分支，发展迅速、应用非常广泛，几乎涉及了所有应用领域。
例如，办公系统、生产管理、财务管理、人事管理、工业管理等，都广泛应用了数据库技术。
本实验指导书是《数据库原理与应用》课程的配套实验资料。
通过安排实验及布置的任务，让学生熟练掌握使用关系数据库管理系统SQLServer2005进行数据库及表的创建和管理、查询、Transact—SQL程序设计、各类约束的创建及使用、视图及索引的创建与管理、SQLServer的存储过程的创建和管理、SQLServer的触发器创建和管理、SQLServer的安全性管理、数据库的备份及恢复。
并能根据实际应用需求进行数据库设计和实现，提高学生的实际动手能力，为其今后在相关领域学习和工作打下较好的基础。
目录
实验一SQLServer2005安装和环境介绍iii
实验二使用向导创建和删除数据库x
实验三数据库的创建与管理xiii
实验四数据表的创建操作xvi
实验五数据表的更新操作xix
实验六单表查询xxi
实验七连接查询xxiv
实验八嵌套查询xxvi
实验九索引的创建和使用xxvii
实验十视图的创建和使用xxviii
实验十一游标的使用xxx
实验十二流控制语句xxxii
实验十三数据完整性的实现xxxiv
实验十四系统安全管理xxxv
实验十五数据库设计xxxvii
实验一SQLServer2005安装和环境介绍
一、实验目的与要求:
1.掌握SQLServer2005服务器的安装方法
2.了解SQLServer2005的环境
3.了解数据库及其对象
4.完成SQLSERVER2005的安装、启动、登录。
二、实验内容和步骤
（1）安装SQLServer2005
1.根据安装机器软硬件的要求，选择一个合适的版本，以下以开发版为例。
2.将SQLServer2005DVD插入DVD驱动器。
如果DVD驱动器的自动运行功能无法启动安装程序，请导航到DVD的根目录然后启动splash.hta。
3.在自动运行的对话框中，单击“运行SQLServer安装向导”。
4.在“最终用户许可防议”页上，阅读许可协议，再选中相应的复选框以接受许可条款和条件。
接受许可协议后即可激活“下一步”按钮。
若要继续，请单击“下一步”。
若要结束安装程序，请单击“取消”。
如图1.1:
图1.1
5.在“SQLServer组件更新”页上，安装程序将安装SQLServer2005的必需软件。
有关组件要求的详细信息，请单击该页底部的“帮助”按钮。
若要开始执行组件的更新，请单击“安装”，更新完成之后若要继续，请单击“完成”。
如图1.2。
图1.2
6.在SQLServer安装向导的“欢迎”页上，单击“下一步”以继续安装。
如图1.3
图1.3
7.在“系统配置检查（scc）”页上，将扫描安装计算机，以检查是否存在可能妨碍安装程序的条件。
如图1.4
图1.4
8.在“注册信息”页上的“姓名”和“公司”文本框中，输入相应的信息。
若要继续，请单击“下一步”。
如图1.5
图1.5
9.在“要安装的组件”页上，请选择要安装的组件。
选择各个组件组时，“要安装的组件”窗格中会显示相应的说明。
您可以选中任意一些复选框。
建议全选。
若要安装单个组件，请单击“高级”。
否则，请单击“下一步”继续。
如图1.6
如图1.6
10.在“实例名”页上，请为安装的软件选择默认实例或已命名的实例。
计算机上必须没有默认实例，才可以安装新的默认实例。
若要安装新的命名实例，请单击“命名实例”，然后在提供的空白处键入一个唯一的实例名。
如图1.7
图1.7
11.在“服务帐户”页上，为SQLServer服务帐户指定用户名、密码和域名。
您可以对所有服务使用一个帐户。
如图1.8
图1.8
12.在“身份验证模式”页上，选择要用于SQLServer安装的身份验证模式。
如果选择Windows身份验证，安装程序会创建一个sa帐户，该帐户在默认情况下是被禁用的。
选择“混合模式身份验证”时，请输入并确认系统管理员（sa）登录名。
建议选择混合模式，并输入安全的密码。
如图1.9
图1.9
13.如果选择ReportingServices作为要安装的功能，将显示“报表服务器安装选项”页。
使用单选按钮选择是否使用默认值配置报表服务器。
如果没有满足在默认配置中安装ReportingServices的要求，则必须选择“安装但不配置服务器”安装选项。
若要继续安装，请单击“下一步”。
如图1.10
图1.10
14.在“错误报告”页上，可以清除复选框以禁用错误报告。
有关错误报告功能的详细信息，请单击该页底部的“帮助”。
若要继续安装，请单击“下一步”。
如图1.11
图1.11
15.在“准备安装”页上，查看要安装的SQLServer功能和组件的摘要。
若要继续安装，请单击“安装”。
如图1.12
图1.12
16.在“安装进度”页上，可以在安装过程中监视安装进度。
若要在安装期间查看某个组件的口志文件，请单击“安装进度”页上的产品或状态名称。
如图1.13
图1.13
17.在“完成MicrosoftSQLServer安装向导”页上，可以通过单击此页上提供的链接查看安装摘要口志。
若要退出SQLServer安装向导，请单击“完成”。
如图1.14
图1.14
18.如果提示您重新启动计算机，请立即重新启动。
19.如果成功安装了SQLServer2005，则在开始菜单中添加了如下程序和相应的服务:
如图1.15
图1.15
（2）SQLServer的环境介绍
1.AnalysisServices
提供“部署向导”，为用户提供将某个AnalysisServices项目的输出部署到某个目标服务器的功能。
2.配置工具
其子菜单中提供的配置管理器“SQLServerConfigulationmanager”用于查看和配置SQLServer的服务。
如图1.16
图1.16
以下是SQLServer2005系统的7个服务。
如图1.17
图1.17
右击某个服务名称，可以查看该服务的属性，并且可以启动、停止、暂停和重新启动相应的服务。
也可以使用操作系统“我的电脑”一“管理”选项，在【计算机管理]窗口中查看和启动、停止、暂停和重新启动相应的服务。
3.文档和教程
提供了SQLServer2005的联机帮助和示例数据库概述。
4.性能工具
子菜单提供了“SQLServerProfiler”和“数据库引擎优化顾问”用户数据
库性能调试和优化工具。
5.SQLServerBusinessIntelligenceDevelopmentStudio
商务智能（BI）系统开发人员设计的集成开发环境，构建于VisualStudio2005技术之上，为商业智能系统开发人员提供了一个丰富、完整的专业开发平台，支持商业智能平台上的所有组件的调试、源代码控制以及脚本和代码的开发。
6.SQLServerManagementStudio
它将SQLServer早期版本中包含的企业管理器、查询分析器和分析管理器的功能组合到单一环境中，为不同层次的开发人员和管理员提供SQLServer访问能力。
实验二使用向导创建和删除数据库
一、实验目的和要求
1.熟悉SQLServer2005中SQLServerManagementStudio的环境
2.了解SQLServer2005数据库的逻辑结构和物理结构
3.掌握使用向导创建和删除数据库的方法
4.熟练使用企业管理器进行数据库的创建和删除操作
5.完成用向导建立和删除数据库的实验报告
二、实验内容和步骤
设有一学籍管理系统，其数据库名为“EDUC"，初始大小为10MB，最大为50MB，数据库自动增长，增长方式是按5%比例增长;口志文件初始为2MB，最大可增长到5MB，按1MB增长。
数据库的逻辑文件名“student_data",物理文件名为“student_data.mdf，存放路径为“E:
\sql_data"。
口志文件的逻辑文件名为“student_log"，物理文件名为“student_log.ldf"，存放路径为“E:
\sql-data"。
（1）使用向导创建上诉描述的数据库。
使用SQLServerManagementStudio（简称SSMS）创建数据库。
1.启动SSMS
在开始菜单单击：
所有程序一SQLServer2005——SQLServer——ManagementStudio。
如图2.1
图2.1
单击“连接”按钮，便可以进入【SQLServerManagementStudio】窗口。
如果身份验证选择的是“混合模式”，则要输入sa的密码。
如图2.2
图2.2
2.建立数据库
在“对象资源管理器”窗口，建立上述数据库EDUC。
在数据库节点上右击选择新建。
同时建立一个同样属性的数据库EDUC1。
如图2.3
图2.3
3.修改数据库名，数据库逻辑名，初始大小，增长方式等。
如图2.4——2.5
图2.4
图2.5
（2）使用向导删除上面建立的数据库。
用SSMS删除添加建立的数据库EDUC1。
实验三数据库的创建与管理
一、实验目的与要求
1.了解SQLServer2005数据库的逻辑结构和物理结构。
2.掌握使用SQL语句创建和删除数据库。
3.熟练使用查询分析器进行数据库的创建和删除操作。
4.完成用sql语句建立和删除数据库的实验报告。
二、实验内容和步骤
1.使用SQLServerManagementStudio（简称SSMS）创建数据库。
步骤1:
启动SSMS
单击开始菜单，所有程序——SQLServer2005——SQLServerManagementStudio。
步骤2:
选“视图”一“模板资源管理器”-Database-createdatabase，双击database。
如图3.1
图3.1
步骤3:
将代码复制到窗口，并把“”换成数据库名“userdb1"，然后单击“!
执行”，右键单击“对象资源管理器”，单击“刷新”。
如图3.2
图3.2
以下是创建数据库userdbl的SQL语句
createdatabaseuserdbl0n
（name='userdb4_data',/*数据文件的逻辑名称，注意不能与日志逻辑同名*/
filename='d:
\sql_data\userdb4.mdf'，/*物理名称，注意路径必须存在*/
size=5,/*数据初始长度为M*/
maxsize=10,/*最大长度为M*/
filegrowth=1）/*数据文件每次增长M*/
lobon
（name=userdb4_log,
filename=d:
\sql-data\userdb4.ldf',
size=2,
maxsize=5,
filegrowth=1）
go运行上述语句建立数据库userdb1.
2.用SQL语句删除步骤一建立的数据库userdb1。
步骤:
新建查询一输入“dropdatabaseuserdbl”一执行。
3.备份数据库
选择userdbl数据库，右击鼠标选择“所有任务|备份数据库”命令，在弹出对话框中输入备份名称，选择“备份到”栏目中的“添加”命令，输入具体的文件名为位置，默认情况备份文件存放在SQLServer的安装目录中如“C:
\ProgramFiles\MicrosoftSQLServer\MSSQL\BACKUP\”；当需要将备份文件存放到自定义文件夹中时，修改上述默认路径。
4.还原数据库
选择userdb1数据库，右击鼠标选择“所有任务|还原数据库”命令，在对话框中会列出已备份过的文件记录，输入相应的选项值按确定即可。
实验四数据表的创建操作
一、实验目的与要求
⏹练习使用企业管理器和SQL语句创建和管理数据库。
⏹熟练掌握使用企业管理器和SQL语句创建、修改和删除表。
二、实验内容和步骤
1、启动企业管理器，创建产品销售数据库CPXS：
数据文件初始大小为5MB，最大大小50MB，增长方式按10%比例增长；日志文件初始为2MB，最大可增长到10MB，按2MB增长；其余参数取默认值。
CREATEDATABASECPXS0N
（NAME='CPXS_DATA',/*数据文件的逻辑名称，注意不能与日志逻辑同名*/
FILENAME='D:
\SQL_DATA\CPXS.MDF'，/*物理名称，注意路径必须存在*/
SIZE=2,/*数据初始长度为M*/
MAXSIZE=10,/*最大长度为M*/
FILEGROWTH=10%）/*数据文件每次增长M*/
LOGON
（NAME=CPXS_LOG,
FILENAME=D:
\SQL-DATA\CPXS.LDF',
SIZE=5,
MAXSIZE=50,
FILEGROWTH=2）
2、用T-SQL语句将CPXS数据文件的增长方式改为按5MB增长。
ALTERDATABASECPXS
MODIFYFILE
（
NAME=CPXS_Data,
FILEGROWTH=5MB
）
3、CPXS数据库包含如下三个表：
CP（产品编号，产品名称，价格，库存量）
XSS（客户编号，客户名称，地区，负责人，电话）
CPXSB（产品编号，客户编号，销售日期，数量，销售额）
三个表结构如图4.1～图4.3所示，请写出创建以上三个表的T-SQL语句并在查询分析器中运行。
图4.1CP表结构
图4.2XSS表结构
4.3CPXSB表结构
CREATETABLECP（
产品编号char（6）PK,
产品名称char（30）,
价格float（8）,
库存量int）;
CREATETABLEXSS（
客户编号char（6）PK,
客户名称char（30）,
地区char（10）,
负责人char（8）,
电话float（12））;
CREATETABLECPXSB（
产品编号char（6）,
客户编号char（6）,
销售日期datetime（8）,
负责人char（8）,
数量int,
销售额float（8）,
PrimaryKey（产品编号,客户编号,销售日期）,
ForeignKey（产品编号）referencesCP（产品编号）,
ForeignKey（客户编号）referencesXSS（客户编号））;
4、修改CPXSB表:
删除“负责人”列；
AltertableCPXSBDropcolumn负责人;
添加新列“单价float”；
AltertableCPXSBadd单价float;
修改“单价float”为“单价int”
AltertableCPXSBaltercolumn单价int;
5、删除CP表。
DroptableCP;
实验五数据表的更新操作
一、实验目的与要求
⏹熟练掌握使用SQL语句实现插入、修改和删除表中数据。
⏹熟悉这些更新语句的语法和用法
二、实验内容和步骤
1、在企业管理器中输入如图5.1～图5.3的CP表、XSS表和CPXSB表的样本数据。
图5.1CP表的样本数据
图5.2XSS表的样本数据
图5.3CPXSB表的样本数据
2、用SQL语句向CP表插入如下记录：
200001
柜式空调
3000
200
200002
微波炉
1000
100
200003
抽油烟机
1200
50
INSERTINTOCP
Values（'200001','柜式空调',3000,200）；
INSERTINTOCP
Values（'200002','微波炉','1000','100'）；
INSERTINTOCP
Values（'200003','抽油烟机',1200,50）;
2、用SQL语句向CP表中增加“产品简列”列，varchar（50），允许为NULL。
ALTERTABLECPADD产品简列varchar（50）;
3、将CP表中每种商品的价格打8折。
UPDATECP
SET价格=价格*0.8；
4、将CP表中价格打9折后小于1500的商品删除。
DELETEFROMCP
WHERE价格*0.9<=1500;
实验六单表查询
一、实验目的与要求
⏹牢记SELECT语句的基本语法格式；
⏹熟练掌握使用SQL语句进行单表查询，注意条件语句的书写。
难点掌握GROUPBY语句和集合函数的搭配使用；
二、实验内容和步骤
对CPXS数据库，完成如下单表查询：
1、简单查询
⏹查询各种产品的产品编号、产品名称和价格。
SELECT产品编号,产品名称,价格FROMCP;
⏹查询地区在“南京”的客户编号和客户名称，结果中各列的标题分别指定为：
Customerid和Customername。
SELECT客户编号as'Customerid',客户名称as'Customername'
FROMXSS
WHERE地区='南京';
⏹
UseCPXS
GO
SELECT*,'评价'=
CASE
WHEN价格<=1000THEN'廉价产品'
WHEN价格<=2000THEN'一般产品'
WHEN价格<=3000THEN'昂贵产品'
ELSE'很昂贵产品'
END
FROMCP
查询CP表中各种产品的产品编号、产品名称和价格，对其价格按以下规则进行转换；若价格小于1000，替换为“廉价产品”，若价格在1000-2000之间，替换为“一般产品”，若价格在大于2000小于3000，替换为“昂贵产品”，若价格大于3000，替换为“很昂贵产品”，列标题更改为“评价”。
⏹求各产品编号、名称和产品总值。
SELECT产品编号,产品名称,价格*库存量as'产品总值'
FROMCP;
⏹查询至少购买了一种产品的客户编号（不重复）。
SELECTdistinct客户编号
FROMCPXSB
WHERE数量!
=0;
⏹查询价格在1000-2000的产品信息。
SELECT*
FROMCP
WHERE价格BETWEEN1000AND2000;
⏹
SELECT*
FROMCP
WHERE产品名称LIKE'冰箱%';
查询产品名称含有”冰箱_A”的产品情况。
2、使用分组和集函数
先将CPXSB表数据修改如下图6.1所示：
图6.1
再完成如下查询：
计算所有产品总价格。
SELECTSUM（数量*销售额）
FROMCPXSB；
求各种产品2004年3月18日销售额。
SELECT销售额
FROMCPXSB
WHERE销售日期={^2/18/2004}
GROUPBY商品编号；
求购买二种以上产品的客户编号。
SELECT客户编号
FROMCPXSB
GROUPBY客户编号
HAVINGCOUNT（商品编号）>2
实验七连接查询
一、实验目的与要求
●熟练掌握使用SQL语句进行连接查询。
二、实验内容和步骤
对CPXS数据库，完成如下连接查询：
1、查询在2004年3月18日有销售的产品名称（不允许重复）。
SQL标准语句：
Select产品名称
FromCPinnerjoinCPXSB
OnCP.产品编号=CPXSB.产品编号
Where销售日期={^03/18/04};
T-SQL扩展语句：
Select产品名称
FromCP,CPXSB
WhereCP.产品编号=CPXSB.产品编号and销售日期={^03/18/04};
2、查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。
SQL标准语句：
Select产品名称，数量
FromCPinnerjoinCPXSBinnerjoinXSS
OnCP.产品编号=CPXSB.产品编号andXSS.客户编号=CPXSB.客户编号
Where客户名称="家电市场"and销售日期={^03/18/04};
T-SQL扩展语句：
Select产品名称，数量
FromCP,CPXSB,XSS
WhereCP.产品编号=CPXSB.产品编号andXSS.客户编号=CPXSB.客户编号and客户名称="家电市场"and销售日期={^03/18/04};
3、查询洗衣机在2004年3月18号的销售额。
Select销售额
FromCP,CPXSB
WhereCP.产品编号=CPXSB.产品编号and产品名称="洗衣机"
and销售日期={^03/18/04};
4、查询购买了产品“100002”的客户的名称、地区、负责人和电话。
Select客户名称,地区,负责人,电话
FromXSS,CPXSB
WhereXSS.客户编号=CPXSB.客户编号and产品编号="100002";
实验八嵌套查询
一、实验目的与要求
●熟练掌握IN子查询
●熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）
二、实验内容和步骤
对CPXS数据库，完成如下嵌套查询：
1、查询在2004年3月18日没有销售的产品名称（不允许重复）。
用IN子查询：
Select产品名称
FromCP
Where产品编号notin（Select产品编号
FromCPXSB
Where销售日期={^03/18/04}）;
2、查询名称为“家电市场”的客户在2004年3月18日购买的产品名称。
用IN子查询：
Select产品名称
FromCP
Where产品编号in（Select产品编号
FromCPXSB
Where销售日期={^03/18/04}and客户编号=（Select客户编号
FromXSS
Where客户名称="家电市场"））;
3、查询销售量大于所有2004年3月18日销售的各产品销售数量的产品编号。
用ALL谓词：
Select产品编号
FromCPXSB
Where数量>all（Select数量
FromCPXSB
Where销售日期={^03/18/04}）;
4、查询购买了所有产品的客户的名称。
S
升级会员 