数据库学习笔记(五)2017.9.22

写在前面:本篇博客大部分内容参考数据库系统概念(本科教学版)第三章(第三章部分的多表操作没有在此处讲,准备挪到第四章再一起讨论)
笔者接下来的代码示例会主要在SQL Server数据库中测试


在开始今天的摸鱼大业之前,让我们先构造一组表,用于演示下面的例子(´`)

BONUS.png
DEPT.png
EMP.png
SALGRADE.png

Having 子句

由于where子句中不能用聚合函数(组函数)做条件,故引入having子句

  • 让我们先看一下各个子句的书写顺序(下面的只是我们一般在写SQL语句时候的书写顺序)
    • SELECT
    • FROM
    • WHERE
    • GROUP BY
    • HAVING
    • ORDER BY
    • 上面各子句的执行顺序:FROM-->WHERE-- > GROUP BY--> HAVING-->SELECT-->ORDER BY

    其中上面除了GROUP BY和HAVING可以互换位置外,其他位置均固定,但最好采用上面的顺序

  • 从上面的讨论中可以看出
    • HAVING一般紧随GROUP BY之后,置于ORDER BY之前
    • 而且当聚集函数(组函数)做条件时,只用用HAVING来处理
  • 举个栗子:求所有平均工资大于2000的部门的部门号以及该部门的平均工资
    • 先来看看每个部门的平均工资
      SELECT DEPTNO, avg(SAL) as avg_sal
      FROM EMP
      GROUP BY DEPTNO
      
      得到如下结果
    • 1.png
    • 接下来开始求题设的问题
      SELECT DEPTNO, avg(SAL) as avg_sal
      FROM EMP
      GROUP BY DEPTNO
      HAVING avg(SAL) > 2000;
      
      得到如下结果
    • 2.png

聚合函数的嵌套

  • 不同数据库对聚集函数嵌套的规定不同
    • Oracle数据库规定最多只能有两层嵌套
    • SQL Server不允许聚集函数嵌套
  • 举个栗子:
    --下面的语句在Oracle数据库中执行是可以的
    SELECT max(avg(sal))
    FROM EMP
    GROUP BY deptno
    
    --上面的语句在SQL Server中执行会报下面的错
    [S0001][130] 不能对包含聚合或子查询的表达式执行聚合函数。
    
  • ┐(´•_•`)┌ 实际上二层以上的嵌套也是毫无意义的(只是瞎哔哔,不是什么重点,有助于理解为什么聚集函数多层嵌套是没有意义的)
    • 首先回忆一下,聚集函数的操作是多行输入,得到一行输出
    • 举个栗子
      SELECT max(max(avg(sal)))
      FROM EMP
      GROUP BY deptno
      
    • 现在来分析上面语句执行的过程
      • (avg)首先根据部门号deptno分成若干组,并计算每组的平均工资(返回若干条记录)
      • (max)然后取各部门最高的平均工资(返回一条记录)
      • (max)这个时候只有一条记录,取最大值也还是那一条记录

子查询

这部分是重点也是难点呀٩(๑`^´๑)۶,不用担心,让笔者带你细细解剖(≖_≖ )
这部分参考了两份笔记和课本

  • 嵌套子查询

    即SQL标准提供嵌套子查询机制,允许将一个完整的select-from-where查询表达式嵌入到另一个查询语句中

  • 子查询嵌套在where和having子句中的情况(把子查询整体当做一组值,可以是0个,1个,或多个)

    • 单行子查询
      • 在单行子查询中,子查询语句只能返回一行数据
      • 当where子句中用下列逻辑符号连接子查询时,便只能连接单行子查询
        • =
        • <
        • >
        • <=
        • >=
        • <>
      • 举个栗子(≖_≖ )
        • 我们现在要找所有员工中,拿到最高工资的人的名字以及工资
        SELECT ENAME, SAL
        FROM EMP
        WHERE SAL = (SELECT max(SAL)
                     FROM EMP);
        
        得到如下结果
      • 3.png
    • 多行子查询
      • 多行子查询中,子查询语句可以返回多行数据
      • 当where子句中用下列关键字连接子查询时,便是多行子查询($代表上面提到的=, >等符号)
        • $ any/some
        • $ all
      • 举几个简单的栗子
        • < any(...) 表示小于子查询结果集中的最大值
        • > any(...) 表示大于子查询结果集中的最小值
        • = any(...) 等价于in
        • = all(...) 没有语法错误,但是只要子查询结果集的个数大于1,则整体的结果必然为空
        • < all(...) 表示小于子查询结果集中的最小值
        • > all(...) 表示大于子查询结果集中的最大值
      • 来举个正经的栗子
        • 比部门号为20的部门的所有员工的工资都要高的员工的名字和工资
        SELECT ENAME, SAL
        FROM EMP
        WHERE SAL > SOME (SELECT SAL
                        FROM EMP
                        WHERE DEPTNO = 10);
        
        得到如下结果
      • 4.png
      • 还有一个要注意的问题(ㅍ_ㅍ),就是子查询中SELECT后面的字段要根据主查询语句中的字段来确定(简单的说就是子查询语句前面,where后面,出现了什么字段,子查询语句的SELECT列表中理论上就应该出现这些字段)
        • 举个栗子
        --下面这个语句是匹配的
        SELECT ENAME, SAL
        FROM EMP
        WHERE SAL > SOME (SELECT SAL
                        FROM EMP
                        WHERE DEPTNO = 10);
        --下面这个语句就不匹配的
        --因为ENAM并没有出现在主查询语句中
        SELECT ENAME, SAL
        FROM EMP
        WHERE SAL > SOME (SELECT ENAME, SAL
                        FROM EMP
                        WHERE DEPTNO = 10);
        
        • 其实换个角度想想,具体执行的时候是将where后面的字段与子查询语句的结果集进行比较,如果字段都不一样,就没有可比性了
      • 对了,还有一个补充,not in + 子查询,如果子查询的结果集中包含NULL值,那整体的结果必为空。因为NULL值是不确定的值,谁也不能保证某个具体的值是否等于NULL。。。
    • 子查询中存在性的判别可以用exists关键字
      • 举个栗子就好
      --下面的语句就查出了所有手底下管了人的经理的信息
      --其实这里已经用到了相关子查询,具体的下面将会讲到
      SELECT *
      FROM EMP as e
      WHERE exists(SELECT 1
                   FROM EMP
                  WHERE EMP.MGR = e.EMPNO);
      
    • 试思考下面两个问题,根据提示理解一下,也可以实际操作验证一下
      • =some 等价于 in, 然而 <>some不等价于not in(<>some表示的是只要不等于结果集中的任意元素即可,可想而知,只要结果集中有大于一个元素,这个条件是恒成立的
      • <>all 等价于 not in,然而=all不等价于in
  • 相关子查询

    • 慎用,因为一旦使用相关子查询,子查询语句可能会被执行很多次,很影响效率
    • 举个栗子
    --下面的语句找出了所有比自己部门平均工资高的员工的信息
    --检索时,每检索一行数据,子查询语句就要被执行一次
    --(因为该子查询语句只有在某个具体的部门号下才会有结果
    --而,要获取部门号,就必须依赖于主查询语句中的某行具体数据)
    SELECT ENAME, SAL, DEPTNO
    FROM EMP e
    WHERE SAL > (SELECT avg(EMP.SAL)
                 FROM EMP
                WHERE e.DEPTNO = EMP.DEPTNO);
    
    • 举个上面举过的栗子
    --下面的语句就查出了所有手底下管了人的经理的信息
    --用exists时,只要找到数据即会返回,不会继续向下检索
    SELECT *
    FROM EMP as e
    WHERE exists(SELECT 1
                 FROM EMP
                WHERE EMP.MGR = e.EMPNO);
    

    由于exists会在找到数据后立即返回,而不是继续向下检索,所以用exists的相关子查询效率会稍微高一丢丢

    • 可以自己验证一下下面结论
      • not exists 不受空值的影响
      • not in 受空值的影响
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 212,332评论 6 493
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,508评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,812评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,607评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,728评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,919评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,071评论 3 410
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,802评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,256评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,389评论 4 332
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,032评论 3 316
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2 350

推荐阅读更多精彩内容

  • 1.简介 数据存储有哪些方式?电子表格,纸质文件,数据库。 那么究竟什么是关系型数据库? 目前对数据库的分类主要是...
    乔震阅读 1,710评论 0 2
  • 5.多表查询 多表查询 目的:从多张表获取数据 前提:进行连接的多张表中有共同的列 等连接 通过两个表具有相同意义...
    乔震阅读 1,216评论 0 0
  • 写在前面:本篇博客大部分内容参考数据库系统概念(本科教学版)第四章(第三章的多表部分会挪到这一部分讲)笔者接下来的...
    SunnyQjm阅读 507评论 0 2
  • 查询是数据的一个重要操作。用户发送查询请求,经编译软件变异成二进制文件供服务器查询,后返回查询结果集给用户,查询会...
    产品小正阅读 1,373评论 0 2
  • 每个人的青春里,都有一些可是告诉又或者不能告诉的秘密,而这些秘密里,总有一部分是关于那些年在你视野里不断出现...
    一森姑娘阅读 292评论 0 0