sql知识归纳

/**------------------------------------------------------------------------
<1>显示加盟商利润最高信息,同时要求大于500的才显示(mymoney having mymoney>500)
-------------------------------------------------------------------------*/
select   Id,a.ShopDomain,ShopName,AddTime,a.mymoney   from   juser a,(select ShopDomain,max(mymoney) as mymoney  from juser group by  ShopDomain, mymoney having mymoney>500) b where  a.ShopDomain=b.ShopDomain  and a.mymoney=b.mymoney






/*------------------------------------------------------------
<2>显示加盟商利润最高信息
------------------------------------------------------------*/
select Id,ShopDomain,ShopName,AddTime,mymoney from juser where mymoney=(select  max(mymoney) from juser)




/*------------------------------------------------------------
<3>SQL语句快速查询存在数据表某个字段的所有存储过程
------------------------------------------------------------*/


SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容    
FROM syscomments sc    
INNER JOIN sysobjects obj ON sc.Id = obj.ID    
WHERE sc.TEXT LIKE '%' + 'Staff_ID' + '%'    
AND TYPE = 'P'   


/*------------------------------------------------------------
<4>表复制标的结构


------------------------------------------------------------*/
1.select * into  新表 from 原表 where 1<>1
2.SELECT TOP 0 * INTO MallShop.dbo.member2 FROM MallShop.dbo.member




/*------------------------------------------------------------
<5>备份数据库、恢复数据库
Backup Database 数据库名称 To Disk='D:/test.bak'
drop database 数据库名 --删除数据库的


------------------------------------------------------------*/




drop database XqtCompany 


Backup Database XqtCompany To Disk='D:/XqtCompany.bak'




use master
drop database XqtCompany
RESTORE DATABASE XqtCompany from disk='d:\XqtCompany.bak'






/*<6>----------------------------删除重复数据----------------------------------------*/
delete from   tuan where id   not  in (select min(id) from  tuan group   by   title) 
/*------------------------------------------------------------*/




/*<7>-------------------（所影响的行数为 4 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录
(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
/------------------------------------------------------------*/


select * from A
left join B 
on A.aID = B.bID


/*------------------------------------------------------------
（所影响的行数为 4 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
------------------------------------------------------------*/
select * from A
right join B 
on A.aID = B.bID
/*------------------------------------------------------------------------*/




/*------------------------------------------------------------
很明显,这里只显示出了 A.aID = B.bID的记录.
这说明inner join并不以谁为基础,它只显示符合条件的记录
------------------------------------------------------------*/


select * from A
inner join B 
on A.aID = B.bID
/*----------------------------------------------------------*/




/*<8>--------------sql sever数据库实例名:------------------*/
select @@servicename
/*------------------------------------------------------*/








/*<9>---------------查看硬盘分区:--------------------------*/
EXEC master..xp_fixeddrives
/*------------------------------------------------------*/








/*<10>---------------------------------查询用户创建的所有数据库:----------------------------------------*/


select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
/*-------------------------------------------------------------------------------------------------------*/






/*<11>------------------------------------------------------------
得到一个表中所有的列名。SQl语句。
select      列名=name      from      syscolumns      where      id=object_id(N'要查的表名')
------------------------------------------------------------*/
select      name=name      from      syscolumns      where      id=object_id(N'UDS_Staff')




/*<12>---------------------------------N到M条记录(要有主索引ID)Select Top M-N * From 表
 Where ID in (Select Top M ID From 表) Order by ID  Desc----------------------------
---------------N=8---------------------------------M=82--------*/






Select Top 80 * From [order] Where ID in (Select Top 82 ID From [order]) Order by ID  Desc
/*------------------------------------------------------------------*/






/*<13>-------------------批量导入----------------------------------*/
Bulk  insert  sale
      From  'e:\test.txt'
      With
      (   fieldterminator=',',


          rowterminator='\n'
      )
/*-------------------------------------------------------*/


/*<14>-----------------------------------------------SQL在单一表中查询具有重复字段值的记录
主要使用HAVING Count(*)>1方法来实现*------------------------------------------------*/
    SELECT juser.loginname, juser.id  
    FROM juser  
    WHERE (((juser.loginname) In (SELECT [loginname] FROM [juser] As Tmp GROUP BY [loginname] HAVING Count(*)>1 )))  
    ORDER BY juser.loginname;  
/*------------------------------------------------------------------------------------------*/




/*<15>----------------------启用 xp cmdshell---------------------------------*/
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
/--------------------------------------------------------------------------/




/*----------------数据库默认实例导出-------------------------*/


--sql2005数据库默认实例导出到excel
EXEC master..xp_cmdshell 'bcp NewMallShop.dbo.ad out c:\temp1.xls -c -U"sa" -P"sa"'


--sql2005数据库默认实例导出到txt
EXEC master..xp_cmdshell 'bcp NewMallShop.dbo.member out c:currency.txt -c -U"sa" -P"sa"' 


/-----------------------------------------------------------------------------/










/*<16>-----------------------------------------------删除表、存储过程、触发器、数据库--------------------------*/
drop table      Student
drop procedure  getnotnull
drop trigger    truStudent
drop database   truStudent
*------------------------------------------------------------------------------------------*/




/*<17>--------------------------------------------某个数据字段在数据库的所有表--------------------------*/
select a.[name]  [order] from sysobjects a,
(
      select [id],count(*) b from syscolumns
      where [name] ='juserid'
      group by [id]
)
b where a.[id]=b.[id]


/*内查询（查询三个表中均存在的ID记录，任何一个表中不存在的id将均被过滤掉*/
select *   from ([order] inner join ProductLeaveBook on ProductLeaveBook.juserid=[order].juserid )inner join 
tuan  on [order].juserid=tuan.juserid






/*外查询（left join ,列出左表中的全部ID，不管ProductLeaveBook、tuan、UserVisitLog表中有无该ID*/
select *   from (([order] left join ProductLeaveBook on [order].juserid=ProductLeaveBook.juserid ) left join 
tuan  on [order].juserid=tuan.juserid) left join UserVisitLog  on [order].juserid=UserVisitLog.juserid




/*------------------------------------------------------------------------------------------*/


/*<18>---------------------------------查找某个字段属于哪个表的SQL语句-------------------------*/
select [name] from sysobjects where id in(select id from syscolumns Where name='juserID') 
/*------------------------------------------------------------------------------------------*/




/*<19>---------------------------------某2个数据字段在数据库的所有表-------------------------*/
select a.[name] [order]from sysobjects a
left join
(
select [id],count(*) b from syscolumns where [name]
in('juserid','memberid') group by [id] having count(*)>1
) b
on a.[id]=b.[id]
where b.id is not null
/*------------------------------------------------------------------------------------------*/








/*<20>----------------------三张表：A (id,xxx) B(id,xxx) C(id,xxx)------------------------*/
/----------------------要根据相同的id显示三张表，做Left Join------------------------/




    SELECT XXX  
    FROM ((A LEFT JOIN B ON A.id = B.id)  
    LEFT JOIN C ON A.id = C.id)  
    WHERE B.id Is Not Null 
/--------------------------------------------------------------------------------------/




/----------------------------------以上总结时间：2014-09-21 16:05:44---------------------------------------------/


/*<21>-----------------------------------------------------------远程----------------------------------*/
--创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '112.175.66.9' 
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'wcgj2198', 'wcgj2198#@!~' 




--查询示例 
select * from [ITSV].[7277_mallshop].dbo.juser  where  userflag=0






--导入到本地数据库相应的表
--方法一
drop  table tuan
select * into tuan from [ITSV].[7277_mallshop].dbo.tuan 
Go 
select *  from  tuan




--方法二
truncate  table  juser
insert [7277_mallshop].dbo.tuan
select * from [ITSV].[7277_mallshop].dbo.tuan 
select *  from  tuan




--以后不再使用时删除链接服务器 
exec sp_dropserver  'ITSV ', 'droplogins ' 
/-------------------------------------------------------------------------------------------------------/














/*<22>---------------查看当前数据库中:--------------------------*/


select name from sysobjects where xtype='TR' --所有触发器
select name from sysobjects   where xtype='P' --所有存储过程
select *    from sysobjects s where s.type in ('P') ;
select name from sysobjects where xtype='V' --所有视图
select name from sysobjects where xtype='U' --所有表
select [name] from [sysdatabases] order by [name]   --查询所有数据库
/*------------------------------------------------------------------------*/


--<23>所有表的记录条数和所占空间大小
CREATE TABLE #_A_TableSpaceUsed( 
TableName SYSNAME,       --数据表名
Rows INT,                --现有的行数
Reserved VARCHAR(20),    --保留的空间总量
DataSize VARCHAR(20),    --数据所使用的空间量
IndexSize VARCHAR(20),   --索引所使用的空间量 
UnUsed VARCHAR(20))      --未用的空间量


EXEC sp_MSForEachTable 
@command1=N'insert into #_A_TableSpaceUsed exec sp_spaceused ''?'''


SELECT * FROM #_A_TableSpaceUsed ORDER BY Rows DESC
DROP TABLE #_A_TableSpaceUsed








/*<24>-----------------------------------查看数据库中所有表的条数  -------------------------*/
select  b.name as tablename ,  
        c.row_count as datacount  
from    sys.indexes a ,  
        sys.objects b ,  
        sys.dm_db_partition_stats c  
where   a.[object_id] = b.[object_id]  
        AND b.[object_id] = c.[object_id]  
        AND a.index_id = c.index_id  
        AND a.index_id < 2  
        AND b.is_ms_shipped = 0  
/*------------------------------------------------------------------------------------------*/


--查询有触发器的表
select name 表格名称 from sysobjects where xtype='U' 
AND id in(select parent_obj from sysobjects where xtype='TR')
--查看当前库中所有的触发器和与之相对应的表《包含视图》：
SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type='TR'
select a.name 数据表名,sysobjects.name as 触发器名,sysobjects.crdate as 创建时间 from sysobjects 
left join (select *from sysobjects where xtype='U')as a on sysobjects.parent_obj=a.id
where sysobjects.xtype='TR'




--显示触发器的定义:
--查询没有触发器的表
select name 表格名称 from sysobjects where xtype='U'  AND id NOT in(select parent_obj from sysobjects where xtype='TR')










--sp_helptext:显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。
 EXEC sp_helptext '触发器名'
 EXEC sp_helptext 'SP_SMS_UpdateOnlineInfo'




--查看存储过程的内容   
 EXEC sp_helptext '存储过程名称'
 EXEC sp_helptext 'sp_BBS_GetBulletin'
 select text from syscomments where id=object_id('触发器名称')
 select text from syscomments where id=object_id('SP_SMS_UpdateOnlineInfo')








select distinct object_name(id) from syscomments where id in
(select object_id from sys.objects where type ='P') and text like'%UDS_Staff%'




--重命名存储过程
sp_rename 'adclick1','adclick'


select *   from UDS_StaffSearch


--查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns where table_name = 'UDS_WorkAttendence_DaySetting'
select * from UDS_Class


--查看所有存储过程与函数
 exec sp_stored_procedures 






--DDL 数据定义语言
--DML 数据操作语言 
--查看表中的触发器类型:
--sp_helptrigger:返回对当前数据库的指定表定义的 DML 触发器的类型。sp_helptrigger 不能用于 DDL 触发器。
--示例:
 EXEC sp_helptrigger '表名'
 EXEC sp_helptrigger 'emp_job'
 EXEC sp_helptrigger 'juser'












/*<25>---------------查询某个表被哪些存储过程使用到 :--------------------------*/
select distinct object_name(id) from syscomments where id in
(select object_id from sys.objects where type ='P') and text like'%TableName%'
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%tablename%'






select distinct object_name(id) from syscomments where id in
(select object_id from sys.objects where type ='P') and text like'%insert into UDS_Staff%'




select distinct object_name(id) from syscomments where id in
(select object_id from sys.objects where type ='P') and text like'%update UDS_Staff%'








select distinct object_name(id) from syscomments where id in
(select object_id from sys.objects where type ='P') and text like'%delete from UDS_Staff%'


/*------------------------------------------------------------------------*/








/----------------------------------以上总结时间：2014-12-10 13:27:40---------------------------------------------/
















/*<26>---------------存储过程中如何用表名作为输入参数--------------------------*/
create procedure aProc
@tablename varchar(30)
as
declare @sqlstr  nvarchar(2000)
begin
set  @sqlstr=N'select count(*) as [表'+@tablename +'的总记录数为] from '+@tablename    --单引号边要加空格
execute sp_executesql  @sqlstr
end
--drop  procedure aProc
--调用  存储过程 表名
aProc   'dbo.UDS_Staff'


/*------------------------------------------------------------------------*/






/*<27>-----------------------------------------------------------
--标示符是SQL中的中括号[]
--SQL的存储过程、动态语句、临时表、游标的综合应用示例
-------------*/


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insertMdfalarmInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[insertMdfalarmInfo]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE PROCEDURE insertMdfalarmInfo
@alarmID      int,            -- 告警器ID
@monitorEquID     varchar(16)


AS
begin
   --drop table #table_tmp
   set @alarmID = 38
   create table #table_tmp 
   (
   [id] int
   )
   set @monitorEquID = 6
  
   declare @selectContainerIDsql NVARCHAR(130)
   set @selectContainerIDsql= 'select monitorSourceID from v_mdfAlarmPortInfo where monitorEquPort in (1,3,5) and monitorEquID = 6 group by monitorSourceID'
  
  
   insert into #table_tmp ([id]) EXECUTE sp_executesql @selectContainerIDsql
   declare countMonitorSourceID cursor for select id from #table_tmp
  
   open countMonitorSourceID
   declare @monitorSourceID int
   fetch next from countMonitorSourceID into @monitorSourceID
   while @@fetch_status = 0
    begin
     print @monitorSourceID
     fetch next from countMonitorSourceID into @monitorSourceID
    end
   close countMonitorSourceID
   drop table #table_tmp
   deallocate countMonitorSourceID
end 
GO
SET QUOTED_IDENTIFIER OFF --这些是 SQL-92 设置语句，使 SQL Server 2000/2005 遵从 SQL-92 规则。
--当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须符合所有 Transact-SQL 标识符规则
GO
SET ANSI_NULLS ON     --SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
--当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行
GO


/*------------------------------------------------------------------------*/










/*<28>------------------------------------------------------------------------*/
--Table有很多列，你需要写一个存储过程，那个Table的参数列表够你写的了。我们可以利用系统表生成这个列表，看T-SQ
select '@' + c.name, col_definition = 
case t.name 
when 'bigint' then 'bigint,' 
when 'int' then 'int,' 
when 'smallint' then 'smallint,' 
when 'tinyint' then 'tinyint,' 
when 'bit' then 'bit,' 
when 'decimal' then 'decimal,' 
when 'numeric' then 'numeric,' 
when 'money' then 'money,' 
when 'smallmoney' then 'smallmoney,' 
when 'float' then 'float,' 
when 'real' then 'real,' 
when 'datetime' then 'datetime,' 
when 'smalldatetime' then 'smalldatetime,' 
when 'sql_variant' then 'sql_variant,' 
when 'timestamp' then 'timestamp,' 
when 'uniqueidentifier' then 'uniqueidentifier,' 
when 'xml' then 'xml,' 
else t.name + '(' + cast(c.prec as varchar(50)) + '),' 
end 
from sysobjects s 
inner join sys.syscolumns c on s.id = c.id 
inner join sys.types t on t.user_type_id = c.xtype 
where s.xtype='U' 
--and s.name = 'tablename' 
and s.name = 'juser' 
/*------------------------------------------------------------------------*/














/*<29>------------------------------------------------------------------------*/


--加密存储过程、触发器
Create procedure getOperationUser WITH ENCRYPTION 
AS 
Select* FROM dbo.TableSpace
GO
drop  procedure  getOperationUser




--对象 'getOperationUser' 的文本已加密。
exec  sp_helptext getOperationUser






--查看某张依赖的存储过程
sp_depends   'UDS_Class'




--查看存储过程中都使用了哪些表
SELECT 
sproc_name = CASE  WHEN RANK_1 > 1 THEN '' ELSE sproc_name   END
,table_name = CASE WHEN RANK_2 > 1 THEN '' ELSE table_name END
,column_name
FROM
(
select sp.name       as sproc_name 
      ,t.name        as table_name 
      ,c.name        as column_name
      ,RANK_1 = ROW_NUMBER()OVER(ORDER BY (SELECT 1 ) )
      ,RANK_2 = ROW_NUMBER()OVER(PARTITION BY t.name ORDER BY sp.name )
 from sys.sql_dependencies d 
 join sys.objects t 
   on t.object_id = d.referenced_major_id 
 JOIN SYS.OBJECTS sp 
   on sp.object_id = d.object_id 
 join sys.columns c 
   on c.object_id = t.object_id 
  and c.column_id = d.referenced_minor_id 
where sp.type = 'P' 
AND sp.name = 'SP_AccountAdd')B
exec sp_helptext 'SP_AccountAdd'


/*------------------------------------------------------------------------*/








/*<30>------------------------------------------------------------------------*/


--SqlServer数据库字典--主键.外键.约束.视图.函数.存储过程.触发器.sql
SELECT DISTINCT 
 
TOP 100 PERCENT o.xtype, 
 
CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN 
 
'主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN' 
 
THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程' 
 
END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间, 
 
c.text AS 声明语句 
 
FROM dbo.sysobjects o LEFT OUTER JOIN 
 
dbo.syscomments c ON o.id = c.id 
 
WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND 
 
(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) 
 
ORDER BY CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 
 
'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' 
 
WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' 
 
ELSE '存储过程' END DESC
/*------------------------------------------------------------------------*/








/*<31>------------------------------------------------------------------------*/


--sql数据库全库查询的问题快速查询特定条件属于哪张表
--假设要查找的关键字是“学生”
declare @str varchar(100)
set @str='学校一楼仓库' 


declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb






--所在的表及字段: [Supplier].[Name]
--所在的表及字段: [OrderList].[company]
--所在的表及字段: [MobileStage].[Card]
--所在的表及字段: [MessageLog].[content]


/*------------------------------------------------------------------------*/






/*<32>------------------------------------------------------------------------*/
--SQL中如何精确判断某字段含有某个值
--select * from tablename where apply_type like '@a,%' or apply_type like '%,@a,%'  or apply_type like '%,@a,' ;


select * from juser where producttypeids like '5124,%' or producttypeids like '%,5124,%'  or producttypeids like '%,5124,' ;




--SQL 如何删除某列中某个特定值
--update 表
--set a1=replace(a1,'10002_','')
update juser
set producttypeids=replace(producttypeids,'5124,','')
/*------------------------------------------------------------------------*/






/*<33>------------------------------------------------------------------------*/
--SQL查询语句 group by后， 字符串合并
DECLARE @t TABLE(id int, value varchar(10))
 
INSERT @t SELECT 1, 'aa'
 
UNION ALL SELECT 1, 'bb'
 
UNION ALL SELECT 2, 'aaa'
 
UNION ALL SELECT 2, 'bbb'
 
UNION ALL SELECT 2, 'ccc'
 
  
 
-- 查询处理
 
SELECT *
 
FROM(
 
    SELECT DISTINCT 
 
        id
 
    FROM @t
 
)A
 
OUTER APPLY(
 
    SELECT 
 
        [values]= STUFF(REPLACE(REPLACE(
 
            (
 
                SELECT value FROM @t N
 
                WHERE id = A.id
 
                FOR XML AUTO
 
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
 
)N
 
  
 
/*--结果
 
id          values
 
----------- ----------------
 
1           aa,bb
 
2           aaa,bbb,ccc
 
  
 
(2 行受影响)
 
--*/






--单个
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
select  *  from   tb


select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') 
from tb 
group by id 




--三个
select
  BeginCity,EndCity,FanDian,
     [CangWei]=stuff((select '/'+[CangWei] from test1 t  
     where t.BeginCity=Test1.BeginCity  and t.EndCity=Test1.EndCity  and t.FanDian=Test1.FanDian   
     for xml path('')), 1, 1, '') 
from  
Test1 
group by 
BeginCity,EndCity,FanDian 
/*------------------------------------------------------------------------*/






/*<34>------------------------------------------------------------------------*/
---SQL通过日期计算年龄  
--BirthDay　　datetime not null
--
--Age　　　　通过公式计算得出
--
--=======================
--
--以上是表的两个字段，通过BirthDay字段的数据自动生成Age字段
select  *  ,(case when (datediff(year,[BirthDay],getdate()) <> 0) then (ltrim(datediff(year,[BirthDay],getdate())) + '岁') else (case when (datediff(month,[BirthDay],getdate()) <> 0) then (ltrim(datediff(month,[BirthDay],getdate())) + '月') else (case when (datediff(day,[BirthDay],getdate()) <> 0) then (ltrim(datediff(day,[BirthDay],getdate())) + '天') else '' end) end) end)  from  ad


--
--sqlserver 数据库里面金额类型为什么不建议用float，实例告诉你为什么不能。
DECLARE @price1 FLOAT;
SET @price1 = 1;
SET @price1 = ( @price1 * 7.1 ) / 7 - @price1 * 0.1
SELECT  @price1;
 
 
DECLARE @price2 DECIMAL;
SET @price2 = 1;
SET @price2 = ( @price2 * 7.1 ) / 7 - @price2 * 0.1
SELECT  @price2;


/*------------------------------------------------------------------------*/




/*<35>------------------------------2015-04-01 15:27:48----------------------------
如果另一个库中没有同名的表
select * into b数据库.dbo.a表 from a数据库.dbo.a表 where 条件
-------------*/
select * into [7277_mallshop].dbo.UserState from bbs.dbo.UserState 
/*------------------------------------------------------------------------*/




/*<35>------------------------------2015-04-05 09:08:40----------------------------


-------------*/
select name from syscolumns where id=object_id('tb_menu') --查询表名为tb_menu的所有列名
select count(name) from syscolumns where id=object_id('tb_menu') --查询表名为tb_menu的所有列名个数
/*------------------------------------------------------------------------*/










/*<35>------------------------------2015-04-05 15:24:27----------------------------


-------------*/






--怎么用sql修改一列的数据使其循环递增
update member set member.id = b.r
from member 
left join 
(
    select a.pk, case a.r % 6349 when 0 then 6349 else a.r % 6349 end as r
    from
    (
        select LoginTimes as pk,row_number() over(order by LoginTimes) as r
        from member
    ) a
) b on member.LoginTimes = b.pk






--为某一列数据数值加1
update member set LoginTimes=cast(LoginTimes as numeric(18,0))+1 where isnumeric(LoginTimes)=1









--更新数据库所有表中同一字段值，SQL语句怎么写
declare @aa varchar(200)
declare @total int ,@tot int ,@tablename char(20)--如果知道表中的个数就可以少定义一个变量
select @tatal=count(*) from tablelist
set tot=1
 while @tot<=@total  
begin
set @tablename =''
select @tablename=tablename from tablelist where num=@tot
set @aa='update'+QUOTENAME(@tablename)+'set LoginTimes=1000 where LoginTimes=163494'
exec(@aa)
set @tot=@tot+1  
end



/*------------------------------------------------------------------------*/