SQL Server 常用sql操作語句－互聯網

/*創建表*/

CREATE TABLE [UserInfo](
    [UserID] [uniqueidentifier] not null,
    [UserName] [varchar](100) not null,
    [LoginNumber] [varchar](50) not null,
    [Password] [varchar](50) not null,
    [a] [decimal](18,2) null,
    [b] [decimal](18,2) null,
  [c] as ([a]*[b]),
  [d] as ([a]*[b]) PERSISTED   /*不設置PERSISTED代表該列是一個虛擬列，也就是這個列實際上是不存在的，只是每次要取這列的值時，sql會按照計算列的公式計算一次，再把結果返回給我們。這樣會存在一些問題，比如計算會消耗一定的時間，而且不能在該列上創建索引。設置PERSISTED后代表該列是實際存在的列*/
    );

/*刪除表*/
drop table [UserInfo];

/*添加主鍵約束*/
ALTER TABLE [UserInfo] ADD CONSTRAINT [PK_UserInfo] primary key([UserID]);

/*添加默認值約束*/
ALTER TABLE [UserInfo] ADD CONSTRAINT [DF_UserInfo_UserID] default (newid()) for [UserID];

/*添加字段*/
alter table [UserInfo] add [Sex] [varchar](10) null;

/*修改字段*/
alter table [UserInfo] alter column [Sex] [varchar](20) null;

/*刪除字段*/
alter table [UserInfo] drop column [Sex];

/*批量插入*/
insert into [UserInfo]([Username],[LoginNumber],[Password],[Sex] select [UserName],[LoginNumber],[Password],[Sex] from [UserInfo];

/*批量更新*/
update [UserInfo1] set [UserInfo1].[Password]=b.[Password],[UserInfo1].[Sex]=b.[Sex] from [UserInfo] b where [UserInfo1].[LoginNumber]=b.[LoginNumber];

/*創建臨時表*/
create table #TempTable(
    [LoginNumber] [varchar](50),
    [Password] [varchar](50)
    );

/*自增列*/
create table [TestTable](
[ID] [int] identity(1,1) not null
);

/* 獲取所有用戶名

* islogin='1'表示帳戶
* islogin='0'表示角色
* status='2'表示用戶帳戶
* status='0'表示系統帳戶
*/
select [name] from sysusers where status='2' and islogin='1'

/*獲取所有數據庫名*/
select [name] from master..sysdatabases order by [name]

/* 獲取所有表名
* xtype='U'表示所有用戶表
* xtype='S'表示所有系統表
*/
select [name] from 數據庫名..sysobjects where [xtype]='U' order by [name]

/*獲取所有字段名*/
select [name] from syscolumns where id=object_id('表名')

/*獲取數據庫所有類型*/

select [name] from systypes

/*獲取指定表中代表主鍵的字段名*/
select name from syscolumns a where exists(select 1 from sysindexkeys where id=a.id and colid=a.colid) and id=object_id('表名');

/*獲取表主鍵*/

select o.name as 表名,c.name as 字段名,k.colid as 字段序號,k.keyno as 索引順序,t.name as 類型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
join systypes t on c.xusertype=t.xusertype
where o.xtype = 'U' and o.name='要查詢的表名'
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)

order by o.name,k.colid

/*獲取表中字段名和類型*/

select c.name as 字段名,t.name as 類型
from sysindexes i
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id
join systypes t on c.xusertype=t.xusertype
where o.xtype = 'U' and o.name='要查詢的表名'
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)
order by o.name

/*獲取表中字段名、類型、默認值*/

select c.name as 字段名,t.name as 類型,e.text as 默認值
from sysindexes i
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id
join systypes t on c.xusertype=t.xusertype
left join syscomments e on e.id=c.cdefault
where o.xtype = 'U' and o.name='DBConfig'
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)
order by o.name
select * from syscomments

/*獲取表所有列*/
SELECT
    表名=case when a.colorder=1 then d.name else '' end,
    字段名=a.name,
    標識=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√' else '' end,
    主鍵=case when exists(SELECT 1 FROM sysobjects where xtype= 'PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
    類型=b.name,
    占用字節數=a.length,
    長度=COLUMNPROPERTY(a.id,a.name, 'PRECISION'),
    小數位數=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale'),0),
    允許空=case when a.isnullable=1 then '√'else '' end,
    默認值=isnull(e.text, ''),
    字段說明=isnull(g.[value], '')
FROM syscolumns a
    left join systypes b on a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id and d.xtype= 'U' and d.name <> 'dtproperties' and d.name = '要查詢的表名'
    left join syscomments e on a.cdefault=e.id
    left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g.name='MS_Description'
order by a.id,a.colorder

/*獲取表自增列名稱*/
select COLUMN_NAME as 標識
FROM INFORMATION_SCHEMA.columns
where TABLE_NAME='要查詢的表' and COLUMNPROPERTY(OBJECT_ID('要查詢的表'),COLUMN_NAME,'IsIdentity')=1

/*獲取表的字段默認值*/
select b.text as 字段默認值
from syscolumns a left join syscomments b on a.cdefault = b.id
where a.id = object_id('要查詢的表') and a.name = '字段'

/*按姓氏筆畫排序*/
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

/*獲取當前數據庫中的所有用戶表*/
select * from sysobjects where xtype='U' and category=0

/*查詢用戶創建的所有數據庫*/
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

/*按全文匹配方式查詢*/
字段名 LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'%[^a-zA-Z0-9]China'
OR 字段名 LIKE N'China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'China'

/*計算執行SQL語句查詢時間*/
declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

/*幾個高級查詢運算詞*/

A：UNION 運算符
UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。
B：EXCEPT 運算符
EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重復行。
C：INTERSECT 運算符
INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重復行。

/*記錄不存在就直接插入新記錄，記錄存在就更新*/

if exists (select 1 from [tb] where [name]='aa')
    begin
        update [tb] set [name]='bb' where [name]='aa'
    end
else
    begin
        insert into [tb]([name]) select 'aa'
end

/*判斷指定表中是否存在指定的列名*/

if(not exists(select * from syscolumns where name = 'IsUpdate' and id in 

(select id from sysobjects where id = object_id(N'[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1))) 

    alter table [TestTable] add [IsUpdate] [int];