SQL SERVER 常用 T-SQL 语句

工作中所遇到的常用SQL语句集合

---

--切换数据库
use c2
go

-- 检查是否存在表：存在就删除
if EXISTS (select * from dbo.SysObjects where id =OBJECT_ID(N'JOIN_BusinessArea') AND OBJECTPROPERTY(id,'IsTable')=1)
drop table 表名称
GO

--查询当前数据库表名
select [name] from sysObjects where xtype='U' and [name] <> 'dtproperties' order by[name]

--查询当前数据库所有用户视图
Select [name] From sysObjects Where xtype='V' And [name]<>'syssegments' And [name]<>'sysconstraints' Order By [name]

--获得指定表中所有的列和类型
Select
c.name As '列名',
t.name As '列类型'
From syscolumns c, systypes t, sysobjects o
Where c.xtype = t.xusertype
And c.id = o.id
And o.name='offline_Product ' --指定表名 base_dict
Order By c.colorder

--获取当前数据库指定列名在那些表中
Select
m.name AS '表名',
c.name As '列名',
t.name As '列类型'
From syscolumns c, systypes t, sysobjects o , ( select [name] from sysObjects where xtype='U' and [name] <> 'dtproperties' ) m
Where c.xtype = t.xusertype
And c.id = o.id
And o.name =m.name
And c.name='product_id'  --指定列名
Order By m.name


--获取当前数据库所有表列名和表类型
Select
m.name AS '表名',
c.name As '列名',
t.name As '列类型'
From syscolumns c, systypes t, sysobjects o , ( select [name] from sysObjects where xtype='U' and [name] <> 'dtproperties' ) m
Where c.xtype = t.xusertype
And c.id = o.id
And o.name =m.name
Order By m.name


--删除表内相同数据
with ops as (select ROW_NUMBER() over(PARTITION BY SpecID order by(select 0))as rn from OFFLINE_ProductSpec) 
delete from ops where rn >1 
go


--查看数据库连接
Select * from sys.dm_exec_connections --此命令可以看到有多少人在连
Select * from sys.dm_exec_sessions --此命令可以看到有多少会话，一个连接可以有多个会话
SELECT * FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = 'BusinessPlat'
select count(*) '连接数' from master.dbo.sysprocesses  where spid>50


-- distinct用于返回唯一不同的值。
-- 实例 select distinct Code,BankID from (select a.Code,b.BankID from BankName as a, sys_Bank as b where b.Bank =a.Name) as c order by c.BankID



-- 添加列alter table 表名 add   新增字段名    字段类型  默认值;
-- 示例：alter table tb  add   col1        int     0;



-- 修改行内容
CASE [列名]
WHEN '0' then '正常'
when '-1' then '已删除'
END AS [列名]

-- 拼接字符串
--STUFF('',1,1,'')
--综合示例(修改行内容 + 拼接字符串 )：
select t1.DetailPID,t1.Price,t1.SpecialPrice,
CASE t1.[Status]
WHEN '0' then '正常'
when '-1' then '已删除'
END AS [status]
,t1.PBarCode,STUFF(
    (
        select '   '+t3.Name+'： '+t2.SpecValue
        from OFFLINE_ProductDetail2Spec t2 
        left join OFFLINE_ProductSpec t3
        on t2.SpecID = t3.SpecID
        where t2.DetailPID = t1.DetailPID order by t2.SpecID
        FOR XML PATH('')
    ),1,1,''
) spec
from OFFLINE_ProductDetail as t1 inner join 
(
    select * from OFFLINE_Product where PID='P001607010000004' and BusinessAreaID='BA011603020002'
) as t0 on t1.PID = t0.PID