oracle总结6

dml---select,update,insert,delete
ddl---alter,create,truncate,rename,drop  
dcl---grant,revoke

---merge

1.数据类型

数值类型

number(p[,s])

number(7,2)

BINARY_FLOAT ,5bytes
BINARY_double,9bytes


日期时间类型

date,   ７bytes
timestamp



字符串类型

char()  :固定长度,如果长度不够，会填充空格，Maximum size is 2000 bytes or characters
varchar2()：可变长度，Maximum size is 4000 bytes or characters


2.创建和管理表

常见的数据库对象:表，视图(view),序列,索引,同义词

表分类：
用户表:用户创建和维护的表
数据字典：oracle创建和维护的表，v$database,v$session,v$tablespace


常用的四种数据字典视图:
v$:该类型视图为动态执行的视图，包含数据库服务器的性能，存储器和锁等信息
user_:包含关于用户所拥有的对象信息
dba_:为受限制的视图，只分配给dba角色使用
all_:包含所有用户可以访问的表的信息


----oracle逻辑体系结构


block(块)----区(extent)----segment(段)----tablespace(表空间)

---查询scott用户下的表存储在那个表空间
select owner,segment_name,segment_type,tablespace_name,blocks,extents from dba_segments
where owner='SCOTT';

---查看表空间
SELECT * FROM v$tablespace;

---查看表空间对应那些datafile(物理数据文件)
select * from v$datafile;

创建表空间

SQL> create tablespace tbs_02 datafile '/u01/app/oracle/oradata/orcl/tbs_02.dbf' size 200m;
SQL> create user yangry identified by yangry default tablespace tbs_02;
SQL> grant dba to yangry;
SQL> conn yangry/yangr;

---在用户yangry下创建表不指定默认表空间
SQL> create table student(stu_id number,stu_name varchar2(50),stu_address varchar2(100));

Table created.
---创建表指定表空间
SQL> create table student1(stu_id number,stu_name varchar2(50),stu_address varchar2(100)) tablespace users;

Table created.

SQL> insert into student values(1,'yangry','yangry');

1 row created.

SQL> insert into student1 values(1,'yangry','yangry');


---create table语法  ddl
create table tbl_name(col1 datatype,col2 datatype,......) tablespace tablespace_name;

#表创建规则
moduleName_tblname

sys_user()
gps_tbl_name
----------------
t_tblname
v_

procedure_pro
function_fun
package_pkg
view
procedure
function
package
---------------

用子查询来创建表

create table tbl_name(col1,col2....) as subquery;

//
create table y1 as select * from hr.employees;

//指定字段
create table y2(employee_id,first_name,last_name,email)
as select employee_id,first_name,last_name,email from hr.employees;


select * from y2;

//创建空表
create table y3 as select * from hr.employees where 1=2;


---default选项
create table teacher(teacher_id number,teacher_name varchar2(50) not null,hire_date date default sysdate);


insert into teacher(teacher_id,teacher_name) values(1,'yangry');

select * from teacher;

---alter table  ddl

#语法
alter table tbl_name add(col1 datatype,....);

alter table teacher add(phone varchar(11));


alter table teacher modify(phone varchar(15) default '15818746953');

insert into teacher(teacher_id,teacher_name) values(2,'zhouwp');

select * from teacher;

#删除列语法
alter table  tbl_name drop column column_name;

alter table teacher drop column phone;


---删除表 ddl
drop table tbl_name;

drop table student;---删除的表还在垃圾回收站　show recyclebin;


清空回收站:purge recyclebin;


drop table student purge;删除表不经过垃圾回收站


---rename tbl_name;---ddl

#rename语法
rename old_name to new_name;
old_name:Specify the name of an existing table, view, sequence, or private synonym.

rename y3 to employ;



#delete,truncate


delete:删除表的数据，但是不会释放表存储空间


delete from tbl_name [[join tbl_name on ... ]where ....];


//删除指定数据
delete from employ where employee_id>=200;


//删除表所有数据
delete from employ;


truncate:删除表的所有行数据并释放所占用的空间

#语法
truncate　table  tbl_name;


create table employ1 as select * from hr.employees;

select count(*) from employ1;

truncate table employ1;


---添加备注语法
comment on column tbl_name.column_name is '备注名称';

#exp
COMMENT ON COLUMN TEACHER.TEACHER_ID IS 'zhujian';

COMMENT ON COLUMN TEACHER.TEACHER_NAME IS 'jiaoshi';

COMMENT ON COLUMN TEACHER.HIRE_DATE IS 'ruzhi date';



---insert


syntax:
insert into tbl_name(col1,col2,col3......) values(value1,value2,value3......)


insert into tbl_name(col1,col2,col3......)  select col1,col2,col3...... from  tbl_name;

insert into employ1  select * from hr.employees;


---update

update tbl_name set column_name=value1 [where condition];

#exp
update employ1 set salary=5000 where employee_id=198;


update employ1 set salary=5000;


3.数据库约束
not null,primary key,foreign key,unique,check

学生表

create table t_student(stu_id number primary key
,course_id number not null
,stu_name varchar2(50) not null
,stu_addr varchar2(100) not null
,email varchar2(255));


alter table t_student add constraint fk_course_id foreign key(course_id)
references t_course(course_id);


课程表
create table t_course(course_id number primary key
,course_name varchar2(50));


insert into t_course(course_id,course_name) values(1,'computer');
insert into t_course(course_id,course_name) values(2,'yuwen');
insert into t_course(course_id,course_name) values(3,'math');
insert into t_course(course_id,course_name) values(4,'cccc');


INSERT INTO T_STUDENT
  ( STU_ID,
    STU_NAME,
    STU_ADDR,
    EMAIL,
    COURSE_ID)
  VALUES
  ( 1,'yangry','yangry','[e-mail]',1);
 
  INSERT INTO T_STUDENT
  ( STU_ID,
    STU_NAME,
    STU_ADDR,
    EMAIL,
    COURSE_ID)
  VALUES
  ( 2,'yangry2','yangry2','[e-mail]',2);
 
 
    INSERT INTO T_STUDENT
  ( STU_ID,
    STU_NAME,
    STU_ADDR,
    EMAIL,
    COURSE_ID)
  VALUES
  ( 3,'yangry3','yangry3','[e-mail]',5);
 

---unique key

unique key语法：
create unique key idx_name on tbl_name(column_name);

INSERT INTO T_STUDENT
  ( STU_ID,
    STU_NAME,
    STU_ADDR,
    EMAIL,
    COURSE_ID)
  VALUES
  ( 3,'yangry2','yangry3','[e-mail]',3);







---check约束
CREATE TABLE employees_demo
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)  NOT NULL
    , email          VARCHAR2(25) NOT NULL
    , salary         NUMBER(8,2) NOT NULL  
    , commission_pct NUMBER(2,2)
    ,CONSTRAINT    emp_salary_min CHECK (salary > 0)
    ) ;


//查看约束
select * from user_constraints;





4.其他数据库对象

CREATE SEQUENCE SEQ_STUDENT
INCREMENT BY 1 　　---每个加１个
START WITH 1 　　---从１开始
MINVALUE 1 　　---最小值
CACHE 20;　　　---oracle预先在内存中存放一些sequence,存取速度快

INSERT INTO T_STUDENT
  (
    STU_ID,
    STU_NAME,
    STU_ADDR,
    EMAIL,
    COURSE_ID )
  VALUES
  (seq_student.nextval,'raoj2','jx2','[e-mail]',3);

//查询序列下一个值
select seq_student.nextval from dual;

//查询序列当前值
select seq_student.currval from dual;



---同义词

同义词其实就是一种替换操作，oracle会自动将同义词替换为它所代表的对象名，再对对象进行操作。

create [public] synonym synonym_name for object;



create synonym employees for hr.employees;

select * from employees;


删除同义词

drop [public] synonym synonym_name;


---索引

普通索引(B树索引),唯一索引，bitmap索引，函数索引

索引目的是加快在一个巨大的表中查询少量的速度。索引中的值将会随表的改变而改变，因此索引会增加DML操作时的负担。

#普通和唯一索引语法 index_name :idx/ind开头
create index/unique index  index_name on tbl_name(col1[,clo2...]);


#bitmap index
create bitmap index index_name on tbl_name(col1[,clo2...]);


#function index

//查看执行计划
select * from employees where upper(last_name)='KING';


//查看执行计划
select * from employees where last_name='King';


create index idx_last_name on employees(upper(last_name));


//命令行查看执行计划
SQL> set autotrace on;
SQL> set linesize 255;
SQL> select * from employees where upper(last_name)='KING';
SQL> set autotrace off;

SQL> set pagesize 200

SQL> select name ,FS_FAILOVER_OBSERVER_HOST from v$database;

NAME
---------
FS_FAILOVER_OBSERVER_HOST
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORCL



SQL> col name for a10;
SQL> col FS_FAILOVER_OBSERVER_HOST for a30;
SQL> select name ,FS_FAILOVER_OBSERVER_HOST from v$database;

NAME       FS_FAILOVER_OBSERVER_HOST
---------- ------------------------------
ORCL




5.用户管理

角色,grant,revoke


create role role_name;

(1)创建角色
SQL> create role access_database;
(2)将权限授予给角色
SQL> grant create session,create table,create view ,select any table to  access_database;

(3)将角色授予给用户
SQL> grant access_database to yangry;


//查看角色具有的权限
SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                   PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
RESOURCE               CREATE SEQUENCE                NO
RESOURCE               CREATE TRIGGER                NO
RESOURCE               CREATE CLUSTER                NO
RESOURCE               CREATE PROCEDURE             NO
RESOURCE               CREATE TYPE                NO
RESOURCE               CREATE OPERATOR                NO
RESOURCE               CREATE TABLE                NO
RESOURCE               CREATE INDEXTYPE             NO



#revoke回收权限

SQL> revoke ACCESS_DATABASE from yangry;


//查看用户拥有那些角色
SQL> select * from user_role_privs where username='YANGRY';

USERNAME               GRANTED_ROLE              ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
YANGRY                   DBA                  NO  YES NO


教师，学生，课程的表关系,要求：
(1)良好的设计规范
(2)表之间做好约束关系
(3)字段备注
(4)每个表不少于10条数据


6.数据泵备份和还原(expdp/impdp) exp/imp---oracle9i

expdp准备步骤:
(1)SQL> conn sys/oracle as sysdba
(2)SQL> create directory exp1 as '/u01/exp';
(3)SQL> grant read,write on directory exp1 to hr;
(4)SQL> select * from dba_directories where directory_name='EXP1';



expdp:
   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp


备份表
[oracle@ora11g exp]$ expdp hr/hr directory=exp1 tables=employees dumpfile=examples.dmp

基于用户恢复
[oracle@ora11g exp]$ expdp hr/hr directory=exp1 schemas=hr dumpfile=hr.dmp

表空间恢复
[oracle@ora11g exp]$ expdp hr/hr directory=exp1 tablespaces=users dumpfile=users.dmp


impdp:

  Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp


[oracle@ora11g exp]$ impdp hr/hr directory=exp1 tables=emp dumpfile=emp.dmp