欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

oracle数据库的一些知识

程序员文章站 2022-07-08 23:06:50
...

在网上查阅到一些关于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;
在执行更新就可以了(先触发器后结束条件)

 

相关标签: orcle 数据库