Oracle笔记三

DDL语句操作表

创建表空间

一个表空间可以关联多个数据文件
create tablespace 表空间名 datefile ‘路径’ size 100m autoextend on next 10m

创建用户及权限

创建用户时可以指定表空间
create user name identified by password default tablespace 表空间名
权限如下
connect，基本权限
resource,开发人员权限
dba，所有权限
可以进入system用户下给其他用户赋权
如grant dba to 用户

创建表

create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person (pid,name,gender,birthday) values
(1,‘小明’，1，to_date(‘1990-01-01’,‘yyyy-MM-dd’));

修改表

1.添加字段
alter table person add (address varchar2(10));
2.修改字段名
alter table person rename pid to user_id;
3.修改字段属性
alter table person modify (name varchar2(20));

删除表

drop table person

约束

约束类型

主键约束（Primary Key），
非空约束(Not Null)
唯一约束(Unique)
外键约束(Foreign Key)
检查性约束(Check)

主键约束

一般建表时把id指定为主键，而且默认不为空。
create table person(
pid number(10) primary key,
name varchar2(10),
gender number(1) default 1,
birthday date
);

非空约束

指定的字段不可以为空
create table person(
pid number(10) not null,
name varchar2(10) not null,
gender number(1) default 1,
birthday date
);

唯一约束

唯一约束的指定字段内容是唯一的
create table person(
pid number(10),
name varchar2(10) unique,
gender number(1) default 1,
birthday date
);

检查约束

可以约束字段值的合法范围
create table person(
pid number(10),
name varchar2(10),
gender number(1) check(gender in (1,2)),
birthday date
);

外键约束

两张表共同有某个字段，即表数据有关联。此时在往两张表里插入数据的时候需要注意，比如给子表插入数据时，插入的数据对应的字段，需要在主表中也有该字段才可以。
这种约束叫做外键约束。

外键一定是主表的主键，在子表里是外键。
删表时先删子表，再删主表，否则由于约束无法删除。
也可以强制删除，但是不建议。

DML语句操作数据

插入数据

insert into person (pid,name,gender,birthday) values
(1,‘小明’，1，to_date(‘1990-01-01’,‘yyyy-MM-dd’));

更新数据

update 表名 set 列名1=值1, 列名2=值2,…where 修改条件;

删除数据

delete from 表名 where 删除条件;
truncate table也可以删除表数据
truncate与delete区别
truncate 先摧毁表结构，再重建表结构
delete删除的数据可能产生碎片，并且不释放空间
delete 删除的数据可以rollback，也可以闪回。

事务

数据库变更，提交事务后数据才真正插入到数据库中；
数据库变更后，未提交事务前，可以回滚，数据就不会插入到数据库；
数据库变更后，已提交事务，不可以回滚。
提交：commit
回滚: rollback

管理其他数据库对象

视图

视图就是封装了一条复杂查询的语句
视图是一个虚表
最大优点是简化复杂的查询
create view empvd20 as select * from emp t where t.deptno=20;
创建完视图后，可以直接使用该视图进行搜索操作，不建议通过视图对表中数据修改，限制比较多。
select * from empvd20;

序列

mysql中有自动增长的列，oracle里没有。
oracle只能通过序列，并在插入数据时使用nextval来完成自动增长功能
create sequence dept_deptid-seq
increment by 1
start with 10
maxvalue 999
nocache
nocycle;

insert into person values(seqpersonid.nextval,‘name’,1,null,‘address’)

索引

索引用于加速数据存取的，像目录一样
单列索引是对单个列建立索引
create index 索引名 on 表名(列名)
create index pname_index on person(name);
复核索引是对多个列建立索引
在同一张表上可以有多个索引，但是要求列的组合必须不同
create index pname_gender_index on person(name,gender);
测试：
建表
create table t_test(tid number,tname varchar2(30));
建序列
create sequence seq_test;
插入数据
begin
for i in 1…5000000
loop
insert into t_test values(seq_test.nextval,‘测试数据’||i);
end loop;
commit;
end;
普通查询并记录查询时间（大概几秒）
select * from t_test where tname= ‘测试数据4567890’;
建索引查询并记录时间（大概零点零几秒）
create index index_text on t_test(tname);
select * from t_test where tname=‘测试数据4567890’;

同义词

可以缩短对象名字长度
可以访问其他用户的数据库对象
create public synonym emp for scott.emp;
select * from emp;
drop public synonym emp;

数据导入导出

文件格式

1.二进制的.dmp格式，可以跨平台，包含权限，用的最广。
2.可用文本编辑器查看的.sql文件，适合小数据量导入导出。
3.PL/sql developer自有的文件格式.pde，只能用pl/sql developer自己导入导出，不能用编辑器查看。

PL/SQLDEV工具导出导入

工具导出：

1.tools->export user object选择选项，导出.sql文件
2.tools->export tables->Oracle Export 选择选项导出.dmp文件

工具导入：

1.tools->import tables->SQL Inserts导入.sql文件
2.tools->import tables->Oracle Import 然后再导入dmp文件

使用命令导出导入

整库

exp system/密码 full=y
导出后会在当前目录下生成一个叫EXPDAT.DMP的备份文件
如果指定备份文件名，则
exp system/密码 file=文件名 full=y
整库导入
imp system/密码 full=y
如果按指定备份文件导入
imp system/密码 full=y file=文件名

用户

exp scott/tiger file=001.dmp
imp scott/tiger file=001.dmp

表

exp scott/tiger file=001.dmp tables=t_person,t_student
imp scott/tiger file=001.dmp tables=t_person,t_student