mysql笔记七 多表查询、子查询

多表查询、子查询

多表查询

数据准备：

# 创建部门表
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列或多列。

多表查询的分类

笛卡尔积

# 需求：查询所有的员工和部门
select *from emp,dept;

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔积。

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

# 设置过滤条件
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

内连接

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

隐式内连接

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

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

举例：

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

显式内连接

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

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

举例：

# 查询唐僧的信息，显示员工 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`='唐僧';

总结内连接查询步骤

1. 确定查询哪些表
2. 确定表连接的条件
3. 确定查询的条件
4. 确定查询的字段

外连接

左外连接

左外连接：使用 LEFT OUTER JOIN ... ON，OUTER 可以省略。

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL。
可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)。

举例：

# 在部门表中增加一个销售部
insert into dept (name) values ('销售部');

# 使用显式内连接查询
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`;

右外连接

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

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL。
可以理解为：在内连接的基础上右表的数据全部显示。

举例：

# 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);

# 使用内连接查询
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.一个查询的结果做为另一个查询的条件。
2.有查询的嵌套，内部的查询称为子查询。
3.子查询要使用括号。

子查询的三种情况

1.子查询的结果是单行单列

2.子查询的结果是多行单列

3.子查询的结果是多行多列

子查询的结果是一个值的时候

子查询结果只要是单行单列，肯定在 WHERE 后面作为条件。

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

子查询结果是多行单列的时候

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符。

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

举例：

# 查询工资大于5000的员工，来自于哪些部门的名字
select name from dept where id in (select dept_id from emp where salary > 5000);

# 查询开发部与财政部所有的员工信息
select *from emp where dept_id in (select id from dept where name in('开发部','财政部'));

子查询的结果是多行多列

子查询结果只要是多列，肯定在 FROM 后面作为表。子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段。

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

举例：

# 查询出 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 ;

MySQL知识点列表

MySQL知识点列表