oracle数据库的一些知识

在网上查阅到一些关于oracle的sql。学习下。

1.oracle.jdbc.driver.OracleDriver
2.jdbc:oracle:thin:@127.0.0.1:1521:orcl,"scott","orcl"	
3.网页登录
  http://localhost:5560/isqlplus
4.修改用户属性及查看所有表
  sqlplus
  scott/tiger@first ,  system/manager
  sys/manager as sysdba后可以给用户授权 grant create table,create view to scott
  //给用户授权使之可以创建表空间
  alter user 用户 quota unlimited on 表空间A;
  alter user 用户 quota unlimited on 表空间B;
  或者放开所有表空间grant unlimited tablespace to 用户;
  或者索性给所有权限grant resource,connect,dba to 用户;
  
//修改表字段
增加表字段 alter table tb01 add email varchar2(30)
修改表字段 alter table tb01 modify email varchar2(30)
删除表字段 alter table tb01 drop(email)
重命名表字段 alter table tb01 rename column email to email2


//创建表空间
Create tablespace DS_EP01
            Logging
            Datafile  ‘绝对路径+文件名’  SIZE 100m
            Extent management local;  
删除表空间（在有权限的情况下） conn sys/manager as sysdba    grant drop tablespace to scott
  如果表空间为空，可直接删除 drop tablespace ts01
  若非空  drop tablespace ts01 including contents (and datafile)
 drop            
//创建用户：create user zbk identified by 123456(密码) default tablespace users(默认表空间为users这张表) quota 10M on users(配额为10M)

  grant create session(登录权限),create table,create view,select any table,select any dictionary to zbk;

--查看用户和默认表空间的关系 
select username,default_tablespace from dba_users;

--对表的comment 
    语法：Comment  on  table table_name is  ‘ xxxxxxx ’;
--对字段的comment 
    语法：comment on column table_name.column_name  is ‘xxxxxxxx’;  

//删除表
drop table tb01  --可以恢复  推荐
drop table tb01 purge  --不可以恢复

--恢复表
flashback table tb01 to before drop 

//将命令行中的显示保存在文件中
spool 'd:\oracle\spool.txt
spool off

//执行建立好的脚步文件（后缀为.sql)
@绝对目录下文件


 
--查看当前用户连接：
select * from v$Session;

--查看用户角色
SELECT * FROM USER_ROLE_PRIVS;

--查看当前用户权限：
select * from session_privs;

--查看所有用户所拥有的角色
SELECT * FROM DBA_ROLE_PRIVS;

--查看所有角色
select * from dba_roles;

--查看数据库名
SELECT NAME FROM V$DATABASE;
 

  删除用户：drop user zbk cascade

  备份：oracle:  create table emp2 as select * from emp;
        db2: create table emp2 like emp
             insert into emp2 select * from emp
  
  字段相同的时候
  insert into emp_bak select * from emp where empno=2222
  
  alter user scott account unlock
  conn scott/tiger;
  show user;
  数据字典表：
  desc dictionary;
  select table_name from dictionary;
  select table_name from user_tables;
  select view_name from user_views;
  select constraint_name from user_contraints;
5.desc emp;
  select * from emp;
  select ename,sal*12 from emp;
6.虚表dual
7.系统时间
  select sysdate from dual;
  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
  
8.表的别名
  select ename 姓名 from emp;（需用特殊字符时加双引号）
9.字符串连接：select ename||'zbk' from emp;
  查询的字符串中含有单引号：select ename||'z''b''k'from emp;

10.to_char和to_date和to_number
   select to_char(sal,'$99,999.999) from emp;美元
   select to_char(sal,'L99,999.999) from emp;人民币￥
   select to_char(hiredate,'yyyy-mm-dd  hh:mi:ss') from emp;
   select to_char(hiredate,'yyyy-mm-dd  hh24:mi:ss') from emp;

   select ename,hiredate from emp where hiredate>to_date('1990-01-01 00-00-00','yyyy-mm-dd       hh:mi:ss');

11.函数lower，upper(ename),substr(ename,2,3), chr(65),ascii('A'),round(23.655)四舍五入
11.nvl  空值   任何含有空值的表达式结果都是空值
   select ename,sal*12+nvl(comm,0) 年薪 from emp;
12.group by
   select deptno,max(sal) from emp group by deptno;
   注意：select的字段必须出现在组函数或group by中
   select ename,smax(sal) from emp group by deptno是错误的写法，因为ename既没有出现在。。。又   没有出现在。。。
13.单条select语句执行顺序
   select deptno,max(sal) from emp
     where sal>1000
      group by deptno
         having max(sal)>2000
           order by deptno desc/asc(默认);
14.子查询和连接
    select ename,sal from emp join (select max(sal) maxsal,deptno from emp group by deptno) t        on ( emp.sal=t.maxsal and emp.deptno=t.deptno); 
15.自身连接
    select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;
16.等值连接
   ①.select ename,dname from emp,dept where emp.deptno=dept.deptno;
   ②.select ename,dname from emp join dept on(emp.deptno=dept.deptno) 
   ③.select ename,dname from emp,dept using(deptno)不推荐 
17.左右外连接
   内连接：select ename,dname from emp join dept on(emp.deptno=dept.deptno);
   左外连接：select ename,dname from emp left (outer) join dept on(emp.deptno=dept.deptno);  
   右外连接：select ename,dname from emp right (outer)join dept on(emp.deptno=dept.deptno);
   全连接：select ename,dname from emp full(outer) join dept on(emp.deptno=dept.deptno);
   
   select * from dept a where not exists (select 1 from emp b where a.deptno=b.deptno);
18.求部门平均薪水的等级
   select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno)    t join salgrade s on (t.avg_sal between s.losal and s.hisal);
   或 
   select  t1.deptno,t2.grade from (select deptno,avg(sal) avg from emp group by deptno) t1,salgrade t2 where t1.avg between t2.losal and t2.hisal;
19.求每个职工工资所处等级
   select ename,sal,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
   求每个部门平均工资所处等级
   select deptno,avg(grade) from ( select ename,sal,grade,deptno from emp e join salgrade s on(e.sal between s.losal and s.hisal)) group by deptno;   
20.雇员中有哪些人是经理人
   select ename from emp where empno in (select distinct mgr from emp);  

   比普通员工最高工资还要高的经理人的名称：
    1.普通员工最高工资
   select max(sal) from emp  where emp.empno not in (select distinct mgr from emp where mgr    is not null);
   

21.面试题：不用组函数，求薪水最高值
   select ename,sal from emp where sal>=all(select sal from emp);  


22.oracle分页
   1.用rownum,且rownum只用于 小于和小于等于，其他的都不能用。
   2.面试题：求薪水最高的前五名员工的姓名和工资
     select ename,sal from emp where rownum<=5; XXX
     select ename,sal from (select ename,sal from emp order by sal desc) where rownum<=5
     求薪水最高的6—10员工的姓名和工资
     可分为以下三步：
       按工资降序排序： select sname,sal from emp order by sal desc;
       为第一步的数据加上rownum:  select sname,sal,rownum r from (select ename,sal from  emp order by sal desc);
       取出第6—10条记录： select ename,sal from(select ename,sal,rownum r from (select ename,sal from  emp order by sal desc)) where r>=6 and r<=10;
23.五种约束constraint：
    1.主键：语法上=非空+唯一  id number primary key,
    2.外键：foreign key(deptno) references dept(deptno),被查考的字段必须是主键
    3.非空：name varchar(2) [constraint stu_name] not null; 
    4.唯一：email varchar(50) unique; 
    5.check：
    组合约束：constraint stu_name_email unique(name,email)
24.索引index：增加读操作的效率，修改时效率降低。适合经常访问较大数据，一般不轻易建立索引
   select index_name from user_indexes;
   create index aaa on student(sname，email);
   drop index aaa;
25.视图view：虚表。 安全，简化查询 但当表修改时也要跟着改,维护代价大。视图可以用来更新数据，   但很   少用 
   create view bbb as select id,name,age from student;
26.序列sequence，一般用于主键  oracle特有
   create sequence seq [start [with] 1] [increment [by] 1];
   select seq.nextval from dual;
   insert into student values(seq.nextval,'aaa','');
   drop sequence seq;
27.三范式
   第一范式：要有主键，列不可分（比如学生信息一个字段，保存学号，姓名，年龄等），无重复列。
   第二范式：非主属性非部分依赖于主关键字
   第三范式：属性不依赖于其它非主属性
28.pl/sql
   打开输出 set serveroutput on
--------------------------------------
begin
   dbms_output.put_line('helloworld');//相当于system.out.println
end;
 /
---------------------------------------
declare
    v_name varchar2(20);//声明变量
begin
    v_name:='zhangbokai';   :=为赋值语句
    dbms_output.put_line(v_name);
end;
/
---------------------------------------
declare
  v_num number:=0;
begin
  v_num:=2/v_num;
  dbms_output.put_line(v_num);
exception
   when others then
    dbms_output.put_line('error');
end;
/
---------------------------------------
--变量声明的规则
1.变量名不能使用保留字 如from，select
2.第一个字符必须是字母
3.变量名最多包含30个字符
4.不要与·数据库的表或者列同名
5.每一行只能声明一个变量

--常用变量类型
1.binary_integer:整数，主要用来计数而不是用来表示字段类型
2.number：数字类型
3.char：定长字符串
4.varchar2：变长字符串
5.date：日期
6.long：长字符串。最长2GB
7.boolean：取值true，false和null(默认)值

--变量声明
declare
   v_id number(1);
   v_count binary_integer :=0;
   v_sal number(7,2) :=4000.00;
   v_date date :=sysdate;
   v_pi constant number(3,2) :=3.14;
   v_valid boolean :=false;
   v_name varchar2(20) not null :='MyName';
begin
    dbms_output.put_line('v_sal value:' || v_sal);  //但不能打印boolean的值
end;
/

--变量声明方式2：使用%type属性（比如emp表中sal的类型改变了，那么你声明的变量类型也需要做相对应的改变，但使用%type属性就不需要了）
declare
   v_empno number(4);
   v_empno2 emp.empno%type;
   v_empno3 v_empno2%type;
begin
   dbms_output.put_line('test');
end
/

--复合变量table,record
1.table（相当于java的数组）
declare
  type aaa is table of emp.empno%type index by binary_integer;  
      --类型aaa是table类型，存放的是emp表的empno，下标是binary_integer类型
  v_empnos aaa;  
      --v_empnos就是aaa这种类型
begin
  v_empnos(0) :=1222;
  v_empnos(1) :=2322;
  v_empnos(-1) :=9999;
  dbms_output.put_line(v_empnos(-1) );
end;
/

2.record（相当于对象）
declare
  type bbb is record
     (
       deptno dept.deptno%type,
       dname dept.dname%type,
       loc dept.loc%type
      );
   v_temp bbb;
begin 
   v_temp.deptno :=50;
   v_temp.dname :='zbk';
   v_temp.loc :='shanghai';
    dbms_output.put_line(v_temp.deptno||'  '||v_temp.dname );
end;
/

但上面的也有不足，比如说dept增加了某个字段，那你的程序也需改变，这是可以使用%rowtype声明record变量

--使用%rowtype声明record变量
declare
   v_temp dept%rowtype;
begin
   v_temp.deptno :=10;
   v_temp.dname :='csg';
   v_temp.loc:='shenzhen';
   dbms_output.put_line(v_temp.deptno||'  '||v_temp.dname );
end;
/


pl/sql中的DDL语句：
begin
   execute immediate 'create table t (t_name varchar2(20) default ''aaa'')';
    --本来是单引号，但以有，所以用两个单引号
end;

--if语句
   --取出7369的薪水，如果<1200，输出low，如果<2000，输出middle，否则high
declare
   v_sal emp.sal%type;
begin
   select sal into v_sal from emp where empno=7369;
  
   if(v_sal<1200) then
      dbms_output.put_line('low');
   elsif(v_sal<2000) then   //注意语法  
      dbms_output.put_line('middle');
   else
      dbms_output.put_line('high');
   end if;
end;

--循环语句 以loop开头，end loop结束

1.相当于 do...while
declare
   i binary_integer :=1;
begin
   loop
     dbms_output.put_line(i);
     i :=i+1;
     exit when( i>=11 );
   end loop;
end;

2.相当于while
declare
   j binary_integer :=1;
begin
   while j<11 loop
     dbms_output.put_line(j);
     j :=j+1; 
   end loop;
end;

3.相当于for循环
declare
   i binary_integer :=1;
begin
   for k in 1..10 loop
     dbms_output.put_line(k);
   end loop;

  for k in reverse 1..10 loop   倒序
     dbms_output.put_line(k);
   end loop;
end;

--异常exception
declare 
    v_temp number(4);
begin
    select empno into v_temp from emp where deptno=10;
exception
    when too_many_rows then
       dbms_output.put_line('太多记录了');
    when no_data_found then
       dbms_output.put_line('没有数据');
    when others then 
       dbms_output.put_line('error');
end；


操作出错时记录错误信息到数据库表中

先建立错误日志表：
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);

创建一个序列，id自增
create sequence seq_errorlog_id start with 1 increment by 1;

declare
   v_deptno dept.deptno%type :=10;
   v_errcode number;
   v_errmsg varchar2(1024);
begin
   delete from dept where deptno=v_deptno;  --因为是emp表的外键，所以不能删除成功
    commit;
exception
   when others then
      rollback
        v_errcode :=SQLCODE;
        v_errmsg :=SQLERRM;
      insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
        commit;
end;


游标（重点）
declare
   cursor c is select * from emp;
   v_emp c%rowtype;
begin
   open c;
   fetch c into v_emp;   取出第一条
   dbms_output.put_line(v_emp.enamae);
   close c;
end;

取出所有
declare
   cursor c is select * from emp;
   v_emp c%rowtype;
begin
   open c;
   loop
      fetch c into v_emp;  
      exit when(c%notfound);  
      dbms_output.put_line(v_emp.enamae);//这三条语句不能颠倒顺序，否则结果不对
   end loop;
   close c;
end;

用for循环时游标不需要声明变量，也不需要被打开和关闭，for循环开始时自动打开，结束时自动关闭.最简单
declare
   cursor c is
      select * from emp;
begin
   for v_emp in  c loop
     dbms_output.put_line(v_emp.enamae);
   end loop;
end;


可更新的游标
declare
   cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
      is
        select * from emp for update;
begin
   for v_temp in c loop
      if(v_temp.sal<2000) then
         update emp set sal=sal*2 where current of c;
      elsif(v_temp.sal=5000) then
         delete from emp where current of c;
      end if; 
    end loop;
     commit;
end;

 
带参数的游标
declare
   cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
      is
        select ename,sal from emp where deptno=v_deptno and job=v_job;
begin
   for v_temp in c(30,'clerk') loop
      dbms_output.put_line(v_temp.enamae);
    end loop;
end;

--存储过程store procedure

create or replace procedure p
is
    cursor c  is
        select * from emp for update;
begin
   for v_temp in c loop
      if(v_temp.sal<2000) then
         update emp set sal=sal*2 where current of c;
      elsif(v_temp.sal=5000) then
         delete from emp where current of c;
      end if; 
    end loop;
     commit;
end;

执行存储工程
exec p或 
begin
p;
end;

--带参数的存储过程
create or replace procedure p
 (v_a in number,v_b (默认in)number,v_return out number,v_temp in out number)
is
begin
   if(v_a>v_b) then
      v_return :=v_a;
   else
      v_return :=v_b;
   end if;
   v_temp :=v_temp+1;
end;

执行此存储过程：
declare
  v_a number :=3;
  v_b number :=4;
  v_return number;
  v_temp number :=5;

begin
  p(v_a,v_b,v_return,v_temp);
  dbms_output.put_line(v_return);
  dbms_output.put_line(v_temp);
end;

--删除存储过程


--函数
create or replace function sal_tax
   (v_sal number)
    return number
is
begin
  if(v_sal < 2000) then
     return 0.10;
  elsif(v_sal < 2750) then
     return 0.15;
  else
     return 0.20;
   end if;
end;

执行函数： select ename,sal_tax(sal) from emp;


--触发器 

先建立这么一张表
create table emp_log
(
uname varchar2(20),
action varchar2(10),
atime date
);

create or replace trigger trig
   after insert or delete or updatae on emp (for each now可加)
begin 
   if updating then
      insert into emp_log values(USER,'insert',sysdate);
   elsif inserting then
      insert into emp_log values(USER,'update',sysdate);
   elsif deleting then
      insert into emp_log values(USER,'delete',sysdate);
   end if;
end;

update emp set sal=sal*2 where deptno=10;
若加上for each now，则插入的数据根据更新的数据条数一样，不加则只是插入一条。

--删除触发器
drop trigger trig;

题目：
update dept set deptno=99 where deptno=10,这句话执行是有错误的，因为deptno是外键
这里可以用触发器

create or replace trigger trig
   after update on dept
   for each row
begin
   update emp set deptno= :NEW.deptno where deptno= :OLD.deptno;
end;
在执行更新就可以了（先触发器后结束条件）