1.什么是多表查询?
一次select语句需要查询的内容来自于不止一张表。
同时从多张表中查询数据。
单表查询:
select id,last_name,salary
from s_emp
where salary > 1400 and dept_id in(41,42)
order by salary desc,id asc;
单表查询能查到的数据是有限的。
查询所有员工的id、last_name?
select id,last_name
from 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 ...
from 表1,表2,表3.....
where ...
order by ....;
出现字段名冲突时,要使用 表名.列名 的形式,
声明可能出现冲突的字段来自于哪张表。
select s_emp.id,last_name,s_dept.id,name
from s_emp,s_dept;
还可以给表名起一个别名。
起到的别名可以在整个select语句中代替表名使用。
语法:
select 别名1.字段,别名2.字段......
from 表1 别名1,表2 别名2;
查询所有员工的id、last_name以及
所在部门的id、name?
select s1.id,s1.last_name,s2.id,s2.name
from s_emp s1,s_dept s2;
2.消除笛卡尔积
笛卡尔积是数学运算中集合进行乘法运算所产生的结果集。
A = { 1 , 2 , 3 }
B = { a , b }
计算:A x B = {(1,a),(1,b),(2,a),(2,b)
(3,a),(3,b)}
一共有2x3=6个结果。
数据库进行多表查询,就会产生笛卡尔积。
表1:学生姓名表
学号 姓名
1 张三
2 李四
3 王五
-------------------
表2:学生成绩表
学号 分数
1 80
2 90
3 70
要求:查询所有学生的id、姓名以及成绩?
1 张三 1 80
1 张三 2 90
1 张三 3 70
2 李四 1 80
2 李四 2 90
2 李四 3 70
3 王五 1 80
3 王五 2 90
3 王五 3 70
思路:
使用where关键字增加、指定查询条件,
从笛卡尔积中把不需要的数据筛除出去。
1)等值连接
将两张表中产生关联的字段使用等号进行连接。
查询所有员工的id、last_name以及
所在部门的id、name?
字段:s_emp.dept_id = s_dept.id
select s1.id,s1.last_name,s2.id,s2.name
from s_emp s1,s_dept s2
where s1.dept_id = s2.id;
2)不等值连接
大于
大于等于
小于
小于等于
逻辑比较符:between in
查询所有员工id、last_name以及
工资收入等级?
select e.id,e.last_name,g.name
from s_emp e,s_gender g
where e.salary between g.minSal and g.maxSal;
或者:where e.salary >=g.minSal
and e.salary <= g.maxSal;
表1:员工表
id salary
1 900
2 1300
3 2100
表2:工资等级表
最小值 最大值 等级名称
0 1000 蓝领
1000 1500 白领
1500 2500 金领
结果:
员工ID 员工工资 最小值 最大值 等级名称
1 900 0 1000 蓝领
1 900 1000 1500 白领
1 900 1500 2500 金领
2 1300 0 1000 蓝领
2 1300 1000 1500 白领
2 1300 1500 2500 金领
3 2100 0 1000 蓝领
3 2100 1000 1500 白领
3 2100 1500 2500 金领
3)外连接
a)左外连接
查询所有员工的id、last_name以及
所在部门的id、name?
select s1.id,s1.last_name,s2.id,s2.name
from s_emp s1,s_dept s2
where s1.dept_id = s2.id;
SQL:insert into s_emp(id,last_name)
values(999,'_briup');
查询所有员工的id、last_name以及
所在部门的id、name?要求把没有部门的员工
也显示出来?
思路:让员工表 左外连接 到部门表。
左外连接:
A左外连接到B,就可以查出来没有B的A。
语法:
1)标准SQL
select...
from 表1 left [outer] join 表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;
两张表顺序不能颠倒。
2)Oracle特色语法
b)右外连接
右外连接和左外连接就是相反的。
A表左外连接到B表,相当于B表右外连接到A表。
语法:
select...
from 表1 right [outer] join 表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?要求把没有员工的部门也查询出来?
使用左外连接和右外连接两种方式。
c)全连接