大型数据库第3章transactsql语言.ppt

*第1页 第3章使用Transact-SQL编程 本章概述 本章要点 本章内容 *第2页 本章概述 l如何方便地在数据库中执行访问数据库对象、查询业务数 据等各种操作呢？这需要掌握使用Transact-SQL语言的 技术。Transact-SQL语言是微软公司在关系型数据库管 理系统Microsoft SQL Server中的ISO SQL的实现。 SQL(Structure Query Language，结构化查询语言)语 言是国际标准化组织(International Standardize Organization，ISO)采纳的标准数据库语言。通过使用 Transact-SQL语言，用户几乎可以完成SQL Server数据 库中的各种操作。本章全面介绍Transact-SQL语言的特 点和使用方式。 *第3页 本章要点 lTransact-SQL语言和SQL语言之间的关系 lTransact-SQL语言的特点和执行方式 l数据定义语言的类型和特点 l数据操纵语言的类型和特点 l数据控制语言的类型和特点 l事务管理语言的类型和特点 l附加语言元素的类型和特点 *第4页 本章内容 第一节 Transact-SQL语言简介 第二节 函数 第三节 增强功能 本章小结 第一节 Transact-SQL语言简介 l1970年6月，埃德加考特(Edgar Frank Codd)在 Communications of ACM 上发表了大型共享 数据库数据的关系模型一文。首次明确而清晰 地为数据库系统提出了一种崭新的模型， 即关系 模型。1970 年以后，考特继续致力于完善与发展 关系理论。1972 年，他提出了关系代数和关系演 算的概念，定义了关系的并、交、投影、选择、 连接等各种基本运算，为SQL语言的形成和发展 奠定了理论基础。1979年，SQL在商业数据库中 成功得到了应用。 *第5页 标准版本 l1986年，美国国家标准化组织正式发表了编号为X3.135- 1986的SQL标准，并且在1987年得到了ISO组织的认可， 被命名为ISO9075-1987。 l后来这个标准在1992、1999、2003、2006、2008年等不 断地得到了扩充和完善。1992年发布的标准是SQL92， 也称为SQL2。1999年发布的标准称为SQL:1999，也称 为SQL3。该版本增加了迭代查询、触发器、控制流以及 面向对象功能。2003年，SQL标准引入了XML支持、自 动生成值等特征。2006年的标准在XML数据的存储和查 询方面有了更多的增强。 *第6页 Transact-SQL语言 lTransact-SQL语言是微软公司在 Microsoft SQL Server系统中使用的语言 ，是对SQL语言的一种扩展形式。 lTransact-SQL语言是一种交互式查询语言 ，具有功能强大、简单易学的特点。 *第7页 特点 Transact-SQL语言有4个特点： l一是一体化的特点，集数据定义语言、数据操纵语言、数 据控制语言、事务管理语言和附加语言元素为一体。 l二是有两种使用方式，即交互使用方式和嵌入到高级语言 中的使用方式。 l三是非过程化语言，只需要提出“干什么”，不需要指出“ 如何干”，语句的操作过程由系统自动完成。 l四是类似于人的思维习惯，容易理解和掌握。 *第8页 类型 l在Microsoft SQL Server 2008系统中，根 据Transact-SQL语言的功能特点，可以把 Transact-SQL语言分为5种类型，即数据 定义语言、数据操纵语言、数据控制语言 、事务管理语言和附加的语言元素。 *第9页 Transact-SQL语言的执行方式 l在Microsoft SQL Server 2008系统中，主 要使用SQL Server Management Studio工 具来执行Transact-SQL语言编写的查询语句 。 l除此之外，还可以使用sqlcmd实用命令、 PowerShell工具来执行Transact-SQL语句 。 l下面主要介绍SQL Server Management Studio工具的特点。 *第10页 在查询窗口中执行Transact- SQL语句 *第11页 数据定义语言 l数据定义语言用于创建数据库和数据库对 象，为数据库操作提供对象。 l例如，数据库以及表、触发器、存储过程 、视图、索引、函数、类型及用户等都是 数据库中的对象，需要定义后才能使用。 l在DDL中，主要的Transact-SQL语句包括 CREATE语句、ALTER语句和DROP语句 。 *第12页 CREATE语句示例 *第13页 ALTER语句示例 *第14页 DROP语句示例 *第15页 数据操纵语言 l数据操纵语言主要是用于操纵表、视图中数据的 语句。当创建表对象之后，初始状态时该表是空 的，没有任何数据。如何向表中添加数据呢？这 时需要使用INSERT语句。如何检索表中数据呢 ？可以使用SELECT语句。如果表中数据不正确 ，可以使用UPDATE语句进行更新。当然，也可 以使用DELETE语句删除表中的数据。 l实际上，DML语言包括了INSERT、SELECT、 UPDATE和DELETE等语句。 *第16页 附加的语言元素 l除了前面介绍的语句之外，Transact-SQL 语言还包括了附加的语言元素。 l这些附加的语言元素主要包括标识符、变 量和常量、运算符、表达式、数据类型、 函数、控制流语言、错误处理语言和注释 等。 l下面，详细介绍这些内容。 *第17页 标识符 l在Transact-SQL语言中，数据库对象的名称就是 其标识符。 l在Microsoft SQL Server系统中，所有的数据库 对象都可以有标识符，例如服务器、数据库、表 、视图、索引、触发器和约束等。大多数对象的 标识符是必需的，例如，创建表时必须为表指定 标识符。但是，也有一些对象的标识符是可选的 ，例如，创建约束时用户可以不提供标识符，其 标识符由系统自动生成。 *第18页 变量和常量 l常量是表示特定数据值的符号，常量也被称为字面量。 l常量的格式取决于它所表示的值的数据类型。例如， This is a book.、May 1, 2006、98321等都是常量 。对于字符常量或时间日期型常量，需要使用单引号引 起来。 l当字符串中有单引号时，则用两个单引号来表示。 lUnicode常量必须有一个大写的N来区别字符串常量。 *第19页 变量和常量 l在Microsoft SQL Server 2008系统中，变 量也被称为局部变量，是可以保存单个特定 类型数据值的对象。 l全局变量以开头命名，局部变量以开 头命名。 l全局变量由系统提供，用于存储一些系统信 息，只可以使用全局变量，不可以自定义全 局变量。 *第20页 声明和赋值 l在Transact-SQL语言中，可以使用DECLARE语句声明 变量。 在声明变量时需要注意： 第一，为变量指定名称，且名称的第一个字符必须是； 第二，指定该变量的数据类型和长度； 第三，默认情况下将该变量值设置为NULL。 l可以在一个DECLARE语句中声明多个变量，变量之间使 用逗号分割开。变量的作用域是可以引用该变量的 Transact-SQL语句的范围。变量的作用域从声明变量的 地方开始到声明变量的批处理的结尾。 *第21页 l有两种为变量赋值的方式：使用SET语句为 变量赋值和使用SELECT语句选择列表中当 前所引用值来为变量赋值。 l使用select和print语句可以显示变量内容 。 使用变量 *第23页 例：查询计算机系学生人数，并输出。 declare renshu int select renshu=count(*) from student where sdept=CS printCS人数：+cast(renshu as varchar(10) 运算符 l运算符是一种符号，用来指定要在一个或 多个表达式中执行的操作。在Microsoft SQL Server 2008系统中，可以使用的运算 符可以分为算术运算符、逻辑运算符、赋 值运算符、字符串串联运算符、按位运算 符、一元运算符和比较运算符等。 *第24页 使用赋值运算符 *第25页 使用字符串串联运算符 *第26页 使用位运算符 *第27页 表达式 l在Transact-SQL语言中，表达式是由标识符、变量、常 量、标量函数、子查询以及运算符等的组合。在 Microsoft SQL Server 2008系统中，表达式可以在多个 不同的位置使用，这些位置包括查询中检索数据的一部分 、搜索数据的条件等。 l表达式可以分为简单表达式和复杂表达式两种类型。简单 表达式只是一个变量、常量、列名或标量函数，复杂表达 式是由两个或多个简单表达式通过使用运算符连接起来的 表达式。在复杂表达式中，两个或多个表达式有相同的数 据类型，优先级低的数据类型可以隐式地转换为优先级高 的数据类型。 *第28页 注释 l所有的程序设计语言都有注释。注释是程序代码 中不执行的文本字符串，用于对代码进行说明或 暂时仅用正在进行诊断的部分语句。 l一般地，注释主要描述程序名称、作者名称、变 量说明、代码更改日期以及算法描述等。 l在Microsoft SQL Server系统中，支持两种注释 方式，即双连字符(-)注释方式和正斜杠星号字符 对(/*/)注释方式。 *第29页 使用注释 *第30页 控制流语言 l一般地，结构化程序设计语言的基本结构 是顺序结构、条件分支结构和循环结构。 顺序结构是一种自然结构，条件分支结构 和循环结构都需要根据程序的执行状况对 程序的执行顺序进行调整。 l在Transact-SQL语言中，用于控制语句流 的语言被称为控制流语言。Microsoft SQL Server 2008系统提供了8种控制流语句。 *第31页 1.Beginend 2.Ifelse 3.while 4.break 5.continue 6.goto 7. Return 8. wairfor Beginend, Ifelse 例：查询并显示MA系人数。 begin declare renshu int select renshu=count(*) from student where sdept=MA if renshu=0 print MA系无人 else printMA人数：+cast(renshu as varchar(10) end while 语法： WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE sql_statement | statement_block BREAK：导致从最内层的 WHILE 循环中退出 CONTINUE：使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后面的任何语句。 while 例：求1到10的累加和。 declare i int,sum int set i=1 set sum=0 while(i=90 then 优秀 when grade=80 then 良好 when grade=70 then 中等 when grade=60 then 及格 when grade) 参数： ：将FROM子句生成的结果集划分 成RANK函数适用的分区。 ：确定将RANK值应用于分区中的行时 所基于的顺序。 注意： 如果两个或多个行与一个排名关联，则每个 关联行将得到相同的排名。例如，如果两 位顶尖销售员具有相同的 SalesYTD 值， 则他们将并列第一。由于已有两行排名在 前，所以具有下一个最大 SalesYTD 的销 售人员将排名第三。因此，RANK 函数并 不总返回连续整数。 例：对每门课程进行排名。 select cno,sno,grade,RANK() over(PARTITION BY CNO ORDER BY grade desc) 名次 from SC （2）DENSE_RANK 返回结果集分区中行的排名，在排名中没有任何间断。行的 排名等于所讨论行之前的所有排名数加一。 语法： DENSE_RANK() OVER() 注意： 如果有两个或多个行受同一个分区中排名的约束，则每个约 束行将接收相同的排名。例如，如果两位顶尖销售员具有 相同的 SalesYTD 值，则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之 前的所有行数加一。因此，DENSE_RANK 函数返回的数 字没有间断，并且始终具有连续的排名。 （3）ROW_NUMBER 返回结果集分区内行的序列号，每个分区的 第一行从 1 开始。 语法： ROW_NUMBER() OVER() 例：显示学生信息并显示行号。 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 （4）NTILE 将有序分区中的行分到指定数目的组中。各 个组有编号，编号从1开始。对于每一个行 ，NTILE 将返回此行所属的组的编号。 语法： NTILE(integer_expression) OVER() 例：将学生成绩按照学号升序排列后分为3组。 select *,NTILE(3) over(order by sno) 组 from sc 例：将学生按学号分组后，每组中按照成绩降序排 列后分为3组。 select *,NTILE(3) over(partition by sno order by grade desc) 组 from sc 二、用户自定义函数 在Microsoft SQL Server 2008系统中，用户 定义函数是接受参数、执行操作并且将运 算结果以值的形式返回的例程。这种返回 值既可以是单个标量值，也可以是一个结 果集。在Microsoft SQL Server 2008系统 中，用户定义函数可以使用Transact-SQL 语言编写，也可以使用.NET编程语言来编 写。 1用户定义函数的特点 使用Transact-SQL编写的用户定义函数通过缓存计 划并在重复执行时重用它来降低Transact-SQL代 码的编译开销。也就是说，每次使用用户定义函 数时均无需重新解析和重新优化，从而大大缩短 了执行时间。减少网络流量，基于某种无法用单 一标量表达式表示的复杂约束来过滤数据的操作 ，可以表示为函数。然后，该函数可以在 WHERE子句中调用，以减少发送至客户端的数 字或行数。 2用户定义函数结构 在Microsoft SQL Server 2008系统中，所有的用户定义函 数都具有相同的由两部分组成的结构：标题和正文。 标题可以定义下列内容。 l具有可选架构/所有者名称的函数名称。 l输入参数名称和数据类型。 l可以用于输入参数的选项。 l返回参数数据类型和可选名称。 l可以用于返回参数的选项。 正文定义了函数将要执行的操作，这些操作可以是一个或多 个Transact-SQL语句，也可以是.NET程序集的引用。 在创建用户定义函数时，每个完全限定用户 函数名称(schema_name.function_name) 必须唯一。 函数的BEGIN END块中的语句不能有任何副作 用。 函数副作用是指对具有函数外作用域(例如修改 数据库表)的资源状态的任何永久性更改。函数中的 语句唯一能做的更改是对函数上的局部对象(如局部 游标或局部变量)的更改。不能在函数中执行的操作 包括：对数据库表的修改，对不在函数上的局部游 标进行操作，发送电子邮件，尝试修改目录，以及 生成返回至用户的结果集。 3定义 在Microsoft SQL Server 2008系统中，使用 CREATE FUNCTION语句可以创建标量函数、内 联表值函数和多语句表值函数。需要说明的是， 如果RETURNS子句指定了一种标量数据类型， 则该函数为标量值。如果RETURNS子句指定了 TABLE，则该函数为表值函数。根据函数主体的 定义方式，表值函数可以分为内联函数或多语句 函数。内联函数可以用于获得参数化视图的功能 。 4查看函数 lMicrosoft SQL Server 2008系统提供了几 个可以用于查看用户定义函数信息的系统 存储过程和目录视图。使用这些工具，可 以查看用户定义函数的定义、获取函数的 架构和创建时间、列出指定函数所使用的 对象等信息。 l可以使用sys.sql_modules、 OBJECT_DEFINITION、sp_helptext等工 具查看用户定义函数的定义，使用 sys.objects、sys.parameters、sp_help 等工具查看有关用户定义函数的信息，使 用sys.sql_dependencies、sp_depends 等工具查看用户定义函数的依赖关系。 5函数种类 l按照函数返回类型，用户自定义函数的类 型分为表值函数（TVF，TABLE-Valued Function）、标量值函数（Scalar-Valued Function,SVF）和聚合函数。按照创建这 些类型和用户自定义函数的技术，分为 Transact-SQL函数和CLR函数。 (1)表值函数 1.多语句表值函数 多语句表值自定义函数返回值是一个表，返 回值表中的数据是由函数体中的语句插入 的。由此可见，它可以进行多次查询,对数 据进行多次筛选与合并。 例1：创建一个无参数的多语句表值函数，实现查询。 Create function dbo.multisql_tablevalued_func() RETURNS Table_Var_Name TABLE (sno char(10),sname char(20) AS BEGIN INSERT INTO Table_Var_Name SELECT sno,sname FROM student RETURN END 例2：创建一个带参数的多语句表值函数。 Create function dbo.multisql_tablevalued_func2(xh char(10) RETURNS Table_Var_Name TABLE (sno char(10),sname char(20) AS BEGIN INSERT INTO Table_Var_Name SELECT sno,sname FROM student where sno=xh RETURN END 执行该函数： Select * from dbo. multisql_tablevalued_func2(201215122 ) 例3：创建一个带参数默认值的多语句表值函数。 Create function dbo.multisql_tablevalued_func3(xh char(10)=201215122) RETURNS Table_Var_Name TABLE (xh char(10),xm char(20) AS BEGIN INSERT INTO Table_Var_Name SELECT sno,sname FROM student where sno=xh RETURN END 执行带默认值的函数： Select * from dbo. multisql_tablevalued_func2(default) 当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能 获取默认值。 (2)内联表值函数 是一种特殊的多语句表值函数，仅含1条SELECT语 句。 内联表值函数以表的形式返问一个值，即它返回的 是一个表。内联表值自定义函数没有由BEGIN END语句块中包含的函数体，而是直接使用 RETURN子句，其中包含的SELECT语句将数据 从数据库中筛选出来形成一个表，使用内联表值 自定义函数可以提供参数化的视图。 功能 例4：创建一个内联表值函数，实现例3的功能。 Create function dbo.inline_func(xh char(10)=201215122) RETURNS TABLE AS RETURN( SELECT sno,sname FROM student where sno=xh) 执行该函数： Select * from dbo.inline_func(default) Select * from dbo.inline_func(0403) (3)标量值函数 标量值函数返回一个确定类型的标量，值的 类型为除text, ntext,image， cursor,timestampp和table类型外的其他 数据类型。 例5：创建一个标量值函数，查询指定姓名的学生的学号。 Create function dbo.scalar_func(xm char(10) RETURNS char(10) AS BEGIN DECLARE Result char(10) SELECT Result=sno FROM student where sname=xm RETURN Result end 执行该函数： Select dbo. scalar_func(张杰) as 学号 第三节 增强功能 Microsoft SQL Server 2008在之前版本的基 础上对Teansact-SQL进行了进一步增强， 使得Transacl-SQL语介的功能更加强大， 也简化了数据库管理员的操作。 1Transact-SQL行构造器 T-SQL行构造器(Row Co