1、第4章 Transact-SQL语言编程,学习目标:掌握transact-sql编程知识综合运用变量、表达式、函数以及流控语句等编写程序代码 重点：掌握常用函数及流控语句的使用,并学会编写sql server 2000程序代码 难点：掌握常用函数及流控语句的使用,并学会编写sql server 2000程序代码,TransactSQL的语法规则,大写：关键字 斜体或小写字母：用户提供的参数| ：分隔括号或大括号内的语法项目。只能选择一个项目 ：可选语法项目 ：必选语法项目 ( )：语句的组成部分，必须输入 ,n:表示前面的项可重复n次，每一项由逗号隔开 n:表示前面的项可重复n次，每一项由空格
2、隔开 加粗：数据库名、表名、列名、索引名、存储过程、 实用工具、数据类型名以及必须按所显示的原样键入的文本。,引用数据库对象名的规则,Server_name.database_name.owner_name.object_name Database_name. owner_name.object_name owner_name.object_name object_name 注：对象的名字可由1128个字符组成，不区分大小写。不允许存在4部分名称完全相同的数据库对象。在同一个数据库里可以存在两个名为EXAMPLE的表格，但前提条件必须是这两个表的拥有者不同,Transact-SQL的语法元素,
3、1.标识符：用来标识服务器、数据库和数据库对象的名称 常规标识符：第一个字符必须是下列字符之一：a-z和A-Z，以及来自其他语言的字母字符或者下划线_、或#。其他字符可以是字母、数字、$、#或下划线（内部不允许有空格或特殊字符，不允许是保留字） 以开始的标识符：表示局部变量或参数 以#开始的标识符：表示临时表或过程 以#开始的标识符：表示全局临时变量 以开始的标识符：表示全局变量,分隔标识符：对不符合所有标识符规则的标识符必须进行分隔，将其包含在双引号“或方括号 内，如ORDER 注: transact-sql 的保留字不能用来做标识符.标识符中也不允许有空格. 数据类型：有系统数据类型与用户
4、定义的数据类型 函数：可以有0个，1个或多个参数，并返回一个值或值的集合 表达式 运算符 注释：只能注释一行代码/* . .*/：可注释大断的代码 7. 保留字：,3.2 函数,SQL Server提供的函数有：聚合函数、配置函数、游标函数、日期函数、数学函数、元数据函数、 行集函数、安全函数、字符串函数、系统函数、文本与图像函数,一、聚合函数（统计函数）：对一组值进行计算并返回一个数值 SUM(ALL|DISTINCTexpression）：求和 MIN (ALL|DISTINCTexpression）：求最小值 MAX(ALL|DISTINCTexpression）：求最大值 COUNT
5、(ALL|DISTINCTexpression|*）：计算总行数 Count(*) :包括含有空值的行，同时不能与distinct一起使用 例： select count( departname) as 系数 from department ( 可先删除一个系，使该行为空） select count( distinct *) as 系数 from department /出现错误信息 AVG (ALL|DISTINCTexpression）:求平均值 注意:select ,group by ,having 可以使用聚合函数,where不行,例3.1 计算course表的总行数 SELECT C
6、OUNT(*) AS Course表的总行数 FROM Course,例3.2 计算course表各门课程限选人数的总人数 SELECT SUM(LimitNum) AS 限选人数总人数 FROM Course,例3.3 统计course表中各门课程中最少报名人数、最多报名人数和平均报名人数 SELECT MIN(WillNum) AS 最少报名人数,MAX(WillNum) AS 最多报名人数, AVG(WillNum) AS 平均报名人数 FROM Course,二、字符串函数：用于对字符串进行连接、截取等操作,ASCII (字符串表达式)：返回字符表达式最左边字符的ASCII码 如：例
7、3.5 CHAR (整型表达式）：将一个ASCII码转换为字符。 Len(字符串表达式）：返回给定字符串数据的长度，不计算尾部的空格。如： select len(123) 例3.6 Left(字符型表达式，整型表达式）： 返回该字符型表达式最左边给定整数个字符. 如： select left(123,) Right(字符型表达式，整型表达式）： 返回该字符型表达式最右边给定整数个字符. Substring(字符串，起始点，n）: 返回该字符串从起始点开始的n个字符。 如: Select x=substring(abcdef,2,3),STR(浮点表达式，长度，小数 )：将浮点表达式转换为所 给
8、定长度的字符串。如： select str (123 , 2) ,str(123,3), str(123,4) , str(123.12, 5,1) Upper(字符型表达式）：将字符型表达式全部转化为大写形式 Lower(字符型表达式）：将字符型表达式全部转化为小写形式 Space(整型表达式）：返回由给定整数个空格组成的字符串 Replicate(字符型表达式，整型表达式）：将给定的字符型表达式的值复制给定的整数次数。如：例3.9 Stuff(字符型表达式1，开始位置，长度，字符型表达式2）：将字符型表达式1从开始位置截断给定长度的子串，然后将字符型表达式2从开始位置补充进去。如：Stuf
9、f(abcdef,2,4,hijkl) ,例3.10 Differences(字符型表达式1，字符型表达式2):返回两个字符表达式发音的相似程度(0-4)。4发音最相似. 如：例3.8,Reverse(字符型表达式)：返回一个与给定字符型表达式恰好顺序颠倒的字符型表达式,即逆序 Ltrim(字符型表达式）：返回删除给定字符串左端空白后的字符串值 Rtrim(字符型表达式）：返回删除给定字符串右端空白后的字符串值 Charindex(字符型表示式1，字符型表达式2开始位置)：从指定位置开始，在字符型表达式2中查找字符型表达式1，如果找到则返回字符型表达式1在字符型表达式2中的开始位置，默认的开始
10、位置是1），即定位.如：例3.4, 3.7,Patindex(%pattern%,字符型表达式）：在字符型表达式中查找给定格式的字符串，如果找到则返回该给定字符串在字符型表达式中的开始位置，否则返回值为0例：use pubs /*pubs是示例数据库*/go select au_lname,au_idfrom authorswhere patindex(%-2%,au_id)0go将画横线部分改为charindex(-2,au_id)0, 试比较运行结果,例3.4 给出“数据库”在“大型数据库技术”中的位置SELECT CHARINDEX(数据库,大型数据库技术),例3.5 返回“Alklk”
11、最左边字符“A”的ASCII码SELECT ASCII(Alklk),例3.6 计算字符串“SQL Server数据库管理系统”的长度SELECT LEN(SQL Server数据库管理系统),例3.7 查找字符串“wo”在“MY wonderful”中的开始位置SELECT CHARINDEX(wo, MY wonderful) SELECT CHARINDEX(wo, MY wonderful,5) SELECT CHARINDEX(wo, MY wonderful,0),例3.8 观察“Hello worle”与“hello wild”的发音相似程度SELECT DIFFERENCE(H
12、ello World, Hello Wild),例3.9 select replicate(Hello,2),space(10),replicate(world,2),例3.10 select stuff(He rld,3,1,llo Wo）,三、日期函数：用于显示日期和时间的信息,Getdate()：返回当前的系统时间如： select getdate() /*显示服务器当前的系统日期和时间*/ Datepart(datepart,date):以整数形式返回给定date型数据的指定日期部分如： select detepart(weekday,getdate() datename(datepa
13、rt,date):以字符串形式返回给定date型数据的指定日期部分如： select detename(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/
14、12/23日，使用日期函数计算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 , expression): 与cast 相似的功能。如：select cast(65 as c
15、har) select convert (char , 65) HOST_ID ( ):返回主机标识 HOST_NAME( )：返回主机名称如：SELECT HOST_NAME(),五、数学函数：用来对数值型数据进行数学运算,ABS（数值表达式）：返回表达式的绝对值。 ACOS（浮点表达式）：返回浮点表达式的反余弦值 ASIN(浮点表达式)：返回浮点表达式的反正弦值 CEILING（数值表达式）：返回大于或等于数值表达式的最小整数。 FLOOR（数值表达式）：返回小于或等于数值表达式的最大整数。如：SELECT CEILING (134.393) /* 135SELECT FLOOR (134
16、.3393) /*134,SQUARE（浮点表达式）：返回浮点表达式的平方 SQRT（浮点表达式）：返回浮点表达式的平方根如： select sqrt(16) /*4 Power(数字表达式，幂）：返回数字表达式的指定次幂的值。如： select power(2,3) /* 8 EXP（浮点表达式）：返回数值的指数形式 LOG（浮点表达式）：返回数值的自然对数值 PI（）：返回的值 ROUND（数值表达式，整型表达式）：将数值表达式四舍五入为整型表达式所给定的精度。,六、元数据函数：返回有关数据库和数据库对象的信息,Col_length(table,column):返回列的长度(以字节为单位)
17、 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_id):返回数据库名如: select db_id(xk) /* 7 或 select db_id(pubs) /* 5,Object_id(object):返回数据库对象标识 Object
18、_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):根据用户的安全标识返回登陆账户名如: select user /*返回当前用户的数据库用户名select suser_sid(sa) /* 返回当前登陆账户的用户安全标识select
19、suser_sname(0x01) 或select suser_sname() /* sa HAS_DBACCESS(database_name):返回用户是否可以访问所给定的数据 库,1为可以,0为不可以.如: select has_dbaccess(xk) /*检查用户是否可以访问xk数据库,八、配置函数,version:返回sql server 当前安装的日期、版本和处理器类型 Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Co
20、rporation 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 server 的本地服务器名称 max_connections:返回允许用户同时连接的最大数,3.3 常量和变量,1.字符串常量：包含在单引号
21、内，由字母数字字符(az,AZ和0 9）以及特殊字符（如！,和#) 组成unicode字符串常量:也用单引号分隔,但是还必须在前面加上N 如: N Tom jones区别: unicode数据中的每个字符都使用两个字节存储,而字符数据中的每个字符则使用一个字节进行存储.因此:unicode数据中所能存储的最大字符串长度是相应的字符数据类型的一半.字符型有三种不同的数据类型: char (用来存储固定长度的字符串) , varchar (用来存储可变长度的字符串), text (用来存储极其大量的信息) Unicode 字符串也有相应的三种数据类型:nchar,nvarchar,ntext,2.
22、数值常量： 二进制常量(二进制类型:用来存储位字符串.binary,varbinary,image): 如 0x12ef bit常量:使用0或1表 datetime常量(日期时间数据类型: datetime , smalldatetime)integer常量(整型: bigint , int , smallint , tinyint , bit ) decimal常量( 精确数: decimal , numeric 在存储时不丢失位数，但要求比real 和float大的空间，同时，在定义时要指定标度和精度标度为小数点之后的位数，精度为数的位数，如123456.789，精度为9，标度为3。 最大
23、精度为),float常量、 real常量(近似数：real 单精度 , float双精度. Real 精度达到7位，float精度达到15位它们不会占用太多的空间但可存储很大范围的数，但它们不是很准确）：采用科学记数法，如：101.5E6 ( 101.56*106)money常量(货币数据类型：money, smallnoney ,是在decimal数的精度和real型数的大小之间的一种折衷)：使用$或17种别的货币符号做前缀。如$14.5 Uniqueredentifer常量:全局唯一标识符，根据对网卡号的识别和从计算机时钟得到的一个惟一数来产生的。,变 量,1.局部变量：是用户在程序中定义
24、的变量，一次只能保存一个值，仅在定义的程序范围内有效,定义：使用declare语句声明局部变量，定义其名字，数据类型和长度declare s varchar(30), x smalldatetime,赋值：初值为null，可使用set或select语句赋值set 语句一次只能给一个局部变量赋值select 语句可以同时给一个或多个变量赋值,显示变量的值：可以使用select 或print语句select s 或 print x,通常在以下几种情况下使用局部变量：作为计数器计算循环执行的次数或控制循环执行的次数 保存由存储过程返回代码返回的数据值 保存数据值以供流控语句测试,2.全局变量：以开头
25、，实际上是SQL Server的配置函数error ：每条t-sal语句执行后，服务器赋给这个变量一个整型值，0如果语句执行成功， 错误号如果执行失败。,例3.32 ：编写计算两个整数之和的程序,例3.33 ：打印course表中有多少种类型的课程，要求声明局部变量，进行赋值，然后打印变量内容,3.4 运算符,1.算术运算符：+，-，*，/，% 2.一元运算符：+，-， 3.比较运算符：=，=，!=，! 4.逻辑运算符：all，and，any，between，exists，in，like，not，or，some 5.字符串连接符：+ 6.赋值运算符：= 7.位运算符：&，|，,3.5 常用的系
26、统存储过程,Sp_configure:显示或改变当前服务器的配置 Sp_help:列出数据库种对象信息或返回所给定的对象的信息 Sp_who:返回用户和进程的信息 Sp_dboption:显示或改变数据库配置选项 Sp_helpdb:列出所给定的数据库或所有数据库信息 Sp_helpprotect:显示对象的用户权限或语句权限的信息 Sp_lock:显示锁的信息，用于诊断锁定与死锁问题 Sp_spaceused：检查当前数据库的使用情况,存储过程是一种数据库对象，它封装了服务器上的transact-sql语句集合，可以重复使用。类似于其它程序语言中的过程或函数。,3.6 批处理,批处理：是包含
27、一个或多个Transact-SQL语句的组，它将一次性地发送到SQL Server中执行。用GO来通知一批Transact-SQL语句的结束。 例3.35,流控语句,1.IF语句（条件执行语句） 2.BEGINEND（语句块）：利用它将几个transact-sql 语句组合起来。格式： begin transact-sql statementsend 3.WHILE(循环）,BREAK（中止循环）和CONTINUE（中止本次循环）WHILE 逻辑表达式1BEGIN语句1IF 逻辑表达式2BREAKELSE 语句2END,4. DECLARE:用来定义局部变量 5. GOTO(无条件执行)GOT
28、O 标号 6. RETURN 7. WAITFOR（调度执行）允许开发者定义一个时间或一个时间间隔，在定义的时间或者经过定义的时间间隔时，余下的transact-sql语句会被执行。WAITFOR DELAY time|TIME time 8. CASE表达式：CASE nput_expressionWHEN when_expression THENresult_expressionnELSE else_result_expressionEND,9.PRINT: 在屏幕上显示用户的信息以及char,varchar数据类型变量的内容 10. Raiserror:通常用在错误处理中，它既可在屏幕上显示用户的信息，又可将错误号保存在Error全局变量中，以备错误处理时使用,
