mysql学习笔记（八）——连接查询（sql92）

连接查询

/*

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

2. 笛卡尔乘积现象：
   （1）含义：表1有m行，表2有n行，结果有m*n行（全部遍历）
   （2）该现象的原因：没有添加有效的连接条件

3. 连接查询的分类
   按年代分：
   sql92: 仅支持内连接
   sql99:（推荐）除了全外连接，其他都支持

按功能分：

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

一. sql92标准

1. 等值连接

案例1：查询女神名和对应的男朋友

SELECT 
	NAME 女神名,boyName 男朋友 
FROM 
	beauty,boys
WHERE
	beauty.`boyfriend_id`=boys.`id`; 

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

SELECT
	last_name 员工名,department_name 
FROM 
	employees,departments
WHERE
	employees.`department_id`=departments.`department_id`;  # 找一样的字段来充当连接条件

#2. 为表起别名
#注意：如果为employees起了别名，则后面就不能用employees限定，只能用别名e

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

SELECT 
	last_name,e.job_id,job_title  # 因为iob_id在两个表中都存在，所以要指明是哪个的，因此，这些字段可以随意交换位置
FROM
	employees e,jobs    # 为表起别名和字段一样
WHERE	
	e.`job_id`=jobs.`job_id`;

#3. 加筛选
#案例1：查询有奖金的员工名、部门名

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

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

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

#4.加分组

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

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

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

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

#2. 非等值连接

案例：查询员工的工资与工资等级

SELECT 
	salary,grade_level
FROM
	employees e,job_grades j
WHERE
	e.`salary`<=highest_sal AND e.`salary`>=lowest_sal;

#3. 自连接
#案例：查询员工名和上级的名字
#分析：相当于连接两个employees表，但第一个是员工表，第二个是领导表

SELECT
	e.last_name 员工名,m.last_name 领导名
FROM
	employees e,employees m
WHERE
	m.`employee_id`=e.`manager_id`;
	employee_id=manager_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
	d.`department_id` 部门号,job_id,location_id
FROM
	employees e, departments d
WHERE
	d.department_id=90 AND e.`department_id`=d.`department_id`;

3. 选择有奖金的员工的last_name,department_name,location_id,city

分析：涉及3个表，三表连接

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. 选择在Toronto工作的员工的last_name,job_id,department_id,department_name**

SELECT
	city,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
	j.`job_id`,d.`department_id`;  # 按字段分组，则该字段中不含有重复的

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

SELECT
	COUNT(department_id) 部门个数,country_id
FROM
	departments d,locations l
WHERE
	d.`location_id`=l.`location_id`
GROUP BY
	country_id
HAVING
	部门个数>2;

/*
#7. 选择指定员工的姓名、员工号、以及他的管理者姓名和员工号,结果类似于下面：
employees Emp# manager mgr#
kochhar 101 king 100
*/

SELECT
	e.last_name employees, e.employee_id "Emp#", m.last_name manager, m.employee_id "mgr#"
FROM
	employees e, employees m
WHERE
	e.`manager_id`=m.`employee_id` AND e.employee_id=101;