Oracle学习笔记

Oracle SQL与PL/SQL实战

最新推荐文章于 2021-03-29 19:43:16 发布

转载最新推荐文章于 2021-03-29 19:43:16 发布 · 1.2k 阅读

文章标签：

#oracle #table #join #insert #integer #user

一、基本

(一) 三种进入客户端的方式

1. Dos命令行下：sqlplus 用户名：scott 密码：tiger,sqlplus scott/tiger

2. 开始－运行－sqlplus 图形版的SQLPLUS

3. http://localhost:5560/isqlplus

(二) 解锁

1. sqlplus sys/*** as sysdba //***为密码

2. alter user scott account unlock; //用户scott解锁

(三) 描述表

1. desc emp //描述emp表

2. dual //只有一条记录，用来计算各种表达式，显示特殊数据

EX：select sysdate from dual; //显示当前时间

(四) 基本设置

1. set linesize 200; //用于设定每行显示的宽度

2. set pagesize 30; //设置显示的页数

(五) 创建新用户

1. backup scott

exp //导出***用户至当前目录

2. 超级管理员登陆

sqlplus sys/*** as sysdba

已在SQL中：conn sys/密码 as sysdba;

删除用户:drop user *** cascade;

3. create user

create user fxw identified by ffxxww default tablespace users quota 10M on users;

4. grant

grant create session, create table, create view to fxw;

5. import the data

imp //由当前目录导入***用户

(六) 简单备份方法

create table dept 2 as select * from dept;

(七) 回退

rollback;

(八) 注释

1. 多行：/* */

2. 单行--

二、 select语句

(一) 取数据

1. select * from salgrade; //把salgrade表中的所有数据取出

2. select ename,deptno from emp; //把emp中所有数据的ename和deptno取出

(二) 取别名

1. select ename,sal*12 annual_sal from emp; //给sal*12取别名annual_sal

2. select ename,sal*12 "annual sal" from emp; //双引号使其保持原来的格式

(三) 空值的处理

1. 任何含有空值的表达式，结果都是空值

2. select ename, sal, comm from emp where comm is null;

3. select ename, sal, comm from emp where comm is not null;

(四) 字符串

1. 字符串的表示：' '

2. 字符串连接符：||

3. 两个单引号''表示一个单引号'

EX:select ename || 'asfsdf' from emp;

(五) distinct

select distinct deptno from emp; //去掉重复的deptno

(六) where

1. select * from emp where deptno = 10; //从emp中取出deptno=10的数据

2. select * from emp where empno < > 10; //<>表示不等于

3. select ename, sal from emp where sal between 800 and 1500;

等价于 select ename, sal from emp where sal >= 800 and sal <= 1500;

4. select sal from emp where sal in (800, 1500, 2000);

//取出sal 等于800或1500或2000的值

5. select ename from emp where ename like '%ALL';

//模糊查询，通配符（正则表达式）

1) _表示一个字符，%表示一个或多个字符

2) 如果本身就有%，使用转义字符

3) 修改转义字符符号

EX:select ename from emp where ename like '%$%ALL' escape '$';

(七) order by

1. select * from dept order by deptno desc;

//排序，默认升序(asc)，降序加desc

2. select * fromemp order by deptno asc, ename desc; //组合

(八) sql_function (单行函数)

1. lower

select lower(ename) from emp; //转换成小写

2. upper

select upper(ename) from emp; //转换成大写

3. substr

select substr(ename, 1, 3) from emp;

//从第一个字符开始截，一共截3个字符

4. chr

select chr(65) from dual; //ASCII码转换成字符

5. ascii

select ascii('A') from dual; //字符转换成ASCII码

6. round

1) select round(23.6552) from dual; //四舍五入

2) select round(23.6552, 2) from dual; //四舍五入到小数点后2位

7. to_char

1) select to_char(sal, '$99,999.9999') from emp;

//格式化输出数字（9代表一位数字，没有就不显示，但对于小数点后的强制显示）

2) select to_char(sal, '$00,000.0000') from emp;

//0代表一位数字，没有显示为0

3) select to_char(sal, 'L99,999.9999') from emp; //L代表本地货币

4) select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;

//格式化输出日期

5) select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

//格式化输出当前时间,24小时格式输出小时数

8. to_date

select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');

//输出1981年2月20日后入职的数据

9. to_number

select sal from emp where sal > to_number('$1,250.00', '$9,999.99');

//输出薪水大于$1,250.00的数据

10. nvl

select ename, sal*12 + nvl(comm, 0) from emp;

//如果comm的值是空值用0替代，否则直接输出

(九) group function(组函数)

1. 区别：

1) 单行函数：一条输入对应一条输出

2) 组函数:多条输入对应一条输出

2. max

select max(sal) from emp; //求最高薪水值

3. min

select min(sal) from emp; //求最低薪水值

4. avg

1) select to_char(avg(sal), '999999.99') from emp;

//求平均薪水值，并保留到小数点后两位

2) select round(avg(sal), 2) from emp;

//求平均薪水值，并四舍五入到小数点后两位

5. sum

select sum(sal) from emp; //求和

6. count

1) select count(*) from emp; //求出emp表中有多少条记录

2) select count(comm) from emp; //count不计算空值

3) select count(*) from emp where deptno = 10;

//10这个部门一共有多少人

(十) group by

1. select deptno, avg(sal) from emp group by deptno; //分组求出平均薪水

2. select deptno, avg(sal) from emp group by deptno, job; //组合分组

3. 错误：select ename, max(sal) from emp;

1) 因为max只能有一个值，但是等于max的值可能有好几个，不能匹配。

2) 应用子查询：

select ename from emp where sal = (select max(sal) from emp);

4. 规则:出现在 select 的字段。如果没有出现在组函数里必须出现在group by里

having

where是对单条语句进行过滤，对分组后的使用having

select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;

(十一) 单条select执行顺序：

select ... from... where ... group by... having ... order by...；

(十二) 子查询

在select语句中套select语句)：

select ename, sal from emp where sal > (select avg(sal) from emp);

技巧：把子查询的结果当成一张表

(十三) table connection

1. 自连接

select e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

2. 连接-SQL1992: select ename, dname from emp, dept; //笛卡尔乘积

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

//等值连接

2) select ename, grade from emp e，salgrade s where e.sal between s.losal and s.hisal; //非等值连接

3. 连接-SQL1999：

1) select ename, dname from emp cross join dept; //笛卡尔乘积

2) select ename, dname from emp join dept on (emp.deptno = dept.deptno);

//等值连接

#:另一种写法：

select ename, dname from emp join dept using(deptno);

//但不推荐，要求条件过多，如类型一致，少了不宜查错

3) select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal); //非等值连接

4. 新语法把过滤条件和连接条件分开，读起来更明确。

select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno);

5. 外连接

1) select e1.ename, e2.ename from emp e1 left outer join emp e2 on(e1.mgr = e2.empno);

//左外连接会把左边那张表的多余（不能和另一张表连接）的数据拿出来,outer可省略

2) select ename, dname from emp e right outer join dept d on (e.deptno = d.deptno); //右外连接

3) select ename, dname from emp e full outer join dept d on (e.deptno = d.deptno); //全连接

(十四) rownum

1. 默认显示：先导入的先显示

2. 伪字段：

rownum是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。

EX：select ename from emp where rownum <= 5;

3. 限制：不能接 > 号和 >= 号

4. oracle特有，其他数据库不兼容

5. 所有数据库里取第几行到第几行最麻烦：

select ename from (select rownum r, ename from emp) where r > 10;

(十五) 多表连接子查询：

1. 求部门中哪些人薪水最高：

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

2. 求部门平均薪水的等级：

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

3. 求部门平均的薪水等级：

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

4. 求哪些人是经理人：

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

5. 不准用组函数，求薪水的最高值：

select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

6. 求平均薪水最高的部门的部门编号：

select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno));

7. 求平均薪水最高的部门的部门名称：

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 deptno, avg(sal) 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 emp group by deptno));

8. 求平均薪水的等级最低的部门的部门名称：

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

);

#：简化办法-创建视图：

1) create view v$_dept_avg_sal_info as 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);

2) select dname, t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1 join dept on (t1.deptno = dept.deptno) where t1.grade = (select min(grade) from v$_dept_avg_sal_info t2);

3) 若权限不足，则：

conn sys/密码 as sysdba;

grant create table, creat view to scott;

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

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)

);

10. 球薪水最高的前5名雇员

select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5;

11. 求薪水最高的第六到第十名雇员

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;

(十六) 面试题：

1. 比较效率

select * from emp where deptno = 10 and ename like ‘%A%’;

select * from emp where ename like ‘%A%’ and deptno = 10;

第一个效率高，对数字的比较效率较高

2. 有三个表格S,C,SC

S(SNO, SNAME) 代表（学号，姓名）

C(CNO, CNAM, CTEACHER)代表（课号，课名，教师）

SC(SNO, CNO, SCGRADE)代表（学号，课号，课号成绩）

问题：

1，找出没选过“liming”老师的所有学生姓名

select sname from s join sc on (s.sno = sc.sno) join c(c.cno = sc.cno) where c.cteacher <> ‘liming’;

2，列出两门以上（含两门）不及格学生姓名及平均成绩

select sname where sno in (select sno from sc where scgrade < 60

group by sno having count(*) >= 2);

3，即学过1号课程又学过2号课程所有学生的姓名

select sname from s where sno in (select sno from sc where cno = 1 and cno in (select sno from sc where cno = 2);

三、 DML语句

(一) select

(二) insert

1. insert into dept2 values (50, ‘bill’, ‘dl’);

2. insert into dept2 (deptno, dname) values (60, ‘setve’);

3. insert into dept2 select * from dept;

#:要求，子查询拿出来的东西必须与插入对象的类型一致

(三) update

update emp2 set sal = sal * 2, ename = ename || ‘-‘ where deptno =10;

(四) delete

delete from dept where deptno = 10;

(五) 事物（transaction）控制语句

1. 开始

一个transaction起始于一条DML语句

#：一个rollback全回退

2. 结束：

1) rollback;

2) commit；

#:之后再rollback;无效

3) 执行DDL或DCL语句时自动commit

4) 正常断开连接，自动commit，非正常断开自动rollback

四、 DDL语句及数据库常用对象

(一) create

1. 创建一张表

create table stu (id number(8) 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(email, name)

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

);

create table class ( id number(4) primary key, name warchar2(20) not null);

2. constraint

1) constraint约束取名

2) 字段约束：写在字段名后限制：无法写组合约束

3) 表级约束：可组合约束

4) 五种约束

a) NOT NULL (非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值

b) CHECK (检查)--检查在约束中指定的条件是否得到了满足

c) UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的

#：两个null不认为是重复

d) PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束(语法上等于唯一+非空，逻辑上还有约束)

e) POREIGN KEY (外键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外键可以指向主键或者其他表的唯一键

#:外键约束被参考的字段必须是主键

#:有子记录则不能删除

(二) alter

1. alter table add(addr varchar2(100));

2. alter table stu drop (addr);

3. alter table stu modify (addr varchar2(150));

4. alter table stu drop constraintstu_class_fk;

5. alter table stu add constraint stu_class_fk foreignkey (class) references class(id);

(三) drop

drop table stu;

(四) 数据库常用对象

1. table

2. 数据字典表

1) dictionary

a) 存放数据字典表的表

b) desc dictionary;

c) select table_name from dictionary where table_name like ‘USER%’;

2) user_tables

a) desc user_tables;

b) select table_name from user_tables;

3) user_views

select view_name from user_views;

4) user_constraints

select constraint_name from user_constraints;

desc user_constraint;

3. index

1) create index idx_stu_email on stu(email);

2) drop index idx_stu_email;

3) select index_name from user_indexes;

4) 当加主键约束或者唯一约束，则自动建立索引

5) 一般在当访问字段量特别大，效率特别低时，建立索引，但不要轻易建立索引

6) 优点：读的时候效率更高

7) 缺点：插入时效率更低，占用空间

4. view

1) 为子查询取别名

2) 视图可以用来更新数据，但是很少用

3) 优点：简化查询，保护私有数据

4) 缺点：增加维护支出（表结构修改，视图也得改）

5. sequence

1) oracle特有

2) 用来产生一个唯一的不间断的数字序列，一般用来做主键

3) create table article (

id number,

title varchar2(1024),

cont long

);

4) create sequence seq;

5) create sequence seq start with 10 increment by 2;

6) drop sequence seq;

7) select seq.nextval from dual;

8) insert into article values(seg.nextval, ‘a’, 1234);

五、数据库设计的三范式

(一) 范式

数据库设计的规则

(二) 原则

不存在冗余数据

(三) 第一范式

要有主键，列不可分

(四) 第二范式

不能存在部分依赖：

当一张表里有多个字段作为主键时，非主键的字段不能依赖部分主键

(五) 第三范式

不能存在传递依赖：

(六) 注意

实际情况具体分析，可能会违反三范式

六、 PL_SQL

(一) oracle内部使用的编程语言

(二) EX

begin

dbms_output.put_line(‘HelloWorld!’);

end;

set serveroutput on; //显示输出

declare

v_name varchar(20); //默认v_开头

begin

v_name := ‘myname’;

dbms_output.put_line(v_name);

ends;

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

elsif

dbms_output.put_line(‘high’);

end if;

end;

(三) 变量

1. 变量声明的规则

1) 变量名不能够使用保留字，如from、select等

2) 第一个字符必须是字母

3) 变量名最多包含30个字符

4) 不要与数据库的表或者列同名

5) 每一行只能声明一个变量

2. 常用变量类型

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

2) number：数字类型

3) char：定长字符串

4) varchar2：变长字符串

5) date：日期

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

7) boolean：布尔类型，可以取值为true、false和null值 //不能打印

3. 变量声明

1) 一般

declare

v_temp number(1);

v_count binary_integer := 0;

v_sal number(7, 2) := 4000.0;

v_date date := sysdate;

v_pi constant number(3, 2) := 3.14;

v_valid bollean := false;

v_name varchar2(20) not null := ‘MyName’;

begin

dbms_output.put_line(‘t_temp valus:’ || v_temp);

end;

2) 使用%type属性

declare

v_empno number(4);

v_empno2 emp.empno%type;

v_empno3 v_empno2%type;

begin

dbms_output.put_line(‘Test’);

end;

4. 复合变量

1) table //类似于数组

declare

type type_table_emp_empno is table of emp.empno%type index by binary_integer;

begin

v_empnos(0) := 7369;

v_empnos(-1) := 9999;

dmbs_output.put_line(v_empnos(-1));

end;

2) record //类似于类

a) 一般

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

dmbs_output.put_line(v_temp.deptno || ‘ ‘ || v_temp.dname);

end;

b) 使用%rowtype属性

declare

v_temp dept%rowttype;

6. PL_SQL中的DML语句

1) select

a) 必须返回一条记录且只返回一条记录

b) into：

select ename,sal into v_ename, v_sal from emp where empno = 7369；

c) EX

declare

v_name emp.ename%type;

v_sal emp.sal%type;

begin

select ename, sal into v_name, v_sal from emp where empno = 7369;

dbms_output.put_line(v_name || ‘ ‘ || v_sal);

end;

ii.

      declare
              v_temp emp%rowtype;
      begin
              select * into v_temp from emp where empno=7369;
              dbms_output.put_line(v_temp.ename || ' ' || v_temp.eno);
      end;

d) sql%rowcount

declare
              v_deptno dept.deptno%type := 10;
              v_count number;
begin
              --update emp set sal := sal/2 where deptno=v_deptno;
              --select deptno into v_deptno from emp where deptno =   v_deptno; //1条记录被影响！

--select count(*) into v_count from emp2;

//1条记录被影响！
              select count(*) into v_count from emp;
              dbms_output.put_line(sql%rowcount || '条记录被影响！');
              commit;
end;

2) 其他与SQL中一样

7. PL_SQL中的DDL语句

在PLSQL中使用DDL语句要加上execute immediate

EX：

begin

execute immediate ‘create table T (nnn varchar2(20) default ‘’aaa’’)’;

end;

8. PL_SQL中的循环语句

1) 相当于doWhile循环

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

loop

while j < 11 loop

dbms_output.put_line(i);

j := j + 1;

end loop;

end;

3) 相当于for循环

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;

9. 错误处理

1) EX

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;

2) DBA常用记录错误的方法

create sequence seq_errorlog_id start 1 increment by 1;

create table errorlog

(

id number primary key,

errcode number,

errmsg varchar2(1024),

errdate date

);

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;

10. cursor

1) EX

a) doWhile

declare

cursor c is

selet * 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;

b) while

declare

cursor c is

selet * 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;

c) for

declare

cursor c is

selet * from emp;

begin

for v_emp in c loop

dbms_output.put_line(v_emp.ename);

end loop;

end;

带参数

declare

cursr c(v_deptno emp.deptno%type, v_job emp.job%type)

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

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;

2) 属性

a) isopen

b) notfound

c) found

d) rowcount

11. 存储过程

1) 创建

create or replace procedure p

…

2) 执行

a) 方法一

exec p;

b) 方法二

begin

end;

3) 带参数的存储过程

create or replace procedure p

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

//v_b number,不写默认是in

begin

if (v_a > v_b) then

v_ret := v_a;

else

v_ret := v_b;

end if;

v_temp := v_temp + 1;

end;

4) show error

显示错误

12. fouction

1) 创建

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;

eld if;

end;

2) 调用

select lower(ename), sal_tax(sal) from emp;

13. 触发器

create table emp2_log

(

uname varchar2(20),

action varchar2(10),

atime date

);

create or replace trigger trig

after insert or delete or update on emp2 for each row

//before

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;

2) 副作用

create or replace trigger trig

after update on dept for each row

begin

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

end;

update dept set deptno = 99 where deptno = 10;

本文来自优快云博客，转载请标明出处：http://:@blog.youkuaiyun.com/BBGASJ/archive/2009/04/10/4061612.aspx