Oracle学习笔记

发表于 | 分类于 | 热度 | 字数统计 8,716

关于 Oracle 这个数据库就不需要做什么解释了，名声那么大，怎么还不死都懂得，就是死贵死贵的，还是要学一学，因为企业用的还是挺多的。
然后来看一下关于它的一些基本介绍，默认端口为 1521 一般固定不用改，数据库的数据文件是 DBF 结尾。
版本有三个，结尾分别为 i（internet）、g（grid）、c（clound）；对应早期、近期、现在；
早期的就不说了，一般没人用了，现在基本用的都是 g 系列，c 系列太贵了是融合了云计算服务器，一般用 g 就够了，g 表示的网格可以简单理解为是一个集群，将多台 Oracle 服务器当做一台主机协调使用

关于SQL

准备知识，SQL92/99 标准的四大分类：

  • DML（数据操纵语言）：
    select,insert,update,delete
  • DDL（数据定义语言）：
    create table,alter table，drop table，truncate table
  • DCL（数据控制语言）：
    grant 权限 to scott，revoke 权限 from scott
  • TCL（事务控制语言）：
    commit，rollback，rollback to savepoint

SQL92/99 标准，可以说是访问任何关系型数据库的标准，基本所有的数据库都或多或少的支持;
有些地方会把 DML 再拆分，将查询独立出来形成 DQL（数据查询语言）

准备工作

安装就不说了，一搜一大把，卸载也有，Oracle 的卸载挺麻烦的，官方给的卸载工具是基本没人用，因为本身无法删除；
一般我们把它称作是 Oracle 数据库服务器，听到这个名字其实指的就是平常说的数据库，但是下面我为了省劲直接说 Oracle 了
然后就是 Oracle 其实包括两大部分，实例和数据库，并且必须通过实例才能操作数据库（SQL 语句是通过实例执行的），数据库可以理解为类，看得见的，比如前面的 DBF 文件，实例理解为对象，是看不见的。
一个数据库就是一个目录，可在安装目录下的 oradata 文件夹下查看，Oracle 创建、删除数据库是非常复杂的，需要专门的工具，经过大约 12 步才能搞定。
连接 Oracle 可以使用自带的 sqlplus (命令行，需要配环境变量)和 sqldeveloper，或者使用第三方的 PLSQL

安装完成后会创建一个超级管理员，用户名：sys，角色：dba；
Oracle 可以使用账号密码登录也可以使用用户和角色登录；
常用的普通用户还有 scott 和 hr，默认都是被锁的，注：从 12c 版本开始 scott 就没了（这其实是最早进入 Oracle 的员工的名字）

1
2
3
4
5
6
7
8
9
10
11
--  斜线前后：username/pwd，或者用角色登录：
> sqlplus / as sysdba

-- 解锁
alter user scott account unlock;

-- 设置密码
alter user scott identified by tiger;

> sqlplus scott/tiger
-- or sqlplus

上面就是一些安装后基础的设置了

基本操作

查看当前用户：show user;
查询当前用户下所有的对象(tab 表每个用户都有)：select * from tab;
执行最近一次命令（专有）：/
查询表结构：desc tName;
条件查询（其中一种，(不)包括12与18）：select * from users where age (not)between 12 and 18;
包含关系：select * from users where age (not)in (8,12);

查询不重复的内容关键字一样是 distinct ，记住了。
Oracle 中有一张叫 dual 的表，称之为哑表或者伪表，只会返回一行数据（比如查询日期：select sysdate from dual ，返回的格式反正很恶心）。
单引号一般用于日期和字符串（字符串），双引号一般用于给列设置别名（常量），字符串与字段可以使用 || 来拼接。
可以适用 spool d:/aa.sql 命令开启“日志记录”，最后执行 spool off; 这样期间执行的 sql 和结果都会保存到这个文件；与之对应的是读取 sql 文件执行命令：@ d:/a.sql; 。
适用条件查询不等于某个数可以使用 a != 30 也可以使用 a <> 30 ；然后 Oracle 中的 where 好像是从右往左进行判断，MySQL 的企业版好像也是（关于效率，现在的版本会自动优化，先去识别筛选最有利的条件）。
占位符和 MySQL 是一样的，下划线表示一个，百分号表示零或者多个，需要查询下划线的用转义：like 'abc\_' escape '\' ;
判断字段是否为 null 只能使用 is (not) null ，它不能参与精确比较，null 可以是任何类型，并且在排序的时候把 null 看做为最大的值；not 最好不要常用，能用其他代替的就不用，会影响效率。

格式化日期显示可以设置下环境变量：nls_date_format=YYYY-MM-DD HH24:MI:SS

常用函数

并不保证在其他的数据库也能适用…..
函数分为两类，单行函数（一进一出）和多行函数（多进一出），也就是传入参数个数的问题；
用到了去查 API 也可以

  • NVL
    用法：NVL(a,b)，如果 a 为 null 就返回 b，如果 a 不为 null 就返回 a

  • NVL2
    用法：NVL2(a,b,c) ，如果 a 不为 null 则返回 b ，否则取 c

  • NULLIF
    用法：NULLIF(a, b) ，在类型一致的情况下，如果 a 和 b 相同则返回 null ，否则返回 a

  • case…end 表达式
    SQL 99 标准里的所以 Mysql 也是支持的，用法：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select name, age,
      case age
        when 12 then age+1
        else age+2
      end "测试"
    from users;

    case 字段
      when 条件1 then 表达式1
      else 表达式2
    end

    这个…..我倒是真没用过，这个主要是用于显示，并不会进行写入

  • decode
    这个函数就是优化了上面的 case 表达式，是 Oracle 专用

    1
    2
    3
    select name, age,
      decode(age,12,age+1,age+2)
    from users;

    也就是说，decode 的第一个参数是字段最后一个是 else 的表达式，中间可以拼多个 when

单行函数

常用的有：lower / upper / initcap ；分别对应为转小写、转大写、首字母大写；
select upper('aaaabv') from dual;
截取字符串使用 substr 函数，两个参数为：从第几个开始取（从 1 开始），取几个；
计算长度用 length 或 lengthb 分别为字符和字节数，字节就和编码有关了；
查找某个字符首次出现的位置 instr(‘hello’, ‘h’) ，返回的坐标是从 1 开始，找不到返回 0 。
select trim('o' from 'ooabcodoo') from dual; ：去掉两边的指定字符。
select replace('abcd','a','t') from dual; ：把 abcd 中的 a 替换为 t。
对数字的处理常用的有 round / trunc / mod 分别对应四舍五入、截取、取余(相当于n1/n2)，需要传入两个参数，第二个是精度，前两个可以用在日期类型上，并且日期是可以直接进行简单的四则运算的（sysdate + 1 就是加一天）。
对日期处理的函数有 add_months / next_day / last_day(最后一天) ，日期的运算是可以负数，就是前一天或者上一个月了

多行函数

最常见的多行函数就是 count(*) 了吧（这是SQL查行数的标准，内部会优化），因为会扫描所有字段，这也是不推荐用的原因，还不如用 count(1)，或者用主键，还可以使用类似 count(distinct dep) ；
max / min / sum / avg 这些就更不用说了，常用的，不要忘了使用分组：

1
2
3
4
5
select deptno "部门编号" ,trunc(avg(sal), 0) "部门平均工资"
from emp
group by deptno
having trunc(avg(sal), 0) > 2000
order by 2 desc;

也是复习下 SQL，虽然和多行函数关系不是很大，记得 order 是放在最后的，因为 where 是先执行而 having 是后执行，所以能用 where 就用 where，避免查询不必要的数据；
如果使用 order by ，那么 select 中的字段必须出现在 order by 中，但是反过来在 order by 中出现的不一定要在 select 中

类型转换

隐式的转换自然就不需要说了，关键是显式的转换

  • 日期转字符串
    示例：select to_char(sysdate, 'yyyy"年"mm dd day hh24:mi:ss') from dual; 不区分大小写
  • 数值转字符串
    示例（比如货币）：select to_char(1234, 'L9,999') from dual; ，9 表示的是数字
  • 字符串转日期
    和上面差不多，就是倒过来使用：select to_char('2017-11-06', 'yyyy-mm-dd') from dual;

多表查询

最简单的笛卡尔集表（列数之和，行数之积，其中很多数据都是不合理的）：
select * from emp,dept;
其实这种方式在 SQL 标准中叫做交叉连接（CROSS JOIN） ，标准的语法是：SELECT * FROM emp CROSS JOIN dept;。

还有就是子查询，那个和 MySQL 基本一致就不多说了，可以用在 select 后也可以用在 where 后，非常的灵活；关于连接：

  • 内连接查询
    只能查询出符合条件的记录（使用 where 对笛卡尔表进行过滤）
  • 外链接查询
    既能查询出符合条件的记录，也能根据一方强行将另一方查询出来
    特别的提一下全（外）连接：FULL OUTER JOIN…ON; –> 把两张表中没有的数据都显示

然后就来看看左外连接/右外连接吧，下面的这个方法是 Oracle 专用，总结一下就是：那边的数据少就在那边写 (+) 但是只能写在最后，从命名上，如果左边的数据多就叫左外连接….
按照这个规则，那么可以总结为：

  • (+)=：放在了等号的左边，表示的是右连接；
  • =(+)：放在了等号的右边，表示的是左连接；

其实并不需要刻意的区分左和右，根据查询的结果如果发现有些需要的数据没有显示出来，就使用此符号更改连接方向就行了。

1
2
3
4
select dept.deptno "部门号" , dept.dname "部门名", count(emp.empno) "人数"
from dept,emp
where emp.deptno(+) = dept.deptno
group by dept.deptno, dept.dname;

然后就是比较难的自连接，只要设置好别名，一切都不是问题。
涉及到子查询的，又分为两类：

  • 单行子查询
    子查询只会返回一个结果，父查询使用 = 、<>、>=、<= 来连接
  • 多行子查询
    子查询会返回多个结果，父查询使用 in、any、all 这些符号来比较

平时尽可能的使用多表查询（最终是查的一张笛卡尔表），效率相对子查询高一些，但是总的来说 多表查询的性能肯定不高

更多参考：https://www.cnblogs.com/mchina/archive/2012/09/07/2651568.html

优化

首先，确定下他们之间的优先级：
from 优先级最高；然后是 where 的优先级次之，group by 的优先级仅次于 where；having 紧随 group by 之后；
order by 写在最后的，优先级是最低的；select 的优先级仅仅是高于 order by。
对于进行连接查询的，首先进行连接然后再对结果进行 where 过滤，所以在连接之前尽量使用 ON 后的条件进行筛选。

having 的效率极低，能不用就别用，前面尽量用 where 筛选好。

因为 in 的效率低，所以用 exists 代替；in 的效率低是因为需要对后面的内容进行一一匹配，当然 exists 并不是完全能替代 in，只是尽量少使用 in（not in）：
select * from school s where exists (select * from student st where st.sid = s.id);

什么时候使用多表连接？什么时候子查询？

  • 如果需要查询的数据在多个表中，一定要使用多表连接
  • 不需要表 A 中的列，但是又有该表 A 的条件，可以用子查询
  • 子查询中如果使用了 in、some any、all 这几个关键字，效率比较低，可以考虑转换成多表关联的方式

稍微总结一下就是：

  • 建议不用 * 来代替所有列名
  • 用 truncate 代替 delete
  • 在确保语句完整性的情况下多用 commit 语句（用在 begin..end 中）
  • 尽量减少表的查询次数（少用子查询）
  • 用 not exists 代替 not in
  • 表连接时，把能过滤多数据的连接放在右边，执行方式是先 ON 后 where，从右往左执行（新版会自动识别，并不需要刻意排序）
  • 合理使用索引
  • sql 语句尽量用大写的，oracle 总是先解析 sql 语句，把小写的转换成大写的在执行
  • 连接多个表时尽量使用表的别名，减少解析时间
  • 优化 group by，将不需要的记录在 group by 之前过滤掉

SQL 的效率查询，也就是慢查询：

1
2
3
4
5
6
7
SELECT EXECUTIONS,DISK_READS,BUFFER_GETS, 
     ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) HIT_RADIO,
     ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXT 
FROM V$SQLAREA 
WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0 
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY 4 DESC;

Mysql 中也有类似的命令，通过这个来排除那些执行慢的 SQL。

集合查询

关键字：
并集：union（只取相交部分的一个）、union all (相交部分的全部，即使重复)
交集：intersect
差集：minus（在第一个并且不在第二个中的）
这些关键字是用在两条查询语句中的，也就是连接两条 select 语句的。
需要注意的有：

  • 集合操作时，必须保证集合的列数是相等的
  • 集合操作时，必须保证集合的列类型是对应相等的
  • 多个集合操作时，结果的列名由前一个集合决定
  • A UNION B UNION C = C UNION B UNION A

从选择来说，优先级：多表查询 –> 子查询 –> 集合查询

分页

在 Oracle 中没有 limit 关键字，分页还是蛮复杂的；
说到这里就得说 Oracle 中的关键字（隐藏列） rownum，它可以当做一个字段写在 select 语句中，与表同存在，并且是一直递增（在一次的查询中，从 1 开始），是 number 类型，能参与运算；
需要注意的是 > 、 >= 、 = 都是无值的（0 除外，>=1 也可以），因为 rownum 是随着记录变化的，后面的可能是无限，无法确定的就返回空，并且 <> 和 < 是一样的。也就是说它只能参与小于或者小于等于的运算。
使用子查询进行分页(查询 2-8 的记录)：

1
2
3
select xx.*
from (select rownum id ,emp.* from emp where rownum <= 8) xx
where id >= 2;

注意：子查询中加别名不能使用双引号了。

分页查询中，不要直接使用 order by 进行排序，这样会打乱 rownum 的顺序，如果确实需要按照某个字段来排序，那么只能使用嵌套子查询，比如：
select rownum,a.* from (select rownum rn,name from (select * from test order by name) where rownum<=30) a where rn>=21;

PS：还有一个隐藏列是 rowid ，它映射每一行数据物理地址的唯一标识，通常适用于删除完全重复的数据。
例如：delete from lyric where rowid not in (select min(rowid) from lyric group by content);

批量操作

基本和 MySQL 保持一致吧，或许是 SQL99 的标准吧；
参考其他表结构来创建表，但是不要数据：

1
2
create table emp as
select * from back_emp where 1 <> 1;

其他的有批量从其他表插入数据的，当然要保证列的类型能对应才行，都写在下面吧：

1
2
3
4
5
6
insert into emp 
select * from back_emp;

-- 选择添加
insert into emp(id,name) 
select id,name from back_emp;

基本都是一个套路，差不多的

关于事务

事务的开始不需要手动执行某条语句（MySQL 是 start transaction），它默认是从第一条 DML 操作（增删改查）作为事务的开始。
提交就是直接输入 commit ，回滚就是 rollback 都一样的了。
关于事务的提交，除了使用上面的显式 commit，还可以是 DDL/DCL/exit（sqlplus 工具） 语句，也就是说当你执行一条 DDL 语句默认会先进行事务提交。
同样也是有隐式回滚的，当关闭窗口、死机、停电等情况时就会进行自动的回滚。

下面就是回滚点的知识了，让我们能回滚到一个事务中的指定位置，看个栗子就知道了：

1
2
3
4
5
6
7
8
9
10
11
select * from emp;
delete from emp where id = 1;

savepoint a; -- 设置回滚点
delete from emp where id = 2;

rollback to savepoint a;
commit;

rollback;
commit;

回滚点多了也不好，起码会占用空间啊，MySQL 也是一样的；
隔离级别我就不说了，前面说过，Oracle 只支持两个

表操作

介绍常用的表修改操作，比如修改表名、增加字段啥的：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 如何修改已经创建的表名
alter table student rename to teacher;

-- 建表以后 增加字段
alter table student add email varchar2(20);

-- 建表以后 修改字段名
alter table student rename column email to yx;

-- 建表以后 修改字段数据类型
alter table student modify name varchar2(10);

-- 建表以后 删除字段
alter table student drop column yx;

-- 建表以后 删除表
drop table student purge;
truncate table student;

关系比较中 <> 是标准语法，可以移植到其他任何平台，!= 是非标准语法，可移植性差。

约束相关

最简单的，约束可以分为这几类：
非空：not null
唯一：unique
检查：check
外键约束：foreign key （references）
主键约束：primary key
举个例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table class(
  id number(3) primary key,
  name char(6) not null unique,
  qq varchar2(20) check(length(qq)>=8),
  tid number(3) references teacher(id)
);

-- 如何添加约束：
alter table test add constraint aa  primary key(tid);

-- 如何查看已经存在的约束：
select constraint_name,constraint_type from user_constraints
where table_name = upper('test');

--如何删除约束：
alter table test drop constraint SYS_C0011126;

创建外键的时候，FOREIGN KEY 关键字是可以省略的，直接写 REFERENCES 。

序列

sequence 一个单独的数据对象，是一个能够生成有序的整数列值的对象；oracle 通过调用序列的形式来实现主键自增。
关于序列的一些常规操作：

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
create sequence seq_test
increment by 1/-1  --增长，一次增1   正数+1  负数-1
start with 1       --从1开始    升序默认就是+1  降序-1
minvalue 1         --最小值     -10的26次幂
maxvalue 100       --最大值     10的27次幂
cycle              --循环       默认 nocycle
nocache;           --不缓存     默认生成20个序列号


-- 查询当前用户下有多少序列
select * from user_sequences;

-- 如何获取下一个列的值
insert into test values(seq_test.nextval,'g');

-- 如何获取当前序列的值
select seq_test.currval from test;

-- 如何删除序列
drop sequence seq_test;

-- 如何修改序列
alter sequence seq_test increment by 50;

-- 消除延迟段创建特性
alter session set deferred_segment_creation=false;

create table name(id number) segment creation immediate;

关于最后一个 消除延迟段创建特性 ，这是因为在 11g+ 的版本中有了一个新特性，它会导致序列直接从 2 开始，尚不知道有什么用处，解决方案可以在建表的时候就指定立即使用序列。

视图

视图 view 可以简单理解为是一张假表，用查询的结果动态生成一张表。
视图是编译后将查询语言保存到数据库中，下次调用视图，可以不用在编译，直接执行。
创建一个简单的视图：create view 视图名 as select * from student;
为什么要使用视图？

  • 节省编译时间，提高查询效率
  • 屏蔽原表中的字段：避免没有权限的用户查询其他的字段（看到不该看的）
  • 视图中能够根据原表的状态动态刷新
  • 简单的视图是可以更新（插入等）原表中的数据
  • 复杂的视图无法更新（插入等），因为涉及到多个表

需要注意的一点是创建视图后也许并不会马上进行编译，或者后来表结构发生了变化视图并不会进行更新，如果想立即生效可以尝试进行手动更新：alter view 视图名 compile;

索引

数据库会在具有唯一性的列上自动添加唯一性索引，索引包含：普通索引（normal）、唯一索引（unique）、位图索引（bitmap）、函数索引。
正常操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建索引
create index 索引名 on 表名(字段);

-- 查询索引
select index_name,table_name,uniqueness,status
from
user_indexes
where table_name = 'STUDENT';

-- 修改索引
alter index index_name rename to new_index_name;

-- 删除索引
drop index 索引名;


-- 唯一性索引
create unique index 索引名 on 表名(列名);

-- 位图索引（分类），这种索引适合用在数据量比较大，基数比较小的列（比如：男/女）
create bitmap index 索引名 on 表名(列名);

-- 函数索引：在一个列上经过函数计算后的结果上创建索引
create index 索引名 on 表名(函数(列名));

创建索引的优缺点：

  • 能够更快的帮助我们进行提高查询效率
  • 增删改表中的数据，数据库就需要耗费资源去维护索引，降低增删改的效率
  • 数据量如果很少，没必要用索引
  • 数据量比较大，不需要经常增删改操作而且查询比较多，适合使用索引

存储过程&触发器

什么是存储过程，简单说就是在服务器端，能够被一个或多个应用程序调用的一段 sql 语句集（已被预编译）。
存储过程的创建等操作和 MySQL 中也大同小异，看些例子：

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
-- 创建/覆盖存储过程
create or replace procedure
过程名（参数名 in 参数类型，参数名 out 参数类型）
as
变量名 变量类型 := 值;
begin
sql语句集;
end;

create or replace procedure
pro_hi(mykey in number,value out varchar)
as
begin
if mykey = 1
then value := '你好';
else if mykey = 2
then value := '再见';
end if;
end if;
end;
/

-- 调用存储过程
declare 
变量 类型:=初始值;
begin
过程名（参数，变量）;
end;


set serveroutput on;
declare
val varchar2(20):='';
begin
pro_hi(1,val);
dbms_output.put_line(val);
pro_hi(2,val);
dbms_output.put_line(val);
end;

其中 := 的意思是赋值，存储过程可以没有参数，如果没有参数则过程名之后不能出现括号。

触发器是在数据库中，在执行对数据有异动的动作时，先行拦截并处理的一种数据库对象，它大部份会设在数据表中，作为强制运行特定动作的程序，因此又称为数据操纵语言触发器

或者可以理解为执行某个操作时自动触发执行存储过程？比如用来日志的记录和主键的自动增长。
触发器可具体分为行级触发器和表级触发器，具体的简单使用：

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
-- 创建触发器：
create trigger 触发器名
before/after  insert/update/delete  on 表名
for each row
begin
sql语句集;
end;

-- 创建一个行级触发器，当执行插入操作时，自动查询序列的下一个值
-- 将查询到的值赋给表中的 id 列
create or replace trigger tri_insert_good
before insert on good
for each row
begin
select seq_ids.nextval
into:new.id
from dual;
end;
/
-- 增加数据
insert into good (good_name) values ('loli');

--创建表级触发器，用于记录日志
create trigger test_dept
before delete or insert or update on dept
declare var_tag varchar2(20); --声明一个变量
begin
if inserting then
var_tag :='insert';
elsif updating then
var_tag :='update';
elsif deleting then
var_tag :='delete';
end if;
insert into test_log values(var_tag,sysdate);
end;
/

使用 elsif 的形式末尾只需要一个 end if 就可以了，如果使用的是 else if 的形式，那么有多少个 if 就要对应多少个 end if。

其他

默认情况下，删除表会放进回收站，通过命令 show recyclebin; 可以查看回收站。
还原表：flashback table name to before drop; 或者 flashback table name to before drop rename to newName;
彻底删除表：drop table users purge;
清空回收站：purge recyclebin;

数据类型中的 varchar2(num) 中的 num 指的是字节数。
关于约束，Oracle 有专用的 check 约束
如果使用 sqlplus 工具进行连接，还可以使用占位符，它会提示你输入相关信息，占位符用 & 符号加描述就可以了，用的应该也不是很多

关于清空表数据可以用传统的 delete 语句，它是从上往下删，速度比较慢，属于 DML 语句，可以进行回滚；
还可以使用表截断：truncate table ，速度快属于 DDL，不可回滚，不支持 where。
还有就是 drop 语句了，这个干的很彻底，属于 DDL，如果删错了，那就跑路吧。drop table student purge;
删除空数据的用 where xx is null ，因为不确定不能用 = 号

查看自己的权限：select * from user_sys_privs;

表空间

oracle 中的用户都有属于自己的默认的空间，在一段内存空间中大部分存储的是表，所以称为表空间。
表空间可简单的分为：系统用户的表空间和普通用户的表空间。
那么，为什么要创建用户的表空间呢？
项目中很可能与其他项目使用同一个数据库，多个用户在使用同一个数据库的时候有可能访问同一个数据库文件，就会造成资源争用问题，给不同的用户指定不同的表空间，就可以让他们使用不同的数据文件，解决争用问题。
数据最终存储在数据块中，从小往大的顺序是：数据块=>盘区=>段=>数据文件=>表空间。
相关操作：

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
/* 第1步：创建临时表空间  */
create temporarytablespace user_temp  
tempfile 'D:\app\user_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  

/* 第2步：创建数据表空间  */
create tablespace user_data  
logging  
datafile 'D:\app\user_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  

/* 第3步：创建用户并指定表空间  */
create user username identified by password  
default tablespace user_data  
temporary tablespace user_temp;  

/* 第4步：给用户授予权限  */
grant connect,resource,dba to username;


-- 创建用户后修改
alter user et1803 default tablespace 表空间名;

-- 删除表空间
drop tablespace 表空间名 including ontents and datailes;

删除表空间后，原先指向该表空间的用户仍然默认的空间位置，需要通过 alter user 命令将用户的表空间指向一个有效的表空间。

truncate 、delete与drop区别

https://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html

相同点：

  1. truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据。
  2. drop、truncate 都是 DDL 语句(数据定义语言)，执行后会自动提交。

不同点：

  1. truncate 和 delete 只删除数据不删除表的结构(定义)，而 drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
  2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
    truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
  3. delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动；
    drop 语句将表所占用的空间全部释放。
    truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。
  4. 速度，一般来说: drop> truncate > delete
  5. 安全性：小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及
  6. delete 是 DML 语句,不会自动提交。drop/truncate 都是 DDL 语句,执行后会自动提交。
  7. TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
    DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
  8. TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。
  9. TRUNCATE TABLE 不能用于参与了索引视图的表。

附:Select 执行顺序

http://www.cnblogs.com/likeju/p/5039128.html

全文的链接在上面，这里摘出其中的一点：注意多表连接的连接条件的选择与表示。
多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别注意。

  • 多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。
  • 连接条件尽量使用聚集索引
  • 注意 ON、WHERE 和 HAVING 部分条件的区别：
    ON 是最先执行， WHERE 次之，HAVING 最后；
    因为 ON 是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，WHERE 也应该比 HAVING 快点的，因为它过滤数据后才进行 SUM

考虑联接优先顺序：

  • INNER JOIN
  • LEFT JOIN (注：RIGHT JOIN 用 LEFT JOIN 替代)
  • CROSS JOIN

其它注意和了解的地方有：

  1. 在 IN 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数
  2. 注意UNION和UNION ALL的区别。–允许重复数据用UNION ALL好
  3. 注意使用DISTINCT，在没有必要时不要用
  4. TRUNCATE TABLE 与 DELETE 区别
  5. 减少访问数据库的次数

查询语句的执行顺序：
1、FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
2、WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。
3、GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
4、HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。
5、SELECT子句：少用号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将号依次转换成所有的列名, 消耗时间。
6、ORDER BY子句：执行顺序为从左到右排序，消耗资源。

附:函数整理补充

单行函数：

  • ceil()：返回大于等于x的最小整数
  • floor()：返回小于等于x的最大整数
  • round()：四舍五入
    round(a1,a2) 保留指定位小数位的四舍五入
  • trunc()：直接截断
    trunc(a1,a2)：保留指定位数的小数
  • sign()：求符号位 正数：1 负数：-1 零返回0
  • power(a,b)：求a的b次方
  • sqrt()：求正平方根

转换函数：

  • to_number(C)：将一个字符类型的数字变成数值类型
  • to_char()：将数字转换为字符串
    常用在货币单位，格式化字符串
    日期转换：to_char(日期，'yyyy-MM-dd HH:mm:ss') OR to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff3')
  • to_date(c1,c2)
    c1:字符类型的日期
    c2:格式，例如： to_date(‘2018-03’,’yyyy-mm’)
    日期可以加减（整数）运算 ，单位是：天，但是日期没法相加
  • months_between(c1,c2) 计算两个日期之间的月份，就是 c1-c2
  • last_day(c1) 计算给定日期的所在月份的最后一天
  • next_day(c1,c2) ，距离周几最近的日期
    c1:日期
    c2:周中的某天

字符函数：

  • lower():转换成小写
  • upper():转换成大写
  • initcap():首字母大写
  • length():求长度
  • substr(c1,c2,c3) 截取字符串
    c1:被截取的字符串
    c2:从哪个位置开始截取
    c3:截取长度 默认截取到最后
  • instr(c1,c2,c3,c4) 索引字符串
    c1:被查询的字符串
    c2:希望找到的字符
    c3:从哪个位置开始找 默认是1
    c4:第几次出现
  • concat(c1,c2) 拼接字符串
  • lpad(c1,c2,c3) 左侧补全
    c1:希望补全的字符串
    c2:补全到多少位
    c3:以哪个字符来补全
  • rpad(c1,c2,c3) 右侧补全
  • trim(c1) 默认c1的两侧去除空格
    trim(c1 from c2) 把c2的两侧移除指定的c1
  • ltrim(c1,c2) 左侧去除
    c1:被去除的字符串
    c2:去除的字符串 默认是空格
  • rtrim(c1,c2) 右侧去除
  • replace(c1,c2,c3) 完全替换
    c1:原字符串
    c2:被替换的字符串
    c3:替换的字符串

通用函数：

  • nvl()：空值处理
    nvl(字段，替换显示的内容)
  • nvl2():空值处理二代
    nvl2(字段，不是空显示什么，是空显示什么)
  • wm_concat(column) 字段合并
    select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum from name group by u_id
    用法详细展示：http://www.cnblogs.com/yangxia-test/p/4272493.html

用于 to_char(numeric) 的模板 ：

模板描述
9带有指定位数的值
0前导零的值
. （句点）小数点
, （逗号）分组（千）分隔符
PR尖括号内负值
S带负号的负值（使用本地化）
L货币符号（使用本地化）
D小数点（使用本地化）
G分组分隔符（使用本地化）
MI在指明的位置的负号（如果数字 < 0）
PL在指明的位置的正号（如果数字 > 0）
SG在指明的位置的正/负号
RN罗马数字（输入在 1 和 3999 之间）
TH or th转换成序数

例子：

输入输出
to_char(now(),’Day, HH12:MI:SS’)'Tuesday , 05:39:18'
to_char(now(),’FMDay, HH12:MI:SS’)'Tuesday, 05:39:18'
to_char(-0.1,’99.99’)' -.10'
to_char(-0.1,’FM9.99’)'-.1'
to_char(0.1,’0.9’)' 0.1'
to_char(12,’9990999.9’)' 0012.0'
to_char(12,’FM9990999.9’)'0012'
to_char(485,’999’)' 485'
to_char(-485,’999’)'-485'
to_char(485,’9 9 9’)' 4 8 5'
to_char(1485,’9,999’)' 1,485'
to_char(1485,’9G999’)' 1 485'
to_char(148.5,’999.999’)' 148.500'
to_char(148.5,’999D999’)' 148,500'
to_char(3148.5,’9G999D999’)' 3 148,500'
to_char(-485,’999S’)'485-'
to_char(-485,’999MI’)'485-'
to_char(485,’999MI’)'485'
to_char(485,’PL999’)'+485'
to_char(485,’SG999’)'+485'
to_char(-485,’SG999’)'-485'
to_char(-485,’9SG99’)'4-85'
to_char(-485,’999PR’)'<485>'
to_char(485,’L999’)'DM 485
to_char(485,’RN’)' CDLXXXV'
to_char(485,’FMRN’)'CDLXXXV'
喜欢就请我吃包辣条吧！

评论框加载失败，无法访问 Disqus

你可能需要魔法上网~~