MySQL学习笔记(十二)__连接查询(一)

连接查询

含义：

又称多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有 m 行，表2 有 n 行，结果 = m*n 行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

按年代分类：
           sql192 标准：仅支持内连接
           sql199 标准；支持内连接+外连接(左外、右外)+交叉连接

按功能分类：
           内连接：
                      等值连接
                      非等值连接
                      自连接
           外连接：
                      左外连接
                      右外连接
                      全外连接
           交叉连接

一、sql192 标准

内连接

1、等值连接

a.多表等值连接的结果为多表的交集部分
b.n表连接，至少需要n-1个连接条件
c.多表的顺序没有要求
d.一般需要为表取别名
e.可以搭配前面所有的子句使用，比如排序、分组、筛选

e.g.：查询员工名和对应的部门名

SELECT
    last_name,
    department_name 
FROM
    employees,
    departments 
WHERE
    employees.department_id = departments.department_id;

2、为表起别名

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

e.g.：查询员工名、工种号、工种名

SELECT
    last_name,
    e.job_id,
    j.job_title 
FROM
    employees AS e,
    jobs AS j 
WHERE
    e.job_id = j.job_id;

3、两个表的顺序可以调换

e.g.：查询员工名、工种号、工种名

SELECT
    last_name,
    e.job_id,
    j.job_title 
FROM
    jobs AS j,
    employees AS e 
WHERE
    e.job_id = j.job_id;

4、可以加筛选

e.g.：查询有奖金的员工名、部门名

SELECT
    last_name,
    department_name,
    commission_pct 
FROM
    employees AS e,
    departments AS d 
WHERE
    e.department_id = d.department_id 
    AND commission_pct IS NOT NULL;

e.g.：查询城市名中第二个字符为o的部门名和城市名

SELECT
    city,
    department_name 
FROM
    locations l,
    departments d 
WHERE
    l.location_id = d.location_id 
    AND city LIKE '_o%’;

5、可以加分组

e.g.：查询每个城市的部门个数

SELECT
    COUNT( * ) 个数,
    city 
FROM
    departments d,
    locations l 
WHERE
    d.location_id = l.location_id 
GROUP BY
    city;

e.g.：查询有奖金的每个部门名和部门的领导编号和该部门的最低工资

SELECT
    department_name,
    d.manager_id,
    MIN( salary ) 
FROM
    departments d,
    employees e 
WHERE
    d.department_id = e.department_id 
    AND commission_pct IS NOT NULL 
GROUP BY
    department_name,
    manager_id;

6、可以加排序

e.g.：查询每个工种的工种名和员工个数，并按员工个数降序

SELECT
    job_title,
    COUNT( * ) 
FROM
    jobs j,
    employees e 
WHERE
    e.job_id = j.job_id 
GROUP BY
    job_title 
ORDER BY
    COUNT( * ) DESC;

7、三表连接

e.g.：查询员工名、部门名、所在城市

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;

非等值连接

与等值连接类似

自连接

e.g.：查询员工名与上级名字

SELECT
    e.employee_id 员工编号,
    e.last_name 员工名,
    m.employee_id 上级编号,
    m.last_name 上级名
FROM
    employees e,
    employees m 
WHERE
    e.manager_id = m.employee_id;