MySQL子查询和多表查询

标量子查询

返回一行,一列,一个数字等.

SELECT name,salary
FROM WORK
WHERE salary>(SELECT AVG(salary)
	FROM WORK
)
ORDER BY salary DESC;

列子查询:

返回一列数据:
操作符:

• in :在指定的集合范围内,多选一. 反: not in
• any/some :与子查询返回的任意一个值比较。
• all :返回的所有值都要满足

SELECT *FROM WORK
WHERE name IN(SELECT name
	FROM WORK
	WHERE absence=3 OR absence=2
);

行子查询

SELECT *FROM WORK
WHERE(absence)=(SELECT absence
FROM WORK
WHERE name='employee 19');

子查询在不同情况下的用法

子查询的结果是单行单列:

• 子查询可以作为条件，使用 比较运算符 进行判断：
  • 运算符：>、>=、<、<=、=

-- 查询员工工资小于平均工资的员工
SELECT * 
FROM emp 
WHERE emp.salary < (SELECT AVG(salary) FROM emp);

子查询的结果是多行单列:

• 子查询作为条件，使用 IN来判断：

-- 查询“财务部”和“市场部”的员工信息
SELECT id 
FROM dept 
WHERE NAME = '财务部' OR NAME = '市场部';

-- 子查询
SELECT * 
FROM emp 
WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

子查询的结果是多行多列:

• 子查询作为一张临时表，与主表进行 表连接：

-- 查询员工入职日期在 2011-11-11 之后的员工信息和部门信息
-- 子查询
SELECT * 
FROM dept t1, (SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2
WHERE t1.id = t2.dept_id;

-- 普通的表连接写法
SELECT * 
FROM emp t1, dept t2 
WHERE t1.dept_id = t2.id AND t1.`join_date` > '2011-11-11';

---

内连接:

1. 显式内连接

使用 INNER JOIN 关键字进行连接.

SELECT 表1.列, 表2.列
FROM 表1
INNER JOIN 表2 ON 表1.连接列 = 表2.连接列;

示例：

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

2. 隐式内连接

使用 WHERE 子句来指定连接条件.

SELECT 表1.列, 表2.列
FROM 表1, 表2
WHERE 表1.连接列 = 表2.连接列;

示例：

SELECT employees.name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.id;

外连接:

1. 左外连接（LEFT JOIN)
   返回左表中的所有记录，如果右表列中没有匹配，返回 NULL 。在FROM 后的表是左表,在JOIN后的是右表.

SELECT 表1.列, 表2.列
FROM 表1
LEFT JOIN 表2 ON 表1.连接列 = 表2.连接列;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

2. 右外连接（RIGHT JOIN）
   和左外连接相反. 在FROM 后的表是左表,在JOIN后的是右表.

SELECT 表1.列, 表2.列
FROM 表1
RIGHT JOIN 表2 ON 表1.连接列 = 表2.连接列;

示例：

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

3. 全外连接（FULL JOIN）
   返回左表和右表中所有记录。如果左表或右表中没有匹配的记录，返回 NULL。

SELECT 表1.列, 表2.列
FROM 表1
FULL JOIN 表2 ON 表1.连接列 = 表2.连接列;

示例：

SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.id;