MySQL查询语句（一）——where语句

select 5种子句介绍
where 条件语句
group by 分组
having 筛选
order by 排序
limit 限制结果条数

创建一个商场商品列表

mysql> use Sudley;
Database changed
mysql> create table goods(
    -> goods_id mediumint(8) unsigned not null auto_increment,
    -> cat_id smallint(5) 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.00 sec)
mysql> desc goods;
+--------------+------------------------+------+-----+---------+----------------+
| Field        | Type                   | Null | Key | Default | Extra          |
+--------------+------------------------+------+-----+---------+----------------+
| goods_id     | mediumint(8) unsigned  | NO   | PRI | NULL    | auto_increment |
| cat_id       | smallint(5)            | 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)

把shop表中数据批量导入测试goods表

上面在数据库Sudley下面创建了一张goods的表格，下面由shop数据库负责数据到Sudley数据库
mysql> insert into Sudley.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 shop.goods

mysql> insert into Sudley.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 shop.goods;

where查询练习

where常用运算符：
比较预算符

运算符	说明	运算符	说明
<	小于	!=或<>	不等于
<=	小于等于	>	大于
=	等于	>=	大于等于
in	在某个集合内	between…and	在某个范围内

逻辑运算符

运算符	说明
NOT或！	逻辑非
AND或&&	逻辑与
OR或||	逻辑或

运算优先级：not>and>or
如果不知道优先级建议运算的时候加()

1、查询商品主键是32的商品

mysql> select goods_id,goods_name,shop_price from goods where goods_id=32;

2、查询不属于第三个栏目的商品

mysql> select goods_id,goods_name,shop_price from goods where cat_id!=3;
mysql> select goods_id,goods_name,shop_price from goods where cat_id<>3;

3、本店价格高于3000的商品

mysql> select goods_id,goods_name,shop_price from goods where shop_price>3000;

4、本店价格低于或等于100的商品

mysql> select goods_id,goods_name,shop_price from goods where shop_price<=100;

5、取出第3个和第6个栏目的商品

mysql> select goods_id,goods_name,shop_price from goods where cat_id in (3,6);
mysql> select goods_id,goods_name,shop_price from goods where cat_id=3 or cat_id=6;

6、取出100<=本店价格<=500的商品

mysql> select goods_id,goods_name,shop_price from goods where shop_price between 100 and 500;
mysql> select goods_id,goods_name,shop_price from goods where shop_price<=500 and shop_price>=100;

7、取出不属于第3个和第11个栏目的商品

mysql> select goods_id,goods_name,shop_price from goods where cat_id not in (3,11);
mysql> select goods_id,goods_name,shop_price from goods where cat_id!=3 and cat_id!=11;

8、取出本店价格大于100小于400，或者大于4000小于5000的商品

mysql> select goods_id,goods_name,shop_price from goods where shop_price>100 and shop_price<500 or shop_price>4000 and shop_price<5000;

9、取出第3个栏目，价格小于1000或大于3000，同时点击量大于5的商品

mysql> select goods_id,goods_name,shop_price from goods where cat_id=3 and (shop_price<1000 or shop_price>3000) and click_count>5;

10、取出1号栏目的商品（1号栏目没有直接放商品，栏目下还细分了小栏目，商品放在小栏目里面）

mysql> select goods_id,goods_name,shop_price from goods where cat_id in (2,3,4,5);

11、查出产品名称以huawei开头的商品（模糊查询，%通配任何字符，_通配一个字符）

mysql> select goods_id,goods_name,shop_price from goods where goods_name like 'huawei%';

理解查询模型

把列看成变量，把where后面的看成if里的判断表达式
哪些行被取出来？------哪行能让where的表达式为真就取出来。
判断一行取出什么就是selecet后面接的
列既然是变量就能运算，而且可以重命名结果显示的列名
12、显示商品的市场价格和本地价格的差价

mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goods where cat_id in (2,3,4,5);

13、查出本店比商场价省200以上的商品

mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goods where (market_price-shop_price)>200;

注意：where后面不能直接用discount代表(market_price-shop_price)，where作用对象是表，discount的作用域是查询结果，对结果进行查询使用having
14、有一个表和数组，要求把num值处于[20,29]之间的值改为20；[30,39]之间的值改为30

mysql> update test set num=floor(num/10)*10 where num>=20 and num<=39;

floor(x)：返回不大于x的最大整数值。
15、把goods表中商品为‘applexxx’的改为HWxxx。

mysql> update goods set goods_name=concat('HW',substring(goods_name,6)) where goods_name like 'apple%';

MySQL查询语句（二）——group by分组及统计函数