我们使用scott用户的几张表来演示。
1. 查看表结构
desc emp;
2. 查询所有列
select * from dept;
3. 查询指定列
select ename, sal, job, deptno from emp;
4. 取消重复行
select distinct deptno, job from emp;
5. 统计行数
select count(*) from emp;
6. 条件查询
1、查询SMITH所在部门,工作,薪水select deptno,job,sal from emp where ename = 'SMITH';
2、查询薪水大于3000的员工信息select * from emp where sal > 3000;
7. 显示每个雇员的年工资
select sal*13+nvl(comm, 0)*13 "年薪" , ename, comm from emp;
8. 显示列别名
select ename "姓名", sal*12 as "年收入" from emp;
9. ||
select ename || ' is a ' || job from emp;
10. like
%表示0到多个字符,_表示任意单个字符。
1、显示首字符为S的员工姓名和工资?select ename,sal from emp where ename like 'S%';
2、显示第三个字符为大写O的所有员工的姓名和工资select ename,sal from emp where ename like '__O%';
11. in
select * from emp where empno in (7844, 7839,123,456);
12. is null
select * from emp where mgr is null;
13. 逻辑操作符
查询工资高于500或者是岗位为 MANAGER 的雇员,同时还要满足他们的姓名首字母为大写的J。select * from emp where (sal >500 or job = 'MANAGER') and ename like 'J%';
14. order by
1、按照工资的从低到高的顺序显示雇员的信息select * from emp order by sal;
2、按照部门号升序而雇员的工资降序排列select * from emp order by deptno,sal desc;
3、按年薪排序select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;
15. 分组函数
1、显示所有员工中的最高工资和最低工资select max(sal),min(sal) from emp;
2、最高工资那个人select ename,sal from emp where sal=(select max(sal) from emp);
3、显示所有员工的平均工资和工资总和select avg(sal),sum(sal) from emp;
4、计算总共有多少员工select count(*) from emp;
16. group by
1、显示每个部门的平均工资和最高工资select avg(sal),max(sal),deptno from emp group by deptno;
2、显示每个部门的每种岗位的平均工资和最低工资select min(sal), avg(sal), deptno, job from emp group by deptno, job;
17. having
显示平均工资低于 2000的部门号和它的平均工资select avg(sal), max(sal), deptno from emp group by deptno having avg(sal) < 2000;
18. 多表查询
1、显示雇员名,雇员工资及所在部门的名字select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno;
2、显示部门号为 10 的部门名、员工名和工资select d.dname, e.ename, e.sal from emp e, dept d where e.deptno = d.deptno and e.deptno = 10;
3、显示各个员工的姓名,工资及工资的级别select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
4、显示雇员名,雇员工资及所在部门的名字,并按部门排序select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno order by e.deptno;
19. 自连接
显示某个员工的上级领导的姓名select worker.ename, boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename = 'FORD';
20. 单行子查询
1、查询出SMITH的部门号select deptno from emp where ename = 'SMITH';
2、显示与SMITH同部门的所有员工select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
21. 多行子查询
查询和部门10的工作相同的雇员的名字、岗位、工资、部门号select distinct job from emp where deptno = 10;
select * from emp where job in (select distinct job from emp where deptno = 10);
22. all
显示工资比部门 30的所有员工的工资高的员工的姓名、工资和部门号select ename, sal, deptno from emp where sal > all (select sal from emp where deptno = 30);
select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30);
23. any
显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号select ename, sal, deptno from emp where sal > any (select sal from emp where deptno = 30);
select ename, sal, deptno from emp where sal > (select min(sal) from emp where deptno = 30);
24. 多列子查询
查询与 SMITH 的部门和岗位完全相同的所有雇员select deptno, job from emp where ename = 'SMITH';
select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');
25. from子句中使用子查询
显示高于自己部门平均工资的员工的信息select deptno, avg(sal) mysal from emp group by deptno;
1 | select e.ename, e.deptno, e.sal, ds.mysal |