详解select查询语句

详解select查询语句

where,group by,having,order by,limit按顺序出现：1-5

1、where的常用运算表达式：

比较运算符：<,<=,=,in(在某集合内),!=或<>,>,>=,between(在某范围内)
逻辑运算符：非：NOT或 |，或：OR或||，与：AND或&&

模糊查询：
like->像
%->通配任意字符
‘_’->通配单个字符

2、group by:把行按字段分组

eps：group by col1,col2,…colN

运用场合：常见于统计场合，如按栏目计算帖子数，统计每个人的平均成绩等
max,min,sum,avg,count
例：

select max(shop_price) from goods;

select cat_id,max(shop_price) from goods group by cat_id;

可以给列或计算结果取别名，用as

select cat_id,sum(shop_price*goods_number) as hk from goods group by cat_id;

3、having

where作用于表中的列，having作用于查询结果中的列，其他用法一样
所以，两者同时使用时：where在前，having在后
查询market_price-shop price大于200的结果

select goods_id,goods_name,market_price-shop price as sheng from where market_price-shop_price>200;
select goods_id,goods_name,market_price-shop price as sheng from having sheng>200;

综合一下：

select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >=2

4、order by 排序功能

按一个或多个字段对结果进行排序

select goods_id,goods_name,shop_price from goods where cat_id=3 order by shop_price;

默认升序排列，或用asc声明，若为降序，用desc声明，则

order by shop_price desc；
select goods_id,goods_name,shop_price from goods where cat_id<>3 order by cat_id,shop_price desc;

先按cat_id升序排列，再按shop_price降序排列：可按多字段排序

5、limit在语句最后，起到限制条目的作用

Limit[offset]N；offset：偏移量（可选项），N：取出条目

select goods_id, goods_name, shop_price from goods where cat_id<>3 order by cat_id,shop_price desc limit 3,3;

从第三行取，取3行

综合查询，例：取出每个栏目下的最贵的商品

select * from (select goods_id, cat_id, goods_name, shop_price from goods oder by cat_id asc,shop_price desc) as tmp group by cat_id;