Oracle 19C学习 - 07. 使用联接从多个表中查询数据

2022-01-31 06:19:10

（本博文采用的数据库是Oracle自带的hr用户范本数据库)

多表查询的准备工作

在hr用户下，创建A表,并插入数值。

--创建表A--
CREATE TABLE A (ID NUMBER);

INSERT INTO A VALUES(1);

COMMIT;

SELECT * FROM A;

--创建表B--

CREATE TABLE B (ID3 NUMBER, ID2 NUMBER(10));

INSERT INTO B VALUES(100,10);

INSERT INTO B VALUES(200,20);

COMMIT;

SELECT * FROM B;

--创建表C--

CREATE TABLE C (ID4 NUMBER, ID2 VARCHAR2(10));

INSERT INTO C VALUES(3,'10');

INSERT INTO C VALUES(4,'20');

COMMIT;

SELECT * FROM C;

--CTAS方式赋值employees表到EMP，带数据--

CREATE TABLE EMP AS SELECT * FROM employees WHERE 1=1;  --将employees的数据和表结构复制到EMP表，WHERE子句可有可无。

SELECT * FROM EMP;

--CTAS方式赋值employees表到EMP1，仅数据结构，无数据--

CREATE TABLE EMP1 AS SELECT * FROM employees WHERE 1=2;  --1=2不成立，所以没有数据。

SELECT * FROM EMP1;

DESC EMP1;

--CRATE TABLE XXX AS SELECT ...    --> CTAS建表方式，不需要提交，属于DDL语言范畴。

使用连接从多个表中获得数据的语法

创建 Natural Joins（自然连接）

NATURAL JOIN子句基于两个表中具有相同名称的所有列。
它从两个表中选择相同名称的列具有相同值的行作为有效数据。
如果具有相同名称的列具有不同的数据类型—会发生隐式数据类型转换。

SELECT employee_id, first_name, job_id, job_title
FROM employees NATURAL JOIN jobs
ORDER BY 1;

EMPLOYEE_ID FIRST_NAME           JOB_ID     JOB_TITLE
----------- -------------------- ---------- -----------------------------------
        100 Steven               AD_PRES    President
        101 Neena                AD_VP      Administration Vice President
        102 Lex                  AD_VP      Administration Vice President
        103 Alexander            IT_PROG    Programmer
        104 Bruce                IT_PROG    Programmer
        105 David                IT_PROG    Programmer
        106 Valli                IT_PROG    Programmer
        107 Diana                IT_PROG    Programmer
        108 Nancy                FI_MGR     Finance Manager
        109 Daniel               FI_ACCOUNT Accountant
        110 John                 FI_ACCOUNT Accountant

employees表和departments表有两个相同的字段。

对于他们的自然连接，需要两个同名字段相同的数据都相等，才能作为有效的记录放到新的连接表中。

SELECT * FROM employees NATURAL JOIN departments; --最后得到的记录数为32条。

DESC employee;


名称             空值?      类型           
-------------- -------- ------------
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    

DESC departments;

名称              空值?      类型           
--------------- -------- ------------ 
DEPARTMENT_ID   NOT NULL NUMBER(4)    
MANAGER_ID               NUMBER(6)

对于我们自己创建的数据库B和数据库C的数据结构如下：

数据库B:

名称空值? 类型
--- --- ----------
ID3     NUMBER
ID2     NUMBER(10)

数据库C:
名称空值? 类型
--- --- ------------
ID4     NUMBER
ID2     VARCHAR2(10)

他们有相同的ID2，但是数据类型不同。在做自然连接的时候，ID2的字段会自动进行隐式数据转换，从而将数据匹配起来。

SELECT * FROM B NATURAL JOIN C;

ID2               ID3        ID4
---------- ---------- ----------
10                100          3
20                200          4

使用USING子句创建链接

查询必须是等值连接，不等值就不链接了，就不输出了。
等值连接中的列必须具有相同的名称，最好数据类型一致（有可能会遇到列名相同，但数据类型不同时，会发生隐式数据类型转换）
USING子句可以含多个列，也可以只使用其中的一个列。
USING(X)中的列不可以接表名或别名等限定语，默认就是指共同列，不能指定表名

SELECT employee_id, last_name, department_id, department_name
FROM employees JOIN departments
USING (department_id);

--也可以同时匹配两个字段，只有两个字段的值都相同的记录才会显示出来。
SELECT employee_id, last_name, department_id, department_name
FROM employees JOIN departments
USING (department_id, manager_id);

与自然连接和USING子句链接等效的SQL语句

SELECT employee_id, last_name,  department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND employees.manager_id = departments.manager_id;

限定不明确的列名

使用表前缀来限定多个表中的列名： employees和departments都有department_id，可使用employees.department_id。
使用表别名代替完整的表名前缀：比如： FROM employees E, 之后可以使用E.department_id。
使用列别名来区分名称相同但位于不同表中的列。SELECT E.department_id FROM employee E

SELECT last_name, salary,department_id, d.department_name
FROM employees E JOIN departments D
USING (department_id);

使用ON子句连接

与JOIN的固定搭配，可以指定指定要连接的列。

SELECT employee_id, last_name, E.job_id, job_title
FROM employees E JOIN jobs J
ON E.job_id = J.job_id;

等效于

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

将附加条件应用于ON字句连接

第一种写法 JOIN...ON...AND
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 100;      --使用department_id关联employees和departments表，并且只保留mamager_id=100的记录。

第二种写法JOIN...ON...WHERE
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 100;

创建三表连接

SELECT employee_id, last_name, department_name, city 
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

自连接

同一张employee表，员工的manager，也是employee中的一条员工记录，创建自连接，来体现出员工以及他的经理。

SELECT emp.last_name "员工", emp.salary "员工工资", 
mgr.last_name "经理", mgr.salary "经理工资"
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

非等式连接

连接的条件不是等式，而是大于、小于、大于等于、小于等于或者BETWEEN...AND的区间范围。

新增一个表，叫做job_class，来把员工的工资分为三类。
CREATE TABLE JOB_CLASS (CLASS CHAR(10), LOW NUMBER, HIGH NUMBER);
INSERT INTO job_class VALUES ('A',1000,9999);
INSERT INTO job_class VALUES ('B',10000,19999);
INSERT INTO job_class VALUES ('C',20000,29999);

使用非等式连接建立关联，employee表的工资字段对比job_class表的工资范畴来对应上相应的工资class。
SELECT employee_id, last_name, salary, class
FROM employees e JOIN job_class j
ON e.salary BETWEEN j.low AND j.high;

内连接Inner Join

内连接只输出相等值的结果，不输出不匹配值作为结果的一部分。

只写JOIN关键字默认就是内连接。

内连接的三种写法：

SELECT * FROM employees e INNER JOIN departments d 
ON e.department_id = d.department_id;

SELECT * FROM employees e JOIN departments d 
ON e.department_id = d.department_id;

SELECT * FROM employees e, departments d
WHERE e.department_id = d.department_id;

外连接Outer Join

左右外连接：返回INNER联接的结果以及左（或右）表中不匹配的行的两个表之间的联接称为左（或右）OUTER联接。
完全外连接：返回一个内部联接的结果以及一个左右联接的结果，是一个完整的外部联接。

对于左右连接，想要完整输出的表，叫做驱动表，与之连接的表，叫做匹配表。

--左外连接，Employees作为驱动表的时候，驱动表178号员工无department_id一样列印出来。
SELECT * 
FROM employees e LEFT JOIN departments d  
ON e.department_id = d.department_id;

--右外连接，Employees作为驱动表的时候，employees表中没有用到的department_id一样列印出来。

SELECT *
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

输出两张表所有的匹配和不匹配的数据就用到全外连接。

SELECT *
FROM employees e FULL JOIN departments d
ON e.department_id = d.department_id
ORDER BY employee_id desc;

笛卡尔积 Cross Join

笛卡尔积并不是按照某个字段匹配，而是直接将左边表的所有行和右边表的第一行进行连接，然后左边表的所有行和右边表的第二行进行连接，以此类推。
笛卡尔积会产生大量行，并且结果很少有用。

左边表m行，右边表n行，笛卡尔积会产生m*n行数据。

SELECT * 
FROM employees e CROSS JOIN departments d;

码农公寓

相关文章