Oracle学习（五）--sql查询（包含子查询）

这里用到了三个表，emp,dept,salgrade

表的sql文件请前往此链接下载：链接：http://pan.baidu.com/s/1eQiV9uE 密码：oanr

注意阅读“请先读我”文件

结构如下面几个图所示

表中的数据有以下图所示：

emp表----员工信息表

dept表-----部门信息表

salgrade表----工资等级表

1.sql查询
案例1:查询员工的编号、姓名、及工资职位
SQL>select empno,ename,sal,job from emp;

案例2:将所有员工的工资+500(字段四则运)

SQL>select ename,sal+500 from emp;
字段起别名(as是可以省略)
SQL>select ename,sal+500 (as) newsal from emp;

案例3:查询所有员工的年薪并+1000(yearsal)显示
SQL>select ename,sal*12+1000 yearsal from emp;

案例4:查询员工的姓名工资(字符串拼接||)
如:员工姓名:smith员工工资:800
SQL>select '员工姓名'||ename||'员工工资'||sal from emp; 

案例5:查询所有员工的编号、姓名、奖金+500显示(null表示无穷大，null参与运算结果仍为null,nvl去除空值)
SQL>select empno,ename,nvl(comm,0)+500 from emp;

案例6:查询员工职位(distinct去除重复值)
SQL>select distinct(job) from emp;

案例7：查询员工为jack所有信息(条件查询)
SQL>select * from emp where ename='SMITH';

案例8:查询员工的编号，姓名，工资，要求工资必须大于1500
SQL>select empno,ename,sal from emp where sal>1500;

案例9：查询工资在1500～3000之间员工的编号、姓名、工资和职位
SQL>select empno,ename,sal,job from emp
where sal>=1500 and sal<=3000;
（between and）
SQL>select empno,ename,sal,job from emp
where sal between 1500 and 3000;

案例10:查询所有职位是秘书或销售人员的员工编号、姓名、职位(or运算)
SQL>select empno,ename,job,sal from emp where job='CLERK' or job='SALESMAN';

案例11:查询姓名以"A"开头的员工编号、姓名，职位(模糊查询，like %表示0或者多个
_表示一个字符)
SQL>select empno,ename,job from emp where ename like 'A%';

案例12:查询姓名中第二个字母是"A"的员工的编号、姓名、职位
SQL>select empno,ename,job from emp whereename like '_A%';

案例13: 查询员工编号、姓名、职位、工资
按工资降序排序(排序order by desc降序，asc升序默认值)
SQL>select empno,ename,job,sal from emp
order by sal desc;
(组函数 count(),avg(),min(),max(),sum())

案例14:查询公司又少个员工
SQL>select count(empno) as pnumber from emp;

案例15:查询公司的平均工资
SQL>select avg(sal) avgsal from emp;

案例16:查询公司的最高工资
SQL>select ename, max(sal) avgsal from emp;//错误，字段不能直接与组函数连 

2.子查询
sql语句中嵌套了其他sql语句
select 字段1,字段2,(sql子查询) from
table_name(子查询) where sal=(子查询)
[分类]查询结果分为单行单列和多行单列
关联程度分为关联子查询与非关联子查询

单行单列－－－－－－－－－－－－－－－

案例17:查询出工资最高员工信息
步骤1:最高工资
SQL>select max(sal) from emp;--5000
步骤2:员工信息
SQL>select empno,ename,sal from emp
where sal=5000;
SQL>select empno,ename,sal from emp
where sal=(select max(sal) from emp);

案例17:查询工资最低的员工信息，且工资增加500；
步骤1:最低工资
SQL>select min(sal) from emp;
步骤2:员工信息(+500)
SQL>select empno,ename,sal+500 as newsal from emp where sal=800;
SQL>select empno,ename,sal+500 as 
newsal from emp where sal=(select min(sal) from emp);

案例18:查询姓名为SMITH的工资与平均工资
步骤1:平均工资
SQL>select avg(sal) from emp;
步骤2:smith的工资信息
SQL>select empno,ename,sal,job from 
emp where ename='SMITH';
SQL>select empno,ename,sal,(select avg(sal) from emp) as avgsal,job from emp where ename='SMITH';

案例19:查询高于平均工资的所有员工的信息
步骤1:查询出平均工资
SQL>select avg(sal) from emp;
步骤2:员工信息(>平均工资)
SQL>select empno,ename,sal,job from 
emp where sal>(select avg(sal) from emp);

案例20:查询销售部门所有职位
步骤1:dept表找到销售部门部门编号
SQL>select detpno from dept where dname='SALES';--30
步骤2:emp表找到所有deptno=30的员工的职位
SQL>select job from emp where deptno=30;
SQL>select distinct(job) from emp where
deptno=(select deptno from dept where dname='SALES');

多行多列－－－－－－－－－－－－－－－－

案例21:查询工资比simth高员工信息
SQL>select empno,ename,sal from emp wheresal>(select sal from emp where ename='SMITH');
插入数据，造成多个simth的情况:
SQL>insert into emp(empno,ename,job,sal,deptno) values(8999,'SMITH','salesman',2000,10);
(all,any,in)
SQL>select empno,ename,sal from emp wheresal>all(select sal from emp where ename='SMITH');--大于最大值(所有)

案例22:查询工资比simth低员工信息
SQL>select empno,ename,sal from emp wheresal<all(select sal from emp where ename='SMITH');
[总结]all大于最大值，小于最小值

案例23:查询出工资比任意smith工资高的员工信息(不等于:!= <>)
SQL>select empno,ename,sal from emp wheresal>any(select sal from emp where ename='SMITH') and ename<>'SMITH';
[总结]any表示任意，大于最小值，小于最大值 

案例24:查询与smith相同部门的其他所有员工信息
步骤1:查询出所有smith的部门编号
SQL>select deptno from emp where ename='SMITH';
步骤2:根据多个部门编号去查询员工
SQL>select empno,ename,job,sal,deptno from emp where deptno in(select deptno from emp where ename='SMITH'); 
[总结]in表示满足集合中任意数据即可


关联子查询与非关联子查询－－－－－－－

(非关联子查询)
案例25:查询所有比平均工资低员工信息
SQL>select empno,ename,job,sal from emp 
where sal<(select avg(sal) from emp);

案例26:查询出king的所有下属的信息
步骤1:查询出king的empno
SQL>select empno from emp where ename='KING';--7839
步骤2:查询所有员工mrg=empno
SQL>select empno,ename,mgr from emp where mgr=7839;
SQL>select empno,ename,mgr from emp wheremp where mgr=(select empno from emp where ename='KING');

(关联子查询)
案例27:查询出哪些员工比本部门平均工资低
步骤1:查询平均工资
SQL>select avg(sal) from emp
步骤2:查询员工信息(sal>平均工资)
SQL>select empno,ename,sal,job from emp e where sal<(select avg(sal) from emp where deptno=e.deptno);