尚学堂Oracle经验

最新推荐文章于 2019-11-25 16:46:29 发布

JJ0320

最新推荐文章于 2019-11-25 16:46:29 发布

阅读量557

点赞数 1

分类专栏： Web框架文章标签： oracle insert table class integer join

本文链接：https://blog.csdn.net/JJ0320/article/details/5562691

版权

Web框架专栏收录该内容

4 篇文章 0 订阅

订阅专栏

注: 这是个人看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;

JJ0320

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录