---多表联合查询
---当需要获取的数据分布在多张表中使用多表联合查询
---SQL92方式
---笛卡尔积:将多个表的数据进行一一对应,所得到结果为多表的笛卡尔积.
---结果数量为所有表数量的乘积
seelct * from emp,dept;
---等值链接
----先做表的笛卡尔积,然后筛选,筛选条件为等值筛选.
---查询员工的姓名,工作,薪资,部门名称
select * from emp,dept where dept.deptno=emp.deptno;---等值连接查询
---可以直接在select子句中使用字段直接获取数据,但是效率低,建议字段前加上表名
---注意:如果是公共字段必须声明表名
select * from emp e,dept d where d.deptno=e.deptno;
---不等值链接
----查询员工的姓名,工作,工资,工资等级
select * from emp e,salgrade s where e.sal>=s.losal and e.sal<= s.hisal;
---自连接
----相当于自己和自己做笛卡尔积
----查询员工姓名,工作,薪资,以及上级领导姓名
select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
---外链接
----左外链接
---查询员工姓名,工作,薪资,部门名称以及没有部门的员工信息
select * from emp e,dept d where e.deptno=d.deptno(+);
----右外链接
---查询员工姓名,工作,薪资,部门名称以及没有员工的部门
select * from emp e,dept d where e.deptno(+)=d.deptno;
----SQL99多表查询
----注意1:依然可以给表添加别名
----注意2;如果使用on或则usering关键字必须对结果进行筛选,必须使用inner join关键字组员于表与表的连接,其中inner可以省去
----注意3:外连接的outer关键字可以不写
----注意4:依然可以使用排序等关键字
----1.笛卡尔积:使用cross join关键字
-----select 内容 from 表名 cross join
-----查询员工及其部门
select * from emp cross join dept;
----2.筛选
-----自然连接:使用关键字natrual join
----使用:select 内容 from 表名 natural join 表名
select* from emp natural join dept;
---特点1:底层是笛卡尔积,按照同名同值字段自动进行等值筛选
---缺点1:如果想按照字段名不同,但是字段值不同筛选怎么办?
----缺点2:如果只想按照部分字段结果筛选怎么办?
----解决1:使用using关键字
--作用1:指明使用指定的字段对联合查询的结果进行等值筛选
--注意:指明的字段必须是两边同名同值字段
--使用:select 内容 from 表名 inner join 表名 using(字段名)
select * from emp inner join dept using(deptno,ename);--底层相当于先做了一个笛卡尔积,再进行等值筛选
------解决2:使用on关键字,进行自定义链接查询
----注意:普通筛选使用where筛选不使用on 好处:便于阅读
----使用:select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
select * from emp inner join dept on emp.deptno=dept.deptno where job='SALESMAN';
------外连接
----左外连接:select 内容 from 表名 left outer join 表名 on 筛选条件
---查询员工姓名,工作,薪资,部门名称以及没有部门的员工信息
select * from emp e left outer join dept d on d.deptno=e.deptno;
----右外连接:select 内容 from 表名 right outer join 表名 on 筛选条件
---查询员工姓名,工作,薪资,部门名称以及没有员工的部门
select * from emp e right outer join dept d on d.deptno=e.deptno;
----全外连接:select 内容 from 表名 fill outer join 表名 on 筛选条件
select * from emp e full outer join dept d on d.deptno=e.deptno;
------自连接
-----查询员工姓名,工作,薪资,以及上级领导姓名
select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno;
----------------------------------------------------------------------------------------三表查询---------------------------------------------------------------------------
---SQL92&SQL99实现三表查询
select * from dept;
create table city(
cid number primary key,
cname varchar(100),
cdes varchar(100)
);
select * from city;
insert into city values (1,'商丘','历史闻名古都');
insert into city values (2,'邯郸','历史闻名古都');
insert into city values (3,'洛阳','历史闻名古都');
insert into city values (4,'开封','历史闻名古都');
select * from city;
select * from dept;
insert into dept values (50,'JAVA学院','上海');
select * from dept;
update dept set loc='1' where deptno=50;
update dept set loc='2' where deptno=40;
update dept set loc='3' where deptno=30;
update dept set loc='4' where deptno=20;
update dept set loc='4' where deptno=10;
select * from dept;
----完成三表联合查询
----SQL92实现:查询员工信息及所在部门及所在城市名称并且员工的工资大于2000或者有奖金
----特点:易于书写,不容易阅读
----缺点:92的SQL语句的结果不清奇
----使用:
----select 内容 (别名,连接符,去除重读,oracle函数,逻辑运算)
---from 表名1,表名2,表名3...
----where 条件(连接条件,普通筛选条件,where子句关键字)
----group by 分组字段
----having 多行函数筛选
----order by排序
select e.*,d.dname,c.cname from emp e,dept d,city c
where (e.deptno=d.deptno and d.loc=c.cid and e.sal>2000)
or(e.deptno=d.deptno and d.loc=c.cid and comm is not null)
----SQL99实现:查询员工信息及部门mc及所在城市
----特点:难于书写易于阅读
----使用:
---select 内容 from 表名1,
---inner join 表名3
--- on 连接条件
--- inner join 表名4
---on 连接条件
---where 普通筛选条件
--- group by 分组条件
--- having 多行函数筛选
---- order by 排序
select * from emp e
inner join dept d
on e.deptno=d.deptno
inner join city c
on d.loc=c.cid
where e.sal>2000 or comm is not null
order by e.sal