mysql--(入门语句16--order by.limit的使用)

#order by

#取出第3个栏目的所有商品
slect goods_id,goods_name,shop_price from goods where cat_id=3;

#order by排序

1.

select goods_id,goods_name,cat_id,shop_price from goods where cat_id=3

order by shop_price;

#排序，可以根据字段来排序
#根据字段可以升序，或者降序排列
#默认是升序排列

#按照价格，把第3个栏目下的商品由高到低排列
select goods_id,cat_id,goods_name,shop_price,from goods where cat_id=3
order by shop_price;//是升序排列

select goods_id,cat_id,goods_name,shop_price,from goods where cat_id=3
order by shop_price desc;//是倒序排列

#显示声明升序排列，可以用asc来声明：
slect goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 or cat_id asc;

2.

#按照栏目进行排序

sleect goods_id,cat_id,goods_name,shop_price from goods
where cat_id <> 3
order byb cat_id;

#先对商品进行栏目排序，在栏目排序的基础上，再进行商品价格的排序。并且是降序
sleect goods_id,cat_id,goods_name,shop_price from goods
where cat_id <> 3
order byb cat_id,shop_price desc;

3.

#order by可以按照多字段排序，order by 列1 [desc/asc],列2[desc/asc]
#按发布时间，由早到晚排序
#add_time
select goods_id,goods_name,add_time from goods order by add_time asc;

#limit

1.

#取价格最高的三个商品
#先排列
select goods_id,goods_name,shop_price from goods order by shop_price desc;
#limit 在语句的最后，起到限制条目的作用
limit[sffset][N]
offset:代表偏移量
N:条目

#取价格第4-第6高的商品：
sleect goods_id,goods_name,shop_price from goods order by shop_price desc limit 3,3;

#取价格的前3高,下面两个都可以实现：
1)slect goods_id,goods_name,shop_price,from goods order by shop_price desc limit 0,3;
2)slect goods_id,goods_name,shop_price,from goods order by shop_price desc limit 3;

#取最贵的商品：
slect goods_id,goods_name,shop_price from goods order by shop_price desc limit 1;
#取最新的商品：
slect goods_id,goods_name,shop_price from goods order by add_time desc limit 1;

#取出每个栏目下的最贵的商品

#来一波错误的示例：
1.max,group
select goods_id,cat_id,goods_name,max(shop_price) from goods group by cat_id;

#取出所有的商品，对比一下，查看是否正确

slect goods_id,cat_id,goods_name,shop_price from goods;

对比之后，可以发现结果不正确，

2.order by

slect goods_id,goods_name,shop_price from goods group by cat_id order by shop_price desc;

以上两个示例都是没有根据组内部去排序，所以得出的结果都只是，在排序的时候，取出的最大值是，遇到组的第一个成员，

3.(先排序，再分组)

select goods_id,goods_name,shop_price,cat_id from goods order by shop_price desc group by cat_id;//直接出现语法错误

 

 
 #5个子句顺序要求的： where,group,having,order by,limit
 #取出每个栏目下的最贵的商品
 #这张表不存在的情况下：
   select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;//先按栏目升序排列，再按价格降序排列
 
 #建立一张临时表
 create table g2 lik goods;
 insert into g2 select *from goods order by cat_id asc, shop_price desc;//排序
 select goods_id,goods_name,cat_id,shop_price from g2;
 truncate g2;//清空表
 
 insert into g2 select *from goods order by shop_price desc;
 select goods_id,goods_name,cat_id,shop_price from g2;
 #最终没有构造成功×

正确的示例：

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