[工学]第6章※ SQL SERVER 数据库管理系统.ppt

第六章 内容提要,6.1 SQL SERVER 概述 6.2 SQL SERVER 的管理 6.2.1 数据库的管理介绍 6.2.2 数据库的系统表介绍 6.2.3 数据类型介绍 6.3 SQL SERVER 的数据库管理操作 6.3.1 标识符的命名规则 6.3.2 运算符、表达式和语句 6.3.3 存储过程 6.3.4 触发器 6.3.5 函数 6.3.6 游标,6.1 SQL Server 概述,SQL Server是使用客户机/服务器(C/S)体系结构的关系型数据库管理系统（RDBMS）。 1988年推出了第一个OS/2版本。 1992年，SQL Server移植到NT上后，Microsoft成了这个项目的主导者。 1994年以后，Microsoft专注于开发、推广SQL Server的Windows NT版本 1996年，Microsoft公司推出了SQL Server 6.5版本 1998年，推出了SQL Server 7.0版本 2000年，推出了SQL Server 2000。 2005年，推出了SQL Server 2005。 2008年，推出了SQL Server 2008。,6.1 MS SQL Server 概述,MS SQL Server 简介,6.2 SQL SERVER的管理,内容提要,SQL Server数据库对象的管理 SQL Server系统数据表组成 SQL Server的数据类型,6.2.1 SQL Server 数据库管理,Microsoft SQL Server 安装成功后，系统会自动创建4个系统数据库 。这些系统数据库的文件存储在Microsoft SQL Server默认安装目录下的MSSQL子目录的Data文件夹中，数据库文件的扩展名为 .mdf，数据库日志文件的扩展名为 .ldf。 SQL Server 系统的数据库管理功能借助于这些系统数据库来实现。,一、SQL Server系统数据库,1.Master 数据库 记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其它系统数据库及用户数据库的相关信息。 2.Model 数据库 是所有用户数据库和Tempdb数据库的模板数据库。,一、SQL Server系统数据库,6.2.1 SQL Server 数据库管理,3. Tempdb数据库 是一个临时数据库。Tempdb数据库由整个系统的所有数据库使用。SQL Server每次启动时，tempdb数据库被重新建立。当用户与SQL Server断开连接时，其临时表和存储过程被自动删除。 4. Msdb数据库 是代理服务数据库来安排报警、作业并记录操作。,一、SQL Server系统数据库,6.2.1 SQL Server 数据库管理,数据库创建有两条途径： 其一，是企业管理器； 其二，Transact SQL命令方式。 1企业管理器 提供的是可视化的操作界面 2SQL命令方式 命令：CREATE DATABASE 说明： 命令执行的位置，可在查询分析器中；或在前台程序中,6.2.1 SQL Server 数据库管理,二、 数据库的创建,System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(); cn.ConnectionString = “Data Source=NTSERVER0SQL2005; User ID=sa;Password=123456;“; cn.Open(); System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand(); cm.Connection = cn; cm.CommandType = CommandType.Text; cm.CommandText = “CREATE DATABASE MYDB2“; cm.ExecuteNonQuery(); cn.Close();,6.2.1 SQL Server 数据库管理,例：ADO.NET连接方式实现数据库的创建：,二、 数据库的创建,Microsoft 公司从此7.0版本后停止了使用数据库设备的概念（数据备份中仍然使用），直接使用操作系统文件，操作系统文件分为：主要数据文件、次数据文件和日志文件。 主要数据文件：是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。 次要数据文件：包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。 日志文件: 包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。,6.2.1 SQL Server 数据库管理,三、 数据库的文件系统(结构),数据库选项是指用来在某个数据管理范围内有效参数，用于控制数据库的某些特性和行为。 选项的设置方法，有两种： 其一，企业管理器中可视化设置 其二，命令方式 1可视化设置(企业管理器) 2命令方式 sp_dboption dbname = database , optname = option_name , optvalue = value ,6.2.1 SQL Server 数据库管理,四、 数据库的选项,说明： 参数说明 A. dbname = database 在其中设置指定选项的数据库的名称。database 的数据类型为 sysname，默认值为 NULL。 B. optname = option_name 要设置的选项的名称。没有必要输入完整的选项名称。Microsoft SQL Server 可识别名称中任何独有的部分。如果选项名称包含空格或者关键字，请将选项名称用引号引起来。如果省略此参数，sp_dboption 将列出处于打开状态的选项。option_name 的数据类型为 varchar(35)，默认值为 NULL。 C. value = value option_name 的新设置。如果省略此参数，sp_dboption 将返回当前设置。value 可以是 true、false、on 或 off。value 的数据类型为 varchar(10)，默认值为 NULL。,6.2.1 SQL Server 数据库管理,四、 数据库的选项, 举例 自动缩小数据库文件的大小 autoshrink 设为 true sp_dboption MyDB, autoshrink,true 更改数据库的名称 single user设为 true sp_dboption MyDB,single user,true sp_renamedb MyDB,MyDB1 sp_dboption MyDB1,single user,false 批量数据操作 select into/bulkcopy设为 true sp_dboption MyDB1, select into/bulkcopy,true,6.2.1 SQL Server 数据库管理,四、 数据库的选项,1查看数据信息 USE 设置当前数据库 Sp_help db_name 查看指定数据库 Sp_databases _查看所有数据库信息 2数据库更名 Sp_renamedb old_name new_name 说明：单用户模式下才能改。 3修改数据库选项 sp_dboption 4修改数据库的大小,6.2.1 SQL Server 数据库管理,五、 数据库的管理,格式： DROP DATABASE dataname_name 例如： DROP DATABASE MyDb,6.2.1 SQL Server 数据库管理,六、 数据库的删除,数据备份对于数据的安全性起到极为重要的作用。操作方式： 其一，可视化操作 其二，命令方式。 可视化操作： 1、备份 2、还原,6.2.1 SQL Server 数据库管理,七、 数据库的备份和还原,在系统数据库中，事先定义了若干的数据表。这些数据表记录了所有服务器活动的信息。 在系统使用中不能直接修改系统表的内容。其中系统表的数据信息的破坏，有可能会造成难以诊断的错误甚至系统瘫痪。 只能通过系统提供的存储过程和系统函数访问这些据表 。 系统数据表可简单分为两大类：服务器级的(只有master中存在)和数据库级的(所有数据库中都存在)。 SQL Server 2005版本,在SYS后加”.”。,6.2.2 SQL Server 系统表简介,一、 系统表的分类,6.2.2 SQL Server 系统表简介,二、服务器级的系统数据表,6.2.2 SQL Server 系统表简介,二、服务器级的系统数据表,记录了各自数据库的系统信息,6.2.2 SQL Server 系统表简介,三、数据库级的系统数据表,6.2.3 系统数据类型,数据表中的列、局部变量在使用之前必须指定所表示的数据类型。 数据类型大致可以分为系统数据类型、自定义数据类型。 常用的系统数据类型有： 数值型、字符型、日期时间型等,系统数据类型是SQL Server预先定义好的，可以直接使用。 1整型数据类型 （1）int（integer）：4个字节 -231231-1 （2）Smallint ： 2个字节 -215215-1 （3）Tinyint ： 1个字节 0255 （4）Bigint： 8个字节 -263263-1,6.2.3系统数据类型,一、系统数据类型,2位数据类型 Bit称为位数据类型，有两种取值：0和1。 如果一个表中有8个或更少的bit列时，用1个字节存放。如果有916个bit列时，用2个字节存放。 在输入0以外的其它值时，系统均把它们当1看待。,6.2.3系统数据类型,一、系统数据类型,3浮点数据类型 用于存储十进制小数 ，采用只入不舍的方式 （1）real：4个字节的，最大7位精确位数。 （2）float：可以精确到第15位小数，默认占用8个字节的存储空间。 float数据类型也可以写为： float(n) 说明： 其中 n为115之间的整数值。 当n取17时，系统用4个字节存储它； 当n取815时，用8个字节存储它。,6.2.3系统数据类型,一、系统数据类型,4.精确表示的浮点数据类型 Decimal和numeric 可以提供小数所需要的实际存储空间，可以用217个字节来存储。 其格式为decimal(p,s)和numeric(p,s) 说明： p _指明小数点两边总位数，s _小数点后的位数 该数据类型是精确表示的，数的表示范围有p决定。 1 9 5 1019 9 2028 13 2938 17 数值类型的总位数不包括小数。 numeric类型的列可作为唯一标识（identity）; decimal可简写为dec. 例如：decimal(10,4)，表示共有10位数，其中整数6位，小数4位。,6.2.3系统数据类型,一、系统数据类型,5货币数据类型 (1)Money：用于存储货币值，数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213685477.5808 922337213685477.5808，精度为货币单位的万分之一。 (2)Smallmoney:其存储范围为: -214748.3468 214748.3467。 当为money或smallmoney的表输入数据时，可以在有效位置前面加一个货币单位符号（如$或其它货币单位的记号）。,6.2.3系统数据类型,一、系统数据类型,6字符(串)数据类型 用来存储各种字母、数字符号和特殊符号。在使用时需要在其前后加上英文单引号。 （1）Char：占用1个字节。 其定义形式为：char（n） n的取值为18000。默认n的值为1。 （2）Varchar：可以存储长达8000个字符的可变长度字符串，和char类型不同varchar类型根据输入数据的实际长度而变化。 其定义形式为：varchar（n）,6.2.3系统数据类型,一、系统数据类型,（3）Nchar：采用Unicode（统一字符编码标准）字符集每个Unicode字符用两个字节为一个存储单位。 其定义形式为：nchar（n） （4）Nvarchar：使用Unicode字符集的Varchar数据类型。 其定义形式为：nvarchar（n）,6.2.3系统数据类型,一、系统数据类型,7日期和时间数据类型 （1）Datetime：占用8个字节。 用于存储日期和时间的结合体，可以存储从公元1753年1月1日零时起公元9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达三百分之一秒，即3.33毫秒。 当存储datetime数据类型时，默认的格式是：MM DD YYYY hh:mm A.M./P.M。当插入数据或者在其它地方使用datetime类型时，需要用单引号把它括起来。,6.2.3系统数据类型,一、系统数据类型,默认January 1,1900 12:00 A.M。可以接受的输入格式如下：Jan 4 1999、JAN 4 1999、January 4 1999、Jan 1999 4、1999 4 Jan和1999 Jan 4。datetime数据类型允许使用/、-和.作为不同时间单位间的分隔符。 （2）Smalldatetime：存储从1900年1月1日2079年6月6日内的日期。4个字节。,6.2.3系统数据类型,一、系统数据类型,8文本和图形数据类型 （1）Text：容量可以在1231-1个字节。 在定义Text数据类型时，不需要指定数据长度，SQL Server会根据数据的长度自动为其分配空间。 （2）Ntext：采用unicode标准字符集，用于存储大容量文本数据。其理论上的容量为230-1（1，073，741，823）个字节。 （3）Image：用于存储照片、目录图片或者图画，其理论容量为231-1（2，147，483，647）个字节。,6.2.3系统数据类型,一、系统数据类型,9二进制数据类型 （1）Binary： 其定义形式为binary（n），数据的存储长度是固定的，即n+4个字节。二进制数据类型的最大长度（即n的最大值）为8000，常用于存储图像等数据。 （2）Varbinary： 其定义形式为varbinary（n），数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。 在输入二进制常量时，需在该常量前面加一个前缀0x。,6.2.3系统数据类型,一、系统数据类型,10特殊数据类型 （1）Timestamp：也称作时间戳数据类型。是一种自动记录时间的数据类型，主要用于在数据表中记录其数据的修改时间。它提供数据库范围内的唯一值。 （2）Uniqueidentifier：也称作唯一标识符数据类型。Uniqueidentifier用于存储一个16字节长的二进制数据类型，它是SQL Server根据计算机网络适配器地址和CPU时钟产生的全局唯一标识符代码（Globally Unique Identifier，简写为GUID）。,6.2.3系统数据类型,一、系统数据类型,11其它类型 （1）sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQL Server数据。 declare x sql_variant set x= asdfasdf select x set x=1223 select x （2）table：用于存储对表或者视图处理后的结果集。,6.2.3系统数据类型,一、系统数据类型,11其它类型 （3）xml类型：存放xml格式的数据串.,6.2.3系统数据类型,一、系统数据类型,自定义数据类型的创建有两种方式： 1使用企业管理器创建 2利用系统存储过程创建 命令格式： sp_addtype typename= type, phystype= system_data_type , nulltype= null_type , owner= owner_name 说明：,6.2.3系统数据类型,二、自定义数据类型,type：指定用户定义的数据类型的名称。 system_data_type：指定相应的系统提供的数据类型的名称及定义。注意，不能使用timestamp数据类型，当所使用的系统数据类型有额外说明时，需要用引号将其括起来。 null_type：指定用户自定义数据类型的null属性，其值可以为null、not null或者nonull。默认与系统默认的null属性相同。 owner_name：指定用户自定义数据类型的所有者。 例如： sp_addtype nametype varchar(50) null,6.2.3系统数据类型,二、自定义数据类型,删除用户自定义数据 1使用企业管理器 2利用系统存储过程 命令格式： sp_droptype typename= type,6.2.3系统数据类型,二、自定义数据类型,举例：,EXEC sp_addtype name, VARCHAR(20), NOT NULL EXEC sp_addtype birthday, datetime, NULL EXEC sp_addtype telephone, varchar(24), NOT NULL EXEC sp_addtype fax, varchar(24), NULL EXEC sp_addtype address, varchar(100), NULL,6.2.3系统数据类型,二、自定义数据类型,6.3 SQL SERVER 的数据库管理操作,内容提要 标识符的命名规则 运算符、表达式和语句 存储过程 触发器 函数 游标,1、定义 用来表示特定意义的字符序列 例如：select -查询命令字 成绩表 -表示学生成绩信息 12 -表示数值12 xyx -表示字符串 2、保留字 在系统中具体特殊定义的标识符。 例如： table select set if else sum where int bit,6.3.1 SQL Server的命名规则,一、标识符,3、常量表示 数 值： 同其他语言 字符串： 前后加单引号 日期时间： 前后加单引号，引号之间按日期时间的格式写即可。 说明： 在表中添加数据时则不需要单引号。 4、自定义表示对象、变量的标识符 如果不特别说明后面所说的标识符均是指该类标识符号。,一、标识符,6.3.1 SQL Server的命名规则,在 MS SQL Server 中标识符的命名方式有两种：规则标识符和定界标识符。 规则标识符：遵守一般标识符有关格式的规定。即： 以字母（汉字）、_、#、开始的字符（包含字母(汉字)、_、#、 、数字、$）序列。 例如： My I X1 x_1 CJB i x #tdb #xyz 学生信息表、学号 等等 说明： 1)、标准标识符格式 下一页,二、自定义标识符的命名,6.3.1 SQL Server的命名规则,标识符的首字母必须是下列两种情况之一： 英文字母(汉字) _、 、# 标识符的首字母以后可以是： 英文字母(汉字) _、 、#、$ 0-9 标识符不能是SQL 的保留字 标识符号内部不容许有空格或特殊字符（运算符等）。,二、自定义标识符的命名,6.3.1 SQL Server的命名规则,2、自定义标识符命名具有特定的含义 开始的表示全局变量 开始的表示局部变量 # 开始的表示全局对象 # 开始的表示局部对象 以其他字母开始的 ，表示永久对象 以sp_开始的为系统存储过程 以xp_开始的为系统扩展存储过程。,二、自定义标识符的命名,6.3.1 SQL Server的命名规则,定界标识符：使用 或“”定界符括起来的符号，定界符内可以规则标识符也可以是非规则标识符。 例如： x -表示对象 等价于 x xh -表示学号 等价于 x zh ang -中间有空格的标识符 js+xs -中间有运算符的标识符 select -标识符为关键字 12 -标识符为关键字,二、自定义标识符的命名,6.3.1 SQL Server的命名规则,6.3.2 SQL Server 编程结构,一、算术运算 +、-、*、/、%(求余) 二、关系运算 、=、或!= 三、逻辑运算,运算符和表达式,6.3.2 SQL Server 编程结构,运算符和表达式,说明： ANY|SOME|ALL 用于标量值与单列集中的值进行比较。 格式： = | | != | | = | ! | 60 等价于 Exists(SELECT CJ FROM CJK WHERE KCMC=计算机 AND CJ60),运算符和表达式,6.3.2 SQL Server 编程结构,例2：判断成绩表中是否所有成绩均不超过91分。,运算符和表达式,6.3.2 SQL Server 编程结构,declare sf bit if 91 all (select cj from cjb) set sf=1 else set sf=0 select sf,6.3.2 SQL Server 编程结构,四、位运算 （同C+） &(与) |(或) (异或) (求反) 五、串运算 + 连接 说明： DECLARE XM CHAR(20) SET XM=张三 SELECT XM + 是个优秀的同学. 将CHAR(20)改为VARCHAR(20)情况有如何？,运算符和表达式,6.3.2 SQL Server 编程结构,运算符的优先级： 当一个复杂的表达式有多个运算符时，运算符优先性决定执行运算的先后次序。执行的顺序可能严重地影响所得到的值。 运算符有下面这些优先等级。在较低等级的运算符之前先对较高等级的运算符进行求值。 +（正）、-（负）、（按位 NOT） *（乘）、/（除）、%（模） +（加）、（+ 串联）、-（减） =, , =, , !=, !, ! 比较运算符 （位异或）、&（位与）、|（位或） NOT AND ALL、ANY、BETWEEN、IN、LIKE、OR、SOME =（赋值）,运算符和表达式,6.3.2 SQL Server 编程结构,1变量的分类 在SQL SERVER 中变量分为两类： 局部变量：以一个开始的字母数字串。 生存期：本模块或批处理中 全局变量：以两个开始的字母数字串。 生存期：本次系统(SQL SERVER服务器) 启动到退出系统 2变量的定义 DECLARE , 例： DECLARE I INT,j INT DECLARE str VARCHAR(20),语句,一、变量的定义及赋值,6.3.2 SQL Server 编程结构,3. 变量的赋值 A简单的赋值 SELECT = SET = 例： SELECT I=100 SET str=张 B取数据表中的数据赋值局部变量 SELECT =， FROM 例： SELECT str=XM FROM DAB WHERE XH=1,语句,一、变量的定义及赋值,6.3.2 SQL Server 编程结构,说明： 若非局部变量，采用的上述语句，将返回结果为记录集，列名为该标识符。 例： SELECT zt=0 SELECT name=XM FROM DAB WHERE XH=1 4全局变量 是用来记录SQL SERVER服务器活动状态的一组数据。 在SQL SERVER 中可以直接使用。,语句,一、变量的定义及赋值,6.3.2 SQL Server 编程结构,常用的全局变量： ERROR 返回最后执行的SQL 语句的错误代码。0表示执行正确，非零表示错误代码。 ROWCOUNT 返回上一语句影响的行数。 CURSOR_ROWS 返回连接上最后打开的游标中当前存在的合格行的数量。可调用 CURSOR_ROWS，以确定当它被调用时，符合游标的行的数目被进行了检索。,语句,一、变量的定义及赋值,6.3.2 SQL Server 编程结构,LOCK_TIMEOUT 返回当前会话的当前锁定超时设置，单位为毫秒。 注： SET LOCK_TIMEOUT 允许应用程序设置语句等待阻塞资源的最长时间。当一条语句已等待超过 LOCK_TIMEOUT所设置的时间，则被锁住的语句将自动取消，并给应用程序返回一条错误信息。 在一个连接的开始，LOCK_TIMEOUT 返回一个 1值。,语句,一、变量的定义及赋值,6.3.2 SQL Server 编程结构,LOCK_TIMEOUT 返回当前会话的当前锁定超时设置，单位为毫秒。 注： SET LOCK_TIMEOUT 允许应用程序设置语句等待阻塞资源的最长时间。当一条语句已等待超过 LOCK_TIMEOUT所设置的时间，则被锁住的语句将自动取消，并给应用程序返回一条错误信息。 在一个连接的开始，LOCK_TIMEOUT 返回一个 1值。,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,1IF语句 格式： IF SQL语句块1 ELSE SQL语句块2 说明: A. SQL语句块 必须为单条命令, 若存在多条命令则必须加语句括号,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,BEGIN 语句1 语句2 END B. 结果为布尔型的表达式. 允许为: 一般表达式 i100 含SELECT 语句的表达式,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,(select avg(cj) from cjb )90 SOME(SELECT CJ FROM CJB WHERE KCMC=JSJ)90 (SELECT AVG(CJ) FROM CJB WHERE KCMC=JSJ)80 SET I=(SELECT COUNT(*) FROM CJB WHERE KCMC=JSJ) exists(select * from sysobjects where name=dab and type=U) WHERE 条件表达式中能使用的表达式.,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,2. WHILE 语句 _ 循环语句 格式: WHILE 语句块 -循环体 说明: 语句块必须为单条语句(命令), 若存在多条命令则必须加语句括号 BEGIN 语句1 语句2 END,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,B. 循环体中可以使用如下命令: BREAK _ 结束循环语句 CONTINUE _ 跳过本次循环. 3. GOTO 语句 _ 无条件转向语句 格式: GOTO 说明: 标号的设置,为: 标号: 4. RETURN 语句 用于存储过程,无条件退出存储过程. 格式: RETURN n 说明: n为返回值,缺省 0 表示存储过程执行正确,非0表示有问题发生.,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,5. CASE 表达式 _ 类似于IIF 格式1: CASE WHEN THEN WHEN THEN .n ELSE END,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,格式2: CASE WHEN THEN WHEN THEN .n ELSE END,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,例: select xm,xb=CASE sex when 1 THEN 男 ELSE 女 END from dab Select xh,kcmc,cj=CASE when cj=90 then 优 when cj=80 then 良 when cj=70 then 中 when cj=60 then 及格 else 不及格 end From cjb,语句,二、常用语句(命令),6.3.2 SQL Server 编程结构,1批处理是指从客户机传送到服务器上的一组完整的数据和SQL指令。 2特点 一个批处理可以只包含一条SQL指令，也可以包含多条SQL指令； 批处理的所有语句被当为一个整体，成组地分析、编译和执行。 若干的SQL语句，只能单独存在一个批中。 CREATE DEFAULT CREATE PROCEDURE CREATE RULE CREATE TRIGGER CREATE VIEW 3批处理的定义 SQL SERVER语句以GO语句作为批处理结束标志。,语句,三、批处理,6.3.3 存储过程,1定义 存储过程是一组事先编译好的SQL代码。 等效于其它编程语言中子程序。 2 为什么使用存储过程？ 执行速度快 存储过程在创建时经过了编译和优化，执行时不在重复该操作而直接执行。（命令执行时必须经过编译和优化） 采用模块化的程序设计 一次创建，无数次调用；将若干命令组合在一起，可实现复杂功能。 存储过程在数据库中，独立于前台应用程序有利于对数据库操作的模块化。,概述,6.3.3 存储过程, 减少网络通信量 存储过程在数据库中，应用程序只需发出一个调用存储过程的命令即可，减少命令的传输。 保证系统的安全 允许设置只允许通过存储过程访问，而不允许直接访问数据库。 3分类,概述,6.3.3 存储过程, 系统存储过程 有SQL SERVER 系统定义的能直接使用的功能模块。 系统存储过程命名以：SP_开始。 系统存储过程保存在master主控数据库中。 另一类有C/C+编写的外部存储过程，称为系统扩展存储过程，以xp_开始。 自定义存储过程 有用户为完成特定的功能而编写的功能模块。,概述,6.3.3 存储过程,格式： CREATE PROCEDURE ； 参数1，参数2， WITH ENCRYPTION AS SQL语句 说明： 1存储过程 为数据库的对象，也遵循对象的命名格式： 服务器.数据库.拥有者.，通常简写为 表明为当前服务器当前数据库的DBO拥有者下的存储过程。 2存储过程名决定了存储过程的存在周期,自定义存储过程,一、创建存储过程,6.3.3 存储过程, 是指存储过程名相同的多个存储过程为区别加的序号。 3参数1，参数2， 参数i 的格式: 参数名 数据类型 = OUTPUT A. 参数名 为局部变量，以开始。 B. 数据类型可以是除image类型外的其它类型。 C. =default 缺省值，调用存储过程时，未给参数赋值时，参数取默认值。 D. OUTPUT 该参数可以从SQL SERVER 中返回值。,自定义存储过程,一、创建存储过程,6.3.3 存储过程,4. WITH ENCRYPTION -加密存储过程体 5过程体 由一条或若干的SQL 语句构成。 * 在过程体中不能包含如下语句：,自定义存储过程,一、创建存储过程,* 可有创建其他数据库对象的语句，也可以引用同一过程中创建的对象。 * 过程体中可以包含调用其它存储过程的语句，其嵌套调用最多可达32层。,6.3.3 存储过程,6存储过程的定义语句必须单独一条语句作为一个批处理。即，过程体后应加“GO” 例1 已知一数据库MyDB中有一数据表dab(myid,xh,xm,dz),编写一储存过程，实现数据的插入。,自定义存储过程,一、创建存储过程,6.3.3 存储过程,自定义存储过程,一、创建存储过程,If exists(select * from sysobjects where name =Insert_dab and type=P) DROP PROCEDURE Insert_dab GO CREATE PROCEDURE Insert_dab xh varchar(10), xm varchar(20)=null, dz varchar(100)=null AS INSERT INTO DAB( XH, XM, DZ) VALUES(XH,XM,DZ) GO,6.3.3 存储过程,例2 已知一数据库MyDB中有一数据表dab(myid,xh,xm,xb),编写一储存过程，取出所有的数据。,自定义存储过程,一、创建存储过程,If exists(select * from sysobjects where name= SelectAll_dab and type=P) DROP PROCEDURE SelectAll_dab GO CREATE PROCEDURE SelectAll_dab AS SELECT * FROM DAB GO,6.3.3 存储过程,1提交（编译） 将存储过程的源代码在查询分析器中运行即可。 2删除 DROP PROCEDURE 3查看 sp_help 说明：只有未加密的存储过程才能查看其源代码。 4修改存储过程,自定义存储过程,二、储存过程的管理,6.3.3 存储过程,格式：ALTER PROCEDURE ； 参数1，参数2， WITH ENCRYPTION AS SQL语句,自定义存储过程,二、储存过程的管理,6.3.3 存储过程,格式： EXECUTE 参数列表 说明： 1参数列表 参数1=,参数2=, *参数i= 缺省时，则按存储过程参数定义的顺序依次赋值，故顺序必须保持一致，若不缺省,参数顺序可以任意。 例 求两个数的差. * 存储过程定义时设置了缺省值的参数，在参数i=缺省情况下，只能最后连续若干的参数可以忽略；而带有“参数i=”的参数表中可以忽略任意位置上设置了缺省值的参数。 例. 求三个数的平均值.,自定义存储过程,三、存储过程的执行 _ SQL SERVER 中的调用,6.3.3 存储过程,2 返回值问题 存储过程中的返回值通常存在这样几种情况： A通过参数返回 在存储过程定义时，参数定义加上“OUTPUT”的参数为变参，如果调用时为变量，并加上了“OUTPUT”，则该变量回返回存储过程中赋给它的值。 若为表达式或调用时没有带“OUTPUT”，则不能返回值。 B返回记录集 在存储过程中，出现非赋值语句的SELECT语句，即为查询语句时，系统会返回查询的结果集。 存在多条这样的语句时，系统将返回多个查询结果集。 CRETURN 返回 存储过程可通过RETURN语句返回存储过程的运行状态。,自定义存储过程,三、存储过程的执行 _ SQL SERVER 中的调用,6.3.3 存储过程,假设学生管理系统中，存在如下定义数据表 1. 学生档案信息(学号、姓名、性别、地址、学院名称、班级名称) 2. 学生成绩(学号、课程名称、考试日期、任课教师、成绩) 3. 学习课程安排(课程名称、课程学时、课程类型) 定义存储过程要求完成如下任务： 1给出学生的学号返回学生的学院名称； 2给出学生的学号和课程，求成绩； 3求某年级学生的某一门课程的成绩,自定义存储过程,四、应用举例,6.3.4 触发器,1定义： 触发器是一种特殊类型的存储过程，当数据发生改变时会自动运行。 2应用特点 A与数据表数据变化有密切关系。 B响应操作：INSERT、UPDATE、DELETE（、SELECT） C触发器可设置对相关数据表的修改以及对响应操作的拒绝。,触发器,触发器,6.3.4 触发器,1格式1： CREATE TRIGGER ON WITH ENCRYPTION FOR DELETE,INSERT,UPDATE AS SQL语句 说明： A触发器必须基于某一数据表。 BWITH ENCRYPTION 对触发器原代码加密。 CFOR DELETE,INSERT,UPDATE 触发器的条件，可为：DELETE、INSERT、UPDATE或其组合。,触发器,一、创建触发器,6.3.4 触发器,DSQL语句： 可为一个SQL语句，也可为多个SQL语句。 触发器中不允许以下 Transact-SQL 语句：,触发器,一、创建触发器,6.3.4 触发器,2格式2 CREATE TRIGGER ON WITH ENCRYPTION FOR INSERT | UPDATE AS IF UPDATE(列名) AND|OR UPDATE(列名) SQL语句 GO 说明： A对数据表插入或修改时，某一列或某几列发生变化时执行。 B测试指令列： UPDATE(列名) AND|OR UPDATE(列名),触发器,一、创建触发器,6.3.4 触发器,3特殊临时表：DELETED和INSERTED 作用 测试激活触发器的操作对数据表的影响。 INSERT操作：除放于数据表外，还将放于INSERTED表中。 DELETE操作：删除数据放于DELETED中。 UPDATE操作：原数据放于DELETED中， 新数据放于INSERTED中。 特点 DELETED和INSERTED中数据不能修改，但可以查询分析。,触发器,一、创建触发器,6.3.4 触发器,4多行数据的处理问题 一条INSERT/UPDATE/DELETE语句只能触发触发器一次，但一个触发器的动作可影响多行数据。 A影响的数据行数： ROWCOUNT 来表示。 B影响数据的判断 相应存放于INSERTED和DELETED中。 5触发器的拒绝回滚 触发器作为一个事务，在触发器中运行ROLLBACK TRANSACTION 语句将回滚整个触发器的操作。,触发器,一、创建触发器,6.3.4 触发器,例1、建立一个触发器，当插入学号为10时取消插入。 INSERT dab(xh,xm,xb,jg,bjmc) VALUES(10,王超,1,北京,计算机二班) CREATE TRIGGER DAB_TRI ON DAB WITH ENCRYPTION FOR INSERT AS IF EXISTS( SELECT * FROM INSERTED WHERE XH=10) ROLLBACK TRANSACTION GO,触发器,一、创建触发器,6.3.4 触发器,例2 删除档案表中某学生的信息时，自动删除成绩表中相应学生的成绩。 CREATE TRIGGER DAB_TRI ON DAB WITH ENCRYPTION FOR DELETE AS DELETE FROM CJB WHERE XH IN (SELECT XH FROM DELETED) GO,触发器,一、创建触发器,6.3.4 触发器,例3 修改学生学号时，自动修改成绩表中的学号 ALTER TABLE DAB ADD Myid int identity CREATE TRIGGER DAB_TRI1 ON DAB WITH ENCRYPTION FOR UPDATE AS IF UPDATE(XH) UPDATE CJB SET XH=NEWXH FROM CJB RIGHT JOIN (SELECT A.XH AS OLDXH,B.XH AS NEWXH FROM DELETED A INNER JOIN INSERTED B ON A.MYID=B.MYID ) C ON CJB.XH=C.OLDXH,触发器,一、创建触发器,6.3.4 触发器,1修改触发器 格式说明：将创建格式中的CREATE改为ALTER即可 2删除触发器 格式： DROP TRIGGER , 3. 查看触发器 sp_helptrigger ,类型 说明: 类型是指INSERT、UPDATE、DELETE，缺省则返回所有类型的触发器。,触发器,二、触发器的管理,五 函数,第一节 系统函数,一、数学函数,6.3.5 函数,第一节 系统函数,二、字符串函数,6.3.5 函数,第一节 系统函数,三、日期函数,6.3.5 函数,第一节 系统函数,四、其它函数,6.3.5 函数,SQL Server 支持三种用户定义函数： 标量函数 内嵌表值函数 多语句表值函数,第二节 自定义函数,概述,标量函数： 返回在 RETURNS 子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括 bigint 和 sql_variant。不支持 timestamp 、 text 、 ntext、image、cursor数据类型、用户定义数据类型和非标量类型（如 table 或 cursor）。,6.3.5 函数,表值函数 返回 table类型。 对于内嵌表值函数，没有函数主体，表是单个 SELECT 语句的结果集。 对于多语句表值函数，在 BEGIN.END 块中定义的函数主体包含 SQL 语句序列，这些语句可生成行(记录)并将行插入到将返回的表中。,第二节 自定义函数,概述,6.3.5 函数,格式：CREATE FUNCTION ( 参数1 类型 =默认值, 参数2 类型 =默认值, ) RETURNS 返回值类型 WITH ENCRYPTION AS BEGIN 函数体 END,第二节 自定义函数,一、 标量函数,6.3.5 函数,说明： 1返回值为某种类型的值，可以是除text、image、timestamp之外的任何类型。 2函数体中必须存在一条返回语句，即： RETURN _函数体最后 3函数的调用位置 可以在数据表列名出现的位置使用函数。 4调用格式： .函数名（）,第二节 自定义函数,一、 标量函数,6.3.5 函数,第二节 自定义函数,一、 标量函数,定义函数： IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME=GET_NEWCJ AND TYPE=F) DROP FUNCTION GET_NEWCJ GO CREAT