One: Create a ecs_goods table to 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 Original 5800 Headphones | 8 | 1 | ECS000002 | 24 | 68.00 | 3 |
| 5 | Sony Ericsson original M2 card Reader | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |
| 6 | Sheng Chuang Kingmax Memory Card | 11 | 0 | ECS000006 | 15 | 42.00 | 0 |
| 7 | Nokia N85 Original stereo Headset HS-82 | 8 | 1 | ECS000007 | 20 | 100.00 | 0 |
| 8 | philips [email Protected] | 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 Albert G101 | 2 | 11 | ECS000016 | 0 | 823.33 | 3 |
| 17 | Amoi N7 | 3 | 5 | ECS000017 | 1 | 2300.00 | 2 |
| 18 | Amoi 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 | Multi-reach 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 $50 Prepaid Card | 13 | 0 | ECS000025 | 2 | 48.00 | 0 |
| 26 | PHS/fixed $20 Prepaid Card | 13 | 0 | ECS000026 | 2 | 19.00 | 0 |
| 27 | China Unicom $100 Prepaid Card | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |
| 28 | China Unicom $50 Prepaid Card | 15 | 0 | ECS000028 | 0 | 45.00 | 0 |
| 29 | Mobile $100 Prepaid Card | 14 | 0 | ECS000029 | 0 | 90.00 | 0 |
| 30 | Mobile $20 Prepaid 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 |
+----------+------------------------------+--------+----------+-----------+--------------+------------+-------- -----+
Two: Nested queries using
1.1: Commodity with primary key 32
Select Goods_id,goods_name,shop_price
From Ecs_goods
where goods_id=32;
1.2: All items not in the 3rd column
Select Goods_id,cat_id,goods_name,shop_price from Ecs_goods
where cat_id!=3;
1.3: Our store price is higher than 3000 yuan goods
Select Goods_id,cat_id,goods_name,shop_price from Ecs_goods
where Shop_price >3000;
1.4: Our 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 items in the 4th or 11th column (not allowed or)
Select Goods_id,cat_id,goods_name,shop_price from Ecs_goods
where cat_id in (4,11);
1.6: Take Out 100<= price <=500 (don't use and)
Select Goods_id,cat_id,goods_name,shop_price from Ecs_goods
Where shop_price between and 500;
1.7: Take out items that are not part of the 3rd column and do not belong to the 11th column (and, or not, 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: Take out goods with a price 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 3rd column below the price <1000 or >3000, and click Volume >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 item under the 1th column (note: There is no product under the 1 column, but there is a sub-section below)
Select Goods_id,cat_id,goods_name,shop_price,click_count from Ecs_goods
where cat_id in (2,3,4,5);
1.11: Take out the product whose name begins with "Nokia"
Select Goods_id,cat_id,goods_name,shop_price from Ecs_goods where goods_name like ' Nokia% ';
1.12: Remove the phone with the name "Nokia Nxx"
Select Goods_id,cat_id,goods_name,shop_price from Ecs_goods
where Goods_name like ' Nokia n__ ';
1.13: Take out the product whose name does 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 3rd column below the price between 1000 and 3000, and click on the volume >5 "Nokia" Start series products
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 and cat_id=3 and click_count>5 and goods_name like ' Nokia% ';
Copyright Notice: Bo Master original articles, reproduced please indicate the source. Http://blog.csdn.net/dzy21
MySQL nested query
