第7章Transact-SQL程序设计.ppt

第7章 Transact-SQL程序设计本章导读：Transact-SQL是内嵌在SQL Server中的结构化查询语言，除了具备数据查询、定义、操纵和控制功能外，还引入了程序设计的思想和过程控制结构，增加了函数、系统存储过程，触发器等。灵活运用Transact-SQL语言，可以编写基于客户/服务器模式下的数据库应用程序。知识要点：程序设计基础知识流程控制语句内置函数用户自定义函数事务处理7.1 程序设计基础知识7.2 流程控制语句7.3 其它语句7.4 内置函数7.5 用户自定义函数7.1 程序设计基础知识程序设计基础知识程序设计的基础是处理数据，而数据在程序中最常见的形式是常量、变量和表达式。7.1.1 常量7.1.2 变量7.1.3 表达式7.1.1 常量常量常量也称字面值或标量值，是表示一个特定数据值的符号，常量的格式取决于它所表示的值的数据类型。SQL Server中常量有如下几种形式：（1）字符串常量字符串常量分为ASCII字符串常量和Unicode字符串常量。ASCII字符串常量ASCII字符串常量：是指用定界符单引号（）括起来，由英文字母（az，AZ）和数字（09）以及特殊符号（！，）等ASCII字符组成的字符序列。如中国，合肥等。如果在字符串中嵌入单引号（），可以使用两个连续的单引号（）表示嵌入的一个单引号（）；而中间没有任何字符的两个连续的单引号（）表示空串。Unicode字符串常量Unicode字符串常量：则是以标识符（N）为前缀，再引导由定界符单引号（）括起来的字符串。如Nchina，Nhefei等。Unicode字符串常量被解释为Unicode数据。Unicode 数据中的每个字符用两个字节存储，而ASCII字符串中的每个字符则使用一个字节存储。注意：Unicode字符串的前缀N必须是大写字母。如：database是字符串常量，而Ndatabase是Unicode常量。7.1.1 常量常量（2）整型常量按照整型常量的不同表示方式，又分为bit常量、interger常量和二进制常量。二进制位常量（bit）由数字 0 或 1 表示，没有定界符。如果使用一个大于1的数字，将被转换为1。十进制常量（interger）由正、负号和数字09组成，正号可以省略。例如：2006、3、-2009。二进制常量使用0 x作为前辍，后面跟随16进制数字字符串，没有定界符。例如：0 xcdE、0 x12E9、0 x（空二进制常量）。7.1.1 常量常量（3）日期/时间常量日期/时间常量用定界符单引号（）括起来的特定格式的字符。SQL Server提供并识别多种格式的日期/时间，使用set dateformat mdy|dmy|ymd命令可以设置日期/时间格式。常见的日期格式有：字母日期格式：April 15, 1998，15-April-1998数字日期格式：10/15/2004，2004-10-15，2009年3月22日未分隔的日期格式：980415，04/15/98常见的时间格式有：14:30:24，04:24 PM7.1.1 常量常量（4）decimal常量decimal常量由正、负号、小数点、数字09组成，正号可以省略。例如：91.3、-2147483648.10。（5）float和real常量float和real常量使用科学记数法表示。例如：101.5E5、0.5E-2。（6）货币常量货币常量是以可选货币符号（$）作为前缀，并可以带正、负号和小数点的一串数字，用来表示正的或负的货币值。SQL Server提供两种数据类型，即money和smallmoney来存储货币数据，存储的精确度为4位小数。例如$20、$45、-$35、$0.22等。（7）uniqueidentifier常量uniqueidentifier常量表示全局唯一标识符值的字符串。可以使用字符或二进制字符串格式指定。例如，以下这两个示例指定相同的GUID。例如：6F9619FF-8B86-D011-B42D-00C04FC964FF、0 xff19966f868b11d0b42d00c04fc964ff7.1.2 变量变量变量是指在程序运行过程中其值可以变化的量，包括变量名和变量值两部分。变量名是对变量的命名，变量值是对变量的赋值。Transact-SQL中变量有两种：全局变量和局部变量。1全局变量全局变量：是SQL Server 2000系统定义并自动赋值的变量，其作用范围是所有程序，主要用来记录SQL Server服务器的活动状态。用户可以引用全局变量但不能改变它的值，全局变量必须以“”开头。SQL Server 2000提供了30多个全局变量，如表7-1所示：7.1.2 变量变量全局变量名全局变量名功能功能connections返回连接或企图连接到SQL Server（最近一次启动以来）的连接次数cpu_busy返回自SQL Server最近一次启动以来，CPU的工作时间总量，单位为毫秒cursor_rows返回当前打开的最后一个游标中还未被读取的有效数据行的行数datefirst返回一个星期中的第一天，set datefirst命令设置datafirst参数值，取值17dbts返回当前数据库的时间戳值，数据库中时间戳值必须是惟一的error返回最近一次执行Transact-SQL语句的错误代码号，0表示成功fetch_status返回最近一次执行fetch 语句的游标状态值identity返回最近一次插入行的identity（标识列）列值idle返回SQL Server处于空闭状态的时间总量，单位为毫秒7.1.2 变量变量2局部变量局部变量：用户自定义的变量，其作用范围是声明它的批处理、存储过程或触发器等程序内部，一般用来存储从表中查询到的数据，或作为程序执行过程中暂存变量。局部变量必须以“”开头，且必须先用declare命令声明后才可使用。声明局部变量的语法格式如下：格式：declare 局部变量名 as 数据类型 ,n说明：（1）局部变量名必须符合标识符命名规则；（2）数据类型可以是系统数据类型，也可以是用户自定义数据类型，但不能定义为text，ntext或image数据类型。如有需要，还需指定数据宽度及小数精度；（3）声明多个局部变量名时，各变量名之间用逗号隔开；（4）局部变量声明后，系统自动初始化赋值为null，局部变量声明时不能同时赋值；（5）给局部变量赋值要用赋值语句，赋值语句有两种：set语句和select语句。7.1.2 变量变量Set语句格式：set =说明：将“表达式”的值赋给“局部变量名”指定的局部变量，一条语句只能给一个变量赋值。【例7-1】 计算两数之和。declare sum int,a as int,b as intset a=10set b=90set sum=a+bprint sum7.1.2 变量变量select语句格式：select =,n from,n where说明：（1）将“表达式”的值赋给“局部变量名”指定的局部变量，或者从筛选记录中计算出 “表达式”的值并赋给“局部变量名”指定的局部变量；（2）select既可以查询数据又可以赋值变量，但不能同时使用，如果select语句返回多个数值（多行记录），则局部变量只取最后一个返回值；（3）一条语句可以给多个变量分别赋值。【例7-2】 计算“选修”表中男生平均成绩和总成绩。use jxgldeclare avgscore float,sumscore floatselect avgscore=avg(成绩),sumscore=sum(成绩) from 学生,选修where 学生.学号=选修.学号 and 性别=男7.1.2 变量变量2全局变量全局变量：是SQL Server 2000系统定义并自动赋值的变量，其作用范围是所有程序，主要用来记录SQL Server服务器的活动状态。用户可以引用全局变量但不能改变它的值，全局变量必须以“”开头。SQL Server 2000提供了30多个全局变量，如表7-1所示：7.1.3 运算符运算符运算符是用来连接运算对象（或操作数）的符号，表达式是指用运算符将运算对象（或操作数）连接起来式子。Transact-SQL提供7类运算符及其对应表达式，分别是算术运算符及表达式、字符串连接运算符及表达式、赋值运算符及表达式、比较运算符及表达式、逻辑运算符及表达式、位运算符及表达式、一元运算符及表达式。1运算符（1）算术运算符算术运算符用于数值型数据的算数运算，算术运算符及其适用数据类型如表7-2所示。7.1.3 运算符运算符表7-2 算术运算符及其含义算术运算术运算符算符含义含义数据类型数据类型+、-、*、/加，减，乘，除int、smallint、tinyint、decimal、float、real、money、smallmoney%求余int、smallint、tinyin7.1.3 表达式表达式（2）关系运算符关系运算符用来比较两个表达式的值是否相同，比较的结果：值相同时为true，否则为false，当参与比较操作数含有null时，结果为unknown。关系运算符及其适用数据类型如表7-3所示。表7-3 关系运算符及其含义算术运算符含义数据类型+、-、*、/加，减，乘，除int、smallint、tinyint、decimal、float、real、money、smallmoney%求余int、smallint、tinyin注意：关系运算符又称为比较运算符，关系运算符不能用于text，ntext，image数据类型运算。另外。有时也把all、any、some、betweenand、in、like当作关系运算符。7.1.3 表达式表达式（3）位运算符位运算符用于对数据进行按位运算。位运算符及其含义如表7-4所示。表7-4 位运算符及其含义在进行整数数据的位运算时，先对整数转换为二进制数据，然后再进行按位计算；也可以对整数和二进制数据进行混合运算，但不能同时为二进制数据类型。位运算所支持的数据类型如表7-5所示。位运算位运算符符含义含义&位与，双目运算，参与运算的两个位值都是1时，结果为1，否则为0|位或，双目运算，参与运算的两个位值都是0时，结果为0，否则为1位异或，双目运算，参与运算的两个位值不同时，结果为1，否则为0位取反，单目运算，即1=0，0=17.1.3 表达式表达式（3）位运算符位运算符用于对数据进行按位运算。位运算符及其含义如表7-4所示。表7-5 参与位运算的左右操作数位运算符含义&位与，双目运算，参与运算的两个位值都是1时，结果为1，否则为0|位或，双目运算，参与运算的两个位值都是0时，结果为0，否则为1位异或，双目运算，参与运算的两个位值不同时，结果为1，否则为0位取反，单目运算，即1=0，0=17.1.3 表达式表达式（4）逻辑运算符逻辑运算符用来将多个关系表达式连接起来进行组合运算，返回值true或false。逻辑运算符及其含义如表7-6所示。表7-6 逻辑运算符及其含义逻辑运逻辑运算符算符含义含义not非运算，单目运算，对关系表达式的值取反，即not(true)=false，not(false)=trueand与运算，双目运算，参与运算的两个关系表达式值都是true时，才为true，否则为falseor或运算，双目运算，参与运算的两个关系表达式值都是false时，才为false，否则为true7.1.3 表达式表达式（5）字符串运算符字符串运算符是用来将两个字符串连接成一个新的字符串的运算符。字符串运算符只有一个，即加号（+）。（6）赋值运算符赋值运算符是将表达式的值赋给变量的运算符号。赋值运算符只有一个，即等号（=）。（7）一元运算符一元运算符只对一个表达式进行运算的运算符号，这个表达式的值可以是数值数据类型中的任何一种数据类型。一元运算符及其含义如表7-7所示。表7-7 一元运算符及其含义一元运算一元运算符符含义含义+表示数据的正号-表示数据的负号求一个数字的补数7.1.3 表达式表达式2运算符的优先级当混合使用多种运算符构成一个复杂的表达式时，表达式中有括号先算括号内，再算括号外；无括号时，运算符的优先级决定了运算的先后顺序，并影响计算的结果。运算符的优先级从高到底排列顺序如表7-8所示。注意：表中同一行各运算符优先级相同，当表达式中含有优先级相同的多个运算符时，根据它们在表达式中的位置，二元运算符按照从左到右的顺序执行，一元运算符按照从右到左的顺序执行。运算符优先级+（正）、-（负）、（按位取反）1*（乘）、/（除）、%（模）2+（加）、（+ 串联）、（减）3=、=、=、!=、!、!=60 begin print 该同学全部通过考试，没有挂考 end 7.2.2 二分支语句二分支语句ifelse语句和if not existselse语句是T-SQL语句提供的两种二分支结构。使用分支结构可以编写进行判断和选择操作的SQL语句（块）代码。1ifelse语句ifelse语句用于判断条件是true或false，并且根据判断结果指定要执行的语句。通常，条件是使用比较运算符对值或变量进行比较的表达式。语法格式如下：if sql语句1|语句块1else sql语句2|语句块2说明：条件表达式为true时，则运行ifelse之间的“sql语句1|语句块1”，否则，如果有else分支，则运行else之后的“sql语句2|语句块2”。【例7-4】 根据给定教师的姓名，查询出该教师的本校工龄是否在30年以上。if (select datediff(year,工作日期,getdate() from 教师 where 姓名=李教师)=30 print 该教师的工龄至少30年，可以提出退休申请else print 该教师的工龄不足30年，不可以提出退休申请7.2.2 二分支语句二分支语句2if not existselse语句if not existselse语句用于检测数据是否存在，而不考虑与之匹配的行数，对于存在性检测而言，使用if not exists要比count(*)0好，效率高。语法格式如下：if not existsql语句1|语句块1else sql语句2|语句块2说明：条件表达式为true时，则运行ifelse之间的“sql语句1|语句块1”，否则，如果有else分支，则运行else之后的“sql语句2|语句块2”。【例7-5】 根据给定课程的课程类型，如果存在就计算该种课程类型的门数。if exists(select * from 课程 where 课程类型=考查) select count(*) as 考查课门数 from 课程 where 课程类型=考查else print 没有考查课运行结果如图7-1所示。7.2.3 多分支表达式多分支表达式case表达式是计算多个条件的表达式，并返回其中一个符合条件的表达式结果。case表达式不是语句，不能独立运行，必须嵌入其它语句中才能起作用。case表达式提供两种格式：简单case表达式和搜索case表达式.7.2.3 多分支表达式多分支表达式1简单case表达式简单case表达式是将表达式与when子句中的值依次进行比较，直到发现第一个与表达式相等的值，便返回该when子句关联then子句中的值，不再判断后续when子句中的值。其语法格式如下：case when then when then when then else end as 表达式别名说明：（1）case函数在其开始处使用一个只计算一次的简单测试表达式。（2）表达式的结果依次与分支中的when子句值进行比较，一旦匹配，就返回when子句关联的then子句值，然后执行end后面的子句。（3）若所有when子句值都不满足表达式的结果，如果有else子句，则返回else子句值。7.2.3 多分支表达式多分支表达式【例7-6】 输出选修信息，并输出表中各课程号对应的课程名。select 学号,课程号,case 课程号 when 01 then 计算机基础 when 02 then ASP程序设计 when 03 then 数据库SQL Server else 其它课程end as 课程名称,成绩from 选修7.2.3 多分支表达式多分支表达式2搜索case表达式case when then when then when when else end说明：（1）依次计算when子句后的“逻辑表达式”，直到找到第一个值为true的“逻辑表达式”，就返回when子句关联的then子句中的结果值，不再判断后续when子句中的值（2）若所有的when子句后的“逻辑表达式”都不满足true，如果有else子句，则返回else子句中的结果值。7.2.3 多分支表达式多分支表达式【例7-7】 查询学生的相关信息，并将成绩按照以下规则替换，60分以下替换不及格，6085替换为合格，85分以上替换为优秀，其它替换为未考。select 学生.学号,姓名,成绩= case when 成绩 is null then 未考 when 成绩 60 then 不及格考 when 成绩 85 then 合格 else 优秀 end from 学生,选修 where学生.学号=选修.学号7.2.3 多分支表达式多分支表达式【例7-8】 计算各教师的各门各班课程的课酬信息，课酬计算公式=学时*课酬标准，其中课酬标准分别如下：教授为50，副教授为45，讲师为35，助教为30。select 教师.工号,课程.课程号,班级.班级号,课酬 = 学时* case when 职称=教授 then 50 when 职称=副教授 then 45 when 职称=讲师 then 35 else 30 end into 课酬 from 教师,课程,授课,班级 where 教师.工号=授课.工号 and 课程.课程号=授课.课程号 and 班级.班级号=授课.班级号goselect * from 课酬go运行结果如图7-2所示。7.2.4 循环语句循环语句可以使用whilecontinuebreak语句重复执行SQL语句或语句块。语法格式如下：while sql语句1|语句块1break sql语句2|语句块2continue说明：（1）首先判断条件是否为true，如果为true，则按顺序往下执行循环体，本次循环执行完毕后，回到while开始处，再次判断条件，如果为true，继续执行循环体，重复前面的步骤，直至while为假，跳出循环体，结束循环；（2）break语句是使程序完全跳出本层循环，结束整个循环体的执行；（3）continue语句是使程序终止本次循环，结束循环体中continue后面语句的执行，返回while开始处，重新开始下一次的while循环。7.2.4 循环语句循环语句【例7-9】 求1100之间的奇数之和。declare sum as intdeclare i as smallintset sum=0set i=0while i=99 break end print 1到100之间的奇数之和为：+convert(char(6),sum) /*输出和*/运行结果如图7-3所示。7.2.5 其它语句其他语句包括批处理语句，数据库切换语句，显示语句等，分别介绍如下：1批处理语句批处理是成组执行一条或多条Transact-SQL语句的语句或命令集合。批处理运行时是一次性分析，编译和执行。一系列顺序提交的批处理称为脚本。一个脚本中可以包含一个或多个批处理。批处理和批处理之间的定界是通过SQL Server的关键字go来定界。在SQL Server中使用批处理有如下限制：（1）大多数create语句不可以在同一个批处理中使用，如create procedure，create rule，create default，create trigger，create view不能混合使用；（2）不能在同一批处理中使用alter table命令修改表结构后，又立即引用其新增的列；（3）不能在同一批处理中，删除一个对象后又立即重建它；（4）用set语句改变的选项在批处理结束时生效。（5）如果在同一批处理中运行多个存储过程，则除第一个存储过程外，其余存储过程在调用时必须使用execute语句7.2.5 其它语句2切换数据库语句在代码中，使用use命令来切换数据库，其语法格式如下：格式：use 数据库名说明：将指定的数据库切换为当前数据库，才可对其及其中的对象做进一步操作。3显示语句Print语句用于向客户端输出信息，其语法格式如下：格式：print 任何ASCII文本|变量|全局变量|字符串表达式说明：（1）向客户端输出一个字符串，一个局部变量或全局变量；（2）如有必要，可用convert或cast函数将其它数据类型数据转换成字符串数据类型。7.2.5 其它语句4暂停语句waitfor语句是使程序暂停一段时长或暂停到某一时刻后继续执行。其语法格式如下：格式：waitfor delay hh:mm:ss|time hh:mm:ss说明：（1）delay关键字表示暂停到由“hh:mm:ss”指定的时长间隔后，再继续执行其后语句，时长最大值为24小时；（2）time关键字表示暂停到由“hh:mm:ss”指定的时刻点，再继续执行其后语句。5注释语句注释是用来说明程序代码的含义，提高程序的可读性，使得日后维护程序更加容易。SQL Server提供了两种形式：格式1：-注释语句格式2：/*注释语句*/说明：（1）-（双连字符）用于单行注释，从双连字符开始到结尾都是注释语句，一般放在程序后面，也可以单独另起一行；（2）/*/用于多行注释，位置比较自由，既可以在放在程序代码后面，也可另起一行，甚至放在程序代码内部。（3）/*/不能跨越批处理，整个注释必须包含在一个批处理中。7.2.5 其它语句【例7-10】 查询学生选修成绩表。use jxgl -切换数据库jxgl为当前数据库select * from 选修where left(学号,6)=080101/*筛选条件*/order by 籍贯 asc /*升序输出，默认值为ASC*/7.2.5 其它语句6无条件退出语句return语句可以出现在T-SQL语句的批处理，语句块和存储过程中的任何位置，其作用无条件地从存储过程、批处理或语句块中退出，其后的语句不会被执行。语法格式如下：格式：return说明：（1）结束当前程序的运行，返回到调用它的上一级程序；（2）整数值是被调用的存储过程向父进程报告本进程的执行状态；（3）如果没有指定返回值，SQL Server系统会根据程序执行的结果返回一个内定值（991），常见内定值及其含义如表7-9所示。7.2.5 其它语句表7-9 内定值及其含义返回值返回值含义含义返回值返回值含义含义0程序执行成功-7资源错误-1找不到对象-8非致命错误-2数据类型错误-9已达到系统的极限-3死锁-10，-11致命的内部不一致错误-4违反权限原则-12表或指针错误-5语法错误-13数据库破环-6用户造成的一般错误-14硬件错误7.2.5 其它语句7无条件跳转语句goto语句改变程序的执行流程，使程序流程被无条件地转移到有标号的语句处继续执行，而位于goto语句和标号之间的语句不会被执行。语法格式如下：格式：goto标号 标号:说明：（1）goto语句和标号可以用在语句块、批处理中和存储过程中，标号可以是数字和字符的组合，但必须以冒号（:）结尾；（2）goto语句破坏了程序结构化的特点，使得程序结构变得复杂而难以理解，建议不用；（3）goto语句实现的逻辑结构完全可以使用其它语句实现，goto语句最好用于跳出深层次嵌套的控制流语句。7.2.5 其它语句【例7-11】 查询选修表，如果其中存在学号为08010101的学生，那么就显示“该学生的成绩存在”，并查询出该学生所有课程的成绩，否则跳转过这些语句，显示“没有此学生的成绩”。if (select count(*) from 选修 where 学号=08010101)=0goto noationbegin print 该学生的成绩存在 select 学号,课程号,成绩 from 选修 where 学号=08010101endnoation: print 该学生的成绩不存在7.2.5 其它语句8返回错误代码语句将报错信息显示在屏幕上，同时记录在NT日志中，其语法格式如下：格式：raiserror (msg_id|msg_str,serverity,state,argument,n)with option,n说明：（1）msg_id是存储于sysmessages表中的用户定义的错误信息标识号。户定义的错误信息标识号应大于50000。由特殊消息产生的错误号是第50000号；（2）msg_str是一条特殊的消息，此消息最多包含4000个字符；（3）serverity表示用户定义的与消息关联的严重级别，用户可以从018之间的严重级别，1925之间的严重级别只能由系统管理员引发。严重等级在25以上的错误在使用raiserror引发时，必须选择with log选项；（4）state从1127的任意整数，表示有关错误发生的状态信息；（5）with option给出raiserror的选项，option取值如表7-10所示。7.2.5 其它语句值含义log错误记录到SQL Server错误日志中和windows NT应用程序日志中nowait将错误消息发送到客户端seterror始终将全局变量error中的值置为用户自定义的报错消息的错误代码或50000表7-10 option取值及其含义7.2.5 其它语句【例7-12】 在屏幕上显示一条信息，在信息中列出当前使用的数据库标识号和名称，信息由格式化字符串直接给出。use jxglgodeclare dbid intset dbid =db_id()declare dbname nvarchar(128)set dbname=db_name()raiserror(当前数据库的id值为：%d,数据库名为：%s.,16,1,dbid,dbname)go运行如果如图7-4所示。图7-4 例7-12运行结果7.3 内置函数内置函数函数是由一条或多条Transact-SQL语句组成的集合，用于完成某个特定的功能。SQL Server提供了两种类型的函数：内置函数和用户自定义函数，用户可以直接调用这些函数。内置函数是系统预定义的函数，是Transact-SQL语言的一部分，一般分为三大类：（1）行集函数：返回的结果是对象，该对象可在Transact-SQL语句中用作表来引用。例如，使用openquery函数执行一个分布式查询，以便从服务器shuju中提取表“student”中的记录。select * from openquery(shuju,select name, id from student)（2）聚合函数：对一组值进行处理和计算，并返回一个单列值。例如，设在当前数据库中拥有一个员工工资表“employee”，其中有一个工资列“salary”，统计所有员工的工资总和，语句为：select sum(salary) from employee（3）标量函数：对传递给它的一个或者多个值进行处理和计算，并返回一个单列值。7.3.1 数学函数数学函数常见的数学函数如表7-11所示。函数名功能函数名功能abs(x)求绝对值log10(x)求以10为底的自然对数sqrt(x)求平方根round(x,n) n0为保留小数位square(x)求平方ceiling(x)求大于等于给定数的最小整数power(x,y) 求x的y次方floor(x)求小于等于给定数的最大整数sin(x)求正弦值pi()返回圆周率cos(x)求余弦值radians(x) 将角度值转换为弧度值tan(x)求正切值degrees(x) 将弧度值转换为角度值log(x)求自然对数sign(x)求一个数的符号exp(x)求指数值rand(x)随机数7.3.2 聚合函数聚合函数常用的系统函数如表7-12所示。聚合函数功能avg(distinct|all表达式)返回表达式(含列名)的平均值，distinct是去掉重复值，all是所有值count(distinct|all表达式) 对表达式指定的列值进行计数，忽略空值，distinct|all含义同上count(distinct|all *)对表或组中的所有行进行计数，包含空值，distinct|all含义同上max(distinct|all表达式)表达式中最大的值，distinct|all含义同上min(distinct|all表达式)表达式中最小的值，distinct|all含义同上sum(distinct|all表达式)表达式值的合计，distinct|all含义同上7.3.2 聚合函数聚合函数【例7-13】 查询学生表中学生总数select count(*) from 选修【例7-14】 统计参加选修课程的学生数。select count(distinct 学号) from 选修【例7-15】 统计学生表中各班级现有男生学生数。select 班号=left(学号,6),count(*) as 人数 from 学生 where 性别=男 group by left(学号,6)注意：只有group by 子句中列才能与聚集函数同时出现在select子句中。【例7-16】 统计选修表中各门课程的最高分、最低分、平均分。select 课程号,max(成绩),min(成绩),avg(成绩) from 选修 group by 课程号7.3.3 系统函数系统函数常用的系统函数如表7-13所示。函数名功能host_id()客户进程的当前住进程的ID号host_name()返回服务器端的计算机的名称suser_sid()返回SQL Server sa登录名的安全标识号db_id()返回指定数据的标志iddb_name()根据数据库的标志ID返回相应的数据库的名字datbaseproperty(数据库,属性名) 返回指定数据库在指定属性上的取值object_id(对象名)返回指定数据库对象的表示IDobject _name(对象Id)根据数据库的标志ID返回相应的数据库对象名字object eproperty(对象Id,属性名)返回指定数据库对象在指定属性上的取值值col_length(数据库表名,列名)返回指定表的指定列的长度col_name(数据库表Id,列序号)返回指定表的指定列的名字7.3.4 字符串函数字符串函数常见的字符串函数如表7-14所示。函数名功能函数名功能upper(str)将字符串转化大写lower(str)将字符串转化小写ltrim(str)删除字符串左边的空格rtrim(str)删除字符串右边的空格char(n)求ASCII码值对应的字符replicate(str,n)字符串连续输出n次left(str,n)从左边获取n个字符串right(str,n)从右边获取n个字符串space(n)输出n个空格nchar(n)返回unicode字符reverse(str)反转输出字符串datalength(str)返回所占字符串的字节数ascii(str)求字符串中第一个字符的ASCII码值charindex(str1,str2,n)从字符串str1中指定位置n处查找字符串str2replace(str1,str2,str3)用字符串str3替换字符串str1中出现的字符串str2stuff(str1,n,m,str2)将str1从位置n到m的字符串替换为str2str(value,n,m) 数字转换成长度为n的字符串，同时含m位小数patindex(%subs%,str)查找字符串str中指定格式的字符串subslen(str)求字符串的字符个数，不包括尾部空格substring(str,n,m)从字符串中指定位置n处开始取m个字符7.3.4 字符串函数字符串函数【7-17】 在“教师”表中查找姓名以“李”开头的教师。select patindex(%李_%,姓名) from 教师7.3.5 日期和时间函数日期和时间函数用于对日期和时间数据进行各种不同的处理或运算，并返回一个字符串、数字值或日期和时间值，常见的字符串表达式如表7-15所示。函数名功能getdate()返回当前系统日期和时间dateadd(间隔因子,n,d)计算日期时间加d加上数字n后日期时间，间隔因子如表7-16所示datediff (间隔因子,d1,d2)计算d1-d2时间间隔，间隔因子如表7-16所示datename(间隔因子,d)返回日期时间d的的名称，如datename(month,1980-3-4)=03day(d)返回日的值month(d)返回月的值year(d)返回年的值表7-15 日期和时间函数及其功能7.4.5 日期和时间函数日期和时间函数时间间隔因子可以使用年月日等表示日期时间的英文全称，也可以使用缩略字母，缩略为字母形式如表7-16所示。表7-16 间隔因子及其功能全全称称yearmonthdayquarterdayofyearweekdayweekhourminutesecondmilliminute简称yyyy|yym|mmdd|dqq|qdy|ywk|wwdwhhmi|nss|sms说明年月月内日数季度年内日数年内周数星期几小时分钟秒毫秒7.4.6 转换函数转换函数一般情况下，SQL Server会自动完成各数据类型之间的转换，有时，自动转换的结果不符合预期结果，这时可考虑利用转换函数进行转换。SQL Server 2000提供了两个转换函数：cast()函数和convert()函数。1cast函数用于将某种数据类型的表达式显式转换为另一种数据类型的数据。格式：cast(表达式 as 数据类型)7.4.6 转换函数转换函数2convert函数也用于将某种数据类型的表达式显式转换为另一种数据类型的数据，convert()函数的优点是可以格式化日期和数值型数据，在将日期时间类型的数据转换为字符类型的数据时，还可以指定转换后的字符样式。格式：convert(数据类型(长度),表达式,格式码)说明：（1）第1个是目标数据类型；（2）第2个是源数据；（3）第3个是可选参数，用于输出字符串格式，适用于日期时间型数据或数值型数据、货币型数据转换为字符型数据，如果格式码为NULL，则返回的结果也为NULL；（4）预定义的符合国际和特殊要求的日期时间输出格式码有30种，如表7-17所示。7.4.6 转换函数转换函数表7-17 convert()函数格式码说明及示例格式码格式码年份位数年份位数小时格式小时格式说明说明示例示例0212默认Apr 25 2005 1:05PM12 美国04/24/0522 ANSI05.04.2532 英国/法国25/04/0542 德国25.04.0552 意大利25-04-0562 定制-仅日期25 Apr 0572 定制-仅日期Apr 25,058 24定制-仅时间13:05:359412默认，毫秒Apr 25 2005 1:05:35:123 PM7.4.6 转换函数转换函数102 美国美国04-25-05112 日本05/04/25122 ISO05042513424欧洲25 Apr 2005 13:05:35:12314 24定制时间，毫秒13:05:35:123100412默认Apr 25 20051:05PM1014 美国04/24/051024 ANSI2005.04.251034 英国/法国25/04/20051044 德国25.04.2005表7-17 convert()函数格式码说明及示例7.4.6 转换函数转换函数1054 意大利意大利25-04-051064 定制-仅日期25Apr20051074 定制-仅日期Apr25，2005108 24定制-仅时间13:05:35109412默认，毫秒Apr 252