DBA视频笔记

Oracle Logo

数据库是个硬伤,最近在X宝(真特么是个神奇的地方)搞了点OCP、OCA、OCM的视频,看点记点……不定时更新……

SQL部分

SQL分类

  1. DML 数据操纵语言
    SELECT、INSERT、DELETE、UPDATE、MERGE(11G)
    SELECT 对表数据进行读操作
    INSERT、DELETE、UPDATE 对表数据进行读操作 会引起UNDO,UBDO会引起REDO
  2. DDL 数据定义语言
    CREATE、ALTER、DROP、TRUNCAT、RENAME、COMMON (会更新数据字典)
  3. TCL 事务操作语言
    COMMIT、ROLLBAK、SAVEPOINT
  4. DCL 权限操作语言
    GRANT、REVOKE

基本SQL

  1. 数据类型
  • 字符型
    CHAR 固定字符,最长2000 (建表后无数据可改变,有数据只能涨)
    VARCHAR2 可变长度,最长4000,最小1
    NCHAR/NVARCHAR2 NCAHR/NVARCHAR2类型的列使用国家字符集(字母汉字一视同仁,都视为同一长度)
    RAW/LANG RAW 固定/可变长二进制数据长度,最大2G (老类型,逐步淘汰)
    LONG 可变长字符数据,最大2G,具有VARCHAR2特性,一个表最多1列 (老类型,逐步淘汰)

  • 数值型
    NUMBER(P,S) 实数类型,以可变长内部类型存储数据,内部格式精度高达38位
    INT NUMBER子类型,范围同上

  • 日期型
    DATE 日期普通格式,精度只能到秒
    TIMESTAMP 日期扩展格式,精度可达秒后小数点9位(十亿分之一秒)
    TIMESTAMP WHITH TIMEZONE 带时区
    TIMESTAMP WITH LOCAL TIMEZONE 时区转换成本地日期

  • LOB型(大对象)
    10G引入,11G重新定义,在字段中存储大数据,所有大对象最大4G
    CLOB 用于存储单字节字符数据,包含在数据库内(查询必须使用 like)
    NCLOB 用于存储多字节字符数据
    BLOB 用于存储二进制数据,包含在数据库内
    BFILE 存储在数据库之外的二进制文件中,文件中数据只能只读访问

    CLOB、NCLOB、BLOB都是内部LOB类型,没有LONG每个表只能有一列的限制;保存图片或者电影BLOB最好,小说等文本使用VLOB最好;LONG、RAW即将废弃,但并未完全废弃,11G重要视图dba_views,对于text(视图定义)仍沿用LONG类型

    Oracle 11g 重新设计了大对象,提出Secure Lobs的概念,相关参数是 db_securefile,采用Secure File的前提是11g以上版本,ASSM管理等;符合这些条件的BasicFile Lobs也可以转换成 SecureFile Lobs,较之前的BasicFile Lobs,SecureFile Lobs有极大改进:压缩、去重、加密

    CREATE TABLE 定义LOB列时,往往用到 LOB_storage_clause;之后对LOB的操作往往是ORACLE提供的 DBMS_LOB 包,通过编写 PL/SQL 块来对LOB数据进行管理

  1. 数据类型转换
  • 隐性类型转换
    是指ORACLE自动完成的类型转换;自一些带有明显意图的字面值上,可由ORACLE自主判断进行数制转换,如
    -- ORACLE 自主将字符型'7788'转换为数值型7788进行比较
    SELECT EMPNO FROM EMP WHERE EMPNO ='7788';
    -- 将日期型转换为字符型进行长度判断
    SELECT LENGTH(SYSDATE) FROM DUAL;
    -- 将字符型 '1028' 转换成数值型进行计算
    SELECT '1028'+123 FROM DUAL;
    -- 将数值型123转换为字符型进行拼接
    SELECT '1028'||123 FROM DUAL;
  • 显性类型转换
    TO_CHAR、TO_DATE、TO_NUMBER
    转换图
  1. 单行函数
      --字符函数
    
      lower('SQL')                    --返回小写
      upper('sql')                    --返回大写
      initcap('sql')                  --首字母大写
      concat('sql','test')            --字符串拼接
      substr('String',1,3)            --字符串截取
      instr('sqlsq#l#sql','#',3)      --从第3个位置查找'#'字符绝对位置
      length('String')                --返回字符串长度
      lpad('test'10,'#')              --左填充
      rpad('test',10,'*')             --右填充
      replace('11111q111','q','0')    --字符串替换
      trim('  1  ')                   --首尾去空格
    
      --数值函数
    
      round(p,s)                --对指定值做四舍五入,s为正数时表示小数点后保留位数,
                                  --也可位负数,意义不大;可用于按照指定精度对十进制数做四舍五入
      round(45.923,1)                 --结果45.9
      round(45.923,0)                 --结果46
      round(45.923,-1)                --结果50
    
      trunc(p,s)                      --对指定数值进行取整,可按精度截断指定十进制数
      trun(45.923,0)                  --结果45
    
      mod(100,12)                     --取余
    
      --日期函数
    
      --日期数值在ORALCE中按照数字存储,所以可以进行加减运算,计算时以天为单位,缺省格式 DD-MON-RR
      SELECT SYSDATE+2 FROM DUAL;                                     --当前日期+2天
      SELECT SYSDATE+2/24 FROM DUAL;                                  --当前日期+2小时
      TO_DATE('2016-01-01 21:40:30','YYYY-MM-DD HH24:MI:SS')          --字符串转日期
      TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')                        --日期转字符串
      MONTHS_BETWEEN                                                  --计算两个日期之间相差月数
      ADD_MONTHS                                                      --给指定日期加月份
      LAST_DAY                                                        --指定日期月份最后一天
      NEXT_DAY                                                        --指定日期下一天 第二个参数为1~7,表示周日~周六
                                                                    --NEXT_DAY(SYSDATE,7)  表示下一个星期六
    
      --对于日期可使用 四舍五入、取整操作 s是'MONTH'按30天计算15舍16进,s是'YEAR'5舍7如
    ROUND(p,s)、TRUNC(p,s)     
    
      --其它函数(表达式)
      DECODE(COLUMN,KEY1,VALUE1,KEY2,VALUE2...DEFAULT)
      CASE COLUMN WHEN XXXX THEN XXXX    --等值判断相当于 DECODE
                  WHEN XXXX THEN XXXX
                  ELSE XXXX END
      CASE WHEN 条件 THEN XXXX           --条件判断
           WHEN 条件 THEN XXXX
           ELSE XXXX END
    
      DISTINCT                           --去重
      CHR()                              --字符转ASCII
      ASCII()                            --ASCII转字符
      SYS_CONTEXT                        --获取上下文环境函数
    

SYS_CONTEXT函数参考 其他参考

select sys_context('USERENV','AUTHENTICATION_TYPE') from dual;--用户的认证类型
select sys_context('USERENV','AUTHENTICATION_DATA') from dual;--未知
select sys_context('USERENV','BG_JOB_ID') from dual;--当前指定id的会话是否为oracle后台程序建立,不是则返回null
select sys_context('USERENV','CLIENT_INFO') from dual;--通过dbms_application_info包可以存储高达64字节的用户会话信息
select sys_context('USERENV','CURRENT_SCHEMA') from dual;--默认的schema将被当做当前的schema。当在当前会话中使用ALTER SESSION SET CURRENT_SCHEMA语句的时候,它的查询返回值将被改变
select sys_context('USERENV','CURRENT_SCHEMAID') from dual;--当前schema的id
select sys_context('USERENV','CURRENT_USER') from dual;--当前的登陆用户
select REPLACE(SUBSTR(sys_context('USERENV','HOST'),1,30),'\',':') from dual;'--当前会话主机操作系统名
select sys_context('USERENV','CURRENT_USERID') from dual;--当前登陆的用户的id
select sys_context('USERENV','DB_DOMAIN') from dual;--为数据库的域指定初始化参数
select sys_context('USERENV','DB_NAME') from dual;--数据库实例名
select sys_context('USERENV','ENTRYID') from dual;--可用的审计标示符。不能再分布式sql语句中使用此选项。使用USERENV关键字必须置AUDIT_TRAIL的初始化参数为真。
select sys_context('USERENV','EXTERNAL_NAME') from dual;--数据库用户的扩展名
select sys_context('USERENV','FG_JOB_ID') from dual;--返回作业id当此会话是客户端进程创建。否则,返回null
select sys_context('USERENV','INSTANCE') from dual;--当前数据库实例的标示id
select sys_context('USERENV','ISDBA') from dual;--当前用户是否是以dba身份登录
select sys_context('USERENV','LANG') from dual;--iso对‘LANGUAGE’的简称,查询的参数比“LANGUAGE”短
select sys_context('USERENV','LANGUAGE') from dual;--结果为当前数据库使用的存储语言,跟上面查询意义一样
select sys_context('USERENV','NETWORK_PROTOCOL') from dual;--用于通信的网络协议
select sys_context('USERENV','NLS_CALENDAR') from dual;--当前会话使用的,格林尼治时间
select sys_context('USERENV','NLS_CURRENCY') from dual;--本地化的货币符,如人民币为¥,美元符为$
select sys_context('USERENV','NLS_DATE_FORMAT') from dual;--当前使用的日期格式,一般中国为dd-mon-rr
select sys_context('USERENV','NLS_DATE_LANGUAGE') from dual;--表示日期的语言,如中文简体SIMPLIFIED CHINESE
select sys_context('USERENV','NLS_TERRITORY') from dual;--数据库服务器所在区域,如中国CHINA
select sys_context('USERENV','OS_USER') from dual;--操作系统的用户名
select sys_context('USERENV','PROXY_USER') from dual;--是否使用代理用户。否返回null
select sys_context('USERENV','PROXY_USERID') from dual;--代理用户id
select sys_context('USERENV','SESSION_USER') from dual;--当前认证的数据库用户名
select sys_context('USERENV','SESSION_USERID') from dual;--当前认证的数据库用户名id
select sys_context('USERENV','SESSIONID') from dual;--当前会话id
select sys_context('USERENV','TERMINAL') from dual;--操作系统用户组
select sys_context('USERENV','IP_ADDRESS') from dual;--当前会话主机ip
select sys_context('USERENV','HOST') from dual;--当前会话主机操作系统名

3. 多行函数


本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 国际许可协议进行许可,转载请注明出处。