Mysql学习（六）

Mysql学习（六）

1.子查询

带IN关键字的子查询

IN关键字表示输入结果与查询结果相同。

一个查询语句的条件可能落在另外一个SELECT语句的查询结果中。

mysql>

SELECT * FROM t_book WHERE bookTypeId IN(SELECT id FROM t_bookType);

+----+------------------+--------+-------+------------+

| id | bookName         | author | price | bookTypeId |

+----+------------------+--------+-------+------------+

|  1 | Jvav编程思想     |埃史尔 | 100   |          1 |

|  2 | Java从入门到放弃 |李XX   | 80    |          1 |

|  3 | 三剑客           |大仲马 | 70    |          2 |

+----+------------------+--------+-------+------------+

3 rows in set

 

带比较运算符的子查询

子查询可以带比较运算符

mysql> SELECT * FROM t_book WHERE price >= (SELECT price FROM t_pricelevel WHERE priceLevel =1);

+----+------------------+--------+-------+------------+

| id | bookName         | author | price | bookTypeId |

+----+------------------+--------+-------+------------+

|  1 | Jvav编程思想     |埃史尔 | 100   |          1 |

|  2 | Java从入门到放弃 |李XX   | 80    |          1 |

+----+------------------+--------+-------+------------+

2 rows in set

 

带Exists关键字的子查询

例如子查询查询到数据，则进行外层查询，否则，不执行外层查询；

mysql> SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_bookType);

+----+------------------+--------+-------+------------+

| id | bookName         | author | price | bookTypeId |

+----+------------------+--------+-------+------------+

|  1 | Jvav编程思想     |埃史尔 | 100   |          1 |

|  2 | Java从入门到放弃 |李XX   | 80    |          1 |

|  3 | 三剑客           |大仲马 | 70    |          2 |

|  4 | 复变函数         |魏教授 | 24.35 |          4 |

+----+------------------+--------+-------+------------+

4 rows in set

 

带Any关键字的子查询

ANY关键词表示满足其中任一条件；

mysql> SELECT * FROM t_book WHERE price >= ANY(SELECT price FROM t_pricelevel);

+----+------------------+--------+-------+------------+

| id | bookName         | author | price | bookTypeId |

+----+------------------+--------+-------+------------+

|  1 | Jvav编程思想     |埃史尔 | 100   |          1 |

|  2 | Java从入门到放弃 |李XX   | 80    |          1 |

|  3 | 三剑客           |大仲马 | 70    |          2 |

+----+------------------+--------+-------+------------+

3 rows in set

 

带ALL关键字的子查询

ALL关键字表示满足全部条件；

mysql> SELECT * FROM t_book WHERE price >= ALL(SELECT price FROM t_pricelevel);

+----+------------------+--------+-------+------------+

| id | bookName         | author | price | bookTypeId |

+----+------------------+--------+-------+------------+

|  1 | Jvav编程思想     |埃史尔 | 100   |          1 |

|  2 | Java从入门到放弃 |李XX   | 80    |          1 |

+----+------------------+--------+-------+------------+

2 rows in set

mysql>

 

 

2.合并查询结果

1.UNIONT

使用UNIONT关键字，数据库系统会将使用查询的结果合到一起，然后去掉相同的记录。

mysql> SELECT id FROM t_book UNION SELECT id FROM t_bookTYpe;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

+----+

4 rows in set

 

2.UNIONT ALL

使用UNIONT ALL重复结果不会去掉

mysql> SELECT id FROM t_book UNION ALL  SELECT id FROM t_bookTYpe;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

|  1 |

|  2 |

|  3 |

+----+

7 rows in set