select 查询：
赋值：赋值不能应用在where中，因为where操作的是磁盘上的文件，可以应用在having筛选中。
例：select (market_price-shop_price) as jiesheng from goods;
一、条件查询：where(针对存在磁盘上的数据文件发挥作用)
select 列名1,列名2... from 表名 where 条件
1.条件表达式表达式为真，则取出该行
2.比较运算符 =，!= , <>(不等于) , <= , >=
3.like,not like ,in ,not in , between,and
字符匹配：'%'匹配多个字符，'_'匹配任意单个字符
4. is null,is not null
示例a：在goods表中取出goods大于30的产品，并显示出goods_id,goods_name,market_price
mysql> select goods_id,goods_name,market_price from goods where goods_id >30;+----------+-----------------+--------------+| goods_id | goods_name | market_price |+----------+-----------------+--------------+| 31 | 摩托罗拉e8 | 1604.39 || 32 | 诺基亚n85 | 3612.00 |+----------+-----------------+--------------+2 rows in set (0.08 sec)
示例b:在goods表中查找goods_name以"诺基亚"开头的行，并打印
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.00 sec)
示例c：在goods表中查找goods_name为"诺基亚"开头后面跟着三个字符的行，并打印
mysql> select goods_id,goods_name from goods where goods_name like "诺基亚___";+----------+--------------+| goods_id | goods_name |+----------+--------------+| 9 | 诺基亚e66 || 23 | 诺基亚n96 || 32 | 诺基亚n85 |+----------+--------------+3 rows in set (0.00 sec)
二、 分组：
group by
一般要配合5个统计函数使用:max(),min(),sum(),avg(),count()
avg():求平均值
mysql> select avg(shop_price) from goods;+-----------------+| avg(shop_price) |+-----------------+| 1232.526774 |+-----------------+1 row in set (0.09 sec)
max():求最大
min():求最小
sum():求和
count(*):统计行数
mysql> select cat_id,avg(shop_price) from goods group by cat_id;+--------+-----------------+| cat_id | avg(shop_price) |+--------+-----------------+| 2 | 823.330000 || 3 | 1746.066667 || 4 | 2297.000000 || 5 | 3700.000000 || 8 | 75.333333 || 11 | 31.000000 || 13 | 33.500000 || 14 | 54.000000 || 15 | 70.000000 |+--------+-----------------+9 rows in set (0.00 sec)
mysql> select cat_id,count(*) from goods group by cat_id;+--------+----------+| cat_id | count(*) |+--------+----------+| 2 | 1 || 3 | 15 || 4 | 3 || 5 | 1 || 8 | 3 || 11 | 2 || 13 | 2 || 14 | 2 || 15 | 2 |+--------+----------+9 rows in set (0.00 sec)
三、 having 筛选：
- 掌握having查询，理解having与where的不同
1.筛选出market_price高于shop_price 200以上的商品
mysql> select goods_name,(market_price-shop_price) as jiesheng from goods having jiesheng > 200;+---------------------------+----------+| goods_name | jiesheng |+---------------------------+----------+| kd876 | 277.60 || 诺基亚e66 | 459.60 || 索爱c702c | 265.60 || 诺基亚5320 xpressmusic | 262.20 || 诺基亚5800xm | 525.00 || 夏新n7 | 460.00 || 夏新t5 | 575.60 || 金立 a30 | 400.00 || 多普达touch hd | 1199.80 || 诺基亚n96 | 740.00 || p806 | 400.00 || 摩托罗拉e8 | 267.39 || 诺基亚n85 | 602.00 |+---------------------------+----------+13 rows in set (0.00 sec)
where条件查询是操作磁盘上文件，不能处理内存中的数据，例如赋值操作。
having筛选是是处理内存中的数据，如果where 和having 都存在时，having必须在where后面。
习题：where-having-group综合习题
要求：查询出2门及2门以上不及格者的平均成绩
+----------+-----------+-------+| name | subject | score |+----------+-----------+-------+| zhangsan | math | 90 || zhangsan | language | 50 || zhangsan | geography | 40 || lisi | language | 55 || lisi | politics | 45 || wangwu | politics | 30 || zhangsan | english | 100 |+----------+-----------+-------+
正确答案：
select name,subject,score,avg(score),sum(score<60) as f from report group by name having f>=2;
+----------+----------+-------+------------+------+| name | subject | score | avg(score) | f |+----------+----------+-------+------------+------+| lisi | language | 55 | 50.0000 | 2 || zhangsan | math | 90 | 70.0000 | 2 |+----------+----------+-------+------------+------+
错误案例：
select name,subject,score,avg(score),count(score<60) as f from report group by name having g f<=2; （count用来数行号的，不判断条件）
+--------+----------+-------+------------+---+| name | subject | score | avg(score) | f |+--------+----------+-------+------------+---+| lisi | language | 55 | 50.0000 | 2 || wangwu | politics | 30 | 30.0000 | 1 |+--------+----------+-------+------------+---+
四、 排序：
order by
- 降序 desc
- 升序 asc(默认是asc)
- 多列排序
select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc,goods_id asc;
+----------+--------+----------------------------------------+------------+| goods_id | cat_id | goods_name | shop_price |+----------+--------+----------------------------------------+------------+| 16 | 2 | 恒基伟业g101 | 823.33 || 22 | 3 | 多普达touch hd | 5999.00 || 32 | 3 | 诺基亚n85 | 3010.00 || 17 | 3 | 夏新n7 | 2300.00 || 9 | 3 | 诺基亚e66 | 2298.00 || 21 | 3 | 金立 a30 | 2000.00 || 24 | 3 | p806 | 2000.00 || 31 | 3 | 摩托罗拉e8 | 1337.00 || 10 | 3 | 索爱c702c | 1328.00 || 13 | 3 | 诺基亚5320 xpressmusic | 1311.00 || 11 | 3 | 索爱c702c | 1300.00 || 12 | 3 | 摩托罗拉a810 | 983.00 || 19 | 3 | 三星sgh-f258 | 858.00 || 15 | 3 | 摩托罗拉a810 | 788.00 || 8 | 3 | 飞利浦9@9v | 399.00 || 20 | 3 | 三星bc01 | 280.00 || 18 | 4 | 夏新t5 | 2878.00 || 14 | 4 | 诺基亚5800xm | 2625.00 || 1 | 4 | kd876 | 1388.00 || 23 | 5 | 诺基亚n96 | 3700.00 || 7 | 8 | 诺基亚n85原装立体声耳机hs-82 | 100.00 || 3 | 8 | 诺基亚原装5800耳机 | 68.00 || 4 | 8 | 诺基亚n85原装充电器 | 58.00 || 6 | 11 | 胜创kingmax内存卡 | 42.00 || 5 | 11 | 索爱原装m2卡读卡器 | 20.00 || 25 | 13 | 小灵通/固话50元充值卡 | 48.00 || 26 | 13 | 小灵通/固话20元充值卡 | 19.00 || 29 | 14 | 移动100元充值卡 | 90.00 || 30 | 14 | 移动20元充值卡 | 18.00 || 27 | 15 | 联通100元充值卡 | 95.00 || 28 | 15 | 联通50元充值卡 | 45.00 |+----------+--------+----------------------------------------+------------+
五、 limit 限制取出条目：
limit 偏移量 条数
习题：查找最新的商品（goods_id最大为最新）
select goods_id,goods_name,shop_price from goods order by goods_id desc limit 0,1;
+----------+--------------+------------+| goods_id | goods_name | shop_price |+----------+--------------+------------+| 32 | 诺基亚n85 | 3010.00 |+----------+--------------+------------+