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

/**

燕十八 公益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)

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