oracle語法大全.doc

第一篇 基本操作-解鎖用戶 alter user 用戶 account unlock;-鎖定用戶 alter user 用戶 account lock;alter user scott account unlock;-創(chuàng)建一個(gè)用戶yc 密碼為a create user 用戶名 identified by 密碼；create user yc identified by a;-登錄不成功，會(huì)缺少create session 權(quán)限，賦予權(quán)限的語法 grant 權(quán)限名 to 用戶；grant create session to yc;-修改密碼 alter user 用戶名 identified by 新密碼；alter user yc identified by b;-刪除用戶drop user yc ;-查詢表空間select *from dba_tablespaces;-查詢用戶信息select *from dba_users;-創(chuàng)建表空間create tablespace ycspacedatafile E:oracleappproduct11.2.0dbhome_1oradataycspace.dbfsize 2mautoextend on next 2m maxsize 5moffline ;-創(chuàng)建臨時(shí)表空間create temporary yctempspacetempfile E:oracleappproduct11.2.0dbhome_1oradataycspace.dbfsize 2mautoextend on next 2m maxsize 5moffline ;-查詢數(shù)據(jù)文件select *from dba_data_files;-修改表空間-1、修改表空間的狀態(tài)-默認(rèn)情況下是online,只有在非離線情況下才可以進(jìn)行修改alter tablespace ycspace offline ; -離線狀態(tài)，不允許任何對(duì)象對(duì)該表空間的使用，使用情況：應(yīng)用需要更新或維護(hù)的時(shí)候；數(shù)據(jù)庫備份的時(shí)候alter tablespace ycspace read write;-讀寫狀態(tài)alter tablespace ycspace online;alter tablespace ycspace read only; -只讀，可以查詢信息，可以刪除表空間的對(duì)象，但是不能創(chuàng)建對(duì)象和修改對(duì)象 。使用情況：數(shù)據(jù)存檔的時(shí)候-2、修改表空間的大小-增加文件的大小alter database datafile E:oracleappproduct11.2.0dbhome_1oradataycspace.dbf resize 10m;-增加數(shù)據(jù)文件alter tablespace ycspace add datafile E:oracleappproduct11.2.0dbhome_1oradataadd.dbf size 2m;-刪除表空間的數(shù)據(jù)文件alter tablespace 表空間的名字 drop datafile 數(shù)據(jù)文件名；-刪除表空間drop tablespace ycspace;-刪除表空間且表空間中的內(nèi)容和數(shù)據(jù)文件drop tablespace ycspace including contents and datafiles;-指定表空間 的 創(chuàng)建用戶的語法create user yc1 identified by a default tablespace ycspace temporary tablespace temp;-刪除用戶drop user yc1;-權(quán)限-賦予創(chuàng)建會(huì)話的權(quán)限grant create session to yc1;-創(chuàng)建一個(gè)表create table studentInfo(sid int,sname varchar2(10);-賦予yc1用戶創(chuàng)建表的權(quán)限grant create table to yc1;-賦予yc1使用表空間的權(quán)限grant unlimited tablespace to yc1;-系統(tǒng)權(quán)限-對(duì)象權(quán)限-插入insert into studentInfo values (2,abcd);-查詢select *from studentInfo;-修改update studentInfo set sid=1;-刪除delete studentInfo ;drop table studentInfo; -系統(tǒng)權(quán)限刪除表-賦權(quán)的語法-系統(tǒng)權(quán)限grant 權(quán)限名（系統(tǒng)權(quán)限或?qū)ο髾?quán)限,角色，all) to 用戶(角色，public) with admin option;-對(duì)象權(quán)限grant 權(quán)限名（系統(tǒng)權(quán)限或?qū)ο髾?quán)限,角色，all) on 用戶(角色，public) with grant option;-收權(quán)語法-系統(tǒng)權(quán)限r(nóng)evoke 權(quán)限名（系統(tǒng)權(quán)限或?qū)ο髾?quán)限,角色，all) from 用戶(角色，public) with admin option;-對(duì)象權(quán)限r(nóng)evoke 權(quán)限名（系統(tǒng)權(quán)限或?qū)ο髾?quán)限,角色，all) from 用戶(角色，public) with grant option;-賦予創(chuàng)建用戶的權(quán)限并且把這個(gè)權(quán)限傳遞下去，即yc1可以給別人賦權(quán)grant create user to yc1 with admin option;-收回權(quán)限,只能收回scottd ,不能收回由scott賦權(quán)的yc1的權(quán)限r(nóng)evoke create user from scott;-查看用戶所具有的權(quán)限select *from user_sys_privs;-對(duì)象權(quán)限詳解select * from emp;-使用yc1來查詢scott里面的emp表select * from scott.emp;-賦予yc1查詢emp表和插入的權(quán)限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;-對(duì)scott的emp表添加數(shù)據(jù)insert into scott.emp(empno,ename) value(111,acv);update scott.emp set ename=ycwhere empno=111;-賦予查詢、賦予刪除、添加、修改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代表所有的對(duì)象權(quán)限select *from scott.emp;select *from scott.dept;insert into scott.dept values(50,企事業(yè)文化部,bumen);-查看角色-dba:數(shù)據(jù)庫管理員，系統(tǒng)最高權(quán)限，可以創(chuàng)建數(shù)據(jù)結(jié)構(gòu)（表空間等）-resource:可以創(chuàng)建實(shí)體（表、視圖），不可以創(chuàng)建數(shù)據(jù)庫的結(jié)構(gòu)-connect:連接的權(quán)限，可以登錄數(shù)據(jù)庫，但是不可以創(chuàng)建實(shí)體和不可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)select *from role_sys_privs;grant connect to yc1;-將可以連接的角色賦予給yc1，則yc1就是應(yīng)該可以連接數(shù)據(jù)庫的人，類似于 create session 。create table StuInfos(sid int);select *from StuInfos;create table stuInfo(sid int primary key , -主鍵 primary key 非空且唯一 （主鍵約束）sname varchar2(10) not null, -姓名不能為空,（非空約束）sex char(2) check(sex in(男,女), -（檢查約束），check,age number(3,1) constraint ck_stuInfo_age check(age10 and age=2;-修改記錄的語法-update 表名 set 字段=值 where 條件update classInfo set cname=三班; -會(huì)修改所有該字段update classInfo set cname=四班 where cid=1;update classInfo set cname=五班, stasuts =未畢業(yè) where cid=3;-alter table classInfo drop constraint SYS_C0011213;-添加多個(gè)時(shí)可以使用序列-用序列來做自動(dòng)增長(zhǎng)create sequence seq_classInfo_cid start with 1001 increment by 1;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,九班,未畢業(yè));insert into classInfo values(seq_classInfo_cid.Nextval,十班,未畢業(yè));create table classInfo2(cid int primary key, -班級(jí)idcname varchar2(20) not null unique , -班級(jí)名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 classInfo2 drop constraint SYS_C0011213;select seq_classInfo_cid.nextval from dual;select seq_classInfo_cid.Currval from dual;-直接創(chuàng)建一個(gè)新表，并拿到另一個(gè)表其中的數(shù)據(jù)create table newTable as select cname,cid from classInfo;create table newTable1 as select *from classInfo;select *from newTable;select *from newTable1;insert into newTable1 values(1008,dg,);第二篇：高級(jí)操作直接在使用scott登陸，進(jìn)行查詢操作-簡(jiǎn)單查詢select *from emp;select empno as id,ename as name from emp;select empno 編號(hào),ename 姓名 from emp;-去除重復(fù)select job from emp;select distinct job from emp;select job,deptno from emp;select distinct job,deptno from emp;-字符串的連接select 員工編號(hào)是 |empno | 姓名是 |ename |工作是|job from emp;-乘法select ename,sal *12 from emp;-加減乘除都類似-限定查詢-獎(jiǎng)金大于1500的select *from emp where sal1500;-有獎(jiǎng)金的select *from emp where comm is not null;-沒有獎(jiǎng)金的select *from emp where comm is null;-有獎(jiǎng)金且大于1500的select *from emp where sal1500 and comm is not null;-工資大于1500或者有獎(jiǎng)金的select *from emp where sal1500 or comm is not null;-工資不大于1500且沒獎(jiǎng)金的select *from emp where sal1500 or comm is not null);-工資大于1500但是小于3000的select *from emp where sal1500 and sal3000;select *from emp where sal between 1500 and 3000; -between是閉區(qū)間，是包含1500和3000的-時(shí)間區(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;-查詢員工編號(hào)select *from 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個(gè)，其他的都可以查-模糊查詢select *from emp where ename like _M%; -第2個(gè)字母為M的select *from emp where ename like %M%;select *from emp where ename like %; -全查詢-不等號(hào)的用法select * from emp where empno !=7369;select *from emp where empno 7369;-對(duì)結(jié)果集排序-查詢工資從低到高select *from emp order by sal asc;select *from emp order by sal desc,hiredate desc; -asc 當(dāng)導(dǎo)游列相同時(shí)就按第二個(gè)來排序-字符函數(shù)select *from dual;-偽表select 2*3 from dual;select sysdate from dual;-變成大寫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,1,3) from dual; -索引從1開始-獲取字符串長(zhǎng)度select length(hello) from dual;-字符串替換select replace(hello,l,x) from dual; -把l替換為x-通用函數(shù)-數(shù)值函數(shù)-四舍五入select round(12.234) from dual;-取整的四舍五入 12select round (12.657,2) from dual; -保留2位小數(shù)select trunc(12.48) from dual;-取整select trunc(12.48675，2) from dual; -保留2位小數(shù)-取余select mod(10,3) from dual;-10/3取余 =1-日期函數(shù)-日期-數(shù)字=日期 日期+數(shù)字=日期 日期-日期=數(shù)字-查詢員工進(jìn)入公司的周數(shù)select ename,round(sysdate -hiredate)/7) weeks from emp;-查詢所有員工進(jìn)入公司的月數(shù)select ename,round(months_between(sysdate,hiredate) months from emp;-求三個(gè)月后的日期select add_months(sysdate,6) from dual;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;-轉(zhuǎn)換函數(shù)select ename ,to_char(hiredate,yyyy) 年，to_char(hiredate,mm)月，to_char(hiredate,dd) 日from emp;select to_char(10000000,$999,999,999) from emp;select to_number(20)+to_number(80) from dual; -數(shù)字相加-查詢員工年薪select ename,(sal*12+nvl(comm,0) yearsal from emp; -空和任何數(shù)計(jì)算都是空-Decode函數(shù)，類似if else if (常用）select decode(1,1,one,2,two,no name) from dual;-查詢所有職位的中文名select ename, decode(job,CLERK,業(yè)務(wù)員,SALESMAN,銷售,MANAGER,經(jīng)理,ANALYST,分析員,PRESIDENT,總裁,無業(yè))from emp;select ename,casewhen job = CLERK then業(yè)務(wù)員when job = SALESMAN then銷售when job = MANAGER then經(jīng)理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.deptno;select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;-查詢出雇員的編號(hào)，姓名，部門編號(hào)，和名稱，地址select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;-查詢出每個(gè)員工的上級(jí)領(lǐng)導(dǎo)select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno;select e.empno,e.ename,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;-查詢出每個(gè)部門的員工/*分析：部門表是全量表，員工表示非全量表，在做連接條件時(shí)，全量表在非全量表的哪端，那么連接時(shí)全量表的連接條件就在等號(hào)哪斷*/-左連接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 SMITH;-查詢工資比公司平均工資高的員工的員工號(hào)，姓名和工資select t.empno,t.ename,t.salfrom emp twhere t.sal(select avg(sal) from emp);-查詢各部門中工資比本部門平均工資高的員工號(hào)，姓名和工資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.sala.avgsal and t.deptno=a.deptno;-查詢姓名中包含字母u的員工在相同部門的員工的員工號(hào)和姓名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(select 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,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后面絕對(duì)不能有select d.dname, d.loc, count(e.empno) from emp e, dept d where 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 10;select *from (select *from emp) where rownum0;-索引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是對(duì)sql語言的過程化擴(kuò)展-declarebegindbms_output.put_line(hello world);end;-declareage number(3);marry boolean := true; -boolean不能直接輸出pname varchar2(10) := re jeknc;beginage := 20;dbms_output.put_line(age);if marry thendbms_o