第六章 MySql-DQL命令

连接查询(这章开始就是重点了,又称多表查询) 何时用到连接查询： 当查询的字段来自于多个表时，就需要用到连接查询 多表查询视图如下：

连接条件查询

select
      beauty.name as "女神",
      boys.boyName as "男神"
from
      beauty , boys
where
      beauty.boyfriend_id = boys.id;

按功能分类如下：

• 内连接：

  1.等值连接
  2.非等值连接
  3.自连接

• 外连接：

  1. 左外连接
  1. 右外连接
  2. 全外连接

• 交叉连接

等值连接案例如下：

• 案例1. 查询beauty女神名和boys表对应的男神名

select
       beauty.name "女神名",
       boys.boyName "男神名"
from
       beauty,boys
where
       beauty.boyfriend_id = boys.id

• 案例2. 查询员工名和对应的部门名

select
       employees.last_name "员工名",
       departments.department_name "部门"
from
       myemployees.employees,myemployees.departments
where
       employees.department_id = departments.department_id;

• 案例3. 查询员工名，工种号，工种名

select
       employees.last_name "员工名",
       jobs.job_id "工种号",
       jobs.job_title "工种名"
from
       myemployees.employees,myemployees.jobs
where
       myemployees.employees.job_id = jobs.job_id;

• 案例4. 为表取别名···区分多个表中重名的字段

select
       e.last_name "员工名",
       j.job_id "工种号",
       j.job_title "工种名"
from
       myemployees.employees as e,myemployees.jobs as j
where
       e.job_id = j.job_id;

等值连接加筛选条件查询

• 案例1. 查询employees和departments表中有奖金的员工名,部门名,以及奖金

select
       last_name as "员工",
       commission_pct as "奖金",
       department_name as "部门名"
from
       employees e join departments d
where
       e.department_id = d.department_id and commission_pct is not null ;

• 案例2. 查询城市名中第二个字符为o的部门名和城市名

select
    department_name "部门名",
    city "城市名"
from
    departments d join locations l
where
    d.location_id = l.location_id and city like '_O%'

等值连接加分组查询

• 案例1. 查询每个城市的部门个数

select
    count(*) as "部门个数",

    city as "城市"
from
    departments d join locations l
where
    d.location_id = l.location_id
group by city;

• 案例2. 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select
       d.department_name as "部门名",
       d.manager_id as "部门领导编号",
       min(e.salary) as "最低工资"
from 
       departments d join employees e
where 
       d.department_id = e.department_id and e.commission_pct is not null
group by 
         d.department_name,d.manager_id;

等值连接加排序查询

• 案例1. 查询每个工种的工种名和员工的个数，并且按员工个数降序

select
       job_title "工种名",
       count(*) "员工的个数"
from
       jobs j join employees e
where
       j.job_id = e.job_id
group by
         job_title
order by 
    count(*) desc ;

等值连接实现三表连接查询

• 案例1. 查询员工名，部门名和所在的城市,并且加like筛选条件,并且部门名降序

select
        last_name "员工名",
        department_name "部门名",
        city "所在城市"
from
        employees e ,departments d ,locations l
where
        e.department_id = d.department_id
        and d.location_id = l.location_id
        and city like 's%'
order by 
    department_name desc ;

以上就是等值连接的操作了,下一章继续推出，非等值连接，自连接，外连接等！ 坚持学习，才是最大的收货，加油哦.