表基本查询用 PL/SQL 的 scott 用户自带的四个表,选用三个表进行练习。
-
雇员表 emp
emp表 -
部门表 dept
dept表 -
工资级别表 salgrade
salgrade表
一、数据分组
① max、min、avg、sum、count
- 显示所有员工中最高工资和最低工资
select max(sal),min(sal) from emp;
- 显示最高工资的员工的名字(涉及子查询)
select ename,sal from emp where sal=(select max(sal)from emp);
- 显示所有员工的平均工资和工资总和
select avg(sal),sum(sal) from emp;
- 计算共有多少员工
select count (*) from emp;
- 显示工资最高的员工的名字,工作岗位
select ename,job,sal from emp where sal=(select max(sal)from emp);
- 显示工资高于平均工资的员工的信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
② group by 和 having 子句
注:
group by :用于对查询的结果分组统计。
having子句:用于限制分组显示结果。
- 显示每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno;
- 显示每个部门的每种岗位的平均工资和最低工资
select min(sal),avg(sal),max(sal),deptno from emp group by deptno,job;
- 显示平均工资低于 2000 的部门号和它的平均工资
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;
总结:数据分组
-
分组函数只能出现再选择列表、having、order by 子句种。
-
如果在 select 语句种同时包含有 group by、having、order by,那么他们的出现顺序必须是 group by、having,order by。
-
在选择列种如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在 group by 子句种中,否则就会出错。
比如:
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
//这里deptno就一定要出现在group by中。
二、多表查询
多表查询是指:基于两个或两个以上的表或视图的查询。
① 笛卡尔积,利用别名
- 显示雇员名,雇员工资及所在部门的名字
select a1.ename,a1.sal,a2.deptno from emp a1,dept a2 where a1.deptno=a2.deptno;
注意:
多表查询的条件是:至少不能少于表的个数-1。也就是说,如果有两个表,他的条件就有一个。如果是三个表,他的条件就有两个。
- 显示部门号为 10 的部门名、员工名和工资
select al.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;
- 显示各个员工的姓名、工资、及其工资的级别
工资级别表在:salgrade 表中
select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
- 显示雇员名、雇员工资及所在部门的名字、并按部门排序
select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
② 自连接 (利用别名)
自连接:同一张表的连接查询
- 显示某个员工的上级领导的姓名
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno;
- 显示 'FORD' 的上级
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename='FORD';
③ 子查询
子查询:嵌入在其他 sql 语句中的 select 语句,也叫嵌套查询
单行子查询:只返回一行数据的子查询语句
select * from table where = (select …… select()) //嵌套格式
- 显示与SMITH同一部门的所有员工
select deptno from emp where ename='SMITH';//先查询出smith的部门号
select * from emp where deptno=(select deptno from emp where ename='SMITH');//再嵌套显示显示与SMITH同一部门的所有员工
注:数据库在执行sql是从左到右顺序执行。
④ 多行子查询:返回多行数据的子查询
- 查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号
select distinct job from emp where deptno=10;//先查询10号部门的工作
select * from emp where job in (select distinct job from emp where deptno=10);//再嵌套查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
⑤ 在多行子查询中使用 all 操作符
- 显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
//方法1
select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);
//方法2
select max(sal) from emp where deptno=30;
select * from emp where sal>(select max(sal) from emp where deptno=30);
⑥ 多列子查询
多列子查询:查询返回多个列数据的子查询语句。
单行子查询:是子查询只返回单列,单行数据。
多行子查询:返回单列多行数据,都是针对单列而言。
- 查询与 SMITH 的部门和岗位完全相同的所有雇员
select deptno,job from emp where ename='SMITH';//先查询出SMITH的部门号,岗位
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');//再嵌套查询与SMITH的部门和岗位完全相同的所有雇员
⑦ 在 from 子句中使用子查询
- 显示高于自己部门平均工资的员工的信息
select deptno,avg(sal) mysal from emp group by deptno;//先查出各个部门的平均工资和部门号
select e.ename,e.deptno,e.sal,ds.mysal from emp e,
(select deptno,avg(sal)mysal from emp group by deptno)ds
where e.deptno =ds.deptno and e.sal>ds.mysal;//再把上面的查询结果看做是一张子表
总结:
当 from 子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌式图。
当 from 子句中使用子查询时,必须给子查询指定别名。
注意:
给表取别名的时候,不能加 as ;但是给列取别名是可以加 as 的。
别名不能用 as 的情况,如:
(select deptno,avg(sal)mysal from emp group by deptno)ds where e.deptno =ds.deptno and e.sal>ds.mysal; //在ds前不能加as,否则会报错
⑧ 分页查询
Oracle分页三种方式:
1. 按 rowid 分页(效率最高,复杂)
2. 按分析函数来分
3. 按 rownum 分页
其中利用 rownum 分页较为简单,所以这里以 rownum 为例:
- 第一步:子查询
select * from emp;
- 第二步:显示 rownum(Oracle分配的)
select a1.*,rownum rn from (select * from emp) al;
- 第三步:选出 rn 编号大于等于 10 的员工信息。
select a1.*,rownum rn from (select * from emp) al where rownum<=10;
- 选出 rn 行号大于等于 10 的员工中再次截取大于等于 6 的行号的员工信息。
如果需在这 rn 行号大于等于 10 的员工中再次截取大于等于 6 的行号就不能再使用 rownum 。需要使用子查询再次切割。
select * from (select a1.*,rownum rn from (select * from emp) al where rownum<=10) where rn >=6;
注意:
指定查询列,只需要修改最里层的子查询。例如:
select * from (select a1.*,rownum rn from (select ename,sal from emp) al where rownum<=10) where rn >= 6;
⑨ 其他
-
使用特点格式插入日期值
使用to_date函数 -
使用子查询插入数据(插入大量数据)
当使用Values子句时,一次只能插入一行数据;
当使用子查询插入数据时,一条insert语句可以插入大量得数据;
当处理行迁移或者装载外部表的数据到数据库时,可以使用子查询来插入数据。
//先新建一张表,这张表想存储emp表中的部分数据,则导入。
create table kkk(myId number(4),myname varcher2(50),myDept number(5));
//把emp表中的10号员工的信息导入到kkk表
insert into kkk (myID,myname,myDept) select empno,ename,deptno from emp where deptno =10;
网友评论