时间：2014年3月29日13:43:46SQL查询表达式
mysql> use ecshop;
Database changed
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> #查看建表语句：
mysql> show create table goods;
mysql> use test;
Database changed
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.00‘,
-> `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.04 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.01 sec)
mysql> #导入商品表的内容32的商品：
mysql> select goods_id,goods_name,shop_price from goods where goods_id=32;
+----------+------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------+------------+
| 32 | 诺基亚3栏目的所有商品
mysql> #即栏目N85原装充电器 5800耳机 M2卡读卡器 KINGMAX内存卡 N85原装立体声耳机5800XM |
| 16 | 2 | 恒基伟业T5 |
| 23 | 5 | 诺基亚/固话|
| 26 | 13 | 小灵通20元充值卡 100元充值卡 50元充值卡 100元充值卡 20元充值卡 not in 效率不高
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 | 诺基亚|
| 3 | 8 | 诺基亚原装|
| 5 | 11 | 索爱原装|
| 6 | 11 | 胜创|
| 7 | 8 | 诺基亚HS-82 |
| 14 | 4 | 诺基亚G101 |
| 18 | 4 | 夏新N96 |
| 25 | 13 | 小灵通50元充值卡 /固话|
| 27 | 15 | 联通|
| 28 | 15 | 联通|
| 29 | 14 | 移动|
| 30 | 14 | 移动|
+----------+--------+------------------------------+
16 rows in set (0.00 sec)
mysql> #<>不等于 等价 3000元的商品Touch HD | 5999.00 |
| 23 | 5 | 诺基亚N85 | 3010.00 |
+----------+--------+----------------+------------+
3 rows in set (0.00 sec)
mysql> #查询本店价格低于或等于N85原装充电器 5800耳机 M2卡读卡器 KINGMAX内存卡 N85原装立体声耳机/固话| 13 | 48.00 |
| 26 | 小灵通20元充值卡 100元充值卡 50元充值卡 100元充值卡 20元充值卡 11个栏目的商品，不能用11个栏目的商品，不能用11个栏目的商品，不能用M2卡读卡器 KINGMAX内存卡 5800XM |
| 18 | 4 | 夏新100和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 | 诺基亚HS-82 | 100.00 |
| 8 | 飞利浦BC01 | 280.00 |
+----------+------------------------------+------------+
3 rows in set (0.00 sec)
mysql> #between and 在两个数之间
mysql> #包括边界值
mysql>
mysql> #取出不属于第11栏目的商品not in分别实现N85原装充电器 5800耳机 N85原装立体声耳机5800XM | 2625.00 |
| 16 | 2 | 恒基伟业T5 | 2878.00 |
| 23 | 5 | 诺基亚/固话| 48.00 |
| 26 | 13 | 小灵通20元充值卡 100元充值卡 50元充值卡 100元充值卡 20元充值卡 N85原装充电器 5800耳机 N85原装立体声耳机5800XM | 2625.00 |
| 16 | 2 | 恒基伟业T5 | 2878.00 |
| 23 | 5 | 诺基亚/固话| 48.00 |
| 26 | 13 | 小灵通20元充值卡 100元充值卡 50元充值卡 100元充值卡 20元充值卡 100且小于4000且小于BC01 | 280.00 |
+----------+--------+------------+------------+
1 row in set (0.00 sec)
mysql> select goods_id,cat_id,goods_name,shop_price from goods where (shop_price>=100 and shop_price <300)>3000 and shop_price <5000);<>
+----------+--------+------------------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+------------------------------+------------+
| 7 | 8 | 诺基亚HS-82 | 100.00 |
| 20 | 3 | 三星N96 | 3700.00 |
| 32 | 3 | 诺基亚or 的高，出于稳妥使用括号
mysql> #取出第<1000<>或>5的系列商品
mysql> select goods_id,goods_name,shop_price from goods
-> where (shop_price<1000>300) and click_count>5;
+----------+------------------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------------------+------------+
| 1 | KD876 | 1388.00 |
| 8 | 飞利浦E66 | 2298.00 |
| 10 | 索爱A810 | 983.00 |
| 13 | 诺基亚5800XM | 2625.00 |
| 15 | 摩托罗拉SGH-F258 | 858.00 |
| 20 | 三星Touch HD | 5999.00 |
| 23 | 诺基亚N85 | 3010.00 |
+----------+------------------------+------------+
14 rows in set (0.00 sec)
mysql> select goods_id,goods_name,shop_price,click_count from goods
-> where (shop_price<1000>300) and click_count>5;
+----------+------------------------+------------+-------------+
| goods_id | goods_name | shop_price | click_count |
+----------+------------------------+------------+-------------+
| 1 | KD876 | 1388.00 | 7 |
| 8 | 飞利浦E66 | 2298.00 | 21 |
| 10 | 索爱A810 | 983.00 | 14 |
| 13 | 诺基亚5800XM | 2625.00 | 6 |
| 15 | 摩托罗拉SGH-F258 | 858.00 | 7 |
| 20 | 三星Touch HD | 5999.00 | 15 |
| 23 | 诺基亚N85 | 3010.00 | 9 |
+----------+------------------------+------------+-------------+
14 rows in set (0.00 sec)
mysql> select goods_id,cat_id,goods_name,shop_price,click_count from goods where
-> cat_id=3 and (shop_price <1000>3000) and click_count>5;
+----------+--------+----------------+------------+-------------+
| goods_id | cat_id | goods_name | shop_price | click_count |
+----------+--------+----------------+------------+-------------+
| 8 | 3 | 飞利浦A810 | 983.00 | 14 |
| 15 | 3 | 摩托罗拉SGH-F258 | 858.00 | 7 |
| 20 | 3 | 三星Touch HD | 5999.00 | 15 |
| 32 | 3 | 诺基亚1个栏目下面的商品:1栏目下面没商品)
mysql>
mysql> select goods_id,cat_id,goods_name from goods where cat_id = 1;
Empty set (0.00 sec)
mysql> #取出名字以<span style=\"\\"font-family:times\">"诺基亚<span style=\"\\"font-family:times\">"开头的商品
mysql> select goods_id,cat_id,goods_name,shop_price from goods where goods_name like ‘诺基亚N85原装充电器 5800耳机 N85原装立体声耳机E66 | 2298.00 |
| 13 | 3 | 诺基亚5800XM | 2625.00 |
| 23 | 5 | 诺基亚N85 | 3010.00 |
+----------+--------+------------------------------+------------+
8 rows in set (0.03 sec)
mysql> #like 模糊查询，
mysql> # % 匹配任意字符
mysql> # _ 匹配单个字节
mysql>
mysql> #取出名字为<span style=\"\\"font-family:times\">"诺基亚<span style=\"\\"font-family:times\">Nxx"的手机
mysql> select goods_id,cat_id,goods_name,shop_price from goods
-> where goods_name like ‘诺基亚N96 | 3700.00 |
| 32 | 3 | 诺基亚<span style=\"\\"font-family:times\">N85 | 3010.00 |
+----------+--------+------------+------------+
2 rows in set (0.00 sec)
mysql> #下划线，匹配任意单个字符
本文出自 “杜国栋个人PHP学习博文” 博客，请务必保留此出处http://duguodong.blog.51cto.com/7667978/1386749
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
