第一篇：Oracle常用語句

Oracle:

1、創(chuàng)建user用戶： create user demo identified by demo account unlock默認(rèn)是lock;

2、權(quán)限回收和設(shè)置： grant connect|resource to demo;revoke connect|resource from demo;

3、修改用戶信息： alter user demo identified by admin;alter user demo account lock;

4、數(shù)據(jù)庫連接: conn sys/change_on_install as sysdba;SYS用戶必須要加后面sysdba5、Conn scott/tiger;conn system/admin;

6、Oracle 服務(wù)開啟 startup open;關(guān)閉服務(wù)shutdowm;

7、Oracle有兩種權(quán)限操作數(shù)據(jù)庫的系統(tǒng)權(quán)限 比如說創(chuàng)建tablesession還有一種是操作數(shù)據(jù)庫中的對象，對表進(jìn)行增刪改查，有三種角色connectresourcedba，角色是一些系統(tǒng)權(quán)限的集合，conect角色主要是些臨時(shí)的用戶，特別是那些不需要建表的用戶，resource是那些比較正式的數(shù)據(jù)庫用戶，可以有自己的表 序列 過程 觸發(fā)器 索引 等等，dba擁有所有系統(tǒng)權(quán)限

8、在oracle中，客戶端把SQL語句發(fā)送給服務(wù)器，服務(wù)器對SQL語句進(jìn)行編譯、執(zhí)行，把執(zhí)行的結(jié)果返回給客戶端

? 數(shù)據(jù)定義語言（DDL），包括CREATE（創(chuàng)建）命令、ALTER（修改）命令、DROP

（刪除）命令等。

? 數(shù)據(jù)操縱語言（DML），包括INSERT（插入）命令、UPDATE（更新）命令、DELETE

（刪除）命令、SELECT … FOR UPDATE（查詢）等。

? 數(shù)據(jù)查詢語言（DQL），包括基本查詢語句、Order By子句、Group By子句等。? 事務(wù)控制語言（TCL），包括COMMIT（提交）命令、SAVEPOINT（保存點(diǎn)）命

令、ROLLBACK（回滾）命令。

? 數(shù)據(jù)控制語言（DCL），GRANT（授權(quán)）命令、REVOKE（撤銷）命令。

9、當(dāng)查詢單行函數(shù)的時(shí)候，from后面使用DUAL表，dual表在系統(tǒng)中只有一行一列，該表在輸出單行函數(shù)時(shí)為了select…from的語法完整性而使用。Select sysdate from dual;select 1+1 from dual;返回值為2；

10、創(chuàng)建表create table infos(....)添加約束alter table infos add constraint info_pk primary key(stuid);

11、Oracle外鍵約束alter table scores add constraint for_name foreign key(stuid)references infos(stuid);score表中添加約束stuid是外鍵是infos表中的主鍵；

12、orderby 默認(rèn)是升序排列

13、一個(gè)數(shù)據(jù)庫中的所有表select * from tab;desc emp;查看表結(jié)構(gòu)

14、輸入一個(gè)/ 是重復(fù)執(zhí)行上一步操作；如果前面語句句尾沒有寫分號，則語句只是保存在緩沖區(qū)中，/可以提交語句;

15、一個(gè)數(shù)據(jù)庫中訪問另一個(gè)數(shù)據(jù)庫中的表比如在sys數(shù)據(jù)庫中select * from scott.emp;

16、根據(jù)結(jié)果集創(chuàng)建表 create table demo as select * from infos;

17、根據(jù)結(jié)果集復(fù)制表結(jié)構(gòu) create table demo as select * from infos where 1=2;

18、INSERT向表中插入一個(gè)結(jié)果集 insert into demo select * from infos;

19、添加數(shù)據(jù)insert into infos values(，，);

20、Commit 是提交用戶操作的結(jié)果只有提交了 數(shù)據(jù)才真正的更新到表中 別的用戶才

能看到最后操作的結(jié)果

21、更新數(shù)據(jù)update infos set stuid='33', name-'整數(shù)' where age-'33';

22、刪除數(shù)據(jù) delete from infos where stuid-'33';

23、刪除整張表truncate table infos;delete from infos;兩者均能刪除數(shù)據(jù)，但前者刪除的數(shù)據(jù)不能回復(fù)，后者刪除的數(shù)據(jù)可以通過日志文件恢復(fù)，表中記錄過多，用truncate會比delete更加效率

24、打開一個(gè)終端 刪除一條數(shù)據(jù)，然后再打開另一個(gè)終端，會發(fā)現(xiàn)數(shù)據(jù)依然存在，在oracle中，每個(gè)連接到數(shù)據(jù)庫的窗口，都會與數(shù)據(jù)庫建立一個(gè)session，一個(gè)session對數(shù)據(jù)的操作 不會馬上反應(yīng)到數(shù)據(jù)庫的真實(shí)數(shù)據(jù)中去，可以允許回滾，當(dāng)一個(gè)session提交所有操作后，數(shù)據(jù)庫才會做真正的修改...25、提交事務(wù)commit回滾rollback;回滾是在提交之前使用，如果已經(jīng)提交了，回滾也沒用...26、字符串連接符 ||

27、起別名select empno id,ename name from emp;最好回避中文

28、package mytest;

29、public class Main {

30、public static void main(String[] args){

31、System.out.println(new A());

32、}

33、}

34、class A35、{

36、}

37、Java在運(yùn)行上述代碼時(shí)輸出一個(gè)很奇異的結(jié)果:

38、mytest.A@60aeb039、嘗試解釋一下可能的原因.40、classA中雖然沒有任何方法，不過他繼承了object類，實(shí)現(xiàn)了里面的tostring()方法，打印出的是該對象在內(nèi)存中的地址對象類型名@內(nèi)存地址

41、Select * from emp where sal between 1500 and 3000;

42、Oracle 對大小寫敏感，所以查詢的時(shí)候應(yīng)該大寫select * from emp where ename=' SMITH';

43、字段in的作用select * from emp where ename in('張三','李四','王五');另外一種就是not inselect * from emp where empno not in(333,4444,555);

44、Like 語句的作用模糊查詢select * from empwhere ename like '_S%';_表示一個(gè)字符，% 表示多個(gè)字符

45、Order by 的使用升序asc降序descselect * from emp where deptno=10order by sal desc ,hiredate asc;薪水降序雇傭日期升序select * from emp where empno<>4444;select * from emp where empno!=4444;

46、Concatselect concat('hello','world')from dual;連接兩個(gè)字符串

47、字符串截取select substr('hello',1,4)from dual返回hellsubstr從一開始截取

48、字符串截取后面幾位 比如說名字的后三位select substr(ename,length(ename)-2,)from emp或者select substr(ename,-3,999)from emp;從倒數(shù)第三個(gè)一直到最后截取

49、NVL（）可以指定值 nvl(comm,0)可以把null值轉(zhuǎn)換成另外一個(gè)值 比如說050、Decode()相當(dāng)于if else。。Select decode(1,1,'hah ',2,'weiwei',3,'huhu')from dual;最

前面是1，后面的那些都是選項(xiàng)，對應(yīng)了哪個(gè)就相應(yīng)的打印出哪個(gè)...51、decode(job,'CLERK','業(yè)務(wù)員','SALESMAN','銷售人員','MANAGER','經(jīng)理','ANALYST','分析員','PRESIDENT','總裁')

52、統(tǒng)計(jì)一張表中的記錄數(shù)select count(*)from emp;

53、Where 只能對單行進(jìn)行過濾，而不能對行組進(jìn)行過濾，group by如果要有條件過濾的話應(yīng)該用havingselect deptno,AVG(sal)from emp having AVG(sal)>2000 group by deptno;

54、使用where也可以同時(shí)使用having，where 是對行進(jìn)行篩選，而having是對組進(jìn)行篩選having 是放在group by 后面 進(jìn)行組篩選

55、在組函數(shù)存在的時(shí)候就不要出現(xiàn)分組查詢語句了select deptno,sum(avg(sal))from emp order by deptno;這就錯了不要出現(xiàn)deptno，因?yàn)橛薪M函數(shù)了

56、子查詢：查詢出工資比7654高，同時(shí)與7788從事同樣工作的全部雇員信息： select * from emp where sal>(select sal from emp where empno=7654)and job=(select job from emp where empno=7788);

57、表的修改: alter table demo add(列名，類型)； alter table demo add(address varchar(30)default '無名氏');修改表字段的值alter table demo modify(address varchar(50)default '張三');modify 關(guān)鍵字

58、為表重命名rename demo to demo01;rename emp to empdemo;

59、約束的種類 ： 主鍵約束唯一約束 外鍵約束非空約束檢查約束

60、Primary keyuniqueforeign keynot nullcheck61、添加約束的語句alter table demo add constraint約束名primary key(id);

62、Alter table demo add constraint 約束名 not null(name);check(sec in('男','女'))unique(name)foreign key(id)references 表名（id）;

63、Rownumselect rownum ,empno,ename from emp;rownum 就是顯示行數(shù) 每一行一個(gè)數(shù)，前五行 select rownum,ename from emp where rownum<=5;假如是十行中的后五行，那就只能先做一個(gè)子查詢select * from(select rownum r,ename from emp where rownum<=10)k where k.r>5;

64、約束的作用就是保證了數(shù)據(jù)的完整性關(guān)聯(lián)上的約束 如果要刪除表的話 應(yīng)該先刪除子表 再刪除父表如果要強(qiáng)制刪除父表則drop table demo cascade constraint;還有一種是級聯(lián)刪除 父類刪除 子表也刪除drop table demo on delete cadcade;

65、Dual表是sys用戶下的一張表從scott用戶下訪問此表應(yīng)該要sys.dual，但是現(xiàn)在沒有，這就是一個(gè)同義詞的概念，用dual代替用戶名.表名創(chuàng)建同義詞create synonym emp for scott.emp;為scott表中的emp 表建一個(gè)同義詞，然后再其他用戶下就可以直接通過emp直接訪問了...Drop synonym emp;刪除同義詞

66、序列，是序列號的生成器，可以為數(shù)據(jù)庫表中的行自動生成序號語句為：create sequence demo increment by 1 start with 10 nomaxvalue(無最大值)maxvalue 100 minvalue

10。。Demo.nextval序列的下一個(gè)內(nèi)容demo.currval序列的當(dāng)前內(nèi)容

67、視圖的功能:一個(gè)試圖實(shí)際上就是封裝了一條復(fù)雜的查詢語句create view demo as 子查詢創(chuàng)建好的視圖以后一樣能夠直接在視圖中進(jìn)行查詢操作完整的語句應(yīng)該是 create or replace view demo as 子查詢假如你要查詢一個(gè)結(jié)果，但是查詢語句特別長，你每次都要寫那個(gè)語句就很麻煩，所以可以把那個(gè)語句包裝成一個(gè)視圖，以后只要select * from demo就能實(shí)現(xiàn)同樣的功能...68、不能更新視圖的創(chuàng)建條件 with check option創(chuàng)建視圖只讀不允許更改with read only69、新建的用戶user需要賦予權(quán)限，如果所有權(quán)限都需要有，那就把權(quán)限分組成各種角色，再賦予connnect resoutce70、進(jìn)入命令行，到指定要備份數(shù)據(jù)庫的文件下exp備份然后用imp恢復(fù)

71、數(shù)據(jù)庫設(shè)計(jì)的三大范式：第一范式，保證數(shù)據(jù)表中的每個(gè)字段不可再分第二范式：。

72、Groupby 和having 的使用having在最后對分組后的數(shù)據(jù)進(jìn)行篩選，而不能用where73、左右連接（+）在左表示右連接在右表示左連接

第二篇：oracle常用語句

Oracel 中常使用的語法(部分)

Oracel 中常使用的語法

2、顯示當(dāng)前連接用戶 ：SQL> show user3、查看系統(tǒng)擁有哪些用戶 ：SQL> select * from all_users;

4、新建用戶并授權(quán)：SQL> create user a identified by a;（默認(rèn)建在SYSTEM表空間下）

SQL> grant connect,resource to a;

5、連接到新用戶 :SQL> conn a/a6、查詢當(dāng)前用戶下所有對象:SQL> select * from tab;

7、建立第一個(gè)表 :SQL> create table a(a number);

8、查詢表結(jié)構(gòu)SQL> desc a9、插入新記錄SQL> insert into a values(1);

10、查詢記錄SQL> select * from a;

11、更改記錄SQL> update a set a=2;

12、刪除記錄SQL> delete from a;

13、回滾SQL> roll;或SQL> rollback;

14、提交SQL> commit;

用戶授權(quán):

GRANT ALTER ANY INDEX TO “user_id ”

GRANT “dba ” TO “user_id ”;

ALTER USER “user_id ” DEFAULT ROLE ALL

創(chuàng)建用戶:

CREATE USER “user_id ” PROFILE “DEFAULT ” IDENTIFIED BY “ DEFAULT TABLESPACE

”USERS “ TEMPORARY TABLESPACE ”TEMP “ ACCOUNT UNLOCK;GRANT ”CONNECT “ TO ”user_id “;

用戶密碼設(shè)定:

ALTER USER ”CMSDB “ IDENTIFIED BY ”pass_word “

表空間創(chuàng)建:

CREATE TABLESPACE ”table_space “ LOGGING DATAFILE

'C:ORACLEORADATAdbstable_space.ora' SIZE 5M

----------

1、查看當(dāng)前所有對象:SQL > select * from tab;

2、建一個(gè)和a表結(jié)構(gòu)一樣的空表

SQL > create table b as select * from a where 1=2;

SQL > create table b(b1,b2,b3)as select a1,a2,a3 from a where 1=2;

3、察看數(shù)據(jù)庫的大小，和空間使用情況

SQL > col tablespace format a20

SQL > select b.file_id文件ID,b.tablespace_name表空間,b.file_name物理文件名,b.bytes總字節(jié)數(shù),(b.bytes-sum(nvl(a.bytes,0)))已使用,sum(nvl(a.bytes,0))剩余,sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name /

dba_free_space--表空間剩余空間狀況

dba_data_files--數(shù)據(jù)文件空間占用情況

4、查看現(xiàn)有回滾段及其狀態(tài) ： SQL > col segment format a30

SQL > SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

5、查看數(shù)據(jù)文件放置的路徑 ：SQL > col file_name format a50

SQL > select tablespace_name,file_id,bytes/1024/1024,file_name fromdba_data_files order by file_id;

6、顯示當(dāng)前連接用戶 :SQL > show user7、把SQL*Plus當(dāng)計(jì)算器 :SQL > select 100*20 from dual;

8、連接字符串：SQL > select 列1 | |列2 from 表1;

SQL > select concat(列1,列2)from 表1;

9、查詢當(dāng)前日期和時(shí)間： SQL > select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss')from dual;

10、用戶間復(fù)制數(shù)據(jù)：SQL > copy from user1 to user2 create table2 using select * from table1;

11、視圖中不能使用order by，但可用group by代替來達(dá)到排序目的SQL > create view a as select b1,b2 from b group by b1,b2;

12、通過授權(quán)的方式來創(chuàng)建用戶 ：SQL > grant connect,resource to test identified by test;

SQL > conn test/test13、查出當(dāng)前用戶所有表名： select unique tname from col;

---------

/* 向一個(gè)表格添加字段 */alter table alist_table add address varchar2(100);

/* 修改字段 屬性 字段為空 */alter table alist_table modify address varchar2(80);

/* 修改字段名字 */

create table alist_table_copy as select ID,NAME,PHONE,EMAIL, QQ as QQ2,/*qq 改為qq2*/

ADDRESS from alist_table;drop table alist_table;

rename alist_table_copy to alist_table /* 修改表名 */

空值處理，有時(shí)要求列值不能為空 ：create table dept(deptno number(2)not null, dname char(14), loc char(13));

在基表中增加一列： alter table dept add(headcnt number(3));

修改已有列屬性 ：alter table dept modify dname char(20);

注：只有當(dāng)某列所有值都為空時(shí)，才能減小其列值寬度。只有當(dāng)某列所有值都為

空時(shí)，才能改變其列值類型。

只有當(dāng)某列所有值都為不空時(shí)，才能定義該列為not null。

例：alter table dept modify(loc char(12));alter table dept modify loc char(12);

alter table dept modify(dname char(13),loc char(12));

查找未斷連接 :

select process,osuser,username,machine,logon_time ,sql_text from v$session a,v$sqltext b where a.sql_address=b.address;

---

1.以USER_開始的數(shù)據(jù)字典視圖包含當(dāng)前用戶所擁有的信息, 查詢當(dāng)前用戶所擁有的表信息:

select * from user_tables;

2.以ALL_開始的數(shù)據(jù)字典視圖包含ORACLE用戶所擁有的信息, 查詢用戶擁有或有權(quán)訪問的所有表信息:

select * from all_tables;

3.以DBA_開始的視圖一般只有ORACLE數(shù)據(jù)庫管理員可以訪問:select * from dba_tables;

4.查詢ORACLE用戶：

conn sys/change_on_installselect * from dba_users;

conn system/manager;select * from all_users;

5.創(chuàng)建數(shù)據(jù)庫用戶：CREATE USER user_name IDENTIFIED BY password;GRANT CONNECT TO user_name;GRANT RESOURCE TO user_name;

授權(quán)的格式: grant(權(quán)限)on tablename to username;

刪除用戶(或表):drop user(table)username(tablename)(cascade);

6.向建好的用戶導(dǎo)入數(shù)據(jù)表 :

IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:EXPDAT.DMPCOMMIT = Y

7.索引： create index [index_name] on [table_name](”column_name ")

第三篇：oracle語句總結(jié)

1、時(shí)間查詢語句

select * from table where date= to_date('2013-01-27','yyyy-mm-dd')

2、單引號、雙引號

數(shù)字不需要

字符串用單引號

雙引號一般是用來轉(zhuǎn)義的，如果alias里面有空格或其它保留符號，必須使用雙引號。而單引號是用來特制的，比如字符串的引用，日期字符串的引用，都必須包括在單引號中，可以參與運(yùn)算或其它表達(dá)式中。兩者不可混用，其中，兩個(gè)單引號可以作為單引號的轉(zhuǎn)義使用，意思就是一個(gè)真正的、沒有特殊功能的單引號。例如 select substr('I''am a pig',1,4)。

3、UPDATE

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

業(yè)務(wù)受理子系統(tǒng)BSS中，--客戶資料表

create table customers

（customer_idnumber(8)not null,--客戶標(biāo)示

city_namevarchar2(10)not null,--所在城市

customer_type char(2)not null,--客戶類型

...)

create unique index PK_customers on customers(customer_id)

由于某些原因，客戶所在城市這個(gè)信息并不準(zhǔn)確，但是在客戶服務(wù)部的CRM子系統(tǒng)中，通過主動服務(wù)獲取了部分客戶20%的所在城市等準(zhǔn)確信息，于是你將該部分信息提取至一張臨時(shí)表中：

create table tmp_cust_city

（customer_idnumber(8)not null,citye_namevarchar2(10)not null，customer_typechar(2)not null)

1)最簡單的形式

--經(jīng)確認(rèn)customers表中所有customer_id小于1000均為'北京'

--1000以內(nèi)的均是公司走向全國之前的本城市的老客戶:)

update customers

setcity_name='北京'

wherecustomer_id<1000

2)兩表(多表)關(guān)聯(lián)update--僅在where字句中的連接

--這次更新的數(shù)據(jù)都是VIP，且包括新增的,所以順便更新客戶類別

update customersa--使用別名

setcustomer_type='01'--01 為vip，00為普通

whereexists(select 1

fromtmp_cust_city b

whereb.customer_id=a.customer_id)

3)兩表(多表)關(guān)聯(lián)update--被修改值由另一個(gè)表運(yùn)算而來

update customers a--使用別名

set city_name =(select b.city_name from tmp_cust_city b

b.customer_id=a.customer_id)

whereexists(select 1

fromtmp_cust_city b

whereb.customer_id=a.customer_id)

4、插入insert into

insert into table-name(字段名1，字段名2)values(‘字段值1’，‘字段值2’)

4、delete

① DELETE FROM COURSES WHERECOURSE_DESIGNATER = 'Java110' where

② DELETE FROM CLASSCONVENINGS WHERECLASSES_NUM_FK> 4AN

D CLASS_CONVENE_DATE = TO_DATE('2006-02-15 00:00:00','YYYY-MM-DD HH24:MI:SS')AND CLASS_LOCATION_FK= 'Seattle-Training Room 1'

5、刪除這些具有重復(fù)字段的數(shù)據(jù)記錄：

delete from tableName a where 字段1,字段2 in(select 字段1,字段2,count(*)from tableName group by 字段1，字段2 having count(*)> 1)

6、修改表列名等信息

alter table [table_name] rename column [column_name] to [new_column_name]

ALTER TABLE SCOTT.TEST RENAME TO TEST1--修改表名

ALTER TABLE SCOTT.TEST RENAME COLUMN NAME TO NAME1--修改表列名

ALTER TABLE SCOTT.TEST MODIFY NAME1 NUMBER(20)--修改字段類型

ALTER TABLE SCOTT.TEST ADD ADDRESS VARCHAR2(40)--添加表列

ALTER TABLE SCOTT.TEST DROP NAME CASCADECONSTRAINTS--刪除表列

第四篇：Oracle SQL精妙SQL語句講解

SQL*PLUS界面：

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

退出：輸入EXIT即可。

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

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

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

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

數(shù)據(jù)庫查詢

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

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

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

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

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

ORDER BY 子句

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

連接查詢

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

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

子查詢

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

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

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

VARCHAR2 變長字符串，最長為2000字符。

NUMBER 數(shù)值型。

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

DATE 日期型。

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

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

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

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

常用函數(shù)用法：

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

函數(shù)名（變元，變元，…）

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

單行函數(shù)

分組函數(shù)

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

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

DISTRNCT 該選項(xiàng)使分組函數(shù)只考慮變元表達(dá)式中的不同值。

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

全部分組函數(shù)（除COUNT（*）外）忽略空值。如果具有分組函數(shù)的查詢，沒有返回行或只有空值（分組函數(shù)的變元取值的行），則分組函數(shù)返回空值。

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

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

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ù)庫是EMP,并用WHERE子句限制了只對名字(ENAME)為’MARTIN’的職工的數(shù)據(jù)進(jìn)行修改,SET子句則說明修改的方式,即把’MARTION’的工作名稱(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ù)庫的修改永久化.設(shè)置AUTOCOMMIT為允許狀態(tài):SQL >SET AUTOCOMMIT ON;

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

SQL>ROLLBACK;

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

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

列定義

完整性約束

表所在表空間

存儲特性

可選擇的聚集

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

語法如下：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ù)類型、長度、缺省值）

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

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

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

2）、視圖

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

引入視圖有下列作用：

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

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

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

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

3）、索引

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

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

按索引列的順序存取表。

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

例如：SQL>CREAT INDEX IC_EMP

ON CLUSTER EMPLOYEE

4）、同義詞

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

引用對象不需指出對象的持有者。

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

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

建立同義詞：

CREATE SYNONYM symnon_name FOR [username.]tablename;

例如：CREAT PUBLIC SYNONYM EMP

FOR SCOTT.EMP @SALES

5）、用戶

CREATE USER username IDENTIFIED BY password;

例如：SQL>CREATE USER SIDNEY

IDENTIFIED BY CARTON;

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

PL/SQL概述。

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

DECLARE

---說明

BEGIN

---語句序列

EXCEPTION

---例外處理程序

END；

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

支持SQL;

生產(chǎn)率高;

性能好;

可稱植性;

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

PL/SQL基礎(chǔ)：

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

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

數(shù)據(jù)類型 子類型

純量類型 數(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

組合 類型 記錄 RECORD

表 TABLE

2）、變量和常量

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

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

DECLARE

變量說明部分；

BEGIN

執(zhí)行語句部分；

[EXCEPTION

例外處理部分；] END;控制語句：

分支語句：

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)語句：

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;

子程序：

存儲過程：

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

BEGIN

執(zhí)行語句；

END 過程名；

。)IS。

存儲函數(shù)：

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

執(zhí)行語句；

END 函數(shù)名

Oracle SQL精妙SQL語句講解

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

--行列轉(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è)語句實(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;

--悲觀鎖定/樂觀鎖定

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

SELECT * FROM t_lock;

--常見的實(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

--對指定的行取得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中的同等語句是 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中的同等語句是 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;

--正確的分頁算法

SELECT *

FROM(SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a

WHERE ROWNUM <= 500)WHERE rn > 480;

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

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

--分頁算法(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)建語句

/*================*/ /* 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);

三、測試要求及語句

/** *公司工資最高的員工列表 子查詢 */

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

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

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

/** *查詢公司平均薪水的等級 */

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

/** *求部門中那些人的工資最高 */

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)

/** *查詢部門平均薪水的等級 */

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)

/** *求部門的平均薪水等級 */

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

/** *平均薪水最高的部門編號與名稱 */

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)

/** *求平均薪水的等級最低的部門名稱 */

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))

/** *求部門經(jī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)理人名稱 */

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復(fù)制表的sql語句

oracle復(fù)制表的sql語句

如下，表a是數(shù)據(jù)庫中已經(jīng)存在的表，b是準(zhǔn)備根據(jù)表a進(jìn)行復(fù)制創(chuàng)建的表：

1、只復(fù)制表結(jié)構(gòu)的sql

create table b as select * from a where 1<>12、即復(fù)制表結(jié)構(gòu)又復(fù)制表中數(shù)據(jù)的sql

create table b as select * from a3、復(fù)制表的制定字段的sql

create table b as select row_id,name,age from a where 1<>1//前提是row_id,name,age都是a表的列

4、復(fù)制表的指定字段及這些指定字段的數(shù)據(jù)的sql

create table b as select row_id,name,age from a

以上語句雖然能夠很容易的根據(jù)a表結(jié)構(gòu)復(fù)制創(chuàng)建b表，但是a表的索引等卻復(fù)制不了，需要在b中手動建立。

5、insert into 會將查詢結(jié)果保存到已經(jīng)存在的表中

insert into t2(column1, column2,....)select column1, column2,....from t1rm_data001.dbf'