子查询/集合查询/行列转换

最新推荐文章于 2022-07-29 17:38:29 发布

马里奥_{}

最新推荐文章于 2022-07-29 17:38:29 发布

阅读量620

点赞数

分类专栏：笔记文章标签： sql

本文链接：https://blog.csdn.net/ma____n/article/details/112546689

版权

笔记专栏收录该内容

16 篇文章 0 订阅

订阅专栏

子查询and集合查询

一、概述

在查询语句中嵌套查询语句的过程
1）：特点
1.子查询语句为一个独立的查询语句(多数情况下)
2.可以嵌套多个子查询语句
3.可以在多个子句中加子查询语句
示例：

  1.查询和SCOTT同部门的员工信息
   --表： EMP  列：* WHERE条件 :部门和SCOTT相同
    SELECT * FROM EMP WHERE DEPTNO=20; 
  --查询scott的部门
    SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT';
  --一句代码实现  子查询语句
    SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT');

二.各个关键字的分析

结构：

   SELECT COLUMN1,.. .
     FROM TABLE_NAME
    WHERE CONDITION
    GROUP BY COLUMN1,..
   HAVING CONDITION
    ORDER BY COLUMN1 ..;

1.根据数据在表中展现形式（不包含空值）：

 SELECT * FROM EMP;

单行单列：SELECT EMPNO FROM EMP WHERE SAL=800;
单行多列：SELECT EMPNO,DEPTNO FROM EMP WHERE SAL=800;
多行单列：SELECT EMPNO FROM EMP ;
多行多列：SELECT EMPNO,DEPTNO FROM EMP ;

2.SELECT +子查询语句

单行单列

   SELECT (SELECT EMPNO FROM EMP WHERE SAL=800)  FROM EMP;--相当于常量，不等价于常量

单行多列

   SELECT (SELECT EMPNO,DEPTNO FROM EMP WHERE SAL=800)  FROM EMP;--报错 值过多

多行单列

   SELECT (SELECT EMPNO FROM EMP )  FROM EMP;--报错 单行子查询返回多个行

多行多列

   SELECT (SELECT EMPNO,DEPTNO FROM EMP )  FROM EMP;--报错 值过多

对应关系：1对1 1对多多对1 多对多

EMPNO: ENAME=1:1
JOB : EMPNO =1:多
EMPNO : JOB =多：1
HIREDATE : DEPTNO = 多：多

3.FROM + 子查询语句

—单行单列：

SELECT * FROM (SELECT ENAME FROM EMP WHERE SAL=800);---可以执行

—单行多列

SELECT * FROM (SELECT ENAME，HIREDATE FROM EMP WHERE SAL=800);---可以执行

—多行多列

SELECT * FROM (SELECT ENAME，HIREDATE FROM EMP );---可以执行

—多行单列

SELECT * FROM (SELECT ENAME FROM EMP );---可以执行

思考：为什么from四种均可？

源于 FROM +数据源(表名、集合)，所以from四种均可。

4.WHERE +子查询语句

单行单列

SELECT * FROM EMP WHERE EMPNO = (SELECT EMPNO FROM EMP WHERE SAL=800);--可以执行

单行多列

SELECT * FROM EMP WHERE (EMPNO,SAL) = (SELECT EMPNO,SAL FROM EMP WHERE SAL=800 );

多行单列

SELECT * FROM EMP WHERE EMPNO = (SELECT EMPNO FROM EMP ); --子查询返回多个行

SELECT * FROM EMP WHERE EMPNO IN(SELECT EMPNO FROM EMP );
SELECT * FROM EMP WHERE EMPNO =ANY(SELECT EMPNO FROM EMP );

多行多列

SELECT * FROM EMP WHERE (EMPNO,SAL) = (SELECT EMPNO,SAL FROM EMP  )--单行子查询返回多个行 

SELECT * FROM EMP WHERE (EMPNO,SAL)IN(SELECT EMPNO,SAL FROM EMP  );

5.GROUP BY

SELECT DEPTNO FROM EMP GROUP BY (SELECT EMPNO FROM EMP WHERE SAL=800);
--分组后不允许出现子查询表达式

6.HAVING +子查询语句

单行单列

SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING EMPNO =(SELECT EMPNO FROM EMP WHERE SAL=800);

单行多列

SELECT EMPNO, DEPTNO
  FROM EMP
 GROUP BY EMPNO, DEPTNO
HAVING (EMPNO, DEPTNO) = (SELECT EMPNO, DEPTNO FROM EMP WHERE SAL = 800);

多行单列

SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING EMPNO =(SELECT EMPNO FROM EMP)--子查询返回多个行

SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING EMPNO IN(SELECT EMPNO FROM EMP);

多行多列

SELECT EMPNO, DEPTNO
  FROM EMP
 GROUP BY EMPNO, DEPTNO
HAVING(EMPNO, DEPTNO)IN (SELECT EMPNO, DEPTNO FROM EMP) ;

7.ORDER BY +子查询语句

单行单列

SELECT * FROM EMP ORDER BY (SELECT EMPNO FROM EMP WHERE SAL=800);--可以执行 不排序

单行多列

SELECT * FROM EMP ORDER BY (SELECT EMPNO,EANME FROM EMP WHERE SAL=800)--值过多

多行单列

SELECT * FROM EMP ORDER BY (SELECT EMPNO FROM EMP )--单行子查询返回多个行

多行多列

SELECT * FROM EMP ORDER BY (SELECT EMPNO,EANME FROM EMP )--值过多

8.小结

1).SELECT :单行单列
2).FROM : 单行单列单行多列多行单列多行多列
3).WHERE : 单行单列单行多列多行单列多行多列
4).GROUP BY:不允许出现子查询语句
5).HAVING : 单行单列单行多列多行单列多行多列
6).ORDER BY : 单行单列

7).SELECT和ORDER BY一致
8).FROM HAVING WHERE 一致:
WHERE和HAVING由于其后添加条件，总有一个条件使代码执行所以均可

三、相关/非相关子查询

IN +子查询：符合子查询语句中结果，即返回查询结果

EXISTS +子查询：当子查询语句有结果返回时，执行主查询语句

NOT EXISTS +子查询：当子查询语句无结果返回时，执行主查询语句

语法：SELECT COLUMN1,COLUMN2... FROM TABLE_NAME WHERE EXISTS (查询语句);

1.不相关子查询：

      子查询语句的执行不受主查询语句的影响，子查询语句能够独立运行

示例：
–不相关子查询
查询工资大于2000的员工所在部门的员工信息

SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE SAL>2000);

SELECT * FROM EMP WHERE DEPTNO NOT IN(SELECT DEPTNO FROM EMP WHERE SAL>2000);

2.相关子查询：

      子查询语句的执行受主查询语句的影响，子查询语句不能独立运行

–相关子查询

查询工资大于2000的员工所在部门的员工信息且部门编号不等于10

SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE SAL>2000 AND DEPTNO<>10);

SELECT *
  FROM EMP A
 WHERE EXISTS (SELECT DEPTNO
          FROM EMP
         WHERE SAL > 2000
           AND DEPTNO <> 10
           AND A.DEPTNO=DEPTNO);

3.—EXISTS书写过程

1.要明确比较的列
2.把比较的列引入子查询中
3.在子查询中将引入的比较列和子查询中列联系起来
4.将两个条件联系以后，根据EISTS独有的规则判断该条数据符不符合子查询的条件，有无结果返回
决定该条数据是否返回到结果集中

4.–注意：

–1.非相关子查询可以用在任何位置,相关子查询仅用在WHERE或HAVING子句中作为条件，两者可以发生转换。
–2.相关子查询之所以存在，是因为在很多情况下使用相关子查询效率比较高。

四、集合查询

1.定义

集合操作：将不同的数据集合按照一定的规则拼接到一起，组成一个新的、临时的数据集合

2.关键字

操作分类：并集交集差集
关键字：

UNION/UNION ALL INTERSECT MINUS

使用：
SELECT 语句
集合关键字
SELECT 语句;

3.并集

3.1.概述
将不同的数据集合按照并集的规则合并到一个数据集合中

3.2.关键字
UNION / UNION ALL
示例
求工资大于1500 的员工信息与工资小于3000的员工信息的并集

   --UNION
       SELECT * FROM EMP WHERE SAL>1500
       UNION
       SELECT * FROM EMP WHERE SAL<3000;

--UNION ALL
  SELECT * FROM EMP WHERE SAL>1500
  UNION ALL
  SELECT * FROM EMP WHERE SAL<3000;

注意：

1).UNION对于集合中的数据会去重，UNION all不会将重复的数据去除
2).UNION会将数据集合中数据按照第一个列升序排列，UNION ALL不会排序

3.3并集编写时的注意点

1).前后数据集合的列的个数和数据类型应一致
2).UNION 会按照第一个列升序排列
3).最终的数据结果显示以第一个数据集合中的列的名称显示

4.交集

4.1.概述
将不同的数据集合按照交集的规则合并到一个数据集合中
4.2.关键字
INTERSECT
示例：

   1.交集示例
     SELECT * FROM EMP WHERE SAL=800 OR SAL=1600
     INTERSECT
     SELECT * FROM EMP WHERE SAL=1600 OR SAL=3000;
        2.数据类型
    --报错  数据类型不一致
    
     SELECT EMPNO,SAL FROM EMP WHERE SAL=800 OR SAL=1600
     INTERSECT
     SELECT EMPNO,JOB FROM EMP WHERE SAL=1600 OR SAL=3000;
     
     --结果中列的顺序以第一个数据集合的顺序为准
        
     SELECT EMPNO,SAL FROM EMP WHERE SAL=800 OR SAL=1600
     INTERSECT
     SELECT EMPNO,DEPTNO FROM EMP WHERE SAL=1600 OR SAL=3000;
     
   3.列的个数
     --列的个数不一致
     SELECT EMPNO,SAL FROM EMP WHERE SAL=800 OR SAL=1600
     INTERSECT
     SELECT EMPNO,SAL,DEPTNO FROM EMP WHERE SAL=1600 OR SAL=3000;
   
   4.排序
     --按照集合中的第一个列升序排列
     SELECT EMPNO,SAL FROM EMP WHERE SAL>1500
     INTERSECT
     SELECT EMPNO,SAL FROM EMP WHERE SAL<3000; 
    
     SELECT SAL,EMPNO FROM EMP WHERE SAL>1500
     INTERSECT
     SELECT SAL,EMPNO FROM EMP WHERE SAL<3000;

5.差集

5.1.概述
将不同的数据集合按照差集的规则合并到一个数据集合中

5.2.关键字

MINUS
示例：

1.差集示例
    --前后集合的顺序不同，集合的结果集也不同
    
     SELECT * FROM EMP WHERE SAL=800 OR SAL=1600
     MINUS
     SELECT * FROM EMP WHERE SAL=1600 OR SAL=3000;
     
     SELECT * FROM EMP WHERE SAL=3000 OR SAL=1600
     MINUS
     SELECT * FROM EMP WHERE SAL=1600 OR SAL=800;
     
   2.数据类型
    --报错  数据类型不一致
    
     SELECT EMPNO,SAL FROM EMP WHERE SAL=800 OR SAL=1600
     MINUS
     SELECT EMPNO,JOB FROM EMP WHERE SAL=1600 OR SAL=3000;
     
     --结果中列的顺序以第一个数据集合的顺序为准
        
     SELECT EMPNO,SAL FROM EMP WHERE SAL=800 OR SAL=1600
     MINUS
     SELECT EMPNO,DEPTNO FROM EMP WHERE SAL=1600 OR SAL=3000;
     
   3.列的个数
     --列的个数不一致
     SELECT EMPNO,SAL FROM EMP WHERE SAL=800 OR SAL=1600
     MINUS
     SELECT EMPNO,SAL,DEPTNO FROM EMP WHERE SAL=1600 OR SAL=3000;
   
   4.排序
     --按照集合中的第一个列升序排列
     SELECT EMPNO,SAL FROM EMP WHERE SAL>1500
     MINUS
     SELECT EMPNO,SAL FROM EMP WHERE SAL<2000; 
    
     SELECT SAL,EMPNO FROM EMP WHERE SAL>1500
     MINUS
     SELECT SAL,EMPNO FROM EMP WHERE SAL<2000;

五行转列/列转行

SCORE_63表
在这里插入图片描述
SCORE_63表转化成SCORE_63_BAK表

SELECT ENAME  姓名,	
					SUM(CASE COURSE WHEN '语文' THEN SCORE END)  语文,
					SUM(CASE  COURSE WHEN '数学' THEN SCORE  END)  数学,
					SUM(CASE COURSE WHEN '英语' THEN SCORE END)   英语
FROM  SCORE_63  GROUP BY ENAME

SCORE_63_BAK表转换成SCORE_63表

SELECT 姓名 ENAME,'数学' COURSE,数学 SCORE FROM SCORE_63_BAK
UNION  
SELECT 姓名 ENAME,'语文' COURSE,语文 SCORE FROM SCORE_63_BAK
UNION 
SELECT 姓名 ENAME,'英语' COURSE,英语 SCORE FROM SCORE_63_BAK

马里奥_{}

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
子查询/集合查询/行列转换

子查询and集合查询一、概述在查询语句中嵌套查询语句的过程1）：特点1.子查询语句为一个独立的查询语句(多数情况下)2.可以嵌套多个子查询语句3.可以在多个子句中加子查询语句示例： 1.查询和SCOTT同部门的员工信息 --表： EMP 列：* WHERE条件 :部门和SCOTT相同 SELECT * FROM EMP WHERE DEPTNO=20; --查询scott的部门 SELECT DEPTNO FROM EMP WHERE ENAME ='SCOT
复制链接

扫一扫