mysql> use test
Database changed
mysql> set names gbk;
Query OK, 0 rows affected (0.02 sec)
mysql> #在PHP中,获取不同栏目的商品
mysql> #在地址栏上获取$_GET['id']
mysql> select goods_id,cat_id,goods_name from goods
-> where cat_id=5;
+----------+--------+------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------+
| 23 | 5 | 诺基亚N96 |
+----------+--------+------------+
1 row in set (0.09 sec)
mysql> select goods_id,cat_id,goods_name from goods
-> where cat_id=4;
+----------+--------+---------------------+
| goods_id | cat_id | goods_name |
+----------+--------+---------------------+
| 1 | 4 | KD876 |
| 14 | 4 | 诺基亚5800XM |
| 18 | 4 | 夏新T5 |
| 33 | 4 | 金立910浪漫镶钻手机 |
+----------+--------+---------------------+
4 rows in set (0.00 sec)
mysql> #取出第4个栏目下的商品,并按价格由高到低排序
mysql> select goods_id,goods_name,shop_price
-> from goods
-> where cat_id=4;
+----------+---------------------+------------+
| goods_id | goods_name | shop_price |
+----------+---------------------+------------+
| 1 | KD876 | 1388.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 18 | 夏新T5 | 2878.00 |
| 33 | 金立910浪漫镶钻手机 | 1233.00 |
+----------+---------------------+------------+
4 rows in set (0.00 sec)
mysql> #为什么和商城取出的数据不一样,
mysql> #因为我们的表没有is_delete和is_on_sale字段
mysql> #因此和商城里取出的数据略有不同是正常的.
mysql> select goods_id,goods_name,shop_price
-> from goods
-> where cat_id=4
-> order by shop_price desc;
+----------+---------------------+------------+
| goods_id | goods_name | shop_price |
+----------+---------------------+------------+
| 18 | 夏新T5 | 2878.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 1 | KD876 | 1388.00 |
| 33 | 金立910浪漫镶钻手机 | 1233.00 |
+----------+---------------------+------------+
4 rows in set (0.00 sec)
mysql> #上面的结果就是用shop_price字段来降序排序
mysql> desc goods;
+--------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------------+------+-----+---------+----------------+
| goods_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| cat_id | smallint(5) unsigned | NO | | 0 | |
| goods_sn | varchar(60) | NO | | | |
| goods_name | varchar(120) | NO | | | |
| click_count | int(10) unsigned | NO | | 0 | |
| goods_number | smallint(5) unsigned | NO | | 0 | |
| market_price | decimal(10,2) unsigned | NO | | 0.00 | |
| shop_price | decimal(10,2) unsigned | NO | | 0.00 | |
| add_time | int(10) unsigned | NO | | 0 | |
| is_best | tinyint(1) unsigned | NO | | 0 | |
| is_new | tinyint(1) unsigned | NO | | 0 | |
| is_hot | tinyint(1) unsigned | NO | | 0 | |
+--------------+------------------------+------+-----+---------+----------------+
12 rows in set (0.19 sec)
mysql> #按发布时间升序排序,即发布早的,时间戳小的,靠前
mysql> select goods_id,goods_name,shop_price
-> from goods
-> where cat_id=4
-> order by add_time asc;
+----------+---------------------+------------+
| goods_id | goods_name | shop_price |
+----------+---------------------+------------+
| 1 | KD876 | 1388.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 18 | 夏新T5 | 2878.00 |
| 33 | 金立910浪漫镶钻手机 | 1233.00 |
+----------+---------------------+------------+
4 rows in set (0.00 sec)
mysql> select goods_id,goods_name,shop_price
-> from goods
-> where cat_id=4
-> order by add_time desc;
+----------+---------------------+------------+
| goods_id | goods_name | shop_price |
+----------+---------------------+------------+
| 33 | 金立910浪漫镶钻手机 | 1233.00 |
| 18 | 夏新T5 | 2878.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 1 | KD876 | 1388.00 |
+----------+---------------------+------------+
4 rows in set (0.00 sec)
mysql> #按栏目排序
mysql> select goods_id,goods_name,shop_price
-> \c
mysql> select goods_id,cat_id,goods_name,shop_price
-> from goods
-> order by cat_id asc;
+----------+--------+------------------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+------------------------------+------------+
| 16 | 2 | 恒基伟业G101 | 823.33 |
| 19 | 3 | 三星SGH-F258 | 858.00 |
| 17 | 3 | 夏新N7 | 2300.00 |
| 15 | 3 | 摩托罗拉A810 | 788.00 |
| 20 | 3 | 三星BC01 | 280.00 |
| 13 | 3 | 诺基亚5320 XpressMusic | 1311.00 |
| 12 | 3 | 摩托罗拉A810 | 983.00 |
| 11 | 3 | 索爱C702c | 1300.00 |
| 10 | 3 | 索爱C702c | 1328.00 |
| 9 | 3 | 诺基亚E66 | 2298.00 |
| 8 | 3 | 飞利浦9@9v | 399.00 |
| 21 | 3 | 金立 A30 | 2000.00 |
| 22 | 3 | 多普达Touch HD | 5999.00 |
| 24 | 3 | P806 | 2000.00 |
| 31 | 3 | 摩托罗拉E8 | 1337.00 |
| 32 | 3 | 诺基亚N85 | 3010.00 |
| 1 | 4 | KD876 | 1388.00 |
| 18 | 4 | 夏新T5 | 2878.00 |
| 33 | 4 | 金立910浪漫镶钻手机 | 1233.00 |
| 14 | 4 | 诺基亚5800XM | 2625.00 |
| 23 | 5 | 诺基亚N96 | 3700.00 |
| 7 | 8 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 4 | 8 | 诺基亚N85原装充电器 | 58.00 |
| 3 | 8 | 诺基亚原装5800耳机 | 68.00 |
| 6 | 11 | 胜创KINGMAX内存卡 | 42.00 |
| 5 | 11 | 索爱原装M2卡读卡器 | 20.00 |
| 26 | 13 | 小灵通/固话20元充值卡 | 19.00 |
| 25 | 13 | 小灵通/固话50元充值卡 | 48.00 |
| 29 | 14 | 移动100元充值卡 | 90.00 |
| 30 | 14 | 移动20元充值卡 | 18.00 |
| 28 | 15 | 联通50元充值卡 | 45.00 |
| 27 | 15 | 联通100元充值卡 | 95.00 |
+----------+--------+------------------------------+------------+
32 rows in set (0.00 sec)
mysql> #我们按栏目升序排列,同一个栏目下的商品,再按商品的价格降序排列
mysql> select goods_id,cat_id,goods_name,shop_price
-> from goods
-> order by cat_id asc, shop_price desc;
+----------+--------+------------------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+------------------------------+------------+
| 16 | 2 | 恒基伟业G101 | 823.33 |
| 22 | 3 | 多普达Touch HD | 5999.00 |
| 32 | 3 | 诺基亚N85 | 3010.00 |
| 17 | 3 | 夏新N7 | 2300.00 |
| 9 | 3 | 诺基亚E66 | 2298.00 |
| 24 | 3 | P806 | 2000.00 |
| 21 | 3 | 金立 A30 | 2000.00 |
| 31 | 3 | 摩托罗拉E8 | 1337.00 |
| 10 | 3 | 索爱C702c | 1328.00 |
| 13 | 3 | 诺基亚5320 XpressMusic | 1311.00 |
| 11 | 3 | 索爱C702c | 1300.00 |
| 12 | 3 | 摩托罗拉A810 | 983.00 |
| 19 | 3 | 三星SGH-F258 | 858.00 |
| 15 | 3 | 摩托罗拉A810 | 788.00 |
| 8 | 3 | 飞利浦9@9v | 399.00 |
| 20 | 3 | 三星BC01 | 280.00 |
| 18 | 4 | 夏新T5 | 2878.00 |
| 14 | 4 | 诺基亚5800XM | 2625.00 |
| 1 | 4 | KD876 | 1388.00 |
| 33 | 4 | 金立910浪漫镶钻手机 | 1233.00 |
| 23 | 5 | 诺基亚N96 | 3700.00 |
| 7 | 8 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 3 | 8 | 诺基亚原装5800耳机 | 68.00 |
| 4 | 8 | 诺基亚N85原装充电器 | 58.00 |
| 6 | 11 | 胜创KINGMAX内存卡 | 42.00 |
| 5 | 11 | 索爱原装M2卡读卡器 | 20.00 |
| 25 | 13 | 小灵通/固话50元充值卡 | 48.00 |
| 26 | 13 | 小灵通/固话20元充值卡 | 19.00 |
| 29 | 14 | 移动100元充值卡 | 90.00 |
| 30 | 14 | 移动20元充值卡 | 18.00 |
| 27 | 15 | 联通100元充值卡 | 95.00 |
| 28 | 15 | 联通50元充值卡 | 45.00 |
+----------+--------+------------------------------+------------+
32 rows in set (0.00 sec)
mysql> #限制条目 limit
mysql> select goods_id,cat_id,goods_name,shop_price
-> from goods
-> where cat_id=3
-> order by shop_price asc
-> limit 10;
+----------+--------+------------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+------------------------+------------+
| 20 | 3 | 三星BC01 | 280.00 |
| 8 | 3 | 飞利浦9@9v | 399.00 |
| 15 | 3 | 摩托罗拉A810 | 788.00 |
| 19 | 3 | 三星SGH-F258 | 858.00 |
| 12 | 3 | 摩托罗拉A810 | 983.00 |
| 11 | 3 | 索爱C702c | 1300.00 |
| 13 | 3 | 诺基亚5320 XpressMusic | 1311.00 |
| 10 | 3 | 索爱C702c | 1328.00 |
| 31 | 3 | 摩托罗拉E8 | 1337.00 |
| 21 | 3 | 金立 A30 | 2000.00 |
+----------+--------+------------------------+------------+
10 rows in set (0.00 sec)
mysql> select goods_id,cat_id,goods_name,shop_price
-> from goods
-> where cat_id=3
-> order by shop_price asc
-> ;
+----------+--------+------------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+------------------------+------------+
| 20 | 3 | 三星BC01 | 280.00 |
| 8 | 3 | 飞利浦9@9v | 399.00 |
| 15 | 3 | 摩托罗拉A810 | 788.00 |
| 19 | 3 | 三星SGH-F258 | 858.00 |
| 12 | 3 | 摩托罗拉A810 | 983.00 |
| 11 | 3 | 索爱C702c | 1300.00 |
| 13 | 3 | 诺基亚5320 XpressMusic | 1311.00 |
| 10 | 3 | 索爱C702c | 1328.00 |
| 31 | 3 | 摩托罗拉E8 | 1337.00 |
| 21 | 3 | 金立 A30 | 2000.00 |
| 24 | 3 | P806 | 2000.00 |
| 9 | 3 | 诺基亚E66 | 2298.00 |
| 17 | 3 | 夏新N7 | 2300.00 |
| 32 | 3 | 诺基亚N85 | 3010.00 |
| 22 | 3 | 多普达Touch HD | 5999.00 |
+----------+--------+------------------------+------------+
15 rows in set (0.00 sec)
mysql> #查询出本店价格最高的前三名
mysql> select goods_id,goods_name,shop_price
-> from goods
-> order by shop_price
-> desc
-> limit 0,3;
+----------+----------------+------------+
| goods_id | goods_name | shop_price |
+----------+----------------+------------+
| 22 | 多普达Touch HD | 5999.00 |
| 23 | 诺基亚N96 | 3700.00 |
| 32 | 诺基亚N85 | 3010.00 |
+----------+----------------+------------+
3 rows in set (0.00 sec)
mysql> #查询出本店最高的 第3名到第5名 商品
mysql> #取第3到第5,即意味跳过 第1,第2,因此偏移量offset是2
mysql> #取第3,4,5条,即取3条,因此N=3
mysql> select goods_id,goods_name,shop_price
-> from goods
-> order by shop_price
-> desc
-> limit 2,3;
+----------+--------------+------------+
| goods_id | goods_name | shop_price |
+----------+--------------+------------+
| 32 | 诺基亚N85 | 3010.00 |
| 18 | 夏新T5 | 2878.00 |
| 14 | 诺基亚5800XM | 2625.00 |
+----------+--------------+------------+
3 rows in set (0.00 sec)
mysql> #offset是跳过的行数,N是实际取的行数
mysql> #取出价格最高的那一行商品
mysql> #思路:按价格降序排列,最高的排前面,取1个,即第1名.
mysql> select goods_id,goods_name,shop_price from goods
-> order by shop_price desc limit 0,1;
+----------+----------------+------------+
| goods_id | goods_name | shop_price |
+----------+----------------+------------+
| 22 | 多普达Touch HD | 5999.00 |
+----------+----------------+------------+
1 row in set (0.01 sec)
mysql> #如果offset为0,可以不写
mysql> #即下语句,也是取出价格最高的一行商品
mysql> select goods_id,goods_name,shop_price from goods
-> order by shop_price desc limit 1;
+----------+----------------+------------+
| goods_id | goods_name | shop_price |
+----------+----------------+------------+
| 22 | 多普达Touch HD | 5999.00 |
+----------+----------------+------------+
1 row in set (0.00 sec)
mysql> # 查询出 每个栏目下id号最大(最新)的一条商品
mysql> #第一种错误,直接group by cat_id
mysql> select goods_id,cat_id,goods_name from goods group by cat_id;
+----------+--------+-----------------------+
| goods_id | cat_id | goods_name |
+----------+--------+-----------------------+
| 16 | 2 | 恒基伟业G101 |
| 8 | 3 | 飞利浦9@9v |
| 1 | 4 | KD876 |
| 23 | 5 | 诺基亚N96 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 25 | 13 | 小灵通/固话50元充值卡 |
| 29 | 14 | 移动100元充值卡 |
| 27 | 15 | 联通100元充值卡 |
+----------+--------+-----------------------+
9 rows in set (0.00 sec)
mysql> select goods_id,cat_id,goods_name from goods group by cat_id;
+----------+--------+-----------------------+
| goods_id | cat_id | goods_name |
+----------+--------+-----------------------+
| 16 | 2 | 恒基伟业G101 |
| 8 | 3 | 飞利浦9@9v |
| 1 | 4 | KD876 |
| 23 | 5 | 诺基亚N96 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 25 | 13 | 小灵通/固话50元充值卡 |
| 29 | 14 | 移动100元充值卡 |
| 27 | 15 | 联通100元充值卡 |
+----------+--------+-----------------------+
9 rows in set (0.00 sec)
mysql> select goods_id,cat_id,goods_name from goods group by cat_id;
+----------+--------+-----------------------+
| goods_id | cat_id | goods_name |
+----------+--------+-----------------------+
| 16 | 2 | 恒基伟业G101 |
| 8 | 3 | 飞利浦9@9v |
| 1 | 4 | KD876 |
| 23 | 5 | 诺基亚N96 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 25 | 13 | 小灵通/固话50元充值卡 |
| 29 | 14 | 移动100元充值卡 |
| 27 | 15 | 联通100元充值卡 |
+----------+--------+-----------------------+
9 rows in set (0.00 sec)
mysql> #另一种错误
mysql> select max(gooods_id),cat_id,goods_name
-> from goods
-> group by cat_id;
ERROR 1054 (42S22): Unknown column 'gooods_id' in 'field list'
mysql> select max(goods_id),cat_id,goods_name
-> from goods
-> group by cat_id;
+---------------+--------+-----------------------+
| max(goods_id) | cat_id | goods_name |
+---------------+--------+-----------------------+
| 16 | 2 | 恒基伟业G101 |
| 32 | 3 | 飞利浦9@9v |
| 33 | 4 | KD876 |
| 23 | 5 | 诺基亚N96 |
| 7 | 8 | 诺基亚N85原装充电器 |
| 6 | 11 | 索爱原装M2卡读卡器 |
| 26 | 13 | 小灵通/固话50元充值卡 |
| 30 | 14 | 移动100元充值卡 |
| 28 | 15 | 联通100元充值卡 |
+---------------+--------+-----------------------+
9 rows in set (0.00 sec)
mysql> #我们这么想,既然group时,mysql是取每个分组下第一次出现的行.
mysql> #我就先把goods_id最大的排前面
mysql> select goods_id,cat_id,goods_name from goods
-> order by cat_id asc,goods_id desc;
+----------+--------+------------------------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------------------------+
| 16 | 2 | 恒基伟业G101 |
| 32 | 3 | 诺基亚N85 |
| 31 | 3 | 摩托罗拉E8 |
| 24 | 3 | P806 |
| 22 | 3 | 多普达Touch HD |
| 21 | 3 | 金立 A30 |
| 20 | 3 | 三星BC01 |
| 19 | 3 | 三星SGH-F258 |
| 17 | 3 | 夏新N7 |
| 15 | 3 | 摩托罗拉A810 |
| 13 | 3 | 诺基亚5320 XpressMusic |
| 12 | 3 | 摩托罗拉A810 |
| 11 | 3 | 索爱C702c |
| 10 | 3 | 索爱C702c |
| 9 | 3 | 诺基亚E66 |
| 8 | 3 | 飞利浦9@9v |
| 33 | 4 | 金立910浪漫镶钻手机 |
| 18 | 4 | 夏新T5 |
| 14 | 4 | 诺基亚5800XM |
| 1 | 4 | KD876 |
| 23 | 5 | 诺基亚N96 |
| 7 | 8 | 诺基亚N85原装立体声耳机HS-82 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 3 | 8 | 诺基亚原装5800耳机 |
| 6 | 11 | 胜创KINGMAX内存卡 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 26 | 13 | 小灵通/固话20元充值卡 |
| 25 | 13 | 小灵通/固话50元充值卡 |
| 30 | 14 | 移动20元充值卡 |
| 29 | 14 | 移动100元充值卡 |
| 28 | 15 | 联通50元充值卡 |
| 27 | 15 | 联通100元充值卡 |
+----------+--------+------------------------------+
32 rows in set (0.00 sec)
mysql> #在此基础,我再一分组,不就行了吗?
mysql> #先order ,再group
mysql> select goods_id,cat_id,goods_name from goods
-> order by cat_id asc,goods_id desc
-> group by cat_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by cat_id' at line 3
mysql> #语法错误
mysql> # 接下来学习子查询
mysql> # 查出本网站最新的(goods_id最大)的一条商品
mysql> # 思路,按goods_id desc排序,再取第一行
mysql> select goods_id,goods_name from goods
-> order by goods_id desc limit 0,1;
+----------+---------------------+
| goods_id | goods_name |
+----------+---------------------+
| 33 | 金立910浪漫镶钻手机 |
+----------+---------------------+
1 row in set (0.00 sec)
mysql> # 查出本网站最新的(goods_id最大)的一条商品,要求:不用排序
mysql> select max(goods_id),goods_name from goods;
+---------------+------------+
| max(goods_id) | goods_name |
+---------------+------------+
| 33 | KD876 |
+---------------+------------+
1 row in set (0.02 sec)
mysql> # 其实这道题非常简单.
mysql> select goods_id,goods_name from goods where goods_id=33;
+----------+---------------------+
| goods_id | goods_name |
+----------+---------------------+
| 33 | 金立910浪漫镶钻手机 |
+----------+---------------------+
1 row in set (0.00 sec)
mysql> # 这个做法不具备通用性,增加或删除了一条商品,则最大goods_id已经不是33了.
mysql> # 我有一个办法,始终能查出最大的goods_id来
mysql> select max(goods_id) from goods;
+---------------+
| max(goods_id) |
+---------------+
| 33 |
+---------------+
1 row in set (0.00 sec)
mysql> select goods_id,goods_name from goods where goods_id=33;
+----------+---------------------+
| goods_id | goods_name |
+----------+---------------------+
| 33 | 金立910浪漫镶钻手机 |
+----------+---------------------+
1 row in set (0.00 sec)
mysql> #以后,凡需要查最新商品,先用max()查出最大的goods_id,
mysql> #然后再根据goods_id查询商品
mysql> # 就算以后商品表再怎么办, select max()语句的返回值,始终是指向最大goods_id的
mysql> select goods_id,goods_name from goods
-> where goods_id=(select max(goods_id) from goods);
+----------+---------------------+
| goods_id | goods_name |
+----------+---------------------+
| 33 | 金立910浪漫镶钻手机 |
+----------+---------------------+
1 row in set (0.00 sec)
mysql> # 用where型子查询,查询"每个栏目下goods_id最大的商品"
mysql> # 第一步:先查出每个栏目下,最大的goods_id.
mysql> select max(goods_id) from goods
-> group by cat_id;
+---------------+
| max(goods_id) |
+---------------+
| 16 |
| 32 |
| 33 |
| 23 |
| 7 |
| 6 |
| 26 |
| 30 |
| 28 |
+---------------+
9 rows in set (0.00 sec)
mysql> select max(goods_id),cat_id from goods
-> group by cat_id;
+---------------+--------+
| max(goods_id) | cat_id |
+---------------+--------+
| 16 | 2 |
| 32 | 3 |
| 33 | 4 |
| 23 | 5 |
| 7 | 8 |
| 6 | 11 |
| 26 | 13 |
| 30 | 14 |
| 28 | 15 |
+---------------+--------+
9 rows in set (0.00 sec)
mysql> #下一步,我们只需,再把goods_id=16,32,33....28的这几条商品取出来.
mysql> select goods_id,goods_name from goods
-> where goods in (select max(goods_id) from goods group by cat_id);
ERROR 1054 (42S22): Unknown column 'goods' in 'IN/ALL/ANY subquery'
mysql> select goods_id,goods_name from goods
-> where goods_id in (select max(goods_id) from goods group by cat_id);
+----------+------------------------------+
| goods_id | goods_name |
+----------+------------------------------+
| 6 | 胜创KINGMAX内存卡 |
| 7 | 诺基亚N85原装立体声耳机HS-82 |
| 16 | 恒基伟业G101 |
| 23 | 诺基亚N96 |
| 26 | 小灵通/固话20元充值卡 |
| 28 | 联通50元充值卡 |
| 30 | 移动20元充值卡 |
| 32 | 诺基亚N85 |
| 33 | 金立910浪漫镶钻手机 |
+----------+------------------------------+
9 rows in set (0.02 sec)
mysql> # 这个结果 你能区分是表,还是查询结果吗
mysql> select goods_id,shop_price,market_price,market_price-shop_price as discount
-> from goods
-> limit 10;
+----------+------------+--------------+----------+
| goods_id | shop_price | market_price | discount |
+----------+------------+--------------+----------+
| 1 | 1388.00 | 1665.60 | 277.60 |
| 4 | 58.00 | 69.60 | 11.60 |
| 3 | 68.00 | 81.60 | 13.60 |
| 5 | 20.00 | 24.00 | 4.00 |
| 6 | 42.00 | 50.40 | 8.40 |
| 7 | 100.00 | 120.00 | 20.00 |
| 8 | 399.00 | 478.79 | 79.79 |
| 9 | 2298.00 | 2757.60 | 459.60 |
| 10 | 1328.00 | 1593.60 | 265.60 |
| 11 | 1300.00 | 0.00 | -1300.00 |
+----------+------------+--------------+----------+
10 rows in set (0.00 sec)
mysql> # from型子查询,查询每个栏目下,goods_id最大的商品
mysql> select goods_id,cat_id,goods_name from goods
-> order by cat_id asc,goods_id desc;
+----------+--------+------------------------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------------------------+
| 16 | 2 | 恒基伟业G101 |
| 32 | 3 | 诺基亚N85 |
| 31 | 3 | 摩托罗拉E8 |
| 24 | 3 | P806 |
| 22 | 3 | 多普达Touch HD |
| 21 | 3 | 金立 A30 |
| 20 | 3 | 三星BC01 |
| 19 | 3 | 三星SGH-F258 |
| 17 | 3 | 夏新N7 |
| 15 | 3 | 摩托罗拉A810 |
| 13 | 3 | 诺基亚5320 XpressMusic |
| 12 | 3 | 摩托罗拉A810 |
| 11 | 3 | 索爱C702c |
| 10 | 3 | 索爱C702c |
| 9 | 3 | 诺基亚E66 |
| 8 | 3 | 飞利浦9@9v |
| 33 | 4 | 金立910浪漫镶钻手机 |
| 18 | 4 | 夏新T5 |
| 14 | 4 | 诺基亚5800XM |
| 1 | 4 | KD876 |
| 23 | 5 | 诺基亚N96 |
| 7 | 8 | 诺基亚N85原装立体声耳机HS-82 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 3 | 8 | 诺基亚原装5800耳机 |
| 6 | 11 | 胜创KINGMAX内存卡 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 26 | 13 | 小灵通/固话20元充值卡 |
| 25 | 13 | 小灵通/固话50元充值卡 |
| 30 | 14 | 移动20元充值卡 |
| 29 | 14 | 移动100元充值卡 |
| 28 | 15 | 联通50元充值卡 |
| 27 | 15 | 联通100元充值卡 |
+----------+--------+------------------------------+
32 rows in set (0.00 sec)
mysql> # 如果存在一张如上表,只要对如上表group一下,即可得到每个栏目goods_id最大的商品
mysql> # 假设存在这张表,表名叫 tmp
mysql> select * from tmp group by cat_id;
ERROR 1146 (42S02): Table 'test.tmp' doesn't exist
mysql> select * from (select goods_id,cat_id,goods_name from goods
-> order by cat_id asc,goods_id desc) as tmp group by cat_id;
+----------+--------+------------------------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------------------------+
| 16 | 2 | 恒基伟业G101 |
| 32 | 3 | 诺基亚N85 |
| 33 | 4 | 金立910浪漫镶钻手机 |
| 23 | 5 | 诺基亚N96 |
| 7 | 8 | 诺基亚N85原装立体声耳机HS-82 |
| 6 | 11 | 胜创KINGMAX内存卡 |
| 26 | 13 | 小灵通/固话20元充值卡 |
| 30 | 14 | 移动20元充值卡 |
| 28 | 15 | 联通50元充值卡 |
+----------+--------+------------------------------+
9 rows in set (0.00 sec)
mysql> # exists 存在,exists子查询
mysql> # 要求:查出有商品的栏目
mysql> # 再建一张表栏目表
mysql> create table category (
-> cat_id int auto_increment primary key,
-> cat_name varchar(20) not null default ''
-> )engine myiasm;
ERROR 1286 (42000): Unknown storage engine 'myiasm'
mysql> create table category (
-> cat_id int auto_increment primary key,
-> cat_name varchar(20) not null default ''
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.28 sec)
mysql> desc category;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| cat_id | int(11) | NO | PRI | NULL | auto_increment |
| cat_name | varchar(20) | NO | | | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.16 sec)
mysql> insert into category
-> \c
mysql> insert into test.category
-> select cat_id,cat_name from shop.category;
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select * from category;
+--------+-------------------+
| cat_id | cat_name |
+--------+-------------------+
| 1 | 手机类型 |
| 2 | CDMA手机 |
| 3 | GSM手机 |
| 4 | 3G手机 |
| 5 | 双模手机 |
| 6 | 手机配件 |
| 7 | 充电器 |
| 8 | 耳机 |
| 9 | 电池 |
| 11 | 读卡器和内存卡 |
| 12 | 充值卡 |
| 13 | 小灵通/固话充值卡 |
| 14 | 移动手机充值卡 |
| 15 | 联通手机充值卡 |
+--------+-------------------+
14 rows in set (0.00 sec)
mysql> # 观察商品表与栏目的关系
mysql> select count(*) ,cat_id from goods group by cat_id;
+----------+--------+
| count(*) | cat_id |
+----------+--------+
| 1 | 2 |
| 15 | 3 |
| 4 | 4 |
| 1 | 5 |
| 3 | 8 |
| 2 | 11 |
| 2 | 13 |
| 2 | 14 |
| 2 | 15 |
+----------+--------+
9 rows in set (0.00 sec)
mysql> #别把14个栏目都取出来,只把下面有商品的栏目取出来
mysql> #取 栏目表,且只取下面有商品的栏目
mysql> # 思考: 什么样的表,叫做下面有商品?
mysql> # 答:设某栏目cat_id为N,则select * from goods where cat_id=N
mysql> # 能取出数据,则说明该栏目有商品
mysql>
mysql> select cat_id,cat_name from category
-> where exists (select * from goods where goods.cat_id=category.cat_id);
+--------+-------------------+
| cat_id | cat_name |
+--------+-------------------+
| 2 | CDMA手机 |
| 3 | GSM手机 |
| 4 | 3G手机 |
| 5 | 双模手机 |
| 8 | 耳机 |
| 11 | 读卡器和内存卡 |
| 13 | 小灵通/固话充值卡 |
| 14 | 移动手机充值卡 |
| 15 | 联通手机充值卡 |
+--------+-------------------+
9 rows in set (0.00 sec)
mysql> #建表时,列后面 not null default '',default 0,这是什么意思
mysql> #答:就是让这个列值不为NULL,如果某个列确实没填值,也有默认值,也不为null
mysql> # 为什么不希望让列的值为null呢?
mysql> create table test9 (
-> sname varchar(20)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.20 sec)
mysql> insert into test9
-> values
-> ('lisi','wangwu','null');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into test9
-> values
-> ('lisi'),('wangwu',('null');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql> insert into test9
-> values
-> ('lisi'),('wangwu'),('null');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test9;
+--------+
| sname |
+--------+
| lisi |
| wangwu |
| null |
+--------+
3 rows in set (0.00 sec)
mysql> insert into test9 values (NULL);
Query OK, 1 row affected (0.02 sec)
mysql> select * from test9;
+--------+
| sname |
+--------+
| lisi |
| wangwu |
| null |
| NULL |
+--------+
4 rows in set (0.00 sec)
mysql> delete from test9 where sname='null';
Query OK, 1 row affected (0.03 sec)
mysql> select * from test9;
+--------+
| sname |
+--------+
| lisi |
| wangwu |
| NULL |
+--------+
3 rows in set (0.00 sec)
mysql> #查询出用户名不为null的行
mysql> select * from test9 where sname!=null;
Empty set (0.00 sec)
mysql> #lisi wangwu没查出来? 为什么
mysql> #查sname为null的行
mysql> select * from test9 where sname=null;
Empty set (0.00 sec)
mysql> #又是空
mysql> select 2>1;
+-----+
| 2>1 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> select 2<1;
+-----+
| 2<1 |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> select 39<60;
+-------+
| 39<60 |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select 'lisi'=null;
+-------------+
| 'lisi'=null |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
mysql> # null为假, lisi=null是假
mysql> select null=null;
+-----------+
| null=null |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> # null=null,还是null,还是假
mysql> select null!=null;
+------------+
| null!=null |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> # null 是空,四大皆空的空.
mysql> # null的比较需要用特殊的运算符 is null ,is not null
mysql> select * from sname where sname is not null;
ERROR 1146 (42S02): Table 'test.sname' doesn't exist
mysql> select * from test9 where sname is not null;
+--------+
| sname |
+--------+
| lisi |
| wangwu |
+--------+
2 rows in set (0.00 sec)
mysql> select * from test9 where sname is null;
+-------+
| sname |
+-------+
| NULL |
+-------+
1 row in set (0.00 sec)
mysql> exit
