oracle學習心得

   1 ... sqlplus 操作

1.  查詢一個資料庫所有表--》 selec * from  tab;
2.  設定頁數 set pagesize 30;
3.  設定每行顯示的數 set linesize 300;
4.  切換使用者  conn 使用者名稱[sys]/密碼 [as sysdba/sys];
5.  查看一個表的結構-->desc 表名
6.  輸入/ 可以重複剛剛執行的語句

 2....  cmd下
1. sqlplus  使用者名稱[sys]/密碼  [as sysdba or sysoper];

 

3.....sql語句操作
1. select job 工作  from emp;  查詢工作列
2. select distinct job 工作  from emp;  查詢工作列消除重複項
3. select  job 工作, sal*12 年薪  from emp;  查詢工作列消除重複項
4. select  * from emp where ename like '_M%'
5. select * from emp where sal like '%5%' 查詢工資包含5 的員工所有資訊

 

oradim -NEW -SID myoracle -STARTMODE m -PFILE D:/app/Administrator/oradata/test.ora
startup -pfile = D:/app/Administrator/oradata/test.ora nomount

 

4....建立表和管理表

1. 常用資料類型 
  --------------------|-------------------------
   資料類型           |    描述
  --------------------|--------------------------------------------
   number,            |  number(n) 表示一個整數，number(m,n) 表示一個小數，小數長度為n,整數長度為m
   -------------------|----------------------------------
   varchar，varchar2  |
 ---------------------|-----------------------------------------
   date ,             |       表示日期類型
----------------------|------------------------------------------------
   blob，             |         位元據大對象4G
----------------------|-----------------------------------------
   clob               |         文本資料4G
----------------------|----------------------------------------

直接建立表
create table 表名
{
       欄位名稱   欄位類型    [default 預設值],
       欄位名稱   欄位類型    [default 預設值],
       欄位名稱   欄位類型    [default 預設值],
}

用查詢複製表
create table 表名 as (子查詢)
如 create table temp as select * from emp where 1=2;可以複製表結構

刪除表的資料
 delete 表名
刪除表結構和資料
 drop table 表名

建立一個person表
 create table  person
(
    pid  varchar2(20) primary key,
    name varchar2(20),
    age number(3),
    birthday  date,
    sex varchar2(2)  default '男'
);

向表中增加資料
 insert into person(pid,name,age,birthday,sex)
 values('111111111111','張三',30,to_date('1976-02-03','yyyy-mm-dd'),'男');

5.....表的結構的文法
   1.增加
   alter table 表名 add(列名 列的類型 [default] 預設值);
   alter table person add(address  varchar2(100)  default '暫無地址');
   2.修改
   alter table 表名 modify(列名 列的類型 [default] 預設值);
   alter table person modify(name varchar(10) default '無名氏');
 
   3.給表重新命名
   rename 表名1 to 現有表名

6...... 約束

 

  1.  利用笛卡爾集來求對應關係

  create table nation(
        name varchar2(20)
  );
 insert into nation values('中國');
 insert into nation values('美國');
 insert into nation values('英國');
 insert into nation values('荷蘭');
 insert into nation values('巴西');

 select  t1.name , t2.name
 from  nation t1 ,nation t2   where  t1.name <> t2.name;

 2. 約束分類

   . 主鍵約束

         表示是一個唯一的標識，本身不可為空
  create table  person
(
    pid  varchar2(20) primary key,
    name varchar2(20),
    age number(3),
    birthday  date,
    sex varchar2(2)  default '男'
);

或者

 drop table person ;  
    create table  person
(
    pid  varchar2(20) ,
    name varchar2(20),
    age number(3),
    birthday  date,
    sex varchar2(2)  default '男',
    constraint person_pid_pk primary key(pid)
);   // person_pid_pk 代資料表條件約束名稱

 

   . 唯一約束
  
          一個表中只允許一個主鍵約束，
          而其他列如果不希望出現重複值的話可以使用唯一約束。
 drop table person ;  
    create table  person
(
    pid  varchar2(20) primary key,
    name varchar2(20) unique,
    age number(3),
    birthday  date,
    sex varchar2(2)  default '男'
);

或者

 drop table person ;  
    create table  person
(
    pid  varchar2(20)  primary key,
    name varchar2(20),
    age number(3),
    birthday  date,
    sex varchar2(2)  default '男',
    constraint person_name_uk unique(pid)
);   // person_pid_pk 代資料表條件約束名稱

 

   . 檢查約束

          檢查一個列的內容是否合法
          例如：年齡 只能是0--150

 drop table person ;  
    create table  person
(
    pid  varchar2(20),
    name varchar2(20) ,
    age number(3)  check(age between 0 and 150),
    birthday  date,
    sex varchar2(2)  default '男',
    constraint person_pid_pk primary key(pid),
    constraint person_name_uk unique(name)
);   // person_pid_pk 代資料表條件約束名稱

   . 非空約束

          例如：姓名欄位裡面不可為空

  drop table person ;   
    create table  person
(
    pid  varchar2(20) primary key,
    name varchar2(20) not null,
    age number(3),
    birthday  date,
    sex varchar2(2)  default '男'
);

 

   . 外鍵約束

       兩張表之間進行外鍵約束
 
    drop table person2 ;   
    create table  person2
(
    pid  varchar2(20) primary key,
    name varchar2(20) not null,
    age number(3),
    birthday  date,
    sex varchar2(2)   default '男',
    foreign key(pid)  references person(pid)
);

如果要刪除父表，可以採用串聯刪除。
drop table person cascade constraint;

  drop table person ;  
  create table  person
(
    pid  varchar2(20) ,
    name varchar2(20),
    age number(3),
    birthday  date,
    sex varchar2(2)  default '男'
);
 
增加約束
alter table  add constraint 約束名稱 約束類型 (限制欄位)

刪除約束
alter table drop constraint 約束名稱

 rownum 是一個偽列  可以通過它來訪問一個表的資料

7...視圖

  1.建立視圖

   create view 視圖名 as  (子查詢)
  create view empv20 as select empno,ename,job,hiredate
  from emp where deptno = 20;  
  建立一個視圖

 
 如果提示沒有許可權  可以conn system/密碼 串連登陸
 然後  grant create any view to scott;
 要撤銷  revoke create any view from scott;

  create  or replace  view 視圖名 as  (子查詢)
  create view empv20 as select empno,ename,job,hiredate
  from emp where deptno = 20;
  如果存在視圖就刪除再建立該視圖

   ****利用視圖可以建立複雜的查詢

 2.更新視圖

      視圖中間沒有真實資料，更新視圖不能更新建立條件
   
    視圖的本質作用是為了查詢 所以為了避免更新視圖
    在建立的時候可以加一條語句
  create or replace view empv20 as select empno,ename,job,hiredate
  from emp where deptno = 20 with read only;
 

8......序列

     在許多資料庫中有自動成長列 ，但是oracle中沒有。
     所以要手工完成自動成長操作。

       create sequence 序列名
     ( 
       [increment by n] [start with n],
      
            
     );
 
 　兩個操作
　　　　nextVal
        currVal
 
create table testseq
(
   next number,
   curr number
);

將一個序列加入到一個表中
insert into testseq(next ,curr) values(myse.nextVal,myse.currval);

create sequence myse  increment by 2 start with 1;

create sequence  myse1 
maxvalue 10
start with 1
increment by 1
cycle
cache 2;

刪除序列
drop  sequence  序列名

9....同義字
   1.select sysdate from dual;
   查詢系統時間
   dual  是一張續表 在sys下的一個表(sys.dual)
 

   scott可以查看此表是因為同義字的關係
 
建立一個同義字
create synonym   同義字名  for  使用者名稱.表名 -->>  同義字名=使用者名稱.表名

刪除同義字
drop synonym  同義字名

10......使用者管理

    在oracle中隊使用者進行建立及其授權的操作

   1.建立使用者
   create user  使用者名稱   identified by 密碼;
   如  create  user test identified by test123;

 
   2. 為使用者授權
   grant　許可權1，許可權2 to  使用者名稱 
   如grant create session to test;   可以讓其登陸而已

  
  如果現在加上要想把多個許可權一次性賦予一個使用者，則可以
  將這些許可權定義成一個角色

  如
   grant connect ,resource to 使用者名稱。
   
   connect，resource 都是角色。

  3.  刪除使用者
      drop  user 使用者名稱
 
  4.修改使用者密碼

      在sys下
      alter user 使用者名稱  identified  by 新密碼。

  5. 讓密碼失效
    alter user 使用者名稱 password expire;
    為了讓其更新密碼

 6. 鎖住使用者
    alter  user 使用者名稱  account lock;
 
 7. 解除鎖定使用者
    alter user 使用者名稱 account unlock;
 
 8. 授權訪問某一張表
    grant select,delete on scott.emp to 使用者名稱

 9. 回收許可權
   revoke  select,delete on scott.emp from  使用者名稱

 

11 ......資料庫的備份與恢複
   . Database Backup   exp
    .資料庫恢複   imp

     在cmd下  直接用exp命令

      會提示一些資訊
      直接斷行符號下去
 

12......巢狀表格

 

 一個部門有多重專案

    建立一個項目類別
   create  type   project_ty  as object(
       proid  number(4),
       pname   varchar(20)
   );
   /

**注意 / 不可少

  // 用一個別名
  create type  project_nt as table of project_ty;
     /

 //建立部門表  嵌套項目類別表
   create table department
(
    deptno  number(2)  primary key ,
    dname   varchar(50),
    projects   project_nt
 )nested  table  projects store as project_nt_tab_temp;

 

 //存入資料
insert into department(deptno,dname,projects)
values(1,'技術部',project_nt(
                  project_ty('1','論文管理系統'),
                  project_ty('2','員工管理系統'),
                  project_ty('3','薪資管理系統')
                   )
);
 
      

12........資料庫設計範式

 

 
      

 

  

 

 

 
 
 
   

  

 

 

 

 

 

 

   

11:39 2011-6-5