Oracle_常用汇总

痛定思痛。 2021-10-06 04:20 429阅读 0赞

### Oracle\_常用汇总 ###

*  表结构操作
 *  表数据操作
 *  分组/模糊/连接/子查询
 *  视图view
 *  存储过程procedure

# 表结构操作 #

create alter drop

1、登陆oracle，连接sysdba用户  
sqlplus / nolog  
conn / as sysdba

2、查询用户和状态  
select username,account\_status from dba\_users;

3、解锁用户  
alter user scott acount unlock;

4、修改用户密码为tiger  
alter user scott identified by tiger;

5、连接scott用户  
conn scott/tiger

6、查看当前连接的用户  
show user;

7、查看当前用户的表  
select table\_name from user\_tables;

8、创建一张表t\_student  
create table t\_student(id integer,name varchar(50));

9、查看表概述  
desc t\_student;

10、给表t\_student添加纪录  
insert into t\_student values(1,‘xiaoming’);

11、查看表t\_student的所以纪录  
select \* from t\_student;

12、修改表结构，添加字段age  
alter table t\_student add age number;

13、修改表结构，删除字段age  
alter table t\_student drop column age;

14、删除表t\_student  
drop table t\_student;

15、修改表字段类型  
alter table t\_yy\_order alter column updated varchar2(20);  
oracle------  
alter table tablename modify filedname varchar2(20);

16、清空指定表数据  
truncate table mytable;  
delete from t\_student where 1=1;

# 表数据操作 #

select insert update delete

1、查询条件  
select name from t\_student where id = 1;

2、排序查询  
select \* from t\_student order by age desc; \#\#desc/asc 降/升

3、是否为空  
select \* from t\_student where age is not null; \#\# is null

4、去重  
select distinct age from t\_student; \#\#查询所有年龄（去重）

5、更新记录，一般带条件更新。避免更新所有记录  
update t\_student set age=18 where id =1;

6、删除记录，一般带条件删除  
delete from t\_student where id = 1;

7、常规函数

select count(0) from t\_student; \#\#查询数据记录数  
select sum(age) from t\_student; \#\# 查询年龄记录的总和  
select avg(age) from t\_student; \#\#查询年龄的平均数  
select sum(decode(age,18,1,0) age\_18),sum(decode(age,28,1,0) age\_28) from t\_student; \#\#查看年龄为18和28的记录和

select max(age) from t\_student; \#\#查看记录中年龄最大的一条  
select min(age) from t\_student; \#\#年龄最小的

# 分组/模糊/连接/子查询 #

group by/like/join on/in

1、分组查询，明确分组的对象（一般用于单表的统计）  
select sex,sum(score) from x\_student group by sex; \#\# 查看男女学生成绩的总和

2、模糊查询  
select \* from t\_student where name like ‘zhang%’; \#\#查询新zhang的所有记录

3、表连接，两表之间一般有关联的字段（下面两条结果一样）  
select x.id,x.name,x.age,b.name from x\_student x join b\_class on x.b\_id = b.id;  
select x.id,x,name,x.age,b.name from x\_student, b\_class where x.b\_id = b.id;

4、子查询  
select \* from x\_student where b\_id in(select id from b\_class where name = ‘一班’); \#\# not in

# 视图view #

创建视图的用户需要有权限  
grant connect,dba to scott;

1、创建一个crud视图  
create or replace view v\_student as select \* from t\_student;

2、创建一个只读视图  
create or replace view v\_student as select id,name from t\_student with read only;

3、对两张关联表的常用结果做一个视图  
create or replace view v\_student as select x.id,x.name,b.name from x\_student x join b\_class b on x.b\_id = b.id;

# 存储过程procedure #

显示执行存储过程的结果：  
开启：SET SERVEROUTPUT ON  
输出：DBMS\_OUTPUT.PUT\_LINE(xxx);

1、简单的procedure结构  
①创建  
create or replace procedure p\_student  
is  
begin  
null;  
end;  
/  
②执行  
方式一：  
excute p\_student;  
方式二：  
begin  
p\_student;  
end;  
/

2、创建一个存储，输入学生姓名，查出该学生的语文和数学分数的总和  
create or replace procedure p\_student(temp\_name in varchar2,temp\_num out number)  
is  
num\_1 number;  
num\_2 number;  
begin  
select yu\_wen,shu\_xue into num\_1,num\_2 from t\_student where name=temp\_name ;  
temp\_num:=num\_1+num\_2;  
end;  
/  
调用：  
declare  
tname varchar(25);  
tnum number;  
begin  
tname:=‘zhangsan’;  
p\_student(tname,tnum);  
DBMS\_OUTPUT.PUT\_LINE(tnum);  
end;  
/  
java代码调用一般用到CallStatement操作

维护存储过程

1、查看过程状态

SELECT object\_name,status FROM USER\_OBJECTS WHERE object\_type=‘PROCEDURE’;

2、重新编译过程

ALTER PROCEDURE xs\_proc COMPILE;

3、查看过程的源代码

SELECT \* FROM USER\_SOURCE WHERE TYPE=‘PROCEDURE’;

4、删除存储过程

DROP PROCEDURE xs\_proc;