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 varchar(1),
salary DOUBLE,
join_date DATE,
dept_id INT
);

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 emp , dept;

-- 交叉查询：
-- 左表的每条数据和右表的每条数据组合。左表有3条，右表有5条，最终组合后3*5=15条数据。
-- 左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔积 
SELECT dept.NAME , emp.id,emp.name , emp.gender , emp.salary, emp.join_date , emp.dept_id FROM dept , emp;

-- 内连接查询，这样我们就能获得我们需要的数据了
-- 语法：select ... from 表1,表2 where 连接条件 [and 其他条件]  -->(连接条件:外键的值等于主表主键的值)
SELECT dept.`NAME` , emp.id , emp.`name` , emp.gender , emp.salary , emp.join_date , emp.dept_id FROM dept,emp WHERE dept.id = emp.dept_id;

-- 显示内连接查询：同样壳以获得想要的数据
-- 语法：select ... from 表1 [inner] join 表2 on 连接条件 [ where 其它条件]
SELECT dept.`NAME` , emp.id , emp.`name` , emp.gender , emp.salary , emp.join_date , emp.dept_id FROM dept INNER JOIN emp ON dept.id = emp.dept_id;

-- 左外连接
-- 语法： select ... from 左表名 left [outer] join 右表名 on 连接条件
SELECT dept.`NAME` , emp.id , emp.`name` , emp.gender , emp.salary , emp.join_date , emp.dept_id FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;


-- 右外连接：
-- 语法：select ... from 左表名 right [outer] join 右表名 on 连接条件
SELECT dept.`NAME` , emp.id , emp.`name` , emp.gender , emp.salary , emp.join_date , emp.dept_id FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;

内连接和外连接的区别：

• 内连接: 查询的是公共部分,满足连接条件的部分
• 左外连接: 左边的数据全部显示出来. 再通过连接条件匹配出右边表的数据, 如果满足连接条件, 展示右边表的数据; 如果不满足, 右边的数据通过null代替
• 右外连接: 右表的数据全部显示出来. 再通过连接条件匹配出左边表的数据, 如果满足连接条件, 展示左边表的数据; 如果不满足, 左边的数据通过null代替

---

子查询

什么是子查询：
一个查询语句的结果作为另一个查询语句的条件

• 有查询的嵌套，内部的查询称为子查询
• 子查询要使用括号
• 子查询结果只要是 单个值 ，肯定在 WHERE 后面作为 条件 SELECT … FROM 表- WHERE 字段 [=,>,<,<>,…]（子查询）;

代码示例：

--  -------------------------------子查询------------------------------------------

-- 获取所有工资的最大值
SELECT MAX(salary) FROM emp;

-- 根据最大值找出对应的员工信息
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

-- 查询平均工资
SELECT AVG(salary) FROM emp;
-- 在员工表查询工资少于平均值的员工信息
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

-- 查询财务部和	开发部的id
SELECT id FROM dept WHERE `NAME` IN('财务部','开发部')

-- 1. 再查询在这些部门id中有哪些员工
SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE `NAME` IN('财务部','开发部'))

-- 1. 在员工表中查询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 e.dept_id = d.id;