DBA视频笔记 — 漠然

DBA视频笔记

2016/01/08 ORACLE

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 转换图
  2. 单行函数 ``` sql –字符函数

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函数参考** **[其他参考](http://alany.blog.51cto.com/6125308/1418163)**
  ``` sql
  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;--当前会话主机操作系统名
  1. 多行函数 转载请注明出处,本文采用 CC4.0 协议授权

Search

    Post Directory