子查询

子查询

作者:LAMP小白  点击:1756  发布日期:2012-10-27 00:57:00  返回列表
MYSQL支持子查询(subquery)

子查询可以返回以下不同类型的信息:
1.标量子查询将返回一个值
2.数据列子查询将返回一个由一个或多个值构成的数据列
3.数据行子查询将返回一个由一个或多个值构成的数据行
4.数据表子查询将返回一个由一个或多个数据行构成的数据表,数据行可以由一个或多个列构成

子查询结果可以用一下不同的方法进行测试
1.可以用=或<之类的比较操作符
2.可以用IN , OUT IN 操作符来测试某给定值是否包含在子查询的结果集中
3.可以用ALL ANY SOME 操作符把某给定值与子查询结果进行比较
4.可以用EXISTS NOT EXISTS 操作符来测试子查询的结果集是否为空

mysql> SELECT * FROM absence WHERE student_id IN (SELECT student_id FROM member WHERE user_name='mio');
+------------+------------+
| student_id | date |
+------------+------------+
| 1 | 2012-10-01 |
| 1 | 2012-10-02 |
| 1 | 2012-10-03 |
| 1 | 2012-10-04 |
+------------+------------+
4 rows in set (0.01 sec)

子查询与关系比较操作符

= <> > >= < <= 操作符用来进行相对值比较,他们可以和标量子查询配合使用,用子查询返回值来构造一个检索条件,再由主查询根据该条件

做进一步检索


mysql> SELECT * FROM absence WHERE student_id = (SELECT student_id FROM member WHERE user_name='mio' AND date='2012-10-

01');
+------------+------------+
| student_id | date |
+------------+------------+
| 1 | 2012-10-01 |
+------------+------------+
1 row in set (0.00 sec)

这种形式的语句有一个共同点,子查询的前面有一个值和一个相对比较操作符,子查询的这种用法要求他只返回一个值,也就是说如果返回了多

个值,那么这个查询就杯具了

如果遇到需要在WHERE中使用聚合函数,那么可以使用子查询解决

mysql> SELECT * FROM absence WHERE date = (SELECT MIN(date) FROM absence);
+------------+------------+
| student_id | date |
+------------+------------+
| 1 | 2012-10-01 |
| 2 | 2012-10-01 |
| 3 | 2012-10-01 |
| 4 | 2012-10-01 |
+------------+------------+
4 rows in set (0.00 sec)

如果子查询返回的是一个数据行,那么可以用一个数据行构造器把一组值(一条临时创建的记录)与子查询的结构进行比较

mysql> SELECT * FROM president WHERE (city,state) = (SELECT city, state FROM president WHERE last_name='mio');
+-----------+------------+--------+------+-------+------------+-------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+-------+
| mio | haha | a | CN | 16 | 2012-10-01 | NULL |
+-----------+------------+--------+------+-------+------------+-------+
1 row in set (0.01 sec)

IN 和 NOT IN 子查询

如果查询将返回多个数据行,我们可以用IN NOT IN来进行判断


mysql> SELECT * FROM president WHERE city IN (SELECT city FROM president WHERE suffix IS null);
+-----------+------------+--------+--------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+--------+-------+------------+------------+
| KAGAMI | MINAMI | NULL | TOYOTA | 15 | 2012-10-01 | NULL |
| YOSITA | KONA | NULL | JP | 14 | 2012-09-11 | NULL |
| NARUTO | YOZIMAKI | NULL | JP | 17 | 2012-09-03 | 2012-11-07 |
+-----------+------------+--------+--------+-------+------------+------------+
3 rows in set (0.00 sec)

ALL ANY SOME子查询

ALL ANY 操作符的常见用法是结合一个相对比较操作符对一个数据列子查询的结果进行测试,他们测试比较值是否与子查询所返回的全部或一

部分值匹配,比如说:如果比较值小于或等于子查询所返回的每一个值,<=ALL将是TRUE,只要比较值小于或等于子查询所返回的任何一个

值,<=ANY将是TRUE,SOME是ANY的一个同义词

mysql> SELECT * FROM president WHERE birth <= ALL (SELECT birth FROM president);
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| SILA | ARK | D | US | 77 | 2012-09-02 | 2012-11-28 |
+-----------+------------+--------+------+-------+------------+------------+
1 row in set (0.00 sec)

IN 和 NOT IN 操作符是 = ANY 和 <> ALL的简写,也就是说,IN操作符含义是 等于子查询返回的某个数据行,NOT IN的含义是 不等于子查询

所返回的任何数据行

EXISTS NOT EXISTS
EXISTS和NOT EXISTS 用来测试子查询是否返回了数据行

mysql> SELECT EXISTS (SELECT * FROM member);
+-------------------------------+
| EXISTS (SELECT * FROM member) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (0.00 sec)

与主查询相关的子查询

子查询可以与主查询相关也可以无关

与主查询无关的子查询不引用主查询里的任何职,子查询可以作为一条独立的查询命令去执行

SELECT j FROM t2 WHERE j IN (SELECT i FROM t1);

与主查询相关的子查询需要引用主查询里的值,所以必须依赖主查询,通常用在EXISTS和NOT EXISTS子查询中

SELECT j FROM t2 WHERE (SELECT i FROM t1 WHERE i = j);

FROM子句中的子查询

子查询可以用在FROM子句里以生成一些值,在这种情况里,子查询的结果在行为上就像是一个数据表,FROM子句里的子查询可以参加关联操作,他的值可以再WHERE子句中被测试

mysql> SELECT * FROM (SELECT 1,2) AS t1 INNER JOIN (SELECT 3,4) AS t2;
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
1 row in set (0.01 sec)


上一篇:外联结 下一篇:快递查询API
0