《Transact-SQL语言 (2).ppt》由会员分享，可在线阅读，更多相关《Transact-SQL语言 (2).ppt（105页珍藏版）》请在三一办公上搜索。
1、第4章 Transact-SQL语言,4.1 数据定义语言,数据定义语言（DDL）是指用来定义和管理数据库以及数据库中各种对象的语句，这些语句包括CREATE、ALTER 和DROP 等。在SQLServer 2005中，数据库对象包括表、视图、触发器、存储过程、规则、默认、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE，ALTER，DROP等语句来完成。,4.1 数据定义语言,例4-1 创建数据库表/*下面的例子将创建表S。*/CREATE TABLE S(sno char(10)NOT NULL/*学号字段*/CONSTRAINT PK_sno PRIMAR
2、Y KEY CLUSTERED/*主键约束*/CHECK(sno like 313005010-90-9)/*检查约束*/,sname char(8)NULL,/*姓名字段*/sex char(2)NULL,/*性别字段*/age int NULL,/*年龄字段*/dept varchar(20)NULL/*系别字段*/),补充知识：1.clustered,我们可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引(clustered index，也称聚类索引、簇集索引)和非聚集索(nonclustered index，也称非聚类索引、非簇集索引)。下面，我们举例来
3、说明一下聚集索引和非聚集索引的区别：,其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音 排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那 么就说明您的字典中没有这个字;同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分 本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引
4、”。,如果您认识某个字，您可以快速地从自典中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的 字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序 并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却 是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上 就是他们在非聚集
5、索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结 果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。,进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。,2.什么是检查约束?,检查约束是一个规则，它确认一个SQL Server表中某条记录中的数据可接受的字段值。检查约束帮助执行域完整性。域完整性定义了一个数据库表中字段的有效值。检查约束可以验证一个单独字段或一些字段的域完整性。你对一个单独的字段可以有多个检查完整性。假如
6、被插入或更新的数据违反了一个检查约束，那么数据库引擎将不答应这个插入或更新的操作发生。,4.1 数据定义语言,例4-2 修改S表，增加一个班号列程序清单如下：ALTER TABLE SADDCLASS_NO CHAR(6),4.1 数据定义语言,例4-3删除S表程序清单如下：DROP table S,4.2 数据操纵语言（DML）,数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。将在第5章详细讲解SELECT等语句的语法及实例。,4.3 数据控制语言,数据控制语言（DCL）是用来设置或更改数据库用户或角色权限的
7、语句，包括GRANT，DENY，REVOKE等语句。在默认状态下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人员才有权力执行数据控制语言。,GRANT语句,1、语句权限与角色的授予 SQL语言使用GRANT语句为用户授予语句权限的语法格式为：GRANT|,|TO|PUBLIC,|WITH ADMIN OPTION其语义为：将指定的语句权限授予指定的用户或角色。其中：（1）PULBIC代表数据库中的全部用户；（2）WITH ADMIN OPTION为可选项，指定后则允许被授权的用户将指定的系统特权或角色再授予其他用户或角色。,GRANT语句,1
8、、语句权限与角色的授予 例4-17给用户 Mary 和 John 以及 Windows NT 组CorporateBobJ 授予多个语句权限。程序清单如下：GRANT CREATE DATABASE,CREATE TABLETO Mary,John,CorporateBobJ 例3-18 为用户ZhangYiLin授予CREATE TABLE的语句权限。程序清单如下：GRANT CREATE TABLETO ZhangYiLin,GRANT语句,2、对象权限与角色的授予 数据库管理员拥有系统权限，而作为数据库的普通用户，只对自己创建的基本表、视图等数据库对象拥有对象权限。如果要共享其他的数据库
9、对象，则必须授予他一定的对象权限。同语句权限的授予类似，SQL语言使用GRANT语句为用户授予对象权限，其语法格式为：GRANT ALL|(列名,列名),ON TO|PUBLIC,|WITH ADMIN OPTION,其语义为：将指定的操作对象的对象权限授予指定的用户或角色。其中：（1）ALL代表所有的对象权限。（2）列名用于指定要授权的数据库对象的一列或多列。如果不指定列名，被授权的用户将在数据库对象的所有列上均拥有指定的特权。实际上，只有当授予INSERT、UPDATE权限时才需指定列名。（3）ON子句用于指定要授予对象权限的数据库对象名，可以是基本表名、视图名等。（4）WITH ADMI
10、N OPTION为可选项，指定后则允许被授权的用户将权限再授予其他用户或角色。,GRANT语句,2、对象权限与角色的授予 例4-19在权限层次中授予对象权限。首先，给所有用户授予 SELECT 权限，然后，将特定的权限授予用户 Mary，John 和 Tom。程序清单如下：GRANT SELECTON sTO publicGOGRANT INSERT,UPDATE,DELETEON sTO Mary,John,TomGO,GRANT语句,2、对象权限与角色的授予 例4-20 将查询T表和修改教师职称的权限授予USER3，并允许将此权限授予其他用户。程序清单如下：GRANT SELECT,UPD
11、ATE(PROF)ON T TO USER3WITH ADMIN OPTION 上例中，USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予USER4：GRANT SELECT,UPDATE(PROF)ON TTO USER4,REVOKE语句,REVOKE语句是与GRANT语句相反的语句，它能够将以前在当前数据库内的用户或者角色上授予或拒绝的权限删除，但是该语句并不影响用户或者角色从其他角色中作为成员继承过来的权限1、语句权限与角色的收回数据库管理员可以使用REVOKE语句收回语句权限，其语法格式为：REVOKE|,|FROM|PUBLIC,|例：收
12、回用户ZHANGYILIN所拥有的CREATE TABLE的语句权限。REVOKE CREATE TABLEFROM ZHANGYILIN,REVOKE语句,例4-21 收回用户USER1对C表的查询权限。程序清单如下：REVOKE SELECTON CFROM USER1例4-22 收回用户USER3查询T表和修改教师职称的权限。程序清单如下：REVOKE SELECT,UPDATE(PROF)ON TFROM USER3在上例中，USER3将对T表的权限授予了USER4，在收回USER3对T表的权限的同时，系统会自动收回USER4对T表的权限。,REVOKE语句,REVOKE语句例4-23
13、首先从 public 角色中收回 SELECT 权限，然后，收回用户 Mary，John 和 Tom 的特定权限。程序清单如下：USE pubsGOREVOKE SELECT ON s FROM publicGOREVOKE INSERT,UPDATE,DELETEON sFROM Mary,John,Tom,DENY语句,DENY语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。否定语句权限的语法形式为：DENY ALL|,|TO|PUBLIC,|否定对象权限的语法形式为：DENY ALL|(列名,列名),ON TO|PUBLIC,|,DENY语句
14、,例4-24首先给 public 角色授予 SELECT 权限，然后，拒绝用户 Mary，John 和 Tom 的特定权限。程序清单如下：USE pubsGOGRANT SELECTON sTO publicGODENY SELECT,INSERT,UPDATE,DELETEON sTO Mary,John,Tom,4.4 系统存储过程,系统存储过程是SQL Server系统创建的存储过程，它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程可以在任意一个数据库中执行。系统存储过程创建并存放于系统数据库master中，并且名称以sp_
15、或者xp_开头。一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其他用户使用。,4.4 系统存储过程,系统存储过程的部分示例如下：sp_addtype：用于定义一个用户定义数据类型；sp_configure：用于管理服务器配置选项设置；xp_sendmail：用于发送电子邮件或寻呼信息；sp_stored_procedures：用于返回当前数据库中的存储过程的清单；sp_help：用于显示参数清单和其数据类型；sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象；sp_helptext：用于显示存储过程的定义文本；sp_rename：用于修改当前数据库中用户对象
16、的名称。,SQL Server2005 系统数据库简介,SQL Server 2005 有 4 个系统数据库，它们分别为 Master、Model、Msdb、Tempdb。（1）Master 数据库是 SQL Server 系统最重要的数据库，它记录了 SQL Server 系统的所有系统信息。这些系统信息包括所有的登录信息、系统 设置信息、SQL Server 的初始化信息和其他系统数据库及用户数据库的 相关信息。因此，如果 master 数据库不可用，则 SQL Server 无法启 动。在 SQL Server 2005 中，系统对象不再存储在 master 数据库 中，而是存储在 Re
17、source 数据库 中。,（2）model 数据库用作在 SQL Server 实例上创建的所有数据库的模 板。因为每次启动 SQL Server 时都会创建 tempdb，所以 model 数据 库必须始终存在于 SQL Server 系统中。当发出 CREATE DATABASE（创 建数据库）语句时，将通过复制 model 数据库中的内容来创建数据库 的第一部分，然后用空页填充新数据库的剩余部分。如果修改 model 数据库，之后创建的所有数据库都将继承这些修改。例如，可以设置权 限或数据库选项或者添加对象，例如，表、函数或存储过程。,（3）Msdb 数据库是代理服务数据库，为其报警、
18、任务调度和记录操作员的操作提供存储空间。（4）Tempdb 是一个临时数据库，它为所有的临时表、临时存储过程及其他临时操作提供存储空间。Tempd数据库由整个系统的所有数据库使用，不管用户使用哪个数据库，他们所建立的所有临时表和存储过程都存储在 tempdb 上。SQL Server 每次启动时，tempdb 数据库被重新建立。当用户与 SQL Server 断开连接时，其临时表和存储过程自动被删除。,4.5 其他语言元素,4.5.1 注释4.5.2 常量和变量4.5.3 运算符和表达式4.5.4 常用函数4.5.5 程序流程控制语句,4.5.1 注释,注释是程序代码中不执行的文本字符串（也称
19、为注解）。使用注释对代码进行说明，不仅能使程序易读易懂，而且有助于日后的管理和维护。注释通常用于记录程序名称、作者姓名和主要代码更改的日期。注释还可以用于描述复杂的计算或者解释编程的方法。在 SQL Server 中，可以使用两种类型的注释字符：一种是ANSI 标准的注释符“-”，它用于单行注释；另一种是与 C 语言相同的程序注释符号，即“/*/”。“/*”用于注释文字的开头，“*/”用于注释文字的结尾，利用它们可以在程序中标识多行文字为注释。当然，单行注释也可以使用“/*/”，我们只需将注释行以“/*”开头并以“*/”结尾即可。反之，段落注释也可以使用“-”，只需使段落注释的每一行都以“-”
20、开头即可。,例 4-25 使用两种注释类型的例子。程序清单如下：USE AdventureWorks GO-First line of a multiple-line comment.-Second line of a multiple-line comment.SELECT*FROM person.address GO/*注释语句的第一行.注释语句的第二行.*/SELECT*FROM Production.Product GO-在 Transact-SQL 语言调试过程中使用注释语句。,4.5.2 常量和变量,1.常量常量也称标量值，其格式取决于其所表示值的数据类型。常量按照其值的类型不同，
21、大致可以分为字符串常量、Unicode字符串、二进制常量、BIT常量、DATETIME常量、INTEGER常量、DECIMAL常量、FLOAT、REAL常量、MONEY常量、UNIQUEIDENTIFIER常量。其中，在表示负的数值常量时，只需在数值常量前加减号（-）即可。,1）字符串常量在SQL Server中，字符串常量包含在单引号中。一般情况下，字符串常量是由字母（az、AZ）、数字字符（09）以及特殊字符（!,#）组成的。如果单引号中的字符串包含一个嵌入的引号，可以使用两个单引号表示嵌入的单引号。其中，空字符串是由两个没有任何字符的单引号（）表示。SELECT The level#jo
22、b_id:30%!,33,Unicode字符串的格式就是在普通字符串前面有一个大写的N，如LXP是字符串常量，而NLXP是一个Unicode字符串变量。对于字符数据存储Unicode数据时，每个字符使用2个字节，而不是1个字节。,2）二进制常量二进制常量是以0 x开头的数字串，但不是字符串，不需要单引号。由于采用十六进制表时，所以看起来像字符串。3）BIT常量BIT常量其实是一种特殊的INT常量，其有效值要么为0，要么为1，不需要单引号。,35,4）DATETIME常量DATETIME常量是由字符串组成的日期表示，如2008-07-10 00:00:00.000。5）INTEGER常量INTE
23、GER常量是由数字（09）组成，没有小数点。,36,6）DECIMAL常量DECIMAL常量是由数字和小数点组成的小数。7）FLOAT和REAL常量FLOAT和REAL常量是由科学记数法表示的小数，如101.5E5（十进制为10150000）。8）MONEY常量以货币符号开头且包含小数点的数字串表示，一般情况下没有单引号。,37,9）UNIQUEIDENTIFIER常量用来表示GUID的，可以使用字符串或二进制表示，6F9619FF-8B86-B42D-00C04FC964CC 0 xff19966f868b11d0b42d00c04fc964cc是完全相同的GUID。,38,2.变量,变量是
24、一种语言中必不可少的组成部分。Transact-SQL 语言中有两种形式的变 量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。1）.局部变量 局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内 部。局部变量被引用时要在其名称前加上标志“”，而且必须先用 DECLARE 命 令定义后才可以使用。定义局部变量的语法形式如下：DECLAER local_variable data_type n其中，参数 local_variable 用于指定局部变量的名称，变量名必须以符号 开 头，并且局部变量名必须符合 SQL Server 的命名规则。参数 data_type
25、用于设置 局部变量的数据类型及其大小。data_type 可以是任何由系统提供的或用户定义 的数据类型。但是，局部变量不能是 text，ntext 或 image 数据类型。使用 DECLARE 命令声明并创建局部变量之后，会将其初始值设为 NULL，如果 想要设定局部变量的值，必须使用 SELECT 命令或者 SET 命令。其语法形式为：SET local_variable=expression 或者 SELECT local_variable=expression,.n 其中，参数 local_variable 是给其赋值并声明的局部变量，参数 expression 是 任何有效的 SQL
26、 Server 表达式。,1）.局部变量,例 4-26 创建一个 myvar 变量，然后将一个字符串值放在变量中，最 后输出 myvar 变量的值。程序清单如下：DECLARE myvar char(20)select myvar=This is a test SELECT myvar GO 例 4-27 通过查询给变量赋值。程序清单如下：USE adventureworks GO DECLARE rows int SET rows=(SELECT COUNT(*)FROM humanresources.employee),2.全局变量全局变量在整个SQL Server系统内使用。存储的通常是
27、一些SQL Server的配置设定值和统计数据。在使用全局变量时应该注意以下几点：全局变量是在服务器级定义的。SQL Server提供的全局变量分为两类：1)与SQL Server 连接有关的全局变量。如：rowcount表示受最近一个语句影响的行数。2)关于系统内部信息有关的全局变量。如：version表示SQL Server的版本号。全局变量反映的是服务器当前的状态，SQL Server 2000提供了30多个全局变量，下面介绍几个常用的全局变量：connections：返回自上次启动SQL Server 以来连接或试图连接的次数。rowcount：返回上一条T-SQL语句影响到的数据行数
28、。,error：返回上一条T-SQL语句执行后的错误号。procid：返回当前存储过程的ID标识。remserver：返回登录记录中远程服务器的名字。spid：返回当前服务器进程的ID标识。version：返回当前SQLServer服务器的版本。3.局部变量与全局变量的显示使用SELECT语句可以显示局部变量和全局变量。语句形式为：显示局部变量：SELECT variable_name 显示全局变量：SELECT variable_name,本章首页,例 4-29 显示到当前日期和时间为止试图登录SQL Server 的次数。程序清单如下：SELECT GETDATE()AS 当前的时期和时间
29、,CONNECTIONS AS 试图登录的次数 其他见书P79,4.5.3 运算符和表达式,在SQL Server 2008的Transact-SQL语言中，运算符是很重要的部分，可以说对于数据的处理都要用到这些运算符。其实，运算符还是主要用于连接表达式、变量以及常量。不同的运算符其使用意义不同，返回值亦不同。本节将详细介绍这些运算符。,44,算术运算符,在SQL脚本中，算术运算符用来对两个表达式进行数学运算，其返回值的数据类型由参与运算的两个表达式的数据类型决定。而这两个表达式值的数据类型可以是相同的数据类型，也可以是能够被SQL Server进行自动类型转换的数据类型。如果参与算术运算的两
30、个表达式不能够被SQL Server自动转换类型，那么将出错。常见运算符：+、-、*、/、%,45,位运算符,在SQL Server中，位运算是一种特殊的运算符，主要是对两个整数转化为二进制后，再执行位运算，这种运算使用较少，但功能很强大，也很重要。,46,按位运算符,DECLARE var_INT_1 INT=223DECLARE var_INT_2 INT=123SELECT var_INT_1,var_INT_2,var_INT_1var_INT_2 AS 结果,47,比较运算符,在SQL Server中，比较运算符是常用的运算符，主要用于比较两个表达式。除了 text，ntext 或
31、image 数据类型的表达式外，比较运算符可以用于所有的表达式。DECLARE var_INT_1 INT=223DECLARE var_INT_2 INT=123IF var_INT_1var_INT_2PRINT 比较运算符：,48,需要注意：当字符型数据进行大小比较时，是根据字符串每个字符的编码从首位开始比较。如cbbz，但cbcbz则错误，因为前面两个字符相同，而cb没有第三个字符，所以认为它小。例题4-30 见书p81例题4-4,逻辑运算符,在SQL脚本中，逻辑运算符主要用于条件语句中条件的测试，其返回值为TRUE或FALSE；算术运算符主要有AND、NOT、OR等。例题p82,50
32、,范围搜索运算符,1.between and如果操作数在某个范围内，则为TRUE，否则FALSE。2.in：表示如果操作数等于表达式列表中的一个，即为TRUE，否则FALSE例题P82,模式匹配运算符,Like 字符串串联运算符字符串串联运算符允许通过加号（+）进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句SELECT abc+def，其结果为abcdef。,在SQL Server 2005中，运算符的优先等级从高到低如下所示，如果优先等级相同，则按照从左到右的顺序进行运算。（1）括号：（）；（2）乘、除、求模运算符：*，/，%；（3）加减运算符：，；（4）比较运算符：=，=，
33、!=，!，!；（5）位运算符：，&，|；（6）逻辑运算符：NOT；（7）逻辑运算符：AND；（8）逻辑运算符：OR。,4.5.4 常用函数,函数综述 系统函数 用户自定义函数的基本操作 创建用户自定义函数 查看用户自定义函数 修改用户自定义函数 删除用户自定义函数,4.5.4 常用函数,函数综述 为了使用户对数据库进行查询和修改时更加方便，SQL server在T-SQL语言中提供了许多内部函数以供调用。用户也可以根据自己的需要创建函数。函数可以由系统提供，也可以由用户创建。系统提供的函数称为内置函数，也叫做系统函数，它为用户方便快捷地执行某些操作提供帮助；用户创建的函数称为用户自定义函数，它
34、是用户根据自己的特殊需求而创建的，用来补充和扩展内置函数。,4.5.4 常用函数,系统函数 标量函数 系统函数 标量函数 聚合函数 行集函数。标量函数 标量函数对单一值操作，返回单一值。只要在能够使用表达式的地方，就可以使用标量函数。数学函数 日期和时间函数 字符串函数 数据类型转换函数。,4.5.4 常用函数,系统函数标量函数 数学函数 1、abs(数值型表达式)功能：返回表达式 的绝对值，其值的数据类型与参数一致。例：SELECT ABS(-1),ABS(0),ABS(1)2、ceiling(数值型表达式)功能：返回最小的大于或等于给定数值型表达式的整数值，值的 类型和给定的值相同。flo
35、or(数值型表达式)功能：返回最大的小于或等于给定数值型表达式的整数值。例：SELECT FLOOR(123.45),CEILING(123.45)SELECT FLOOR(-123.45),CEILING(-123.45),4.5.4 常用函数,系统函数标量函数 数学函数 3、SQUARE(float表达式)函数 功能：此函数用于返回给定表达式的平方值 例如：SQUARE(3)的结果为9.0。POWER（数值型表达式1，数值型表达式2）函数 功能：此函数用于返回给定表达式乘指定次方的值。成方运算函 数返回值的数据类型与第一个参数的数据类型相同。例如：POWER(2,3)表示2的3次方。SEL
36、ECT POWER(2,-3),POWER(2.0,-3),POWER(2.000,-3)4、sign(数值型表达式)功能：判断数值的正负属性，+1表示正数；-1表示负数。,4.5.4 常用函数,系统函数标量函数 数学函数 5、rand(整型表达式)功能：返回一个位于0和1之间的随机数，在单个查询中反复调用 rand()将产生相同的值。例：DECLARE counter smallint SET counter=1 WHILE counter 5 BEGIN SELECT RAND(counter)Random_Number SET NOCOUNT ON SET counter=counter
37、+1 SET NOCOUNT OFF END GO,4.5.4 常用函数,系统函数标量函数 数学函数 6、round(数值表达式，整数)功能：将数值四舍五入成整数指定的精度形式。整数为正表示要 进行的运算位置在小数点后，为负表示在小数点前。例：SELECT ROUND(789.34,1),ROUND(789.34,0)SELCET ROUND(789.34,-1),ROUND(789.34,-2)7、三角函数 ACOS(float表达式)反余弦函数：返回以弧度表示的角度值。ASIN(float表达式)反正弦函数：返回以弧度表示的角度值。ATAN(float表达式)反正切函数：返回以弧度表示的角
38、度值。SIN(float表达式)正弦函数：返回输入表达式的三角正弦值。COS(float表达式)余弦函数：返回输入表达式的三角余弦值。TAN(float表达式)正切函数：返回输入表达式的三角正切值。COT(float表达式)余切函数：返回输入表达式的三角余切值。,4.5.4 常用函数,系统函数标量函数日期和时间函数 GETDATE（）：返回当前系统日期和时间。DATEADD（datepart,number,date）：在date值上加上datepart和number参数指定的时间间隔，返回新的datetime值。DATEDIFF（datepart,startdate,enddate）：返回跨两
39、个指定日期的日期和时间边界数。DATENAME（datepart,date）：返回代表指定日期的指定日期部分的字符串。DATEPART（datepart,date）：返回代表指定日期的指定日期部分的整数。YEAR（date）：返回表示指定日期中的年份的整数。MONTH（date）：返回代表指定日期月份的整数。DAY（date）：返回代表指定日期的天的日期部分的整数。,4.5.4 常用函数,系统函数标量函数一个date 型数据日期部分的取值如下：Datepart 缩写 Datepart 缩写 year yy,yyyy quarter qq,q month mm,m dayof year dy,y
40、 day dd,d week wk,ww weekday dw hour hh minute mi,n second ss,s millisecond ms,4.5.4 常用函数,系统函数标量函数 例：查看今天的年月日，并以格式化的形式显示。在查询分析器中输 入以下代码：SELECT 今天是+DATENAME(YY,GETDATE()+年+DATENAME(MM,GETDATE()+月+DATENAME(DD,GETDATE()+日 例：用日期函数计算教师表中教师的年龄。其程序代码如下：USE STUDENT GO SELECT 姓名,DATEDIFF(YY,出生日期,GETDATE()AS
41、年龄 FROM 教师 GO,4.5.4 常用函数,系统函数标量函数字符串函数 1、ASCLL(字符)功能：返回字符的ASCLL码值，值的类型为整型。2、CHAR(整型表达式)功能：将整型的ASCII代码转换为字符。3、CHARINDEX(字符型表达式1,字符型表达式2,开始位置)功能：返回字符串中指定表达式的起始位置。4、LOWER(字符型表达式)功能：将大写字符数据转换为小写字符。UPPER(字符型表达式)：功能：将小写字符数据转换为大写字符。,4.5.4 常用函数,系统函数标量函数字符串函数 5、STR(float型表达式,长度,小数点后长度)功能：将数字数据转换为字符数据。6、LEFT(
42、字符型表达式,整型表达式)功能：返回字符串中从左边开始指定个数的字符。RIGHT(字符型表达式,整型表达式)功能：返回字符串中从右边开始指定个数的字符。7、LTRIM(字符型表达式)功能：删除起始空格后返回字符表达式。RTRIM(字符型表达式)功能：截断所有尾随空格后返回一个字符串。8、LEN(字符串表达式)功能：返回给定字符串表达式的字符（而不是字节）个数，其中 不包含尾随空格。,4.5.4 常用函数,系统函数标量函数字符串函数 9、SUBSTRING(expression,start,length)功能：从expression的第start个字符处返回length个字符。例：将学生表中的出
43、生日期的月份转化为字符串，并测试其长度：SELECT LEN(STR(MONTH(出生日期)FROM 学生 WHERE 学号例：使用字符串函数查找姓刘的同学，并格式化显示其出生年月。USE STUDENT GO SELECT 姓名,STR(YEAR(出生日期)+年+LTRIM(STR(MONTH(出生日期)+月 AS 出生年月 FROM 学生 WHERE LEFT(姓名,1)=刘 GO,4.5.4 常用函数,系统函数标量函数数据类型转换函数 1、CAST(expression AS data_type)功能：将某种数据类型的表达式显式转换为另一种数据类型。2、CONVERT(data_type
44、(length),expression,style)功能：将表达式的值从一种数据类型转换为另一种数据类型。例：USE STUDENT GO SELECT 学号+同学平均成绩为+CAST(AVG(成绩)AS CHAR(2)+分 FROM 课程注册 GROUP BY 学号 GO,4.5.4 常用函数,系统函数标量函数其它函数 1、isdate(表达式)功能：确定输入表达式的值是否为有效日期，如果是返回1，否则返回0。例：select isdate(“Feb 7 1995 11:00pm”),isdate(null),isdate(“1998.12.02”),isdate(“1997.2.29”),
45、isdate(“1/23/95”)go 2、isnull(表达式1，表达式2)功能：判断表达式1的值是否为空，如果是，则返回表达式2的值；如果不是则返回表达式1的值。使用此函数时，表达式1和表达式 2的类型必须相同。3、print(字符串表达式)功能：将字符串输出给用户。,4.5.4 常用函数,系统函数标量函数 聚合函数 聚合函数对一组值进行计算后，向调用者返回单一的值。一般情况下，它经常与SELECT语句的GROUP BY 子句一同使用。1、COUNT(*)功能：用于计算所有行数 2、MIN(数值表达式)功能：用于计算表达式的最小值 3、MAX(数值表达式)功能：用于计算表达式的最大值 4、
46、SUM(数值表达式)功能：用于计算表达式的和 5、AVG(数值表达式)功能：用于计算表达式的平均值,4.5.4 常用函数,系统函数标量函数 聚合函数 例：使用聚合函数统计STUDENT数据库中学生的成绩情况。USE STUDENT GO SELECT COUNT(*)AS 课程门数,MAX(成绩)AS 最高分数,MIN(成绩)AS 最低分数,SUM(成绩)AS 总成绩,AVG(成绩)AS 平均成绩 FROM 课程注册 GROUP BY 学号 GO,4.5.4 常用函数,用户自定义函数 在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。用户自
47、定义函数可以在企业管理器中创建，也可以使用CREATE FUNCTION 语句创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。标量函数：返回单个数据值。表值函数：返回值是一个记录集合表。在此函数中，return语 句包含一条单独的select语句。多语句表值函数：返回值是由选择的结果构成的记录集。,4.5.4 常用函数,用户自定义函数1、使用CREATE FUNCTION语句创建用户自定义函数 在查询分析器中，可以使用CREATE FUNCTION创建用户自定义函数，其语法格式如下：CREATE FUNCTION o
48、wner_name.function_name(parameter_name AS scalar_parameter_data_type=default,.n)RETURNS scalar_return_data_type AS BEGIN function_body RETURN scalar_expression END,4.5.4 常用函数,用户自定义函数 function_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用于该函数本身；
49、相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定default关键字才能获得默认值。scalar_parameter_data_type：参数的数据类型。scalar_return_data_type：是用户定义函数的返回值。可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。function_body：位于begin和end之间的一系列 Transact-S
50、QL 语句，其只用于标量函数和多语句表值函数。scalar_expression：用户自定义函数中返回值的表达式。,4.5.4 常用函数,用户自定义函数 例：在STUDENT库中创建一个用户自定义函数XUEFEN，该函数通过输入成 绩来判断是否取得学分，当成绩大于等于50时，返回取得学分，否则，返回未取得学分。其代码如下：CREATE FUNCTION xuefen(inputxf int)RETURNS nvarchar(10)BEGIN declare retrunstr nvarchar(10)If inputxf=50 set retrunstr=取得学分 else set retru
