小粥重学mysql(5)之多表查询

小粥重学mysql(5)之多表查询

环境搭建

创建部门表

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

语法

select
	列名列表
from
	表名列表
where
	条件列表

select * from emp,dept;

# 查询出来的结果集为  笛卡尔积
--    A集合，B集合：所有集合的组合情况

-- 消除笛卡尔积   去除无用数据

消除笛卡尔积之内连接查询

隐式内连接

使用where条件，

消除无用的数据

# 查询所有员工信息和对应的部门信息

select 
	e.NAME 员工姓名,
	e.gender 性别,
	d.NAME 部门名称
from 
	emp e, -- 设置别名
	dept d 
where 
	e.dept_id = d.id 

显示内连接

# 查询所有员工信息和对应的部门信息

select
	e.NAME 员工姓名,
	e.gender 性别,
	d.NAME 部门名称
from 
	emp e 
inner join  -- inner可以省略
	dept d on e.dept_id = d.id 

注意事项

1.从哪些表查询数据

2.查询条件

3.查询哪些字段

消除笛卡尔积之外连接查询

语法

左外连接

查询的是左表所有数据，及其交集部分

# 查询所有员工信息，如果员工有部门，则查询部门名称；没有部门，则不显示部门名称

SELECT
	e.*,
	d.`NAME` 
FROM
	emp e
LEFT JOIN -- left outer join
	dept d
ON e.`dept_id`=d.`id`

右外连接

查询的是右表所有数据，及其交集部分

SELECT
	e.*,
	d.`NAME` 
FROM
	emp e
RIGHT JOIN -- left outer join
	dept d
ON e.`dept_id`=d.`id`

消除笛卡尔积之子查询

查询中嵌套查询，称嵌套查询为子查询

子查询结果为单行单列

# 查询工资最高员工的信息

SELECT
	e.*
FROM 
	emp e
WHERE
	e.`salary` = (SELECT MAX(salary) FROM emp)

# 查询员工工资小于平均水平的人

SELECT
	e.*
FROM
	emp e
WHERE
	e.`salary` < (SELECT AVG(e.`salary`) FROM emp e)

子查询作为条件。

使用运算符判断：> >= < <= =

子查询结果为多行单列

# 查询财务部所有的员工信息

SELECT
	e.*
FROM
	emp e
WHERE
	e.`dept_id` = (SELECT d.`id` FROM dept d WHERE d.`NAME` = '财务部')

# 查询财务部和市场部所有的员工信息

SELECT
	e.*
FROM
	emp e
WHERE
	e.`dept_id`  IN -- 多行单列为集合。in 在集合里取值
	(SELECT d.`id` FROM dept d WHERE d.`NAME` = '财务部' OR d.`NAME` = '市场部')

子查询结果为多行多列

# 查询员工入职日期为 2011年11月11号的人

--   -----------子查询的方法
SELECT 
p.*,
d.`NAME`
FROM
dept d,
(SELECT * FROM emp e WHERE e.`join_date` > '2011-11-11') p

WHERE d.id = p.dept_id 



--   ------------左外连接
SELECT 
	e.*,d.`NAME` 
FROM 
	emp e 
LEFT JOIN 
	dept d 
ON 
	d.`id` = e.`dept_id`  
WHERE 
	e.`join_date`>'2011-11-11'

子查询可以作为一张虚拟表