多表查询（连接查询）

• 在使用数据库查询语句时，单表的查询有时候不能满足项目的业务需求，在项目开发过程中，有很多需求都是要涉及到多表的连接查询
• 最终需要查询的数据来源于不同的表中时，这个时候需要考虑使用连接查询
  • 新建一张员工表

--  emp 
drop table if exists emp;
create table if not exists emp(
empno int primary key auto_increment,
ename varchar(10) not null,
job varchar(20),
salary double(8,2),
mgr int(10),
bonus double(6,2),
hiredate date,
deptno int(10)
);

• • 插入数据

insert into emp values(null,'mary','sales',7000,6,1000,'2014-1-1',10);
insert into emp values(null,'lily','sales',6000,1,800,'2014-5-1',10);
insert into emp values(null,'tom','sales',5000,1,4000,'2014-3-1',10);
insert into emp values(null,'james','account',8000,6,null,'2014-2-1',20);
insert into emp values(null,'scott','teaching',8000,6,3000,'2014-1-20',30);
insert into emp values(null,'tom','BOSS',38000,null,null,'2013-1-20',30);
insert into emp values(null,'kitty','teaching',7000,5,700,'2014-5-20',30);
insert into emp values(null,'kitty','teaching',6000,5,500,'2014-6-20',30);
insert into emp values(null,'green','analyst',15000,6,1000,'2014-2-20',40);
insert into emp values(null,'brown','analyst',12000,9,7000,'2014-4-20',40);
insert into emp values(null,'danis','department',3000,6,800,'2014-3-4',50);
insert into emp values(null,'brown','department',1800,11,600,'2014-4-20',50);
insert into emp values(null,'smith','department',1200,11,500,'2014-5-20',50);

• • 部门表

drop table if exists dept;
create table if not exists dept(
deptno int primary key,
dname varchar(30),
dlocation varchar(255)
);
insert into dept values(10,'teaching','北京');
insert into dept values(20,'analyst','武汉');
insert into dept values(30,'department','上海');
insert into dept values(40,'sales','郑州');
insert into dept values(50,'account','广州');	

笛卡尔积介绍 

• 对于数据库中 针对于两张表的记录数的所有记录进行匹配。
• 将A表中每条记录 与 B表中每条记录进行 匹配 获得笛卡尔积

select * from emp;
select * from dept;
select * from emp,dept; 显示结果就是笛卡尔积 

• 笛卡尔积结果 就是 两个表记录乘积 例如A 表3条 B表4条
• 注意： 笛卡尔积结果是无效的，必须从笛卡尔积中选取有效的数据结果 ！！！找到他们的关联关系
• 因为记录所有的显示出来 而我们需要的往往是有效的数据
• 

 

连接查询

内连接查询

• 关键字：inner join --- on
• 内链接查询的数据和等值关联查询得到的数据是一样的，是2中表中共有的数据
• 语句：select * from a_table a inner join b_table b on a.a_id = b.a_id;

外连接查询

• 左外连接 - 以左表为基准表，左表的所有的数据全部会显示，不管右表是否有匹配项
• 关键字：left [outer] join on
• 语句：SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

右外连接

• 关键字：right join on / right outer join on
• 语句：SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

连接查询比较

 

代码理解

-- 查看三门成绩都及格的人数有多少
SELECT COUNT(id)
FROM exam
WHERE chinese > 70 and math > 70 and english >70


-- sum() 求和，财务报表的时候，求一列的和
-- avg() 求平均值，某一列的平均值
-- max(),min() 求最大值和最小值的，都是列名
-- 查询数学学科的最高分，最低分，平均分
SELECT MAX(math),MIN(math),AVG(math)
FROM exam


-- 求三门学科的总的平均分
SELECT AVG(chinese),AVG(math),AVG(english) FROM exam
SELECT AVG(math) FROM exam
SELECT AVG(english) FROM exam


SELECT c,m,e,(c+m+e)/3
FROM (SELECT AVG(chinese) c,AVG(math) m,AVG(english) e FROM exam)tb_avg


-- 模糊查询 like 
-- col like '规则'
-- 可以使用 _ 一个_代码一个字符  %若干个字符  如
-- name like '张_' 查询所有姓张的，姓名是2个字的
-- 
SELECT *
FROM exam
WHERE name like '%张%'     -- 包含张的都出来
-- WHERE name like '张%'   -- 查询所有的姓张的
-- WHERE name like '张_'   -- 姓名是2个字的姓张的




-- in（集合）
-- 批处理的时候。可以和or进行转换使用的
-- 查询id为1，2，3，4的学生的考试信息
SELECT *
FROM exam
WHERE id=1 


-- in（集合）
-- 批处理的时候。可以和or进行转换使用的
-- 查询id为1，2，3，4的学生的考试信息
SELECT *
FROM exam
WHERE id in(1,2,3,4)
-- WHERE id=1 or id=2 or id=3 or id=4


--  删除id为3，5，6的考试信息
DELETE from exam WHERE id in(3,5,6)


-- is NULL, 判断某一列是不是为null
--  is not null , 某一列不为空
-- 查询有哪些学生是没有参加考试的
SELECT *
FROM exam
WHERE chinese is null or math is null or english is NULL




-- 查询那些人是没有奖金的
-- 查询哪些人是有奖金的，按照奖金由高到底的进行排序
SELECT ename,job
FROM emp
WHERE emp.bonus is not null
order by emp.bonus desc




-- 查询所有员工的个人信息和他对应的部门信息
-- 等值关联查询，
-- 特点 ： 2张表中共有的数据
-- 也叫内链接查询
-- tableA a INNER JOIN tableB b ON a.col=b.col
SELECT * 
from emp,dept
WHERE emp.deptno = dept.deptno


SELECT *
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno




-- 外连接查询
-- 左外链接，右外链接，其实是可以互相转换的
-- 左外链接，a left join b on a.col = b.col
-- 右外链接  a right join b on a.col = b.col


-- 所有的老公的信息和他对应的老婆信息
-- 过滤笛卡尔积
SELECT *
FROM husband h ,wife w
WHERE h.wid = w.wid


SELECT *
FROM husband INNER JOIN wife ON husband.wid = wife.wid


-- 左外链接
-- 将左表所有的数据全部展示出来，右表匹配的数据才会展示，不匹配的不展示
SELECT *
FROM husband LEFT JOIN wife on husband.wid = wife.wid


SELECT *
FROM husband RIGHT JOIN wife on husband.wid = wife.wid