Oracle子查询

01 返回单行的子查询

SELECT STU.*
FROM STU
WHERE AGE = (SELECT AGE FROM STU WHERE NAME = '林琳');

SELECT STU.*
FROM STU
WHERE AGE >= (SELECT AVG(AGE) FROM STU);

SELECT *
FROM STU
WHERE NO = (SELECT NO FROM GRADE WHERE SCORE = 85);
--OR
SELECT S.*
FROM STU S INNER JOIN GRADE G
ON S.NO = G.NO
WHERE G.SCORE = 85;
--使用单行子查询时，一定要确认子查询只能返回单个值
--否则使用>,<,=,>=,<=,<>运算符将会报错

02 用IN实现多行子查询

SELECT STU.*
FROM STU
WHERE DEPT IN(SELECT DEPT FROM STU WHERE NAME = '陈诚');

SELECT STU.*
FROM STU
WHERE NO NOT IN(SELECT NO FROM GRADE WHERE SCORE < 90)
AND NO IN(SELECT NO FROM GRADE);
--OR
SELECT STU.*, GRADE.*
FROM STU INNER JOIN GRADE
ON STU.NO = GRADE.NO
WHERE GRADE.SCORE > 90;
--如果能用子查询一般用子查询取代连接查询，子查询可读性更强效率更高

03 EXISTS子查询

SELECT STU.*
FROM STU
WHERE EXISTS(SELECT 1 FROM GRADE WHERE NO = STU.NO 
                AND NAME = '计算机基础');
--EXISTS判断子查询返回值，如果为空则返回false，否则返回true
--执行流程：
--在stu中依次取出每条记录的no值，用改制去检查表grade的where条件
--如果grade表中存在no=stu.no并且name='计算机基础'的记录，
--EXISTS返回true并取此记录的no对应的stu的记录
--依次循环，一直到stu表中所有记录比较完成

SELECT STU.*
FROM STU
WHERE NOT EXISTS(SELECT 1 FROM GRADE WHERE NO = STU.NO 
                AND NAME = '计算机基础');

04 EXISTS代替单行子查询

SELECT S1.*
FROM STU S1
WHERE EXISTS(SELECT 1 FROM STU S2
        WHERE S1.AGE = S2.AGE
        AND S2.NAME = '林琳');
--OR
SELECT STU.*
FROM STU
WHERE AGE = (SELECT AGE FROM STU WHERE NAME = '林琳');

05 含聚合函数的相关子查询

SELECT STU.*
FROM STU
WHERE 1 = (SELECT COUNT(*) FROM GRADE WHERE NO = STU.NO);

06 带IN的相关子查询

SELECT STU.*
FROM STU
WHERE '计算机基础' IN (SELECT NAME FROM GRADE WHERE NO = STU.NO);

07 包含分组的相关子查询

SELECT DEPT, AVG(AGE)
FROM STU S1
GROUP BY S1.DEPT
HAVING AVG(AGE) < ANY(SELECT AGE
                        FROM STU S2
                        WHERE S1.DEPT = S2.DEPT);
--ANY表示任意个，小于任意一个即可符合条件
--分组年龄平均值小于分组内任意个年龄即符合条件         
--相关子查询中子查询的查询条件依赖与外层父查询的某个列值并依次执行
--执行效率一般低于连接查询，但子查询的性能完全依赖于查询和有关数据。    

08 带ALL的子查询

SELECT STU.*
FROM STU
WHERE DEPT <>'12计算机'
AND AGE < ALL(SELECT AGE FROM STU WHERE DEPT = '12计算机');    
-- ALL表示所有值比较并都满足条件才为true；ANY 表示任意值满足即条件成立 

09 嵌套子查询

SELECT STU.*
FROM STU
WHERE NO IN(SELECT NO FROM GRADE
            WHERE NAME IN (SELECT NAME FROM COURSE
                            WHERE SCORE = 4))
      AND DEPT = '12计算机'
ORDER BY AGE DESC;
--ORACLE支持的嵌套层次最多为255，应尽量多层嵌套，使用表连接查询性能可能会更高

10 FROM子句后的子查询

SELECT *
FROM(SELECT * FROM STU WHERE AGE >22);
--FROM子查询经常在做临时表中使用

11 SELECT子句后的子查询

SELECT STU.*,(SELECT SYSDATE FROM DUAL)
FROM STU;
--SELECT子句中使用子查询时一定要保证是单行返回值子查询才能成功执行

12 HAVING子句后的子查询

SELECT DEPT ,COUNT(1)
FROM STU
GROUP BY DEPT
HAVING DEPT IN
(SELECT DEPT FROM STU WHERE AGE>22)

13 子查询返回空值

SELECT STU.*
FROM STU
WHERE DEPT = (SELECT DEPT
                FROM STU
                WHERE AGE < 20)
ORDER BY NO
--子查询查询结果集为null，将导致父查寻结果集也为NULL

ALTER TABLE WEICK.STU
 DROP PRIMARY KEY CASCADE;

DROP TABLE WEICK.STU CASCADE CONSTRAINTS;

CREATE TABLE WEICK.STU
(
  NO      VARCHAR2(10 BYTE),
  NAME    VARCHAR2(10 BYTE),
  GENTLE  VARCHAR2(2 BYTE),
  AGE     NUMBER(2),
  DEPT    VARCHAR2(20 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;


CREATE INDEX WEICK.INDEX_NAME_RE ON WEICK.STU
(NAME)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX WEICK.INDEX_NO_DEPT ON WEICK.STU
(NO, DEPT)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

ALTER TABLE WEICK.STU ADD (
  PRIMARY KEY
  (NO)
  USING INDEX WEICK.INDEX_NO_DEPT
  ENABLE VALIDATE);

SET DEFINE OFF;
Insert into WEICK.STU
   (NO, NAME, GENTLE, AGE, DEPT)
 Values
   ('120006', '李飒', '男', 12, '12工商管理');
Insert into WEICK.STU
   (NO, NAME, GENTLE, AGE, DEPT)
 Values
   ('120005', '林琳', '女', 22, '12计算机');
Insert into WEICK.STU
   (NO, NAME, GENTLE, AGE, DEPT)
 Values
   ('120004', '杨过', '男', 22, '12计算机');
Insert into WEICK.STU
   (NO, NAME, GENTLE, AGE, DEPT)
 Values
   ('120003', '张清', '女', 21, '12外语');
Insert into WEICK.STU
   (NO, NAME, GENTLE, AGE, DEPT)
 Values
   ('120001', '陈诚', '男', 23, '12计算机');
Insert into WEICK.STU
   (NO, NAME, GENTLE, AGE, DEPT)
 Values
   ('120002', '李宗赫', '男', 25, '12图形');
COMMIT;