蜀山趙氏SQL手冊

現在做IT的各個要求是全能，這個是百分之百不現實的。如何能夠在最短的時間内，找到自己想要的東西。其實最好的是自己将以前看過的東西進行一個總結。現在我貼出我很早以前寫的SQL幫助手冊。都是很基礎的東西，主要是格式。

希望對于那些業餘要搞搞SQLSERVER的朋友一些幫助吧。

-------------------------------------------------------------------------------------------------

在SQL語句中 CASE 與進階語言SWITCH  适用一個條件判斷有多個值的情況分别執行不同操作

case... when

commit  結束目前事務

在裡面還用到了 comment on 添加注釋

comment on table 添加表注釋

comment on column 添加列注釋

格式如下

comment on table 表名 is '注釋'；

主鍵primary key

添加格式

insert into 表名(字段名) values ('插入内容')，

添加主鍵

insert into 表名 primary key not null

添加主鍵格式2

add primary key (表名)

identity屬性

作用 建立一個辨別列

identity [(seed,increment)]  預設狀态為identity(1,1)

--seed的作用是裝在到表中第一行所使用的值

--increment 作為增量值

select max(字段名) from 表名

--判斷字段名組裡字段最大的值

升序和降序排列：

order by ...desc

降序排列

order by ...asc

升序排列

執行個體 ：

select * from  表名   order by 字段 desc   從大到小排列

select * from 表名  order by 字段 asc 從小到大排列

繼續執行個體

select 列名 from 表名 order by 字段 desc

alter 修改

alter table 表名 add 字段 date--類型

alter table 表名

删除字段

delete  from 表名 where 條件

各種計算語句

select avg(字段名)，max(字段名)，min(字段名)，sum(字段名)，max(字段名)-min(字段名)

--avg 求平均數    max 最大值   min 最小值  sum 求和 max-min 求值之間

update語句

update 表名 set 字段名 where 字段名=‘ ’

求最大值max()

執行個體求年齡最大的員工姓名

selcet name from person where age=(select age from person where max(age))3

消息 4145，級别 15，狀态 1，第 2 行

在應使用條件的上下文(在 ')' 附近)中指定了非布爾類型的表達式。  有錯

as 别名

格式：

select 列名(s)  from 表名  as 别名      表的文法

select 列名(s)  as 别名    from 表名    列的文法

執行個體：

select 别名.orderID, 别名.lastname,别名.fristname from 表名 as 别名 where 别名.orderID='字段名' where 别名.lastname='字段名'

--作用在于簡化編寫過程。。。。。。

注意事項：

在使用alter table 表名 add 字段名      --字段名後面不能使用 not null  在添加新字段的時候必須使字段可以為空設定

添加個删除主鍵名

alter table book_2 add  constraint 限制名 primary key(列名)

--constraint 限制     意思就是添加限制名及主鍵      

alter table book_2 drop constraint 限制名

--删除限制名  取消主鍵限制

查詢用昵稱顯示列名

select 昵稱=列名 from  表名

SELECT ColumnList FROM TableA inner join tableB on linkList

用于執行TableA和TableB之間的嚴格限制關系，即内聯關系，就是說linkList中提出的等于的限制關系，是左右對等的 比如

a.code=b.code，那麼a中的code必須在b中存在并且等于

如果是Left join，那麼 a.code=b.code，就是a.code未必存在于b中，如果存在，則執行對等關系

inner join内聯

left join左聯

right　join右聯

cross join交叉聯

full join

on　後面跟着條件

不重複顯示查詢結果語句

union 和SELECT DISTINCT Date

SQL語句1  union SQL語句2

如果一個語句中存在重複資訊則使用select distinct data

select distinct 列名 from 表名

使用 full join 執行個體

select 字段名 from 表名 full join 表名2 on 表名.字段名=表名2.字段名2

top語句

select top 排名 * from 表名

按照百分比例取條數

select top 數字 percent * from 表名

--percent 是百分數

LIKE與[]的使用執行個體

select 列名 from 表名 where 列名 like '[開頭字母或數字]%'

以指定字母或數字開頭的列名輸出

select 列名 from 表名 where 列名 like '%[開頭字母或數字]%'

包含有指定字母或數字的列名輸出

select 列名 from 表名 where 列名 like '[！/^開頭字母或數字]%'

不包含指定字母或數字的列名輸出

in操作符的應用

執行個體說明

select * from 表名 where 列名 in ('列名包含字段')

輸出結果 為制定包含字段的列名全部資訊

貌似和select 包含字段的列名 from 表名 相等   在未知情況下用in解決

between範圍語句

select * from 表名 where 列名 between '範圍1' and '範圍2'

由于資料庫處理差異不同可能存在 包含範圍1 不包含 範圍2  包含範圍1和2   不包含範圍1和2 的情況出現

inner join内聯執行個體

select 别名.列名  from 表名 as 别名

inner join 表名2 as 别名

on 别名.主鍵名=别名.主鍵名2

group by 别名.列名

備份附件 into / into ... in

執行個體

select 列名/*

into 新表名

from 舊表名

select 列名/* 

into 資料庫名.dbo.新表名

from 資料庫名.dbo.舊表名

--将舊表中的字段資訊調入另一個資料庫中并建立新表

SELECT 表名.列名,表名.列名

INTO 新表名

FROM 舊表名

INNER JOIN 列名

ON 表名.主鍵名=表名2.主鍵名

--為新表添加舊表中的特定資訊

date類型

integer(size)

int(size)

smallint(size)

tinyint(size)

 僅容納整數。在括号内規定數字的最大位數。

decimal(size,d)

numeric(size,d)

 容納帶有小數的數字。

"size" 規定數字的最大位數。"d" 規定小數點右側的最大位數。

char(size) 容納固定長度的字元串（可容納字母、數字以及特殊字元）。

在括号中規定字元串的長度。

varchar(size) 容納可變長度的字元串（可容納字母、數字以及特殊的字元）。

在括号中規定字元串的最大長度。

date(yyyymmdd) 容納日期。

not null 如果不想字段中插入數值就無法更新和插入新資料

unique 添加限制  每個表可以添加多條限制  但是 主鍵  primary key 隻能增加一條 

删除限制

alter 表名 drop constraint 列名

外鍵設定

了解外鍵作用：

1.  FOREIGN KEY 限制用于預防破壞表之間連接配接的動作。

2.  FOREIGN KEY 限制也能防止非法資料插入外鍵列，因為它必須是它指向的那個表中的值之一。

CREATE TABLE 表名

(

列名 int NOT NULL PRIMARY KEY,

列名 int NOT NULL,

列名 int FOREIGN KEY REFERENCES 表名1(主鍵名)

)

check 限制

對表和列進行限制  限制範圍是限定列的值

列名 int NOT NULL check (列名>0),

--check>0表示列名值必須大于零才能被輸入

列名 date NOT NULL,

列名 date,

CONSTRAINT 限制名 CHECK (限制條件)

--命名限制和 定義多個限制

alter table 表名 add check (列名條件)

--為存在的列名添加check  限制

alter table 表名 add constraint 限制名 check (列名條件)

--添加限制名和限制條件

default限制

說明：default 限制用于向列中插入預設值，如果沒有其他規定則向新紀錄插入預設值

getdate()參數  可以用于插入系統值

orderdate date default getdate()

index索引

建立一個索引

create index 索引名

on 表名(列名)

删除索引

drop index 表名.索引名

顯示與特定時間所查天數

Select datediff (day,getdate(),'2008-08-08')

建立check限制

格式：alter table 表名 add constraint 限制名 check(列名 限制條件)

執行個體：alter table book_2 add constraint aaaa check(b_sn between 1 and 100)

identity關鍵字 執行auto-increment指令

用法

create table 表名

    列名 date primary key identity,

    列名 date not null,

--預設地，IDENTITY 的開始值是 1，每條新紀錄遞增 1。

要規定 "P_Id" 列以 20 起始且遞增 10，請把 identity 改為 IDENTITY(20,10)。

date函數

GETDATE() 傳回目前日期和時間

DATEPART() 傳回日期/時間的單獨部分

DATEADD() 在日期中添加或減去指定的時間間隔

DATEDIFF() 傳回兩個日期之間的時間

CONVERT() 用不同的格式顯示日期/時間

SQL Server 使用下列資料類型在資料庫中存儲日期或日期/時間值：

DATE - 格式 YYYY-MM-DD

DATETIME - 格式: YYYY-MM-DD HH:MM:SS

SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS

TIMESTAMP - 格式: 唯一的數字

NULL函數

我們可以當它為占位符

在執行的時候我們使用IS NULL

select 列名

from 表名

where 列名 is null / where 列名 not is null

null在計算方面

SELECT 列名,含整型字段的列名*(含整型字段的列名2 +ISNULL(含整型字段的列名3,0))

FROM Products

--這裡isnull的作用是将 為空的字段 以0表示 友善計算

資料類型

Character 字元串：

資料類型 描述 存儲

char(n) 固定長度的字元串。最多 8,000 個字元。 n

varchar(n) 可變長度的字元串。最多 8,000 個字元。  

varchar(max) 可變長度的字元串。最多 1,073,741,824 個字元。  

text 可變長度的字元串。最多 2GB 字元資料。  

Unicode 字元串：

nchar(n) 固定長度的 Unicode 資料。最多 4,000 個字元。  

nvarchar(n) 可變長度的 Unicode 資料。最多 4,000 個字元。  

nvarchar(max) 可變長度的 Unicode 資料。最多 536,870,912 個字元。  

ntext 可變長度的 Unicode 資料。最多 2GB 字元資料。  

Binary 類型：

bit 允許 0、1 或 NULL  

binary(n) 固定長度的二進制資料。最多 8,000 位元組。  

varbinary(n) 可變長度的二進制資料。最多 8,000 位元組。  

varbinary(max) 可變長度的二進制資料。最多 2GB 位元組。  

p_w_picpath 可變長度的二進制資料。最多 2GB。  

Number 類型：

tinyint 允許從 0 到 255 的所有數字。 1 位元組

smallint 允許從 -32,768 到 32,767 的所有數字。 2 位元組

int 允許從 -2,147,483,648 到 2,147,483,647 的所有數字。 4 位元組

bigint 允許介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之間的所有數字。 8 位元組

decimal(p,s) 固定精度和比例的數字。允許從 -10^38 +1 到 10^38 -1 之間的數字。

p 參數訓示可以存儲的最大位數（小數點左側和右側）。p 必須是 1 到 38 之間的值。預設是 18。

s 參數訓示小數點右側存儲的最大位數。s 必須是 0 到 p 之間的值。預設是 0。

 5-17 位元組

numeric(p,s) 固定精度和比例的數字。允許從 -10^38 +1 到 10^38 -1 之間的數字。

smallmoney 介于 -214,748.3648 和 214,748.3647 之間的貨币資料。 4 位元組

money 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之間的貨币資料。 8 位元組

float(n) 從 -1.79E + 308 到 1.79E + 308 的浮動精度數字資料。參數 n 訓示該字段儲存 4 位元組還是 8 位元組。float(24) 儲存 4 位元組，而 float(53) 儲存 8 位元組。n 的預設值是 53。  4 或 8 位元組

real 從 -3.40E + 38 到 3.40E + 38 的浮動精度數字資料。 4 位元組

Date 類型：

datetime 從 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度為 3.33 毫秒。 8 bytes

datetime2 從 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度為 100 納秒。 6-8 bytes

smalldatetime 從 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度為 1 分鐘。 4 bytes

date 僅存儲日期。從 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 3 bytes

time 僅存儲時間。精度為 100 納秒。 3-5 bytes

datetimeoffset 與 datetime2 相同，外加時區偏移。 8-10 bytes

timestamp 存儲唯一的數字，每當建立或修改某行時，該數字會更新。timestamp 基于内部時鐘，不對應真實時間。每個表隻能有一個 timestamp 變量。  

其他資料類型：

資料類型 描述

sql_variant 存儲最多 8,000 位元組不同資料類型的資料，除了 text、ntext 以及 timestamp。

uniqueidentifier 存儲全局辨別符 (GUID)。

xml 存儲 XML 格式化資料。最多 2GB。

cursor 存儲對用于資料庫操作的指針的引用。

table 存儲結果集，供稍後處理。

資料庫伺服器

DBMS - 資料庫管理系統（Database Management System）

資料庫管理系統是一種可以通路資料庫中資料的計算機程式。

DBMS 使我們有能力在資料庫中提取、修改或者存貯資訊。

不同的 DBMS 提供不同的函數供查詢、送出以及修改資料。

RDBMS - 關系資料庫管理系統（Relational Database Management System）

關系資料庫管理系統 (RDBMS) 也是一種資料庫管理系統，其資料庫是根據資料間的關系來組織和通路資料的。

20 世紀 70 年代初，IBM 公司發明了 RDBMS。

RDBMS 是 SQL 的基礎，也是所有現代資料庫系統諸如 Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及 Microsoft Access 的基礎。

AVG的運用

--AVG 平均值

select avg(列名) from 表名

--求列的平均值

select 列名 from 表名 where 列名>(select AVG(列名) from 表名)

--列出大于平均數的字段名 比如列出年齡大于平均數的 人的姓名

其他的比如MAX和MIN  大家舉一反三

count的運用

--count 表中所占的數列

select count(列名) from 表名

--指定的列名下字段的列數

select count(列名) from 表名 where 列名='字段名'

還有求偶數

%2=0

求奇數

%2=1

select 列名 from 表名 where 列名%2=0

group by 分組

--适合于分與求和差等計算式的常用

執行個體：select 列名1 sun(列名2) from 表名 group by 列名1

--結果進行分組求和。

分組求和

按照個人習慣寫的 使用having 函數 加在group by後面  做為合計函數的條件語句

加having執行個體：

select 列名 sum(列名2)

where 列名='字段名'

group by 列名

having sum(列名)>指定數值

--where 不能作為合計函數條件的時候用having 設定合計函數條件使用

大小寫轉化

ucase()

--這個很簡單 點下就行了

select ucase(列名) as 列名 from 表名

--将列名轉化成大寫

lcase()

--轉化成小寫

select lcase(列名) as 列名 from 表名

len()函數

--提取列中字段所占長度

select len(列名) as 别名 from 表名

getdate()顯示時間語句

select getdate() 列名 from 表名

知識點： TRUNCATE 删除表中的所有行，而不記錄單個行删除操作，不能帶條件

         STATISTICS 顯示磁盤活動量的資訊。

         +在程式裡面是連接配接的意思 比如 ['+tablename+'] 是用來表示字元串

    @PageSize int =5

    --頁面大小

    @TotalCount int out

    --總共的列

sp_executesql

執行可以多次重用或動态生成的 Transact-SQL 語句或批處理。Transact-SQL 語句或批處理可以包含嵌入參數。

文法

sp_executesql [@stmt =] stmt

[

    {, [@params =] N'@parameter_name  data_type [,...n]' }

    {, [@param1 =] 'value1' [,...n] }

]

參數

[@stmt =] stmt

包含 Transact-SQL 語句或批處理的 Unicode 字元串，stmt 必須是可以隐式轉換為 ntext 的 Unicode 常量或變量。不允許使用更複雜的 Unicode 表達式（例如使用 + 運算符串聯兩個字元串）。不允許使用字元常量。如果指定常量，則必須使用 N 作為字首。例如，Unicode 常量 N'sp_who' 是有效的，但是字元常量 'sp_who' 則無效。字元串的大小僅受可用資料庫伺服器記憶體限制。

簡單的單表分頁

use zyx

go

SELECT TOP 3 *

FROM

        (

        SELECT ROW_NUMBER() OVER (ORDER BY b_id) AS RowNumber,* FROM book

        ) A

WHERE RowNumber > 3*(2-1)

RANK ( ) OVER ( [query_partition_clause] order_by_clause )

DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

可實作按指定的字段分組排序，對于相同分組字段的結果集進行排序,

其中PARTITION BY 為分組字段，ORDER BY 指定排序字段

rank,row_number,desc_rank的差別

--rank根據order by的排名會出現并列的現象，在排名順序時出現1，2，3，4，4，6，7，7，9

--desc_rank根據order by的排名會出現并列現象，但是不會不會跳過顯示1，2，2，3，3，4，4

--row_number根據order by的排名不會出現并列現象1,2,3,4,5,6,7

use zyp

declare @table table (b_sn varchar(50),b_name varchar(50), b_id int,count int)

insert @table

select b_sn,b_name,b_id,count(b_id) from book_2 as c

group by b_sn,b_name,b_id

select c.b_sn,c.b_name,min(c.b_id)

from

select a.b_sn,a.b_name,b.b_id from

select b_sn,b_name,max(count) as Cou from @table

group by b_sn,b_name

) as a

inner join @table as b

on a.b_sn=b.b_sn and a.b_name=b.b_name and a.cou=b.count

) as c

group by c.b_sn,c.b_name

--查詢出現頻率最多的分組記錄（取最小值）

剛剛寫的簡單的動态删除記錄的存儲過程

create proc pghh

@b_sn varchar(50),@tablename varchar(50)

as

declare @sql varchar(50)

set @sql= ' delete ' +'from '+ @tablename + ' where '+' b_sn =''' + @b_sn+''''

exec(@sql)

exec pghh '3453', 'book_2'

執行個體學習下

if(@shth='' and @djh='')

begin

--取不同的發票号

 create table #tmp(hh int identity(1,1),fpno varchar(20))

 insert into #tmp(fpno)

 select distinct(fpno) from yyjs

 where ywlxdm=@ywlxdm

  and (len(@dwdm)<=7 and zddm like @dwdm+'%'

  or len(@dwdm)=8 and left(dwdm,8)=@dwdm

  or len(@dwdm)=9 and zddm+fjh=@dwdm

  or len(@dwdm)=10 and left(dwdm,10)=@dwdm

  or len(@dwdm)=14 and dwdm=@dwdm)

  and (comm=0 or comm=3)

  and crq>=@crq1 and crq<=@crq2

  and (@djlx='1'

  or @djlx='2' and zyno='999999'

  or @djlx='3' and zyno='999998'

  or @djlx='4' and zyno not like '9%')

 order by fpno

--計算出按@PageCount分頁後共有多少頁

 declare @PageSize1 decimal(18,2)

 select @PageSize1=cast(@PageSize as varchar(10))+'.00'

 select @PageCount=ceiling(count(*)/@PageSize1) from #tmp

 select @fpzs=count(*) from #tmp --------------------------------總的發票張數

 select @yxzs=count(distinct(fpno)) from yyjs where

 ywlxdm=@ywlxdm

  and c2=''

  or @djlx='4' and zyno not like '9%')---------------------有效發票張數

 set @chzs=@fpzs-@yxzs -------------------------------------------沖紅張數

 insert into T_sum_SGQDCXTB

 (YHDM,YWLXDM,DWDM,DWMC,FPH,CZDM,CZMC,HTH,SHTH,FZXM,CRQ,ZL,JE,KMBT,PZBT,KKJE,N3,SFJE,KPLX,CHBZ,SGX)

 select @yhdm,@ywlxdm,zddm+fjh as zddm,'',fpno,dwdm,'',hth,'',fzxm,left(crq,4)+'-'+substring(crq,5,2)+'-'+right(crq,2),isnull(sum(zl),0),isnull(sum(je),0),

 isnull(sum(bzje),0),isnull(sum(n2),0),isnull(sum(kkje),0),isnull(sum(n3),0),sum(je)+sum(bzje)+sum(n2)-sum(kkje)-sum(n3),

 case zyno when '999999' then '主機開票' when '999998' then '手工開票' else '補錄發票' end,c2,fjh

 from yyjs

 and (len(@dwdm)<=7 and zddm like @dwdm+'%'

 or len(@dwdm)=8 and left(dwdm,8)=@dwdm

 or len(@dwdm)=9 and zddm+fjh=@dwdm

 or len(@dwdm)=10 and left(dwdm,10)=@dwdm

 or len(@dwdm)=14 and dwdm=@dwdm)

 and (comm=0 or comm=3)

 and crq>=@crq1 and crq<=@crq2

 and (@djlx='1'

 or @djlx='2' and zyno='999999'

 or @djlx='3' and zyno='999998'

 or @djlx='4' and zyno not like '9%')

 and(@hth=''

 or hth like '%'+@hth)

 and fpno in (select fpno from #tmp where ceiling(hh/@PageSize1)=@Page)

 group by zddm,fpno,dwdm,hth,fzxm,crq,zyno,c2,fjh

--處理完成後删除臨時表

 drop table #tmp

end

create proc phoo111

   @hs1 varchar(50),@b_id varchar(50),@tablename varchar(50),@rownumber varchar(50),@ys1 varchar(50),@ys2 varchar(50)

declare @sql varchar(200)

set @sql='select'+' top '  + @hs1+'*'+ ' from'+ '  (select row_number() over (order by '+@b_id+') as rownumber,* from '+@tablename+') A  where '+ @rownumber+ '>'+ @hs1+'*('+@ys1+'-'+@ys2+')'

exec (@sql)

簡單的動态分頁存儲過程

增加rollback 復原和commit 送出事務執行個體

執行個體是為現實效果 可以改下 看下效果

begin tran

    declare @1 varchar(50)

declare @sql varchar(100)

      set @1=1

   while   @1<=100  

  begin

 -- select  @sql= b_id from book_3

  print @1

 set @1 =@1+ 1

 end

   if @1=1

    rollback tran

   else

 print 'noooo'