数据库实验一到实验七(邯院专版)

邯 郸 学 院 实验教学讲稿实验教学讲稿 2013 2014 学年学年 第一学期第一学期 分院 系 部 信息工程学院 教 研 室 网络工程 课 程 名 称 数据库原理 授 课 班 级 网络工程 2011 级本科班 主 讲 教 师 李 娜 职 称 讲师 使 用 教 材 数据库系统概论 邯邯郸郸学学院院制制 实验实验 1 认识认识 DBMS 系统 数据库及数据库表的建立实验系统 数据库及数据库表的建立实验 一 一 实验目的实验目的 本实验的目的是使学生熟悉 SQL Server Management Studio 的使用 方 法 熟悉 SQL SERVER 的常用数据类型 加深对 SQL 语言的数据定 义语句的理解 熟练掌握数据库及数据库表的创建 修改和删除 二 二 实验时数 实验时数 2 学时学时 三 三 实验内容实验内容 分别使用SQL 语言和对象资源管理器完成以下操作 1 1 创建数据库创建数据库 创建数据库名为 COMPANY1 主数据文件的逻辑名称为 COMPANY DATA 操作 系统文件的名称为 D MSSQL DATA COMPANY MDF 大小为 20MB 最大为 50MB 以 25 的速度增长 日志文件的逻辑名称为 COMPANY LOG 操作系统文件的名称为 D MSSQL DATA COMPANY LDF 大小为 3MB 最大为 10MB 以 1MB 的速度增长 Create database company1 On primary name company1 data Filename D MSSQL DATA COMPANY MDF Size 20mb Maxsize 50mb Filegrowth 25 Log on name company log Filename D MSSQL DATA COMPANY LDF Size 3mb Maxsize 10mb Filegrowth 1mb 2 2 修改数据库修改数据库 1 将数据库 COMPANY1 的主数据文件的初始分配空间大小扩充到 40MB use company1 go alter database company1 modify file name D MSSQL DATA COMPANY MDF size 40mb 2 将数据库 COMPANY1 改名为 COMPANY Exec sp renamedb company1 company 3 3 创建表创建表 在名为 COMPANY 数据库中创建以下四个表 1 部门表 表名 DEPA 列名数据类型描述 DNODECIMAL 2 部门编号 DNAMEVARCHAR 16 部门名称 ADDRVARCHAR 20 部门地址 2 雇员表 表名 EMPL 列名数据类型描述 ENODECIMAL 4 员工编号 ENAMECHAR 8 员工姓名 BIRTHDATE DATETIME出生日期 SALARYDECIMAL 7 2 工资 DNODECIMAL 2 所在部门编号 3 项目表 表名 PROJ 列名数据类型描述 PNODECIMAL 3 项目编号 PNAME VARCHAR 24 项目名称 4 工作表 表名 JOB 列名数据类型描述 ENODECIMAL 4 员工编号 PNODECIMAL 3 项目编号 DAYSINT工作天数 use company go create table 列名 类型 4 4 修改表结构修改表结构 1 修改部门表 DEPA 添加一列部门电话 列名 Telephone 数据类型 VARCHAR 15 use company go alter table depa add Telephone varchar 15 null go 2 为项目表 PROJ 添加 Begindate 和 Enddate 列 数据类型为 DATETIME use company go alter table proj add begindate datetime null Add enddate datetime null go 3 删除项目表 PROJ 中的 Enddate 列 use company go alter table proj drop column enddate go 5 5 删除表删除表 1 删除项目表 PROJ drop table proj 6 6 添加记录 添加记录 1 向 DEPA 表添加 14 号部门 客户中心 地址为开发区紫光路 2 号 use company insert into depa values 14 客户中心 开发区紫光路2号 null go 2 向 DEPA 表添加 15 号部门 技术支持部 地址未详 use company insert into depa values 15 技术支持部 null null go 3 向 DEPA 表添加 13 号部门 财务部 地址未详 use company insert into depa values 13 财务部 null null go 4 向 EMPL 表添加 1401 号员工 张山 出生日期 1977 年 9 月 1 日 工资 4050 元 14 号部门 use company insert into empl values 1401 张山 1977 9 1 4050 14 go 5 向 EMPL 表添加 1402 号员工 何宜 生日不祥 工资不祥 15 号部门 use company insert into empl values 1402 何宜 null null 15 go 6 向 PROJ 表添加 103 号项目 历史学院档案库管理系统 use company insert into proj values 103 历史学院档案库管理系统 go 7 向 PROJ 表添加 104 号项目 ATM 机管理系统 use company insert into proj values 104 ATM机管理系统 go 8 向 JOB 表添加 1401 号员工 参与了 104 号项目 35 天 use company insert into job values 1401 104 35 go 9 向 JOB 表添加 1402 号员工 参与了 103 号项目 20 天 use company insert into job values 1402 103 20 go 7 7 修改记录 修改记录 1 把 DEPA 表中 客户中心 的地址改为 大连甘井子区红岭路 use company update depa set ADDR 大连甘井子区红岭路 where DNO 14 2 把 DEPA 表中 15 号部门的地址用沈阳东郊路 120 号填充 use company update depa set ADDR 沈阳东郊路120号 where DNO 15 3 把 EMPL 表中何宜的生日 工资用 1981 年 12 月 20 日 3000 元填充 use company update empl set BIRTHDATE 1981 12 20 SALARY 3000 where ENAME 何宜 GO 4 把 EMPL 表中张山的部门号改为 15 号 use company update empl set DNO 15 where ENAME 张山 GO 5 把 JOB 表中参与 104 号项目的每人增加 5 天 use company update job set DAYS DAYS 5 where PNO 104 GO 6 把 JOB 表中参与 103 号项目的每人天数乘以系数 0 8 use company update job set DAYS DAYS 0 8 where PNO 103 GO 8 8 删除记录 删除记录 1 删除地址为空的部门 delete from depa where ADDR null 2 删除 JOB 表中参与 104 号项目的员工 delete from job where PNO 104 3 删除 JOB 表中天数低于 25 天的员工 delete from job where DAYS4 2 分别统计工作表 Job 中各项目的参与开发的员工人数 并且列名分别用 项 目编号 及 员工人数 表示 use COMPANY select PNO as 项目编号 count PNO as 员工人数 from JOB group by PNO Having PNO is not null 实验实验 3 数据库的多表连接和子查询数据库的多表连接和子查询 一 一 实验目的实验目的 本实验的目的是使学生加深对 SQL 和 SQL 语言的查询语句的理解 熟练 掌握数据库的多表连接查询和子查询 二 二 实验时数实验时数 2 学时学时 三 三 实验内容实验内容 多表连接查询 多表连接查询 1 对两张数据表使用内连接进行查询 1 查询姓名为 王一伟 的员工所在部门的名称 并且列名用汉字表示 use company select DEPA DNAME as 部门名称 EMPL ENAME as 姓名 from EMPL inner join DEPA on DEPA DNO EMPL DNO where EMPL ENAME 王一伟 2 查询编号为 1002 的员工姓名及该员工所在部门的名称 并且列名用汉字表示 use company select EMPL ENO as 部门编号 DEPA DNAME as 部门名称 EMPL ENAME as 姓名 from EMPL inner join DEPA on DEPA DNO EMPL DNO where EMPL ENo 1202 2 对多张数据表使用内连接进行查询 1 查询姓名为 陈晨 的员工参与过的所有项目的名称 并且列名用汉字表示 use company select EMPL ENAME as 姓名 EMPL ENO as 员工编号 JOB PNO as 项目名称 from EMPL inner join JOB on EMPL ENO JOB ENO where EMPL ENAME 李晨 2 查询编号为 1202 的员工姓名及该员工参与过的所有项目的名称 并且列 名用汉字表示 use company select EMPL ENAME as 姓名 EMPL ENO as 员工编号 JOB PNO as 项目名称 from EMPL inner join JOB on EMPL ENO JOB ENO where EMPL ENO 1202 3 对两张数据表使用左连接进行查询 1 使用左连接查询所有员工的基本信息及参与项目开发情况 如果某员工参与 过项目开发 则列出该员工所参与的所有项目的编号 否则该项以空值表示 use company select EMPL ENO as 员工编号 EMPL ENAME as 姓名 EMPL BIRTHDATE as 出生日期 EMPL SALARY as 工资 Empl DNO as 部门编号 JOB PNO as 项目编号 from EMPL left outer join JOB on EMPL ENO JOB ENO 2 使用左连接查询所有员工的基本信息及所在部门名称 如果某员工尚未分配 到任何部门 则该员工的部门名称项以空值表示 use company select EMPL ENO as 员工编号 EMPL ENAME as 姓名 EMPL BIRTHDATE as 出生日期 EMPL SALARY as 工资 Empl DNO as 部门编号 DEPA DNAME as 部门名称 from EMPL left outer join DEPA on EMPL DNO DEPA DNO 4 对两张数据表使用右连接进行查询 1 使用右连接查询所有员工的基本信息及参与项目开发情况 如果某员工参与 过项目开发 则列出该员工所参与的所有项目的编号 否则该项以空值表示 use company select EMPL ENO as 员工编号 EMPL ENAME as 姓名 EMPL BIRTHDATE as 出生日期 EMPL SALARY as 工资 Empl DNO as 部门编号 JOB PNO as 项目编号 from EMPL RIGHT outer join JOB on EMPL ENO job ENO 2 使用右连接查询所有员所在部门名称 如果某员工尚未分配到任何部门 则 该员工的部门名称项以空值表示 use company select EMPL ENO as 员工编号 EMPL ENAME as 姓名 EMPL BIRTHDATE as 出生日期 EMPL SALARY as 工资 Empl DNO as 部门编号 DEPA DNAME AS 部门名称 from EMPL right outer join DEPA on EMPL DNO DEPA DNO 子查询 子查询 1 多表单值子查询 1 查询编号为 1001 的员工的部门名 use company select DNAME from DEPA where DNO select DNO from EMPL where ENO 1001 2 查询 客户中心 部门的员工数 use company select count DNO as 员工数 from EMPL where DNO select DNO from DEPA where DNAME 客户中心 3 查询陈晨所在的项目编号 use COMPANY select PNO as 项目编号 from JOB where ENO select ENO from EMPL where ENAME 李晨 2 多表多值子查询 1 查询 1985 年以后出生的员工在所在项目的工作天数 use COMPANY select DAYS as 工作天数 from JOB where ENO in select ENO from EMPL where BIRTHDATE 1985 2 查询参与项目天数超过 40 天的员工姓名 use COMPANY select ENAME as 员工姓名 from EMPL where ENO in select ENO from JOB where DAYS 40 3 查询工资低于 2000 的员工的所属部门 use COMPANY SELECT DNAME AS 部门 from DEPA where DNO in select DNO from EMPL where SALARY select avg empl salary from empl and empl dno in select depa dno from depa where depa dname 客户中心 2 查询 开发系统 项目的参与部门 SELECT T1 DNAME FROM PROJ S1 JOB S2 EMPL S3 DEPA T1 WHERE T1 DNO S3 DNO AND S3 ENO S2 ENO AND S2 PNO S1 PNO AND S1 PNAME 开发系统 3 查询 宣传 部门雇员参加的项目名称 SELECT T1 PNAME FROM PROJ T1 JOB S1 EMPL S2 DEPA S3 WHERE T1 PNO S1 PNO AND S1 ENO S2 ENO AND S2 DNO S3 PNO AND S3 PNAME 宣传 4 相关子查询 1 查询参与两个项目以上的员工姓名 SELECT T1 ENO T2 ENAME FROM SELECT ENO COUNT PNO COUNT FROM JOB GROUP BY ENO HAVING COUNT 2 T1 EMPL T2 WHERE T1 ENO T2 ENO 2 查询所有未参与项目的员工姓名 SELECT T1 ENAME FROM EMPL T1 WHERE T1 ENO NOT IN SELECT ENO FROM JOB 实验实验 4 数据库的视图与索引定义数据库的视图与索引定义 一 一 实验目的实验目的 本实验的目的是使学生掌握索引与视图的定义与维护操作 加深对索引与 视图在关系数据库中的作用的理解 二 二 实验时数实验时数 2 学时学时 相关知识相关知识 建立索引是加快查询速度的有效手段 用户可以根据应用环境的需要 在 基本表上建立一个或多个索引 以提供多个存取路径 加快查找速度 视图是根据子模式建立的虚拟表 一个视图可以由一个表构造 也可以由 多个表构造 查看和修改视图就如同对表的操作一样 非常容易 三 三 实验内容实验内容 索引索引 1 创建索引 1 为 PROJ 表的 PNAME 列创建非聚集索引 Pindex use COMPANY go create nonclustered index Pindex on PROJ PNAME go 2 为 DEPA 表的 DNAME 降序 ADDR 两列创建唯一非聚集索引 Dindex use COMPANY go create unique nonclustered index Dindex on DEPA DNAME desc ADDR go 3 在PROJ表的 PNAME 列上创建唯一非聚集索引 Pindex 若该索引已存在则 删除后重建 use COMPANY go create unique nonclustered index Pindex on PROJ PNAME go 2 查看索引 1 查看 DEPA 表的索引信息 use COMPANY go exec sp helpindex DEPA 2 查看数据库 COMPANY 索引使用的空间信息 use COMPANY go exec sp spaceused 3 索引更名 1 将 DEPA 表的索引 Dindex 名更改为 Dindex1 Use COMPANY Go Sp rename DEPA Dindex Dindex1 go 4 删除索引 1 删除 DEPA 表的索引 Dindex1 use COMPANY go drop index DEPA Dindex1 视图视图 1 创建视图 1 创建 14 号部门员工的视图 EMPL 14 ENO ENAME SALARY CREATE VIEW EMPL 14 ENO ENAME SALARY AS SELECT EN0 ENAME SALARY FROM EMPL WHERE DN0 14 2 创建包含项目名与员工名及工作天数的视图 Prjemp CREATE VIEW Prjemp AS SELECT PROJ PNAME EMPL ENAME J0B DAYS FROM PROJ J0B EMPL WHERE EMPL EN0 J0B EN0 AND J0B PN0 PROJ PNO 3 在视图 EMPL 14 上创建工资在 3000 以上的员工的视图 EMPL 14H3 CREATE VIEW EMPL 14H3 AS SELECT ENO ENAME SALARY FROM EMPL 14 WHERE SALARY 3000 4 创建每个部门的平均工资的视图 E AVG DNO SAVG CREATE VIEW E AVG DNO SAVG AS SELECT DN0 AVG SALARY FROM EMPL GROUP BY DN0 2 查询视图 1 根据视图 Prjemp 查询做 设计系统 项目的所有员工及工作天数 SELECT ENAME DAYS FROM Prjemp WHERE PNAME N 设计系统 2 根据视图 E AVG 查询 客户中心 的平均工资 SELECT DNO SAVG FROM E AVG WHERE DNO SELECT DN0 FROM DEPA WHERE DNAME N 客户中心 3 更新视图 1 在视图 EMPL 14 中将 1042 号员工的姓名改为 李立春 UPDATE EMPL 14 SET ENAME N 李立春 WHERE ENO 1402 2 向视图 EMPL 14 中插入一条新记录 1050 赵英 3500 INSERT INTO EMPL EN0 ENAME SALARY DN0 VALUES 1050 N 赵英 3500 14 3 删除视图 EMPL 14 中 1042 号员工的记录 DELETE FROM EMPL 14 WHERE ENO 1402 4 删除视图 1 删除视图 Prjemp DROP VIEW Prjemp 实验实验 5 5 数据库的安全性控制数据库的安全性控制 一 一 实验目的实验目的 本实验的目的是通过实验使学生加深对数据安全性的理解 并掌握 SQL Server 中有关用户 角色及操作权限的管理方法 二 二 实验时数实验时数 2 学时学时 三 三 实验内容实验内容 分别使用 SQL 语言和对象资源管理器完成以下操作 1 建立数据库登录帐户 S1 和 D1 密码均为 1234 登录帐户 S1 默认数据库 分别为 COMPANY 其它保持默认值 EXEC sp addlogin S1 1234 COMPANY EXEC sp addlogin D1 1234 2 将 S1 映射为 COMPANY 数据库的同名数据库用户 USE COMPANY GO CREATE USER S1 FOR LOGIN S1 GO 3 将 D1 映射为 COMPANY 数据库的数据库用户 数据库用户名为 S2 USE COMPANY GO CREATE USER S2 FOR LOGIN D1 GO 3 对数据库用户授予权限 1 授予 S1 对 EMPL 表插入数据的权限 并验证该权限 方法 通过登录帐户 S1 登录 SQL Server 然后插入一条数据来验证该权限 GRANT INSERT ON EMPL TO S1 2 授予S2创建表的权限 并验证该权限 方法 通过登录帐户D1登录SQL Server 然后创建 张表来验证该权限 GRANT create table TO S2 3 收回 S1 对 EMPL 表插入数据的权限 并验证该权限 方法 通过登录帐户 S1 登录 SQL Server 然后插入一条数据来验证该权限 REVOKE INSERT on EMPL from S1 4 创建用户角色 1 在 COMPANY 数据库中创建角色 MYROLE 授予该角色查询和修改 EMPL 表的权 限 CREATE ROLE MYROLE GRANT SELECT UPDATE ON EMPL TO MYROLE 2 将 S1 添加为角色 MYROLE 的成员 并验证 s1 是否能够查询 EMPL 表 exec sp addrolemember MYROLE S1 3 将 S2 添加为角色 MYROLE 的成员 并验证 s2 是否能够修改 EMPL 表 exec sp addrolemember MYROLE S2 5 收回数据库用户权限 1 采用用户角色方法收回 S1 和 S2 修改 EMPL 表的权限 并验证 S1 是否还能修 改 EMPL 表 REVOKE UPDATE ON EMPL FROM MYROLE 2 拒绝 S2 查询 EMPL 表的权限 并验证 S2 是否还能查询 EMPL 表 REVOKE SELECT ON EMPL FROM S2 实验实验 6 数据完整性数据完整性 一 实验目的一 实验目的 本实验的目的是通过实验使学生加深对数据完整性的理解 掌握数据完整 性的约束条件 学会创建和使用触发器 二 二 实验时数实验时数 2 学时学时 相关知识相关知识 SQL Server 实现数据完整性的具体方法有 4 种 约束 缺省 规则和触发 器 触发器是实施复杂完整性的特殊类型的存储类型 触发器不需要专门语句 调用 当对它所保护数据进行修改时自动激活 以防止对数据进行不正确 未 授权或不一致的修改 三 三 实验内容实验内容 1 创建包含非空约束字段的表 创建一个员工联系信息表 Contact 一共有 3 个字段 雇员号 Eno 数据类型为 4 位长定长字符串 电话 Tel 数据类型为 8 位长定长字符串 地址 Addr 数据类型为 20 位变长字符串 要求雇员号和电话不能为空 use COMPANY create table Mail Eno char 4 not null Mail char 8 not null Addr varchar 20 2 创建包含唯一约束字段的表 创建员工邮箱表 Mail 一共有 2 个字段 雇员号 Eno 数据类型为 4 位长定长字符串 邮箱地址 Mail 数据类型为 20 位变长字符串 要求雇员号非空 邮箱地址唯一 use COMPANY create table Mail Eno char 4 not null Mail varchar 20 alter table Mail alter table Mail add constraint Em unique Mail 3 创建包含主码约束字段的表 创建一个员工卡信息表 Card 一共有 3 个字段 雇员号 Eno 数据类型为 4 位长定长字符串 卡号 Cno 数据类型为 8 位长定长字符串 开卡日期 Startday 日期格式 要求雇员号和卡号联合做主键 use COMPANY create table Card Eno char 4 not null Cno char 8 not null Startday datetime alter table Card add constraint Ca primary key Eno Cno 4 创建包含检查约束字段的表 完成百分比用小数表示且数值小于 1 创建包含检查约束字段的表 完成百分比用小数表示且数值小于 1 创建包含检查约束字段的表 3 个字段 项目号 Pno 数据类型为 4 位长定长字符串 完成百分比用小数表示且数值小于 1 创建包含检查约束字段的表 Percentage 数据类型为 DECIMAL 3 2 已用费用 Pem 数据类型为 INT 要求 完成百分比用小数表示且数值小于 1 创建包含检查约束字段的表 1 已用费用的取值为 0 100000 用规则实现 use COMPANY create table Process Pno char 4 not null Percentage decimal 3 2 not null P