100分求SQL server 中系统函数精细讲解

100分求SQL server 中系统函数精细讲解

系统函数用于获取有关计算机系统用户数据库和数据库对象的信息系统函数可以让用户在得到信息后使用条件语句根据返回的信息进行不同的操作与其它函棚纯数一样可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数1APP_NAMEAPP_NAME函数语法如下APP_NAMEAPP_NAME函数返回当前执行的应用程序的名称稿亏其返回值类型为nvarchar128例4-77测试当前应用程序是否为SQL Server Query Analyzerdeclare @currentApp varchar50set @currentApp = app_nameif @currentApp 'SQL Query Analyzer'print 'This process was not started by a SQL Server Query Analyzer query session.'运行结果如下-------------------- ------- -----------The commands completed successfully./* 表明当前应用程序是SQL Server Query Analyzer*/2COALESCECOALESCE函数语法如下COALESCE [...n]COALESCE函数返回众多表达式中第一个非NULL表达式的值如果所有的表达式均为NULL则COALESCE函数返回NULL值COALESCE函数等效于下面的CASE语句CASEWHEN expression1 IS NOT NULL THEN expression1…WHEN expressionN IS NOT NULL THEN expressionELSE NULL例4-78declare @x char @y char10 @d datetimeset @y = 'first'set @d = getdateselect COALESCE@x @y convertchar20 @d运行结果如下-------------------first3COL_LENGTHCOL_LENGTH函数语法如下COL_LENGTHCOL_LENGTH函数返回表中指定字段的长度值其返回值为INT类型例4-79use panguselect col_length'employee'键和神 'e_name' as employee_name_length运行结果如下employee_name_length--------------------204COL_NAMECOL_NAME函数语法如下COL_NAMECOL_NAME函数返回表中指定字段的名称即列名其返回值为SYSNAME类型其中table_id和column_id都是INT类型的数据函数用table_id和column_id参数来生成列名字符串关于如何获得table_id和column_id请参见OBJECT_ID函数例4-80use panguselect col_nameobject_id'employee' ordinal_positionfrom information_schema.columnswhere table_name = 'employee'运行结果如下----------------------emp_ide_namebirthdayjob_leveldept_idhire_datee_wage7 rows affected5DATALENGTHDATALENGTH函数语法如下DATALENGTHDATALENGTH函数返回数据表达式的数据的实际长度其返回值类型为INTDATALENGTH函数对VARCHARVARBINARYTEXTIMAGENVARCHAR和NTEX等能存储变动长度数据的数据类型特别实用NULL的长度为NULL例4-81use panguselect length = datalengthe_name e_namefrom employeeorder by length运行结果如下length e_name----------- --------------------… …4 张三4 李四… …6 梁山伯6 祝英台… …6DB_IDDB_ID函数语法如下DB_ID['database_name']DB_ID函数返回数据库的编号其返回值为SMALLINT类型如果没有指定database_name则返回当前数据库的编号例4-82use masterselect name db_idname as db_idfrom sysdatabasesorder by dbid运行结果如下name database_id------------------------------------------------------------------------------------master 1tempdb 2model 3msdb 4pubs 5Northwind 6PANGU 77 rows affected7DB_NAMEDB_NAME函数语法如下DB_NAMEdatabase_idDB_NAME函数返回数据库的名称其返回值类型为NVARCHAR128database_id是SMALLINT类型的数据如果没有指定database_id则返回当前数据库的名称例4-83use masterselect dbid db_namedbid as db_namefrom sysdatabasesorder by dbid运行结果如下dbid dbname--------------------------------------1 master2 tempdb3 model4 msdb5 pubs6 Northwind7 PANGU7 rows affected8FORMATMESSAGEFORMATMESSAGE函数语法如下FORMATMESSAGE [...n]FORMATMESSAGE函数用于从系统信息表sysmessages中挑选一条现存的信息再将其格式转换为用户需要的形式其功能类似于RAISERROR命令不同的是RAISER-ROR命令立即打印出信息而FORMATMESSAGE函数返回编辑过的信息给调用它的程序其返回值类型为NVARCHAR其中message_number是存储在sysmessages系统表中的信息ID号param_value是在信息中使用的参数最多可使用20个参数FORMATMESSAGE函数查找使用当前语言平台语言的信息如果没有本地语言的信息则使用U.S. English版本语言的信息例4-84假定有一个编号为10001的信息存储在sysmessages表中此信息的内容为The Length of rows %column_name in %table_name is %column_num.FORMATMESSAGE函数用值myColumnmyTable和20分别替代上述参数值将结果存储在变量@test1中程序如下declare @test1 varchar100select @test1 = formatmessage10001 'myColumn' 'myTable' 209GETANSINULLGETANSINULL函数语法如下GETANSINULL['database_name']GETANSINULL函数返回当前数据库默认的NULL值其返回值类型为INTGETANSINULL函数对ANSI空值NULL返回1如果没有定义ANSI空值则返回0例4-85select getansinull'pangu'运行结果如下------110HOST_IDHOST_ID函数语法如下HOST_IDHOST_ID函数返回服务器端计算机的名称其返回值类型为NCHAR例4-86declare @hostID char8select @hostID = host_idprint @hostID运行结果如下-60677111HOST_NAMEHOST_NAME函数语法如下HOST_NAMEHOST_NAME函数返回服务器端计算机的名称其返回值类型为CHAR8例4-87declare @hostNAME nchar20select @hostNAME = host_nameprint @hostNAME运行结果如下XUJIN12IDENTITYIDENTITY函数语法如下IDENTITY[ seed increment] [AS column_name]IDENTITY函数只在SELECTINTO语句中使用用于插入一个identity column列到新表中其返回值类型与data_type的类型相同其中data_type指定数据类型data type是INTEGER 或DECIMAL类的数据类型Seed指定identity column的初值即第一个值Increment指定增加一个记录时记录的identitycolumn值应在前记录的基础上增加的值seed和increment的缺省值为1column_name是所插入的identity column的列名虽然AS column_name为可选项但由于函数在表中插入了一个新列所以必须指定列名如果不使用AS column_name选项可以使用以下两种方式来指定列名1 select identityint 11 as column_nameinto newtablefrom oldtable2 select column_name = identityint 1 1into newtablefrom oldtable有关identity column的信息请参见管理数据库表中的列属性章节13IDENT_INCRIDENT_INCR函数语法如下IDENT_INCR'table_or_view'IDENT_INCR函数返回表中标识性字段identity column的增量若无此字段则返回NULL值其返回值类型为NUMERIC例4-88use panguselect table_name ident_incrtable_name as ident_incrfrom information_schema.tableswhere ident_incrtable_name is not nullselect ident_incr'employee'运行结果如下table_name ident_incr-----------------------------------------------------------dtproperties 1----------------------------------------NULL14IDENT_SEEDIDENT_SEED函数语法如下IDENT_SEEDIDENT_SEED函数返回表中标识性字段identity column的初值若无此字段则返回NULL值其返回值类型为NUMERIC例4-89use panguselect table_name ident_seedtable_name as ident_seedfrom information_schema.tableswhere ident_seedtable_name is not nullselect ident_seed'employee'运行结果如下table_name ident_seed-------------------------------------------------------dtproperties 1----------------------------------------NULL15INDEX_COLINDEX_COL函数语法如下INDEX_COLINDEX_COL函数返回表内索引识别码为index_id的索引的名称并找出组成该索引的列组合中第key_id个列名其返回值类型为NCHAR例4-90use pangudeclare @table_id int @table_name char10set @table_name = 'employee'select @table_id = id --从系统对象表中选取employee表的编号from sysobjectswhere name = @table_nameprint 'Index information for the ' + @table_name + ' table'select index_col@table_name indid 1from sysindexeswhere indid inselect indid --从系统索引表中选取索引编号from sysindexeswhere id = @table_id运行结果如下Index information for the employee table--------------------------emp_ide_wagebirthdaydept_idhire_datejob_levele_name16ISDATEISDATE函数语法如下ISDATEISDATE函数判断所给定的表达式是否为合理日期如果是则返回1不是则返回0例4-91declare @x char12set @x = '1/1/2000'select isdate@x isdate'1999-10-1 12:00am' isdate'Jan 23 2000 21:00pm'isdate'20001226' isdate'2000101' isdate'16/23/99'运行结果如下----------- ----------- ----------- ----------- ----------- -----------1 1 1 1 0 017ISNULLISNULL函数语法如下ISNULLISNULL函数将表达式中的NULL值用指定值替换如果check_expresssion不是NULL则返回其原来的值否则返回replacement_value的值! replacement_value的数据类型应与check_expresssion一致例4-92use panguselect avgisnulle_wage $1000.00from employee运行结果如下---------------------3973.333318ISNUMERICISNUMERIC函数语法如下ISNUMERICISNUMERIC函数判断所给定的表达式是否为合理的数值INTEGERFLOATINGPOINT NUMBERMONEY或DECIMAL类型如果是则返回1不是则返回0! 检查MONEY型数据时应先用CONVERT函数将其转换为数值型或字符型例4-93declare @pay moneyset @pay = $100select isnumericconvertint10 @payuse panguselect isnumericbirthday isnumericconvertchar10 e_wagefrom employee运行结果如下-----------11 rows affected----------- -----------0 10 119NEWIDNEWID函数语法如下NEWIDNEWID函数返回一个UNIQUEIDENTIFIER类型的数值此函数使用计算机的网卡的Ethernet地址加上经由CPU Clock产生的数字而得到其返回值由于网卡的Ethernet地址是全球惟一的在未来近一百年内不会生产出相同Ethernet地址的网卡因而函数所产生的数字也是惟一的例4-94declare @xyz uniqueidentifierset @xyz = newidprint 'Value of @xyz is: '+ convertvarchar255 @xyz运行结果如下Value of @xyz is: 8953F4C7-5D4E-11D4-8F18-E9780FD95B7A20NULLIFNULLIF函数语法如下NULLIFNULLIF函数在expression1与expression2相等时返回NULL值若不相等时则返回expression1的值其返回值类型与expression1相同expression1与expression2应为同类的数据类型例4-95declare @x varchar20 @y char20 @z charselect @x = 'SQL' @y = 'SQL' @z = 1select nullif@x @y nullif@x @z运行结果如下-------------------- --------------------NULL SQL21OBJECT_IDOBJECT_ID函数语法如下OBJECT_IDOBJECT_ID函数返回数据库对象的编号其返回值类型为INT例4-96use panguselect object_id'department'运行结果如下-----------485576768! 当指定一个临时表的表名时其表名的前面必须加上临时数据库名tempdb如selectobject_id'tempdb..#mytemptable'22OBJECT_NAMEOBJECT_NAME函数语法如下OBJECT_NAMEOBJECT_NAME函数返回数据库对象的名称其返回值类型为NCHAR例4-97use panguselect table_catalog table_namefrom information_schema.tableswhere table_name = object_name485576768运行结果如下table_catalog table_name------------------------------------------------------------------PANGU department23PARSENAMEPARSENAME函数语法如下PARSENAMEPARSENAME函数返回一个数据库对象完整名称中的特定部分对象名称或数据库拥有者名称或数据库名称或服务器名称其返回值类型为NCHAR其中object_piece为INT类型其取值如下对象名称Object name数据库拥有者名称Owner name数据库名称Database name服务器名称Server name如果object_name或object_piece为NULL则返回NULL值例4-98use panguselect parsename'employee' 1 as 'object name'parsename' employee ' 2 as 'owner name'parsename' employee ' 3 as 'database name'parsename' employee ' 4 as 'server name'运行结果如下object name owner name database name server name------------------------------------------------------------------------------------------------employee NULL PANGU NULL24PERMISSIONSPERMISSIONS函数语法如下PERMISSIONS[object_id [ 'column_name']]PERMISSIONS函数用于获取当前用户对某一对象的存取权限或对某一命令的执行权限其返回值为一个32位的bitmap值其中低16位表示目前用户对象的存取权限设定值高16位表示用户是否可以开放此对象的权限给其它人如果不指定object_id则函数返回目前用户的命令执行权限的bitmap值使用column_name选项可以得到表的列权限各权限值见表4-10至4-12表4-10 命令执行权限的bitmap值Bit decBit hex命 令 权 限1 0x1CREATE DATABASE master database only2 0x2 CREATE TABLE4 0x4 CREATE PROCEDURE8 0x8 CREATE VIEW16 0x10 CREATE RULE32 0x20 CREATE DEFAULT64 0x40 BACKUP DATABASE128 0x80 BACKUP LOG256 0x100 Reserved表4-11 对象存取权限的bitmap值Bit decBit hex对 象 权 限1 0x1 SELECT ALL2 0x2 UPDATE ALL4 0x4 REFERENCES ALL8 0x8 INSERT16 0x10 DELETE32 0x20EXECUTE procedures only4096 0x1000SELECT ANY at least one column8192 0x2000 UPDATE ANY16384 0x4000 REFERENCES ANY表4-12 列对象存取权限的bitmap值Bit decBit hex列 权 限1 0x1 SELECT2 0x2 UPDATE4 0x4 REFERENCES有关PERMISSIONS函数使用方法我们将在以后的章节分别讲述25STATS_DATESTATS_DATE函数语法如下STATS_DATESTATS_DATE函数返回最新的索引统计日期其返回值类型为DATETIME例4-99use panguselect 'index name' = i.name 'statistics date' = stats_datei.id i.indidfrom sysobjects o sysindexes iwhere o.name = 'employee' and o.id = i.id运行结果如下index name statistics date------------------------------------------------------------------------------------------employee NULLPK_employee NULL_WA_Sys_e_wage_1B0907CE 2000-07-15 21:41:20.157_WA_Sys_birthday_1B0907CE 2000-07-16 11:29:59.990_WA_Sys_dept_id_1B0907CE 2000-07-16 21:19:40.203_WA_Sys_hire_date_1B0907CE 2000-07-16 21:41:38.470_WA_Sys_job_level_1B0907CE 2000-07-16 22:05:43.003_WA_Sys_e_name_1B0907CE 2000-07-16 22:08:17.153e_name 2000-07-19 09:25:06.890e_birthday 2000-07-19 09:25:32.180e_hire_date 2000-07-19 09:25:54.69711 rows affected26SUSER_SIDSUSER_ SID函数语法如下SUSER_SID['login_name']SUSER_SID函数根据用户登录名返回用户的SIDSecurity Identification Number安全帐户名号其返回值类型为INT如果不指定login_name则返回当前用户的SID号例4-100use panguselect suser_sid'sa'运行结果如下-----------------------------------0x0127SUSER_SNAMESUSER_SNAME函数语法如下SUSER_SNAME[server_user_sid]SUSER_SNAME函数根据SID号返回用户的登录名如果没有指定server_user_sid则返回当前用户的登录名其返回值类型为NCHAR例4-101select suser_sname0x01运行结果如下-----------------------------------sa28USER_IDUSER_ID函数语法如下USER_ID['user_name']USER_ID函数根据用户数据库的用户名返回用户的数据库ID号其返回值类型为INT如果没有指定user_name则返回当前用户的数据库ID号例4-102use panguselect user_id'dbo'运行结果如下------129USER_NAMEUSER_NAME函数语法如下USER_NAME[user_id]USER_NAME函数根据用户的数据库ID号返回用户的数据库用户名其返回值类型为NCHAR如果没有指定user_id则返回当前数据库的用户名例4-103use panguselect user_name运行结果如下------dbo