你的位置:首页 > 数据库

[数据库]Oracle学习笔记五 SQL命令(三):Group by、排序、连接查询、子查询、分页


GROUP BY和HAVING子句

GROUP BY子句

用于将信息划分为更小的组
每一组行返回针对该组的单个结果

--统计每个部门的人数:Select count(*) from emp group by deptno; --根据部门分组,并统计 Select deptno, count(*) form emp group by deptno;    select deptno, avg(sal) from emp group by deptno; --每个部门的平均工资

 

HAVING子句

用于指定 GROUP BY 子句检索行的条件

Select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; --找出平均工资大于2000的部门;

 

数据排序

Order by 列名1 asc | desc, 列2 asc | desc; --默认为asc升序Select * from emp order by sal; --根据工资升序排序Select * from emp order by sal desc; --工资降序Select * from emp order by sal desc, hiredate asc; --按工资降序排,如果工资一样,则按日期升序排;

 

 

 查询语句的执行顺序

Where ,group by , having

先根据where条件将符合要求的数据筛选出来,

再根据group by来进行分组

最后将分组之后的数据用having进行约束,得到select的结果

 

多表查询

Select e.ename, t.tid from emp e, test t; --笛卡尔集,交叉查询

 

 

连接查询

内连接: inner join返回多个表之间共同的数据

Select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;
Select empno,ename,dname form emp inner join dept on emp.deptno = dept.deptno;

 

外连接:

左连接: left join  数据的结果以左表为主,右表如果在左表中不存在就显示是否为空!

select s.*, c.cid, c.grade from student s left join cs c on s.sid = c.sid

右连接: right join

全连接: full join

 

具体参看:IndexMan 文章 oracle连接查询详解

 

子查询

在一个查询中可以包含另一个查询,它可以出现在任何一个地方,外部查询得到结果,内部查询返回条件

 

--得到工资最高的人的信息select * from emp where sal = (select max(sal) from emp); 
--查询所有和SMITH在同一个部门的人select ename from emp where deptno = (select deptno from emp where ename = 'SMITH');
--列出与smith在同一个部门的员工信息Select * from emp where deptno = (select deptno form emp were ename = 'SMITH');
--与MARTIN是同一个领导的员工Select * form emp where mrg = (select mgr from emp where ename = 'MARTIN');
--列出所有在New York办公的员工--连接查询方式:select * from emp inner join dept on emp.deptno = dept.deptno where LOC = 'NEW YORK';--子查询方式:select * from emp where deptno = (select deptno from dept where LOC = 'NEW YORK');
--列出所有在New York和DALLAS办公的员工 Select * from emp where deptno in (select deptno from dept where loc = 'NEW YORK' or loc = 'DALLAS'); Select * from emp where deptno = any (select deptno from dept where loc = 'NEW YORK' or loc = 'DALLAS');

 数据的分页

--查询前5条  Select rownum, emp.* from emp where rownum <=5;--查询第6条以后的数据  select * from emp where empno not in(select empno from emp where rownum <=5);--查询第6-10条数据    ○ Select * from (select * from emp where empno not in(select empno from emp where rownum <=5)) where rownum <=5;    ○ select * from emp where empno not in(select empno from emp where rownum <=5) and rownum <= 5;--总结  ○ Select * from (select * from emp where empno not in(select empno from emp where rownum <= size * (page - 1))) where rownum <= size;  ○ select * from emp where empno not in(select empno from emp where rownum <= size * (page - 1)) and rownum <= size;  --排序后再分页查询  --将排序之后的数据编号(效率排第2)  select * from (select rownum rn, d.* from (select * from emp order by sal desc) d) where rn > 5 and rn < 11;  --分页函数:row_number函数(效率排第1)  select row_number() over(order by sal desc) rn, emp.* from emp;  --分页  select * from (select row_number() over(order by sal desc) rn, emp.* from emp) where rn > 5 and rn < 11;