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. 多行函数

Database
#SQL #Oracle
DBA视频笔记
https://mritd.com/2016/01/08/dba-video-node/
作者
Kovacs
发布于
2016年1月8日
许可协议