mysql select
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 | +----------+--------------+------------+
