连接查询（多表查询）——MySQL

Posted on by hackdl

连接查询（多表查询）

又称多表查询，当查询的字段涉及多个表的时候，就要用到连接查询

分类：

为表起别名：

  • 提高语句的简洁度
  • 区分多个重名字段
  • 注意：如果为表起了别名，则查询的字段就不能使用原来的别名去限定

内连接

查询A、B 交集部分数据

语法：

  1. 隐式内连接
    select 字段列表 from 表1，表2 where 筛选条件 ;

  2. 显式内连接
    select 字段列表 from 表1 【inner】 join 表2 on 连接条件 ... ;

  • 例题：

查询每一个员工的姓名 , 及关联的部门的名称
表结构: emp , dept
连接条件: emp.dept_id = dept.id

  • 隐式内连接实现
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

若果有员工没有部门，则不会显示

  • 显示内连接实现
select e.name, d.name 
from emp as e 
join dept as d 
on e.dept_id = d.id;

外连接

左右外连接

  1. 左外连接

左外连接相当于查询表A(左表)的所有数据和中间绿色的交集部分的数据。
表1的位置为左表，表2的位置为右表

select  字段列表  
from  表1  
left 【outer】 join  表2  
on  条件...
  1. 右外连接
    右外连接相当于查询表B(右表)的所有数据和中间绿色的交集部分的数据。
    表1的位置为左表，表2的位置为右表
select  字段列表  
from  表1  
right 【outer】 join  表2  
on  条件...

  • 想把右外连接改成左外连接，并且查询结果不改变，可以把right改为left，并且把表1和表2的位置调换

  • 例题：

  1. 查询emp表的所有数据, 和对应的部门信息
    由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，因为有一些中间数据查询不到，需要考虑使用外连接查询。
    表结构: emp, dept
    连接条件: emp.dept_id = dept.id
    （左外连接）
select e.*, d.name  
from  emp as e
left outer join dept as d
on e.dept_id = d.id
  1. 查询dept表的所有数据, 和对应的员工信息

表结构: emp, dept
连接条件: emp.dept_id = dept.id
(右外连接)

select d.*, e.* 
from emp e  # 左表
right outer join dept as d  # 右表 
on e.dept_id = d.id;

将右外改为左外

select d.*, e.* 
from dept as d 
left outer join emp as e 
on e.dept_id = d.id;

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

  • 语法：
select  字段列表  
from  表1  as 别名1  
join 表1  as 别名2  
on 条件....

注意：自连接表一定要起别名

对于自连接查询，可以是内连接查询，也可以是外连接查询。

  • 例题： 1. 查询员工 及其 所属领导的名字，

普通员工和领导其实都属于员工，都在员工表当中，每个员工又有一项manager_id记录这他的领导的id值
用内连接

select a.name , b.name 
from emp as a , emp as b 
where a.managerid = b.id;
  • 例题2. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
    表结构: emp a , emp b
    用外连接
select a.name as '员工', b.name as '领导' 
from emp as a 
left join emp as b 
on a.managerid = b.id;
  1. 当要查询girl表中的g_name和boy表中的b_name，girl表中的boyfriend_id与boy表中的id是对应的
    select g_name,b_name from boy,bearty
    where girl.boufriend_id = boy.id
    如果不加where筛选条件的话，会显示笛卡尔乘机的效果，出现许多的无用项

会用第一个表中的每一行和第二个表中的每一行进行逐个匹配，然后进行筛选，如果匹配会筛选出来
可以自由调换表的顺序

  1. 查询员工名和对应的部门名，员工名在employees表中，部门名在department表中
    select last_name,department_name
    from employees,department
    where employees.'department_id',department.'department_id'

SQL92语法

等值连接

为表起别名

当语句中经常出表名作用域的时候，每条语句就会很长，为精简语句

  1. 查询员工名，工种号，工种名
    select e.last_name,e.job_id,j.job_title
    from employees as e , job as j
    where e.'job_id'= j.job_id

加筛选条件

  1. 查询有奖金的员工名、部门名
    select last_name,department_name
    from employees as e , department as d
    where e.'department_id' = d.'department_id'
    and e.'commission_pct'is not null 已经有一个where筛选了不能再用where，用and

  2. 查询城市名中第二个字符为o的部门名和城市名
    select department_name,city
    from department as d,location as l
    where d.'location_id' = l.'location_id'
    and city like '_o%'

加分组

  1. 查询每个城市的部门个数
    select count(*) as 个数 , city
    from department as d = location as l
    group by city

  2. 查询有奖金的每个部门的部门名，和部门的领导编号和该部门的最低工资标准
    select department_name , manager_id,min(salary)
    from employees as e , department as d
    where e.'department_id' = d.'department_id'
    and commission_pct is not null
    group by department_name,d,manager_id ;

加排序

  1. 查询每个工种的工种名和员工的个数，并按员工个数降序
    select job_title , count(*)
    from employees as e , job as j
    where e.'job_id'= j.job_id
    group by job_title
    order by count(*) desc

实现三表连接

  1. 查询员工名，部门名，和所用城市名
    select last_name,department_name,city
    from employees as e , department as d , location as l
    where e.'department_id' = d.'department_id'
    and d.'location_id' = l.'location_id'

非等值连接

也就是把上面的等于号换成了不等于（大于、小于、不等）

  1. 查询员工的工资和工资级别
    select salary,grade_level
    from employees e,job_frades g
    where salary between g.'lowest_sal' and g.'higthest_sal'
    追加其他条件。
    and g.'lowest_sal'='A';只查看等级为A的

自连接

把原来这一张表当做多张表来使用，由表中的数据找到另一个数据，再由找到的数据回过头来找另一个数据，可以这样往复下去

  1. 查询员工名，和他对应的上级名，每一个有员工对应的上级编号
    select e.employee_id,e.last_name,m.employee_id,m.last_name
    from employees.e,employees.me代表员工表，m代表领导表。其实都在一张表里，重命名来避免冲突
    where e.'manager_id' = m.'employees_id'

SQL99语法

语法：
select 查寻列表
from 表1 as 别名 【连接类型】
join 表2 as 别名
on【连接条件】
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

内连接

1.等值连接

  • 特点
    1. 可以添加排序、分组、筛选
    2. inner可以省略
    3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读。
    4. inner join连接和SQL192语法中的等值连接效果是一样的，都是查询多表的交集。

  1. 查询员工名，部门名
    select last_name,department_name
    from department d
    innner join demployees as e
    on e.'department_id' = d.'department_id'

  2. 查询名字中包含e的员工名和工种名(添加了筛选条件)
    select last_name,job_id
    from employees as e
    inner join as j
    on e.'job_id' = j.'job_id'
    where e.'last_name' like '%e%' ;

  3. 查询部门个数>3的城市名和部门个数（添加分组、筛选条件）
    select city,count(*) as 部门个数
    from departments as d
    inner hoin locations as l
    on d.'location_id' = l.'location_id'
    group by city
    having count(*)>3

  4. 查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序
    select count(*) , department_id
    from employees as e
    inner hoin departments as d
    on e.'department_id' = d.'department_id'
    group by department_name
    having count(*)>3
    order by count(*) desc

1.非等值连接

  1. 查询员工的工资级别
    select salary,grade_level
    from employees as e
    join job_grades as g
    on e.'salary' between g.'lowest_sal' and g.'hightest_sal' ;

  2. 查询每个工资级别的个数，并按照级别降序。
    select count(*),grade_level
    from employees as e
    join job_grades as g
    on e.'salary' between g.'lowest_sal' and g.'hightest_sal'
    having count(*)>20
    order by grade_lecel desc

自连接

把原来这一张表当做多张表来使用，由表中的数据找到另一个数据，再由找到的数据回过头来找另一个数据，可以这样往复下去

  1. 查询员工名，和他对应的上级名 (每一个有员工对应的上级编号)
    select m.employee_id,m.last_name
    from employees as e e代表员工表，m代表领导表。其实都在一张表里，重命名来避免冲突
    join employees as m
    on e.'manager_id' = m.'employees_id'

外连接

用于查询主表的时候，主表中没有，但是附表中，主表通过外连接附表来查询数据
特点：

  1. 外连接查询结果为主表中的所有数据。
    如果主表中有与之匹配的显示匹配值。
    表中没有与之匹配的显示null
    外连接查询结果=内连接结果+主表中有而从表中没有的数据

  2. 左外连接，left join左边的是主表
    右外连接,right join右边的是主表。

  3. 左外和右外交换两个表的顺序可以实现同样的效果。

  4. 查询男朋友 不在男神表的的女神名

    左外连接
    SELECT b.*,bo.*
    FROM boys bo
    LEFT OUTER JOIN beauty b
    ON b.'boyfriend_id' = bo.'id'
    WHERE b.'id' IS NULL;

  5. 查询哪个部门没有员工

    1. 左外
      SELECT d.*,e.employee_id
      FROM departments d
      LEFT OUTER JOIN employees e
      ON d.'department_id' = e.'department_id'
      WHERE e.'employee_id' IS NULL;
    2. 右外
      SELECT d.*,e.employee_id
      FROM employees e
      RIGHT OUTER JOIN departments d
      ON d.'department_id' = e.'department_id'
      WHERE e.'employee_id' IS NULL;

全外
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.'boyfriend_id' = bo.id;

交叉连接

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

sql92和 sql99 pk

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: