MS-SQL資料庫開發常用匯總 （收藏）

 MS-SQL資料庫開發常用匯總

1.按姓氏筆畫排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.資料庫加密:
select encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同 encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同
3.取回表中欄位:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+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',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
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
8.如何修改資料庫的名稱:
sp_renamedb 'old_name', 'new_name'
9：獲取當前資料庫中的所有使用者表
select Name from sysobjects where xtype='u' and status>=0
10：獲取某一個表的所有欄位
select name from syscolumns where id=object_id('表名')
11：檢視與某一個表相關的檢視、儲存過程、函式
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12：檢視當前資料庫中所有儲存過程
select name as 儲存過程名稱 from sysobjects where xtype='P'
13：查詢使用者建立的所有資料庫
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
14：查詢某一個表的欄位和資料型別
select column_name,data_type from information_schema.columns
where table_name = '表名'
[n].[標題]:
Select * From TableName Order By CustomerName
[n].[標題]:
來自http://dev.csdn.net/develop/article/83/83138.shtm

一、 只複製一個表結構，不復制資料

select top 0 * into [t1] from [t2]

二、 獲取資料庫中某個物件的建立指令碼

1、 先用下面的指令碼建立一個函式


if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
 drop function fgetscript
go

create function fgetscript(
 @servername varchar(50)     --伺服器名
 ,@userid varchar(50)='sa'    --使用者名稱,如果為nt驗證方式,則為空
 ,@password varchar(50)=''    --密碼
 ,@databasename varchar(50)    --資料庫名稱
 ,@objectname varchar(250)    --物件名

) returns varchar(8000)
as
begin
 declare @re varchar(8000)        --返回指令碼
 declare @srvid int,@dbsid int       --定義伺服器、資料庫集id
 declare @dbid int,@tbid int        --資料庫、表id
 declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變數

--建立sqldmo物件
 exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
 if @err<>0 goto lberr

--連線伺服器
 if isnull(@userid,'')='' --如果是 Nt驗證方式
 begin
  exec @err=sp_oasetproperty @srvid,'loginsecure',1
  if @err<>0 goto lberr

  exec @err=sp_oamethod @srvid,'connect',null,@servername
 end
 else
  exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password

 if @err<>0 goto lberr

--獲取資料庫集
 exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
 if @err<>0 goto lberr

--獲取要取得指令碼的資料庫id
 exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
 if @err<>0 goto lberr

--獲取要取得指令碼的物件id
 exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
 if @err<>0 goto lberr

--取得指令碼
 exec @err=sp_oamethod @tbid,'script',@re output
 if @err<>0 goto lberr

 --print @re
 return(@re)

lberr:
 exec sp_oageterrorinfo NULL, @src out, @desc out
 declare @errb varbinary(4)
 set @errb=cast(@err as varbinary(4))
 exec master..xp_varbintohexstr @errb,@re out
 set @re='錯誤號: '+@re
   +char(13)+'錯誤源: '+@src
   +char(13)+'錯誤描述: '+@desc
 return(@re)
end
go


2、 用法如下
用法如下,

print dbo.fgetscript('伺服器名','使用者名稱','密碼','資料庫名','表名或其它物件名')

3、 如果要獲取庫裡所有物件的指令碼，如如下方式


declare @name varchar(250)
declare #aa cursor for
 select name from sysobjects where xtype not in('S','PK','D','X','L')
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
 print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
 fetch next from #aa into @name
end
close #aa
deallocate #aa

4、 宣告，此函式是csdn鄒建鄒老大提供的
三、 分隔字串
如果有一個用逗號分割開的字串，比如說"a,b,c,d,1,2,3,4"，如何用t-sql獲取這個字串有幾個元素，獲取第幾個元素的值是多少呢？因為t-sql裡沒有split函式，也沒有陣列的概念，所以只能自己寫幾個函式了。
1、 獲取元素個數的函式


create function getstrarrlength (@str varchar(8000))
returns int
as
begin
  declare @int_return int
  declare @start int
  declare @next int
  declare @location int
  select @str =','+ @str +','
  select @str=replace(@str,',,',',')
  select @start =1
  select @next =1
  select @location = charindex(',',@str,@start)
  while (@location <>0)
  begin
    select @start = @location +1
    select @location = charindex(',',@str,@start)
    select @next =@next +1
  end
 select @int_return = @next-2
 return @int_return
end

2、 獲取指定索引的值的函式


create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
  declare @str_return varchar(8000)
  declare @start int
  declare @next int
  declare @location int
  select @start =1
  select @next =1 --如果習慣從0開始則select @next =0
  select @location = charindex(',',@str,@start)
  while (@location <>0 and @index > @next )
  begin
    select @start = @location +1
    select @location = charindex(',',@str,@start)
    select @next =@next +1
  end
  if @location =0 select @location =len(@str)+1 --如果是因為沒有逗號退出，則認為逗號在字串後
  select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗號之後的位置或者就是初始值1
  if (@index <> @next ) select @str_return = '' --如果二者不相等，則是因為逗號太少，或者@index小於@next的初始值1。
  return @str_return
end

3、 測試


SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)

四、 一條語句執行跨越若干個資料庫
我要在一條語句裡操作不同的伺服器上的不同的資料庫裡的不同的表,怎麼辦呢?
第一種方法:


select * from OPENDATASOURCE('SQLOLEDB','Data Source=遠端ip;User ID=sa;Password=密碼').庫名.dbo.表名

第二種方法:
先使用聯結伺服器:


EXEC sp_addlinkedserver '別名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=遠端名;UID=使用者;PWD=密碼;'
exec sp_addlinkedsrvlogin  @rmtsrvname='別名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密碼'
GO

然後你就可以如下：


select * from 別名.庫名.dbo.表名
insert 庫名.dbo.表名 select * from 別名.庫名.dbo.表名
select * into 庫名.dbo.新表名 from 別名.庫名.dbo.表名
go

五、 怎樣獲取一個表中所有的欄位資訊
蛙蛙推薦：怎樣獲取一個表中所有欄位的資訊
先建立一個檢視


Create view fielddesc    
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as

length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
from syscolumns c  
join systypes t on c.xtype = t.xusertype
join sysobjects o on o.id=c.id
left join    sysproperties p on p.smallid=c.colid and p.id=o.id    
where o.xtype='U'


查詢時:


Select * from fielddesc where table_name = '你的表名'
還有個更強的語句，是鄒建寫的，也寫出來吧


SELECT
 (case when a.colorder=1 then d.name else '' end) N'表名',
 a.colorder N'欄位序號',
 a.name N'欄位名',
 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'標識',
 (case when (SELECT count(*)
 FROM sysobjects
 WHERE (name in
           (SELECT name
          FROM sysindexes
          WHERE (id = a.id) AND (indid in
                    (SELECT indid
                   FROM sysindexkeys
                   WHERE (id = a.id) AND (colid in
                             (SELECT colid
                            FROM syscolumns
                            WHERE (id = a.id) AND (name = a.name))))))) AND
        (xtype = 'PK'))>0 then '√' else '' end) N'主鍵',
 b.name N'型別',
 a.length N'佔用位元組數',
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位數',
 (case when a.isnullable=1 then '√'else '' end) N'允許空',
 isnull(e.text,'') N'預設值',
 isnull(g.[value],'') AS N'欄位說明'
--into ##tx

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'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid  
order by object_name(a.id),a.colorder


六、 時間格式轉換問題
因為新開發的軟體需要用一些舊軟體生成的一些資料，在時間格式上不統一，只能手工轉換，研究了一下午寫了三條語句，以前沒怎麼用過convert函式和case語句，還有"+"操作符在不同上下文環境也會起到不同的作用，把我搞暈了要，不過現在看來是差不多弄好了。

1、把所有"70.07.06"這樣的值變成"1970-07-06"


UPDATE lvshi
SET shengri = '19' + REPLACE(shengri, '.', '-')
WHERE (zhiyezheng = '139770070153')
2、在"1970-07-06"裡提取"70","07","06"


SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,
      SUBSTRING(shengri, 9, 2) AS day
FROM lvshi
WHERE (zhiyezheng = '139770070153')

3、把一個時間型別欄位轉換成"1970-07-06"


UPDATE lvshi
SET shenling = CONVERT(varchar(4), YEAR(shenling))
      + '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2),
      month(shenling)) ELSE CONVERT(varchar(2), month(shenling))
      END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2),
      day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
WHERE (zhiyezheng = '139770070153')

七、 分割槽檢視
分割槽檢視是提高查詢效能的一個很好的辦法


--看下面的示例

--示例表
create table tempdb.dbo.t_10(
id int primary key check(id between 1 and 10),name varchar(10))

create table pubs.dbo.t_20(
id int primary key check(id between 11 and 20),name varchar(10))

create table northwind.dbo.t_30(
id int primary key check(id between 21 and 30),name varchar(10))
go

--分割槽檢視
create view v_t
as
select * from tempdb.dbo.t_10
union all
select * from pubs.dbo.t_20
union all
select * from northwind.dbo.t_30
go

--插入資料
insert v_t select 1 ,'aa'
union  all select 2 ,'bb'
union  all select 11,'cc'
union  all select 12,'dd'
union  all select 21,'ee'
union  all select 22,'ff'

--更新資料
update v_t set name=name+'_更新' where right(id,1)=1

--刪除測試
delete from v_t where right(id,1)=2

--顯示結果
select * from v_t
go

--刪除測試
drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
drop view v_t

/**//*--測試結果

id          name       
----------- ----------
1           aa_更新
11          cc_更新
21          ee_更新

（所影響的行數為 3 行）
==*/


八、 樹型的實現


--參考

--樹形資料查詢示例
--作者: 鄒建

--示例資料
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中國'
union  all  select 0,'美國'
union  all  select 0,'加拿大'
union  all  select 1,'北京'
union  all  select 1,'上海'
union  all  select 1,'江蘇'
union  all  select 6,'蘇州'
union  all  select 7,'常熟'
union  all  select 6,'南京'
union  all  select 6,'無錫'
union  all  select 2,'紐約'
union  all  select 2,'舊金山'
go

--查詢指定id的所有子
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
 declare @l int
 set @l=0
 insert @re select @id,@l
 while @@rowcount>0
 begin
  set @l=@l+1
  insert @re select a.[id],@l
  from [tb] a,@re b
  where a.[pid]=b.[id] and b.[level]=@l-1
 end
/**//**//**//*--如果只顯示最明細的子(下面沒有子),則加上這個刪除
 delete a from @re a
 where exists(
  select 1 from [tb] where [pid]=a.[id])
--*/
 return
end
go

--呼叫(查詢所有的子)
select a.*,層次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go

--刪除測試
drop table [tb]
drop function f_cid
go


九、 排序問題

CREATE TABLE [t] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

下面這句執行5次


insert t values (newid())

檢視執行結果


select * from t

1、 第一種


select * from t
 order by case id when 4 then 1
                  when 5 then 2
                  when 1 then 3
                  when 2 then 4
                  when 3 then 5 end

2、 第二種


select * from t order by (id+2)%6

3、 第三種


select * from t order by charindex(cast(id as varchar),'45123')

4、 第四種


select * from t
WHERE id between 0 and 5
order by charindex(cast(id as varchar),'45123')

5、 第五種


select * from t order by case when id >3 then id-5 else id end

6、 第六種


select * from t order by id / 4 desc,id asc
十、 一條語句刪除一批記錄
首先id列是int標識類型別，然後刪除ID值為5,6,8,9,10,11的列，這裡的cast函式不能用convert函式代替，而且轉換的型別必須是varchar，而不能是char，否則就會執行出你不希望的結果，這裡的"5,6,8,9,10,11"可以是你在頁面上獲取的一個chkboxlist構建成的值，然後用下面的一句就全部刪
除了，比迴圈用多條語句高效吧應該。


delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0

還有一種就是


delete from table1 where id in(1,2,3,4 )

十一、獲取子表內的一列資料的組合字串
下面這個函式獲取05年已經註冊了的某個所的律師，唯一一個引數就是事務所的名稱，然後返回zhuce欄位裡包含05字樣的所有律師。


CREATE   FUNCTION fn_Get05LvshiNameBySuo  (@p_suo Nvarchar(50))
RETURNS Nvarchar(2000)
AS
BEGIN  
 DECLARE @LvshiNames varchar(2000), @name varchar(50)
 select @LvshiNames=''
 DECLARE lvshi_cursor CURSOR FOR
資料庫裡有1,2,3,4,5 共5條記錄,要用一條sql語句讓其排序,使它排列成4,5,1,2,3,怎麼寫?
　　--資料操作

　　 SELECT --從資料庫表中檢索資料行和列
　　　　　　INSERT --向資料庫表新增新資料行
　　　　　　DELETE --從資料庫表中刪除資料行
　　　　　　UPDATE --更新資料庫表中的資料

　　--資料定義

　　 CREATE TABLE --建立一個資料庫表
　　　　　　DROP TABLE --從資料庫中刪除表
　　　　　　ALTER TABLE --修改資料庫表結構
　　　　　　CREATE VIEW --建立一個檢視
　　　　　　DROP VIEW --從資料庫中刪除檢視
　　　　　　CREATE INDEX --為資料庫表建立一個索引
　　　　　　DROP INDEX --從資料庫中刪除索引
　　　　　　CREATE PROCEDURE --建立一個儲存過程
　　　　　　DROP PROCEDURE --從資料庫中刪除儲存過程
　　　　　　CREATE TRIGGER --建立一個觸發器
　　　　　　DROP TRIGGER --從資料庫中刪除觸發器
　　　　　　CREATE SCHEMA --向資料庫新增一個新模式
　　　　　　DROP SCHEMA --從資料庫中刪除一個模式
　　　　　　CREATE DOMAIN --建立一個資料值域
　　　　　　ALTER DOMAIN --改變域定義
　　　　　　DROP DOMAIN --從資料庫中刪除一個域

　　--資料控制

　　 GRANT --授予使用者訪問許可權
　　　　　　DENY --拒絕使用者訪問
　　　　　　REVOKE --解除使用者訪問許可權

　　--事務控制

　　 COMMIT --結束當前事務
　　　　　　ROLLBACK --中止當前事務
　　　　　　SET TRANSACTION --定義當前事務資料訪問特徵

　　--程式化SQL

　　 DECLARE --為查詢設定遊標
　　　　　　EXPLAN --為查詢描述資料訪問計劃
　　　　　　OPEN --檢索查詢結果開啟一個遊標
　　　　　　FETCH --檢索一行查詢結果
　　　　　　CLOSE --關閉遊標
　　　　　　PREPARE --為動態執行準備SQL 語句
　　　　　　EXECUTE --動態地執行SQL 語句
　　　　　　DESCRIBE --描述準備好的查詢　　

　　---區域性變數

　　 declare @id char(10)
　　　　　　--set @id = '10010001'
　　　　　　select @id = '10010001'　　

　　---全域性變數

　　---必須以@@開頭　　

　　--IF ELSE

　　declare @x int @y int @z int
　　　　　　select @x = 1 @y = 2 @z=3
　　　　　　if @x > @y
　　　　　　 print 'x > y' --列印字串'x > y'
　　　　　　else if @y > @z
　　　　　　 print 'y > z'
　　　　　　else print 'z > y'
　　　　　　--CASE
　　　　　　use pangu
　　　　　　update employee
　　　　　　set e_wage =
　　　　　　 case
　　　　　　 when job_level = ’1’ then e_wage*1.08
　　　　　　 when job_level = ’2’ then e_wage*1.07
　　　　　　 when job_level = ’3’ then e_wage*1.06
　　　　　　 else e_wage*1.05
　　　　　　 end
　　　　　　--WHILE CONTINUE BREAK
　　　　　　declare @x int @y int @c int
　　　　　　select @x = 1 @y=1
　　　　　　while @x < 3
　　　　　　 begin
　　　　　　 print @x --列印變數x 的值
　　　　　　 while @y < 3
　　　　　　 begin
　　　　　　 select @c = 100*@x + @y
　　　　　　 print @c --列印變數c 的值
　　　　　　 select @y = @y + 1
　　　　　　 end
　　　　　　 select @x = @x + 1
　　　　　　 select @y = 1
　　　　　　 end
　　　　　　--WAITFOR

　　--例 等待1 小時2 分零3 秒後才執行SELECT 語句

　　waitfor delay ’01:02:03’
　　　　　　select * from employee

　　　　--例 等到晚上11 點零8 分後才執行SELECT 語句

　　　　waitfor time ’23:08:00’


SELECT　　

　　 select *(列名) from table_name(表名) where column_name operator value ex宿主)
　　　　 select * from stock_information where stockid = str(nid)
　　　　 stockname = 'str_name'
　　　　 stockname like '% find this %'
　　　　 stockname like '[a-zA-Z]%' --------- ([]指定值的範圍)
　　　　 stockname like '[^F-M]%' --------- (^排除指定範圍)
　　　　 --------- 只能在使用like關鍵字的where子句中使用萬用字元)
　　　　 or stockpath = 'stock_path'
　　　　 or stocknumber < 1000
　　　　 and stockindex = 24
　　　　 not stocksex = 'man'
　　　　 stocknumber between 20 and 100
　　　　 stocknumber in(10,20,30)
　　　　 order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
　　　　 order by 1,2 --------- by列號
　　　　 stockname = (select stockname from stock_information where stockid = 4)
　　　　 --------- 子查詢
　　　　 --------- 除非能確保內層select只返回一個行的值
　　　　 --------- 否則應在外層where子句中用一個in限定符
　　　　 select distinct column_name form table_name
　　 --------- distinct指定檢索獨有的列值，不重複
　　　　 select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
　　　　 select stockname , "stocknumber" = count(*) from table_name group by stockname
　　　　 --------- group by 將表按行分組,指定列中有相同的值
　　　　 having count(*) = 2 --------- having選定指定的組

　　 select *
　　　　 from table1, table2
where table1.id *= table2.id -------- 左外部連線，table1中有的而table2中沒有得以null表示
　　　　 table1.id =* table2.id -------- 右外部連線
　　　　 select stockname from table1
　　　　 union [all] -------- union合併查詢結果集，all-保留重複行
　　　　 select stockname from table2　

　　 insert　

　　 insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx"
　　 value (select Stockname , Stocknumber from Stock_table2)
　　 -------value為select語句　　

　　 update　　

　　 update table_name set Stockname = "xxx" [where Stockid = 3]
　　 Stockname = default
　　 Stockname = null
　　 Stocknumber = Stockname + 4　　

　　 delete　　

　　 delete from table_name where Stockid = 3
　　 truncate table_name --------- 刪除表中所有行，仍保持表的完整性
　　 drop table table_name --------- 完全刪除表　

　　 alter table -------- 修改資料庫表結構　　

　　 alter table database.owner.table_name add column_name char(2) null ..
　　 sp_help table_name -------- 顯示錶已有特徵
　　 create table table_name (name char(20), age smallint, lname varchar(30))
　　 insert into table_name select -------- 實現刪除列的方法（建立新表）
　　 alter table table_name drop constraint Stockname_default
　　 --------- 刪除Stockname的default約束




常用函式(function)　　

    轉換函式

    convert(資料型別,值,格式)

　　統計函式

　　AVG --求平均值
　　COUNT --統計數目
　　MAX --求最大值
　　MIN --求最小值
　　SUM --求和　

　　AVG

　　use pangu
　　select avg(e_wage) as dept_avgWage
　　from employee
　　group by dept_id　　

　　MAX

　　--求工資最高的員工姓名
　　use pangu
　　select e_name
　　from employee
　　where e_wage =
　　 (select max(e_wage)
　　 from employee)　　

　　STDEV()

　　--STDEV()函式返回表示式中所有資料的標準差
　　--STDEVP()
　　--STDEVP()函式返回總體標準差　　

　　VAR()

　　--VAR()函式返回表示式中所有值的統計變異數　　

　　VARP()

　　--VARP()函式返回總體變異數　　

　　算術函式　　

　　三角函式

　　SIN(float_expression) --返回以弧度表示的角的正弦
　　COS(float_expression) --返回以弧度表示的角的餘弦
　　TAN(float_expression) --返回以弧度表示的角的正切
　　COT(float_expression) --返回以弧度表示的角的餘切

　　反三角函式

　　ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
　　ACOS(float_expression) --返回餘弦是FLOAT 值的以弧度表示的角
　　ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
　　ATAN2(float_expression1,float_expression2)
　　 ------返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
　　DEGREES(numeric_expression)
　　 ------把弧度轉換為角度返回與表示式相同的資料型別可為
　　 ------INTEGER/MONEY/REAL/FLOAT 型別
　　RADIANS(numeric_expression)
------把角度轉換為弧度返回與表示式相同的資料型別可為

　　 ------INTEGER/MONEY/REAL/FLOAT 型別
　　EXP(float_expression) --返回表示式的指數值
　　LOG(float_expression) --返回表示式的自然對數值
　　LOG10(float_expression)--返回表示式的以10 為底的對數值
　　SQRT(float_expression) --返回表示式的平方根

　　取近似值函式

　　CEILING(numeric_expression)
-------返回>=表示式的最小整數返回的資料型別與表示式相同可為
　　 -------INTEGER/MONEY/REAL/FLOAT 型別
　　FLOOR(numeric_expression)
-------返回<=表示式的最小整數返回的資料型別與表示式相同可為
　　 -------INTEGER/MONEY/REAL/FLOAT 型別
　　ROUND(numeric_expression)
-------返回以integer_expression 為精度的四捨五入值返回的資料
　　 -------型別與表示式相同可為INTEGER/MONEY/REAL/FLOAT 型別
　　ABS(numeric_expression)
-------返回表示式的絕對值返回的資料型別與表示式相同可為
　　 -------INTEGER/MONEY/REAL/FLOAT 型別
　　SIGN(numeric_expression)
-------測試引數的正負號返回0 零值1 正數或-1 負數返回的資料型別
　　 -------與表示式相同可為INTEGER/MONEY/REAL/FLOAT 型別
　　PI() -------返回值為π 即3.1415926535897936
　　RAND([integer_expression])
-------用任選的[integer_expression]做種子值得出0-1 間的隨機浮點數


字串函式

　　ASCII() ------函式返回字元表示式最左端字元的ASCII 碼值
　　CHAR() ------函式用於將ASCII 碼轉換為字元
　　 ------如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函式會返回一個NULL 值
　　LOWER() ------函式把字串全部轉換為小寫
　　UPPER() ------函式把字串全部轉換為大寫
　　STR() ------函式把數值型資料轉換為字元型資料
　　LTRIM() ------函式把字串頭部的空格去掉
　　RTRIM() ------函式把字串尾部的空格去掉
　　LEFT(),RIGHT(),SUBSTRING() --函式返回部分字串
　　CHARINDEX(),PATINDEX() --函式返回字串中某個指定的子串出現的開始位置
　　SOUNDEX() ------函式返回一個四位字元碼
　　 ------SOUNDEX函式可用來查詢聲音相似的字串但SOUNDEX函式對數字和漢字均只返回0 值
　　DIFFERENCE() ------函式返回由SOUNDEX 函式返回的兩個字元表示式的值的差異
　　 ------0 兩個SOUNDEX 函式返回值的第一個字元不同
　　 ------1 兩個SOUNDEX 函式返回值的第一個字元相同
　　 ------2 兩個SOUNDEX 函式返回值的第一二個字元相同
　　 ------3 兩個SOUNDEX 函式返回值的第一二三個字元相同
　　 ------4 兩個SOUNDEX 函式返回值完全相同同
　　QUOTENAME() ------函式返回被特定字元括起來的字串

　　/**//**//**//*select quotename('abc', '{') quotename('abc')
　　執行結果如下

　　{
　　{abc} [abc]*/
　　REPLICATE() ------函式返回一個重複character_expression 指定次數的字串
　　/**//**//**//*select replicate('abc', 3) replicate( 'abc', -2)

　　執行結果如下

　　abcabcabc NULL*/
　　REVERSE() ------函式將指定的字串的字元排列順序顛倒
　　REPLACE() ------函式返回被替換了指定子串的字串
　　/**//**//**//*select replace('abc123g', '123', 'def')

　　執行結果如下
　　
　　abcdefg*/　　

　　SPACE() ------函式返回一個有指定長度的空白字串
　　STUFF() ------函式用另一子串替換字串指定位置長度的子串　　

　　資料型別轉換函式

　　CAST() 函式語法如下
　　CAST() ( AS [ length ])
　　CONVERT() 函式語法如下
　　CONVERT() ([ length ], [, style])
　　select cast(100+99 as char) convert(varchar(12), getdate())

　　執行結果如下
　　199 Jan 15 2000　

　　日期函式

　　DAY() ------函式返回date_expression 中的日期值
　　MONTH() ------函式返回date_expression 中的月份值
　　YEAR() ------函式返回date_expression 中的年份值
　　DATEADD( , ,)
　　 -----函式返回指定日期date 加上指定的額外日期間隔number 產生的新日期
　　DATEDIFF( , ,)
　　 -----函式返回兩個指定日期在datepart 方面的不同之處

　　DATENAME( ,  ------函式以字串的形式返回日期的指定部分
　　DATEPART( ,  ------函式以整數值的形式返回日期的指定部分
　　GETDATE() ------函式以DATETIME 的預設格式返回系統當前的日期和時間　　

　　系統函式

　　APP_NAME() ------函式返回當前執行的應用程式的名稱
　　COALESCE() -----函式返回眾多表示式中第一個非NULL 表示式的值
　　COL_LENGTH(<'table_name'>, <'column_name'> ----函式返回表中指定欄位的長度值
　　COL_NAME(,  ----函式返回表中指定欄位的名稱即列名
　　DATALENGTH() -----函式返回資料表示式的資料的實際長度
　　DB_ID(['database_name']) ------函式返回資料庫的編號
　　DB_NAME(database_id) ------函式返回資料庫的名稱
　　HOST_ID() -----函式返回伺服器端計算機的名稱
　　HOST_NAME() -----函式返回伺服器端計算機的名稱
　　IDENTITY([, seed increment]) [AS column_name])
　　 --IDENTITY() 函式只在SELECT INTO 語句中使用用於插入一個identity column列到新表中
　　/**//**//**//*select identity(int, 1, 1) as column_name
　　 into newtable
　　 from oldtable*/

　　ISDATE() ----函式判斷所給定的表示式是否為合理日期
　　ISNULL(,  --函式將表示式中的NULL 值用指定值替換
　　ISNUMERIC() ----函式判斷所給定的表示式是否為合理的數值
　　NEWID() ----函式返回一個UNIQUEIDENTIFIER 型別的數值
　　NULLIF(,  
　　 ----NULLIF 函式在expression1 與expression2 相等時返回NULL 值若不相等時則返回xpression1 的值 *******************Transact_SQL********************

--語 句                                功 能
--資料操作
SELECT      --從資料庫表中檢索資料行和列
INSERT      --向資料庫表新增新資料行
DELETE      --從資料庫表中刪除資料行
UPDATE      --更新資料庫表中的資料
--資料定義
CREATE TABLE    --建立一個資料庫表
DROP TABLE     --從資料庫中刪除表
ALTER TABLE     --修改資料庫表結構
CREATE VIEW     --建立一個檢視
DROP VIEW     --從資料庫中刪除檢視
CREATE INDEX    --為資料庫表建立一個索引
DROP INDEX     --從資料庫中刪除索引
CREATE PROCEDURE   --建立一個儲存過程
DROP PROCEDURE    --從資料庫中刪除儲存過程
CREATE TRIGGER    --建立一個觸發器
DROP TRIGGER    --從資料庫中刪除觸發器
CREATE SCHEMA    --向資料庫新增一個新模式
DROP SCHEMA     --從資料庫中刪除一個模式
CREATE DOMAIN    --建立一個資料值域
ALTER DOMAIN    --改變域定義
DROP DOMAIN     --從資料庫中刪除一個域
--資料控制
GRANT      --授予使用者訪問許可權
DENY      --拒絕使用者訪問
REVOKE      --解除使用者訪問許可權
--事務控制
COMMIT      --結束當前事務
ROLLBACK     --中止當前事務
SET TRANSACTION    --定義當前事務資料訪問特徵
--程式化SQL
DECLARE      --為查詢設定遊標
EXPLAN      --為查詢描述資料訪問計劃
OPEN      --檢索查詢結果開啟一個遊標
FETCH      --檢索一行查詢結果
CLOSE      --關閉遊標
PREPARE      --為動態執行準備SQL 語句
EXECUTE      --動態地執行SQL 語句
DESCRIBE     --描述準備好的查詢

---區域性變數
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'

---全域性變數
---必須以@@開頭

--IF ELSE
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
 print 'x > y' --列印字串'x > y'
else if @y > @z
 print 'y > z'
else print 'z > y'

--CASE
use pangu
update employee
set e_wage =
 case
  when job_level = ’1’ then e_wage*1.08
  when job_level = ’2’ then e_wage*1.07
  when job_level = ’3’ then e_wage*1.06
  else e_wage*1.05
 end

--WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
 begin
  print @x --列印變數x 的值
  while @y < 3
   begin
    select @c = 100*@x + @y
    print @c --列印變數c 的值
    select @y = @y + 1
   end
  select @x = @x + 1
  select @y = 1
 end

--WAITFOR
--例 等待1 小時2 分零3 秒後才執行SELECT 語句
waitfor delay ’01:02:03’
select * from employee
--例 等到晚上11 點零8 分後才執行SELECT 語句
waitfor time ’23:08:00’
select * from employee

 

***SELECT***

   select *(列名) from table_name(表名) where column_name operator value
   ex:(宿主)
  select * from stock_information where stockid   = str(nid)
     stockname = 'str_name'
     stockname like '% find this %'
     stockname like '[a-zA-Z]%' --------- ([]指定值的範圍)
     stockname like '[^F-M]%'   --------- (^排除指定範圍)
     --------- 只能在使用like關鍵字的where子句中使用萬用字元)
     or stockpath = 'stock_path'
     or stocknumber < 1000
     and stockindex = 24
     not stocksex = 'man'
     stocknumber between 20 and 100
     stocknumber in(10,20,30)
     order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
     order by 1,2 --------- by列號
     stockname = (select stockname from stock_information  where stockid  = 4)
     --------- 子查詢
     --------- 除非能確保內層select只返回一個行的值，
     --------- 否則應在外層where子句中用一個in限定符
  select distinct column_name form table_name --------- distinct指定檢索獨有的列值，不重複
  select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
  select stockname , "stocknumber" = count(*) from table_name group by stockname
                                      --------- group by 將表按行分組,指定列中有相同的值
          having count(*) = 2  ---------  having選定指定的組
        
  select *
  from table1, table2                  
  where table1.id *= table2.id -------- 左外部連線，table1中有的而table2中沒有得以null表示
     table1.id =* table2.id -------- 右外部連線

  select stockname from table1
  union [all]  -----  union合併查詢結果集，all-保留重複行
  select stockname from table2

***insert***

  insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
              value (select Stockname , Stocknumber from Stock_table2)---value為select語句

***update***

  update table_name set Stockname = "xxx" [where Stockid = 3]
         Stockname = default
         Stockname = null
         Stocknumber = Stockname + 4

***delete***

  delete from table_name where Stockid = 3
  truncate table_name ----------- 刪除表中所有行，仍保持表的完整性
  drop table table_name --------------- 完全刪除表

***alter table*** --- 修改資料庫表結構

  alter table database.owner.table_name add column_name char(2) null ..
  sp_help table_name ---- 顯示錶已有特徵
  create table table_name (name char(20), age smallint, lname varchar(30))
  insert into table_name select  ----- 實現刪除列的方法（建立新表）
  alter table table_name drop constraint Stockname_default ---- 刪除Stockname的default約束
    
***function(/**//*常用函式*/)***

----統計函式----
AVG    --求平均值
COUNT   --統計數目
MAX    --求最大值
MIN    --求最小值
SUM    --求和

--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id

--MAX
--求工資最高的員工姓名
use pangu
select e_name
from employee
where e_wage =
 (select max(e_wage)
  from employee)

--STDEV()
--STDEV()函式返回表示式中所有資料的標準差

--STDEVP()
--STDEVP()函式返回總體標準差

--VAR()
--VAR()函式返回表示式中所有值的統計變異數

--VARP()
--VARP()函式返回總體變異數

----算術函式----

/**//***三角函式***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的餘弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的餘切
/**//***反三角函式***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回餘弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
        --返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
                       --把弧度轉換為角度返回與表示式相同的資料型別可為
        --INTEGER/MONEY/REAL/FLOAT 型別
RADIANS(numeric_expression) --把角度轉換為弧度返回與表示式相同的資料型別可為
        --INTEGER/MONEY/REAL/FLOAT 型別
EXP(float_expression)  --返回表示式的指數值
LOG(float_expression)  --返回表示式的自然對數值
LOG10(float_expression)--返回表示式的以10 為底的對數值
SQRT(float_expression) --返回表示式的平方根
/**//***取近似值函式***/
CEILING(numeric_expression)  --返回>=表示式的最小整數返回的資料型別與表示式相同可為
        --INTEGER/MONEY/REAL/FLOAT 型別
FLOOR(numeric_expression)    --返回<=表示式的最小整數返回的資料型別與表示式相同可為
        --INTEGER/MONEY/REAL/FLOAT 型別
ROUND(numeric_expression)    --返回以integer_expression 為精度的四捨五入值返回的資料
        --型別與表示式相同可為INTEGER/MONEY/REAL/FLOAT 型別
ABS(numeric_expression)      --返回表示式的絕對值返回的資料型別與表示式相同可為
        --INTEGER/MONEY/REAL/FLOAT 型別
SIGN(numeric_expression)     --測試引數的正負號返回0 零值1 正數或-1 負數返回的資料型別
        --與表示式相同可為INTEGER/MONEY/REAL/FLOAT 型別
PI()       --返回值為π 即3.1415926535897936
RAND([integer_expression])   --用任選的[integer_expression]做種子值得出0-1 間的隨機浮點數


----字串函式----
ASCII()         --函式返回字元表示式最左端字元的ASCII 碼值
CHAR()   --函式用於將ASCII 碼轉換為字元
    --如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函式會返回一個NULL 值
LOWER()   --函式把字串全部轉換為小寫
UPPER()   --函式把字串全部轉換為大寫
STR()   --函式把數值型資料轉換為字元型資料
LTRIM()   --函式把字串頭部的空格去掉
RTRIM()   --函式把字串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING()  --函式返回部分字串
CHARINDEX(),PATINDEX()  --函式返回字串中某個指定的子串出現的開始位置
SOUNDEX()  --函式返回一個四位字元碼
    --SOUNDEX函式可用來查詢聲音相似的字串但SOUNDEX函式對數字和漢字均只返回0 值     
DIFFERENCE()    --函式返回由SOUNDEX 函式返回的兩個字元表示式的值的差異
    --0 兩個SOUNDEX 函式返回值的第一個字元不同
    --1 兩個SOUNDEX 函式返回值的第一個字元相同
    --2 兩個SOUNDEX 函式返回值的第一二個字元相同
    --3 兩個SOUNDEX 函式返回值的第一二三個字元相同
    --4 兩個SOUNDEX 函式返回值完全相同
                                       

QUOTENAME()  --函式返回被特定字元括起來的字串
/**//*select quotename('abc', '{') quotename('abc')
執行結果如下
----------------------------------{
{abc} [abc]*/

REPLICATE()     --函式返回一個重複character_expression 指定次數的字串
/**//*select replicate('abc', 3) replicate( 'abc', -2)
執行結果如下
----------- -----------
abcabcabc NULL*/

REVERSE()       --函式將指定的字串的字元排列順序顛倒
REPLACE()       --函式返回被替換了指定子串的字串
/**//*select replace('abc123g', '123', 'def')
執行結果如下
----------- -----------
abcdefg*/

SPACE()   --函式返回一個有指定長度的空白字串
STUFF()   --函式用另一子串替換字串指定位置長度的子串


----資料型別轉換函式----
CAST() 函式語法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函式語法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])

select cast(100+99 as char) convert(varchar(12), getdate())
執行結果如下
------------------------------ ------------
199   Jan 15 2000

----日期函式----
DAY()   --函式返回date_expression 中的日期值
MONTH()   --函式返回date_expression 中的月份值
YEAR()   --函式返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
    --函式返回指定日期date 加上指定的額外日期間隔number 產生的新日期
DATEDIFF(<datepart> ,<number> ,<date>)
    --函式返回兩個指定日期在datepart 方面的不同之處
DATENAME(<datepart> , <date>)  --函式以字串的形式返回日期的指定部分
DATEPART(<datepart> , <date>)  --函式以整數值的形式返回日期的指定部分
GETDATE()  --函式以DATETIME 的預設格式返回系統當前的日期和時間

----系統函式----
APP_NAME()      --函式返回當前執行的應用程式的名稱
COALESCE()  --函式返回眾多表示式中第一個非NULL 表示式的值
COL_LENGTH(<'table_name'>, <'column_name'>) --函式返回表中指定欄位的長度值
COL_NAME(<table_id>, <column_id>)   --函式返回表中指定欄位的名稱即列名
DATALENGTH() --函式返回資料表示式的資料的實際長度
DB_ID(['database_name']) --函式返回資料庫的編號
DB_NAME(database_id)  --函式返回資料庫的名稱
HOST_ID()     --函式返回伺服器端計算機的名稱
HOST_NAME()     --函式返回伺服器端計算機的名稱
IDENTITY(<data_type>[, seed increment]) [AS column_name])
 --IDENTITY() 函式只在SELECT INTO 語句中使用用於插入一個identity column列到新表中
/**//*select identity(int, 1, 1) as column_name
 into newtable
 from oldtable*/
ISDATE()  --函式判斷所給定的表示式是否為合理日期
ISNULL(<check_expression>, <replacement_value>) --函式將表示式中的NULL 值用指定值替換
ISNUMERIC()  --函式判斷所給定的表示式是否為合理的數值
NEWID()   --函式返回一個UNIQUEIDENTIFIER 型別的數值
NULLIF(<expression1>, <expression2>)
 --NULLIF 函式在expression1 與expression2 相等時返回NULL 值若不相等時則返回expression1 的值