Oracle学习笔记

关于 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 开始),取几个;
计算长度用 lengthlengthb 分别为字符和字节数,字节就和编码有关了;
查找某个字符首次出现的位置 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

你可能需要魔法上网~~