oracle語法大全

1、第一篇 基本操作-解鎖用戶 alter user 用戶 account unlock;-鎖定用戶 alter user 用戶 account lock;alter user scott account unlock;-創(chuàng)建一個用戶yc 密碼為a create user 用戶名 identified by 密碼；create user yc identified by a;-登錄不成功，會缺少create session 權限，賦予權限的語法 grant 權限名 to 用戶；grant create session to yc;-修改密碼 alter user 用戶名 identified by

2、 新密碼；alter user yc identified by b;-刪除用戶drop user yc ;-查詢表空間select *from dba_tablespaces;-查詢用戶信息select *from dba_users;-創(chuàng)建表空間create tablespace ycspacesize 2mautoextend on next 2m maxsize 5moffline ;-創(chuàng)建臨時表空間create temporary yctempspacesize 2mautoextend on next 2m maxsize 5moffline ;-查詢數據文件select *fro

3、m dba_data_files;-修改表空間-1、修改表空間的狀態(tài)-默認情況下是online,只有在非離線情況下才可以進行修改alter tablespace ycspace offline ; -離線狀態(tài)，不允許任何對象對該表空間的使用，使用情況：應用需要更新或維護的時候；數據庫備份的時候alter tablespace ycspace read write;-讀寫狀態(tài)alter tablespace ycspace online;alter tablespace ycspace read only; -只讀，可以查詢信息，可以刪除表空間的對象，但是不能創(chuàng)建對象和修改對象 。使用情況：數據

4、存檔的時候-2、修改表空間的大小-增加文件的大小-增加數據文件-刪除表空間的數據文件alter tablespace 表空間的名字 drop datafile 數據文件名；-刪除表空間drop tablespace ycspace;-刪除表空間且表空間中的內容和數據文件drop tablespace ycspace including contents and datafiles;-指定表空間 的 創(chuàng)建用戶的語法create user yc1 identified by a default tablespace ycspace temporary tablespace temp;-刪除用戶dr

5、op user yc1;-權限-賦予創(chuàng)建會話的權限grant create session to yc1;-創(chuàng)建一個表create table studentInfo(sid int,sname varchar2(10);-賦予yc1用戶創(chuàng)建表的權限grant create table to yc1;-賦予yc1使用表空間的權限grant unlimited tablespace to yc1;-系統(tǒng)權限-對象權限-插入insert into studentInfo values (2,'abcd');-查詢select *from studentInfo;-修改update

6、studentInfo set sid=1;-刪除delete studentInfo ;drop table studentInfo; -系統(tǒng)權限刪除表-賦權的語法-系統(tǒng)權限grant 權限名（系統(tǒng)權限或對象權限,角色，all) to 用戶(角色，public) with admin option;-對象權限grant 權限名（系統(tǒng)權限或對象權限,角色，all) on 用戶(角色，public) with grant option;-收權語法-系統(tǒng)權限revoke 權限名（系統(tǒng)權限或對象權限,角色，all) from 用戶(角色，public) with admin option;-對象權限

7、revoke 權限名（系統(tǒng)權限或對象權限,角色，all) from 用戶(角色，public) with grant option;-賦予創(chuàng)建用戶的權限并且把這個權限傳遞下去，即yc1可以給別人賦權grant create user to yc1 with admin option;-收回權限,只能收回scottd ,不能收回由scott賦權的yc1的權限revoke create user from scott;-查看用戶所具有的權限select *from user_sys_privs;-對象權限詳解select * from emp;-使用yc1來查詢scott里面的emp表select

8、 * from scott.emp;-賦予yc1查詢emp表和插入的權限grant select on emp to yc1;grant insert on emp to yc1;grant update(empno,ename) on emp to yc1;grant delete on emp to yc1;-對scott的emp表添加數據insert into scott.emp(empno,ename) value(111,'acv');update scott.emp set ename='yc'where empno=111;-賦予查詢、賦予刪除、添

9、加、修改grant select on 表名 to 用戶-grant select,delete,update,insert on 表名 to 用戶grant select,delete,update,insert on emp to yc1;grant all on dept to yc1; -all代表所有的對象權限select *from scott.emp;select *from scott.dept;insert into scott.dept values(50,'企事業(yè)文化部','bumen');-查看角色-dba:數據庫管理員，系統(tǒng)最高權限，可

10、以創(chuàng)建數據結構（表空間等）-resource:可以創(chuàng)建實體（表、視圖），不可以創(chuàng)建數據庫的結構-connect:連接的權限，可以登錄數據庫，但是不可以創(chuàng)建實體和不可以創(chuàng)建數據庫結構select *from role_sys_privs;grant connect to yc1;-將可以連接的角色賦予給yc1，則yc1就是應該可以連接數據庫的人，類似于 create session 。create table StuInfos(sid int);select *from StuInfos;create table stuInfo(sid int primary key , -主鍵 primary

11、 key 非空且唯一 （主鍵約束）sname varchar2(10) not null, -姓名不能為空,（非空約束）sex char(2) check(sex in('男','女'), -（檢查約束），check,age number(3,1) constraint ck_stuInfo_age check(age>10 and age<100) , -也可以用varchar ；age between 10 and 100 ,在10和100之間，是一個閉區(qū)間tel number(15) unique not null, -唯一約束，address

12、 varchar2(200) default '什么鬼')insert into stuInfo values（3,'大大','男',18，4321543,default);insert into stuInfo values(1,'張三','男',10);select *from stuInfo;drop table stuInfo;create table classInfo(cid int primary key, -班級idcname varchar2(20) not null unique -班級名)cr

13、eate table stuInfo(sid int primary key,sname varchar2(20),cid int constraint fofk_stuInfo_cid references classInfo(cid) on delete cascade)insert into classInfo values(1,'1班');insert into classInfo values(2,'2班');insert into classInfo values(3,'3班');insert into classInfo value

14、s(4,'4班');select *from classInfo;select *from stuInfo;insert into stuInfo values(1001,'張三',2);insert into stuInfo values(1002,'張四',4);update classInfo set cid=1 where cid=8;drop table stuInfo;-要先刪除這個drop table classInfo; -再刪除這個delete classInfo where cid=4 ;-同時刪除這兩個表中的4-刪除用戶的時

15、候drop user yc1 cascade -刪除用戶的同時把它創(chuàng)建的對象都一起刪除-修改表-1、添加表中字段-alter table 表名 add 字段名 類型alter table classInfo add status varchar2(10) default '未畢業(yè)'-2、修改已有字段的數據類型-alter table 表名 modify 字段名 類型alter table classInfo modify status number(1)-3、修改字段名-alter table 表名 rename column 舊字段名 to 新的字段名alter table

16、classInfo rename column cname to 班級名;-4、刪除字段-alter table 表名 drop column 字段名alter table classInfo drop column status ;-5、修改表名-rename 舊表名 to 新表名rename classInfo to 班級信息；-刪除表-1、截斷表效率高，每刪除一次會產生一次日志 2、截斷會釋放空間，而delete不會釋放空間-刪除表結構和數據drop table 表名；-刪除表中所有數據truncate table classInfo;delete classInfo;create ta

17、ble classInfo(cid int primary key, -班級idcname varchar2(20) not null unique , -班級名stasuts varchar2(100);select *from classInfo;-數據的操作-增加數據語法-insert into 表名(列名，.) values (對應的數據的值）；insert into classInfo values(1,'一班','未畢業(yè)');-需要按照表結構的順序插入insert into classInfo values(4,'六班','未

18、畢業(yè)');insert into classInfo(cname,cid) values('二班',2); -需要按照括號中的順序插入，但是 not null primary key 必須插入的。insert into classInfo(cname,cid) values('三班',3);-刪除的語法-delete 表名 where 條件delete classInfo where cid>=2;-修改記錄的語法-update 表名 set 字段='值' where 條件update classInfo set cname=

19、9;三班' -會修改所有該字段update classInfo set cname='四班' where cid=1;update classInfo set cname='五班', stasuts ='未畢業(yè)' where cid=3;-alter table classInfo drop constraint SYS_C0011213;-添加多個時可以使用序列-用序列來做自動增長create sequence seq_classInfo_cid start with 1001 increment by 1;insert into cl

20、assInfo values(seq_classInfo_cid.Nextval,'七班','未畢業(yè)');insert into classInfo values(seq_classInfo_cid.Nextval,'八班','未畢業(yè)');insert into classInfo values(seq_classInfo_cid.Nextval,'九班','未畢業(yè)');insert into classInfo values(seq_classInfo_cid.Nextval,'十班

21、9;,'未畢業(yè)');create table classInfo2(cid int primary key, -班級idcname varchar2(20) not null unique , -班級名stasuts varchar2(100);select *from classInfo2;drop table classInfo2;insert into classInfo2 select *from classInfo;insert into classInfo(cname,cid) select cname,cid from classInfo;alter table

22、classInfo2 drop constraint SYS_C0011213;select seq_classInfo_cid.nextval from dual;select seq_classInfo_cid.Currval from dual;-直接創(chuàng)建一個新表，并拿到另一個表其中的數據create table newTable as select cname,cid from classInfo;create table newTable1 as select *from classInfo;select *from newTable;select *from newTable1;i

23、nsert into newTable1 values(1008,'dg','');第二篇：高級操作直接在使用scott登陸，進行查詢操作-簡單查詢select *from emp;select empno as id,ename as name from emp;select empno 編號,ename 姓名 from emp;-去除重復select job from emp;select distinct job from emp;select job,deptno from emp;select distinct job,deptno from emp;

24、-字符串的連接select '員工編號是' |empno | '姓名是' |ename |'工作是'|job from emp;-乘法select ename,sal *12 from emp;-加減乘除都類似-限定查詢-獎金大于1500的select *from emp where sal>1500;-有獎金的select *from emp where comm is not null;-沒有獎金的select *from emp where comm is null;-有獎金且大于1500的select *from emp where

25、 sal>1500 and comm is not null;-工資大于1500或者有獎金的select *from emp where sal>1500 or comm is not null;-工資不大于1500且沒獎金的select *from emp where sal<=1500 and comm is null;select *from emp where not (sal >1500 or comm is not null);-工資大于1500但是小于3000的select *from emp where sal>1500 and sal<30

26、00;select *from emp where sal between 1500 and 3000; -between是閉區(qū)間，是包含1500和3000的-時間區(qū)間select *from emp where hiredate between to_date('1981-01-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');-查詢雇員名字select *from emp where ename='SMITH'-查詢員工編號select *from

27、 emp where empno=7369 or empno=7499 or empno=7521;select *from emp where empno in(7369,7499,7521);select *from emp where empno not in(7369,7499,7521); -排除這3個，其他的都可以查-模糊查詢select *from emp where ename like '_M%' -第2個字母為M的select *from emp where ename like '%M%'select *from emp where ena

28、me like '%' -全查詢-不等號的用法select * from emp where empno !=7369;select *from emp where empno<> 7369;-對結果集排序-查詢工資從低到高select *from emp order by sal asc;select *from emp order by sal desc,hiredate desc; -asc 當導游列相同時就按第二個來排序-字符函數select *from dual;-偽表select 2*3 from dual;select sysdate from dua

29、l;-變成大寫select upper('smith') from dual;-變成小寫select lower('SMITH') from dual;-首字母大寫select initcap('smith') from dual;-連接字符串select concat('jr','smith') from dual; -只能在oracle中使用select 'jr' |'smith' from dual; -推薦使用-截取字符串select substr('hello

30、9;,1,3) from dual; -索引從1開始-獲取字符串長度select length('hello') from dual;-字符串替換select replace('hello','l','x') from dual; -把l替換為x-通用函數-數值函數-四舍五入select round(12.234) from dual;-取整的四舍五入 12select round (12.657,2) from dual; -保留2位小數select trunc(12.48) from dual;-取整select trunc(

31、12.48675，2) from dual; -保留2位小數-取余select mod(10,3) from dual;-10/3取余 =1-日期函數-日期-數字=日期 日期+數字=日期 日期-日期=數字-查詢員工進入公司的周數select ename,round(sysdate -hiredate)/7) weeks from emp;-查詢所有員工進入公司的月數select ename,round(months_between(sysdate,hiredate) months from emp;-求三個月后的日期select add_months(sysdate,6) from dual;

32、select next_day(sysdate,'星期一') from dual; -下星期select last_day(sysdate) from dual; -本月最后一天select last_day(to_date('1997-1-23','yyyy-MM-dd') from dual;-轉換函數select ename ,to_char(hiredate,'yyyy') 年，to_char(hiredate,'mm')月，to_char(hiredate,'dd') 日from emp;

33、select to_char(10000000,'$999,999,999') from emp;select to_number('20')+to_number('80') from dual; -數字相加-查詢員工年薪select ename,(sal*12+nvl(comm,0) yearsal from emp; -空和任何數計算都是空-Decode函數，類似if else if (常用）select decode(1,1,'one',2,'two','no name') from dual

34、;-查詢所有職位的中文名select ename, decode(job,'CLERK','業(yè)務員','SALESMAN','銷售','MANAGER','經理','ANALYST','分析員','PRESIDENT','總裁','無業(yè)')from emp;select ename,casewhen job = 'CLERK' then'業(yè)務員'when job = 'SALESM

35、AN' then'銷售'when job = 'MANAGER' then'經理'when job = 'ANALYST' then'分析員'when job = 'PRESIDENT' then'總裁'else'無業(yè)'endfrom emp;-多表查詢select *from dept;select *from emp,dept order by emp.deptno;select *from emp e,dept d where e.deptno=d.de

36、ptno;select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;-查詢出雇員的編號，姓名，部門編號，和名稱，地址select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;-查詢出每個員工的上級領導select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno;select e.empno,e.ename,

37、d.dnamefrom emp e,dept d ,salgrade s, emp e1where e.deptno=d.deptnoand e.sal between s.losaland s.hisaland e.mgr=e1.empno;select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno(+) ;-外連接select *from emp order by deptno;-查詢出每個部門的員工/*分析：部門表是全量表，員工表示非全量表，在做連接條件時，全量表在非全量表的哪端，那么連接

38、時全量表的連接條件就在等號哪斷*/-左連接select * from dept d,emp e where d.deptno=e.deptno(+) order by e.deptno;-右連接select * from emp e，dept d where e.deptno(+)=d.deptno order by e.deptno;-作業(yè)-查詢與smith相同部門的員工姓名和雇傭日期select *from emp twhere t.deptno= (select e.deptno from emp e where e.ename='SMITH')and t.ename&l

39、t;> 'SMITH'-查詢工資比公司平均工資高的員工的員工號，姓名和工資select t.empno,t.ename,t.salfrom emp twhere t.sal>(select avg(sal) from emp);-查詢各部門中工資比本部門平均工資高的員工號，姓名和工資select t.empno,t.ename,t.salfrom emp t, (select avg(e.sal) avgsal,e.deptno from emp e group by e.deptno) awhere t.sal>a.avgsal and t.deptno=a

40、.deptno;-查詢姓名中包含字母u的員工在相同部門的員工的員工號和姓名select t.empno,t.ename from emp twhere t.deptno in( select e.deptno from emp e where e.ename like '%U%')and t.empno not in ( select e.empno from emp e where e.ename like '%U%') ;-查詢管理者是king的員工姓名和工資select t.ename,t.sal from emp twhere t.mgr in(sele

41、ct e.empno from emp e where e.ename='KING');-sql1999語法select *from emp join dept using(deptno) where deptno=20;select *from emp natural join dept;select *from emp e join dept d on e.deptno=d.deptno;select *from dept;select *from dept d left join emp e on d.deptno=e.deptno;select *from dept d

42、,emp e where d.deptno=e.deptno(+);-分組select count(empno) from emp group by deptno;select deptno,job,count(*) from emp group by deptno,job order by deptno;select *from EMP for UPDATE;-group by 后面有的字段，select后才可以有，group by后面沒有的字段，select后面絕對不能有select d.dname, d.loc, count(e.empno) from emp e, dept d whe

43、re e.deptno = d.deptno group by d.dname, d.loc ;-子查詢select *from emp t where t.sal>(select *from emp e where e.empno=7654);select rownum ,t.* from emp t where rownum <6 ;-pagesize 5select *from(select rownum rw,a.* from (select *from emp ) a where rownum <16) b where b.rw>10;select *from

44、 (select *from emp) where rownum>0;-索引create index person_index on person(p_name);-視圖create view view2 as select *from emp t where t.deptno=20;select *from view2;-pl/sql-plsql是對sql語言的過程化擴展-declarebegindbms_output.put_line('hello world');end;-declareage number(3);marry boolean := true; -bo

45、olean不能直接輸出pname varchar2(10) := 're jeknc'beginage := 20;dbms_output.put_line(age);if marry thendbms_output.put_line('true');elsedbms_output.put_line('false');end if ;dbms_output.put_line(pname);end;-常量和變量-引用變量,引用表中的字段的類型Myname emp.ename%type; -使用into來賦值declarepname emp.enam

46、e%type;beginselect t.ename into pname from emp t where t.empno=7369;dbms_output.put_line(pname);end;-記錄型變量Emprec emp%rowtype; -使用into來賦值declareEmprec emp%rowtype;beginselect t.* into Emprec from emp t where t.empno=7369;dbms_output.put_line(Emprec.empno | ' '|Emprec.ename|' '|Emprec.

47、job);end;-if分支語法1：IF 條件 THEN 語句1;語句2;END IF;語法2：IF 條件 THEN 語句序列1；ELSE 語句序列 2；END IF；語法3：IF 條件 THEN 語句;ELSIF 條件 THEN 語句;ELSE 語句;END IF;-1declarepname number:=#beginif pname = 1 thendbms_output.put_line('我是1');elsedbms_output.put_line('我不是1');end if;end;-2declarepname number := #begini

48、f pname = 1 thendbms_output.put_line('我是1');elsif pname = 2 thendbms_output.put_line('我是2');elsedbms_output.put_line('我不是12');end if;end;-loop循環(huán)語句語法2：LoopEXIT when 條件;End loop-1declarepnum number(4):=0;beginwhile pnum < 10 loopdbms_output.put_line(pnum);pnum := pnum + 1;en

49、d loop;end;-2 (最常用的循環(huán))declarepnum number(4):=0;beginloopexit when pnum=10;pnum:=pnum+1;dbms_output.put_line(pnum);end loop;end;-3declarepnum number(4);beginfor pnum in 1 . 10 loopdbms_output.put_line(pnum);end loop;end;-游標語法：CURSOR 游標名 (參數名 數據類型,參數名 數據類型,.) IS SELECT 語句;例如：cursor c1 is select ename

50、from emp;declarecursor c1 isselect * from emp;emprec emp%rowtype;beginopen c1;loopfetch c1into emprec;exit when c1%notfound;dbms_output.put_line(emprec.empno | ' ' | emprec.ename);end loop;close c1; -要記得關閉游標end;-例外-異常，用來增強程序的健壯性和容錯性- no_data_found (沒有找到數據)-too_many_rows (select into語句匹配多個行)-

51、zero_pide ( 被零除)-value_error (算術或轉換錯誤)-timeout_on_resource (在等待資源時發(fā)生超時)-寫出被0除的例外程序declarepnum number(4) := 10;beginpnum := pnum / 0;exceptionwhen zero_pide thendbms_output.put_line('被0除了');when value_error thendbms_output.put_line('算術或轉換錯誤');when others thendbms_output.put_line('

52、其他異常');end;-自定義異常-No_data exception;-要拋出raise no_data;declarecursor c1 isselect * from emp t where t.deptno = 20;no_data exception;emprec emp%rowtype;beginopen c1;loopfetch c1into emprec;if c1%notfound thenraise no_data;elsedbms_output.put_line(emprec.empno | ' ' | emprec.ename);end if;e

53、nd loop;close c1;exceptionwhen no_data thendbms_output.put_line('無員工');when others thendbms_output.put_line('其他異常');end;-存儲過程語法：create or replace PROCEDURE 過程名(參數名 in/out 數據類型)ASbeginPLSQL子程序體；End;或者create or replace PROCEDURE 過程名(參數名 in/out 數據類型)isbeginPLSQL子程序體；End 過程名;-創(chuàng)建一個存儲過程helloworldcreate or replace procedure helloworld isbegindbms_output.put_line('hello world');end helloworld;-創(chuàng)建一個漲工資的create or replace procedure addsal(eno in emp.empno%type) isemprec emp%rowtype;beginselect * i