oraclecode常用语句大全

--查询当前数据库名称 10

v$database 10

--查询所有表空间的名称 10

dba_tablespaces 10

--创建永久表空间 10

create tablespace 10

datafile 10

size 10

autoextend on 10

next 10

maxsize 10

--创建临时表空间 10

create temporary tablespace 10

--删除表空间 10

drop tablespace 10

including contents and datafiles 11

drop tablespace 11

--查询所有用户的名称 11

dba_users 11

--创建用户 11

create user 11

identified by 11

default tablespace 11

quota 11

temporary tablespace 11

--更改用户密码 11

alter user  identified by 11

alter user 11

--删除用户 11

drop user  cascade 11

drop user 11

--查询用户的系统权限 11

user_sys_privs 11

dba_sys_privs 11

--查询用户的对象权限 12

user_tab_privs 12

dba_tab_privs 12

--查询角色 12

dba_roles 12

--创建角色 12

create role 12

--为角色授权 12

grant to 12

grant 12

--为用户授权 13

--查询用户(角色)与角色的关系 13

dba_role_privs 13

--查询角色与权限的关系 13

--删除角色 13

drop role 13

--创建学生信息表 13

create table 13

--添加主键约束确保字段值不能为空且不能重复 13

primary key 13

--添加非空属性确保字段不能为空 14

not null 14

--添加检查约束确保字段只能填写指定数据 14

check 14

unique 14

--添加默认值 14

default 14

--删除表的列、添加表的列

 

--创建课程信息表 14

--创建学生成绩表 15

--添加外键约束 15

foreign key 15

--组合主键 16

--查询当前用户的可用数据表 16

--查询表所有信息 16

select 16

--仅查询多字段信息 16

--带时字段带表达式 16

--简单的数学运算 17

dual 17

--获得当前日期时间 17

sysdate 17

systimestamp 17

current_date 17

current_timestamp 17

--使用字段别名 17

as 17

--查询员工姓名和年收入 17

nvl(comm,0) 17

--字符串连接 18

|| 18

--查询时消除冗余 18

distinct 18

--同时查询部门编号与岗位 18

--带条件的查询 18

where 18

ename='CLARK 18

sal>1500 18

sal>1500 18

ename>'FORD' 19

where sal>=800 and sal<=1500 19

where sal between and 19

is null 19

is not null 19

where sal in (800,1100,1500) 19

--模糊查询 20

like ‘%A%’ 20

__A% 20

order by asc 20

desc 20

avg(sal) 21

group by 21

max(sal) 21

--交叉连接 22

cross join 22

--内连接 23

inner join 23

--三个表的嵌套查询 23

--求各个部门薪水最高的员工信息 25

伪列 25

rownum 25

--取出第十条记录之后的数据 25

--按薪水由高至低排列，找出第6-10名的员工信息 26

--保存数据 26

insert into 26

values 26

--同时插入多条记录 26

union all 26

--更新数据 27

update set 27

update 27

rollback; 27

--删除数据 27

delete from 27

commit; 27

--数据控制语言 27

grant 27

revoke 27

--各种函数 27

abs 28

ceil 28

floor 28

trunc 28

round 28

dbms_random.value 28

concat 28

initcap 28

upper 28

lower 28

instr 28

length 28

rpad 29

lpad 29

rtrim 29

ltrim 29

substr 29

replace 29

reverse 29

to_char 29

L9999999.9999999 29

--字符变数字 29

to_number 29

--字符变日期，数字变日期 30

to_date 30

add_months 30

sysdate+1 30

count 30

--行最大值，行最小值 30

greatest 30

least 31

分析函数 decode 31

嵌套查询 31

--行级锁 31

--表级锁 31

in share mode 31

in exclusive mode 31

lock 31

--私有同义词 31

synonym for 31

drop synonym 32

--公有同义词 32

public synonym 32

--序列 32

sequence 32

start with 32

.nextval 32

.currval 32

--视图 32

create view 32

--复制表 32

--索引 33

create index on 33

create index 33

create unique index 33

重建索引 33

删除索引 33

组合索引 33

--PL/SQL 33

主体 33

begin 33

end; 34

--声明 34

declare 34

x number; 34

变量赋值 34

x := 1000; 34

系统输出 34

dbms_output.put_line 34

使用某表某字段的类型 34

sal%type 34

if语句 35

if 35

end if; 35

if elsif 35

return 36

case 36

when then 36

when 36

end case; 36

loop 37

loop 37

end loop; 38

exit when 38

while 38

for 39

for cnt in 1..1000 loop 39

异常处理 39

exception 39

no_data_found 39

too_many_rows 40

自定义异常 抛出异常 40

raise 40

raise_application_error 40

raise 40

raise_application_error 41

隐式游标 41

sql%found 41

sql%rowcount 41

显式游标 42

cursor is 42

cur%notfound 42

cursor 42

open 42

fetch 42

close 42

取余mod 42

变量声明同时赋值 43

当前游标current of 43

procedure 43

as 43

带参数的过程 44

带返回值得过程 44

--根据员工编号求取该员工的姓名和薪水 45

function 46

return number 46

--根据部门编号获取该部门的薪水总和 47

--触发器 47

--语句级触发器 47

trigger 47

after 48

on 48

行级触发器 49

for each row 49

:new.a 49

:old.a 49

--查询当前数据库名称

v$database;

select name from v$database;

 

--查询所有表空间的名称

dba_tablespaces

select tablespace_namefrom dba_tablespaces;

 

--创建永久表空间

create tablespace MYTABLESPACE

datafile 'D:\oracle_lesson\oradata\orcl\MYTABLESPACE.DBF'

size 100M

autoextend on  next 50M

maxsize 500M;

 

--创建临时表空间

create temporary tablespace MYTEMP

tempfile 'D:\oracle_lesson\oradata\orcl\MYTEMP.DBF'

size 50M

autoextend on

next 10M

maxsize 200M;

 

--删除表空间

drop tablespace

including contents and datafiles

drop tablespace mytablespaceincluding contents and datafiles;

drop tablespace mytempincluding contents and datafiles;

 

--查询所有用户的名称

dba_users

select usernamefrom dba_users;

 

--创建用户[注意:密码不用数字也不要和用户名一样]

create user tom

identified by orcl

default tablespace mytablespace

quota 20Mon mytablespace

temporary tablespace mytemp;

 

--更改用户密码

alter user  identified by

alter user tomidentified by orcl;

 

--删除用户

drop user  cascade

drop user tomcascade;

 

--查询用户的系统权限

user_sys_privs

dba_sys_privs

select *from user_sys_privs;

select *from dba_sys_privs;

 

--查询用户的对象权限

user_tab_privs

dba_tab_privs

select *from user_tab_privs;

select *from dba_tab_privs;

 

--查询角色

dba_roles

select role from dba_roles;

 

--创建角色

create role student;

create role teacher;

create role director;

 

--为角色授权

grant to

grant select any table to student;

 

grant studentto teacher;

grant insert any table to teacher;

 

grant teacherto director;

grant update any table to director;

grant delete any table to director;

 

--为用户授权

grant directorto tom;

grant connect to tom;

 

--查询用户(角色)与角色的关系

dba_role_privs

select *from dba_role_privs;

 

--查询角色与权限的关系

select *from dba_tab_privs;

select *from dba_sys_privs;

 

--删除角色

drop role director;

--创建学生信息表

create table student

(

  id   number(4),

  name nvarchar2(4),

  gender     char(2),

  birthday   date,

  address    varchar2(100)

);

 

--删除表的列

alter table 表名称  Drop column 列名称

--添加表的列

alter table 表名称  add column 列名称

 

--添加主键约束确保字段值不能为空且不能重复

primary key

alter table student

add constraint pk_student_idprimary key (id);

 

--添加非空属性确保字段不能为空

not null

alter table student

modify name not null;

 

--添加检查约束确保字段只能填写指定数据

check

alter table student

add constraint ck_student_gendercheck (gender='男' or gender='女');

 

--在假设生日不能重复的前提下--添加唯一约束确保字段仅仅不能重复

unique

alter table student

add constraint uq_student_birthdayunique (birthday);

 

--添加默认值属性确保在不填写的情况下自动填写的数据

default

alter table student

modify addressdefault ('北京八维研修学院宿舍');

 

 

--创建课程信息表

create table course

(

  id   number(2),

  title      varchar2(20),

  period     number(2)

);

 

alter table course

add constraint pk_course_idprimary key (id);

 

alter table course

modify titlenot null;

 

alter table course

add constraint ck_course_periodcheck(period>=40 and period<=80);

 

 

--创建学生成绩表

create table grade

(

  sid  number(4),

  cid  number(2),

  mark number(3)

);

 

--添加外键约束确保所用数据来源于其它表中的数据

foreign key

alter table grade

add constraint fk_grade_sidforeign key (sid) references student(id);

 

alter table grade

add constraint fk_grade_cidforeign key (cid)references course(id);

 

--组合主键

alter table grade

add constraint pk_grade_sid_cidprimary key (sid,cid);

 

alter table grade

add constraint ck_grade_markcheck(mark>=0 and mark<=100);

 

--查询当前用户的可用数据表

select table_namefrom user_tables;

 

--查询表所有信息

select *from emp;

 

select *from dept;

 

select *from salgrade;

 

--仅查询多字段信息

select empno,ename,deptnofrom emp;

 

--带时字段带表达式 

select ename,12*salfrom emp;

 

--简单的数学运算

dual

select 200+300,12*3 from dual;

 

--获得当前日期时间

sysdate

systimestamp

current_date

current_timestamp

select sysdate from dual;

select systimestamp from dual;

select current_date from dual;

select current_timestamp from dual;

 

--使用字段别名

as

select enameas "姓名",12*salas "年薪"from emp;

select ename "姓名",12*sal "年薪"from emp;

 

--查询员工姓名和补贴

select ename,commfrom emp;

 

--查询员工姓名和年收入

Select decode(comm,500,’A’,300,’B’,’C’) from emp ;

nvl(comm,0)

select ename "姓名",12*(sal+nvl(comm,0)) "年收入"from emp;

 

--字符串连接

||

select 'abc'||'def'||'gh' "con"from dual;

select 'Mr.'||enamefrom emp;

 

--查询时消除冗余

distinct 

select distinct deptnofrom emp;

 

--同时查询部门编号与岗位

select distinct deptno,jobfrom emp;

 

--带条件的查询 

where

ename='CLARK

sal>1500

sal>1500;

select *from empwhere deptno=10;

 

--查询部门编号不是10的员工信息

select *from empwhere deptno!=10;

select *from empwhere deptno<>10;

 

--查询“CLARK”员工的信息

select *from empwhere ename='CLARK';

 

--查询薪水多于1500元的员工信息

select *from empwhere sal>1500;

 

--查询排在“FORD”之后的员工信息

ename>'FORD'

select *from empwhere ename>'FORD';

 

--查询薪水在800至1500元之间的员工信息

where sal>=800 and sal<=1500

where sal between and

select *from empwhere sal>=800 and sal<=1500;

select *from empwhere sal between 800 and 1500;

 

--查询没有津贴的员工信息

is null

is not null

select *from empwhere commis null;

 

--查询有津贴的员工信息

select *from empwhere commis not null;

 

--查询薪资指定的员工信息

where sal in (800,1100,1500)

select *from empwhere sal=800 or sal=1100 or sal=1500;

select *from empwhere sal in (800,1100,1500);

 

--查询在1981年12月3日之后入职的员工信息

select *from empwhere hiredate >'3-12月-1981';

 

--查询在10号部门工作并且薪水多于1000元的员工信息

select *from empwhere deptno=10 and sal>1000;

 

--查询在10号部门工作或者薪水多于1000元的员工信息

select *from empwhere deptno=10 or sal>1000;

 

--查询在10号部门工作但薪水只有1300或5000元的员工信息

select *from empwhere deptno=10 and (sal=1300 or sal=5000);

select *from empwhere deptno=10 and salin(1300,5000);

-- 模糊查询

like ‘%A%’

__A%

--查询姓名中包含”A”字符串的员工信息(模糊)

select *from empwhere enamelike '%A%';

select *from empwhere enamelike '%A';

select *from empwhere enamelike 'A%';

select *from empwhere enamelike 'A____';

select *from empwhere enamelike '__A%';

select *from empwhere enamelike '%A__';

 

--按员工编号升序排列显示员工信息

order by asc

select *from emporder by empno asc;

 

--按员工编号降序排列显示员工信息

select *from emporder by empnodesc;

 

--按部门编号升序且姓名降序的方式排列员工信息

desc

select *from emporder by deptnoasc,enamedesc;

 

--按员工编号升序排列不在10号部门工作的员工信息

select *

from emp

where deptno!=10 

order by empnodesc;

 

--查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列

select emp.*,12*sal a

from emp

where enamenot like '_A%' and sal>800

order by adesc;

 

--求每个部门的平均薪水

avg(sal)

group by

select avg(sal) from emp;

select deptnofrom empgroup by deptno;

select deptno,avg(sal)from empgroup by deptno;

 

--求各个部门的最高薪水

max(sal)

select max(sal) from emp;

select deptno,max(sal)from empgroup by deptno;

 

select min(sal)from emp;

select sum(sal)from emp;

 

--求每个部门每个岗位的最高薪水

select *from emp;

select job,deptnofrom empgroup by job,deptno;

select job,deptno,max(sal)from empgroup by job,deptno;

 

--求平均薪水大于2000的部门编号

select deptno,avg(sal)

from emp

group by deptno

having avg(sal)>2000;

 

--将员工薪水大于1200且部门平均薪水大于2000的部门编号列出来，

--按部门平均薪水降序排列

select deptno,avg(sal) av

from emp

where sal>1200

group by deptno

having avg(sal)>2000

order by avdesc;

 

--求最高薪水的员工信息

select max(sal)from emp;

select *from empwhere sal=5000;

select *from empwhere sal=(select max(sal)from emp);

 

--求多于平均薪水的员工信息

select avg(sal)from emp;

select *from empwhere sal>(select avg(sal)from emp);

 

--交叉连接

cross join

select *from emp;

select *from dept;

select *from emp,dept;

select *from empcross join dept;

--内连接

inner join on

--查询员工姓名及所在部门名称

select ename,dnamefrom emp,deptwhere emp.deptno=dept.deptno;

select ename,dname

from empinner join dept on emp.deptno=dept.deptno;

 

--求每个员工及他的经理姓名

select *from emp;

 

select employee.enameas "员工",manager.enameas "经理"

from emp employeecross join emp manager

where employee.mgr=manager.empno;

 

select employee.enameas "员工",manager.enameas "经理"

from emp employeeinner join emp manager

on employee.mgr=manager.empno;

 

--查询员工姓名及其薪水等级

select *from emp;

select *from salgrade;

select *from empcross join salgrade;

select enameas "员工姓名",gradeas "薪水等级"

from empinner join salgrade

on salbetween losaland hisal;

 

--三个表的嵌套查询

--输出非办事员的员工姓名，所在部门名称及薪水等级

select *from emp;

select *from dept;

select *from salgrade;

 

select *from emp,dept,salgrade;

 

select *

from empcross join dept

         cross join salgrade;

 

select ename,dname,grade

from empcross join dept

         cross join salgrade

where emp.deptno=dept.deptno

      and emp.salbetween losaland hisal

      and job!='CLERK';

 

select ename,dname,grade

from empinner join depton emp.deptno=dept.deptno

         inner join salgradeon emp.salbetween losaland hisal

where job!='CLERK';

 

--输出第二个字母不是”A”员工姓名，所在部门名称及薪水等级

select ename,dname,grade

from empinner join depton emp.deptno=dept.deptno

         inner join salgradeon emp.salbetween losaland hisal

where enamenot like '_A%';

 

--求各个部门薪水最高的员工信息

select deptno,max(sal)from empgroup by deptno;

select *from emp;

 

select ename,deptno,sal

from empinner join (select deptno d,max(sal) mfrom empgroup by deptno) e

         on emp.sal=e.mand emp.deptno=e.d

order by deptnoasc;

 

--求取所有部门的员工姓名

select *from dept;

select *from emp;

select *from deptcross join emp;

select dept.deptno,dname,enamefrom deptinner join empon dept.deptno=emp.deptno;

select dept.deptno,dname,enamefrom deptleft join empon dept.deptno=emp.deptno;

 

伪列

--输出记录的序号

rownum

select rownum,emp.*from emp;

 

--输出前五条记录

select rownum,emp.*from empwhere rownum<=5;

 

--取出第十条记录之后的数据

select *from (

    select rownum r,emp.*from emp)

where r>10;

 

--按薪水由高至低排列，找出第6-10名的员工信息

select *from

  (select rownum r,e.*from 

      (select *

       from emp

       order by saldesc) e) e2

where r>=6 and r<=10;    

 

 

--保存数据

insert into student(id,name,gender,birthday,address)

values(1000,'李四','男','12-11月-1990',default);

 

insert into student(id,name,gender,birthday,address)

values(1001,'王五','女',sysdate,'北京市东城区小燕胡同');

 

--同时插入多条记录

insert into student(id,name,gender,birthday,address)

select 1002,'赵六','女','18-2月-1993','北京市东城区小燕胡同' from dual。

union all

select 1003,'张三','男','20-8月-1989','北京市海淀区小燕胡同' from dual;

 

 

select *from student;

 

--更新数据

update set

update studentset birthday='26-6月-1992' where id=1001;

 

update student

set birthday='18-10月-1991',address='北京市朝阳区光明里小区' 

where id=1003;

rollback;

 

--删除数据

delete from studentwhere id=1003;

commit;

 

--数据控制语言

grant select on studentto tom;

grant insert on studentto tom;

revoke select on studentfrom tom;

 

--各种函数

--绝对值，向上取整，向下取整，取若干位小数不四舍五入，取若干位小数四舍五入，取特定范围内随机数，

--字符串链接，变首字母为大写，全部大写，全部小写，从字符串第若干位查询第若干次出现的字符串的索引值

--字符串长度，为字符串增长并补特定符号（左右），使用特定字符截取字符，从特定字符截取特定长字符，

--替换字符，字符反转，

abs

ceil

floor

trunc

round

dbms_random.value

select abs(-25)from dual;

select ceil(-35.68)from dual;

select floor(-35.68)from dual;

select trunc(12.345678,2)from dual;

select round(12.345678,2)from dual;

select dbms_random.value(10,20)from dual;--包括10，不包括20

concat

initcap

upper

lower

select concat('app','le') word from dual;

select concat('Mr.',ename) enamefrom emp;

select initcap(ename)from emp;

select upper(lower(ename))from emp;

instr

length

select instr('aabbaaccaaddaaeeaaff','aa',5,3)from dual;

select ename,length(ename)from emp;

rpad

lpad

rtrim

ltrim

substr

replace

reverse

select rpad('abc',8,'*')from dual;

select lpad('abc',8,'*')from dual;

select rtrim('aabbccbbaa','ab')from dual;

select ltrim('aabbccbbaa','ab')from dual;

select substr('abcdefg',3,4)from dual;

select replace('aabbaacc','aa','X')from dual;

select reverse('12345')from dual;

 

--数字变字符，数字变人民币字符，日期变字符

to_char

L9999999.9999999

select 123,to_char(123)from dual;

select to_char(1000.12345,'L9999999.9999999')from dual;

select to_char(1000.12345,'L0000000.0000000')from dual;

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')from dual;

select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

 

--字符变数字

to_number

select to_number('1200')from dual;

select '1200'+'1300' from dual;

 

--字符变日期，数字变日期

to_date

select to_date('2002-12-20','yyyy-mm-dd')from dual;

select to_date(20021220,'yyyymmdd')from dual;

 

--加月份，减月份，加天，加小时，加分钟，算月时间差

add_months

sysdate+1

select sysdate,add_months(sysdate,2)from dual;

select sysdate,add_months(sysdate,-12)from dual;

select sysdate,sysdate+1 from dual;

select sysdate,sysdate+1/24/60 from dual;

select months_between(to_date('2014-2-5','yyyy-mm-dd'),

                      to_date('2013-12-11','yyyy-mm-dd'))from dual;

 

--统计个数

count

select ename,commfrom emp;

select count(comm)from emp;

 

 

select to_char(round(avg(sal),2),'L9999.99')from emp;

 

--行最大值，行最小值

greatest

least

select greatest(1,2,3,4,5)from dual;

select least(1,2,3,4,5)from dual;

 

select user from dual;

 

分析函数 decode

Select decode(floor(mark/10),10,’优秀’,9,’ 优秀’,8,’良好’,7,’中等’,6,’及格’,’不及格’);

嵌套查询

Select * from emp where sal>(select avg(sal) from emp);

 

--行级锁

此四种操作自动加行级锁：

insert

delete

update

select * from emp where for update;

 

--表级锁

in share mode

in exclusive mode

lock table empin share mode 共享锁;

lock table empin exclusive mode 排它锁;

 

--序列

sequence 

start with

.nextval

.currval

create sequence s0start with 1000;

select s0.nextval from dual;

create sequence sq_t03 start with 100 increment by 10;

select sq|_to1.currval from dual;

 

--视图

create view v0

as

select *from(

select rownum r,e.*from(

select *from emporder by saldesc)e)

where r>=6 and r<=10;

 

select *from v0;

 

--复制表

create table emp0

as

select *from emp;

 

select *from emp0where empno=7900;

select *from emp0where ename='SMITH';

select *from emp0where empno>7000 and ename<'ZOO'

--索引

create index on

create index i0on emp0 (empno);

create unique index i1on emp0(ename);

create index i2on emp0 (empno,ename);

重建索引

Alter index <索引名> rebuild;

删除索引

Drop index <索引名>;

组合索引

Select * from emp0 where empno>7000 and ename<’ral’;

Create index ins2 on emp0 (empno,ename);