数据库笔记（四）

写在前面：本篇博客大部分内容参考数据库系统概念（本科教学版）第三章(第三章部分的多表操作没有在此处讲，准备挪到第四章再一起讨论)
下面的例子中的测试都是在MySQL数据库中测试的

---

集合运算

此处集合运算的概念与数学中集合的概念类似，可以借助文氏图加深理解。故此处对并运算进行展开，其他啊两个自己类比，后面还会对集合运算的几个注意点进提醒。

• union(集合并运算)

  • union 默认去除重复，并升序排序（因为涉及到排序，故而会有效率上的劣势）
  • union all 就可保留重复项，并且保留原序（不进行排序）

  • 举个栗子（现有两张表如下）

    Student表 Teacher表

    • 执行以下运算

      SELECT age
      FROM Student
      UNION
      SELECT age
      FROM Teacher

      得到如下结果(得到的结果没有重复元素，但是没有排序，说明MySQL数据库在此处没有对其进行排序输出，但是Oracle数据库是会对数据进行升序排序的)

      

    • 执行以下运算

      SELECT age
      FROM Student
      UNION ALL
      SELECT age
      FROM Teacher

      得到如下结果(可以知道，UNION ALL 保留了重复元素)

      

• intersect(集合交运算)

• except(集合差运算)

• Note:

  • 参与集合运算的两个视图的列数要一致

    • 举个栗子

      -- 下面的语句执行就会出错
      -- 因为第一个视图有两个字段，而第二个视图只有一个字段，无法进行集合运算
      SELECT age, score
      FROM Student
      UNION ALL
      SELECT age
      FROM Teacher

  • 参与集合运算的两个视图对应位置的字段的数据类型应该是一致的（数据类型兼容即可，字段名无需一致）

    • 举个栗子

      -- 按上面的说法下面语句执行是非法的
      -- 因为第二个字段的数据类型不兼容，一个是浮点型，一个是字符串类型
      SELECT age, score
      FROM Student
      UNION ALL
      SELECT age, tech_name
      FROM Teacher

      上面的书法在Oracle数据库里得到了证实，确实执行是会报错的，
      那MySQL数据库呢？我们执行以下，得到以下结果

      

      说明该写法在Mysql里面竟然是合法的（所以所有的特性都要视不同的数据库，视情况而分析）

  • 若无字段可加，又需保证列数相同，可控NULL做填充

    • 举个栗子

      SELECT age, score
      FROM Student
      UNION ALL
      SELECT age, NULL
      FROM Teacher

      得到如下结果哦

      

  • 集合运算的结果视图的字段名以第一个结果集的字段名为主

    • 举个栗子

      SELECT stu_name
      FROM Student
      UNION
      SELECT tech_name
      FROM Teacher

      得到如下结果：

      

  • Oracle数据库中，差运算不是except， 而是minus；Oracle数据库union可以后面跟all， 但是interscet和minus后面不可以（不同数据库不同）

空值NULL

• NULL就是不确定的值，参与数值运算和字符串运算时，不同的数据库采取的处理不同；
• Oracle 数据库中，NULL无论参与数值运算还是字符串运算，都会使整体为NULL

• 获取系统时间

  • Oracle数据库的查询语句必须有from

  • 故采用如下方法获取系统时间

    -- 其中dual是Oracle数据库系统自带的一行一列的表
    -- 其他数据库没有这个表
    SELECT SYSDATE
    FROM dual

  • MySQL就没有这个限制

    -- 在MySQL和SQL Server中用下面的语句就可获得系统时间
    SELECT SYSDATE

• 日期类型的运算

  • 可与数值类型做加减运算（在Oracle数据库里面单位为天，·.·在MySQL里面竟然是秒）

    • 举个栗子（MySQL里面测试）

      SELECT SYSDATE() - 1, SYSDATE(), SYSDATE() + 1

      得到如下结果

      

  • 可与日期类型做减运算，得到连个日期之前的差值

  • 不可与日期类型做加运算（在Oracle数据库执行日期间加运算直接报错，Mysql数据库直接真的返回两个日期加以后的值，不过巨耗时，mmp）
  • 与NULL值运算得NULL值（在Oracle和MySQL上测试结果都正确）
• where 子句，判断是否为空
  • 用is NULL 和 is not NULL 判断
  • 而不用 = NULL 和 != NULL
• 布尔变量有三个取值：TRUE，FALSE, NULL
  • 三种取值以and，or相连时的结果，与并联串联电路的判断类
  • TRUE and NULL = NULL
  • TRUE OR NULL = TRUE
  • FALSE and NULL = FALSE
  • FALSE OR NULL = NULL
  • 举个栗子

    -- 下面的语句将会返回Student表中的所有信息
    -- 因为where子句后面的条件为真
    SELECT *
    FROM Student
    WHERE TRUE OR NULL

去重关键字 distinct

• 跟在SELECT的后面，并且置于所有字段的前面
• 会将其后的字段都作为判断重复的条件
• 举个栗子

  -- 下面的语句就是列出学生表中的数据，并去除stu_name和age都相同的数据重复
  SELECT DISTINCT stu_name, age
  FROM Student

聚集函数（Aggregate Functions）

多行输入，一行输出

• 此类函数有MIN，MAX，COUNT，AVG，SUM。其中AVG和SUM只能参与数值运算
• 聚集函数在使用时会忽略空值NULL
  • 除了COUNT(*)，COUNT(*)在统计的时候是不忽略空值的

• Count

  • 在计数时，忽略空值项

  • 默认是统计重复项的，其中ALL是默认的，顾可以不显示指明

    COUNT(ALL age)

  • 如果统计去除重复以后的结果,可如下面写法

    COUNT(DISTINCT age)

  • Oracle中用作对null值数据处理的函数nvl

    # 下面的语句表示对数据表中的分数做加和，如果遇到为空的项，则取其值为0
    SELECT SUM(NVL(score, 0))
    FROM Student

  • COUNT(*)==>可返回满足where子句条件的所有数据的数量

    • 这是COUNT独有的用法，其他聚集函数里面只能放字段或表达式

• SUM

  • SUM(age) + SUM(score) >= SUM(age + score)
  • 因为聚集函数在使用时会忽略空值，而NULL值直接参与运算可能会导致整体为空，顾有上述结论
  • 当且仅当数据中没有空值时，上述等号成立

分组聚集（Aggregation）

• GROUP BY 字段序列
  • GROUP BY 后面跟的字段序列作为分组条件，值相同的为一组
  • 可以是多个字段（顺序不影响结果）
• 当SELECT列表中出现了聚集函数，select中能出现以下字段
  • 可以放group by 后面的字段
  • 可以放聚集函数处理了的字段或表达式

    上面两种情况下的字段在每组的取值都是唯一的，故而可以保证结果集中每一项的行数是一致的

having， 解决where子句中不能包含聚集函数的问题

• 传送门