oracle

Oracle操作命令

文件操作命令

start 文件路径.sql(开始)

edit  文件路径.sql(编辑)

spool 文件路径.sql(记录屏幕显示)

conn  用户名/密码  (连接用户)

set pageSize 设置显示的行数(默认14行)

clear                     清屏

 

oracle操作工具

sql plus

sql plus worksheet

oracle enterprise manager

pL/SQL Developer(需下载)

 

 

oracle安装成功后，会默认生成三个用户

sys     用户：超级管理员     权限最高    它的角色 dba      密码 change_on_install

system  用户：是系统管理员   权限也很高  它的角色 dbaoper  密码 manager

scott   用户：普通用户        密码      tiger

sys与system的区别

sys有create databse 的权限，而system没有,其它相似,日常对oracle管理过程中，使用system

 

 

创建用户(须DBA权限)

create user mv identified by m123(密码必须字母开头)

修改密码

password 用户名

删除用户

drop user 用户名

 

为新用户增加权限(grant)(当创建一个新用户时，没有连接数据库的权限，需手动分配)

权限分为:系统权限、对象权限

系统权限：用户对数据库的相关权限(create session)；

对象权限：用户对其他用户的数据对象操作的权限(select,insert,update,delete,all,create index)；

数据对象（表、视图、索引器、函数、过程、触发器、序列、......）

grant connect to mv(连接数据库的权限)

角色：预定义角色(connect(包含create session))、自定义角色(dba、resource)

grant resource to mv (创建表的权限)

 

grant select on emp to mv(将emp表授权给mv用户)

 

select *From scott.emp (查询scott的emp表)

 

回收权限(revoke)

revoke select on scott.emp form mv(收回用户mv对scott用户的emp表的查询权限)

oracle表的管理(创建，维护，修改, 查询, 数据库的创建)

1、表名和列的命名规范

a)        必须为字母开头

b)        长度不能超过30个字符

c)        不能使用oracle的保留字

d)       只能使用如下字符 A~Z、 a~z、 0~9、 $、#等

2、oracle的数据类型

a)        字符型 

                        i.              char         定长  最大2000个字符(速度快)

                      ii.              varchar2(20)  变长  最大4000个字符

                    iii.              clob       (character large object)      字符型大对象最大4G

b)        数字型

                        i.              Number 可以表示整数，也可以表示小数

                      ii.              Number(5,1)   表示一个小数有5位有效数，2位小数(-999.99_999.99)

                    iii.              Number(5)    表示一个5位整数(-99999_99999)

c)        日期类型

                        i.              Date      包含年月日和时分秒

                      ii.              Timestamp    这是oracle9i对date数据类型的扩展(高精度)

d)       图片

                        i.             

//创建sequence 主键自增长(ORA-02289: 序列不存在的解决)

CREAT  SEQUENCE  (表名)_SEQ

 

INCREMENT BY 1 -- 每次加几个

 

START WITH 1 -- 从1开始计数

 

NOMAXVALUE -- 不设置最大值

 

NOCYCLE -- 一直累加，不循环

 

NOCACHE -- 不建缓冲区

Blob      二进制数据 可以存放图片/声音 4G(安全性高)

3、建表

a)        学生表

                        i.              Sql>create table student(  --表名

Xh  number(4),    --学号

Xm varchar2(20), --姓名

Sex char(2),     --性别

Birthday              date,   --出生日期

Sal  number(7,2)         --奖学金

);

4、修改表字段

a)        添加一个字段

                        i.              Sql>alter table student add(classid number(2));

b)        修改字段的长度

                        i.              Sql>alter table student modify (xm varchar2(30));

c)        修改字段的类型/或是名字(不能有数据)

                        i.              Sql>alter table student modify (xm char(30));

d)       删除一个字段

                        i.              Sql>alter table student drop column sal;

e)        修改表的名字

                        i.              Sql>rename student to stu;

f)         删除表

                        i.              Sql>drop table student;

g)        查看表

                        i.              Sql>desc student（查看表结构，字段名称、类型等…）

5、添加数据

a)        所有字段都插入

                        i.              Insert into student values(‘A001’,’张三’,’男’,’01-5月-1997’,10);

1.        oracle中默认的日期格式’DD-MOD-YY’

2.        改日期的默认格式：alter session set nls_date_format = ‘yyyy-mm-dd’;

3.        修改后：insert into student values(‘A002’,’mike’,’男’,’1997-05-10’,10);

4.        查询某个字段为null的：Select *from student where birthday is null;

6、修改数据

a)        改一个字段:

                        i.              Update student set sex = ‘女’ where xh = ‘A001’;

b)        修改多个字段：

                        i.              Update student set sex = ‘男’,birthday = ‘1980-04-01’ where xh =’A001’;

c)        修改含有null值的数据

                        i.              Update student set sex = ‘男’ where birthday is null;

7、删除数据

a)        删除数据：

                        i.              Delete from student;(表结构还在,写日志,可以恢复) avepoint aa(设置保存点)、rollback to aa(回滚到保存点)

                      ii.              Drop table student;   删除表的结构和数据

                    iii.              Delete from student where xh=’A001’; 删除一条记录

                    iv.              Truncate table student ;(表结构还在，不写日志无法找回删除的记录,速度快)

8、查询数据

a)        Select distinct depton,job from emp;过滤重复行     

                        i.              Select 语句不区分大小写,where name = ‘此处区分大小写’;

                      ii.              select sal*13 "年工资",ename from emp;(为工资列取别名)；

                    iii.              select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;使用算数表达式显示每个雇员的年工资;

b)        处理null值

                        i.              使用nvl函数来处理

c)        如何连接字符串(||)

                        i.              Select ename || ‘is a’ || job from emp;

d)       使用where子句

                        i.              select ename,hiredate from emp where hiredate>'1-1月-1982';注意日期的格式

e)        使用like操作符

                        i.              %;表示任意0到多个字符

                      ii.              _;表示任意单个字符(一个_表示一个字符)

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

f)         在where条件中使用in

                        i.              select *from emp where empno in(7844,234,456);查询连续的员工号

g)        使用is null操作符

                        i.              Select  *from emp where mgr is null;

h)        使用逻辑操作符号

                        i.              select *from emp where (sal>500 or job='MANAGER') and ename like 'J%';

i)          使用order by (多个字段排序)

                        i.              select * from emp order by deptno,sal desc; (按照部门号升序而雇员的工资降序排列) 只使用一个order by;

j)          使用列的别名排序

                        i.              Select ename,(sal +nvl(comm,0))*12 as “年薪” from emp order by “年薪” asc;

9、Oracle表复杂查询

a)        数据分组-------max(最大)、min(最小)、avg(平均)、sum(总数)、count(总数量)

                        i.              Select min(sal),max(sal) from emp;

                      ii.              Select ename,sal from emp where sal=(select max(sal) from emp)

                    iii.              Select * form emp where sal>(select avg(sal) from emp);员工工资大于平均工资

b)       Grop by 和 having 子句

                        i.              Grop by 用于对查询的结果分组统计；

                      ii.               Having 子句用于限制分组显示结晶；

                    iii.              Select avg(sal),max(sal),deptno from emp group by deptno;

                    iv.              Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);

                      v.              分组函数只能出现在选择列表,having、order by 子句中;先有group by 、having、order by;

10、        Oracle 复杂查询----多表查询

11、            (笛卡尔集)规定：多表查询的条件是 至少不能少于表的个数-1

                        i.              Select a1.ename,as.sal,a2.dname from emp a1,dept a2 where a1.deptno = a2.deptno;

显示各个员工的姓名、工资、及其工资的级别

                      ii.              select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

b)       自表查询(是指在同一张表的连接查询)

                        i.              显示某个员工的上级领导的姓名<例：显示’FORD’的上级>

                      ii.              select worker.ename,boss.ename from emp worker,emp boss where worker.mgr =boss.empno and worker.ename='FORD';

c)        子查询(是指嵌入在其它sql语句中的select语句)单例子查询

                        i.              单行子查询：只返回一行数据的子查询语句；

1.        select *from emp where  deptno = (select deptno from emp where ename='SMITH');

                      ii.              多行子查询：返回多行数据的子查询；

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

                    iii.              在多行子查询中使用all操作符

1.        显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

a)        select *from emp where sal> all (select sal from emp where deptno =30);

b)       select *from emp where sal>(select max(sal) from emp where deptno =30);

                    iv.              在多行子查询中使用any操作符

1.        显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

a)        select *from emp where sal> any (select sal from emp where deptno =30);

b)       select *from emp where sal>(select min(sal) from emp where deptno =30);

d)       多例子查询(是指查询返回多个列数据的子查询语句)

1.        查询与smith的部门和岗位完全相同的所有雇员

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

                                                                  i.              注:where(deptno,job)中的列一定要与查询的列相同select deptno,job

2.        在from子句中使用子查询(显示高于自己部门平均工资的员工的信息)

a)        查询出各个部门的平均工资和部门号

                                                                  i.              Select deptno,avg(sal) as mysal from emp group by deptno;    

b)        把上面的查询看做是一张 子表a1

                                                                  i.               select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2, (Select deptno,avg(sal) as mysal from emp group by deptno) a1 where a2.deptno = a1.deptno and a2.sal > a1.mysal;

                                                                ii.              注：当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫作内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名，表取别名时不能加 as ,而列可以.

12、        Oracle表复杂查询--------分页查询(共有三种方式)

1.        rownum 分页

a)          select a1.* , rownum rn from(select *from emp) a1

2.        显示6—10行的数据

a)      select * from (select a1.*,rownum rn from(select * from emp) a1 where rownum <=10) where rn >=6;

3.        按字段sal排序

a)      select * from (select a1.*,rownum rn from(select * from emp order by sal asc) a1 where rownum <=10) where rn >=6;

13、        用查询结果创建新表(一种快捷的建表方法)

1.       一般用于测试时使用

a)      create table myemp2(id,ename,sal) as select empno,ename,sal from emp;

14、        Oracle复杂查询-----合并查询

a)        合并查询：有时为了合并多个select语句的结果，可以使用集合操作符号 union(联合)、union all、intersect、minus(减去)

                                                                  i.              Union：用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行.

1.        select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job = 'MANAGER';

                                                                ii.              Union all：与union相似，但是它不会取消重复行，而且不会排序。

                                                              iii.              Intersect：使用该操作符用于取得两个结果集的交集(重复的部分)；

                                                              iv.              Minus：使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据；

1.        select ename,sal,job from emp where sal > 2500 minus select ename,sal,job from emp where job = 'MANAGER';

15、        创建新的数据库

a)        使用DBCA[数据库配置助手]

                        i.              NEW DATABASE 

 

 

 

JAVA程序操作Oracle数据库

1         Java连接oracle

1.1      使用jdbc_odbc桥连接方式

public static void main(String[] args) {

      

       try {

           //1加载驱动

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

           //2得到连接

                  Connection ct = DriverManager.getConnection("jdbc:odbc:testOracleMv", "scott", "tiger");

          

          

           Statement sm = ct.createStatement();

          

          

           ResultSet rs = sm.executeQuery("select *From emp");

          

           while(rs.next())

           {

              System.out.println("用户名："+rs.getString("ENAME"));

             

           }

       } catch (Exception e) {

           e.printStackTrace();

       }

    }

注：需要配置数据源(ODBC数据源管理器)

 

 

 

 

 

 

 

 

1.2使用jdbc直连方式

public static void main(String[] args) {

      

       try {

          

           //1、加载驱动

           Class.forName("oracle.jdbc.driver.OracleDriver");

           //2、得到连接

                  Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORC","scott","tiger");

          

           Statement st = ct.createStatement();

          

           ResultSet rs = st.executeQuery("select *From emp");

          

           while(rs.next())

           {

              System.out.println("用户名："+rs.getString("ENAME"));

             

           }

           rs.close();

           ct.close();

           st.close();

       } catch (Exception e) {

           e.printStackTrace();

       }

    }

2、使用选定格式插入日期值(to_date函数)

Insert into emp values(9997,’小明’,’MANAGER’,7782,to_date(‘1988-12-12’),78,55,10)

3、         使用子查询插入数据(进行行迁移)

a)      Insert into kkk(Myid,myname,mydept) select empo,ename,depton form emp where deptno = 10;

b)      首先创建一个新表

                    i.              Create table kkk(myId number(4),myname varchar2(50),myDept number(5));

4、         使用子查询更新数据

a)      希望员工scott的岗位、工资、补助与smith员工一样

                    i.              Update emp set(job,sal,comm)=(select job,sal,comm from emp where ename=’SMITH’)where ename=’SCOTT’;

5、       Oracle中事务处理

                        i.              事务用于保证数据的一致性，它由一组相关的dml(增，删，改)语句组成，该组的dml语句要么全部成功，要么全部失败。(没有查询语句)

       如：网上转账就是典型示的要用事务来处理，用以保证数据的一致性。

                      ii.              事务和锁

a)        当执行事务操作时(dml语句)，oracle会在被作用的表上加锁，防止其它用户改表的结构，这对用户来讲是非常重要的。

                    iii.              提交事务

a)        当执行使用commit语句可以提交事务，当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，当使用commit语句结束事务子后，其它会话将可以查看到事务变化后的新数据。

                     iv.              回退事务

a)        保存点(savepoint)：保存点是事务中的一点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点，当执行(rollback)时，通过指定保存点可以回退到指定的点。

b)        例：savepoint a1;   //设置保存点为a1

                                      i.              对数据的dml (注：中间操作数据时不能使用commit)

c)         rollback to a1;  //取消部分事务，回滚到a1保存点

d)        rooback; //取消全部事务

                       v.              java程序中使用事务

public static void main(String[] args) {

      

       try {

          

           //1、加载驱动

           Class.forName("oracle.jdbc.driver.OracleDriver");

           //2、得到连接

                  Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORC","scott","tiger");

           ct.setAutoCommit(false); //加入事务处理

           Statement st = ct.createStatement();

           //给scott的sal - 100

           st.executeUpdate("update emp set sal= sal-100 where ename=’SCOTT’");

           int i=7/0;

           //给smith的sal+100

           st.executeUpdate("update emp set sal= sal+100 where ename=’SMITH’");

           ct.commit();//提交事务

           rs.close();

           ct.close();

           st.close();

       } catch (Exception e) {

           ct.rollback();//如果发生异常，就回滚事务

           e.printStackTrace();

       }

    }

                    i.              只读事务

a)      只读事务指只允许执行查询的操作，而不允许执行任何其它dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据，只读事务不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。

b)      设置只读事务

                                  i.              Set transaction read only;

oracle 删除后的数据恢复（30分钟前的操作）

CREATE TABLE QUICK_TABLE AS

SELECT * FROM pdc_document AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)

Insert into 原表名 select *From QUICK_TABLE;----恢复到原来的表中

 

--------------------------------------------------------------------------------------------------------------------------------

解决使用PL/SQL提示"动态执行表不可访问，本会话的自动统计被禁止"的错误,一消息如下: V$SESSION,V$SESSTAT,V$STATNAME没有被授权,没有权限访问这几张表。

用SYS登录，授权给相应的用户，授权SQL如下: 

 

grant select on v_$statname to scott; 

grant select on v_$sesstat to scott; 

grant select on v_$session to scott; 

grant select on v_$mystat to scott; 

scott 就是要被授权的用户名。

---------------------------------------------配置oracle监听------------------------------------------------------------------

例:

f:\oracle\product\10.2.0\client_2\NETWORT\ADMIN\tnsnames.ora

-----------------------------oracle设置还原点-------------------------------------

savepoint p;     ---设置还原点(p：还原点名)

 

delete from tablename;  ----基本操作

 

rollback to p;   ---回退到还原点