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;--当前会话主机操作系统名

3. 多行函数


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