多表查询
2020-03-06 本文已影响0人
风中小酌
多表查询时，容易产生笛卡尔积，应避免在无任何条件时做多表联合查询。
内连接
- 等值连接
查询条件中使用=作为连接条件
SQL> select employees.last_name, departments.department_name from employees, departments where employees.department_id = departments.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Tobias Purchasing
需要注意的是，为了连接N个表，至少需要N-1个连接条件
- 非等值连接
连接条件中不使用=作为连接条件, Between...and..., >,<,!=,>=,<=
SQL> SELECT EM.SALARY, GR.GRA FROM EMPLOYEES EM, JOB_GRADES GR WHERE EM.SALARY BETWEEN GR.LOWEST_SAL AND GR.HIGHEST_SAL;
SALARY GRA
---------- ----------
2100.00 A
2200.00 A
2200.00 A
- 自连接
当同一表中数据存在层级关系时，可以使用自连接查询
SQL> select em.last_name, em.employee_id, e2.last_name, e2.employee_id from employees em, employees e2 where em.manager_id = e2.employee_id;
LAST_NAME EMPLOYEE_ID LAST_NAME EMPLOYEE_ID
------------------------- ----------- ------------------------- -----------
Kumar 173 Cambrault 148
Bates 172 Cambrault 148
Smith 171 Cambrault 148
外连接
查询出符合连接条件数据的同时，还包含孤儿数据（被连接的列值为空的数据）。
外连接时，查询条件使用 ON，不使用 WHERE;
左外连接包含左表的孤儿数据；
右外连接包含右表的孤儿数据；
全外连接包含两个表中的孤儿数据。
- 左外连接 left outer join
SQL> select em.last_name, de.department_name from employees em left outer join departments de on em.department_id = de.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins Accounting
Grant
- 右外连接 right outer join
SQL> select em.last_name, de.department_name from employees em right outer join departments de on em.department_id = de.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins Accounting
Treasury
Corporate Tax
- 全外连接 full outer join
SQL> select em.last_name, de.department_name from employees em full outer join departments de on em.department_id = de.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins Accounting
Grant
Gietz Accounting
Higgins Accounting
Treasury
Corporate Tax
Oracle数据库特有语法(+), 在等值连接中，当不需要显示该列的孤儿数据时，在列名后加上(+)
SQL> select em.last_name, de.department_name from employees em, departments de where em.department_id(+) = de.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins Accounting
Treasury
Corporate Tax
Control And Credit
- 交叉连接 CROSS JOIN
导致两个表交叉乘积，与笛卡尔乘积效果相同
select em.last_name, de.depatment_id from employees em cross join departments de;
- 自然连接 NATURAL JOIN
相当于等值连接，基于两表之间有相同名字的所有列，在匹配列中取等值的行
前提：两表需有相同名字的列；同名列的数据类型应当相同
SQL> select de.department_id, de.department_name, lo.CITY from departments de NATURAL JOIN locationS lo;
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
60 IT Southlake
50 Shipping South San Francisco
10 Administration Seattle
- 对于自然连接的补充，仅对指定的列作等值连接 USING
当有多个列匹配时，用USING子句匹配指定的列；
如果某列在USING中使用，在引用该列时，列名前不能加限定词
NATURAL JOIN 和 USING不能同时使用
SQL> select de.department_id, de.department_name, lo.city from departments de NATURAL JOIN locationS lo USING(location_id);
select de.department_id, de.department_name, lo.city from departments de NATURAL JOIN locations lo USING(location_id)
ORA-00933: SQL 命令未正确结束
SQL> select de.department_id, de.department_name, lo.city from departments de JOIN locations lo USING(location_id);
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
60 IT Southlake
50 Shipping South San Francisco
10 Administration Seattle
SQL> select de.department_id, de.department_name, lo.city from departments de JOIN locations lo USING(location_id) where lo.location_id = 1800;
select de.department_id, de.department_name, lo.city from departments de JOIN locations lo USING(location_id) where lo.location_id = 1800
ORA-25154: USING 子句的列部分不能有限定词
SQL> select de.department_id, de.department_name, lo.city from departments de JOIN locations lo USING(location_id) where location_id = 1800;
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
20 Marketing Toronto
- 内连接 INNER JOIN
使用 INNER JOIN 连接表，其后用 ON 给定连接条件;
连接条件如果为等值时，可以使用 USING
SQL> select e.last_name, d.department_name, l.city from employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN locations l ON d.location_id = l.location_id where e.employee_id = 200;
LAST_NAME DEPARTMENT_NAME CITY
------------------------- ------------------------------ ------------------------------
Whalen Administration Seattle
使用USING
SQL> select e.last_name, d.department_name, l.city from employees e INNER JOIN departments d USING(department_id) INNER JOIN locations l USING(location_id) where e.employee_id = 200;
LAST_NAME DEPARTMENT_NAME CITY
------------------------- ------------------------------ ------------------------------
Whalen Administration Seattle
练习
查询显示A表的last_name, department_id, B表的department_name，A、B表中都有相同的 department_id:
三种解答
等值连接
select a.last_name, a.department_id, b.department_name from a, b where a.department_id = b.department_id;
内连接
select a.last_name, a.department_id, b.department_name from a INNER JOIN b ON a.department_id = b.department_id;
USING子句
select a.last_name, department_id, b.department_name from a INNER JOIN b USING(department_id);
子查询
子查询是一个select语句，嵌套在另一个select语句之中的子句。
- 单行子查询
子查询返回单行结果，比较运算符为单行运行算
可用于 WHERE 子句、HAVING 子句
WHERE子句
SQL> select em.last_name, em.employee_id, em.salary from employees em where em.salary = (select min(salary) from employees);
LAST_NAME EMPLOYEE_ID SALARY
------------------------- ----------- ----------
Olson 132 2100.00
HAVING子句
SQL> select em.department_id, min(em.salary) from employees em group by em.department_id having min(em.salary) > (select min(salary) from employees where department_id=50);
DEPARTMENT_ID MIN(EM.SALARY)
------------- --------------
100 6900
30 2500
7000
90 17000
20 6000
70 10000
110 8300
80 6100
40 6500
60 4200
10 4400
11 rows selected
- 多行子查询
子查询返回结果为多行
运算付有 IN, ANY, ALL
IN 举例，查询每个部门最低工资的雇员信息
SQL> select e.last_name, e.salary, e.department_id from employees e where e.salary||e.department_id in (select min(salary)||department_id from employees group by department_id);
LAST_NAME SALARY DEPARTMENT_ID
------------------------- ---------- -------------
Kochhar 17000.00 90
De Haan 17000.00 90
Lorentz 4200.00 60
Popp 6900.00 100
ANY 举例，小于 ANY中的最大值，大于ANY中的最小值，条件即成立
SQL> select em.employee_id, em.last_name, em.job_id, em.salary from employees em where em.salary < any(select salary from employees where job_id='IT_PROG') and job_id <> 'IT_PROG';
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
132 Olson ST_CLERK 2100.00
136 Philtanker ST_CLERK 2200.00
128 Markle ST_CLERK 2200.00
135 Gee ST_CLERK 2400.00
127 Landry ST_CLERK 2400.00
191 Perkins SH_CLERK 2500.00
ALL 举例，小于ALL中最小值，大于ALL中最大值，条件才成立
SQL> select last_name, job_id, salary from employees where job_id <> 'IT_PROG' and salary < all(select salary from employees where job_id = 'IT_PROG');
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
Bull SH_CLERK 4100.00
Bell SH_CLERK 4000.00
Everett SH_CLERK 3900.00
Chung SH_CLERK 3800.00
