ORACLE数据库-查询语句SELECT

---------查询语句
--完整语法描述：
--SELECT [ALL|DISTINCT] TOP n[PERCENT] WITH TIES select_list
--[INTO[new table name]]
--[FROM{table_name|view_name}[(optimizer_hints)]
--[,{table_name2|view_name2}[(opyimizer_hints)]
--[...,table_name16|view_name16][(optimizer hints)]]]
--[WHERE clause]
--[GROUP BY clause]
--[HAVING clause]
--[ORDER BY clause]
--[COMPUTE clause]
--[FOR BROWSE]
----------简单查询（仅含有SELECT和FROM 子句的查询是简单查询）
--显示表中的所有行和列
SELECT * FROM EMP;
--1.使用FROM 子句指定表
SELECT * FROM EMP,DEPT;--FROM后接多个表，按笛卡尔积来显示
--2.使用SELECT指定列
SELECT EMPNO,ENAME FROM EMP;--可以通过改变列的顺序来显示查询结果，甚至可以多次指定同一列
SELECT EMPNO,LOC FROM EMP,DEPT;--这生成的也是笛卡尔积，只不过只选了两列
--3.算数表达式
--SELECT语句中可以包括（加减乘除和括号的算术运算符）
SELECT EMPNO,ENAME,SAL*(1+0.1) FROM EMP;--给员工加薪百分之十
SELECT EMPNO "编号" FROM EMP;
SELECT EMPNO 编号 FROM EMP;
SELECT EMPNO AS 编号 FROM EMP;--都可以给表里的列添加上标题，不过最好不用汉语名字，如果列标题里含有一些特殊符号如空格，则指定的列标题必须用双引号括起来；
SELECT EMPNO AS A A FROM EMP;--ORA-00923: 未找到要求的 FROM 关键字,因为列标题中加了空格；
SELECT EMPNO AS "A A" FROM EMP;--加上双引号这样就可以了
--4.DISTINCT关键字
--如考勤记录表中仅显示考勤人员而不显示时间时，人员的名字会有很大的重复
--这时需要DISTINCT关键字来去除结果集中重复的行
SELECT DISTINCT DEPTNO FROM EMP;--显示了EMP表中有员工的部门号
SELECT DEPTNO FROM EMP;--返回了EMP表中该列的所有值
--------WHERE子句
--WHERE子句用于筛选从FROM子句中返回的值，完成的是选择操作；
--WHERE将对FROM子句指定的数据表进行判断，只有满足WHERE子句判断条件的行才会显示，不满足WHERE条件的不包括在结果集中；
--WHERE子句位于FROM之后
SELECT ENAME FROM EMP WHERE SAL>1500;
--1.条件表达式
--A=B,A>B,A<B,A!=B 或A<>B,A LIKE B ，其中LIKE 是匹配运算符，"%" 代表0,1,或多个任意字符，"_"代表一个任意字符；
--NOT <条件表达式> 它用于对结果取反；
如查询EMP表中名字里有"S"的员工姓名；
SELECT ENAME FROM EMP WHERE ENAME LIKE'%S%';
--2.连接运算符
--AND 在其左右的条件都为TRUE时，AND 运算符才返回TRUE;OR 在其左右的条件有一个为TRUE 就返回TRUE值；
SELECT * FROM EMP WHERE SAL>2000 AND DEPTNO=20;
SELECT * FROM EMP WHERE SAL>2000 OR DEPTNO=20;
SELECT * FROM EMP WHERE DEPTNO=10 OR DEPTNO=30;--ORACLE 有优先级判断顺序的，可以通过加括号将表达式括起来，增加可读性；
--3.NULL值
--用来描述记录中没有定义内容的字段值，判断某个条件时，返回值可能为TRUE,FALSE,UNKNOWN;
SELECT * FROM EMP WHERE COMM IS NULL;
如下面的例子:
CREATE TABLE DEPARTMENTS(DEPARTMENT_ID VARCHAR2(10),DEPARTMENT_NAME VARCHAR2(10),MANAGER_ID VARCHAR(10));
INSERT INTO DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID) VALUES(30，'数据库',NULL);
SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID FROM DEPARTMENTS WHERE MANAGER_ID = NULL;--没有结果，证明不能使用"=" ;
SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID FROM DEPARTMENTS WHERE MANAGER_ID IS NULL;--这个表达才对，用"IS NULL" 和"IS NOT NULL";
--ORDER BY 子句,对检测到的结果进行排序
--SELECT 列名 FROM  表名 ORDER BY [(要排序的列，ASC,DESC)];
ASC为默认排序，且它为升序排列；DESC为降序排列；
如：SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>2000 ORDER BY SAL DESC;
如果需要对多个列进行排序，只需要在 ORDER BY 子句后指定多个列名。这样当输出排列结果时，首先根据第一列进行排序，
当第一列的值相同时，再对第二列进行比较排序。比如，先对job排序，然后再排sal这样就可以使雇员的薪资分工种显示；
select * from emp for update;--更新表数据
--GROUP BY 子句
--在查询结果集中对记录进行分组，以汇总数据或者分组显示单行的汇总信息；
--使用GROUP BY 子句和统计函数，可以实现对查询结果中每一组数据进行分类统计
常用统计函数：COUNT()---返回找到的记录行数；
MIN()---返回一个数字列或是计算列的最小值；
MAX()---最大值；
SUM()---计算总和；
AVG()---平均值；
在使用 group by 子句时，必须满足下面的条件:
1.在SELECT子句的后面只可以有两类表达式，统计函数和进行分组的列名；
2.在SELECT子句中的列名必须是进行分组的列，除此之外添加其他的列都是错误的；
  但是 group by 子句后面的列名可以不出现在SELEECT子句中；
3.如果使用了 WHERE 子句，那么所有参加分组计算的数据必须首先满足 WHERE 子句指定的条件；
4.在默认情况下，将 group by 子句指定的分组列升序排列，如需重新排序，可以使用 ORDER BY 子句指定新的排列顺序；
SELECT 子句中包含了未参与分组的列，会报错：
SELECT JOB,SUM(JOB),DEPTNO FROM EMP WHERE SAL>2000 GROUP BY DEPTNO;
--ORA-00979: 不是 GROUP BY 表达式
--GROUP BY 子句对多个列进行分组，在这种情况下，GROUP BY 子句将在主分组范围内进行二次分组；
如：对各个部门中的各个工种类型进行分组；
SELECT DEPTNO,JOB,AVG(SAL),SUM(SAL),COUNT(*) FROM EMP GROUP BY DEPTNO,JOB;
在 group by 子句中还可以加上 ROLLUP 和 CUBE，这两个运算符在功能上非常类似，
group by 子句中使用它们后，都会在查询结果中附加一行汇总信息；
如：SELECT JOB,AVG(SAL),SUM(SAL),MAX(SAL),COUNT(*) FROM EMP GROUP BY ROLLUP(JOB);--最后一行多了个汇总行
SELECT JOB,AVG(SAL),SUM(SAL),MAX(SAL),COUNT(*) FROM EMP GROUP BY CUBE(JOB);--第一行多了个汇总行
--5.HAVING 子句
--HAVING 子句通常与GROUP BY 子句一起使用，在完成对分组结果统计后，可以使用HAVING 子句对分组结果做进一步的筛选；
--如果不用GROUP BY 子句则HAVING 与WHERE 子句一样；
--HAVING 与WHERE 子句都是定义搜索条件的，但是HAVING 子句与组相关，WHERE 子句与单个行相关；
--如果 SELECT 语句中使用了GROUP BY 子句， HAVING 将用于GROUP BY 子句创建的那些组。
--如果指定了WHERE 子句，而没有指定GROUP BY 子句，那么HAVING 子句将用于WHERE 子句的输出，并且整个输出被看成是一个组；
--如果SELECT 语句既没有指定GROUP BY 也没有指定WHERE 则HAVING 子句将应用于FROM 子句的输出，将其看成是一个组；
--SELECT 语句中子句的处理顺序 --FROM --WHERE --GROUP BY --HAVING --SELECT --ORDER BY;
如：列出平均薪资大于10000的统计信息
SELECT JOB,AVG(SAL),SUM(SAL),MAX(SAL),COUNT(*)
FROM EMP GROUP BY JOB HAVING AVG(SAL)>10000;
-----------------多表连接查询
--1.简单连接
--基本形式：仅仅通过SELECT 子句和FROM 子句来连接多个表，其查询结果是一个通过笛卡尔积所生成的表；
笛卡尔积所生成的表，就是由一个基表中每一行与另一个基表的每一行连接在一起所生成的表，其查询结果的行数是两个基表行数的积。
如：SELECT EMPNO,ENAME,DNAME FROM EMP,DEPT;
--条件限定：笛卡尔积产生大量冗余信息，所以用WHERE 子句提供连接条件，可以有效避免笛卡尔积的出现；
--WHERE 子句限定时，只有第一个表中的列与第二个表中的相应的列相互匹配后才会在结果集中显示，这是连接查询中最常用的形式。
SELECT EMPNO,ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;--所要显示的列名不相同时可以不用显示其所在表；
SELECT DEPTNO ,ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;--ORA-00918: 未明确定义列
SELECT EMPNO,ENAME,DNAME FROM EMP ,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.DNAME='SALES';--可以限定多个条件；
SELECT * FROM TAB;--查询当前用户所有的表；
SELECT * FROM DEPT;
--表别名
SELECT D.DEPTNO,D.DNAME,E.EMPNO,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;--给表起别名来方便明确定义其中的列；
注意：如果表指定了别名，那么语句中的所有子句都必须使用别名，而不允许再使用实际的表名；
如：
SELECT DEPT.DEPTNO, DEPT.DNAME, E.EMPNO, E.ENAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND D.DNAME = 'SALES'; --ORA-00904: "DEPT"."DNAME": 标识符无效
因为ORACLE各子句执行顺序是FROM,WHERE,SELECT,当 FROM指定了表别名后，表的真实名称将被替换。SELECT用原名选择的列将无法被找到；
--2.JOIN 连接
语法格式：--FROM JOIN_TABLE1 JOIN_TYPE JOIN_TABLE2
           --[ON(JOIN_CONDITION)]
--JOIN_TYPE 为连接类型，包括内连接，外链接和自连接。连接查询中的ON(JOIN_CONDITION)指出连接条件，它由
--被连接表的列和比较运算符、逻辑运算符等构成。
（1）内连接(INNER JOIN)
一般用关键字 INNER JOIN，可以省略INNER关键字，而只使用JOIN关键字表示内连接。
内连接使用比较运算符时，在连接的某些列之间进行比较操作，并列出表中与连接条件相匹配的数据行；
如：
SELECT E.EMPNO,E.ENAME,D.DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE E.JOB='CLERK';
内连接实现两个以上表的查询：
SELECT E.EMPNO,E.ENAME,D.DNAME,J.JOBNAME
FROM EMP E INNER JOIN JOBS J
  ON E.JOBID=J.JOBID
  INNER JOIN DEPT D
  ON E.DEPTNO=D.DEPTNO
  WHERE E.JOBID='IT_PROG';--只是一个例子哦；
（2）自然连接（NATURAL JOIN）
自然连接与内连接的功能相似，在使用自然连接查询多个表时，ORACLE会将第一个表中的那些列
与第二个表中具有相同名称的列进行连接。在自然连接中，用户不需要明确指定进行连接的列，系统
会自动完成这一任务。--内连接有一个条件就是连接的各表之间必须具有相同名称的列。（但与现实意义脱轨，两张表的字段名也许相同但所包含的意思却是会不同的）
自然连接的规律：存在相同的列，列中数据不一致，则连接数据一致的记录；
                 显示相同列所对应表的其他字段信息；
                 存在多个相同的列，对应的行的相关列的数据必须完全一致；
                 NULL<>NULL 空值不等于空值。  
SELECT E.ENAME,D.DNAME
FROM EMP E NATURAL JOIN DEPT D
WHERE D.DNAME='SALES';
（3）外连接(LEFT/RIGHT/FULL JOIN)
内连接消除了与另一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除返回所有匹配
的行外，还会返回一部分或全部不匹配的行，这主要取决于外连接的种类。
外连接分为左连接和右连接——LEFT JOIN(或 LEFT OUTER JOIN)和 RIGHT JOIN(或 RIGHT OUTER JOIN) 。
全外连接——FULL JOIN 。
SELECT E.EMPNO,E.ENAME,D.DNAME,D.DEPTNO 
FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
(左外连接以EMP表为主-没有40部门号)
SELECT E.EMPNO,E.ENAME,D.DNAME,D.DEPTNO 
FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
(右外连接以DEPT表为主-有40部门)
完全外连接相当于同时执行了一个左外连接和一个右外连接； 
(4)自连接
有时用户可能会拥有自引用式外键。自引用式外键意味着表中的一个列可以是该表主键的一个外键。
如EMP 表的EMP.MGR列可以是另一行的EMP.EMPNO；
语句如下：
SELECT E.EMPNO,E.ENAME,M.ENAME
FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO;
再如：
SELECT E.ENAME,M.ENAME,D.DNAME,D.DEPTNO
FROM EMP E,EMP M,DEPT D
WHERE E.MGR=M.EMPNO AND E.DEPTNO=D.DEPTNO;
(5)交叉连接（CROSS JOIN）——相当于产生笛卡尔积
SELECT * FROM EMP CROSS JOIN DEPT;
(6)(+)来进行左右外连接——一般不用这个
D.DEPTNO=E.DEPTNO(+)--则以DEPT 的 DEPTNO 为基准
D.DEPTNO(+)=E.DEPTNO--则以EMP 的 DEPTNO 为基准
(+)写左边为左连接，写右边为右连接；
SELECT E.ENAME,E.EMPNO,D.DEPTNO
FROM EMP E,DEPT D
WHERE D.DEPTNO=E.DEPTNO(+);--以DEPT 为基准


SELECT E.ENAME,E.EMPNO,D.DEPTNO
FROM EMP E,DEPT D
WHERE D.DEPTNO(+)=E.DEPTNO;--以EMP 为基准
-------------------查询的集合操作
--集合操作就是将两个或多个SQL 查询结果合并构成复合查询，以完成一些特殊的任务需求；
1. UNION--将多个查询结果相加，形成一个结果集。
       --UNION 运算会将集合中的重复记录虑除。
SELECT * FROM DEPT
UNION 
SELECT 50,'教学','北京' FROM DUAL;


SELECT 50 AS DEPTNO,'教学' AS DNAME,'北京' AS LOC FROM DEPT 
UNION 
SELECT * FROM DEPT;


SELECT 50,'教学','北京' FROM DEPT/DUAL
UNION
SELECT * FROM DEPT;--这个就不对了，表头也变了；


SELECT DEPTNO AS 部门号,DNAME AS 部门名称,LOC AS 地址 FROM DEPT;
SELECT DEPTNO AS "部门号",DNAME AS "部门名称",LOC AS "地址" FROM DEPT;
SELECT '部门号' AS DEPTNO,'部门名称'AS DNAME,'地址'AS LOC FROM DEPT WHERE DEPTNO=40;--这种是给字段列表赋予了一个临时值的感觉
SELECT DEPTNO AS '部门号',DNAME AS '部门名称',LOC AS '地址' FROM DEPT;--给列建立别名只能用双引号或不用
2. UNION ALL--将多个查询结果相加，形成一个结果集。
            --与UNION 不同的是UNION ALL 操作符形成的结果集中包含有两个子结果集重复的行。
3. INTERSECT --也用于对两个SQL 语句所产生的结果集进行处理。
          --不同的是UNION 相当于OR 运算，即并运算；
          --而INTERSECT 相当于AND 运算，即交集运算。           
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE 'C%'
INTERSECT
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE 'B%';
(这个只有以S开头的员工姓名)
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE 'C%'
UNION
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE 'B%';
(这个以S，C，D开头的员工姓名都有)
4. MINUS--它可以找到两个给定的集合之间的差集，即该操作符会返回所有从第一个查询中返回，但是没有在第二个查询中返回的记录。
        --MINUS相当于差集运算。
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE 'C%'
MINUS
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE 'B%';
(这个就只有以C开头的员工姓名了)


----------------ORACLE 查询的子查询
  子查询和连接查询一样，都提供了使用单个查询访问多个表中数据的方法，
子查询提供了一种进一步的有效方法来表示WHERE子句中的条件。子查询是一个SELECT语句，
它可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中使用。
SELECT E.* FROM (SELECT * FROM EMP WHERE EMPNO=7654) M,EMP E WHERE M.SAL<E.SAL;
SELECT * FROM EMP WHERE SAL>(SELECT SAL,COMM FROM EMP WHERE EMPNO=7654);--ORA-00913: 值过多
SELECT * FROM EMP WHERE SAL>(SELECT HIREDATE FROM EMP WHERE EMPNO=7654);--ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 DATE
SELECT * FROM EMP WHERE SAL>(SELECT COMM FROM EMP WHERE EMPNO=7654);--看来只要数据一致就会有结果的，数据类型一致可以进行比较。
SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE EMPNO=7654);
1. IN 关键字
-- 可以将原表中特定列的值与子查询返回的结果集中的值进行比较，如果莫航的特定列的值存在，则
--在SELECT 语句的查询结果中就包含这一行。
SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DEPTNO=20); 
注意：在使用子查询时，子查询返回的结果必须和外层引用列的值在逻辑上具有可比较性。
2. ALL 关键字
  =ALL 查询等于子查询中所有值的值；
  >ALL 大于子查询中的所有值，即大于最大值；
  <ALL 小于子查询中的所有值，即小于最小值；
3. ANY 关键字
  =ANY 与 IN 关键字的效果一致；
  >ANY 大于子查询中的任何一个值即可，即大于最小值；
  <ALL 小于子查询中的任何一个值即可，即小于最大值；
4. EXISTS 关键字
--EXISTS 关键字只注重子查询是否返回行，如果子查询返回一个或多个行，那么EXISTS 便返回TRUE ，否则返回FALSE 。
--要使EXISTS 关键字有意义，则应在子查询中建立搜索条件。
SELECT EMPNO, ENAME
  FROM EMP E
 WHERE EXISTS (SELECT *
          FROM DEPT D
         WHERE E.DEPTNO = D.DEPTNO
           AND DEPTNO = 20);
在该语句中外层的SELECT语句返回的每一行数据都要由子查询来评估。
如果EXISTS关键字里的条件为真则查询结果中就包含这一行，否则该行被丢弃。因此整个的查询结果取决于内层的子查询。
小提示：EXISTS关键字的返回值取决于查询是否会返回行，而不是行的内容，所以子查询中，输出列表可以用‘*’代替。
5. 比较运算符
‘=’‘<’‘>’‘<=’‘>=’‘<>’‘!=’
注意：使用比较运算符必须保证子查询的返回结果只包含一个值。