MySQL—连表查询

目录

1.连接查询—内连接

1.1隐式内连接

1.1.1等值连接和非等值连接

1.2.显示内连接—inner join

2.连接查询—外连接

2.1 左外连接 

 2.2 右外连接 

3.自连查询

 4.子查询

 5.组合查询

6.注意事项 

正确代码

错误示范 

 

---

1.连接查询—内连接

1.1隐式内连接

笛卡尔乘积，错误示范

select student.id ,teacher.t_id from student,teacher ;

 正确示范

 后面要加上条件证明两表有相同的字段

select s.id, t.t_id from student s,teacher t where s.teacher_id= t.t_id;

1.1.1等值连接和非等值连接

等值连接指的多表查询语句中的连接条件使用的是等号。

非等值连接指的是多表查询语句中的连接条件使用的不是等号而是>,>=,<,<=,!=以及使用between进行范围判断的称之为非等值连接。

 

1.2.显示内连接—inner join

-- 显示连接: A表 inner join B表 on 连表条件。 
2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)

select * from tb_emp  inner join tb_dept on tb_emp.dept_id=tb_dept.id;
select * from tb_emp e inner join tb_dept d on e.dept_id=d.id;

-- 上面的 inner可以省略。

select * from tb_emp e  join tb_dept d on e.dept_id=d.id;

2.连接查询—外连接

2.1 左外连接 

-- 语法: select 查询列集 from A表 left join B表 on 连表条件
-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)

select * from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
select * from tb_emp e left join tb_dept d on e.dept_id=d.id;

 -- 2．查询dept表的所有数据，和对应的员工信息(右外连接)

 2.2 右外连接 

select * from tb_emp e right join tb_dept d on e.dept_id=d.id;

3.自连查询

自己和自己相连接查询。
select * from A表 join A表 on 连表条件。

 -- 1．查询员工及其所属领导的名字。你要查询的结果再一张表中，但是还不能使用单表查询得到结果。

select a.name,b.name from tb_emp a join tb_emp b on a.managerid=b.id;

-- 2．查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来

select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;

 4.子查询

一个查询的结果 作为另一个查询的条件 或者 临时表。
-- 查询市场部的员工信息-----
-- 子查询返回的结果一列一条记录。 这个时候可以用=
 

select * from tb_emp where dept_id=(select id from tb_dept where name='市场部')

-- 查询市场部和研发部员工的信息。in 
 -- -- 查询市场部和研发部员工的信息。
   -- a) 查询市场部和研发部的编号
     select id from tb_dept where name in('市场部','研发部')

   -- b) 再员工表中根据部门编号查询员工信息
     select * from tb_emp where dept_id in (select id from tb_dept where name in('市场部','研发部'))

 5.组合查询

多个查询的结果 组合到一起。
sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录，不合并。 

注意: 这两条sql返回的字段必须一样。

    
    select name from tb_emp where salary>8000
    UNION 
    select * from tb_emp where age>40;

6.注意事项 

如果查询语句中出现了多个表中都存在的字段，则必须指明是哪个表中的字段

 建议：从sql 优化的角度，建议多表查询时，每个字段前都指明其所在的表。

正确代码

select s.id, t.t_id from student s,teacher t where s.teacher_id= t.t_id;

可以给表起别名，在 SELECT 和 WHERE 中使用表的别名。 

如果给表起了别名，一旦在 SELECT 或 WHERE 中使用表名的话，则必须使用表的别名，而不能再使用表的原名。

错误示范 

select s.id, t.t_id from student s,teacher t where student.teacher_id= teacher.t_id;