Mysql之子查询和union

在之前的表基础上创建表

CREATE TABLE `price_level` (
	`id` INT ,
	`priceLevel` INT ,
	`price` FLOAT ,
	`description` VARCHAR (300)
); 
INSERT INTO `price_level` (`id`, `priceLevel`, `price`, `description`) VALUES('1','1','80.00','价格贵的书');
INSERT INTO `price_level` (`id`, `priceLevel`, `price`, `description`) VALUES('2','2','60.00','价格适中的书');
INSERT INTO `price_level` (`id`, `priceLevel`, `price`, `description`) VALUES('3','3','40.00','价格便宜的书');

 

In  关键字的子查询

一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。
SELECT * FROM book WHERE booktypeId IN (SELECT id FROM booktype);

SELECT * FROM book WHERE booktypeId NOT IN (SELECT id FROM booktype);

带比较运算符的子查询

子查询可以使用比较运算符

SELECT * FROM book WHERE price>=(SELECT price FROM price_level WHERE priceLevel=1);

带 Exists  关键字的子查询

假如子查询查询到记录，则进行外层查询,否则,不执行外层查询;

SELECT * FROM book WHERE EXISTS (SELECT * FROM booktype);

SELECT * FROM book WHERE NOT EXISTS (SELECT * FROM booktype);

 带 Any  关键字的子查询

ANY 关键字表示满足其中任一条件
SELECT * FROM book WHERE price>= ANY (SELECT price FROM price_level);

 带 All  关键字的子查询

ALL 关键字表示满足所有条件

SELECT * FROM book WHERE price>= ALL (SELECT price FROM price_level);

合并查询结果

UNION

  UNION 关键字是，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录；
  SELECT id FROM book UNION SELECT id FROM price_level 

UNION ALL

  使用 UNION ALL，不会去除掉系统的记录  
  SELECT id FROM book UNION ALL SELECT id FROM price_level   

   注意:使用union 查询的字段个数需要一致，查询的字段名需要一致