1 #使用数据库 2 USE db_book; 3 4 DROP TABLE t_pricelevel; 5 #创建表 6 create table `t_pricelevel` ( 7 `id` int PRIMARY KEY NOT NULL auto_increment, 8 `priceLevel` int , 9 `price` float , 10 `description` varchar (300) 11 ); 12 13 #插入数据 14 insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('1','1','80.00','价格贵的书'); 15 insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('2','2','60.00','价格适中的书'); 16 insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('3','3','40.00','价格便宜的书'); 17 18 19 20 #第四节:子查询 21 #4.1带in关键字的子查询 22 #一个查询语句的条件可能落在另一个select的查询结果中 23 SELECT * FROM t_book WHERE bookTypeId IN (SELECT id FROM t_booktype); 24 SELECT * FROM t_book WHERE bookTypeId NOT IN (SELECT id FROM t_booktype); 25 26 #4.2带比较运算符的子查询 27 #子查询可以使用比较运算符 28 SELECT * FROM t_book WHERE price >= (SELECT price FROM t_pricelevel WHERE priceLevel=1);#查询价格贵的书 29 30 #4.3带exists关键子的子查询 31 #假如子查询查询到记录,则进行外层查询,否则,不执行外层查询 32 SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);#如果子查询的结果有数据,外查询就查询 33 SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype WHERE id=5);#如果子查询的结果没有数据,外查询就不会查询 34 35 SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);#查询结果没有数据 36 SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype WHERE id=5);#查询结果有数据 37 38 #4.4带any关键字的子查询 39 #ANY关键字表示满足其中任一条件 40 SELECT * FROM t_book WHERE price >= ANY (SELECT price FROM t_pricelevel);#满足最小的 41 42 #4.5带all关键字的子查询 43 #ALL关键字表示满足所有条件 44 SELECT * FROM t_book WHERE price >= ALL (SELECT price FROM t_pricelevel);#满足最大的 45 46