Oracle 多表查询

最新推荐文章于 2024-10-02 10:51:38 发布
最新推荐文章于 2024-10-02 10:51:38 发布
阅读量919 收藏 16
点赞数 11
分类专栏： 数据库 Oracle 文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ggfgfggg/article/details/138012408
版权

关联查询

一、sql:1992语法的连接

  • 语法规则
    SELECT tables1.column,tables2.column
    FROM tables1,tables2
    WHERE tables1.column1 = tables2.column2;
  • 连接的类型

等值连接：Equi join
非等值连接：Non-equi join
外连接：Outer join
自连接：Self join

注意：
1、在where子句中写入连接条件。
2、当多个表中有重名列时，必须在列的名字前加入表名作为前缀。

92语法： 数据来自于多张表。
注意： 明确引用同名的列，必须使用表名或者别名区分。

  1. 笛卡尔积
    select 字段列表 from 表1,表2,表3…
  2. 等值连接：去关系列相同的记录
    select 字段列表 from 表1,表2,表3… where 表1.列 = 表2.列 and 表1.列 = 表3.列;
  3. 非等值连接：取关系列不同的记录 != > < >= <= between and
    select 字段列表 from 表1,表2,表3… where 表1.列 != 表2.列 and 表1.列 != 表3.列;
  4. 自连接：(特殊的等值连接)列来自于同一张表，不同角度看待表
    select 字段列表 from 表1 e,表1 m where e.列1 = m.列2;
  5. 外连接：在等值的基础上，确保一张表（主表）的记录都存在从表满足则匹配，不满足补充null。
    (1)左外：主表在左边
    (2)右外：主表在右边

笛卡尔积

笛卡尔积： 当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积，关联后的总记录条数为M * N，一般不要使用。
select * from emp e,dept d;
在这里插入图片描述

等值连接

等值连接： 两个表中包含相同的列名。

  • 语法规则
    SELECT table1.column,table2.column
    FROM table1,table2
    WHERE table1.column1 = table2.column2;
  • 笛卡尔积：表*表
  • 主外键
    1、在外键表中的映射字段称为外键 Foreign key
    2、在主键表中的唯一字段称为主键 Primary key

例： 查询雇员的名称和部门的名称
select ename,dname from emp,dept where emp.deptno = dept.deptno;
在这里插入图片描述

非等值连接

非等值连接： 两个表中没有相同的列名，但是某一个列在另一张表的列的范围之中；<,>,<=,>=,!=,between,and连接时称为非等值连接。

  • 语法规则
    SELECT table1.column,table2.column
    FROM table1,table2
    WHERE table1.column1 != table2.column2;

**例：**查询雇员名称以及自己的薪水等级
select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;
在这里插入图片描述

自连接

自连接： 将一张表1当成不同的表来看待，自己关联自己。
**例：**将雇员和他经理的名称查出来。
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
在这里插入图片描述

外连接

外连接： 在等值的基础上，确保一张表（主表）的记录都存在从表满足则匹配，不满足补充null。
例： 需要将雇员表中的所有数据都进行显示。
分析：利用等值连接的话会把关联到的数据显示，没有关联到的数据不会显示，此时需要外连接。
select * from emp e,dept d where e.deptno = d.deptno; --等值连接
在这里插入图片描述
select * from emp e,dept d where e.deptno = d.deptno(+); --左外连接
在这里插入图片描述
select * from emp e,dept d where e.deptno(+) = d.deptno; --右外连接
在这里插入图片描述
我们学习完92语法可以明显发现92的表连接语法的问题，在92语法中，多张表的连接条件会放在where子句中，同时where需要对表进行过滤，因此，相当于将过滤条件和连接条件揉到一起，太乱了，因此出现了99语法。

二、sql:1999语法的连接

SQL1999修正了上述的问题，吧连接条件，过滤条件分开来，包括以下新的TABLE JOIN句法机构：

  1. CROSS JOIN
  2. NATURAL JOIN
  3. USING子句
  4. ON子句
  5. LEFT OUTER JOIN
  6. RIGHT OUTER JOIN
  7. FULL OUTER JOIN
  8. INNER JOIN

交叉连接

CROSS JOIN： 等同于92语法中的笛卡尔积。
例： select * from emp cross join dept;
在这里插入图片描述

自然连接

NATURAL JOIN： 相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接，当两张表中不具有相同的列名的时候，会进行笛卡尔积操作,自然连接跟92语法的自连接没有任何关系。
例：
select * from emp e natural join dept d;
在这里插入图片描述
select * from emp e natural join salgrade sg;
在这里插入图片描述
注意： 自然连接的结果不保留重复的属性。

USING创建连接

USING子句： 可以使用using作为连接条件。
例:
select deptno from emp e join dept d using(deptno);
在这里插入图片描述
select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;
在这里插入图片描述

ON创建连接

ON子句： 可以添加任意的连接条件。

1、自然连接的条件是基于表中所有同名列的等值连接。
2、为了设置任意的连接条件或者指定连接的列，需要使用ON子句。
3、连接条件与其它的查询条件分开书写。
4、使用ON子句使查询语句更容易理解。

例：
相当于92语法中的等值连接
select * from emp e join dept d on e.deptno = d.deptno;在这里插入图片描述
相当于92语法中的非等值连接
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
在这里插入图片描述

左外连接

LEFT OUTER JOIN： 会把左表中的全部数据正常显示，右表没有对应数据直接显示空即可。
例： select * from emp e left outer join dept d on e.deptno = d.deptno;
在这里插入图片描述

右外连接

RIGHT OUTER JOIN： 会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可。
例： select * from emp e right outer join dept d on e.deptno = d.deptno;
在这里插入图片描述

FULL OUTER JOIN

FULL OUTER JOIN： 相当于左外连接和右外连接的合集。
例： select * from emp e full outer join dept d on e.deptno = d.deptno;
在这里插入图片描述

INNER JOIN

INNER JOIN： 两张表的连接查询，只会查询出有匹配记录的数据。
例： select * from emp e inner join dept d on e.deptno = d.deptno;
在这里插入图片描述

三、子查询

概念： SQL允许多层嵌套。子查询，即嵌套在其它查询中的查询。

  • 语法规则
    SELECT select_list
    FROM table
    WHERE expr operator (SELECT select_list FROM table);

理解子查询的关键在于把子查询当作一张表来看待。 外层的语句可以把内嵌的子查询返回的结果当成一张表使用。
1、子查询要用括号括起来。
2、将子查询放在比较运算符的右边。（增强可读性）

子查询的种类

  • 按照子查询返回的记录数，子查询可以分为单行子查询和多行子查询。

在这里插入图片描述

单行子查询

  1. 子查询返回一条记录。
  2. 使用单行记录比较运算符。
OperatorMeaning
=Equal to
>Greater than
>=Greater than or equal to
<Less than
<=Less than or equal to
<>Not equal to

例： 有哪些人的薪水是在整个雇员的平均薪水之上？
1、先求平均薪水
select avg(e.sal) from emp e;
在这里插入图片描述
2、把所有人的薪水与平均薪水比较
select * from emp e where e.sal > (select avg(e.sal) from emp e);
在这里插入图片描述
注意：此处嵌套的子查询在外层查询处理之前执行。

多行子查询

  1. 子查询返回多行行记录。
  2. 使用集合比较运算符。
运算符含义
IN等于列表中的任何值
some将值与子查询返回的任意一个值进行比较
ALL比较子查询返回的每一个值
  • 在多行子查询中使用in
    例： 我们要查在雇员中有哪些人是经理人？
    1、查询所有经理人编号
    select distinct e.mgr from emp e; 在这里插入图片描述
    2、在雇员表中过滤这些编号即可
    select * from emp e where e.empno in (select distinct e.mgr from emp e);
    在这里插入图片描述
  • 在多行子查询中使用some all
    例： 找出部门编号为20的所有员工中收入最高的职员？
    1、找出部门编号为20的所有员工的收入
    select sal from emp where deptno = 20;
    在这里插入图片描述
    2、比较这些员工的工资找出最高工资的职员
    select * from emp where sal >= all(select sal from emp where deptno = 20) and deptno = 20;
    在这里插入图片描述

在From字句中使用子查询

例： 求每个部门平均薪水的等级？
1、先求出部门的平均薪水
select e.deptno,avg(e.sal) from emp e group by e.deptno;
在这里插入图片描述
2、跟薪水登记表做关联，求出平均薪水的等级
select t.deptno, sg.grade from salgrade sg join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t on t.vsal between sg.losal and sg.hisal;
在这里插入图片描述

练习

  • 题1：求平均薪水最高的部门的部门编号
    1、求部门的平均薪水
    select e.deptno,avg(e.sal) from emp e group by e.deptno;
    在这里插入图片描述
    2、求平均薪水最高的部门
    select max(t.vsal) from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t;
    在这里插入图片描述
    3、求部门编号
    select t.deptno from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t where t.vsal = (select max(t.vsal) from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t);
    在这里插入图片描述
  • 题2：求部门平均的薪水等级
    1、求部门每个人的薪水等级
    select e.deptno,sg.grade from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
    在这里插入图片描述
    2、按照部门求平均薪水等级
    select t.deptno, avg(t.grade) from (select e.deptno, sg.grade from emp e join salgrade sg on e.sal between sg.losal and sg.hisal) t group by t.deptno;
    在这里插入图片描述
  • 题3：求薪水最高的前5名雇员
    select * from (select * from emp order by sal desc) t where rownum <= 5;
    在这里插入图片描述注意：限制输出，limit是mysql中用来做限制输出的，但是oracle中不是；在Oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，但是rownum不能直接使用，需要嵌套使用。
  • 题4：求薪水最高的第6到10名雇员
    select t.,rownum from (select * from emp order by sal desc) t where rownum <= 10;
    在这里插入图片描述
    方式一：select * from (select t.    , rownum rn from (select * from emp order by sal desc) t where rownum <= 10) t where t.rn > 5 and t.rn <= 10;
    方式二：select * from (select t.*, rownum rn from (select * from emp order by sal desc) t) t where t.rn > 5 and t.rn <= 10;
    在这里插入图片描述
    注意：使用rownum的时候必须要在外层添加嵌套，此时才能将rownum作为其中的一个列，然后在进行限制输出。

四、行转列

例1：
1、建表
create table tmp(rq varchar2(10),shengfu varchar2(5));
insert into tmp values(‘2005-05-09’,‘胜’);
insert into tmp values(‘2005-05-09’,‘胜’);
insert into tmp values(‘2005-05-09’,‘负’);
insert into tmp values(‘2005-05-09’,‘负’);
insert into tmp values(‘2005-05-10’,‘胜’);
insert into tmp values(‘2005-05-10’,‘负’);
insert into tmp values(‘2005-05-10’,‘负’);
在这里插入图片描述
2、要求

日期胜负
2005-05-0922
2005-05-1012

3、解答
select rq,decode(shengfu,‘胜’,1) 胜,decode(shengfu,‘负’,1) 负 from tmp;
在这里插入图片描述
select rq,count(decode(shengfu, ‘胜’, 1)) 胜,count(decode(shengfu, ‘负’, 1)) 负 from tmp group by rq;
在这里插入图片描述
例2：
1、建表
create table STUDENT_SCORE(name VARCHAR2(20),subject VARCHAR2(20),score NUMBER(4,1));
insert into student_score (NAME, SUBJECT, SCORE) values (‘张三’, ‘语文’, 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘张三’, ‘数学’, 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘张三’, ‘英语’, 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘李四’, ‘语文’, 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘李四’, ‘数学’, 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘李四’, ‘英语’, 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘王五’, ‘语文’, 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘王五’, ‘数学’, 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘王五’, ‘英语’, 91.0);
在这里插入图片描述
2、要求

姓名语文数学英语
张三788898
李四897690
王五996691

至少四种方式解答。
3、解答
方式一：decode
select ss.name,max(decode(ss.subject, ‘语文’, ss.score)) 语文,max(decode(ss.subject, ‘数学’, ss.score)) 数学,max(decode(ss.subject, ‘英语’, ss.score)) 英语 from student_score ss group by ss.name;
在这里插入图片描述
方式二：case when
select ss.name,max(case ss.subject when ‘语文’ then ss.score end) 语文,max(case ss.subject when ‘数学’ then ss.score end) 数学,max(case ss.subject when ‘英语’ then ss.score end) 英语 from student_score ss group by ss.name;
在这里插入图片描述
方式三：join
select ss.name,ss.score 语文 from student_score ss where ss.subject = ‘语文’;
在这里插入图片描述
select ss.name,ss.score 数学 from student_score ss where ss.subject = ‘数学’;
在这里插入图片描述
select ss.name,ss.score 英语 from student_score ss where ss.subject = ‘英语’;
在这里插入图片描述
select ss01.name, ss01.score 语文, ss02.score 数学, ss03.score 英语 from (select ss.name, ss.score from student_score ss where ss.subject = ‘语文’) ss01 join (select ss.name, ss.score from student_score ss where ss.subject = ‘数学’) ss02 on ss01.name = ss02.name join (select ss.name, ss.score from student_score ss where ss.subject = ‘英语’) ss03 on ss01.name = ss03.name;
在这里插入图片描述
方式四：union all
select t.name, sum(t.语文), sum(t.数学), sum(t.英语) from (select ss01.name, ss01.score 语文, 0 数学, 0 英语 from student_score ss01 where ss01.subject = ‘语文’ union all select ss02.name, 0 语文, ss02.score 数学, 0 英语 from student_score ss02 where ss02.subject = ‘数学’ union all select ss03.name, 0 语文, 0 数学, ss03.score 英语 from student_score ss03 where ss03.subject = ‘英语’) t group by t.name;
在这里插入图片描述

m(^.^)m
关注
专栏目录
oracle多表联合查询更新,ORACLE 两表关联更新三种方式
weixin_36438124的博客
04-11 7768
不多说了，我们来做实验吧。创建如下表数据select * from t1 ;select * from t2;现需求：参照T2表，修改T1表，修改条件为两表的fname列内容一致。方式1，update常见陷阱：UPDATE T1SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)执行后T1结果如下：有一行原有值，被...
Oracle多表查询
niubaosama的博客
03-02 9668
什么是多表查询 多表查询：当查询的数据并不是来源一个表时，需要使用多表链接操作完成查询。根据不同表中的数据之间的关系查询相关联的数据 多表链接方式 内连接 连接两个表，通过相等或不等判断链接列，称为内连接。在内连接中典型的联接运算有=或<>之类的比较运算符，包括等值连接和自然连接 等值连接 非等值连接 自连接 SQL99：交叉连接（cross join） SQL99：内连接（inner join） SQL99：自然连接（natural join） 外连接 在两个表之间的连
Oracle多表查询，四种连接方式
热门推荐
ImezZ
03-22 3万+
    一、聚合函数:（都会忽略null数据）         1、常用的有5种：将字段中所有的数据聚合在一条中             1、sum(字段名)        ：求总和                 2、avg(字段名)        ：求平均值             3、max(字段名)        ：求最大值             4、min(字段名)        ：求...
oracle多表查询中的去重问题
Exception_404的博客
10-09 8351
select O.PARTY_ORG_NAME,R.ROLE_CODE,R.ROLE_NAME  from T_CCPC_DB_URP T   left join T_CCPC_DB_ORGANIZATION O on O.PARTY_ORG_ID=T.ORG_ID  left join T_EAP_SYS_ROLE R on R.ROLE_ID=T.ROLE_ID  where USER_ID=...
Oracle 多表联合查询优化
长镜头
09-19 5516
Oracle有个高速缓冲的概念，这个高速缓冲就是存放执行过的SQL语句，那oracle在执行sql语句的时候要做很多工作，例如解析sql语句，估算索引利用率，绑定变量，读取数据块等等这些操作。假设高速缓冲里已经存储了执行过的sql语句，那就直接匹配执行了，少了步骤，自然就快了，但是经过测试会发现高速缓冲只对简单的表起作用，多表的情况完全没有效果，例如在查询单表的时候那叫一个快，但...
Oracle多表关联查询
pan_junbiao的博客
03-12 8519
在实际的应用系统开发中会涉及多个数据表，每个表的信息不是独立存在的，而是若干个表之间的信息存在一定的关联，这样当用户查询某一个表的信息时，很可能需要查询关联表的信息，这就是多表关联查询。 1、内连接 内连接是一种常见的多表关联查询方式，一般使用关键字INNER JOIN来实现。其中，INNER关键字可以省略，当只使用JOIN关键字时，语句只表示内连接操作。在使用内连接查询多个表时，必须在FRO
oracle基础-多表关联查询
geminigoth的博客
07-16 5477
多表查询 关联查询 外连接 左外连接 右外连接 子查询 自然连接
Oracle 联表查询
泥巴城 的博客
07-02 7505
笔者本是个Android开发工程师，处于工作需要不得不去客串几天java后台，谢谢接口。 以下是在写接口时用到的一个功能点，叫做联表查询，虽然不是我Android端的知识，但毕竟也是自己不懂的东西，总结下来吧。 联表查询，顾名思义，指的是结合多张数据库表进行查询，取两张表的内容结合成一个视图返回。 在我的项目中，使用的是Spring Boot框架。 下面用一个例子来简单说一下联表查询怎么用...
Oracle数据库—— 多表查询
shilukun的博客
09-24 2901
Oracle数据库—— 多表查询 （一）笛卡尔问题 1、笛卡尔积会在下面条件下产生： 省略连接条件 连接条件无效 所有表中的所有行互相连接 2.为了避免笛卡尔积，可以在WHERE加入有效的连接条件 （二）Oracle连接 1、内连接 合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行 （1）等值连接 使用连接在多个表中查询数据 SELECTtable1.column, table2.column FROMtable1, table2 W...
Oracle 查询表结构
Little_fxc的博客
10-08 4646
Oracle 查询表结构。
Oracle 多表查询优化
09-06
Oracle 多表查询优化 Oracle 多表查询优化是指在 Oracle 数据库管理系统中，为了提高多表查询的效率和性能采取的一些优化策略和技术。在 Oracle 中，多表查询是指从多个表中检索数据的操作。这种操作可能会占用大量...
ORACLE多表查询优化
10-09
Oracle 多表查询优化 在 Oracle 数据库中，多表查询是一种常见的查询方式，但它也可能会对数据库性能产生影响。本文将讨论 Oracle 多表查询优化的几种方法，帮助开发者提高数据库性能。 1. 选择最有效率的表名顺序...
oracle多表查询PPT学习教案.pptx
10-07
oracle多表查询PPT学习教案.pptx
oracle 多表查询 索引,关于多表间连接查询建立索引的问题
weixin_39613188的博客
04-06 1055
解释计划获取:成本 (所有行)执行步骤:19SELECT STATEMENT18SORT (GROUP BY)17HASH JOIN1TABLE ACCESS (FULL), M_MARKET (UCADM)16HASH JOIN10HASH JOIN8VIEW, DV_ACCOUNTDETAIL (UCADM)7SORT (UNIQUE)6UNION-ALL3...
滚雪球学MySQL[7.3讲]：数据库日志与审计详解：从错误日志到审计日志的配置与使用
**My Coding Family**
10-01 1462
在上一期中，我们深入探讨了数据库的安全策略，特别是如何通过权限管理、加密和用户认证来保护数据库免受未授权的访问。然而，尽管这些预防性措施非常重要，但仅靠安全策略并不能完全规避数据库运行中的潜在问题和安全隐患。要确保数据库的长期稳定、安全运行，日志与审计成为不可或缺的工具。通过对数据库活动进行记录和审查，管理员可以及时发现并解决系统中的故障和异常行为。本期文章将深入探讨日志与审计，具体涵盖日志的类型及配置（如错误日志、慢查询日志）以及审计日志的使用与配置。
SpringBoot之Profile的两种使用方式
shg的博客
09-30 1029
通常项目开发过程中，会经历多种环境转换，典型的如开发环境(dev)、测试环境(test)和生产环境(prod)。在这三种不同的环境下，连接数据库时使用的配置信息是不同的，即三个不同环境对应三个不同的数据库。 现在的需求是：当在不同的环境下，想通过修改配置文件来连接不同的数据库。比如在开发过程中启动项目时，想连接开发环境对应的数据库，可以在配置文件中指定 environment = dev。其他环境类似，此时就需要用到Spring为我们提供的Profile功能。
SQL中基本SELECT语句及常见关键字的使用（内连接，左/右连接）
qq_43800449的博客
09-29 850
DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create 、 drop 、 alter 、create等。DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录（增删改查），并检查数据完整性，常用的语句关键字主要包括 insert 、 delete 、 update 等。
有哪些优化数据库性能的方法？如何定位慢查询？数据库性能优化全攻略：从慢查询定位到高效提升
最新发布
智通全网络工作室的博客
10-02 628
数据库性能优化是一个循序渐进的过程，从简单的索引和查询优化开始，到复杂的分区和配置调整。定位慢查询是优化过程中的关键环节，可以通过开启慢查询日志和使用EXPLAIN工具来找出性能瓶颈。希望这些技巧能帮助你优化数据库性能，提升应用的响应速度和用户体验！如果你在优化过程中遇到任何问题，或者有其他经验和技巧，欢迎在下方留言讨论！
Oracle数据库物理结构操作管理
m0_74344909的博客
09-29 417
Oracle物理结构操作管理
Oracle多表查询详解与消除笛卡尔积
"Oracle多表查询的基本概念和操作方法" 在Oracle数据库系统中，多表查询是处理复杂数据需求的关键技术。当需要从多个相关的表中提取数据时，单表查询无法满足需求，这时就需要使用多表查询。多表查询允许我们结合...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值