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
