博客

概述

MySQL中以下几个个常用子句及其用法

1. 
   IN子句
   

2. 
   EXISTS子句
   

3. 
   ANY子句
   

4. 
   ALL子句
   

用例表

-- 创建一张用例表

drop table if exists tb_user;
create table tb_user(
id int auto_increment,
name char(10),
age int,
sex char(2),
primary key(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 auto_increment=1 collate=utf8mb4_general_ci;
-- 设置auto_increment自动增长初始值，默认1，此处无需设置
INSERT INTO `tb_user` VALUES (3, '何宇', 7, '男');
INSERT INTO `tb_user` VALUES (7, '张玉', 8, '女');
INSERT INTO `tb_user` VALUES (8, '张强', 32, '男');
INSERT INTO `tb_user` VALUES (22, '夏雪', 18, '女');
INSERT INTO `tb_user` VALUES (31, '何洁', 24, '女');
INSERT INTO `tb_user` VALUES (34, '赵晓', 21, '女');
INSERT INTO `tb_user` VALUES (35, '赵密', 34, '女');

select * from tb_user;

1.IN子句

假设IN子句所得集合A(21,22,23,24)，

则整个句子将查询所有包含在在集合A内的记录

select * from tb_user where id in (select id from tb_user where age>20 and age<25);
-- 若 in子句为null，则整个查询结果为null
select * from tb_user where id in (select id from tb_user where id<2);
-- where子句"="包含子查询，需指定limit返回一条记录
select * from tb_user where id= ( select id from tb_user where age=20 limit 1);

2. EXISTS子句

EXISTS子句根据其内查询语句的结果集空或者非空，返回个布尔值



非空EXISTS子句为true，执行查询

select * from tb_user where exists (select * from tb_user where id=7);

-- not exists 是exist的否定，若该子句中查询结果为null时，not exists判定为true，执行查询
select * from tb_user where not exists (select * from tb_user where id=100);

3. ANY子句

与some子句用法相同



假设ANY子句得到一个数据集合记为A(31,32,33,34),



则 id>ANY子句所得集合A中任意一个数即符合条件，



这样id取值(32,33,34,35,…)均符合条件

select * from tb_user where id>any ( select id from tb_user where id>30 and id<35);

-- 若any子句为null，整个查询为null
select * from tb_user where id>any ( select id from tb_user where id>1000);

select * from tb_user where id>some ( select id from tb_user where id>30 and id<35);

4. ALL子句

假设ALL子句得到一个数据集合记为A(31,32,33,34),



则 id>ALL子句中所有的元素，



这样的id取值(35,36,37,…)均符合条件

select * from tb_user where id>all (select id from tb_user where id>30 and id<35);

select * from tb_user where id>all (select id from tb_user where id>30 and id<35);

-- 若all子句为null，则会查询所有
select * from tb_user where id>all (select id from tb_user where id>1000);