数据库原理-实验教学讲稿(邯院版)

邯郸学院实验教学讲稿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的速度增长。Createdatabasecompany1Onprimary(name=company_data,Filename=’c:\data\student.mdf’,Size=20,Maxsize=50,Filegrowth=25%)Logon(name=company_log,Filename=’c:\data\student.ldf’,Size=1,Maxsize=12,Filegrowth=1)2.修改数据库(1)将数据库COMPANY1的主数据文件的初始分配空间大小扩充到40MB.Alterdatabasecompany1Modifyfile(name=’c:\data\student.mdf’,Size=40)(2)将数据库COMPANY1改名为COMPANYExecsp_renamedb‘company1’,’company’3.创建表在名为COMPANY数据库中创建以下四个表：(1)部门表，表名DEPA列名数据类型描述DNODECIMAL(2)部门编号DNAMEVARCHAR(16)部门名称ADDRVARCHAR(20)部门地址Createtabledepa(dnoDECIMAL(2)notnull,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工作天数4.修改表结构1）修改部门表DEPA，添加一列部门电话，列名Telephone，数据类型VARCHAR(15)。AltertabledepaAddTelephoneVARCHAR(15)2）为项目表PROJ添加Begindate和Enddate列，数据类型为DATETIME。3）删除项目表PROJ中的Enddate列。AltertableprojDropcolumnEnddate5.删除表1）删除项目表PROJ。Droptableproj6、添加记录：1）向DEPA表添加14号部门，客户中心，地址为开发区紫光路2号。Insertintodepa(dno,dname,addr)values(14,’客户中心’,’开发区紫光路2号’)2）向DEPA表添加15号部门，技术支持部，地址未详。3）向DEPA表添加13号部门，财务部，地址未详。……4）向EMPL表添加1401号员工，张山，出生日期1977年9月1日，工资4050元，14号部门。5）向EMPL表添加1402号员工，何宜，生日不祥，工资不祥，15号部门。……6）向PROJ表添加103号项目，历史学院档案库管理系统。7）向PROJ表添加104号项目，ATM机管理系统。……8）向JOB表添加1401号员工，参与了104号项目35天。9）向JOB表添加1402号员工，参与了103号项目20天。……7、修改记录：1）把DEPA表中’客户中心’的地址改为’大连甘井子区红岭路’;Updatedepasetaddr=’大连甘井子区红岭路’wheredname=’客户中心’2）把DEPA表中15号部门的地址用沈阳东郊路120号填充。3）把EMPL表中何宜的生日、工资用1981年12月20日，3000元填充。4）把EMPL表中张山的部门号改为15号。5）把JOB表中参与104号项目的每人增加5天。6）把JOB表中参与103号项目的每人天数乘以系数0.8。8、删除记录：1）删除地址为空的部门。Deletefromdepawhereaddr=null2）删除JOB表中参与104号项目的员工。3）删除JOB表中天数低于25天的员工。4）删除生于1980年后，且工资低于4000的员工。5）删除属于14号部门的员工。6）删除15号部门的信息。9．使用对象资源管理器分离和附加数据库COMPANY实验2数据库的单表查询、分组查询与聚集函数的使用一、实验目的本实验的目的是使学生熟悉SQLServerManagementStudio的使用方法。加深对SQL语言的查询语句的理解。熟练掌握数据库的单表查询（包括投影、选择条件表达，数据排序，模糊查询等）,熟练掌握数据库的分组、统计、计算和集合的操作方法。二、实验时数2学时三、实验内容1.简单的SELECT查询1）查询雇员表Empl中所有员工的详细信息，并且列名用汉字表示。selectENOAS员工编号,ENAMEAS员工姓名,BIRTHDATEAS出生日期,SALARYAS工资,DNOAS部门编号fromEMPL2）查询项目表Proj中所有项目的详细信息，并且列名用汉字表示。selectPNOAS项目编号,PNAMEAS项目名称fromPROJ2.使用WHERE子句进行查询1）查询雇员表Empl中李强的员工编号。selectENOAS员工编号fromEMPLwhereename='李强'2）查询项目表Proj中项目编号为“101”的项目名称。selectPNAMEAS项目名称fromPROJwherePNO='101'3.使用DISTINCT关键字去掉重复行1）查询雇员表Empl中出现的所有部门编号，要求无重复。selectdistinctENOAS员工编号fromEMPL2）查询工作表Job中参与过项目开发的所有员工编号，要求无重复。selectdistinctENOAS员工编号fromJOB（为什么没有项目的不会被选中）4.使用ORDERBY子句对查询结果进行排序1）查询雇员表Empl中所有员工的详细信息，结果按员工姓名降序排列。selectENOAS员工编号,ENAMEAS员工姓名,BIRTHDATEAS出生日期,SALARYAS工资,DNOAS部门编号fromEMPLorderbyENAMEDESC2）查询工作表Job中的工作信息，结果按工作天数升序排列。selectdistinctENOAS员工编号,PNO,DAYSfromJOBORDERBYDAYSASC5.使用LIKE子句进行模糊查询1）查询所有姓王的员工的编号及姓名，并且以员工编号及员工姓名作为列名。selectENOAS员工编号,ENAMEAS姓名fromEMPLwhereENAMElike'王%'2）查询雇员表Empl中姓名以“伟”字结尾的员工信息。selectENOAS员工编号,ENAMEAS员工姓名,BIRTHDATEAS出生日期,SALARYAS工资,DNOAS部门编号fromEMPLwhereENAMElike'%伟'6.使用聚集函数MIN、MAX进行查询1）查询雇员表Empl中员工的最低工资，并且列名用“最低工资”表示。selectMIN(SALARY)as最低工资fromEMPL2）查询雇员表Empl中员工的最高工资，并且列名用“最高工资”表示。selectMAX(SALARY)as最低工资fromEMPL7.使用聚集函数COUNT进行查询1）查询雇员表Empl中所有员工的总人数，并且列名用“员工总数”表示。selectcount(ENAME)as员工总数fromEMPL2）查询14号部门的人数，并且列名用“客户中心部人数”表示。selectcount(DNO)as客户中心部人数fromEMPLWHEREDNO='14'8.使用分组子句GROUPBY与聚集函数MIN、MAX进行查询1）查询每个部门中年龄最大的员工，显示部门编号和出生日期。selectdistinctmax(BIRTHDATE)as年龄,DNOAS部门编号FROMEMPLgroupbyBIRTHDATE,DNO2）分别统计雇员表Empl中各部门员工的最高工资，并且列名分别用“部门编号”及“最高工资”表示。9.使用分组子句GROUPBY与聚集函数SUM、AVG进行查询1）分别统计雇员表Empl中各部门员工的工资总额，并且列名分别用“部门编号”及“各部门工资总额”表示。SELECTDNOAS部门编号,sum(SALARY)AS工资综合fromEMPLGROUPBYDNO2）分别统计雇员表Empl中各部门员工工资的平均数，并且列名分别用“部门编号”及“各部门工资平均数”表示。SELECTDNOAS部门编号,AVG(SALARY)AS平均工资fromEMPLGROUPBYDNO10.使用分组子句GROUPBY与聚集函数COUNT进行查询1）统计员工人数多于4人的部门信息，并且列名分别用“部门编号”及“员工人数”表示。selectDNOAS部门编号,count(ENAME)as部门人数fromemplgroupbyDNOhavingcount(ENAME)42）分别统计工作表Job中各项目的参与开发的员工人数，并且列名分别用“项目编号”及“员工人数”表示。selectPNOAS项目编号,count(ENO)as部门人数fromJOBgroupbyPN实验3数据库的多表连接和子查询一、实验目的本实验的目的是使学生加深对SQL和SQL语言的查询语句的理解。熟练掌握数据库的多表连接查询和子查询。二、实验时数2学时三、实验内容㈠多表连接查询：1.对两张数据表使用内连接进行查询1）查询姓名为“王一伟”的员工所在部门的名称，并且列名用汉字表示。selectdname部门名称fromdepa,emplwhereempl.ename='王一伟'andempl.dno=depa.dno2）查询编号为“1002”的员工姓名及该员工所在部门的名称，并且列名用汉字表示。selectename员工姓名,dname部门名称fromempl,depawhereempl.eno='1002'andempl.dno=depa.dno2.对多张数据表使用内连接进行查询1）查询姓名为“陈晨”的员工参与过的所有项目的名称，并且列名用汉字表示。selectempl.enameas姓名,proj.pnameas项目名称fromempl,job,projwhereempl.eno=job.enoandjob.pno=proj.pnoandempl.ename='陈晨'2）查询编号为“1202”的员工姓名及该员工参与过的所有项目的名称，并且列名用汉字表示。selectempl.enameas姓名,proj.pnameas项目名称fromempl,job,projwhereempl.eno=job.enoandjob.pno=proj.pnoandempl.eno='1402'3.对两张数据表使用左连接进行查询1）使用左连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该