oracle操作的一些基本文法__oracle

alter table userInfo add(msn varchar2(20));

1、建表

    create table userInfo (

       id number(6),

       name varchar2(20),

       sex number(1),

       age number(3),

       birthday date,

       address varchar2(50),

       email varchar2(25),

       tel number(11)

    );

2、建立約束

    不帶約束名稱的：

    create table userInfo (

       id number(6) primary key,--主鍵

       name varchar2(20) not null,--非空

       sex number(1),

       age number(3) default 18,

       birthday date,

       address varchar2(50),

       email varchar2(25) unique,--唯一

       tel number(11),

       deptno number(2) references dept(deptno)—外鍵

    ); 

    帶約束名稱：

    create table userInfo (

           id number(6) constraint id_pk primary key,

           name varchar2(20) constraint name_nn not null,

           sex number(1),

           age number(3) default 18,

           birthday date,

           address varchar2(50),

           email varchar2(25) constraint email_uqe unique,

           tel number(11),

           deptno number(2) constraint dept_deptno_ref references dept(deptno)

    );

    列模式：

    create table userInfo (

           id number(6),

           name varchar2(20),

           sex number(1),

           age number(3) default 18,

           birthday date,

           address varchar2(50),

           email varchar2(25),

           tel number(11),

           deptno number(2),

           constraint id_pk primary key (id),--也可以兩個以上，聯合主鍵

           constraint dept_deptno_ref foreign key (deptno) references dept(deptno),

           constraint emial_name_uqe unique (email, name)

    );

    Alter模式：

        alter table userInfo add(msn varchar2(20));

        alter table userInfo modify(msn varchar2(25));

        alter table userInfo drop(msn);

        alter table userInfo drop constraint id_pk;

        alter table userInfo add constraint id_pk primary key (id);

3、建立視圖

    create table v$_dept_view

    as

    select deptno, dname from dept;

    --重新編譯視圖

    alter view v$_dept_view compile;

    提示：視圖一般是一個表或多個表的查詢或子查詢，這樣可以減少代碼量，但同時增加了對資料庫檢視的維護程度，如：某個表欄位被刪除或是修改，視圖也要重新建立或修改，同時佔用了資料庫的一部分空間；視圖就是一個虛擬表格；

4、建立索引

    普通索引：create index idx_dpt_dname on dept(dname);

    聯合索引：create index idx_dept_dname_deptno on dept(dname, deptno);

    --唯一索引

    create unique index idx_emp_ename on scott.emp(ename);

    --反向鍵索引

    create index idx_emp_rev_no on scott.emp(empno) reverse;

    --位元影像索引

    create bitmap index idx_emp_name on scott.emp(dname);

    --索引組織表,一定要有主鍵

    create table tab (

        id int primary key,

        name varchar2(20)

    )    organization index;

    --索引組織表的insert效率非常低

    --分區表索引

    create index idx_name on table(col) local/global;

    --索引分割區

    提示：當給表建立主鍵或唯一鍵約束時，系統也會建立一個約束給該欄位；同樣建立索引也會佔用資料庫空間；索引在訪問、查詢的時候效率有提高，但是在修改表的時候效率就會降低；

5、建立序列

    create sequence seq;

    select seq.nextval from dual;

    insert into tab values(sql.nextval, ‘music’);

    create sequence seqtab

    start with 2 –從2開始

    increment by 3—每次加3

    nomaxvalue—沒有最大值

    minvalue 1—最小值1

    nocycle—不迴圈

    nocache;--不緩衝

    --修改序列 ，不能修改起始值

    alter sequence seqtab

    maxvalue 1000;

6、建立同義字

    同義字，顧名思義就是說別名、或是另一個名字。

    create synonym scott_emp for scott.emp;

    create public synonym scott_dept for scott.dept;

    select * from scott_emp;

    select * from scott_dept;

7、建立資料表空間

    create tablespace HooMS

    datafile 'E:\HooMS.dbf'

    size 5M

    autoextend on next 2M maxsize 10M;

    --建立使用者、分配可以動作表空間

    create user hoo

    identified by hoo

    default tablespace  HooMS

    temporary tablespace temp;

    --建立資料表空間

    create tablespace myMS

    datafile 'c:\myMS.dbf'

    size 1M

    autoextend on;

    --擴充資料表空間--修改資料表空間大小

    alter database

    datafile 'c:\myMS.dbf'

    resize 2M;

    --擴充資料表空間--添加資料檔案

    alter tablespace myMS

    add datafile 'c:\myMS_2.dbf'

    size 1M;

    --設定dbf檔案自動成長

    alter database 

    datafile 'c:\myMS_2.dbf'

    autoextend on next 2M maxsize 4M;

    --資料表空間重新命名

    alter tablespace myMS

    rename to hooMS;

    --分離資料表空間（離線）

    alter tablespace hooMS

    offline temporary;

    --歸檔模式下離線

    alter tablespace hooMS

    offline immediate;

    --使資料表空間聯機

    alter tablespace hooMS online;

    --刪除無資料的資料表空間

    drop tablespace hooMS;

    --刪除帶資料的資料表空間

    drop tablespace hooMS

    including contents;