mysql-連接配接查詢（sql92）

**連接配接查詢**

含義：又稱多表查詢，當查詢的字段來自于多個表時，就會用到連接配接查詢

笛卡爾乘積現象：表1 有m行，表2有n行，結果=m*n行

發生原因：沒有有效的連接配接條件
如何避免：添加有效的連接配接條件

分類：

	按年代分類：
	sql92标準:僅僅支援内連接配接
	sql99标準【推薦】：支援内連接配接+外連接配接（左外和右外）+交叉連接配接
	
	按功能分類：
		内連接配接：
			等值連接配接
			非等值連接配接
			自連接配接
		外連接配接：
			左外連接配接
			右外連接配接
			全外連接配接
		交叉連接配接




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

一、sql92标準
1、等值連接配接
① 多表等值連接配接的結果為多表的交集部分
②n表連接配接，至少需要n-1個連接配接條件
③ 多表的順序沒有要求
④一般需要為表起别名
⑤可以搭配前面介紹的所有子句使用，比如排序、分組、篩選
#案例1：查詢女神名和對應的男神名
SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
#案例2：查詢員工名和對應的部門名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;



2、為表起别名
①提高語句的簡潔度
②區分多個重名的字段
注意：如果為表起了别名，則查詢的字段就不能使用原來的表名去限定
#查詢員工名、工種号、工種名
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

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：查詢有獎金的員工名、部門名
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(*) 個數,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)
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`;

6、可以加排序
#案例：查詢每個工種的工種名和員工的個數，并且按員工個數降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

7、可以實作三表連接配接？
#案例：查詢員工名、部門名和所在的城市
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：查詢員工的工資和工資級别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

/*
select salary,employee_id from employees;
select * from 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);

*/

3、自連接配接
#案例：查詢 員工名和上級的名稱
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`;