多表查询是从多个表中获取数据的操作,通常用于建立表与表之间的关联。根据不同的需求,查询可以分为内连接、外连接和子查询。
1. 内连接查询
内连接查询返回两个表中匹配的记录,即交集部分数据。内连接可以是隐式或显式:
1.1 隐式内连接
SELECT tb_emp.name, tb_dept.name
FROM tb_emp, tb_dept
WHERE tb_emp.dept_id = tb_dept.id;
select 字段列表 from 表1 , 表2 where 条件 ... ;
1.2 显式内连接
SELECT tb_emp.name, tb_dept.name
FROM tb_emp INNER JOIN tb_dept
ON tb_emp.dept_id = tb_dept.id;
select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
使用了别名的多表查询:
select emp.name , dept.name
from tb_emp emp inner join tb_dept dept
on emp.dept_id = dept.id;
注意事项:
一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字段。
2. 外连接查询
外连接分为左外连接和右外连接:
2.1 左外连接
返回左表的所有记录,以及匹配的右表记录。
SELECT tb_emp.name, tb_dept.name
FROM tb_emp LEFT JOIN tb_dept
ON tb_emp.dept_id = tb_dept.id;
select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件
... ;
2.2 右外连接
返回右表的所有记录,以及匹配的左表记录。
SELECT tb_dept.name, tb_emp.name
FROM tb_emp RIGHT JOIN tb_dept
ON tb_emp.dept_id = tb_dept.id;
select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件
... ;
注意事项:
左外连接和右外连接是可以相互替换的,只需要调整连接查询时SQL语句中表的先后顺序就可以 了。而我们在日常开发使用时,更偏向于左外连接。
3. 子查询
SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。
子查询可以书写的位置:
1. where之后 2. from之后 3. select之后
子查询是查询语句嵌套在另一个查询语句中,常用于复杂查询。根据返回结果的不同,子查询分为以下几类:
3.1 标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符: = <> > >= < <=
SELECT *
FROM tb_emp
WHERE dept_id = (SELECT id FROM tb_dept WHERE name = '教研部');
3.2 列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:
SELECT *
FROM tb_emp
WHERE dept_id IN (SELECT id FROM tb_dept WHERE name IN ('教研部', '咨询部'));
3.3 表子查询
返回多行多列,通常作为临时表使用。
SELECT e.*, d.*
FROM (SELECT * FROM tb_emp WHERE entrydate > '2010-01-01') e
LEFT JOIN tb_dept d
ON e.dept_id = d.id;