sql語句大全

一 基本操作
1) desc,describe 作用是顯示資料表的結構 使用形式： desc 資料表名
2) distinct 消除重複的資料 使用形式：select distinct 欄位名 from 資料表
3) order by 欄位1 asc,欄位2 desc
4) 巢狀查詢 select emp.empno,emp.ename,emp.job,emp.sal
from scott.emp
where sal>=(select sal from scott.emp where ename = 'WARD');
5) in 巢狀查詢 select emp.empno,emp.ename,emp.job,emp.sal
from scott.emp
where sal in (select sal from scott.emp where ename = 'WARD');
6) any 巢狀查詢 select emp.empno,emp.ename,emp.job,emp.sal
from scott.emp
where sal > any (select sal from scott.emp where job = 'MANAGER');
等價於 （1） select sal from scott.emp where job = 'MANAGER'
（2） select emp.empno,emp.ename,emp.job,emp.sal
from scott.emp
where sal > （1）中查出的資料a or sal > （1）中查出的資料b or sal > （1）中查出的資料c
eg:
select sal from scott.emp where job = 'MANAGER'結果;12,10,13
等價於 sal=12,10,13 或者 SAL>(12 OR 10 OR 13)

7) 交操作就是集合中交集的概念。屬於集合A且屬於集合B的元素總和就是交集。 在【命令編輯區】執行下列語句。

eg:
(select djbh from ck_rwd_hz) intersect(select djbh from ck_rwd_mx) 單據編號相同的
select * from ck_rwd_mx a,
((select djbh from ck_rwd_hz) intersect(select djbh from ck_rwd_mx)) b
where a.djbh =b.djbh

二 函式

1）ceil 取大於等於數值N的最小整數 ceil(N); select mgr,mgr/100,ceil(mgr/100) from scott.emp;
2）floor 取小於等於數值N的最大整數 floor(N); select mgr,mgr/100,floor(mgr/100) from scott.emp;
3）mod m整除n後的餘數 mod(m,n)
4）power m的n次方 mod(m,n)
5）round m的四捨五入，保留n位 mod(m,n)
select round(8.655,2)from dual; 8.66
select round(8.653,2)from dual; 8.65

6）sign n>0,取1；n=0,取0；n<0,取-1；
7）avg 求平均值 avg(欄位名)
8）count 統計總數 count(欄位名) select (*) from scott.emp; select count(distinct job) from scott.emp;
9）min 計算數值型欄位最小值 select min(sal) 最少薪水 from scott.emp;
10）max 計算數值型欄位最大值 select max(sal) 最高薪水 from scott.emp;
11）sum 計算數值型欄位總和 select sum(sal) 薪水總和 from scott.emp;

三 錄入資料
1)單行資料錄入
insert into 資料表（欄位1，欄位2，...）valuse（欄位名1的值，欄位名2的值，...）
數值型欄位可以直接寫值；字元型欄位加單引號；日期型欄位加單引號，同時要注意年月日的排列順序
2）多行資料錄入
insert into 資料表（欄位名1，欄位名2，...）
（select（欄位名1或運算，欄位名2或運算，...）from資料表 where 條件）

3）表間的資料複製
create table scott.test
as
(
select distinct empno,ename,hiredate from scott.emp where empno>=7000
);
create table spkpk_liu as select * from spkfk; 建立表又複製資料，但是建立的表資訊不全
全表備份的時候可以這樣些。
一般在建了表之後根據需要再看是否要把索引和主鍵這些再建上去。

而且" create table spkpk_liu as select * from spkfk
"
這樣建表後，表的很多引數值都是預設的最小的值，比如原表的初始值10M，新表的可能只有256K。
正式的環境裡面用到的表，一般不推薦這樣建表。

用這個方法僅僅有點偷懶，這樣做的話，一個語句可以實現建表和插入資料的目的。
比如需要修改表A的資料，修改前可能考慮把A表的資料備份一個。
這個時候就可以用create table ... as...
這樣方便將來回復A表的資料
自己除錯程式的時候都可以這樣處理 但是不能像這樣建立過程、包、函式

四 刪除資料
delete 刪除資料； truncate刪除整個表資料但保留結構
1）刪除記錄
delete from scott.test where empno >= 7500 and empno <=8000;
2）刪除整個資料
truncate table scott.test;

truncate,delete,drop的異同點
注意:這裡說的delete是指不帶where子句的delete語句

相同點:truncate和不帶where子句的delete, 以及drop都會刪除表內的資料
不同點:
1. truncate和 delete只刪除資料不刪除表的結構(定義)
drop語句將刪除表的結構被依賴的約束(constrain),觸發器(trigger),索引(index); 依賴於該表的儲存過程/函式將保留,但是變為invalid狀態.


2.delete語句是dml,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發.
truncate,drop是ddl, 操作立即生效,原資料不放到rollback segment中,不能回滾. 操作不觸發trigger. 顯然drop語句將表所佔用的空間全部釋放

3.速度,一般來說: drop> truncate > delete
使用上,想刪除部分資料行用delete,注意帶上where子句. 回滾段要足夠大，可以通過ROBACK回滾，相當有恢復的餘地
想刪除表,當然用drop
想保留表而將所有資料刪除. 如果和事務無關,用truncate即可. truncate table xx 刪除整個表的資料，沒有恢復的餘地，好處可以整理表內碎片，釋放空間
所以最好一般先備份資料
如果是整理表內部的碎片,可以用truncate跟上reuse stroage,再重新匯入/插入資料


五 更新資料
update 資料表
set 欄位名1=新的賦值，欄位名2=新的賦值，....
where 條件

update scott.emp
set empno=8888,ename='TOM',hiredate='03-9月 -2002'
where empno = 7566;

update scott.emp
set sal=
(select sal+300 from scott.emp where empno = 8099)
where empno=8099;


decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,預設值)

六 資料匯出

1 將資料庫TEST完全匯出,使用者名稱system 密碼manager 匯出到D:\daochu.dmp中
expsystem/manager@TESTfile=d:\daochu.dmp full=y
2 將資料庫中system使用者與sys使用者的表匯出
expsystem/manager@TESTfile=d:\daochu.dmp ōwner=(system,sys)
3 將資料庫中的表table1 、table2匯出
expsystem/manager@TESTfile=d:\daochu.dmp tables=(table1,table2)
4 將資料庫中的表table1中的欄位filed1以"00"打頭的資料匯出
expsystem/manager@TESTfile=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

explmis_wh/lmis@lmisbuffer=10000 ōWNER=lmis_wh rows=n file=d:\lmis_wh_nodata.dmp log=d:\lmis_wh_nodata.log
implmis/lmis@lmisbuffer=10000 fromuser=lmis_wh touser=lmis file=d:\lmis_wh_nodata.dmp log=d:\lmis_wh_nodata.log

C:\>implmis/lmis@lmisbuffer=50000000 full=n file=e:\daochu.dmp ignore=y rows=y
commit=y compile=y fromuser=lmis_wh touser=lmis

七 資料的匯入
1 將D:\daochu.dmp 中的資料匯入 TEST資料庫中。
impsystem/manager@TEST file=d:\daochu.dmp
上面可能有點問題，因為有的表已經存在，然後它就報錯，對該表就不進行匯入。
在後面加上 ignore=y 就可以了。
2 將d:\daochu.dmp中的表table1 匯入
impsystem/manager@TEST file=d:\daochu.dmp tables=(table1)

SQL定義：SQL是一種面向資料庫的通用資料處理語言規範，能完成以下幾類功能：提取查詢資料，插入修改刪除資料，生成修改和刪除資料庫物件，資料庫安全控制，資料庫完整性及資料保護控制。

SQL分類：
DDL—資料定義語言（CREATE，ALTER，DROP，DECLARE）
DML—資料操縱語言（SELECT，DELETE，UPDATE，INSERT）
DCL—資料控制語言（GRANT，REVOKE，COMMIT，ROLLBACK）

DDL—資料庫定義語言：直接提交的。
CREATE：用於建立資料庫物件。
DECLARE：除了是建立只在過程中使用的臨時表外，DECLARE語句和CREATE語句非常相似。唯一可以被宣告的物件是表。並且必須放入使用者臨時表空間。
DROP：可以刪除任何用CREATE（資料庫物件）和DECLARE（表）建立的物件。
ALTER：允許修改某些資料庫物件的資訊。不能修改索引。


八 下面主要基於物件介紹基本的語法

1、資料庫：
建立資料庫：CREATE DATABASE database-name [USING CODESET codeset TERRITORY territory]
注：內碼表的問題。
刪除資料庫：drop database dbname

2、表：
建立新表：
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根據已有的表建立新表：
A：create table tab_new like tab_old
B：create table tab_new as select col1,col2… from tab_old definition only
修改表：
增加一個列:
Alter table tabname add column col type
注：列增加後將不能刪除。DB2中列加上後資料型別也不能改變，唯一能改變的是增加varchar型別的長度。新增主鍵：
Alter table tabname add primary key(col)
刪除主鍵：
Alter table tabname drop primary key(col)
刪除表：drop table tabname
alter table BMDOC_LIUF
drop constraint PK1_BMDOC cascade;

3、表空間：
建立表空間：create tablespace tbsname pagesize 4k managed by database using (file ‘file’ size)
表空間加入容器：alter tablespace tablespace_name add(file 'filename' size)
注：該操作是不可逆的，加入容器後將不能將其刪除，因此在加入的時候注意。
刪除表空間：drop tablespace tbsname

4、索引：
建立索引：create [unique] index idxname on tabname(col….)
刪除索引：drop index idxname
注：索引是不可更改的，想更改必須刪除重新建。

5、檢視：
建立檢視：create view viewname as select statement
刪除檢視：drop view viewname
注：檢視唯一能修改的是引用型別列，改變列的範圍。其他定義好了都不能修改。當檢視基於的基表drop後，檢視變為無效。

DML—資料庫操縱語言，它不隱式地提交當前事務 ，是否提交視環境設定而定。

SELECT：從表中查詢符合資料
注：條件中連線的問題，避免出現笛卡兒乘積

DELETE：刪除已有表的資料
UPDATE：更新已有表的資料
INSERT：向已有表中插入資料
注：DELETE，UPDATE和INSERT是否直接提交取決與執行語句所在的環境。
在執行時注意事務日誌滿的情況。

2、DELETE：從表中刪除記錄
語法格式：
DELETE FROM tablename WHERE (conditions)

3、INSERT：向表中插入記錄
語法格式：
INSERT INTO tablename (col1,col2,…) VALUES (value1,value2,…);
INSERT INTO tablename (col1,col2,…) VALUES (value1, value2,…), (value1, value2,…),……
Insert不會等待任何程式，不會導致鎖定

4、UPDATE：
語法格式：
UPDATE tabname SET (col1=values1,col2=values2,…) WHERE (conditions);
注：update的速度比較慢，要在相應列上建立索引。

九 許可權

DCL—資料控制語言
GRANT—授予使用者許可權
REVOKE—撤消使用者許可權
COMMIT—提交事務，可以使資料庫的修改永久化
ROLLBACK—回滾事務，消除上一個COMMIT命令後的所做的全部修改，使得資料庫的內容恢復到上一個COMMIT執行後的狀態.。

1、GRANT：所有著或者管理員把訪問許可權賦給其他使用者
語法格式：
grant [all privileges|privileges,….] on tabname | viewname to [public|user,….]

2、REVOKE:取消某一使用者的某中訪問許可權
語法格式：
Revoke [all privileges|privileges,….] on tabname | viewname from [public|user,….]
注：不能取消例項級別的使用者的任何許可權，他們不是通過grant授權的，是通過組實現的許可權。

3、COMMIT:把事務中所做的修改永久化記錄到資料庫。
語法格式：
commit [work]

4、ROLLBACK:將上次提交以來所做的更改全部撤消。
語法格式：
Rollback [work]

十 高階sql簡單介紹

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

B： EXCEPT 運算子
EXCEPT 運算子通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重複行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重複行。

C： INTERSECT 運算子
INTERSECT 運算子通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重複行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重複行。
注：使用運算詞的幾個查詢結果行必須是一致的。


附錄：常用函式介紹
1、型別轉化函式：
轉化為數字型別的：
decimal, double, Integer, smallint,real
Hex(arg):轉化為引數的16進製表示。
轉化為字串型別的：
char, varchar
Digits(arg):返回arg的字串表示法，arg必須為decimal。
轉化為日期時間的：
date, time,timestamp

2、時間日期：
year, quarter, month, week, day, hour, minute ,second
dayofyear(arg):返回arg在年內的天值
Dayofweek(arg):返回arg在周內的天值
days(arg):返回日期的整數表示法，從0001-01-01來的天數。
midnight_seconds(arg):午夜和arg之間的秒數。
Monthname(arg):返回arg的月份名。
Dayname(arg):返回arg的星期。

3、字串函式：
length,lcase, ucase, ltrim, rtrim
Coalesce(arg1,arg2….):返回引數集中第一個非null引數。
Concat (arg1,arg2):連線兩個字串arg1和arg2。
insert(arg1,pos,size,arg2):返回一個，將arg1從pos處刪除size個字元，將arg2插入該位置。
left(arg,length):返回arg最左邊的length個字串。
locate(arg1,arg2,<pos>):在arg2中查詢arg1第一次出現的位置，指定pos，則從arg2的pos處開始找arg1第一次出現的位置。
posstr(arg1,arg2):返回arg2第一次在arg1中出現的位置。
repeat(arg1 ,num_times):返回arg1被重複num_times次的字串。
replace(arg1,arg2,arg3):將在arg1中的所有arg2替換成arg3。
right(arg,length):返回一個有arg左邊length個位元組組成的字串。
space(arg):返回一個包含arg個空格的字串。
substr(arg1,pos,<length>):返回arg1中pos位置開始的length個字元，如果沒指定length，則返回剩餘的字元。

4、數學函式：
Abs, count, max, min, sum
Ceil(arg):返回大於或等於arg的最小整數。
Floor(arg):返回小於或等於引數的最小整數。
Mod(arg1,arg2):返回arg1除以arg2的餘數，符號與arg1相同。
Rand():返回1到1之間的隨機數。
Power(arg1,arg2):返回arg1的arg2次方。
Round(arg1,arg2):四捨五入截斷處理，arg2是位數，如果arg2為負，則對小數點前的數做四捨五入處理。
Sigh(arg):返回arg的符號指示符。-1,0,1表示。
truncate(arg1,arg2):截斷arg1，arg2是位數，如果arg2是負數，則保留arg1小數點前的arg2位。

5、其他：
nullif(arg1,arg2)：如果2個引數相等，則返回null，否則，返回引數1