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;
      

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

 4、Oracle子查询

Oracle的子查询具体实现的语法与MySQL类似

• 操作符含义

  • 
  • 返回多值的条件子查询
  • 

5、Oracle常用函数

更多的函数见。。。。。

• 字符函数

  • LENGTH：查询字符串中的字符个数

  • select length('你好，世界'),
           length('hello, world')
    from dual;

  • LENGTHB：查询字符串中的字节个数

  • select lengthb('你好，世界'),
           lengthb('hello, world')
    from dual;

  • SUBSTR：查询字符串的子串

  • select substr('hello,oracle!',2,2) from dual;
    select substr('hello,oracle!',2) from dual;
    select substr('hello,oracle!',-7) from dual;
    

  • CONCAT：字符串拼接

  • select concat('ABC', 'D') from dual;

  • 也可以用||  对字符串进行拼接

  • select 'ABC' || 'D' from dual;

• 数值函数

  • ROUND：四舍五入函数

  • select round(100.567) from dual;
    select round(100.567,2) from dual;
    

  • TRUNC：截取函数

  • select trunc(100.567) from dual;
    select trunc(100.567,2) from dual;
    

  • MOD：取模

  • select mod(10, 3) from dual;

• 日期函数

  • SYSDATE：获取当前日期和时间

  • select sysdate from dual;

  • ADD_MINTHS：在指定日期的基础上加指定的月

  • select add_months(sysdate, 2) from dual;

  • LAST_DAY：求指定日期所在月的最后一天

  • select last_day(sysdate) from dual;

  • TRUNC：日期截断

  • select trunc(sysdate) from dual
    select trunc(sysdate, 'yyyy') from dual
    select trunc(sysdate, 'mm') from dual

• 转换函数

  • TO_CHAT：数字转字符串

  • select to_char(1024) from dual

  • TO_CHAT：日期转字符串

  • select to_char(sysdate, 'yyyy-mm-dd') from dual
    select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual
    select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

  • TO_DATE：字符串转日期

  • select to_date('2017-01-01', 'yyyy-mm-dd') from dual

  • TO_NUMBER：字符串转数字

  • select to_number('100'), '100' from dual

• 其他函数

  • NVL：空值处理函数