MySQL学习笔记(八)_sql92之内连接查询

连接查询

1. 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

2. 笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

   发生原因：没有有效的连接条件

3. 分类：

   1. 按年代分类 ：sq192标准：仅仅支持内连接

      ​ sq199标准：支持所有内连接+外连接（左，右连接）+交叉连接

   2. 按功能分类：

      1. 内连接：
         1. 等值连接
         2. 非等值连接
         3. 自连接
      2. 外连接：
         1. 左外连接
         2. 右外连接
         3. 全外连接
      3. 交叉连接

4. sq192标准

   1. 等值连接
      特点：

      1. 多表等值连接的结果为多表的交集部分
      2. n表连接，至少需要n-1个连接条件
      3. 多表的顺序没有要求
      4. 一般需要为表起别名
      5. 可以搭配前面介绍的所有字句使用，比如排序，分组，筛选

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

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

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

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

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

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

      Note:1.若使用别名则前后都需使用，否则会报错

      ​ 2.from 后面的内容可以交换顺序

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

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

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

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

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

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

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

      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.manager_id;
      

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

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

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

      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 ASC;
      

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

      SELECT salary, grade_level
      FROM employees e,job_grades g
      WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;
      (and g.grade_level = 'A') 
      

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

       ```
       SELECT e.employee_id 员工编号, e.salary, m.employee_id 领导编号, m.salary
       FROM employees e, employees m
       WHERE e.manager_id = m.employee_id;
       ```