MySql（十）Sql92之内连接查询

叁歲伎倆 2022-10-02 04:43 258阅读 0赞

# 文章目录 #

连接查询的分类  
Sql92  
    等值连接查询  
    非等值连接查询  
    自连接查询

## 连接查询的分类 ##

1.按照年代进行分类

> 1.  Sql92标准：仅仅支持内连接
> 2.  Sql99标准（推荐）：支持内连接和外连接（左外和右外）以及交叉连接

2.按照功能进行分类

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

##    Sql92 ##

Sql92标准：仅仅支持内连接

### 等值连接查询 ###

/**案例1：查询女神名和对应的男神名**/
    SELECT name,boyName
    FROM beauty,boys
    WHERE beauty.boyfriend_id = boys.id;
    /**案例2：查询员工名对应的部门名**/
    SELECT last_name,department_name
    FROM employees,departments
    WHERE employees.'department_id' = departments.'department_id';
    /**案例3：查询员工名、工种号和工种名**/
    SELECT last_name,employees.job_id,job_title
    FROM employees,jobs
    WHERE employees.'job_id' = jobs.'job_id';

注意：在操作过程中可以选择为表其别名，好处如下：

> 1.  提高语句的简简洁度
> 2.  区分多个重名的字段

/**查询员工名、工种号和工种名**/
    SELECT last_name,e.job_id,job_title
    FROM employees e,jobs j
    WHERE d.'job_id'= j.'job_id'

1. 添加筛选的等值查询

/**查询有奖金的员工名、部门名**/
    SELECT last_name,department_name
    FROM employeess e,departments d
    WHERE e.'department_id' = d.'department_id' 
    AND e.'commission_pct' IS NOT NULL;
    /**查询城市名中第二个字符为o的城市名和部门名**/
    SELECT city, department_name
    FROM department d,location l
    WHERE d.'location_id' = l.'location_id'
    AND city LIKE '_O%';

2. 添加分组的等值查询

/**查询每个城市的部门个数**/
    SELECT COUNT(*) 部门个数,city
    FROM location l,department t
    WHERE l.'location_id' = t.'location_id'
    GROUP BY city
    /**查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资**/
    SELECT department_name,d.manager_id,MIN(salary)
    FROM department d, employees e
    WHERE d.'department_id' = e.'department_id'
    AND commission_pct IS NOT NULL
    GROUP BY department_name;

3.添加排序的等值查询

/**查询每个工种的工种名和员工的个数，并且按照员工的个数降序排序**/
    SELECT job_title,COUNT(*) 员工个数
    FROM job j,employees e
    WHERE j.'job_id' = e.'job_id'
    GROUP BY job_title
    ORDER BY 员工个数 DESC;

4.三表连接的等值查询

/**查询员工名、部门名和所在的城市**/
    SELECT employee_name,department_name,city
    FROM employees e, departement d,location l 
    WHERE e.'department_id' = d.'department_id'
    AND d.'location_id' = l.'location_id';

总结：

> 1.  多表链接查询的结果为多个表的交集部分
> 2.  n表链接，至少需要n-1个连接条件
> 3.  多表的顺序没有要求
> 4.  一般情况下需要为表起别名
> 5.  可以搭配排序、分组、筛选来进行使用

### 非等值连接查询 ###

表：job\_grade

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>grade_level</th> 
   <th>lowest_sal</th> 
   <th>highest_sal</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>A&nbsp;</td> 
   <td>1000</td> 
   <td>2999</td> 
  </tr> 
  <tr> 
   <td>B</td> 
   <td>3000</td> 
   <td>5999</td> 
  </tr> 
  <tr> 
   <td>C</td> 
   <td>6000</td> 
   <td>9999</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>10000</td> 
   <td>12000</td> 
  </tr> 
 </tbody> 
</table>

/**查询员工的工资和工资级别**/
    SELECT salary,grade_level
    FROM employees e, job_grade jg
    WHERE salary BETWEEN lowest_sal AND highest_sal
    /**查询员工的工资和工资级别为A的工资**/
    SELECT salary,grade_level
    FROM employees e, job_grade jg
    WHERE salary BETWEEN lowest_sal AND highest_sal
    AND grade_level = 'A';

### 自连接查询 ###

表employees结构如下：

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>employee_id</th> 
   <th>last_name&nbsp;</th> 
   <th>manager_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>100</td> 
   <td>K_ing</td> 
   <td>(NULL)</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>Kochhar</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>102</td> 
   <td>De Haan&nbsp;</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>103</td> 
   <td>Hunold</td> 
   <td>102</td> 
  </tr> 
  <tr> 
   <td>104</td> 
   <td>Ernst</td> 
   <td>103</td> 
  </tr> 
 </tbody> 
</table>

/**查询员工名和上级名称**/
    SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
    FROM employees e, employees m
    WHERE e.'employee_id' = 'm.manager_id';

本文原文地址：[https://blog.csdn.net/qq\_43952245/article/details/90485006\#\_1][https_blog.csdn.net_qq_43952245_article_details_90485006_1]

[https_blog.csdn.net_qq_43952245_article_details_90485006_1]: https://blog.csdn.net/qq_43952245/article/details/90485006#_1