oracle基本操作

1、用什么数据库

1、项目的规模

a负载量有多大，用户量多大  小型100  中性5000-15000

b成本

c安全性

2、sql*plus常用命令

1、修改密码passw scott

2、文件操作命令

说明：运行脚本  @ d:\a.sql 或者start d:\a.sql

spool 可以将屏幕截取到指定文件中

3、用户管理

1、创建用户：create user xiaoming identified by m123;

2、删除用户：dorp user soctt;

3、授权

grant connect to xiaoming；  角色 connect、dba、resource

grant select on emp to xiaoming;   授权小明查看emp表的权限

grant select on emp to xiaoming;   授权小明增、删、改、查emp表的权限

grant  select  on  emp  to  xiaoming  with grant option;  小明可以将emp表查询的权限给别人   

4、系统权限：grant  connect  to  xiaoming  with  admin  option;

5、收回权限：revoke  select  on  emp  from  xiaoming收回小明查询的权限

6、管理用户口令

1、创建profile文件

Create  profile  aaa1  limit failed_login_attempts  3  password_lock_time  2;

创建一个profile文件限制用户每天登录密码只能输错三次，超过锁定用时间2天。

Alter  user  xiaoming  profile  aaa1;

把创建的profile负给小明用户；

7、给锁定用户解锁

Alter  user  xiaoming  account  unlock;

8、终止口令

Create  profile  bbb1  limit  password_life_time  10  password_grace_time 2;

限制用户每隔10天换一次密码 宽限两天

删除profile

Drop  profile  aaa1;

4、表的管理 

1、表的管理

char定长（查询速度快，浪费空间）  varchar变长（节省空间）blob  二进制数据 可以存放图片跟声音

添加一个字段

Alert  table  student  add(id varchar2(32),name varchar2(20));

修改字段的长度

Alter  table  student  modify(xm varchar(30));

修改字段的类型/或是名字（不能有数据）

alter  table  student  modify(xm char(30));

删除一个字段

alter  table  student  drop  column  sal;

修改表的名字

Rename  student  to  stu;

删除表

Drop  table  student;

插入数据

Insert  into  student  values('8-8月-1987'）；

插入部分字段

Insert  into  student(xh,xm,sex)  values('a003','john','女');

修改日期格式

Alter  session  set  nls_date_format = 'yyyy-mm-dd';

修改字段

Update  student  set  sex='女' , birthday='1980-01-1'  where  xh='a001'

2、表的查询

Set  timing  on  打开操作时间

1、取消重复行

Select  distinct  deptno  job  from  emp;

Select  sal*13+nvl(comm,0)*13, ename  from  emp;如果comm出现空值用0代替,NVL()函数用于处理空值

2、like操作符

%:表示任意0到多个字符  _：表示任意单个字符

select  ename  sal  from  emp  where  ename  like '__O%'

3、where条件中使用In

select  * from  emp  where  empno  in(123,234,456);表示empno里有这些

4、having语句例子

select  avg(sal), max(sal), deptno  from  emp group by deptno having avg(sal)>2000 order  by  avg(sal)  desc;

5、between语句例子

select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

单行子查询：只返回一行数据的子查询

多行子查询：返回多行数据的子查询

select * from emp where job in(select distinct job from emp where deptno=10);

6、多行子查询any操作符

select ename,sal,dept from emp where sal>any(select sal from emp where deptno=30);

7、多列子查询

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

查询高于自己部门平均工资的员工

select * from emp e,(select deptno,avg(sal) mysal from emp group by deptno) e1 where e.sal>e1.mysal and e.deptno=e1.deptno;

8、分页查询

Select * from(select * from (select e.*,rownum rn from emp e)  where rn>1) where rn<6;

根据rowid来分

select * from t where rowid in(select rid from(select rownum rn,rid from(select rowid rid, cid from t order by cid desc) where rownum<10000) where rn>99980) order by  cid desc;

9、用查询结果创建一个新表

create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

union all,union操作符 取得两个结果集的并集

select ename,sal,job from emp where sal >2500 union select ename,sal,job from emp where job='manager';

10、decode

decode(a.PROVIDER_CODE,'CTC','电信','CUC','联通','CMC','移动')

 

5、事物

oracle事物：用于保证数据的一致性

set transaction read only;  设置只读事物   

savepoint a1;创建保存点

6、函数

1、字符函数：lower(char)转换大些upper(char)转换小些  length(char)字符长度  substr(char,m,n)取字符串  ||合并字符串  replace(char,a,b)

2、数学函数

7、数据库管理

1、备份表

exp  userid=scott/123456@ORCL  tables=(emp) file = d:/e1.dmp rows=n

2、备份案例

exp  userid=scott/123456@ORCL  owner=scott file=d:/e1.dmp

3、导出数据库

exp userid=system/123456  full=y  inctype=complete  file =d:/aa.dmp

4、导入表

imp userid=scott/123456@ORCL  tables=(emp) file=d:/e1.dmp;

5、倒入数据库 

imp userid=system/123456 full=y file=d:/aa.dmp;

6、数据字典

数据字典记录了数据库的系统信息，他是只读表和视图的集合，数据字典的所有者是sys用户，用户只能在数据字典上执行查询操作，而其维护和修改是有系统自动完成的；

数据字典组成：数据字典基表（存放静态数据）和数据字典视图（动态数据）

7、创建表空间 

create tablespace sp001 datafile 'd:\sp001.dbf' size 20m uniform size 128k;

往表空间里建表 create table student() tablespace sp001;

设置表空间只读  alter tablespace sp001 read only; alter tablespace sp001 read write;

使表空间脱机 alter tablespace sp001 offline;

使表空间联机 alter tablespace sp001 online;

显示表空间所有表  select * from all_tables where tablespace_name="sp001";

查看表属于哪个表空间  select tablespace_name,table_name from user_tables where table_name="emp";

删除表空间： drop tablespace sp001;

增加数据文件大小 alter tablespace sp001 'd:\sp001.dbf' resize 2m;

 

 

8、约束

1、案例

五种约束：not null(非空)、unique（唯一）、primary key（主键）、foreign key(外键)、check(用于强制数据必须满足条件)

create table customer(customerId char(8) primary key,name varchar(50) not null,

address varchar(50),email varchar(50),

sex char(2) default '男' check(sex in('男'，'女'））;性别必须男或女  默认男

9、索引 

create index 索引名 on表名(列名）

1、单列索引

create index indexName on emp(ename);

2、复合索引

create index indexName on emp(ename,job);

查询当前用户索引信息

select index_name,index_type from user_indexes where table_name='表名'；

10、pl/sql编程

1、过程

1、创建过程

create procedure sp_prol is begin 执行部分 insert into emp('dengh','aaa'); end;

show error  查看错误信息

如何调用过程

exec 过程名（参数）

call 过程名（参数）

2、命名规范 

标识符号命名规范

变量   v_作为前缀

常量   c_作为前缀

游标   _cursor作为后缀

例外   _e作为前缀

3、过程案例

过程作用：提高数据库执行效率、提高安全性、可复用、提高应用程序的性能

declare  定义变量

v_ename varchar(5);

begin

执行部分

select ename into v_ename from emp where empno=&aa;

dbms_output.put_line('用户名是：'||v_ename);

end;

/

 

create procedure sp_pro3(spname varchar,newSal number) is

begin

--执行部分，根据用户名修改工资

update emp set sal=newSal where ename=spname;

end;

2、函数

函数用于返回特定数据

基本结构

CREATE  OR  REPLACE   PROCEDURE存储过程名字

(

参数1 IN NUMBER,

参数2 IN NUMBER

)AS

变量1 INTEGER:=0;

变量2DATE;

BEGIN

END存储过程名字

 

1、函数案例

输入雇员的姓名返回该雇员的年薪

create function sp_fun4(spName varchar) return number is yearSal number(7,2);

begin --执行部分

select sal*12+nvl(comm,0)*12 into yearSal from emp where ename = spName;

return yearSal;

end;

/

call sp_func4('scott');  函数调用

 

3、包

包是用于逻辑上组合过程和函数,包由规范和包体组成

1、包案例

create package sp_package

--包体

is procedure update_sal(name varchar); --过程

function annual_income(name varchar) return number;  --函数

end;

2、给包实现包体

create package body sp_package is procedure update_sal(name varchar,newsal number)

is begin

update emp set sal=new sal where ename = name;

end;

function annual_income(name varchar)

return number is annual_salary number;

begin

select sal*12+nvl(comm,0) into annual_salary from emp

where ename=name;

return annual_salary;

end;

end;

exec sp_package1.update_sal1('SCOTT',120);调用包

 

3、定义并使用变量

1、标量类型（scalar）

输入员工号，显示雇员姓名、工资、个人所得税，（税率0.03）为例。说明变量的使用

 

declare

c_tax_rate number(3,2):=0.03;

--用户名

v_ename varchar(5); --emp.ename%type;该发方法就不要定义字符串长度

v_sal number(7,2);

v_tax_sal number(7,2);

begin

select ename,sal into v_ename,v_sal from emp where empno=&no;

--计算所得税

v_tax_sal :=v_sal*c_tax_rate;

dbms_output.put_line('姓名是：'||v_ename||'工资：'||v_sal||'所得税：'||v_tax_sal);

end;

 

2、复合变量（composite):用于存放多个值得变量

declare --申明

--定义一个pl/sql记录类型emp_record_type

type emp_record_type is record(

name emp.ename%type,

salary emp.sal%type,

title emp.job%type);

--定义了一个变量sp_record变量类型emp_record_type

sp_record emp_record_type;

begin

select ename,sal,job into sp_record

from emp where empno=7788;

dbms_output.put_line('员工名：'||sp_record.name);

end;

 

3、参照变量-ref cursor游标变量

输入一个部门号，显示该部门所有员工姓名和工资

declare

--定义一个游标

type sp_emp_cursor is ref cursor;

--定义一个游标变量

test_cursor sp_emp_cursor;

--定义变量

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

--把test_cursor和一个select结合

open test_cursor for select ename,sal from emp where deptno=&no;

--循环取出

loop

    fetch test_cursor into v_ename,v_sal;

    when v_sal<1000 then

    update emp set emp.sal=v_sal+10;

    exit when test_cursor%notfound;

    dbms_output.put_line('名字:'||v_ename||'工资:'||v_sal);

end loop;

end;

/

4、触发器、过程

触发器是指隐含的执行存储过程

create trigger 创建触发器

条件分支语句

一、if -- then

二、if -- then --else

三、if -- then -- else if -- else

创建一个过程输入一个名字如果他的工资少于2000加10%工资

create or replace procedure sp_003(spname varchar) is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where ename=spname;

if v_sal<2000 then

update emp set sal=v_sal*1.1 where ename=spname;

end if;

end;

/                                                                                                                                                                                             

编写一个过程，可以输入一个雇员的编号，如果该雇员的职位为president就让他的工资增加1000，如果该雇员的职位是

MANAGER工资增加500其他增加200

create or replace procedure sp_004(sp_empno number) is

v_empno emp.job%type;

begin

select job into v_empno from emp where empno=sp_empno;

if v_empno='PRESIDENT' then

update emp set sal = sal+1000 where empno=sp_empno;

elsif v_empno='MANAGER' then

update emp set sal = sal+500 where empno = sp_empno;

else

update emp set sal = sal+200 where empno = sp_empno;

end if;

end;

/

 

有输入和输出的存储过程

--in表示输入 默认为in

--out表示输出  表示返回值

create or replace procedure sp_pro8

(spno in number,spname out varchar) is

begin

select ename into spname from emp where empno=spno;

end;

/

 

--返回结果集过程

 

1、创建一个包 在该包中，定义一个类型  类型为游标

create or replace package tespackage as

type test_cursor is ref cursor；

end tespackage;

2、建立有返回值得存储过程

create or replace procedure sp_pro9(spno in number,p_cursor out tespackage.test_cursor) is

begin

open p_cursor for select deptno from emp where deptno=spno;

end;

3、如何在java中的用该过程

//加载驱动

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//得到链接

Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott", "123456");

/**

* java程序调用一个返回结果集的过程

*/

CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");

cs.setString(1, "emp");

cs.setInt(2, 6);

cs.setInt(3, 2);

//注册

cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);

cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);

cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

//得到结果集

int myrows = cs.getInt(4);// cs.getInt(4) 中的4由过程参数决定

int mycount = cs.getInt(5);

System.out.println(myrows+"---"+mycount);

ResultSet rs = (ResultSet)cs.getObject(6);

while(rs.next()){

System.out.println(rs.getString("ename"));

}

ct.close();

cs.close();

4、创建一个分页过程

create or replace

procedure fenye(tableName in varchar2,

pagesize in number,

pagenow in number,

myrows out number, --总记录数

mycount out number, --总页数

pageList out tespackage.test_cursor)  --返回的记录集

is

--定义

v_sql varchar(1000);

v_begin number:=(pagenow-1)*pagesize+1;

v_end number:=pagenow*pagesize;

begin

v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;

open pageList for v_sql;

--计算myrows和mycount

v_sql:='select count(*) from '||tableName;

--执行sql并把返回的值付给myrows

execute immediate v_sql into myrows;

--计算mycount

if mod(myrows,pagesize)=0 then

mycount := myrows/pagesize;

else

mycount := myrows/pagesize+1;

end if;

--关闭游标

end;

11、视图

1、视图描述

视图是一个虚拟的表，其内容有查询定义。同真实表一样，视图包含一系列带有名称的列和行的数据。但是，视图并不存在数据库中以存储的数据值集形式存在。行和列数据来自定义视图的查询所引用的表，并且在引用视图时生成动态。

2、视图与表的区别

1、表要占用磁盘空间，视图不要。

2、视图不能添加索引。

3、使用视图可以简化复杂查询。

4、视图用利于提高安全性，比如不同用户查看不同视图。

3、创建视图

Create view 视图名 as select语句 [with read only]

4、创建或修改视图

Create or replace view 视图名 as select语句 [with read only]

5、删除视图

Drop view 视图名

6、案例

Create view myview as select e.empno,e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

查询视图

Select * from myview;