1、SQL Server,第6章 SQL Server内置函数,第6章 SQL Server内置函数,6.1 配置函数 6.2 系统函数 6.3 系统统计函数 6.4 时间日期函数 6.5 字符串函数 6.6 数学函数 6.7 图像和文本函数 6.8 安全函数 6.9 其他函数,6.1 配置函数,作用：返回系统的配置选项当前设置信息。 特点：以两个开头，调用时不带参数。 列表：表6-1罗列了所有配置函数及其含义 。 举例： 【例6-5】下面的示例返回当前用户进程的进程ID、登录名和用户名。 SELECT SPID AS ID, SYSTEM_USER AS 登录名, USER AS 用户名,6.2
2、 系统函数,1CASE函数：计算条件列表并返回多个可能结果之一。 简单CASE函数CASE WHEN THEN WHEN THEN ELSE END CASE搜索函数 CASEWHEN THEN WHEN THEN ELSE END,1CASE函数：计算条件列表并返回多个可能结果之一。,6.2 系统函数,例子： 【例6-6】显示每个学生的姓名、来自的省份和主修专业。 SELECT Sname AS 姓名, CASE Scity , WHEN 杭州 THEN 浙江 WHEN 宁波 THEN 浙江WHEN 金华 THEN 浙江WHEN 西安 THEN 陕西WHEN 太原 THEN 山西END AS
3、 省份, Smajor AS 专业 FROM Student,【例6-7】显示学生的学号以及学生成绩(按优、良、中、及格、不及格、未知显示)。 SELECT Sno AS 学号, 成绩 = CASE , WHEN Grade=90 THEN 优 WHEN Grade=80 THEN 良 WHEN Grade=70 THEN 中 WHEN Grade=60 THEN 及格 WHEN Grade=0 THEN 不及格 ELSE 未知 END FROM Enrollment,6.2 系统函数,2CAST和CONVERT ：将某种数据类型的表达式显式转换为另一种数据类型的值并返回 。格式：CAST (
4、 AS () ) CONVERT (), , style) 若实现日期时间和字符型之间的转换，则style取表6-2所列的值。 若将FLOAT 或 REAL转换到字符型，则style取表6-3所列的值。 若将MONEY或SMALLMONEY转换到字符型，则style取表6-4所列的值。 【例6-9】下面是使用CAST和CONVERT的简单例子。 SELECT CAST(ABC AS CHAR(10) )+CDE SELECT CONVERT(VARCHAR(60), GETDATE(), 126),6.2 系统函数,3COALESCE(, ., ) 返回第一个非空表达式的值。若所有表达式均空，
5、则返回NULL。所有表达式必须具有相同类型，或者可隐性转换为相同类型。 等价于下列CASE函数：CASEWHEN IS NOT NULL THEN WHEN IS NOT NULL THEN ELSE NULL END 4ERROR: 以INT返回最近的错误代码（0表示正确）。 5FORMATMESSAGE (, , ., ) 将master数据库sysmessages表中error列值为的消息中的n个参数用、替换后返回整个消息文本。,6ISNULL(check_expression, replacement_value) 如果表达式check_expression的值为NULL，则函数返回表
6、达式replacement_value的值，否则返回表达式check_expression的值。返回值类型和check_expression类型相同。 【例6-12】下面示例查询学生的学号和平均成绩，如果某门课的成绩为空，则以60分计。SELECT Sno AS 学号, AVG(ISNULL(Grade, 60) AS 平均成绩 FROM Enrollment GROUP BY Sno 7ISNUMERIC (expression) 确定表达式expression的值是否为有效的数值。若表达式expression的值为有效的整数、浮点数、MONEY或DECIMAL类型，则返回1，否则返回0。
7、8NEWID( ) ：返回UNIQUEIDENTIFIER类型的唯一值。,6.2 系统函数,9NULLIF (expression1, expression2) 若表达式expression1的值等于expression2的值，则返回空，否则返回expression1的值。 【例6-15】下面示例计算每个学生获得及格以上成绩的总分。计算时若某门课成绩为空，则以60分计；若成绩低于60分则以NULL计（即不算到总分中）。SELECT Sno, SUM(NULLIF(CASEWHEN Grade IS NULL THEN 60WHEN Grade 60 THEN 1ELSE Grade END,
8、-1 ) ) FROM Enrollment GROUP BY Sno,6.2 系统函数,返回,10PERMISSIONS ( objectid , column ) 以NCHAR类型返回一个包含位图的值，表明当前用户对语句、对象或列的操作权限。其中objectid是对象标识符。 如果未指定objectid，则位图值包含当前用户的语句权限，否则包含当前用户在objectid所指对象上的操作权限； 当objectid是表时，用column指明要返回权限信息的列名。 未指定objectid时，返回值及其含义见表6-5。指定objectid但未指定column时，返回值及其含义见表6-6。同时指定了
9、objectid和column时，返回值及其含义见表6-7。 返回值的低16位反映对当前用户的安全帐户所授予的权限； 返回值的高16位反映当前用户可以授予其他用户的权限。 【例6-16】确定当前用户是否能够在Course表中插入数据行。IF PERMISSIONS(OBJECT_ID(Course)&8=8 PRINT 当前用户可以在Course表中插入数据行。 ELSE PRINT 当前用户不能在Course表中插入数据行。,6.2 系统函数,6.3 系统统计函数,作用：系统统计函数用来返回系统的各种统计信息。 特点：一个是行集函数，其余是标量函数。 列表：表6-10罗列了所有系统统计函数及
10、其含义。 举例： 【例6-17】下面的示例显示到当前日期和时间为止试图登录的次数。SELECT GETDATE( ) AS 时间日期, CONNECTIONS AS 登陆次数,6.4 时间日期函数,作用：对日期和时间输入值执行操作，返回一个字符串、数字值或日期和时间值。这些函数都是标量函数。 1DATEADD(datepart, number, date) 在指定日期时间上加一段时间，得到新的DATETIME值并返回。 datepart是规定应向日期和时间的哪一部分计算新值的参数，具体取值如表6-11所示； number是将要累加到datepart所规定部分的值； date用来指定基础日期时间
11、。 【例6-28】下面的示例将当前日期时间加上3周后显示出来。DECLARE old DATETIME, new DATETIME SELECT old=GETDATE( ), new=DATEADD(wk, 3, old ) SELECT old AS 当前日期时间, new 三周后的日期时间,6.4 时间日期函数,2DATEDIFF(datepart, startdate, enddate ) 以INT类型返回两个给定日期时间值之差。 datepart规定了计算差额的日期时间部分，取值如表6-11所示； startdate和enddate分别给出了起点日期时间和终止日期时间。 【例6-29
12、】计算执行一个语句块所消耗的时间(单位为毫秒)。 DECLARE start DATETIME, end DATETIME SELECT start=GETDATE( ) /*获得语句块的时间起点*/ BEGIN /*语句块开始*/ IF EXISTS(SELECT * FROM sysobjects WHERE name=mytb AND xtype=U ) DROP TABLE mytb SELECT * INTO mytb FROM sysobjects END /*语句块结束*/ SELECT end=GETDATE( ) /*获得语句块的时间终点*/ SELECT DATEDIFF(
13、ms, start, end) AS 消耗的时间(ms) ,6.4 时间日期函数,3DATENAME(datepart, date ) 以NVARCHAR类型返回代表指定日期时间的指定部分的字符串。 datepart规定了日期时间部分，取值如表6-11所示； date给出了指定日期时间。 【例6-30】从当前日期中提取所处月份和本年的第几个星期信息。 DECLARE date DATETIME SELECT date= GETDATE( ) SELECT DATENAME(m,date) 月份, DATENAME(wk,date) 星期 4DATEPART(datepart, date) 以I
14、NT类型返回代表指定日期时间的指定部分的整数。 5DAY(date) ：以INT类型返回给定日期时间是本月的几号。 6GETDATE( )：以DATETIME类型返回当前系统日期和时间。 7GETUTCDATE( ) ：以DATETIME返回当前格林尼治标准时间。 8MONTH(date) 、YEAR(date) ：以INT返回日期时间的月份、年份,6.5 字符串函数,作用：对字符串执行操作，返回字符串或数值。都是标量函数。 1ASCII(character_expression) : 以INT返回character_expression最左端字符的ASCII代码值 。 2CHAR(integ
15、er_expression) ：以CHAR(1)类型返回ASCII代码等于整型表达式integer_expression的值的字符。 【例6-33】生成一张可见字符的ASCII码表。 CREATE TABLE AscTB ( Character CHAR(1), Ascii INT ) DECLARE asc INT SET asc=33 WHILE asc=126 BEGIN INSERT INTO AscTB VALUES(CHAR(asc), asc)SET asc=asc+1 END SELECT * FROM AscTB,6.5 字符串函数,3UNICODE(ncharacter_e
16、xpression) ：以INT返回给定字符串最左端字符的UNICODE代码值。 4NCHAR(integer_expression) ：以NCHAR(1)类型返回UNICODE代码等于整型表达式integer_expression的值的字符。 【例6-35】生成一张含94个汉字的UNICODE码表。 CREATE TABLE UniTB ( Character NCHAR(1), Unicode INT ) DECLARE uni INT, count INT SELECT uni = UNICODE(N啊), count=0 WHILE count94 BEGIN INSERT INTO
17、UniTB VALUES(NCHAR(uni), uni) SELECT uni=uni+1, count=count+1 END SELECT * FROM UniTB 5 CHARINDEX(expression1, expression2 , start_location ) 在expression2中从start_location位置开始搜索expression1的首次出现，返回首字符位置。注意字符位置从1开始计算。,6.5 字符串函数,6SOUNDEX(character_expression)：以CHAR类型返回字符串的四字符代码，常用来评估两个字符串的相似性。 7DIFFEREN
18、CE(character_expression1, character_expression2) 以INT类型返回两个字符表达式的SOUNDEX值之差别。 8LEFT(character_expression, count) ：以VARCHAR类型返回从字符串character_expression左边截取的长度为count的子串。 9RIGHT(character_expression, count) ：以VARCHAR类型返回从字符串character_expression右边截取的长度为count的子串。 10SUBSTRING(character_expression , start
19、, length ) ：返回字符串character_expression中从位置start开始的长度为length的子串。 11LEN(string_expression) ：返回给定字符串表达式的字符数，不包含尾随空格。 12LOWER(character_expression) 、UPPER(character_expression) 将给定字符串中的大写(小写)字母转换为小写(大写)字母。,6.5 字符串函数,13LTRIM(character_expression) 、RTRIM(character_expression) 将给定字符串左(右)边前导(尾随)空格删除后返回整个字符串。
20、 【例6-44】下面的示例展示了LTRIM和RTRIM函数的用法。 DECLARE string CHAR(20) SET string = ABCDEFG PRINT + LTRIM(string)+ PRINT + RTRIM(string)+ PRINT + LTRIM(RTRIM(string)+ 14REPLACE(字符串1,字符串2,字符串3)：用字符串3替换字符串1中字符串2的所有出现并返回替换结果。 15QUOTENAME(character_string , quote_character ) ：将字符串character_string首尾加上定界符quote_charact
21、er后返回。 16REPLICATE(character_expression, count)：将给定字符串重复count次后返回。 17REVERSE(character_expression)：将给定字符串反转后返回。,6.5 字符串函数,18SPACE(count )：返回由count个空格组成的字符串。 19STR(float_expression , length , decimal ) ：将给定数值转换成长度为length，小数位数为decimal的数字字符串。 20STUFF(string_expression1, start, length, string_expression
22、2) 用字符串string_expression2替换string_expression1中从start开始的length个字符并返回替换结果。 【例6-50】用ijklmn替换ABCDEF中第2个字符开始的3个字符。 PRINT STUFF(ABCDEF, 2, 3, ijklmn),6.6 数学函数,作用：对数值型输入值执行计算，并返回一个数值。这些函数都是标量函数。 列表：表6-12罗列了所有数学函数及其含义 。 举例： 【例6-56】下例产生4个不同的随机数。DECLARE counter SMALLINT SET counter = 1 WHILE counter 5 BEGIN P
23、RINT RAND(counter) SET counter = counter + 1 END,6.7 图像和文本函数,作用：对文本或图像输入值或列执行操作，返回有关这些值的信息。这些函数都是标量函数。 1PATINDEX(%pattern%, expression)：返回给定字符串中模式串pattern第一次出现的起始位置。如果没有找到该模式，则返回零。 【例6-58】在字符串中搜索由4个数字构成的串，并返回其位置。PRINT PATINDEX(%0-90-90-90-9%, ABC057186718889DEF) 2TEXTPTR( column )：以VARBINARY类型返回对应于T
24、EXT、NTEXT或IMAGE列column的文本指针值。 3TEXTVALID( table.column, text_ ptr )：检查table表column列文本指针text_ ptr的有效性。如果指针有效则返回 1，否则返回 0。 有效文本指针用于READTEXT、WRITETEXT和UPDATETEXT 语句。有关用法的详细示例请参阅5.3.2。,6.8 安全函数,作用：用于返回有关用户、角色、跟踪等信息。这些函数有的是行集函数，有的是标量函数。 1:FN_TRACE_GETEVENTINFO(trace_id)：返回有关跟踪的事件信息(含2列的行集) 。其中trace_id是此跟
25、踪的整型标识码 。 2:FN_TRACE_GETFILTERINFO(trace_id)：返回有关跟踪的筛选信息(含4列的行集)。 3:FN_TRACE_GETINFO(trace_id)：返回有关跟踪的筛选信息(含3列的行集)。 4:FN_TRACE_GETTABLE(filename, number_files)：以表格格式返回跟踪文件信息。 5HAS_DBACCESS (database_name)：确定当前用户是否可以访问指定的数据库，若是则返回1。 6IS_MEMBER ( group | role )：确定当前用户是否是指定Windows NT组或SQL Server角色的成员，若
26、是则返回1，否则返回0或NULL（当group或role无效时）。,6.8 安全函数,7IS_SRVROLEMEMBER(role , login )：指明当前的用户登录是否为指定服务器角色的成员。若是则返回1，否则返回0或NULL(当role或login无效时)。 8SUSER_SID(login )：以VARBINARY(85)类型返回用户登录名login的安全标识号(SID)。若未指定login，则返回当前用户的SID。 9SUSER_SNAME(server_user_sid )：从安全标识号server_user_sid返回登录标识名。如果未指定server_user_sid，则返回
27、当前用户的登录标识名。 10USER_ID(user)：以SMALLINT类型返回用户user的数据库标识号。若省略user，则假定为当前用户。 11USER：以CHAR类型返回当前用户的数据库用户名。,6.9 其他函数,包括：游标函数和元数据函数。游标函数用来返回有关游标的信息；元数据函数返回有关数据库和数据库对象的信息。 除一个行集函数外，这些函数都是标量函数。 1CURSOR_ROWS ：以INT类型返回与最后打开的游标相连接的合格行数量。其返回值的含义见表6-13。 2CURSOR_STATUS(cursor_type, cursor)：以SMALLINT类型返回给定游标的状态(如表6
28、-14)。cursor_type是游标类型，它可以取：1）local：表明cursor是一个本地游标名常量，2）global：表明cursor是一个全局游标名常量；3）variable表明cursor是一个游标变量。 3FETCH_STATUS：以INT类型返回最近一条FETCH语句的执行状态，返回值的含义见表6-15。 4COL_LENGTH(table, column)：以INT类型返回table表中column列的定义长度（单位为字节）。其中table和column分别是表名和列名。 5COL_NAME(table_id, column_id)：返回table_id表中column_id
29、列的名字。table_id和column_id分别是表和列标识号。,6.9 其他函数,6COLUMNPROPERTY(id, column, property)：返回列或存储过程参数的信息。id是表或过程标识符；column是列名或参数名；property是要返回的信息类型，它可以取表6-16所列值之一。 7DATABASEPROPERTY(database, property)：返回给定数据库的指定选项或属性的当前设置。其中database是数据库名，propert是要返回的属性名称，其取值和含义请参阅SQL Server帮助。 8DATABASEPROPERTYEX(database, p
30、roperty)：返回给定数据库的指定选项或属性的当前设置。 9DB_ID(database_name)：返回数据库database_name的数据库标识号。若未指定database_name，则返回当前数据库的标识号。 10DB_NAME(database_id)：返回标识号为database_id的数据库名。若未指定database_id，则返回当前数据库名。 11FILE_ID(file_name)：返回当前数据库中逻辑文件名为file_name的文件标识号。 12FILE_NAME (file_id)：返回文件标识号为file_id的逻辑文件名。,6.9 其他函数,13FILEGROU
31、P_ID (filegroup_name)：返回文件组名为filegroup_name的文件组标识号。 14FILEGROUP_NAME(filegroup_id)：返回文件组标识号为filegroup_id的文件组名。 15FILEGROUPPROPERTY (filegroup_name, property)：返回指定文件组的property属性值。filegroup_name是文件组名，property是要返回的文件组属性名, 取IsReadOnly、IsUserDefinedFG和IsDefault之一，分别用来测试文件组是否为只读的、用户定义的和默认的。 16FILEPROPERTY
32、(file_name, property)：返回指定文件的property属性值。file_name是文件名，property是要取的文件属性名，取IsReadOnly、IsPrimaryFile、IsLogFile 和SpaceUsed 之一，前三个分别用来测试文件是否为只读文件、主文件和日志文件，最后一个用来获取文件所占空间大小（以页为单位）。,17:FN_LISTEXTENDEDPROPERTY(property, t0, n0, t1, n1, t2, n2)：返回数据库对象的扩展属性值。返回值是一个拥有4列的表。 property是属性名，有效值是DEFAULT、NULL或属性名；
33、t0是0级对象类型，有效值是USER、TYPE、DEFAULT和NULL； n0指定0级对象名称，其有效值为DEFAULT、NULL或对象名； t1是1级对象类型，其有效值为TABLE、VIEW、PROCEDURE、FUNCTION、DEFAULT、RULE、DEFAULT和NULL； n1指定1级对象名称，其有效值为DEFAULT、NULL或对象名； t2是2级对象类型，其有效值为COLUMN、PARAMETER、INDEX、CONSTRAINT、TRIGGER、DEFAULT和NULL； n2指定2级对象名称，其有效值为DEFAULT、NULL或对象名。 18FULLTEXTCATALOG
34、PROPERTY(catalog_name, property)：返回全文目录的property属性信息。catalog_name是全文目录名，property是要返回的全文目录属性名，其取值和含义请参阅SQL Server帮助。,6.9 其他函数,19FULLTEXTSERVICEPROPERTY (property)：返回全文服务级属性信息。property是要返回属性名，取值和含义请参阅SQL Server帮助。 20INDEX_COL (table, index_id , key_id)：返回索引列的名称。其中table是表的名称，index_id是索引的标识号，key_id是索引键的
35、标识号。 21INDEXKEY_PROPERTY(table_ID, index_ID, key_ID, property) 返回索引键信息。table_ID是表标识，index_ID是索引标识，key_ID是索引键标识，property是信息类型，取ColumnId或 IsDescending，分别用来返回索引键的列ID和索引列存储顺序（0表升序，1表降序）。 22INDEXPROPERTY (table_ID, index, property)：给定表标识号table_ID、索引名称index及属性名称property，返回指定的索引属性值。其中property是要返回的索引属性类型，其取
36、值和含义请参阅SQL Server帮助。 23OBJECT_ID(object)：返回数据库对象标识号。object是对象名称。,6.9 其他函数,24OBJECT_NAME (object_id)：返回标识号为object_id的对象名称。 25OBJECTPROPERTY(id , property)：返回当前数据库中标识号为id的对象信息。property是要返回的信息类型，其取值和含义请参阅帮助。 26PROCID：返回当前存储过程的标识符。 27SQL_VARIANT_PROPERTY(expression, property)： 返回SQL_VARIANT值的信息。其中expression是SQL_VARIANT类型的表达式，property是要返回的信息类型，它可以取表6-17所列值之一。 28TYPEPROPERTY (type, property )： 返回数据类型type的property信息。其中type是数据类型的名称，property是要返回的信息类型，它可以取表6-18所列值之一。,6.9 其他函数,Thank You !,
