OCP SQL和sqlplus使用

rownum 记录在结果集中的位置。
rowid 记录在表中的位置。
序列的 CURRVAL 和 NEXTVAL。
USER：
SELECT USER From DUAL
当前日期和时间戳：SYSDATE 和 SYSTIMESTAMP
SELECT SYSDATE, SYSTIMESTAMP From DUAL

伪表

DUAL

查看表、对象定义

describe 对象名

NULL

NULL不是0，不是\0字符、也不是空格，它是数据库专有值，表示未赋值。
数学表达式中，包含了NULL，则结果为NULL。
nvl(col, 0)，如果col为NULL，则转换为0，PG中对应的函数：coalesce(col, 0)
NVL2(expr1,expr2,expr3) 如果expr1非空，则返回expr2，否则返回expr3
nullif(expr1,expr2) 如果expr1和expr2相同，则返回null，否则返回expr1

对于NULL的比较

1. 可以借助nvl
2. is null, is not null
包含 null 值的列排序时，默认把 null 值看做无限大:
在升序排序时， null 最后
在降序排序时， null 最前

||表示连接字符串

select 'desc '|| table_name from user_tables;

distinct去重

注意，一个select list里只能有一个distinct，select list如果有多个列，distinct是对它们组合成的行去重，而不是对单独某个列去重。
select distinct deptno,job from emp; --- 正确
select empno,distinct deptno from emp; --- 错误
select distinct empno,distinct deptno from emp; --- 错误

order by

1. order by 后面不能跟 LOB、 LONG、 LONG ROW 类型的列。
2. select语句中有 distinct，order by 后面的列，必须出现在select列表中。

模糊查询 like

select * from emp2 where ename like 'S%'; --走索引
select * from emp2 where ename like '%S%'; --全表扫描，不走索引

查看执行计划

explain plan for select * from emp2 where ename like 'S%';
@?/rdbms/admin/utlxplp --？表示$ORACLE_HOME

统计数据

execute dbms_stats.gather_table_stats('schema1','t1');
execute dbms_stats.gather_schema_stats('schema1');
execute dbms_stats.gather_database_stats();
ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;

函数和存储过程

1. 函数有1个返回值，而存储过程是通过参数返回的，可以有多个或者没有
2. 调用的区别，函数可以在查询语句中，直接调用，而存储过程必须单独调用
一般情况下，函数是用来计算并返回一个计算结果，而存储过程，是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）oracle中存储过程都属于某个包，例如上面的execute dbms_stats.gather_schema_stats('schema1');
3. 单行函数可以嵌套任意层级，执行顺序是由内到外。

隐式数据类型转换

数值和日期转换函数，会用到格式掩码，所谓格式掩码就是一个控制数值和函数打印格式的字符串，例如‘YYYY-MM-DD HH24:MI:SS’。

修改当前会话日期格式

alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';

case条件表达式

对于每一行的job列，根据其值，做不同处理：
select empno,ename,sal,job,
case job when 'ANALYST' then sal*1.1
when 'CLERK' then sal*1.15
when 'MANAGER' then sal*1.2
else sal*1.25
end new_sal
from emp order by job;
第二种形式：
select empno,ename,sal,job,
case when job='ANALYST' then sal*1.1
when job='CLERK' then sal*1.15
when job='MANAGER' then sal*1.2
else sal*1.25
end new_sal
from emp order by job;
也可以用来处理NULL值：
select comm,case when comm is null then 0 else comm end new_comm from emp;
decode函数有同样的功能：
select empno,ename,job,sal,
decode(job,'ANALYST', sal*1.1,
'CLERK', sal*1.15,
'MANAGER', sal*1.20,
sal*1.25) new_sal
from emp order by job;

聚合函数group by

聚合函数是，多行输入，一行输出，多行就是，group by分组后，属于同一组的那些行。
group by中的列可以不出现在select列表中，而select列表中的列，如果不再聚合函数中，必须出现在group by从句中。

对聚合函数的结果进行过滤

不能使用where，而是使用having，例如：
select deptno,max(sal) from emp group by deptno having max(sal) >2900;
having子句可以放在group by前面，也可以放在group by 后面。
分组函数最多嵌套两层(两个函数)，嵌套时必须有 group by：
select max(avg(sal)) from emp group by deptno; （按deptno分组以后，每组内，对sal列先算平均值，再求所有组中最大的）

多表连接查询

连接查询，可以把所有的表罗列在from中，然后在where中把查询条件列出来，也可以使用join，超过两个表的连接，都可以等价转换为两个表连接的组合。
多表连接时尽量使用别名，这样能减少内存使用，提高性能。
内连接（inner join）：
连接时指定条件，两个表中符合条件的行，组成一个新行。其中的using <列名> 就相当于以这个列相等为条件做JOIN，前提是两个表都有这么个同名的列，而且类型相同。
外连接：分左连接（left outer join）、右连接（right outer join）和全外连接（full outer join），结果集除了包括两个表中符合join条件的行，对于左连接，还包括左边表不符合条件的行，对于右连接，还包括右边表不符合条件的行，对于全外连接，还包括左右两边不符合条件的行，不符合条件的行只包含一次。
Oracle的外连接也可以用where表达式表示：
例如：... where t1.col(+) = t2.col 表示结果包括t2的所有行，... where t1.col = t2.col(+) 表示结果包括t1的所有行，但是这是错误的：... where t1.col(+) = t2.col(+)。
natural join：以两表中同名同类型的列，做等值join，如果有多列同名同类型，则共同作为join条件。
join 条件也可以是非等值：
非等值可用于join on或from where
self join：
一个表自己和自己连接，可以看做两个一样的表做join查询，一般使用两个别名，也可以使用左连接或右连接。

子查询

子查询在主查询之前执行，可以出现在select、where、having、from从句中，子查询必须放在括号内，单行操作符对应输出单行结果的子查询，多行操作费对应输出多行结果的子查询。
和多行子查询搭配的表达式：in、not in、any、all、exists。
not in 表达式中如果出现null，则总是返回False，我们通常的理解相反。
多列子查询：
select empno,ename,sal,job from emp
where (job,sal) = (select job,sal from emp where empno=7788)
and empno<>7788;
等价于
select empno,ename,sal,job from emp
where job=(select job from emp where empno=7788)
and sal=(select sal from emp where empno=7788)
and empno<>7788;
多行多列子查询：
select empno,ename,sal,job from emp
where (job,sal) in (select job,sal from emp where deptno=10)
and empno<>7788;
关联子查询：
普通子查询：在主查询执行之前，子查询首先执行一次，子查询的结果要在主查询中使用。
关联子查询：需要重复执行子查询。
例如，查询比其所在部门平均工资高的员工：
select e.ename,e.sal,e.deptno
from emp e where e.sal > (select avg(s.sal) from emp s
where e.deptno = s.deptno);
对每个员工，都要执行一次子查询。
关联子查询有等价的join查询:
select e.name, e.deptno, e.sal, avg(s.sal) x from emp e join emp s on e.deptno = s.depno group by e.name, e.deptno, e.sal having e.sal > x;

create table、insert、update都可以结合子查询。

创建表

create table ... as select:
注意这里AS是必须的。
create table emp10 as select empno,ename,deptno from emp where 1=2;
not null 被继承，但 primary key、 unique、 foreigne key以及隐式的 not null(主键列)都不会被继承。
创建表时指定压缩：
作用于堆表
create table t1 (id number, name varchar2(10)) compress basic;
create table t1 (id number,name varchar2(10)) row store compress advanced;
一张表最多可以有1000个列。
创建临时表：
事务级别临时表，事务结束则删除记录，不删除表：
create global temporary table t1( a number(3), b varchar2(30) ) on commit delete rows;
会话级别临时表，会话结束则删除记录，不删除表：：
create global temporary table t1 (a number(3), b varchar2(30) ) on commit preserve rows;

修改表

alter table t1 add (a varchar2(9));
alter table t1 modify (a varchar2(15)); 修改某列的类型
alter table t1 drop column a;
alter table t1 rename column a to ename;
alter table t1 read only; 设只读不能修改表内数据，但是可以修改表定义、删除表
alter table t1 read write; 恢复读写

更改对象名称

可用于表、视图、序列、同义词等
rename dept to department;

给表增加注释

comment on table emp is 'Employee Information';
comment on column emp.deptno is 'Department Number';
查看注释的数据字典：
USER_COL_COMMETS
ALL_COL_COMMENTS
USER_TAB_COMMENTS
ALL_TAB_COMMENTS

标记列不可用

表中数据量非常大时，如果在业务高峰时间直接执行 alter table drop column
可能会收到如下错误 ORA-01562 - failed to extend rollback segment number string
Oracle 推荐使用 SET UNUSED 选项标记一列（或多列），使该列不可用（set unused 不会真地删除字段）。然后，当业务量下降后再使用 DROP UNUSED column 选项删除被被标记为不可用的列。
用这种方式删除多列时效率更高，但不可逆，无法再还原该字段及其内容。
alter table t1 set unused (a, b);
alter table t1 drop unused columns;
考点：
1）如果 set unused 某列，该列上有索引，约束，并定义了视图，引用过序列：
索引和约束自动删除，序列无关，视图保留定义。
2）无法删除属于 SYS 的表中的列，会报 ORA-12988 错误，哪怕是 sys 用户都不可以。

数据类型

参考 Data Types
字符类型：
char(n) 存储时使用空格填满空白内容，n 默认值 1，范围 1~2000字节
varchar2(n) 可变长 n 必须指定，范围 1~4000字节
以上两个字符类型中，n是字节数，不是字符数。
nchar(n) 是用两个字节表示一个字符，固定长度，n表示字符个数，最多2000字节（1000字符）。
nvarchar2(n) 也是用两个字节表示一个字符，变长，n表示字符个数，最多4000字节（2000字符）。
NCLOB、NCHAR和NVARCHAR2是Oracle特有的，专门为了高效存储和处理多字节字符的类型，一般以AL16UTF16、ZHS16GBK编码，这样一个汉字只用两个字节存储。
并不是有NCHAR可以存储多字节字符，CHAR只能存储单字节字符，CHAR和VARCHAR2也可以存储多字节字符。存储的是否是多字节字符，只取决于编码。以AL32UTF8编码的CHAR和VARCHAR2以变长方式存储多字节字符，一个汉字占3个字节。
CHAR和NCHAR的编码在创建数据库时设置，一旦创建便更改受限。
可以通过select * from nls_database_parameters;查看
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
数字类型：
NUMBER(p, s) 占1~22字节 p表示整个数字的位数，s表示小数点右侧的数字位数。
BINARY_FLOAT 占4字节，单精度浮点数（不精确）
BINARY_DOUBLE 占8字节，双精度浮点数（不精确）
其它类型是由NUMBER变化而来。
日期类型：
DATE 日期类型精确到秒，占7个字节
TIMESTAMP 时间戳类型精确到纳秒timestamp(9)，占7~11个字节
TIMESTAMP WITH TIME ZONE 比TIMESTAMP多了时区信息，占13个字节
上面是存时间点，下面是存储时间段：
如何表示时间段呢？
INTERVAL YEAR TO MONTH -- interval '120-3' year(3) to month 120年3个月
INTERVAL DAY TO SECOND -- interval '11 10:09:08.555' day to second(3) 11天10时9分8.555秒
这两个也可以变化为：
interval ‘30’ year、interval ‘30’ month、interval ‘30’ day、interval ‘30’ hour、interval ‘30’ minute、interval ‘30’ second
参考 Oracle Interval类型 -Oracle教程™

大对象：
CLOB/NCLOB 字符，(4 gigabytes - 1) * (database block size)
BLOB 二级制数据，最大(4 gigabytes - 1) * (database block size) ？？？到底多大？
BFILE 一个指针，指向存储在数据库外的，操作系统上的文件（最大4GB）
大对象有许多限制，不能出现在order by、group by里，也不能有约束。

INSERT

insert使用default：
如果表定义的列有default值，insert时，对这个列也可用default值：
insert into test values (2,default);
insert into ... select:
insert into emp1 select * from emp where job='MANAGER';
insert all ... select:
同时向多个表插入
insert all into t1
into t2
into t3
select * from t4;
insert all into t1 (c1, c2, c3)
into t2 (c1, c2, c3)
into t3 (c1, c2, c3)
select 1, 'adfdsfa', '1990-12-20' from dual;

UPDATE

update可以更新多个列：
update t1 set t1.a = 10, t1.b = 'hahah', t1.c = sysdate where t1.d = 1；
子查询更新：
update t1
set (a, b) = (select a, b from t1 where c=7499)
where c = 7698;
update t1
set a = (select a from t2 where c =7788)
where b = (select b from t2 where c = 7788);
关联子查询更新：
update emp1 e set mgr = (select sal from emp2 d where d.emp2no = e.emp2no);

delete和truncate

delete是DML，慢，会产生许多undo数据，用于回滚，可加删除条件，不修改High Water Mark，High Water Mark表示曾经占用的最大存储空间，在全表扫描时会以HWM扫描磁盘，如果HWM不降，即使数据被删除，也会扫描很多磁盘。
truncate是DDL，快，没有undo数据，无法回滚，删除全表数据，会将High Water Mark置为0。
查看表的HWM：
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = <tablename>;
一、oracle 高水位线详解 - Ruthless - 博客园

事务

在sqlplus中，不需要BEGIN显式开启事务，事务默认开始，要执行COMMIT才能提交，Oracle默认不是自动提交。
DDL语句执行完会隐式执行COMMIT。
rollback-savepoint：
savepoint实现回滚时的部分回滚，即回滚到保存点，主要用于显示rollback到指定保存点，然后继续执行事务。
Oracle过程语言PL/SQL：
定义PL/SQL代码块：
DECLARE
-- 声明变量
BEGIN
-- 调用查询语句，DML，执行流控制语句，包函数，存储过程
-- 不能直接执行DDL，可以通过包函数执行DDL，例如：
-- DBMS_UTILITY.EXEC_DDL_STATEMENT()
END;
也可以没有DECLARE部分，只有BEGIN ... END；部分，注意BEGIN和DECLARE后没有分号，END后面要有分号，定义完代码块后并不执行，输入"/"执行。
一个PL/SQL代码块默认就是一个事务，即使代码块里有COMMIT也不会真的提交，而是整个BEGIN-END作为一个事务提交时，内层的事务才真的提交保存。BEGIN-END可以嵌套，但是内层不会真的提交，还是最外层事务提交时内存事务才真正地提交。
Oracle支持三种事务隔离级别：
Read Committed
Serializable
Read Only
默认事务隔离级别是Read Committed，Serializable一致性最严格，但并发性差，Read Only不能修改数据。
参考：Oracle Isolation Level Tips

select for update

对所查询的行加写锁，例如：
select * from emp where empno=7788 for update;
如果发现所查询的行已经被其它事务锁住，则返回失败：
select * from emp where empno=7788 for update nowait;
select for update通常是为了实现所查询记录的可重复读，Oracle默认事务隔离级别是read committed，这种隔离级别，在第二次读取某条记录时，读到的值可能和第一次不同（期间被修改并提交，不可重复读），要避免这种情况，可以将隔离级别设为serializable，也可以用select for update锁住这几行记录。

函数和存储过程的定义

函数和存储过程是命名的PL/SQL块。
定义函数：
CREATE FUNCTION function_name
([arg1 [ IN | OUT | IN OUT }] type1 [DEFAULT value1]],
[arg2 [ IN | OUT | IN OUT }] type2 [DEFAULT value1]],
...... )
RETURN return_type
IS | AS
-- 声明部分
BEGIN
-- 执行部分
RETURN expression
EXCEPTION
-- 异常处理部分
END function_name;
定义存储过程：
CREATE PROCEDURE procedure_name
([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1]],
[arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],
......)
IS | AS
-- 声明部分
BEGIN
-- 执行部分
EXCEPTION
-- 异常处理部分
END procedure_name;
绑定变量：
var name varchar2(10)
exec: data1:=10;
select * from emp1 where no = :data1

merge into

类似于upsert(但是感觉不然upsert好用)，如果数据存在，则用源表的记录update对应行，如果不存在则insert，常用于将源表的记录融合到目的表，例如，将t2中新增和修改过的记录同步到t1中：merge into t1 using t2 on (t1.pk= t2.pk)
when matched then
update set t1.a=t2.a, t1.b=t2.b, t1.c=t2.c
when not matched then
insert values (t2.pk, t2.a, t2.b, t2.c);

常用数据字典

USER_：用户所有的对象的信息；
ALL_：用户拥有的和有权限操作的对象的信息；
DBA_：只有具有 DBA 角色的用户可以访问，里面包含数据库所有对象的信息；
V$_：动态性能视图（数据来自内存）、数据库服务器的性能和锁的相关信息。

约束

种类：
primary key -- 唯一非空，是记录的唯一标识
foreign key
not null
unique
check -- 用于约束列的取值
约束相关数据字典：USER_CONSTRAINTS，USER_CONS_COLUMNS
主键约束和唯一键约束需要索引，如果不存在， oracle 会自动创建索引。
可以在表定义列时定义约束，也可以用alter table 创建。
foreign key：
alter table t1 add constraint fk_a foreign key (a) references t2 (a) [on delete {cascade}|{set null}];
t2是父表（被引用表），t1是子表。
父表被引用的列必须是primary key或有unique约束。
on delete cascade -- 父表中的记录删除，子表中引用它的记录也要删除。
on delete set null -- 父表中的记录删除，子表中引用它的记录设为null。
外键约束的父表和子表也可以是一个表。
使能约束：
alter table t1 disable/enable constraint <constraint-name>
对于没有主键的表，添加主键，一定要验证主键列是否重复的。
alter table emp3 add constraint pk_emp3 primary key (no);

视图

分为简单视图和复杂视图，简单视图是对一个表建的查询；复杂视图是多表连接查询。
创建或更新视图定义：
create or replace view <view-name> as subquery [with check option] [with read only]
force -- 即使子查询的表不存在也创建
with check option 对视图插入和更细时，检查是否符合where条件。
with read only 在Oracle中，可以对视图进行增删改（本质上是更改基表），如果不想更改视图，创建视图时指定这个选项。
可以用 create or replace 的对象有： functions，procedures，packages，types，synonyms，trigger, views，没有 table，也没有 sequence。
查看编译错误信息：
select text from USER_ERRORS;
视图相关数据字典：
user_views，视图就是定义保存在user_views里的子查询。
查看视图定义：
select view_name, text from user_views where view_name = 'V_EMP1';

序列

create sequence <seq-name>
increment by n -- n为正数是递增，为负数递减
start with n
maxvalue \ nomaxvalue -- 递增，超过最大值会报错，否则一直增加（直到类型溢出？）
minvalue \ nomaxvalue -- 递减，小于最小值会报错，否则一直减少
cycle \ nocycle -- 如果nocycle，超过最大值就会报错
cache n \no cache -- n默认20，cache缓存在内存，如果崩溃退出，下次开始从缓存的最大值之后。
select <seq-name>.nextval from dual; -- 导致增加
select <seq-name>.currval from dual; -- 不会导致增加
修改序列 alter sequence，有一定限制，只能基于序列现在的值，不能修改初始值，除非重建序列。
数据字典：
user_sequences

同义词

可以给任何对象创建同义词，语法：
create [public] synonym <同义词> for <对象>
同义词分共有同义词和私有同义词，共有同义词在synonym前加public。
私有同义词 -- 只能自己访问。
公有同义词 -- 任何有权限的用户都可以访问，公有同义词不是模式对象，不能用模式名做前缀。

索引

需要索引的情况：
1. 列中的数值分布比较广（分散）
2. 列中包含大量NULL值
3. 列经常在where或JOIN中出现
4. 表数据量大，经常被访问，被访问数据量占总数据量的2%-4%
不需要索引的情况：
1. 表更新非常频繁
2. 表很小或查询数据量常常大于2%-4%
3. 列不经常出现在WHERE或JOIN或只是表达式的参数
为了优化JOIN操作，可以在JOIN条件列上创建索引。
在定义了primary key和unique后自动创建索引。
create [unique] [bitmap] index <index-name> on table (<col1>, ....)
这个索引是B树索引，有键值快速得到rowid，由rowid得到记录在磁盘中的位置。
反向索引：
create index mgr_idx on emp1(mgr) reverse;
对于键值连续的列，可以防止RAC中B树叶节点被争用（考点），例如键值20和21会分布在不同的块中。
基于函数的索引：
create index fun_idx on emp1(lower(ename)); -- 以列的函数值作为索引键值
以函数作为条件，在WHERE中查询时，用到这个索引，例如：
select * from emp1 where lower(ename) = ‘howard’;
压缩索引：
create index comp_idx on emp1(sal) compress; -- 重复键的值只存储一次，其它相同键值的记录的rowid紧挨在一起
升序或降序：
create index deptno_job_idx on emp1(deptno desc, job asc);
select rowid, dbms_rowid.rowid_object(rowid),
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid),
dbms_rowid.rowid_row_number(rowid)
from emp1 where rowid = 'AAAF7DAABAAAN6RAAI';
重建索引：
alter index ind_test_id rebuild online;
设置索引不可用：
alter index ind_test_id unusable; -- 不删除、不更新、不使用
alter index ind_test_id rebuild; -- 恢复要rebuild
设置对优化器不可见：
alter index ind_test_id invisible;
alter index ind_test_id visible;
设置了对优化器不可见后，执行计划就不走索引了，但更改表的时候，还是会维护索引的。
数据字典：
user_indexes
user_ind_columns

sqlplus常用功能

设置列宽：
col col_name for a30

在 sqlplus 中，执行脚本：
SCOTT@PROD> @/home/oracle/p1_01.sql

定义变量：
define _val = 5
删除变量
undefine _val
在SQL中，使用&<变量名>引用变量
Q1: select * from t1 where a = &_val;
如果_val没有事先定义，每次执行Q1时，都会要求输入_val的值，但如果使用&&_val引用_val，则只会要求输入一次。

sqlplus命令行中的set并不是设置变量，而是设置sqlplus相关功能：
set timing on
set timing off

SQL执行日志：
spool /home/oracle/emp1.sql
select * from emp;
spool off;

在sqlplus命令行执行shell命令，前加ho，例如：

SYS@ORCL> ho pwd
/home/oracle

howard_shooter

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
OCP SQL和sqlplus使用

从sqlplus登录，可以执行的语句五花八门，这里要注意区别3种：1. sqlplus命令，例如col col_name for a302. Oracle系统管理命令，例如startup，show parameter3. SQL命令，必须以;结尾，前两种不用以；结尾关于SQL的语法，熟悉的就不多说了，只说一些需要提醒的：伪列：rownum 记录在结果集中的位置。rowid 记录在表中的位置。序列的CURRVAL 和 NEXTVAL。USER： SELECT USER From...
复制链接

扫一扫