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