SQLSERVER2008 （T-SQL汇总二）

T-SQL 汇总之四 COUNT(字段)与COUNT(*)之间的区别复制代码 最后一个介绍的函数就是统计记录数量的COUNT，这个函数有一点特别，因为它的即可以像其他聚合函数一样使用字段名做参数，也可以使用星号“*”做为参数。我们执行下面的SQL： SELECT COUNT(*),COUNT(FNumber) FROM T_Employee 执行完毕我们就能在输出结果中看到下面的执行结果： 8 8 可以看到COUNT(*)、COUNT(FNumber)两种方式都能统计出记录的条数，据此为数不少的开发人员都认为COUNT(*)、COUNT(字段名)这两种使用方式是等价的。下面通过例子来说 明，为了看到两种使用方式的区别需要首先向表T_Employee 中插入一条新的数据，执行下面的SQL： INSERT INTO T_Employee(FNumber,FAge,FSalary) VALUES('IT002',27,2800) 需要注意的就是这句INSERT语句没有为FName 字段赋值，也就是说新插入的这条数据的FName 字段值为空，可以执行SELECT * FROM T_Employee 来查看表T_Employee 中的内容： FNumber FName FAge FSalary DEV001 Tom 25 8300.00 DEV002 Jerry 28 2300.80 HR001 Jane 23 2200.88 HR002 Tina 25 5200.36 IT001 Smith 28 3900.00 IT002 <NULL> 27 2800.00 SALES001 John 23 5000.00 SALES002 Kerry 28 6200.00 SALES003 Stone 22 1200.00 可以看到FNumber 为IT002 的行的FName字段是空值。接着执行下面的SQL： SELECT COUNT(*),COUNT(FNumber),COUNT(FName) FROM T_Employee 执行完毕我们就能在输出结果中看到下面的执行结果： 9 9 8 可以看到COUNT(*)、COUNT(FNumber)两个表达式的计算结果都是9，而COUNT(FName)的计算结果是8。也就反应出了两种使用方式的区别：COUNT(*)统计的是结果集的总条数，而 COUNT(FName)统计的则是除了结果集中FName 不为空值（也就是不等于NULL）的记录的总条数。 由于FNumber 为IT002 的行的FName 字段是空值，所以COUNT(FName)的计算结果是8。因此在使用聚合函数COUNT 的时候一定要区分两种使用方式的区别，以防止出现数据错误。 [[提示：如果查询出来的条件没有NULL值 ，那么俩种查询条件无任何区别。]]T-SQL 汇总之五 GOTO语句复制代码/** GOTO语句可以实现无条件跳转 语法格式 GOTO  lable 其中lable为要跳转到的语句标号 遇到GOTO语句时直接跳转到lable标签处继续执行,而goto之后的语句不再执行 **/ declare @result int=0 declare @sn int =0 loop_1:--定义标号 set @result=@result+@sn set @sn=@sn+1 if(@sn<=100) goto loop_1--如果小于100就跳转到loop_1标号处 print @result复制代码T-SQL 汇总之六 常用日期操作获取当前日期时间select getdate()结果：2012-07-12 06:20:58.670获取星期几复制代码----返回tableCREATE FUNCTION [dbo].[WeekDayName](    )RETURNS @WeekDayName TABLE([Id] INT,[WeekDayName] NVARCHAR(15))ASBEGIN        INSERT INTO @WeekDayName ([Id],[WeekDayName]) VALUES (0,'Saturday'),(1,'Sunday'),(2,'Monday'),(3,'Tuesday'),(4,'Wednesday'),(5,'Thursday'),(6,'Friday')    RETURNEND复制代码复制代码----返回名称CREATE FUNCTION [dbo].[WeekDayNameOfDate](  @Date DATETIME)RETURNS NVARCHAR(10)BEGINRETURN    CASE (DATEPART(dw, @Date) + @@DATEFIRST) % 7    WHEN 0 THEN 'Saturday'    WHEN 1 THEN 'Sunday'    WHEN 2 THEN 'Monday'    WHEN 3 THEN 'Tuesday'    WHEN 4 THEN 'Wednesday'    WHEN 5 THEN 'Thursday'    WHEN 6 THEN 'Friday'      ENDEND----调用表值函数 select * from dbo.WeekDayName()----调用标量值函数select dbo.WeekDayNameOfDate('2012-01-03')复制代码复制代码select getdate(),datename(weekday,getdate()),datepart(weekday,getdate())--datename 返回星期名称;--datepart 返回数值;--例子:select   case   datepart(dw,getdate()) when   1   then   '星期天 ' when   2   then   '星期一 ' when   3   then   '星期二 ' when   4   then   '星期三 ' when   5   then   '星期四 ' when   6   then   '星期五 ' when   7   then   '星期六 ' end复制代码复制代码View Code  select getdate() --结果2011-10-23 11:06:23.390 select datediff(yyyy,getdate(),'2011-01-01') select datediff(yyyy,getdate(),'2010-1-1') select datediff(yyyy,getdate(),'2012-1-1') --可用于判断数据库里面的数据是否属于同年 --如果为同一年则返回0 --前年日期返回负数 --明后年日期返回大于零的正整数 ------------------------------------ --一个月第一天的 Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) --本周的星期一 Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --一年的第一天 Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) --季度的第一天 Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) --当天的半夜 Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) --上个月的最后一天 Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) --去年的最后一天 Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) --本月的最后一天 Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) --本年的最后一天 Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) --本月的第一个星期一 select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)  ------------------------------------------- --建立一个表，其中的记录包含有当前的日期和时间，可以添加一个DATETIME型字段，指定其缺省值为函数GETDATE（）的返回值，就象这样： Create TABLE #site_log (username VARCHAR(40),useractivity VARCHAR(100),entrydate DATETIME DEFAULT GETDATE()) drop table #site_log --转换日期和时间 --函数GETDATE（）的返回值在显示时只显示到秒。实际上，SQL Sever内部时间可以精确到毫秒级（确切地说，可以精确到3.33毫秒） Select CONVERT(VARCHAR(30),GETDATE(),9) 在函数CONVERT（）中你可以使用许多种不同风格的日期和时间格式 日期和时间的类型： 类型值   标准        输出 0         Default    mon dd yyyy hh:miAM 1         USA        mm/dd/yy 2         ANSI    yy.mm.dd 3         British/French dd/mm/yy 4         German    dd.mm.yy 5         Italian    dd-mm-yy 6         -        dd mon yy 7         -        mon dd,yy 8         -        hh:mi:ss 9         Default    + milliseconds--mon dd yyyy    hh:mi:ss:mmmAM(or ) 10        USA        mm-dd-yy 11        JAPAN    yy/mm/dd 12        ISO        yymmdd 13        Europe    Default + milliseconds--dd mon yyyy hh:mi:ss:mmm(24h) 14        -        hh:mi:ss:mmm(24h) select convert(varchar(30),getdate(),106) select convert(varchar(30),getdate(),6) /**类型0,9,和13总是返回四位的年。     对其它类型，要显示世纪，把style值加上100。     类型13和14返回24小时时钟的时间。     类型0,7,和13返回的月份用三位字符表示（用Nov代表November）     对表中所列的每一种格式，你可以把类型值加上100来显示有世纪的年（例如，00年将显示为2000年）*/ --抽取日期和时间 --在许多情况下，也许只想得到日期和时间的一部分，而不是完整的日期和时间。为了抽取日期的特定部分，你可以使用函数DATEPARt() select datepart(dd,getdate()) select datepart(mm,getdate()) select datepart(yyyy,getdate()) select datepart(yy,getdate()) --函数DATEPART（）的参数是两个变量。第一个变量指定要抽取日期的哪一部分；第二个变量是实际的数据。在这个例子中，函数DATEPART（）抽取月份，因为mm代表月份。下面是这个Select 语句的输出结果： --函数DATEPART（）的返回值是一个整数 --日期的各部分及其简写 日期部分    简写    值 year        yy        1753--9999 quarter     qq        1--4 month        mm        1--12 day of year dy        1--366 day            dd        1--31 week        wk        1--53 weekday     dw        1--7(Sunday--Saturday) hour        hh        0--23 minute        mi        0--59 second        ss        0--59 milisecond    ms        0--999 --当你需要进行日期和时间的比较时，使用函数DATEPART（）返回整数是有用的。要以更易读的格式得到部分的日期和时间，你可以使用函数DATENAME（），如下例所示： Select site_name ‘Site Name’ DATENAME(mm，site_entrydate) ‘Month Posted’ FROM site_directory select datename(mm,getdate()) --函数DATENAME()和函数DATEPART()接收同样的参数 --它的返回值是一个字符串，而不是一个整数 --可以用函数DATENAME()来抽取一个星期中的某一天。下面的这个例子同时抽取一周中的某一天和日期中的月份： select datename(dw,getdate())+'-'+datename(mm,getdate()) as 'Day and Month Posted' --返回日期和时间范围 --当分析表中的数据时，也许希望取出某个特定时间的数据 --也许对特定的某一天中,比如说2000年12月25日――访问者在你站点上的活动感兴趣,要取出这种类型的数据,你也许会试图使用这样的Select语句 Select * FROM weblog Where entrydate="12/25/20000" --不要这样做。这个Select语句不会返回正确的记录 --它将只返回日期和时间是12/25/2000 12:00:00:000AM的记录,换句话说，只有刚好在午夜零点输入的记录才被返回。 --问题是SQL Sever将用完整的日期和时间代替部分日期和时间。 --例如，当你输入一个日期，但不输入时间时，SQL Sever将加上缺省的时间"00:00:00:000AM" --当你输入一个时间，但不输入日期时，SQL Sever将加上缺省的日期"Jan 1 1900"。 --要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。例如，下面的这个Select 语句将能返回正确的记录： create table #dt(da datetime,id int)--验证sql缺省日期时间 insert into #dt values('2011-10-10',1) insert into #dt values('09:10:10',2) select * from #dt drop table #dt Select * FROM weblog  Where entrydate>=”12/25/2000” AND entrydate<”12/26/2000” --这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于12/25/2000 00:00:00:000AM并小于12/26/2000 00:00:00:000AM的记录。换句话说，它将正确地返回2000年圣诞节这一天输入的每一条记录。 --另一种方法是，你可以使用LIKE来返回正确的记录。通过在日期表达式中包含通配符"%"，你可以匹配一个特定日期的所有时间。这里有一个例子： Select * FROM weblog Where entrydate LIKE ‘Dec 25 2000%’ --这个语句可以匹配正确的记录。因为通配符“％”代表了任何时间。 --使用这两种匹配日期和时间范围的函数，你可以选择某个月，某一天，某一年，某个小时，某一分钟，某一秒，甚至某一毫秒内输入的记录。但是，如果你使用 LIKE 来匹配秒或毫秒，你首先需要使用函数CONVERT（）把日期和时间转换为更精确的格式 --比较日期和时间 --最后，还有两个日期和时间函数对根据日期和时间取出记录是有用的。使用函数DATEADD（）和DATEDIFF（），你可以比较日期的早晚。例如，下面的Select语句将显示表中的每一条记录已经输入了多少个小时： select datediff(mm,getdate(),'2011-10-10') --函数DADEDIFF()的参数是三个变量。第个变量指定日期的某一部分。在这个例子中，是按小时对日期进行比较，在日期2000年11月1日和2000年11月30日的指定时间之间有689个小时。另外两个参数是要进行比较的时间。为了返回一个正数，较早的时间应该先给。 --函数DATEADD()把两个日期相加。当你需要计算截止日期这一类的数据时，这个函数是有用处的 select dateadd(mm,-1,getdate()); --函数DATEADD()的参数有三个变量。第一个变量代表日期的某一部分，这个例子用到了代表月份的mm。 --第二个变量指定了时间的间隔――在本例中是一个月。 --最后一个变量是一个日期 --注意： --使用函数DATEADD（）把一个日期加上一个月，它并不加上30天。这个函数只简单地把月份值加1。 --各种日期格式转换 --------------------------------------- Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM  Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06  Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16  Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06  Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06  Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06  Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06  Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06  Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46  Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM  Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06  Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16  Select CONVERT(varchar(100), GETDATE(), 12): 060516  Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937  Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967  Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47  Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157  Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM  Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16  Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47  Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250  Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM  Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006  Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16  Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006  Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006  Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006  Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006  Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006  Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49  Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM  Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006  Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16  Select CONVERT(varchar(100), GETDATE(), 112): 20060516  Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513  Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547  Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49  Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700  Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827:  Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AMT-SQL 汇总之七 系统表复制代码 MSSQL中各个系统表的作用  sysaltfiles    主数据库               保存数据库的文件 syscharsets    主数据库               字符集与排序顺序 sysconfigures  主数据库               配置选项 syscurconfigs  主数据库               当前配置选项 sysdatabases   主数据库               服务器中的数据库 syslanguages   主数据库               语言 syslogins      主数据库               登陆帐号信息 sysoledbusers  主数据库               链接服务器登陆信息 sysprocesses   主数据库               进程 sysremotelogins主数据库               远程登录帐号 syscolumns     每个数据库             列 sysconstrains  每个数据库             限制 sysfilegroups  每个数据库             文件组 sysfiles       每个数据库             文件 sysforeignkeys 每个数据库             外部关键字 sysindexs      每个数据库             索引 sysmenbers     每个数据库             角色成员 sysobjects     每个数据库             所有数据库对象 syspermissions 每个数据库             权限 systypes       每个数据库             用户定义数据类型 sysusers       每个数据库             用户 --用什么方法可以得到一个表中所有的列名。SQl语句。 select   列名=name   from   syscolumns   where   id=object_id(N'要查的表名')  use gpStrudy select   name="name"   from   syscolumns   where   id=object_id(N'bookTable')复制代码