第一篇：Oracle常用語句

Oracle:

1、創建user用戶： create user demo identified by demo account unlock默認是lock;

2、權限回收和設置： grant connect|resource to demo;revoke connect|resource from demo;

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

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

6、Oracle 服務開啟 startup open;關閉服務shutdowm;

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

8、在oracle中，客戶端把SQL語句發送給服務器，服務器對SQL語句進行編譯、執行，把執行的結果返回給客戶端

? 數據定義語言（DDL），包括CREATE（創建）命令、ALTER（修改）命令、DROP

（刪除）命令等。

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

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

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

令、ROLLBACK（回滾）命令。

? 數據控制語言（DCL），GRANT（授權）命令、REVOKE（撤銷）命令。

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

10、創建表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 默認是升序排列

13、一個數據庫中的所有表select * from tab;desc emp;查看表結構

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

15、一個數據庫中訪問另一個數據庫中的表比如在sys數據庫中select * from scott.emp;

16、根據結果集創建表 create table demo as select * from infos;

17、根據結果集復制表結構 create table demo as select * from infos where 1=2;

18、INSERT向表中插入一個結果集 insert into demo select * from infos;

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

20、Commit 是提交用戶操作的結果只有提交了 數據才真正的更新到表中 別的用戶才

能看到最后操作的結果

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

22、刪除數據 delete from infos where stuid-'33';

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

24、打開一個終端 刪除一條數據，然后再打開另一個終端，會發現數據依然存在，在oracle中，每個連接到數據庫的窗口，都會與數據庫建立一個session，一個session對數據的操作 不會馬上反應到數據庫的真實數據中去，可以允許回滾，當一個session提交所有操作后，數據庫才會做真正的修改...25、提交事務commit回滾rollback;回滾是在提交之前使用，如果已經提交了，回滾也沒用...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在運行上述代碼時輸出一個很奇異的結果:

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

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

42、Oracle 對大小寫敏感，所以查詢的時候應該大寫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%';_表示一個字符，% 表示多個字符

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;連接兩個字符串

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;從倒數第三個一直到最后截取

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

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

52、統計一張表中的記錄數select count(*)from emp;

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

54、使用where也可以同時使用having，where 是對行進行篩選，而having是對組進行篩選having 是放在group by 后面 進行組篩選

55、在組函數存在的時候就不要出現分組查詢語句了select deptno,sum(avg(sal))from emp order by deptno;這就錯了不要出現deptno，因為有組函數了

56、子查詢：查詢出工資比7654高，同時與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 關鍵字

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 就是顯示行數 每一行一個數，前五行 select rownum,ename from emp where rownum<=5;假如是十行中的后五行，那就只能先做一個子查詢select * from(select rownum r,ename from emp where rownum<=10)k where k.r>5;

64、約束的作用就是保證了數據的完整性關聯上的約束 如果要刪除表的話 應該先刪除子表 再刪除父表如果要強制刪除父表則drop table demo cascade constraint;還有一種是級聯刪除 父類刪除 子表也刪除drop table demo on delete cadcade;

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

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

10。。Demo.nextval序列的下一個內容demo.currval序列的當前內容

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

71、數據庫設計的三大范式：第一范式，保證數據表中的每個字段不可再分第二范式：。

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

第二篇：oracle常用語句

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

Oracel 中常使用的語法

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

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

SQL> grant connect,resource to a;

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

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

8、查詢表結構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;

用戶授權:

GRANT ALTER ANY INDEX TO “user_id ”

GRANT “dba ” TO “user_id ”;

ALTER USER “user_id ” DEFAULT ROLE ALL

創建用戶:

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

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

用戶密碼設定:

ALTER USER ”CMSDB “ IDENTIFIED BY ”pass_word “

表空間創建:

CREATE TABLESPACE ”table_space “ LOGGING DATAFILE

'C:ORACLEORADATAdbstable_space.ora' SIZE 5M

----------

1、查看當前所有對象:SQL > select * from tab;

2、建一個和a表結構一樣的空表

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、察看數據庫的大小，和空間使用情況

SQL > col tablespace format a20

SQL > select b.file_id文件ID,b.tablespace_name表空間,b.file_name物理文件名,b.bytes總字節數,(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--數據文件空間占用情況

4、查看現有回滾段及其狀態 ： SQL > col segment format a30

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

5、查看數據文件放置的路徑 ：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、顯示當前連接用戶 :SQL > show user7、把SQL*Plus當計算器 :SQL > select 100*20 from dual;

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

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

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

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

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

12、通過授權的方式來創建用戶 ：SQL > grant connect,resource to test identified by test;

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

---------

/* 向一個表格添加字段 */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 /* 修改表名 */

空值處理，有時要求列值不能為空 ：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);

注：只有當某列所有值都為空時，才能減小其列值寬度。只有當某列所有值都為

空時，才能改變其列值類型。

只有當某列所有值都為不空時，才能定義該列為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_開始的數據字典視圖包含當前用戶所擁有的信息, 查詢當前用戶所擁有的表信息:

select * from user_tables;

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

select * from all_tables;

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

4.查詢ORACLE用戶：

conn sys/change_on_installselect * from dba_users;

conn system/manager;select * from all_users;

5.創建數據庫用戶：CREATE USER user_name IDENTIFIED BY password;GRANT CONNECT TO user_name;GRANT RESOURCE TO user_name;

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

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

6.向建好的用戶導入數據表 :

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語句總結

1、時間查詢語句

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

2、單引號、雙引號

數字不需要

字符串用單引號

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

3、UPDATE

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

業務受理子系統BSS中，--客戶資料表

create table customers

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

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

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

...)

create unique index PK_customers on customers(customer_id)

由于某些原因，客戶所在城市這個信息并不準確，但是在客戶服務部的CRM子系統中，通過主動服務獲取了部分客戶20%的所在城市等準確信息，于是你將該部分信息提取至一張臨時表中：

create table tmp_cust_city

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

1)最簡單的形式

--經確認customers表中所有customer_id小于1000均為'北京'

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

update customers

setcity_name='北京'

wherecustomer_id<1000

2)兩表(多表)關聯update--僅在where字句中的連接

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

update customersa--使用別名

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

whereexists(select 1

fromtmp_cust_city b

whereb.customer_id=a.customer_id)

3)兩表(多表)關聯update--被修改值由另一個表運算而來

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、刪除這些具有重復字段的數據記錄：

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）命令的編輯與運行：

在命令提示符后輸入SQL命令并運行，以分號結束輸入；以斜杠結束輸入；以空行結束輸入；

利用SQL緩沖區進行PL/SQL塊的編輯和運行；

利用命令文件進行PL/SQL塊的編輯和運行。

數據庫查詢

用SELECT語句從表中提取查詢數據。語法為

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

說明：SELECT子句用于指定檢索數據庫的中哪些列，FROM子句用于指定從哪一個表或視圖中檢索數據。

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

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

ORDER BY 子句

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

連接查詢

利用SELECT語句進行數據庫查詢時，可以把多個表、視圖的數據結合起來，使得查詢結果的每一行中包含來自多個表達式或視圖的數據，這種操作被稱為連接查詢。

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

子查詢

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

基本數據類型（NUMBER,VARCHAR2,DATE）O

RACEL支持下列內部數據類型：

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

NUMBER 數值型。

LONG 變長字符數據，最長為2G字節。

DATE 日期型。

RAW 二進制數據，最長為255字節。

LONG RAW 變長二進制數據，最長為2G字節。

ROWID 二六進制串，表示表的行的唯一地址。

CHAR 定長字符數據，最長為255。

常用函數用法：

一個函數類似于一個算符，它操作數據項，返回一個結果。函數在格式上不同于算符，它個具有變元，可操作0個、一個、二個或多個變元，形式為：

函數名（變元，變元，…）

函數具有下列一般類形：

單行函數

分組函數

單行函數對查詢的表或視圖的每一行返回一個結果行。它有數值函數，字符函數，日期函數，轉換函數等。

分組函數返回的結果是基于行組而不是單行，所以分組函數不同于單行函數。在許多分組函數中可有下列選項：

DISTRNCT 該選項使分組函數只考慮變元表達式中的不同值。

ALL該選項使分組函數考慮全部值，包含全部重復。

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

5、數據操縱語言命令：

數據庫操縱語言（DML）命令用于查詢和操縱模式對象中的數據，它不隱式地提交當前事務。它包含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子句指明了要修改的數據庫是EMP,并用WHERE子句限制了只對名字(ENAME)為’MARTIN’的職工的數據進行修改,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命令只能刪除整行,而不能刪除某行中的部分數據.4）事務控制命令

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

回滾命令(ROLLBACK):消除上一個COMMIT命令后的所做的全部修改,使得數據庫的內容恢復到上一個COMMIT執行后的狀態.使用方法是:

SQL>ROLLBACK;

創建表、視圖、索引、同義詞、用戶。、表是存儲用戶數據的基本結構。

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

列定義

完整性約束

表所在表空間

存儲特性

可選擇的聚集

從一查詢獲得數據

語法如下：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);

更改表作用：

增加列

增加完整性約束

重新定義列（數據類型、長度、缺省值）

修改存儲參數或其它參數

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

顯式地分配一個范圍

2）、視圖

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

引入視圖有下列作用：

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

隱藏數據復雜性。

為數據提供另一種觀點。

促使ORACLE的某些操作在包含視圖的數據庫上執行，而不在另一個數據庫上執行。

3）、索引

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

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

按索引列的順序存取表。

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

例如：SQL>CREAT INDEX IC_EMP

ON CLUSTER EMPLOYEE

4）、同義詞

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

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

引用對象不需指出它所位于的數據庫。

為對象提供另一個名字。

建立同義詞：

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擴展PL/SQL簡介

PL/SQL概述。

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

DECLARE

---說明

BEGIN

---語句序列

EXCEPTION

---例外處理程序

END；

它有以下優點:

支持SQL;

生產率高;

性能好;

可稱植性;

與ORACLE集成.PL/SQL體系結構

PL/SQL運行系統是種技術，不是一種獨立產品，可認為這種技術是PL/SQL塊和子程序的一種機，它可接收任何有效的PL/SQL塊或子程序。如圖所示：

PL/SQL機可執行過程性語句，而將SQL語句發送到ORACLE服務器上的SQL語句執行器。在ORACLE預編譯程序或OCI程序中可嵌入無名的PL/SQL塊。如果ORACLE具有PROCEDURAL選件，有名的PL/SQL塊（子程序）可單獨編譯，永久地存儲在數據庫中，準備執行。

PL/SQL基礎：

PL/SQL有一字符集、保留字、標點、數據類型、嚴密語法等，它與SQL有相同表示，現重點介紹。

1）、數據類型：如下表所示

數據類型 子類型

純量類型 數值 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程序中可將值存儲在變量和常量中，當程序執行時，變量的值可以改變，而常量的值不能改變。

3）、程序塊式結構：

DECLARE

變量說明部分；

BEGIN

執行語句部分；

[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;

循環語句：

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 過程名(參數說明1，參數說明2，[局部說明]

BEGIN

執行語句；

END 過程名；

。)IS。

存儲函數：

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

執行語句；

END 函數名

Oracle SQL精妙SQL語句講解

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

--行列轉換 行轉列

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;

--行列轉換 列轉行

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;

--行列轉換 行轉列 合并

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;

--實現一條記錄根據條件多表插入 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;

--如果存在就更新，不存在就插入用一個語句實現 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);

--抽取/刪除重復記錄

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的不同適用環境--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 實現批量

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;

--常見的實現邏輯，隱含bug DECLARE

v_cnt NUMBER;BEGIN

--這里有并發性的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;

--高并發環境下，安全的實現邏輯 DECLARE

v_cnt NUMBER;BEGIN

--對指定的行取得lock

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

--在有lock的情況下繼續下面的操作

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--小計合計 SELECT CASE

WHEN a.deptno IS NULL THEN

'合計'

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

'小計'

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;

--當前行數據和前/后n行的數據比較 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;

一、數據表設計圖

二、創建語句

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

/** *查詢每一個員工的經理人及自己的名字 */

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

/** * 求那些人是經理人 */

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

/** *不準用組函數 求薪水的最高值 */

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

/** *求部門經理人中平均薪水最低的部門名稱 */

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))/** *求必普通員工的最高薪水還要高的經理人名稱 */

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

oracle復制表的sql語句

如下，表a是數據庫中已經存在的表，b是準備根據表a進行復制創建的表：

1、只復制表結構的sql

create table b as select * from a where 1<>12、即復制表結構又復制表中數據的sql

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

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

4、復制表的指定字段及這些指定字段的數據的sql

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

以上語句雖然能夠很容易的根據a表結構復制創建b表，但是a表的索引等卻復制不了，需要在b中手動建立。

5、insert into 會將查詢結果保存到已經存在的表中

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