你的位置:首页 > 数据库

[数据库]oracle基础学习


 

--解锁用户,需要使用dba权限conn sys/1234 as dba;alert user scott account unlock;/--initcap方法,实现字符串首字符大写,其余字符小写,受空格,逗号等非字母符号限制     select initcap('hello word,da jia hao!') Hello from dual;--平均工资大于2000的分组select avg_sal,deptno from   (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal >2000;having语句用法:select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;/--求部门中哪些人的薪水最高select ename,sal from emp e join (select max(sal) max_sal,deptno from emp group by deptno) t on (e.deptno = t.deptno and e.sal = t.max_sal)/--求部门平均薪水的等级select deptno,avg_sal,grade from salgrade s join (select avg(sal) avg_sal,deptno from emp group by deptno) t on (t.avg_sal between s.losal and hisal)/--求部门平均的薪水等级select avg(t.grade) avg_grade,t.deptno from (select ename,deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t group by t.deptno/--雇员中哪些人是经理人select ename from emp where empno in (select distinct mgr from emp)/--不用函数求薪水的最高值select distinct ename,sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal))/select t.ename,t.sal from (select ename,sal from emp order by sal desc) t where rownum<2(当最大薪水有多值时不对)/--求平均薪水最高的部门的部门编号select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno))等同于:select deptno,avg_sal from(select avg(sal) avg_sal,deptno from emp group by deptno)where avg_sal = (select max(avg(sal)) from emp group by deptno)/--求平均薪水最高的部门名称select dname from dept where deptno = (  select deptno from     ( select deptno,avg(sal) avg_sal from emp group by deptno )   where avg_sal =    ( select max(avg_sal) from       (select avg(sal) avg_sal,deptno from emp group by deptno)     ))/--求平均薪水的等级最低的部门的部门名称select t.deptno,t.dname from dept t where t.deptno = (  select deptno from   (    select deptno,grade,avg_sal from       (select avg(sal) avg_sal,deptno from emp group by deptno)         join salgrade s on (avg_sal between s.losal and s.hisal)    where grade =       (        select min(grade) from         (          select deptno,grade,avg_sal from             (select avg(sal) avg_sal,deptno from emp group by deptno)             join salgrade s on (avg_sal between s.losal and s.hisal)        )      )  )  )等同于:select t.deptno,dname,avg_sal,grade from(  select deptno,avg_sal,grade from    (select avg(sal) avg_sal,deptno from emp group by deptno) t1  join salgrade s on (t1.avg_sal between s.losal and s.hisal)) tjoin dept d on (t.deptno = d.deptno)where t.grade =   (    select min(grade) from     (      select deptno,avg_sal,grade from        (select avg(sal) avg_sal,deptno from emp group by deptno) t1      join salgrade s on (t1.avg_sal between s.losal and s.hisal)    )    )/--求平均等级最低的部门名称select t.dname from dept t where t.deptno = (   select deptno from     ( select deptno,avg(grade) avg_grade from      ( select e.deptno,s.grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)      )     group by deptno    )   where avg_grade =     ( select min(avg_grade) from       (select deptno,avg(grade) avg_grade from         (select e.deptno,s.grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)         )       group by deptno       )     ) )/--创建视图,首先赋予权限conn sys/1234 as sysdba; sys不校验密码,任意密码即可。grant create table,create view to scott;create view v_dept_avg_sal_info asselect deptno,grade,avg_sal from(select deptno,avg(sal) avg_sal from emp group by deptno) tjoin salgrade s on (t.avg_sal between s.losal and s.hisal);因此,求平均等级最低的部门名称可以用一下语句替换:select t.deptno,d.dname,avg_sal,grade fromv_dept_avg_sal_info t join dept d on (t.deptno = d.deptno)where t.grade = (select min(grade) from v_dept_avg_sal_info);/--求部门经理人中平均薪水最低的部门名称(经理人指empno存在于mgr中的员工)select dname, t.deptno, avg_sal from (select deptno, avg(sal) avg_sal     from emp     where empno in (select distinct mgr from emp where mgr is not null)     group by deptno) t join dept d  on (d.deptno = t.deptno) where t.avg_sal =    (select min(avg_sal)     from (select deptno, avg(sal) avg_sal         from emp         where empno in            (select distinct mgr from emp where mgr is not null)         group by deptno))创建视图v_mgr_sal_info,如下: create view v_mgr_sal_info asselect deptno, avg(sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno;select dname, t.deptno, avg_sal from v_mgr_sal_info t join dept d  on (d.deptno = t.deptno) where t.avg_sal = (select min(avg_sal) from v_mgr_sal_info);/--求比普通员工的最高薪水还高的经理人名称:select ename from emp where empno in (select distinct mgr from emp where mgr is not null)  and sal >    (select max(sal)     from emp     where empno not in        (select distinct mgr from emp where mgr is not null))/--删除用户drop user mc cascade;--备份用户 backup scottexp--创建用户create user mc identified by 1234 default tablespace quota 10M on users;--赋予权限grant create session,create table,create view to mc;--导入数据imp--快速备份表,即创建一个一样的表create table dept1 as select * from dept;--求薪水值最高的前五名雇员;select ename,sal from   (select ename,sal from emp order by sal desc ) where rownum<6;/--求薪水值第六名到第十名的雇员;select ename,sal from  (select ename,sal,rownum r from    (select ename ,sal from emp order by sal desc)  )where r>5 and r <11;//*** 有3个表S,C,SC* S(SNO,SNAME)代表(学号,姓名);* C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)* SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)* 问题:1,找出没选过“黎明”老师的所有学生姓名。* 2,列出2门以上(含2门)不及格学生姓名及平均成绩。* 3,既学过1号课程又学过2号课程所有学生的姓名。* 请用标准SQL语言写出答案。*/答案:1,select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno) where c.cteacher <> '黎明';2,--学生姓名select sname from s where s.sno in ( select sno from sc where sc.scgrade < 60 group by sco having count(*) >=2 );--平均成绩select avg(scgrade) from sc where sc.sno in ( select sno from sc where sc.scgrade < 60 group by sco having count(*) >=2 ) group by sc.sno;3,select sname from s where s.sno in (select sno from sc where sc.cno =1 and sno in ( selcet distinct sno from sc where cno = 2 ) );--一个事务(transaction)起始于一条dml语句(insert,update,delete),终止于①commit语句;②ddl语句(create,drop)和dcl语句(grant,remove);正常断开连接时,transaction自动提交,非正常断开连接,transaction自动回滚。--创建一张学生表  --约束类型:不为空约束,主键约束,外键约束,唯一性约束,check约束create table stu1   --字段级约束条件(  id number(6),  name varchar2(20) constraint stu_name_nn not null,--不为空约束  sex number(1),  age number(3),  sdate date,  grade number(2) default 1,  class number(4) references class(id),--外键约束,外键约束被参考条件必须是主键  email varchar2(50) unique --唯一约束条件);create table stu   (  id number(6) primary key,--主键约束  name varchar2(20) constraint stu_name_nn not null,--不为空约束  sex number(1),  age number(3),  sdate date,  grade number(2) default 1,  class number(4),  email varchar2(50),  constraint stu_name_email_uni unique(name,email), --表级约束  constraint stu_class_fk_foregin key (class) references class(id)); create table class(  id number(4) primary key,  name varchar2(20) not null);alert table stu add(addr varchar2(100));alert table stu drop (addr);alert table stu modify (addr varchar2(120));alert table stu add constraint stu_class_fk foreign key (class) references class(id);--创建与删除索引create index idx_stu_email on stu (email);drop index idx_stu_email;/三范式:①要有主键,列不可分;②不能存在部分依赖;(联合主键情况下,非主键列不能部分依赖于主键)即每张表只描述一件事③确保每列都和主键列直接相关,而不是间接相关。--pl/sql结构化语言--变量声明的规则: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值。--最简单的语句块begin  dbms_output.put_line('HelloWorld');end;/在dos命令窗口输出命令:set serveroutput on;--简单的PL/SQL语句块declare  v_name varchar2(20);begin  v_name := 'myname';  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;/--变量声明declare  v_temp 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_temp value:' || v_sal);end;/注意:dbms_output.put_line命令不能打印布尔类型。--变量声明,使用%type属性declare  v_empno number(4);  v_empno2 emp.empno%type;  v_empno3 v_empno2%type;begin  dbms_output.put_line('Test');end;/--Table变量类型,相当于java中的数组。declare  type type_table_emp_empno is table of emp.empno%type index by binary_integer;  v_empnos type_table_emp_empno;begin  v_empnos(0) := 7369;  v_empnos(2) := 7839;  v_empnos(-1) := 9999;  dbms_output.put_line(v_empnos(-1));end;/--Table类型实例讲解使用记录类型变量只能保存一行数据,这限制了SELECT语句的返回行数,如果SELECT语句返回多行就会错。Oracle提供了另外一种自定义类型,也就是表类型,它是对记录类型的扩展,允许处理多行数据,类似于表。创建表类型的语法如下:TYPE table_name IS TABLE OF data_type [ NOT NULL ]INDEX BY BINARY_INTEGER ;语法说明如下:--table_name 创建的表类型名称。--IS TABLE 表示创建的是表类型。--data_type 可以是任何合法的PL/SQL数据类型,例如varchar2。--INDEX BY BINARY_INTEGER 指定系统创建一个主键索引,用于引用表类型变量中的特定行。使用表类型的例子: 例一:直接对表类型变量赋值declaretype my_emp is table of scott.emp%rowtypeindex by binary_integer;new_emp my_emp;beginnew_emp(1).empno:=6800;new_emp(1).ename:='tracy';new_emp(1).job:='clerk';new_emp(1).sal:=2500;new_emp(2).empno:=6900;new_emp(2).ename:='luck';new_emp(2).job:='manager';new_emp(2).sal:=4000;dbms_output.put_line(new_emp(1).empno ||','||new_emp(1).ename||','||new_emp(1).job||','||new_emp(1).sal);dbms_output.put_line(new_emp(2).empno ||','||new_emp(2).ename||','||new_emp(2).job||','||new_emp(2).sal);end;/例二:使用表类型变量的方法:变量名.方法名[索引号]declaretype my_emp is table of scott.emp%rowtypeindex by binary_integer;new_emp my_emp;beginnew_emp(1).empno:=6800;new_emp(1).ename:='tracy';new_emp(1).job:='clerk';new_emp(1).sal:=2500;new_emp(2).empno:=6900;new_emp(2).ename:='luck';new_emp(2).job:='manager';new_emp(2).sal:=4000;dbms_output.put_line(new_emp.first||','||new_emp.count||','||new_emp.last);dbms_output.put_line(new_emp(1).empno ||','||new_emp(1).ename||','||new_emp(1).job||','||new_emp(1).sal);dbms_output.put_line(new_emp(2).empno ||','||new_emp(2).ename||','||new_emp(2).job||','||new_emp(2).sal);--new_emp.delete(1);dbms_output.put_line(new_emp.next(1));end;//例三:与游标结合使用declaretype my_emp is table of scott.emp%rowtypeindex by binary_integer;new_emp my_emp;v_num number:=0;cursor cur_emp is select empno,ename,job,sal from scott.emp;beginfor v_emp in cur_emp loopv_num:=v_num+1;select * into new_emp(v_num) from scott.empwhere ename=v_emp.ename;end loop;for i in 1..new_emp.count loopdbms_output.put_line(new_emp(i).empno ||','||new_emp(i).ename||','||new_emp(i).job||','||new_emp(i).sal);end loop;end;/注意:不能直接对表变量赋值:select * into new_emp from scott.emp where deptno=30; 这种赋值方法是错的,赋值需要使用下标,如上面的的例子。--Record变量类型declare  type type_record_dept is record    (      deptno dept.deptno%type,      dname dept.dname%type,      loc dept.loc%type    );    v_temp type_record_dept;begin  v_temp.deptno := 50;  v_temp.dname := 'aaaa';  v_temp.loc := 'bj';  dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);end;/--使用%rowtype声明record变量declare  v_temp dept%rowtype;begin  v_temp.deptno := '50';  v_temp.dname := 'aaaa';  v_temp.loc := 'bj';  dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);end;/--sql语句的运用--select语句declare  v_ename emp.ename%type;  v_sal emp.sal%type;begin  select ename,sal into v_ename,v_sal from emp where empno = 7369;  dbms_output.put_line(v_ename || ' ' || v_sal);end;/--select语句declare  v_emp emp%rowtype;begin  select * into v_emp from emp where empno = 7369;  dbms_output.put_line(v_emp.ename);end;/--insert语句declare  v_deptno dept.deptno%type := 50;  v_dname dept.dname%type := 'aaaa';  v_loc dept.loc%type := 'bj';begin  insert into dept values(v_deptno,v_dname,v_loc);  commit;end;/--update语句declare  v_deptno emp.deptno%type := 50;  v_count number;begin  update emp set sal = sal/2 where deptno = v_deptno;  --select deptno into v_deptno from emp where empno = 7369;  --select count(*) into v_count from emp;  dbms_output.put_line(sql%rowcount || '条记录被影响');  commit;end;/注意:sql代表刚刚执行过的语句,rowcount代表刚执行过的语句影响的记录。--PL/SQL中执行DDL语句begin  execute immediate 'create table T (nnn 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 when循环,相当于java中的do..while循环declare  i binary_integer := 1;begin  loop    dbms_output.put_line(i);    i := i+1;    exit when (i >= 11);  end loop;end;/--while循环declare  j binary_integer := 1;begin  while j < 11 loop    dbms_output.put_line(j);    j := j+1;  end loop;end;/--for循环declare  k binary_integer := 1;begin  for k in 1..10 loop    dbms_output.put_line(k);  end loop;end;/declare  k binary_integer := 1;begin  for k in reverse 1..10 loop    --逆序    dbms_output.put_line(k);  end loop;end;/--错误处理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 others then    dbms_output.put_line('没数据');end;/declare  v_temp number(4);begin  select empno into v_temp from emp where empno = 2222;exception  when no_data_found then    dbms_output.put_line('没数据');end;/--创建错误日志create table errorlog(  id number primary key,  errcode number,  errmsg varchar2(1024),  errdate date);/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;  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.ename);  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.ename);  end loop;  close c;end;/--while循环declare  cursor c is    select * from emp;  v_emp emp%rowtype;begin  open c;  fetch c into v_emp;  while (c%found) loop    dbms_output.put_line(v_emp.ename);    fetch c into v_emp;  end loop;  close c;end;/--for循环declare  cursor c is    select * from emp;begin  for v_emp in c loop    dbms_output.put_line(v_emp.ename);  end loop;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.ename);  end loop;end;/--可更新的游标declare  cursor c  is    select * from emp2 for update;begin  for v_temp in c loop    if (v_temp.sal < 2000) then      update emp2 set sal = sal * 2 where current of c;    elsif (v_temp.sal = 5000) then      delete from emp2 where current of c;    end if;  end loop;  commit;end;/--oracle集合类型的first、next、prior、 last方法创建一个存储过程,用于产生oracle三种集合类型的数据 create or replace procedure plch_mainis TYPE NESTED_T   IS TABLE OF NUMBER;--嵌套表类型 TYPE ASSOCIATIVE_T IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; --索引表类型 TYPE VARRAYS_T   IS VARRAY(10) OF NUMBER; --变长数组类型  lst_nested nested_t:=nested_t(-2000,null,3500); lst_associative associative_t;--索引表类型的集合不能直接赋值 可以从其特性来理解(可以是稀疏排列的) lst_varrays varrays_t:=varrays_t(-2000,null,3500);begin  lst_associative(1):=-2000; lst_associative(3):=null;--注意,这里没有lst_associative数组第二个元素 lst_associative(4):=3500;  dbms_output.put_line('以下为嵌套表的输出内容:'); dbms_output.put_line('lst_nested(1)   :'||lst_nested(1)); dbms_output.put_line('lst_nested(2)   :'||lst_nested(2)); dbms_output.put_line('lst_nested(3)   :'||lst_nested(3)); dbms_output.put('lst_nested.first-1:');  dbms_output.put_line(lst_nested.first-1); dbms_output.put_line('lst_nested.first :'||lst_nested.first); dbms_output.put_line('lst_nested.prior(1):'||lst_nested.prior(1)); dbms_output.put_line('lst_nested.next(1):'||lst_nested.next(1)); dbms_output.put_line('lst_nested.next(2):'||lst_nested.next(2)); dbms_output.put_line('lst_nested.next(3):'||lst_nested.next(3)); dbms_output.put_line('lst_nested.last  :'||lst_nested.last);  dbms_output.put_line('以下为索引表的输出内容:');  dbms_output.put_line('lst_associative(1)   :'||lst_associative(1)); dbms_output.put_line('lst_associative(3)   :'||lst_associative(3)); dbms_output.put_line('lst_associative(4)   :'||lst_associative(4)); dbms_output.put('lst_associative.first-1:'); dbms_output.put_line(lst_associative.first-1); dbms_output.put_line('lst_associative.first :'||lst_associative.first); dbms_output.put_line('lst_associative.prior(1):'||lst_associative.prior(1)); dbms_output.put_line('lst_associative.next(1):'||lst_associative.next(1)); dbms_output.put_line('lst_associative.next(2):'||lst_associative.next(2)); dbms_output.put_line('lst_associative.next(4):'||lst_associative.next(4)); dbms_output.put_line('lst_associative.last  :'||lst_associative.last);  dbms_output.put_line('以下为变长数组的输出内容:'); dbms_output.put_line('lst_varrays(1)   :'||lst_varrays(1)); dbms_output.put_line('lst_varrays(2)   :'||lst_varrays(2)); dbms_output.put_line('lst_varrays(3)   :'||lst_varrays(3)); dbms_output.put('lst_varrays.first-1:'); dbms_output.put_line(lst_varrays.first-1); dbms_output.put_line('lst_varrays.first :'||lst_varrays.first); dbms_output.put_line('lst_varrays.prior(1):'||lst_varrays.prior(1)); dbms_output.put_line('lst_varrays.next(1):'||lst_varrays.next(1)); dbms_output.put_line('lst_varrays.next(2):'||lst_varrays.next(2)); dbms_output.put_line('lst_varrays.next(3):'||lst_varrays.next(3)); dbms_output.put_line('lst_varrays.last  :'||lst_varrays.last);end;/ 存储过程编译成功,以下调用并查看结果。特别注意三种数组中第一个元素的prior方法、最后一个元素的next方法都是可以执行出来的;并且索引表类型中未定义的元素序列也有prior、next方法。 SQL> exec plch_main; 以下为嵌套表的输出内容:lst_nested(1)   :-2000lst_nested(2)   :lst_nested(3)   :3500lst_nested.first-1:0lst_nested.first :1lst_nested.prior(1):lst_nested.next(1):2lst_nested.next(2):3lst_nested.next(3):lst_nested.last  :3以下为索引表的输出内容:lst_associative(1)   :-2000lst_associative(3)   :lst_associative(4)   :3500lst_associative.first-1:0lst_associative.first :1lst_associative.prior(1):lst_associative.next(1):3lst_associative.next(2):3lst_associative.next(4):lst_associative.last  :4以下为变长数组的输出内容:lst_varrays(1)   :-2000lst_varrays(2)   :lst_varrays(3)   :3500lst_varrays.first-1:0lst_varrays.first :1lst_varrays.prior(1):lst_varrays.next(1):2lst_varrays.next(2):3lst_varrays.next(3):lst_varrays.last  :3 PL/SQL procedure successfully completed .first方法是数组中第一个元素的下标值(序列值).prior(n)方法是数组中第n个元素的前一个元素的下标值(序列值)