Oracle基础

1.常用命令

1.conn切换用户
  conn 用户名/密码
2.show显示当前用户
  show user
3.disc断开和oracle的连接，但是不退出sqlplus窗口
  disc
4.exit断开和oracle的连接，同时退出sqlplus窗口
  exit
5.password修改密码（如果给自己改密码，可以不带用户名。如果给别人修                    改密码<前提是system或者sys来修改>，则需要带                        用户名）
  password 用户名
6.&交互命令
  SQL> select * from emp where job='&job';
  输入job的值：clerk
  原值  1：select * from emp where job='&job';
  新值  1：select * from emp where job='clerk';
7.edit用于编辑脚本
  edit d://aa.sql
8.spool把屏幕上显示的记录保存到文件中，以供以后分析
  spool d:/bak.sql 
  select * from emp;
  spool off;

                           sql plus命令

1.linesize用于控制每行显示多少个字符，默认80字符
  set linesize 120（显示的字符数）

2.pagesize用于每页显示多少行
  set pagesize 100

2.数据类型

                       oracle常用数据类型
一.文本，二进制类型

1.char(size)存放字符串，最大2000个字符，是定长。（一个汉字占两个字符）
       char（32）最多放32个字符，如果超过就报错；如果不够，则用空格补全。
2.varchar2（size）变长，最多存放4000个字符。如果不够，不会补空格。

注意：如果我们的数据长度是固定的，则使用char来存放（速度快）。如果
存放的数据长度是变化的，则使用varchar。

3.nchar（Unicode编码，一个汉字占一个字符），定长。

注意：编码方式Unicode（一个汉字占用nchar的一个字符空间，而一个汉字占用                       char的两个字符空间）
4.nvarchar2（size）（Unicode编码）最多存放4000个字符，变长。

5.clob（character large object）字符型大对象，最大8T，变长。

6.blob（binary large object）二进制数据，可以存放图片/声音8T
    说明：我们在实际开发中很少把文件存放在数据库中（效率问题），实际上我们一般记录文件的一个路径（https：//www.baidu.com/image/2.jpg）然后通过io/网络来操作。
    如果我们要求文件安全性，可以考虑放入数据库

二.数值类型
1.number（p，s）可以存放整数，也可以存放小数，变长。
p为整数位（有效位），s为小数位。范围：1<=p<=38,-84<=s<=127
保存数据范围：-1.0e-130<=number value<1.0e+126
保存早机器内部的范围：1到22bytes
number（5,2）表示一个小数有5位有效数，2位小数，范围-999.99到999.99
如果这个字段输入111.126，则真正保存的数据是111.13（四舍五入）
number（5）《=》number（5,0）
表示一个整数，-99999到99999
如果输入11111.319，则真正保存的数据是11111
三.日期类型
1.date用于表示时间（年/月/日/时/分/秒）

    create table test8(birthday date);
   添加时要用默认格式（‘day-month-year’）
    insert into test8（‘11-11月-11’）
如果我们想使用自己习惯的日期添加，但是需要借助oracle函数
2.TIMESTAMP（n）--邮戳
它与date的区别，也就是它的特点：当你在对数据进行更新的时候，它会自动更新日期

3.分页查询

select t2.* from(select t1.*,rownum rn from (select * from emp) t1 where rownum<=6) t2 where rn>=4;作为分页查询的模板，只需要修改表名，6和4就可以了。

                           oracle分页查询

第一层：select * from emp;
第二层：select t1.*,rownum rn from (select * from emp) t1         where rownum<=6;
第三层： select t2.* from(select t1.*,rownum rn from 
        (select * from emp) t1 where rownum<=6) t2 where rn>=4;

                          oracle合并查询

1.union去掉重复行

2.union all 不会去掉重复行，不会排序

3.intersect 取交集

4.minus取差集

                      oracle的内连接和外连接

1.内连接特点：只有两张表都匹配上，才被选中。

select stu.name,stu.id,exam.grade from stu,exam where stu.id=exam.id;

2.使用左外连接

第一种：select stu.name,stu.id,exam.grade from stu left join exam on stu.id=exam.id;
如果stu的记录没有和exam任何一条记录匹配，stu也要被选中。
在left join左边就是左表。
第二种：select stu.name,stu.id,exam.grade from stu，exam where stu.id=exam.id（+）;

3.右外连

第一种：select stu.name,stu.id,exam.grade from stu right join exam on stu.id=exam.id;
如果右表的记录没有和左表任何一条记录匹配，右表也要被选中。
在right join右边就是右表。
select stu.name,stu.id,exam.grade from stu，exam where stu.id（+）=exam.id;

（+）写在左便，就表示右外连

4.完全外连

显示所以成绩和所有人姓名

select stu.id,stu.name,exam.grade from stu full outer join exam on stu.id=exam.id;

4.事务处理

                             事务处理

1.事务(transaction)
1-1.保存点savepoint（一个事务中可以保存多个保存点）
savepoint 保存点名称
rollback to 保存点（回退到指定的保存点，一旦回退了，就不能再次回退）

注意：一旦提交了事务，则不能会退到任何保存点

2.
2-1.把事务设为不自动提交

conn.setAutoCommit(false);
ps=conn.prepareStatement(sql);
ps=conn.prepareStatement(sql2);
ps.executeUpdate();
ps.executeUpdate();
2-2.把两个操作，看做一个事务提交
conn.commit();

2-3.

catch(Exception e1){
         //如果抛出异常,我们可以回滚事务,在catch里面写
          e1.printStackTrace(); 
      try{
           conn.rollback();
          }
       catch(Exception e){
           e.printStackTrace(); 
          }
}

3.脏读：一个事务读取另一个事务未提交的修改时，产生脏读。在oracle中没有脏读。

4.不可重复读：在同一个事务中的同一个查询，如果因为别的事务修改(update)或删除(delete)，造成本事务查询的结果不一致，这种现象为不可重复读。

5.幻读：在同一个事务中的同一个查询，如果因为别的事务添加(insert)，造成本事务查询的结果不一致，这种现象为幻读。

6.事务隔离级别：它用于指定事务的隔离程度（默认是read commit，不会出现脏读）

6-1.如何把oracle的事务隔离级别设为serializable（串行化）

set transaction isolation level read commit默认，不会出现脏读
set transaction isolation level serializable
set transaction read only
6-2.oracle有三种隔离级别

read commit，默认，不会出现脏读，可能出现不可重复读和幻读
serializable 保证不出现脏读，不可重复读和幻读
read only    保证不出现脏读，不可重复读和幻读

注意：serializable和read only的区别：
 
serializable可以执行dml（数据操作语言）语句
read only只能读取数据。

6-3.Java程序中设置隔离级别
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITED)
一般情况下，我们无需设置

5.plsql编程

                            plsql编程

1.编写一个存储过程，该过程可以向表中添加一条记录

create procedure mypro is
begin
insert into emp(empno,ename) values(4455,'3333');
end;
/

如何调用存储过程
1.先看如何在控制台调用

exec 过程名（参数1，参数2）

exec mypro

2.java程序调用

3.一个可输入参数的简单存储过程

create procedure mypro2(in_empno number) is
    begin
   delete from emp where empno=in_empno;
    end;
    /

exec mypro2(4455)

4.pl/sql可以做什么？
利用pl/sql可以开发过程、函数、包（包体）、触发器
他们的基本编程单元是 块

5.编程规范
5-1.单行注释--，多行注释/*...*/
5-2.当定义变量时，建议用v_作为前缀 v_sal;
...常量，c作为前缀 c_rate;
...游标，_cursor作为后缀 emp_cursor
...例外，e_作为前缀 e_error

6.块的开发（block）

块的基本结构

declare--定义变量部分

begin--执行部分

exception--捕获例外

end；
/

declare和exception部分可选，并不是必须的

6-1只有执行部分（输出，hello world）

默认情况下，hello world不会输出需要设置 (set serveroutput on;)

begin
dbms_output.put_line('hello world');
end;
/

6-2.根据用户输入的雇员编号，输出雇员名字
declare      //定义变量
v_ename varchar2(8);
begin
   --把查询的enamel放入v_ename变量
  select ename into v_ename from emp where empno=&empno;
   --输出v_ename
  dbms_output.put_line('雇员名是'||v_ename);
end;

将其改为一个过程

create procedure mys(in_empno number) is    
v_ename varchar2(8);
begin
  select ename into v_ename from emp where empno=in_empno;
  dbms_output.put_line('雇员名是'||v_ename);
end;

6-3.包含定义部分，执行部分和例外处理部分
如果输入了不存在的雇员编号，会出错，所以使用exception
declare    
v_ename varchar2(8);
begin
  select ename into v_ename from emp where empno=&empno;
  dbms_output.put_line('雇员名是'||v_ename);
  exception
    when no_data_found then
      dbms_output.put_line('你输入的编号不正确,我帮你加一条');
insert into emp(empno,ename) values(1112,'aaaa');
end;
异常的基本语法是
exception
when 异常的名称 then
//对异常进行处理的代码
//......
when 异常的名称 then
//对异常的处理

oracle过程，可以指定是输入参数，还是输出参数

create procedure 过程名（变量名 in 变量类型...
                           变量名 out 变量类型...）

//定义变量

begin

//执行的语句

end；

                             java调用存储过程

CallableStatement cs=null;
                        cs=conn.prepareCall("{call mypro1(?,?)}");
            cs.setString(1,"SMITH");
            cs.setInt(2, 8888);
            cs.execute();

如何在存储过程中，返回一个结果（这个结果可能是简单的一个值，也可能是一个集合）

create function 函数名（参数1...）
return 数据类型 is
定义变量
begin
执行语句 
end

如果调用函数
1.select 函数名（实际参数）from dual
2.在java中调用：

                                包

为什么需要包？
使用包可以更好的管理自己写的函数、过程

建包，声明函数或过程

create [or replace] package 包名 is
   --声明函数
function 函数名（参数1...）return 返回类型；
   --声明过程
procedure 过程名（参数1...）；
end；

实现过程或函数

create or replace package body 包名 is
--实现过程
create procedure 过程名（参数1..）is
--定义变量
begin
--执行语句
end；

--实现函数
create function 函数名（参数1..）return 数据类型 is
--定义变量
begin
--执行语句
end；
end；

在调用包中的某个方法的时候，需要这样调

1.控制台
exec 方案名.包名.过程名（参数1..）
call 方案名.包名.函数名（参数1..）

2.在java中
String sql="{call scott.mypackage1.pro1(?,?)}";
String sql="select scott.mypackage.fun1('KING') from dual";

                           初识触发器
触发器是一个隐含执行的过程，它不是由程序员或者dba来显示调用
，而是因为某个操作引发执行的。

                       pl/sql语法

pl/sql的数据类型分为：
1.标量类型（scalar）
定义一个小数给一个初值5.4  ：=是pl/sql的赋值号
v_sal2 number(6,2):=5.4

为了让我们在pl/sql编程中，让变量的类型和大小与表的列的大小和类型一致可以使用%type

v_sal emp.sal%type
v_ename emp.ename%type

2.复合变量
2-1.pl/sql记录

type 自己定义的记录名称 is recode（
  变量 变量的类型，
  变量 变量的类型，
）

create or replace procedure pro（v_in _empno in number）
is
--定义一个记录数据类型
type hsp_emp_record is recode(
v_ename emp.ename%type,
v_sal emp.sal%type,
v_job emp.job%type
);
--定义一个变量，该变量的类型是hsp_emp_recode
v_emp_record hsp_emp_recode;
begin
select ename,sal,job into v_emp_record from emp where empno=v_in_empno;
dbms_output.put_line('名字：'||v_emp_record.v_ename);
end;

参照变量

1.游标变量:通过游标，我们可以取得返回结果集的任何一行数据，从而提高共享的效率

定义游标变量：
type 自定义游标名 is ref cursor；
变量名 自定义游标名；

打开游标：
open 游标变量名 for select 语句；

取出当前游标指向的行：
fetch 游标变量 into 其它变量名；

判断游标是否指向记录最后：
游标变量%notfound

                    输入部门号，输出姓名和工资

create or replace procedure pro2(v_in_deptno number) is
--定义一个游标变量类型
type hsp_emp_cursor is ref cursor;
--定义一个游标
v_emp_cursor hsp_emp_cursor;
--定义两个变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行语句，打开游标
open v_emp_cursor for select ename,sal from emp where deptno=v_in_deptno;
--循环取出
loop
fetch v_emp_cursor into v_ename,v_sal;--这句话，会让v_emp_cursor向                                         下走
--判断游标是否到达记录的末尾
 exit when v_emp_cursor%notfound;
dbms_output.put_line('name:'||v_ename||'sal:'||v_sal);
end loop;
--关闭游标
close v_emp_cursor;
end;

                       将sal<2000的人，sal加10000

create or replace procedure pro2(v_in_deptno number) is
type hsp_emp_cursor is ref cursor;
v_emp_cursor hsp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_empno emp.empno%type;
begin
open v_emp_cursor for select ename,sal,empno from emp where deptno=v_in_deptno;
loop
fetch v_emp_cursor into v_ename,v_sal,v_empno;
 exit when v_emp_cursor%notfound;
if v_sal<2000 then
  update emp set sal=sal+10000 where empno=v_empno;
  end if;
end loop;
close v_emp_cursor;
end;

函数和过程的区别：
函数必须有返回值，而过程可以没有。
函数和过程在java中调用不一样表

函数select自己的函数名（列）from 
过程使用CallableStatement完成调用

                           控制语句

1.条件语句
分为三种：if--then/if--then--else/if--then---elsif...else

1-1.if 条件表达式 then
---执行语句
---执行语句
end if；
        工资<2000雇员的工资提高10%
create or replace procedure pro(v_in_ename varchar2) is
v_sal emp.sal%type;
begin
  select sal into v_sal from emp where ename=v_in_ename;
  if v_sal<2000 then
    update emp set sal=sal*1.1 where ename=v_in_ename;
    end if;
end; 

1-2.if 条件表达式 then
--执行语句
--执行语句
else
--执行语句
end if；

             奖金为0，加200。不为0，,加100

create or replace procedure pro(v_in_ename varchar2) is
v_comm emp.comm%type;
begin
  select comm into v_comm from emp where ename=v_in_ename;
  if v_comm<>0 then
    update emp set comm=comm+100 where ename=v_in_ename;
    else
       update emp set comm=200 where ename=v_in_ename;
       end if;
end; 

1-3.if 条件表达式 then
--执行语句
elsif 条件表达式 then（可以有多个elsif语句）
--执行语句
else（可以没有）
--执行语句
end if；

输入雇员编号，如果职位是president工资加1000，如果是manager加500，否则加200

create or replace procedure pro(v_in_empno number) is
v_job emp.job%type;
begin
  select job into v_job from emp where empno=v_in_empno;
  if v_job='PRESIDENT' then
    update emp set sal=sal+1000 where empno=v_in_empno;
    elsif v_job='MANAGER' then
     update emp set sal=sal+500 where empno=v_in_empno; 
     else
       update emp set sal=sal+200 where empno=v_in_empno; 
       end if;
  end;

特别说明：pl/sql中字符串的比较是=，比如‘aaa’=‘sss’

                          循环结构

1.loop（以loop开头，end loop结尾）

loop
--执行语句
exit when 条件表达式
end loop

说明：这里的条件表达式如果为true，则继续执行，否则退出

      用户表右id和name属性，循环添加（输入用户id和添加个数）

create or replace procedure pro(v_name varchar2,n number) is
v_id number:=1;
begin
loop
insert into users5 values(v_id,v_name);
exit when v_id=n;
v_id:=v_id+1;
end loop;
end;

2.while

while 条件表达式 loop
--执行语句
end loop；

               输入用户名，循环10个，从11号开始

 create or replace procedure pro(v_name varchar2,n number) is
 v_empno number:=11;
 begin
   while v_empno<=10+n loop
     insert into users5 values(v_empno,v_name);
     v_empno:=v_empno+1;
     end loop;
   end;

                         顺序控制语句

3.goto语句，跳转到特定标号取执行语句

《start loop》
...
goto start loop；
...
goto end loop；
...
《end loop》

null不会执行任何语句
if ... then
...
else
null；

                             分页过程

编写一个过程，输入雇员编号，返回雇员姓名

在java中调用返回列表值（集合/表）

编写一个过程，输入编号，返回该部门所有信息

1.创建一个包，该包中定义一个游标类型
create or replace package pack1 is
type my_cursor is ref cursor;
end;

2.编写过程
create or replace procedure pro1
(v_in_deptno in number,v_out_result out pack1.my_cursor) is
begin
open v_out_result for select * from emp where 
deptno=v_in_deptno;
--为了让java程序可以使用游标，不能在这里关闭游标
--close v_out_result
end;

3.编写java程序
     CallableStatement cs=null
     Class.forName("???");
     conn=DriverManager.getConnection("???","scott","zy");
     cs=conn.prepareCall("{call pro1(?,?)}");
     cs.setInt(1, 10);
            //给第二个？注册，因为是输出                    cs.registerOutParamete(2,oracle.jdbc.OracleTypes.CURSOR);
     cs.execute();
     rs=(ResultSet) cs.getObject(2);
        while(rs.next()){
                System.out.println(rs.getString("ename")+"                         "+rs.getDouble("sal"));
            }

编写一个存储过程，输入表名，每页显示记录数、当前页。返回结果集

1.创建一个包

create or replace package zy is
type my_cursor is ref cursor;
end;

2.编写过程

 create or replace procedure pro(v_table varchar2,v_pagesize number,v_pagenow number,
 v_out_res out zy.my_cursor) is
 v_start number;
 v_end number;
 v_sql varchar2(2000);
   begin
 v_start:=v_pagesize*(v_pagenow-1)+1;
 v_end:=v_pagesize*v_pagenow;
 v_sql:='select t2.* from(select t1.*,rownum rn from 
 (select * from '||v_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
 open v_out_res for v_sql;
 end;

3.编写java程序
CallableStatement cs=null
Class.forName("????");
            
conn=DriverManager.getConnection("????","scott","zy");
            
cs=conn.prepareCall("{call pro(?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 3);
cs.setInt(3, 2);
//给第二个？注册，因为是输出
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR); 
cs.execute();
rs=(ResultSet) cs.getObject(4);
   while(rs.next()){
    System.out.println(rs.getString("ename")+" "+rs.getDouble("sal"));
            }

                        分页程序的扩展

编写一个存储过程，输入表名，每页显示记录数、当前页。返回结果集、返回共有多少页，返回共有多少条记录

 create or replace procedure pro(v_table varchar2,v_pagesize number,v_pagenow number,
 v_out_res out zy.my_cursor,v_out_rows out number,v_out_pagecount out number) is
 v_start number;
 v_end number;
 v_sql varchar2(2000);
   begin
 v_start:=v_pagesize*(v_pagenow-1)+1;
 v_end:=v_pagesize*v_pagenow;
 v_sql:='select t2.* from(select t1.*,rownum rn from 
 (select * from '||v_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
 open v_out_res for v_sql;
 select count(*) into v_out_rows from emp;
 if mod(v_out_rows,v_pagesize)=0 then
   v_out_pagecount:=v_out_rows/v_pagesize;
   else
     v_out_pagecount:=(v_out_rows/v_pagesize)+1;
   end if;
 end;

 

6.sql函数

                                  sql函数

1.to_date(string,'format')将字符串转换为oracle的一个日期

insert into emp (empno,hiredate) 
values(2222,to_date('1988-11-11','yyyy-mm-dd'))

2.to_char
select to_char(sysdate,'yyyy-mm-dd') from dual
返回2016-08-13

3.使用子查询完成行迁移的需求

a.使用sql建表
create table temp# as select empno,ename 
from emp where ename like 'S%'

b.使用子查询完成更新

update emp set(job,sal)=(select job,sal from emp where ename='SMITH')
where ename='SCOTT'

                                sql函数分类
一：函数从大的方面分为单行函数，比如length
                      和多行函数，比如max（），min（），avg（）......

二：单上函数分为：

1.字符函数：

1-1.replace替换
select replace(ename,'A','a')将所有的A替换成a（但数据库里面的数据没有变化，只是显示的时候变化）

1-2.instr(C1,C2,I,J)在一个字符串中搜索指定的字符串，返回发现指定字符串的位置）

C1 被搜索的字符串
C2 希望搜索的字符串zy
I  搜索的开始位置，默认是1
J  出现的位置，默认是1

select instr('oracle traing','ra',1,2) from dual在oracle traing找ra，从第一个位置开始找，查找第二个ra出现的位置

1-3.ascii返回指定字符的十进制数

select ascii('A') from dual
返回65

1-4.chr给出整数返回对应的字符

select chr(65)from dual
返回A

1-5.concat连接两个字符串

select concat('000','111') from dual
返回000111

select concat（ename,'是好人'）from emp可以是变量

1-6.initcap返回字符串，并将第一个字母变为大写
select initcap('smith') from dual
返回Smith

1-7.length返回字符串的长度length
select length('sss') from dual
返回3
select length('好啊') from dual
返回2，说明length在统计的时候，汉字也是一个空间

1-8.lower返回字符串，并将所有的字符小写
select lower('AAccVV') from dual
返回aaccvv

1-9.upper返回字符串，并将所有的字符大写
select lower('AAccVV') from dual
返回AACCVV

1-10.substr（‘string’，start，count）取子串
select substr('abcdefg',1,4) from dual
返回abcd

1-11.lpad，rpad
select lpad('Page',10,'*') from dual（左边填充，Page要显示的字符，10显示10个字符串，）
返回******Page

select rpad('Page',10,'*') from dual
返回返回Page******

1-12.ltrim，rtrim删除左，右边出现的字符串
select rtrim('scdfff','f') from dual
返回scd

select ltrim('ffffscd','f') from dual
返回scd

1-13.trim去掉指定字符的前面和后面的字符
select trim('a' from 'aa000999a') from dual
返回000999

1-14.ceil返回大于或等于给出数字的最小整数(向上取整）
select ceil(23.56) from emp
返回24 

1-15.floor 向下取整
select floor(23.98) from dual
返回23

1-16.exp返回数字e的n次方根
select exp(2) from dual

1-17.mod函数 取模
select mod(5,2) from dual
返回1

1-18.power（n1，n2）返回n1的n2次方
select power(2,10) from dual
返回1024

1-19.round四舍五入
1-20.trunc截取一个整数
select trunc(45.6) from dual
select trunc(45.1) from dual
都返回45
select trunc(125.899,2) from dual
返回125.89

1-21.sign大于0返回1，小于0返回-1，等于0返回0
select sign(10) from dual
返回1

2日期函数

2-1.add_months
select add_months(to_date('1999-2-9','yyyy-mm-dd'),2) from dual
返回1999-4-9

2-2.last_day返回日期的最后一天
select last_day(sysdate) from dual
返回2016/8/31 17:47:2

2-3.sysdate系统时间

2-4.hextoraw将十六进制转换成二进制

2-5.rawtohex二进制转换成十六进制

2-6.to_number将字符转换成数字

2-7.decode类似于switch语句
select deocde(deptno,
                     10,'10号部门'，
                     20，'10号部门'，
                     30，'10号部门'，) from emp

                        oracle系统函数

1.当前数据库实例

select sys_context('userenv','db_name') from dual
返回orcl

7.表的管理

create table students(
Id number,
name varchar2(64),
sex char(2),
birthday date,
fellowship number(8,2),
resume blob
);

                                 表的管理

1.添加一个新的字段
alter table 表名 add （新的列名 列的数据类型）

2.修改字段的类型
alter table 表名 modify （列名 列的数据类型）

3.删除一个字段
alter table 表名 drop colomn 列名

4.修改表的名字
rename 旧表的名字 to 新的名字

                         oracle的crud操作

crud就是c （create） r （retrieve/read）

1.删除数据有两种方式

第一种：delete from 表名
第二种：truncate table 表名 （速度快但是不能回滚）

2.select * from emp where ename='SMITH'
特别说明：oracle的sql不区分大小写，但是内容区分大小写（SMITH必须大写）

                         oracle常用函数

1.处理数据为空的函数：nvl
nvl（comm，0）如果comm为空，就返回0.否则返回本身comm

2.在查询是如果希望把多列拼接起来，作为一列返回，可以使用||
具体用法：select ename||'是一个'||job from emp;

结果：ENAME||'是一个'||JOB
      -------------------------
      SMITH是一个CLERK
      ALLEN是一个SALESMAN
      WARD是一个SALESMAN

面试题：我们希望删除一个用户，同时保留改用户的数据对象，怎么处理？
1）锁定改用户  alter user 用户名 account lock
2）这时该用户已经不能登录到数据库了，但是system用户依然可以使用它的数据对象。
3）解锁 alter user 用户名 account unlock

3.查找1982-1-1后入职的员工（使用to_char函数）
select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1982-1-1';

查询1980年入职的员工
select * from emp where to_char(hiredate,'yyyy')='1980';
查询4月份入职的员工
select * from emp where to_char(hiredate,'mm')='4';

                             oracle查询

1.数据分组-max，min，avg，sum，count

1-1 avg（sal）会不会把工资为空的统计进去？不会，忽略空。
如果你希望空值也考虑进去
select sum(sal)/count(*) from emp;
1-2 count(*)也可以对一个字段进行统计 比如count（comm）

多表的查询条件是，条件的人数不能少于表的个数-1

8.管理权限和角色

    系统权限：
1.create session连接数据库
2.create table建表
3.create view 建视图
4.create public synonym建同义词
5.create procedure建过程、函数、包
6.create trigger建触发器
7.create cluster建簇

如何使用select语句来查询有哪些系统权限
select * from system_privilege_map order by name

   系统权限案例：

创建两个用户ken，tom，初始阶段没有任何权限，如果登录就会出错

create user ken identified by m123
create user tom identified by m123

给用户ken赋权限：（带上with admin option）

grant 权限名称 to 用户名[with admin option]

grant create session to ken with admin option （with admin     option就表示ken可以把他得到的权限，继续向别的用户转发）
grant create table to ken with admin option 
grant create view to ken
   回收系统权限：

revoke create session from ken

  一个用户可以访问另一个用户的表：

  授予对象权限：

grant 对象权限 on 方案.数据对象 to 用户名[角色]
      [with grant option]可以把权限直接赋给角色

希望monkey用户操作scott.emp表
1.希望monkey用户可以查询scott.emp的数据

grant select on scott.emp to monkey

2.希望monkey用户可以修改scott.emp的数据

grant update on scott.emp to monkey

3.一次把所有权限赋给monkey

grant all on scott.emp to monkey

4.如果要修改表的结构，要授予alter权限

grant alter on scott.emp to monkey
 
5.如果想在别的方案的表上建立索引，则必须授予index权限

grant index on scott.emp to monkey

  回收对象权限：

revoke  对象权限 on方案.数据对象 from 用户
注意：对象的权限是级联回收

revoke select on scott.emp from monkey

                          角色的管理

1.角色是一组权限的集合，目的是为了简化对权限的管理，从而达到对用户简单的管理。

2.角色的分类：预定义角色和自定义角色。角色可以包含系统权限和对象权限。

2-1.预定义角色有33种，常用的是connect，dba，resource。

注意：如何查看某个角色具有那些权限？

select * from dba_sys_privs where grantee='DBA'//dba角色的名称要大写

select * from dba_sys_privs where grantee='CONNECT'

2-2.自定义角色：

2-2-1.建立角色（不验证）

create role 角色名 not identified

2-2-2.建立角色（带验证）

create role 角色名 identified by 密码

2-3.创建角色
a.  create role myrole not identified
b.grant create session to myrole  //给角色赋权限
  grant select on scott.emp to myrole
  grant insert on scott.emp to myrole
  grant uodate on scott.emp to myrole
这时，我们可以把myrole这个自定义角色赋给指定的某个用户（aa）

c.create user aa identified by m123
d.grant myrole to aa

3.删除角色（预定义角色也可以删除）

drop role 角色名

drop role myrole

9.异常处理

                                  例外处理

begin

执行语句...

exception

when 异常情况名 then

执行代码

when 异常情况名 then

执行代码

when others then

end

输入雇员编号，显示雇员名字。如果不存在编号，就会有异常

create or replace procedure pro(v_in_empno number) is
v_ename emp.ename%type;
begin
  select ename into v_ename from emp where empno=v_in_empno;
  dbms_output.put_line('姓名:'||v_ename);
  exception
    when no_data_found then
      dbms_output.put_line('Do not have this number');
end;

注意：

捕获异常的两个目的：1.给用户提示更加明确。
                    2.可能需要对异常进行业务处理

例外的几个常见案例：

1.too_many_rows返回多行

2.case_not_found

3.zero_divide除零

      数据库管理员和对数据库（方案、表）的备份和恢复

sys和system用户的比较

1.sys用户对应方案存放的数据更重要，system用户对应方案存放次一级的数据
2.sys用户只能以as sysdba 或者system登录，system可以用normal登录。

3.使用import和export对数据库（方案、表）进行备份和恢复

4.导出自己表

exp userid=用户名/密码@数据库实例名 tables=（表名1，表名2...）file=备份路径

比如导出emp表

exp userid=scott/zy@orcl tables=(emp,dept) file=d:/emp.dmp

特别说明：该命令在dos窗口下执行

5.导出别人的表

exp userid=用户名/密码@数据库实例名 tables=（
方案名.表名1，表名2...）file=备份路径

6.导出表的结构（不要数据）

exp userid=用户名/密码@数据库实例名 tables=（表名1，表名2...）file=备份路径 rows=n

7.直接导出表

主要目的是提高导出速度

exp userid=用户名/密码@数据库实例名 tables=（表名1，表名2...）file=备份路径 direct=y

8.导出方案

8-1.导出自己的方案

exp userid=用户名/密码@数据库实例名 owner=用户名 
file=备份路径 

8-2.导出别人的方案

exp userid=用户名/密码@数据库实例名 
owner=（用户1，用户2）file=备份路径 

10.视图和触发器

                                  视图

1.视图是oracle的一种数据对象，视图的主要用处是简化操作，提高安全，满足不同用户的查询需求，视图不是一个真正存在的物理表，它是根据别的表，动态生成。

2.创建一个视图

创建和emp表的（empno，ename，job）完全一致的视图

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

说明：如果我们创建视图的时候，我们带了with read only 则该视图只能读，而不能进行其他操作。

注意:当删除视图的一条记录时，它对应的表中的那条记录也会被删除。
如果创建视图的时候，不希望用户通过对视图的操作，来改变原表的数据。建议带上with read only

create view empview as select empno,ename,job from emp;

查询部门编号，部门名字和雇员名字

视图简化：

create view myview as select empno,dname,ename from emp,dept where emp.deptno=dept.deptno with read only

2.删除视图

drop view 视图名

3.视图和表的区别：

3-1.表要占用磁盘空间，视图不占
3-2.视图不能加索引
3-3.视图可以简化操作
3-4.视图提高安全性

                              触发器

1.提出问题：
  当用户登录的时候，自动记录该用户的名字，登录时间，ip...
  当用户在星期天对某张表进行delete时，提示用户不能操作
  当用户删除某条记录时，自动将该记录保存到另一张表去

2.触发器的分类：

2-1.dml（insert，delete，update）触发器
2-2.ddl（create table create view...drop...）触发器
2-3.系统触发器（与系统相关的触发器，比如用户登录，退出，启动数据库，关闭数据库）

3.创建触发器

create [or replace] trigger trigger_name
{before|after}
{insert|delete|update[of column[,column...]]}
on[schema.]table_name
[for each row]
[when condition]
begin
trigger_body;
end;

案例：在某张表（my_emp）添加一条记录时，提示“添加了一条数据”

create or replace trigger tri1
after insert on
scott.my_emp
begin
  dbms_output.put_line('添加了一条记录');
  end;

在某张表（my_emp）修改多条数据的时候，提示多次“修改了数据”

=》讲解行级触发器和语句级触发器

create or replace trigger tri2
after update on
scott.emp
for each row--表示这是一个行级触发器（有这条语句就是行级触发器，没有就是语句级触发器）
begin
  dbms_output.put_line('修改了一条数据');
  end;

dml触发器：

为了禁止工作人员在休息日改变原信息，开发人员可以建立before语句触发器，从而实现数据的安全

create or replace trigger tri3
before delete on
scott.emp
begin
if to_char(sysdate,'day') in ('星期五','星期六') then
  dbms_output.put_line('休息日，不能删除原');
  RAISE_APPLICATION_ERROR(-20001,'休息日，不能删除原');
  end if;
end;

特别说明： RAISE_APPLICATION_ERROR这个过程是oracle提供的，可以传入两个参数，第一个是自定的错误号-20000到-20999之间，第二个参数是提示一个信息

4.为了禁止工作人员在休息日改变员工信息，开发人员可以建立before语句触发器，从而实现数据的安全性，再给出提示时，明确提示用户是进行的insert，update，还是delete操作

create or replace trigger tri4
before delete or insert or update on
scott.emp
begin
  case
    when deleting then
      dbms_output.put_line('不能shanchu');
      raise_application_error(-20002,'不能shanchu');
    when updating then
      dbms_output.put_line('不能xiugai');
      raise_application_error(-20003,'不xiugai');
     when inserting then
      dbms_output.put_line('不能charu');
      raise_application_error(-20002,'不能cahru');
   end case;
end;

1.修改员工的工资时，显示修改前后的工资
2.如何确保修改工资不能低于原来工资

create or replace trigger tri4
before update on
scott.emp
for each row
begin
  if :new.sal<:old.sal then
    dbms_output.put_line('工资不能低于原来工资');
    raise_application_error(-20005,'工资不能低于原来工资');
    else
      dbms_output.put_line('原来的工资是'||:old.sal||'现在的工资是'||:new.sal);
       end if;
end;

:new访问操作完成后列的值
:old访问操作完成前列的值

编写一个触发器，保证用户在删除表（emp）的一条记录时，自动把删除的记录备份到另一张表中（emp_bak）(要事先建好emp_bak表)

create or replace trigger tri1
before delete on
scott.emp
for each row
begin
  --执行语句
  insert into emp_bak(id,name) values(:old.empno,:old.ename);
end;

触发器管理

1.禁用

alter trigger 触发器名 disable

2.激活

alter trigger 触发器名 enable

3.禁用一张表所有触发器

alter table 表名 disable all trigger（10g）；

4.激活一张表所有触发器

alter table 表名 enable all trigger（10g）；

5.删除触发器

drop trigger 触发器名

11.数据库完整性

                             

                           数据库完整性

1.维护数据库的完整性

有三种方法：约束，触发器，应用程序（函数，过程）

1-1.  not null 
      unique（唯一），不能重复，可以为空，而且可以为多个空
      primary key
      foreign key
      check

注意：primary key和unique的区别：
 
a. 一张表可以有多个unique，但只能有一个主键
b. 每张表都应该有一个主键
c. 主键不能为空，unique可以为空
d. primary key所在列，会自动创建索引，unique不会自动创建索引

1-2.

  第一： alter table 表名 add constraint 约束名 约束种类                     [check/unique/primwry key...]（字段）
  第二： alter table 表名 modify 字段名 not null
1-3.删除约束

  alter table 表名 drop constraint 约束名

约束名：一个表的每一个约束都对应一个名称。因为一张表只有一个主键，因此在删除主键的时候，可以直接使用primary key
alter table 表名 drop constraint primary key
如果有外键值，指向修改主键的某个值，则不能删除成功，需要使用cascade（级联）

12.序列

                                 序列（sequence）

1.需求：在oracle的一张表中，我们希望有一列，假设该列是整形，我们希望添加记录的时候，该列能够自动增长比如从1开始增长，每次增长1）

解决方式：oracle是利用序列（sequence）来完成

2.如何创建一个序列：

   create sequence mysql
   start with 1      //初始值
   increment by 1   //一次增长多少
   minvalue 1
   maxvalue 30000
   cycle  //cycle表示序列增加到30000，从1重新开始，如果不重新            开始则使用nocycle
   nocache  //不缓存（如果是cache 10：表示一次产生10个号共你                       使用，使用缓存产生号，优点是提高效率，                     缺点是可能产生跳号）
   /

创建一张表：
create table testzy（
id number primary key,name varchar2(32））

使用：insert into testzy values(mysql.nextval,'abx');

说明：mysql：表示序列名字，nextval:这时一个关键字

3.序列的细节

3-1.可以为表中的列自动产生值
3-2.由用户创建数据库对象，并可由多个用户共享。system可以使用scott创建的序列
3-3.如果system使用scott的序列，从什么开始增长?答：接着增长
3-4.一般用于主键或唯一列（unique）
3-5.可以使用 序列名.currval来看当前的序列到多少号

select scott.mysql.currval from dual

                                 索引

1.创建索引

1-1.单列索引：

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

1-2.复合索引：

create index 索引名 on 表名（列名1，列名2）

细节：在大表里建索引才有意义；
      在where字句或是连接条件上经常引用的列上建立索引；
      索引的层次不要超过4层

13.用户管理

                           oracle用户管理

需求：假设你是oracle管理员，当一个同事入职，你需要给他分配一个账号

1.创建用户（需要dba权限的用户才能操作，比如system，sys。用户密码不            能以数字开头）
  create user 用户名 identified by 密码
        因为oracle刚刚创建的用户是没有任何权限的，所以需要管理员给        用户分配相应的权限，才能够登录。
        比如给 xiaoming分配权限
2.如何给用户分配权限（system给用户分配权限/回收权限）
   分配权限：grant create session to xiaoming
3.oracle管理用户的机制(原理）
 （1）权限：分为系统权限和对象权限（表）
  (1-1)系统权限是和数据库本身管理相关的权限
        create session登录权限
        create table创建表的权限
        create index
        create view
        create sequence...
 （1-2）对象权限是和用户操作数据对象相关的权限
        update
        insert
        delete
        select
 （2）角色：分为自定义角色和预定义角色（角色中含义许多权限）
 （2-1）预定义角色：把常用的权限集中起来，形成角色。比如dba，        connect，resource
  分配角色:grant connect to xiaoming
  回收角色:revoke connect from xiaoming
 （2-2）自定义角色：自己定义角色
4.删除用户
  drop user用户名[cascade]
  当我们删除一个用户的时候，如果这个用户已经创建过数据对象，那么我们在删除用户的时候，需要加选项cascade，表示把这个用户删除，同时把该用户创建的数据对象一并删除
5.oracle的方案（schema）：
  
  理解：当一个用户创建好以后，如果该用户创建了一个任意数据对象，这时我们的dbms就会创建一个对应的方案与该用户对应。并且该方案的名字和用户名一致。
   比如说：scott用户创建了一个表，那么这个表是放在scott方案里面的。如果小红也创建了一个表，那么她的表是放在她的方案里面的。
   如果想看到某个用户的方案有什么数据对象，我们可以用pl/sql developer
   方案这个概念的设计运用
要求：完成功能：让xiaohong用户可以去查询scott的emp表   
      步骤:1.先用scott登录（conn scott/zy）
           2.赋权限（grant select on emp to xiaohong）
            xiaohong 查询scott的emp表时，需要
             select * from scott.emp
6.权限转移：
   6-1.系统权限：with admin option
   6-2.对象权限：with grant option表示得到对象权限的用户，可以把权限继续分配。
  将scott的emp表的权限给tea，然后tea把emp表的权限给stu
  conn scott/zy
  grant all on scott.emp to tea with grant option;(将scott的权限给        tea)
  conn tea/tea
  grant all on scott.emp to stu(将emp表的权限给stu表)

                         用户口令管理

1.账户锁定
    1-1.使用profile文件对口令进行管理
需求：只允许某个用户，最多登录三次，如果三次没有成功，则锁定两天，两天后才能重新登录
    create profile 文件名 limit failed_login_attempts 3 
    password_lock_time 2;
    分配文件：
    alter user 用户名 profile 文件名（被分配的用户三次密码输错，就                                      会被锁定两天）
    1-2.解锁（给锁定的用户解锁）
    alter user 用户名 account unlock
2.终止口令
    2-1需求：说一个账号密码，最多用10天，宽限期为2天，到时必须设置新            的密码
    create profile 文件名 limit password_life_time 10
    password_grace_time 2;
    分配文件：
    alter user 用户名 profile 文件名
    2-2解锁（给锁定的用户解锁）
    alter user 用户名 account unlock
3.删除profile文件
  drop profile文件名
4.远程登录DB（启动服务）
    4-1.windows操作系统
        可以在dos窗口下运行
       1） lsnrctl start[用于启动监听服务]
       2）  oradim -startup -sid ORCL[启动数据库实例] 
    4-2.Unix操作系统/linux操作系统
       1） lsnrctl start[用于启动监听服务]

                               oracle的认证方式

  1-1.普通用户，默认是以数据库方式验证，比如conn scott/zy
  1-2.特权用户，默认是以操作系统认证（即：只要当前这个用户实在ora_dba这个组中，则可以通过），比如
        conn system/zy as sysdba dbms一看到as sysdba则认为要以特权用户登录，前面的用户名和密码不看，登录后，自动切换成sys用户<=>
          conn sys/zy
  1-2-1.如果当前用户不在ora_dba组，这时使用数据库验证方式
  1-2-2.我们可以通过修改sqlnet.ora文件，让特权用户登录的时候，直接使用数据库验证，搜索到sqlnet.ora文件修改
  SQLNET.AUTHENTICATION SERVICES=(NIS)将NIS改为NONE

  2.如何找回管理员的密码

   2-1.搜索名为PWD数据库实例名.ora文件
a.数据库实例名是根据实际情况定，比如PWDORCL.ora
b.拷贝一份密码文件,然后删除文件
c.生成新的密码文件，在dos窗口输入（PWDORCL.ora是密码文件）
orapwd file=F：\hsporacle\oracle\database\PWDorclhsp.ora password=shunping entries=10
file：表示密码文件全路径名
password：表示新的密码
entries：表示登录sys最多用户
如果希望新的密码生效，则需要重新启动数据库实例