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
WHEREbeauty.`boyfriend_id`=boys.`id`; 

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

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

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

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

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

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

SELECTlast_name,department_name
FROMemployees e,departments d
WHERE e.`commission_pct` IS NOT NULL AND e.`department_id`=d.`department_id`;

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

SELECTdepartment_name,city
FROMdepartments AS d,locations AS l
WHEREl.`city` LIKE ("_o%") AND d.`location_id`=l.`location_id`;

#4.加分组

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

SELECT COUNT(department_id) 部门个数,city 
FROMdepartments d,locations l
WHEREd.`location_id`=l.`location_id`
GROUP BYcity;

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

SELECTdepartment_name,d.manager_id,MIN(salary)   # departments中的manager_id中无重复
FROMdepartments d,employees e
WHEREd.`department_id`=e.`department_id` AND e.`commission_pct` IS NOT NULL
GROUP BYd.`department_name`,d.`manager_id`;

#2. 非等值连接

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

SELECT salary,grade_level
FROMemployees e,job_grades j
WHEREe.`salary`<=highest_sal AND e.`salary`>=lowest_sal;

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

SELECTe.last_name 员工名,m.last_name 领导名
FROMemployees e,employees m
WHEREm.`employee_id`=e.`manager_id`;employee_id=manager_id;

练习：

#1.显示所有员工的姓名，部门号和部门名称

SELECTlast_name,d.`department_id`,department_name
FROMemployees e,departments d
WHEREe.`department_id`=d.`department_id`;

2. 查询90号部门员工的job_id和90号部门的location_id

SELECTd.`department_id` 部门号,job_id,location_id
FROMemployees e, departments d
WHEREd.department_id=90 AND e.`department_id`=d.`department_id`;

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

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

SELECTlast_name,department_name,l.location_id,city
FROMemployees e,departments d,locations l
WHEREe.`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**

SELECTcity,last_name, job_id, d.department_id, department_name
FROMemployees e, departments d, locations l
WHEREe.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` AND city="Toronto";

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

SELECTdepartment_name, job_title,MIN(salary)
FROMemployees e, departments d, jobs j
WHEREe.`department_id`=d.`department_id` AND e.`job_id`=j.`job_id`
GROUP BYj.`job_id`,d.`department_id`;  # 按字段分组，则该字段中不含有重复的

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

SELECTCOUNT(department_id) 部门个数,country_id
FROMdepartments d,locations l
WHEREd.`location_id`=l.`location_id`
GROUP BYcountry_id
HAVING部门个数>2;

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

SELECTe.last_name employees, e.employee_id "Emp#", m.last_name manager, m.employee_id "mgr#"
FROMemployees e, employees m
WHEREe.`manager_id`=m.`employee_id` AND e.employee_id=101;