求知 文章 文库 Lib 视频 iProcess 课程 角色 咨询 工具 讲座 Modeler   Code  
会员   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
     
   
分享到
循序渐进学Oracle之复杂查询
 
火龙果软件    发布于 2014-02-25
 

前言

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

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

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

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

(3)计算共有多少员工

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

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

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

2-group by 和having子句

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

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

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

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

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

重点总结:

对数据分组的总结:

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

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

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

范例:

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

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

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

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

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

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

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

分析:

a)套路:

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

*2张表使用别名;

*找出2张表“纽带”;

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

范例:

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

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

扩展:

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

自连接:

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

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

分析:

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

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

子句查询

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

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

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

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

范例:

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

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

范例:

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

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

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

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

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

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

方法1:

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

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

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

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

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

方法1:

方法2:效率高!

(12)多列子查询

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

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

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

范例:

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

范例:难度 ★★

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

PS:

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

*给表起别名时,不能加 as.

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

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

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

(13)分页查询

Oracle分页一共有3种方式:

方法:rownum分页:

范例:

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

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

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

 

注释:

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

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

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

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

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

归纳总结:

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

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

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

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

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

c.查询出4~9

PS:

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

相关文章

我们该如何设计数据库
数据库设计经验谈
数据库设计过程
数据库编程总结
 
相关文档

数据库性能调优技巧
数据库性能调整
数据库性能优化讲座
数据库系统性能调优系列
相关课程

高性能数据库设计与优化
高级数据库架构师
数据仓库和数据挖掘技术
Hadoop原理、部署与性能调优
 
分享到
 
 


MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号