Oracle数据库相关操作

因为关于数据库的操作少用，所以很容易忘记一下命令。在这里记下来方便以后查找：
查询当前数据名：select name from v$database;
在Oracle中：
双引号的作用是：假如建立对象的时候，对象名、字段名加双引号，则示意 Oracle将严格区分大小写，否则Oracl都默认大写。
而单引号则示意：这个加了单引号的字段是一个字类似字符串，并不区分大小写。
如果在导入sql脚本@oracle.sql的时候出现“无效的月份”的错误提示，可以通过以下两行命令中的一个搞定（我的英文系统执行的是第一个）：
执行“alter session set nls_language=american”。
执行“alter session set nls_date_format='DD-MON-YY'”，也可以自定义格式。
使用子查询更新数据：
让scott的岗位，工资，补助和smith员工一致：
update emp set (job,sal,comm) = (select job,sal,comm from emp where ename = 'SMITH') where ename = 'scott';
创建一个用户： create user july identified by snaillocke;
以dba身份授权给新建用户july：
conn sys/snaillocke as sysdba;
grant connect to july;
如果对表空间 'USERS' 无权限：grant resource to july;
Oracle约束：
创建表：
create table goods(
goodsId char(8) primary key,
goodsName varchar2(30),--30代表最大字符数为30，一个中文两个字符
unitprice number(10,2) check (unitprice >0),--单价大于0
category varchar2(8),
provider varchar2(20));

create table customer(customerId char(8) primary key,
name varchar2(50) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(2) default '男' check(sex in ('男','女')),--男或女，默认为男
charId char(18) );

create table purchase (
customerId char(8) references customer(customerId),
goodId char(8) references goods(goodsId),
nums number(5) check (nums between 1 and 30));--数量在1到30之间
创建表从另一个表中取数据过来：create table emp2 as select * from emp;
维护表：
如果在建表的时候忘记了对表建立约束，可以使用alter table命令为表增加约束，需注意：增加not null约束时，需要使用modify选项，而增加其他四种约束使用add：
alter table goods modify goodsName not null;
alter table customer add constraint card_unique unique(charId);
alter table customer add constraint address_check check (address in( 'guangzhou','beijing','shanghai'));
增加一个字段：alter table test add name varchar2(320);
增加主键约束：alter table test add primary key(id);
删除约束：
alter table 表名 drop constraint 约束名称；
在删除主键约束时，如果在两张表上存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项，如：
alter table 表名 drop primary key cascade;
查看约束信息：
通过数据字典视图user_constraints可以显示当前用户所有的约束的信息：
select constraint_name,constraint_type,status,validated from user_constraints where table_name ='表名';
通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息：
select column_name ,position from user_cons_columns where constraint_name='约束名';
表级定义和列级定义：
在定义外间的时候，列级定义不用加上foreign key，但是在表级定义中要加上foreign key。（不确定）
索引：
目的：加快检索速度；
create index 索引名称 on 表名（列名1，列名2）;列名是有顺序的。
建立索引的原则：
  在大表上建立索引才有意义；
  在where子句或者连接条件上经常引用的列上建立做引
  索引的层次不应该超过4层；
建立索引的代价：
1，建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引；
2，更新数据的时候，系统必须有额外的时间来同事对索引进行更新，以维持数据和索引的一致性。
一下情况不应该建立索引：
1，很少或者从不使用的字段；
2，逻辑型的字段，如男或女（是或否）等。
索引的管理：
显示表的所有的索引：
select  index_name,index_type from user_indexes where table_name = '表名';
显示索引列：
select table_name,column_name from user_ind_columns where index_name = 'IND_ENAME';
权限和角色
分为两种：系统权限和对象权限。
系统权限：
系统权限是指执行特定类型sql命令的权利，它用户控制用户可移植性的一个或是一组数据库操作。显示左右的系统权限：select * from system_privilege_map;
授权：
grant create session,create table to july with admin option;with admin option代表授予的用户可以将这些权限往下继续分发。
回收权限：
revoke create session from july;july不可以登录，但是被july分发的权限的用户仍然可以登录。
对象权限：
对象权限指访问其他方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限。比如smith用户要访问scott.emp表（scott：方案，emp：表），则必须在scott.emp表上具有对象的权限。
授权：
从oracle9i以后，dba用户和对象的所有者均可以授权给其他用户。
grant select on emp（可选字段,...） to july with grant option;授权给july查询emp表的权利。with grant option代表可以下授。
回收权限：
对象的权限回收会级联回收（和系统权限不一样）
查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
scott用户默认拥有的角色有：connect和resource

角色：
角色是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。
常用的预定义角色：
1，connect角色：
  一般开发人员的大部分权限。
2，resource角色：
  具有应用开发人员所需要的其它权限，比如存储过程，触发器等。
3，dba角色：
  具有所有的系统权限。不具备sysdab和sysoper的特权（启动和关闭数据库）。
自定义权限（需要有create role的系统权限）：
建立角色：
  create role myrole not identified;
  grant 权限名 to 角色名 with admin option
    如：grant select on emp to myrole;
        grant update on emp to myrole;
   drop role myrole;
如果角色被删除，那么被授予该角色的用户所具有的权限消失。
查看系统默认的角色：select * from dba_roles;
显示角色所具有的系统权限：
select privilege,admin_option from role_sys_privs where role = '角色名';
显示角色具有的对象权限：
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限；
显示用户具有的角色以及默认角色：
select granted_role,default_role from dba_role_privs where grantee='用户名';
数据库的导入与导出：
导入表：
exp userid=username/password@orcl tables=([username.]tablename1,[username.]tablename2...) file=d:\x\y\z.dmp;
如果只是导出表结构，而不要数据，则在导入表命令后面加上rows=n或者direct=y即可。
导出整个数据库（足够的权限）：
exp userid=username/password@orcl full=y inctype=complete file=d:\x\y\z.dmp.
导入表：
imp userid=username/password@orcl tables=(tablename1,tablename2) file d:/x/y/z.dmp.
导入表结构只要在导入表命令后面加上rows=n即可。
导入整个数据库：imp userid=username/password@orcl full=y   file=d:\x\y\z.dmp.
在导入的时候，只有在被导入表空间里不缺少约束时候才能导入。如：从A用户导入一个受到A用户另一张表约束的表a.dmp，在导入到B用户的时候，必须在B用户表里存在受到约束的表，否则导入失败。
数据字典：
数据字典记录了数据库的系统信息，它是只读表和试图的集合，数据字典的所有者为sys用户。用户只能在数据字典上执行查询操作（select语句），而其维护和修改由系统自动完成。
数据字典的组成：
包括字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表，数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息，数据字典视图主要包括user_xxx,all_xxx,dba_xxx三种类型。
user_xxx：如user_tables，当前用户拥有的表。
all_xxx:如all_tables,当前用户可以访问到的所有表
dba_xxx:如dba_tables，当前数据库所有的表。查询这种数据字典，要求用户必须拥有dba角色，或者拥有select any table系统权限。
表空间：
用户必须拥有create tablespace的系统权限才能建立表空间。
在建立数据库之后，为了便于管理表，最好建立自己的表空间：
create tablespace myspace datafile 'd:/myspace.dbf' size 10m uniform size 128k;创建一个叫myspace的表空间，该表空间的数据放在'd:/x/y/zz.bdf'里，128k代表区的大小。
创建表的时候指定表空间：
create table testspace(id number,name varchar2(20)) tablespace myspace;
改变表空间状态（一般在进行系统维护和数据维护时）：
a，使表空间脱机/联机：
alter tablespace myspace offline/online;
b，只读/可写表空间：
alter tablespace myspace read only/write;
查询已知表所在的表空间：
select tablespace_name,table_name from user_tables where table_name ='TESTSPACE';注意名字要大写，否则查不到。
删除表空间：
drop tablespace myspace including contents and datafiles;
扩展表空间，为表空间增加更多的存储空间：
alter tablespace myspace add datafile 'd:/x/y/zz.dbf' size 10m;
移动数据文件：
1，首先确定数据文件所在的表空间：
select tablespace_name from dba_datafiles where file_name ='d:/x/y/zz.dbf';
2，使表空间脱机：
alter tablespace myspace offline;
3，使用命令移动数据文件到指定的目标位置：
host move d:/x/y/zz.dbf e:/y/z/zz.dbf;
4,执行alter tablespace 命令：
在屋里上移动了数据后，还必须执行alter tablespace命令对数据文件进行逻辑修改。
alter tablespace myspace rename datafile 'd:/x/y/zz.dbf' to 'c:/y/x/zz.dbf';
5,使表空间联机：
altertablespace myspace online;