[基础] 子查询的用法

Mysql子查询

 

子选择基本用法 

1、用子选择来生成一个参考值 

在这种情况下，用内层的查询语句来检索出一个数据值，然后把这个数据值用在外层查询语句的比较操作中。

 

select * from xxx where col=[any|all](select * from xxxx);

该句法可分为加关键词和不加关键词的写法，

当不加关键词的时候，子查询语句返回的是一个离散值(注意是一个)，查询语句将以子查询语句的结果作为自己where子句的条件进行查询，

该句法可以在子查询语句前加入any、all、some等关键字，此时子查询语句返回的是一组离散值。any则表示,查询语句是以子查询返回的值作为一个范围，在此值范围内进行查询，其与in关键字相像；all关键不太好了解，表示起全部匹配啥的。

 

比如说，如果要查询表中学生们在某一天的测验成绩，就应该使用一个内层查询先找到这一天的测验的事件号，然后在外层查询语句中用这个事件号在成绩表里面找到学生们的分数记录。具体语句为： 

select * from score where  

id=(select event_id from event where date='2002-03-21' and type='Q'); 

需要注意的是：在应用这种内层查询的结果主要是用来进行比较操作的分法时，内层查询应该只有一个输出结果才对。看例子，如果想知道哪个美国总统的生日最小，构造下列查询 

select * from president where birth=min(birth) 

这个查询是错的！因为MySQL不允许在子句里面使用统计函数！min()函数应该有一个确定的参数才能工作!所以我们改用子选择: 

select * from president where birht=(select min(birth) from presidnet); 

 

 

2、exists 和 not exists 子选择 

上一种用法是把查间结果由内层传向外层、本类用法则相反，把外层查询的结果传递给内层。看外部查询的结果是否满足内部查间的匹配径件。

这种“由外到内”的子迭择用法非常适合用来检索某个数据表在另外一个数据表里面有设有匹配的记录 

 

 

找两个表内都存在的数据 

select i1 from t1 where exists(select * from t2 where t1.i1=t2.i2); 

 

找t1表内存在,t2表内不存在的数据 

select i1 form t1 where not exists(select * from t2 where t1.i1=t2.i2); 

 

 

3、 in 和not in 子选择 

找两个表内都存在的数据 

select i1 from t1 where i1 in (select i2 from t2); 

 

找t1表内存在,t2表内不存在的数据 

select i1 form t1 where i1 not in (select i2 from t2);

 

 

4、select.... from (select.....) as name where ......

该句法，在平时用的比较少，而且并不好理解。其实是这么一回事，通过子查询执行的结果来构造一张新的表（这个表是一张衍生数据表，是一张虚拟的表），其用来作为主句的查询的对象，该句法功能非常强大，在一些复杂的查询中会经常用到。?

子查询虽然挺方便，但其有不少缺点，其不支持limit，而且经实验证明其执行效率相当不理想，在一般的情况下，还是不推荐是用子查询

 

 

把子选择查询改写为关联查询的方法 

1，匹配型子选择查询的改写 

下例从score数据表里面把学生们在考试事件（T）中的成绩（不包括测验成绩！）查询出来。 

Select * from score where event_id in (select event_id from event where type='T'); 

 

这个子查询可以被改写为一个简单的关联查询： 

Select score.* from score, event where score.event_id=event.event_id and event.event_id='T'; 

 

下例可以用来找出所有女学生的成绩。 

Select * from score where student_id in (select student_id form student where sex = ‘f'); 

可以把它转换成一个如下所示的关联查询： 

Select * from score,student 

Where student_id =student.student_id and student.sex ='f'; 

 

把匹配型子选择查询改写为一个关联查询是有规律可循的。下面这种形式的子选择查询： 

Select * from tablel 

Where column1 in (select column2a from table2 where column2b = value); 

可以转换为一个如下所示的关联查询： 

Select tablel. * from tablel,table2 

Where table.column1 = table2.column2a and table2.column2b = value;

 

 

非匹配(即缺失)型子选择查询的改写 

子选择查询的另一种常见用途是查找在某个数据表里有、但在另一个数据表里却没有的东西。正如前面看到的那样，这种“在某个数据表里有、在另一个数据表里没有”的说法通常都暗示着可以用一个left join 来解决这个问题。请看下面这个子选择查询，它可以把没有出现在absence数据表里的学生(也就是那些从未缺过勤的学生)给查出来： 

Select * from student 

Where student_id not in (select student_id from absence); 

 

这个子选择查询可以改写如下所示的left join 查询： 

Select student. * 

From student left join absence on student.student_id =absence.student_id 

Where absence.student_id is null; 

 

把非匹配型子选择查询改写为关联查询是有规律可循的。下面这种形式的子选择查询： 

Select * from tablel 

Where column1 not in (select column2 from table2); 

可以转换为一个如下所示的关联查询： 

Select tablel . * 

From tablel left join table2 on tablel.column1=table2.column2 

Where table2.column2 is null; 

注意：这种改写要求数据列table2.column2声明为not null。