mysql_查询语句_having_orderby_limit_嵌套查询_子查询

作者: 鲁智深 分类: PHP 发布时间: 2015-09-24 17:39

七、having

having和where区别:

1、where:针对查询前的列发挥作用

2、having:针对查询结果的列发挥作用

3、where和having都是用来筛选数据的,where可以用having代替

1
2
3
//查询本店售出价比进货价高200以上的商品
select * from goods where shop_price-goods_price>=200;//where写法
select *,shop_price-goods_price as cj from goods having cj>=200;//having写法
1
2
3
4
5
6
7
//创建学生表
create table stubent(
id int auto_increment primary key,
name varchar(16) not null default "",
job varchar(32) not null default "",
score tinyint not null default 0
)
1
2
3
4
5
6
7
8
//插入数据
insert into stubent(name,job,score) values
("张三","语文",55),
("张三","数学",65),
("张三","英文",45),
("李四","语文",35),
("李四","数学",65),
("李四","英文",75)

应用:查询两门及两门以上科目不及格的学生的平均分

1
2
1、查出两门及两门以上科目不及格的学生
select name,count(*) as gk from stubent where scene<60 group by name having gk>=2;
1
2
2、求平均分
select name,sum(score<60) as gk,avg(score) from stubent group by name having gk>=2;

八、order by

select * from stubent order by 字段 方法;

select * from stubent order by score asc;

order by 字段 默认是按当前字段升序排列

order by 字段 asc 升序排列

order by 字段 desc 降序排列

order by 字段 rand();随机排列

//种类(cat_id)降序排列,每个种类下的商品价格升序排列

//对多个字段进行排序,我们可以连用,写在前面的字段优先级高
select * from goods order by cat_id desc,shop_price asc;

九、limit(分页就是用limit制作的)

limit [offset],lenght

offset:偏移量(从那个位置开始截),可选参数,默认是0

lenght:截取的长度

1
2
//截取价格最贵的前三个手机
select *from goods order by shop_price desc limit 0,3;//先价格降序排列,在截取前3条

十、嵌套查询

思路:查询的时候from后面的跟这是表名,也可以跟select查询的结果

1
select * from (select * from goods order by cat_id asc,shop_price desc) as t group by cat_id;

十一、子查询

where型子查询(商品表案例)

1
2
//查询最新的商品(不要order by)
select * from goods where id=(select max(id) from goods);
1
2
//取出每个种类的最新商品
select * from goods where id in(select max(id) from goods group by cat_id);

总结:把内存查询结构,当作内层查询结果

from型子查询(学生表案例)

//用子查询查出挂科2门及2门以上的同学平均成绩

1
2
//先查出哪些同学挂科2门及2门以上
select name,count(*) as gk from stubent where score=2;
1
2
//平均成绩
select name,avg(score) from stubent where name in(select name from(select name,count(*) as gk from stubent where score=2) as t) group by name;

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注