本笔记不适合初学者。本笔记不适合初学者。本笔记不适合初学者。

Oracle训练账户scott

scott用户是一个专门为初学者设计的示例账户，它提供了一系列预先创建的数据库和数据表，可以直接练习SQL而无需自己创建。

Oracle基础语法

函数

本章节仅介绍一些常用函数。

函数测试一般使用dual表，该表没有数据，查询什么就返回什么。

数值函数

  1. abs(x)：返回x的绝对值。 
    1
    2
    -- 123
    SELECT abs(-123) from dual;
  2. mod(x, y)：返回x除以y的余数。
    在mod(x, y)中，没有正负数的概念，结果只和x的正负性相关。取余操作是一个持续相减的过程，通过x持续减去y，我们可以还可以得到x的小数部分：当y=1时，可以直接得到x的小数部分。 
    1
    2
    3
    4
    5
    6
    -- 3
    SELECT mod(13, 5) FROM dual;
    -- 0.556
    SELECT mod(12.556, 1) FROM dual;
    -- 4
    SELECT mod(4, 12) FROM dual;
  3. ceil(x)和floor(x)：向上取整和向下取整。 
    1
    2
    -- 13 and 12
    SELECT ceil(12.556),floor(12.556) FROM dual;
  4. round(x,[d])：对x进行四舍五入，默认不保留小数。可选参数d表示要保留的小数位数。 
    1
    2
    3
    4
    -- 13
    SELECT round(12.556) FROM dual;
    -- 12.56
    SELECT round(12.556, 2) FROM dual;
  5. trunc(x,[y])：截取数字x。y默认为0。如果y为正数，则截取小数点后y位；如果为负数，则先保留整数部分，然后从个位开始向前y位，并将遇到的数字都变为0。该函数不进行四舍五入，直接截取。 
    1
    2
    3
    4
    5
    6
    -- 12
    SELECT trunc(12.556) FROM dual;
    -- 12.55
    SELECT trunc(12.556, 2) FROM dual;  
    -- 10
    SELECT trunc(12.556, -1) FROM dual;
  6. sign(x)判断x的正负。正数返回1，负数返回-1，0返回0。 
    1
    2
    -- 1
    SELECT sign(12) FROM dual;
  7. power(x,y)：返回x的y次方。 
    1
    2
    -- 2的3次方，8
    SELECT power(2, 3) FROM dual;

字符函数

  1. 大小写切换
    大小写切换分为三个函数：upper(s)、lower(s)和initcap(s)。upper(s)用于将字符串s转为大写，lower(s)用于将字符串s转为小写，initcap(s)仅仅将字符串s首字母转为大写。 
    1
    2
    3
    4
    5
    6
    -- SOMETHING
    SELECT upper('SOMEthing') FROM dual;
    -- something
    SELECT lower('SOMEthing') FROM dual;
    -- Something
    SELECT initcap('something') FROM dual;
  2. length(s)和lengthb(s)：用于计算字符串的字符和字节长度。 
    1
    2
    3
    4
    -- 2
    SELECT length('你好') FROM dual;
    -- 6
    SELECT lengthb('你好') FROM dual;
  3. trim(s)：去除字符串两端的空格。
    去除函数还有ltrim(s1, [s2])和rtrim(s1, [s2])，默认去除字符串s1左侧/右侧的空格，添加参数s2后用于去除字符串s1左侧/右侧的s2。 
    1
    2
    3
    4
    -- something
    SELECT trim('  something   ') FROM dual;
    SELECT ltrim('   something') FROM dual;
    SELECT rtrim('somethingaaaaa', 'a') FROM dual;
  4. lpad(s1, x, s2)和rpad(s1, x, s2)：在字符串s1左侧/右侧填充s2直到整个字符串的长度为x。 
    1
    2
    3
    4
    -- hhhhhhello
    SELECT lpad('hello', 10, 'h') FROM dual;
    -- worldddddd
    SELECT rpad('world', 10, 'd') FROM dual;
  5. instr(s1, s2, [x, [y]])：在s1中从第x位开始，查找s2第y次出现的位置。
    • x和y默认为1。
    • 如果x为负数，则从后往前查找。 
      1
      2
      3
      4
      5
      6
      -- 5
      SELECT instr('hello world', 'o') FROM dual;
      -- 8
      SELECT instr('hello world', 'o', 1, 2) FROM dual;
      -- 8
      SELECT instr('hello world', 'o', -1) FROM dual;
  6. substr(s, x, [y]])：在s中从第x位开始截取字符串，截取长度为y。
    • 如果省略y，则从第x位开始截取到末尾。
    • 如果x为负数，则从倒数第x位开始截取。 
      1
      2
      3
      4
      5
      6
      -- world
      SELECT substr('hello world', 6) FROM dual;
      -- llo wor
      SELECT substr('hello world', 3, 7) FROM dual;
      -- rld
      SELECT substr('hello world', -3) FROM dual;
  7. replace(s1, s2, [s3]将s1中的s2替换为s3，s3不写则替换为空。 
    1
    2
    3
    4
    -- hello oracle
    SELECT replace('hello world', 'world', 'oracle') FROM dual;
    -- hello
    SELECT replace('hello world', ' world') FROM dual;
  8. translate(s1, s2, s3)：将s1中的s2逐一替换为s3。translate()中可以使用额外的字符用于将某些文本替换为空（不是s1中出现的字符就行）。 
    1
    2
    3
    4
    5
    6
    -- 这是一二三四五
    SELECT translate('这是12345', '12345', '一二三四五') FROM dual;
    -- heo word
    SELECT translate('hello world', '*l', '*') FROM dual;
    SELECT translate('hello world', '?l', '?') FROM dual;
    SELECT translate('hello world', 'al', 'a') FROM dual;

转换函数

日期函数