数据库实验指导SQLServerWord文档格式.docx
- 文档编号:22177466
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:35
- 大小:1.24MB
数据库实验指导SQLServerWord文档格式.docx
《数据库实验指导SQLServerWord文档格式.docx》由会员分享,可在线阅读,更多相关《数据库实验指导SQLServerWord文档格式.docx(35页珍藏版)》请在冰豆网上搜索。
1)启动SQLServer2005:
依次单击开始->
所有程序->
SQLServer2005->
SQLServerManagementStudio启动SQLServer2005数据库管理系统
2)登录数据库服务器:
点击“连接到服务器“对话框中的连接按钮连接到SQLServer2005数据库服务器。
3)创建数据库Student
在SQLServer2005数据库管理系统的左边栏“对象资源管理器”中右击数据库对象,在弹出的快捷菜单中单击“新建数据库”命令。
在弹出的“新建数据库”对话框右侧的数据库名称中输入数据库名称“Student”,然后单击确定。
4)在Student数据库中新建表”Student”
单击SQLServer2005数据库管理系统的左侧的“对象资源管理器”栏中的”刷新”
按钮,以显示出新建的数据库“Student”。
依次展开左侧栏对象资源管理器中的“数据库”->
“Student”,并右击Student数据库中的表项目,在弹出的快捷菜单中单击“新建表”命令。
在右侧在工作区中输入“Student”表的信息,该表具有如下列:
列名
数据类型
完整性约束
Sno
CHAR(5)
主码
Sname
CHAR(10)
非空
Ssex
BIT
无
Sage
INT
Sdept
CHAR(15)
单击文件菜单中的保存命令保存该表,并取名为“Student”。
方法二、使用SQL语句创建数据库和表
1)单击工具栏中的“新建查询”按钮,并在弹出的“连接到服务器”对话框中单击“连接”按钮,新建一个SQL脚本。
2)在右侧的SQL脚本输入框中输入如下SQL代码:
CREATEDATABASESTUDENT--创建STUDENT数据库
GO
USESTUDENT--转到STUDENT数据库
CREATETABLESTUDENT--在STUDENT数据库中创建表STUDENT
(
SnoCHAR(5)PRIMARYKEY,
SnameCHAR(10)NOTNULL,
SsexBIT,
SageINT,
SdeptCHAR(15)
)
单击工具栏中的
执行按钮,运行SQL语句,完成数据库及表的创建。
四、实验任务
1.用两种方法建立一个新数据库HRM(也可任意取名)。
2.在数据库HRM中,建立如下所示的三个表:
Employee表
长度
是否允许为空
说明
EmployeeID
Char
6
否
员工编号,主键
Name
10
姓名
Birthday
Datetime
8
出生日期
Sex
Bit
1
性别
Address
20
是
地址
Zip
邮编
PhoneNumber
12
电话号码
EmailAddress
30
电子邮件地址
DepartmentID
3
员工部门号,外键
Departments表
员工部门号,主键
DepartmentName
部门名
Note
Text
16
备注
Salary表
员工编号,外键
Income
Float
收入
OutCome
支出
3.对三张表输入数据(可任意输入,但注意要符合完整性要求,否则会出错,在本实验中,外键设置可先不做要求)
五、实验报告要求
1、要求给出三个表创建的SQL语句,要求建表时给出主外键约束、唯一约束、取空值约束、用户自定义的约束等。
(注:
在实验报告上只需写出其中一个表的SQL语句即可)
2、举例说明如何操作会违背完整性(实体完整性、参照完整性)。
2.2SQL数据查询语句
●了解SQLServer2005查询编辑器的启动,熟悉如何在SQLServer2005查询编辑器查询记录。
●掌握SELECT语句的基本语法和查询条件表示方法
●掌握GROUPBY和ORDERBY子句的作用和使用方法
●掌握连接查询和子查询的使用方法
1、单击工具栏中的“新建查询”按钮,并在弹出的“连接到服务器”对话框中单击“连接”按钮,新建一个SQL脚本。
2、在SQL脚本中写数据查询语句执行;
从表中查询数据:
在查询分析器的查询窗口中输入SQL语句,如图所示。
点击
按钮,执行该SQL语句,在查询窗口下部出现一个输出窗口,如图所示。
三、实验任务
1.对上节建立的表输入数据:
Departments表:
财务部
2
研发部
人力资源部
1001
李勇
78-3-12
河南
475001
1002
王敏
80-11-2
475002
1003
刘晨
78-6-22
475003
2001
张立
78-8-1
475004
2002
刘毅
82-1-23
475005
2003
张玫
81-3-15
475006
3001
徐静
76-8-12
475007
3002
赵军
79-2-19
475008
3600
1500
3300
1000
3700
1200
4000
1600
3800
1800
4200
2000
4100
2、练习下面简单的查询语句:
a)查询每个雇员的所有信息
b)查询每个雇员的地址和电话
c)查询EmployeeID为000001的雇员的地址和电话。
d)查询女雇员地址和电话,并用AS子句将结果中各列的标题分别指定为“地址”和“电话”。
e)计算每个雇员的实际收入。
f)找出所有姓王的雇员的部门号。
思考:
找出所有地址中含有“中山”的雇员的号码和部门号。
(WhereAddresslike‘%中山%’)
3、练习多表连接查询和嵌套查询:
a)查询每个雇员的情况及工资情况(工资=Income-Outcome)
b)查询财务部工资在2200元以上的雇员姓名及工资情况
c)查询研发部在1966年以前出生的雇员姓名及其工资详情
d)查询人力资源部雇员的最高和最低工资
e)将各雇员的情况按工资由低到高排列
f)求各部门的雇员数
g)找出所有在财务部和人力资源部工作的雇员的编号
h)统计人力资源部工资在2500以上雇员的人数
i)求财务部雇员的总人数
j)求财务部雇员的平均工资
k)查找比所有财务部的雇员工资都高的雇员的姓名
l)查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名
m)查找在财务部工作的雇员的情况
四、实验报告要求
1、写出及上述查询任务相对应的SQL查询语句(注:
在实验报告上写出2-d、2-e、2-f及3中所有练习的SQL语句)
2、并记录在实验过程中遇到的问题、解决办法及心得体会。
2.3视图的定义和操作
一、实验目的:
理解视图的概念,掌握视图的使用方法。
二、预计实验时间:
上机一次
三、实验过程
1.对应HRM数据库,参照前面实验中完成的查询,按如下要求自行设计视图:
1)基于单个表按投影操作定义视图。
举例:
定义一个视图用以查看所有员工的编号、姓名和出生日期。
2)基于单个表按选择操作定义视图。
定义一个满足sex=’true’的员工的所有信息的视图。
3)基于单个表按选择和投影操作定义视图。
定义一个视图用以查看部门号码为‘2’的所有员工的姓名、电话和邮件地址。
4)基于多个表根据连接操作定义视图。
定义一个视图用以查看所有员工的姓名、部门名及工资。
5)基于多个表根据嵌套查询定义视图。
定义一个比所有财务部的雇员工资都高的雇员的信息的视图
6)定义含有虚字段(即基本表中原本不存在的字段)的视图。
定义一个视图用以查看所有雇员的编号、姓名、年龄。
2.分别在定义的视图上进行查询、插入、更新和删除操作,分情况(查询、更新)讨论哪些操作可以成功完成,哪些不能成功完成,并分析原因。
四、实验报告要求:
1、在实验报告中要给出上述任务的视图定义语句;
实验报告上写出4、5、6的SQL语句)
2、分情况讨论哪些操作可以成功完成,哪些不能成功完成。
查询操作:
举出一个进行查询操作的例子。
更新操作:
更新操作包含插入、更新、删除,针对不能成功完成更新操作的情况举出两个例子,并分析原因。
2.4SQL数据更新语句
●加深对SQL数据更新(插入、修改及删除)语句的基本语法格式的掌握
●掌握单个元组及多个元组的插入、修改及删除操作的实现过程
●加深对更新操作时数据库中数据一致性问题的了解
●加深对约束条件在数据更新操作执行中的作用问题的了解
方法一:
在SSMS中插入、删除及修改数据,
方法二:
在查询编辑器中写SQL插入、删除及修改数据
设有一个SPJ数据库,包括S,P,J,SPJ四个关系模式:
S(SNO,SNAME,STATUS,CITY)
P(PNO,PNAME,COLOR,WEIGHT)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商(STATUS)、供应商所在城市(CITY)组成;
零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)
组成;
工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成;
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,表示某供应商供应某种零件给某工程项目的数量为QTY。
试完成以下操作:
1.在S表中插入元组“s6,华誉,40,广州”
2.在J表中插入元组“j8,传感器厂”
3.对每一个供应商,求他为各种工程供应零件的总数量,并将此结果存入数据库
4.将P表中PNO值为p6的元组的color属性值改为绿,weight属性值改为60
5.将SPJ表中前4个元组的qty属性值统一修改为300
6.将S表中city属性名含有“京”或“津”的相应status属性值增加100
7.将供应商s2为“一汽”工程项目所供应的零件数量修改为2000
8.将全部红色零件的颜色修改为浅红色
9.由s5供给j4的零件p6改为由s3供应
10.在SPJ表中新增一名为SDATE的属性列,对该表中的每一元组在SDATE属性列上填上实验当时的日期和时间
11.删除所在城市为“广州”的供应商记录
12.删除所有零件名称中第一个字为“螺”字的零件记录,并在供应情况表中删除相应的记录
13.删除s3和s4两供应商为“三建”工程供应“螺母”或“螺丝刀”零件的相应供应情况数据信息
1、写出及上述任务相对应的SQL更新语句(实验报告上写出3、5、10的SQL语句)
实验3数据完整性和安全性管理
3.1数据完整性管理
●掌握域完整性的实现方法。
●掌握实体完整性的实现方法。
●掌握参照完整性的方法。
数据库的完整性设置。
三、实验步骤
●可视化界面的操作方法:
实体完整性
1.将student表的“sno”字段设为主键:
在表设计界面中,单击左边的行选定块,选定“sno”字段,单击工具按钮
设置主键。
如图1所示:
图1
1.将“sc”表的“sno”和“cno”设置为主键:
在表设计界面中,单击并按住Ctrl键拖动左边的行选定块,选定sno和cno字段,单击工具按钮
如图2所示:
图2
域完整性
3.将“ssex”字段设置为只能取“男”,“女”两值:
在表设计界面,点击鼠标右键——CHECK约束,——添加约束,添加CK_student_sex名称,然后在约束表达式框中输入“ssexin('
男'
'
女'
)”。
如图3所示。
图3
参照完整性
4.将“student”表和“sc”表中的“sno”字段设为参照:
打开“sc”表的设计界面,点击工具栏按钮
在弹出的属性(properties)对话框中点击“新建”按钮,在“主键表(Primarykeytable)”下拉框中选择“student”表,在其下的字段选择框中选择“sno”,在“外键表(Foreignkeytable)”下拉框中选择“sc”表,在其下的字段选择框中选择“sno”,单击关闭即可。
见图4。
图4
●命令方式操作方法:
1.将“student”表的“sno”字段设为主键:
当“student”表已存在则执行:
altertablestudentaddconstraintpk_snoprimarykey(sno)
当“student”表不存在则执行:
Createtablestudent(snoCHAR(5)primarykey,
snameCHAR(10)NOTNULL,
ssexCHAR
(2),
sageint,
sdeptCHAR(4))
注:
可用命令“droptablestudent”删除“student”表
2.添加一身份证号字段,设置其惟一性.(注:
操作前应删除表中的所有记录)
Altertablestudentaddidchar(18)unique(id)
3.将“sc”表的“sno”和“cno”设置为主键:
当“sc”表已存在则执行:
altertablescaddconstraintPK_SnoCnoprimarykey(sno,cno)
当“sc”表不存在则执行:
Createtablesc(snoCHAR(5),
cnoCHAR
(2),
gradeINTNULL,
constraintPK_SnoCnoprimarykey(sno,cno))
4.将“ssex”字段设置为只能取“男”,“女”两值:
altertablestudentaddconstraintCK_Sexcheck(ssexin('
'
))
当“student”表不存在则执行:
snameCHAR(10),
ssexCHAR
(2)check(ssexin('
)),
sageint,sdeptCHAR(4))
5.设置学号字段只能输入数字:
altertablestudentaddconstraintCK_Sno_Formatcheck(snolike'
[0-9][0-9][0-9][0-9][0-9]'
6.设置身份证号的输入格式:
altertablestudentaddconstraintCK_ID_Formatcheck((idlike'
[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_'
)OR(idlike'
[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'
7.设置18位身份证号的第7位到第10位为合法的年份(1900-2050)
altertablestudentaddconstraintCK_ID_Format2check(len(id)=18and((convert(smallint,substring(id,7,4))>
=1900)and(convert(smallint,substring(id,7,4))<
=2050)))
6.设置男生的年龄必须大于22,女生的年龄必须大于20.
AltertablestudentaddconstraintCK_agecheck(ssex='
andsage>
=22orssex='
=20)
参照完整性
9.将“student”表和“sc”表中的“sno”字段设为参照:
altertablescaddconstraintFP_snoforeignkey(sno)referencesstudent(sno)
Createtablesc(snoCHAR(5)constraintFP_sno
foreignkeyreferencesstudent(sno),
cnoCHAR
(2),gradeINTNULL,
constraintPK_SnoCnoprimarykey(sno,cno))
●完整性验证
1.实体完整性:
在“student”表数据浏览可视化界面中输入学号相同的两条记录将会出现错误如图5所示:
图5
或者在命令窗口输入下面两条命令也会出现错误提示:
insertintostudentvalues('
95003'
张三'
24,'
CS'
42222919901012903X'
95001'
李四'
21,'
422229199510129031'
图6
下面的语句用来验证“sc”表中的实体完整性:
insertintoscvalues('
95002'
'
10'
65)
90)
图7
2.域完整性:
使用下面的语句验证“ssex”字段的域完整性:
95009'
张匀'
大'
20,'
‘42222919901012904X’)
3.参照完整性:
使用下面的语句“验证”sc表中的“sno”字段的域完整性(假设student表中没有学号为“95998”的学生记录):
98998'
98)
四、实验任务:
1、建立课程的实体完整性,和课程号cno的参照完整性;
2、对HRM数据库,练习建立三个表的主外键约束、唯一约束、取空值约束、用户自定义的约束(参考HRM数据库表定义图中说明);
3、建立salary表的Income字段限定在0-9999之间。
1、写出及上述任务相对应的SQL语句
3.2数据库安全性管理
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 指导 SQLServer