Oracle 多表查询

最新推荐文章于 2024-09-17 15:41:34 发布

我爱学习2333

最新推荐文章于 2024-09-17 15:41:34 发布

阅读量447

点赞数

本文链接：https://blog.csdn.net/qq_46278748/article/details/119655983

版权

多表连接查询

连接查询分两大类，一个是使用连接谓词进行连接，第二个是使用关键字 jion 进行连接

连接谓词：

格式：select <输出字段列表> from 表1 ，表2 ... where <表1.字段名1><连接谓词><表2.字段名2>

连接字段：必须是可比较的

连接谓词包括：= 、< 、 <= 、 > 、 >= 、 != 、 <> 等，当比较符是= 时，称为等值连接。当连接谓词是不等运算符时，称为不等连接

【举例】查询学生的基本信息和成绩信息。
SELECT  a.* , b.*
FROM  student a, score b
WHERE   a.id=b.student_id

自然连接：

自然连接（Natural join）是特殊的等值连接，他要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉

针对多表查询，如果引用的列为被查询的表所共有，则引用列时必须用表名来指定是来源于哪个表，指定方法为：表名.列名。反之，不一定需要表名来指定来源

【举例】查询学生的基本信息和成绩信息。
等值连接：
SELECT  *  FROM  student a, score b
WHERE  a.student_id = b.student_id
自然连接：
select * from tab_student a natural join tab_score b;

复合条件连接：

在多表连接查询时含有多个连接条件称为复合条件连接

【举例】查询学生的学号、姓名、所学课程的名称和成绩信息。
SELECT    a.student_id,student_name,course_name,grade
FROM  student a, score b, course c
WHERE  a.student_id = b.student_id
         AND  b.course_id = c.course_id

自连接：

一个表与自身进行连接称为自连接

在同一个表中查找具有相同字段值的行，则可以使用自连接。在使用自连接时需要为表指定两个别名，并且对所有引用的列均要用别名来指定其来源

【举例】查找同一课程成绩相同的学生的学号、课程号和成绩。
SELECT  a.student_id,b.student_id,a.course_id,a.grade
FROM   score  a, score  b
WHERE   a.grade=b.grade
                AND a.student_id<>b.student_id
	      AND  a.course_id=b.course_id

关键字join 连接：

以JOIN关键字来连接表的方式，增强了表的连接能力和连接的灵活性

格式：

select <输出字段列表> from 表名1<连接类型> 表名2 on <连接条件> [ <连接类型> 表名3 on <连接条件> ] .....

参数说明：

1、表名1，表名2，表名3等是用来指明需要连接的表

2、on：用来指明连接条件

3、连接类型有：内连接：inner join

外连接： outer join

外连接分为：

左外连接： left outer join

右外连接：right outer join

全外连接： full outer join

内连接：

等值连接，不等连接和自然连接属于内连接。以join 关键字来实现内连接——按照on所指定的连接条件合并两个表，返回满足条件的行

【举例】查询学生的基本信息和成绩信息
SELECT  a.* , b.*
FROM  student a INNER  JOIN  score  b
      ON  a.student_id = b.student_id

在内连接中，inner 可以省略，使用内连接后仍然可以使用where 子句对连接后的记录进行筛选

【举例】查询学号为0801101学生的基本信息和成绩信息。
SELECT  a.student_id, student_name, class_id,  
                 sex,   born_date,  address, tel, resume, 
                 course_id, grade
FROM  student a  INNER  JOIN  score  a
        ON  a.student_id=b.student_id
WHERE  a.student_id= '0801101'

外连接：

外连接返回到查询结果集合中的不仅包含符合连接条件的行，还包含左表（左外连接时），右表（右外连接时）或两个边接表（全外连接）中的所有数据行。

左外连接：

查询输出结果中显示符合条件的数据行以及左边数据表中不符合条件的数据行。

格式：from 左表名 left [ outer] join 右表名 on 连接条件

【举例】查找所有学生的学号、姓名以及他们所学的课程编号和成绩，没有课程成绩的学生信息也要显示。
SELECT  a.student_id, student_name, course_id, grade
FROM  student  a LEFT  OUTER  JOIN  score  b
      ON  a.student_id=b.student_id
Oracle简写：
SELECT  a.student_id, student_name, course_id, grade
FROM  student  a,score  b
      where  a.student_id=b.student_id(+)

右外连接：

查询输出结果中显示符合条件的数据以及右边数据表中不符合条件的数据行。

格式：from 左表名 right [ outer ] join 右表名 on 连接条件

【举例】查询学生的学号、所在的班级编号和班级名称，班级表中没有连接成功的记录也要显示。
SELECT a.student_id, class.class_id, class_name
FROM  student a RIGHT OUTER JOIN class b
            ON a.class_id=b.class_id
Oracle简写：
SELECT a.student_id, class.class_id, class_name
FROM  student a ,class b where 
            a.class_id(+)=b.class_id

全外连接：

查询输出结果中显示符合条件的数据行右边以及左边数据表中不符合条件的数据行。

格式：from 左表名 full [ outer] join 右表名 on 连接条件

【举例】查询学生的学号、所学课程名称及成绩，两表中没有连接成功的记录均要显示（采用全外连接)。
SELECT  student_id, course_name, grade
FROM  score a FULL OUTER JOIN  course b
             ON  a.course_id=b.course_id

交叉连接：

交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积

【举例】查询学生的基本信息及其成绩信息（使用交叉连接）。
SELECT  * FROM  student  CROSS  JOIN  score
SELECT  * FROM  student , score

嵌套子查询：

子查询是一个嵌套在 select ， nsert ，update 或 delete 语句中的 where 或 having 子句内，或者嵌套在其他子查询中的 select 查询

子查询可以嵌套，它能将比价复杂的查询分解为几个简单的查询。一个 select ——from ——where 语句称为一个查询块。将一个查询块嵌套在另外一个 where 子句或者 having 短语的条件中的查询称为嵌套查询

外部查询 SELECT class_name

FROM class

WHERE class_id IN

内部查询 ( SELECT class_id

（子查询） FROM student

WHERE student_name=‘张小云’

）

嵌套查询的执行顺序：

首先执行最底层的内部查询（即子查询），它的查询结果并不显示，而是传递给外层查询，用来做外部查询的查询条件，即按照由里向外的顺序执行。

可以使用子查询的地方有：

1、使用 in 或者 not in 的集合查询中

2、update ，delete 和 insert 语句中

3、使用比较运算符时

4、使用any 或 all 时

5、使用 exists 或者 not exists 引入的存在测试时

6、有表达式的地方

带谓词 in 或 not in 的子查询：

in 子查询是把子查询的结果作为外部查询的条件，判断外部查询中的某个值是否属于子查询的结果集合

格式： <表达式> [ not ] in (子查询)

【举例】查找选修了1002号课程的学生的学号、姓名和班级编号。
SELECT  student_id, student_name, class_id
FROM   student
WHERE  student_id   IN
     (SELECT  student_id  
      FROM    score
      WHERE   course_id=‘1002’)

带有比较运算符的子查询：
当子查询返回的是单值时，子查询可以由一个比较运算符（=、<、<=、>、>=、!=或<>）引入，当子查询可能返回多个值时，则把比较运算符与 all 和 any 结合使用

格式：表达式 { 比较运算符} {all 或 any} （子查询）

all：表达式要与子查询的结果集中的所有值进行比价，当表达式与每个值都满足比较关系时，才返回 true ，否则返回 false

any: 表达式只要与子查询的结果集中的任何一个值满足比较关系式，就返回 true ，否则返回 false

【举例】查询选修了1001号课程且成绩比0801103号学生1001号课程成绩高的学生的学号、课程编号和成绩。
SELECT  student_id,  course_id,  grade
FROM  score
WHERE  course_id='1001'
      AND  grade > (SELECT  grade
                    FROM  score
                    WHERE  student_id='0801103'
                            AND  course_id='1001')

insert ，delete 和 update 语句中的子查询：

子查询可以嵌套在insert ，delete ，update 语句中，把子查询的结果插入到新表中或者用来设置删除和修改记录的条件

插入操作：INSERT和SELECT语句结合起来，可以往指定的表中插入批量的记录

格式：

INSERT [INTO] <表名>[（<字段1>[，<字段2>…]）]

【举例】创建一个新的学生表 st_info，要求：包含学号、姓名和备注三个字段，然后将student表中相应的字段值插入到表st_info中，最后显示st_info表中的记录。
CREATE  TABLE  st_info
( 学号char(10)  PRIMARY KEY,  
  姓名 char(8), 
  备注 char(30)  )
--
INSERT  INTO  st_info(学号,姓名,备注)
SELECT  student_id, student_name,resume
FROM  student
--
SELECT  * FROM  st_info

SELECT [（<字段A>[，<字段B>…]）]

FROM <表名>

[WHERE<条件表达式>]

修改操作：

子查询与update 嵌套，子查询用来指定修改的条件

【举例】将08011班学生1001号课程的成绩增加5分。
 UPDATE  score
 SET  grade=grade+5
 WHERE  student_id  in
       (SELECT  student_id
        FROM student
        WHERE  class_id='08011')

删除操作：

子查询与delete 嵌套，子查询用来指定删除的条件

【举例】删除没有选修1001号课程的学生记录。
DELETE  student
WHERE  student_id  not  in
        (SELECT  student_id
         FROM  score
         WHERE         
         course_id='1001' )