mysql表间关系与表连接查询

mysql学习笔记3

表间关系:一对一,一对多,多对多

一对一:直接将对方主键设置为自身的一个外键(至于哪个表设无关紧要) 例如相对使用比较少。HR系统中，员工与简历(实际开发用的很少,因为一对一完全可以建到一张表里)

一对多:将一作为主表 ,多作为附表, 附表内加一个一的外键, 例如最常用的关系 部门和员工,将部门作为主表,员工表内加一个部门关联的外键.

多对多:再多建一个中间关系表来描述左右表关系,* 例如 选课系统中 课程和学生的关系， 一门课程可以有多个学生选择，一个学生选择多门课程

表连接查询:

三类:交叉查询,内连接,外连接

注:mysql内交叉和内连接结果没什么区别

交叉查询:

内连接查询:

隐式内连接: 关键词select from where

-- 设置过滤条件 Column 'id' in where clause is ambiguous
 select * from emp,dept where id=5; 
 select * from emp,dept where emp.`dept_id` = dept.`id`;

显示内连接:关键词select 显示 form 表1 inner join 表2 on 条件

确定查询哪些表
select * from emp inner join dept;

确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.`dept_id` = d.`id`; 

确定查询条件，我们查询的是唐僧的信息，员工表.name='唐僧'
select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧 ';

确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称
select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名
select e.`id` 编号,e.`name` 姓名,e.`gender` 性别,e.`salary` 工资,d.`name` 部门名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

外连接:

左外连接:SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

-- 在部门表中增加一个销售部 
insert into dept (name) values ('销售部'); select * from dept; 
 
-- 使用内连接查询 
select * from dept d inner join emp e on d.`id` = e.`dept_id`; 
 
-- 使用左外连接查询 
select * from dept d left join emp e on d.`id` = e.`dept_id`; 

右外连接:SELECT 字段名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 条件用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示

-- 在员工表中增加一个员工  
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null); 
select * from emp;  
-- 使用内连接查询  
select * from dept inner join emp on dept.`id` = emp.`dept_id`; 
 
-- 使用右外连接查询 
select * from dept right join emp on dept.`id` = emp.`dept_id`;

子查询:本质就是嵌套查询

-- 1) 查询最高工资是多少 
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息 (查询结果为单个时用=号或者in都可)
select * from emp where salary = (select max(salary) from emp);

-- 1) 查询平均工资是多少 
select avg(salary) from emp; 
-- 2) 到员工表查询小于平均的员工信息 
select * from emp where salary < (select avg(salary) from emp); 

-- 先查询大于5000的员工所在的部门id (查询结果为多个时,用in)
select dept_id from emp where salary > 5000; 
-- 再查询在这些部门id中部门的名字   Subquery returns more than 1 row 
select name from dept where id = (select dept_id from emp where salary > 5000); 
select name from dept where id in (select dept_id from emp where salary > 5000)

子查询也可以放在前面select那边
-- 查询出2011年以后入职的员工信息，包括部门名称 
-- 在员工表中查询2011-1-1以后入职的员工 
select * from emp where join_date >='2011-1-1'; 
 
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id 
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;


还可以使用表连接(on 或者  union用来去重)
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1'; 
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';