MySQL之子查询

yzm4399

已于 2022-02-28 11:41:13 修改

阅读量538

点赞数

分类专栏： mysql 文章标签： mysql

于 2022-02-28 11:38:58 首次发布

本文链接：https://blog.csdn.net/qq_43654581/article/details/123151203

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1.准备数据

create table customer (
id int not null auto_increment,
username varchar(50) not null comment '用户名',
phone char(11) not null comment '联系电话',
address varchar(200) default null comment '发货地址',
remarks text comment '备注',
money double(11,2) not null default '0.00' comment '金钱',
state tinyint not null default '1' comment '用户状态：0冻结，1正常',
register_date datetime default current_timestamp comment '注册时间',
primary key (id)
);

create table orders (
id int not null auto_increment,
cust_id int not null comment '消费者id',
pre_amount double(11,2) not null default '0.00' comment '优惠金额',
pay_amount double(11,2) not null comment '支付金额',
state tinyint not null default '0' comment '运输状态：0发货中，1运输中，2已签收',
create_date datetime default current_timestamp comment '下单时间',
primary key (id)
)ENGINE=InnoDB AUTO_INCREMENT=10001;

create table goods(
id int not null auto_increment,
name varchar(20) not null comment '商品名称',
price double(11,2) not null default '0.00' comment '商品价格',
left_num int not null default '0' comment '商品库存',
primary key (id)
)ENGINE=InnoDB AUTO_INCREMENT=101;

create table order_goods(
ord_id int not null,
goo_id int not null,
goo_num int not null default 1
);

insert into customer values
(0,"zhangsan",12363592095,"北京东城区","",50000.00,1,now()),
(0,"lisi",11783450392,"上海浦东新区","",35000.00,1,now()),
(0,"wanwu",13666664089,"广州天河区","",18000.00,1,now()),
(0,"zhaoliu",18632506489,"武汉洪山区","",40000.00,1,now()),
(0,"zhuoqi",13652024437,"深圳罗湖区","",20000.00,1,now());

在这里插入图片描述

insert into orders values
(0, 1, 120.00, 900.00, 0, now()),
(0, 1, 20.00, 100.00, 1, now()),
(0, 2, 50.00, 500.00, 2, now()),
(0, 3, 10.00, 187.00, 0, now()),
(0, 3, 110.00, 328.00, 2, now()),
(0, 3, 2.00, 18.00, 0, now()),
(0, 4, 200.00, 2190.00, 0,now());

在这里插入图片描述

insert into goods values
(0, "智能手机", 1020.00, 100),
(0, "冰箱", 520, 50),
(0, "笔记本电脑", 2390.00, 80),
(0, "一箱矿泉水", 20.00, 200),
(0, "扫地机器人", 120.00, 100),
(0, "电动剃须刀", 197.00, 120),
(0, "方便面", 5.00, 1000),
(0, "洗衣机", 418.00, 50);

在这里插入图片描述

insert into order_goods values
(10001, 101, 1),
(10002, 105, 1),
(10003, 102, 1),
(10003, 104, 1),
(10003, 107, 2),
(10004, 106, 1),
(10005, 108, 1),
(10005, 104, 1),
(10006, 107, 4),
(10007, 103, 1);

在这里插入图片描述

2.子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

比较运算符

查询金额小于平均值的客户信息

select * from customer where money < (select avg(money) from customer);

在这里插入图片描述

IN 关键字

查询订单未签收状态的客户信息

select * from customer where id in (
	select distinct os.cust_id from orders os where os.state != 2 
);

在这里插入图片描述

select * from customer where id not in (
	select distinct os.cust_id from orders os where os.state != 2 
);

在这里插入图片描述

EXISTS 关键字

子查询，它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行
有库存100的商品就查出所有商品

select * from goods where exists (
	select id from goods where left_num = 100
);

在这里插入图片描述
子查询中的条件语句一般需要带上外查询的表做关联，不然子查询的条件可能会一直为真，或者一直为假，外查询的表进行循环匹配的时候，要么全部都查询出来，要么一条也没有。
只查询购买商品数量大于2的订单信息

select * from orders os where not exists (
	select og.ord_id from order_goods og where og.ord_id = os.id and og.goo_num > 2
);

在这里插入图片描述

ANY 关键字

满足其中任意一个条件。(满足最低要求的条件)

select * from goods where price > any (select price from goods where id < 105);

等同于

select * from goods where price > (select min(price) from goods where id < 105);

在这里插入图片描述

ALL 关键字

满足所有条件。(满足最高要求的条件)

select * from goods where price > all (select price from goods where id > 105);

在这里插入图片描述

3. in 跟 exists 的比较

exists：
1.不需要关注内层子查询的返回值，只要有结果集存在则整个子查询可以看作一个条件为真的语句，不然就是一个条件为假的语句。为真时外层查询才会执行。

select * from table where exists (select id from table2 where...)
select * from table where exists (select name from table2 where...)

返回字段是id还是name并不重要。
2.需要注意子查询中的条件语句一般需要带上外查询的表做关联，不然子查询的条件可能会一直为真，或者一直为假，外查询的表进行循环匹配的时候，要么全部都查询出来，要么一条也没有。
3.外表进行循环匹配，循环执行次数为外表的记录数，每次执行都跟内表匹配一次。其中内表(子查询)可以使用索引，外查询全表扫描
假如：外表有1000条记录，内表有10000条记录，那么最多有可能遍历1000次。
伪代码

select a.* from A a where exists(select 1 from B b where a.id = b.id);

List resultSet=[];
Array A=(select * from A)

for(int i=0;i<A.length;i++) {
   //执行select 1 from B b where b.id=a.id是否有记录返回
   if(exists(A[i].id) {    
       resultSet.add(A[i]);
    }
}
return resultSet;

适用：内表数据大于外表数据

in：
1.内层子查询的返回字段必须跟接收字段对应，不然会报错。

select * from customer where id in ( select id from customer where money > 20000.00);
select * from customer where (id,username) in ( select id,username from customer where money > 20000.00);

2.内表子查询只会查询一次，然后把结果集存在临时文件中，然后再与外表进行匹配，其中外查询与子查询都可以使用索引。
假如：外表有1000条记录，内表有10000条记录，那么最多有可能遍历1000*10000次，效率差。
伪代码

select * from A where id in(select id from B);

List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);

for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;