`
雪馨25
  • 浏览: 129345 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论

oracle简单学习总结(四)——高级查询

阅读更多

一、多表联合查询

通过连接可以建立多表查询,多表查询的数据可以来自多个表,但是表之间必须有适当的连接条件。。一般N个表进行连接,需要至少N-1个连接条件,才能够正确连接

二、 相等连接

两个表具有相同意义的列,建立相等连接条件。使用相等连接进行两个表的查询时,只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。

2.1 建表:

sql: CREATE TABLE bookInfortable (id int NOT NULL primary key,pid int null,name varchar(50) NULL,publishdate date NOT NULL);

(id,pid对应 bookTypetable 的id所属类型,name书名, publishdate出版日期

sql: CREATE TABLEbookTypetable (id int NOT NULL primary key,type varchar(50) NOT NULL,manager varchar(50) NOT NULL,payint NOT NULL );

(id,type类型,manager 管理员 ,)

sql: CREATE TABLEpayGradeTable (id int NOT NULL primary key,Grade int NOT NULL,LOSAL NUMBER NOT NULL,HISAL NUMBER NOT NULL);

(id,Grade 等级,LOSAL 最低标准 ,HISAL 最高标准)

2.2 相等连接

sql: SELECT a.id,a.name,a.publishdate,a.pid,b.id,b.type,b.manager FROMbookinfortable a ,booktypetable b WHERE a.pid=b.id ;

说明: 在FROM从句中依次列出两个表的名称,在表的每个列前需要添加表名,用“.”分隔,表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。如果表名很长,可以为表起一个别名,进行简化,别名跟在表名之后,用空格分隔。( bookinfortable a bookinfortable 别名 a)

2.3 相等连接附加判断条件

sql: SELECT a.id,a.name,a.publishdate,a.pid,b.id,b.type,b.manager FROMbookinfortable a ,booktypetable b WHEREa.pid=b.id and a.name != '读者' ;

三、外连接

除了显示满足相等连接条件的记录外,还显示那些不满足连接条件的行,不满足连接条件的行将显示在最后。外连操作符为(+),它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同,

sql: SELECT a.id,a.name,a.publishdate,a.pid,b.id,b.type,b.manager FROMbookinfortable a ,booktypetable b WHEREa.pid(+)=b.id ;

sql: SELECT a.id,a.name,a.publishdate,a.pid,b.id,b.type,b.manager FROMbookinfortable a ,booktypetable b WHEREa.pid=b.id(+) ;

说明: 连接 显示后表 中不满足连接条件的行; 链接 显示前表 中不满足连接条件的行

四、不等连接

sql: SELECT b.id, b.type,b.manager,b.pay,p.grade,p.losal,p.hisal FROM booktypetable b,paygradetable p WHERE b.pay BETWEEN p.losal AND p.hisal;

说明: 查询管理员的工资所在等级,通过将 管理员工资与不同的工资上下限范围相比较,取得工资的等级,并在查询结果中显示出雇员的工资等级。

五、自连接

自连接就是一个表,同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本),可以通过不同的别名区别两个相同的表。

5.1建表

sql: CREATE TABLE workertable(id int NOT NULL primary key,name varchar(50) NOT NULL,manageridint NULL);

5.2 显示雇员名称和雇员的经理名称

sql: SELECT worker.id,worker.name||' 的经理是 '||manager.name AS 雇员的经理 FROM workertable worker, workertable manager WHERE worker.managerid = manager.id order by worker.id;

说明: workertable 表分别起了两个别名worker和manager,可以想像,第一个表是雇员表,第二个表是经理表,因为经理也是雇员。然后通过worker 表的 managerid 字段同manager 表的id字段建立连接

六、统计查询

Oracle提供了一些函数来完成统计工作,这些函数称为组函数 ,组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。

函 数 说 明
AVG 求平均值
COUNT 求计数值,返回非空行数,*表示返回所有行
MAX 求最大值
MIN 求最小值
SUM 求和
STDDEV 求标准偏差,是根据差的平方根得到的
VARIANCE 求统计方差


分组函数中SUM和AVG只应用于数值型的列,MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。使用GROUP BY 从句可以对数据进行分组。所谓分组,就是按照列的相同内容,将记录划分成组,对组可以应用组函数。如果不使用分组,将对整个表或满足条件的记录应用组函数。在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT 表示对每一个非NULL值,如果存在重复值,则组函数只运算一次。如果不指明上述关键字,默认为ALL。

6.1 COUNT

sql: SELECT COUNT(*) FROM bookinfortable t;

说明: 使用组函数COUNT统计记录个数;*代表返回所有行数,否则返回非NULL行数。

sql: SELECT COUNT(pid) FROM bookinfortable t;

说明: 返回pid非空的数量

sql: SELECT COUNT( DISTINCT pid) FROM bookinfortable ;

说明: 该查询返回bookinfortable 表中不同pid个数,不包括空的。如果不加DISTINCT,则返回的是非空的个数。

6.2 AVG

sql: SELECT AVG(t.pay) FROM booktypetable t WHERE t.pay>2000;

说明: 增加了WHERE条件,WHERE条件先执行,在进行均分

6.3 MAX(),MIN()

sql: SELECT MAX(publishdate) 最大日期,MIN(publishdate) 最小日期 FROM bookinfortable;

6.4 分组统计

前提:对booktypetable增加一行相同type的数据


sql: SELECT type,SUM(pay) FROM booktypetable GROUP BY type;

说明: 按类型分组,有多少种不同类型就分多少组,并将相同组的工资进行求和。分组查询允许在查询列表中包含分组列,在查询列中,不能使用分组列以外的其他列,否则会产生错误信息。

6.5 HAVING

重点:区分 having 与 where

1、having子句与where都是设定条件的语句。

2、优先 执行顺序:where子句 >(sum,min,max,avg,count)>having子句

3、 对分组数据再次判断时要用having。如果不用这些关系就不存在使用having。直接使用where就行了。

where sql : select t.type,t.manager, max(t.pay) from booktypetable t where t.manager!='李莘' group by( t.type,t.manager ) ;


having sql: select t.type, max(t.pay) from booktypetable t group by t.type having max(t.pay)>3500;

where and having sql: select t.type,t.manager, max(t.pay) from booktypetable t where t.manager!='李莘' group by ( t.type,t.manager ) having max(t.pay)>3500;

说明: HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件,则WHERE条件在分组之前执行,HAVING条件在分组后执行。

6.6 order by 排序

sql: select t.type 类型, t.manager 管理员,t.pay 工资 from booktypetable t order by 工资;

说明: order by后面可以跟别名也可以跟数据库字段

6.7 组函数的嵌套使用

sql: select min(avg(t.pay)) from booktypetable t GROUP BY t.type;

错误: select t.type ,min(avg(t.pay)) from booktypetable t GROUP BY t.type;这个语句是错误的,因为各类型平均工资的最低值不是属于某个部门的

说明: 该查询先统计各部门的平均工资,然后求得其中的最大值。虽然在查询中有分组列,但在查询字段中不能出现分组列。

6.8 子查询

通过把一个查询的结果作为另一个查询的一部分,可以实现这样的查询功能。。子查询比主查询先执行,结果作为主查询的条件,在书写上要用圆括号扩起来,并放在比较运算符的右侧。子查询可以嵌套使用,最里层的查询最先执行。子查询可以在SELECT 、INSERT 、UPDATE 、DELETE等语句中使用。

sql: SELECT b.* FROM booktypetable b WHERE b.pay>(SELECT t.pay FROM booktypetable t WHERE t.manager='王磊');

sql: SELECT b.* FROM booktypetable b WHERE b.pay>(SELECT t.pay FROM booktypetable t WHERE t.manager='赵元') and b.type=(SELECT t.type FROM booktypetable t WHERE t.manager='赵元');

说明: 两个子查询出现在两个条件中,用AND连接表示需要同时满足。在子查询中也可以使用组函数。

sql: SELECT t.manager,t.pay FROM booktypetable t WHERE t.pay >(SELECT AVG(pay) FROM booktypetable);

6.9 多行子查询

原表中增加数据

sql: SELECT b.* FROM booktypetable b WHERE b.pay < ANY (SELECT t.pay FROM booktypetable t WHERE t.type ='杂志') AND b.type <> '杂志';

说明: 如果子查询返回多行的结果,则我们称它为多行子查询。多行子查询要使用不同的比较运算符号,它们是IN、ANY和ALL。

6.10 多列子查询

如果子查询返回多列,则对应的比较条件中也应该出现多列,这种查询称为多列子查询。

例:查询职务和部门与SCOTT相同的雇员的信息。

* 再次强调rownum的查询

6.10 集合运算

操 作 描 述
UNION 并集,合并两个操作的结果,去掉重复的部分
UNION ALL 并集,合并两个操作的结果,保留重复的部分
MINUS 差集,从前面的操作结果中去掉与后面操作结果相同的部分
INTERSECT 交集,取两个操作结果中相同的部分



分享到:
评论

相关推荐

    Oracle经典教程1——走进Oracle

    ### Oracle经典教程1——走进Oracle #### Oracle简介 Oracle是一种基于对象的关系型数据库管理系统,它由美国甲骨文公司开发并维护。Oracle数据库系统因其高度可靠性和强大的数据处理能力而闻名,广泛应用于各种...

    Java自学之路——超仔细含练习项目及源码 Java知识体系最强总结————含各个阶段的面试题

    - **简单性**:相比其他高级语言,Java语法更加简洁明了,易于学习。它去除了C++中的复杂特性,如指针、运算符重载等,这使得Java更易于理解和使用。 - **面向对象**:Java是一种完全面向对象的语言,支持封装、...

    韩顺平老师oracle视频教程听课笔记

    通过以上知识点的总结,我们可以了解到Oracle的基础安装配置以及基本命令的使用方法,并且深入理解了如何进行Oracle的用户管理和权限分配。这些知识点对于初学者来说非常重要,也是后续学习Oracle数据库管理和开发的...

    Oracle SQL高级编程(资深Oracle专家力作,OakTable团队推荐)--随书源代码

    他认为对于SQL的学习是永无止境的,相信每一个查询Oracle数据库的人都需要精通SQL语言,才能写出高效的查询。他参与本书的编写就是为了帮助别人实现这一目标。 目录 封面 -11 封底 -10 扉页 -9 版权 -8 版权声明 -7...

    linux、windows、oracle RAC 、HMC 虚拟机学习环境 无需安装

    ### Linux、Windows、Oracle RAC、HMC 虚拟机学习环境——无需安装 #### 知识点一:Linux虚拟机环境 - **Linux发行版**:此虚拟机环境预装了Red Hat Enterprise Linux(RHEL)6.5 32位版本,这是一个广泛应用于...

    MLDn学习笔记 —— JDBC

    **JDBC基础与应用** Java Database Connectivity (JDBC) 是Java平台中用于访问数据库的标准Java API,它允许Java程序与各种关系型...通过阅读《220_JDBC.pdf》这份文档,你可以更深入地学习JDBC的细节和高级特性。

    Oracle教程(三) 基础教程

    从数据库的基本概念到高级查询技术,本书为读者提供了全面的学习资源。无论你是刚刚接触Oracle的新手还是希望深入掌握Oracle技能的专业人士,都能从中受益匪浅。通过学习这些基础知识和技术,你将能够更加熟练地管理...

    dbForge Studio for Oracle.rar

    《dbForge Studio for Oracle——强大的Oracle数据库管理利器》 dbForge Studio for Oracle是一款高效、全面的Oracle数据库管理工具,专为数据库开发人员和系统管理员设计,提供了丰富的功能,旨在优化Oracle数据库...

    韩顺平+oracle+ppt.pdf

    根据给定的文件信息,我们可以总结出以下关于Oracle...以上就是根据给定文档信息所总结出来的关于Oracle的关键知识点,涵盖了从基础使用到高级特性的各个方面。这些知识点对于学习和使用Oracle数据库来说是非常重要的。

    Oracle ODI官方培训文档

    #### 四、总结 Oracle ODI 11g官方培训文档是一个非常全面的学习资源,它不仅涵盖了ODI的基础操作,还包括了大量的高级功能和应用场景。通过这份文档的学习,不仅可以掌握ODI的基本使用方法,还可以深入了解如何利用...

    Oracle.PL.SQL.Language.Pocket.Reference.4th.Edition

    通过对本书的学习,读者不仅能够掌握PL/SQL的基础语法,还能深入了解高级特性和最佳实践,从而提升在Oracle数据库环境下的开发效率和代码质量。无论对于初学者还是经验丰富的开发者来说,这本书都是不可或缺的宝贵...

    Big Data Retriever

    本节通过实际案例,让学习者掌握如何使用Oracle NoSQL Database进行数据存储和检索。 - **环境搭建**:安装配置Oracle NoSQL Database环境。 - **数据加载**:将数据加载到Oracle NoSQL Database中。 - **数据检索**...

    一步一步学RMAN

    ### 一步一步学RMAN #### 一、RMAN简介与基础知识 ...这些基础知识对于进一步深入学习RMAN的高级功能至关重要。掌握了这些基本技能后,接下来可以探索更多关于RMAN的数据备份、恢复策略以及灾难恢复等高级主题。

    jpivot学习总结.doc

    &lt;jp:mondrianQuery dataSource="" id="query01" jdbcDriver="oracle.jdbc.driver.OracleDriver" jdbcUrl="jdbc:oracle:thin:ngykt/ngyktadmin@172.16.46.241:1521:orcl10" catalogUri="/WEB-INF/queries/feeSchema....

    Java应用程序——控制台输入[归类].pdf

    Java语言的强大之处在于其丰富的类库和强大的功能,使得开发者能够高效地构建各种类型的应用,从简单的控制台应用程序到复杂的网络服务和移动应用。 控制台输入是Java应用程序中最基础的部分,特别是在教学和学习...

    Javaweb开发学习路线及Java三大框架分享?.docx

    在每个项目完成后,进行总结和思考,探究背后的原理,这将有助于深化理解,使你不再只是简单地使用框架,而是能够灵活地应用和扩展它们。通过不断实践和反思,框架的学习将变得更加容易和自然。

    SQL21简单语句1111111111111111

    经过这一周的学习,读者应该已经掌握了SQL的基础知识,包括SQL语句的基本语法、常用函数的使用、子句的运用以及表的联合和子查询等高级特性。接下来,将进一步学习数据操作、表的创建与操作、视图和索引等内容,以...

    Java基础知识总结(超详细整理)

    Java是一种广泛使用的高级编程语言,由Sun Microsystems公司(现为Oracle Corporation的一部分)于1995年发布。它以其“一次编写,到处运行”的特性而闻名,这意味着编写好的Java代码可以在任何支持Java的平台上运行...

Global site tag (gtag.js) - Google Analytics