Transact-SQL语言编程学习目标

1、Transact-SQL语言编程学习目标 第第4章章 Transact-SQL语言编程语言编程 学习目标学习目标: 掌握掌握transact-sql编程知识编程知识 综合运用变量、表达式、函数以及流控语句等编写程序代码综合运用变量、表达式、函数以及流控语句等编写程序代码 重点：掌握常用函数及流控语句的使用重点：掌握常用函数及流控语句的使用,并学会编写并学会编写sql server 2000程序代码程序代码 难点：掌握常用函数及流控语句的使用难点：掌握常用函数及流控语句的使用,并学会编写并学会编写sql server 2000程序代码程序代码 Transact-SQL语言编程学习目标 Trans

2、actSQL的语法规则的语法规则 大写：关键字 斜体或小写字母：用户提供的参数 | ：分隔括号或大括号内的语法项目。只能选择一个项目 ：可选语法项目 ：必选语法项目 ( )：语句的组成部分，必须输入 ,n:表示前面的项可重复n次，每一项由逗号隔开 n:表示前面的项可重复n次，每一项由空格隔开 加粗：数据库名、表名、列名、索引名、存储过程、 实用工 具、数据类型名以及必须按所显示的原样键入的文本。 Transact-SQL语言编程学习目标 引用数据库对象名的规则引用数据库对象名的规则 Server_name.database_name.owner_name. object_name Databa

3、se_name. owner_name.object_name owner_name.object_name object_name 注：对象的名字可由1128个字符组成，不区分大小写。不 允许存在4部分名称完全相同的数据库对象。在同一个数据 库里可以存在两个名为EXAMPLE的表格，但前提条件必须 是这两个表的拥有者不同 Transact-SQL语言编程学习目标 Transact-SQL的语法元素的语法元素 1.标识符：用来标识服务器、数据库和数据库对象标识符：用来标识服务器、数据库和数据库对象 的名称的名称 常规标识符：第一个字符必须是下列字符之一：常规标识符：第一个字符必须是下列字符之一

4、： a-z和和A-Z，以及来自其他语言的字母字符或，以及来自其他语言的字母字符或 者下划线者下划线_、或或#。其他字符可以是字母、数字、。其他字符可以是字母、数字、 $、#或下划线（内部不允许有空格或特殊字符，不允许或下划线（内部不允许有空格或特殊字符，不允许 是保留字）是保留字） 以以开始的标识符：表示局部变量或参数开始的标识符：表示局部变量或参数 以以#开始的标识符：表示临时表或过程开始的标识符：表示临时表或过程 以以#开始的标识符：表示全局临时变量开始的标识符：表示全局临时变量 以以开始的标识符：表示全局变量开始的标识符：表示全局变量 Transact-SQL语言编程学习目标 分隔标识符

5、：对不符合所有标识符规则的标识分隔标识符：对不符合所有标识符规则的标识 符必须进行分隔，将其包含在双引号符必须进行分隔，将其包含在双引号“或方括或方括 号号 内，如内，如ORDER 注注: transact-sql 的保留字不能用来做标识符的保留字不能用来做标识符.标识标识 符中也不允许有空格符中也不允许有空格. 数据类型：有系统数据类型与用户定义的数据数据类型：有系统数据类型与用户定义的数据 类型类型 函数：可以有函数：可以有0个，个，1个或多个参数，并返回一个或多个参数，并返回一 个值或值的集合个值或值的集合 表达式表达式 运算符运算符 注释：注释： ：只能注释一行代码：只能注释一行代码

6、/* . .*/：可注释大断的代码：可注释大断的代码 7. 保留字：保留字： Transact-SQL语言编程学习目标 3.2 函数函数 SQL Server提供的函数有：聚合函数、提供的函数有：聚合函数、 配置函数、游标函数、日期函数、数学配置函数、游标函数、日期函数、数学 函数、元数据函数、函数、元数据函数、 行集函数、安全行集函数、安全 函数、字符串函数、系统函数、文本与函数、字符串函数、系统函数、文本与 图像函数图像函数 Transact-SQL语言编程学习目标 一、聚合函数（统计函数）：对一组值进行计算并返一、聚合函数（统计函数）：对一组值进行计算并返 回一个数值回一个数值 SUM(

7、ALL|DISTINCTexpression）：求和）：求和 MIN (ALL|DISTINCTexpression）：求最小值）：求最小值 MAX(ALL|DISTINCTexpression）：求最大值）：求最大值 COUNT (ALL|DISTINCTexpression|*）：计算总行数）：计算总行数 Count(*) :包括含有空值的行，同时不能与包括含有空值的行，同时不能与distinct一起使用一起使用 例：例： select count( departname) as 系数系数 from department ( 可先可先 删除一个系，使该行为空）删除一个系，使该行为空） se

8、lect count( distinct *) as 系数系数 from department /出现错误信出现错误信 息息 AVG (ALL|DISTINCTexpression）:求平均值求平均值 注意注意:select ,group by ,having 可以使用可以使用聚合函数聚合函数,where不行不行 Transact-SQL语言编程学习目标 例例3.1 计算计算course表的总行数表的总行数 SELECT COUNT(*) AS Course表的总行数表的总行数 FROM Course 例例3.2 计算计算course表各门课程限选人数的总人数表各门课程限选人数的总人数 SEL

9、ECT SUM(LimitNum) AS 限选人数总人数限选人数总人数 FROM Course 例例3.3 统计统计course表中各门课程中最少报名人数、表中各门课程中最少报名人数、 最多报名人数和平均报名人数最多报名人数和平均报名人数 SELECT MIN(WillNum) AS 最少报名人数最少报名人数, MAX(WillNum) AS 最多报名人数最多报名人数, AVG(WillNum) AS 平均报名人数平均报名人数 FROM Course Transact-SQL语言编程学习目标 二、字符串函数：用于对字符串进行连接、截取等操作二、字符串函数：用于对字符串进行连接、截取等操作 AS

10、CII (字符串表达式字符串表达式)：返回字符表达式最左边字符的：返回字符表达式最左边字符的ASCII 码码 如：例如：例 3.5 CHAR (整型表达式）：将一个整型表达式）：将一个ASCII码转换为字符。码转换为字符。 Len(字符串表达式）：返回给定字符串数据的长度，不计算字符串表达式）：返回给定字符串数据的长度，不计算 尾部的空格。如：尾部的空格。如： select len(123) 例例3.6 Left(字符型表达式，整型表达式）：字符型表达式，整型表达式）： 返回该字符型表达式返回该字符型表达式 最左边给定整数个字符最左边给定整数个字符. 如：如： select left(123,

11、) Right(字符型表达式，整型表达式）：字符型表达式，整型表达式）： 返回该字符型表达式返回该字符型表达式 最右边给定整数个字符最右边给定整数个字符. Substring(字符串，起始点，字符串，起始点，n）: 返回该字符串从起始点开始返回该字符串从起始点开始 的的n个字符。个字符。 如如: Select x=substring(abcdef,2,3) Transact-SQL语言编程学习目标 STR(浮点表达式浮点表达式，长度，长度，小数，小数 )：将浮点表达式转换为所：将浮点表达式转换为所 给定长度的字符串。给定长度的字符串。 如：如： select str (123 , 2) ,st

12、r(123,3), str(123,4) , str(123.12, 5,1) Upper(字符型表达式）：将字符型表达式全部转化为大写形式字符型表达式）：将字符型表达式全部转化为大写形式 Lower(字符型表达式）：将字符型表达式全部转化为小写形字符型表达式）：将字符型表达式全部转化为小写形 式式 Space(整型表达式）：返回由给定整数个空格组成的字符串整型表达式）：返回由给定整数个空格组成的字符串 Replicate(字符型表达式，整型表达式）：将给定的字符型表字符型表达式，整型表达式）：将给定的字符型表 达式的值复制给定的整数次数。如：例达式的值复制给定的整数次数。如：例3.9 Stu

13、ff(字符型表达式字符型表达式1，开始位置，长度，字符型表达式，开始位置，长度，字符型表达式2）：）： 将字符型表达式将字符型表达式1从开始位置截断给定长度的子串，然后将字从开始位置截断给定长度的子串，然后将字 符型表达式符型表达式2从开始位置补充进去。从开始位置补充进去。 如：如：Stuff(abcdef,2,4,hijkl) ,例例3.10 Differences(字符型表达式字符型表达式1，字符型表达式，字符型表达式2):返回两个字符表返回两个字符表 达式发音的相似程度达式发音的相似程度(0-4)。4发音最相似发音最相似. 如：例如：例3.8 Transact-SQL语言编程学习目标 R

14、everse(字符型表达式字符型表达式)：返回一个与给定字符型表达式恰：返回一个与给定字符型表达式恰 好顺序颠倒的字符型表达式好顺序颠倒的字符型表达式,即逆序即逆序 Ltrim(字符型表达式）：返回删除给定字符串左端空白后字符型表达式）：返回删除给定字符串左端空白后 的字符串值的字符串值 Rtrim(字符型表达式）：返回删除给定字符串右端空白后字符型表达式）：返回删除给定字符串右端空白后 的字符串值的字符串值 Charindex(字符型表示式字符型表示式1，字符型表达式，字符型表达式2开始位置开始位置)：从：从 指定位置开始，在字符型表达式指定位置开始，在字符型表达式2中查找字符型表达式中查找

15、字符型表达式1， 如果找到则返回字符型表达式如果找到则返回字符型表达式1在字符型表达式在字符型表达式2中的开始中的开始 位置，默认的开始位置是位置，默认的开始位置是1），即定位），即定位. 如：例如：例3.4, 3.7 Transact-SQL语言编程学习目标 Patindex(%pattern%,字符型表达式）：在字符型表达式中字符型表达式）：在字符型表达式中 查找给定格式的字符串，如果找到则返回该给定字符串在字查找给定格式的字符串，如果找到则返回该给定字符串在字 符型表达式中的开始位置，否则返回值为符型表达式中的开始位置，否则返回值为0 例：例：use pubs /*pubs是示例数据库是

16、示例数据库*/ go select au_lname,au_id from authors where patindex(%-2%,au_id)0 go 将画横线部分改为将画横线部分改为charindex(-2,au_id)0, 试比较试比较 运行结果运行结果 Transact-SQL语言编程学习目标 例例3.4 给出给出“数据库数据库”在在“大型数据库技术大型数据库技术”中的位置中的位置 SELECT CHARINDEX(数据库数据库,大型数据库技术大型数据库技术) 例例3.5 返回返回“Alklk”最左边字符最左边字符“A”的的ASCII码码 SELECT ASCII(Alklk) 例例3

17、.6 计算字符串计算字符串“SQL Server数据库管理系统数据库管理系统”的长的长 度度 SELECT LEN(SQL Server数据库管理系统数据库管理系统) Transact-SQL语言编程学习目标 例例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”

18、的发音相似程度的发音相似程度 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） Transact-SQL语言编程学习目标 三、日期函数：用于显示日期和时间的信息三、日期函数：用于显示日期和时间的信息 Getdate()：返回当前的系统时间：返回当前的系统时间 如：如： select getdate() /*显示服务器当前的系统日期和时间显示服务器当前的系统日期

19、和时间*/ Datepart(datepart,date):以整数形式返回给定以整数形式返回给定date型数据的型数据的 指定日期部分指定日期部分 如：如： select detepart(weekday,getdate() datename(datepart,date):以字符串形式返回给定以字符串形式返回给定date型数型数 据的指定日期部分据的指定日期部分 如：如： select detename(weekday,getdate() Dateadd(detepart,number,date):将日期元数加上日期将日期元数加上日期,得到得到 一个新的日期。一个新的日期。 如：如： sele

20、ct dateadd(day,7,getdate() select dateadd(month,7,getdate() Transact-SQL语言编程学习目标 Datediff(datepart,start,enddate):返回开始日期和结束返回开始日期和结束 日期在给定日期元数上的差值日期在给定日期元数上的差值 如：如：select datediff(dd , 2006-3-8,getdate() 例例3.13 Mary的生日为的生日为1979/12/23日，使用日期函数计日，使用日期函数计 算算mary现在的年龄现在的年龄 select 年龄年龄=datediff(yy,1979/12

21、/23,getdate() Day(date):返回某月几号的整数值返回某月几号的整数值 month(date):返回月份返回月份 year(date)：返回年份：返回年份 如：如： select day(getdate() Select year(2006-12-3) Transact-SQL语言编程学习目标 四、系统函数：用来获取四、系统函数：用来获取SQL Server的有关信息的有关信息 Cast( 表达式 as data_type)：将表达式显示转换为另一种 数据类型。 Convert(data_type , expression): 与cast 相似的功能。 如：select ca

22、st(65 as char) select convert (char , 65) HOST_ID ( ):返回主机标识 HOST_NAME( )：返回主机名称 如：SELECT HOST_NAME() Transact-SQL语言编程学习目标 五、数学函数：用来对数值型数据进行数学运算五、数学函数：用来对数值型数据进行数学运算 ABS（数值表达式）：返回表达式的绝对值。 ACOS（浮点表达式）：返回浮点表达式的反余弦值 ASIN(浮点表达式)：返回浮点表达式的反正弦值 CEILING（数值表达式）：返回大于或等于数值表达式 的最小整数。 FLOOR（数值表达式）：返回小于或等于数值表达式的

23、最大整数。 如：SELECT CEILING (134.393) /* 135 SELECT FLOOR (134.3393) /*134 Transact-SQL语言编程学习目标 l SQUARE（浮点表达式）：返回浮点表达式的平方 l SQRT（浮点表达式）：返回浮点表达式的平方根 l 如： select sqrt(16) /*4 l Power(数字表达式，幂）：返回数字表达式的指定次 幂的值。 如： select power(2,3) /* 8 l EXP（浮点表达式）：返回数值的指数形式 l LOG（浮点表达式）：返回数值的自然对数值 l PI（）：返回的值 l ROUNDROUND

24、（数值表达式，整型表达式）：将数值表达式四（数值表达式，整型表达式）：将数值表达式四 舍五入为整型表达式所给定的精度。舍五入为整型表达式所给定的精度。 Transact-SQL语言编程学习目标 六、元数据函数：返回有关数据库和数据库对象的信息六、元数据函数：返回有关数据库和数据库对象的信息 Col_length(table,column):返回列的长度(以字节为单位) Col_name(table_id,column_id):返回数据列的名称 如: select col_length(stucou,couno) /*用于显示stucou表 中couno列的长度 select col_name

25、(object_id(student),1) /* 用于显示student 表中第1列的名称 DB_ID(database_name):返回数据库标识 DB_NAME(database_id):返回数据库名 如: select db_id(xk) /* 7 或 select db_id(pubs) /* 5 Transact-SQL语言编程学习目标 Object_id(object):返回数据库对象标识 Object_name(object_id):返回数据库对象名 如: select object_id(student) select object_name(object_id(studen

26、t) /* student Transact-SQL语言编程学习目标 七、安全函数七、安全函数 User :返回当前用户的数据库用户名 User_id(user):返回用户标识 Suser_sid(login):返回登陆账户的安全标识 Suser_sname(server_user_sid):根据用户的安全标识返回登陆账户名 如: select user /*返回当前用户的数据库用户名 select suser_sid(sa) /* 返回当前登陆账户的用户安全标识 select suser_sname(0 x01) 或select suser_sname() /* sa HAS_DBACCES

27、S(database_name):返回用户是否可以访问所给定的 数据 库,1为可以,0为不可以. 如: select has_dbaccess(xk) /*检查用户是否可以访问检查用户是否可以访问xk数据库数据库 Transact-SQL语言编程学习目标 八、配置函数 version:返回sql server 当前安装的日期、版本和处理 器类型 SQL Server的版本和补丁号。对应关系如下： language : 返回当前使用语言 servername ：返回运行sql server 的本地服务器名称 max_connections:返回允许用户同时连接的最大数 Transact-SQL语

28、言编程学习目标 3.3 常量和变量常量和变量 1.字符串常量：包含在单引号内，由字母数字字符字符串常量：包含在单引号内，由字母数字字符(az,AZ和和0 9）以）以 及特殊字符（如！及特殊字符（如！,和和#) 组成组成 unicode字符串常量字符串常量:也用单引号分隔也用单引号分隔,但是还必须在前面加上但是还必须在前面加上N 如如: N Tom jones 区别区别: unicode数据中的每个字符都使用两个字节存储数据中的每个字符都使用两个字节存储,而字符数据中的而字符数据中的 每个字符则使用一个字节进行存储每个字符则使用一个字节进行存储.因此因此:unicode数据中所能存储的最大数据中

29、所能存储的最大 字符串长度是相应的字符数据类型的一半字符串长度是相应的字符数据类型的一半. 字符型有三种不同的数据类型字符型有三种不同的数据类型: char (用来存储固定长度的字符串用来存储固定长度的字符串) , varchar (用来存储可变长度的字符串用来存储可变长度的字符串), text (用来存储极其大量的信息用来存储极其大量的信息) Unicode 字符串也有相应的三种数据类型字符串也有相应的三种数据类型:nchar,nvarchar,ntext Transact-SQL语言编程学习目标 2.数值常量：数值常量： 二进制常量二进制常量(二进制类型二进制类型:用来存储位字符用来存储位

30、字符 串串.binary,varbinary,image): 如如 0 x12ef bit常量常量:使用使用0或或1表表 datetime常量常量(日期时间数据类型日期时间数据类型: datetime , smalldatetime) integer常量常量(整型整型: bigint , int , smallint , tinyint , bit ) decimal常量常量( 精确数精确数:decimal , numeric 在存储时不丢失在存储时不丢失 位数，但要求比位数，但要求比real 和和float大的空间，同时，在定义时要指定大的空间，同时，在定义时要指定 标度和精度标度为小数点之

31、后的位数，精度为数的位数，如标度和精度标度为小数点之后的位数，精度为数的位数，如 123456.789，精度为，精度为9，标度为，标度为3。 最大精度为最大精度为) Transact-SQL语言编程学习目标 float常量、常量、 real常量常量(近似数：近似数：real 单精度单精度 , float双精度双精度. Real 精度达到精度达到7位，位，float精度达到精度达到15位它们不会占用太多位它们不会占用太多 的空间但可存储很大范围的数，但它们不是很准确）：采的空间但可存储很大范围的数，但它们不是很准确）：采 用科学记数法，如：用科学记数法，如：101.5E6 ( 101.56*10

32、6) money常量常量(货币数据类型：货币数据类型：money, smallnoney ,是在是在 decimal数的精度和数的精度和real型数的大小之间的一种折衷型数的大小之间的一种折衷)：使用：使用$ 或或17种别的货币符号做前缀。如种别的货币符号做前缀。如$14.5 Uniqueredentifer常量常量:全局唯一标识符，根据对网卡号的识全局唯一标识符，根据对网卡号的识 别和从计算机时钟得到的一个惟一数来产生的。别和从计算机时钟得到的一个惟一数来产生的。 Transact-SQL语言编程学习目标 变变 量量 1.局部变量：是用户在程序中定义的变量，一次局部变量：是用户在程序中定义的

33、变量，一次 只能保存一个值，仅在定义的程序范围内有效只能保存一个值，仅在定义的程序范围内有效 定义：使用定义：使用declare语句声明局部变量，定义其语句声明局部变量，定义其 名字，数据类型和长度名字，数据类型和长度 declare s varchar(30), x smalldatetime 赋值：初值为赋值：初值为null，可使用，可使用set或或select语句赋值语句赋值 set 语句一次只能给一个局部变量赋值语句一次只能给一个局部变量赋值 select 语句可以同时给一个或多个变量赋值语句可以同时给一个或多个变量赋值 显示变量的值：可以使用显示变量的值：可以使用select 或或p

34、rint语句语句 select s 或或 print x Transact-SQL语言编程学习目标 通常在以下几种情况下使用局部变量：通常在以下几种情况下使用局部变量： 作为计数器计算循环执行的次数或控制循环执行作为计数器计算循环执行的次数或控制循环执行 的次数的次数 保存由存储过程返回代码返回的数据值保存由存储过程返回代码返回的数据值 保存数据值以供流控语句测试保存数据值以供流控语句测试 2.全局变量：以全局变量：以开头，实际上是开头，实际上是SQL Server的配置函数的配置函数 error ：每条：每条t-sal语句执行后，服务器赋给这个变量语句执行后，服务器赋给这个变量 一个整型值，

35、一个整型值，0如果语句执行成功，如果语句执行成功， 错误号如果执错误号如果执 行失败。行失败。 例例3.32 ：编写计算两个整数之和的程序：编写计算两个整数之和的程序 例例3.33 ：打印：打印course表中有多少种类型的课程，要求表中有多少种类型的课程，要求 声明局部变量，进行赋值，然后打印变量内容声明局部变量，进行赋值，然后打印变量内容 Transact-SQL语言编程学习目标 3.4 运算符运算符 1.算术运算符：算术运算符：+，-，*，/，% 2.一元运算符：一元运算符：+，-， 3.比较运算符：比较运算符：=，=，=，!=，! 4.逻辑运算符：逻辑运算符：all，and，any，between，exists，in， like，not，or，some 5.字符串连接符：字符串连接符：+ 6.赋值运算符：赋值运算符：= 7.位运算符：位运算符：&，|， Transact-SQL语言编程学习目标 3.5 常用的系统存储过程常用的系统存储过程 Sp_configure:显示或改变当前服务器的配置显示或改变当前服务器的配置 Sp_help:列出数据库种对象信息或返回所给定的对象的信息列出数据库种对象信