oracle的sql语句训练
--查询工资最高的人的名字
select ename ,sal from emp where sal=(select max(sal) from emp );
--求出员工的工资在所有人的平均工资之上的人
select ename ,sal from emp where sal >(select avg(sal) from emp );
--求出每个部门中工资最高的那个人的名字
select ename,sal from emp join (select deptno,max(sal) max_sal from emp group by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
--求出每个部门的平均薪水
select deptno, avg(sal) from emp group by deptno;
--求出每个部门的平均薪水的等级是多少( 每个部门的平均薪水 ---的等级这个是求等级)
select deptno,grade from salgrade join (select deptno,avg(sal) avg_sal from emp group by deptno) t on t.avg_sal between losal and hisal;
--求部门平均的薪水等级(薪水等级的平均)
select avg(grade) from
(select deptno,grade from salgrade join (select deptno,avg(sal) avg_sal from emp group by deptno) t on t.avg_sal between losal and hisal);
--求雇员中哪些是经理人
select ename from emp where empno in (select mgr from emp);
--求雇员所对应的经理人
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;
--查询平均薪水最高部门的部门编号
select deptno,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)--当做一张表,否则没有表可以from
where avg_sal =
(select max(avg_sal)from (select deptno,avg(sal) avg_sal from emp group by deptno ))--当做一个值;
--查询平均薪水最高部门的部门名称
select dname from dept where deptno=
(select deptno from --当做一个值
(select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg(sal))from emp group by deptno ) );--组函数的嵌套,最多两层,就是这种两层(())
--查询部门平均薪水的等级
select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal;
--查询平均薪水等级最低的部门的名称--利用视图可以简化程序代码
select dname from dept where deptno=
( select deptno from
((select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal))
where grade=
(select min(grade) from
(select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal)));
--利用视图简化代码
create view my_view as (select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal);
--创建视图要用sys用户来给scott用户授权
用sys用户登录成功后,
grant create table,create view to scott;
授权成功后才可以利用scott普通用户创建视图;
--用视图来简化上面的代码
select dname from dept where deptno=
( select deptno from
(my_view )
where grade=
(select min(grade) from
my_view ));
--求比普通员工最高薪水还要高的经理人的名称
普通员工:即不在mgr这个里面的就是普通员工
经理人:在mgr这个里面的就是经理人
--普通员工的最高薪水
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);--对于有null值的要进行这样处理
--再求比这个最高工资还要高的经理人的名称
select ename from emp where empno in (select distinct mgr from emp where mgr is not null)
and sal >
(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
--求部门经理人中平均薪水最低的部门名称
分析:
哪些部门中有哪些人是经理人,这些经理人所在部门,薪水是多少。
select dname from dept d join (
select deptno,avg(sal) avg_sal from (select ename,sal,deptno from emp where empno in(select mgr from emp )) group by deptno ) s
on d.deptno =s.deptno
where avg_sal=
(select min(avg_sal) from (select deptno,avg(sal) avg_sal from (select ename,sal,deptno from emp where empno in(select mgr from emp )) group by deptno));
--求薪水最高的前5名的雇员
select ename,sal from (select ename,sal from emp order by sal desc) where rownum <=5;
mysql的分页是limit a,b oracle的分页比较特殊,没有limit只有rownum,这个是不显示的一个伪字段。这个字段只能使用小于或者小于等于,不能使用大于或者大于等于。
如果分页只能先查询出rownum这列 select rownum from emp,然后从这里面再rownum>或者<哪个数字。
select rownum ,ename from (select rownum no from emp) t where no>3 and no<10;
--求薪水最高的第6名到第10名的雇员
select ename,sal from (select ename,sal,rownum r from (select ename,sal from emp order by sal desc) ) where r>=6 and r<=10;
--求最后入职的5名员工(按照入职时间降序排列,取出前五个即可)
select ename,hiredate,rownum from(select ename,hiredate from emp order by hiredate desc ) where rownum<=5;
--比较效率
select * from emp where deptno=10 and ename like '%A%' --这个效率高些,跟java的短路与类似
select * from emp where ename like '%A% and deptno=10
相关文章
- 关于SQL与ORACLE创建本地临时表和全局临时表的方法和区别
- 【转】oracle数据库中的sqlnet.ora listener.ora tnsnames.ora的作用及使用示例
- Oracle数据库:oracle数据表格dmp,sql,pde格式导入与导出,视图、序列、索引等对象的导出,oracle完结,后续开启mysql的学习
- Oracle数据库:oracle执行计划性能代价cost,全表扫描速度慢,索引扫描速度快
- Oracle数据库:oracle用命令定义非空not null,unique唯一性,主键primary key,外键foreign key,check检查,启用enable,禁用disable约束
- Oracle数据库:oracle数据定义语言DDL,查询表、序列、索引、视图、创建表,修改表的列alter,rename to,删除表数据truncate和表drop
- Oracle数据库:oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
- Oracle数据库:oracle多表查询,等值连接,非等值连接,自连接的sql语句和规则
- Oracle数据库:oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
- Oracle数据库:oracle函数,单行函数,多行函数,upper,lower,initcap,字符串函数
- Oracle数据库:oracle 11g安装教程,已安装好的oracle各个文件夹的作用,oracle用户权限怎么样
- oracle用expdp定时备份所有步骤详解听语音
- oracle查锁及解锁命令
- 《Oracle PL/SQL必知必会》——1.3 试一试
- 基于Java(SSH)+ Oracle 实现的(Web)视频教学平台【100010405】
- ORACLE内部操作
- oracle索引的操作
- 转 【ORACLE】ORA-12537 问题整理
- Oracle数据库 | Oracle并发与一致性
- [Oracle工程师手记] 利用 DBMS_SQLTUNE.report_sql_monitor 生成 SQL 语句的监控信息
- [Oracle 工程师手记] Windows 环境下,获取与 oracle 相关 registry 的小技巧
- [Oracle工程师手记]如何收集 10046 trace
- Oracle死锁解决方法
- Oracle 堵塞(blocking blocked)
- oracle备份和升级数据库
- 怎样使用oracle 的DBMS_SQLTUNE package 来执行 Sql Tuning Advisor 进行sql 自己主动调优
- Oracle 基础系列之1.2 oracle的基本使用
- Oracle 基础系列之1.1 oracle的安装
- Mybatis+Oracle搭配insert空值报错之myBatis+mysql驱动+oracle驱动的源码分析