三人行之一屋二夫,电脑报价大全,讲座网
对于查询在之前已经学过了简单查询、限定查询、查询排序,这些都属于 sql 的标准语句,而上一章的单行函数,主要功能是为了弥补查询的不足。
而从多表查询开始就正式进入到了复杂查询部分。
多表查询就是在一条查询语句中,从多张表里一起取出所需要的数据。如果要想进行多表查询,直接在 from 子句之后跟上多个表即可,语法如下:
select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],... from 表名称1[表别名1],表名称2[表别名2],... [where 条件(s)] [order by 排序的字段1 asc|desc,排序的字段2 asc|desc,...];
下面就将采用 emp 表和 dept 表一起进行多表查询,查询之前多做一个步骤,先确定 emp 和 dept 表中的数据量分别有多少,可以使用 count() 函数统计
范例:统计 emp 表中的数据量 (14 行记录)
select count(*) from emp;
范例:统计 dept 表中的数据量 (4行记录)
select count(*) from dept;
范例:现在查询所有的雇员和部门的全部详细信息
select count(*) from emp,dept;
范例:利用等值条件来处理笛卡尔积
select * from emp,dept where emp.deptno = dept.deptno;
范例:查看 sh.sales 表的数据量 (918843条记录)
select count(*) from sh.sales;
范例:查看 sh.costs 表的数据量 (82112条记录)
select count(*) from sh.costs;
范例:如果现在直接将这两张表进行多表查询,那么来观察问题
select count(*) from sh.sales,sh.costs;
范例:查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息
select emp.empno,emp.ename ,emp.job,emp.sal,dept.dname,dept.loc from emp,dept where emp.deptno = dept.deptno
select e.empno, e.ename, e.job, e.sal, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;
范例:查询出每个雇员的编号、姓名、雇佣日期、基本工资、工资等级
select e.empno, e.ename, e.hiredate, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
范例:为了更加清楚的显示出工资等级的信息,现在希望可以按如下格式进行替换显示:
grade = 1 : 显示为 “ e等工资 ”
grade = 2 : 显示为 “ d等工资 ”
grade = 3 : 显示为 “ c等工资 ”
grade = 4 : 显示为 “ b等工资 ”
grade = 5 : 显示为 “ a等工资 ”
select e.empno, e.ename, e.hiredate, e.sal, decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
范例:查询出每个雇员的姓名、职位、基本工资、部门名称、工资等级
select e.ename, e.job, e.sal, d.dname, decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') grade from emp e, dept d, salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal;
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(8888,'李华华','clerk',7369,sysdate,800,100,null);
select * from emp;
范例:使用等值连接
select * from emp e, dept d where e.deptno = d.deptno;
范例:使用左外连接,显示雇员编号是8888的信息
select * from emp e, dept d where e.deptno = d.deptno(+);
范例:使用右外连接,显示部门编号为40的信息
select * from emp e, dept d where e.deptno(+) = d.deptno;
select * from emp;
范例:查询出每个雇员的编号、姓名及其上级领导的编号、姓名
select e.empno eno, e.ename ename, m.empno mno, m.ename mname from emp e, emp m where e.mgr = m.empno;
现在表中一共有15条记录,但是只有14条的记录显示,等值连接在没有条件满足的时候,是不可能有数据显示的。
在 emp 表中 king 这个雇员是没有领导的,这个时候就必须考虑外连接。
select e.empno eno, e.ename ename, m.empno mno, m.ename mname from emp e, emp m where e.mgr = m.empno(+);
对于没有领导信息的雇员,对应的领导信息,全部使用 null 进行表示
范例:查询出在1981年雇员的全部雇员的编号、姓名、雇佣日期(按照年-月-日显示)、工作、领导姓名、雇员月工资、雇员年工资(基本工资+奖金)、雇员工资等级、部门编号、部门名称、部门位置、并且要求这些雇员的月基本工资在1500~3500之间,将最后的结果按照年工资的降序排列,如果年工资相等,则按工作进行排序
select e.empno, e.ename, e.hiredate, e.sal, (e.sal+nvl(e.comm,0))*12 income from emp e where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500;
select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income, m.ename mname from emp e, emp m where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500 and e.mgr = m.empno(+);
select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income, m.ename mname, d.deptno dno, d.dname dname, d.loc from emp e, emp m, dept d where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500 and e.mgr = m.empno(+) and e.deptno = d.deptno;
select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income, m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade from emp e, emp m, dept d,salgrade s where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500 and e.mgr = m.empno(+) and e.deptno = d.deptno and e.sal between s.losal and s.hisal;
select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income, m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade, decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') 工资等级 from emp e, emp m, dept d,salgrade s where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500 and e.mgr = m.empno(+) and e.deptno = d.deptno and e.sal between s.losal and s.hisal;
select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income, m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade, decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') 工资等级 from emp e, emp m, dept d,salgrade s where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500 and e.mgr = m.empno(+) and e.deptno = d.deptno and e.sal between s.losal and s.hisal; order by income desc, e.job;
select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],... from 表1 表别名1 [cross join 表2 表别名2]| [natural join 表2 表别名2]| [join 表2 using(关联列名称)]| [join 表2 on(关联条件)]| [left|right|full outer join 表2 on(关联条件)] [where 条件(s)] [order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];
select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],... from 表1 表别名1 [cross join 表2 表别名2]| [where 条件(s)] [order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];
范例:使用交叉连接查询信息
select * from emp cross join dept;
select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],... from 表1 表别名1 [natural join 表2 表别名2]| [where 条件(s)] [order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];
范例:使用自然连接查询信息
select * from emp natural join dept;
select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],... from 表1 表别名1 [join 表2 using(关联列名称)]| [where 条件(s)] [order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];
范例:使用 using 子句查询信息
select * from emp join dept using(deptno);
select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],... from 表1 表别名1 [join 表2 on(关联条件)]| [where 条件(s)] [order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];
范例:使用 on 子句查询信息
select * from emp e join salgrade s on(e.sal between s.losal and s.hisal);
select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],... from 表1 表别名1 [left|right|full outer join 表2 on(关联条件)] [where 条件(s)] [order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];
范例:实现右外连接
select * from emp e right outer join dept d on (e.deptno = d.deptno);
范例: 实现左外连接
select * from emp e left outer join dept d on (e.deptno = d.deptno);
范例:实现全外连接
select * from emp e full outer join dept d on (e.deptno = d.deptno);
查询语句 [union | union all | intersect | minus] 查询语句 ...
范例:并集操作:union , union all
select * from dept;
select * from dept where deptno = 10;
这个时候两个查询结果返回的列的结构相同。
select * from dept union select * from dept where deptno = 10;
第一个查询已经包含了第二个查询的内容,所以重复数据不显示了。
范例:使用 union all 显示全部
select * from dept union all select * from dept where deptno = 10;
范例:查询所有销售人员和办事人员的信息
select * from emp where job = 'salesman' or job = 'clerk';
select * from emp where job in ('salesman', 'clerk');
select * from emp where job = 'salesman' union select * from emp where job = 'clerk'
范例:使用 minus 执行差集操作
select * from dept minus select * from dept where deptno = 10;
7.6.3 交集操作
范例:使用 intersect 执行交集操作
select * from dept intersect select * from dept where deptno = 10;
说明:本学习资料是根据李兴华的oracle开发实战经典整理
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
Oracle字符串函数length、upper、lower、initcap、 concat、instr、replace
网友评论