数据库系统管理(王岚主编)实验 PPT课件

数据库基础 1 第二部分SQLServer2005实验 数据库基础 2 内容 实验1SQLServer2005管理工具的使用实验2创建数据库与二维表实验3数据库的查询实验4数据库的更新实验5T SQLServer2005编程 数据库基础 3 内容 实验6索引与完整性实验7存储工程与触发器实验8数据库的安全性实验9备份恢复与导入导出 数据库基础 4 实验1SQLServer2005管理工具的使用 目的与要求 1 掌握SQLServer2005服务器的安装 2 掌握SQLServerManagementStudio的基本使用方法 3 对数据库及其对象有基本了解 数据库基础 5 实验1SQLServer2005管理工具的使用 实验准备 1 了解SQLServer2005各种版本安装的软硬件要求 2 了解SQLServer支持的身份验证模式 3 SQLServer各组件的主要功能 4 对数据库 表 数据库对象有基本了解 5 了解在SQLServerManagementStudio中执行SQL语句的方法 数据库基础 6 实验1SQLServer2005管理工具的使用 实验内容1 利用SQLServerManagementStudio查看系统自带的master数据库 1 打开SQLServerManagementStudio窗口 2 在 连接到服务器 对话框中 选择服务器类型 服务器名称 设置好身份验证模式后 单击 连接 按钮 3 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 系统数据库 master节点 则将列出该数据库的所有对象 如 表 视图 可编程性 ServiceBroker 安全性等 4 选中master的 表 系统表 将列出master数据库所有的表 我们可以查看各表的相关信息 如列 键 约束 触发器 索引等 数据库基础 7 实验1SQLServer2005管理工具的使用 2 通过T SQL语句查询数据库中表的记录 1 以master数据库的表spt values为例 点击工具栏中的 新建查询 按钮 2 在编辑窗口中输入如下T SQL语句 usemasterselect fromspt valuesgo点击工具栏中的 执行 按钮 看看执行结果是什么 数据库基础 8 实验1SQLServer2005管理工具的使用 思考与练习 通过T SQL语句查询master数据库spt monitor表的记录 数据库基础 9 实验2创建数据库与二维表 目的与要求 1 了解SQLServer数据库的逻辑结构和物理结构 2 了解表的结构特点 3 了解SQLServer的基本数据类型 4 了解空值概念 5 学会在SQLServerManagementStudio中创建数据库和表 6 学会使用T SQL语句创建数据库和表 数据库基础 10 实验2创建数据库与二维表 实验准备 1 创建数据库必须要确定数据库名 所有者 数据库大小 初始大小 最大文件大小 是否启用自动增长及增长方式等 和存储数据库的文件 2 确定数据库包含哪些表以及所包含的各表的结构 还要了解SQLServer的常用数据类型 以便创建数据库和表 3 了解2种常用的创建数据库 表的方法 即通过SQLServerManagementStudio图形工具中创建和使用T SQL的CREATEDATABASE语句创建 数据库基础 11 实验2创建数据库与二维表 实验内容1 实验题目创建用于企业管理的员工管理数据库 数据库名为YGGL 包含员工的信息 部门信息以及员工的薪水信息 数据库YGGL包含下列3个表 1 Employees 员工自然信息表 2 Departments 部门信息表 3 Salary 员工薪水情况表 各表的结构分别如表T2 1 表T2 2 表T2 3所示 数据库基础 12 实验2创建数据库与二维表 表T2 1Employees表结构 数据库基础 13 实验2创建数据库与二维表 表T2 2Departments表结构 数据库基础 14 实验2创建数据库与二维表 表T2 3Salary表结构 数据库基础 15 实验2创建数据库与二维表 2 实验步骤 1 在SQLServerManagementStudio中创建数据库YGGL要求 数据库YGGL初始大小为10MB 最大大小为50MB 数据库自动增长 增长方式是按5 比例增长 日志文件初始为2MB 最大可增长到5MB 默认为不限制 按1MB增长 默认是按10 比例增长 数据库基础 16 实验2创建数据库与二维表 在 对象资源管理器 窗格中右击 数据库 节点 从弹出的菜单中选择 新建数据库 命令 打开 新建数据库 窗口 在 数据库名称 文本框中输入数据库名称YGGL 然后在 数据库文件 选项卡中按要求设置数据库大小 增长方式和增长比例等 单击 确定 按钮 完成数据库YGGL的创建 数据库基础 17 实验2创建数据库与二维表 2 在SQLServerManagementStudio中删除创建的数据库YGGL在SQLServerManagementStudio中展开 数据库 节点 在其子节点 YGGL 上单击鼠标右键 选择 删除 打开 删除对象 窗口 单击 确定 按钮 即删除了创建的数据库YGGL 数据库基础 18 实验2创建数据库与二维表 3 使用T SQL语句创建数据库YGGL按照步骤 1 中的要求创建数据库YGGL 数据库基础 19 实验2创建数据库与二维表 打开SQLServerManagementStudio窗口 点击工具栏中的 新建查询 按钮 在编辑窗口中输入以下T SQL语句 CREATEDATABASEYGGLON NAME YGGL Data FILENAME c ProgramFiles MicrosoftSQLServer MSSQL 1 MSSQL Data YGGL Data mdf SIZE 10MB MAXSIZE 50MB FILEGROWTH 5 LOGON 数据库基础 20 实验2创建数据库与二维表 NAME YGGL Log FILENAME c ProgramFiles MicrosoftSQLServer MSSQL 1 MSSQL Data YGGL Log ldf SIZE 2MB MAXSIZE 5MB FILEGROWTH 1MB GO点击工具栏中的 执行 按钮 执行上述语句 并在 对象资源管理器 窗格中查看执行结果 数据库基础 21 实验2创建数据库与二维表 4 在SQLServerManagementStudio中分别创建表Employees Departments和Salary在SQLServerManagementStudio中依次展开 数据库 YGGL 节点 在子节点 表 上单击鼠标右键 选择 新建表 命令 在右边的表属性栏中显示了设置此表格的相关信息 输入表名Employees 在窗口的中间可以设置此表的一些列属性 输入Employees表的各字段信息 保存后即创建了表Employees 按照同样的操作过程创建表Departments和Salary 数据库基础 22 实验2创建数据库与二维表 5 在SQLServerManagementStudio中删除创建的表Employees Departments和Salary在SQLServerManagementStudio中依次展开 数据库 YGGL 表 节点 在子节点 dbo Employees 上单击鼠标右键 选择 删除 命令 打开 删除对象 窗口 点击 确定 按钮 即删除了创建的表Employees 按照同样的操作过程删除表Departments和Salary 数据库基础 23 实验2创建数据库与二维表 6 使用T SQL语句创建表Employees Departments和Salary点击工具栏中的 新建查询 按钮 在编辑窗口中输入以下T SQL语句 USEYGGLCREATETABLEEmployees EmployeeIDchar 6 NOTNULL Namechar 10 NOTNULL SexbitNOTNULL 数据库基础 24 实验2创建数据库与二维表 PhoneNumberchar 12 NULL EmailAddresschar 20 NULL DepartmentIDchar 3 NOTNULL GO点击工具栏中的 执行 按钮 执行上述语句 即可创建表Employees 按照同样的操作过程创建表Departments和Salary 并在 对象资源管理器 窗格中查看执行结果 数据库基础 25 实验3数据库的查询 目的与要求 1 掌握SELECT语句的基本语法 2 掌握子查询的表示 3 掌握连接查询的表示 4 掌握数据汇总的方法 5 掌握SELECT语句的GROUPBY子句的作用和使用方法 6 掌握SELECT语句的ORDERBY子句的作用和使用方法 数据库基础 26 实验3数据库的查询 实验准备 1 了解SELECT语句的基本语法格式 2 了解SELECE语句的执行方法 3 了解子查询的表示方法 4 了解连接查询的表示 5 了解数据汇总的方法 6 了解SELECT语句的GROUPBY子句的作用和使用方法 7 了解SELECT语句的ORDERBY子句的作用 数据库基础 27 实验3数据库的查询 实验内容1 SELECT语句的基本使用 1 对于实验2给出的数据库表结构 查询每个雇员所有数据 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECT FROMEmployeesGO 数据库基础 28 实验3数据库的查询 思考与练习 用SELECT语句查询Departments和Salary表的所有记录 2 查询每个雇员的电话号码和电子邮件地址 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTPhoneNumber EmailAddressFROMEmployeesGO 数据库基础 29 实验3数据库的查询 思考与练习 用SELECT语句查询Departments和Salary表的一列或若干列 3 查询EmployeeID为300380的雇员的电话和电子邮件 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTPhoneNumber EmailAddressFROMEmployeesWHEREEmployeeID 300380 GO 数据库基础 30 实验3数据库的查询 思考与练习 用SELECT语句查询Departmens和Salary表中满足指定条件的1列或若干列 4 查询Employees表中女雇员的电话和电子邮件地址 使用AS子句将结果中各列的标题分别指定为电话 邮箱 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTPhoneNumberAS电话 EmailAddressAS邮箱FROMEmployeesWHEREsex 0GO注意 使用AS子句可指定目标列的标题 数据库基础 31 实验3数据库的查询 5 计算每个员工的实际收入 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTEmployeeID 实际收入 InCome OutComeFROMSalaryGO 数据库基础 32 实验3数据库的查询 6 找出所有姓王的员工的部门号 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTDepartmentIDFROMEmployeesWHEREnameLIKE 王 GO 数据库基础 33 实验3数据库的查询 思考与练习 找出所有使用 新浪 邮箱的员工的号码和部门号 数据库基础 34 实验3数据库的查询 7 找出所有收入在1500 2500元之间的员工号码 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTEmployeeIDFROMSalaryWHEREInComeBETWEEN1500AND2500GO 数据库基础 35 实验3数据库的查询 思考与练习 找出所有在部门 1 或 3 工作的员工的号码 注意 在SELECT语句中LIKE BETWEEN AND IN NOT及CONTAIN谓词的作用 数据库基础 36 实验3数据库的查询 2 子查询的使用查找在营销部工作的员工的情况 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECT FROMEmployeesWHEREDepartmentID SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName 营销部 GO 数据库基础 37 实验3数据库的查询 思考与练习 用子查询的方法查找所有收入在2000元以下的员工的情况 数据库基础 38 实验3数据库的查询 3 连接查询的使用查询每个员工的情况以及薪水的情况 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTEmployees Salary FROMEmployees SalaryWHEREEmployees EmployeeID Salary EmployeeIDGO 数据库基础 39 实验3数据库的查询 思考与练习 查询每个员工的情况以及其工作部门的情况 数据库基础 40 实验3数据库的查询 4 数据汇总 1 求研发部员工的平均收入 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTAVG InCome AS 研发部平均收入 FROMSalaryWHEREEmployeeIDIN SELECTEmployeeIDFROMEmployeesWHEREDepartmentID SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName 研发部 GO 数据库基础 41 实验3数据库的查询 思考与练习 查询研发部员工的最高和最低收入 数据库基础 42 实验3数据库的查询 2 求研发部员工的平均实际收入 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTAVG InCome OutCome AS 研发部平均实际收入 FROMSalaryWHEREEmployeeIDIN 数据库基础 43 实验3数据库的查询 SELECTEmployeeIDFROMEmployeesWHEREDepartmentID SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName 研发部 GO 数据库基础 44 实验3数据库的查询 思考与练习 查询研发部员工的最高和最低实际收入 数据库基础 45 实验3数据库的查询 3 求研发部的总人数 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTCOUNT EmployeeID FROMEmployeesWHEREDepartmentID SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName 研发部 GO 数据库基础 46 实验3数据库的查询 思考与练习 统计研发部收入在2000元以上员工的人数 数据库基础 47 实验3数据库的查询 5 GROUPBY ORDERBY子句的使用 1 求各部门的员工数 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTCOUNT EmployeeID FROMEmployeesGROUPBYDepartmentIDGO 数据库基础 48 实验3数据库的查询 思考与练习 统计各部门收入在2500元以上的员工的人数 数据库基础 49 实验3数据库的查询 2 将各员工的情况按收入由高到低排列 在查询编辑窗口中输入如下语句并执行 USEYGGLSELECTEmployees Salary FROMEmployees SalaryWHEREEmployees EmployeeID Salary EmployeeIDORDERBYInComeDESCGO 数据库基础 50 实验3数据库的查询 思考与练习 将各员工的情况按员工编号排列 数据库基础 51 实验4数据库的更新 目的和要求 1 学会在SQLServerManagementStudio中对数据库表进行插入 修改和删除数据操作 2 学会使用T SQL语句对数据库表进行插入 修改和删除数据操作 3 了解数据更新操作时要注意数据完整性 4 了解T SQL语句对表数据操作的灵活控制功能 数据库基础 52 实验4数据库的更新 实验准备 1 了解对表数据的插入 修改和删除都属于表数据的更新操作 对表数据的操作都可以在SQLServerManagementStudio中进行 也可以由T SQL语句实现 2 掌握T SQL中用于对表数据进行插入 修改和删除的命令分别是INSERT UPDATE和DELETE 或TRUNCATETABLE 要特别注意在执行插入 删除 修改等数据更新操作时 必须保证数据完整性 3 了解使用T SQL语句在对表数据进行插入 修改和删除时 比通过SQLServerManagementStudio图形工具操作表数据更为灵活 功能更强大 数据库基础 53 实验4数据库的更新 实验内容1 实验题目分别通过SQLServerManagementStudio和T SQL语句 按照以下实验步骤向建立的数据库YGGL的3个表Employees Departments和Salary中插入多行数据记录 然后修改和删除一些记录 使用T SQL语句进行有限制的修改和删除 数据库基础 54 实验4数据库的更新 2 实验步骤 1 在SQLServerManagementStudio中向数据库YGGL表加入数据 在SQLServerManagementStudio中向表Employees中加入如表T4 1所示的记录 数据库基础 55 实验4数据库的更新 表T4 1Employees数据记录 数据库基础 56 实验4数据库的更新 在SQLServerManagementStudio中依次展开 数据库 YGGL 表 节点 在子节点 dbo Employees 上单击鼠标右键 选择 打开表 命令 逐字段输入各记录值 输入完后 关闭该选项卡窗格 在SQLServerManagementStudio中向表Departments中加入如表T4 2所示的记录 数据库基础 57 实验4数据库的更新 表T4 2Departments数据记录 数据库基础 58 实验4数据库的更新 在SQLServerManagementStudio中向表Salary中加入如表T4 3所示的记录 数据库基础 59 实验4数据库的更新 表T4 3Salary数据记录 数据库基础 60 实验4数据库的更新 2 在SQLServerManagementStudio中修改数据库YGGL表的数据 在SQLServerManagementStudio中删除表Employees的第7行和Salary的第7行 注意进行删除操作时 作为两表主键的EmployeeID的值 以保持数据的完整性 在节点 dbo Employees 上单击鼠标右键 选择 打开表 命令 选择要删除的行 单击鼠标右键 选择 删除 命令 单击 是 按钮 数据库基础 61 实验4数据库的更新 在SQLServerManagementStudio中删除表Departments的第4行 同时也要删除表Employees的第6行 操作方法同 在SQLServerManagementStudio中将表Employees中编号为202897的记录的部门编号改为4 在节点 dbo Employees 上单击鼠标右键 选择 打开表 命令 将光标定位至编号为202897的记录的DepartmentID字段 将值2改为4 数据库基础 62 实验4数据库的更新 3 使用T SQL命令修改数据库YGGL表数据 使用T SQL命令分别向YGGL数据库Employees Departments和Salary表中插入1行记录 数据库基础 63 实验4数据库的更新 点击工具栏中的 新建查询 按钮 在编辑窗口中输入以下T SQL语句 USEYGGLINSERTINTOEmployeesVALUES 102427 刘涛 1 89324852 NULL 4 GOINSERTINTODepartmentsVALUES 6 管理部 NULL GO 数据库基础 64 实验4数据库的更新 INSERTINTOSalaryVALUES 102427 1321 37 79 GO点击工具栏中的 执行 按钮 执行上述语句 在SQLServerManagementStudio中分别打开YGGL数据库的Employees Departments和Salary表 观察数据的变化 数据库基础 65 实验4数据库的更新 使用T SQL命令修改Salary表中的某个记录的字段值 点击工具栏中的 新建查询 按钮 在编辑窗口中输入以下T SQL语句 USEYGGLUPDATESalarySETInCome 2367 76WHEREEmployeeID 203586 GO点击工具栏中的 执行 按钮 执行上述语句 在SQLServerManagementStudio中打开YGGL数据库的Salary表 观察数据的变化 数据库基础 66 实验4数据库的更新 修改表Employees和Departments的记录值 仍然要注意完整性 操作过程同 使用T SQL命令修改Salary表中的所有记录的字段值 点击工具栏中的 新建查询 按钮 在编辑窗口中输入以下T SQL语句 USEYGGLUPDATESalarySETInCome InCome 200GO点击工具栏中的 执行 按钮 执行上述语句 将所有职工的收入都增加200 可见 使用T SQL语句操作表数据比通过SQLServerManagementStudio图形工具更为灵活 数据库基础 67 实验4数据库的更新 使用TRUNCATETABLE语句删除表中所有行 点击工具栏中的 新建查询 按钮 在编辑窗口中输入以下T SQL语句 USEYGGLTRUNCATETABLESalaryGO点击工具栏中的 执行 按钮 执行上述语句 将删除Salary表中所有行 注意 实验时一般不轻易做这个操作 后面的实验还要用到这些数据 因此可见一个临时表 输入少量数据后进行 数据库基础 68 实验5T SQLServer2005编程 实验5T SQLServer2005编程目的与要求 1 掌握用户自定义类型的使用 2 掌握变量的分类及其使用 3 掌握各种运算符的使用 4 掌握各种控制语句的使用 5 掌握系统函数及其用户自定义函数的使用 数据库基础 69 实验5T SQLServer2005编程 实验准备 1 了解T SQL支持的各种基本数据类型 2 了解自定义数据类型使用的一般步骤 3 了解T SQL各种运算符 控制语句的功能及使用方法 4 了解系统函数的调用方法 5 了解用户自定义函数使用的一般步骤 数据库基础 70 实验5T SQLServer2005编程 实验内容1 自定义数据类型的使用 1 对于实验2给出的数据表结构 自定义1个数据类型ID type 用于描述员工编号 在查询编辑窗口中输入如下语句并执行 USEYGGLEXECsp addtype ID type char 6 notnull GO 数据库基础 71 实验5T SQLServer2005编程 2 重新创建YGGL数据库的Employees表 在查询编辑窗口中输入如下程序并执行 USEYGGLIFEXISTS SELECTnameFROMsysobjectsWHEREtype U andname Employees DROPtableEmployees 数据库基础 72 实验5T SQLServer2005编程 CREATETABLEEmployees EmployeeIDID type Namechar 10 NOTNULL SexbitNOTNULL PhoneNumberchar 2 NULL EmailAddresschar 20 NULL DepartmentIDchar 3 NOTNULL GO 数据库基础 73 实验5T SQLServer2005编程 2 自定义函数的使用 1 定义一函数实现如下功能 对于1个给定的DepartmentID之值 查询该值在Departments表中是否存在 若存在返回0 否则返回 1 在查询编辑窗口中输入如下程序并执行 CREATEFUNCTIONCHECK ID departmentidchar 3 RETURNSintegerAS 数据库基础 74 实验5T SQLServer2005编程 begindeclare numintIFEXISTS SELECTDepartmentIDFROMDepartmentsWHERE departmentid DepartmentID select num 0ELSEselect num 1return numendGO 数据库基础 75 实验5T SQLServer2005编程 2 写一段T SQL脚本程序调用上述函数 当向Employees表插入一条记录时 首先调用函数CHECE ID检索该记录的DepartmentID之值在表Departments的DepartmentID字段中是否存在对应值 若存在 则将该记录插入Employees表 在查询编辑窗口中输入如下程序并执行 USEYGGLDECLARE numintSELECT num dbo CHECK ID 2 IF num 0insertintoEmployeesvalues 783462 刘伟 0 63237846 NULL 2 GO 数据库基础 76 实验5T SQLServer2005编程 思考与练习 编写如下程序 1 自定义一个数据类型 用于描述YGGL数据库中的DepartmentID字段 然后编写代码重新定义数据库的各表 2 当对Departments表的DepartmentID字段值修改时 对Employees表中对应的DepartmentID字段值也进行相应修改 3 对Employees表进行修改时 不允许对DepartmentID字段值进行修改 数据库基础 77 实验6索引与完整性 目的与要求 1 掌握索引的使用方法 2 掌握数据完整性的实现方法 数据库基础 78 实验准备 1 了解索引的作用与分类 2 掌握索引的创建方法 3 理解数据完整性的概念及分类 4 掌握各种数据完整性的实现方法 实验6索引与完整性 数据库基础 79 实验内容1 建立索引 1 使用图形化工具创建索引 打开SQLServerManagementStudio窗口 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 YGGL 表 dbo Salary 节点 在其子节点 索引 上单击鼠标右键 选择 新建索引 命令 打开新建索引窗口 输入索引名称为 employee ind 选择索引类型 并点击 添加 按钮 选择添加到索引键的列 点击 确定 按钮返回 常规 页面 配置其他选项后 点击 确定 按钮完成创建 实验6索引与完整性 数据库基础 80 2 使用T SQL语句创建索引 在SQLServerManagementStudio查询编辑窗口中输入如下程序 USEYGGLIFEXISTS SELECTnameFROMsysindexesWHEREname depart ind DROPINDEXEmployees depart indGOUSEYGGLCREATEINDEXdepart indONEmployees DepartmentID 点击工具栏中的 执行 按钮 执行上述语句 并在 对象资源管理器 窗格中查看执行结果 实验6索引与完整性 数据库基础 81 思考与练习 什么情况下可以看到建立索引的好处 实验6索引与完整性 数据库基础 82 2 数据完整性 1 域完整性的实现 打开SQLServerManagementStudio窗口 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 YGGL 表 在其子节点 dbo Salary 上单击鼠标右键 从弹出的快捷菜单中选择 设计 命令 再单击鼠标右键 选择 CHECK约束 命令 打开 CHECK约束 对话框 点击 添加 按钮 在 表达式 文本框中输入要设置的CHECK约束文本 InCome OutCome 单击 关闭 按钮完成约束的创建 实验6索引与完整性 数据库基础 83 2 实体完整性的实现 打开SQLServerManagementStudio窗口 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 YGGL 表 在其子节点 dbo Employees 上单击鼠标右键 从弹出的快捷菜单中选择 设计 命令 右键单击要设置主键的列EmployeeID 选择 设置主键 命令完成设置PRIMARYKEY约束的列 实验6索引与完整性 数据库基础 84 再单击鼠标右键 从弹出的快捷菜单中选择 索引 键 命令 打开 索引 键 对话框 点击 添加 按钮 再点击 列 右侧的按钮 打开 索引列 对话框 在 列名 的下拉菜单中选择 EmailAddress 点击 确定 返回 索引 键 对话框 输入 名称 后点击 关闭 按钮完成设置UNIQUE约束的列 实验6索引与完整性 数据库基础 85 3 参照完整性的实现 在节点 dbo Employees 上单击鼠标右键 从弹出的快捷菜单中选择 设计 命令 单击鼠标右键选择 关系 命令 打开 外键关系 对话框 点击 添加 按钮 再点击 表和列规范 右侧的按钮 打开 表和列 对话框 输入关系名 主键表 选择 Departments 并在下面两个下拉菜单中都选择 EmployeeID 实验6索引与完整性 数据库基础 86 思考与练习 1 实现将多个CHECK约束用于单个列 2 通过在表级创建CHECK约束 将一个CHECK约束应用于多个列 实验6索引与完整性 数据库基础 87 实验7存储工程与触发器 目的与要求 1 掌握索引的使用方法 2 掌握存储过程的使用方法 3 掌握触发器的使用方法 数据库基础 88 实验7存储工程与触发器 实验准备 1 了解索引的作用与分类 2 掌握索引的创建方法 3 理解数据完整性的概念及分类 4 了解各种数据完整性的实现方法 5 了解存储过程的使用方法 6 了解触发器的使用方法 7 了解inserted逻辑表和deleted逻辑表的使用 数据库基础 89 实验7存储工程与触发器 实验内容1 创建触发器对于YGGL数据库 表Employees的DepartmentID列与表Departments的DepartmentID列应满足参照完整性规则 即 1 向Employees表添加1条记录时 该记录的DepartmentID值在Departments表中应存在 2 修改Departments表DepartmentID字段值时 该字段在Employees表中的对应值也应修改 3 删除Departments表中1条记录时 该记录DepartmentID字段值在Employees表中对应的记录也应被删除 数据库基础 90 实验7存储工程与触发器 对于上述参照完整性 在此通过触发器实现 在SQLServerManagementStudio查询编辑窗口中输入如下程序并执行 向Employees表插入或修改1条记录时 通过触发器检查记录的DepartmentID值在Departments表中是否存在 若不存在 则取消插入或修改操作 数据库基础 91 实验7存储工程与触发器 USEYGGLGOCREATETRIGGEREmployeesInsondbo EmployeesFORINSERT UPDATEASBEGINIF SELECTins departmentidfrominsertedins NOTIN SELECTdepartmentidFROMdepartments ROLLBACKEND 数据库基础 92 实验7存储工程与触发器 修改Departments表DepartmentID字段值时 该字段在Employees表中的对应值也进行相应修改 数据库基础 93 实验7存储工程与触发器 USEYGGLGOCREATETRIGGERDepartmentsUpdateondbo DepartmentsFORUPDATEASBEGINIF COLUMNS UPDATED 01 0UPDATEEmployeesSETDepartmentID SELECTins Departm ntIDfromINSERTEDins WHEREDepartmentID SELECTDepartmentIDFROMdeleted ENDGO 数据库基础 94 实验7存储工程与触发器 删除Departments表中1条记录的同时删除该记录DepartmentID字段值在Employees表中对应的记录 数据库基础 95 实验7存储工程与触发器 USEYGGLGOCREATETRIGGERDepartmentsDeleteondbo DepartmentsFORDELETEASBEGINDELETEFROMEmployeesWHEREDepartmentID SELECTDepartmentIDFROMdeleted ENDGO 数据库基础 96 实验7存储工程与触发器 2 创建存储过程在SQLServerManagementStudio查询编辑窗口中输入各存储过程的代码并执行以下程序 1 添加员工记录的存储过程EmployeeAdd 数据库基础 97 实验7存储工程与触发器 思考与练习 上述触发器的功能用完整性的方法完成 数据库基础 98 实验7存储工程与触发器 USEYGGLGOCREATEPROCEDUREEmployeeAdd employeeidchar 6 namechar 10 sexbit phonenumberchar 12 emailaddresschar 20 departmentidchar 3 ASBEGININSERTINTOEmployeesVALUES employeeid name sex phonenumber emailaddress departmentID ENDRETURNGO 数据库基础 99 实验7存储工程与触发器 2 修改员工记录的存储过程EmployeeUpdate USEYGGLGOCREATEPROCEDUREEmployeeUpdate empidchar 6 employeeidchar 6 namechar 10 sexbit phonenumberchar 12 emailaddresschar 20 departmentidchar 3 AS 数据库基础 100 实验7存储工程与触发器 BEGINUpdateEmployeesSETEmployeeID employeeid Name name Sex sex PhoneNumber phonenumber EmailAddress emailaddress DepartmentID departmentIDWHEREEmployeeID empidENDRETURNGO 数据库基础 101 实验7存储工程与触发器 3 删除员工记录的存储过程EmployeeDelete USEYGGLGOCREATEPROCEDUREEmployeeDelete employeeidchar 6 ASBEGINDELETEFROMEmployeesWHEREEmployeeID employeeidENDRETURNGO 数据库基础 102 实验7存储工程与触发器 3 调用存储过程USEYGGLEXECEmployeeAdd 376735 赵伟 1 65841859 1 GOUSEYGGLEXECEmployeeUpdate 376735 376753 赵伟 1 65841859 2 GOUSEYGGLEXECEmployeeDelete 376753 GO分析一下此段程序执行时可能出现哪几种情况 数据库基础 103 实验7存储工程与触发器 思考与练习 编写以下T SQL程序 1 自定义一个数据类型 用于描述YGGL数据库中的DepartmentID字段 然后编写代码重新定义数据库各表 2 对于YGGL数据库 表Employees的EmployeeID列与表Salary的EmployeeID列应满足参照完整性规则 请用触发器实现两个表之间的参照完整性 3 编写对YGGL数据库各表进行插入 修改 删除操作的存储过程 然后 编写1段程序调用这些存储过程 数据库基础 104 实验8数据库的安全性 实验8 1数据库用户权限的设置实验8 2服务器角色的应用实验8 3数据库角色的应用 数据库基础 105 实验8 1数据库用户权限的设置 实验目的 1 掌握 Windows身份验证 模式下登录名的创建方法 2 掌握 SQLServer身份验证 模式下登录名的创建方法 3 掌握数据库用户权限的设置方法 数据库基础 106 实验8 1数据库用户权限的设置 实验准备 1 了解 Windows身份验证 及 SQLServer身份验证 模式下登录名的创建方法 2 了解数据库用户权限的设置与回收方法 数据库基础 107 实验8 1数据库用户权限的设置 实验内容1 通过SQLServerManagementStudio创建 Windows身份验证 登录名 1 打开SQLServerManagementStudio窗口 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 安全性 节点 在其子节点 登录名 上单击鼠标右键 选择 新建登录名 命令 数据库基础 108 实验8 1数据库用户权限的设置 2 在 登录名 新建 窗口中 选择 Windows身份验证 登录名 3 点击 搜索 按钮 打开 选择用户或组 对话框 点击 高级 按钮后 再点击 立即查找 按钮 选择Windows用户名 点击 确定 4 选择用户或组后 点击 确定 按钮 返回 登录名 新建 窗口 再点击 确定 按钮 数据库基础 109 实验8 1数据库用户权限的设置 2 使用T SQL语句创建 SQLServer身份验证 登录名在SQLServerManagementStudio查询编辑窗口中输入如下程序 CREATELOGINnewloginWITHPASSWORD 123456 GO点击工具栏中的 执行 按钮 执行上述语句 并在 对象资源管理器 窗格中查看执行结果 数据库基础 110 实验8 1数据库用户权限的设置 3 设置权限 1 创建与登录名newlogin对应的数据库用户newuser 打开SQLServerManagementStudio窗口 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 YGGL 安全性 在其子节点 用户 上单击鼠标右键 从弹出的快捷菜单中选择 新建用户 命令 打开 数据库用户 新建 窗口 数据库基础 111 实验8 1数据库用户权限的设置 输入数据库用户名newuser 指定对应的登录名newlogin 设置用户拥有的架构 设置数据库角色成员成分 点击 确定 完成数据库用户的创建 数据库基础 112 实验8 1数据库用户权限的设置 2 赋予数据库用户newuser对表Employees和Departments进行插入 修改 删除的操作权限及对表Salary进行查询的操作权限 在SQLServerManagementStudio查询编辑窗口中输入如下程序并执行 USEYGGLGRANTINSERT UPDATE DELETEONEmployeesTonewuserGRANTINSERT UPDATE DELETEONDepartmentsTonewuserGRANTSELECTONSalaryTonewuserGO 数据库基础 113 实验8 1数据库用户权限的设置 思考与练习 如何取消赋予用户newuser的权限 数据库基础 114 实验8 2服务器角色的应用 实验目的掌握服务器角色的用法实验准备 1 了解服务器角色的分类 2 了解每类服务器角色的功能 数据库基础 115 实验8 2服务器角色的应用 实验内容 1 将登录名 newlogin 添加到 sysadmin 固定服务器角色 打开SQLServerManagementStudio窗口 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 安全性 服务器角色 节点 在其子节点 sysadmin 上单击鼠标右键 选择 属性 命令 打开 服务器角色属性 sysadmin 窗口 点击 添加 按钮 再点击 浏览 按钮 打开 查找对象 对话框 选中 newlogin 后 连续三次点击 确定 按钮 数据库基础 116 实验8 2服务器角色的应用 2 将登录名 newlogin 添加到 serveradmin 服务器角色中 并从 sysadmin 服务器角色中将 newlogin 删除 在SQLServerManagementStudio查询编辑窗口中输入如下程序 EXECSP addsrvrolemember newlogin serveradmin GOEXECsp dropsrvrolemember newlogin sysadmin GO执行以上程序 并在 对象资源管理器 中查看结果 数据库基础 117 实验8 2服务器角色的应用 思考与练习 如何查看登录名隶属于哪些服务器角色 数据库基础 118 实验8 3数据库角色的应用 实验目的 1 掌握数据库角色的分类 2 掌握数据库角色的作用 3 掌握数据库角色的使用方法 实验准备 1 了解数据库角色的分类 2 了解数据库角色的使用方法 数据库基础 119 实验8 3数据库角色的应用 实验内容 1 查看固定数据库角色db datawriter的属性 并将数据库用户newuser添加到该角色中 打开SQLServerManagementStudio窗口 从 对象资源管理器 窗格中依次展开SQLServer服务器下的 数据库 YGGL 安全性 角色 数据库角色 节点 在其子节点 db datawriter 上单击鼠标右键 选择 属性 命令 打开 数据库角色属性 db datawriter 窗口 点击 添加 按钮 再点击 浏览 按钮 打开 查找对象 对话框 选中 newuser 后 连续三次点击 确定 按钮 数据库基础 120 实验8 3数据库角色的应用 2 在YGGL数据库中创建用户定义数据库角色 db user 并将数据库用户 newuser 添加到该角色中 在SQLServerManagementStudio查询编辑窗口中输入如下程序 USEYGGLGOCREATEROLEdb userGOEXECsp addrolemember db user newuser GO执行以上程序 并在 对象资源管理器 中查看结果 数据库基础 121 实验8 3数据库角色的应用 思考与练习 如何查看数据库用户隶属于哪些数据库角色 数据库基础 122 实验9备份恢复与导入导出 实验9 1数据