(完整版)Oracle操作语句大全

Oracle 操作语句大全

1。desc（描述） emp 描述emp这张表

dept 部门表

salgrade 薪水等级

4。select ＊from table 查找表中的元素

是系统中的一张空表

6。select *from dual

sysdate from dual 取出系统时间

8。select ename，sal*12 "annul sal”（取的别名） from emp； 查找用户姓名和用户的年薪

9。任何含有空值的数学表达式的值都是空值

select ename,sal*12+comm from emp;

ename｜|sal from emp 其中的|｜相当于将sal全部转化为字符串

11.表示字符串的方法

select ename ||’ajjf’ from emp；

12.如果其中有一个单引号就用2个单引号来代替他

select ename||'sakj’ 'lds'from emp;

13。select distinct deptno from emp (去除部门字段中重复的部分，关键字distinct)

14。select distinct deptno,job from emp;(去除这2个字段中重复的组合)

＊from dept where deptno=10； 取出条件(取出部门编号为10的记录）

16。select * from emp where ename='CLIRK'; 取出部门中姓名为clirk的记录(注意取出过程中ename用单引号隔开)

17。select ename，sal from emp where sal>1500; 取出部门中薪水大于1500的人的姓名

18。select ename，sal,deptno from emp where deptno<〉 10 取出部门中的部门号不等于10的

ename，sal，deptno from emp where ename>’CBA' 取出部门中员工名字大于CBA的员工（实际比较的是ACIIS码)

20。select ename，sal from emp where sal between 800 and 1500

select ename,sal from emp where sal〉=800 and sal<=1500; （取出800和1500之间的数)

ename,sal,comm from emp where comm is null （选出其中的空值)

select enmae，sal,comm from emp where comm is not null（选出其中的非空值）

ename，sal,comm from emp where sal in （800，1500，2000);取出这3者之中的

select ename,sal,comm from emp where ename in(’simth')；

23。select ename，sal,hiredate from emp where hiredata>'3-04月-81’；宣传符合条件的日期

ename，sal,from emp where sal〉1000 or deptno=10； 找出工资薪水大于1000或者部门号等于10的员工

25。select ename,sal from emp where sal not in（500,1000); 查找薪水不在500到1000的员工姓名和月薪

ename,sal from emp where ename like ’％ALL%’；

select ename，sal from emp where ename like ’_％A％'; 查找姓名中含有ALL的客户信息，一个横线代表一个通配符

27。select ename，sal from emp where ename like '_%$％％’ escape '＄’; 自己指定转易字符

select ename,sal from emp where ename like ’_%％％'； 查找中间含有％相匹配的客户信息，运用转易字符

＊ from dept order by deptno 对表中元素按部门号排序

select *from dept order by deptno desc 默认为升序，可以用desc按降序

ename，sal from emp where sal <〉1000 order by sal desc 按照查询条件来查询，并排序（asc升序排列）

ename，sal*12 from emp where ename not like ’_％A%' and sal>800

order by sal desc

31。select lower(ename） from emp 将ename都转化为小写 lower是函数能将字母转化为小写

32。select ename from emp where lower(ename） like ’_％a％’; 找出ename 中所有的含有a的字符

substr(ename,2，3) form emp 从第2个字符开始截取3个字符

chr（65） from dual; 将65转化为字符

35。select ascii('A’) from dual 将ACSII码转化为字符串

36。select round(23。565）from dual 四舍五入

round(23,4565,2）from dual 四舍五入到第二位

37。select to_char(sal，'＄99。999.9999’） from emp 按指定格式输出

select to_char(sal,’L99,999,9999’) form emp L代表本地字符

38。select hiredate from emp

select to_char（hiredate,'YYYY-MM-DD HH:MI：SS) from emp; 时间格式的显示

select to_char(sysdate，’YYYY-MM—DD HH:MI：ss） from dual; 十二小时制显示系统时间

select to_char（sysdate,’YYYY—MM-DD HH24：MI:SS) from dual 二四小时制显示系统时间

39。select ename，hiredate from emp where hiredate 〉 to_date('2005-2—3 12：32:23','YYYY—MM-DD HH：MI：SS'）;

40 select sal from emp where sal〉to_number(’$1，250.00’,’$9,999.99’)；

取出比它大的一切字符串（把特定格式的数字转化成字符）

41 select ename,sal+nvl(comm，0) from emp; 讲comm值为空的用0来替换，单行函数（以一条记录为条件)一条对一条

42。select Max（sal） from emp；

select Min（sal） from emp；

select avg（sal） from emp;

select sum（sal) from emp;

select count（*） from emp； 查看表中一共有多少条记录

select count（*) from emp where deptno=10； 查找部门10一共有多少人；

43。select avg（sal)，deptno from emp group by deptno； 按部门号进行分组

select deptno，job，max（sal) from emp group by job,deptno; 按工作和部门号进行分组；

44。select ename from emp where sal=（select max(sal) from emp)； 子查询，查找部门中薪水最高的员工姓名

by 注意：出现在select列表中的字段,如果没有出现在组函数中必须出现在group by子句中

avg（sal）,deptno from emp group by deptno having avg(sal）>2000;

选出部门中平均薪水大于2000的部门，

* from emp where sal〉100 group by deptno having ...。。.。。。。order

by。。.。。.。.

先取数据--过滤数据———--—分组-———对分组限制-—----—排序

48。select avg（sal) from emp where sal>2000 group by deptno having

avg(sal）>1500 order by avg（sal) desc;

查找部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列

ename from emp where sal〉（select avg(sal) from emp）；

查找出员工中薪水位于部门平均薪水之上的所有员工

ename，sal from emp join（select max(sal） max_sal from emp group

by deptno) t on（=t,max_sal and =）;

查找每个部门中薪水最高的

51。select e1。ename,e2。ename from emp e1,emp e2 where e1。mgr=;

表的自连接

dname，ename from emp cross join dept 交叉连接，笛卡尔

SQL99中的新语法

ename,dname from emp join dept on（=);

ename,dname from emp join dept using(deptno); 查找emp和dept表中deptno相同的部分。

ename，dname,grade from emp e join dept d on(=d。depno）

join salgrade s(e。sal between

and ） （三表查找）

where ename not like '_％A%'；

56。select e1。ename， from emp e1 join emp e2 on（e1。mgr=e2。deptno）;

表的自连接

57。select e1。ename,e2。ename from emp e1 left join emp e2 on（e1。mgr=）

左外表连接

select ename,dname from emp e right join dept d on(=d。deptno)右外连接

select ename，dname from emp e full join dept d on(=)全连接

58。求部门中薪水最高的

select ename,sal from emp join （select max(sal) max_sal， deptno from emp

group by deptno) t

on （=t。max_sal and emp。deptno=)；

59.求部门中薪水等级的平均值

select deptno，avg(grade） from（select deptno,ename，grade,from emp join

salgrade s on（ between s。losal and s。hisal))t group by deptno;

60。查找雇员中哪些是经理人

select ename from emp where empno in（select mgr from emp）；

61。select distinct from emp e1 join emp e2 on(〈e2。sal); 自连接(不用组函数求出最高薪水）

select distinct sal from emp where not in (select ename from e1。sal from

emp e1 join emp e2 on(e1。sal〈e2。sal））；

deptno from (select avg（sal) max_sal deptno from emp group by

deptno） where max_sal=（select max(avg_sal） from (select

avg（sal) avg_sal deptno from emp group by deptno)）； 查找部门中部门薪水最大的部门号

63。求平均薪水最大的部门的部门编号

select deptno，avg_sal from（select avg(sal） avg_sal,deptno from emp group

by

deptno）where avg_sal=(select max（avg(sal）） from emp group by deptno）;

DML语句：更、删、改、查

创建权限， conn sys/admin as sysdba

grant create table,create view to scott;

首先在C：下面建个文件夹备份文件

1。createNewUser方法

1.—-backup scott

exp

user(创建用户）用超级管理员模式进入

create user yun identified by kang1234 default tablespace users quota 10M

on users;

grant create session,create table,create view to kafei（给kafei这个用户授予权限）

the data（导入备份数据）

imp

2。insert

insert into dept values （50，'game'，'bj’) 插入一条记录

insert into dept2 （deptno，dname） values （78，’games’)； 插入指定的几条记录

insert into dept2 select ＊from dept 插入指定的表（表结构要一样)

rollback； 回退

create table emp2 as select * from emp; 创建数据库表2来备份emp这张表

emp2 set sal=sal*12 where deptno=10； update的用法

from dept2 where deptno〈25 ; 删除语句的用法

DDL语言

1.创建表：create table t(a varchar2(10)）;

table t 删除表

3。commit 所有的提交，所有修改都结束了.对于rollback无效，一个事务开始于第1条DML语句

碰到执行DDL DCL语句事务自动提交 对于rollback无效

建表语句

建学生信息表:

create table stu

（id number（6），

name varchar2（20） constraint stu_name_nn not null，

sex number（1），

age number(3),

sdate date,

grade number（2) default 1，

class number(4）,

email varchar2（50) unique （唯一约束）

）;

非空 唯一 主键 外键 chick

create table stu

（id number（6) primary key，(主键约束）

name varchar2(20) constraint stu_name_nn not null，(非空约束)

sex number（1),

age number(3)，

sdate date，

grade number（2) default 1，

class number（4),

email varchar2(50），

constraint stu_name_uui unique(email,name) 组合性约束

)；

主键约束方法二

create table stu

(id number(6),

name varchar2(20) constraint stu_name_nn not null,(非空约束)

sex number(1)，

age number(3）,

sdate date，

grade number（2） default 1,

class number(4) references class(id）,（参考class 这张表,参考字段)

email varchar2(50）,

constraint stu_id_pk primary key(id），

constraint stu_name_uui unique(email，name） 组合性约束

);

外键约束

create table class

(id number（4) primary key,（id为被参考字段,被参考的字段必须是主键）

name varchar2（20） not null

）

create table stu

(

id number（6),

name varchar2（20) constraint stu_name_nn not null，(非空约束）

sex number（1）,

age number(3），

sdate date,

grade number（2） default 1,

class number(4)

email varchar2(50），

constraint stu_class_fk foreign key(class) references class(id）,

constraint stu_id_pk primary key（id),

constraint stu_name_uui unique(email，name) 组合性约束

)；

像外键中插入关键字，

into class values(1000，'c1'）；

into stu（id,name，class，email) values(1,'a',1000,’a’)；

table stu add（addr varchar（20));添加表的结构

table stu drop(addr)； 删除表结构

5。alter table stu modify(addr varchar2(150）);修改精度

table stu drop constraint stu_class_fk； 删除约束条件

table stu add constraint stu_class_fk forengn key（class) references

class（id），添加约束条件

查找当前用户下有哪些表和哪些视图及哪些约束

table_name from user_names

9。select view_name from view_names

10。select constraint_name,table_name from user_constraints;

desc dictionary数据字典表

desc user_tables当前用户下面有多少张表

select table_name from user_tables; 查找当前用户有多少张表

索引：

创建索引

create index idx_stu_email on stu(email）;

drop index idx_stu_email；

查找索引

select index_name from user_indexes；

索引读的速度快了，插入速度变慢

view 视图

视图赠加了维护的量

序列：

create table arcticle

(id number，

title varchar2(1024），

cont long

)；

序列的创建sequence产生独一无二的序列,而且是oracle独有的

create sequence seq;

select seq。nextval from dual； 查找序列号

insert into arcticle values(seq。nextval,'a’，'b'）;往表中插入序列

数据库设计的3范式

第一范式: 设计任何表都要有主键，列不可分

第二范式： 如果有2个主键的话,不能存在部分依赖

第三范式， 不能存在传递依赖

PL—sql

例子1：

SQL〉 set serveroutput on;

SQL> begin(必要的-—程序开始执行)

2 dbms__line('hello world’)；

3 end;（结束）

4 /

例子2：

SQL> declare

2 v_name varchar2(20）；

3 begin

4 v_name：=’myname’；

5 dbms_output。put_line(v_name);

6 end；

7 /

myname

例子3：

SQL〉 declare

2 v_num number：=0；

3 begin

4 v_num：=2/v_num；

5 dbms_output。put_line（v_num）；

6 end；

7 /

declare

*

ERROR 位于第 1 行:

ORA-01476： 除数为 0

ORA—06512: 在line 4

例子4：

declare

v_num number：=0;

begin

v_num:=2/v_num；

dbms_output。put_line(v_num)；

exception

when others then

dbms_output。put_line（'error’);

end；

/

变量声明的规则

1.变量名不能够使用保留字,如from,select等

2.第一字符必须是字母。

3。变量名最多包含30个字符

4.不要与数据库的表或者列同名

5。每一行只能声明一个变量

常用变量类型

1。 binary_interger,整数，主要用来计数，而不是用来表示字段类型

2. number 数字类型

3. char 定长字符串

4。 varchar2 变长字符串

5。 date 日期

6。long 长字符串，最长2GB

7。boolean 布尔类型，可以取true false 和null的值

例5：

declare

v_temp number（1）;

v_count binary_integer：=0；

v_sal number（7,2)：=4000.00;

v_date date:=sysdate；

v_pi constant number（3,2):=3。14；

v_valid boolean：=false;

v_name varchar2（20) not null：='myname';

begin

dbms__line('v_temp value：’｜｜v_temp);

end；

用-—可以注释一行

例6:

declare

v_empno number(4）;

v_empno2 %type;

v_empno3 v_empno2%type;

begin

dbms__line(’test’）;

end;

例7

table变量类型

set serveroutput on；

declare

type type_table_emp_empno is table of %type

binary_integer;

v_empnos type_table_emp_empno;

begin

v_empnos(0)：=7369；

v_empnos(2）:=7869；

v_empnos（—1）：=9999；

dbms__line（v_empnos（—1)）;

end;

例8

Record 变量类型

set serveroutput on；

declare

type type_record_dept is record

（

deptno dept。deptno%type，

dname dept。dname%type,

loc %type

);

v_temp type_record_dept；

begin

v_temp。deptno:=50；

by index

v_：='aaaa’;

v_temp。loc:=’bj';

dbms__line（v_temp。deptno｜｜' '｜｜v_)；

end；

例9：

使用％rowtype声明record变量(表结构的变化同时也能代理储存过程的变化）

set serveroutput on；

declare

v_temp dept%rowtype；

begin

v_temp。deptno：=50;

v_temp。loc：='aaaa';

v_:=’bj';

dbms_output。put_line(v_temp。deptno｜|' ’｜｜v_)；

end；

例10;

declare

v_name ％type;

v_sal %type;

begin

select ename，sal into v_name,v_sal from emp where empno=7369；(将ename和sal的值放在v_name和v_sal里面）

例11:

declare

v_name emp。ename%type;

v_sal ％type;

begin

select ename，sal into v_name，v_sal from emp where empno=7369；

dbms_output。put_line(v_name||' ’||v_sal）；

end;

dbms_output。put_line(v_name｜|' ’||v_sal）;

end；

例12:

declare

v_deptno %type:=50;

v_dname ％type:=’aaaa’；

v_loc ％type:='bj';

begin

insert into dept2 values（v_deptno，v_dname,v_loc）；

commit;

end；

例13:

declare

v_deptno emp2。deptno%type：=50；

v_count number；

begin

update emp2 set sal=sal/2 where deptno=v_deptno;

dbms__line(sql％rowcount ||'条记录被影响’);（sql为关键字，代表上一条语句

commit；

end;

/

例14：

declare

v_deptno emp2。deptno%type：=50;

v_count number；

begin

—-update emp2 set sal=sal/2 where deptno=v_deptno;

select deptno into v_deptno from emp2 where empno=7369；

dbms_output。put_line（sql％rowcount ｜｜’条记录被影响'）;(sql为关键字,代表上一条语句

commit;

end；

/

例15

declare

v_deptno emp2。deptno％type:=50;

v_count number;

begin

--update emp2 set sal=sal/2 where deptno=v_deptno；

—-select deptno into v_deptno from emp2 where empno=7369；

select count（*） into v_count from emp2; （select必须和into一起使用）

dbms__line(sql％rowcount ||’条记录被影响'）;

commit;

end；

/

PL/SQL里面执行DDL语句

begin

execute immediate ’create table T(nnn varchar2(20） default ''aaa'’)’;

end;

PL/SQL的分支语句：

declare

v_sal %type;

begin

select sal into v_sal from emp

where empno=7369；

if(v_sal〈1200) then

dbms__line(’low')；

elsif(v_sal<2000) then

dbms__line（'middle’)；

else

dbms_output。put_line（'high'）；

end if;

end;

pL/Sql循环

declare

i binary_integer：=1；

begin

loop

dbms__line（i）；

i:=i+1;

exit when（i〉=11）；

end loop;

end；

PL/SQL for循环

begin

for k in 1。。10 loop

dbms__line（k);

end loop；

for k in reverse 1.。10 loop

dbms__line(k);

end loop;

end;

exception 捕获异常

declare

v_temp number（4)；

begin

select empno into v_temp from emp where deptno=10;

exception

when too_many_rows then

dbms_output。put_line('太多记录了’);

when others then

dbms__line(’error')；

end;

没有数据错误

declare

v_temp number（4）;

begin

select empno into v_temp from emp where deptno=2222；

exception

when no_data_found then

dbms_output。put_line(’没数据’);

when others then

dbms_output。put_line(’error’)；

end;

/

错误处理

create table errorlog

（

id number primary key,

errcode number，

errmsg varchar2(1024),

errdate date

）；

create sequence seq_errorlog_id start with 1 increment by 1；

declare

v_deptno dept。deptno％type：=10；

v_errcode number；

v_errmsg varchar2（1024）；

begin

delete from dept where deptno=v_deptno;

exception

when others then

rollback；

v_errcode：=SQLCODE;

v_errmsg：=SQLERRN;

insert into errorlog values（seq_errorlog_id。nextval,v_errcode，v_errmsg，sysdate）；

commit；

end；

游标

declare

cursor c is

select * from emp;

v_emp c%rowtype；

begin

open c；

fetch c into v_emp； ——(取游标的第一个值插入v_emp,在不断的循环)

dbms__line(v_emp。ename）；

close c；

end；

例子

declare

cursor c is

select * from emp;

v_emp c％rowtype；

begin

open c;

loop

fetch c into v_emp;

exit when(c％notfound);

dbms__line（v_emp。ename);

end loop；

close c；

end；

declare

cursor c is

select ＊ from emp;

v_emp c%rowtype;

begin

open c；

fetch c into v_emp;

while（c%found） loop

dbms__line（v_emp。ename);

fetch c into v_emp；

end loop;

close c;

end;

declare

cursor c is

select ＊ from emp；

begin

for v_emp in c loop

dbms_output。put_line(v_emp。ename)；

end loop；

end；

带参数的游标

declare

cursor c（v_deptno emp。deptno％type，v_job %type)

is

select ename，sal from emp where deptno=v_deptno and job=v_job;

begin

for v_temp in c（30，'chick') loop

dbms__line(v_temp。ename）；

end loop；

end;

可更新的游标

存储过程

create or replace procedure p

is

cursor c is

select ＊ from emp2 for update；

begin

for v_emp in c loop

if (v_emp。deptno=10) then

update emp2 set sal=sal+10 where current of c;

elsif(v_emp。deptno=20) then

update emp2 set sal=sal+20 where current of c；

else

update emp2 set sal=sal+50 where current of c；

end if;

end loop；

commit;

end;

exec p执行存储过程

begin

p:

end；

带参数的存储过程

create or replace procedure p

(v_a in number，v_b number,v_ret out number，is

begin

if（v_a〉v_b) then

v_ret:=v_a;

else

v_ret：=v_b;

end if;

v_temp：=v_temp+1；

end;

调用存储过程

declare

v_a number：=3；

v_b number：=4;

v_ret number;

v_temp number:=5；

begin

p(v_a，v_b，v_ret,v_temp）；

dbms__line（v_ret);

dbms_output。put_line(v_temp）；

end；

show error返回错误信息

删除存储过程

存储过程中的函数

create or replace function sal_tax

（v_sal number）

return number

is

begin

if(v_sal〈2000） then

return 0.10;

elsif(v_sal〈2750） then

） v_temp in out number

return 0。5;

else

return 0。20;

end if；

end;

/

触发器

create or replace trigger trig

after update on dept

for each row

begin

update emp set deptno=： where deptno=：OLD。deptno;

end;

/