Oracle_常用汇总

表结构操作

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;