group by ，order by ，having

group by

--使用group by语句查询

select count(studName) from student_1 group by study

select count(uname) from userluo group by age;

根据某一个字段进行分组

select age,count(*) from userluo group by age;

select uname,count(*) from userluo group by uname;

矛盾。。。。。

尽管deptno和deptname是一一对应的，但group by不知道。。。按照语法来检查。。。

Group by 后面也可以有多个字段，但是要求和前面一致。。。。。。。。

-- 单列分组

--显示每个部门的平均工资和最高工资

Select deptno,avg(sal),max(sal) from emp

group by deptno;

-- 多列分组

--显示每个部门、每种岗位的平均工资和最高工资

Select deptno,job,avg(sal),max(sal) from emp

group by deptno,job; -- 注意： deptno 和 job 完全相同才显示一条记录。

Having 和 group by 配套使用。。。

可以在 group 分组以后再过滤，

select age,count(*) from userluo group by age having count(*)<2;

--使用having语句

select trim(studName),count(*) from student_1 group by trim(studName) having count(*)=2;

select count(*) studName from student_1 group by studName having count(*)=2;

order by

--使用order by语句,desc是降序排列，asc是升序排列,默认为升序排列

select * from student_1 order by age desc;

select * from student_1 order by age sname desc;先按age排列，相同的age再按sname排列，

group by ，order by ，having