MySql数据库基本操作四（连接查询）

连接查询

表数据链接: https://pan.baidu.com/s/1uDxNjKVKlz9uVicf9IYCkg 提取码: 2y1w

  含义：又称为多表查询
  笛卡尔积现象：表1有m行，表2有n行，结果为m*n行
  发生原因：没有有效的链接条件
  如何避免：添加有效的链接条件

分类：

按年代分类：
  sql92标准：仅仅支持内连接
  sql99标准：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
  内连接：
    等值连接
    非等值连接
  自连接
  外连接：
    左外连接
    右外连接
    全外连接
  交叉连接：

一、sql92标准

1、等值连接
  多表连接的结果为多表的交集部分
  n表连接，至少需要n-1个连接条件
  多表的顺序没有要求
  一般需要为表起别名
  可以搭配前面介绍的所有子句使用

  案例：查询女生名和对应男生名

SELECT name,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

  案例：查询员工名和对应 的部门名

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

为表起别名
  提高语句的简洁度
  区分多个重名的字段
  注意：如果起了别名，不能使用原始表名

  案例：查询员工名、工种号、工种名

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

  案例：查询有奖金的部门员工名、部门名

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

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

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

  案例：查询每个城市的部门的个数

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

  案例：查询出有奖金的每个部门的部门名和各部门的领导编号和该部门的最低工资

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,d.department_id;

  案例：查询每个工种的工种名和员工的个数，并且按元工个数降序

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

  案例：查询员工名、部门名和所在的城市

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;

2、非等值连接
  案例：查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

自连接
  案例：查询员工名和上级的名称

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;

二、sql99语法

  语法：
    SELECT 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名
    on 连接条件
    where 筛选条件
    GROUP BY 分组
    HAVING 筛选条件
    ORDER BY 排序列表
  内连接：inner
  外连接
    左外:left [outer]
    右外:right [outer]
    全外:full[outer]
  交叉连接：cross

内连接

  语法：
    select 查询列表
    from 表名
    inner join 表2
    on 连接条件

1、等值连接
  案例：查询员工名、部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;

  案例：查询名字中包含e的员工名和工种名（条件筛选）

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%';

  案例：查询部门个数>3的城市名和部门个数（添加分组+筛选）

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT(*)>3;

  案例：查询那个部门的员工个数>3的部门名和员工个数，并按个数降序
  查询每个部门的员工个数

SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name;

  在结果上筛选>3的记录

SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

  案例：查询员工名、部门名、工种名，并按部门名降序（三表查询）

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id
ORDER BY department_name DESC;

2、非等值连接
  查询员工的工资级别

SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

  案例：查询工资级别的个数>20的个数，并且按工资级别降序

SELECT COUNT(*),grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

自连接
  案例：查询员工名字、上级的名字

  案例：查询员工名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id;

  案例：查询姓名中包含字符K的员工名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.last_name LIKE '%k%';