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自主判断进行数制转换，如
    1
    2
    3
    4
    5
    6
    7
    8
    -- 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. 单行函数
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
      --字符函数

      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函数参考 其他参考
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    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;--当前会话主机操作系统名
  2. 多行函数
Database
#SQL #Oracle
DBA视频笔记
https://mritd.com/2016/01/08/dba-video-node/
作者
Kovacs
发布于
2016年1月8日
许可协议