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

电玩女神 2022-08-12 00:10 226阅读 0赞

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

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

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

结构如下面几个图所示

![SouthEast][]

![SouthEast 1][]

![SouthEast 2][]

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

emp表----员工信息表

![SouthEast 3][]

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

![SouthEast 4][]

salgrade表----工资等级表

![SouthEast 5][]

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);

[http_pan.baidu.com_s_1eQiV9uE]: http://pan.baidu.com/s/1eQiV9uE
[SouthEast]: /images/20220810/e2e389a34f034f6787e907bf253689ad.png
[SouthEast 1]: /images/20220810/f8002ae09afc4e21951c63322173879d.png
[SouthEast 2]: /images/20220810/4aca6468c66d48358ee7954a9bff6c42.png
[SouthEast 3]: /images/20220810/9eee6e063c0242e2aa516755e8cdf05b.png
[SouthEast 4]: /images/20220810/73899261232c4ce4abac74f25c55e774.png
[SouthEast 5]: /images/20220810/cc672455144941b0929f0f2fb099e4e2.png