C/C++教程

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

本文主要是介绍Oracle 19C学习 - 07. 使用联接从多个表中查询数据,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

(本博文采用的数据库是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;

 

这篇关于Oracle 19C学习 - 07. 使用联接从多个表中查询数据的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!