进阶6 连接查询（sql92内连接）

进阶6：连接查询

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

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
（1）按年代分类：sql标准（仅仅支持内连接）
sql99标准【推荐】（支持内连接）（支持外连接：左外和右外）（支持交叉连接）
（2）按功能分类：内连接（等值连接，非等值连接，自连接）
外连接（左外连接，右外连接，全外连接）
交叉连接
*/

----------------------------补充：笛卡尔乘积现象----------------------------------

SELECT * FROM beauty;
SELECT * FROM boys;

错误输出1—没有添加有效条件

SELECT NAME,boyName FROM beauty,boys;

正确输出2—添加有效条件

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

----------------------------一.sql92标准（仅支持内连接）------------------------------------------------

#-------------------------------【注意：必须加上连接条件】【若不加连接条件，则是笛卡尔乘积】----------------------------------
#-------------------------------（1） 等值连接---------------------------------------------------

内连接：等值连接 非等值连接 自连接

1.【等值连接】

等值连接：连接条件为“=”

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

案例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;

2.【为表起别名】

案例1：查询员工名，工种号，工种名

输出结果1

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

输出结果2------可以起别名

/*
起别名：
提高语句的简洁度
区分多个重名的字段

注意：
如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
SELECT last_name, e.job_id, job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

3.【两个表的顺序可以切换】

输出结果3------两个表的顺序可以切换

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

4.【可以加筛选】

案例1：查询有奖金的员工名，部门名

输出结果-------【添加筛选条件】【and 相当于 &&】

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

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

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

5.【可以加分组】

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

输出结果------可以加分组

SELECT COUNT(DISTINCT department_name) 个数, city
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY city;

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

输出结果1--------【无法确定department_name与manager_id一一对应，所以用group by，变为了每个部门每个领导】

解析 ----------- 在employees表中，一个department_id对应了6个manager_id。【不是一一对应】

在departments表中，一个department_id对应了一个manager_id。【一一对应】

自己速记：因为查询了两个字段，所以分组也写这两个字段。（除非这两个字段一一对应，最好都加上比较靠谱）

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;

输出结果2

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

6.【可以加排序】

having：过滤分组（新的数据源） order by：分组排序（原来的数据）

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

#输出结果--------可以加排序
SELECT job_title, COUNT()
FROM jobs j, employees e
WHERE j.job_id = e.job_id
GROUP BY job_title
ORDER BY COUNT() DESC;

7.【可以实现三表连接】

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

输出结果1-------可以实现三表连接

解析：一个人对应一个部门，一个部门对应一个城市

SELECT last_name, department_name, city # step3
FROM employees e, departments d, locations l # step1
WHERE e.department_id = d.department_id # step2
AND d.location_id = l.location_id; # step2

输出结果2-----【添加筛选条件】

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;

#-------------------------------（2） 非等值连接---------------------------------------------------

1.【非等值连接】

/*
（1）非等值连接：连接条件是“>” “<” “<>”
（2）非等值连接：可以排序，筛选，分组。和等值连接一样。
*/

案例1：查询员工的工资和工资级别

step1:导入job_grades表

创建等级表的sql脚本。（F8执行—>默认创建在当前库“myemployees”—>刷新，即可看见job_grades表）

CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);

INSERT INTO job_grades
VALUES (‘A’, 1000, 2999);

INSERT INTO job_grades
VALUES (‘B’, 3000, 5999);

INSERT INTO job_grades
VALUES(‘C’, 6000, 9999);

INSERT INTO job_grades
VALUES(‘D’, 10000, 14999);

INSERT INTO job_grades
VALUES(‘E’, 15000, 24999);

INSERT INTO job_grades
VALUES(‘F’, 25000, 40000);

step2:job_grades表导入成功，查看该表

SELECT * FROM job_grades;
SELECT salary, employee_id FROM employees;

step3:查询员工的工资和工资级别（非等值查询）（连接条件：“>” “<” “<>”）

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

2.【非等值连接：可以排序，筛选，分组】

案例2：查询员工的工资和工资级别。要求：工资级别=“A”，

SELECT salary, grade_level
FROM employees, job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal
AND grade_level = ‘A’;

#-------------------------------（3） 自连接---------------------------------------------------

1.【自连接】

/*
（1）自连接相当于等值连接。
（2）起别名，就相当于重新创建了两个相同的表
（3）但是，等值连接涉及多个表（连接条件为“=”），
而自连接涉及到的表只有自己（自己连接自己，除非自己表中有特殊的字段才可以连接，比如下例中的employees表）
*/

案例1：查询 员工名和上级的名称

例如：（1）Neena的领导编号是100，而对应的员工编号为100的员工姓名是K_ing。--------故Neena的领导姓名是K_ing

（2）想象为2个表，员工表“e” 和 领导表“m”。

解析：（1）筛选出employee_id = manager_id的员工姓名

（2）相当于两个相同的表连接起来。之前是不用的表连接。

（3）起别名，就相当于重新创建了两个相同的表

SELECT e.last_name, e.manager_id, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;