SQL 常用操作

[一篮饭特稀原创，转载请注明出处http://www.cnblogs.com/wanghafan/archive/2012/02/15/2352106.html]

--备份表

select * into F02_TF_RNFL_BACK from F02_TF_RNFL

--复制数据库

新建一个库B
B的右键-任务-导入数据，从A到B
A的右键-任务-生成脚本-...-勾选存储过程，导出到查询页
从查询页执行脚本到B

--分组 最新一条记录

select [EstimatedIncomeT].*  
   from [EstimatedIncomeT], 
        ( 
           select ProjectID,max(EstimatedDate) as EstimatedDate from [EstimatedIncomeT] group by ProjectID
        ) a 
 where [EstimatedIncomeT].EstimatedDate=a.EstimatedDate AND [EstimatedIncomeT].ProjectID=a.ProjectID

--计算两个记录之间某字段的差值 

SELECT a.*,(a.费用 - b.费用 ) as 差额费用
from tb a 
left join tb b
on a.IID=b.IID+1

--行号 

select CAST(ROW_NUMBER() OVER(order by Name) AS varchar(32)) AS rowindex

--联合查询 后 排序 

要求：
在一个语句中完成先按县市雨量值从大到小排，最后排全市雨量平均值

语句：
select stnm,val,0 as OrderID from M_RNFL_R
union 
select '全市' as stnm,avg(val),1 as OrderID from (select val from M_RNFL_R) as A
order by OrderID asc,val desc

结果：
stnm val
温岭 258.1 
玉环 256.2 
路桥 222.7 
黄岩 161.0 
椒江 139.1 
三门 123.7 
临海 97.6 
仙居 57.3 
天台 45.5 
全市 151.3

--截取时间 

select datepart(yyyy,getdate()) 
select datepart(mm,getdate()) 
select datepart(dd,getdate())
select datepart(hh,getdate())

--读取库中的所有表名  

SELECT 'select * from ' +  name from sysobjects where xtype='u'

--读取指定表的所有列名  

select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')

--更新字段里的空格 

update 表名 set 字段名=REPLACE(字段名,' ','')

--批量插入数据

全部字符串
select 'insert into M01_ST_RVFCCH_B (STCD,WRZ,GRZ) values('''+STCD+''','''+TFLZ+''','''+MFLZ+''')'
from INFO_LRFLZ_B
包含整型rec_id
select ' update txl_person set rec_id='+ cast(rec_id as varchar(20)) +' where pname='''+[name]+''' ' from A03_RECIPIENT

--获取日期部分 

更新获取的日期部分：实际上是在原有日期上加上相应天数
update M02_ST_RIVER_R0 set TM=dateadd( day, 10, tm ) where tm>='2008-11-29' and tm<'2008-11-30'
获取到小时
1.字符串：select convert(varchar(10),getdate(),020) + ' ' + datename(hour,getdate())+':00:00.000'
2.日期型：cast((convert(varchar(10),getdate(),020) + ' ' + datename(hour,getdate())+':00:00.000') as datetime)

--日期时间转字符串 

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(), 130): 18 ???? ?????? 1427 10:57:49:907AM

--用变量名作为表名的解决方案 

情景：
　　写procedure时要根据特定变量的值动态创建table（表名是全部或部分特定变量的值）。例如：
　　declare @tablename char(10)
　　set @tablename='test'
       想用变量@tablename的值作为表名创建一个新表，那么create table @tablename (test char(8))在SQL server 2k中执行就会遇到问题，
　　
解决：
　　//如果表不存在则创建
　　IF not EXISTS (SELECT name FROM sysobjects 　　 
　　WHERE name =@tablename AND type = 'U')
　　begin
　　set
　　@createtable='create table '+ @tablename+' (myname char(2))'
　　exec(@createtable)
　　end

--重命名列 

下例将表 customers 中的列 contact title 重命名为 title。 
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

--修改表结构 

ALTER TABLE W02_ZHLXR ADD town varchar(20) null
ALTER TABLE W02_ZHLXR ADD ScaleLevel varchar(50) null

--生成随机数 

0至N-1之间，如cast( floor(rand()*100) as int)就会生成0至99之间任一整数 
1至N之间，如cast(ceiling(rand() * 100) as int)就会生成1至100之间任一整数

--建表 

create table SC 
( 
sno char(6), 
cno char (8), 
constraint PK_SC primary key (sno,cno), 
constraint FK_s foreign key (sno) references S(sno), 
constraint FK_c foreign key (cno) references C(cno) 
)

--insert select 

错误：insert into w01_setup (stcd,isshow,isshowrep)
（SELECT distinct(stcd） FROM M01_ST_STBPRP_B WHERE sttp IN ('1', '3')，1，1）
正确：insert into w01_setup (stcd,isshow,isshowrep)
SELECT distinct(stcd),1,1 FROM M01_ST_STBPRP_B WHERE sttp IN ('1', '3')

--用visio 2003建立sql server反向工程 

<1>建立数据源
打开控制面板—>管理工具—>数据源 (ODBC)，或直接运行odbccp32.cpl命令。打开ODBC数据源管理器。添加一个系统数据源。选择Sql Server作为数据源的驱动程序，键入数据源名称demo，选择合适的sql server服务器，然后一路默认下去。（在后面的步骤中可以选择需要进行反向工程的数据库，默认是master）数据源信息如图一示。
<2>在visio中新建数据库模型图
打开visio 2003,文件—>新建—>数据库—>数据库模型图。建立数据库模型图之后，菜单栏多出一个菜单项"数据库"。
<3>反向工程
菜单项数据库—>反向工程。启动反向工程向导，一步步完成反向工程设置。
1.建立与数据库的连接。选择visio驱动程序的类型决定了可以连接的数据库类型及可用选项。数据源指定数据库位置及连接信息。这里我们选择visio驱动程序为Microsoft Sql Server,数据源为刚建立的demo。如图二示。
2.连接数据源。输入用户名和密码用于连接数据源。
3.选择要进行反向工程的对象类型，如图三示。
4.选择要进行反向工程的表和视图。
5.选择要进行么向工程的存储过程。
6.是否想让选择了反向工程的项添加到当前工程中。选择是才能生成图。
结果大致向下面这样。

--查找存储过程中出现过的文字 

在syscomments系统表里查，结合sysobjects系统表取得过程名
select b.name from 数据库名.dbo.syscomments a,数据库名.dbo.sysobjects b where a.id=b.id and b.xtype='p' and a.text like '%insert into%'
“数据库名”替换成你实际的数据库名，因为每个数据库里的存储过程都是单独保存在自己数据库的syscomments表内，所以要加上数据库名，这样就不用在切换数据库上下文了。另外加密的存储过程是查不出来的。