Oracle-PL/SQL学习~~~从增删改查学起~~~查询语句

一、SQL SELECT 语法简介

    PL/SQL 的系列文章是学习丁世峰前辈的<<Oracle PL/SQL 从入门到精通>>这本书，各位同学可购买本书自己查看。

SELECT {  [alias.]column | expression | [alias.]* [,...] }
FROM [schema.]table [alias] ;

最简单的一个查询sql：

SELECT * FROM DUAL;

DUAL是Oracle的一个虚拟表，里面只有一条空的数据，我们可以根据需求来使用，比如计算或者验证语法：

SELECT 1+1 T FROM DUAL;
SELECT TO_DATE('2020-11-18','YYYY-MM-DD') T FROM DUAL;
SELECT TO_DATE('2020-11-18','DD') T FROM DUAL;

上列语句中的T为别名，将自己查到的字段起一个自己需要的名称。如果别名中有空格，特殊字符或者大小写敏感字符，要使用双引号。

查询语句中可以加上运算符，如+ - * / :

SELECT (1+1)-10*2/5 T FROM DUAL;

SQL语句中的括号可以改变运算顺序。

使用DISTINCT关键字可以获取字段唯一值，但是DISTINCT会使索引失效，在程序中尽量不要使用。我在日常查询时使用此关键字去重。

SELECT DISTINCT JOB FROM EMP;

使用 || 符号可以连接一个或多个值,使之成为单个字段

SELECT ename || '的薪资是：' || sal 员工薪水 FROM emp ;

可以使用 WHERE 子句进行条件查询，过滤掉不需要的语句。对于数字类型，可以直接输入条件值，对于日期和字符串，需要使用单引号括住条件值。单引号中的字符串是区分大小写的。

SELECT * FROM emp WHERE sal >= 1000;
SELECT * FROM emp WHERE name = '张三' AND job = 'Clerk' ;

BETWEEN-AND操作符：要比较的值是否在两个值之间。使用时注意左右两端是闭区间， 即>=  <= ，按照业务考虑是否使用。

IN操作符：要比较的值是否在任意的值列表中间。

LIKE操作符：通过使用通配符来匹配一个字符模板。

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2500;
SELECT * FROM emp WHERE job IN ('销售'，'技术') ;
SELECT * FROM emp WHERE name LIKE '%张%' ;
SELECT * FROM emp WHERE name LIKE '_三%' ;

用 IS NULL 或 IS NUT NULL 来判断NULL值。=

SELECT * FROM emp WHERE mgr IS NULL;
SELECT * FROM emp WHERE mgr IS NOT NULL ;

在WHERE子句中可以使用逻辑运算符：

AND： 如果两个组成部分的条件为真，则返回True。

OR：如果两个组成部分中的任何一个条件为真，则返回True。

NOT：如果跟随的条件为假，则返回True。

SELECT * FROM emp WHERE deptno = 20 AND hiredate LIKE '%82' ;
SELECT * FROM emp WHERE deptno = 20 OR hiredate LIKE '%82' ;
SELECT * FROM emp WHERE NOT (deptno = 20 AND hiredate LIKE '%82') ;

WHERE子句中运算符优先级

计算顺序	运算符
1	算术运算符，例如+、-、*、/运算符
2	连接运算符，例如||运算符
3	比较运算符，例如>、<、>= 、<=、<>运算符
4	IS[NOT] NULL 、LIKE、[NOT] IN
5	[NOT] BETWEEN
6	NOT 逻辑条件
7	AND 逻辑条件
8	OR 逻辑条件

可以使用 ORDER BY 子句进行排序。ORDER BY 子句必须是SELECT语句的最后一个子句，否自SELECT语句将会执行失败。

可选择 ASC 和 DESC 表示排序的方向。默认是 ASC， 表示升序排序。DESC指定降序排序。

SELECT * FROM emp WHERE depno = 20 ORDER BY empno;

使用 GROUP BY 子句，配合统计函数，可以实现分组统计查询。

SELECT SUM(sal) t FROM emp  GROUP BY deptno ;

使用 HACING 子句可以对分组的结果进行进一步的过滤。

SELECT deptno, job,c SUM(sal) FROM emp GROUP BY deptno, job HAVING SUM(sal) > 2000;

ROWNUM 伪列是Oracle先查到结果集之后再加上去的一个伪列，这个伪列对符合条件的结果添加一个从1开始的序列号。

SELECT ROWNUM, empno,ename FROM emp WHERE deptno = 20;
SELECT ROWNUM, empno,ename FROM emp WHERE ROWNUM <= 10 ;

ROWID是一种数据类型，它使用基于64位编码的18个字符来唯一标识一条记录的物理位置的一个ID。有点类似于主键。不过与主键的本质区别是ROWID一般情况下是按照递增的顺序排列的。

默认情况下，索引会按照ROWID的顺序显示，因此当对两条完全相同的记录进行排序时，结果会按照ROWID的顺序进行排序。

SELECT ROWIDTOCHAR(ROWID), ename, empno from emp where ROWNUM <= 5 ;

ROWID的另一个重要的作用是删除完全重复的两条记录。

DELETE FROM emp WHERE ROWID NO IN (SELECT MIN(ROWID) 
                                    FROM emp 
                                    GROUP BY empno) ;

二、多表连接查询

内连接

SELECT emp.empno, emp.ename, emp.job, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno ;

外连接

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(+);   --左外连接

右外连接： 当(+)符号出现在等号的左边时，将返回table2表中所有的数据。

左外连接： 当(+)符号出现在等号的右边时，将返回table1表中所有的数据。

SELECT a.column, b.column
FROM table1 a
RIGHT JOIN table2 b ON a.column = b.column;   --右外连接

SELECT a.column, b.column
FROM table1 a
LEFT JOIN table2 b ON a.column = b.column;   --左外连接

在查询时我也可以使用子查询来查询数据

SELECT * FROM emp WHERE sal > (
                                SELECT sal 
                                    FROM emp 
                                WHERE ename = 'SMITH'
                               );

子查询分为两类：

相关子查询：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。

非相关子查询：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

非相关子查询根据返回的结果可以细分为如下的3类：

单行单列子查询：又称为标量子查询，通常与比较运算符比如=、>、<、!=、<=、>=联合使用。

多行单列资产寻：返回单列多行数据时，使用特定的关键字如ANY和ALL来将外层查询的单个值与子查询的多行进行比较。

多列子查询：返回多列数据的子查询，通常用在UPDATE语句中。

比较操作符ANY的含义如下：

<ANY 意思是小于最大值

>ANY 意思是大于最小值

=ANY 等同于IN

<ALL 意思是小于最小值

>ALL 意思是大于最大值

如下语句为相关子查询，每当主查询或外查询执行一次时，会执行一次相关子查询。

SELECT e1.empno, e1.ename, e1.deptno
FROM emp e1
WHERE e1.sal > (SELECT AVG(sal) 
                    FROM EMP e2 
                WHERE e2.deptno = e1.deptno );

表集合操作符：

联合运算：从两个查询返回的结果集去掉重复值后合并后的结果，使用UNION操作符。

全联合运算：与联合运算相似，返回两个查询结果的并集，但是包括所有的重复值，使用UNION ALL操作符。

相交运算：返回多个查询结果中的相同的行，使用INTERSECT操作符。

相减运算：返回在第一个查询中存在而在第二个查询中不存在的行，使用MINUS操作符。

其中ORDER BY 子句只能放在最后一个SELECT语句中，放在任何其他的位置都会导致语句错误。

层次化查询：

ORACLE提供了如下3个构造来有效和有用的处理层次化查询问题：

START WITH .. CONNECT BY 子句

PRIOR操作符

LEVEL伪列

START WITH .. CONNECT BY 的基本语法：

[[START WITH condition] CONNECT BY condition2]

START WITH condition1 : 指定层次结构中的根节点，所有满足condition1条件的都可以被考虑为根节点。如果没有指定START WITH子句，那么会使得所有的行都被考虑为根节点。这显然不符合

树状查询的规律，也可以再condition1中包含子查询。

CONNECT BY condition2: 指定层次结构中父行和字行之间的关系，这个关系是一个比较表达式，用来从当前的行开始比较其父行的列的相应值。condition2必须包含PRIOR操作符，用于指定列是来自

父行，condition不能包含子查询。

SELECT employee_id, manager_id, first_name
    FROM employee
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id ;

PRIOR后面的列代表父行的列，PRIOR可以位于比较操作符的任何一方，而不在乎是否是在CONNECT BY 后面。

SELECT employee_id, manager_id, first_name
    FROM employee
START WITH manager_id is null
CONNECT BY manager_id = PRIOR employee_id ;

可以使用LEVEL伪列查询树状结构所处的层次。

SELECT LEVEL, employee_id, manager_id, first_name
    FROM employee
START WITH manager_id is null
CONNECT BY manager_id = PRIOR employee_id ;