oracle数据库学习笔记（五）

第五章 多表查询

1.什么是多表查询？
同时查询不止一张表。
要查询的数据来自于不同的表中。

单表查询：所查询的所有内容都来自于同一张表
select id,last_name,salary
from s_emp
where dept_id = 41 and salary >1400
order by salary desc,id asc;
单表查询可以查到的内容是有限的。

多表查询语法：
在from子句后面使用逗号并列要查询的多张表名
select 字段1，字段2，字段3....
from 表名1,表名2
where 条件...
order by 字段 排序规则;

查询所有员工的id、last_name?
select id,last_name
form s_emp;

查询所有部门的id、name？
select id,name
from s_dept;

要求：查询所有员工的id、last_name以及
所在部门的id、name？
select id,last_name,id,name
from s_emp,s_dept;

当字段名冲突时，
需要使用 表名.列名的形式声明要查询的字段
具体来自于哪张表。
select s_emp.id,last_name,s_dept.id,name
from s_emp,s_dept;

还可以给表起一个别名。
select 字段
from 表名1 别名1,表名2 别名2.....;
select s1.id,last_name,s2.id,name
from s_emp s1,s_dept s2;

2.消除笛卡尔积
笛卡尔积是数学运算中集合相乘所产生的结果 。

A = { 1 , 2 , 3}
B = { a , b , c}

A x B = {(1,a),(1,b),(1,c),
(2,a),(2,b),(2,c),
(3,a),(3,b),(3,c)}
3x3=9个结果。

在数据库中进行多表查询就会产生笛卡尔积。

表1：学生姓名表
学号 姓名
1 张三
2 李四
3 赵六

表2：学生成绩表
学号 成绩
1 90
2 80
3 59

查询：每一个学生姓名以及成绩？

学号 姓名 学号 成绩
1 张三 1 90
2 李四 1 90
3 赵六 1 90

1 张三 2 80
2 李四 2 80
3 赵六 2 80

1 张三 3 59
2 李四 3 59
3 赵六 3 59

思路：
通过where子句添加查询时的条件限制，
用来消除掉无效、没有意义的结果。

1)等值连接
使用等号将两张表中有关联的字段连接起来。
关系：员工表的dept_id字段 等于 部门表中的id字段
select s1.id,last_name,s2.id,name
from s_emp s1,s_dept s2
where s1.dept_id = s2.id;

2)不等值连接
查询：所有员工的id、salary以及收入等级？
s_emp、s_gender 做关联查询

select e.id,e.salary,g.name
from s_emp e,s_gender g
where e.salary between g.minSal and g.maxSal;

员工表
id salary
1 1200
2 2400
3 1700

收入等级表
最小值 最大值 等级名称
0 1000 蓝领
1000 1500 白领
1500 2500 金领

1 1200 0 1000 蓝领
2 2400 0 1000 蓝领
3 1700 0 1000 蓝领

1 1200 1000 1500 白领
2 2400 1000 1500 白领
3 1700 1000 1500 白领

1 1200 1500 2000 金领
2 2400 1500 2000 金领
3 1700 1500 200 金领

3)外连接
a)左外连接
查询所有员工的id、last_name以及
所在部门的id、name？
select e.id,e.last_name,d.id,d.name
from s_emp e,s_dept d
where e.dept_id = d.id;

查询所有员工的id、last_name以及
所在部门的id、name？要求把没有部门的员工
也查询出来？
思路：需要让员工表左外连接到部门表。
就可以查出来没有部门的员工。
select e.id,e.last_name,d.id,d.name
from s_emp e left join s_dept d
on e.dept_id = d.id;

A表左外连接到B表，就可以查出没有B的A。

语法：
标准SQL：一套标准、规范，在任何一个关系型数据库
都可以执行的SQL命令。
select ....
from 表1 别名1 left [outer] join 表2 别名2
on 连接条件;

b)右外连接
A表左外连接到B表，就相当于
B表右外连接到A表。
语法：
标准SQL：
select ....
from 表1 别名1 right [outer] join 表2 别名2
on 连接条件;

select e.id,e.last_name,d.id,d.name
from s_emp e left join s_dept d
on e.dept_id = d.id;
等同于：
select e.id,e.last_name,d.id,d.name
from s_dept d right join s_emp e
on e.dept_id = d.id;

先向数据库中插入一条数据：
insert into s_dept(id,name)
values(1000,'Teaching');
commit;

练习:查询所有员工的id、last_name以及
所在部门的id、name？要求把所有没有
员工的部门也查询出来？
关系：部门表 左外连接到 员工表;
员工表 右外连接到 部门表;
select e.id,e.last_name,d.id,d.name
from s_dept d left join s_emp e
on e.dept_id = d.id;

select e.id,e.last_name,d.id,d.name
from s_emp e right join s_dept d
on e.dept_id = d.id;

Oracle特色语法：
左外连接：
使用where关键字声明两张表的连接关系，
然后把(+)放到数据较少的一方。
查询.....要求把没有部门的员工也显示出来？
select e.id,e.last_name,d.id,d.name
from s_emp e,s_dept d
where e.dept_id = d.id(+);

查询.....要求把所有的部门都显示出来？
select e.id,e.last_name,d.id,d.name
from s_emp e,s_dept d
where e.dept_id(+) = d.id;

c)全连接
全连接就等同于同时使用左外连接和右外连接。
注意：全连接没有Oracle特色语法。
必须使用标准SQL(full join ... on ...)。

例如：查询所有员工的id、last_name，以及
对应部门的id、name？
要求把没有部门的员工和没有员工的部门都显示出来？
select e.id,e.last_name,d.id,d.name
from s_emp e full join s_dept d
on e.dept_id = d.id;

select e.id,e.last_name,d.id,d.name
from s_dept d full join s_emp e
on e.dept_id = d.id;

语法：
select ...
from 表1 别名1 full [outer] join 表2 别名2
on 连接条件;

A全连接到B，就可以查出来没有A的B以及没有B的A。

4)自连接
一张表自己和自己产生关联。
虽然是一张表，但是查询时当成两张表使用。

例如：
查询所有员工的id、last_name
以及经理的id、last_name？

select s1.id,s1.last_name,s2.id,s2.last_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id;

思考：如何查出所有没有员工的经理？
如何查出所有没有经理的员工？

5)集合连接

3.Oracle数据库中的伪列
在Oracle数据库中，存在一种比较特殊的字段，叫伪列。
不是一种真正含义的字段。
不包含在任何一张表中，但是出现在任何一次select查询
结果之中。

1)rowid
用来标识当前数据的物理存储位置。
例如：
select id,last_name,rowid
from s_emp;
2)rownum
用来标识当前表中数据的条数。
注意：rownum字段必须从1开始。
先有结果集，才有标号。
select id,last_name,rownum
from s_emp;

用途？
分页查询
例如：
查询员工表中前十条数据？
select id,last_name
from s_emp
where rownum <= 10;

查询11-20条数据？
使用子查询(嵌套查询)
错误写法：
select id,last_name
from s_emp
where rownum >= 11 and rownum <=20;

在web应用程序中分页的实现过程:
1)基于前端的分页
后台代码把所有数据都从数据库中查出，
然后全部返回给前端页面。
在前端页面通过JS等技术，控制数据的显示和隐藏。
以此来达到分页的效果。
不推荐使用。
2)
3)