《数据库编程(1).pdf》由会员分享，可在线阅读，更多相关《数据库编程(1).pdf（56页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 )1 数据库原理与应用数据库原理与应用 第第7 7章章 数据库高级应用数据库高级应用 (1)(1)2 复复 习习 SQL Server中的数据类型中的数据类型？函数和过程函数和过程？)3 第第7 7章章 数据库高级应用数据库高级应用 7.1 T-SQL程序设计程序设计 7.2 存储过程存储过程 7.3 触发器触发器 7.4 备份和还原备份和还原 )4 7.1 7.1 T TSQLSQL程序设计程序设计 7.1.1 7.1.1 T T-SQLSQL程序的结构与批处理程序的结构与批处理 7.1.2 7.1.2 变量变量 7.1.3 7.1.3 控制语句控制语句 7.1.4 7.1.4 常用命令常
2、用命令 7.1.5 7.1.5 常用函数常用函数 )5 7.1.1 7.1.1 T T-SQLSQL程序的结构与批处理程序的结构与批处理 T T-SQLSQL语言就是在标准语言就是在标准SQLSQL的基础上进行扩充而推的基础上进行扩充而推 出的出的SQL ServerSQL Server专用专用的结构化的结构化SQLSQL。主要用途：设计服务器端能够在后台执行的程序主要用途：设计服务器端能够在后台执行的程序 块。块。1.Transact-SQL程序的结构程序的结构 例如，打开例如，打开Teach数据库，并从数据表数据库，并从数据表SC中读取中读取学号为“学号为“S1”同学所学课程的平均分，如果
3、该平均同学所学课程的平均分，如果该平均分大于等于分大于等于60分，则程序输出“总评及格”，否则分，则程序输出“总评及格”，否则输出“总评成绩不及格”。输出“总评成绩不及格”。USE Teach -将将教学管理数据库教学管理数据库Teach置为当前置为当前数据库数据库 GO DECLARE AvgScore DECIMAL/*定义变量定义变量AvgScore*/SET AvgScore=60 /*给变量给变量AvgScore赋值初值赋值初值*/IF(SELECT AVG(Score)FROM SC WHERE SNo=S1)=AvgScore -输出结果输出结果 PRINT 总评超过总评超过+C
4、ONVERT(VARCHAR(8),AvgScore)ELSE -输出结果输出结果 PRINT 总评没达到总评没达到+CONVERT(VARCHAR(8),AvgScore)-执行批处理执行批处理 GO )7 7.1.1 7.1.1 T T-SQLSQL程序的结构与批处理程序的结构与批处理 T T-SQLSQL语言就是在标准语言就是在标准SQLSQL的基础上进行扩充而推的基础上进行扩充而推 出的出的SQL ServerSQL Server专用的结构化专用的结构化SQLSQL。主要用途：设计服务器端能够在后台执行的程序主要用途：设计服务器端能够在后台执行的程序 块。块。1.Transact-SQ
5、L程序的结构程序的结构 例如，打开例如，打开Teach数据库，并从数据表数据库，并从数据表SC中读取中读取学号为“学号为“S1”同学所学课程的平均分，如果该平均同学所学课程的平均分，如果该平均分大于等于分大于等于60分，则程序输出“总评及格”，否则分，则程序输出“总评及格”，否则输出“总评成绩不及格”。输出“总评成绩不及格”。可以看出，一个可以看出，一个T T-SQLSQL程序与一般的高级语言程序与一般的高级语言 的语法要素基本一致，包括：的语法要素基本一致，包括：注释、变量与常量、注释、变量与常量、各种运算符、函数与表达式、流程控制语句、批处各种运算符、函数与表达式、流程控制语句、批处 理理
6、等等 。)8 7.1.1 7.1.1 T T-SQLSQL程序的结构与批处理程序的结构与批处理 2.T-SQL程序的批处理程序的批处理 在在T T-SQLSQL程序内，程序内，两个两个“GOGO”标记符之间标记符之间的代码的代码称为一个称为一个“批批”。SQL ServerSQL Server对对T T-SQLSQL程序的编译和执行是按照程序的编译和执行是按照“批批”为单位来进行的，称为为单位来进行的，称为批处理批处理。一个一个T T-SQLSQL程序内可以包含多个程序内可以包含多个“批批”)9 7.1 7.1 T TSQLSQL程序设计程序设计 7.1.1 7.1.1 T T-SQLSQL程
7、序的结构与批处理程序的结构与批处理 7.1.2 7.1.2 变量变量 7.1.3 7.1.3 控制语句控制语句 7.1.4 7.1.4 常用命令常用命令 7.1.5 7.1.5 常用函数常用函数 )10 7.1.2 7.1.2 变量变量 T T-SQLSQL中可以使用两种变量中可以使用两种变量 局部变量局部变量和和全局变量全局变量 1.1.局部变量局部变量 用户可以定义的变量用户可以定义的变量 变量名不区分大小写变量名不区分大小写 必须以必须以 开头开头 先声明后使用先声明后使用 )11 7.1.2 7.1.2 变量变量 (1)(1)局部变量的定义局部变量的定义 DECLARE 变量名变量名
8、数据类型数据类型 ,变量名变量名 数据类型数据类型,(2)(2)局部变量的赋值局部变量的赋值 SET语句语句：SET 局部变量名局部变量名 表达式表达式 SELECT语句语句：SELECT 局部变量名局部变量名 表达式表达式 ,n )12 7.2.2 7.2.2 变量变量 (3)(3)局部变量的输出局部变量的输出 SELECT语句语句：SELECT 局部变量名局部变量名,n PRINT语句语句：PRINT 局部变量名局部变量名 )13 7.2.2 7.2.2 变量变量 例例1定义一个变量，并赋值。定义一个变量，并赋值。declare sno varchar(10),sn varchar(10)
9、set sno=2008110121 set sn=王华王华 select sno,sn Print sno,sn )14 复习复习 变量的声明变量的声明 DECLARE 变量名变量名 数据类型数据类型 变量赋值变量赋值 SET 变量名变量名=表达式表达式 SELECT 变量名变量名=表达式表达式,.n 变量输出变量输出 PRINT 变量名变量名 SELECT 变量名变量名,.n 提示提示：SELECT 通常用于通常用于返回单个值返回单个值；若是；若是返回多返回多个个值，结果会值，结果会怎样？怎样？)15 7.1.2 7.1.2 变量变量 例例2 2 查询学号为查询学号为S7S7学生的姓名和系
10、部。学生的姓名和系部。declare sno varchar(10),v1 varchar(10),v2 varchar(20)SET sno=s7 SELECT Sname,dept FROM Student WHERE sno=sno SELECT v1 AS 姓名姓名,v2 AS 系部系部 V1=V2=)16 7.1.2 7.1.2 变量变量 2.2.全局变量全局变量 是是SQL SERVERSQL SERVER系统内部使用的变量，反应服务器系统内部使用的变量，反应服务器当前的活动状态当前的活动状态 由由系统定义和维护的系统定义和维护的，用户只能使用全局变量，用户只能使用全局变量。全局变
11、量是全局变量是只读只读的。的。必须以必须以“”开头开头 )17 7.1.2 7.1.2 变量变量 2.2.全局变量全局变量 SELECT*Form Student-返回最后执行的返回最后执行的T-SQL 语句的错误代码语句的错误代码 SELECT ERROR /*返回返回SQL上允许的同时用户连接的最大数。返回上允许的同时用户连接的最大数。返回的数不必为当前配置的数值的数不必为当前配置的数值*/SELECT MAX_CONNECTIONS as s )18 7.1.2 7.1.2 变量变量 3.3.注释符注释符 在在T T-SQLSQL中可以使用两类注释符：中可以使用两类注释符：(1)ANSI
12、(1)ANSI标准的注释符标准的注释符 -用于单行注释用于单行注释 (2)(2)与与C C语言相同的程序注释符语言相同的程序注释符 /*/*/*/可在程序中标识多行文字为注释。可在程序中标识多行文字为注释。)19 7.1 7.1 T TSQLSQL程序设计程序设计 7.1.1 7.1.1 T T-SQLSQL程序的结构与批处理程序的结构与批处理 7.1.2 7.1.2 变量变量 7.1.3 7.1.3 控制语句控制语句 7.1.4 7.1.4 常用命令常用命令 7.1.5 7.1.5 常用函数常用函数 )20 7.1.3 7.1.3 控制语句控制语句 BEGIN END IF ELSE 三种使
13、用方式三种使用方式 IF IF ELSE IF 嵌套嵌套 )21 7.1.3 7.1.3 控制语句控制语句 例例33如果如果“数学数学”的平均成绩高于的平均成绩高于6060分，则显示分，则显示“该课该课程教学成绩合格程教学成绩合格”。IF()=60 print 该课程教学成绩合格该课程教学成绩合格!else select 该课程教学成绩不合格该课程教学成绩不合格!Select avg(grade)from SC,Course where Cname=数学数学 and o=o )22 7.1.3 7.1.3 控制语句控制语句 IF NOT EXISTS(SELECT 子子查询查询)ELSE 测试
14、数据是否存在测试数据是否存在 )23 7.1.3 7.1.3 控制语句控制语句 例例44从从S S表中查询学号表中查询学号S1S1的学生信息，如果存在显示的学生信息，如果存在显示该学生信息，否则输出该学生信息，否则输出“不存在该学生信息不存在该学生信息”。IF EXISTS(Select *from S where Sno=S7)Select *from S where Sno=S7 else Print 不存在该学生信息不存在该学生信息!)24 7.1.3 7.1.3 控制语句控制语句 CASE WHEN THEN WHEN THEN ELSE END 有两种有两种形式形式 )25 7.1.
15、3 7.1.3 控制语句控制语句 例例55Select Sn,Sex from S=CASE Sex WHEN 男男 THEN M WHEN 女女 THEN F END )26 7.1.3 7.1.3 控制语句控制语句 例例66Select Sno,Cno,Score FROM SC=CASE WHEN Score IS NULL THEN 缺考缺考 WHEN Score=60 and Score=90 THEN 优秀优秀 END )27 7.1.3 7.1.3 控制语句控制语句 WHILE BEGIN BREAK CONTINUE 命令行或程序块命令行或程序块 END )28 7.1.3 7
16、.1.3 控制语句控制语句 补充补充 显示字符串“显示字符串“China”中每个字符。中每个字符。declare p int,string char(8)set p=1 set string=China while p=datalength(string)begin select substring(string,p,1)set p=p+1 end )29 WAITFOR DELAY -设定等待时间设定等待时间|TIME -设定设定结束时间结束时间点点|ERROREXIT -到处理非正常中断到处理非正常中断|PROCESSEXIT 正常或非正常中断正常或非正常中断|MIRROREXIT 镜像设
17、备失败镜像设备失败 GOTO 标识符标识符 RETURN(整数值整数值)7.1.3 7.1.3 控制语句控制语句 P257-258 取值：取值：0-99 )30 7.1 7.1 T TSQLSQL程序设计程序设计 7.1.1 7.1.1 T T-SQLSQL程序的结构与批处理程序的结构与批处理 7.1.2 7.1.2 变量变量 7.1.3 7.1.3 控制语句控制语句 7.1.4 7.1.4 常用命令常用命令 7.1.5 7.1.5 常用函数常用函数 )31 7.1.4 7.1.4 常用命令常用命令 BACKUP、RESTORE 用于将数据库内容用于将数据库内容或日志或日志备份和恢复操作备份和
18、恢复操作。CHECKPOINT 用于将当前工作的数据库中被更改过的数据页用于将当前工作的数据库中被更改过的数据页或日志页从数据缓冲器中强制写入硬盘。或日志页从数据缓冲器中强制写入硬盘。DBCC 用于验证数据库完整性、查找错误、分析系统用于验证数据库完整性、查找错误、分析系统使用情况等。使用情况等。)32 7.1.4 7.1.4 常用命令常用命令 DECLARE 用于声明一个或多个局部变量、游标变量或表用于声明一个或多个局部变量、游标变量或表变量。变量。DECLARE local_variable data_type|cursor_variable_name CURSOR|table_type_
19、definition ,.n 例如：例如：DECLARE x CHAR,y CHAR(10)SELECT x=123,y=data_type PRINT x )33 7.1.4 7.1.4 常用命令常用命令 EXECUTE 用来执行存储过程。用来执行存储过程。KILL 用于终止某一过程的执行。用于终止某一过程的执行。PRINT 向客户端返回一个用户自定义的信息，即显示向客户端返回一个用户自定义的信息，即显示一个字符串、变量值。一个字符串、变量值。PRINT any ASCII text|local_variable|FUNCTION|string_expression )34 7.1.4 7.
20、1.4 常用命令常用命令 SHUTDOWN 用于停止用于停止SQL Server 的执行的执行 SHUTDOWN WITH NOWAIT READTEXT、WRITETEXT 用于向数据类型为用于向数据类型为TEXT、NTEXT 或或IMAGE 的列中读取和写入数据。的列中读取和写入数据。USE 用于改变当前使用的数据库为指定的数据库用于改变当前使用的数据库为指定的数据库。USE database )35 7.1 7.1 T TSQLSQL程序设计程序设计 7.1.1 7.1.1 T T-SQLSQL程序的结构与批处理程序的结构与批处理 7.1.2 7.1.2 变量变量 7.1.3 7.1.3
21、 控制语句控制语句 7.1.4 7.1.4 常用命令常用命令 7.1.5 7.1.5 常用函数常用函数 )36 7.1.5 7.1.5 常用函数常用函数 1.1.统计函数统计函数 2.2.算数函数算数函数 3.3.字符串函数字符串函数 (1)(1)字符转换函数字符转换函数 ASCII 和和CHAR LOWER 和和UPPER STR(数值表达式数值表达式,长度长度,)(2)(2)去空格函数去空格函数 LTRIM 和和RTRIM select stdev(score)from sc )37 7.1.5 7.1.5 常用函数常用函数 3.3.字符串函数字符串函数 (3(3)取子串函数取子串函数 L
22、EFT、RIGHT、SUBSTRING 4.4.数据类型转换函数数据类型转换函数 CAST CONVERT 5.5.日期函数日期函数 DAY、MONTH、YEAR()DATEADD(,)DATEDIFF(,)T int cast(T as nvarchar(100)convert(nvarchar(100)，T)38 第第7 7章章 数据库高级应用数据库高级应用 7.1 T-SQL程序设计程序设计 7.2 存储过程存储过程 7.3 触发器触发器 7.4 备份和还原备份和还原 )39 7.2 7.2 存储过程存储过程 7.2.1 7.2.1 存储过程概述存储过程概述 7.2.2 7.2.2 创建
23、存储过程创建存储过程 7.2.3 7.2.3 查看、改名、删除存储过程查看、改名、删除存储过程 7.2.6 7.2.6 执行存储过程执行存储过程 7.2.7 7.2.7 修改存储过程修改存储过程 )40 1.1.执行执行T T-SQLSQL的特点的特点 能对能对SQL ServerSQL Server数据库执行操作是哪些语言？数据库执行操作是哪些语言？-只有只有T T-SQLSQL语句语句 高级语言如何访问数据库？高级语言如何访问数据库？-通过调用通过调用T T-SQLSQL语句实现语句实现 T T-SQLSQL语句如何执行？语句如何执行？-由由SQL ServerSQL Server服务器上
24、的负责引擎编译执行服务器上的负责引擎编译执行 -执行执行效率较低效率较低 7.2.1 7.2.1 存储过程概述存储过程概述 (1)(1)每次调用，都是先编译再执行每次调用，都是先编译再执行 (2)(2)应用程序对应用程序对T T-SQLSQL是逐句执行，复杂的是逐句执行，复杂的T T-SQLSQL会产生大的数据流量会产生大的数据流量 )41 2.2.存储过程概念存储过程概念 存储过程的定义存储过程的定义 是一组为了完成特定功能的是一组为了完成特定功能的SQLSQL语句集。语句集。存储过程可以被调用存储过程可以被调用 包括：应用程序、存储过程、客户机管理工具包括：应用程序、存储过程、客户机管理工
25、具 它类似于其他编程语言中它类似于其他编程语言中“过程过程”可以带输入、输出参数可以带输入、输出参数 7.2.1 7.2.1 存储过程概述存储过程概述 )42 3.3.存储过程的优点存储过程的优点 模块化程序设计模块化程序设计 隶属于数据库，应用程序随意调用；隶属于数据库，应用程序随意调用；数据库编程人员创建，可独立修改；数据库编程人员创建，可独立修改；高效率的执行高效率的执行 存储过程在服务器端运行；存储过程在服务器端运行；存储过程执行一遍后，常驻内存；存储过程执行一遍后，常驻内存；减少网络流量减少网络流量 可作为安全机制使用可作为安全机制使用 7.2.1 7.2.1 存储过程概述存储过程概
26、述 )43 4.4.存储过程的分类存储过程的分类 系统存储过程：系统存储过程：前缀是前缀是“sp_sp_”，可以在任何数据库使用，可以在任何数据库使用;本地存储过程：本地存储过程：用户定义的存储过程用户定义的存储过程 扩展存储过程扩展存储过程 用于扩展用于扩展SQL ServerSQL Server服务器功能服务器功能 前缀是前缀是“xp_xp_”7.2.1 7.2.1 存储过程概述存储过程概述 )44 7.2 7.2 存储过程存储过程 7.2.1 7.2.1 存储过程概述存储过程概述 7.2.2 7.2.2 创建存储过程创建存储过程 7.2.3 7.2.3 查看、改名、删除存储过程查看、改名
27、、删除存储过程 7.2.6 7.2.6 执行存储过程执行存储过程 7.2.7 7.2.7 修改存储过程修改存储过程 )45 创建存储过程时，需确定的三个部分创建存储过程时，需确定的三个部分：(1)(1)参数参数 所有的输入参数和输出参数。所有的输入参数和输出参数。(2)(2)内容内容 被执行的针对数据库的操作语句，包括调用其被执行的针对数据库的操作语句，包括调用其他存储过程的语句。他存储过程的语句。(3)(3)返回状态值返回状态值 以指明调用是成功还是失败。以指明调用是成功还是失败。7.2.2 7.2.2 创建存储过程创建存储过程 )46 存储过程的创建存储过程的创建 7.2.2 7.2.2
28、创建存储过程创建存储过程 CREATE PROCEDURE procedure_nameprocedure_name ;number;number parameter parameter data_typedata_typeVARYING=defaultVARYING=defaultOUTPUTOUTPUT ,.n,.n WITHWITH RECOMPILERECOMPILE|ENCRYPTIONENCRYPTION|RECOMPILERECOMPILE,ENCRYPTIONENCRYPTION FOR REPLICATION FOR REPLICATION AS AS sql_stateme
29、ntsql_statement .n .n 重编译重编译 加密加密 )47 7.2.2 7.2.2 创建存储过程创建存储过程 例例1111在在TeachTeach数据库中，创建一个名称为数据库中，创建一个名称为MyProcMyProc的不的不带参数的存储过程，该存储过程的功能是从数据表带参数的存储过程，该存储过程的功能是从数据表S S中中查询所有男同学的信息查询所有男同学的信息。CREATE PROCEDURE MyProc AS SELECT*FROM S WHERE sex=男男 )48 例例1212在在TeachTeach数据库中，创建一个名称为数据库中，创建一个名称为InsRecIns
30、Rec的的存储过程，该存储过程的功能是向存储过程，该存储过程的功能是向S S数据表中插入一数据表中插入一条记录，新记录的值由参数提供。条记录，新记录的值由参数提供。CREATE PROCEDURE InsRec (sno char(6),sn char(20),age numeric(5),sex char(2),dept char(10)AS INSERT INTO S VALUES(sno,sn,age,sex,dept)7.2.2 7.2.2 创建存储过程创建存储过程 )49 7.2 7.2 存储过程存储过程 7.2.1 7.2.1 存储过程概述存储过程概述 7.2.2 7.2.2 创建
31、存储过程创建存储过程 7.2.3 7.2.3 查看、改名、删除存储过程查看、改名、删除存储过程 7.2.6 7.2.6 执行存储过程执行存储过程 7.2.7 7.2.7 修改存储过程修改存储过程 )50 1.1.查看查看 sp_helptext 存储过程名存储过程名 7.2.3 7.2.3 查看、改名、删除存储过程查看、改名、删除存储过程 2.2.重命名重命名 sp_rename 原存储过程名，新存储过程名原存储过程名，新存储过程名 3.3.删除删除 DROP PROCEDURE 存储过程名存储过程名 )51 7.2.6 7.2.6 执行存储过程执行存储过程 EXECUTE 存储过程名存储过程
32、名 参数参数1,参数参数2,例如：例如：EXEC MyProc EXEC InsRecord S1,王华王华,18,男男,计算机系计算机系 或或：EXEC InsRecord sn=王华王华,sno=S1,age=18,dept=计算机系计算机系,sex=男男 例例1313在在TeachTeach数据库中，创建一个名称为数据库中，创建一个名称为InsRecInsRec的的存储过程，该存储过程的功能是向存储过程，该存储过程的功能是向S S数据表中插入一数据表中插入一条记录，新记录的值由参数提供，如果未提供系别条记录，新记录的值由参数提供，如果未提供系别DeptDept的值时，由参数的默认值的值时
33、，由参数的默认值“无无”代替。代替。CREATE PROCEDURE InsRec (sno char(6),sn char(20),age numeric(5),sex char(2),dept char(10)=无无)AS INSERT INTO S VALUES(sno,sn,age,sex,dept)执行：执行：EXEC InsRec S1,王王华华,18,男男,default 或：或：EXEC InsRec sn=王华王华,sno=S1,age=18,sex=男男,dept=default 例例14 14 用于计算指定学生的总分。用于计算指定学生的总分。CREATE PROC tg(
34、sn char(10),total int OUTPUT)AS SELECT total=SUM(grade)FROM student,sc WHERE Sname=sn and student.sno=sc.sno GROUP BY student.sno DECLARE total int EXEC tg 刘晨刘晨,total SELECT 刘晨刘晨,total OUTPUT )54 7.2.7 7.2.7 修改存储过程修改存储过程 ALTER PROCEDURE procedure_name;number procedure_name;number parameter data_type
35、VARYING=defaultOUTPUT parameter data_typeVARYING=defaultOUTPUT ,.n ,.n WITHWITH RECOMPILERECOMPILE|ENCRYPTIONENCRYPTION|RECOMPILERECOMPILE,ENCRYPTIONENCRYPTION FOR REPLICATION FOR REPLICATION AS AS sql_statement .n sql_statement .n 为什么要修改为什么要修改而不新建？而不新建？)55 总结总结 1.存储过程的作用存储过程的作用 2.存储过程的基本操作存储过程的基本操作 创建、执行创建、执行 )56 下课了。下课了。休息。休息。