oracle语法大全

1、第一篇 基本操作-解锁用户 alter user 用户 account unlock;-锁定用户 alter user 用户 account lock;alter user scott account unlock;-创建一个用户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;-创建表空间create tablespace ycspacesize 2mautoextend on next 2m maxsize 5moffline ;-创建临时表空间create temporary yctempspacesize 2mautoextend on next 2m maxsize 5moffline ;-查询数据文件select *fro

3、m dba_data_files;-修改表空间-1、修改表空间的状态-默认情况下是online,只有在非离线情况下才可以进行修改alter tablespace ycspace offline ; -离线状态，不允许任何对象对该表空间的使用，使用情况：应用需要更新或维护的时候；数据库备份的时候alter tablespace ycspace read write;-读写状态alter tablespace ycspace online;alter tablespace ycspace read only; -只读，可以查询信息，可以删除表空间的对象，但是不能创建对象和修改对象 。使用情况：数据

4、存档的时候-2、修改表空间的大小-增加文件的大小-增加数据文件-删除表空间的数据文件alter tablespace 表空间的名字 drop datafile 数据文件名；-删除表空间drop tablespace ycspace;-删除表空间且表空间中的内容和数据文件drop tablespace ycspace including contents and datafiles;-指定表空间 的 创建用户的语法create user yc1 identified by a default tablespace ycspace temporary tablespace temp;-删除用户dr

5、op user yc1;-权限-赋予创建会话的权限grant create session to yc1;-创建一个表create table studentInfo(sid int,sname varchar2(10);-赋予yc1用户创建表的权限grant create table to yc1;-赋予yc1使用表空间的权限grant unlimited tablespace to yc1;-系统权限-对象权限-插入insert into studentInfo values (2,'abcd');-查询select *from studentInfo;-修改update

6、studentInfo set sid=1;-删除delete studentInfo ;drop table studentInfo; -系统权限删除表-赋权的语法-系统权限grant 权限名（系统权限或对象权限,角色，all) to 用户(角色，public) with admin option;-对象权限grant 权限名（系统权限或对象权限,角色，all) on 用户(角色，public) with grant option;-收权语法-系统权限revoke 权限名（系统权限或对象权限,角色，all) from 用户(角色，public) with admin option;-对象权限

7、revoke 权限名（系统权限或对象权限,角色，all) from 用户(角色，public) with grant option;-赋予创建用户的权限并且把这个权限传递下去，即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,'企事业文化部','bumen');-查看角色-dba:数据库管理员，系统最高权限，可

10、以创建数据结构（表空间等）-resource:可以创建实体（表、视图），不可以创建数据库的结构-connect:连接的权限，可以登录数据库，但是不可以创建实体和不可以创建数据库结构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之间，是一个闭区间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 -删除用户的同时把它创建的对象都一起删除-修改表-1、添加表中字段-alter table 表名 add 字段名 类型alter table classInfo add status varchar2(10) default '未毕业'-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,'一班','未毕业');-需要按照表结构的顺序插入insert into classInfo values(4,'六班','未

18、毕业');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 ='未毕业' 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,'七班','未毕业');insert into classInfo values(seq_classInfo_cid.Nextval,'八班','未毕业');insert into classInfo values(seq_classInfo_cid.Nextval,'九班','未毕业');insert into classInfo values(seq_classInfo_cid.Nextval,'十班

21、9;,'未毕业');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;-直接创建一个新表，并拿到另一个表其中的数据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是闭区间，是包含1500和3000的-时间区间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','业务员','SALESMAN','销售','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁','无业')from emp;select ename,casewhen job = 'CLERK' then'业务员'when job = 'SALESM

35、AN' then'销售'when job = 'MANAGER' then'经理'when job = 'ANALYST' then'分析员'when job = 'PRESIDENT' then'总裁'else'无业'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;-作业-查询与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循环语句语法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 (最常用的循环)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 (在等待资源时发生超时)-写出被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 过程名;-创建一个存储过程helloworldcreate or replace procedure helloworld isbegindbms_output.put_line('hello world');end helloworld;-创建一个涨工资的create or replace procedure addsal(eno in emp.empno%type) isemprec emp%rowtype;beginselect * i