《第4章 tsql编程.ppt》由会员分享，可在线阅读，更多相关《第4章 tsql编程.ppt（34页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第4章章 Transact-SQL语言编程语言编程学习目标学习目标:掌握掌握transact-sql编程知识编程知识 综合运用变量、表达式、函数以及流控语句等编写程序代码综合运用变量、表达式、函数以及流控语句等编写程序代码重点：重点：掌握常用函数及流控语句的使用掌握常用函数及流控语句的使用,并学会编写并学会编写sql server 2000程序代码程序代码难点：难点：掌握常用函数及流控语句的使用掌握常用函数及流控语句的使用,并学会编写并学会编写sql server 2000程序代码程序代码TransactSQL的语法规则的语法规则大写：关键字斜体或小写字母：用户提供的参数|：分隔括号或大括号
2、内的语法项目。只能选择一个项目：可选语法项目：必选语法项目()：语句的组成部分，必须输入,n:表示前面的项可重复n次，每一项由逗号隔开n:表示前面的项可重复n次，每一项由空格隔开加粗：数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及必须按所显示的原样键入的文本。引用数据库对象名的规则引用数据库对象名的规则Server_name.database_name.owner_name.object_nameDatabase_name.owner_name.object_nameowner_name.object_nameobject_name注：对象的名字可由1128个字符组成，不区分
3、大小写。不允许存在4部分名称完全相同的数据库对象。在同一个数据库里可以存在两个名为EXAMPLE的表格，但前提条件必须是这两个表的拥有者不同Transact-SQL的语法元素的语法元素1.标识符：用来标识服务器、数据库和数据库对象标识符：用来标识服务器、数据库和数据库对象 的名称的名称常规标识符：第一个字符必须是下列字符之一：常规标识符：第一个字符必须是下列字符之一：a-z和和A-Z，以及来自其他语言的字母字符或以及来自其他语言的字母字符或 者下划线者下划线_、或或#。其他字符可以是字母、数字、。其他字符可以是字母、数字、$、#或下划线（内部不允许有空格或特殊字符，不允许是保或下划线（内部不允
4、许有空格或特殊字符，不允许是保留字）留字）以以开始的标识符：表示开始的标识符：表示局部局部变量或参数变量或参数以以#开始的标识符：表示开始的标识符：表示临时临时表或过程表或过程以以#开始的标识符：表示开始的标识符：表示全局全局临时变量临时变量以以开始的标识符：表示开始的标识符：表示全局全局变量变量分隔标识符：对不符合所有标识符规则的标识分隔标识符：对不符合所有标识符规则的标识符必须进行分隔，将其包含在双引号符必须进行分隔，将其包含在双引号“或方括或方括号号 内，如内，如ORDER注注:transact-sql 的保留字不能用来做标识符的保留字不能用来做标识符.标识标识符中也不允许有空格符中也不
5、允许有空格.2.数据类型：有系统数据类型与用户定义的数据数据类型：有系统数据类型与用户定义的数据类型类型3.函数：可以有函数：可以有0个，个，1个或多个参数，并返回一个或多个参数，并返回一个值或值的集合个值或值的集合4.表达式表达式5.运算符运算符6.注释：注释：只能注释一行代码：只能注释一行代码/*.*/：可注释大断的代码：可注释大断的代码7.保留字：保留字：3.2 函数函数SQL Server提供的函数有：聚合函数、提供的函数有：聚合函数、配置函数、游标函数、日期函数、数学配置函数、游标函数、日期函数、数学函数、元数据函数、函数、元数据函数、行集函数、安全函行集函数、安全函数、字符串函数、
6、系统函数、文本与图数、字符串函数、系统函数、文本与图像函数像函数 一、聚合函数（统计函数）：对一组值进行计算并返一、聚合函数（统计函数）：对一组值进行计算并返回一个数值回一个数值SUM(ALL|DISTINCTexpression）：）：求和求和MIN(ALL|DISTINCTexpression）：）：求最小值求最小值MAX(ALL|DISTINCTexpression）：）：求最大值求最大值COUNT(ALL|DISTINCTexpression|*）：）：计算总行数计算总行数Count(*):包括含有空值的行，同时不能与包括含有空值的行，同时不能与distinct一起使用一起使用例：例：
7、select count(departname)as 系数系数 from department(可先可先删除一个系，使该行为空）删除一个系，使该行为空）select count(distinct*)as 系数系数 from department /出现错误信出现错误信息息AVG(ALL|DISTINCTexpression）:求平均值求平均值注意注意:select,group by,having 可以使用可以使用聚合函数聚合函数,where不不行行例例3.1 计算计算course表的表的总行数总行数 SELECT COUNT(*)AS Course表的总行数表的总行数 FROM Course例
8、例3.2 计算计算course表各门课程限选人数的表各门课程限选人数的总人数总人数SELECT SUM(LimitNum)AS 限选人数总人数限选人数总人数FROM Course例例3.3 统计统计course表中各门课程中表中各门课程中最少最少报名人数、报名人数、最多最多报名人数和报名人数和平均平均报名人数报名人数SELECT MIN(WillNum)AS 最少报名人数最少报名人数,MAX(WillNum)AS 最多报名人数最多报名人数,AVG(WillNum)AS 平均报名人数平均报名人数FROM Course二、字符串函数：用于对字符串进行连接、截取等操作二、字符串函数：用于对字符串进行
9、连接、截取等操作ASCII(字符串表达式字符串表达式)：返回字符表达式最左边字符的：返回字符表达式最左边字符的ASCII码码 如：如：例例 3.5CHAR(整型表达式）：将一个整型表达式）：将一个ASCII码转换为字符。码转换为字符。Len(字符串表达式）：返回给定字符串数据的长度，不计算字符串表达式）：返回给定字符串数据的长度，不计算尾部的空格。尾部的空格。如：如：select len(123)例例3.6 Left(字符型表达式，整型表达式）：字符型表达式，整型表达式）：返回该字符型表达式返回该字符型表达式最最左边左边给定整数个字符给定整数个字符.如：如：select left(123,)R
10、ight(字符型表达式，整型表达式）：字符型表达式，整型表达式）：返回该字符型表达式返回该字符型表达式最最右边右边给定整数个字符给定整数个字符.Substring(字符串，起始点，字符串，起始点，n）:返回该字符串从起始点开返回该字符串从起始点开始的始的n个字符。个字符。如如:Select x=substring(abcdef,2,3)STR(浮点表达式浮点表达式，长度，长度，小数，小数 )：将浮点表达式转换为：将浮点表达式转换为所所 给定长度的字符串。给定长度的字符串。如：如：select str(123,2),str(123,3),str(123,4),str(123.12,5,1)Upp
11、er(字符型表达式）：将字符型表达式全部转化为字符型表达式）：将字符型表达式全部转化为大写大写形式形式Lower(字符型表达式）：将字符型表达式全部转化为字符型表达式）：将字符型表达式全部转化为小写小写形式形式Space(整型表达式）：返回由给定整数个空格组成的字符串整型表达式）：返回由给定整数个空格组成的字符串Replicate(字符型表达式，整型表达式）：将给定的字符型表字符型表达式，整型表达式）：将给定的字符型表达式的值复制给定的整数次数。达式的值复制给定的整数次数。如：例如：例3.9Stuff(字符型表达式字符型表达式1，开始位置，长度，字符型表达式，开始位置，长度，字符型表达式2）：
12、）：将字符型表达式将字符型表达式1从开始位置截断给定长度的子串，然后将字符从开始位置截断给定长度的子串，然后将字符型表达式型表达式2从开始位置补充进去。从开始位置补充进去。如：如：Stuff(abcdef,2,4,hijkl),例例3.10Differences(字符型表达式字符型表达式1，字符型表达式，字符型表达式2):返回两个字符表返回两个字符表达式发音的相似程度达式发音的相似程度(0-4)。4发音最相似发音最相似.如：例如：例3.8Reverse(字符型表达式字符型表达式)：返回一个与给定字符型表达式恰：返回一个与给定字符型表达式恰好好顺序颠倒顺序颠倒的字符型表达式的字符型表达式,即逆序
13、即逆序Ltrim(字符型表达式）：返回删除给定字符串字符型表达式）：返回删除给定字符串左端左端空白后空白后的字符串值的字符串值Rtrim(字符型表达式）：返回删除给定字符串字符型表达式）：返回删除给定字符串右端右端空白后空白后的字符串值的字符串值Charindex(字符型表示式字符型表示式1，字符型表达式，字符型表达式2开始位置开始位置)：从：从指定位置开始，在指定位置开始，在字符型表达式字符型表达式2中查找字符型表达式中查找字符型表达式1，如果找到则返回字符型表达式如果找到则返回字符型表达式1在字符型表达式在字符型表达式2中的开始中的开始位置，默认的开始位置是位置，默认的开始位置是1），），
14、即定位即定位.如：例如：例3.4,3.7Patindex(%pattern%,字符型表达式）：在字符型表达式中字符型表达式）：在字符型表达式中查找给定格式的字符串，如果找到则返回该给定字符串在字符查找给定格式的字符串，如果找到则返回该给定字符串在字符型表达式中的开始位置，否则返回值为型表达式中的开始位置，否则返回值为0 例：例：use pubs /*pubs是示例数据库是示例数据库*/go select au_lname,au_id from authors where patindex(%-2%,au_id)0 go 将画横线部分改为将画横线部分改为charindex(-2,au_id)0,
15、试比较试比较 运行结果运行结果 例例3.4 给出给出“数据库数据库”在在“大型数据库技术大型数据库技术”中的位置中的位置 SELECT CHARINDEX(数据库数据库,大型数据库技术大型数据库技术)例例3.5 返回返回“Alklk”最左边字符最左边字符“A”的的ASCII码码 SELECT ASCII(Alklk)例例3.6 计算字符串计算字符串“SQL Server数据库管理系统数据库管理系统”的长度的长度 SELECT LEN(SQL Server数据库管理系统数据库管理系统)例例3.7 查找字符串查找字符串“wo”在在“MY wonderful”中的开始位置中的开始位置 SELECT
16、CHARINDEX(wo,MY wonderful)SELECT CHARINDEX(wo,MY wonderful,5)SELECT CHARINDEX(wo,MY wonderful,0)例例3.8 观察观察“Hello worle”与与“hello wild”的发音相似程度的发音相似程度 SELECT DIFFERENCE(Hello World,Hello Wild)例例3.9 select replicate(Hello,2),space(10),replicate(world,2)例例3.10 select stuff(He rld,3,1,llo Wo）三、日期函数：用于显示日期
17、和时间的信息三、日期函数：用于显示日期和时间的信息Getdate()：返回当前的系统时间返回当前的系统时间 如：如：select getdate()/*显示服务器当前的系统日期和时间显示服务器当前的系统日期和时间*/Datepart(datepart,date):以整数形式返回给定以整数形式返回给定date型数据的型数据的指定日期部分指定日期部分 如：如：select detepart(weekday,getdate()datename(datepart,date):以字符串形式返回给定以字符串形式返回给定date型数型数据的指定日期部分据的指定日期部分 如：如：select detename
18、(weekday,getdate()Dateadd(detepart,number,date):将日期元数加上日期将日期元数加上日期,得到得到一个新的日期。一个新的日期。如：如：select dateadd(day,7,getdate()select dateadd(month,7,getdate()Datediff(datepart,start,enddate):返回开始日期和结束返回开始日期和结束日期在给定日期元数上的差值日期在给定日期元数上的差值 如：如：select datediff(dd,2006-3-8,getdate()例例3.13 Mary的生日为的生日为1979/12/23日
19、，使用日期函数计日，使用日期函数计算算mary现在的年龄现在的年龄 select 年龄年龄=datediff(yy,1979/12/23,getdate()Day(date):返回某月几号的整数值返回某月几号的整数值month(date):返回月份返回月份year(date)：返回年份返回年份如：如：select day(getdate()Select year(2006-12-3)四、系统函数：用来获取四、系统函数：用来获取SQL Server的有关信息的有关信息Cast(表达式 as data_type)：将表达式显示转换为另一种数据类型。Convert(data_type,express
20、ion):与cast 相似的功能。如：select cast(65 as char)select convert(char,65)HOST_ID():返回主机标识HOST_NAME()：返回主机名称 如：SELECT HOST_NAME()五、数学函数：用来对数值型数据进行数学运算五、数学函数：用来对数值型数据进行数学运算ABS（数值表达式）：返回表达式的绝对值。ACOS（浮点表达式）：返回浮点表达式的反余弦值ASIN(浮点表达式)：返回浮点表达式的反正弦值CEILING（数值表达式）：返回大于或等于数值表达式的最小整数。FLOOR（数值表达式）：返回小于或等于数值表达式的最大整数。如：SEL
21、ECT CEILING(134.393)/*135 SELECT FLOOR(134.3393)/*134lSQUARE（浮点表达式）：返回浮点表达式的平方lSQRT（浮点表达式）：返回浮点表达式的平方根l 如：select sqrt(16)/*4lPower(数字表达式，幂）：返回数字表达式的指定次幂的值。如：select power(2,3)/*8lEXP（浮点表达式）：返回数值的指数形式lLOG（浮点表达式）：返回数值的自然对数值lPI（）：返回的值lROUNDROUND（数值表达式，整型表达式）：将数值表达式四数值表达式，整型表达式）：将数值表达式四舍五入为整型表达式所给定的精度。舍五
22、入为整型表达式所给定的精度。六、元数据函数：返回有关数据库和数据库对象的信息六、元数据函数：返回有关数据库和数据库对象的信息Col_length(table,column):返回列的长度(以字节为单位)Col_name(table_id,column_id):返回数据列的名称 如:select col_length(stucou,couno)/*用于显示stucou表 中couno列的长度 select col_name(object_id(student),1)/*用于显示student表中第1列的名称DB_ID(database_name):返回数据库标识DB_NAME(database_
23、id):返回数据库名 如:select db_id(xk)/*7 或 select db_id(pubs)/*5Object_id(object):返回数据库对象标识Object_name(object_id):返回数据库对象名 如:select object_id(student)select object_name(object_id(student)/*student七、安全函数七、安全函数User:返回当前用户的数据库用户名User_id(user):返回用户标识Suser_sid(login):返回登陆账户的安全标识Suser_sname(server_user_sid):根据用户的
24、安全标识返回登陆账户名 如:select user /*返回当前用户的数据库用户名 select suser_sid(sa)/*返回当前登陆账户的用户安全标识 select suser_sname(0 x01)或select suser_sname()/*saHAS_DBACCESS(database_name):返回用户是否可以访问所给定的数据 库,1为可以,0为不可以.如:select has_dbaccess(xk)/*检查用户是否可以访问检查用户是否可以访问xk数据库数据库八、配置函数version:返回sql server 当前安装的日期、版本和处理器类型Microsoft SQL
25、Server 2000-8.00.760(Intel X86)Dec 17 2002 14:22:05 Copyright(c)1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0(Build 2195:Service Pack 3)其中的8.00.760就是SQL Server的版本和补丁号。对应关系如下：8.00.194 SQL Server 2000 RTM 8.00.384(SP1)8.00.534(SP2)8.00.760(SP3)language:返回当前使用语言servername：返回运行sql
26、 server 的本地服务器名称max_connections:返回允许用户同时连接的最大数3.3 常量和变量常量和变量1.字符串常量：包含在单引号内，由字母数字字符字符串常量：包含在单引号内，由字母数字字符(az,AZ和和0 9）以）以及特殊字符（如！及特殊字符（如！,和和#)组成组成 unicode字符串常量字符串常量:也用单引号分隔也用单引号分隔,但是还必须在前面加上但是还必须在前面加上N如如:N Tom jones 区别区别:unicode数据中的每个字符都使用两个字节存储数据中的每个字符都使用两个字节存储,而字符数据中的而字符数据中的每个字符则使用一个字节进行存储每个字符则使用一个字
27、节进行存储.因此因此:unicode数据中所能存储的最大数据中所能存储的最大字符串长度是相应的字符数据类型的一半字符串长度是相应的字符数据类型的一半.字符型有三种不同的数据类型字符型有三种不同的数据类型:char(用来存储固定长度的字符串用来存储固定长度的字符串),varchar(用来存储可变长度的字符串用来存储可变长度的字符串),text(用来存储极其大量的信息用来存储极其大量的信息)Unicode 字符串也有相应的三种数据类型字符串也有相应的三种数据类型:nchar,nvarchar,ntext 2.数值常量：数值常量：二进制常量二进制常量(二进制类型二进制类型:用来存储位字符串用来存储位
28、字符串.binary,varbinary,image):如如 0 x12efbit常量常量:使用使用0或或1表表datetime常量常量(日期时间数据类型日期时间数据类型:datetime,smalldatetime)integer常量常量(整型整型:bigint,int,smallint,tinyint,bit)decimal常量常量(精确数精确数:decimal,numeric 在存储时不丢失在存储时不丢失位数，但要求比位数，但要求比real 和和float大的空间，同时，在定义时要指定大的空间，同时，在定义时要指定标度和精度标度为小数点之后的位数，精度为数的位数，如标度和精度标度为小数点
29、之后的位数，精度为数的位数，如123456.789，精度为，精度为9，标度为，标度为3。最大精度为最大精度为)float常量、常量、real常量常量(近似数：近似数：real 单精度单精度 ,float双精双精度度.Real 精度达到精度达到7位，位，float精度达到精度达到15位它们不会占用位它们不会占用太多的空间但可存储很大范围的数，但它们不是很准确）：太多的空间但可存储很大范围的数，但它们不是很准确）：采用科学记数法，如：采用科学记数法，如：101.5E6 (101.56*106)money常量常量(货币数据类型：货币数据类型：money,smallnoney,是在是在decimal数
30、的精度和数的精度和real型数的大小之间的一种折衷型数的大小之间的一种折衷)：使用：使用$或或17种别的货币符号做前缀。如种别的货币符号做前缀。如$14.5Uniqueredentifer常量常量:全局唯一标识符，根据对网卡号的全局唯一标识符，根据对网卡号的识别和从计算机时钟得到的一个惟一数来产生的。识别和从计算机时钟得到的一个惟一数来产生的。变变 量量1.局部变量：是用户在程序中定义的变量，一次局部变量：是用户在程序中定义的变量，一次 只能保存一个值，仅在定义的程序范围内有效只能保存一个值，仅在定义的程序范围内有效定义：使用定义：使用declare语句声明局部变量，定义其语句声明局部变量，定
31、义其 名字，数据类型和长度名字，数据类型和长度declare s varchar(30),x smalldatetime赋值：初值为赋值：初值为null，可使用可使用set或或select语句赋值语句赋值 set 语句一次只能给一个局部变量赋值语句一次只能给一个局部变量赋值select 语句可以同时给一个或多个变量赋值语句可以同时给一个或多个变量赋值显示变量的值：可以使用显示变量的值：可以使用select 或或print语句语句 select s 或或 print x 通常在以下几种情况下使用局部变量：通常在以下几种情况下使用局部变量：作为计数器计算循环执行的次数或控制循环执行作为计数器计算循
32、环执行的次数或控制循环执行的次数的次数保存由存储过程返回代码返回的数据值保存由存储过程返回代码返回的数据值保存数据值以供流控语句测试保存数据值以供流控语句测试2.全局变量：以全局变量：以开头，实际上是开头，实际上是SQL Server的配置函数的配置函数 error ：每条每条t-sal语句执行后，服务器赋给这个变量语句执行后，服务器赋给这个变量一个整型值，一个整型值，0如果语句执行成功，如果语句执行成功，错误号如果执错误号如果执行失败。行失败。例例3.32：编写计算两个整数之和的程序：编写计算两个整数之和的程序例例3.33：打印：打印course表中有多少种类型的课程，要求表中有多少种类型的
33、课程，要求 声明局部变量，进行赋值，然后打印变量内容声明局部变量，进行赋值，然后打印变量内容3.4 运算符运算符1.算术运算符：算术运算符：+，-，*，/，%2.一元运算符：一元运算符：+，-，3.比较运算符：比较运算符：=，=，=，!=，!4.逻辑运算符：逻辑运算符：all，and，any，between，exists，in，like，not，or，some5.字符串连接符：字符串连接符：+6.赋值运算符：赋值运算符：=7.位运算符：位运算符：&，|，3.5 常用的系统存储过程常用的系统存储过程Sp_configure:显示或改变当前服务器的配置显示或改变当前服务器的配置Sp_help:列出
34、数据库种对象信息或返回所给定的对象的信息列出数据库种对象信息或返回所给定的对象的信息Sp_who:返回用户和进程的信息返回用户和进程的信息Sp_dboption:显示或改变数据库配置选项显示或改变数据库配置选项Sp_helpdb:列出所给定的数据库或所有数据库信息列出所给定的数据库或所有数据库信息Sp_helpprotect:显示对象的用户权限或语句权限的信息显示对象的用户权限或语句权限的信息Sp_lock:显示锁的信息，用于诊断锁定与死锁问题显示锁的信息，用于诊断锁定与死锁问题Sp_spaceused：检查当前数据库的使用情况检查当前数据库的使用情况存储过程是一种数据库对象，它封装了服务器上
35、的存储过程是一种数据库对象，它封装了服务器上的transact-sql语语句集合，可以重复使用。类似于其它程序语言中的过程或函数。句集合，可以重复使用。类似于其它程序语言中的过程或函数。3.6 批处理批处理批处理：是包含一个或多个批处理：是包含一个或多个Transact-SQL语语句的组，它将一次性地发送到句的组，它将一次性地发送到SQL Server中中执行。用执行。用GO来通知一批来通知一批Transact-SQL语句语句的结束。的结束。例例3.35流控流控语句语句1.IF语句（条件执行语句）语句（条件执行语句）2.BEGINEND（语句块）：利用它将几个语句块）：利用它将几个transa
36、ct-sql 语语句组合起来。句组合起来。格式：格式：begin transact-sql statements end3.WHILE(循环）循环）,BREAK（中止循环）和中止循环）和CONTINUE（中止中止本次循环）本次循环）WHILE 逻辑表达式逻辑表达式1 BEGIN 语句语句1 IF 逻辑表达式逻辑表达式2 BREAK ELSE 语句语句2 END4.DECLARE:用来定义局部变量用来定义局部变量5.GOTO(无条件执行无条件执行)GOTO 标号标号6.RETURN7.WAITFOR（调度执行）调度执行）允许开发者定义一个时间或一个时间间隔，在定义的允许开发者定义一个时间或一个时
37、间间隔，在定义的时间或者经过定义的时间间隔时，余下的时间或者经过定义的时间间隔时，余下的transact-sql语语句会被执行。句会被执行。WAITFOR DELAY time|TIME time8.CASE表达式：表达式：CASE nput_expression WHEN when_expression THEN result_expression n ELSE else_result_expression END9.PRINT:在屏幕上显示用户的信息以及在屏幕上显示用户的信息以及char,varchar数数据类型变量的内容据类型变量的内容10.Raiserror:通常用在错误处理中，它既可在屏幕上显通常用在错误处理中，它既可在屏幕上显示用户的信息，又可将错误号保存在示用户的信息，又可将错误号保存在Error全局变量全局变量中，以备错误处理时使用中，以备错误处理时使用