SQL Server常文法語句操作

標籤：des   blog   os   使用   io   ar   for   資料   art   

SQL Server語句操作  

--1、擷取表的主鍵欄位
SELECT name FROM SysColumns WHERE id=Object_Id(‘表名‘) and colid=(select top 1 colid from sysindexkeys where id=Object_Id(‘表名‘))

select A.COLUMN_NAME  
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A join
(select * from sysobjects where xtype=N‘PK‘ ) B
on object_id(A.CONSTRAINT_NAME)=B.id where a.table_name=‘表名‘

第一種方法只使用於表中只有一個主鍵，不適合與多欄位聯合主鍵。
第二種方法，兩中都適合。

--2、擷取表的欄位名、類型、長度
select dbo.sysobjects.name as Table_name, 
dbo.syscolumns.name as Column_name,
dbo.systypes.name as Type_name,
dbo.systypes.length as Type_length,
columnproperty(dbo.syscolumns.id,dbo.syscolumns.name,‘precision‘) as Column_lengh
from dbo.syscolumns inner join dbo.sysobjects 
on dbo.syscolumns.id = dbo.sysobjects.id
left join dbo.systypes
on dbo.syscolumns.xtype = dbo.systypes.xusertype
where dbo.sysobjects.name = ‘denglu‘ --表名
and (dbo.sysobjects.xtype = ‘u‘) 
and (not (dbo.sysobjects.name like ‘dtproperties‘))

--1、建立資料庫
create database DataBase_Name

--2、刪除資料庫
drop database DataBase_Name

--4、建立新表
create table Table_Name
(
    --建立列
    col1 type1 [not null] [primary key],
    col2 type2 [not null]
    
    --執行個體
    Name varchar(100) not null primary key,
    Age Int not null
)
--根據已有的表建立新表
 create table New_Table like Old_Table --使用舊錶建立新表
 create table New_Table as select col1,col2,col3 from Ole_Table definition only --definition only 就是在create table 的時候不同時進行資料插入。那個關鍵字就是指“只定義無資料"

--5、刪除新表
 drop table Table_Name
 
 --6、添加一個列
 Alter table Table_Name add column col type --col 為新增列名，type為類型
     --註：列增加後將不能刪除，DB2中列加上後資料類型也不能改變，唯一能改變的是增加是varchar的長度。
 
 --7、添加主鍵
  Alter table Table_Name add primary key(col) --col 為列名
  
 --8、刪除主鍵
  Alter table Table_Name drop primary key(col) --col為列名
 
 --9、建立索引
  create [unique] index idxname on TableName(col1,col2)
 
 --10、刪除索引
  drop index idxname --註：索引是不可更改的，想更改必須刪除新建立

 --11、建立視圖
  create view View_Name as select col1,col2,col3 from Table_Name

--12、刪除視圖
  drop view View_Name
  
 --13、幾個簡單的基本的Sql語句
      select * from Table_Name where 條件
      Insert into Table_Name(Field1,Field2) values(values1,values2)
      delete from Table_Name where 範圍
      update Table_Name set field1=values1,field2=values2 where 條件
      select * from Table_Name where field1 like ‘%values1%‘ --like 的文法很精妙,請查資料
      select * from Table_Name order by field1,field2 desc --desc為倒敘排列，asc為正序排列
      select count as ‘總數名‘ from Table_Name
      select sum(field1) as ‘總和‘ from Table_Name
      select avg(field2) as ‘平均值‘ from Table_Name
      select max(field3) as ‘最大值‘ from Table_Name
      select min(filed4) as ‘最小值‘ from Table_Name

----14、幾個進階查詢運算詞
A：union 運算子
 union運算子通過組合其他兩個結果表（例如 Table_Name1 和 Table_Name2）並消去表中任何重複行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 union all），不消除重複行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。

B:Except 運算子
Except 運算子通過包括所有在Table1中但不在
Table2中的行並清除所有重複行而派生出一個結果表，當All隨Except一起使用時(Except All),不清除重複行。

C：Intersect 運算子
Intersect 運算子通過只包括Table1和Table2中都有的行並清除所有重複行而派生出一個結果表，當all隨intersect一起使用時(Intersect all),不清除重複行
註：使用運算詞的幾個查詢結果行必須是一致的。

--15、使用外串連
A、left (outer) join:
左外串連(左串連)：結果集包括串連表的匹配行，也包括左串連表的所有行。

Sql：select a.a,a.b,b.a,b.b from a left out join b on a.a=b.b

B、right(outer)join:
右外串連(右串連)：結果集既包括串連表的匹配串連行，也包括右串連表的所有行。

C、full/cross (outer)join:
全外串連：不僅包括符號串連表的匹配行，還包括兩個串連表中得所有記錄。

--16、分組：group by
一張表,一旦分組完成後,查詢後只能得到組相關的資訊,
組相關的資訊:(統計資訊)count,sum,max,min,avg

分組的標準,在SQL Server中分組時：不能以text,ntext,image類型的欄位作為分組依據，
在select 統計函數中得欄位，不能喝普通的欄位放在一起；

--17、對資料庫進行操作：
分離資料庫：sp_detach_db DataBase_Name 路徑名
附加資料庫：sp_attach_db DataBase_Name 路徑名

--18、修改資料庫的名稱
sp_renamedb ‘Old_DatabaseName‘ ‘New_DatabaseName‘

 

--1、複製表(只複製結構，源表名:a,新表名:b)(Access可用)
方法一：select * into b from a where 1<>1 (僅用於SQLServer)
方法二：select top 0 * into b from a

--2、拷貝表(拷貝資料,源表名：a，新表名：b)(Access可用)
insert into b(a,b,c) select d,e,f from a;

--3、跨資料庫之間表的拷貝(具體資料使用絕對路徑)(Access可用)
insert into b(a,b,c) select d,e,f from a in ‘具體資料庫‘ where 條件

--4、子查詢(表名1：ta，表名2：tb)
select a,b,c from ta where a in (select d from tb)
select a,b,c from ta where a in (1,2,3)

--5、顯示文章、提交人和最後回複時間
select a.title,a.username,b.adddate from ta,(select max(adddate) adddate from tb where ta,title=tb.title) b

--6、外串連查詢(表名1：ta，表名2：tb)
select ta.a,ta.b,ta.c,tb.c,tb.f from ta left out join tb on ta.a=tb.c

--7、線上視圖查詢
select * from (select a,b,c from ta)T where T.a>1

--8、between的用法，between限制查詢資料範圍時包括了邊界值，not between不包括
select * from table1 where timea between time1 and time2
select a,b,c from table1 where a not between 數值1 and 數值2

--9、in的使用方法
select * from table1 where a [not] in(‘值1‘,‘值2‘,‘值3‘,‘值4‘)

--10、兩張關聯表，刪除主表中已經在副表中沒有的資訊
delete from table1 where not exists (select * from table2 where table1.field1=table2.field1)

--11、四表聯查問題
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ‘條件‘

--12、排程提前五分鐘提醒
select * from 排程 where datediff(‘minute‘，f 開始時間,getdate())>5

--13、一條sql語句搞定資料庫分頁
select top 10 b.* from (select top 20 主鍵欄位，排序欄位 from 表名 order by 排序欄位 desc)a,表名 b where b.主鍵欄位=a.主鍵欄位 order by a.排序欄位
具體實現：
關於資料庫分頁：
declare @start int,@end int
declare @sql nvarchar(600)

set @sql=‘select top‘+str(@[email protected]+1)+‘* from userinfo where userID not in (select top ‘+str(@start-1)+‘ userID from userinfo where userID>-1)‘
exec sp_executesql @sql

注意:在top後不能直接跟一個變數,所以在實際應用中只有這樣的進行特殊的處理.userID為一個識別欄位，如果top後還有具體的欄位，這樣做是非常有好處的，因為這樣可以避免top的欄位如果是邏輯索引的，查詢的結果後實際表中得不一致(邏輯索引中得資料有可能和資料表中得不一致，而查詢時如果處在索引則首先查詢索引)

--14、前10條記錄
select top 10 * from table1 where ‘條件‘

--15、選擇在每一組b值相同的資料中對應的a最大的記錄的所有資訊(類似這樣的用法可以用於論壇每月熱門排行榜，每月熱銷產品分析，按科目成績排名等等。)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

--16、包括所有在TableA 中但不在TableB和TableC中得行並消除所有重複行而派生一個結果行
(select a from TableA) except (select a from TableB) except (select a from TableC)

--17、隨機取出10條資料
select top 10 * from Table_Name order by newid()

--18、隨機播放記錄
select newid()

--19、重複資料刪除記錄
delete from Table_Name where id not in(select max(id) from Table_Name group by col1,col2)

select distinct * into temp from Table_Name 
delete from Table_Name
Insert into Table_Name select * from temp
--評價:這種操作牽連大量的資料的移動,這種做法不適合大容量的資料操作

--在一個外部表格中匯入資料,由於某些原因第一次只匯入了一部分,但很難判讀具體位置,這樣只有在下一次全部匯入,這樣也就產生好多重複的欄位,怎麼重複資料刪除欄位
alter table Table_Name
--添加一個自增列
add column_b int identity(1,1)

delete from Table_Name where column_b not in(select max(column_b) from Table_Name group by column1,column2)

--刪除遞增列
alter table Table_Name drop column column_b

--20、列出資料庫裡的所有表名
select name from sysobjects where type=‘sa‘ --sa為使用者

--21、列出表裡的所有列名
select name from syscolumns where id=object_id(‘Table_Name‘)

--22、列示type、vender、pcs欄位，以type欄位排列，case可以方便地實現多重選取，類似select中得case
select type,sum(case vender when ‘A‘then pcs else 0 end),sum(case vender when ‘c‘ then pcs else 0 end),sum(case vender when ‘B‘ then pcs else 0 end)from Table_Name group by type

--23、初始化表Table
truncate table Table_Name

--24、選擇從10到15的記錄
select top 5 * from (select top 15 * from Table_Name order by id asc)Table_new order by id desc

第三層 出神入化

--1、1=1 1=2的使用，在SQL語句組合時用得較多
where 1=1 是表示選擇全部，
where 1=2 是表示全部不選
如:
if @strWhere !=‘‘
begin
  set @strSQL=‘select count(*) as Total from [‘[email protected]+‘] where ‘[email protected] 
end
else
begin
  set @strSQL=‘select count(*) as Total from [‘[email protected]+‘]‘
end

--2、收縮資料庫
--重建索引
dbcc reindex
dbcc indexdefrag
--收縮資料和日誌
dbcc shrinkdb
dbcc shrinkfile

--3、壓縮資料庫
dbcc shrinkdatabase(DataBase_Name)

--4、轉移資料庫給新使用者已存在使用者權限
exec sp_change_users_login ‘update_one‘,‘newname‘,‘oldname‘
go

--5、檢查備份組
restore verifyonly from disk=‘路徑‘

--6、修複資料庫
alter database [dvbbs]set single_user
go
dbcc checkdb(‘dvbbs‘,repair_allow_data_loss)with Tablock
go
alter database [dvbbs]set multl_user
go

經典
--1、按姓氏筆畫排序
select * from Table_Name order by CustomerName Collate chinese_PRC_Stroke_ci_as --從少到多

--2、資料庫加密
select enctypt(‘原始密碼‘)
select pwdencrypt(‘原始密碼‘)
select pwdcompare(‘原始密碼‘,‘加密後密碼‘)=1--相同，否則不相同 encrype(‘原始密碼‘)

select pwdencrypt(‘原始密碼‘)
select pwdcompare(‘原始密碼‘,‘加密後密碼‘)=1--相同；否則不相同

--3、取回表中欄位
declare @list varchar(1000)
declare @sql nvarchar(1000)
select @[email protected]+‘,‘+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=‘表A‘
set @sql=‘select‘+right(@list,len(@list)-1)+‘from 表A‘
exec (@sql)

--4、查看硬碟分區
Exec master..xp_fixeddrives

--5、比較 A,B 表是否相等
if(select checksum_agg(binary_checksum(*))from 表A)
=(select checksum_agg(binary_checksum(*))from 表B)
print ‘相等‘
else
print ‘不相等‘

--6、殺掉所有的事件探查器進程：
declare hcforeach cursor global for select ‘kill‘+rtrim(spid) from master.dbo.sysprocesses
where program_name in(‘SQL profiler‘,‘SQL 事件探查器‘)

--7、記錄搜尋
--開頭到n條記錄
select top n * from 表

--N到M條記錄(要有主索引ID)
select top M-N * from 表 where ID in (select top M ID from 表)order by ID desc

--N到結尾記錄
select top N * from order by ID desc

案例
例如 1:一張表有一萬多條記錄，表的第一個欄位RecID是自增長欄位，寫一個SQL語句，找出表的第31到di40個記錄
select top 10 recid from A where recid not in(select top 30 recid from A)
分析：如果這樣寫會產生某些問題，如果recid在表中存在邏輯索引。
select top 10 recid from A where ...是從索引中尋找，而後面的 select top 30 recid from A 則在資料表中尋找，這樣由於索引中得順序有可能和資料表中得不一致，這樣就導致查詢到得不是本來的欲得到的資料.
解決方案：
 1、用 order by:
    select top 30 recid from A order by ricid 如果該欄位不是自增長，就會出現問題.
 2、在那個子查詢中也加條件：
    select top 30 recid from A where recid>-1

例如 2:查詢表中的最後幾條記錄,並不知道這個表共有多少資料以及表結構
set @s=‘select top 1 from T where pid not in(select top‘+str(@count-1)+‘ pid from T)‘
print @s 
exec sp_executesql @s

--8、擷取當前資料庫中得所有使用者表
select Name from sysobjects where type=‘u‘ and status>=0

select Name from sysobjects where xtype=‘u‘ and status>=0

--9、擷取某一個表的所有欄位
select name from syscolumns where id=object_id(‘表名‘)

select name from syscolumns where id in(select id from sysobjects where type=‘u‘ and name=‘表名‘)

--10、查看與某一個表相關的試圖、預存程序、函數
select a.* from sysobjects a,syscomments b where a.id=b.id and b.text like ‘%表名%‘

--11、查看當前資料庫中所有預存程序
select name as ‘預存程序名稱‘ from sysobjects where xtype=‘p‘

--12、查詢使用者建立的所有資料庫
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

--13、查詢某一個表的欄位和資料類型
select column_name,data_type from information_schema.columns
where table_name=‘userinfo‘ --userinfo 表名

--14、不同伺服器資料庫之間的資料操作
--建立串連伺服器

exec sp_addlinkedserver ‘ITSV‘,‘‘,‘SQLOLEDB‘,‘遠程伺服器名或IP地址‘

exec sp_addlinkedsrvlogin ‘ITSV‘,‘false‘,null,‘使用者名稱‘,‘密碼‘

--查詢樣本
select * from ITSV.資料庫名.dbo.表名

--匯入樣本
select * into 表 from ITSV.資料庫名.dbo.表名

--以後不再使用時刪除串連伺服器
exec sp_dropserver ‘ITSV‘,‘droplogins‘

--串連遠程/區域網路資料(openrowset/openquery/opendatasource)

-- 1、openrowset

--查詢樣本
select * from openrowset(‘SQLOLEDB‘,‘sql伺服器名‘;‘使用者名稱‘,‘密碼‘,‘資料庫名.dbo.表名‘)

--產生本地表
select * into 表 from openrowset( ‘SQLOLEDB ‘, ‘sql伺服器名 ‘; ‘使用者名稱 ‘; ‘密碼 ‘,資料庫名.dbo.表名)

--把本地表匯入遠端資料表
insert openrowset(‘SQLOLEDB‘,‘sql伺服器名‘;‘使用者名稱‘;‘密碼‘,資料庫名.dbo.表名) select * from 本地表

--更新本地表
update b set b.列 A=a.列 A from openrowset(‘SQLOLEDB‘,‘sql伺服器名‘;‘使用者名稱‘;‘密碼‘,資料庫名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1

-- 2 openquery 用法需要建立一個串連

--首先建立一個串連建立串連伺服器
exec sp_addlinkedserver ‘ITSV‘,‘‘,‘SQLOLEDB‘,‘遠程伺服器名或IP地址‘

--查詢
select * from openquery(ITSV,‘select * from 資料庫.dbo.表名‘)

--把本地表匯入遠端資料表
insert openquery(ITSV,‘select * from 資料庫.dbo.表名‘) select * from 本地表

--更新本地表
update b set b.列 B=a.列 B from(ITSV,‘select * from 資料庫.dbo.表名‘) as a inner join 本地表 b on a.列 A=b.列 A

-- 3 opendatasource/openrowset
select * from opendatasource(‘SQLOlEDB‘,‘Data Source=ip/serverName;User ID=登入名稱;password=密碼‘).text.dbo.roy_ta

--本地表匯入遠端資料表
insert opendatasource(‘SQLOLEDB‘,‘Data Source=ip/ServerName;User ID=登入名稱;Password=密碼‘).資料庫.dbo.表名 select * from 本地表

SQL Server常文法語句操作