Oracle基础以及一些‘方言’（二）

1、Oracle的查询语法结构

• Oracle 的单表查询的语法结构：

  • SELECT 1 FROM 2 WHERE 3 GROUP BY 4 HAVING 5 ORDER BY 6

  • 其每个关键词的功能与MySQL中的功能已知，不过分页查询的关键词 limit 并不在Oracle的语法结构中。
• 伪列：

  • 在 Oracle 的表的使用过程中 ，实际表中还有一些附加的列，称为伪列。

    伪列就 像表中的列一样 ，但是在表中并不存储。伪列只能查询，不能进行增删改操作。

  • ROWID

    • 表中的每一行在数据文件中都有一个物理地址 ，ROWID 伪列返回的就是该行的物理地址。

    • 由于 ROWID 返回的是该行的物理地址，因此 ROWID 可以唯一的标识表中的一行,且使用 ROWID 可以快速的定位表中的某一行。

  • ROWNUM

    • 在查询的结果集中 ，ROWNUM 为结果集中每一行标识一个行号 ，第一行返回 1 ， 第二行返回 2 ，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。        

    • ROWNUM 的功能

      • 限制结果集大小：可以用来限制查询返回的行数，如取前 N 行。

      • 分页查询：在分页查询中，通过 ROWNUM 可以实现简单的分页逻辑。

  • ROWID：唯一标识数据库中每一行的物理位置，常用于快速访问和更新特定行。

  • ROWNUM：表示查询结果集中每行的行号，常用于限制查询结果集大小和实现分页查询。

2、Oracle分页查询

• Oracle的简单的条件查询与MySQL相似，较为不同的是Oracle中不支持分页查询的 limit 语法，并且多了伪列的概念。想要实现分页查询，我们可以借助 伪列 rownum 的概念。

• 分页查询
  • 返回查询的前5个的学生的信息

  • select *
    from STUDENT
    where rownum <= 5
    ;

  • 如果我们需要返回第6~8条结果呢？

  • select *
    from STUDENT 
    where rownum > 5 and rownum <= 8
    ;

  • 执行如上命令的结果为空

  • 原因：这是因为 Oracle 在返回结果时，会逐行分配
            对于第一行，它的 ROWNUM 是 1。
            然后，WHERE 子句会检查第一行，但由于 ROWNUM 是 1，因此它被排除。
            由于第一行已经被排除，第二行的 ROWNUM 依然是 1。
            这导致永远没有 ROWNUM 为 2 的行出现。

  • 解决：使用多层嵌套查询，并为内部查询的rownum分配别名

  • with t1 as (
    select s.*
            , ROWNUM as r
    from STUDENT s)
    select *
    from t1
    where r > 5 and r <= 8
    ;

3、Oracle多表查询

• 内连接

  • Oracle的内连接与MySQL的sql语法类似，当不添加限制条件时为交叉连接，即构成笛卡尔积。

  • select *
    from Course
    inner join TEACHER on Course.t_id = Teacher.t_id;

• 外连接

  • 按照 SQL1999 标准的语法 ，查询语句如下 ：

    • 左外连接：

    • select *
      from Course
      left join TEACHER on Course.t_id = Teacher.t_id;

      ​​​​​​​

    • 右外连接：

    • select *
      from Course
      right join TEACHER on Course.t_id = Teacher.t_id;

  • 按照 ORACLE 提供的语法 ，就很简单了 ：

    • 左外连接

    • ​select *
      from Course， TEACHER
      where Course.t_id = Teacher.t_id(+);
      

    •  右外连接

    • ​select *
      from Course， TEACHER
      where Course.t_id(+) = Teacher.t_id;
      

    • 注意：如果是左外连接，就在右表所在的条件一端填上 (+)，同理，如果是右外连接，就在左表所在的条件一端填上 (+)。