Mysql nested query _ MySQL

1. create an ecs_goods table and insert the following data: bytes | goods _ 1: Create an ecs_goods table and insert the following data:
+ ---------- + ---------------------------- + -------- + ---------- + ----------- + -------------- + ------------ + --------------- +
| Goods_id | goods_name | cat_id | brand_id | goods_sn | goods_number | shop_price | click_count |
+ ---------- + ---------------------------- + -------- + ---------- + ----------- + -------------- + ------------ + --------------- +
| 1 | KD876 | 4 | 8 | ECS000000 | 10 | 1388.00 | 7 |
| 4 | Nokia N85 original charger | 8 | 1 | ECS000004 | 17 | 58.00 | 0 |
| 3 | Nokia 5800 headphones | 8 | 1 | ECS000002 | 24 | 68.00 | 3 |
| 5 | Sony Ericsson original M2 card reader | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |
| 6 | Shengchuang KINGMAX memory card | 11 | 0 | ECS000006 | 15 | 42.00 | 0 |
| 7 | Nokia N85 original stereo headphones HS-82 | 8 | 1 | ECS000007 | 20 | 100.00 | 0 |
| 8 | Philips 9 @ 9 v | 3 | 4 | ECS000008 | 17 | 399.00 | 9 |
| 9 | Nokia E66 | 3 | 1 | ECS000009 | 13 | 2298.00 | 20 |
| 10 | Sony Ericsson C702c | 3 | 7 | ECS000010 | 7 | 1328.00 | 11 |
| 11 | Sony Ericsson C702c | 3 | 7 | ECS000011 | 1 | 1300.00 | 0 |
| 12 | Motorola A810 | 3 | 2 | ECS000012 | 8 | 983.00 | 14 |
| 13 | Nokia 5320 XpressMusic | 3 | 1 | ECS000013 | 8 | 1311.00 | 13 |
| 14 | Nokia 5800XM | 4 | 1 | ECS000014 | 4 | 2625.00 | 6 |
| 15 | Motorola A810 | 3 | 2 | ECS000015 | 3 | 788.00 | 8 |
| 16 | Henderson tech G101 | 2 | 11 | ECS000016 | 0 | 823.33 | 3 |
| 17 | Xia xinn7 | 3 | 5 | ECS000017 | 1 | 2300.00 | 2 |
| 18 | Xia Xin T5 | 4 | 5 | ECS000018 | 1 | 2878.00 | 0 |
| 19 | Samsung SGH-F258 | 3 | 6 | ECS000019 | 0 | 858.00 | 7 |
| 20 | Samsung BC01 | 3 | 6 | ECS000020 | 13 | 280.00 | 14 |
| 21 | Gionee A30 | 3 | 10 | ECS000021 | 40 | 2000.00 | 4 |
| 22 | reaching Touch HD | 3 | 3 | ECS000022 | 0 | 5999.00 | 15 |
| 23 | Nokia N96 | 5 | 1 | ECS000023 | 8 | 3700.00 | 17 |
| 24 | P806 | 3 | 9 | ECS000024 | 148 | 2000.00 | 36 |
| 25 | PHS/fixed-line 50 yuan recharge card | 13 | 0 | ECS000025 | 2 | 48.00 | 0 |
| 26 | PHS/fixed-line 20 yuan recharge card | 13 | 0 | ECS000026 | 2 | 19.00 | 0 |
| 27 | China Unicom 100 RMB recharge card | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |
| 28 | China Unicom 50 RMB recharge card | 15 | 0 | ECS000028 | 0 | 45.00 | 0 |
| 29 | mobile 100 RMB recharge card | 14 | 0 | ECS000029 | 0 | 90.00 | 0 |
| 30 | Mobile 20 RMB recharge card | 14 | 0 | ECS000030 | 9 | 18.00 | 1 |
| 31 | Motorola E8 | 3 | 2 | ECS000031 | 1 | 1337.00 | 5 |
| 32 | Nokia N85 | 3 | 1 | ECS000032 | 1 | 3010.00 | 9 |
+ ---------- + ---------------------------- + -------- + ---------- + ----------- + -------------- + ------------ + --------------- +
II. nested query
1.1: a commodity whose primary key is 32
Select goods_id, goods_name, shop_price
From ecs_goods
Where goods_id = 32;
1.2: all products not listed in section 3rd
Select goods_id, cat_id, goods_name, shop_price from ecs_goods
Where cat_id! = 3;


1.3: products with prices higher than 3000 RMB


Select goods_id, cat_id, goods_name, shop_price from ecs_goods
Where shop_price> 3000;


1.4: the store price is less than or equal to 100 yuan of goods
Select goods_id, cat_id, goods_name, shop_price from ecs_goods where shop_price <= 100;


1.5: take out the products of column 4th or column 11th (not allowed or)
Select goods_id, cat_id, goods_name, shop_price from ecs_goods
Where cat_id in (4, 11 );



1.6: 100 <= Price <= 500 (and not allowed)
Select goods_id, cat_id, goods_name, shop_price from ecs_goods
Where shopp_price between 100 and 500;

1.7: Retrieve products that do not belong to column 3rd and do not belong to column 11th (and, or not in, respectively)
Select goods_id, cat_id, goods_name, shop_price from ecs_goods where cat_id! = 3 and cat_id! = 11;
Select goods_id, cat_id, goods_name, shop_price from ecs_goods where cat_id not in (3,11 );


1.8: goods whose prices are greater than 100 and less than 300, or greater than 4000 and less than 5000 ()
Select goods_id, cat_id, goods_name, shop_price from ecs_goods where shop_price> 100 and shop_price <300 or shop_price> 4000 and shop_price <5000;


1.9: take out the price <3rd or> 1000 under the 3000 columns, and click more than 5 series of products
Select goods_id, cat_id, goods_name, shop_price, click_count from ecs_goods where
Cat_id = 3 and (shop_price <1000 or shop_price> 3000) and click_count> 5;


1.10: take out the items under the 1st columns (note: 1 there are no items under the column, but there are items under its subcolumns)
Select goods_id, cat_id, goods_name, shop_price, click_count from ecs_goods
Where cat_id in (2, 3, 4, 5 );


1.11: products whose names start with "Nokia"
Select goods_id, cat_id, goods_name, shop_price from ecs_goods where goods_name like 'Nokia % ';


1.12: retrieve the phone number named "Nokia Nxx"
Select goods_id, cat_id, goods_name, shop_price from ecs_goods
Where goods_name like 'Nokia N __';


1.13: products whose names do not start with "Nokia"
Select goods_id, cat_id, goods_name, shop_price from ecs_goos
Where goods_name not like 'Nokia % ';


1.14: take out the prices of the 3rd topics between 1000 and 3000, and click products starting with 5 "Nokia ".
Select goods_id, cat_id, goods_name, shop_price from ecs_goods where
Cat_id = 3 and shop_price> 1000 and shop_price <3000 and click_count> 5 and goods_name like 'Nokia % ';


Select goods_id, cat_id, goods_name, shop_price from ecs_goods where
Shop_price between 1000 and 3000 and cat_id = 3 and click_count> 5 and goods_name like 'Nokia % ';