第一篇：精妙SQL語(yǔ)句

精妙SQL語(yǔ)句

? 說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b)select * into b from a where 1<>1

? 說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b)insert into b(a, b, c)select d,e,f from b;? 說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

? 說(shuō)明：外連接查詢(xún)(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ? 說(shuō)明：日程安排提前五分鐘提醒

select * from 日程安排 where datediff('minute',f開(kāi)始時(shí)間,getdate())>5 ? 說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息

delete from info where not exists(select * from infobz where info.infid=infobz.infid)

? 說(shuō)明：--

SQL:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE

FROM TABLE1，(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM(SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM')= TO_CHAR(SYSDATE, 'YYYY/MM'))X，(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM')=

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM')|| '/01','YYYY/MM/DD')1 FROM Handle a)

? 一個(gè)SQL語(yǔ)句的問(wèn)題:行列轉(zhuǎn)換 select * from v_temp 上面的視圖結(jié)果如下: user_name role_name-------------------------系統(tǒng)管理員 管理員

feng 管理員

feng 一般用戶(hù)

test 一般用戶(hù)

想把結(jié)果變成這樣: user_name role_name---------------------------系統(tǒng)管理員 管理員

feng 管理員,一般用戶(hù)

test 一般用戶(hù)

=================== create table a_test(name varchar(20),role2 varchar(20))insert into a_test values('李','管理員')insert into a_test values('張','管理員')insert into a_test values('張','一般用戶(hù)')insert into a_test values('常','一般用戶(hù)')

create function join_str(@content varchar(100))returns varchar(2000)as begin declare @str varchar(2000)set @str='' select @str=@str+','+rtrim(role2)from a_test where [name]=@content select @str=right(@str,len(@str)-1)return @str end go

--調(diào)用：

select [name],dbo.join_str([name])role2 from a_test group by [name]

--select distinct name,dbo.uf_test(name)from a_test ? 快速比較結(jié)構(gòu)相同的兩表

結(jié)構(gòu)相同的兩表，一表有記錄3萬(wàn)條左右，一表有記錄2萬(wàn)條左右，我怎樣快速查找兩表的不同記錄？

============================ 給你一個(gè)測(cè)試方法，從northwind中的orders表取數(shù)據(jù)。select * into n1 from orders select * into n2 from orders

select * from n1 select * from n2

--添加主鍵，然后修改n1中若干字段的若干條

alter table n1 add constraint pk_n1_id primary key(OrderID)alter table n2 add constraint pk_n2_id primary key(OrderID)

select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1

應(yīng)該可以，而且將不同的記錄的ID顯示出來(lái)。下面的適用于雙方記錄一樣的情況，select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1)至于雙方互不存在的記錄是比較好處理的--刪除n1,n2中若干條記錄

delete from n1 where orderID in('10728','10730')delete from n2 where orderID in('11000','11001')

--*************************************************************--雙方都有該記錄卻不完全相同

select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1)union--n2中存在但在n1中不存的在10728,10730 select * from n1 where OrderID not in(select OrderID from n2)union--n1中存在但在n2中不存的在11000,11001 select * from n2 where OrderID not in(select OrderID from n1)? 四種方法取表里n到m條紀(jì)錄：

1.select top m * into 臨時(shí)表(或表變量)from tablename order by columnname--將top m筆插入 set rowcount n select * from 表變量 order by columnname desc

2.select top n * from(select top m * from tablename order by columnname)a order by columnname desc

3.如果tablename里沒(méi)有其他identity列，那么： select identity(int)id0,* into #temp from tablename

取n到m條的語(yǔ)句為：

select * from #temp where id0 >=n and id0 <= m

如果你在執(zhí)行select identity(int)id0,* into #temp from tablename這條語(yǔ)句的時(shí)候報(bào)錯(cuò),那是因?yàn)槟愕腄B中間的select into/bulkcopy屬性沒(méi)有打開(kāi)要先執(zhí)行： exec sp_dboption 你的DB名字,'select into/bulkcopy',true

4.如果表里有identity屬性，那么簡(jiǎn)單：

select * from tablename where identitycol between n and m ? 如何刪除一個(gè)表中重復(fù)的記錄？

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分組字段﹐即主鍵字段 as begin declare @max integer,@id varchar(30),@sql varchar(7999),@type integer select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*)from ' +@t_name +' group by ' +@f_key +' having count(*)> 1' exec(@sql)open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin

select @max = @max-1 set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name)and name=@f_key if @type=56 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql)fetch cur_rows into @id,@max end

close cur_rows deallocate cur_rows set rowcount 0 end

select * from systypes select * from syscolumns where id = object_id('a_dist')? 查詢(xún)數(shù)據(jù)的最大排序問(wèn)題（只能用一條語(yǔ)句寫(xiě)）

CREATE TABLE hard(qu char(11),co char(11),je numeric(3, 0))

insert into hard values('A','1',3)insert into hard values('A','2',4)insert into hard values('A','4',2)insert into hard values('A','6',9)insert into hard values('B','1',4)insert into hard values('B','2',5)insert into hard values('B','3',6)insert into hard values('C','3',4)insert into hard values('C','6',7)insert into hard values('C','2',3)

要求查詢(xún)出來(lái)的結(jié)果如下：

qu co je

---------------------------A 6 9 A 2 4 B 3 6 B 2 5 C 6 7 C 3 4

就是要按qu分組，每組中取je最大的前2位！而且只能用一句sql語(yǔ)句?。?/p>

select * from hard a where je in(select top 2 je from hard b where a.qu=b.qu order by je)

? 求刪除重復(fù)記錄的sql語(yǔ)句？

怎樣把具有相同字段的紀(jì)錄刪除，只留下一條。例如，表test里有id,name字段

如果有name相同的記錄 只留下一條，其余的刪除。name的內(nèi)容不定，相同的記錄數(shù)不定。有沒(méi)有這樣的sql語(yǔ)句？

============================== A:一個(gè)完整的解決方案：

將重復(fù)的記錄記入temp1表: select [標(biāo)志字段id],count(*)into temp1 from [表名] group by [標(biāo)志字段id] having count(*)>1

2、將不重復(fù)的記錄記入temp1表: insert temp1 select [標(biāo)志字段id],count(*)from [表名] group by [標(biāo)志字段id] having count(*)=1

3、作一個(gè)包含所有不重復(fù)記錄的表：

select * into temp2 from [表名] where 標(biāo)志字段id in(select 標(biāo)志字段id from temp1)

4、刪除重復(fù)表: delete [表名]

5、恢復(fù)表：

insert [表名] select * from temp2

6、刪除臨時(shí)表: drop table temp1 drop table temp2 ================================ B: create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分組字段﹐即主鍵字段 as begin declare @max integer,@id varchar(30),@sql varchar(7999),@type integer select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*)from ' +@t_name +' group by ' +@f_key +' having count(*)> 1' exec(@sql)open cur_rows

fetch cur_rows into @id,@max while @@fetch_status=0 begin

select @max = @max-1 set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name)and name=@f_key if @type=56 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql)fetch cur_rows into @id,@max end

close cur_rows deallocate cur_rows set rowcount 0 end

select * from systypes select * from syscolumns where id = object_id('a_dist')? 行列轉(zhuǎn)換--普通

假設(shè)有張學(xué)生成績(jī)表(CJ)如下

Name Subject Result 張三 語(yǔ)文 80 張三 數(shù)學(xué) 90 張三 物理 85 李四 語(yǔ)文 85 李四 數(shù)學(xué) 92 李四 物理 82

想變成

姓名 語(yǔ)文 數(shù)學(xué) 物理

張三 80 90 85 李四 85 92 82

declare @sql varchar(4000)set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end)['+Subject+']'

from(select distinct Subject from CJ)as a

select @sql = @sql+' from test group by name' exec(@sql)

行列轉(zhuǎn)換--合并

有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1

如何化成表B: id pid 1 1,2,3 2 1,2 3 1

創(chuàng)建一個(gè)合并的函數(shù)

create function fmerg(@id int)returns varchar(8000)as begin

declare @str varchar(8000)set @str=''

select @str=@str+','+cast(pid as varchar)from 表A where id=@id set @str=right(@str,len(@str)-1)return(@str)End go

--調(diào)用自定義函數(shù)得到結(jié)果

select distinct id,dbo.fmerg(id)from 表A ? 如何取得一個(gè)數(shù)據(jù)表的所有列名

方法如下：先從SYSTEMOBJECT系統(tǒng)表中取得數(shù)據(jù)表的SYSTEMID,然后再SYSCOLUMN表中取得該數(shù)據(jù)表的所有列名。

SQL語(yǔ)句如下：

declare @objid int,@objname char(40)set @objname = 'tablename'

select @objid = id from sysobjects where id = object_id(@objname)

select 'Column_name' = name from syscolumns where id = @objid order by colid

或

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'

? 通過(guò)SQL語(yǔ)句來(lái)更改用戶(hù)的密碼

修改別人的,需要sysadmin role

EXEC sp_password NULL, 'newpassword', 'User'

如果帳號(hào)為SA執(zhí)行EXEC sp_password NULL, 'newpassword', sa ? 怎么判斷出一個(gè)表的哪些字段不允許為空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename ? 如何在數(shù)據(jù)庫(kù)里找到含有相同字段的表？

a.查已知列名的情況

SELECT b.name as TableName,a.name as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U'

AND a.name='你的字段名字'

? 未知列名查所有在不同表出現(xiàn)過(guò)的列名

Select o.name As tablename,s1.name As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = 'U' And Exists（Select 1 From syscolumns s2 Where s1.name = s2.name And s1.id <> s2.id)

? 查詢(xún)第xxx行數(shù)據(jù)

假設(shè)id是主鍵：

select * from(select top xxx * from yourtable)aa where not exists(select 1 from(select top xxx-1 * from yourtable)bb where aa.id=bb.id)

如果使用游標(biāo)也是可以的

fetch absolute [number] from [cursor_name] 行數(shù)為絕對(duì)行數(shù)

? SQL Server日期計(jì)算

a.一個(gè)月的第一天

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)b.本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)c.一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)d.季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)e.上個(gè)月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))f.去年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))g.本月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))h.本月的第一個(gè)星期一

select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

i.本年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

? 獲取表結(jié)構(gòu)[把 'sysobjects' 替換 成 'tablename' 即可]

SELECT CASE IsNull(I.name, '')When '' Then '' Else '*'

End as IsPK，Object_Name(A.id)as t_name, A.name as c_name，IsNull(SubString(M.text, 1, 254), '')as pbc_init, T.name as F_DataType，CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')WHEN '' Then Cast(A.prec as varchar)

ELSE Cast(A.prec as varchar)+ ',' + Cast(A.scale as varchar)END as F_Scale，A.isnullable as F_isNullAble FROM Syscolumns as A JOIN Systypes as T

ON(A.xType = T.xUserType AND A.Id = Object_id('sysobjects'))LEFT JOIN(SysIndexes as I JOIN Syscolumns as A1

ON(I.id = A1.id and A1.id = object_id('sysobjects')and(I.status & 0x800)= 0x800 AND A1.colid <= I.keycnt))

ON(A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid))LEFT JOIN SysComments as M

ON(M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint')= 1)ORDER BY A.Colid ASC

? 提取數(shù)據(jù)庫(kù)內(nèi)所有表的字段詳細(xì)說(shuō)明的SQL語(yǔ)句

SELECT

(case when a.colorder=1 then d.name else '' end)N'表名', a.colorder N'字段序號(hào)', a.name N'字段名'，(case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then ''else '' end)N'標(biāo)識(shí)'，(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'類(lèi)型'，a.length N'占用字節(jié)數(shù)'，COLUMNPROPERTY(a.id,a.name,'PRECISION')as N'長(zhǎng)度'，isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as N'小數(shù)位數(shù)',(case when a.isnullable=1 then ''else '' end)N'允許空', isnull(e.text,'')N'默認(rèn)值'，isnull(g.[value],'')AS N'字段說(shuō)明' 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

? 快速獲取表test的記錄總數(shù)[對(duì)大容量表非常有效]

快速獲取表test的記錄總數(shù):

select rows from sysindexes where id = object_id('test')and indid in(0,1)

update 2 set KHXH=(ID+1)2 2行遞增編號(hào)

update [23] set id1 = 'No.'+right('00000000'+id,6)where id not like 'No%' //遞增

update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6)//補(bǔ)位遞增 delete from [1] where(id%2)=1 奇數(shù)

? 替換表名字段

update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/')where domurl like '%Upload/Imgswf/%' ? 截位

SELECT LEFT(表名, 5)

下列語(yǔ)句部分是Mssql語(yǔ)句，不可以在access中使用。

SQL分類(lèi)：

DDL-數(shù)據(jù)定義語(yǔ)言(CREATE，ALTER，DROP，DECLARE)

DML-數(shù)據(jù)操縱語(yǔ)言(SELECT，DELETE，UPDATE，INSERT)

DCL-數(shù)據(jù)控制語(yǔ)言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,簡(jiǎn)要介紹基礎(chǔ)語(yǔ)句：

1、說(shuō)明：創(chuàng)建數(shù)據(jù)庫(kù)

CREATE DATABASE database-name

2、說(shuō)明：刪除數(shù)據(jù)庫(kù)

drop database dbname

3、說(shuō)明：備份sql server

---創(chuàng)建 備份數(shù)據(jù)的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

---開(kāi)始 備份

BACKUP DATABASE pubs TO testBack

4、說(shuō)明：創(chuàng)建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根據(jù)已有的表創(chuàng)建新表：

A：create table tab_new like tab_old(使用舊表創(chuàng)建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、說(shuō)明：刪除新表drop table tabname

6、說(shuō)明：增加一個(gè)列

Alter table tabname add column col type

注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類(lèi)型也不能改變，唯一能改變的是增加varchar類(lèi)型的長(zhǎng)度。

7、說(shuō)明：添加主鍵： Alter table tabname add primary key(col)

說(shuō)明：刪除主鍵： Alter table tabname drop primary key(col)

8、說(shuō)明：創(chuàng)建索引：create [unique] index idxname on tabname(col….)

刪除索引：drop index idxname

注：索引是不可更改的，想更改必須刪除重新建。

9、說(shuō)明：創(chuàng)建視圖：create view viewname as select statement

刪除視圖：drop view viewname

10、說(shuō)明：幾個(gè)簡(jiǎn)單的基本的sql語(yǔ)句

選擇：select * from table1 where 范圍

插入：insert into table1(field1,field2)values(value1,value2)

刪除：delete from table1 where 范圍

更新：update table1 set field1=value1 where 范圍

查找：select * from table1 where field1 like ?%value1%?---like的語(yǔ)法很精妙，查資料!

排序：select * from table1 order by field1,field2 [desc]

總數(shù)：select count * as totalcount from table1

求和：select sum(field1)as sumvalue from table1

平均：select avg(field1)as avgvalue from table1

最大：select max(field1)as maxvalue from table1

最小：select min(field1)as minvalue from table1

11、說(shuō)明：幾個(gè)高級(jí)查詢(xún)運(yùn)算詞

A： UNION 運(yùn)算符

UNION 運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)（即 UNION ALL），不消除重復(fù)行。兩種情況下，派生表的每一行不是來(lái)自 TABLE1 就是來(lái)自 TABLE2。

B： EXCEPT 運(yùn)算符

EXCEPT 運(yùn)算符通過(guò)包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí)(EXCEPT ALL)，不消除重復(fù)行。

C： INTERSECT 運(yùn)算符

INTERSECT 運(yùn)算符通過(guò)只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 INTERSECT 一起使用時(shí)(INTERSECT ALL)，不消除重復(fù)行。

注：使用運(yùn)算詞的幾個(gè)查詢(xún)結(jié)果行必須是一致的。

12、說(shuō)明：使用外連接

A、left outer join：

左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。

C：full outer join：

全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。

其次，大家來(lái)看一些不錯(cuò)的sql語(yǔ)句

1、說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b)(Access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b)(Access可用)

insert into b(a, b, c)select d,e,f from b;

3、說(shuō)明：跨數(shù)據(jù)庫(kù)之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑)(Access可用)

insert into b(a, b, c)select d,e,f from b in ?具體數(shù)據(jù)庫(kù)? where 條件

例子：..from b in '“&Server.MapPath(”.“)&”data.mdb“ &”' where..4、說(shuō)明：子查詢(xún)(表名1：a 表名2：b)

select a,b,c from a where a IN(select d from b)或者: select a,b,c from a where a IN(1,2,3)

5、說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

6、說(shuō)明：外連接查詢(xún)(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、說(shuō)明：在線(xiàn)視圖查詢(xún)(表名1：a)

select * from(SELECT a,b,c FROM a)T where t.a > 1;

8、說(shuō)明：between的用法,between限制查詢(xún)數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 數(shù)值1 and 數(shù)值2

9、說(shuō)明：in 的使用方法

select * from table1 where a [not] in(?值1?,?值2?,?值4?,?值6?)

10、說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息

delete from table1 where not exists(select * from table2 where table1.field1=table2.field1)

11、說(shuō)明：四表聯(lián)查問(wèn)題：

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、說(shuō)明：日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff('minute',f開(kāi)始時(shí)間,getdate())>5

13、說(shuō)明：一條sql 語(yǔ)句搞定數(shù)據(jù)庫(kù)分頁(yè)

select top 10 b.* from(select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc)a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段

14、說(shuō)明：前10條記錄

select top 10 * form table1 where 范圍

15、說(shuō)明：選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類(lèi)似這樣的用法可以用于論壇每月排行榜,每月熱銷(xiāo)產(chǎn)品分析,按科目成績(jī)排名,等等.)

select a,b,c from tablename ta where a=(select max(a)from tablename tb where tb.b=ta.b)

16、說(shuō)明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表

(select a from tableA)except(select a from tableB)except(select a from tableC)

17、說(shuō)明：隨機(jī)取出10條數(shù)據(jù)

select top 10 * from tablename order by newid()

18、說(shuō)明：隨機(jī)選擇記錄

select newid()

19、說(shuō)明：刪除重復(fù)記錄

Delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)

20、說(shuō)明：列出數(shù)據(jù)庫(kù)里所有的表名

select name from sysobjects where type='U'

21、說(shuō)明：列出表里的所有的

select name from syscolumns where id=object_id('TableName')

22、說(shuō)明：列示type、vender、pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn)多重選擇，類(lèi)似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 tablename group by type

顯示結(jié)果：

type vender pcs

電腦 A 1

電腦 A 1

光盤(pán) B 2

光盤(pán) A 2

手機(jī) B 3

手機(jī) C 3

23、說(shuō)明：初始化表table1

TRUNCATE TABLE table1

24、說(shuō)明：選擇從10到15的記錄

select top 5 * from(select top 15 * from table order by id asc)table_別名 order by id desc

隨機(jī)選擇數(shù)據(jù)庫(kù)記錄的方法（使用Randomize函數(shù)，通過(guò)SQL語(yǔ)句實(shí)現(xiàn)）

對(duì)存儲(chǔ)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)來(lái)說(shuō)，隨機(jī)數(shù)特性能給出上面的效果，但它們可能太慢了些。你不能要求ASP“找個(gè)隨機(jī)數(shù)”然后打印出來(lái)。實(shí)際上常見(jiàn)的解決方案是建立如下所示的循環(huán)：

Randomize

RNumber = Int(Rnd*499)+1

While Not objRec.EOF

If objRec(“ID”)= RNumber THEN

...這里是執(zhí)行腳本...end if

objRec.MoveNext

Wend

這很容易理解。首先，你取出1到500范圍之內(nèi)的一個(gè)隨機(jī)數(shù)（假設(shè)500就是數(shù)據(jù)庫(kù)內(nèi)記錄的總數(shù)）。然后，你遍歷每一記錄來(lái)測(cè)試ID 的值、檢查其是否匹配RNumber。滿(mǎn)足條件的話(huà)就執(zhí)行由THEN 關(guān)鍵字開(kāi)始的那一塊代碼。假如你的RNumber 等于495，那么要循環(huán)一遍數(shù)據(jù)庫(kù)花的時(shí)間可就長(zhǎng)了。雖然500這個(gè)數(shù)字看起來(lái)大了些，但相比更為穩(wěn)固的企業(yè)解決方案這還是個(gè)小型數(shù)據(jù)庫(kù)了，后者通常在一個(gè)數(shù)據(jù)庫(kù)內(nèi)就包含了成千上萬(wàn)條記錄。這時(shí)候不就死定了？

采用SQL，你就可以很快地找出準(zhǔn)確的記錄并且打開(kāi)一個(gè)只包含該記錄的recordset，如下所示：

Randomize

RNumber = Int(Rnd*499)+ 1

SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber

set objRec = ObjConn.Execute(SQL)

Response.WriteRNumber & “ = ” & objRec(“ID”)& “ ” & objRec(“c_email”)

不必寫(xiě)出RNumber 和ID，你只需要檢查匹配情況即可。只要你對(duì)以上代碼的工作滿(mǎn)意，你自可按需操作“隨機(jī)”記錄。Recordset沒(méi)有包含其他內(nèi)容，因此你很快就能找到你需要的記錄這樣就大大降低了處理時(shí)間。

再談隨機(jī)數(shù)

現(xiàn)在你下定決心要榨干Random 函數(shù)的最后一滴油，那么你可能會(huì)一次取出多條隨機(jī)記錄或者想采用一定隨機(jī)范圍內(nèi)的記錄。把上面的標(biāo)準(zhǔn)Random 示例擴(kuò)展一下就可以用SQL應(yīng)對(duì)上面兩種情況了。

為了取出幾條隨機(jī)選擇的記錄并存放在同一recordset內(nèi)，你可以存儲(chǔ)三個(gè)隨機(jī)數(shù)，然后查詢(xún)數(shù)據(jù)庫(kù)獲得匹配這些數(shù)字的記錄：

SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber & “ OR ID = ” & RNumber2 & “ OR ID = ” & RNumber3

假如你想選出10條記錄（也許是每次頁(yè)面裝載時(shí)的10條鏈接的列表），你可以用BETWEEN 或者數(shù)學(xué)等式選出第一條記錄和適當(dāng)數(shù)量的遞增記錄。這一操作可以通過(guò)好幾種方式來(lái)完成，但是 SELECT 語(yǔ)句只顯示一種可能（這里的ID 是自動(dòng)生成的號(hào)碼）：

SQL = “SELECT * FROM Customers WHERE ID BETWEEN ” & RNumber & “ AND ” & RNumber & “+ 9”

注意：以上代碼的執(zhí)行目的不是檢查數(shù)據(jù)庫(kù)內(nèi)是否有9條并發(fā)記錄。

隨機(jī)讀取若干條記錄，測(cè)試過(guò)

Access語(yǔ)法：SELECT top 10 * From 表名 ORDER BY Rnd(id)

Sql server:select top n * from 表名 order by newid()

mysqlelect * From 表名 Order By rand()Limit n

Access左連接語(yǔ)法(最近開(kāi)發(fā)要用左連接,Access幫助什么都沒(méi)有,網(wǎng)上沒(méi)有Access的SQL說(shuō)明,只有自己測(cè)試, 現(xiàn)在記下以備后查)

語(yǔ)法elect table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where...使用SQL語(yǔ)句 用...代替過(guò)長(zhǎng)的字符串顯示

語(yǔ)法：

SQL數(shù)據(jù)庫(kù)：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename

Access數(shù)據(jù)庫(kù)：SELECT iif(len(field)>2,left(field,2)+'...',field)FROM tablename;

Conn.Execute說(shuō)明

Execute方法

該方法用于執(zhí)行SQL語(yǔ)句。根據(jù)SQL語(yǔ)句執(zhí)行后是否返回記錄集，該方法的使用格式分為以下兩種：

1．執(zhí)行SQL查詢(xún)語(yǔ)句時(shí)，將返回查詢(xún)得到的記錄集。用法為：

Set 對(duì)象變量名=連接對(duì)象.Execute(“SQL 查詢(xún)語(yǔ)言”)

Execute方法調(diào)用后，會(huì)自動(dòng)創(chuàng)建記錄集對(duì)象，并將查詢(xún)結(jié)果存儲(chǔ)在該記錄對(duì)象中，通過(guò)Set方法，將記錄集賦給指定的對(duì)象保存，以后對(duì)象變量就代表了該記錄集對(duì)象。

2．執(zhí)行SQL的操作性語(yǔ)言時(shí)，沒(méi)有記錄集的返回。此時(shí)用法為：

連接對(duì)象.Execute “SQL 操作性語(yǔ)句” [, RecordAffected][, Option]

·RecordAffected 為可選項(xiàng)，此出可放置一個(gè)變量，SQL語(yǔ)句執(zhí)行后，所生效的記錄數(shù)會(huì)自動(dòng)保存到該變量中。通過(guò)訪(fǎng)問(wèn)該變量，就可知道SQL語(yǔ)句隊(duì)多少條記錄進(jìn)行了操作。

·Option 可選項(xiàng)，該參數(shù)的取值通常為adCMDText，它用于告訴ADO，應(yīng)該將Execute方法之后的第一個(gè)字符解釋為命令文本。通過(guò)指定該參數(shù)，可使執(zhí)行更高效。

·BeginTrans、RollbackTrans、CommitTrans方法

這三個(gè)方法是連接對(duì)象提供的用于事務(wù)處理的方法。BeginTrans用于開(kāi)始一個(gè)事物；RollbackTrans用于回滾事務(wù)；CommitTrans用于提交所有的事務(wù)處理結(jié)果，即確認(rèn)事務(wù)的處理。

事務(wù)處理可以將一組操作視為一個(gè)整體，只有全部語(yǔ)句都成功執(zhí)行后，事務(wù)處理才算成功；若其中有一個(gè)語(yǔ)句執(zhí)行失敗，則整個(gè)處理就算失敗，并恢復(fù)到處里前的狀態(tài)。

BeginTrans和 CommitTrans用于標(biāo)記事務(wù)的開(kāi)始和結(jié)束，在這兩個(gè)之間的語(yǔ)句，就是作為事務(wù)處理的語(yǔ)句。判斷事務(wù)處理是否成功，可通過(guò)連接對(duì)象的Error集合來(lái)實(shí)現(xiàn)，若Error集合的成員個(gè)數(shù)不為0，則說(shuō)明有錯(cuò)誤發(fā)生，事務(wù)處理失敗。Error集合中的每一個(gè)Error對(duì)象，代表一個(gè)錯(cuò)誤信息。

常用SQL語(yǔ)句書(shū)寫(xiě)技巧(轉(zhuǎn))

關(guān)鍵詞： SQL

SQL結(jié)構(gòu)化查詢(xún)字符串的改寫(xiě)，是實(shí)現(xiàn)數(shù)據(jù)庫(kù)查詢(xún)性能提升的最現(xiàn)實(shí)、最有效的手段，有時(shí)甚至是唯一的手段，比如在不允許大幅度修改現(xiàn)有數(shù)據(jù)庫(kù)結(jié)構(gòu)的情況下。

通過(guò)優(yōu)化SQL語(yǔ)句提高查詢(xún)性能的關(guān)鍵是：

l 根據(jù)實(shí)際需求情況，建立合適的索引； l 使用一切可能的方式去利用好索引，避免全表掃描；

l 盡量減少內(nèi)存及數(shù)據(jù)I/O方面的開(kāi)銷(xiāo)

一、建立索引

（一）建立“適當(dāng)”的索引，是快速查詢(xún)的基礎(chǔ)。

索引（index）是除表之外另一重要的、用戶(hù)定義的存儲(chǔ)在物理介質(zhì)上的數(shù)據(jù)結(jié)構(gòu)。當(dāng)根據(jù)索引碼的值搜索數(shù)據(jù)時(shí)，索引提供了對(duì)數(shù)據(jù)的快速訪(fǎng)問(wèn)。事實(shí)上，沒(méi)有索引，數(shù)據(jù)庫(kù)也能根據(jù)SELECT語(yǔ)句成功地檢索到結(jié)果，但隨著表變得越來(lái)越大，使用“適當(dāng)”的索引的效果就越來(lái)越明顯。注意，在這句話(huà)中，我們用了“適當(dāng)”這個(gè)詞，這是因?yàn)椋绻褂盟饕龝r(shí)不認(rèn)真考慮其實(shí)現(xiàn)過(guò)程，索引既可以提高也會(huì)破壞數(shù)據(jù)庫(kù)的工作性能。

索引實(shí)際上是一種特殊的目錄，SQL SERVER提供了兩種索引：

l 聚集索引（clustered index，也稱(chēng)聚類(lèi)索引、簇集索引）

我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱(chēng)為“聚集索引”。

例如：

漢語(yǔ)字典中按拼音查某一個(gè)字，就是使用“聚集索引”，實(shí)際上，你根本用不著查目錄，直接在字典正文里找，就能很快找到需要的漢字（假設(shè)你知道發(fā)音）。

l 非聚集索引（nonclustered index，也稱(chēng)非聚類(lèi)索引、非簇集索引）

我們把目錄純粹是目錄，正文純粹是正文的排序方式稱(chēng)為“非聚集索引”。

例如：

漢語(yǔ)字典中按部首查某一個(gè)字，部首目錄和正文一定要刻意的通過(guò)頁(yè)碼才能聯(lián)系到一起，其順序不是天然一致的。

聚集索引與非聚集索引的最大區(qū)別就在于：聚集索引是對(duì)原數(shù)據(jù)表進(jìn)行排序，因此只要符合索引條件，就能夠直接連續(xù)的讀取數(shù)據(jù)記錄，幾乎可以達(dá)到對(duì)數(shù)據(jù)表的零掃描；而非聚集索引僅僅只是另外建了一張索引表，取數(shù)據(jù)的時(shí)候，從索引表取得結(jié)果后，還需要到指針?biāo)傅臄?shù)據(jù)行讀取相應(yīng)數(shù)據(jù)，因此，在性能上，聚集索引會(huì)大大優(yōu)于非聚集索引。

但是在一張表中，聚集索引只允許一個(gè)，是比較寶貴的，因此要盡可能的用于那些使用頻率最高的索引上。另外，查詢(xún)時(shí)必需要用到索引的起始列，否則索引無(wú)效。另外，起始列也必需是使用頻繁的列，那樣的索引性能才會(huì)達(dá)到最優(yōu)化。

（二）表：何時(shí)應(yīng)使用聚集索引或非聚集索引

動(dòng)作描述 使用聚集索引 使用非聚集索引

列經(jīng)常被分組排序 ○ ○

返回某范圍內(nèi)的數(shù)據(jù) ○

一個(gè)或極少不同值

小數(shù)目的不同值 ○

大數(shù)目的不同值 ○

頻繁更新的列 ○

外鍵列 ○ ○

主鍵列 ○ ○

頻繁修改索引列 ○

（三）索引建立的一些注意項(xiàng)

1、不要把聚集索引浪費(fèi)在主鍵上，除非你只按主鍵查詢(xún)

雖然SQL SERVER默認(rèn)是在主鍵上建立聚集索引的，但實(shí)際應(yīng)用中，這樣做比較浪費(fèi)。通常，我們會(huì)在每個(gè)表中都建立一個(gè)ID列，以區(qū)分每條數(shù)據(jù)，并且這個(gè)ID列是自動(dòng)增大的，步長(zhǎng)一般為1。此時(shí)，如果我們將這個(gè)列設(shè)為主鍵，SQL SERVER會(huì)將此列默認(rèn)為聚集索引。這樣做有好處，就是可以讓您的數(shù)據(jù)在數(shù)據(jù)庫(kù)中按照ID進(jìn)行物理排序，但這樣做實(shí)用價(jià)值不大。

從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據(jù)查詢(xún)要求，迅速縮小查詢(xún)范圍，避免全表掃描。在實(shí)際應(yīng)用中，因?yàn)镮D號(hào)是自動(dòng)生成的，我們并不知道每條記錄的ID 號(hào)，所以我們很難在實(shí)踐中用ID號(hào)來(lái)進(jìn)行查詢(xún)。這就使讓ID號(hào)這個(gè)主鍵作為聚集索引成為一種資源浪費(fèi)。聚集索引相對(duì)與非聚集索引的優(yōu)勢(shì)是很明顯的，而每個(gè)表中只能有一個(gè)聚集索引的規(guī)則，這使得聚集索引變得更加寶貴，應(yīng)該用在其他查詢(xún)頻率高的字段上。其次，讓每個(gè)ID號(hào)都不同的字段作為聚集索引也不符合“大數(shù)目的不同值情況下不應(yīng)建立聚合索引”規(guī)則；當(dāng)然，這種情況只是針對(duì)用戶(hù)經(jīng)常修改記錄內(nèi)容，特別是索引項(xiàng)的時(shí)候會(huì)負(fù)作用，但對(duì)于查詢(xún)速度并沒(méi)有影響。

2、索引的建立要根據(jù)實(shí)際應(yīng)用需求來(lái)進(jìn)行

并非是在任何字段上簡(jiǎn)單地建立索引就能提高查詢(xún)速度。聚集索引建立的規(guī)則大致是“既不能絕大多數(shù)都相同，又不能只有極少數(shù)相同”。舉個(gè)例子，在公文表的收發(fā)日期字段上建立聚合索引是比較合適的。在政務(wù)系統(tǒng)中，我們每天都會(huì)收一些文件，這些文件的發(fā)文日期將會(huì)相同，在發(fā)文日期上建立聚合索引對(duì)性能的提升應(yīng)該是相當(dāng)大的。在群集索引下，數(shù)據(jù)物理上按順序存于數(shù)據(jù)頁(yè)上，重復(fù)值也排列在一起，因而在范圍查找時(shí)，可以先找到這個(gè)范圍的起末點(diǎn)，且只在這個(gè)范圍內(nèi)掃描數(shù)據(jù)頁(yè)，避免了大范圍掃描，提高了查詢(xún)速度。

另一個(gè)相反的例子：比如在雇員表的“性別”列上只有“男”與“女”兩個(gè)不同值，因此就完全沒(méi)必要建立索引。

3、在聚集索引中加入所有需要提高查詢(xún)速度的字段，形成復(fù)合索引

根據(jù)一些實(shí)驗(yàn)的結(jié)果，我們可以得出一些可供參考的結(jié)論：

ü 僅用復(fù)合聚集索引的起始列作為查詢(xún)條件和同時(shí)用到復(fù)合聚集索引的全部列的查詢(xún)，速度是幾乎一樣的，甚至比后者還要快（在查詢(xún)結(jié)果集數(shù)目一樣的情況下）；

ü 僅用復(fù)合聚集索引的非起始列作為查詢(xún)條件的話(huà)，這個(gè)索引是不起任何作用的。

ü 復(fù)合聚集索引的所有列都用上，而且因?yàn)椴樵?xún)條件嚴(yán)格，查詢(xún)結(jié)果少的話(huà)，會(huì)形成“索引覆蓋”，性能可以達(dá)到最優(yōu)。

ü 最重要的一點(diǎn)：無(wú)論是否經(jīng)常使用復(fù)合聚合索引的其他列，其起始列一定要是使用最頻繁的列。

4.根據(jù)實(shí)踐得出的一些其他經(jīng)驗(yàn)，特定情況下有效

ü 用聚合索引比用不是聚合索引的主鍵速度快；

ü 用聚合索引比用一般的主鍵作order by速度快，特別是在小數(shù)據(jù)量情況；

ü 使用聚合索引內(nèi)的時(shí)間段，搜索時(shí)間會(huì)按數(shù)據(jù)占整個(gè)數(shù)據(jù)表的百分比成比例減少，而無(wú)論聚合索引使用了多少個(gè)；

ü 日期列不會(huì)因?yàn)橛蟹置氲妮斎攵鴾p慢查詢(xún)速度；

ü 由于改變一個(gè)表的內(nèi)容，將會(huì)引起索引的變化。頻繁的insert,update,delete語(yǔ)句將導(dǎo)致系統(tǒng)花費(fèi)較大的代價(jià)進(jìn)行索引更新，引起整體性能的下降。一般來(lái)講，在對(duì)查詢(xún)性能的要求高于對(duì)數(shù)據(jù)維護(hù)性能要求時(shí)，應(yīng)該盡量使用索引，否則，就要慎重考慮一下付出的代價(jià)。在某些極端情況下，可先刪除索引，再對(duì)數(shù)據(jù)庫(kù)表更新大量數(shù)據(jù)，最后再重建索引，新建立的索引總是比較好用。

二、編寫(xiě)優(yōu)化的SQL語(yǔ)句，充分利用索引

下面就某些SQL語(yǔ)句的where子句編寫(xiě)中需要注意的問(wèn)題作詳細(xì)介紹。在這些where子句中，即使某些列存在索引，但是由于編寫(xiě)了劣質(zhì)的SQL，系統(tǒng)在運(yùn)行該SQL語(yǔ)句時(shí)也不能使用該索引，而同樣使用全表掃描，這就造成了響應(yīng)速度的極大降低。

SQL語(yǔ)句在提交給數(shù)據(jù)庫(kù)進(jìn)行操作前，都會(huì)經(jīng)過(guò)查詢(xún)分析階段，SQLSERVER內(nèi)置的查詢(xún)優(yōu)化器會(huì)分析查詢(xún)條件的的每個(gè)部分，并判斷這些條件是否符合掃描參數(shù)（SARG）的標(biāo)準(zhǔn)。只有當(dāng)一個(gè)查詢(xún)條件符合SARG的標(biāo)準(zhǔn)，才可以通過(guò)預(yù)先設(shè)置的索引，提升查詢(xún)性能。

SARG的定義：用于限制搜索操作的一種規(guī)范，通常是指一個(gè)特定的匹配，一個(gè)確定范圍內(nèi)的匹配或者兩個(gè)以上條件的AND連接。一般形式如下：

列名 操作符 <常數(shù) 或 變量> 或

<常數(shù) 或 變量> 操作符 列名

列名可以出現(xiàn)在操作符的一邊，而常數(shù)或變量出現(xiàn)在操作符的另一邊。如：

Name=?張三?

價(jià)格>5000

5000<價(jià)格

Name=?張三? and 價(jià)格>5000

如果一個(gè)表達(dá)式不能滿(mǎn)足SARG的形式，那它就無(wú)法限制搜索的范圍了，也就是說(shuō)SQL SERVER必須對(duì)每一行都判斷它是否滿(mǎn)足WHERE子句中的所有條件，既進(jìn)行全表掃描。所以，一個(gè)索引對(duì)于不滿(mǎn)足SARG形式的表達(dá)式來(lái)說(shuō)是無(wú)用的, 如：當(dāng)查詢(xún)條件為“價(jià)格*2 >5000”時(shí)，就無(wú)法利用建立在價(jià)格字段上的索引。

SQLSERVER內(nèi)置了查詢(xún)優(yōu)化器，能將一些條件自動(dòng)轉(zhuǎn)換為符合SARG標(biāo)準(zhǔn)，如：將“價(jià)格*2 >5000” 轉(zhuǎn)換為“價(jià)格 >2500/2 ”，以達(dá)到可以使用索引的目的，但這種轉(zhuǎn)化不是100%可靠的，有時(shí)會(huì)有語(yǔ)義上的損失，有時(shí)轉(zhuǎn)化不了。如果對(duì)“查詢(xún)優(yōu)化器”的工作原理不是特別了解，寫(xiě)出的SQL語(yǔ)句可能不會(huì)按照您的本意進(jìn)行查詢(xún)。所以不能完全依賴(lài)查詢(xún)優(yōu)化器的優(yōu)化，建議大家還是利用自己的優(yōu)化知識(shí)，盡可能顯式的書(shū)寫(xiě)出符合SARG標(biāo)準(zhǔn)的 SQL語(yǔ)句，自行確定查詢(xún)條件的構(gòu)建方式，這樣一方面有利于查詢(xún)分析器分析最佳索引匹配順序，另一方面也有利于今后重讀代碼。

介紹完SARG后，我們?cè)俳Y(jié)合一些實(shí)際運(yùn)用中的例子來(lái)做進(jìn)一步的講解：

1、Like語(yǔ)句是否屬于SARG取決于使用%通配符的樣式

如：name like ?張%?，這就屬于SARG

而：name like ?%張? ,就不屬于SARG

通配符%在字符串首字符的使用會(huì)導(dǎo)致索引無(wú)法使用，雖然實(shí)際應(yīng)用中很難避免這樣用，但還是應(yīng)該對(duì)這種現(xiàn)象有所了解，至少知道此種用法性能是很低下的。

2、“非”操作符不滿(mǎn)足SARG形式，使得索引無(wú)法使用

不滿(mǎn)足SARG形式的語(yǔ)句最典型的情況就是包括非操作符的語(yǔ)句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。

下面是一個(gè)NOT子句的例子：

...where not(status ='valid')

not運(yùn)算符也隱式的包含在另外一些邏輯運(yùn)算符中，比如<>運(yùn)算符。見(jiàn)下例：

...where status <>'invalid';

再看下面這個(gè)例子：

select * from employee where salary<>3000;

對(duì)這個(gè)查詢(xún)，可以改寫(xiě)為不使用not：

select * from employee where salary<3000 or salary>3000;

雖然這兩種查詢(xún)的結(jié)果一樣，但是第二種查詢(xún)方案會(huì)比第一種查詢(xún)方案更快些。第二種查詢(xún)?cè)试S對(duì)salary列使用索引，而第一種查詢(xún)則不能使用索引。

3、函數(shù)運(yùn)算不滿(mǎn)足SARG形式，使得索引無(wú)法使用

例：下列SQL條件語(yǔ)句中的列都建有恰當(dāng)?shù)乃饕?，但?zhí)行速度卻非常慢：

select * from record where substring(card_no,1,4)=′5378′(13秒)

select * from record where amount/30< 1000（11秒）

select * from record where convert(char(10),date,112)=′19991201′（10秒）

分析：

where子句中對(duì)列的任何操作結(jié)果都是在SQL運(yùn)行時(shí)逐列計(jì)算得到的，因此它不得不進(jìn)行全表掃描，而沒(méi)有使用該列上面的索引；如果這些結(jié)果在查詢(xún)編譯時(shí)就能得到，那么就可以被SQL優(yōu)化器優(yōu)化，使用索引，避免表搜索，因此將SQL重寫(xiě)成下面這樣：

select * from record where card_no like ′5378%′（< 1秒）

select * from record where amount < 1000*30（< 1秒）

select * from record where date= ′1999/12/01′（< 1秒）

你會(huì)發(fā)現(xiàn)SQL明顯快很多

4、盡量不要對(duì)建立了索引的字段，作任何的直接處理

select * from employs where first_name + last_name ='beill cliton';

無(wú)法使用索引

改為：

select * from employee where

first_name = substr('beill cliton',1,instr('beill cliton',' ')-1)and

last_name = substr('beill cliton',instr('beill cliton',' ')+1)

則可以使用索引

5、不同類(lèi)型的索引效能是不一樣的，應(yīng)盡可能先使用效能高的

比如：數(shù)字類(lèi)型的索引查找效率高于字符串類(lèi)型，定長(zhǎng)字符串char，nchar的索引效率高于變長(zhǎng)字符串varchar,nvarchar的索引。

應(yīng)該將

where username='張三' and age>20

改進(jìn)為

where age>20 and username='張三'

注意：

此處，SQL的查詢(xún)分析優(yōu)化功能可以做到自動(dòng)重排條件順序，但還是建議預(yù)先手工排列好。

6、盡量不要使用 is null 與 is not null作為查詢(xún)條件

任何包含null值的列都將不會(huì)被包含在索引中，如果某列數(shù)據(jù)中存在空值，那么對(duì)該列建立索引的性能提升是值得懷疑的，尤其是將null作為查詢(xún)條件的一部分時(shí)。建議一方面避免使用is null和is not null, 另一方面不要讓數(shù)據(jù)庫(kù)字段中存在null, 即使沒(méi)有內(nèi)容，也應(yīng)利用缺省值，或者手動(dòng)的填入一個(gè)值，如:?? 空字符串。

7、某些情況下IN 的作用與OR 相當(dāng)，且都不能充分利用索引

例：表stuff有200000行，id_no上有非群集索引，請(qǐng)看下面這個(gè)SQL：

select count(*)from stuff where id_no in(′0′,′1′)（23秒）

where條件中的′in′在邏輯上相當(dāng)于′ or′，所以語(yǔ)法分析器會(huì)將in(′0′,′1′)轉(zhuǎn)化為id_no =′0′ or id_no=′1′來(lái)執(zhí)行。我們期望它會(huì)根據(jù)每個(gè)or子句分別查找，再將結(jié)果相加，這樣可以利用id_no上的索引；但實(shí)際上,它卻采用了“OR策略”，即先取出滿(mǎn)足每個(gè)or子句的行，存入臨時(shí)數(shù)據(jù)庫(kù)的工作表中，再建立唯一索引以去掉重復(fù)行，最后從這個(gè)臨時(shí)表中計(jì)算結(jié)果。因此，實(shí)際過(guò)程沒(méi)有利用id_no 上索引，并且完成時(shí)間還要受tempdb數(shù)據(jù)庫(kù)性能的影響。

實(shí)踐證明，表的行數(shù)越多，工作表的性能就越差，當(dāng)stuff有620000行時(shí)，執(zhí)行時(shí)間會(huì)非常長(zhǎng)！如果確定不同的條件不會(huì)產(chǎn)生大量重復(fù)值，還不如將or子句分開(kāi)：

select count(*)from stuff where id_no=′0′

select count(*)from stuff where id_no=′1′

得到兩個(gè)結(jié)果，再用union作一次加法合算。因?yàn)槊烤涠际褂昧怂饕?，?zhí)行時(shí)間會(huì)比較短，select count(*)from stuff where id_no=′0′

union

select count(*)from stuff where id_no=′1′

從實(shí)踐效果來(lái)看，使用union在通常情況下比用or的效率要高的多，而exist關(guān)鍵字和in關(guān)鍵字在用法上類(lèi)似，性能上也類(lèi)似，都會(huì)產(chǎn)生全表掃描，效率比較低下，根據(jù)未經(jīng)驗(yàn)證的說(shuō)法，exist可能比in要快些。

8、使用變通的方法提高查詢(xún)效率

like關(guān)鍵字支持通配符匹配，但這種匹配特別耗時(shí)。例如：select * from customer where zipcode like “21_ _ _”，即使在zipcode字段上已建立了索引，在這種情況下也可能還是采用全表掃描方式。如果把語(yǔ)句改為：select * from customer where zipcode >“21000”，在執(zhí)行查詢(xún)時(shí)就會(huì)利用索引，大大提高速度。但這種變通是有限制的，不應(yīng)引起業(yè)務(wù)意義上的損失，對(duì)于郵政編碼而言，zipcode like “21_ _ _” 和 zipcode >“21000” 意義是完全一致的。

9、組合索引的高效使用

假設(shè)已在date，place，amount三個(gè)字段上建立了組合索引

select count(*)from record

where date > ′19991201′ and date < ′19991214′ and amount > 2000

(< 1秒)

select date,sum(amount)from record group by date

（11秒）

select count(*)from record

where date > ′19990901′ and place in(′BJ′,′SH′)

（< 1秒）

這是一個(gè)設(shè)置較合理的組合索引。它將date作為前導(dǎo)列，使每個(gè)SQL都可以利用索引，并且在第一和第三個(gè)SQL中形成了索引覆蓋，因而性能達(dá)到了最優(yōu)。如果索引不便于更改，修正SQL中的條件順序以配合索引順序也是可行的。

10、order by按聚集索引列排序效率最高

排序是較耗時(shí)的操作，應(yīng)盡量簡(jiǎn)化或避免對(duì)大型表進(jìn)行排序，如縮小排序的列的范圍，只在有索引的列上排序等等。

我們來(lái)看：（gid是主鍵，fariqi是聚合索引列）

select top 10000 gid,fariqi,reader,title from tgongwen

用時(shí)：196 毫秒。掃描計(jì)數(shù) 1，邏輯讀 289 次，物理讀 1 次，預(yù)讀 1527 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

用時(shí)：4720毫秒。掃描計(jì)數(shù) 1，邏輯讀 41956 次，物理讀 0 次，預(yù)讀 1287 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時(shí)：4736毫秒。掃描計(jì)數(shù) 1，邏輯讀 55350 次，物理讀 10 次，預(yù)讀 775 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

用時(shí)：173毫秒。掃描計(jì)數(shù) 1，邏輯讀 290 次，物理讀 0 次，預(yù)讀 0 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

用時(shí)：156毫秒。掃描計(jì)數(shù) 1，邏輯讀 289 次，物理讀 0 次，預(yù)讀 0 次。

從以上我們可以看出，不排序的速度以及邏輯讀次數(shù)都是和“order by 聚集索引列” 的速度是相當(dāng)?shù)?，但這些都比“order by 非聚集索引列”的查詢(xún)速度是快得多的。

同時(shí)，按照某個(gè)字段進(jìn)行排序的時(shí)候，無(wú)論是正序還是倒序，速度是基本相當(dāng)?shù)摹?/p>

三、關(guān)于節(jié)省數(shù)據(jù)查詢(xún)系統(tǒng)開(kāi)銷(xiāo)方面的措施

1、使用TOP盡量減少取出的數(shù)據(jù)量

TOP是SQL SERVER中用來(lái)提取前幾條或前某個(gè)百分比數(shù)據(jù)的關(guān)鍵詞。

select top 20 gid,fariqi,reader,title from tgongwen order by gid desc select top 60 percent gid,fariqi,reader,title from tgongwen order by gid desc

在實(shí)際的應(yīng)用中，應(yīng)該經(jīng)常利用top 剔除掉不必要的數(shù)據(jù)，只保留必須的數(shù)據(jù)集合。這樣不僅可以減少數(shù)據(jù)庫(kù)邏輯讀的次數(shù)，還能避免不必要的內(nèi)存浪費(fèi)，對(duì)系統(tǒng)性能的提升都是有好處的。

2、字段提取要按照“需多少、提多少”的原則，避免“select *”

這個(gè)舉個(gè)例子：

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時(shí)：4673毫秒

select top 10000 gid,fariqi,title from tgongwen order by gid desc

用時(shí)：1376毫秒

select top 10000 gid,fariqi from tgongwen order by gid desc

用時(shí)：80毫秒

由此看來(lái)，字段大小越大，數(shù)目越多，select所耗費(fèi)的資源就越多，比如取int類(lèi)型的字段就會(huì)比取char的快很多。我們每少提取一個(gè)字段，數(shù)據(jù)的提取速度就會(huì)有相應(yīng)的提升。提升的幅度根據(jù)舍棄的字段的大小來(lái)判斷。

3、count(*)與 count(字段)方法比較

我們來(lái)看一些實(shí)驗(yàn)例子（gid為T(mén)gongwen的主鍵）：

select count(*)from Tgongwen

用時(shí)：1500毫秒

select count(gid)from Tgongwen

用時(shí)：1483毫秒

select count(fariqi)from Tgongwen

用時(shí)：3140毫秒

select count(title)from Tgongwen 用時(shí)：52050毫秒

從以上可以看出，用count(*)和用 count(主鍵)的速度是相當(dāng)?shù)模鴆ount(*)卻比其他任何除主鍵以外的字段匯總速度要快，而且字段越長(zhǎng)，匯總速度就越慢。如果用 count(*)，SQL SERVER會(huì)自動(dòng)查找最小字段來(lái)匯總。當(dāng)然，如果您直接寫(xiě)count(主鍵)將會(huì)來(lái)的更直接些。

4、有嵌套查詢(xún)時(shí)，盡可能在內(nèi)層過(guò)濾掉數(shù)據(jù)

如果一個(gè)列同時(shí)在主查詢(xún)和where子句中出現(xiàn)，很可能當(dāng)主查詢(xún)中的列值改變之后，子查詢(xún)必須重新查詢(xún)一次。而且查詢(xún)嵌套層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢(xún)。如果子查詢(xún)不可避免，那么要在子查詢(xún)中過(guò)濾掉盡可能多的行。

5、多表關(guān)聯(lián)查詢(xún)時(shí)，需注意表順序，并盡可能早的過(guò)濾掉數(shù)據(jù)

在使用Join進(jìn)行多表關(guān)聯(lián)查詢(xún)時(shí)候，應(yīng)該使用系統(tǒng)開(kāi)銷(xiāo)最小的方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表，并注意優(yōu)化表順序；說(shuō)的簡(jiǎn)單一點(diǎn)，就是盡可能早的將之后要做關(guān)聯(lián)的數(shù)據(jù)量降下來(lái)。

一般情況下，sqlserver 會(huì)對(duì)表的連接作出自動(dòng)優(yōu)化。例如：

select name,no from A

join B on A.id=B.id

join C on C.id=A.id

where name='wang'

盡管A表在From中先列出，然后才是B,最后才是C。但sql server可能會(huì)首先使用c表。它的選擇原則是相對(duì)于該查詢(xún)限制為單行或少數(shù)幾行，就可以減少在其他表中查找的總數(shù)據(jù)量。絕大多數(shù)情況下，sql server 會(huì)作出最優(yōu)的選擇，但如果你發(fā)覺(jué)某個(gè)復(fù)雜的聯(lián)結(jié)查詢(xún)速度比預(yù)計(jì)的要慢，就可以使用SET FORCEPLAN語(yǔ)句強(qiáng)制sql server按照表出現(xiàn)順序使用表。如上例加上：SET FORCEPLAN ON…….SET FORCEPLAN OFF 表的執(zhí)行順序?qū)?huì)按照你所寫(xiě)的順序執(zhí)行。在查詢(xún)分析器中查看2種執(zhí)行效率，從而選擇表的連接順序。SET FORCEPLAN的缺點(diǎn)是只能在存儲(chǔ)過(guò)程中使用。

小結(jié)：

? 聚集索引比較寶貴，應(yīng)該用在查詢(xún)頻率最高的地方；

? 在數(shù)據(jù)為“既不是絕大多數(shù)相同，也不是極少數(shù)相同”狀態(tài)時(shí)，最能發(fā)揮聚集索引的潛力；

? 復(fù)合索引的設(shè)置和使用要注意保持順序一致；

? 條件子句的表達(dá)式最好符合SARG規(guī)范，是可利用索引的；

? 任何對(duì)列的操作都導(dǎo)致全表掃描，如數(shù)據(jù)庫(kù)函數(shù)、計(jì)算表達(dá)式等，查詢(xún)時(shí)應(yīng)盡可能將操作移至等號(hào)的某一邊；

? 要注意含有null值時(shí)，是不能充分利用索引的；

? exist, in、or等子句常會(huì)使索引失效；

如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開(kāi)，再用union拼合；

? 排序時(shí)應(yīng)充分利用帶索引的字段；

? 盡可能早，快的過(guò)濾掉無(wú)用的數(shù)據(jù)，只將必須的數(shù)據(jù)帶到后續(xù)的操作中去

從前面講敘的內(nèi)容可以看出，SQL語(yǔ)句優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用分析優(yōu)化器可以識(shí)別的SARG規(guī)范語(yǔ)句，充份利用索引，減少數(shù)據(jù)的I/O次數(shù)，盡量避免全表掃描的發(fā)生。

以上內(nèi)容有些是指導(dǎo)性的理論原則，有些是實(shí)際摸索的經(jīng)驗(yàn)，大家在使用時(shí)應(yīng)靈活處理，根據(jù)實(shí)際情況，選擇合適的方法。本文中列舉的實(shí)驗(yàn)數(shù)據(jù)僅作比對(duì)用，不具備普遍意義。大家在實(shí)際項(xiàng)目中，應(yīng)充分利用性能監(jiān)測(cè)和分析工具（如SQLSERVER帶的相關(guān)工具）來(lái)檢驗(yàn)自己的優(yōu)化效果。

此外，還有很重要的一點(diǎn)要提醒大家，同樣復(fù)雜的數(shù)據(jù)操作，在SQLSERVER數(shù)據(jù)庫(kù)級(jí)別完成的代價(jià)要遠(yuǎn)遠(yuǎn)小于在應(yīng)用端用程序代碼完成的代價(jià)，所以建議大家全面，深入的學(xué)習(xí)SQL語(yǔ)法中重要關(guān)鍵字的應(yīng)用，如：Group By，Having等，盡量把數(shù)據(jù)操作任務(wù)放在數(shù)據(jù)庫(kù)系統(tǒng)中完成。數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的性能優(yōu)化是一個(gè)復(fù)雜的過(guò)程，上述這些只是在SQL語(yǔ)句層次的一種體現(xiàn)，深入研究還會(huì)涉及數(shù)據(jù)庫(kù)層的資源配置、網(wǎng)絡(luò)層的流量控制以及操作系統(tǒng)層的總體設(shè)計(jì)等等，這些將在以后的文章中詳細(xì)論述

如何獲得sql查詢(xún)侯滿(mǎn)足條件的記錄數(shù)

select kch into :ls_kch from cj_cjb where kch = :s_kch and kscj < 60 using ltr;

請(qǐng)問(wèn)如何知道符合條件kch的紀(jì)錄數(shù)。SELECT KCH, COUNT(*)INTO :ls_kch, :ll_count cj_cjb WHERE kch = :s_kch AND kscj < 60 UAING ltr;

ll_count

SRY少了個(gè)FROM

執(zhí)行完后看：

sqlca.sqlnrows

呵呵select只能返回一條記錄啊，要是有多條記錄符合條件pb是會(huì)報(bào)錯(cuò)的。

are2000(烏鴉與土狗)說(shuō)的對(duì)呀

只是想知道符合條件的紀(jì)錄數(shù)，直接這樣寫(xiě)：

SELECT COUNT(*)

INTO :ll_count

from cj_cjb

WHERE kch = :s_kch

AND kscj < 60

UAING sqlca;

樓上說(shuō)的對(duì)啊

呵呵

多行的話(huà)要用游標(biāo)的

用游標(biāo)取的時(shí)候,在for 循環(huán)中加個(gè)變量就可以知道條數(shù),數(shù)據(jù)也取出來(lái)了~~

如何查詢(xún)SQLSERVER數(shù)據(jù)庫(kù)DB1中所有表的記錄數(shù)？

use DB1

if object_id('tempdb..##')is not null drop table ##

select cast(null as sysname)as 表名稱(chēng), 1 as 記錄數(shù) into ## where 1 = 0

declare @TableName sysname

declare testcur cursor for select [name] from sysobjects where xtype ='U' order by [name]

open testcur

fetch next from testcur into @TableName

while @@fetch_status = 0

begin

exec('insert into ## select ''' + @TableName + ''',(select count(1)from ' + @TableName + ')')

fetch next from testcur into @TableName end

close testcur

deallocate testcur

select * from ##

drop table ##

Pubwin EP數(shù)據(jù)備份:

SQL server2000版本重裝前，需要備份哪些數(shù)據(jù)

SQL server2000版本重裝前，需要備份數(shù)據(jù)庫(kù)文件，文件路徑為： HintsoftPubwinServerdatabase 下的“l(fā)ocal_Data.MDF”和“l(fā)ocal_log.LDF”

除了備份數(shù)據(jù)庫(kù)文件以外還有就是需要備份數(shù)據(jù)庫(kù)的備份文件 其路徑為 ： HintsoftPubwinServerappServserverwebappsNetCafebackuplongtermdata 還有一個(gè)方面需要注意的是 網(wǎng)吧的會(huì)員是否存在頭像與身份證證件照 如有的話(huà) 請(qǐng)備份

HintsoftPubwinServerappServserverwebappsNetCafeheadphotos HintsoftPubwinServerappServserverwebappsNetCafephotos ； 備份好后，重新安裝將備份文件放置相應(yīng)的目錄即可。如何防止遠(yuǎn)程修改數(shù)據(jù)庫(kù)

一、屏蔽1433端口（以win2000為例）： 設(shè)置安全策略：

“控制面板”—〉“管理工具”—〉“本地安全策略”

選擇IP安全策略—〉創(chuàng)建IP安全策略—〉建立名稱(chēng)—〉“激活默認(rèn)響應(yīng)規(guī)則”下一步—〉初始身份驗(yàn)證方法選擇“win2000默認(rèn)（V5）”—〉彈出的警告界面直接確認(rèn)—〉完成建立安全策略。

選擇你新建的策略—〉屬性—〉添加—〉選擇“此規(guī)則不指定隧道” —〉網(wǎng)絡(luò)類(lèi)型選擇“所有網(wǎng)絡(luò)連接”—〉身份驗(yàn)證方法“win2000默認(rèn)（V5）” —〉彈出的警告界面直接確認(rèn)—〉“所有IP通訊”—〉篩選器選擇“要求安全設(shè)置”—〉繼續(xù)下一步完成選中“所有 IP 通訊”—〉點(diǎn)“編輯”按鈕，打開(kāi)“IP篩選器列表”—〉繼續(xù)點(diǎn)“編輯”按鈕，打開(kāi)“篩選器 屬性” —〉在“尋址”中，源地址選擇“任何IP地址”，目的地址選擇“我的IP地址”，同時(shí)選中“鏡像”—〉在“協(xié)議”中，協(xié)議選擇“TCP”，設(shè)置協(xié)議端口為 “從任意端口”到“到此端口：1433” —〉確定，為了安全起見(jiàn)，最好再新建一個(gè)IP篩選器屏蔽1434端口。

完成上面配置后，在剛配置的策略點(diǎn)擊右鍵，選擇指派，完成后重新啟動(dòng)機(jī)器。如何驗(yàn)證數(shù)據(jù)庫(kù)的1433已經(jīng)不能連接？

1）局域網(wǎng)內(nèi)找一個(gè)機(jī)器（非本機(jī)）安裝企業(yè)管理，添加注冊(cè)剛剛配制過(guò)安全策略的服務(wù)器，應(yīng)該是那個(gè)等待注冊(cè)的畫(huà)面，狀態(tài)中顯示：“正在驗(yàn)證注冊(cè)信息”或拒絕連接或服務(wù)未開(kāi)啟的提示。

2）局域網(wǎng)內(nèi)找一個(gè)機(jī)器（非本機(jī)），在dos控制臺(tái)下，輸入telnet EP服務(wù)器IP 1433 如果安全策略應(yīng)用成功的話(huà)，應(yīng)該不能夠連接，會(huì)出現(xiàn)如下的話(huà)：正在連接到xxxxxxx...無(wú)法打開(kāi)到主機(jī)的連接 在端口 1433 : 連接失敗。如果應(yīng)用安全策略失敗，則能夠連接成功。

二、關(guān)閉不安全的服務(wù)

第一步只是屏蔽了其它機(jī)器連接數(shù)據(jù)庫(kù)的端口，但是操作系統(tǒng)本身還存在一些漏洞，這些漏洞同樣會(huì)導(dǎo)致數(shù)據(jù)庫(kù)不安全?？梢赃\(yùn)行services.msc進(jìn)入本地服務(wù)管理，禁用WMI和Server服務(wù)（屏蔽WMI和 IPC共享漏洞），但是有些網(wǎng)吧需要用到Server服務(wù)的部分功能，所以對(duì)于Server服務(wù)，可以使用以下兩種較為靈活的方法來(lái)屏蔽： 1）批處理自啟動(dòng)法：

打開(kāi)記事本，輸入以下內(nèi)容（記得每行最后要回車(chē)）： net share ipc$ /delete net share admin$ /delete net share c$ /delete net share d$ /delete net share e$ /delete ……（你有幾個(gè)硬盤(pán)分區(qū)就寫(xiě)幾行這樣的命令）

保存為NotShare.bat（注意后綴?。缓蟀堰@個(gè)批處理文件拖到“程序”→“啟動(dòng)”項(xiàng)，這樣每次開(kāi)機(jī)就會(huì)運(yùn)行它，也就是通過(guò)net命令關(guān)閉共享。

如果哪一天你需要開(kāi)啟某個(gè)或某些共享，只要重新編輯這個(gè)批處理文件即可（把相應(yīng)的那個(gè)命令行刪掉）。2）注冊(cè)表改鍵值法 “ 開(kāi)始”→“運(yùn)行”輸入“regedit”確定后，打開(kāi)注冊(cè)表編輯器，找到“HKEY_LOCAL_MACHINESYSTEM CurrentControlSetServiceslanmanserverparameters”項(xiàng)，雙擊右側(cè)窗口中的 “AutoShareServer”項(xiàng)將鍵值由1改為0，這樣就能關(guān)閉硬盤(pán)各分區(qū)的共享。如果沒(méi)有AutoShareServer項(xiàng)，可自己新建一個(gè)再改鍵值。然后還是在這一窗口下再找到“AutoShareWks”項(xiàng)，也把鍵值由1改為0，關(guān)閉admin$共享。最后到 “HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsa”項(xiàng)處找到 “restrictanonymous”，將鍵值設(shè)為1，關(guān)閉IPC$共享。

PUBWIN后臺(tái)網(wǎng)頁(yè)應(yīng)該映射那個(gè)端口? 8443，443

sql server多表關(guān)聯(lián)update

UPDATE Tab1 SET a.Name = b.Name FROM Tab1 a,Tab2 b WHERE a.ID = b.ID

批量insert數(shù)據(jù)

insert into student(sno,sname,ssex,sage,sdept)select '95001','李勇','男','20','cs' union select '95002','劉晨','女','19','is' union select '95003','王敏','女','18','ma' union select '95004','張立','男','19','is'

第二篇：精妙SQL語(yǔ)句收集

SQL編程經(jīng)典-精妙SQL語(yǔ)句收集

一、基礎(chǔ)

1、說(shuō)明：創(chuàng)建數(shù)據(jù)庫(kù)

CREATE DATABASE database-name

2、說(shuō)明：刪除數(shù)據(jù)庫(kù)

drop database dbname

3、說(shuō)明：備份sql server---創(chuàng)建 備份數(shù)據(jù)的 device USE master

EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:mssql7backupMyNwind_1.dat’

---開(kāi)始 備份

BACKUP DATABASE pubs TO testBack

4、說(shuō)明：創(chuàng)建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根據(jù)已有的表創(chuàng)建新表：

A：create table tab_new like tab_old(使用舊表創(chuàng)建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、說(shuō)明：刪除新表

drop table tabname

6、說(shuō)明：增加一個(gè)列

Alter table tabname add column col type 注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類(lèi)型也不能改變，唯一能改變的是增加varchar類(lèi)型的長(zhǎng)度。

7、說(shuō)明：添加主鍵： Alter table tabname add primary key(col)說(shuō)明：刪除主鍵： Alter table tabname drop primary key(col)

8、說(shuō)明：創(chuàng)建索引：create [unique] index idxname on tabname(col….)

刪除索引：drop index idxname

注：索引是不可更改的，想更改必須刪除重新建。

9、說(shuō)明：創(chuàng)建視圖：create view viewname as select statement 刪除視圖：drop view viewname

10、說(shuō)明：幾個(gè)簡(jiǎn)單的基本的sql語(yǔ)句

選擇：select * from table1 where 范圍

插入：insert into table1(field1,field2)values(value1,value2)刪除：delete from table1 where 范圍

更新：update table1 set field1=value1 where 范圍

查找：select * from table1 where field1 like ’%value1%’---like的語(yǔ)法很精妙，查資料!排序：select * from table1 order by field1,field2 [desc] 總數(shù)：select count as totalcount from table1 求和：select sum(field1)as sumvalue from table1 平均：select avg(field1)as avgvalue from table1 最大：select max(field1)as maxvalue from table1 最?。簊elect min(field1)as minvalue from table1

11、說(shuō)明：幾個(gè)高級(jí)查詢(xún)運(yùn)算詞

A： UNION 運(yùn)算符

UNION 運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)（即 UNION ALL），不消除重復(fù)行。兩種情況下，派生表的每一行不是來(lái)自 TABLE1 就是來(lái) 自 TABLE2。

B： EXCEPT 運(yùn)算符

EXCEPT 運(yùn)算符通過(guò)包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí)(EXCEPT ALL)，不消除重復(fù)行。

C： INTERSECT 運(yùn)算符

INTERSECT 運(yùn)算符通過(guò)只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 INTERSECT 一起使用時(shí)(INTERSECT ALL)，不消除重復(fù)行。

注：使用運(yùn)算詞的幾個(gè)查詢(xún)結(jié)果行必須是一致的。

12、說(shuō)明：使用外連接 A、left outer join：

左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。

C：full outer join：

全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。

二、提升

1、說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b)(Access可用)法一：select * into b from a where 1<>1 法二：select top 0 * into b from a

2、說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b)(Access可用)insert into b(a, b, c)select d,e,f from b;

3、說(shuō)明：跨數(shù)據(jù)庫(kù)之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑)(Access可用)insert into b(a, b, c)select d,e,f from b in ‘具體數(shù)據(jù)庫(kù)’ where 條件

例子：..from b in ’“&Server.MapPath(”.“)&”data.mdb“ &”’ where..4、說(shuō)明：子查詢(xún)(表名1：a 表名2：b)

select a,b,c from a where a IN(select d from b)或者: select a,b,c from a where a IN(1,2,3)

5、說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

6、說(shuō)明：外連接查詢(xún)(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、說(shuō)明：在線(xiàn)視圖查詢(xún)(表名1：a)

select * from(SELECT a,b,c FROM a)T where t.a > 1;

8、說(shuō)明：between的用法,between限制查詢(xún)數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 數(shù)值1 and 數(shù)值2

9、說(shuō)明：in 的使用方法

select * from table1 where a [not] in(‘值1’,’值2’,’值4’,’值6’)

10、說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息

delete from table1 where not exists(select * from table2 where table1.field1=table2.field1)

11、說(shuō)明：四表聯(lián)查問(wèn)題：

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、說(shuō)明：日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff(’minute’,f開(kāi)始時(shí)間,getdate())>5

13、說(shuō)明：一條sql 語(yǔ)句搞定數(shù)據(jù)庫(kù)分頁(yè)

select top 10 b.* from(select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc)a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段

14、說(shuō)明：前10條記錄

select top 10 * form table1 where 范圍

15、說(shuō)明：選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類(lèi)似這樣的用法可以用于**每月排行榜,每月熱銷(xiāo)產(chǎn)品分析,按科目成績(jī)排名,等等.)

select a,b,c from tablename ta where a=(select max(a)from tablename tb where tb.b=ta.b)

16、說(shuō)明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表

(select a from tableA)except(select a from tableB)except(select a from tableC)

17、說(shuō)明：隨機(jī)取出10條數(shù)據(jù)

select top 10 * from tablename order by newid()

18、說(shuō)明：隨機(jī)選擇記錄

select newid()

19、說(shuō)明：刪除重復(fù)記錄

Delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)

20、說(shuō)明：列出數(shù)據(jù)庫(kù)里所有的表名 select name from sysobjects where type=’U’

21、說(shuō)明：列出表里的所有的

select name from syscolumns where id=object_id(’TableName’)

22、說(shuō)明：列示type、vender、pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn)多重選擇，類(lèi)似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 tablename group by type 顯示結(jié)果：

type vender pcs 電腦 A 1 電腦 A 1 光盤(pán) B 2 光盤(pán) A 2 手機(jī) B 3 手機(jī) C 3

23、說(shuō)明：初始化表table1

TRUNCATE TABLE table1

24、說(shuō)明：選擇從10到15的記錄

select top 5 * from(select top 15 * from table order by id asc)table_別名 order by id desc

三、技巧1、1=1，1=2的使用，在SQL語(yǔ)句組合時(shí)用的較多

“where 1=1” 是表示選擇全部 “where 1=2”全部不選，如：

if @strWhere!=’’

begin

set @strSQL = ’select count(*)as Total from *’ + @tblName + ’+ where ’ + @strWhere end else begin

set @strSQL = ’select count(*)as Total from *’ + @tblName + ’+’ end

我們可以直接寫(xiě)成

set @strSQL = ’select count(*)as Total from *’ + @tblName + ’+ where 1=1 安定 ’+ @strWhere

2、收縮數(shù)據(jù)庫(kù)--重建索引

DBCC REINDEX

DBCC INDEXDEFRAG--收縮數(shù)據(jù)和日志

DBCC SHRINKDB DBCC SHRINKFILE

3、壓縮數(shù)據(jù)庫(kù)

dbcc shrinkdatabase(dbname)

4、轉(zhuǎn)移數(shù)據(jù)庫(kù)給新用戶(hù)以已存在用戶(hù)權(quán)限

exec sp_change_users_login ’update_one’,’newname’,’oldname’

go

5、檢查備份集

RESTORE VERIFYONLY from disk=’E:dvbbs.bak’

6、修復(fù)數(shù)據(jù)庫(kù)

ALTER DATABASE [dvbbs] SET SINGLE_USER GO

DBCC CHECKDB(’dvbbs’,repair_allow_data_loss)WITH TABLOCK GO

ALTER DATABASE [dvbbs] SET MULTI_USER GO

7、日志清除 SET NOCOUNT ON

DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT

USE tablename--要操作的數(shù)據(jù)庫(kù)名

SELECT @LogicalFileName = ’tablename_log’,--日志文件名

@MaxMinutes = 10,--Limit on time allowed to wrap log.@NewSize = 1--你想設(shè)定的日志文件的大小(M)

--Setup / initialize

DECLARE @OriginalSize int SELECT @OriginalSize = size FROM sysfiles

WHERE name = @LogicalFileName

SELECT ’Original Size of ’ + db_name()+ ’ LOG is ’ +

CONVERT(VARCHAR(30),@OriginalSize)+ ’ 8K pages or ’ + CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+ ’MB’

FROM sysfiles

WHERE name = @LogicalFileName CREATE TABLE DummyTrans

(DummyColumn char(8000)not null)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)SELECT @StartTime = GETDATE()，@TruncLog = ’BACKUP LOG ’ + db_name()+ ’ WITH TRUNCATE_ONLY’

DBCC SHRINKFILE(@LogicalFileName, @NewSize)EXEC(@TruncLog)

--Wrap the log if necessary.WHILE @MaxMinutes > DATEDIFF(mi, @StartTime, GETDATE())--time has not expired AND @OriginalSize =(SELECT size FROM sysfiles WHERE name = @LogicalFileName)AND(@OriginalSize * 8 /1024)> @NewSize BEGIN--Outer loop.SELECT @Counter = 0

WHILE((@Counter < @OriginalSize / 16)AND(@Counter < 50000))BEGIN--update

INSERT DummyTrans VALUES(’Fill Log’)DELETE DummyTrans

SELECT @Counter = @Counter + 1 END

EXEC(@TruncLog)END

SELECT ’Final Size of ’ + db_name()+ ’ LOG is ’ + CONVERT(VARCHAR(30),size)+ ’ 8K pages or ’ + CONVERT(VARCHAR(30),(size*8/1024))+ ’MB’ FROM sysfiles

WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF

8、說(shuō)明：更改某個(gè)表

exec sp_changeobjectowner ’tablename’,’dbo’

9、存儲(chǔ)更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128)AS

DECLARE @Name as NVARCHAR(128)DECLARE @Owner as NVARCHAR(128)

DECLARE @OwnerName as NVARCHAR(128)

DECLARE curObject CURSOR FOR select ’Name’ = name，’Owner’ = user_name(uid)from sysobjects

where user_name(uid)=@OldOwner order by name

OPEN curObject

FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0)BEGIN

if @Owner=@OldOwner begin

set @OwnerName = @OldOwner + ’.’ + rtrim(@Name)exec sp_changeobjectowner @OwnerName, @NewOwner end

--select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner END

close curObject

deallocate curObject GO

10、SQL SERVER中直接循環(huán)寫(xiě)入數(shù)據(jù)

declare @i int set @i=1 while @i<30 begin

insert into test(userid)values(@i)set @i=@i+1 end

第三篇：Oracle SQL精妙SQL語(yǔ)句講解

SQL*PLUS界面：

登錄：輸入SQLPLUS回車(chē)；輸入正確的ORACLE用戶(hù)名并回車(chē)；輸入用戶(hù)口令并回車(chē)，顯示提示符：SQL>

退出：輸入EXIT即可。

2）命令的編輯與運(yùn)行：

在命令提示符后輸入SQL命令并運(yùn)行，以分號(hào)結(jié)束輸入；以斜杠結(jié)束輸入；以空行結(jié)束輸入；

利用SQL緩沖區(qū)進(jìn)行PL/SQL塊的編輯和運(yùn)行；

利用命令文件進(jìn)行PL/SQL塊的編輯和運(yùn)行。

數(shù)據(jù)庫(kù)查詢(xún)

用SELECT語(yǔ)句從表中提取查詢(xún)數(shù)據(jù)。語(yǔ)法為

SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

說(shuō)明：SELECT子句用于指定檢索數(shù)據(jù)庫(kù)的中哪些列，F(xiàn)ROM子句用于指定從哪一個(gè)表或視圖中檢索數(shù)據(jù)。

SELECT中的操作符及多表查詢(xún)WHERE子句。(LIKE,IS,…)

WHERE子句中的條件可以是一個(gè)包含等號(hào)或不等號(hào)的條件表達(dá)式，也可以是一個(gè)含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比較運(yùn)算符的條件式，還可以是由單一的條件表達(dá)通過(guò)邏輯運(yùn)算符組合成復(fù)合條件。

ORDER BY 子句

ORDER BY 子句使得SQL在顯示查詢(xún)結(jié)果時(shí)將各返回行按順序排列，返回行的排列順序由ORDER BY 子句指定的表達(dá)式的值確定。

連接查詢(xún)

利用SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)查詢(xún)時(shí)，可以把多個(gè)表、視圖的數(shù)據(jù)結(jié)合起來(lái)，使得查詢(xún)結(jié)果的每一行中包含來(lái)自多個(gè)表達(dá)式或視圖的數(shù)據(jù)，這種操作被稱(chēng)為連接查詢(xún)。

連接查詢(xún)的方法是在SELECT命令的FROM子句中指定兩個(gè)或多個(gè)將被連接查詢(xún)的表或視圖，并且在WHERE子句告訴ORACLE如何把多個(gè)表的數(shù)據(jù)進(jìn)行合并。根據(jù)WHERE子句中的條件表達(dá)式是等還是不等式，可以把連接查詢(xún)分為等式連接和不等式連接。

子查詢(xún)

如果某一個(gè)SELECT命令（查詢(xún)1）出現(xiàn)在另一個(gè)SQL命令（查詢(xún)2）的一個(gè)子句中，則稱(chēng)查詢(xún)1是查詢(xún)2的子查詢(xún)。

基本數(shù)據(jù)類(lèi)型（NUMBER,VARCHAR2,DATE）O

RACEL支持下列內(nèi)部數(shù)據(jù)類(lèi)型：

VARCHAR2 變長(zhǎng)字符串，最長(zhǎng)為2000字符。

NUMBER 數(shù)值型。

LONG 變長(zhǎng)字符數(shù)據(jù)，最長(zhǎng)為2G字節(jié)。

DATE 日期型。

RAW 二進(jìn)制數(shù)據(jù)，最長(zhǎng)為255字節(jié)。

LONG RAW 變長(zhǎng)二進(jìn)制數(shù)據(jù)，最長(zhǎng)為2G字節(jié)。

ROWID 二六進(jìn)制串，表示表的行的唯一地址。

CHAR 定長(zhǎng)字符數(shù)據(jù)，最長(zhǎng)為255。

常用函數(shù)用法：

一個(gè)函數(shù)類(lèi)似于一個(gè)算符，它操作數(shù)據(jù)項(xiàng)，返回一個(gè)結(jié)果。函數(shù)在格式上不同于算符，它個(gè)具有變?cè)?，可操?個(gè)、一個(gè)、二個(gè)或多個(gè)變?cè)?，形式為?/p>

函數(shù)名（變?cè)?，變?cè)?，…?/p>

函數(shù)具有下列一般類(lèi)形：

單行函數(shù)

分組函數(shù)

單行函數(shù)對(duì)查詢(xún)的表或視圖的每一行返回一個(gè)結(jié)果行。它有數(shù)值函數(shù)，字符函數(shù)，日期函數(shù)，轉(zhuǎn)換函數(shù)等。

分組函數(shù)返回的結(jié)果是基于行組而不是單行，所以分組函數(shù)不同于單行函數(shù)。在許多分組函數(shù)中可有下列選項(xiàng)：

DISTRNCT 該選項(xiàng)使分組函數(shù)只考慮變?cè)磉_(dá)式中的不同值。

ALL該選項(xiàng)使分組函數(shù)考慮全部值，包含全部重復(fù)。

全部分組函數(shù)（除COUNT（*）外）忽略空值。如果具有分組函數(shù)的查詢(xún)，沒(méi)有返回行或只有空值（分組函數(shù)的變?cè)≈档男校?，則分組函數(shù)返回空值。

5、數(shù)據(jù)操縱語(yǔ)言命令：

數(shù)據(jù)庫(kù)操縱語(yǔ)言（DML）命令用于查詢(xún)和操縱模式對(duì)象中的數(shù)據(jù)，它不隱式地提交當(dāng)前事務(wù)。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面簡(jiǎn)單介紹一下：

1)UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

例如：S QL>UPDATE EMP

SET JOB =’MANAGER’

WHERE ENAME=’MAPTIN’;

SQL >SELECT * FROM EMP;

UPDATE子句指明了要修改的數(shù)據(jù)庫(kù)是EMP,并用WHERE子句限制了只對(duì)名字(ENAME)為’MARTIN’的職工的數(shù)據(jù)進(jìn)行修改,SET子句則說(shuō)明修改的方式,即把’MARTION’的工作名稱(chēng)(JOB)改為’MARAGER’.2)INSERT INTO tablename {column1,column2,…} VALUES {expression1,expression2,…};

例如：SQL>SELECT INTO DEPT（DNAME，DEPTNO）

VALUES（‘ACCOUNTING’，10）

3)DELETE FROM tablename WHERE {conditions};

例如：SQL>DELETE FROM EMP

WHERE EMPNO = 7654;

DELETE命令刪除一條記錄,而且DELETE命令只能刪除整行,而不能刪除某行中的部分?jǐn)?shù)據(jù).4）事務(wù)控制命令

提交命令(COMMIT):可以使數(shù)據(jù)庫(kù)的修改永久化.設(shè)置AUTOCOMMIT為允許狀態(tài):SQL >SET AUTOCOMMIT ON;

回滾命令(ROLLBACK):消除上一個(gè)COMMIT命令后的所做的全部修改,使得數(shù)據(jù)庫(kù)的內(nèi)容恢復(fù)到上一個(gè)COMMIT執(zhí)行后的狀態(tài).使用方法是:

SQL>ROLLBACK;

創(chuàng)建表、視圖、索引、同義詞、用戶(hù)。、表是存儲(chǔ)用戶(hù)數(shù)據(jù)的基本結(jié)構(gòu)。

建立表主要指定義下列信息：

列定義

完整性約束

表所在表空間

存儲(chǔ)特性

可選擇的聚集

從一查詢(xún)獲得數(shù)據(jù)

語(yǔ)法如下：CREATE TABLE tablename

(column1 datatype [DEFAULT expression] [constraint], column1 datatype [DEFAULT expression] [constraint], ……）

[STORAGE子句] [其他子句…]；

例如：

SQL>CREATE TABLE NEW_DEPT(DPTNO NUMBER(2), DNAME CHAR(6), LOC CHAR(13);

更改表作用：

增加列

增加完整性約束

重新定義列（數(shù)據(jù)類(lèi)型、長(zhǎng)度、缺省值）

修改存儲(chǔ)參數(shù)或其它參數(shù)

使能、使不能或刪除一完整性約束或觸發(fā)器

顯式地分配一個(gè)范圍

2）、視圖

視圖是一個(gè)邏輯表，它允許操作者從其它表或視圖存取數(shù)據(jù)，視圖本身不包含數(shù)據(jù)。視圖所基于的表稱(chēng)為基表。

引入視圖有下列作用：

提供附加的表安全級(jí)，限制存取基表的行或/和列集合。

隱藏?cái)?shù)據(jù)復(fù)雜性。

為數(shù)據(jù)提供另一種觀點(diǎn)。

促使ORACLE的某些操作在包含視圖的數(shù)據(jù)庫(kù)上執(zhí)行，而不在另一個(gè)數(shù)據(jù)庫(kù)上執(zhí)行。

3）、索引

索引是種數(shù)據(jù)庫(kù)對(duì)象。對(duì)于在表或聚集的索引列上的每一值將包含一項(xiàng)，為行提供直接的快速存取。在下列情況ORACLE可利用索引改進(jìn)性能：

按指定的索引列的值查找行。

按索引列的順序存取表。

建立索引： CREATE [UNIQUE] INDEX indexname ON tablename(column ,。。)；

例如：SQL>CREAT INDEX IC_EMP

ON CLUSTER EMPLOYEE

4）、同義詞

同義詞：為表、視圖、序列、存儲(chǔ)函數(shù)、包、快照或其它同義詞的另一個(gè)名字。使用同義詞為了安全和方便。對(duì)一對(duì)象建立同義詞可有下列好處：

引用對(duì)象不需指出對(duì)象的持有者。

引用對(duì)象不需指出它所位于的數(shù)據(jù)庫(kù)。

為對(duì)象提供另一個(gè)名字。

建立同義詞：

CREATE SYNONYM symnon_name FOR [username.]tablename;

例如：CREAT PUBLIC SYNONYM EMP

FOR SCOTT.EMP @SALES

5）、用戶(hù)

CREATE USER username IDENTIFIED BY password;

例如：SQL>CREATE USER SIDNEY

IDENTIFIED BY CARTON;

Oracle擴(kuò)展PL/SQL簡(jiǎn)介

PL/SQL概述。

PL/SQL是Oracle對(duì)SQL規(guī)范的擴(kuò)展，是一種塊結(jié)構(gòu)語(yǔ)言，即構(gòu)成一個(gè)PL/SQL程序的基本單位（過(guò)程、函數(shù)和無(wú)名塊）是邏輯塊，可包含任何數(shù)目的嵌套了快。這種程序結(jié)構(gòu)支持逐步求精方法解決問(wèn)題。一個(gè)塊（或子塊）將邏輯上相關(guān)的說(shuō)明和語(yǔ)句組合在一起，其形式為：

DECLARE

---說(shuō)明

BEGIN

---語(yǔ)句序列

EXCEPTION

---例外處理程序

END；

它有以下優(yōu)點(diǎn):

支持SQL;

生產(chǎn)率高;

性能好;

可稱(chēng)植性;

與ORACLE集成.PL/SQL體系結(jié)構(gòu)

PL/SQL運(yùn)行系統(tǒng)是種技術(shù)，不是一種獨(dú)立產(chǎn)品，可認(rèn)為這種技術(shù)是PL/SQL塊和子程序的一種機(jī)，它可接收任何有效的PL/SQL塊或子程序。如圖所示：

PL/SQL機(jī)可執(zhí)行過(guò)程性語(yǔ)句，而將SQL語(yǔ)句發(fā)送到ORACLE服務(wù)器上的SQL語(yǔ)句執(zhí)行器。在ORACLE預(yù)編譯程序或OCI程序中可嵌入無(wú)名的PL/SQL塊。如果ORACLE具有PROCEDURAL選件，有名的PL/SQL塊（子程序）可單獨(dú)編譯，永久地存儲(chǔ)在數(shù)據(jù)庫(kù)中，準(zhǔn)備執(zhí)行。

PL/SQL基礎(chǔ)：

PL/SQL有一字符集、保留字、標(biāo)點(diǎn)、數(shù)據(jù)類(lèi)型、嚴(yán)密語(yǔ)法等，它與SQL有相同表示，現(xiàn)重點(diǎn)介紹。

1）、數(shù)據(jù)類(lèi)型：如下表所示

數(shù)據(jù)類(lèi)型 子類(lèi)型

純量類(lèi)型 數(shù)值 BINARY_INTEGER NATURAL,POSITIVE

NUMBER DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT

字符 CHAR CHARACTER,STRING

VARCHAR2 VARCHAR

LONG

LONG RAW

RAW

RAWID

邏輯 BOOLEAN

日期 DATE

組合 類(lèi)型 記錄 RECORD

表 TABLE

2）、變量和常量

在PL/SQL程序中可將值存儲(chǔ)在變量和常量中，當(dāng)程序執(zhí)行時(shí)，變量的值可以改變，而常量的值不能改變。

3）、程序塊式結(jié)構(gòu)：

DECLARE

變量說(shuō)明部分；

BEGIN

執(zhí)行語(yǔ)句部分；

[EXCEPTION

例外處理部分；] END;控制語(yǔ)句：

分支語(yǔ)句：

IF condition THEN

Sequence_of_statements;

END IF;

IF condition THEN

Sequence_of_statement1;

ELSE

Sequence_of_statement2;

END IF;

IF condition1 THEN

Sequence_of_statement1;

ELSIF condition2 THEN

Sequence_of_statement2;

ELSIF condition3 THEN

Sequence_of_statement3;

END IF;

循環(huán)語(yǔ)句：

LOOP

Sequence_of_statements;

IF condition THEN

EXIT;

END IF;

END LOOP;

WHILE condition LOOP

Sequence_of_statements;

END LOOP;

FOR counter IN lower_bound..higher_bound LOOP

Sequence_of_statements;

END LOOP;

子程序：

存儲(chǔ)過(guò)程：

CREATE PROCEDURE 過(guò)程名(參數(shù)說(shuō)明1，參數(shù)說(shuō)明2，[局部說(shuō)明]

BEGIN

執(zhí)行語(yǔ)句；

END 過(guò)程名；

。)IS。

存儲(chǔ)函數(shù)：

CREATE FUNCTION 函數(shù)名(參數(shù)說(shuō)明1，參數(shù)說(shuō)明2。。)RETURN 類(lèi)型 IS [局部說(shuō)明] BEGIN

執(zhí)行語(yǔ)句；

END 函數(shù)名

Oracle SQL精妙SQL語(yǔ)句講解

好東西，大家趕緊收藏吧~~~

--行列轉(zhuǎn)換 行轉(zhuǎn)列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc

SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

--行列轉(zhuǎn)換 列轉(zhuǎn)行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal

FROM(SELECT a.*, b.rn

FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列轉(zhuǎn)換 行轉(zhuǎn)列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q

FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q

FROM(SELECT a.card_code, a.q, b.rn

FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--實(shí)現(xiàn)一條記錄根據(jù)條件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

INSERT ALL WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一個(gè)語(yǔ)句實(shí)現(xiàn) DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

SELECT * FROM t_mg;

MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/刪除重復(fù)記錄

DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;

INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

SELECT b.code, b.NAME

FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

FROM t_dup a)b WHERE b.rn > 1;

--IN/EXISTS的不同適用環(huán)境--t_orders.customer_id有索引 SELECT a.*

FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a.*

FROM t_employees a WHERE EXISTS(SELECT 1

FROM t_orders b

WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);

--t_employees.department_id有索引 SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);

--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual

CONNECT BY ROWNUM <=10;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');

--不建議使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';

--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

SELECT * FROM t_loop;

--逐行提交 DECLARE BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

COMMIT;

END LOOP;END;

--模擬批量提交 DECLARE

v_count NUMBER;BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

v_count := v_count + 1;

IF v_count >= 100 THEN

COMMIT;

END IF;

END LOOP;

COMMIT;END;

--真正的批量提交 DECLARE

CURSOR cur IS

SELECT * FROM user_objects;

TYPE rec IS TABLE OF user_objects%ROWTYPE;

recs rec;BEGIN

OPEN cur;

WHILE(TRUE)LOOP

FETCH cur BULK COLLECT

INTO recs LIMIT 100;

--forall 實(shí)現(xiàn)批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);

COMMIT;

EXIT WHEN cur%NOTFOUND;

END LOOP;

CLOSE cur;END;

--悲觀鎖定/樂(lè)觀鎖定

DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

--常見(jiàn)的實(shí)現(xiàn)邏輯，隱含bug DECLARE

v_cnt NUMBER;BEGIN

--這里有并發(fā)性的bug

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;END;

--高并發(fā)環(huán)境下，安全的實(shí)現(xiàn)邏輯 DECLARE

v_cnt NUMBER;BEGIN

--對(duì)指定的行取得lock

SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

--在有l(wèi)ock的情況下繼續(xù)下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;--提交并且釋放lock END;

--硬解析/軟解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

SELECT * FROM t_hard;

DECLARE

sql_1

VARCHAR2(200);BEGIN

--hard parse

--java中的同等語(yǔ)句是 Statement.execute()

FOR i IN 1..1000 LOOP

sql_1 := 'insert into t_hard(id)values(' || i || ')';

EXECUTE IMMEDIATE sql_1;

END LOOP;

COMMIT;

--soft parse

--java中的同等語(yǔ)句是 PreparedStatement.execute()

sql_1

:= 'insert into t_hard(id)values(:id)';

FOR i IN 1..1000 LOOP

EXECUTE IMMEDIATE sql_1

USING i;

END LOOP;

COMMIT;END;

--正確的分頁(yè)算法

SELECT *

FROM(SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a

WHERE ROWNUM <= 500)WHERE rn > 480;

--分頁(yè)算法(why not this one)SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分頁(yè)算法(why not this one)SELECT b.*

FROM(SELECT a.*, ROWNUM rn

FROM t_employees a

WHERE ROWNUM < = 500

ORDER BY first_name)b WHERE b.rn > 480;

--OLAP--小計(jì)合計(jì) SELECT CASE

WHEN a.deptno IS NULL THEN

'合計(jì)'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

'小計(jì)'

ELSE

'' || a.deptno

END deptno，a.empno，a.ename，SUM(a.sal)total_sal

FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分組排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳躍的rank

rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分組排序

rank()over(ORDER BY sal DESC)r3

FROM scott.emp a

ORDER BY a.deptno,a.sal DESC;

--當(dāng)前行數(shù)據(jù)和前/后n行的數(shù)據(jù)比較 SELECT a.empno，a.ename，a.sal，--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3

FROM scott.emp a ORDER BY a.sal DESC;

一、數(shù)據(jù)表設(shè)計(jì)圖

二、創(chuàng)建語(yǔ)句

/*================*/ /* DBMS name: ORACLE Version 9i */ /* Created on: 2008-11-10 23:39:24 */ /*================*/

alter table “emp”

drop constraint FK_EMP_REFERENCE_DEPT;

drop table “dept” cascade constraints;

drop table “emp” cascade constraints;

drop table “salgrade” cascade constraints;

/*================*/ /* Table: “dept” */ /*================*/

create table dept(deptno NUMBER(11)not null, dname VARCHAR2(15)not null, loc VARCHAR2(15)not null, constraint PK_DEPT primary key(deptno));

/*================*/ /* Table: “emp” */ /*================*/

create table emp(empno NUMBER(11)not null, deptno NUMBER(11), ename VARCHAR2(15)not null, sal NUMBER(11)not null, job VARCHAR2(15)not null, mgr NUMBER(11)not null, hirdate DATE not null, comm NUMBER(11)not null, constraint PK_EMP primary key(empno));

/*================*/ /* Table: salgrade */ /*================*/

create table salgrade(grade NUMBER(11)not null, losal NUMBER(11)not null, hisal NUMBER(11)not null, constraint PK_SALGRADE primary key(grade));

alter table emp add constraint FK_EMP_REFERENCE_DEPT foreign key(deptno)references dept(deptno);

三、測(cè)試要求及語(yǔ)句

/** *公司工資最高的員工列表 子查詢(xún) */

select t.ename,t.sal from emp t where t.sal =(select max(sal)from emp)

/** *查詢(xún)每一個(gè)員工的經(jīng)理人及自己的名字 */

select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno)

/** *查詢(xún)公司平均薪水的等級(jí) */

select s.grade from salgrade s where(select avg(t.sal)from emp t)between s.losal and s.hisal

/** *求部門(mén)中那些人的工資最高 */

select d.dname,ename,sal from(select t.deptno,ename,sal from(select deptno,max(sal)as max_sal from emp group by deptno)e join emp t on(e.deptno = t.deptno and t.sal = max_sal))et join dept d on(d.deptno = et.deptno)

/** *查詢(xún)部門(mén)平均薪水的等級(jí) */

select d.dname,avg_sal,grade from(select deptno,avg_sal,grade from(select deptno,avg(sal)as avg_sal from emp group by deptno)e join salgrade s on(e.avg_sal between s.losal and s.hisal))es join dept d on(es.deptno = d.deptno)

/** *求部門(mén)的平均薪水等級(jí) */

select deptno,avg(grade)from(select deptno,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal))t group by t.deptno

/** * 求那些人是經(jīng)理人 */

select ename from emp e where empno in(select distinct mgr from emp)

/** *不準(zhǔn)用組函數(shù) 求薪水的最高值 */

select ename from emp where empno not in(select distinct e1.empno from emp e1 join emp e2 on(e1.sal

/** *平均薪水最高的部門(mén)編號(hào)與名稱(chēng) */

select d.deptno,dname from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join dept d on(d.deptno = t1.deptno)where avg_sal =(select max(avg_sal)from(select deptno,avg(sal)avg_sal from emp group by deptno)t2)

/** *求平均薪水的等級(jí)最低的部門(mén)名稱(chēng) */

select dname from dept d where d.deptno in(select deptno from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t2 where t2.grade =(select min(grade)from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t3))

/** *求部門(mén)經(jīng)理人中平均薪水最低的部門(mén)名稱(chēng) */

select d.dname,t1.avg_sal from dept d join(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno)t1 on(d.deptno = t1.deptno)where avg_sal =(select min(avg_sal)from(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno))/** *求必普通員工的最高薪水還要高的經(jīng)理人名稱(chēng) */

select ename from(select e2.ename,e2.empno,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t where t.sal >(select max(e.sal)from emp e where e.empno not in(select e1.mgr from emp e1 join emp e2 on(e1.mgr = e2.empno)))

/** *求薪水最高的第6名到10名雇員 */

SELECT * FROM(SELECT A.*, ROWNUM RN FROM(SELECT * FROM(select e1.ename,e1.sal from emp e1 order by e1.sal desc))A WHERE ROWNUM <= 10)WHERE RN >= 6

第四篇：Oracle-SQL精妙SQL語(yǔ)句講解

Oracle: SQL精妙SQL語(yǔ)句講解

一、重復(fù)操作查詢(xún)

--where條件得distinct systemdicid作為唯一標(biāo)識(shí) select *

from dmis_zd_systemdic t WHERE typeid = '06012'

and t.systemdicid in(select min(systemdicid)from dmis_zd_systemdic where typeid = '06012'

group by name)order by orderno;

二、檢查表是否存在

select count(tname)from tab where tname = upper('表名');

三、日期函數(shù)

--返回當(dāng)前日期的第一天

select trunc(sysdate,'year')from dual;--返回當(dāng)前日期月份的第一天

select trunc(sysdate,'month')from dual;--上月最后一天

select last_day(add_months(sysdate,-1))from dual;--給定日期后最近星期幾得日期

select next_day(to_date('2009-12-01', 'yyyy-mm-dd'), '星期一')next_day from dual;

四、同一張表中，根據(jù)一個(gè)字段更新另一個(gè)字段

update(select t.fgenerationtime as ftime, t.fgeneratedateall as str from dmis_fs_approvebook t where t.fgenerationtime is not null)set str = TO_CHAR(ftime, 'yyyy-mm-dd')where str is null;

五、重復(fù)數(shù)據(jù)查詢(xún) select * FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

六、合并不同表的數(shù)據(jù)（merge into）merge into student s using(select id, name, tel from test001)x on(s.s_id = x.id)when matched then

update set s_name = x.name when not matched then

insert(s_id, s_name, s_age)values(x.id, x.name, x.tel);commit;

七、查詢(xún)執(zhí)行sql（v$sql）

select t.module, t.first_load_time, t.sql_text from v$sql t order by first_load_time desc;

2、數(shù)據(jù)庫(kù)精度修改處理--Create table /*drop table temp_data;*/ create table temp_data(FID VARCHAR2(40)not null, USEHOURS NUMBER(10)default 0, FVOLTAGE NUMBER(10)default 0, INVOLTAGE NUMBER(10)default 0);alter table TEMP_DATA add constraint tempfid primary key(FID);

insert into temp_data select a.fid, a.usehours, a.fvoltage, a.involtage from dmis_fs_factorymonthdetail a;

update dmis_fs_factorymonthdetail t set t.usehours = '', t.fvoltage = '', t.involtage = '';

alter table DMIS_FS_FACTORYMONTHDETAIL modify USEHOURS NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify FVOLTAGE NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify INVOLTAGE NUMBER(10,1);

update(select a.usehours as tusehours, b.usehours as fusehours, a.fvoltage as tfvoltage, b.fvoltage as ffvoltage, a.involtage as tinvoltage, b.involtage as finvoltage, a.fid as ffid, b.fid as tfid from dmis_fs_factorymonthdetail a, temp_data b where a.fid = b.fid)tt set tt.tusehours = tt.fusehours, tt.tfvoltage = tt.ffvoltage, tt.tinvoltage = tt.finvoltage where ffid = tfid;drop table temp_data;commit;

3、恢復(fù)drop掉的存儲(chǔ)過(guò)程 用sys用戶(hù)登陸，執(zhí)行如下的查詢(xún)：

SQL> select text from dba_source as of timestamp to_timestamp('2009-03-06 09:45:00', 'YYYY-MM-DD

HH24:MI:SS')

where

owner='IPRA'

and

name=

'P_IPACCHECK_NC' order by line;

4、刪除某個(gè)用戶(hù)下的對(duì)象--刪除某個(gè)用戶(hù)下的對(duì)象 set heading off;set feedback off;spool c:dropobj.sql;prompt--Drop constraint select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';prompt--Drop tables select 'drop table '||table_name ||';' from user_tables;

prompt--Drop view select 'drop view ' ||view_name||';' from user_views;

prompt--Drop sequence select

--行列轉(zhuǎn)換 行轉(zhuǎn)列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);INSERT INTO t_change_lc SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;SELECT * FROM t_change_lc;SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0))q1, SUM(decode(a.q, 2, a.bal, 0))q2, SUM(decode(a.q, 3, a.bal, 0))q3, SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;--行列轉(zhuǎn)換 列轉(zhuǎn)行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0))q1, SUM(decode(a.q, 2, a.bal, 0))q2, SUM(decode(a.q, 3, a.bal, 0))q3, SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;SELECT * FROM t_change_cl;SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal FROM(SELECT a.*, b.rn FROM t_change_cl a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列轉(zhuǎn)換 行轉(zhuǎn)列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;SELECT * FROM t_change_lc_comma;SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q FROM(SELECT a.card_code, a.q, row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn FROM t_change_lc_comma a)t1 START WITH t1.rn = 1

CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;SELECT * FROM t_change_cl_comma;SELECT t.card_code, substr(t.q, instr(';' || t.q, ';', 1, rn), instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q FROM(SELECT a.card_code, a.q, b.rn FROM t_change_cl_comma a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--實(shí)現(xiàn)一條記錄根據(jù)條件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;INSERT ALL

WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE

INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一個(gè)語(yǔ)句實(shí)現(xiàn) DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));SELECT * FROM t_mg;MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/刪除重復(fù)記錄 DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;SELECT * FROM t_dup;SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);SELECT b.code, b.NAME FROM(SELECT a.code, a.NAME, row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn FROM t_dup a)b WHERE b.rn > 1;--IN/EXISTS的不同適用環(huán)境--t_orders.customer_id有索引 SELECT a.*

FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);SELECT a.*

FROM t_employees a WHERE EXISTS(SELECT 1 FROM t_orders b WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);--t_employees.department_id有索引 SELECT a.*

FROM t_employees a WHERE a.department_id = 10 AND EXISTS

(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS

SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM <=10;CREATE INDEX idx_nonfbi ON t_fbi(dt);DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');--不建議使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;SELECT * FROM t_loop;--逐行提交 DECLARE BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP INSERT INTO t_loop VALUES cur;COMMIT;END LOOP;END;

--模擬批量提交 DECLARE

v_count NUMBER;BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP INSERT INTO t_loop VALUES cur;v_count := v_count + 1;IF v_count >= 100 THEN COMMIT;END IF;END LOOP;COMMIT;END;

--真正的批量提交 DECLARE CURSOR cur IS

SELECT * FROM user_objects;TYPE rec IS TABLE OF user_objects%ROWTYPE;recs rec;BEGIN OPEN cur;WHILE(TRUE)LOOP FETCH cur BULK COLLECT INTO recs LIMIT 100;

--forall 實(shí)現(xiàn)批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;

--悲觀鎖定/樂(lè)觀鎖定 DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;SELECT * FROM t_lock;

--常見(jiàn)的實(shí)現(xiàn)邏輯，隱含bug DECLARE v_cnt NUMBER;BEGIN

--這里有并發(fā)性的bug SELECT MAX(ID)INTO v_cnt FROM t_lock;--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;END;

--高并發(fā)環(huán)境下，安全的實(shí)現(xiàn)邏輯 DECLARE v_cnt NUMBER;BEGIN

--對(duì)指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

--在有l(wèi)ock的情況下繼續(xù)下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;--提交并且釋放lock END;

--硬解析/軟解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);SELECT * FROM t_hard;DECLARE

sql_1 VARCHAR2(200);BEGIN

--hard parse--java中的同等語(yǔ)句是 Statement.execute()FOR i IN 1..1000 LOOP sql_1 := 'insert into t_hard(id)values(' || i || ')';EXECUTE IMMEDIATE sql_1;END LOOP;COMMIT;--soft parse--java中的同等語(yǔ)句是 PreparedStatement.execute()sql_1 := 'insert into t_hard(id)values(:id)';FOR i IN 1..1000 LOOP EXECUTE IMMEDIATE sql_1 USING i;END LOOP;COMMIT;END;

--正確的分頁(yè)算法 SELECT *

FROM(SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500)WHERE rn > 480;

--分頁(yè)算法(why not this one)SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分頁(yè)算法(why not this one)SELECT b.*

FROM(SELECT a.*, ROWNUM rn FROM t_employees a WHERE ROWNUM < = 500 ORDER BY first_name)b WHERE b.rn > 480;--OLAP

--小計(jì)合計(jì) SELECT CASE

WHEN a.deptno IS NULL THEN '合計(jì)'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN '小計(jì)' ELSE

'' || a.deptno END deptno, a.empno, a.ename, SUM(a.sal)total_sal FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分組排序 SELECT a.deptno, a.empno, a.ename, a.sal,--可跳躍的rank rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1,--密集型rank dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2,--不分組排序

rank()over(ORDER BY sal DESC)r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;--當(dāng)前行數(shù)據(jù)和前/后n行的數(shù)據(jù)比較 SELECT a.empno, a.ename, a.sal,--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1,--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3 FROM scott.emp a ORDER BY a.sal DESC;

第五篇：SQL高手篇精妙SQL語(yǔ)句介紹

說(shuō)明:復(fù)制表(只復(fù)制結(jié)構(gòu),源表名:a 新表名:b)SQL: select * into b from a where 1<>1

說(shuō)明:拷貝表(拷貝數(shù)據(jù),源表名:a 目標(biāo)表名:b)SQL: insert into b(a, b, c)select d,e,f from b;

說(shuō)明:顯示文章、提交人和最后回復(fù)時(shí)間

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

說(shuō)明:外連接查詢(xún)(表名1:a 表名2:b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

說(shuō)明:日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff('minute',f開(kāi)始時(shí)間,getdate())>5

說(shuō)明:兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息

SQL:

delete from info where not exists(select * from infobz where info.infid=infobz.infid)

說(shuō)明:--SQL:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM(SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM')= TO_CHAR(SYSDATE, 'YYYY/MM'))X,(SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM')= TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM')|| '/01','YYYY/MM/DD')1 FROM Handle a)