Oracle基础命令

本文字数： 1.3k 阅读时长 ≈ 5 分钟

主流关系型数据库: Oracle，DB2，Sybase，SQL server，MySQL
这里主要记录一下oracle数据库初步学习过程，涉及少量与mysql的不同讨论。基本的SQL知识

SQL（Structured Query Language）

常用关键字

  • SQL可分为
    • 数据定义语言DDL: CREATE, ALTER, DROP, TRUNCATE
    • 数据操纵语言DML: INSERT, UPDATE, DELETE
    • 事务控制语言TCL: COMMIT, ROLLBACK, SAVEPOINT
    • 数据查询语言DQL: SELECT
    • 数据控制语言DCL: GRANT, REVOKE, CREATE USER
  • NUMBER: 数字类型
    • 定义: NUMBER（P，S）
    • FYI: MySQL里没有number类型，对应的是int，float……
  • CHAR: 固定长度的字符类型
    • 定义: CHAR(N)
    • 最多保存2000字节
    • FYI: 对应MySQL里的CHAR
  • VARCHAR2: 变长的字符类型
    • 定义: VARCHAR2(N)
    • 最多保存4000字节
    • FYI: 对应MySQL里的VARCHAR
  • DATE: 日期时间数据
    • 默认格式: DD-MON-RR
  • DEFAULT: 指定默认值
  • NOT NULL: 非空约束
  • DROP
    • 删除列或表
  • LONG: VARCHAR2加长版
    • 限制: 每个表只能有一个long列，不能作为主键，不能建立索引，不能出现在查询条件中
    • FYI: MySQL没有long型
  • CLOB: 存储定长或变长字符串，oracle建议使用clob替代long型
  • 执行DML操作后，需要在执行commit，才算真正确认了此操作

常用函数

  • UPPER, LOWER, INITCAP: 大小写转换
  • TRIM, LTRIM, RTRIM: 截去子串
  • LPAD, RPAD: 补位函数
    • LPAD(char1, n, char2)
    • RPAD(char1, n, char2)
  • SUBSTR: 获取子串
    • SUBSTR(char, m[, n])
  • INSTR: 返回子串在父串中的位置
    • INSTR(char1, char2[, n[, m]])
      • n: 开始搜索的位置，默认为1
      • m: 指定子串第m次出现，默认为1
      • 没找到，返回0
  • ROUND：四舍五入
    • ROUND(n[, m])
      • n: 可以是任何数字，指要被处理的数字
      • m：必须是整数，四舍五入的位数，可以是负数，默认是0
  • MOD: 取余
    • MOD(m, n) n为0直接返回m
  • CEIL
    • CEIL(n)
  • FLOOR
    • FLOOR(n)
  • CONCAT, “||”
日期操作

  • DATE

    • 范围：公元前4712年1月1日至公元9999年12月31日
    • 占用7个字节
    • byte 1: 世纪+100
    • byte 2: 年
    • byte 3: 月
    • byte 4: 日
    • byte 5: 小时 + 1
    • byte 6: 分 + 1
    • byte 7: 秒 + 1

  • TIMESTAMP

    • 最高精度可以到ns
    • 占用7或者11个字节，精度为0，用7字节存储，精度大于0用11字节存储
    • 精度：第8-11字节，内部运算类型为整型

  • SYSDATE: SystemDate

  • SYSTIMESTAMP: 返回当前系统日期和时间，精确到毫秒

  • TO_DATE：字符串转换为日期类型

    • TO_DATE(char[, fmt[, nlsparams]])

      • fmt: 格式

        202005251720HLa2kn12

      • nlsparams: 指定日期语言

  • TO_CHAR

    • TO_CHAR(date[, fmt[, nlsparams]])

  • LAST_DAY

    • LAST_DAY(date): 返回日期date所在月的最后一天

  • ADD_MONTHS

    • ADD_MONTHS(date, i)

  • MONTHS_BETWEEN

    • MONTHS_BETWEEN(date1, date2): 计算月份差

  • NEXT_DAY

    • NEXT_DAY(date, char): 返回date日期的下一个周几

  • LEAST, GREATEST

    • GREATEST(expr1[,expr2[, expr3]]…)

  • EXTRACT

    • EXTRACT(date from datetime): 提取日期中的年、月、日等
空值操作
  • NVL
    • NVL(expr1, expr2): expr1为null则转变为expr2，数据类型必须一致
  • NVL2
    • NVL2(expr1, expr2, expr3): expr1非null返回我想expr2，为null返回expr3

高级查询

子查询
分页查询
  • ROWNUM：伪列，用于返回标识行数据顺序的数字
    • 只能从1计数，不能从结果集中直接截取
    • 部分数据需要用到行内视图
    • PageN: (n - 1) * pageSize + 1 ~ n * pageSize
DECODE函数

  • DECODE(expr, search1, result1[,search2, result2….][, default])

  • 用途： 比较expr的值，若匹配到哪一个search，返回对应result，类似于case语句

  • 应用场景：

    • 分组查询
    1
    2
    SELECT DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION')job, COUNT(1)job_count FROM emp 
    GROUP BY DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');
    • 字段内容排序
      ORDER BY DECODE(job, 'ANALYST', '1', 'MANAGER', '2', 'OPERATION', '3')
ROW_NUMBER函数
  • ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2): 根据col1分组，在分组内部根据col2排序
  • 此函数计算的值标识每组内部排序后的顺序编号，组内连续且唯一
RANK_NUMBER函数
  • 类似于ROW_NUMBER, 跳跃排序，可以有重复值
DENSE_RANK
  • 类似于RANK_NUMBER, 连续排序，有重复，无跳跃
UNION
  • UNION: 自动合并去重，排序
  • UINION ALL：合并不去重，不排序
  • INTERSECT：交集
  • MINUS：差集
高级分组函数
  • ROLLUP: 从右向左以一次少一列的方式组合直到所有列都去掉
    • GROUP BY ROLLUP(a, b, c)
  • CUBE: 所以维度的取值集合
    • GROUP BY CUBE(a, b, c)
  • GROUPING SETS:
    • GROUP BY GROUPING SETS(a, b, c)

视图

  • 视图(VIEW) 也称虚表，是一组数据的逻辑表示，对应于一条select语句
  • 分类：
    • 简单视图
    • 复杂视图
    • 连接视图
  • 作用：
    • 简化复杂查询
    • 限制数据访问
      • GRANT CREATE VIEW TO user;
  • 限制约束
    • [WITH CHECK OPTION]
    • [WITH READ ONLY]

序列

  • 创建序列
    • CREATE SEQUENCE[schema.]sequence_name
      [START WITH i][INCREMENT BY j][MAXVALUE m | NOMAXVALUE][MINVALUE n | NOMINVALUE][CYCLE | NOCYCLE][CACHE p | NOCACHE]
  • 使用序列
    • NEXTVAL: 获取序列的下个值
    • CURRVAL：获取序列的当前值

索引

  • 索引(INDEX)是一种允许直接访问数据表中某一数据行的数据结构，为了提高查询效率而引入，是独立于表的对象，可以存放与表不同的表空间中
  • Syntax：
    • CREATE [UNIQUE] INDEX index_name ON table(column[, column…]);
    • UNIQUE表示唯一索引

约束

  • 非空约束 NOT NULL
  • 唯一性约束 UNIQUE
  • 主键约束 PRIMARY KEY
  • 外键约束 FOREIGN KEY
  • 检查约束 CHECK