mysql 外连接 insert_MySQL总结(十)表连接查询(内、外连接)-详解

表连接查询

1.什么是多表查询

准备数据

# 创建部门表

create table dept(

id int primary key auto_increment,

name varchar(20)

)

insert into dept (name) values ("开发部"),("市场部"),("财务部");

# 创建员工表

create table emp (

id int primary key auto_increment,

name varchar(10),

gender char(1), -- 性别

salary double, -- 工资

join_date date, -- 入职日期

dept_id int,

foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)

)

insert into emp(name,gender,salary,join_date,dept_id) values("孙悟空","男",7200,"2013-02-24",1);

insert into emp(name,gender,salary,join_date,dept_id) values("猪八戒","男",3600,"2010-12-02",2);

insert into emp(name,gender,salary,join_date,dept_id) values("唐僧","男",9000,"2008-08-08",2);

insert into emp(name,gender,salary,join_date,dept_id) values("白骨精","女",5000,"2015-10-07",3);

insert into emp(name,gender,salary,join_date,dept_id) values("蜘蛛精","女",4500,"2011-03-14",1);

多表查询的作用：

比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。

如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

多表查询的分类：

2. 笛卡尔积现象

2.1 什么是笛卡尔积现象

-- 需求：查询所有的员工和所有的部门

select * from emp,dept;

2.2 如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有 员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

-- 设置过滤条件 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 emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

3. 内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

3.1 隐私内连接

看不到 JOIN 关键字，条件使用 WHERE 指定

-- SELECT 字段名 FROM 左表, 右表 WHERE 条件

select * from emp,dept where emp.`dept_id` = dept.`id`;

3.2 显示内连接

使用 INNER JOIN ... ON 语句, 可以省略 INNER

-- SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能查询出需要的数据，使用内连接。

1)确实查询哪些表

select * from emp inner join dept;

2)确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

3)确定查询条件，我们查询的是唐僧的信息，员工表.name="唐僧"

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`="唐僧";

4)确定查询字段，查询唐僧的信息，显示员工 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`="唐僧";

5)我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名

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`="唐僧";

3.3 总结内连接查询步骤：

1)确定查询哪些表

2)确定表连接的条件

3)确定查询的条件

4)确定查询的字段

4. 左外连接

使用 LEFT OUTER JOIN ... ON，OUTER 可以省略

-- 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`;

5. 右外连接

使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略

-- 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`;

OK,这篇就到这里