MYSQL之子查询

本文主要介绍一些关于子查询的语句。

CREATE TABLE `t_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookName` varchar(20) DEFAULT NULL,
  `price` decimal(6,2) DEFAULT NULL,
  `author` varchar(20) DEFAULT NULL,
  `bookTypeId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

insert  into `t_book`(`id`,`bookName`,`price`,`author`,`bookTypeId`) values (1,'Java编程思想','100.00','埃史尔',1),(2,'Java从入门到精通','80.00','李钟尉',1),(3,'三剑客','70.00','大仲马',2),(4,'生理学(第二版)','24.00','刘先国',4);

CREATE TABLE `t_booktype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookTypeName` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

insert  into `t_booktype`(`id`,`bookTypeName`) values (1,'计算机类'),(2,'文学类'),(3,'教育类');

create table `t_pricelevel` (
	`id` int ,
	`priceLevel` int ,
	`price` float ,
	`description` varchar (300)
); 
insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('1','1','80.00','价格贵的书');
insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('2','2','60.00','价格适中的书');
insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('3','3','40.00','价格便宜的书');

查询所有t_book中的数据，其中booktypeId在表t_booktype中
SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);

查询所有t_book中的数据，其中booktypeId不在表t_booktype中
SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);

查询价格贵（大于价格水平1）的书
SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);

如果SELECT * FROM t_booktype返回不为空，查询t_book中所有数据
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);

如果SELECT * FROM t_booktype返回为空，查询t_book中所有数据
SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);

查询大于等于任意一个在价格表中价格的书
SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);

查询大于等于所有在价格表中价格的书
SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);