Oracle应用实战七——多表查询+PL/SQL

1

多表查询

内连接

使用一张以上的表做查询就是多表查询

语法: SELECT {DISTINCT} *|列名.. FROM 表名 别名,表名1 别名

{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}

范例:查询员工表和部门表

select * from emp , dept

我们发现产生的记录数是56条,我们还会发现emp表是14条,dept表是4条,56正是emp表和dept表的记录数的乘积,我们称其为笛卡尔积。

如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大,对性能造成影响,想要去掉笛卡尔积我们需要关联查询。

在两张表中我们发现有一个共同的字段是deptno,deptno就是两张表的关联的字段,我们可以使用这个字段来做限制条件,两张表的关联查询字段一般是其中一张表的主键,另一张表的外键。

select * from emp,dept where emp.deptno=dept.deptno;

关联之后我们发现数据条数是14条,不在是56条。

多表查询我们可以为每一张表起一个别名

select * from emp e, dept d where e.deptno = d.deptno;

范例:查询出雇员的编号,姓名,部门的编号和名称,地址

select e.empno, e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;

范例:查询出每个员工的上级领导

分析:emp表中的mgr字段是当前雇员的上级领导的编号,所以该字段对emp表产生了自身关联,可以使用mgr字段和empno来关联

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 , --员工表 emp e2 -- 领导表 where e1.mgr=e2.empno

范例:在上一个例子的基础上查询该员工的部门名称

分析:只要在上一个例子基础上再加一张表的关联,使用deptno来做关联字段即可

select e1.empno,e1.ename,d.dname,e2.empno,e2.ename from emp e1 , --员工表 emp e2, -- 领导表 dept d where e1.mgr=e2.empno and e1.deptno=d.deptno

范例:查询出每个员工编号,姓名,部门名称,工资等级和他的上级领导的姓名,工资等级

select e.empno, e.ename, decode(s.grade, 1, '一级', 2, '二级', 3, '三级', 4, '四级', 5, '五级') grade, d.dname, e1.empno, e1.ename, decode(s1.grade, 1, '一级', 2, '二级', 3, '三级', 4, '四级', 5, '五级') grade from emp e, emp e1, dept d, salgrade s, salgrade s1 where e.mgr = e1.empno and e.deptno = d.deptno and e.sal between s.losal and s.hisal and e1.sal between s1.losal and s1.hisal

2

外连接(左右连接)

右连接

当我们在做基本连接查询的时候,查询出所有的部门下的员工,我们发现编号为40的部门下没有员工,但是要求把该部门也展示出来,我们发现上面的基本查询是办不到的

select * from emp e, dept d where e.deptno(+) = d.deptno

使用(+)表示左连接或者右连接,当(+)在左边表的关联条件字段上时是左连接,如果是在右边表的关联条件字段上就是右连接。

范例:查询出所有员工的上级领导

分析:我们发现使用我们以前的做法发现KING的上级领导没有被展示,我们需要使用左右连接把他查询出来

select e1.empno,e1.ename, e2.empno,e2.ename from emp e1, --员工表 emp e2 -- 领导表 where e1.mgr = e2.empno(+)

3

sql1999对SQL的支持

ON子句,自己编写连接条件(重要)

On相当于where

select * from emp e join dept d on d.deptno=e.deptno

左连接和右连接LEFT JOIN和RIGHT JOIN(重要)

select * from dept d left join on d.deptno=e.deptno

select * from emp e right join dept d on d.deptno=e.deptno

4

分组统计(掌握)

分组统计需要使用GROUP BY来分组

语法:

SELECT * | 列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名1 ASC | DESC,列名2 .. .ASC | DESC

范例:查询每个部门的人数

select deptno, count(1) from emp group by deptno;

范例:查询出每个部门的平均工资

select deptno, round( avg(sal) ,1) avgsal from emp group by deptno;

如果我们想查询出来部门编号,和部门下的人数

我们发现报了一个ORA-00937的错误

注意:

1. 如果使用分组函数,SQL只可以把GOURP BY分组条件字段和分组函数查询出来,不能有其他字段。

2. 如果使用分组函数,不使用GROUP BY 只可以查询出来分组函数的值

范例:按部门分组,查询出部门名称和部门的员工数量

select e.deptno, d.dname, count(1) from emp e, dept d where e.deptno = d.deptno group by e.deptno, d.dname;

范例:查询出部门人数大于5人的部门

分析:需要给count(1)加条件,此时在本查询中不能使用where,可以使用HAVING

select e.deptno, d.dname, count(1) from emp e, dept d where e.deptno = d.deptno having count(1) > 5 group by e.deptno, d.dname;

范例:查询出部门平均工资大于2000的部门

select e.deptno, d.dname, round(avg(e.sal),1) avgsal from emp e, dept d where e.deptno = d.deptno having avg(e.sal) > 2000 group by e.deptno, d.dname;

5

子查询(掌握)

1.子查询

在一个查询的内部还包括另一个查询,则此查询称为子查询。

Sql的任何位置都可以加入子查询。

范例:查询比7654工资高的雇员

分析:查询出7654员工的工资是多少,把它作为条件

select * from emp e where e.sal > (select sal from emp e where empno = 7654)

所有的子查询必须在“()”中编写

子查询在操作中有三类:

单列子查询:返回的结果是一列的一个内容

单行子查询:返回多个列,有可能是一个完整的记录

多行子查询:返回多条记录

范例:查询出比雇员7654的工资高,同时从事和7788的工作一样的员工

select *from emp ewhere e.sal > (select sal from emp e where empno = 7654) and e.job = (select job from emp e where empno = 7788)

范例:要求查询每个部门的最低工资和最低工资的雇员和部门名称

select d.dname,e.ename,e.sal from dept d,emp e,(select deptno ,min(sal) minsal from emp group by deptno) twhere d.deptno=t.deptnoand e.sal=t.minsaland t.deptno=e.deptno

在返回多条记录的子查询可以把它的结果集当做一张表,给起个别名, 如图中的a。

2.exists和not exists关键字

exists (sql 返回结果集为真)

not exists (sql 不返回结果集为真)

范例:查询出有员工的部门有哪些?

改为exists

select * from dept d where exists (select * from emp e where e.deptno=d.deptno)

范例:查询出没有员工的部门

select * from dept d where not exists (select * from emp e where e.deptno=d.deptno)

3.Insert中加入子查询(了解)

为了不破坏emp中数据的完整性,新建一个emp1表

create table emp1 as select * from emp;--emp1和emp中数据一致

语法:

Insert into 表名(列1,列2,列3,….)

Select 列1,列2,列3,….

From 表名

Where条件

注意:

1. 此时不要写values

2. 插入的列名与查询的列名要一致

4.Update与子查询

范例:给NEW YORK地区部门的员工涨100工资

update emp1set sal = sal + 100where deptno = (select deptno from dept where loc = 'NEW YORK')

5.Delete与子查询

范例:删除工资等级为4的经理人员

delete from emp1 where empno in (select e.empno from emp1 e, salgrade swhere e.sal between s.losal and s.hisaland s.grade=4and e.job=' MANAGER')

6

Rownum与分页查询(掌握)

ROWNUM:表示行号,实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。

范例:查询emp表带有rownum列

select rownum, t.* from emp t

我们可以根据rownum来取结果集的前几行,比如前5行

但是我们不能取到中间几行,因为rownum不支持大于号,只支持小于号,如果想实现我们的需求怎么办呢?答案是使用子查询,也正是oracle分页的做法。

第一种写法:

select *from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 5

第二种写法:

select * from (select rownum r ,emp.* from emp) bwhere b.r >5 and b.r <11

7

视图

视图就是封装了一条复杂查询的语句。

语法1.:CREATE VIEW 视图名称 AS 子查询

范例:建立一个视图,此视图包括了20部门的全部员工信息

create view empvd20 as select * from emp t where t.deptno = 20

视图创建完毕就可以使用视图来查询,查询出来的都是20部门的员工

语法2:CREATE OR REPLACE VIEW 视图名称 AS 子查询

如果视图已经存在我们可以使用语法2来创建视图,这样已有的视图会被覆盖。

create or replace view empvd20 as select * from emp t where t.deptno = 20

那么视图可以修改吗?

我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。

我们可以设置视图为只读。

语法3:CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY

create or replace view empvd20 as select * from emp t where t.deptno = 20 with read only

8

索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数,从而

提高数据访问性能。索引有很多种我们主要介绍常用的几种:

为什么添加了索引之后,会加快查询速度呢?

图书馆:如果杂乱地放书的话检索起来就非常困难,所以将书分类,然后再建一个箱子,箱

子里面放卡片,卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多,

这个就有点像索引。索引的好处就是提高你找到书的速度,但是正是因为你建了索引,就应该有人专门来维护索引,维护索引是要有时间精力的开销的,也就是说索引是不能乱建的,所以建索引有个原则:如果有一个字段如果不经常查询,就不要去建索引。现在把书变成我们的表,把卡片变成我们的索引,就知道为什么索引会快,为什么会有开销。

创建索引的语法:

创建索引:

单列索引

单列索引是基于单个列所建立的索引,比如:

CREATE index 索引名 on 表名(列名)

复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引,但是

要求列的组合必须不同,比如:

Create index emp_idx1 on emp(ename,job);

Create index emp_idx1 on emp(job,ename);

范例:给person表的name建立索引

create index pname_index on person(name);

范例:给person表创建一个name和gender的索引

create index pname_gender_index on person(name, gender);

索引使用原则

Ø 在大表上建立索引才有意义

Ø 在where子句后面或者是连接条件上的字段建立索引(经常使用的列才建索引)

Ø 索引的层次不要超过4层

索引原理参考:

参考网址:

http://blog.csdn.net/kennyrose/article/details/7532032

9

PL/SQL(掌握)

1.简介

什么是PL/SQL?

PL/SQL(Procedure Language/SQL)

PLSQL是Oracle对sql语言的过程化扩展,指在SQL命令语言中增加了过程处理语句(如分支、循环等),使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

范例1:为职工涨工资,每人涨10%的工资。

update emp set sal=sal*1.1

范例2:例2: 按职工的职称长工资,总裁涨1000元,经理涨800元,其他人员涨400元。

这样的需求我们就无法使用一条SQL来实现,需要借助其他程序来帮助完成,也可以使用pl/sql。

2.pl/sql程序语法

程序语法:

[declare]说明部分 (变量说明,游标申明,例外说明 〕 begin语句序列 (DML语句〕… [exception]异常处理语句 End;

3.常量和变量定义

在程序的声明阶段可以来定义常量和变量。

变量的基本类型就是ORACLE中的建表时字段的变量如char, varchar2, date, number, boolean, long

定义语法:

v_name char(15); v_sal number(9,2);

说明变量名、数据类型和长度后用分号结束说明语句。

常量定义:

married constant boolean:=true

引用变量

%type;

引用型变量,即v_name的类型与emp表中ename列的类型一样

在sql中使用into来赋值

declare v_name emp.ename%type;beginselect t.ename into v_name from emp t where t.empno = 7369;dbms_output.put_line(v_name);end;

记录型变量

%rowtype

记录变量分量的引用

v_row.ename:='ADAMS';

declarev_row emp%rowtype;beginselect * into v_row from emp t where t.empno = 7369;dbms_output.put_line(v_row.ename || ' ' || v_row.sal);end;

4.if分支

语法1:

IF 条件 THEN

语句1;

语句2;

END IF;

语法2:

IF 条件 THEN

语句序列1;

ELSE

语句序列 2;

END IF;

语法3:

IF 条件 THEN 语句;

ELSIF 条件 THEN 语句;

ELSIF 条件 THEN 语句;

ELSE 语句;

END IF;

范例1:如果从控制台输入1则输出我是1

declare pnum number := &num;begin if pnum = 1 then dbms_output.put_line('我是1'); end if;end;

范例2:如果从控制台输入1则输出我是1否则输出我不是1

declare mynum number := &num;begin if mynum = 1 then dbms_output.put_line('我是1'); else dbms_output.put_line('我不是1'); end if;end;

范例3:判断人的不同年龄段18岁以下是未成年人,18岁以上40以下是成年人,40以上是老年人

declare mynum number := &num;begin if mynum < 18 then dbms_output.put_line('未成年人'); elsif mynum >= 18 and mynum < 40 then dbms_output.put_line('中年人'); elsif mynum >= 40 then dbms_output.put_line('老年人'); end if;end;

其中语法2比较常用

语法1:

WHILE 条件 LOOP

.. .

total : = total + salary;

END LOOP;

语法2:

Loop

EXIT [when 条件];

……

End loop

语法3:

FOR I IN 1 . . 3 LOOP

语句序列 ;

END LOOP ;

范例:使用语法1输出1到10的数字

declare step number := 1;begin while step <= 10 loop dbms_output.put_line(step); step := step + 1; end loop;end;

范例:使用语法2输出1到10的数字

declare step number := 1;begin loop exit when step > 10; dbms_output.put_line(step); step := step + 1; end loop;end;

范例:使用语法3输出1到10的数字

declare step number := 1;begin for step in 1 .. 10 loop dbms_output.put_line(step); end loop;end;

原文发布于微信公众号 - Java帮帮(javahelp)

原文发表时间:2017-03-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏眯眯眼猫头鹰的小树杈

猫头鹰的深夜翻译:如何优化MYSQL查询

索引除了能够确保唯一的标记一条记录,还能是MySQL服务器更快的从数据库中获取结果。索引在排序中的作用也非常大。

922
来自专栏杨建荣的学习笔记

MySQL表连接优化的初步分析

每每一些很深刻的优化案例时,就会无比想念Oracle里的优化技巧,因为无论是从工具还是信息,都会丰富许多。

852
来自专栏沃趣科技

SQL优化案例-正确的使用索引(二)

下面sql 30秒执行出结果,查看sql谓词中有like,我们知道谓词中有这样的语句是不走索引的(为了保护客户的隐私,表名和部分列已经重命名)。

1446
来自专栏landv

一、K3 WISE 开发插件《K3 WISE常用数据表整理》

4157
来自专栏数据和云

实战演练:通过伪列、虚拟列实现SQL优化

本文是技术同仁 蔡亮 在日常工作中通过试验,总结出的一些技巧方案,供大家参考学习。在此,感谢蔡亮的供稿分享,希望大家也可以后续将学习工作中遇到的问题,解决方法分...

1293
来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

1733
来自专栏「3306 Pai」社区

NOT NULL列用IS NULL也能查到数据?

有没有觉得很奇怪,为什么查到了2条 dt 列值为 '0000-00-00 00:00:00' 的记录?

970
来自专栏程序员宝库

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下:

4433
来自专栏程序员宝库

电商系统设计之商品 (上)

电商大伙每天都在用,类似某猫,某狗等。电商系统设计看似复杂又很简单,看似简单又很复杂。本章适合初级工程师及中级工程师细看,大佬请随意。

2854
来自专栏乐沙弥的世界

Oracle 索引质量分析

      索引质量的高低对数据库整体性能有着直接的影响。良好高质量的索引使得数据库性能得以数量级别的提升,而低效冗余的索引则使得数据库性能缓慢如牛,即便是使用...

841

扫码关注云+社区

领取腾讯云代金券