SQL数据库基础三 多表查询

表与表之间的关系

一对一关系(不常用 可以写成一张表)

如果考虑到效率问题  可以对表进行拆分 

多对多的关系(利用第三张表来表示关系)

并且第三张表作为从表 拥有其他两个主表的外键

ensample: 老师表 学生表 中间表 建立多对多的关系

CREATE table teacher(
	tid int primary key,
	tname varchar(20)
);
create table student(
	sid int primary key,
	sname varchar(20)
);
create table link(
	tid int,
	sid int
);
alter table link add constraint fk_teacher_link_tid foreign key(tid)
references teacher(tid);
alter table link add constraint fk_student_link_sid foreign key(sid)
references student(sid);

合并查询

union 两个表的并集(前提: 两个表字段名 类型相同)

select * from A union select * from B;

union all 把两个表的数据合并到一起

select * from A union all select * from B;

多表查询

注意: 多表查询 会产生笛卡尔积 (产生大量无用数据)

select * from A,B;

可以利用两张表中相同的字段 去除重复的无用数据(99查询法)

select * from student s ,score c where s.stuid = c.stuid;

利用别名查询学生编号 和 学生分数

select s.stuid,c.stuid from student s, score c where stuid=c.tuid;

三个表查询

select * from student, score, course;

去除错误数据(通过字段的联系)

select s.stuname,c.score,o.courseid from student s,score c,course o

where s.stuid=c.stuid and c.courseid=o.courseid;

连接查询(多表查询)

内连接(inner  可以省略)

on 后面是去除重复数据的条件

select * from student s inner join score c on s.stuid = c.stuid;

三个表的内连接查询

select * from student s inner join score c on s.stuid = c.stuid 

join course o on c.courseid=o.courseid;

外链接  左外连接、右外连接

关键词 outer (可以省略)

左外连接 就是以左边的表为主  可以查询出左边表的所有数据

右外连接同理

select * from student right outer join score
on student.stuid = score.stuid;

自然连接  natural join

可以自动匹配两个表中相同的字段的值(字段名和类型相同)

select * from 表1 natural join 表2; 

子查询 (嵌套查询)

创建 员工表和部门表

ensample: 查询工资高于JONES的员工信息

SELECT * from emp where sal>(

select sal from emp where ename='jones');

查询与SCOTT同一个部门的员工

SELECT * from emp where deptno=(
select deptno from emp where ename='SCOTT');

工资高于30号部门所有人的员工信息

SELECT * from emp where sal>(

SELECT MAX(sal) from emp where deptno=30);

查询工作和工资与MARTIN（马丁）完全相同的员工信息

方法一:

select * from emp where job = (
select job from emp where ename = 'MARTIN') and sal =(

select sal from emp where ename = 'MARTIN');

方法二:

select * from emp where (sal,job) in (

SELECT sal,job from emp where ename = 'MARTIN');

查询有2个以上直接下属的员工信息

select * from emp where empno in(

select mgr from emp group by mgr having count(mgr) >=2 );

查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
利用e.deptno=d.deptno 去掉(笛卡尔积)没用的信息

select e.empno, e.sal, d.dname, d.loc from emp e,dept d 

where e.deptno=d.deptno and e.empno=7788;

 自连接查询

要查找的信息在一张表中 单一一次查不出来

需要把这张表 当做两个表 来连接查询

ensample:   求7369员工编号、姓名、经理编号和经理姓名

select e1.empno, e1.ename, e2.empno, e2.ename from emp e1, emp e2

where e1.empno = e2.mgr and e2.emepno = 7389;

求各个部门薪水最高的员工所有信息

把查询出来的结果 当做一张表 来查询 

---先查询出各部门最高工资 

select  deptno, max(sal) from emp group by deptno; 

将查出来最高工资结果当做一个临时表

select * from emp e1,
(
select  deptno, max(sal) from emp group by deptno; 

) e2 where e1.sal=e2.msal and e1.deptno=e2.deptno;