1 DML语言
2 多表查询
2.1 连接表查询
2.1.1 内连接
2.1.2 外连接
2.2 自连接查询
3 子查询
4 综合练习
5 exists关键字
6 分页查询
7 集合运算符
1 DML语言
1.1 insert
插入一条数据到员工表
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1001,'李四','研发部',7788,sysdate,100,20000,10);
创建一个emp表的备份表emp_bak,要求表结构和emp一样
create table emp_bak as select * from emp where 1=2;
注意:条件1=2,表示false的意思,查询的结果是没有数据的。所有使用1=2表示创建一个没有数据的表结构将emp表的数据复制到emp_bak表里面
insert into emp_bak select * from emp;
复制数据的语法:insert into <表名> select * from <表名>
1.2 delete
删除员工编号为1000的员工
delete from emp where empno=1000;
删除员工表所有数据
delete from emp;
截断表数据(truncate)
删除员工表的所有数据。(truncate的效率要比delete高)
truncate table emp;
1.3 update
- 更新员工编号为1001的员工的工资为2000
update emp set sal=2000 where empno=1001;
2 多表查询
多表查询就是查询语句中有两个以上的表。
2.1 连接表查询
- 查询员工的信息包括部门的信息(以部门编号为连接条件)
select * from emp,dept where emp.deptno=dept.deptno;
我们将同时查询两个表,再使用where连接条件的格式的多表查询,称为等值查询
2.1.1 内连接
内连接:同时查询的两个关联表,只要任何一个表找不到关联表对应的数据,就会忽略不显示该数据(等同值查询)。
语法:select * from <左表> inner join <右表> on <关联条件>
- 查询员工的信息,包括部门的信息
select * from emp inner join dept on emp.deptno=dept.deptno;
2.1.2 外连接
左外连接
左外连接:同时查询两个表,以左表为主表,主表的数据会被全部查询出来;如果找不到对应右表的数据,右表显示的字段值为null。
语法:select * from <左表> left [outer] join <右表> on <关联条件>
- 查询员工的信息包括部门的信息。要求显示所有的员工信息
select * from emp left outer join dept on emp.deptno=dept.deptno;
outer可省略
右外连接
右外连接:同时查询两个表,以右表为主表,主表的数据会被全部查询出来;如果找不到对应左表的数据,左表显示的字段值为null。
- 查询员工的信息包括部门的信息。要求显示所有部门的信息
select * from emp right outer join dept on emp.deptno=dept.deptno;
outer可省略
现实中编写SQL语句尽量使用外连接。因为外连接的效率高于内连接。
Oracle中外连接的特殊写法:(了解)
在条件上加“+”,标识的是非主表!
select * from emp inner join dept on emp.deptno(+)=dept.deptno;
–右外连接
select * from emp inner join dept on emp.deptno=dept.deptno(+);
–左外连接
2.2 自连接查询
自连接查询,就是连接的两个表时相同的。自连接是一个特殊的连接表查询。
- 查询员工上司的名字
员工的信息:select * from emp;
上司的名字:select ename from emp;
连接:select a.*,b.ename,b.empno from emp a inner join emp b on a.mgr=b.empno;
3 子查询
将有两个以上select的查询语句称为组合查询,而嵌套在另一个查询里面的查询语句,称为子查询。
- 查询和SCOTT员工部门相同的员工信息。
查询SCOTT员工的部门:select deptno from emp where ename = 'SCOTT';
员工信息:select * from emp;
组合:select * from emp where deptno = (select deptno from emp where ename = 'SCOTT');
子查询的三种情况:
子查询作为条件的值,在WHERE后面
select * from emp where deptno = (select deptno from emp where ename = 'SCOTT');
子查询作为表,在FROM后面
select e.avgsal from (select avg(sal) as avgsal from emp) e;
子查询作为一个查询的返回值,在SELECT后面(了解)
查询员工的部门名字,不能使用连接表查询:
select emp.*,(select dname from dept where deptno=emp.deptno) from emp;
4 综合练习
高级查询,就是查询语句里面包括了多表查询以及子查询。
高级查询的思路:遇到复杂的查询语句,先拆分,后组合!
组合的三种情况:单行单列;多行单列;多行多列
单行单列
单行单列就是组合的条件是一个值。
- 查询工资比SCOTT用户高的员工
SCOTT的员工工资:select sal from emp where ename ='SCOTT';
员工信息:select * from emp;
组合:select * from emp where sal>(select sal from emp where ename ='SCOTT') ;
多行单列
多行单列就是组合的条件是多个值的列表。
- 查询工资比名字中带“U”的员工高的员工信息
名字中带“U”的员工工资:select sal from emp where ename like '%U%';
员工信息:select * from emp;
组合:select * from emp where sal > (select max(sal) from emp where ename like '%U%');
多行多列
多行多列就是组合的条件是一个表。
查询每一个部门中工资最低的员工信息
每一个部门中最低的工资 :select deptno,min(sal) from emp group by deptno;
员工信息:select * from emp;
组合:
select * from emp,(select deptno,min(sal) as minsal from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal = tmp.minsal;
查询工资大于本岗位平均工资的员工信息
本岗位平均工资(按岗位分组,求平均工资):select job ,avg(sal) from emp group by job;
员工信息:select * from emp;
组合:
select * from emp,(select job ,avg(sal) as avgsal from emp group by job) tmp where emp.job = tmp.job and emp.sal > tmp.avgsal;
查询工资大于最小工资且小于最大工资的员工信息
最大工资和最小工资:select max(sal),min(sal) from emp;
员工信息:select * from emp;
组合:
select * from emp,(select max(sal) as maxsal,min(sal) as minsal from emp) tmp where emp.sal > tmp.minsal and emp.sal < tmp.maxsal;
5 exists关键字
语法:select * from <表> where exsits(<子查询>)
子查询有值:返回true;
select * from <表> where exsits(有值) 等同于 select * from <表> where 1=1
子查询无值:返回false;
select * from <表> where exsits(无值) 等同于 select * from <表> where 1=2
查询有部门的员工的信息(必须员工表的deptno和部门表的deptno都有对应的记录)
select * from emp where exists(select deptno from dept where deptno=emp.deptno);
exsits的应用场景,清除脏数据。
select * from emp where not exists(select deptno from dept where deptno=emp.deptno);
6 分页查询
Oracle是不支持像MySQL那样的limit关键字的,它是通过伪列rownum实现分页的。
Oracle数据库,在查询时会为每一条记录创建一个序号,这个序号就是伪列rownum。每一个select都会重新产生一个rownum。
注意:返回结果有两个或两个字段以上,*必须要加上表名或表别名限制范围
select emp.* , rownum from emp;
获得员工表中1-10的数据
select emp.* , rownum from emp where rownum <=10;
需求:获得员工表中5-10的数据
错误代码:select emp.*,rownum from emp where rownum <=10 and rownum >=5;
rownum不能直接使用大于或者大于等号(因为第一次判断条件时就为false了)
只能间接使用。先生成rownum后再使用子查询
select * from (select emp.* , rownum as r from emp) tmp where tmp.r>=5 and tmp.r<=10;
需求:员工表按部门排序后,获得5-10的数据
select emp.*,rownum from emp order by deptno;
上面代码排序后rownum序号就乱了。原因:from>where>group by>having>select>order by
如何解决?使用子查询实现排序后再生成rownum
先排序,后生成rownum:select tmp.*,rownum as r from(select emp.* from emp order by deptno) tmp;
再使用子查询解决不能使用大于号的问题:select * from (select tmp.*,rownum as r from(select emp.* from emp order by deptno) tmp)tmp1 where tmp1.r>=5 and tmp1.r<=10;
7 集合运算符(了解)
union
- 求工资在1600~3000 并集 2000-5000的员工信息,包括上下限
select * from emp where sal between 1600 and 3000 union select * from emp where sal between 2000 and 5000;
union all
- 求工资在1600~3000 并集 2000-5000的员工信息,包括上下限。包括重复的
select * from emp where sal between 1600 and 3000 union all select * from emp where sal between 2000 and 5000;
intersect
- 求工资在1600~3000 交集 2000-5000的员工信息,包括上下限
select * from emp where sal between 1600 and 3000 intersect select * from emp where sal between 2000 and 5000;
minus
- 求工资在1600~3000 减去 2000-5000的差集员工信息,包括上下限
select * from emp where sal between 1600 and 3000 minus select * from emp where sal between 2000 and 5000;
集合运算符效率非常低。能不用尽量不用。
以下是SCOTT数据表的关系图