ocp-071知识点

发表于 更新于 阅读次数：

using

1
SELECT DEPTNO,ENAME FROM EMP E LEFT JOIN (SELECT DEPTNO, DNAME FROM DEPT) D USING(DEPTNO);
  • 可以使用表别名；但using内不能用;select 列不能用别名
  • using连接两个表的列名必须完全一致

分组函数

  • count/avg/max/min
  • avg 参数只能是数值类型
  • 嵌套使用时必须有group by
  • Where 后面不能用分组函数

count

1
2
3
4
select count(*) from emp ; -- 14
select count(1) from emp ; -- 14
select count(mgr) from emp; -- 13 
说明不统计mgr为null的记录；count * 和 1 都会统计为null的记录。

运算符

1
2
3
4
5
6
7
8
9
优先级
算术运算符(即‘+’,‘-’，‘*’,‘/’)                   1
连接运算符（即‘||’）                                2
比较运算符（即‘>’，‘>=’，‘<’，‘<=’，‘<>’）          3
Is [not] null,[not] like,[not] in           4
[not] between-and                       5
not                                     6
and                                      7
or                                      8

可括号改变优先级

  • 小于ANY，相当于多个条件or,也相当于小于最大值
  • 小于ALL，相当于多个条件and，也相当于小于最小值

子查询

  • In(subquery) subquery结果集包含null值时，会自动忽略空值
  • not in(subquery) subquery结果集包含null值时,主查询会查询不到结果的(no rows)
  • 非相关子查询是独立于外部查询的子查询， 子查询总共执行一次， 执行完毕后将值传递给外部查询。
  • 相关子查询的执行依赖于外部查询的数据， 外部查询执行一行， 子查询就执行一次。

提示框

prompts

  • & 用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值
  • &&用来创建一个持久变量，当用&&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次

空值

  • 插入字符串空值

    1
    2
    update dept set loc='' where deptno=40;
    update dept set loc=null where deptno=40;

  • 查询空值必须使用is (not) null

单行函数

  • concat 参数个数不能超过2个，参数类型是字符、数字、日期都行

  • instr 获取字符在字符串中位置

    • 四个参数(被搜索的字符串, 搜索的字符, 开始查找的位置[负数要倒数位置]，出现的次数)
    • 两个参数 [默认第一个字符，从前往后，第一次出现]
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
      select instr('helloworld','lo') from dual; --返回结果：4    即：在“lo”中，“l”开始出现的位置
      select instr('helloworld','wo') from dual; --返回结果：6    即“w”开始出现的位置

      select instr('helloworld','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
      select instr('helloworld','l',3,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
      select instr('helloworld','l',4,2) from dual;  --返回结果：9    也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
      select instr('helloworld','l',-1,1) from dual;  --返回结果：9    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
      select instr('helloworld','l',-2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第二次出现的“l”的位置
      select instr('helloworld','l',2,3) from dual;  --返回结果：9    也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
      select instr('helloworld','l',-2,3) from dual; --返回结果：3    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置

  • lpad( string, padded_length, [ pad_string ] )

    • string 准备被填充的字符串
    • padded_length 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符
    • pad_string 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

  • NVL2(exp1,exp2,exp3) 

    1
    2
    如果 exp1 不为空 ,函数 返回 exp2 的值; 
    如果 exp2 为空 ,函数 返回 exp3 的值;

  • NVL(exp1,exp2)

    1
    2
    3
    如果 exp1 为空 , 函数 返回 exp2 的值 ; 
    如果 exp1 不为空, 函数 返回 exp1 的值 ; 
    如果两个参数都为NULL ，则返回NULL

  • MONTHS_BETWEEN(date1, date2) 两个日期之间相差的月数 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12

      CREATE TABLE "PROGRAMS" 
       (	"PROG_ID" NUMBER NOT NULL ENABLE, 
    	"PROG_COST" NUMBER, 
    	"START_DATE" DATE NOT NULL ENABLE, 
    	"END_DATE" DATE
       ) ;
    Insert into PROGRAMS (PROG_ID,PROG_COST,START_DATE,END_DATE) values (1,234,to_date('2019-07-09 05:33:50','YYYY-MM-DD HH24:MI:SS'),null);
    Insert into PROGRAMS (PROG_ID,PROG_COST,START_DATE,END_DATE) values (2,2,to_date('2019-05-08 05:39:54','YYYY-MM-DD HH24:MI:SS'),to_date('2019-08-04 05:40:05','YYYY-MM-DD HH24:MI:SS'));

    -- 相差几个月
    SELECT MONTHS_BETWEEN (start_date, end_date) FROM programs;

  • INITCAP(string) 首字母大写，其他小写

DDL

  • DDL不能roolback；且隐式提交(自动执行commit)
  • drop的表会放进回收站、占用空间不回收
  • 会同时删除表上的索引和约束

to_char

https://blog.csdn.net/shangboerds/article/details/46473117

  • to_char(number, ‘format’)
  • format=’9,99’ 要大于number位数，否则变为####
  • 千位符和小数点可以与符号G和D互换
  • 小数会进位
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
    --  $1,890.55

    SELECT TO_CHAR(1890.55,'$99,999.99') FROM DUAL;
    --     $1,890.55

    SELECT TO_CHAR(561890.55,'$99G999D99') FROM DUAL;
    -- ###########

    select TO_CHAR(1890.55, '$9,999') from dual;
    --  $1,891

集合运算

  • UNION 和集

  • UNION ALL 并集

  • INTERSECT 交集

  • MINUS 差集

    • 列个数、类型一致但列名不一致时以前面的名称位置

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      select empno "empid", ename, 'a'
      from emp where empno=7369
      UNION
      select empno, ename "empname", 'b'
      from emp where empno=7566

      empid   ENAME   'a'
      -----------------
      7369	SMITH	a
      7566	JONES	b

    • 只能在最后order by

order by

  • 默认ASC

  • 可以有多个列排序,也可以使用列数代替列名

    1
    select empno,ename from emp order by 1 desc, ename desc;

  • UNION时不能出现在语句中间，只能给union结果排序

  • nulls last(first)
    desc时null会当做最大，asc时会当做最小;
    可以使用nulls改变null值的位置

    1
    2
    3
    SELECT ename, comm FROM emp order by comm nulls first ;
    SELECT ename, comm FROM emp order by comm nulls last;
    SELECT ename, comm FROM emp order by comm desc nulls last ;

ESCAPE

转义符号(可以看做escape后边的字符不转义，直接输出)

1
2
3
SELECT *
FROM users
WHERE user_name LIKE '%ch\_%' ESCAPE ' \';

external table

  • create table as select external_table 语句会生成一个正常的表及数据

SEQUENCE

  • 如果使用 cache， 关闭数据库时， 所有已生成并缓存， 但还没有发出的数值会丢失

  • oracle11g 不能作为字段的默认值,12c可以

    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
    --- 12c
    --结果显示可以插入null、字面值,而不使用sequence的值。插入default值的时候就使用sequence的值。
    create table sequence_test_tab(id number default test_seq.nextval);
    insert into sequence_test_tab values(null);
    insert into sequence_test_tab values(default);
    insert into sequence_test_tab values(10000);

    -- 11g不支持，但可以使用触发器达到相同效果
    -- 1、表结构中log_id 非空、且为主键字段
    create table TL_M_QRTZ_LOG
    (
    log_id NUMBER(8) not null,
    job_id NUMBER(8) not null,
    seq_no NUMBER(4),
    start_time DATE not null,
    end_time DATE,
    exec_state VARCHAR2(256) default '0',
    expr_eval VARCHAR2(256),
    fail_reason VARCHAR2(1024)
    )

    -- 2、建立触发器
    create or replace trigger log_trigger before
    insert on TL_M_QRTZ_LOG for each row
    when (new.LOG_ID is null)
    begin
    select qrtz_sequence.nextval into:new.LOG_ID from dual;
    end;

    -- 3、数据插入语句
    INSERT INTO TL_M_QRTZ_LOG (
    JOB_ID,
    SEQ_NO,
    START_TIME,
    END_TIME,
    EXEC_STATE,
    FAIL_REASON)
    VALUES (
    #jobId#,
    #seqNo#,
    #startTime#,
    #endTime#,
    #execState#,
    #failReason#)

  • sequence生成序号

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE SEQUENCE seq1
    START WITH 100
    INCREMENT BY 10
    MAXVALUE 200
    CYCLE
    NOCACHE;

    SELECT seq1.nextval FROM dual; 
    -- 生成顺序
    -- 100 110 120 ... 200 1 11 21 ... 191 1 11 ......

  • 修改与删除

    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 可修改最大值、缓存、循环选项、增量值
    alter sequence dept_deptno
    increment by 1
    maxvalue 999999
    nocache
    nocycle;

    -- 删除序列
    drop sequence dept_deptno;

like

1
2
-- 字符大小写的问题
select * from emp where ename like 'Mc%';

coalesce

  • 合并多个结果集数据，达到case…when相同的效果
  • (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值
  • 函数里面的数据类型，必须全部都跟第一列的数据类型一致

decode

DECODE(value,if 条件1，then 值1，if 条件2，then 值2，…，else 其他值)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 将员工工资sal大于2000的标记为'high'，其他为'low'----

-- 使用case when
SELECT
    ENAME,
    CASE
            WHEN sal > 2000 THEN 'HIGH'
            ELSE 'LOW'
        END AS SALEVEL
FROM
    EMP;

-- 使用decode
SELECT ENAME ,
DECODE(sign(SAL-2000), -1, 'HIGH','LOW') SALEVEL 
FROM EMP;

-- sign比较两个数大小
-- (select从句不能直接使用比较符号，要使用比较函数)
sign(n) 取数字n的符号,大于0返回1, 小于0返回-1, 等于0返回0


-- 作为条件判断函数使用
SELECT ENAME, JOB, DECODE(JOB, 'MANAGER', 'IS MANAGER', 'NOT MANAGER') JOBTYPE FROM EMP;

if

case when

https://www.cnblogs.com/aipan/p/8078835.html

grant

1
2
3
4
5
6
7
8
9
-- 给用户角色权限
-- (如登录、创建视图等都是角色权限)把角色赋予给用户后，用户就有登录和创建视图的权限了
GRANT CONNECT,RESOURCE TO zhang;

-- 给用户ZHANG.PERSON表的特定操作权限
GRANT SELECT,DELETE,UPDATE,INSERT ON ZHANG.PERSON TO CHENMH ;

-- 把ZHANG.PERSON表的所有权限给CHENMH
GRANT ALL PRIVILEGES ON ZHANG.PERSON TO CHENMH;

view视图

  • 分为简单视图和复杂视图

    • 简单视图：只有一个表、可执行DML
    • 复杂视图：一个或多个表、包含分组数据、使用函数、不能执行DML

  • 视图创建及删除

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- 授予创建视图的权限
    GRANT CREATE VIEW TO SCOTT;
    -- 收回权限
    revoke CREATE VIEW from SCOTT;

    -- 创建视图
    CREATE VIEW VIEW_EMP AS SELECT * FROM EMP;
    CREATE OR REPLACE VIEW VIEW_EMP_JOB AS SELECT DISTINCT JOB FROM EMP;
    -- 删除视图
    DROP VIEW VIEW_EMP;

  • with check option 限定操作范围

  • with read only 禁止DML操作

    1
    2
    3
    4
    -- 只读的视图
    CREATE VIEW VIEW_EMP AS 
    SELECT * FROM EMP
    with read only;

  • USER_VIEW 查看视图的创建信息

synonym 同义词

  • 如一个表名字特别长， 可以创建同义词， 简化对对象的访问

  • Oracle中同义词是任何表、视图、物化视图、序列、存储过程、函数、包、类型、JAVA类对象、用户定义类型,或是其他的同义词的别名

  • public和private同义词可以重名

    1
    2
    private同义词只能自己访问， public同义词任何有权限的用户都可以访问。
    公有同义词不是模式对象， 不能用模式名做前缀

  • 一个schame下table可以与synonyms同名

  • 创建及删除synonym

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 普通用户要具有创建同义词的权限
    grant create synonym to scott;
    grant create public synonym to scott;

    -- 创建一个public同义词
    create public synonym scottemp for emp;

    -- 把scottemp同义词的访问权限给hr用户
    grant select on scottemp to hr;

    -- 删除
    drop synonym scottemp;

NULLIF

  • 语法NULLIF ( expression1 , expression2 )
  • 输出结果：
    如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。
    如果两个表达式相等，NULLIF 返回空值NULL。

隐式转换

implicate conversion 隐式转换
explicate conversion 直接转换

句子

1
2
They can be created on tables and clusters.
它们可以在表和集群上创建