MYSQL之子查询

来源：互联网发布：ubuntu 16.04 syslog 编辑：程序博客网时间：2024/06/02 10:19

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

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);

0 0