Num58 Oracle总结

表空间：

安装一个oracle，只有一个数据库。

ORACLE数据库的逻辑单元。 数据库---表空间 一个表空间可以与多个数据文件（物理结构）关联

一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

create tablespace itcastspace
datafile 'c:itcast.dbf'
size 100m
autoextend on
next 10m

itcastspace 为表空间名称

datafile 指定表空间对应的数据文件

size 后定义的是表空间的初始大小

autoextend on 自动增长 ，当表空间存储都占满时，自动增长

next 后指定的是一次自动增长的大小。

用户：

create user itcastuser
identified by itcast
default tablespace itcastspace

identified by 后边是用户的密码

default tablespace 后边是表空间名称

oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

用户权限：

Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。

CONNECT角色： --是授予最终用户的典型权利，最基本的

ALTER SESSION --修改会话

CREATE CLUSTER --建立聚簇

CREATE DATABASE LINK --建立数据库链接

CREATE SEQUENCE --建立序列

CREATE SESSION --建立会话

CREATE SYNONYM --建立同义词

CREATE VIEW --建立视图

RESOURCE角色： --是授予开发人员的

CREATE CLUSTER --建立聚簇

CREATE PROCEDURE --建立过程

CREATE SEQUENCE --建立序列

CREATE TABLE --建表

CREATE TRIGGER --建立触发器

CREATE TYPE --建立类型

DBA角色：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除

grant dba to itcastuser

进入system用户下给用户赋予dba权限，否则无法正常登陆

事务：

oracle不能够自动提交事务。

truncat与delete区别：

1. delete删除的数据可以rollback

2. delete删除可能产生碎片，并且不释放空间

3.truncate是先摧毁表结构，再重构表结构

(磁盘碎片，
删除一条数据，空间不会有人来占，空间碎片
truncate，整个空间被摧毁，不会出现碎片问题)

序列：

Oracle的自动增长只能够依靠序列来完成。

范例:创建一个seqpersonid的序列,验证自动增长的操作

CREATE SEQUENCE seqpersonid;

序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：

nextval :取得序列的下一个内容

currval :取得序列的当前内容

select seqpersonid.nextval from dual;

select seqpersonid.currval from dual;

在插入数据时需要自增的主键中可以这样使用

在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。

序列的管理一般使用工具来管理。

完整语法：

CREATE SEQUENCE 序列名 
[INCREMENT BY n] 
[START WITH n] 
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}] 
[{CYCLE|NOCYCLE}] 
[{CACHE n|NOCACHE}];

检查约束：

mysql中没有检查约束。

使用检查约束可以来约束字段值的合法范围。

范例：创建一张表性别只能是1或2

create table person(

 pid number(10),name varchar2(10),gender number(1) check(gender in (1,2)),birthday date

);

检查约束也可以自定义

create table person(
       pid      number(10),name     varchar2(10),gender   number(1),birthday date,constraint person_gender_ck check(gender in (1,2))
);

模糊查询和不等于语句：

在LIKE中主要使用以下两种通配符

“%”：可以匹配任意长度的内容

“_”:可以匹配一个长度的内容

排序中的空值问题：

排序 order by 经验：当排序时存在null时就会产生问题 nulls first,nulls last

--查询雇员的工资从低到高

select * from emp order by sal nulls first;

select * from emp order by sal desc nulls last ;

dual是伪表：

空值处理nvl：

select ename,nvl(comm,0),sal*12+nvl(comm,0) from emp;

Decode函数：

该函数类似if....else if...esle

select ename,decode(job,'clerk','业务员','salesman','销售','predident','总裁'
              '无业'
               ) from emp;

case when:

select t.empno,t.ename,case
         when t.job = 'CLERK' then
          '业务员'
         when t.job = 'MANAGER' then
          '经理'
         when t.job = 'ANALYST' then
          '分析员'
         when t.job = 'PRESIDENT' then
          '总裁'
         when t.job = 'SALESMAN' then
          '销售'
          else 
            '无业'
       end
  from emp t

集合操作：

并集UNION，UNION ALL：

select  * from emp where sal>1500 
UNION 
select * from emp where deptno=20

交集INTERSECT：

select  * from emp where sal>1500 
intersect
select * from emp where deptno=20

差集 Minus:

select  * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where ='MANAGER' or job='PRESIDENT';

exists和not exists关键字:

exists （sql 返回结果集为真）

not exists (sql 不返回结果集为真）

分页（Rownnum）(Oracle特有)：

--分页思想 子查询
select * from
(select e.*,rownum r from emp e) t
where t.r<11 and t.r>5

视图：

视图就是封装了一条复杂查询的语句。

视图是供查询用的，所以是read-only。

索引（适合查询，如果插入频繁，不建议使用）：

PLSQL:

[declare]
	    begin

	    [exception]
	    end;

变量的赋值： :=
判断: =

变量:

v_name  char(15); 
v_sal  number(9,2);

常量：

married   constant boolean:=true

引用变量：

%type;

引用型变量，即v_name的类型与emp表中ename列的类型一样

在sql中使用into来赋值

declare 
v_name emp.ename%type;
begin
select t.ename into v_name from emp t where t.empno = 7369;
dbms_output.put_line(v_name);
end;

记录型变量：

%rowtype

记录变量分量的引用

v_row.ename:='ADAMS';

declare
v_row emp%rowtype;
begin
select * into v_row from emp t where t.empno = 7369;
dbms_output.put_line(v_row.ename || ' ' || v_row.sal);
end;

游标（指针，结果集）：

异常：

存储过程（效率高，预编译）（重点：面试要问）：

存储过程写过吗？
存储过程写过几十行的，多的没写了

存储过程的优点，为什么要使用？

效率高，预编译

存储函数：

存储函数是给存储过程来调用的

存储函数可以在sql中来利用的

存储过程没有return

触发器（可以理解为监听器之类的，一触发就会发生的事情）：

触发器比约束强大

触发器里面不可以有事务

其他：

sql优化
尽量使用子查询，不要使用多表连接。
尽量不要用select *,要把字段写出来,提高效率。

建立索引会影响数据的存储

PLSql是过程化编程语言，是为了结合sql

不用左右连接，会出现如果有一个是空值的情况，会显示不出来该条数据。

A left join B on ....
where ...=...(+) '+'号写在相对的那一方

select ....group by ...
(select 后面有的字段 group by 都要有，除了使用函数的)

别名中间不可以加空格

oracle区分大小写

_(下划线) 是占位符

!= 不等号
<> 不等号

函数:
initcap 第一个大写
substr 截取，起始位置，0,1是一回事
replace 替换
length 长度
concat 连接

数值函数：
round 四舍五入
trunc 截取
mod 取余

mysql 取时间：select now();
oracle :select sysdate+10 from dual;
months_between (23 日期函数)

nvl(comm,0) 为空，当作0 看待

order by 始终放最后

where 后面跟的是存在的物理列

insert into emp1
select * from myemp; 用别名或者保证数据类型是一样的

create view view_minsal as select * from .....

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!