oracle常用知识

 

 

**关于oracle自带的表***********************************8
emp：
empno：员工编号； ename：员工名字； job：员工工种； mgr: 上司； hiredate：入职时间；sal： 基本工资；comm：补贴；     deptno：所属部门编号；

dept：
deptno：部门编号； dname：部门名称； loc：地理位置；

salgrade：
grade： 工资等级； losal：最低限额； hisal：最高限额；

dual：
系统自带的一张空表； 可用于计算数据：select 2*3 from dual;
**sql_function1**********************************************************

select lower(ename) from emp; 取出的名字全部变成小写。
select ename from emp where lower(ename) like'_a%';取出的名字变成小写后                                                   不含字母a
select substr(ename, 2, 3) from emp;从第二个字符截，截取三个字符。

select cha(65) from dual; 将数字转化为字符（显示为a）。

select ascii('A') from dual; 将字符转化为数字。

select round(23.652) from dual; (显示24)
select round(23.652, 2) from dual; （显示23.65）
select round(23.652, -1) from dual; （显示20）

select to_char(sal, '$99,999.9999')from emp;强制转化为指定的格式。
select to_char(sal, 'L0000.0000')from emp;同上。
select to_char(hiredate, YYYY-MM-DD HH:MI:SS) from emp;对时间格式显示处                      理。
select to_char(sysdate, YYYY-MM-DD HH:MI:SS) from emp; 12进制。
select to_char(sysdate, YYYY-MM-DD HH24:MI:SS) from emp;24进制。
***********************************************************************


**sql_function2*******************************************************
select ename, hiredate from emp where hiredate >to_date('1981-2-20' 12:34:52, 'YYYY-MM-DD HH24:MI:SS'); 函数to_date将字符转化为时间格式。
select sal from emp where sal >to_number('$1,250.00', '$9,999.99');函数to_number将字符转化为数字格式，以作比较。
select ename sal*12 + nvl(comm 0) from emp;函数nvl作用为当comm为null的时候当作处理，避免了comm为null给结果带来的不便。
*************************************************************************


**group_function*******************************************************
select max(sal) from emp;输出薪水值最高的。
select min(sal) from emp;输出薪水值最低的。
select avg(sal) from emp;输出平均薪水值。
select to_char(avg(sal),'99999999.99') fromemp;按照指定格式输出平均薪水                   值。

select round(avg(sal),2) fromemp;   精确到小数点后面2位。
select sum(sal) from emp;  输出薪水值的总和。
select count(*) from emp;求出一共有多少条记录。
select count(*) from emp where deptno = 10; 求部门为10号的记录条数。
select count(ename) from emp; 求一共有几个名字。
select count(comm) from emp; 求非空comm的记录条数。
select count(deptno) from emp;
select count(distinct deptno) from emp;
*************************************************************************



**group_by*************************************************************
select deptno, avg(sal) from emp group by deptno; 将部门薪水平均分组。
select deptno, job, max(sal) from emp group by deptno; 按组合分组。
select ename, max(sal) from emp where sal = (select max(sal) fromemp);
select ename max(sal) from emp group by deptno; 这样是错误的。
select deptno max(sal) from emp group by deptno; 这样可行。
************************************************************************


**having****************************************************************
select avg(sal), deptno from emp group by deptno;
select avg(sal), deptno from emp group by deptno having avg(sal)> 2000; having是对分组进行限制。

1 selectavg(sal)       选择
2 from emp       表原
3 where sal >1200       条件过滤
4 group bydeptno       分组
5 having avg(sal) > 1500 对结果进行限制
6 order by avg(sal) desc 对产生的结果进行排序
**********************************************************************



**子查询**************************************************************
select 语句里面套另外一个select语句。
select ename, sal from emp where sal > (selectavg(sal) from emp);

select ename,sal from emp join(select max(sal) max_sal, deptno fromemp group by deptno) t on (emp.sal = t.max_sal and emp.deptno =t.deptno);


**self_table**********************************************************
自连接：select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr =e2.empno; 把一个表当成两个来使用。
***********************************************************************



**sql1999_table_connections********************************************
1999年标准：select ename, dname from emp cross join dept;
旧：
新：select ename, dname from emp join dept on (emp.deptno =deptno);
select ename, dname from emp join deptusing(deptno);(了解即可，不推荐使                       用)
select ename, grade from emp e join salgrade s on (e.sal betweens.losal and s.hisal);
左外连接：select e1.ename, e2.ename from emp e1 left join emp e2on(e1.mgr = e2.empno);
右外连接：select ename, dname from emp e right outer join dept d(e.deptno = d.deptno);
全外连接：select ename, dname from emp e full join dept d (e.deptno =d.deptno);
************************************************************************



**求部门平均薪水等级****************************************************
select deptno, avg(grade) from (select deptno, ename, grade fromemp join salgrade s on (t.avg_sal between s.losal and s.hisal)) tgroup by deptno;
*************************************************************************



**部门中那些人是经理人**************************************************
select ename from emp where empno in (select distinct mgr fromemp);
************************************************************************

**不用组函数求薪水的最高值（面试题）************************************
select distinct sal from emp where sal not in (select distincte1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
************************************************************************


**平均薪水最高的部门的编号****************************************
select deptno, avg_sal from
(select avg(sal) avg_sal , deptno from emp group by deptno)          whereavg_sal =       
(select max(avg_sal) from
       (select avg(sal) avg_sal , deptno from emp group by deptno)
)
*********************************************************************


**求平均薪水最高的部门的部门名称
select dname from dept where deptno =
(
select deptno, avg_sal from
    (selectavg(sal) avg_sal , deptno from emp group by deptno)       whereavg_sal =       
    (selectmax(avg_sal) from
      (select avg(sal) avg_sal , deptno from emp group by deptno)
    )
)
方法二：
select dname from dept where deptno =
(
select deptno, avg_sal from
    (selectavg(sal) avg_sal , deptno from emp group by deptno)       whereavg_sal =
    (selectmax(avg_sal) from
    (selectavg(sal) avg_sal, deptno from emp group by deptno)
    )
)
**********************************************************************


**求平均薪水的等级最低的部门的部门名称




**************************************


**creat new user andinsert****************************************
1--backup scott
2--create user
   create user wp identified bywp default tablespaceusers     quota 10M on users；(创建新用户)
   grant create session, createtable , create view towangpeng(赋予新用    户权限)
3--import the data

insert into dept values (50, 'game' 'bj'); 插入数据。
insert into dept (deptno, dname) values (60,'game'); 同上。
insert into dept2 select * from dept; 数据又挨着插了一遍。
rollback；回退命令。
create table dept2 as select * from dept; 备份数据。
*************************************************************************


**rownum***************************************************************
select emp, ename from emp where rownum<=5;   取前四行。
rownum只能和< 和<=使用，大于号和等于号不支持。
select ename , sal from
(select ename, sal from emp order by sal desc) where rownum<=5;


select ename, sal from
(
    selectename,sal, rownum r from
     (select ename, sal from emp order by sal desc)
   ) where r >=6andr<=10;    求薪水最高的第6到第10名雇员。
************************************************************************

**update*******************************************************
update emp2 set sal = sal*2, ename=ename||'-' where deptno = 10;更改。
delete from emp2; 删除。
****************************************************************


**创建新表与约束****************************************************
create table stu
(
id number(6) primarykey,     //主键约束，主键非空且唯一，也可以写在后面             constraintstu_id_pk primary key(id),
name varchar2(20) constraint stu_name_nn not null, //notnull指定                         必须为非空
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(email,name)   //email与name组合                      唯一
)

外键参考的值段必须是主键，加上constraint stu_class_fk foreign (class) referenceclass(id),
*************************************************************************

**alter修改表结构***************************************************
alter table stu add(addr varchar2(100)); //添加字段
alter table stu drop(asddr);
alter table stu modify(asddr varchar2(50));
alter table stu drop constraint stu_class_fk; //删除约束条件
alter table stu add constraint stu_class_fk foreign key (class)reference class (id);  //重新添加约束条件
****************************************************************

**查询系统的表**********************************************
select table_name from user_tables; //当前用户下所有的表
select view_name fromuser_views;   //当前用户下的视图
select constraint_name，table_name from user_constraints;
descdictionary     //数据字典表
******************************************************************

**索引********************************************************
create index idx_stu_email on stu(email); //创建索引
drop indexidx_stu_email;     //删除索引
select index idx_name from user_indexes;
select view_name from user_views;
desc (视图的名称)
create view v$_stu as select id, name, age fromstu;   只给予查看id，name，age的权力，保护私有数据。
*********************************************************************

**sequence序列*****************************************************
create sequenceseq;   //创建序列
select seq.nextval from dual;
insert into article values (seq.nextval, 'a', 'b');//添加数据*********************************************************************

**三范式**********************************************************
第一范式：要有主键且列不可分。
第二范式：不能存在部分依赖：非主键的字段不能依赖于组合主键的一部分。
第三范式：不能存在传递依赖。
*******************************************************************

**PL_SQL语句*************************************************************
简单小程序：
SQL> set serveroutput on;
SQL> begin
      dbms_output.put_line('HelloWorld!');
    end;
    /
HelloWorld!   （显示的结果）
运行中dcomcnfg命令是查看系统组件服务

变量申明的规则：
变量名不能使用保留字，如from，select等
第一个字符必须是字母。
变量名最多包含30个字符
不要与数据库的表或者列同名
每一行只能申明一个变量

常用变量的类型：
binary_integer: 整数，主要用来计数而不是用来表示字段类型
number：数字类型
char：定长字符串
varchar2：变长字符串
date：日期
long：长字符串，最长2G
boolean：布尔类型，可取true，false和null值。

---Table变量类型
declare
   type_table_emp_empno is table of emp.empno%type index bybinary_integer;
begin
   v_empnos(0) :=7369;
   v_empnos(2) :=7839;
   v_empnos(-1) :=9999;
  dbms_output.put_line(v_empnos(-1));
end;


---Record变量类型
declare
    v_tempdept%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;

---使用%rowtype申明record变量
declare
    v_tempdept%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;

----------------------------
declare
   v_ename emp.ename%type;
   v_sal emp.sal%type;
begin
    selectename, sal into v_ename,v_sal from emp where empno = 7369;
   ebms_output.put_line(v_ename || ' ' || v_sal);
end;
/
(显示的结果为 SMITH 800)

----------------------------
declare
    v_deptnoemp2.deptno%type :=10;
    v_countnumber;
begin
    --updateemp2 set sal = sal/2 where deptno = v_deptno;
    selectcount(*) into v_count from emp2;
   dbms_output.put_line(sql%rowcount || '条记录被影响');
   commit;
end;
--ddl语句---------------------------

begin
   execute immediate 'createtable T (nnn varchar2(20) default "aaa")';
end;
/
---------------------------------------
declare
    v_salemp.sal%type;
begin
    select salinto v_sal from emp
       where empno = 7369;
    if (v_sal< 1200) then
       dbms_output.put_line('low');
    elseif(v_sal <2000) then
       dbms_output.put_line('middle');
    else
       dbms_output.put_line('hign');
end if;
end;
/
-----------------------------------------------
declare
    v_salemp.sal%type;
begin
    select salinto v_sal from emp
       where empno =7839;
    if(v_sal< 2500) then
        update emp set sal = sal*2 where empno = 7839;
        dbms_output.put_line(sal);
    if(v_sal =2500) then
        dbms_output.put_line(sal);
    then
        update emp set sal = sal/2 where empno = 7839;
        dbms_output.put_line(sal);
    endif;
end;
--错误处理----------------------------------------------------------
create sequence seq_errorlog_id start with 1 increment by 1;

declare
    v_deptnodept.deptno%type :=10;
    v_errcodenumber;
    v_errmsgvarchar2(1024);
begin
    delete fromdept where deptno = v_deptno;
       commit;
exception
    when othersthen
       rollback;
           v_errcode := SQLCODE;
           v_errsmg :=SQLRRM;
    insert intoerrorlog values (seq_errorlog_id.nextval, v_errmsg, sysdate);
    commit;
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;
/

--使用for循环的游标
declare
   cursor c is
     select * from emp;
begin
   for v_emp in c loop
      dbms_output.put_line(v_emp.ename) (v_emp在前面已经申明)
      end loop;
end;

--带参数的游标
declare
   cursor c(v_deptnoemp.deptno%type, v_job emp.job%type)
   is
     select ename, sal from emp where deptno = v_deptno and job =v_job;
   --v_temp c%rowtype;
begin
   for v_temp in c(30, 'CLERK')loop
     dbms_output.put_line(v_temp.ename);
--存储过程
当有编译的错误时 ，用命令show error 可显示出错的地方。
create or replace procedure p
is
   cursor c is
     select * from emp2 for update;
begin
     for v_emp in c loop
        if (v_emp.deptno = 10) then
           update emp2 set sal = sal +10 where current of c;
    elseif(v_emp.deptno = 20) then
           update emp2 set sal = sal +20 where current of c;
         else
           update emp2 set sal = sal +20 where current of c;
         end if;
     end loop;
     commit;
end;
select * from emp2;
begin
    p;
end;
--带参数的存储过程
create or replace procedure p
   (v_a in number, v_b number,v_ret out number, v_temp in outnumber)   
is
.
.
.
*********************************************************************

**trigger************************************************************
创建触发器：
create or replace trigger trig
    after updateon dept
    for eachrow
begin
    update empset deptno = :NEW.deptno where deprno = :OLD.deptno;
end;
/
然后就可以这样子更改了： update emp set deptno = 99 where deptno = 10;
************************************************************************

**树状结构的存储与展示**************************************************
create table article
(
id number primary key,
cont varchar2(4000)
pid number,
isleaf number(1), --0代表非叶子节点，1代表叶子节点
alevel number(2),
);
insert into article values (1, '蚂蚁大战大象', 0, 0, 0);
insert into article values(2,'大象被打趴下了'，1，0，1)；
insert into article values(3,'蚂蚁也不好过'，2，1，2)；
insert into article values(4,'瞎说'，2，0，2)；
insert into article values(5,'没有瞎说'，4，1，3)；
insert into article values(6,'怎么可能'，1，0，1)；
insert into article values(7,'怎么没有可能'，6，1，2)；
insert into article values(8,'可能性是很大的'，6，1，2)；
insert into article values(9,'大象进医院了'，2，0，2)；
insert into article values(10,'护士是蚂蚁'，9，1，3)；
commit;