使用SQL语句
SQL语言包括数据查询语言(SELECT)、数据操纵语言(INSERT、UPDATE、DELETE)、事务控制语言(COMMIT、ROLLBACK、SAVEPOINT)、数据定义语言(CREATE TABLE、ALTER TABLE、DROP)、数据控制语言(GRANT、REVOKE)等五个部分。
当编写PL/SQL应用程序时，只能直接嵌入SELECT...INTO...语句、DML语句和事务控制语句。
(一)使用基本查询
①简单查询语句
(1) 语法：SELECT <*,column [别名],...> FROM TABLE|VIEW;
其中：*表示检索所有列，column用于指定要检索的列或表达式*（多列或多表达式之间用逗号分隔），别名用于指定列或表达式的别名，FROM用于指定要检索的表或视图。
注：*和column不能混合使用
(2)DESC(describe) 表名称 --用于确定表结构
(3)检索日期列时，日期默认显示格式为'dd-mon-yy',如果要想使用'yyyy-mon-dd'格式，则必须使用TO_CHAR函数进行转换。
例：
SQL> select ename,job,hiredate from emp where deptno=10; --默认
ENAME JOB HIREDATE
---------- --------- --------------
CLARK MANAGER 09-6月 -81
KING PRESIDENT 17-11月-81
MILLER CLERK 23-1月 -82
SQL> select ename,job,to_char(hiredate,'yyyy-mon-dd') from emp where deptno=10; --使用to_char函数
ENAME JOB TO_CHAR(HIREDATE
---------- --------- ----------------
CLARK MANAGER 1981-6月 -09
KING PRESIDENT 1981-11月-17
MILLER CLERK 1982-1月 -23
(4)使用distinct去除重复行
注：distinct一定要放在第一个选择列的前面
例：
SQL> SELECT deptno,job FROM EMP; --未使用distinct取消重复行
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
30 SALESMAN
20 MANAGER
30 SALESMAN
30 MANAGER
10 MANAGER
20 ANALYST
10 PRESIDENT
30 SALESMAN
20 CLERK
30 CLERK
20 ANALYST
10 CLERK
已选择14行。
SQL> select distinct deptno,job from emp; --使用distinct取消重复行
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
已选择9行。
(5)指定列别名
默认情况下，列标题是大写格式的列名或表达式。通过使用列别名，可以改变列标题的显示样式。
指定列别名：
|- 列名或表达式 别名 或 列名或表达式 AS 别名 --列标题是大写格式的别名
|- 列名或表达式 “别名” 或 列名或表达式 AS “别名” --列标题保留别名的格式，并且别名可以包含特殊字符和空格 ，建议使用这种方式
例：
SQL> select deptno*2,'loc: '||dname,loc from dept where deptno=1 --默认情况下
DEPTNO*2 'LOC:'||DNAME LOC
---------- ------------------- -------------
20 loc: ACCOUNTING 纽约
SQL> select deptno a,'loc: '||dname as b,loc as c from dept where deptno=10; --指定别名时未用双引号引住
A B C
---------- ------------------- -------------
10 loc: ACCOUNTING 纽约
SQL> select deptno "a",'loc: '||dname as "b /*",loc as c from dept where deptno=10; --指定别名时用双引号引住
a b /* C
---------- ------------------- -------------
10 loc: ACCOUNTING 纽约
(6)使用算术表达式
在执行查询操作时，可以在数字列上使用算术表达式，可以使用括号改变优先级。
例：
SQL> select (deptno+1)*2 "部门号",dname||' '||loc as 部门名及位置 from dept;
部门号 部门名及位置
---------- ----------------------------
22 ACCOUNTING 纽约
42 RESEARCH 达拉斯
62 SALES 芝加哥
82 OPERATIONS 波士顿
(7)处理null
null表示空值，它既不是空格也不是0。当算术表达式包含null时，其结果也是null。
例1：
SQL> SELECT ENAME,SAL,COMM,SAL+COMM FROM EMP where deptno=30;
ENAME SAL COMM SAL+COMM
---------- ---------- ---------- ----------
ALLEN 1600 300 1900
WARD 1250 500 1750
MARTIN 1250 1400 2650
BLAKE 2850
TURNER 1500 0 1500
JAMES 950
已选择6行。
NVL(expr1,expr2):如果expr1是null,则返回expr2,否则返回expr1。expr1、expr2可以是任何数据类型，但两者的数据类型要匹配。
例2：
SQL> SELECT ENAME,SAL,COMM,SAL+nvl(COMM,0) FROM EMP where deptno=30;
ENAME SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
ALLEN 1600 300 1900
WARD 1250 500 1750
MARTIN 1250 1400 2650
BLAKE 2850 2850
TURNER 1500 0 1500
JAMES 950 950
已选择6行。
NVL2(expr1,expr2,expr3):如果expr1是null，则返回expr3。如果expr1不是null,则返回expr2。expr1可以是任何数据类型，expr2、expr3可以是除long之外的任何数据类型。但expr1、expr2、expr3三者数据类型必须要匹配。
例3：
SQL> SELECT ENAME,SAL,COMM,SAL+nvl2(COMM,comm,0) FROM EMP where deptno=30;
ENAME SAL COMM SAL+NVL2(COMM,COMM,0)
---------- ---------- ---------- ---------------------
ALLEN 1600 300 1900
WARD 1250 500 1750
MARTIN 1250 1400 2650
BLAKE 2850 2850
TURNER 1500 0 1500
JAMES 950 950
已选择6行。
(8)连接字符串
使用'||'实现字符串之间的连接。如果要在字符串中加入数字，则在||后直接指定数字。如果要在字符串中加入字符或日期，则在||后必须用单引号引住。
例：
SQL> select deptno||123,dname||'2010-01-01' from dept where deptno=10;
DEPTNO||123 DNAME||'2010-01-01'
------------------------------------------- ------------------------
10123 ACCOUNTING2010-01-01
(9)所有比较操作符
=、！=或<>、>=、<=、>、<
[not] between m and n
[not] in(list)
[not] like
is [not] null
(10)所有逻辑运算符
NOT、AND、OR 优先级递减
逻辑运算符的优先级低于任何一种比较运算符
(11)使用where子句
语法：SELECT <*,column [别名],...> FROM TABLE|VIEW [WHERE conditions];
①在where条件中使用数字值
当在WHERE条件中使用数字值时，既可以使用单引号引住数字值，也可以直接引用数字值。
例：
SQL> select * from dept where deptno between '1' and 20;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING 纽约
20 RESEARCH 达拉斯
②在where条件中使用字符值 --注：字符值区分大小写，并要用引号引住
例：
SQL> select * from dept where dname=UPPER('ACCOUNtING'); --upper、lower
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING 纽约
③在where条件中使用日期值
在where条件中使用日期值时，必须要用单引号引住，并且日期格式必须要符合默认日期显示格式。如果日期值不符合默认日期显示格式，那么必须要用TO_DATE函数进行转换。
例1:日期格式符合默认日期显示格式
SQL> SELECT * FROM EMP WHERE HIREDATE<'01-1月-1981';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
例2： 期格式不符合默认日期显示格式
SQL> SELECT * FROM EMP WHERE HIREDATE<'1981-01-01';
SELECT * FROM EMP WHERE HIREDATE<'1981-01-01'
*
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
例3：使用to_date函数
SQL> SELECT * FROM EMP WHERE HIREDATE<to_date('1981-01-01','yyyy-mm-dd');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
④在where条件中使用[not] between m and n n>=m
例：
SQL> select * from dept where deptno not between 20 and 30;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING 纽约
40 OPERATIONS 波士顿
⑤在where条件中使用[not] LIKE
通配符：'%'：表示0个或多个字符。'_'：表示1个字符。
⑥在where条件中使用[NOT] IN(LIST)
例1：IN(LIST)
SQL> select * from dept where deptno in(10,20);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING 纽约
20 RESEARCH 达拉斯
例2：[NOT] IN(LIST)
SQL> select * from dept where deptno not in(10,20);
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES 芝加哥
40 OPERATIONS 波士顿
(12)使用ORDER BY子句
当执行查询操作时，默认情况下会按照行数据插入的先后顺序来显示行数据。
语法：
SELECT <*,column [别名],...> FROM TABLE|VIEW [WHERE conditions] [ORDER BY COL1[ASC|DESC], COL1[ASC|DESC],......]; --ASC：默认、可省略、升序 DESC：降序
注：|-当在SELECT语句中同时包含多个子句(WHERE、GROUP BY、ORDER BY等)时，ORDER BY必须是最后一条子句。
|-NULL值比任何列值都大
排序：
-
使用多列或多表达式排序 --当第一个列或表达式存在相同数据时，然后以第二个列或表达式进行排序，以此类推
-
使用非选择列表列进行排序
例：用非选择列表列进行排序
SQL> SELECT DNAME,LOC FROM DEPT ORDER BY DEPTNO DESC;
DNAME LOC
-------------- -------------
OPERATIONS 波士顿
SALES 芝加哥
RESEARCH 达拉斯
ACCOUNTING 纽约
-
使用列别名进行排序
例：使用列别名进行排序
SQL> SELECT deptno AS "部门号",DNAME,LOC FROM DEPT ORDER BY 部门号 DESC;
部门号 DNAME LOC
---------- -------------- -------------
40 OPERATIONS 波士顿
30 SALES 芝加哥
20 RESEARCH 达拉斯
10 ACCOUNTING 纽约
-
使用列位置编号排序 按照1，2，......
例：使用列位置编号排序
SQL> select ename,sal,deptno from emp order by 3,2 desc,1 asc;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
CLARK 2450 10
MILLER 1300 10
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
ADAMS 1100 20
SMITH 800 20
BLAKE 2850 30
ALLEN 1600 30
TURNER 1500 30
MARTIN 1250 30
WARD 1250 30
JAMES 950 30
(二)数据分组
当执行数据统计时，需要将表中的数据划分成几个组，最后统计每个组的数据结果。在关系数据库中，数据分组是通过使用group by子句、分组函数以及having子句共同实现的。其中group by子句用于指定要分组的列(如：deptno、job、mgr)，而分组函数则用于显示统计结果(如：count、sum、max、min、avg)，而having子句则用于限制分组显示结果。
(1)分组函数
分组函数作用于多行，并返回一个结果，所以有时候也叫做多行函数。一般情况下，分组函数要与group by子句结合使用。在使用分组函数时，如果忽略了group by子句，那么会汇总所有行，并产生一个结果。
注：
1、当使用分组函数时，分组函数只能出现在选择列表、HAVING子句、ORDER BY子句中，而不能出现在WHERE子句、GROUP BY子句中。
2、除了分组函数COUNT(*)之外，其他分组函数多会忽略NULL行。
3、当使用分组函数时，在分组函数中可以指定ALL、DISTINCT选项，其中ALL为默认。
max:该函数用于取得列或表达式的最大值，适用于任何数据类型
min:该函数用于取得列或表达式的最小值，适用于任何数据类型
avg:该函数用于取得列或表达式的平均值，只适用于数字类型
sum:该函数用于得列或表达式的总和，只适用于数字类型
count:该函数用于取得总计行数
例1： max函数使用示例
SQL> select max(empno) 最大雇员号,max(ename) 最大雇员名,job 职位 from emp group by job;
最大雇员号 最大雇员名 职位
---------- ---------- ---------
7934 SMITH CLERK
7844 WARD SALESMAN
7839 KING PRESIDENT
7782 JONES MANAGER
7902 SCOTT ANALYST
例2： min函数使用示例
SQL> select min(empno) 最小雇员号,min(ename) 最小雇员名,job 职位 from emp group by job;
最小雇员号 最小雇员名 职位
---------- ---------- ---------
7369 ADAMS CLERK
7499 ALLEN SALESMAN
7839 KING PRESIDENT
7566 BLAKE MANAGER
7788 FORD ANALYST
例3：avg函数使用示例
SQL> select avg(sal+nvl(comm,0)) 平均月薪,deptno from emp group by deptno order by 2;
平均月薪 DEPTNO
---------- ----------
2916.66667 10
2175 20
1933.33333 30
例4：sum函数使用示例
SQL> select sum(sal+nvl(comm,0)) 平均月薪,deptno from emp group by deptno order by 2;
平均月薪 DEPTNO
---------- ----------
8750 10
10875 20
11600 30
例5、count(*)函数使用示例
SQL> select count(*) from emp;
COUNT(*)
----------
14
例6、使用COUNT(表达式)，只统计非null行
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
例7：DISTINCT使用示例
SQL> SELECT DEPTNO,COUNT(DISTINCT DEPTNO) FROM EMP GROUP BY DEPTNO;
DEPTNO COUNT(DISTINCTDEPTNO)
---------- ---------------------
10 1
20 1
30 1
例8：ALL使用示例
SQL> SELECT DEPTNO,COUNT(ALL DEPTNO) FROM EMP GROUP BY DEPTNO;
DEPTNO COUNT(ALLDEPTNO)
---------- ----------------
30 6
20 5
10 3
(2)group by和having
group by子句用于指定要分组的列，而having子句则用于限制分组显示结果。
注：如果在SELECT选择列表中同时包含有列、表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中。
使用group by子句和having子句的语法：
SELECT column,group_function FROM table
[WHERE conditions]
[GROUP BY column,expression,......] --不能使用列别名
[HAVING group_condition] --不能使用列别名
[ORDER BY col1 ASC,col2 DESC,...]; --能够使用列别名
|-当进行单列分组时，会基于列的每个不同值生成一个数据统计结果。
|-当进行多列分组时，会基于多个列的不同值生成数据统计结果。
注：查询结果有多少行取决于GROUP BY将表中数据分成多少个组。
例1：单列分组示例
SQL> SELECT MIN(SAL+NVL(COMM,0)) AS "最低工资",MAX(SAL+NVL2(COMM,COMM,0)) AS "最高工资",DEPTNO AS "
部门号" FROM EMP GROUP BY DEPTNO ORDER BY "部门号";
最低工资 最高工资 部门号
---------- ---------- ----------
1300 5000 10
800 3000 20
950 2850 30
例2：多列分组示例
SQL> SELECT DEPTNO AS "部门号",JOB AS "职位",MIN(SAL+NVL(COMM,0)) AS "最低工资",MAX(SAL+NVL2(COMM,CO
MM,0)) AS "最高工资" FROM EMP GROUP BY DEPTNO,JOB ORDER BY "部门号";
部门号 职位 最低工资 最高工资
---------- --------- ---------- ----------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
20 ANALYST 3000 3000
20 CLERK 800 1100
20 MANAGER 2975 2975
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 1500 2650
已选择9行。
例3：HAVING使用示例
SQL> SELECT DEPTNO AS "部门号",AVG(SAL+NVL2(COMM,COMM,0)) AS "平均工资",MIN(SAL+NVL(COMM,0)) AS "最
低工资",MAX(SAL+NVL2(COMM,COMM,0)) AS "最高工资" FROM EMP
2 GROUP BY DEPTNO
3 HAVING MIN(SAL+NVL(COMM,0))>=1000
4 ORDER BY DEPTNO;
部门号 平均工资 最低工资 最高工资
---------- ---------- ---------- ----------
10 2916.66667 1300 5000
(3)ROLLUP、CLUB、GROUPING、GROUPING SETS
(三)连接查询
连接查询是指基于两个或两个以上表或视图的查询。
重命名表名：
1、old_table_name TO new_table_name --自动将小写字母转换为大写字母作为表名称，可以指定中文名
2、old_table_name TO "new_table_name" --可以包含特殊字符和空格，保持定义时格式，建议使用
指定表别名： -----注：表名称与别名之间不能有AS
1、表名称 别名
2、表名称 "别名"
注：指定了表别名之后，就不再允许使用表名称
①当使用连接查询时，应该在列名前加表名作为前缀。因为SELECT子句使用到的列可能在多个表中存在，防止触发列的二义性而报错。
例1：列名前加表名
SQL> select ename,job,dname from dept,emp where dept.deptno=emp.deptno and dept.deptno=10;
ENAME JOB DNAME
---------- --------- --------------
CLARK MANAGER ACCOUNTING
KING PRESIDENT ACCOUNTING
MILLER CLERK ACCOUNTING
例2：列名前不加表名
SQL> select ename,job,dname from dept,emp where dept.deptno=emp.deptno and deptno=10;
select ename,job,dname from dept,emp where dept.deptno=emp.deptno and deptno=10
*
第 1 行出现错误:
ORA-00918: 未明确定义列
②笛卡儿积(交叉连接)
当使用连接查询时，必须在WHERE子句中指定有效的连接条件。如果指定了无效的连接条件，那么会导致生成笛卡尔积(X*Y)
例：笛卡尔积
SQL> select * from dept,emp where dname=upper('sales');
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ -------------- ------------- ----- ---------- --------- ----- ----------- --------- --------- ------
30 SALES 芝加哥 7369 SMITH CLERK 7902 1980-12-17 800.00 20
30 SALES 芝加哥 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
30 SALES 芝加哥 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
30 SALES 芝加哥 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
30 SALES 芝加哥 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
30 SALES 芝加哥 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
30 SALES 芝加哥 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
30 SALES 芝加哥 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
30 SALES 芝加哥 7839 KING PRESIDENT 1981-11-17 5000.00 10
30 SALES 芝加哥 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
30 SALES 芝加哥 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
30 SALES 芝加哥 7900 JAMES CLERK 7698 1981-12-3 950.00 30
30 SALES 芝加哥 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
30 SALES 芝加哥 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
③相等连接
相等连接是指使用相等比较符(=)，指定连接条件的连接查询。该种连接查询主要用于检索主从表之间的相关数据。
例：相等连接使用示例
SQL> select empno,ename,job,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=30;
EMPNO ENAME JOB DNAME
----- ---------- --------- --------------
7499 ALLEN SALESMAN SALES
7521 WARD SALESMAN SALES
7654 MARTIN SALESMAN SALES
7698 BLAKE MANAGER SALES
7844 TURNER SALESMAN SALES
7900 JAMES CLERK SALES
④不等连接
不等连接是指在连接条件中使用除相等比较符外的其他比较操作符的连接查询，并且不等连接主要用于在不同表之间显示特定范围的信息。
例：不等连接使用示例
SQL> select grade,ename,job from emp a,salgrade b where a.sal between b.losal and hisal order by grade;
GRADE ENAME JOB
---------- ---------- ---------
1 SMITH CLERK
1 JAMES CLERK
1 ADAMS CLERK
2 WARD SALESMAN
2 MARTIN SALESMAN
2 MILLER CLERK
3 TURNER SALESMAN
3 ALLEN SALESMAN
4 CLARK MANAGER
4 BLAKE MANAGER
4 JONES MANAGER
4 SCOTT ANALYST
4 FORD ANALYST
5 KING PRESIDENT
14 rows selected
⑤自连接
自连接是指在同一张表之间的连接查询，它主要用在自参照表上显示上下级关系或层次关系。因为自连接是在同一张表之间的连接，所以必须要定义表别名。
自参照表是指在不同列之间具有参照关系或主从关系的表（如：EMP表EMPNO与MGR字段）
例1：
SQL> select a.empno as "雇员号",a.ename as "雇员名",b.ename as "---管理员" from emp a,emp b where a.mgr=b.empno order by 3;
雇员号 雇员名 ---管理员
------ ---------- ----------
7900 JAMES BLAKE
7654 MARTIN BLAKE
7499 ALLEN BLAKE
7844 TURNER BLAKE
7521 WARD BLAKE
7934 MILLER CLARK
7369 SMITH FORD
7788 SCOTT JONES
7902 FORD JONES
7698 BLAKE KING
7566 JONES KING
7782 CLARK KING
7876 ADAMS SCOTT
13 rows selected
⑥内连接、外连接
内连接用于返回满足连接条件的记录。外连接不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的记录。
语法：
SELECT table1.column ,... , table2.column ,...
FROM table1 < [ INNER] | LEFT [OUT] | RIGHT [OUT] | FULL [OUT] > JOIN table2
ON conditions;
其中：[INNER] JOIN:内连接 INNER可省略
LEFT [OUT] JOIN:左外连接
RIGHT [OUT] JOIN:右外连接
FULL [OUT] JOIN:完全外连接
ON子句用于指定连接条件
注：如果使用FROM子句指定内、外连接，则必须使用ON子句指定连接条件。如果使用(+)操作符指定外连接，则必须使用WHERE子句指定连接条件。
1、内连接
内连接用于返回满足连接条件的记录。
1.1使用[INNER] JOIN指定内过接
例1：使用INNER JOIN 进行内连接
SQL> SELECT A.ENAME,A.JOB,B.DNAME FROM EMP A INNER JOIN DEPT B ON A.DEPTNO=B.DEPTNO AND B.DEPTNO=10;
ENAME JOB DNAME
---------- --------- --------------
CLARK MANAGER ACCOUNTING
KING PRESIDENT ACCOUNTING
MILLER CLERK ACCOUNTING
例2：省略INNER进行内连接
SQL> SELECT A.ENAME,A.JOB,B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO=B.DEPTNO AND B.DEPTNO=1
ENAME JOB DNAME
---------- --------- --------------
CLARK MANAGER ACCOUNTING
KING PRESIDENT ACCOUNTING
MILLER CLERK ACCOUNTING
1.2默认情况下，在执行连接查询时如果没有指定任何连接操作符，那么这些连接查询多属于内连接。但是只能使用WHERE子句指定连接条件。
例3：不使用任何连接操作符，而使用ON子句指定连接条件
SQL> SELECT A.ENAME,A.JOB,B.DNAME FROM EMP A,DEPT B ON A.DEPTNO=B.DEPTNO AND B.DEPTNO=10;
SELECT A.ENAME,A.JOB,B.DNAME FROM EMP A,DEPT B ON A.DEPTNO=B.DEPTNO AND B.DEPTNO=10
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
例4：不使用任何连接操作符，使用WHERE子句指定连接条件
SQL> SELECT A.ENAME,A.JOB,B.DNAME FROM EMP A,DEPT B WHERE A.DEPTNO=B.DEPTNO AND B.DEPTNO=1
ENAME JOB DNAME
---------- --------- --------------
CLARK MANAGER ACCOUNTING
KING PRESIDENT ACCOUNTING
MILLER CLERK ACCOUNTING
1.3 自然连接：如果主表的主键列和从表的外部键列名称相同，那么还可以使用NATURAL JOIN 自动执行内连接操作
SQL> SELECT A.ENAME,A.JOB,B.DNAME FROM EMP A Natural Join DEPT B;
ENAME JOB DNAME
---------- --------- --------------
SMITH CLERK RESEARCH
ALLEN SALESMAN SALES
WARD SALESMAN SALES
JONES MANAGER RESEARCH
MARTIN SALESMAN SALES
BLAKE MANAGER SALES
CLARK MANAGER ACCOUNTING
SCOTT ANALYST RESEARCH
KING PRESIDENT ACCOUNTING
TURNER SALESMAN SALES
ADAMS CLERK RESEARCH
JAMES CLERK SALES
FORD ANALYST RESEARCH
MILLER CLERK ACCOUNTING
已选择14行。
(四)子查询
子查询是指嵌入在其他SELECT语句中的SELECT语句，也称为嵌套查询。
注：
-
当在DDL语句中引用子查询时，可以带有ORDER BY子句
-
当在WHERE子句、SET子句中引用子查询时，不能带有ORDER BY子句。
子查询可以分为：单行子查询、多行子查询、多列子查询。
4.1单行子查询
单行子查询是指只返回单行单列数据的子查询语句。当在WHERE子句中使用单行子查询时，可以使用单行比较符(>、<、>= 、<= 、=、<>、!=)。
例：
SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME=UPPER('SCOTT'));
ENAME JOB SAL
---------- --------- ---------
FORD ANALYST 3000.00
ADAMS CLERK 1100.00
SCOTT ANALYST 3000.00
JONES MANAGER 2975.00
SMITH CLERK 800.00
4.2多行子查询
多行子查询是指只返回多行单列数据的子查询语句。当在WHERE子句中使用多行子查询时，必须要使用多行比较符(IN 、ALL 、ANY)，它们作用如下：
比较符 作用
IN 匹配于子查询结果的任一个值即可
ALL 必须要符合查询结果的所有值
ANY 只有符合查询结果的任一个值即可
ALL和ANY操作符不能单独使用，而只能与单行比较符(>、<、>= 、<= 、=、<>、!=)结合使用。
例1：在多行子查询中使用IN操作符
SQL> select ename,job,dname from emp inner join dept on emp.deptno=dept.deptno and dept.deptno in (select deptno from dept where deptno<=20);
ENAME JOB DNAME
---------- --------- --------------
SMITH CLERK RESEARCH
JONES MANAGER RESEARCH
CLARK MANAGER ACCOUNTING
SCOTT ANALYST RESEARCH
KING PRESIDENT ACCOUNTING
ADAMS CLERK RESEARCH
FORD ANALYST RESEARCH
MILLER CLERK ACCOUNTING
8 rows selected
例2：在多行子查询中使用ALL操作符
SQL> SELECT A.ENAME,A.JOB,B.DNAME FROM EMP A INNER JOIN DEPT B
2 ON A.DEPTNO=B.DEPTNO
3 AND A.SAL>ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);
ENAME JOB DNAME
---------- --------- --------------
JONES MANAGER RESEARCH
SCOTT ANALYST RESEARCH
KING PRESIDENT ACCOUNTING
FORD ANALYST RESEARCH
例3：在多行子查询中使用ANY操作符
SQL> SELECT A.ENAME,A.JOB,B.DEPTNO,B.DNAME
2 FROM EMP A INNER JOIN DEPT B
3 ON A.DEPTNO=B.DEPTNO
4 AND A.DEPTNO<ANY(SELECT DEPTNO FROM DEPT WHERE DEPTNO<=20);
ENAME JOB DEPTNO DNAME
---------- --------- ------ --------------
CLARK MANAGER 10 ACCOUNTING
KING PRESIDENT 10 ACCOUNTING
MILLER CLERK 10 ACCOUNTING
4.3多列子查询
多列子查询是指返回多列数据的子查询语句。当多列子查询语句返回单行数据时，在WHERE子句中可以使用单行比较符。当多列子查询语句返回多行数据时，在WHERE子句中必须使用多行比较符(IN、ALL、ANY)。
例1：多列子查询返回单行数据
SQL> SELECT ENAME "员工姓名",JOB AS "工作",NVL2(COMM,COMM,0)+SAL AS "薪水",dname as "部门" from emp a,dept b
2 where a.deptno=b.deptno
3 and (a.deptno,a.job)=(select deptno,job from emp where ename=upper('smith'))
4 and a.empno <>all(select empno from emp where ename=upper('smith'));
员工姓名 工作 薪水 部门
---------- --------- ---------- --------------
ADAMS CLERK 1100 RESEARCH
例2：多列子查询返回多行数据
6415
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
