SQL基础-->多表查询

--==========================

--SQL基础-->多表查询

--==========================

/*

一、多表查询

简言之,根据特定的连接条件从不同的表中获取所需的数据

笛卡尔集的产生条件:

省略连接条件

连接条件无效

第一个表中的所有行与第二个表中的所有行相连接

二、多表查询语法:*/

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

/*

但要注意where 不要省了,省略where 即为笛卡尔集,而且where 条件要有效,

两张表间有一个相同的字段,才好进行有效的多表查询

查询时列名前,加表名或表别名前辍(如果字段在两个表中是唯一的可以不加)

为了简化SQL书写,可为表名定义别名,格式:from 表名别名

如:from emp e,dept d

建议使用表的别名及表前缀,使用表别名可以简化查询,而使用表前缀则可以提高查询性能

例:查询每个员工的工号,姓名,工资,部门名和工作地点*/

select empno,ename,sal,dname,loc from emp,dept

where emp.deptno=dept.deptno;

/*

三、多表连接类型:

从数据显示方式来讲有:内连接和外连接。

内连接:只返回满足连接条件的数据。

外连接:除了返回满足连接条的行以外,还返回左(右)表中,不满足条件的行,

称为左(右)连接

内连接*/

select empno,ename,sal,dname,loc from emp,dept

where emp.deptno=dept.deptno; --(Oracle 8i 及以前的写法)

--内连接的另一种写法:

select empno,ename,job,sal,dept.deptno,dname,loc

from emp join dept on (emp.deptno=dept.deptno); -- (SQL 99的写法)

/*

外连接:

两个表的查询中,使用外连接可以查询另一个表或者两个中不满足连接条件的数据。

外连接的符号是(+),(+)要放在字段名后。(+)对面的那个表,会全部显示。

外连接语法*/

SELECT table1.column, table2.column --右外连接

FROM table1, table2

WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column --左外连接

FROM table1, table2

WHERE table1.column = table2.column(+);

--例:

select empno,ename,job,sal,dept.deptno,dname,loc

from emp,dept

where emp.deptno(+)=dept.deptno; -- (Oracle 8i 及以前的写法)

--另一种写法(右连接): -- (SQL 99的写法)

select empno,ename,job,sal,dept.deptno,dname,loc

from emp right join dept on (emp.deptno=dept.deptno);

--左连接: (SQL 99的写法)

select empno,ename,job,sal,dept.deptno,dname,loc

from emp left join dept on (emp.deptno=dept.deptno);

--全连接(满连接) (SQL 99的写法)

select empno,ename,job,sal,d.deptno,dname,loc

from emp e full join dept d on (e.deptno=d.deptno);

/*

自然连接 (SQL 99的写法)

以两个表具有相同的字段的所有列为基础,可采用自然连接(natural join)

它将选择两个表中那些在所有匹配的列中值相等的行。

如果列具有相同的名称,但数据类型能够不同,则会报错*/

select empno,ename,job,sal,deptno,dname,loc

from emp natural join dept;

/*

自连接:

将自身表的一个镜像当另一个表来对待。*/

select ... from emp e,emp d ...

--下面列出SQL 99的语法供参考

SELECT table1.column, table2.column

FROM table1

[CROSS JOIN table2] |

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

ON(table1.column_name = table2.column_name)] |

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name = table2.column_name)];

--在下面的语法中

table1.column --指明从中检索数据的表和列

CROSS JOIN --返回两个表的笛卡尔集

NATURAL JOIN --根据相同的列名连接两个表

JOIN table

USING column_name --根据列名执行等值连接

JOIN table ON

table1.column_name --根据ON 子句中的条件执行等值连接

= table2.column_name

LEFT/RIGHT/FULL OUTER

/*

使用using子句创建连接

如果几个列具有相同的名称,但是数据类型不匹配,那么可以使用using子句来修改natural join

子句以指定要用于等值连接的列

在多个列匹配时,使用using 子句只匹配一个列

在引用列中不要使用表名或别名

natural join 和using 子句是互不相容的 */

--例:

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE location_id = 1400;

--下面的语句无效,因为where 子句中限定了location_id

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE d.location_id = 1400;

ORA-25154: column part of USING clause cannot have qualifier

--注意:

-- 两个表中名称相同的列在使用时不能有任何限定符,这一限制同样适用于natural join

/*

四、演示: */

--笛卡尔集

SQL> select empno,ename,dname from emp,dept;

EMPNO ENAME DNAME

---------- ---------- --------------

7369 SMITH ACCOUNTING

7499 ALLEN ACCOUNTING

7521 WARD ACCOUNTING

7566 JONES ACCOUNTING

7654 MARTIN ACCOUNTING

7698 BLAKE ACCOUNTING

7782 CLARK ACCOUNTING

7788 SCOTT ACCOUNTING

7839 KING ACCOUNTING

7844 TURNER ACCOUNTING

7876 ADAMS ACCOUNTING

--中间结果省略

56 rows selected.

--使用cross join 实现交叉连接,即笛卡尔集

SQL> select empno,ename,dname from emp

2 cross join dept;

EMPNO ENAME DNAME

---------- ---------- --------------

7369 SMITH ACCOUNTING

7499 ALLEN ACCOUNTING

7521 WARD ACCOUNTING

7566 JONES ACCOUNTING

7654 MARTIN ACCOUNTING

7698 BLAKE ACCOUNTING

7782 CLARK ACCOUNTING

7788 SCOTT ACCOUNTING

7839 KING ACCOUNTING

7844 TURNER ACCOUNTING

7876 ADAMS ACCOUNTING

--等值连接(Oracle 写法)

SQL> select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;

EMPNO ENAME DNAME

---------- ---------- --------------

7369 SMITH RESEARCH

7499 ALLEN SALES

7876 ADAMS RESEARCH

--部分结果省略

7902 FORD RESEARCH

7934 MILLER ACCOUNTING

14 rows selected.

--等值连接(SQL 99 写法)

SQL> select e.empno,e.ename,d.dname from emp e

2 inner join dept d

3 on e.deptno = d.deptno;

EMPNO ENAME DNAME

---------- ---------- --------------

7369 SMITH RESEARCH

7499 ALLEN SALES

7876 ADAMS RESEARCH

--部分结果省略

7902 FORD RESEARCH

7934 MILLER ACCOUNTING

14 rows selected.

--注意:表别名不支持as 用法

SQL> select e.empno,e.ename,d.dname from emp as e

2 inner join dept d

3 on e.deptno = d.deptno;

select e.empno,e.ename,d.dname from emp as e

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

--等值连接并增加条件

SQL> select e.empno,e.ename,d.dname from emp e,

2 dept d

3 where d.deptno = e.deptno

4 and e.ename = 'SCOTT';

EMPNO ENAME DNAME

---------- ---------- --------------

7788 SCOTT RESEARCH

--非等值连接

--查询雇员的姓名、薪水、级别且部门为的记录

SQL> select ename,sal,grade

2 from emp,salgrade

3 where sal between losal and hisal

4 and emp.deptno = 20;

ENAME SAL GRADE

---------- ---------- ----------

SCOTT 3000 4

FORD 3000 4

JONES 2975 4

ADAMS 1100 1

SMITH 800 1

--使用SQL 99写法实现上述功能

SQL> select e.ename,e.sal,s.grade

2 from emp e

3 join salgrade s

4 on e.sal between losal and hisal

5 and e.deptno = 20;

ENAME SAL GRADE

---------- ---------- ----------

SCOTT 3000 4

FORD 3000 4

JONES 2975 4

ADAMS 1100 1

SMITH 800 1

--右外连接

--注意:右外连接时,加号在等号的左边

--可以看到,左表emp中的列有为空值的

SQL> select e.ename,e.deptno,d.dname

2 from emp e,dept d

3 where e.deptno(+) = d.deptno;

ENAME DEPTNO DNAME

---------- ---------- --------------

CLARK 10 ACCOUNTING

KING 10 ACCOUNTING

MILLER 10 ACCOUNTING

JONES 20 RESEARCH

FORD 20 RESEARCH

ADAMS 20 RESEARCH

SMITH 20 RESEARCH

SCOTT 20 RESEARCH

WARD 30 SALES

TURNER 30 SALES

ALLEN 30 SALES

ENAME DEPTNO DNAME

---------- ---------- --------------

JAMES 30 SALES

BLAKE 30 SALES

MARTIN 30 SALES

OPERATIONS

15 rows selected.

--使用SQL 99写法实现右外连接

SQL> select e.ename,e.deptno,d.dname

2 from emp e

3 right join dept d

4 on e.deptno = d.deptno ;

ENAME DEPTNO DNAME

---------- ---------- --------------

CLARK 10 ACCOUNTING

KING 10 ACCOUNTING

MILLER 10 ACCOUNTING

JONES 20 RESEARCH

FORD 20 RESEARCH

ADAMS 20 RESEARCH

SMITH 20 RESEARCH

SCOTT 20 RESEARCH

WARD 30 SALES

TURNER 30 SALES

ALLEN 30 SALES

ENAME DEPTNO DNAME

---------- ---------- --------------

JAMES 30 SALES

BLAKE 30 SALES

MARTIN 30 SALES

OPERATIONS

--左外连接

--注意:左外连接时,加号在等号的右边

SQL> select d.dname,e.ename,e.deptno

2 from dept d,emp e

3 where d.deptno = e.deptno(+)

4 order by d.deptno;

DNAME ENAME DEPTNO

-------------- ---------- ----------

ACCOUNTING CLARK 10

ACCOUNTING KING 10

ACCOUNTING MILLER 10

RESEARCH JONES 20

RESEARCH FORD 20

RESEARCH ADAMS 20

RESEARCH SMITH 20

RESEARCH SCOTT 20

SALES WARD 30

SALES TURNER 30

SALES ALLEN 30

DNAME ENAME DEPTNO

-------------- ---------- ----------

SALES JAMES 30

SALES BLAKE 30

SALES MARTIN 30

OPERATIONS

15 rows selected.

--使用SQL 99写法实现左外连接

SQL> select d.dname,e.ename,e.deptno

2 from dept d

3 left join emp e

4 on d.deptno = e.deptno

5 order by d.deptno;

DNAME ENAME DEPTNO

-------------- ---------- ----------

ACCOUNTING CLARK 10

ACCOUNTING KING 10

ACCOUNTING MILLER 10

RESEARCH JONES 20

RESEARCH FORD 20

RESEARCH ADAMS 20

RESEARCH SMITH 20

RESEARCH SCOTT 20

SALES WARD 30

SALES TURNER 30

SALES ALLEN 30

DNAME ENAME DEPTNO

-------------- ---------- ----------

SALES JAMES 30

SALES BLAKE 30

SALES MARTIN 30

OPERATIONS

15 rows selected.

--自连接

SQL> select e.ename || ' works for ' || m.ename

2 from emp e,emp m

3 where e.empno = m.mgr;

E.ENAME||'WORKSFOR'||M.ENAME

-------------------------------

FORD works for SMITH

BLAKE works for ALLEN

BLAKE works for WARD

KING works for JONES

BLAKE works for MARTIN

KING works for BLAKE

KING works for CLARK

JONES works for SCOTT

BLAKE works for TURNER

SCOTT works for ADAMS

BLAKE works for JAMES

E.ENAME||'WORKSFOR'||M.ENAME

-------------------------------

JONES works for FORD

CLARK works for MILLER

13 rows selected.

--自然连接

SQL> select empno,ename,job,deptno,dname,loc

2 from emp

3 natural join dept;

EMPNO ENAME JOB DEPTNO DNAME LOC

---------- ---------- --------- ---------- -------------- -------------

7369 SMITH CLERK 20 RESEARCH DALLAS

7499 ALLEN SALESMAN 30 SALES CHICAGO

7521 WARD SALESMAN 30 SALES CHICAGO

7566 JONES MANAGER 20 RESEARCH DALLAS

7654 MARTIN SALESMAN 30 SALES CHICAGO

7698 BLAKE MANAGER 30 SALES CHICAGO

7782 CLARK MANAGER 10 ACCOUNTING NEW YORK

7788 SCOTT ANALYST 20 RESEARCH DALLAS

7839 KING PRESIDENT 10 ACCOUNTING NEW YORK

7844 TURNER SALESMAN 30 SALES CHICAGO

7876 ADAMS CLERK 20 RESEARCH DALLAS

EMPNO ENAME JOB DEPTNO DNAME LOC

---------- ---------- --------- ---------- -------------- -------------

7900 JAMES CLERK 30 SALES CHICAGO

7902 FORD ANALYST 20 RESEARCH DALLAS

7934 MILLER CLERK 10 ACCOUNTING NEW YORK

14 rows selected.

--使用using 子句创建连接

SQL> select e.empno,e.ename,d.dname,d.loc

2 from emp e

3 join dept d

4 using (deptno)

5 where deptno in (20,40);

EMPNO ENAME DNAME LOC

---------- ---------- -------------- -------------

7369 SMITH RESEARCH DALLAS

7566 JONES RESEARCH DALLAS

7788 SCOTT RESEARCH DALLAS

7876 ADAMS RESEARCH DALLAS

7902 FORD RESEARCH DALLAS

/*

五、更多*/

Oracle 数据库实例启动关闭过程

Oracle 10g SGA 的自动化管理

使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例

Oracle实例和Oracle数据库(Oracle体系结构)

SQL 基础-->常用函数

SQL基础-->过滤和排序

SQL 基础-->SELECT 查询

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

SQL 基础--> 子查询

ORA-01427: single-row subquery returns more than one row

652
来自专栏数据和云

深入解析:半连接与反连接的原理和等价改写方法

半连接的原理及等价改写 1. 什么是半连接 当两张表进行关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXI...

3407
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

1654
来自专栏乐沙弥的世界

当心 CREATE TABLE AS

对 DBA 而言,CREATE TABLE AS 可谓是家常便饭,顺手拈来。需不知该方式虽然简单,但疏忽也容易导致意想不到的问题。笔者前阵子就碰上...

1042
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------多表查询

  SUM(SAL)                                                                      ...

750
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------,多行函数

  SUM(SAL)                                                                      ...

1170
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

3045
来自专栏Hadoop数据仓库

一个用 Oracle 函数索引进行优化的例子

表中有500万条记录,原来没有索引: set timing on set autotrace traceonly SQL> select count(*), ...

2386
来自专栏数据库新发现

如何从结果集中获得随机结果

全表扫描(Full table Scan) 全表扫描返回表中所有的记录。 执行全表扫描,Oracle读表中的所有记录,考查每一行是否满足WHERE条件。Orac...

1032
来自专栏数据和云

Oracle Hints - 先知的提示

在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。 在Oracle 11g中,新增的视图V$SQL_HINT记录了Oracle数据库中...

2966

扫码关注云+社区

领取腾讯云代金券