mysql-sql92标准-连接查询

ゝ一世哀愁。 2023-03-03 11:26 17阅读 0赞

### 含义 ###

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

select 字段1，字段2
    from 表1，表2,...;

笛卡尔积乘积现象：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接。  
表1有m行，表2有n行，结果m\*n行  
发生的原因：没有有效的连接条件  
如何避免：添加有效的连接条件

### 分类 ###

**按年代分类：**  
sql92标准：仅支持内连接：包括等值、非等值、自连接  
sql99标准：支持内连（等值、非等值、自连接），外连（左外、右外）全外（mysql不支持），交叉连接  
**按功能分类：**

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

### 等值连接语法 ###

select 查询列表
    	from 表1 别名,表2 别名
    	where 表1.key=表2.key
    	【and 追加筛选条件】
    	【group by 分组字段】
    	【having 分组后的筛选】
    	【order by 排序字段】

eg:查询员工名和对应的部门名

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

eg2:查询员工名、工种号、工种名

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

**注：给表起别名**  
好处：

*  提高语句的简洁性
 *  区分多个重名的字段\*\*

**添加筛选** 追加and  
eg3：查询有奖金的员工名、部门名

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

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

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

**加分组函数**  
eg5：查询每个城市的部门个数

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

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

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

**加排序**  
eg7:查询每个工种的工种名和员工个数，并且按照员工个数降序

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

**实现三表连接**  
eg8：查询员工名、部门名、和所在的城市

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;

### 多表查询的特点 ###

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

### 非等值连接语法 ###

select 查询列表
    	from 表1 别名,表2 别名
    	where 非等值的连接条件
    	【and 筛选条件】
    	【group by 分组字段】
    	【having 分组后的筛选】
    	【order by 排序字段】

eg：查询员工的工资和工资级别

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

### 自连接语法 ###

select 查询列表
    	from 表 别名1,表 别名2
    	where 等值的连接条件
    	【and 筛选条件】
    	【group by 分组字段】
    	【having 分组后的筛选】
    	【order by 排序字段】

eg:查询员工名、和上级领导的名称

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

等值连接、非等值连接、自连接多表连接查询-练习题  
\#1、查询所有员工的姓名、部门号、和部门名称

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

\#2、查询90号部门员工的job\_id和90号部门的location\_id

SELECT job_id,location_id, d.department_id
    FROM employees e,departments d
    WHERE e.`department_id`=d.`department_id`
    AND d.`department_id`=90；

\#3、选择所有有奖金的员工的

last_name,department_name,location_id,city
    SELECT last_name,department_name,l.location_id,city
    FROM employees e,departments d,locations l
    WHERE e.`department_id`=d.`department_id`
    AND d.`location_id`=l.location_id
    AND e.`commission_pct` IS NOT NULL;

\#4、选择city在Toronto工作的员工的

last_name,job_id,department_id,department_name
    SELECT last_name,job_id,d.department_id,department_name
    FROM employees e,departments d,locations l
    WHERE e.`department_id`=d.`department_id`
    AND d.`location_id`=l.location_id
    AND city="Toronto";

\#5、查询每个工种、每个部门的部门名称、工种名和最低工资

SELECT department_name,job_title,MIN(salary)
    FROM employees e,departments d,jobs j
    WHERE e.`department_id`=d.`department_id`
    AND e.`job_id`=j.`job_id`
    GROUP BY department_name,job_title;

\#6、查询每个国家下的部门个数大于2的国家编号

SELECT COUNT(*),country_id
    FROM departments d,locations l
    WHERE d.`location_id`=l.`location_id`
    GROUP BY country_id
    HAVING COUNT(*)>2;

\#7、选择指定员工的姓名、员工号，以及他的管理者的姓名和员工号

SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
    FROM employees e,employees m
    WHERE e.`manager_id`=m.employee_id
    AND e.`last_name`='kochhar';