数据库实验一到实验七邯院专版.docx
- 文档编号:9742693
- 上传时间:2023-02-06
- 格式:DOCX
- 页数:46
- 大小:145.17KB
数据库实验一到实验七邯院专版.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数据库的视图与索引定义
一、实验目的
本实验的目的是使学生掌握索引与视图的定义与维护操作
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 七邯院 专版