mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use gyshop
Database changed
mysql> #查看goods表的建表语句
mysql> show create table goods;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| goods | 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 '',
`goods_name_style` varchar(60) NOT NULL DEFAULT '+',
`click_count` int(10) unsigned NOT NULL DEFAULT '0',
`brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`provider_name` varchar(100) NOT NULL DEFAULT '',
`goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_weight` decimal(10,3) unsigned NOT NULL DEFAULT '0.000',
`market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`promote_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`promote_start_date` int(11) unsigned NOT NULL DEFAULT '0',
`promote_end_date` int(11) unsigned NOT NULL DEFAULT '0',
`warn_number` tinyint(3) unsigned NOT NULL DEFAULT '1',
`keywords` varchar(255) NOT NULL DEFAULT '',
`goods_brief` varchar(255) NOT NULL DEFAULT '',
`goods_desc` text NOT NULL,
`goods_thumb` varchar(255) NOT NULL DEFAULT '',
`goods_img` varchar(255) NOT NULL DEFAULT '',
`original_img` varchar(255) NOT NULL DEFAULT '',
`is_real` tinyint(3) unsigned NOT NULL DEFAULT '1',
`extension_code` varchar(30) NOT NULL DEFAULT '',
`is_on_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
`is_alone_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
`is_shipping` tinyint(1) unsigned NOT NULL DEFAULT '0',
`integral` int(10) unsigned NOT NULL DEFAULT '0',
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
`sort_order` smallint(4) unsigned NOT NULL DEFAULT '100',
`is_delete` tinyint(1) 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',
`is_promote` tinyint(1) unsigned NOT NULL DEFAULT '0',
`bonus_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`last_update` int(10) unsigned NOT NULL DEFAULT '0',
`goods_type` smallint(5) unsigned NOT NULL DEFAULT '0',
`seller_note` varchar(255) NOT NULL DEFAULT '',
`give_integral` int(11) NOT NULL DEFAULT '-1',
`rank_integral` int(11) NOT NULL DEFAULT '-1',
`suppliers_id` smallint(5) unsigned DEFAULT NULL,
`is_check` tinyint(1) unsigned DEFAULT NULL,
PRIMARY KEY (`goods_id`),
KEY `goods_sn` (`goods_sn`),
KEY `cat_id` (`cat_id`),
KEY `last_update` (`last_update`),
KEY `brand_id` (`brand_id`),
KEY `goods_weight` (`goods_weight`),
KEY `promote_end_date` (`promote_end_date`),
KEY `promote_start_date` (`promote_start_date`),
KEY `goods_number` (`goods_number`),
KEY `sort_order` (`sort_order`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account |
| class |
| m1 |
| member |
| salary |
| stu |
| test |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
+----------------+
13 rows in set (0.45 sec)
mysql> 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;
Query OK, 0 rows affected (0.13 sec)
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.09 sec)
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_time,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)
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.03 sec)
mysql> #本店价格高于3000元的商品
mysql> select goods_id,goods_name,shop_price where shop_price>3000;
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 'where shop_price>3000' at line 1
mysql> select goods_id,goods_name,shop_price from goods where shop_price>3000;
+----------+----------------+------------+
| 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> #查询出本店价低于或等于100元的商品
mysql> select goods_id,goods_name,shop_price from goods where shop_price<=100;
+----------+------------------------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------------------------+------------+
| 4 | 诺基亚N85原装充电器 | 58.00 |
| 3 | 诺基亚原装5800耳机 | 68.00 |
| 5 | 索爱原装M2卡读卡器 | 20.00 |
| 6 | 胜创KINGMAX内存卡 | 42.00 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 25 | 小灵通/固话50元充值卡 | 48.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 27 | 联通100元充值卡 | 95.00 |
| 28 | 联通50元充值卡 | 45.00 |
| 29 | 移动100元充值卡 | 90.00 |
| 30 | 移动20元充值卡 | 18.00 |
+----------+------------------------------+------------+
11 rows in set (0.01 sec)
mysql> #取出第4栏目和第11栏目的商品,不能用or
mysql> select goods_id,cat_id,goods_name from goods
-> where cat_id in (4,11); #cat_id 在4,11这个集合里都满足.
+----------+--------+---------------------+
| goods_id | cat_id | goods_name |
+----------+--------+---------------------+
| 1 | 4 | KD876 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 6 | 11 | 胜创KINGMAX内存卡 |
| 14 | 4 | 诺基亚5800XM |
| 18 | 4 | 夏新T5 |
| 33 | 4 | 金立910浪漫镶钻手机 |
+----------+--------+---------------------+
6 rows in set (0.00 sec)
mysql> #取出价格>=100元,且<=500元.不让用and
mysql> select goods_id,goods_name,shop_price
-> from goods
-> where shop_price
-> between 100 and 500;
+----------+------------------------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------------------------+------------+
| 7 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 8 | 飞利浦9@9v | 399.00 |
| 20 | 三星BC01 | 280.00 |
+----------+------------------------------+------------+
3 rows in set (0.00 sec)
mysql> #取出不在第3个栏目且不在第11个栏目的商品,用not in 和and 分别实现
mysql> #即栏目的id,是3的不要,是11的也不要.
mysql> select goods_id,cat_id,goods_name from goods
-> where cat_id not in (3,11);
+----------+--------+------------------------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------------------------+
| 1 | 4 | KD876 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 3 | 8 | 诺基亚原装5800耳机 |
| 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浪漫镶钻手机 |
+----------+--------+------------------------------+
15 rows in set (0.00 sec)
mysql> # 用and 来实现,$cat_id!=3&&$cat_id!=11
mysql> select goods_id,cat_id,goods_name
-> from goods
-> where cat_id!=3 and cat_id!=11;
+----------+--------+------------------------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------------------------+
| 1 | 4 | KD876 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 3 | 8 | 诺基亚原装5800耳机 |
| 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浪漫镶钻手机 |
+----------+--------+------------------------------+
15 rows in set (0.00 sec)
mysql> #极端人格分裂购物者
mysql> #第么找特便宜的在[100,300]之间的,要么买贵的,在[4000,5000]之间的.
mysql> #用php是这样 if(($price>=100&&$price<=300) ||($price>=4000&&$price<=5000))
mysql> select goods_id,goods_name,shop_price
-> from goods
-> where shop_price>=100 and shop_price<=300 or shop_price>=4000 and shop_price<=5000;
+----------+------------------------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------------------------+------------+
| 7 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 20 | 三星BC01 | 280.00 |
+----------+------------------------------+------------+
2 rows in set (0.00 sec)
mysql> select goods_id,goods_name,shop_price
-> from goods
-> where shop_price>=100 and shop_price<=300 or shop_price>=3000 and shop_price<=5000;
+----------+------------------------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------------------------+------------+
| 7 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 20 | 三星BC01 | 280.00 |
| 23 | 诺基亚N96 | 3700.00 |
| 32 | 诺基亚N85 | 3010.00 |
+----------+------------------------------+------------+
4 rows in set (0.00 sec)
mysql> #取出第3个栏目下,价格小于1000或者>3000,同时点击量大于等于5的商品
mysql> select goods_id,goods_name,cat_id,shop_price,click_count
-> from goods
-> where cat_id=3 and shop_price<1000 or shop_price>3000 and click_count>=5;
+----------+----------------+--------+------------+-------------+
| goods_id | goods_name | cat_id | shop_price | click_count |
+----------+----------------+--------+------------+-------------+
| 8 | 飞利浦9@9v | 3 | 399.00 | 9 |
| 12 | 摩托罗拉A810 | 3 | 983.00 | 13 |
| 15 | 摩托罗拉A810 | 3 | 788.00 | 8 |
| 19 | 三星SGH-F258 | 3 | 858.00 | 7 |
| 20 | 三星BC01 | 3 | 280.00 | 14 |
| 22 | 多普达Touch HD | 3 | 5999.00 | 15 |
| 23 | 诺基亚N96 | 5 | 3700.00 | 17 |
| 32 | 诺基亚N85 | 3 | 3010.00 | 9 |
+----------+----------------+--------+------------+-------------+
8 rows in set (0.00 sec)
mysql> select goods_id,goods_name,cat_id,shop_price,click_count
-> from goods
-> where cat_id=3 and (shop_price<1000 or shop_price>3000) and click_count>=5;
+----------+----------------+--------+------------+-------------+
| goods_id | goods_name | cat_id | shop_price | click_count |
+----------+----------------+--------+------------+-------------+
| 8 | 飞利浦9@9v | 3 | 399.00 | 9 |
| 12 | 摩托罗拉A810 | 3 | 983.00 | 13 |
| 15 | 摩托罗拉A810 | 3 | 788.00 | 8 |
| 19 | 三星SGH-F258 | 3 | 858.00 | 7 |
| 20 | 三星BC01 | 3 | 280.00 | 14 |
| 22 | 多普达Touch HD | 3 | 5999.00 | 15 |
| 32 | 诺基亚N85 | 3 | 3010.00 | 9 |
+----------+----------------+--------+------------+-------------+
7 rows in set (0.00 sec)
mysql> #取出1号栏目下的商品
mysql> select goods_id,cat_id,goods_name from goods
-> where cat_id=1;
Empty set (0.00 sec)
mysql> #查出名称以诺基亚开头的商品
mysql> #例诺基亚N96 ,诺基亚原装充电器
mysql> #模糊查询可以做到
mysql> select goods_id,goods_name from goods
-> where goods_name like '诺基亚%';
+----------+------------------------------+
| goods_id | goods_name |
+----------+------------------------------+
| 4 | 诺基亚N85原装充电器 |
| 3 | 诺基亚原装5800耳机 |
| 7 | 诺基亚N85原装立体声耳机HS-82 |
| 9 | 诺基亚E66 |
| 13 | 诺基亚5320 XpressMusic |
| 14 | 诺基亚5800XM |
| 23 | 诺基亚N96 |
| 32 | 诺基亚N85 |
+----------+------------------------------+
8 rows in set (0.02 sec)
mysql> select goods_id,goods_name from goods
-> where goods_name like '诺基亚N85%';
+----------+------------------------------+
| goods_id | goods_name |
+----------+------------------------------+
| 4 | 诺基亚N85原装充电器 |
| 7 | 诺基亚N85原装立体声耳机HS-82 |
| 32 | 诺基亚N85 |
+----------+------------------------------+
3 rows in set (0.05 sec)
mysql> #我去逛商场时,看了一款手机,诺基亚Nxx系列.
mysql> #具体型号记不清了,但是N后面是2字.
mysql> #用"_"匹配任意单个字符
mysql> select goods_id,goods_name from goods
-> where goods_name like '诺基亚N__';
+----------+------------+
| goods_id | goods_name |
+----------+------------+
| 23 | 诺基亚N96 |
| 32 | 诺基亚N85 |
+----------+------------+
2 rows in set (0.00 sec)
mysql> #重要:理解查询的模型
mysql> select goods_id,goods_name,shop_price from goods
-> ;
+----------+------------------------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------------------------+------------+
| 1 | KD876 | 1388.00 |
| 4 | 诺基亚N85原装充电器 | 58.00 |
| 3 | 诺基亚原装5800耳机 | 68.00 |
| 5 | 索爱原装M2卡读卡器 | 20.00 |
| 6 | 胜创KINGMAX内存卡 | 42.00 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 8 | 飞利浦9@9v | 399.00 |
| 9 | 诺基亚E66 | 2298.00 |
| 10 | 索爱C702c | 1328.00 |
| 11 | 索爱C702c | 1300.00 |
| 12 | 摩托罗拉A810 | 983.00 |
| 13 | 诺基亚5320 XpressMusic | 1311.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 15 | 摩托罗拉A810 | 788.00 |
| 16 | 恒基伟业G101 | 823.33 |
| 17 | 夏新N7 | 2300.00 |
| 18 | 夏新T5 | 2878.00 |
| 19 | 三星SGH-F258 | 858.00 |
| 20 | 三星BC01 | 280.00 |
| 21 | 金立 A30 | 2000.00 |
| 22 | 多普达Touch HD | 5999.00 |
| 23 | 诺基亚N96 | 3700.00 |
| 24 | P806 | 2000.00 |
| 25 | 小灵通/固话50元充值卡 | 48.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 27 | 联通100元充值卡 | 95.00 |
| 28 | 联通50元充值卡 | 45.00 |
| 29 | 移动100元充值卡 | 90.00 |
| 30 | 移动20元充值卡 | 18.00 |
| 31 | 摩托罗拉E8 | 1337.00 |
| 32 | 诺基亚N85 | 3010.00 |
| 33 | 金立910浪漫镶钻手机 | 1233.00 |
+----------+------------------------------+------------+
32 rows in set (0.00 sec)
mysql> select goods_id,goods_name,shop_price where shop_price > 5000;
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 'where shop_price > 5000' at line 1
mysql> select goods_id,goods_name,shop_price from goods where shop_price > 5000;
+----------+----------------+------------+
| goods_id | goods_name | shop_price |
+----------+----------------+------------+
| 22 | 多普达Touch HD | 5999.00 |
+----------+----------------+------------+
1 row in set (0.00 sec)
mysql> #把列看成变量, 把where后面看成PHP中if(exp)里的exp 表达式
mysql> #哪些行,被取出来? ---哪一行能让exp为真,哪一行就能取出来
mysql> #判断下一行取出什么?
mysql> select goods_id,goods_name from goods
-> where 1>2;
Empty set (0.00 sec)
mysql> #where 1是什么意思?
mysql> select goods_id,goods_name from goods where 1;
+----------+------------------------------+
| goods_id | goods_name |
+----------+------------------------------+
| 1 | KD876 |
| 4 | 诺基亚N85原装充电器 |
| 3 | 诺基亚原装5800耳机 |
| 5 | 索爱原装M2卡读卡器 |
| 6 | 胜创KINGMAX内存卡 |
| 7 | 诺基亚N85原装立体声耳机HS-82 |
| 8 | 飞利浦9@9v |
| 9 | 诺基亚E66 |
| 10 | 索爱C702c |
| 11 | 索爱C702c |
| 12 | 摩托罗拉A810 |
| 13 | 诺基亚5320 XpressMusic |
| 14 | 诺基亚5800XM |
| 15 | 摩托罗拉A810 |
| 16 | 恒基伟业G101 |
| 17 | 夏新N7 |
| 18 | 夏新T5 |
| 19 | 三星SGH-F258 |
| 20 | 三星BC01 |
| 21 | 金立 A30 |
| 22 | 多普达Touch HD |
| 23 | 诺基亚N96 |
| 24 | P806 |
| 25 | 小灵通/固话50元充值卡 |
| 26 | 小灵通/固话20元充值卡 |
| 27 | 联通100元充值卡 |
| 28 | 联通50元充值卡 |
| 29 | 移动100元充值卡 |
| 30 | 移动20元充值卡 |
| 31 | 摩托罗拉E8 |
| 32 | 诺基亚N85 |
| 33 | 金立910浪漫镶钻手机 |
+----------+------------------------------+
32 rows in set (0.00 sec)
mysql> #第二点:把列看成变量
mysql> #既然是变量,变量之间就可以运算
mysql> #取出商品id,商品名,本店价比市场价省的钱.
mysql> select goods_id,goods_name,market_price-shop_price
-> from goods
-> where 1;
+----------+------------------------------+-------------------------+
| goods_id | goods_name | market_price-shop_price |
+----------+------------------------------+-------------------------+
| 1 | KD876 | 277.60 |
| 4 | 诺基亚N85原装充电器 | 11.60 |
| 3 | 诺基亚原装5800耳机 | 13.60 |
| 5 | 索爱原装M2卡读卡器 | 4.00 |
| 6 | 胜创KINGMAX内存卡 | 8.40 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 20.00 |
| 8 | 飞利浦9@9v | 79.79 |
| 9 | 诺基亚E66 | 459.60 |
| 10 | 索爱C702c | 265.60 |
| 11 | 索爱C702c | -1300.00 |
| 12 | 摩托罗拉A810 | 196.60 |
| 13 | 诺基亚5320 XpressMusic | 262.20 |
| 14 | 诺基亚5800XM | 525.00 |
| 15 | 摩托罗拉A810 | 157.60 |
| 16 | 恒基伟业G101 | 164.67 |
| 17 | 夏新N7 | 460.00 |
| 18 | 夏新T5 | 575.60 |
| 19 | 三星SGH-F258 | 171.60 |
| 20 | 三星BC01 | 56.00 |
| 21 | 金立 A30 | 400.00 |
| 22 | 多普达Touch HD | 1199.80 |
| 23 | 诺基亚N96 | 740.00 |
| 24 | P806 | 400.00 |
| 25 | 小灵通/固话50元充值卡 | 9.59 |
| 26 | 小灵通/固话20元充值卡 | 3.80 |
| 27 | 联通100元充值卡 | 5.00 |
| 28 | 联通50元充值卡 | 5.00 |
| 29 | 移动100元充值卡 | -90.00 |
| 30 | 移动20元充值卡 | 3.00 |
| 31 | 摩托罗拉E8 | 267.39 |
| 32 | 诺基亚N85 | 602.00 |
| 33 | 金立910浪漫镶钻手机 | 246.60 |
+----------+------------------------------+-------------------------+
32 rows in set (0.00 sec)
mysql> #表里面原来没有"market_price-shop_price"的列
mysql> #这一列其实是一个运算结果,术语叫"广义投影",
mysql> #你就把列看成变量,来运算即可.
mysql> #列的运算结果,可以当成列看,还可以起个列别名
mysql> select goods_id,goods_name,(market_price-shop_price) as discount
-> from goods
-> where cat_id!=3;
+----------+------------------------------+----------+
| goods_id | goods_name | discount |
+----------+------------------------------+----------+
| 1 | KD876 | 277.60 |
| 4 | 诺基亚N85原装充电器 | 11.60 |
| 3 | 诺基亚原装5800耳机 | 13.60 |
| 5 | 索爱原装M2卡读卡器 | 4.00 |
| 6 | 胜创KINGMAX内存卡 | 8.40 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 20.00 |
| 14 | 诺基亚5800XM | 525.00 |
| 16 | 恒基伟业G101 | 164.67 |
| 18 | 夏新T5 | 575.60 |
| 23 | 诺基亚N96 | 740.00 |
| 25 | 小灵通/固话50元充值卡 | 9.59 |
| 26 | 小灵通/固话20元充值卡 | 3.80 |
| 27 | 联通100元充值卡 | 5.00 |
| 28 | 联通50元充值卡 | 5.00 |
| 29 | 移动100元充值卡 | -90.00 |
| 30 | 移动20元充值卡 | 3.00 |
| 33 | 金立910浪漫镶钻手机 | 246.60 |
+----------+------------------------------+----------+
17 rows in set (0.01 sec)
mysql> #顾客说,帮我查查你家的货
mysql> #查出本店价比市场省的钱,而且!!! ,而且省200以上的商品.
mysql> select goods_id,goods_name,(market_price-shop_price) as discount
-> from goods
-> where (market_price-shop_price) > 200;
+----------+------------------------+----------+
| goods_id | goods_name | discount |
+----------+------------------------+----------+
| 1 | KD876 | 277.60 |
| 9 | 诺基亚E66 | 459.60 |
| 10 | 索爱C702c | 265.60 |
| 13 | 诺基亚5320 XpressMusic | 262.20 |
| 14 | 诺基亚5800XM | 525.00 |
| 17 | 夏新N7 | 460.00 |
| 18 | 夏新T5 | 575.60 |
| 21 | 金立 A30 | 400.00 |
| 22 | 多普达Touch HD | 1199.80 |
| 23 | 诺基亚N96 | 740.00 |
| 24 | P806 | 400.00 |
| 31 | 摩托罗拉E8 | 267.39 |
| 32 | 诺基亚N85 | 602.00 |
| 33 | 金立910浪漫镶钻手机 | 246.60 |
+----------+------------------------+----------+
14 rows in set (0.00 sec)
mysql> #有同学说,你这个似乎麻烦了,discount>200不就行了吗
mysql> select goods_id,goods_name,(market_price-shop_price) as discount
-> from goods
-> where discount>200;
ERROR 1054 (42S22): Unknown column 'discount' in 'where clause'
mysql> # 一道面试题
mysql> create table mian (
-> num int
-> );
Query OK, 0 rows affected (0.44 sec)
mysql> insert into mian
-> values
-> (3),(12),(15),(25),(23),(29),(34),(37),(32),(45),(48),(52);
Query OK, 12 rows affected (0.11 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select * from mian;
+------+
| num |
+------+
| 3 |
| 12 |
| 15 |
| 25 |
| 23 |
| 29 |
| 34 |
| 37 |
| 32 |
| 45 |
| 48 |
| 52 |
+------+
12 rows in set (0.00 sec)
mysql> #把num一定当成变量看,因此num/10取整,再乘以10
mysql> update mian set num=floor(num/10)*10
-> where num>=20 and num<=39;
Query OK, 6 rows affected (0.08 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from mian;
+------+
| num |
+------+
| 3 |
| 12 |
| 15 |
| 20 |
| 20 |
| 20 |
| 30 |
| 30 |
| 30 |
| 45 |
| 48 |
| 52 |
+------+
12 rows in set (0.00 sec)
mysql> select goods_id,goods_name,substring(goods_name,3) from goods
-> where goods_name like '诺基亚%';
+----------+------------------------------+--------------------------+
| goods_id | goods_name | substring(goods_name,3) |
+----------+------------------------------+--------------------------+
| 4 | 诺基亚N85原装充电器 | 亚N85原装充电器 |
| 3 | 诺基亚原装5800耳机 | 亚原装5800耳机 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 亚N85原装立体声耳机HS-82 |
| 9 | 诺基亚E66 | 亚E66 |
| 13 | 诺基亚5320 XpressMusic | 亚5320 XpressMusic |
| 14 | 诺基亚5800XM | 亚5800XM |
| 23 | 诺基亚N96 | 亚N96 |
| 32 | 诺基亚N85 | 亚N85 |
+----------+------------------------------+--------------------------+
8 rows in set (0.00 sec)
mysql> select goods_id,goods_name,substring(goods_name,4) from goods
-> where goods_name like '诺基亚%';
+----------+------------------------------+-------------------------+
| goods_id | goods_name | substring(goods_name,4) |
+----------+------------------------------+-------------------------+
| 4 | 诺基亚N85原装充电器 | N85原装充电器 |
| 3 | 诺基亚原装5800耳机 | 原装5800耳机 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | N85原装立体声耳机HS-82 |
| 9 | 诺基亚E66 | E66 |
| 13 | 诺基亚5320 XpressMusic | 5320 XpressMusic |
| 14 | 诺基亚5800XM | 5800XM |
| 23 | 诺基亚N96 | N96 |
| 32 | 诺基亚N85 | N85 |
+----------+------------------------------+-------------------------+
8 rows in set (0.00 sec)
mysql> select goods_id,goods_name,concat('HTC',substring(goods_name,3)) from goods
-> where goods_name like '诺基亚%';
+----------+------------------------------+---------------------------------------+
| goods_id | goods_name | concat('HTC',substring(goods_name,3)) |
+----------+------------------------------+---------------------------------------+
| 4 | 诺基亚N85原装充电器 | HTC亚N85原装充电器 |
| 3 | 诺基亚原装5800耳机 | HTC亚原装5800耳机 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | HTC亚N85原装立体声耳机HS-82 |
| 9 | 诺基亚E66 | HTC亚E66 |
| 13 | 诺基亚5320 XpressMusic | HTC亚5320 XpressMusic |
| 14 | 诺基亚5800XM | HTC亚5800XM |
| 23 | 诺基亚N96 | HTC亚N96 |
| 32 | 诺基亚N85 | HTC亚N85 |
+----------+------------------------------+---------------------------------------+
8 rows in set (0.00 sec)
mysql> select goods_id,goods_name,concat('HTC',substring(goods_name,4)) from goods
-> where goods_name like '诺基亚%';
+----------+------------------------------+---------------------------------------+
| goods_id | goods_name | concat('HTC',substring(goods_name,4)) |
+----------+------------------------------+---------------------------------------+
| 4 | 诺基亚N85原装充电器 | HTCN85原装充电器 |
| 3 | 诺基亚原装5800耳机 | HTC原装5800耳机 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | HTCN85原装立体声耳机HS-82 |
| 9 | 诺基亚E66 | HTCE66 |
| 13 | 诺基亚5320 XpressMusic | HTC5320 XpressMusic |
| 14 | 诺基亚5800XM | HTC5800XM |
| 23 | 诺基亚N96 | HTCN96 |
| 32 | 诺基亚N85 | HTCN85 |
+----------+------------------------------+---------------------------------------+
8 rows in set (0.00 sec)
mysql> exit
