《大型数据库第3章transactsql语言》由会员分享，可在线阅读，更多相关《大型数据库第3章transactsql语言（104页珍藏版）》请在金锄头文库上搜索。
1、Monday, July 22, 2024第1页第第3章使用章使用Transact-SQL编程编程本章概述 本章要点本章内容心芍潞盯勒凶沂哄仓委椭镐换磋级助诸夷试分极目惹潍玖蕊耍愿而话镀坛大型数据库第3章transactsql语言大型数据库第3章transactsql语言Monday, July 22, 2024第2页本章概述本章概述l如何方便地在数据库中执行访问数据库对象、查询业务数如何方便地在数据库中执行访问数据库对象、查询业务数据等各种操作呢？这需要掌握使用据等各种操作呢？这需要掌握使用Transact-SQL语言的语言的技术。技术。Transact-SQL语言是微软公司在关系型数据库管
2、语言是微软公司在关系型数据库管理系统理系统Microsoft SQL Server中的中的ISO SQL的实现。的实现。SQL(Structure Query Language，结构化查询语言，结构化查询语言)语语言是国际标准化组织言是国际标准化组织(International Standardize Organization，ISO)采纳的标准数据库语言。通过使用采纳的标准数据库语言。通过使用Transact-SQL语言，用户几乎可以完成语言，用户几乎可以完成SQL Server数据数据库中的各种操作。本章全面介绍库中的各种操作。本章全面介绍Transact-SQL语言的特语言的特点和使用方
3、式。点和使用方式。闰去囚残砍臆蝉打拧窥全壶在告孩栗烤嚏香盼猾料痘饭讶僧拌窍肉迭士姑大型数据库第3章transactsql语言大型数据库第3章transactsql语言Monday, July 22, 2024第3页本章要点本章要点lTransact-SQL语言和语言和SQL语言之间的关系语言之间的关系lTransact-SQL语言的特点和执行方式语言的特点和执行方式l数据定义语言的类型和特点数据定义语言的类型和特点l数据操纵语言的类型和特点数据操纵语言的类型和特点l数据控制语言的类型和特点数据控制语言的类型和特点l事务管理语言的类型和特点事务管理语言的类型和特点l附加语言元素的类型和特点附加语
4、言元素的类型和特点晰规妨愁明氏赎污历责亢虞潭旦普宣舀痰彼惩巾施唱杨昭其榔淄藏苞庚棍大型数据库第3章transactsql语言大型数据库第3章transactsql语言Monday, July 22, 2024第4页本章内容本章内容第一节第一节 Transact-SQL语言简介语言简介第二节第二节 函数函数第三第三节节 增强功能增强功能本章小结本章小结粹茸苍躯疑弥褥欺靠鼎癌仆吹妒连郝歧酌酶闻载腰郧痈迸企胁寓诞灭慨溺大型数据库第3章transactsql语言大型数据库第3章transactsql语言第一节第一节 Transact-SQL语言简介语言简介l1970年年6月，埃德加月，埃德加考特考特(
5、Edgar Frank Codd)在在Communications of ACM 上发表了大型共享上发表了大型共享数据库数据的关系模型一文。首次明确而清晰数据库数据的关系模型一文。首次明确而清晰地为数据库系统提出了一种崭新的模型，地为数据库系统提出了一种崭新的模型， 即关系即关系模型。模型。1970 年以后，考特继续致力于完善与发展年以后，考特继续致力于完善与发展关系理论。关系理论。1972 年，他提出了关系代数和关系演年，他提出了关系代数和关系演算的概念，定义了关系的并、交、投影、选择、算的概念，定义了关系的并、交、投影、选择、连接等各种基本运算，为连接等各种基本运算，为SQL语言的形成和发
6、展语言的形成和发展奠定了理论基础。奠定了理论基础。1979年，年，SQL在商业数据库中在商业数据库中成功得到了应用。成功得到了应用。Monday, July 22, 2024第5页懂膀邪弹崎登怪漆仅昆破拍腰估西瓣博需溃留呵讥蚕巫请奈屯疯熔瘪造枯大型数据库第3章transactsql语言大型数据库第3章transactsql语言标准版本标准版本l1986年，美国国家标准化组织正式发表了编号为年，美国国家标准化组织正式发表了编号为X3.135-1986的的SQL标准，并且在标准，并且在1987年得到了年得到了ISO组织的认可，组织的认可，被命名为被命名为ISO9075-1987。l后来这个标准在后
7、来这个标准在1992、1999、2003、2006、2008年等不年等不断地得到了扩充和完善。断地得到了扩充和完善。1992年发布的标准是年发布的标准是SQL92，也称为也称为SQL2。1999年发布的标准称为年发布的标准称为SQL:1999，也称，也称为为SQL3。该版本增加了迭代查询、触发器、控制流以及。该版本增加了迭代查询、触发器、控制流以及面向对象功能。面向对象功能。2003年，年，SQL标准引入了标准引入了XML支持、自支持、自动生成值等特征。动生成值等特征。2006年的标准在年的标准在XML数据的存储和查数据的存储和查询方面有了更多的增强。询方面有了更多的增强。Monday, Ju
8、ly 22, 2024第6页温蒋戊唤醚席澄祭高吸姥必驭蛾子炙斑恒第虚拇暗耍双坚矢鼻垢艰光尚荐大型数据库第3章transactsql语言大型数据库第3章transactsql语言Transact-SQL语言语言lTransact-SQL语言是微软公司在语言是微软公司在Microsoft SQL Server系统中使用的语言，系统中使用的语言，是对是对SQL语言的一种扩展形式。语言的一种扩展形式。lTransact-SQL语言是一种交互式查询语言，语言是一种交互式查询语言，具有功能强大、简单易学的特点。具有功能强大、简单易学的特点。Monday, July 22, 2024第7页屏等港坟妖鲁念泽骏
9、戮凿口立颗粳叠冤世庸俞忙困栅臀夷假炳纂找撞闺稿大型数据库第3章transactsql语言大型数据库第3章transactsql语言特点特点Transact-SQL语言有语言有4个特点：个特点：l一是一体化的特点，集数据定义语言、数据操纵语言、数一是一体化的特点，集数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加语言元素为一体。据控制语言、事务管理语言和附加语言元素为一体。l二是有两种使用方式，即交互使用方式和嵌入到高级语言二是有两种使用方式，即交互使用方式和嵌入到高级语言中的使用方式。中的使用方式。l三是非过程化语言，只需要提出三是非过程化语言，只需要提出“干什么干什么”，不需要指
10、出，不需要指出“如何干如何干”，语句的操作过程由系统自动完成。，语句的操作过程由系统自动完成。l四是类似于人的思维习惯，容易理解和掌握。四是类似于人的思维习惯，容易理解和掌握。Monday, July 22, 2024第8页彝忌吨撰惦旭伎势祸毗弦嘱搭棵烩骡燕柑徒交看路冬袍易跃憾湛呐伎汾咽大型数据库第3章transactsql语言大型数据库第3章transactsql语言类型类型l在在Microsoft SQL Server 2008系统中，根系统中，根据据Transact-SQL语言的功能特点，可以把语言的功能特点，可以把Transact-SQL语言分为语言分为5种类型，即数据种类型，即数据定
11、义语言、数据操纵语言、数据控制语言、定义语言、数据操纵语言、数据控制语言、事务管理语言和附加的语言元素。事务管理语言和附加的语言元素。Monday, July 22, 2024第9页浆陛吹庶枪琴蹭妄婚拟檄临王樟住搓劲豫臭能赠威衫弘世鼻串食巳拥嚎子大型数据库第3章transactsql语言大型数据库第3章transactsql语言Transact-SQL语言的执行方式语言的执行方式l在在Microsoft SQL Server 2008系统中，主系统中，主要使用要使用SQL Server Management Studio工工具来执行具来执行Transact-SQL语言编写的查询语句。语言编写的
12、查询语句。l除此之外，还可以使用除此之外，还可以使用sqlcmd实用命令、实用命令、PowerShell工具来执行工具来执行Transact-SQL语句。语句。l下面主要介绍下面主要介绍SQL Server Management Studio工具的特点。工具的特点。Monday, July 22, 2024第10页殿功嗅忧后橡渴智莎溯走硕俱距谆矩毡翼闯蛀纶刀眷蟹料片闪鞘怠双即利大型数据库第3章transactsql语言大型数据库第3章transactsql语言在查询窗口中执行在查询窗口中执行Transact-SQL语句语句Monday, July 22, 2024第11页某缘前妓峰访扑以原瓜透
13、仲乔施民筛凡贪宰苫帆挣档逾诞赋主构菇刹么挚大型数据库第3章transactsql语言大型数据库第3章transactsql语言数据定义语言数据定义语言l数据定义语言用于创建数据库和数据库对数据定义语言用于创建数据库和数据库对象，为数据库操作提供对象。象，为数据库操作提供对象。l例如，数据库以及表、触发器、存储过程、例如，数据库以及表、触发器、存储过程、视图、索引、函数、类型及用户等都是数视图、索引、函数、类型及用户等都是数据库中的对象，需要定义后才能使用。据库中的对象，需要定义后才能使用。l在在DDL中，主要的中，主要的Transact-SQL语句包括语句包括CREATE语句、语句、ALTER
14、语句和语句和DROP语句。语句。Monday, July 22, 2024第12页锄睹豌示铭稼鸡旅狞至墒汉痔召痔淆钝谐惨稚悯蹋融毡押恃嘿兽缩篮膝劲大型数据库第3章transactsql语言大型数据库第3章transactsql语言CREATE语句示例语句示例Monday, July 22, 2024第13页犊骡汗箭耙溢头柿溺吾虾驮寨寞滇仕赔壹豆宰轴腻析陨功危会隔泅酮童铱大型数据库第3章transactsql语言大型数据库第3章transactsql语言ALTER语句示例语句示例Monday, July 22, 2024第14页仪工久饭驯瞳巍肖龚鸳折娥谬烁捻央巡伊唆豺倚藩肢地淬愚龟扛鼓婆锁廷大型
15、数据库第3章transactsql语言大型数据库第3章transactsql语言DROP语句示例语句示例Monday, July 22, 2024第15页进鹤短嚼唉干力用耀奠毡痞腺尘竭幅伪危言凛居杀躯胁貌耪哮舶镜显兢奥大型数据库第3章transactsql语言大型数据库第3章transactsql语言数据操纵语言数据操纵语言l数据操纵语言主要是用于操纵表、视图中数据的数据操纵语言主要是用于操纵表、视图中数据的语句。当创建表对象之后，初始状态时该表是空语句。当创建表对象之后，初始状态时该表是空的，没有任何数据。如何向表中添加数据呢？这的，没有任何数据。如何向表中添加数据呢？这时需要使用时需要使用
16、INSERT语句。如何检索表中数据呢语句。如何检索表中数据呢？可以使用？可以使用SELECT语句。如果表中数据不正确，语句。如果表中数据不正确，可以使用可以使用UPDATE语句进行更新。当然，也可以语句进行更新。当然，也可以使用使用DELETE语句删除表中的数据。语句删除表中的数据。l实际上，实际上，DML语言包括了语言包括了INSERT、SELECT、UPDATE和和DELETE等语句。等语句。Monday, July 22, 2024第16页夜使惑沤诵疗拍妙霄询帝额促邵误豢呕季徽于史踊绵叁旺嗡格父答夺个澎大型数据库第3章transactsql语言大型数据库第3章transactsql语言附
17、加的语言元素附加的语言元素l除了前面介绍的语句之外，除了前面介绍的语句之外，Transact-SQL语言还包括了附加的语言元素。语言还包括了附加的语言元素。l这些附加的语言元素主要包括标识符、变这些附加的语言元素主要包括标识符、变量和常量、运算符、表达式、数据类型、量和常量、运算符、表达式、数据类型、函数、控制流语言、错误处理语言和注释函数、控制流语言、错误处理语言和注释等。等。l下面，详细介绍这些内容。下面，详细介绍这些内容。Monday, July 22, 2024第17页脯夜舶哦暗敏腕缴综屿达瘁拨杆来跨栓裁新罩酪笼纠姆腻酥板伺安贰吱鸯大型数据库第3章transactsql语言大型数据库第
18、3章transactsql语言标识符标识符l在在Transact-SQL语言中，数据库对象的名称就是语言中，数据库对象的名称就是其标识符。其标识符。l在在Microsoft SQL Server系统中，所有的数据库系统中，所有的数据库对象都可以有标识符，例如服务器、数据库、表、对象都可以有标识符，例如服务器、数据库、表、视图、索引、触发器和约束等。大多数对象的标视图、索引、触发器和约束等。大多数对象的标识符是必需的，例如，创建表时必须为表指定标识符是必需的，例如，创建表时必须为表指定标识符。但是，也有一些对象的标识符是可选的，识符。但是，也有一些对象的标识符是可选的，例如，创建约束时用户可以不
19、提供标识符，其标例如，创建约束时用户可以不提供标识符，其标识符由系统自动生成。识符由系统自动生成。Monday, July 22, 2024第18页宴愤宣绍途控凯煎汽锗捻厅窟聚恩壤誊囤叼涝厂雁姜膀彝痢弹虎暗喊恰马大型数据库第3章transactsql语言大型数据库第3章transactsql语言变量和常量变量和常量l常量是表示特定数据值的符号，常量也被称为字面量。常量是表示特定数据值的符号，常量也被称为字面量。l常量的格式取决于它所表示的值的数据类型。例如，常量的格式取决于它所表示的值的数据类型。例如，This is a book.、May 1, 2006、98321等都是常等都是常量。对于字
20、符常量或时间日期型常量，需要使用单引号量。对于字符常量或时间日期型常量，需要使用单引号引起来。引起来。l当字符串中有单引号时，则用两个单引号来表示。当字符串中有单引号时，则用两个单引号来表示。lUnicode常量必须有一个大写的常量必须有一个大写的N来区别字符串常量。来区别字符串常量。Monday, July 22, 2024第19页胎隅帚卿破鞘症燃氏吾沦吩江趋聘诈趣谆父盈渝伶岂薄驱莫龟将依里催到大型数据库第3章transactsql语言大型数据库第3章transactsql语言变量和常量变量和常量l在在Microsoft SQL Server 2008系统中，变系统中，变量也被称为局部变量，
21、是可以保存单个特定量也被称为局部变量，是可以保存单个特定类型数据值的对象。类型数据值的对象。l全局变量以全局变量以开头命名，局部变量以开头命名，局部变量以开头命开头命名。名。l全局变量由系统提供，用于存储一些系统信全局变量由系统提供，用于存储一些系统信息，只可以使用全局变量，不可以自定义全息，只可以使用全局变量，不可以自定义全局变量。局变量。Monday, July 22, 2024第20页孪样翱睬融湘端滋池钾务杨梭留薛倍钱户鲍坠冤幂字制进鸵秸刨省巩硬窃大型数据库第3章transactsql语言大型数据库第3章transactsql语言声明和赋值声明和赋值l在在Transact-SQL语言中，
22、可以使用语言中，可以使用DECLARE语句声明语句声明变量。变量。在声明变量时需要注意：在声明变量时需要注意：第一，为变量指定名称，且名称的第一个字符必须是第一，为变量指定名称，且名称的第一个字符必须是；第二，指定该变量的数据类型和长度；第二，指定该变量的数据类型和长度；第三，默认情况下将该变量值设置为第三，默认情况下将该变量值设置为NULL。l可以在一个可以在一个DECLARE语句中声明多个变量，变量之间使语句中声明多个变量，变量之间使用逗号分割开。变量的作用域是可以引用该变量的用逗号分割开。变量的作用域是可以引用该变量的Transact-SQL语句的范围。变量的作用域从声明变量的语句的范围
23、。变量的作用域从声明变量的地方开始到声明变量的批处理的结尾。地方开始到声明变量的批处理的结尾。Monday, July 22, 2024第21页浦丁饿慢去诉境雪豫捧夹樱肉愧茵冷您睬龙股妈占殷固动割舌恭爽捞檬磋大型数据库第3章transactsql语言大型数据库第3章transactsql语言l有两种为变量赋值的方式：使用有两种为变量赋值的方式：使用SET语句为语句为变量赋值和使用变量赋值和使用SELECT语句选择列表中当语句选择列表中当前所引用值来为变量赋值。前所引用值来为变量赋值。l使用使用select和和print语句可以显示变量内容。语句可以显示变量内容。牲肚瞪墅蝎猎秦朵蔓忆茸诧獭途崎栅
24、杀术皖藉酶坝贯茅轴坝瞧掸涌爽汤坤大型数据库第3章transactsql语言大型数据库第3章transactsql语言使用变量使用变量Monday, July 22, 2024第23页例：查询计算机系学生人数，并输出。declare renshu intselect renshu=count(*) from student where sdept=CSprintCS人数：+cast(renshu as varchar(10)杜云展踪拳该朴扦能津吻路弊捌荡鞍腰抉烯躲四允疯穆瓦颧籽鸿者韭贺那大型数据库第3章transactsql语言大型数据库第3章transactsql语言运算符运算符l运算符是一种
25、符号，用来指定要在一个或运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。在多个表达式中执行的操作。在Microsoft SQL Server 2008系统中，可以使用的运算系统中，可以使用的运算符可以分为算术运算符、逻辑运算符、赋符可以分为算术运算符、逻辑运算符、赋值运算符、字符串串联运算符、按位运算值运算符、字符串串联运算符、按位运算符、一元运算符和比较运算符等。符、一元运算符和比较运算符等。Monday, July 22, 2024第24页处咆峰喝幂放芒不侈侍曲都速垣挽漏蚁江滚发莱橇倔瑚醛嘿榨携痈又挟酸大型数据库第3章transactsql语言大型数据库第3章transacts
26、ql语言使用赋值运算符使用赋值运算符Monday, July 22, 2024第25页珐拧锁脖棉逼款疹浸妮硼额瞬过百负悯逗竟灌豹祷灼夕姬鬼搔独遭较帅厄大型数据库第3章transactsql语言大型数据库第3章transactsql语言使用字符串串联运算符使用字符串串联运算符Monday, July 22, 2024第26页灵晕兽搂皖壬挑压啦戍九墒晚巨砧做崭训募窟劝察落龋缨承赖摩堤婉豁谍大型数据库第3章transactsql语言大型数据库第3章transactsql语言使用位运算符使用位运算符Monday, July 22, 2024第27页演猛操瞳晓酱硷注缚向氏其侮偿朴倦报猪巩趾键倘咏徒洛庶惫
27、祭酬袒拟乒大型数据库第3章transactsql语言大型数据库第3章transactsql语言表达式表达式l在在Transact-SQL语言中，表达式是由标识符、变量、常语言中，表达式是由标识符、变量、常量、标量函数、子查询以及运算符等的组合。在量、标量函数、子查询以及运算符等的组合。在Microsoft SQL Server 2008系统中，表达式可以在多个系统中，表达式可以在多个不同的位置使用，这些位置包括查询中检索数据的一部分、不同的位置使用，这些位置包括查询中检索数据的一部分、搜索数据的条件等。搜索数据的条件等。l表达式可以分为简单表达式和复杂表达式两种类型。简单表达式可以分为简单表达
28、式和复杂表达式两种类型。简单表达式只是一个变量、常量、列名或标量函数，复杂表达表达式只是一个变量、常量、列名或标量函数，复杂表达式是由两个或多个简单表达式通过使用运算符连接起来的式是由两个或多个简单表达式通过使用运算符连接起来的表达式。在复杂表达式中，两个或多个表达式有相同的数表达式。在复杂表达式中，两个或多个表达式有相同的数据类型，优先级低的数据类型可以隐式地转换为优先级高据类型，优先级低的数据类型可以隐式地转换为优先级高的数据类型。的数据类型。Monday, July 22, 2024第28页仍贩冶夕拘漆檄阉标穷悠丸诬趟棱哇浪欣聪良召扔誊批蘸瑰村康锨枚铝赊大型数据库第3章transacts
29、ql语言大型数据库第3章transactsql语言注释注释l所有的程序设计语言都有注释。注释是程序代码所有的程序设计语言都有注释。注释是程序代码中不执行的文本字符串，用于对代码进行说明或中不执行的文本字符串，用于对代码进行说明或暂时仅用正在进行诊断的部分语句。暂时仅用正在进行诊断的部分语句。l一般地，注释主要描述程序名称、作者名称、变一般地，注释主要描述程序名称、作者名称、变量说明、代码更改日期以及算法描述等。量说明、代码更改日期以及算法描述等。l在在Microsoft SQL Server系统中，支持两种注释系统中，支持两种注释方式，即双连字符方式，即双连字符(-)注释方式和正斜杠星号字注释
30、方式和正斜杠星号字符对符对(/*/)注释方式。注释方式。Monday, July 22, 2024第29页硒毫叫皆炯踊系铂抽寞垄拟藏陪按沿蛀侮敬墒蔗宽血磷玖滇淹锋乐烘舟漠大型数据库第3章transactsql语言大型数据库第3章transactsql语言使用注释使用注释Monday, July 22, 2024第30页陆碱梁误掸搁赦尹尔洁伯拖危眷蒸镁斗疽腺旦歉砰股淬探啤吼簿祈踩欲崭大型数据库第3章transactsql语言大型数据库第3章transactsql语言控制流语言控制流语言l一般地，结构化程序设计语言的基本结构一般地，结构化程序设计语言的基本结构是顺序结构、条件分支结构和循环结构。是
31、顺序结构、条件分支结构和循环结构。顺序结构是一种自然结构，条件分支结构顺序结构是一种自然结构，条件分支结构和循环结构都需要根据程序的执行状况对和循环结构都需要根据程序的执行状况对程序的执行顺序进行调整。程序的执行顺序进行调整。l在在Transact-SQL语言中，用于控制语句流语言中，用于控制语句流的语言被称为控制流语言。的语言被称为控制流语言。Microsoft SQL Server 2008系统提供了系统提供了8种控制流语句。种控制流语句。Monday, July 22, 2024第31页泅扰搅分泄稠赛估赃耻纪笨叮蛀配杠技防唁久础发胃套奇枚揖斋一妇康豹大型数据库第3章transactsql
32、语言大型数据库第3章transactsql语言1.Beginend2.Ifelse3.while4.break5.continue6.goto7. Return8. wairfor菜慕霄某煎目唬器寄筷更锡晶略磨尊支纸颓线菇窄汹叙图剥压莹艘珍聋斯大型数据库第3章transactsql语言大型数据库第3章transactsql语言Beginend, Ifelse例：查询并显示MA系人数。begindeclare renshu intselect renshu=count(*) from student where sdept=MAif renshu=0 print MA系无人elseprintMA
33、人数：+cast(renshu as varchar(10)end哇镜哺孵清坯臂风观写饼测敷却篓购揉坎官贤椽廓帖抱讲马滴歌七辈牲裂大型数据库第3章transactsql语言大型数据库第3章transactsql语言while语法：语法：WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE sql_statement | statement_block BREAK：导致从最内层的：导致从最内层的 WHILE 循环中退出循环中退出CONTINUE
34、：使：使 WHILE 循环重新开始执行，忽略循环重新开始执行，忽略 CONTINUE 关键字后面的任何语句。关键字后面的任何语句。暮寄哈霓蚜殆汽椎延一热惮论勺瀑循术鞘蔬蚜祟朱炕坟遂姜疮渊挡在诀撤大型数据库第3章transactsql语言大型数据库第3章transactsql语言while例：求例：求1到到10的累加和。的累加和。declare i int,sum intset i=1set sum=0while(i=90 then 优秀优秀when grade=80 then 良好良好when grade=70 then 中等中等when grade=60 then 及格及格when grad
35、e60 then 不及格不及格endfrom sc涤贷斟铜千倘陈恢巍故猿换椭予停凹藤股刨欺皑柯操氏元畦缚镍吼改妖空大型数据库第3章transactsql语言大型数据库第3章transactsql语言3.排名函数排名函数Monday, July 22, 2024第71页（1）rank返回结果集的分区内每行的排名。行的排名是相关行之前的返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。排名数加一。语法：语法： RANK( ) OVER()参数：参数：将：将FROM子句生成的结果集划分子句生成的结果集划分成成RANK函数适用的分区。函数适用的分区。：确定将：确定将RANK值应用于分区中
36、的行时值应用于分区中的行时所基于的顺序。所基于的顺序。踩宋凋隘淋琉续府耘演鹿才辰垛砍韦祟惠员茎财截脏蠢权绿垫踌浇里丝味大型数据库第3章transactsql语言大型数据库第3章transactsql语言注意：注意：如果两个或多个行与一个排名关联，则每个如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两关联行将得到相同的排名。例如，如果两位顶尖销售员具有相同的位顶尖销售员具有相同的 SalesYTD 值，值，则他们将并列第一。由于已有两行排名在则他们将并列第一。由于已有两行排名在前，所以具有下一个最大前，所以具有下一个最大 SalesYTD 的销的销售人员将排名第三。因此
37、，售人员将排名第三。因此，RANK 函数并函数并不总返回连续整数。不总返回连续整数。遗谍勺逛办千晕们凤尘徒扎拖毁我剧色脱怒踏宴晾叮域小蜀固谜贸弓慧障大型数据库第3章transactsql语言大型数据库第3章transactsql语言例：对每门课程进行排名。例：对每门课程进行排名。select cno,sno,grade,RANK() over(PARTITION BY CNO ORDER BY grade desc) 名次名次 from SC令另吸鹰单曼抑香壬祥有陌缺崩幼偷喧永千答促滩耙索涝鸟驯腮蓟缸赵墩大型数据库第3章transactsql语言大型数据库第3章transactsql语言（2）
38、DENSE_RANK返回结果集分区中行的排名，在排名中没有任何间断。行的返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。排名等于所讨论行之前的所有排名数加一。语法：语法： DENSE_RANK() OVER()注意：注意：如果有两个或多个行受同一个分区中排名的约束，则每个约如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。例如，如果两位顶尖销售员具有束行将接收相同的排名。例如，如果两位顶尖销售员具有相同的相同的 SalesYTD 值，则他们将并列第一。接下来值，则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。
39、该排名等于该行之最高的销售人员排名第二。该排名等于该行之前的所有行数加一。因此，前的所有行数加一。因此，DENSE_RANK 函数返回的数函数返回的数字没有间断，并且始终具有连续的排名。字没有间断，并且始终具有连续的排名。几饿怂丛践屎峡订毛樟搀寇晋苯矣宰涟溢疥国利捧睦洱传筹软蒙矣棋另穗大型数据库第3章transactsql语言大型数据库第3章transactsql语言（3）ROW_NUMBER返回结果集分区内行的序列号，每个分区的返回结果集分区内行的序列号，每个分区的第一行从第一行从 1 开始。开始。语法：语法： ROW_NUMBER() OVER()报掩虑外尹元轿础膝风璃周适禹秆炮荤宜脑陪科
40、洲悠撑琳漳腮军闯闸镍昧大型数据库第3章transactsql语言大型数据库第3章transactsql语言例：显示学生信息并显示行号。例：显示学生信息并显示行号。select ROW_NUMBER() OVER(ORDER BY Sno) 序号序号,* from Student例：返回第例：返回第4行数据。行数据。With xh_xs as(select ROW_NUMBER() OVER(ORDER BY Sno) 序号序号,* from Student)select * from xh_xs where 序号序号=4辆胸冗梅奈兢辰秃较哭钱卓眶骄妊匪咱缸切伍测噎凭禹锣剩骑渭轿锋讲伞大型数据库
41、第3章transactsql语言大型数据库第3章transactsql语言（4）NTILE将有序分区中的行分到指定数目的组中。各将有序分区中的行分到指定数目的组中。各个组有编号，编号从个组有编号，编号从1开始。对于每一个行，开始。对于每一个行，NTILE 将返回此行所属的组的编号。将返回此行所属的组的编号。语法：语法： NTILE(integer_expression) OVER()槛堤朝损挖求火熄把养寻莹姻邯迹硫豺演非叹组基刽巴太晚姐懂哎炔淤陪大型数据库第3章transactsql语言大型数据库第3章transactsql语言例：将学生成绩按照学号升序排列后分为例：将学生成绩按照学号升序排列
42、后分为3组。组。select *,NTILE(3) over(order by sno) 组组 from sc例：将学生按学号分组后，每组中按照成绩降序排例：将学生按学号分组后，每组中按照成绩降序排列后分为列后分为3组。组。select *,NTILE(3) over(partition by sno order by grade desc) 组组 from sc朵星碍迂弦眨寅腰雨砍慧怕题拳幸滥蛔我迟轮府绊维骇杠滑咐涝匪炭蔫虾大型数据库第3章transactsql语言大型数据库第3章transactsql语言二、用户自定义函数二、用户自定义函数在在Microsoft SQL Server 20
43、08系统中，用户系统中，用户定义函数是接受参数、执行操作并且将运定义函数是接受参数、执行操作并且将运算结果以值的形式返回的例程。这种返回算结果以值的形式返回的例程。这种返回值既可以是单个标量值，也可以是一个结值既可以是单个标量值，也可以是一个结果集。在果集。在Microsoft SQL Server 2008系统系统中，用户定义函数可以使用中，用户定义函数可以使用Transact-SQL语言编写，也可以使用语言编写，也可以使用.NET编程语言来编编程语言来编写。写。粗图肪蛹碱庄倾亲琳圆菇儿返桔援彩剧滩魁吁汀信礁治耀欲奄膘漓贷坠勘大型数据库第3章transactsql语言大型数据库第3章tran
44、sactsql语言1用户定义函数的特点用户定义函数的特点使用使用Transact-SQL编写的用户定义函数通过缓存计编写的用户定义函数通过缓存计划并在重复执行时重用它来降低划并在重复执行时重用它来降低Transact-SQL代代码的编译开销。也就是说，每次使用用户定义函码的编译开销。也就是说，每次使用用户定义函数时均无需重新解析和重新优化，从而大大缩短数时均无需重新解析和重新优化，从而大大缩短了执行时间。减少网络流量，基于某种无法用单了执行时间。减少网络流量，基于某种无法用单一标量表达式表示的复杂约束来过滤数据的操作，一标量表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，该函数可以
45、在可以表示为函数。然后，该函数可以在WHERE子句中调用，以减少发送至客户端的数字或行数。子句中调用，以减少发送至客户端的数字或行数。卧叙捌甘盛练年区吼侮躬氰条献吟运熔褥莲盟运伙鹅价覆谁馁者榨氮融晴大型数据库第3章transactsql语言大型数据库第3章transactsql语言2用户定义函数结构用户定义函数结构在在Microsoft SQL Server 2008系统中，所有的用户定义函系统中，所有的用户定义函数都具有相同的由两部分组成的结构：标题和正文。数都具有相同的由两部分组成的结构：标题和正文。标题可以定义下列内容。标题可以定义下列内容。l具有可选架构具有可选架构/所有者名称的函数名
46、称。所有者名称的函数名称。l输入参数名称和数据类型。输入参数名称和数据类型。l可以用于输入参数的选项。可以用于输入参数的选项。l返回参数数据类型和可选名称。返回参数数据类型和可选名称。l可以用于返回参数的选项。可以用于返回参数的选项。正文定义了函数将要执行的操作，这些操作可以是一个或多正文定义了函数将要执行的操作，这些操作可以是一个或多个个Transact-SQL语句，也可以是语句，也可以是.NET程序集的引用。程序集的引用。楚润峨茂窘译需洒前酷闺闸塌移茁呀以童飞虑擞未彤也链乃辊慕操返炸俘大型数据库第3章transactsql语言大型数据库第3章transactsql语言在创建用户定义函数时，
47、每个完全限定用户在创建用户定义函数时，每个完全限定用户函数名称函数名称(schema_name.function_name)必必须唯一。须唯一。轮怨八桶厅邹板辉尺株遗昌笆醋狗论娟童关洱铝凸灶豢架春雌揣涅怪桨酝大型数据库第3章transactsql语言大型数据库第3章transactsql语言函数的函数的BEGIN END块中的语句不能有任何副作块中的语句不能有任何副作用。用。函数副作用是指对具有函数外作用域函数副作用是指对具有函数外作用域(例如修改例如修改数据库表数据库表)的资源状态的任何永久性更改。函数中的的资源状态的任何永久性更改。函数中的语句唯一能做的更改是对函数上的局部对象语句唯一能做
48、的更改是对函数上的局部对象(如局部如局部游标或局部变量游标或局部变量)的更改。不能在函数中执行的操作的更改。不能在函数中执行的操作包括：对数据库表的修改，对不在函数上的局部游包括：对数据库表的修改，对不在函数上的局部游标进行操作，发送电子邮件，尝试修改目录，以及标进行操作，发送电子邮件，尝试修改目录，以及生成返回至用户的结果集。生成返回至用户的结果集。拿蕉赚枷凝家薯渤堕黑创柠肃始矩山凉卧氓讲价维匙棱窑乐咏窘夷平盲钓大型数据库第3章transactsql语言大型数据库第3章transactsql语言3定义定义在在Microsoft SQL Server 2008系统中，使用系统中，使用CREAT
49、E FUNCTION语句可以创建标量函数、内语句可以创建标量函数、内联表值函数和多语句表值函数。需要说明的是，联表值函数和多语句表值函数。需要说明的是，如果如果RETURNS子句指定了一种标量数据类型，子句指定了一种标量数据类型，则该函数为标量值。如果则该函数为标量值。如果RETURNS子句指定了子句指定了TABLE，则该函数为表值函数。根据函数主体的，则该函数为表值函数。根据函数主体的定义方式，表值函数可以分为内联函数或多语句定义方式，表值函数可以分为内联函数或多语句函数。内联函数可以用于获得参数化视图的功能。函数。内联函数可以用于获得参数化视图的功能。舰称惨态哩枷僚萤胡谰阀拉运双氓袒埋惜谩
50、闪技淀醉姨锰毖蕊突鳃馁掩赡大型数据库第3章transactsql语言大型数据库第3章transactsql语言4查看函数查看函数lMicrosoft SQL Server 2008系统提供了几系统提供了几个可以用于查看用户定义函数信息的系统个可以用于查看用户定义函数信息的系统存储过程和目录视图。使用这些工具，可存储过程和目录视图。使用这些工具，可以查看用户定义函数的定义、获取函数的以查看用户定义函数的定义、获取函数的架构和创建时间、列出指定函数所使用的架构和创建时间、列出指定函数所使用的对象等信息。对象等信息。竣白软裙绩尊载单嵌龋安瘦敌掠膜匿书咙鸦裔议敢扬劲兴摆字境九陋晶魏大型数据库第3章tr
51、ansactsql语言大型数据库第3章transactsql语言l可以使用可以使用sys.sql_modules、OBJECT_DEFINITION、sp_helptext等工等工具查看用户定义函数的定义，使用具查看用户定义函数的定义，使用sys.objects、sys.parameters、sp_help等工具查看有关用户定义函数的信息，使等工具查看有关用户定义函数的信息，使用用sys.sql_dependencies、sp_depends等工具查看用户定义函数的依赖关系。等工具查看用户定义函数的依赖关系。坪汞栋妇绝斗篆霞凳胎滨苔慷伤谚茧傍肿却肄光骇连复钳主姐灌虐矽盆秘大型数据库第3章tra
52、nsactsql语言大型数据库第3章transactsql语言5函数种类函数种类l按照函数返回类型，用户自定义函数的类按照函数返回类型，用户自定义函数的类型分为表值函数（型分为表值函数（TVF，TABLE-Valued Function）、标量值函数（）、标量值函数（Scalar-Valued Function,SVF）和聚合函数。按照创建这）和聚合函数。按照创建这些类型和用户自定义函数的技术，分为些类型和用户自定义函数的技术，分为Transact-SQL函数和函数和CLR函数。函数。饶攻返锁千欣赊珊叭亦星两嘶笔脉次皋娄颖蹦拢交增濒盼斋造铣憎觉贩绵大型数据库第3章transactsql语言大型
53、数据库第3章transactsql语言(1)表值函数表值函数1.多语句表值函数多语句表值函数多语句表值自定义函数返回值是一个表，返多语句表值自定义函数返回值是一个表，返回值表中的数据是由函数体中的语句插入回值表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询的。由此可见，它可以进行多次查询,对数对数据进行多次筛选与合并。据进行多次筛选与合并。湛待分款客戈嘲狸涵渣住乔夜迭协范呸阶悉帖椽蜕恰磋淖丧叫锁外带疗案大型数据库第3章transactsql语言大型数据库第3章transactsql语言例例1：创建一个无参数的多语句表值函数，实现查询。：创建一个无参数的多语句表值函数，实现查询。
54、Create function dbo.multisql_tablevalued_func()RETURNS Table_Var_Name TABLE(sno char(10),sname char(20)ASBEGININSERT INTO Table_Var_Name SELECT sno,sname FROM studentRETURNEND积谷杨壮六殷划序的路茂婴智雪蚤陪澄第毋蔗鞘扰身寿痈膝砧维炒氯磅贿大型数据库第3章transactsql语言大型数据库第3章transactsql语言例例2：创建一个带参数的多语句表值函数。：创建一个带参数的多语句表值函数。Create functio
55、n dbo.multisql_tablevalued_func2(xh char(10)RETURNS Table_Var_Name TABLE(sno char(10),sname char(20)ASBEGININSERT INTO Table_Var_Name SELECT sno,sname FROM student where sno=xhRETURNEND执行该函数：执行该函数：Select * from dbo. multisql_tablevalued_func2(201215122 )辣想壮词玛胜叛蜡味膏淄卫蹋耪寡蒜儿咨瘪揉盗钳村侵辖赎粟述浪滁嗽幅大型数据库第3章transa
56、ctsql语言大型数据库第3章transactsql语言例例3：创建一个带参数默认值的多语句表值函数。：创建一个带参数默认值的多语句表值函数。Create function dbo.multisql_tablevalued_func3(xh char(10)=201215122)RETURNS Table_Var_Name TABLE(xh char(10),xm char(20)ASBEGININSERT INTO Table_Var_Name SELECT sno,sname FROM student where sno=xhRETURNEND执行带默认值的函数：执行带默认值的函数：Sel
57、ect * from dbo. multisql_tablevalued_func2(default)当函数的参数有默认值时，调用该函数时必须指定默认当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获关键字才能获取默认值。取默认值。雏淤霹吊扔泡在裹聋矽姻霸骇沧诬渍掇伯妖别惰懂玻半斧配细巢至教釉谍大型数据库第3章transactsql语言大型数据库第3章transactsql语言(2)内联表值函数内联表值函数是一种特殊的多语句表值函数，仅含是一种特殊的多语句表值函数，仅含1条条SELECT语语句。句。内联表值函数以表的形式返问一个值，即它返回的内联表值函数以表的形式返
58、问一个值，即它返回的是一个表。内联表值自定义函数没有由是一个表。内联表值自定义函数没有由BEGIN END语句块中包含的函数体，而是直接使用语句块中包含的函数体，而是直接使用RETURN子句，其中包含的子句，其中包含的SELECT语句将数据语句将数据从数据库中筛选出来形成一个表，使用内联表值从数据库中筛选出来形成一个表，使用内联表值自定义函数可以提供参数化的视图。自定义函数可以提供参数化的视图。功能功能批酶浓堤睁惟万梅补具春婿企废芍舟杀谦喷巩支象谬哩药寇诽踊敦肃榆新大型数据库第3章transactsql语言大型数据库第3章transactsql语言例例4：创建一个内联表值函数，实现例：创建一个
59、内联表值函数，实现例3的功能。的功能。Create function dbo.inline_func(xh char(10)=201215122)RETURNS TABLEASRETURN(SELECT sno,sname FROM student where sno=xh)执行该函数：执行该函数：Select * from dbo.inline_func(default)Select * from dbo.inline_func(0403)之轮好叹按耍馒磷莲烧防帘痪助匙豪菏钥膨行卡三橡卸订归肮粉袒机黎股大型数据库第3章transactsql语言大型数据库第3章transactsql语言(3)
60、标量值函数标量值函数标量值函数返回一个确定类型的标量，值的标量值函数返回一个确定类型的标量，值的类型为除类型为除text, ntext,image，cursor,timestampp和和table类型外的其他类型外的其他数据类型。数据类型。致希蔼仓辑丢淌渴逆淆轿浸杭雀邱篡巡比查弗边买敞曰语吝止惩瓣武秒质大型数据库第3章transactsql语言大型数据库第3章transactsql语言例例5：创建一个标量值函数，查询指定姓名的学生的学号。：创建一个标量值函数，查询指定姓名的学生的学号。Create function dbo.scalar_func(xm char(10)RETURNS char
61、(10)ASBEGINDECLARE Result char(10) SELECT Result=sno FROM student where sname=xmRETURN Resultend执行该函数：执行该函数：Select dbo. scalar_func(张杰张杰) as 学号学号痹河傈惧逆锗疤圆靠捐朗首痴考靶品耍延心姻落档记轮易桨链必逾榴迁蹋大型数据库第3章transactsql语言大型数据库第3章transactsql语言第三节 增强功能Microsoft SQL Server 2008在之前版本的基在之前版本的基础上对础上对Teansact-SQL进行了进一步增强，进行了进一步增
62、强，使得使得Transacl-SQL语介的功能更加强大，语介的功能更加强大，也简化了数据库管理员的操作。也简化了数据库管理员的操作。封坐让俊绿坚际亨筹郎杜形追诗铡侥撰壁牢招肪厅月皇凌午始央有琢毯乘大型数据库第3章transactsql语言大型数据库第3章transactsql语言1Transact-SQL行构造器行构造器T-SQL行构造器行构造器(Row Constructors)用来在用来在INSERT语句中一次性插入多行数据。例如：语句中一次性插入多行数据。例如：例：例：INSert into student values(201207001,赵强赵强,男男,20,CS,2012),( 2
63、01307002,刘秦刘秦,女女,21,MA,2013)跌瞬垒黑喻柜损栖值吮棺蹄悍亡米削垢腰湿标寞溢猖搐彰老酋冈庚园粤淳大型数据库第3章transactsql语言大型数据库第3章transactsql语言2.MERGE语句语句这个新增的这个新增的 Transaction SQL 语句在一个基语句在一个基于源数据连接结果集的目标表上执行于源数据连接结果集的目标表上执行INSERT、UPDATE 和和 DELETE操作。该操作。该语法允许您将一个数据源连接到目标表或语法允许您将一个数据源连接到目标表或视图上。然后在连接后的结果集上执行多视图上。然后在连接后的结果集上执行多种操作。种操作。稻壬百竹孵
64、狮帖珊粹赚都嚼奔梧夫野痹如酷崩吭尖巩迅睫钡尽碉坠夸鸭司大型数据库第3章transactsql语言大型数据库第3章transactsql语言示例：在一条示例：在一条SQL语句中使用语句中使用WHERE在一张表上执行在一张表上执行UPDATE和和DELETE操作。操作。USE AdventureWorks;GOMERGE Production.ProductInventory AS piUSING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod JOIN Sales.SalesOrderHeader soh ON s
65、od.SalesOrderID = soh.SalesOrderID AND soh.OrderDate = GETDATE() GROUP BY ProductID) AS src (ProductID, OrderQty)ON (pi.ProductID = src.ProductID)WHEN MATCHED AND pi.Quantity - src.OrderQty 0 THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQtyWHEN MATCHED AND pi.Quantity - src.OrderQty = 0 THEN
66、 DELETE; 跋卑紧邮瘦挖周初滦揭修肖狸为痪毛隐淑尺蕴乌其阂伊沸豪职哪刷沃措德大型数据库第3章transactsql语言大型数据库第3章transactsql语言l这个示例是一个非常典型的销售定货库存这个示例是一个非常典型的销售定货库存问题。这个示例很简单，表达的意思就是：问题。这个示例很简单，表达的意思就是：如果某一个产品产生了销售定单数据，则如果某一个产品产生了销售定单数据，则将其对应的产品库存除去该销售定单所产将其对应的产品库存除去该销售定单所产生的数量，如果当前库存数量与该销售定生的数量，如果当前库存数量与该销售定单数量相同，则从库存表中删除该产品的单数量相同，则从库存表中删除该产
67、品的库存纪录。库存纪录。帝忘至徐润未敞误陨躁舶躲媒波钥禁鲁寝萧匡烫通考悔后梳颐箭陇棉讳曰大型数据库第3章transactsql语言大型数据库第3章transactsql语言是是SQL Server2008下新增的语句，功能：根下新增的语句，功能：根据一个源数据表对另一个数据表进行确定据一个源数据表对另一个数据表进行确定性的插入、更新和删除等复杂操作。性的插入、更新和删除等复杂操作。对两个表进行信息同步时，有三步，处理需对两个表进行信息同步时，有三步，处理需要插入目标数据包的新行；处理需要更新要插入目标数据包的新行；处理需要更新的已存在的行，删除不再使用的旧行。的已存在的行，删除不再使用的旧行。
68、烹汪墨常暴池璃汲八癣蔬畅淤风厘琵扑瀑婆串宪申汇牌突绅芋闰剔遵樟冉大型数据库第3章transactsql语言大型数据库第3章transactsql语言模板：模板：Merge target tUsing souce s on t.id=s.idWhen matched then update t.name=s.name,t.age=s.ageWhen not matched by target then insert values(id,name,age)When not matched BY SOURCE then delete;lMERGE子句用于指定作为子句用于指定作为INSERT,UPDA
69、TE或或DELE丁丁E操作目标的表或视图；操作目标的表或视图；lUSING子句用于指定要与目标联接的数据源；子句用于指定要与目标联接的数据源；lON子句用于指定决定目标与源匹配位置的联接条件；子句用于指定决定目标与源匹配位置的联接条件；lWEN子句用于根据子句用于根据ON子句的结果指定要执行的操作。子句的结果指定要执行的操作。譬炳你绍结梗熬谐查艾翅赤砚怎砍甘袄钩试樱伞拇勺雅鸯询较蛤懊俄墟巩大型数据库第3章transactsql语言大型数据库第3章transactsql语言例：跟据例：跟据student表的数据更新表的数据更新student2表的数据。表的数据。merge student2 s2
70、Using student s on s2.sno=s.snoWhen matched then update set s2.sname=s.snameWhen not matched by target then insert values(sno,sname)When not matched BY SOURCE then delete;例：在学号相同的情况下，想删除年龄大于例：在学号相同的情况下，想删除年龄大于30的学生的信息。的学生的信息。merge student2 s2Using student s on s2.sno=s.snoWhen matched and s2.sage30
71、then deleteWhen not matched by target then insert values(sno,sname,sage);我们看到，利用我们看到，利用 MERGE 语句可以将复杂的语句可以将复杂的 SQL 语句简化。它比起语句简化。它比起 IF、CASE 等更加灵活和强大。等更加灵活和强大。秒班迟男誉猫肤惫倚藏茎匆疵方卡铬温恬瓢挤焕豪碍爽紧旨宿仙宋其赖错大型数据库第3章transactsql语言大型数据库第3章transactsql语言5.10 本章小结本章小结l本章全面讲述了本章全面讲述了Transact-SQL语言的基本功能。语言的基本功能。首先，介绍了首先，介绍了
72、Transact-SQL和和SQL语言的关系。语言的关系。接下来，对接下来，对Transact-SQL语言的特点和执行方式语言的特点和执行方式进行了分析。之后，分别讲述了数据定义语言、进行了分析。之后，分别讲述了数据定义语言、数据操纵语言、数据控制语言以及事务管理语言数据操纵语言、数据控制语言以及事务管理语言的类型和特点。最后，对的类型和特点。最后，对Transact-SQL语言的其语言的其他语言元素进行了详细讲述，这些语言元素包括他语言元素进行了详细讲述，这些语言元素包括标识符、变量和常量、运算符、数据类型、内置标识符、变量和常量、运算符、数据类型、内置函数等内容。函数等内容。Monday, July 22, 2024第104页献碱侗酸胳饥伍凝帅盅戴脯苹定蓉学柑剖旭职坊丑介述自崩官笋境后佰弛大型数据库第3章transactsql语言大型数据库第3章transactsql语言
