004第四章 表的查询 完

第四章查询

目标:

1.掌握简单查询

2.掌握复杂查询

3.掌握分页查询

准备知识:

▲emp、deptno、salgrade表结构

Empno—员工编号;ename—员工姓名;job—工作、职位、岗位

Mgr---上级编号;hiredate—入职时间;sal—工资;comm—奖金;deptno—部门编号Dname—部门名称;loc—部门地址;crade—等级;losal—最低工资;hisal—最高工资

▲SQL（Structured Query Language结构化查询语言）语句分类:

◆DML:(Data Manipulation Language 数据操作语言）由insert、update、delete三个组成

◆DDL: (Data Definition Language 数据定义语言) 由create、drop、alter、truncate组成

◆DCL: (Data Control Language 数据控制语言) 由grant、revoke 组成

◆查询语句:由select组成他是SQL中用的最多的。

◆事务控制语言:由commit、rollback、savepoint组成

一、Oracle中的简单查询

★不带条件的查询:

▼查看表结构:

SQL> desc emp;

▼查看表中所有列;(尽量少用select *)

SQL>select * from emp;

▼查看部分列；

SQL>select ename,job,sal from emp;

▼去掉重复的行distinct

SQL>select distinct job from emp;

▼统计表中记录的条数

SQL>select count(*) from emp;

★使用算术运算符

▼查询emp表中所有员工的姓名，年薪(不考虑奖金)

SQL>select ename,sal,sal*12 from emp;

▼查询emp表中所有员工的姓名，年薪(考虑奖金)

SQL> select ename,sal,comm,(sal+comm)*12 from emp;

注意:上面的查询结果出问题，没有奖金的人年薪都为空。因为oracle中任何数据和null运行结果都为null。可以使用

nvl函数处理

SQL> select ename,sal,comm,(sal+nvl(comm,0))*12 from emp;

给列取别名

SQL> select ename,sal,comm,(sal+nvl(comm,0))*12 as “年薪” from emp;//as可以省略

★带条件的查询:

▼查询20号部门的员工信息

SQL> select * from emp where deptno=20;

▼查询SMITH的工作岗位，工资，部门编号

SQL> select ename,job,sal,deptno from emp where ename='SMITH';

注意:oracle不分大小写，但是oracle中存储的数据是区分大小写的。

▼查询出工资高于3000的员工姓名，工资，部门编号

SQL> select ename,sal,deptno from emp where sal>3000;

▼查询出1981.6.30号之后入职的员工信息

SQL> select * from emp where hiredate>'30-6月-1981';

▼查询出年薪（计算奖金，年薪按12个月计算）高于20000的员工姓名、工资、奖金、年薪

SQL> select ename,sal,comm,(sal+nvl(comm,0))*12 from emp where (sal+nvl(comm,0))*12>20000;

★使用逻辑操作符:

and、or、not；between…and、in

▼查询出工资在2000--3000之间的员工信息

SQL> select * from emp where sal>=2000 and sal<=3000;

SQL> select * from emp where sal between 2000 and 3000;//包含2000和3000 ▼查询出编号为7369,7499,7788的员工信息

SQL> select * from emp where empno=7369 or empno=7499 or empno=7788;

SQL> select * from emp where empno in(7369,7499,7788);

▼查询出工作岗位不为MANAGER的员工信息

SQL> select * from emp where job!='MANAGER';//oracle中不等号可以用!= 或<>

SQL> select * from emp where not job='MANAGER';

▼查询出20号部门中工资高于或等于2000的员工信息

SQL> select * from emp where deptno=20 and sal>=2000;

▼查询出10号部门中的所有经理，和20号部门中不是经理但是工资高于2000的员工信息SQL> select * from emp where (deptno=10 and job='MANAGER') or

(deptno=20 and job!='MANAGER' and sal>2000);

▼查询出入职时间早于1981年-6-30号，工作岗位是经理，但是工资低于3000的员工信息SQL> select * from emp where hiredate<'30-6月-1981' and job='MANAGER' and sal<3000;

★模糊查询like

_:代表一个任意字符

%:代表0到多个任意字符

▼查找出姓名第一个字母是S的员工信息

SQL> select * from emp where ename like 'S%';

▼查找出姓名第二个字母是L的员工信息

SQL> select * from emp where ename like '_L%';

▼查找出姓名最后一个字母是N的员工信息

SQL> select * from emp where ename like '%N';

▼查找出姓名中包含字母A的员工信息

SQL> select * from emp where ename like '%A%';

▼查找出姓名中不包含字母A的员工信息

SQL> select * from emp where ename not like '%A%';

▼查找出姓名长度为5个字母的员工信息

SQL> select * from emp where ename like '_____';

★排序order by [asc/desc]

asc：从小到大(默认为asc)

desc：从大到小

▼查询出emp表中员工信息，要求按照工资从低到高排序

SQL> select * from emp order by sal asc;//asc可以省略

▼查询出emp表中员工信息，要求按照工资从高到低排序

SQL> select * from emp order by sal desc;

▼查询出emp表中员工姓名，工资，年薪，并且按照年薪的从高到低排序(考虑奖金)

SQL> select ename,sal,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪" desc;

▼(多列排序)查找出员工信息，按照部门从小到大排序，在部门内按照工资从大到小排序

SQL> select * from emp order by deptno asc,sal desc;

▼(多列排序)查找出员工信息，按照部门从大到小排序，在部门内按照工资从大到小排序

SQL> select * from emp order by deptno desc,sal desc;

★分组查询

使用分组函数max,min,avg,sum,count;

▼查询出emp表中最高工资

SQL> select max(sal) from emp;

▼查询出emp表中工资总和

SQL> select sum(sal) from emp;

▼查询出emp表中最高工资的员工姓名

正确做法如下:

SQL> select ename from emp where sal=(select max(sal) from emp);

▼查询出emp表中工资高于平均工资的员工信息

SQL> select * from emp where sal>(select avg(sal) from emp);

使用group by 和having

▼查询出每个部门的最高，最低，平均工资

SQL> select deptno,max(sal),min(sal),avg(sal) from emp group by deptno;

▼查询出emp表中每个部门的工资总和

SQL> select deptno, sum(sal) from emp group by deptno;

▼查询每个部门的平均工资，显示其中平均工资高于2000的部门

SQL> select deptno, avg(sal) from emp group by deptno having avg(sal)>=2000;

▼查询出不同工作的最高工资，最低工资

SQL> select job,max(sal),min(sal) from emp group by job;

▼查询出不同工作的最高工资，最低工资,结果根据最高工资从高到底排序

SQL> select job,max(sal),min(sal) from emp group by job order by max(sal) desc;

▼查询出不同工作岗位的平均工资，只显示平均工资高于1500的信息，并且平均工资按照从低到高显示

SQL> select job,avg(sal) from emp group by job having avg(sal)>1500 order by avg(sal);

注意:

当SQL语句查询的列中出现了分组函数max,min,avg,sum时，如果还有其他列，必须要对其他列使用group by分组，否则会报错。

当SQL语句中出现group by,order by,having时，他们的顺序是group by》》having》》order by

二、Oracle中的复杂查询

★多表查询

在实际情况中，很多时候查询一张表不能满足要求，此时需要查询多张表来达到要求。

（如果n个表查询，至少需要n-1个条件才不会出现笛卡尔积）

▼从emp和dept表中查询出每个员工姓名，工作，部门名，部门所在地

SQL> select ename,job,dname,loc from emp,dept where emp.deptno=dept.deptno;

//前面的查询的列，最好指明是哪个表的，因为如果两个表中有同名的列就会报错。

SQL> select emp.ename,emp.job,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;

//如果感觉表名太长，可以给表取别名

SQL> select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

▼查询出10号部门中员工的员工编号，员工姓名，工资，部门名

SQL> select empno, ename, sal, dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

▼从emp表和salgrade表中查询出员工姓名，工资，工资级别

SQL> select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;

▼从emp表和salgrade表中查询出工资在3级的员工姓名，工作岗位，工资，所在部门编号。

SQL> select e.ename,e.job,e.sal,e.deptno from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal and s.grade=3;

▼（自连接）查询出emp表中FORD的姓名，上级编号，上级姓名

SQL> select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.ename='FORD';

▼（自连接）查询出emp表中入职时间比自己上级早的员工姓名，入职时间，上级姓名，上级入职时间SQL> select e1.ename,e1.hiredate,e2.ename,e2.hiredate from emp e1,emp e2 where e1.mgr=e2.empno and e1.hiredate

★子查询

在查询语句中还嵌套有查询语句。

●单行子查询(子查询返回一条记录)

▼查询出与SMITH在同一个部门的员工信息

第一步:查询出SMITH所在部门编号

第二步:根据第一步的结果进行查询

第三步:合并第一、二部

▼查询出比FORD入职早的员工信息

第一步:查询出FORD的入职时间

第二步:根据第一步结果进行查询

第三步:合并第一二步

▼查询出与FORD同一天入职或者工资相同的员工信息,不包含FORD自己。

SQL> select * from emp where (hiredate=(select hiredate from emp where ename='FORD') or sal=(select

sal from emp where ename='FORD')) and ename!='FORD';

●多行子查询(子查询返回多条记录)

▼(all)查询出比20号部门所有员工工资都要高的员工信息

方法一:使用all，all代表所有

SQL> select * from emp where sal>all(select sal from emp where deptno=20);

方法二:使用max函数

SQL> select * from emp where sal>(select max(sal) from emp where deptno=20);

▼(any)查询出比20号部门任意一个员工工资都要高的员工信息

方法一:使用any,any代表任意一个

SQL> select * from emp where sal>any(select sal from emp where deptno=20) and deptno!=20;

方法二:使用min函数

SQL> select * from emp where sal>(select min(sal) from emp where deptno=20) and deptno!=20;

▼(in)查询出与20号部门中任意一个员工工作相同的员工信息

SQL> select * from emp where job in(select distinct job from emp where deptno=20) and deptno!=20;

▼查询出emp表中比所有经理工资都要高的员工信息

方法一:使用all，all代表所有

SQL> select * from emp where sal>all(select sal from emp where job='MANAGER');

方法二:使用max函数

SQL> select * from emp where sal>(select max(sal) from emp where job='MANAGER');

▼查询出emp表中比任意一个经理工资高的员工信息

方法一:使用any,any代表任意一个

SQL> select * from emp where sal>any(select sal from emp where job='MANAGER') and job=!'MANAGER';

方法二:使用min函数

SQL> select * from emp where sal>(select min(sal) from emp where job='MANAGER') and job=!'MANAGER';

●多列子查询(子查询中返回多列)

▼查询出与SMITH同一个部门，且工作岗位相同的员工信息，不包含SMITH

//使用普通子查询

SQL> select * from emp where (job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH')) and ename!='SMITH';

//使用多列子查询

SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename!='SMITH';

●from子查询

▼查询出高于自己部门平均工资的员工姓名，工资，部门编号，部门平均工资

第一步:查询出各个部门的平均工资

第二步:将第一步查询到的结果当做a表，连接a表和emp表进行查询

SQL>select e.ename,e.sal,a.deptno,a.avg_sal from emp e,a where e.deptno=a.deptno and e.sal>a.avg_sal;

第三步:用select deptno,avg(sal) avg_sal from emp group by deptno来替换a

SQL>select e.ename,e.sal,a.deptno,a.avg_sal from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) a where e.deptno=a.deptno and e.sal>a.avg_sal;

★合并查询

在SQL语句中，有时需要合并多条SQL语句的查询结果，可以使用结合操作符

union,union all，intersect，minus

并并交差

A {1,2,3}

B {3,4,5}

A union

B = {1,2,3,4,5}

A union all

B ={1,2,3,3,4,5}

A intersect

B ={3}

A minus

B ={1,2}

▼union 查询出，工资>=2500 或者工作为MANAGER的员工信息

SQL> select * from emp where sal>=2500 or job='MANAGER';

SQL>( select * from emp where sal>=2500) union (select * from emp where job='MANAGER');

▼union all和union很像，但是他不会取消两个中重复的行。

SQL>( select * from emp where sal>=2500) union all (select * from emp where job='MANAGER');

▼intersect 查询出emp中工资>=2500 且工作为MANAGER的员工信息

SQL> select * from emp where sal>=2500 and job='MANAGER';

SQL> ( select * from emp where sal>=2500) intersect (select * from emp where job='MANAGER');

▼minus 查询出emp中工资>=2500 且工作不为MANAGER的员工信息

SQL> select * from emp where sal>=2500 and job!='MANAGER';

SQL> ( select * from emp where sal>=2500) minus (select * from emp where job='MANAGER');

三、Oracle中的分页查询

在实际应用中，对数据分页基本上每个项目都应用到了。分页查询有多种方法可以完成，eg:rowid、根据函数、根据rownum。我们主要讲解rownum分页。

rownum是一个伪列,每一查询结果都有一个rownum字段，rownum用来记录查询出来的数据在第几行。rownum这一个字段并没有保存在数据库中，是随着SQL语句执行完成而产生的.

▼查询出emp表中所有员工信息和rownum

SQL> select emp.*,rownum from emp;

▼查询出emp表中前5名员工信息

SQL> select emp.*,rownum from emp where rownum<=5;

思考:能不能查询rownum>=10的数据?

不行，因为rownum是执行完SQL语句之后才生成的。所以注意rownum不能使用>,>=,=多少

▼查询出emp表中第4—6条记录

第一步:查询出前6条

SQL> select emp.*,rownum rn from emp where rownum<=6;

第二步:查询出第一步结果中的4—6条

SQL>select * from (第一步的结果) where rn>=4;

第三步:合并第一二部

SQL>select * from (select emp.*,rownum rn from emp where rownum<=6) where rn>=4;

注意:因为有时候涉及到排序，所以一般标准写法如下:

SQL>select * from (select e.*,rownum rn from (select * from emp) e where rownum<=6) where rn>=4;

▼查询出emp表中工资最高的三位员工信息,不考虑奖金

SQL> select * from (select e.*,rownum rn from (select * from emp order by sal desc) e where rownum<=3) where rn>=1; ▼查询出emp表中工资第二高的员工信息,不考虑奖金

SQL> select * from (select e.*,rownum rn from (select * from emp order by sal desc) e where rownum<=2) where rn>=2; ▼查询出emp表中工资排在第3—5的员工信息,不考虑奖金

SQL> select * from (select e.*,rownum rn from (select * from emp order by sal desc) e where rownum<=5) where rn>=3;