oracle-子查询

爱被打了一巴掌 2021-09-23 06:36 749阅读 0赞

*  简单概念
 *  （下面六点是单列子查询）
 *  在where子句中使用子查询
 *  不能使用order by子句
 *  在having子句中使用子查询
 *  使用in操作符实现指定匹配查询
 *  使用any操作符实现任意匹配查询
 *  使用all操作符实现全部匹配查询
 *   
 *  实现多列子查询
 *  实现关联子查询
 *  实现嵌套子查询
 *  在update和delete语句中使用子查询

子查询可以实现从另外一个表获取数据，从而限制当前查询语句的返回结果

**一：简单概念**

//单行子查询和多行子查询
    //单行子查询：一行或者不返回
    //多行子查询：返回多行
    
    //子查询三种子类型：
    //多列子查询：返回多行
    //关联子查询：返回一列或者多列，可以使用exists和not exists操作符
    //嵌套子查询：子查询中包含有子查询
    
    //子查询注意：
    //子查询要用()括起来
    //子查询要放在比较操作符的右边
    //当子查询的返回值是一个集合不是一个值时，不能使用单行操作符，必须根据需要使用any,in,all,exists等

**二：在where子句中使用子查询**

//在where子句中使用子查询
    //operator：操作符，比如当行操作符=这些，多行操作符all这些
    select column_list from table_name where expression operator
    //condition：条件
    (select column_name from table_name where condition group by exp Having having)

**三：不能使用order by子句**

//不能使用order by子句情况：
    //在where子句中使用子查询时，子查询语句不能使用order by子句

**四：在having子句中使用子查询**

//在having子句中使用子查询
    select age,count(*) as "超过平均年龄的每个阶段的统计" from a group by age having age>(select avg(age) from a);

**五：使用in操作符实现指定匹配查询**

![20190902161807226.png][]表a

![20190902161827241.png][]表a\_sex

//使用in操作符实现指定匹配查询
    
    //使用in操作符，查询性别是男和中的人的全部信息
    select *from a where sex in (select id from a_sex where name in ('男','中'));
    
    //使用not in操作符，查询性别是女的人的全部信息
    select *from a where sex not in (select id from a_sex where name in ('男','中'));

附加：

//子查询查出是null，就是没查到东西，not in是返回全部记录
    select *from a where sex not in
    (select sex from a where id>6);

![201909031126428.png][]

//子查询查出有一列是null值，则没有记录
    select *from a where sex not in
    (select sex from a where id<6);
    //下面是子查询结果

![20190903112820553.png][]

增加一条数据

![2019090311300979.png][]

//子查询查出的结果不是null，再not in，剩下符合的记录有一列有空值，那条记录不显示
    select *from a where sex not in
    (select sex from a where id>4);

**六：使用any操作符实现任意匹配查询**

//使用any操作符实现任意匹配查询，女是2中是3，满足>2或者>3都可以
    //注意：any前要有一个单行操作符
    select *from a where sex > any (select id from a_sex where name in ('女','中'));

**七：使用all操作符实现全部匹配查询**

//使用all操作符实现全部匹配查询，女是2中是3，满足>2且>3才可以，所以这里没有数据
    //注意：all前要有一个单行操作符
    select *from a where sex > all (select id from a_sex where name in ('女','中'));

**八：实现多列子查询**

//在前面的单行子查询和多行子查询都是针对单列而言的
    //实现多列子查询：针对多列(我分为多列单行子查询和多列多行子查询)

//成对比较：要求多个列的数据必须同时匹配
    //获得以name为组(相同的为一组)中每组年龄最低的人的具体信息
    select id,name from a where (name,age) in (select name,min(age) from a group by name);

![20190902170029622.png][]

![20190902170123100.png][]子查询查询出来的

![20190902171517916.png][]

//非成对比较：通过指定连接关键字，比如and或or等，指定多个列的数据是否必须同时匹配，
    //如果使用and，表示同时匹配，如果使用or，表示不必同时匹配
    //获得以name为组(相同的为一组)中每组年龄最低的人的具体信息
    select id,name from a where name in (select name from a) and age in (select min(age) from a group by name);

![20190902171740792.png][]第二个子查询的结果

![2019090217182033.png][]第一个子查询的结果

因为使用的是and，要同时匹配，所以最终结果是：

![20190902171914804.png][]

**九：实现关联子查询**

![20190902183501631.png][]

//实现关联子查询
    //将表a设置两个别名，将一个表在逻辑上看成是两个表
    //这条语句可以理解为查age小于同为sex一组的平均age的人的信息
    select id,name,age from a a_bmo
    where age<(select avg(age) from a a_bmt where a_bmo.sex=a_bmt.sex);

理解：满足where a\_bmo.sex=a\_bmt.sex条件的

1-1,3-3以及2,4,5组合的9列，1-1平均是14.8,3-3平均是15，都不满足

select avg(age) from a a\_bmt where a\_bmo.sex=a\_bmt.sex在2,4,5三条记录上去查平均年龄

满足条件age<(2+19+8)/3，则只有2和5这两条记录，最后查出这两条记录的信息

![20190902183957449.png][]

验证上面想法（id是唯一的）：

select id,name,age from a a_bmo
    where age<=(select avg(age) from a a_bmt where a_bmo.id=a_bmt.id);

这样就只有a表的1对应b表的1，以此类推(有五组各是一条满足)

满足条件的只有一条，自然求平均就是满足条件的当条的age值，比如1,14.8/1=14.8,所以<=14.8只有一条（注意是从满足条件的那条里面选）

(1)在关联查询中使用exists操作符

//在关联查询中使用exists操作符
    //exists用来检查子查询返回的行是否存在，也可以在非关联子查询中使用
    //查询是否存在age小于10的人的具体信息
    select id,name,age from a a_bmo where exists 
    (select id from a a_bmt where a_bmo.id=a_bmt.id and a_bmt.age<10);
    
    //exists只是用来检查子查询返回的数据是否存在，可以使用1替代列，提高性能
    select id,name,age from a a_bmo where exists 
    (select 1 from a a_bmt where a_bmo.id=a_bmt.id and a_bmt.age<10);

(2)在关联查询中使用not exists操作符

//在关联查询中使用not exists操作符
    //not exists用来检查子查询返回的行是否不存在，也可以在非关联子查询中使用
    //查询是否不存在age小于10的人的具体信息
    //no exists只是用来检查子查询返回的数据是否不存在，可以使用1替代列，提高性能
    select id,name,age from a a_bmo where not exists 
    (select 1 from a a_bmt where a_bmo.id=a_bmt.id and a_bmt.age<10);

（3）exists和in比较

(1)exists

![20190903110122255.png][]比如表a

//exists和in比较
    //in是实现指定匹配，检查特定的值是否包含在值列表中，该操作符是针对特定的值
    //exists只是检查列是否存在，针对行的存在性
    //not exists和not in,如果一个值列表中包含空值，前者返回是true，后者返回false

//not exists和not in,如果一个值列表中包含空值，前者返回是true，后者返回false
    如下：原本1-1,2-2都满足子查询的条件，最终结果应该是没有记录才对，可是1是有一个空值，所以最终结果会得到它
    select id,name from a a_bmo where not exists 
    (select 1 from a a_bmt where a_bmo.sex=a_bmt.sex);

(2)in

//子查询查出是null，就是没查到东西，not in是返回全部记录
    select *from a where sex not in
    (select sex from a where id>6);

![201909031126428.png][]

//子查询查出有一列是null值，则没有记录
    select *from a where sex not in
    (select sex from a where id<6);
    //下面是子查询结果

![20190903112820553.png][]

增加一条数据

![2019090311300979.png][]

//子查询查出的结果不是null，再not in，剩下符合的记录有一列有空值，那条记录不显示
    select *from a where sex not in
    (select sex from a where id>4);

![20190903113550563.png][]

**十：实现嵌套子查询**

![20190903115050535.png][]表a

![20190903115112743.png][]表a\_sex

//求表a中年龄大于（（表a中男和女两组的各组的平均年龄）的最大值）的人的信息
    //建议不要使用嵌套子查询，嵌套层次越多查询性能越低，建议使用表连接查询
    select *from a where age>
    (select max(avg(age)) from a where sex in
    (select id from a_sex where name in('男','女'))
    group by id
    );

![20190903115155737.png][]

**十一：在update和delete语句中使用子查询**

//在update和delete语句中使用子查询
    update a set name=(select sum(age) from a) where id='3';
    delete from a where id in(select id from a where id>1);

[20190902161807226.png]: /images/20210923/aca5e792efd247b3b2773bac48be82b2.png
[20190902161827241.png]: /images/20210923/f93ce230785e4b62af0351626bb6ef6b.png
[201909031126428.png]: /images/20210923/a8976b3f882d44e39295471cd05dd6a6.png
[20190903112820553.png]: /images/20210923/b8d6cc3025314263a50b4674d4c4136b.png
[2019090311300979.png]: /images/20210923/c2ead4c0e3ee4f41843946999a254445.png
[20190902170029622.png]: /images/20210923/5f79508ef3664948b43d848786deeeb8.png
[20190902170123100.png]: /images/20210923/8be079111a8e4b30b7f60deff57a1876.png
[20190902171517916.png]: /images/20210923/45687f10087c4aa79203d870e77588dc.png
[20190902171740792.png]: /images/20210923/5ca212ac2de84eaf82146c7b9af88a0a.png
[2019090217182033.png]: /images/20210923/885c8cbbd6a04afe948436a41eff5630.png
[20190902171914804.png]: /images/20210923/6a2055194eaf4c22a52f8133c05a1201.png
[20190902183501631.png]: /images/20210923/7252efb7bf154680aa46fb148ab52699.png
[20190902183957449.png]: /images/20210923/9f26667f65244b32b756f21c5b72d74b.png
[20190903110122255.png]: /images/20210923/1a4a696c99a9463e8440d99ddadba332.png
[20190903113550563.png]: /images/20210923/8b05484348b5448795da119cc9a28561.png
[20190903115050535.png]: /images/20210923/42a149b88c104bc8973c707b3255d9c8.png
[20190903115112743.png]: /images/20210923/ec27193a362a46a0aa7fb3cff401f10e.png
[20190903115155737.png]: /images/20210923/258c495cd8ab4a71a89bd6d5e801f176.png