sqlserver不太常见的，可能常见但又疑问的tsql语句

2013年10月29日16:01:58 当数据有 time类型列时候，比如 打电话的通话时长，我们查询时候不方便，我们可以添加一个冗余列，直接统计秒 ，但是 后期知道的，现在我把例如 00:12:23 的时间换成秒 例如列 bridgeDuration是时长，我们换成秒，存到这里bridgeDura

2013年10月29日16:01:58

当数据有 time类型列时候，比如 打电话的通话时长，我们查询时候不方便，我们可以添加一个冗余列，直接统计秒 ，但是 后期知道的，现在我把例如　00:12:23  的时间换成秒

例如列  bridgeDuration是时长，我们换成秒，存到这里bridgeDurationInt

UPDATE dbo.PhoneRecords SET bridgeDurationInt=(CAST(LEFT(bridgeDuration,2) AS int)*3600)+(CAST(RIGHT(LEFT(bridgeDuration,5),2) AS int)*60)+CAST(RIGHT(LEFT(bridgeDuration,8),2)  AS int)

 

 

2013年9月24日16:47:55

跨服务器查询，需要先添加服务器链接

下面的ip地址可以换掉
EXEC sp_droplinkedsrvlogin '192.168.10.150',NULL
EXEC sp_dropserver '192.168.10.150'

EXEC sp_addlinkedserver '192.168.10.150', ' ', 'SQLOLEDB', "10.50.1790.0"
EXEC sp_addlinkedsrvlogin '192.168.10.150', 'false',null, 'it', 'its123'

 

 

2013年9月16日9:58:40 增加

查看sql server中对象的数量:<br>表有多少个<br>select count(1) from sysobjects where xtype='U'<br>视图:<br>select count(1) from sysobjects where xtype='V'<br>存储过程<br>select count(1) from sysobjects where xtype='P'

 

我自己写了个小例子，做发布和订阅的时候，对比两个数据库中的表，同名字的给选择出来

<span>select</span> <span>*</span> <span>from</span> 数据库1.dbo.sysobjects <span>as</span><span> crm
</span><span>inner</span> <span>join</span> 数据库2.dbo.sysobjects <span>as</span><span> cpp
</span><span>on</span> crm.name <span>=</span><span> cpp.name
</span><span>where</span> crm.xtype<span>=</span><span>'</span><span>U</span><span>'</span>  <span>and</span> cpp.xtype<span>=</span><span>'</span><span>U</span><span>'</span> 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.给某张表增加列

例如

  <span>alter</span> <span>table</span><span> dbo.表名称
    </span><span>add</span> EmployeeName <span>nvarchar</span>(<span>50</span>) <span>default</span> <span>'</span><span>无</span><span>'</span> <span>not</span> <span>null</span><span>, 
        EmployeeId    </span><span>int</span> <span>default</span> <span>0</span> <span>not</span> <span>null</span>

 

 

2.复制出新表

SELECT * INTO 新表 FROM 旧表

 当然  新表==  库名.架构名.表名称，旧表也是

就可以实现 跨库复制表过来

 

3.查看某张表中所有的列名称

select Name from SysColumns  where id=Object_id('表名称') 

 

 

4. 求某两列的百分比，分母可能为0的情况

select conv=case when
字段=0 then 0
else
1/cast(字段 as numeric(5,2))
end
from 表 where 其他条件

 

 

 

5. 查出的结果，再添加一个自动增长列（排序列）

假如取个临时表的名字叫#tempT

select identity(int,1,1) as id,* into #tempT from (另一个select结果集，或者表名称)

 

删除临时表

if object_id('tempdb..#tempT') is not null
Begin
drop table #tempT
End

 

 

 

6.数据库角色  权限说明

db_owner 执行数据库中的所有维护和配置活动。 
db_accessadmin 添加或删除 Windows 用户、组和 SQL Server 登录的访问权限。 
db_datareader 读取所有用户表中的所有数据。 
db_datawriter 添加、删除或更改所有用户表中的数据。 
db_ddladmin 在数据库中运行任何数据定义语言 (DDL) 命令。 
db_securityadmin 修改角色成员身份并管理权限。 
db_backupoperator 备份数据库。 
db_denydatareader 无法读取数据库用户表中的任何数据。 
db_denydatawriter 无法添加、修改或删除任何用户表或视图中的数据。

 

7.修改 sa用户的密码

exec sp_password null,'000','sa' 

 

8.SQLServer  Update多条件多表关联更新

update Customer <span>set</span> employeeid=<span>0</span>,employeeName=<span>''</span> <span>where</span> Id <span>in</span>(<span>14775</span>,<span>14776</span>,<span>14778</span><span>)
</span><span>select</span> employeeid,employeeName <span>from</span> dbo.Customer  <span>as</span> c <span>where</span> c.Id <span>in</span>(<span>14775</span>,<span>14776</span>,<span>14778</span><span>)
update 
A 
</span><span>set</span> A.EmployeeId=B.EmployeeId,A.EmployeeName=B.EmployeeName <span>from</span><span> 
(SELECT </span>*<span>  FROM dbo.Customer ) A
inner join 
(SELECT </span>* FROM Contact <span>as</span> bb WHERE bb.CustomerId <span>in</span>(<span>14775</span>,<span>14776</span>,<span>14778</span><span>))B 
on A.Id</span>=<span>B.CustomerId

</span><span>select</span> employeeid,employeeName <span>from</span> dbo.Contact  <span>as</span> c <span>where</span> c.CustomerId <span>in</span>(<span>14775</span>,<span>14776</span>,<span>14778</span><span>)
</span><span>select</span> employeeid,employeeName <span>from</span> dbo.Customer  <span>as</span> c <span>where</span> c.Id <span>in</span>(<span>14775</span>,<span>14776</span>,<span>14778</span>)

 9.mysql写法

UPDATE `visitlegalizelog`,`visituser` SET visitlegalizelog.Tel = visituser.Tel WHERE visitlegalizelog.PhoneMac = visituser.PhoneMac