MYSQL---多表查询

/*
1 子查询
2 内连接
4 右连接
5 全连接连接
3 左连接
6 交叉连接
7 自连接
*/

– 子查询效率没有连接查询高 ，能用连接查询的，就尽量不要用子查询
SELECT c_name FROM class WHERE c_id =(SELECT c_id FROM student WHERE s_name =‘张三2’);

– 连接查询
SELECT a.a_id,a.a_name,b.b_id,b.b_name FROM a,b WHERE a.a_id=b.b_id;

– 内连接
SELECT a.a_id,a.a_name,b.b_id,b.b_name FROM a JOIN b ON a.a_id=b.b_id WHERE b_name=‘aa’;

– 左连接
SELECT a.a_id,a.a_name,b.b_id,b.b_name FROM a LEFT JOIN b ON a.a_id=b.b_id

– 右连接
SELECT a.a_id,a.a_name,b.b_id,b.b_name FROM a RIGHT JOIN b ON a.a_id=b.b_id;

– 全连接 mysql 不支持 ，但是可以用其他的方式来实现
– select a.a_id,a.a_name,b.b_id,b.b_name from a full join b on a.a_id=b.b_id;

SELECT a_id FROM a
UNION ALL
SELECT b_id FROM b;

– mysql中全连接
SELECT a.a_id,a.a_name,b.b_id,b.b_name FROM a LEFT JOIN b ON a.a_id=b.b_id
UNION
SELECT a.a_id,a.a_name,b.b_id,b.b_name FROM a RIGHT JOIN b ON a.a_id=b.b_id;

– 交叉连接
select * from emp,dept where emp.d_id= dept.d_id and emp.e_name=“张三”;;

– 自连接
– 电器下面的子分类
select * from category where p_id =
(select a_id from category where c_name=“电器”);

– 两张表以上联查
方式一
SELECT a.a_id,a.a_name,b.b_id,b.b_name,c…c_id FROM a,b,c WHERE a.a_id=b.b_id and b.b_id=c.c_id;

方式二
SELECT A.,B.,C.*
FROM A
inner join B on A.a_id=B.b_id
inner join C on B.b_id=C.c_id
where
group by
having
order by
limit

注意 a.id b_id c_id 一般是主外键的 关系。