DBMS(5).ppt

通信与信息工程学院毛建华Tel 56337674 O Email mjh 办公室 行键楼324 数据库应用 回顾 下列T SQL语句有错吗 实现了什么功能 SELECT FROMSWHERES IN SELECT FROMSC 上述语句可以用下面的连接替换吗 SELECT FROMSINNERJOINSCONS S SC S 下列语句实现了什么功能 SELECT FROMSWHERES NOTIN SELECTS FROMSC 上述语句可以用下面的连接替换吗 SELECT FROMSINNERJOINSCONS S SC S SELECTS FROMSC 可以 查看没有选课的学生 有的子查询不能用连接替换 这就是典型的例子 回顾 下列语句实现了什么功能 SELECT FROMSWHEREEXISTS SELECT FROMSCWHERES S S 请使用IN子查询改写上述语句 请使用连接改写上述语句 查询选了课程的学员 SELECT FROMSWHERES IN SELECTS FROMSC SELECT FROMSINNERJOINSCONS S SC S SC 学生选课成绩表 S 学生信息表 C 课程信息表 思考 试用SQL语句进行下列查询 1 检索 程军 老师所授课程的课程号 C 和课程名 CNAME 2 检索年龄大于21的男学生学号 S 和姓名 SNAME 3 检索至少选修 程军 老师所授全部课程的学生学号 SNAME 4 检索 李强 同学不学课程的课程号 C 5 检索至少选修两门课程的学生学号 S 6 检索全部学生都选修的课程的课程号和课程名 7 检索选修课程包含 程军 老师所授课程之一的学生学号 8 检索选修课程号为k1和k5的学生学号 9 检索选修全部课程的学生姓名 3 5索引与视图 3 5 1索引与视图的创建和撤消3 5 2对视图的更新操作3 5 3视图的优点 索引的创建和撤消 1 什么是索引 索引含义与日常意义上的 索引 一词并无多大区别 想想小时候查字典 它是用于提高数据库表数据访问速度的数据库对象 索引类型 唯一索引 唯一索引不允许两行具有相同的索引值主键索引 为表定义一个主键将自动创建主键索引 主键索引是唯一索引的特殊类型 主键索引要求主键中的每个值是唯一的 并且不能为空聚集索引 Clustered 表中各行的物理顺序与键值的逻辑 索引 顺序相同 每个表只能有一个非聚集索引 Non clustered 非聚集索引指定表的逻辑顺序 数据存储在一个位置 索引存储在另一个位置 索引中包含指向数据存储位置的指针 可以有多个 小于249个 两种类型的索引 聚集索引 两种类型的索引 非聚集索引 索引的创建和撤消 1 索引的创建 其句法如下 CREATE UNIQUE INDEXON 例 CREATETABLES S CHAR 4 NOTNULL SNAMECHAR 8 NOTNULL AGECHAR 1 SEXCHAR 1 CREATEINDEXS INDEXONS S CREATEUNIQUEINDEXSC INDEXONSC S ASC C DESC 索引的创建和撤消 2 索引的撤消 索引不需要时 可以用 DROPINDEX 语句撤消 其句法如下 DROPINDEX例 DROPINDEXS INDEX SC INDEX 创建索引的指导原则 请按照下列标准选择建立索引的列 该列用于频繁搜索该列用于对数据进行排序请不要使用下面的列创建索引 列中仅包含几个不同的值 表中仅包含几行 为小型表创建索引可能不太划算 因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长 CREATE UNIQUE CLUSTERED NONCLUSTERED INDEXindex nameONtable name column name WITHFILLFACTOR x UNIQUE表示唯一索引 可选CLUSTERED NONCLUSTERED表示聚集索引还是非聚集索引 可选FILLFACTOR表示填充因子 指定一个0到100之间的值 该值指示索引页填满的空间所占的百分比 索引的创建 创建索引可用 CREATEINDEX 语句实现 唯一索引 聚集索引或非聚集索引 填充因子 系数 指定一个0 100之间的值 表示索引页填充的百分比 SQLServer创建索引 SQLServer创建索引 例1 在 学生表 的 姓名 列上创建非聚集索引 USE实例数据库CREATECLUSTEREDINDEXname idxON学生表 姓名 GO 例2 在 学生表 的 学号 列上创建唯一聚集索引 USE实例数据库CREATEUNIQUECLUSTEREDINDEXid idxON学生表 学号 GO 例3 在 选课表 的 学号 和 课程号 列上创建组合索引 USE实例数据库CREATEINDEXindex 1ON选课表 学号 课程号 GO 是一种数据库对象 常用于集中 简化和定制显示数据信息 视图是从一个或者多个表 或视图 中导出表 其结构和数据是建立在表的查询基础上 视图是虚拟表 所对应的数据并不实际地以视图结构存储在数据库中 而是存储在视图所引用的表中 视图的基本概念 什么是视图 CREATEVIEWview stuInfo stuMarksASSELECT姓名 stuName 学号 stuInfo stuNo 基于学员信息表和成绩表创建视图 教员需要的视图 方便查看学员的成绩 班主任需要的视图 方便查看学员的档案 视图的优点 视图是用户一级的数据观点 由于有了视图 使数据库系统具有下列优点 1 视图提供了一定程度的逻辑数据独立性 如 将S SNO SNAME SEX AGE DEPT 分为SX SNO SNAME AGE 和SY SNO SEX DEPT 两个关系 CREATEVIEWS SNO SNAME SEX AGE DEPT ASSELECTSX SNO SX SNAME SY SEX SX AGE SY DEPTFROMSX SYWHERESX SNO SY SNO 2 简化了用户观点 数据库的全部结构是复杂的 并有多种联系 3 数据的安全保护功能 视图的创建 创建视图可用 CREATEVIEW 语句实现 句法如下CREATEVIEW 列表名 AS例3 27对于教学数据库中基本表S SC C 用户经常要用到S SNAME CNAME和GRADE等列的数据 那么可用下列语句建立视图 CREATEVIEWSTUDENT GRADE S SNAME CNAME GRADE ASSELECTS S SNAME CNAME GRADEFROMS SC CWHERES S SC S ANDSC C C C 创建视图时应该注意以下情况 只能在当前数据库中创建视图 视图引用的基表或视图被删除 则该视图不能使用 如果视图中某一列是函数 数学表达式 常量或者来自多个表的列名相同 则必须为列定义名称 数据修改语句不能违反数据完整性规则 视图的名称必须遵循标识符的规则 且对每个用户必须是唯一的 视图的撤消 在视图不需要时 可以用 DROPVIEW 语句把其从系统中撤消 其句法如下 DROPVIEW视图名例3 28撤消STUDENT GRADE视图 可用下列语句实现 DROPVIEWSTUDENT GRADE 对视图的更新操作 对视图的查询操作和基本表一样 如 SELECTSNAME GRADEFROMSTUDENT GRADEWHERECNAME 数据库 更新操作有所不同 一般只有对 行列子集视图 才能更新 定义3 1如果视图是从单个基本表只使用选择 投影操作导出的 并且包含了基本表的主键 那么这样的视图称为 行列子集视图 且可以被执行更新操作 下列视图是否允许更新操作 设有一个视图定义CREATEVIEWSUDENT GRADEASSELECTS S SNAME CNAME GRADEFROMS SC CWHERES S SC S ANDSC C C C 如果用户要在视图中执行插入操作 INSERTINTOSTUDENT GRADEVALUES S24 WANG MATHS 80 若在基本表C中 课程名为MATHS的课程号有多个 则往基本表SC插入元组时 课程号C 就不定了 因此 对这个视图的更新是不允许的 下列视图是否允许更新操作 设有一个视图定义 包含每个学生选修课程 成绩为非空 的门数和平均成绩 CREATEVIEWS GRADE S C NUM AVG GRADE ASSELECTS COUNT C AVG GRADE FROMSCWHEREGRADEISNOTNULLGROUPBYS 视图S GRADE虽然是从单个基本表导出 但导出时使用了分组和聚集操作 因此也是不能更新的 譬如 在未更改基本表SC中值时 要在视图S GRADE中更改学生的平均成绩 显然是不切实际的 下列视图是否允许更新操作 如果定义了一个有关男学生的视图 CREATEVIEWS MALEASSELECTS SNAME AGEFROMSWHERESEX M 这个视图是从单个关系只使用选择和投影导出的 并且包含键S 因此是可更新的 如 执行插入操作 INSERTINTOS MALEVALUES S28 WU 18 系统自动会把它转变成下列语句 INSERTINTOSVALUES S28 WU 18 M 举例1 创建视图 view 1 该视图包含三个列 分别来自S表的 SNAME C表的 CNAME 和SC表的 GRADE CREATEVIEWview 1ASSELECTS SNAME C CNAME SC GRADEFROMCINNERJOINSCONC C SC C INNERJOINSONSC S S S GO SQLServer创建视图 存储过程 storedprocedure 是存储在服务器上的一组预编译的SQL语句 由SQL语句和SPL语句组成 存储过程 procedure 类似于C语言中的函数 用来执行管理任务或应用复杂的业务规则 存储过程可以带参数 也可以返回结果 intsum inta intb ints s a b returns 存储过程相当于C语言中的函数 3 6存储过程与触发器 1 增强SQL语言的功能和灵活性 可完成复杂运算 2 保证数据的安全性和完整性 3 改善SQL语句的性能 能以极快速度执行 4 可以降低网络的通信量 5 集中控制体现企业规则的运算程序 存储过程的优点 系统存储过程 由系统提供 用于从系统表中获取信息 如 SP HELP 获取对象信息SP CONFIGURE 配置服务器XP SENDMAIL 发送电子邮件用户自定义存储过程 由用户自己创建能完成某一特定功能的存储过程 存储过程的分类 创建与维护自定义存储过程 1 限制a 名称限制 必须符合标识符的命名规则b T SQL语句限制 不能使用的SQL语句CREATEDEFAULTCREATETRIGGERCREATEPROCEDURECREATEVIEWCREATERULE CREATEPROC EDURE procedure name parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION ASsql statement 新存储过程的名称 过程中的参数 参数数据类型 参数的默认值 参数是返回参数 指定过程要执行的操作 过程中要包含的任意数目和类型的Transact SQL语句 使用Createprocedure语句 执行存储过程 1 使用Transact SQL语句执行存储过程 EXEC UTE return status procedure name parameter value variable OUTPUT DEFAULT n WITHRECOMPILE 关键字 可选整型变量 保存存储过程的返回状态 过程参数 过程中参数的值 用来保存参数或返回参数的变量 指定存储过程必须返回一个参数 根据过程的定义 提供参数的默认值 book 实例1 查询表Book的内容的存储过程 CREATEPROCEDUREquerybookASSELECT FROMbookEXECquerybook 举例 CREATEPROCinsertbookparam1char 10 param2varchar 20 param3money param4moneyOUTPUTWITHENCRYPTION 加密ASINSERTINTObook 编号 书名 价格 VALUES param1 param2 param3 SELECTparam4 SUM 价格 FROMbook 实例2 加入记录到表book 并查询表中所有书籍的总金额 DECLAREtotalpricemoneyEXECinsertbook 003 VB 100 totalprice 执行 思考 Product Order 请建立一个存储过程 按编号为连接条件 将两个表连接成一个临时表 该表只含编号 产品名 客户名 订金 总金额 订金 订数 SQLServerManagementStudio对象资源管理器中选择要创建存储过程的数据库 SQLServer创建存储过程 选择 可编程性 节点中的 新建存储过程 在打开的文本框中输入创建存储过程的T SQL语句 定义存储过程的语法CREATEPROC EDURE 存储过程名 参数1数据类型 默认值OUTPUT 参数n数据类型 默认值OUTPUTASSQL语句GO和C语言的函数一样 参数可选参数分为输入参数 输出参数输入参数允许有默认值 SQLServer创建存储过程 选择存储过程 单击鼠标右键 选择 执行存储过程 命令 SQLServer执行存储过程 存储过程执行完后的结果显示在窗口右下角 3 7事务 银行转帐 例如 银行转帐问题 假定资金从帐户A转到帐户B 至少需要两步 帐户A的资金减少然后帐户B的资金相应增加 帐户A 帐户B 假定张三的帐户直接转帐1000元到李四的帐户 为什么需要事务 CREATETABLEbank customerNameCHAR 10 顾客姓名currentMoneyMONEY 当前余额 GOALTERTABLEbankADDCONSTRAINTCK currentMoneyCHECK currentMoney 1 GOINSERTINTObank customerName currentMoney VALUES 张三 1000 INSERTINTObank customerName currentMoney VALUES 李四 1 创建帐户表 存放用户的帐户信息 添加约束 根据银行规定 帐户余额不能少于1元 否则视为销户 张三开户 开户金额为1000元 李四开户 开户金额1元 目前两个帐户的余额总和为 1000 1 1001元 模拟实现转帐 从张三的帐户转帐1000元到李四的帐户 转帐测试 张三转账1000元给李四 我们可能会这样这样编写语句 张三的帐户少1000元 李四的帐户多1000元UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 张三 UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 李四 GO 再次查看转帐后的结果 SELECT FROMbankGO 请问 执行转帐语句后 张三 李四的帐户余额为多少 张三的帐户没有减少但李四的帐户却多了1000元1000 1001 2001元总额多出了1000元 张三的帐户减少1000元 李四的帐户增加1000元UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 张三 UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 李四 GO 错误原因分析 UPDATE语句违反约束 余额 1元 执行失败 所以张三还是1000元 继续往下执行 执行成功 所以李四变为1001元 如何解决呢 使用事务 什么是事务 事务 TRANSACTION 是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交 要么都执行 要么都不执行事务是一个不可分割的工作逻辑单元 转帐过程就是一个事务 它需要两条UPDATE语句来完成 这两条语句是一个整体 如果其中任一条出现错误 则整个转帐业务也应取消 两个帐户中的余额应恢复到原来的数据 从而确保转帐前和转帐后的余额不变 即都是1001元 如何创建事务 T SQL使用下列语句来管理事务 开始事务 BEGINTRANSACTION提交事务 COMMITTRANSACTION回滚 撤销 事务 ROLLBACKTRANSACTION一旦事务提交或回滚 则事务结束 判断某条语句执行是否出错 使用全局变量 ERROR ERROR只能判断当前一条T SQL语句执行是否有错 为了判断事务中所有T SQL语句是否有错 我们需要对错误进行累计 如 SET errorSum errorSum error 关键语句讲