跟燕十八学习PHP-第二十四天-mysql查询模型(非常重要)-CSDN博客

本文链接：https://blog.csdn.net/phperlang/article/details/8112605

/**
燕十八公益PHP培训
课堂地址:YY频道88354001
学习社区:www.zixue.it
**/

分析商城的表并建立类似的小型表

商品表
goods_id
cat_id
goods_sn
goods_name
click_count
goods_number
market_price
shop_price
add_time
is_best
is_new
is_hot

#创建一个仿ecshop的迷你商品表
CREATE TABLE `goods` (
`goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`click_count` int(10) unsigned NOT NULL DEFAULT '0',
`goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',
`market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
`is_best` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#把ecshop中的商品表数据批量导入测试goods表
insert into test.goods
select

goods_id,cat_id,goods_sn,goods_name,click_count,goods_number,market_price,shop_price,add_tim

e,is_best,is_new,is_hot from gyshop.goods;

mysql> # 接下来,把ec的商品表的内容导入到这张goods里,供练习用.
mysql> insert into test.goods
-> select

goods_id,cat_id,goods_sn,goods_name,click_count,goods_numer,market_price,shop_price,add_time

,is_best,is_new,is_hot from gyshop.goods;
ERROR 1054 (42S22): Unknown column 'goods_numer' in 'field list'
mysql> insert into test.goods
-> select

goods_id,cat_id,goods_sn,goods_name,click_count,goods_number,market_price,shop_price,add_tim

e,is_best,is_new,is_hot from gyshop.goods;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0

mysql> #查询练习
mysql> #查询商品主键是32的商品
mysql> select goods_id,goods_name,shop_price from goods where goods_id=32;
+----------+------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------+------------+
| 32 | 诺基亚N85 | 3010.00 |
+----------+------------+------------+
1 row in set (0.06 sec)

mysql> #查出不属于第3个栏目的所有商品
mysql> #即cat_id不等于3
mysql> select goods_id,cat_id,goods_name from goods where cat_id!=3;
+----------+--------+------------------------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------------------------+
| 1 | 4 | KD876 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 3 | 8 | 诺基亚原装5800耳机 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 6 | 11 | 胜创KINGMAX内存卡 |
| 7 | 8 | 诺基亚N85原装立体声耳机HS-82 |
| 14 | 4 | 诺基亚5800XM |
| 16 | 2 | 恒基伟业G101 |
| 18 | 4 | 夏新T5 |
| 23 | 5 | 诺基亚N96 |
| 25 | 13 | 小灵通/固话50元充值卡 |
| 26 | 13 | 小灵通/固话20元充值卡 |
| 27 | 15 | 联通100元充值卡 |
| 28 | 15 | 联通50元充值卡 |
| 29 | 14 | 移动100元充值卡 |
| 30 | 14 | 移动20元充值卡 |
| 33 | 4 | 金立910浪漫镶钻手机 |
+----------+--------+------------------------------+
17 rows in set (0.05 sec)

燕十八老师太幽默了, 昨天的视频如下:

http://www.tudou.com/programs/view/xBe0ysJFM_k/