尚学堂Oracle经验

来源：互联网发布：语音计算器软件下载编辑：程序博客网时间：2024/04/17 01:11

注: 这是个人看Oracle视频时写下的笔记, 多有错误, 望各位切勿吝惜赐教.

1. Dos下登陆超级管理员： sqlplus sys/密码 as sysdba

2.更改管理员：alter user scott account unlock;

3.数据的备份.

A导出:

Cmd下: exp (调用Oracle的程序) 导出

B create user

create user JJ (用户名) identified by Jack (密码) default tablespace users quota 10M on users

授权:

grant create session, create table, create view to JJ

3. 起别名：select ename, sal*12 anuual_sal from emp; --为sal*12起别名：anuual_sal

4.任何含有空值的表达式结果都为空值；

对于空值的处理： select ename, sal ,comm. From emp where comm. is null;(选空值)

select ename, sal ,comm. From emp where comm. is not null;(选非空值)

让空值在运算中等于0：

select ename, sal*12 + nvl(comm., 0) from emp; --若comm为空值，则comm=0

5. 转换为字符串:elect ename||sal from emp; --将ename与sal转换为字符串而其并在一起（结果如：SMITH800）

6.单引号表示字符串 'adkfjkda', 连续的两个单引号能表示为一个字符单引号；如('abc''def'

其结果为：abc'def

7.去掉重复值： select distinct deptno from emp;

去点组合后重复值： select distinct deptno, job from emp;

8. ”不等于” 不是”！=” ，是”<>”

9.in 的用法select ename, sal, comm.,from emp where sal in (800, 1500, 2000) --sal等于800，1500，2000，字符亦可

select ename, sal, comm.,from emp where sal not in (800, 1500, 2000) --sal不等于800，1500，2000，字符亦可

10.模糊查询：

Select ename, sal, comm., from emp where ename like ‘%ALL%’; --在ALL的左边或右边有多个字符的。

Select ename, sal, comm., from emp where ename like ‘-A%’; --横线代表一个字母。

11.转义字符：

Select ename, sal, comm., from emp where ename like ‘/%’; -- ‘/’转义字符

Select ename, sal, comm., from emp where ename like ‘$%’ escape ‘$’; -- ‘escape指定转义字符

12.排序

Select ename, sal, deptno from emp order by deptno asc; --默认为正排序（亦可用asc）

Select ename, sal, deptno from emp order by deptno desc; --反排序

13.截字符：

Select substr(ename , 2, 4) from emp --从第2个开始，截4个字符

14.ASCII编码的转换

Select chr(65) from dual; --65对应的字符

Select ascii(‘A’) from dual; --A对应的ASCII

15.四舍五入：

Select round(23.56) from dual; --等于24

Select round(23.56， 2) from dual; --精确到小数点后两位

Select round(23.56， -1) from dual; --等于20

16.字符格式转换：

select to_char(sal, '$99,999.9999') from emp; -- 9代表一个数字 $800.0000

select to_char(sal, '$0000,0000') from emp; -- 9代表一个数字 $ 0800.0000

select to_char(sal, 'L99,999.9999') from emp; -- 9代表一个数字￥800.0000

还有to_number , to_date等；

日期：

Select to_char(hiredate, ‘YYYY-MM-DD HH:MI:SS’) from emp;

Select to_char(sysdate, ‘YYYY-MM-DD HH:MI:SS’) from emp; --2009-09-21 01:09:18

Select to_char(sysdate, ‘YYYY-MM-DD HH:MI:SS’) from emp; ----2009-09-21 13:09:18

17. group_by:(分组输入只有

Select deptno, max(sal) from emp group by deptno; --按deptno分组，然后取sal的最大值，

Select deptno, avg(sal) from emp group by deptno; --按deptno分组，然后取sal的平均值，

Select deptno, avg(sal) from emp group by deptno,job; --按deptno, job组合分组，然后取sal的平均值，

Select deptno, avg(sal) from emp group by deptno,job; --按deptno,job组合分组，然后取sal的平均值

在group by 中， select字段若没有出现在主函数中，就必须出现在group by 中，否则出错：

Select ename, max(sal) from emp group by deptno; (错) --ename不是唯一的。

18．使用having对分组进行限制。

Select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;--按deptno分组后， --取sal平均值大于--2000的

19.过滤的顺序, 其执行的顺序也如下：

Select * from emp

where sal > 1000

group by deptno

having

order by

20.关于子连接的，要把生成的结果看做一张表：

select ename, sal from emp

join (select max(sal) max_sal, deptno from emp group by deptno) t

on (emp.sal = t.max_sal and emp.deptno = t.deptno);-- 求每一组中最多的sal，并显示enamel

21. 关于在文本编译命令： ed；

22. SQL表连接99标准

交叉连接: select ename, dname from emp cross join dept;

( == select ename, dname, grade from emp e, dept d, salgrade e

Where e.deptno = d. deptno and e.sal between a.losal and s.hisal and

Job <> ‘CLERK’;

等值连接: select ename, dname from emp join dept on (emp.deptno = dept.deptno);

== select ename, dname from emp join dept using(deptno); --不推荐

(== select ename, dname from emp, dept where emp.deptno = dept.deptno;)

左外连接: 将左边的表多余的数据拿出来 left (outer) join

右外连接: 将右边的表多余的数据拿出来right (outer) join

全外连接: 将左. 右边的表多余的数据拿出来 full join;

23. 若存在空值, 系统会认为所有的number都在这里面了

24. 数据的备份.

A导出:

Cmd下: exp (调用Oracle的程序) 导出

B create user

create user JJ (用户名) identified by Jack (密码) default tablespace users quota 10M on users

授权:

grant create session, create table, create view to JJ

Oracle中自动配好的表:

emp

Name Null? Type

----------------------------------------- -------- ------------

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

dept

Name Null? Type

----------------------------------------- -------- --------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

salgrade

Name Null? Type

----------------------------------------- -------- ----------

GRADE NUMBER

LOSAL NUMBER

HISAL NUMBER

题目:

一.求部门中哪些人的薪水最高

select ename, sal from emp

join (select max(sal) max_sal, deptno from emp group by deptno) t

on (emp.sal = t.max_sal and emp.deptno = t.deptno)

二.求部门平均薪水的等级

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 deptno, avg(grade) from

(select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal) t

Group by deptno;

四雇员中有哪些人是经理人

select ename from emp where empno in(select distinct mgr from emp);

不用组函数，求薪水的最高值（面试题）

select distinct sal from emp where sal not in

(select distinct e1.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)

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 , 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 dname, t1.deptno, grade, avg_sal from

(

select deptno, grade, avg_sal 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)

)t1

join dept on (t1.deptno = dept.deptno)

where t1.grade =

(

select min(grade) from

(

select deptno, grade, avg_sal 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 avg_sal from

(

avg(m2) avg_sal (

(select distinct empno,ename, mgr from emp) m

join emp e on ( m.empno = e.empno)

) m2

)group by deptno

九. 求比普通员工的最高薪水还要高的经理人名称

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)

)

十. 求薪水最高的前5名雇员

Oracle对象:

create table stu

(

id number(6),

--将非空列为关键字 stu_name_nn

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) unique,

--定义外键约束,外键约束必须是主键references

constraint stu_class_fk foreign key(class) references class(id),

constraint stu_id_pk primary key (id),

-- email, name组合唯一(表集约束)

constraint stu_name_class_uni unique(name, class)

);

create table stu

(

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) ,

email varchar2(50) unique,

constraint stu_class_fk foreign key(class) references class(id),

constraint stu_id_pk primary key (id),

constraint stu_name_class_uni unique(name, class)

);

create table class

(

id number(4) primary key, --定义主键

name varchar2(20) not null

)

create table class

(

id number(4) primary key,

name varchar2(20) not null

)

alter table stu add(addr varchar2(100)); --修改字段

alter table stu modify(addr varchar2(50)); --修改字段属性

alter table stu drop constraint stu_class_fk; --修改约束条件

alter table stu add constraint stu_class_fk foreign key(class) references class (id);--增加约束条件

数据字典表

select table_name from user_tables;

select view_name from user_views;

select constraint_name from user_constraints;

select index_name from user_indexs;

create index idx_stu_email on stu(email); --亦可用两个字段的组合建立索引

drop index idx_stu_email;

数据字典表的表:dictionary

create view v$_stu as select id, name, age from stu; --视图均以v$开头, 虚表,

视图: 好处 :可授权给别人查看部分数据

坏处: 增加维护开销,

故除非你确定视图有用处, 不然勿建.

视图可更新数据, 但少用.

create sequence seq; --创建序列

insert into article values( seq.nextval, 'a', 'b');

在提高效率的方法中, 建立索引是第一优先考虑的方法, 然后再想表结构的问题.

三范式:

第一范式: 每张表要有主键, 列不可分.

第二范式:　在多对多的关系中：　一个表里面有多个字段作为主键，　非主键不能部分依赖主键．要分割成为３张表．建立多张＂关系表＂

第三范式：不存在传递依赖．

PL_SQL语言

分四部分：

第一 declare

第二 begin

第三exception

set serveroutput on; --使输出流到SQL（dos）窗口中

begin

dbms_output.put_line(‘HelloWrod!’);

end;

DECLARE

变量声明

BEGIN

执行代码块

EXCEPTION

异常执行代码块

END

set serveroutput on;

begin

dbms_output.put_line('HelloWrod!');

end;

例一

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;

常用变量类型

1.binary_integer: 整数，主要用来计数而不是用来表示字段类型

2.number：数字类型

3.char：定长字符串

4.varchar2：变长字符串

5.date：日期

6. long：长字符串，最长2GB

7. boolean：布尔类型，可以取值为true、flase 和null 值 //不可打印,一定要赋初值

declare

v_temp number(1);

v_count binary_integer := 0;

v_sal number(7,2) := 4000.00;

v_date date := sysdate;

--constant == finally

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_temp);

end;

set serveroutput on;

--变量声明, 使用%type属性

declare

v_empno number(4);

v_empno2 emp.empno%type;

v_empno3 v_empno2%type;

begin

dbms_output.put_line('Test');

end;

--Table变量类型, 相当于数组

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) := 7954;

v_empnos(2) := 7932;

v_empnos(-1) := 9999;

dbms_output.put_line(v_empnos(-1));

end;

--Record变量类型, 相当于类

set serveroutput on;

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 := 'djf';

v_temp.loc := 'bj';

dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);

end;

--使用%rowtype声明record变量

set serveroutput on;

declare

v_temp dept%rowtype;

begin

v_temp.deptno := 50;

v_temp.dname := 'djf';

v_temp.loc := 'bj';

dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);

end;

在PL-SQL, select 中必须有且只有返回一条语句

set serveroutput on;

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;

declare

v_deptno emp2.depno%type = 50;

v_count number;

begin

--update emp2 set sal = sal/2 where deptno = v_deptno;

--select deptno into v_deptno from emp2 where empno = 7369; --1条记录受影响

--select count(*) into v_count from emp2; --1条记录受影响

dbms_output.put_line(sql%rowcount || '条记录受影响');

commit;

end;

DML语句:

例: update, insert, delete

DDL语句:

例: 数据定义语言

DCL语句:

例: grant

begin

execute immediate 'create table T (nnn varchar2(20))';

end;

--if语句

--取出7369的薪水, 若 <1200，则输出'low' 若 <2000,则输出 middle 若 > 2000, 则输出 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;

--循环

--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循环

用 for/in 对数组进行循环就是小菜一碟

　　public void testArrayLooping(PrintStream out) throws IOException {

　　int[] primes = new int[] { 2, 3, 5, 7, 11, 13, 17, 19, 23, 29 };

　　// Print the primes out using a for/in loop

　　for (int n : primes) {

　　out.println(n);

　　}

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;

结果:

--错误处理

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('error');

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;

--关键字SQLCODE, 错误的代码行 (都是负数)

v_errmsg := SQLERRM;

--关键字SQLERRM, 错误信息

insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg,

sysdate);

commit;

end;

--获取错误时间

select to_char(errdate, 'YYYY--MM--DD HH24:MI:SS') from errorlog;

--游标(注意，当游标找不到所指结果集时，它仍然指向上一次所得结果集）

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;

--与循环结合

--1.与do while()

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;

--2.与while()

declare

cursor c is

select * from emp;

v_emp c%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;

--3.与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)

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);

end loop;

end;

--可更新的游标

declare

cursor c

select * from emp2 for update;

--v_temp c%rowtype;

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;

--存储过程

create or replace procedure p

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;

elsif(v_emp.deptno = 20) then

update emp2 set sal = sal + 20 where current of c;

else

update emp2 set sal = sal + 50 where current of c;

end if;

end loop;

commit;

end;

--执行存储过程

--1.

exec p;

--2.

begin

end;

--带参数的存储过程procedure

create or replace procedure p

(v_a in number, v_b number, v_ret out number, v_temp in out number)

begin

if( v_a > v_b) then

v_ret := v_a;

else

v_ret := v_b;

end if;

v_temp := v_temp + 1;

end;

--调用

declare

v_a number := 3;

v_b number := 4;

v_ret number;

v_temp number := 5;

begin

p(v_a, v_b, v_ret, v_temp);

dbms_output.put_line(v_ret);

dbms_output.put_line(v_temp);

end;

--函数 function

create or replace function sal_tax

(v_sal number)

return number

begin

if(v_sal < 2000) then

return 0.10;

elsif(v_sal < 2750) then

return 0.15;

else

return 0.20;

end if;

end;

--触发器 trigger

create or replace trigger trig

after insert or delete or update on emp2 for each row

begin

if inserting then

insert into emp2_log values(USER, 'insert', sysdate);

elsif updating then

insert into emp2_log values(USER, 'update', sysdate);

elsif deleting then

insert into emp2_log values(USER, 'delete', sysdate);

end if;

end;

--副作用(少用)

create or replace trigger trig

after update on dept

for each row

begin

update emp set deptno =: NEW.deptno where deptno =: OLD:deptno;

end;

--树状结构

create table article

(

id number primary key,

cont varchar2(4000),

pid number,

isleaf number(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);

create or replace procedure p(v_pid article.pid%type, v_level binary_integer) is

cursor c is select * from article where pid = v_pid;

v_preStr varchar2(1024) := '';

begin

for i in 0..v_level loop

v_preStr := v_preStr || '****';

end loop;

for v_article in c loop

dbms_output.put_line(v_preStr || v_article.cont);

if(v_article.isleaf = 0) then

p(v_article.id, v_level + 1);

end if;

end loop;

end;

end loop;