mysql c select_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) fromgoods;+-----------------+

| 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 bycat_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 bycat_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 |

+----------+--------------+------------+