循序渐进学Oracle之复杂查询(重点)

前言

在实际应用中经常需要执行复杂的数据统计,经常需要显示多张表的数据,故必须要高度重视复杂的select语句!

1-数据分组的重要函数: max、min、avg、sum、count

(1)查询出所有员工中最高薪水和最低薪水者及其姓名的信息.

image

image

(2)查询出所有员工的平均工资和工资总和

image

(3)计算共有多少员工

image

(4)查询出工资最高和最低的员工的名字、工作岗位

image

(5)查询出工资高于平均工资的员工信息

image

(6)查询出工资低于平均工资和入职日期在1982年12月1日之前的员工且他们的工资都上涨10%后的信息

image

2-group by 和having子句

group by用于对查询的结果分组统计;

having子句用于限制分组显示结果;

(1)查询出每个部门的平均工资和最高工资;

image

(2)查询出每个部门的每种岗位的平均工资和最低工资;

image

(3)查询出平均工资低于2000的部门号和它的平均工资.

image

重点总结:

对数据分组的总结:

1.分组函数只能出现在选择列表、having、order by子句中;

2.若在select语句中同时包含有group by、having、order by则它们的顺序是group byhavingorder by;(先分组à再筛选à最后排序)

3.在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现group by子句中,否则会出错!

范例:

image

3-多表查询(重中之重!)

多表查询是基于两个和两个以上的表或视图的查询。

在实际应用中,查询单个表可能不满足要求,(例如: 查询出sales部门位置和其员工的姓名),这种情况下需要使用到(deptno表和emp表).

(1)查询出雇员名、雇员工资和所在部门的名字(笛卡尔积);

原则:多表查询的条件是:至少不能少于 表的个数-1

范例:查询出部门名(dname),员工名(ename)和薪水(sal)的信息

image

image

(2)查询出部门号为10的部门名、员工名和工资;

分析:

a)套路:

*因为有3个列(部门号为10部门名、员工名和工资)所以先用3个?(问号)代替之;

*2张表使用别名;

*找出2张表“纽带”;

*用实际列名代替3个?号。

范例:

image

image

image

image

上面把所有部门的都查询出来,但是我们只要 “部门号为10”:

image

(3)查询出每个员工的姓名、工资及其工资的级别。

image

image

image

扩展:

(4)查询出雇员、雇员工资及所在部门的名字,并按部门排序。

image

image

自连接:

是指在同一张表的连接查询。

(5)查询出某个员工的上级领导的姓名

分析:

No.1: 先看FORD –> MGR 7566(上级领导编号) –> EMPNO 7566(雇员编号) –> 雇员名字JONES

image

No.2: 我们可以把同一张表emp表看成2张表(a1、a2)!

image

子句查询

*子查询:是指嵌入在其它sql语句中的select语句,也叫嵌套查询。

*单行子查询:是指只返回一行数据的子查询语句;

*多行子查询:是指返回多行数据的子查询。(4、5张已经是很复杂了,不建议超过它!)

(6) 单行子查询:是指只返回一行数据的子查询语句;

范例:

查询出与SMITH同一部门的所有员工。

image

(7)多行子查询:是指返回多行数据的子查询。(4、5张已经是很复杂了,不建议超过它!)

范例:

查询出与部门编号为10的工作岗位相同的雇员的名字、岗位、工资、部门号

image

(10)在多行子查询中使用all操作符

范例:all:含义是所有XXX!

查询出工资比部门编号30的所有员工的工资高的员工的姓名、工资和部门编号。

注释:查询出来的每条记录中的sal都应大于部门编号为30号中的员工薪水即可!

其实只要比最高的工资高即可)!

方法1:

image

方法2:效率高得多 比 方法1

232250651.jpg

(11)在多行子查询中使用any操作符

范例:any:含义是任意一个XXX!

查询出工资比部门编号是30的任意一个员工的工资高的员工的姓名、工资和部门编号。

注释:查询出来的工资只要比部门编号为30中任一个员工大(其实只要比最低的工资高即可),就可以了!

方法1:

image

方法2:效率高!

image

(12)多列子查询

*单行子查询是指子查询只返回单列单行数据;

*多行子查询是指返回单列多行数据,都是针对单列而言的,

*多列子查询则是指查询返回多个列数据的子查询语句。

范例:

查询出与SMITH的部门和岗位完全相同的所有雇员。

image

范例:难度 

查询出高于自己部门平均工资的员工的信息。

PS:

*给列起别名时,可以 as;

*给起别名时, as.

image

归纳:from子句中使用子查询

当在from子句中使用子查询时,该子查询会被作为一个视图来对待,故叫内嵌视图

当在from子句中使用子查询时,必须给子句查询指定别名。

(13)分页查询

Oracle分页一共有3种方式:

方法:rownum分页:

范例:

按雇员的id号升序取出 8~12这5行!

No.1步: (select * from emp )当作是内嵌视图且起个别名a1,如下图所示:

image

No.2步: 显示rownum[Oracle分配的] (注:当作公式记住!)

image

注释:

* (select * from emp) a1表示: 给(select * from emp)起个别名为a1;

* rownum rn 表示:显示rownum(行号)且别名是rn;

* a1.* 表示: 将子查询(select * from emp)取出来,再查询1次.

No.3步: 取出/筛选/截取rn列小于等于12;

image

//将(select a1.*,rownum rn from (select * from emp) a1 where rownum<=12)当作内嵌视图(from子查询),再次查询之,如下图所示:

image

归纳总结:

a. 如果指定查询列,则仅需修改最内层的子查询; (select * from emp)

范例: 查询出rn列,行号为8~12的雇员姓名、薪水的信息。

image

b.如何排序,仅需修改最内层的子查询;

范例:查询出rn列,行号为8~12的雇员姓名、薪水且按薪水的升序排列的信息。

image

范例:查询出rn列,行号为8~12的雇员姓名、薪水且按薪水的降序排列的信息。

image

c.查询出4~9

image

PS:

统计一张表(emp)总共有多少行?

image

(14)用查询结果创建新表

范例:在生产环境中,我们不能对正在运行的表作增、删、改、查,则可用此方法创建一张新表(testemp),再在testemp表做测试!

image

(15)合并查询 注:实际工作中,用得很少!绝世高手才用!查询速度非常快!

在实际应用中,为了合并多个select语句的结果,可以使用集合操作符unio、unio all、intersect、minus 。

1)union

用于取得2个结果集的并集。union会自动去掉结果中重复的行。

2)union all

用于取得2个结果集的并集。union all不会去掉结果中重复的行,而且不会排序。

3)intersect

用于取得2个结果集的交集

4)minus

用于取得2个结果集的差集。只显示在第1个集合中,而不存在第2个集合中的数据。

范例:union用于取得2个结果集的并集。union会自动去掉结果中重复的行.

image

范例:union all用于取得2个结果集的并集。union all不会去掉结果中重复的行,而且不会排序.

image

范例:intersect用于取得2个结果集的交集。

image

范例:minus用于取得2个结果集的差集。只显示在第1个集合中,而不存在第2个集合中的数据.

image

2-左、右连接

关于左、右连接指的是查询判断条件的参考方向,例如:有如下查询:

071933546.jpg

部门一共有4个,但是现在只返回3个部门的信息,缺少40部门的信息,因为在雇员表中没有一条记录是属于40部门的,故现在不会显示40部门的信息。即:现在的查询是以emp表为参考,如果现在我非要显示40部门的信息呢?就必须改变参考方向,于是左、右连接就应运而生了!

范例:

072015204.jpg

如上图所示:40部门出现了!故发现参考方向改变了,而“(+)”就用于左、右连接的更改,此种符号有以下两种使用情况:

* (+)= :放在了等号的左边,表示的是右连接;

*=(+) : 放在了等号的右边,表示的是左连接

但是没必要刻意的区分是左,还是右,只是根据查询结果而定,若所需要的数据没有显示出来,就改变连接的方向即可。

范例:查询出每个雇员的姓名和领导的姓名

072657638.jpg

由上图可知,此时的查询的结果少了1条记录,即缺少了“KING”的记录,是因为KING没有上级领导,而想解决此问题需要使用左、右连接的问题了。

072825927.jpg

注:此种符号是Oracle数据库自己独有的,其他数据库不能使用!

3-SQL1999语法:

SQL语法之中,也提供了一套用于表连接的操作SQL,格式如下:

072942498.jpg

分解上面的多个语法:

1、交叉连接(CROSS JOIN):用于产生笛卡尔积;


SQL> SELECT * FROM emp CROSS JOIN dept;

笛卡尔积本身并不是属于无用的内容,在某些情况下还是需要使用的。

2、自然连接(NATURAL JOIN):自动找到匹配的关联字段,消除笛卡尔积;

073348508.jpg

但是并不是所有的字段都是关联字段,设置关联字段需要通过约束指定。

3JOIN…USING子句:用户自己指定一个消除笛卡尔积的字段;

073453117.jpg

4JOIN…ON子句:用户自己指定一个可以消除笛卡尔积的关联条件;

073557493.jpg

5、连接方向的改变:

()连接:LEFT OUTER JOIN…ON

()连接:RIGHT OUTER JOIN…ON

()连接:FULL OUTER JOIN…ONà将两张表中没有的数据都显示出来。

范例:

073703694.jpg

没有40部门,我们换成RIGHT JOIN…ON ,如下图所示:

073949376.jpg

由上可知:40部门出现了!

Oracle之外的数据库(SQLSERVERMYSQLDB2等)都使用以上的SQL1999语法操作,故必须要会!(当然,一直使用Oracle数据库,就可以不会:))

再次强调:多表查询的性能肯定不高,而且性能一定要在大数据量的情况下,才能发现。



本文转自

beyondhedefang

 51CTO博客,原文链接:http://blog.51cto.com/beyondhdf/1324596 ,如需转载请自行联系原作者

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值