数据库实验一到实验七邯院专版.docx
《数据库实验一到实验七邯院专版.docx》由会员分享，可在线阅读，更多相关《数据库实验一到实验七邯院专版.docx（46页珍藏版）》请在冰豆网上搜索。
数据库实验一到实验七邯院专版
邯郸学院
实验教学讲稿
2013～2014学年第一学期
分院（系、部）：
信息工程学院
教研室：
网络工程
课程名称：
数据库原理
授课班级：
网络工程2011级本科班
主讲教师：
李娜
职称：
讲师
使用教材：
《数据库系统概论》
邯郸学院制
实验1认识DBMS系统、数据库及数据库表的建立实验
一、实验目的
本实验的目的是使学生熟悉SQLServerManagementStudio的使用方
法，熟悉SQLSERVER的常用数据类型，加深对SQL语言的数据定义语句的理解。
熟练掌握数据库及数据库表的创建、修改和删除。
二、实验时数：
2学时
三、实验内容
分别使用SQL语言和对象资源管理器完成以下操作：
1.创建数据库
创建数据库名为COMPANY1，主数据文件的逻辑名称为COMPANY_DATA,操作系统文件的名称为D:
\MSSQL\DATA\COMPANY.MDF,大小为20MB,最大为50MB,以25%的速度增长。
日志文件的逻辑名称为COMPANY_LOG,操作系统文件的名称为D:
\MSSQL\DATA\COMPANY.LDF,大小为3MB,最大为10MB,以1MB的速度增长。
Createdatabasecompany1
Onprimary
（name=company1_data,
Filename=’D:
\MSSQL\DATA\COMPANY.MDF’,
Size=20mb,
Maxsize=50mb,
Filegrowth=25%）
Logon
（name=company_log,
Filename=’D:
\MSSQL\DATA\COMPANY.LDF’,
Size=3mb,
Maxsize=10mb,
Filegrowth=1mb）
2.修改数据库
（1）将数据库COMPANY1的主数据文件的初始分配空间大小扩充到40MB.
usecompany1
go
alterdatabasecompany1
modifyfile
（name='D:
\MSSQL\DATA\COMPANY.MDF',
size=40mb）
（2）将数据库COMPANY1改名为COMPANY
Execsp_renamedb’company1’,’company’
3.创建表
在名为COMPANY数据库中创建以下四个表：
（1）部门表，表名DEPA
列名数据类型描述
DNODECIMAL
（2）部门编号
DNAMEVARCHAR（16）部门名称
ADDRVARCHAR（20）部门地址
（2）雇员表，表名EMPL
列名数据类型描述
ENODECIMAL（4）员工编号
ENAMECHAR（8）员工姓名
BIRTHDATEDATETIME出生日期
SALARYDECIMAL（7,2）工资
DNODECIMAL
（2）所在部门编号
（3）项目表，表名PROJ
列名数据类型描述
PNODECIMAL（3）项目编号
PNAMEVARCHAR（24）项目名称
（4）工作表，表名JOB
列名数据类型描述
ENODECIMAL（4）员工编号
PNODECIMAL（3）项目编号
DAYSINT工作天数
usecompany
go
createtable
（列名+类型）
4.修改表结构
1）修改部门表DEPA，添加一列部门电话，列名Telephone，数据类型VARCHAR（15）。
usecompany
go
altertabledepa
addTelephonevarchar（15）null
go
2）为项目表PROJ添加Begindate和Enddate列，数据类型为DATETIME。
usecompany
go
altertableproj
addbegindatedatetimenull
Addenddatedatetimenull
go
3）删除项目表PROJ中的Enddate列。
usecompany
go
altertableproj
dropcolumnenddate
go
5.删除表
1）删除项目表PROJ。
droptableproj
6、添加记录：
1）向DEPA表添加14号部门，客户中心，地址为开发区紫光路2号。
usecompany
insertintodepa
values（'14','客户中心',‘开发区紫光路2号’，,null）
go
2）向DEPA表添加15号部门，技术支持部，地址未详。
usecompany
insertintodepa
values（'15',技术支持部',null,null）
go
3）向DEPA表添加13号部门，财务部，地址未详。
usecompany
insertintodepa
values（'13','财务部',null,null）
go
……
4）向EMPL表添加1401号员工，张山，出生日期1977年9月1日，工资4050元，14号部门。
usecompany
insertintoempl
values（'1401','张山','1977-9-1','4050','14'）
go
5）向EMPL表添加1402号员工，何宜，生日不祥，工资不祥，15号部门。
……
usecompany
insertintoempl
values（'1402','何宜',null,null,'15'）
go
6）向PROJ表添加103号项目，历史学院档案库管理系统。
usecompany
insertintoproj
values（'103','历史学院档案库管理系统'）
go
7）向PROJ表添加104号项目，ATM机管理系统。
usecompany
insertintoproj
values（'104','ATM机管理系统'）
go
8）向JOB表添加1401号员工，参与了104号项目35天。
usecompany
insertintojob
values（'1401','104',‘35'）
go
9）向JOB表添加1402号员工，参与了103号项目20天。
usecompany
insertintojob
values（'1402','103','20'）
go
7、修改记录：
1）把DEPA表中’客户中心’的地址改为’大连甘井子区红岭路’;
usecompany
updatedepa
setADDR='大连甘井子区红岭路'
whereDNO=14
2）把DEPA表中15号部门的地址用沈阳东郊路120号填充。
usecompany
updatedepa
setADDR=沈阳东郊路120号'
whereDNO=15
3）把EMPL表中何宜的生日、工资用1981年12月20日，3000元填充。
usecompany
updateempl
setBIRTHDATE='1981-12-20',SALARY='3000'
whereENAME='何宜'
GO
4）把EMPL表中张山的部门号改为15号。
usecompany
updateempl
setDNO='15'
whereENAME='张山'
GO
5）把JOB表中参与104号项目的每人增加5天。
usecompany
updatejob
setDAYS=DAYS+5
wherePNO='104'
GO
6）把JOB表中参与103号项目的每人天数乘以系数0.8。
usecompany
updatejob
setDAYS=DAYS*0.8
wherePNO='103'
GO
8、删除记录：
1）删除地址为空的部门。
deletefromdepawhereADDR=null
2）删除JOB表中参与104号项目的员工。
deletefromjobwherePNO='104'
3）删除JOB表中天数低于25天的员工。
deletefromjobwhereDAYS<25
4）删除生于1980年后，且工资低于4000的员工。
5）删除属于14号部门的员工。
deletefromemplwhereDNO=14
6）删除15号部门的信息
。
deletefromemplwhereDNO=15
9．使用对象资源管理器分离和附加数据库COMPANY
实验2数据库的单表查询、分组查询与聚集函数的使用
一、实验目的
本实验的目的是使学生熟悉SQLServerManagementStudio的使用方法。
加深对SQL语言的查询语句的理解。
熟练掌握数据库的单表查询（包括投影、选择条件表达，数据排序，模糊查询等）,熟练掌握数据库的分组、统计、计算和集合的操作方法。
二、实验时数2学时
三、实验内容
1.简单的SELECT查询
1）查询雇员表Empl中所有员工的详细信息，并且列名用汉字表示。
selectENOAS员工编号,ENAMEAS员工姓名,BIRTHDATEAS生日,SALARYAS薪资,DNOAS部门编号
fromempl
2）查询项目表Proj中所有项目的详细信息，并且列名用汉字表示。
selectPNAMEAS项目名称,PNOAS项目编号
fromproj
2.使用WHERE子句进行查询
1）查询雇员表Empl中李强的员工编号。
useCOMPANY
selectENO
fromEMPL
whereENAME='李强'
2）查询项目表Proj中项目编号为“101”的项目名称。
useCOMPANY
selectPNAME
fromPROJ
wherePNO='101'
3.使用DISTINCT关键字去掉重复行
1）查询雇员表Empl中出现的所有部门编号，要求无重复。
useCOMPANY
selectdistinctDNO
fromEMPL
2）查询工作表Job中参与过项目开发的所有员工编号，要求无重复。
useCOMPANY
selectdistinctENO
fromJOB
wherePNOisnotnull
4.使用ORDERBY子句对查询结果进行排序
1）查询雇员表Empl中所有员工的详细信息，结果按员工姓名降序排列。
useCOMPANY
selectENOAS员工编号,ENAMEAS员工姓名,BIRTHDATEAS生日,SALARYAS薪资
fromEMPL
orderbyENAMEdesc
2）查询工作表Job中的工作信息，结果按工作天数升序排列。
useCOMPANY
selectENOAS员工编号,PNOAS项目名称,DAYSas工作天数
fromJOB
orderbyDAYSASC
5.使用LIKE子句进行模糊查询
1）查询所有姓王的员工的编号及姓名，并且以员工编号及员工姓名作为列名。
useCOMPANY
selectENOAS员工编号,ENAMEAS员工姓名
fromEMPL
whereENAMElike'王_'
2）查询雇员表Empl中姓名以“伟”字结尾的员工信息。
useCOMPANY
selectENOAS员工编号,ENAMEAS员工姓名,BIRTHDATEAS生日,SALARYAS薪资
fromEMPL
whereENAMElike'%伟'
6.使用聚集函数MIN、MAX进行查询
1）查询雇员表Empl中员工的最低工资，并且列名用“最低工资”表示。
useCOMPANY
selectmin（SALARY）AS最低工资
fromEMPL
2）查询雇员表Empl中员工的最高工资，并且列名用“最高工资”表示。
useCOMPANY
selectmax（SALARY）AS最高工资
fromEMPL
7.使用聚集函数COUNT进行查询
1）查询雇员表Empl中所有员工的总人数，并且列名用“员工总数”表示。
useCOMPANY
selectcount（ENAME）AS员工总人数
fromEMPL
2）查询14号部门的人数，并且列名用“客户中心部人数”表示。
useCOMPANY
selectcount（DNO）AS客户中心部人数
fromEMPL
whereDNO=14
8.使用分组子句GROUPBY与聚集函数MIN、MAX进行查询
1）查询每个部门中年龄最大的员工，显示部门编号和出生日期。
useCOMPANY
selectmin（BIRTHDATE）as出生日期,DNOas部门编号
fromEMPL
groupbyDNO
2）分别统计雇员表Empl中各部门员工的最高工资，并且列名分别用“部门编号”及“最高工资”表示。
useCOMPANY
selectmax（SALARY）as最高工资,DNOas部门编号
fromEMPL
groupbyDNO
9.使用分组子句GROUPBY与聚集函数SUM、AVG进行查询
1）分别统计雇员表Empl中各部门员工的工资总额，并且列名分别用“部门编号”及“各部门工资总额”表示。
useCOMPANY
selectsum（SALARY）as各部门工资总额,DNOas部门编号
fromEMPL
groupbyDNO
2）分别统计雇员表Empl中各部门员工工资的平均数，并且列名分别用“部门编号”及“各部门工资平均数”表示。
useCOMPANY
selectavg（SALARY）as各部门工资平均数,DNOas部门编号
fromEMPL
groupbyDNO
10.使用分组子句GROUPBY与聚集函数COUNT进行查询
1）统计员工人数多于4人的部门信息，并且列名分别用“部门编号”及“员工人数”表示。
useCOMPANY
selectcount（ENAME）as员工人数,DNOas部门编号
fromEMPL
groupbyDNO
havingcount（ENAME）>4
2）分别统计工作表Job中各项目的参与开发的员工人数，并且列名分别用“项目编号”及“员工人数”表示。
useCOMPANY
selectPNOas项目编号,count（PNO）as员工人数
fromJOB
groupbyPNO
HavingPNOisnotnull
实验3数据库的多表连接和子查询
一、实验目的
本实验的目的是使学生加深对SQL和SQL语言的查询语句的理解。
熟练掌握数据库的多表连接查询和子查询。
二、实验时数2学时
三、实验内容
㈠多表连接查询：
1.对两张数据表使用内连接进行查询
1）查询姓名为“王一伟”的员工所在部门的名称，并且列名用汉字表示。
usecompany
selectDEPA.DNAMEas部门名称,EMPL.ENAMEas姓名
fromEMPLinnerjoinDEPA
onDEPA.DNO=EMPL.DNO
whereEMPL.ENAME='王一伟'
2）查询编号为“1002”的员工姓名及该员工所在部门的名称，并且列名用汉字表示。
usecompany
selectEMPL.ENOas部门编号,DEPA.DNAMEas部门名称,EMPL.ENAMEas姓名
fromEMPLinnerjoinDEPA
onDEPA.DNO=EMPL.DNO
whereEMPL.ENo='1202'
2.对多张数据表使用内连接进行查询
1）查询姓名为“陈晨”的员工参与过的所有项目的名称，并且列名用汉字表示。
usecompany
selectEMPL.ENAMEas姓名,EMPL.ENOas员工编号,JOB.PNOas项目名称
fromEMPLinnerjoinJOB
onEMPL.ENO=JOB.ENO
whereEMPL.ENAME='李晨'
2）查询编号为“1202”的员工姓名及该员工参与过的所有项目的名称，并且列名用汉字表示。
usecompany
selectEMPL.ENAMEas姓名,EMPL.ENOas员工编号,JOB.PNOas项目名称
fromEMPLinnerjoinJOB
onEMPL.ENO=JOB.ENO
whereEMPL.ENO='1202'
3.对两张数据表使用左连接进行查询
1）使用左连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。
usecompany
selectEMPL.ENOas员工编号,EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工资,Empl.DNOas部门编号,JOB.PNOas项目编号
fromEMPLleftouterjoinJOB
onEMPL.ENO=JOB.ENO
2）使用左连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。
usecompany
selectEMPL.ENOas员工编号,EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工资,Empl.DNOas部门编号,DEPA.DNAMEas部门名称
fromEMPLleftouterjoinDEPA
onEMPL.DNO=DEPA.DNO
4.对两张数据表使用右连接进行查询
1）使用右连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。
usecompany
selectEMPL.ENOas员工编号,EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工资,Empl.DNOas部门编号,JOB.PNOas项目编号
fromEMPLRIGHTouterjoinJOB
onEMPL.ENO=job.ENO
2）使用右连接查询所有员所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。
usecompany
selectEMPL.ENOas员工编号,EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工资,Empl.DNOas部门编号,DEPA.DNAMEAS部门名称
fromEMPLrightouterjoinDEPA
onEMPL.DNO=DEPA.DNO
㈡子查询：
1.多表单值子查询
1）查询编号为1001的员工的部门名。
usecompany
selectDNAME
fromDEPA
whereDNO=（selectDNO
fromEMPL
whereENO='1001'）
2）查询‘客户中心’部门的员工数。
usecompany
selectcount（DNO）as员工数
fromEMPL
whereDNO=（selectDNO
fromDEPA
whereDNAME='客户中心'）
3）查询陈晨所在的项目编号。
useCOMPANY
selectPNOas项目编号
fromJOB
whereENO=（selectENO
fromEMPL
whereENAME='李晨'）
2.多表多值子查询
1）查询1985年以后出生的员工在所在项目的工作天数。
useCOMPANY
selectDAYSas工作天数
fromJOB
whereENOin（selectENO
fromEMPL
whereBIRTHDATE>'1985'）
2）查询参与项目天数超过40天的员工姓名。
useCOMPANY
selectENAMEas员工姓名
fromEMPL
whereENOin（selectENO
fromJOB
whereDAYS>'40'）
3）查询工资低于2000的员工的所属部门。
useCOMPANY
SELECTDNAMEAS部门
fromDEPA
whereDNOin（selectDNO
fromEMPL
whereSALARY<'2000'）
3.多层嵌套子查询
1）查询‘客户中心'部门中，收入高于平均工资的员工。
selectempl.enameas工资高于平均工资的员工
fromempl
wheresalary>（selectavg（empl.salary）
fromempl
）
and
empl.dnoin（selectdepa.dno
fromdepa
wheredepa.dname='客户中心'）
2）查询“开发系统”项目的参与部门。
SELECTT1.DNAME
FROMPROJS1,JOBS2,EMPLS3,DEPAT1
WHERET1.DNO=S3.DNO
ANDS3.ENO=S2.ENO
ANDS2.PNO=S1.PNO
ANDS1.PNAME='开发系统'
3）查询'宣传'部门雇员参加的项目名称。
SELECTT1.PNAME
FROMPROJT1,JOBS1,EMPLS2,DEPAS3
WHERET1.PNO=S1.PNO
ANDS1.ENO=S2.ENO
ANDS2.DNO=S3.PNO
ANDS3.PNAME='宣传'
4．相关子查询
1）查询参与两个项目以上的员工姓名。
SELECTT1.ENO,T2.ENAME
FROM（
SELECTENO,COUNT（PNO）COUNT
FROMJOB
GROUPBYENO
HAVINGCOUNT>=2）T1,EMPLT2
WHERET1.ENO=T2.ENO
2）查询所有未参与项目的员工姓名。
SELECTT1.ENAME
FROMEMPLT1
WHERET1.ENONOTIN（SELECTENOFROMJOB）
实验4数据库的视图与索引定义
一、实验目的
本实验的目的是使学生掌握索引与视图的定义与维护操作
升级会员 