欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle code常用语句大全(必学)

程序员文章站 2022-07-03 09:26:13
--查询当前数据库名称 10 v$database 10 --查询所有表空间的名称 10 dba_tablespaces 10 --创建永久表空间 10 create t...

--查询当前数据库名称 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_name from 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 mytablespace including contents and datafiles;

drop tablespace mytemp including contents and datafiles;

--查询所有用户的名称

dba_users

select username from dba_users;

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

create user tom

identified by orcl

default tablespace mytablespace

quota 20M on mytablespace

temporary tablespace mytemp;

--更改用户密码

alter user identified by

alter user tom identified by orcl;

--删除用户

drop user cascade

drop user tom cascade;

--查询用户的系统权限

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 student to teacher;

grant insert any table to teacher;

grant teacher to director;

grant update any table to director;

grant delete any table to director;

--为用户授权

grant director to 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_id primary key (id);

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

not null

alter table student

modify name not null;

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

check

alter table student

add constraint ck_student_gender check (gender='男' or gender='女');

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

unique

alter table student

add constraint uq_student_birthday unique (birthday);

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

default

alter table student

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

--创建课程信息表

create table course

(

id number(2),

title varchar2(20),

period number(2)

);

alter table course

add constraint pk_course_id primary key (id);

alter table course

modify title not null;

alter table course

add constraint ck_course_period check(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_sid foreign key (sid) references student(id);

alter table grade

add constraint fk_grade_cid foreign key (cid) references course(id);

--组合主键

alter table grade

add constraint pk_grade_sid_cid primary key (sid,cid);

alter table grade

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

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

select table_name from user_tables;

--查询表所有信息

select * from emp;

select * from dept;

select * from salgrade;

--仅查询多字段信息

select empno,ename,deptno from emp;

--带时字段带表达式

select ename,12*sal from 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 ename as "姓名",12*sal as "年薪" from emp;

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

--查询员工姓名和补贴

select ename,comm from 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.'||ename from emp;

--查询时消除冗余

distinct

select distinct deptno from emp;

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

select distinct deptno,job from emp;

--带条件的查询

where

ename='CLARK

sal>1500

sal>1500;

select * from emp where deptno=10;

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

select * from emp where deptno!=10;

select * from emp where deptno<>10;

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

select * from emp where ename='CLARK';

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

select * from emp where sal>1500;

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

ename>'FORD'

select * from emp where ename>'FORD';

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

where sal>=800 and sal<=1500

where sal between and

select * from emp where sal>=800 and sal<=1500;

select * from emp where sal between 800 and 1500;

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

is null

is not null

select * from emp where comm is null;

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

select * from emp where comm is not null;

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

where sal in (800,1100,1500)

select * from emp where sal=800 or sal=1100 or sal=1500;

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

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

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

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

select * from emp where deptno=10 and sal>1000;

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

select * from emp where deptno=10 or sal>1000;

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

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

select * from emp where deptno=10 and sal in(1300,5000);

-- 模糊查询

like ‘%A%’

__A%

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

select * from emp where ename like '%A%';

select * from emp where ename like '%A';

select * from emp where ename like 'A%';

select * from emp where ename like 'A____';

select * from emp where ename like '__A%';

select * from emp where ename like '%A__';

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

order by asc

select * from emp order by empno asc;

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

select * from emp order by empno desc;

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

desc

select * from emp order by deptno asc,ename desc;

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

select *

from emp

where deptno!=10

order by empno desc;

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

select emp.*,12*sal a

from emp

where ename not like '_A%' and sal>800

order by a desc;

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

avg(sal)

group by

select avg(sal) from emp;

select deptno from emp group by deptno;

select deptno,avg(sal) from emp group by deptno;

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

max(sal)

select max(sal) from emp;

select deptno,max(sal) from emp group by deptno;

select min(sal) from emp;

select sum(sal) from emp;

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

select * from emp;

select job,deptno from emp group by job,deptno;

select job,deptno,max(sal) from emp group 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 av desc;

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

select max(sal) from emp;

select * from emp where sal=5000;

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

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

select avg(sal) from emp;

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

--交叉连接

cross join

select * from emp;

select * from dept;

select * from emp,dept;

select * from emp cross join dept;

--内连接

inner join on

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

select ename,dname from emp,dept where emp.deptno=dept.deptno;

select ename,dname

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

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

select * from emp;

select employee.ename as "员工",manager.ename as "经理"

from emp employee cross join emp manager

where employee.mgr=manager.empno;

select employee.ename as "员工",manager.ename as "经理"

from emp employee inner join emp manager

on employee.mgr=manager.empno;

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

select * from emp;

select * from salgrade;

select * from emp cross join salgrade;

select ename as "员工姓名",grade as "薪水等级"

from emp inner join salgrade

on sal between losal and hisal;

--三个表的嵌套查询

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

select * from emp;

select * from dept;

select * from salgrade;

select * from emp,dept,salgrade;

select *

from emp cross join dept

cross join salgrade;

select ename,dname,grade

from emp cross join dept

cross join salgrade

where emp.deptno=dept.deptno

and emp.sal between losal and hisal

and job!='CLERK';

select ename,dname,grade

from emp inner join dept on emp.deptno=dept.deptno

inner join salgrade on emp.sal between losal and hisal

where job!='CLERK';

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

select ename,dname,grade

from emp inner join dept on emp.deptno=dept.deptno

inner join salgrade on emp.sal between losal and hisal

where ename not like '_A%';

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

select deptno,max(sal) from emp group by deptno;

select * from emp;

select ename,deptno,sal

from emp inner join (select deptno d,max(sal) m from emp group by deptno) e

on emp.sal=e.m and emp.deptno=e.d

order by deptno asc;

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

select * from dept;

select * from emp;

select * from dept cross join emp;

select dept.deptno,dname,ename from dept inner join emp on dept.deptno=emp.deptno;

select dept.deptno,dname,ename from dept left join emp on dept.deptno=emp.deptno;

伪列

--输出记录的序号

rownum

select rownum,emp.* from emp;

--输出前五条记录

select rownum,emp.* from emp where 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 sal desc) 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 student set birthday='26-6月-1992' where id=1001;

update student

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

where id=1003;

rollback;

--删除数据

delete from student where id=1003;

commit;

--数据控制语言

grant select on student to tom;

grant insert on student to tom;

revoke select on student from 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) ename from 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,comm from 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 emp in share mode 共享锁;

lock table emp in exclusive mode 排它锁;

--序列

sequence

start with

.nextval

.currval

create sequence s0 start 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 emp order by sal desc)e)

where r>=6 and r<=10;

select * from v0;

--复制表

create table emp0

as

select * from emp;

select * from emp0 where empno=7900;

select * from emp0 where ename='SMITH';

select * from emp0 where empno>7000 and ename<'ZOO'

--索引

create index on

create index i0 on emp0 (empno);

create unique index i1 on emp0(ename);

create index i2 on emp0 (empno,ename);

重建索引

Alter index <索引名> rebuild;

删除索引

Drop index <索引名>;

组合索引

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

Create index ins2 on emp0 (empno,ename);