SQLServer数据库常用操作命令

实现SQL Server 自增标志列清零

dbcc checkident(tb_qx_resource, reseed, 0) 

数据库表复制：

select * into xngzf.dbo.tb_qx_action from Smlw.dbo.tb_qx_action

数据库表删除：

drop table tb_qx_department;

获取当前日期及格式

elect getdate()--获取完整日期 具体到毫秒         2012-02-15 11:41:24.903
select convert(varchar,getdate(),120) --具体到秒  2012-02-15 11:46:04
select convert(varchar,getdate(),121)             2012-02-15 11:46:43.810
select convert(nvarchar,getdate(),20)             2012-02-15 11:45:42
select convert(nvarchar,getdate(),21)             2012-02-15 11:47:37.340
select convert(nvarchar,getdate(),22)             02/15/12 11:48:01 AM
select convert(nvarchar,getdate(),23)             2012-02-15
select convert(nvarchar,getdate(),24)             11:48:42
select convert(nvarchar,getdate(),25)             2012-02-15 11:49:00.030
select convert(nvarchar,getdate(),100)            02 15 2012 11:51AM
select convert(nvarchar,getdate(),101)            02/15/2012
select convert(nvarchar,getdate(),102)            2012.02.15
select convert(nvarchar,getdate(),103)            15/02/2012
select convert(nvarchar,getdate(),104)            15.02.2012
select convert(nvarchar,getdate(),105)            15-02-2012
select convert(nvarchar,getdate(),106)            15 02 2012
select convert(nvarchar,getdate(),107)            02 15, 2012
select convert(varchar(10),getdate(),108) --时间  11:47:15
select convert(nvarchar,getdate(),109)            02 15 2012 11:54:16:250AM
select convert(nvarchar,getdate(),110)            02-15-2012
select convert(nvarchar,getdate(),111)            2012/02/15
select convert(nvarchar,getdate(),112)            20120215
select convert(nvarchar,getdate(),113)            15 02 2012 11:55:18:293
select convert(nvarchar,getdate(),114)            11:55:32:373

mssql 追加行号 row_number()的用法

  在使用查询结果的时候需要生成一个新的行号用来做统计，oracle 中有这个功能，mssql 用得很少不清楚有没有这项功能，

搜索了一下，网上的解决方法乱七八糟，有限竟然需要一张临时表，我汗，最后发现了一个比较好的解决方法，row_number()

select row_number() over(order by tablename.id) as row_number,* from tablename

   MSSQL分页模板

SELECT fo.htID,
       fo.htCode,
       fo.isSignHt,
       fo.isPayMonenty,
       fo.isToHouse,
       fo.isDisHt,
       fo.isDisRent,
       fo.htStatus,
       fo.tzsID,
       fo.bailMoney,
       fo.bailmonthMoneyPre,
       fo.bailmonthMoneyReal,
       fo.fyZujinMonth,
       fo.zuhuID,
       fo.zuhuName,
       fo.identification,
       fo.shoujiPhone,
       fo.fyID,
       fo.fyZldz,
       fo.fyHxCode,
       fo.fyJizuMj
FROM   (SELECT ht.htID,
               ht.htCode,
               ht.isSignHt,
               ht.isPayMonenty,
               ht.isToHouse,
               ht.isDisHt,
               ht.isDisRent,
               ht.htStatus,
               ht.lastTime,
               tzs.tzsID,
               child.bailMoney,
               child.bailmonthMoneyPre,
               child.bailmonthMoneyReal,
               child.fyZujinMonth,
               czf.zuhuID,
               czf.zuhuName,
               czf.identification,
               czf.shoujiPhone,
               fang.fyID,
               fang.fyZldz,
               fang.fyHxCode,
               fang.fyJizuMj,
               Row_number()
                 OVER (
                   ORDER BY ht.htID) AS RowNo
        FROM   tb_xngzf_basic_ht ht
               LEFT JOIN tb_xngzf_basic_chengzf czf
                      ON ht.chengzfID = czf.zuhuID
               LEFT JOIN tb_xngzf_basic_tzs tzs
                      ON tzs.tzsID = ht.tzsID
               LEFT JOIN tb_xngzf_basic_fang fang
                      ON ht.fyID = fang.fyID
               LEFT JOIN tb_xngzf_basic_ht_child child
                      ON ht.htID = child.htID
               LEFT JOIN tb_qx_user us
                      ON us.userID = ht.lastPerson
        WHERE  1 = 1
               AND fang.fyXqCode = '6'
               AND fang.fyDhCode = '16') AS fo
WHERE  RowNo >= 0
       AND RowNo <= 10
ORDER  BY fo.htID,
          fo.htCode,
          fo.lastTime DESC