oracle系列之表操作

wind998899

oracle表操作
1.創(chuàng)建表：
       a. 創(chuàng)建xs表中計算機(jī)專業(yè)學(xué)生的備份
           Create table xs_jsj as select * from xs where zym=’計算機(jī)’;
       b.完整的例子：
　　　　　 Create table test (xm char(20) not null,zy varchar(30) default (‘計算機(jī)’));
                -- Create table
                create table DEPT
                                (
                                    DEPTNO NUMBER(2) not null,
                                    DNAME VARCHAR2(14),
                                    LOC    VARCHAR2(13)
                                  )
                                tablespace USERS
                                pctfree 10
                                initrans 1
                                maxtrans 255
                        storage
                                (
                                 initial 64K
                                 minextents 1
                                 maxextents unlimited
                                 );
                -- Create/Recreate primary, unique and foreign key constraints
                            alter table DEPT
                             add constraint PK_DEPT primary key (DEPTNO)
                             using index
                             tablespace USERS
                             pctfree 10
                             initrans 2
                             maxtrans 255
                             storage
                             (
                                initial 64K
                                minextents 1
                                maxextents unlimited
                 );
         c.規(guī)則
         　　　　Alter table 命令有許多選項，一個記住語法的方法是Oracle執(zhí)行這個操作需要的信息：
                1）、你不得不告訴Oracle你準(zhǔn)備alter什么表：
                         Alter table table_name
                2)、然后，你準(zhǔn)備做什么？Adding 一個約束
                         ALTER TABLE table_name ADD CONSTRAINT
                3）、強(qiáng)烈建議但不要求為約束定義個名字。約束名不需要放在引號里，但會以大寫字母形式存儲在數(shù)據(jù)字典里，
                         ALTER TABLE temp ADD CONSTRAINT pk_temp
                4）、表示約束類型將是Primary Key，Unique，F(xiàn)oreign Key，或Check約束
                         ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY
                5)、跟在約束類型后面有幾種特殊選項，Primary Key和Unique約束，需要指定約束的列；Check約束需要指定約束規(guī)則。
                        ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID);
                6)、Primary Key和Unique約束應(yīng)當(dāng)指定索引的表空間名，使用USING INDEX TABLESPACE子句.
                        ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE student_index;
                        創(chuàng)建表及索引:
                            create table DEPT                              
                                            (
                                             DEPTNO NUMBER(2) not null,
                                             DNAME VARCHAR2(14),
                                             LOC    VARCHAR2(13)
                                            )
                                            tablespace USERS
                                             pctfree 10
                                             initrans 1
                                             maxtrans 255
                                             storage
                                             (
                                                initial 64K
                                                minextents 1
                                                maxextents unlimited
                                             );
                                            
                                            create table employees
                                            (empno number(5),
                                            deptno number(3) not null,
                       constraint emp_pk primary  key(empno,deptno) 　�。�/有名的主鍵
                                            using index tablespace index
                                            storage (initial 64K
                                            next 64K
                                            )
                                            )
                                           
                                 alter table DEPT              
add constraint PK_DEPT primary key (DEPTNO)
                                             using index
                                             tablespace USERS
                                             pctfree 10
                                             initrans 2
                                             maxtrans 255
                                             storage
                                             (
                                                initial 64K
                                                minextents 1
                                                maxextents unlimited
                                             );
２.修改表.
        Alter table table_name
        Add column_name type [default expression]    增加新列
　　    Modify datatype default expression           修改已有列和屬性
　　　　Storage storage_clause 　　　　　　          修改存儲特征
　　　　Drop drop_clause                             刪除約束條件
　　　　　　　a.改變表所在的表空間
        　alter   table   name   move   tablespace   newtablespace
        例：　①在表xs中添加兩列.jsj,djsm
          Alter table xs add(jxj num<img src="<img src="<img src="">">">ber(1),djsm varchar(40) default ‘獎金1000’)；
             ②在表xs中修改名為djsm的列的默認(rèn)值
　　　　　　　　　Alter talbe xs modify(djsm default ‘獎金800’)
            �、墼诒碇袆h除名為jxj和djxm的列.
                　        Alter table xs drop column jxj;
               　 Alter table xs drop column djsm;
            　④修改表xs_kc的存儲特征
　　　　　　　　　Alter talbe xs pctfree 20 maxtrans 200
           　 ⑤為表xs_jsj加上主鍵
　　　　　　　　　Alter table xs_jsj add (constraint “pk_jsj” primary key(xh));
              ⑥ 增加列
　                語法：ALTER TABLE [schema.] table_name ADD column_definition　type
　                例：　ALTER TABLE orders ADD order_date DATE;
             ⑦更改列
　　　　　      語法：ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;
　　　　        例：　ALTER TABLE orders MODITY (quantity number(10,3),status varchar2(15));
              ⑧.更改表名:
                 alter table t rename to s;
              ⑨.更改列名
                 alter table t rename column n to s;
 
3.約束
  Oracle數(shù)據(jù)庫的完整性約束規(guī)則
  唯一性約束(Unique constraint)
  唯一性約束可以保護(hù)表中多個數(shù)據(jù)列，保證在保護(hù)的數(shù)據(jù)列中任何兩行的數(shù)據(jù)都不相同。唯一性約束與表一起創(chuàng)建，在唯一性約束創(chuàng)建后，可以使用ALTER TABLE語句修改。
   語法：
     column_name data_type CONSTRAINT constraint_name UNIQUE
  如果唯一性約束保護(hù)多個數(shù)據(jù)列，那么唯一性約束要作為表約束增加。語法如下：
            CONSTRAINT constraint_name (column) UNIQUE
            USING INDEX TABLESPACE (tablespace_name)
            STORAGE (stored clause)
   唯一性約束由一個B-tree索引增強(qiáng)，所以可以在USING子串中為索引使用特殊特征，比如表空間或存儲參數(shù)。CREATE TABLE語句在創(chuàng)建唯一性約束的同時也給目標(biāo)數(shù)據(jù)列建立了一個唯一索引

       CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vin VARCHAR2(10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN unique_auto UNIQUE (policy_id,vin) bitsCN_com
    USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) )用戶可以禁用未以性約束，但他仍然存在，禁用唯一性約束使用ALTER TABLE 語句
      ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;                  

     刪除唯一性約束，使用ALTER TABLE....DROP CONSTRAIN語句   

        ALTER TABLE insured_autos DROP CONSTRAIN unique_name;

        注意用戶不能刪除在有外部鍵指向的表的唯一性約束。這種情況下用戶必須首先禁用或刪除外部鍵（foreign key)。   刪除或禁用唯一性約束通常同時刪除相關(guān)聯(lián)的唯一索引，因而降低了數(shù)據(jù)庫性能。經(jīng)常刪除或禁用唯一性約束有可能導(dǎo)致丟失索引帶來的性能錯誤。要避免這樣錯誤，可以采取下面的步驟

　　1、在唯一性約束保護(hù)的數(shù)據(jù)列上創(chuàng)建非唯一性索引。
    2、添加唯一性約束
     主鍵(Primary Key)約束

　表有唯一的主鍵約束。表的主鍵可以保護(hù)一個或多個列，主鍵約束可與NOT NULL約束共同作用于每一數(shù)據(jù)列。NOT NULL約束和唯一性約束的組合將保證主鍵唯一地標(biāo)識每一行。像唯一性約束一樣，主鍵由B-tree索引增強(qiáng)。 創(chuàng)建主鍵約束使用CREATE TABLE語句與表一起創(chuàng)建，如果表已經(jīng)創(chuàng)建了，可以使用ALTER TABLE語句。
          CREATE TABLE policies (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, holder_name VARCHAR2(40), gender VARCHAR2(1), marital_status VARCHAR2(1), date_of_birth DATE );

　　與唯一性約束一樣，如果主鍵約束保護(hù)多個數(shù)據(jù)列，那么必須作為一個表創(chuàng)建約束。

CREATE TABLE insured_autos (policy_id NUMBER, vin VARCHAR2(40), coverage_begin DATE, coverage_term NUMBER, CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) );

      禁用或刪除主鍵必須與ALTER TABLE 語句一起使用
       ALTER TABLE policies DROP PRIMARY KEY;
       ALTER TABLE policies DISABLE PRIMARY KEY;
　　1、創(chuàng)建約束
           CREATE TABLE students
           (student_id    VARCHAR2(10) NOT NULL,
            student_name VARCHAR2(30) NOT NULL,
             college_major VARCHAR2(15) NOT NULL,
             status        VARCHAR2(20) NOT NULL,
              state         VARCHAR2(2),
    　       license_no    VARCHAR2(30)) TABLESPACE student_data;
　　　2、創(chuàng)建主鍵
 　　　　　　　ALTER TABLE students
               ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
               USING INDEX TABLESPACE student_index;
　　　3、創(chuàng)建Unique約束
                 ALTER TABLE students
                 ADD CONSTRAINT uk_students_license
                 UNIQUE (state, license_no)
                 USING INDEX TABLESPACE student_index;
　　　4、創(chuàng)建Check約束
                   ALTER TABLE students
                   ADD CONSTRAINT ck_students_st_lic
                   CHECK ((state IS NULL AND license_no IS NULL) OR
                  (state IS NOT NULL AND license_no is NOT NULL));
                添加check約束
　　　　　      alter table emp add constraint con check(dept_salary>0);
　　　　　　   　con 為約束名，　dept_salary為字段名
      5、創(chuàng)建外鍵約束
           　ALTER TABLE students
          　 ADD CONSTRAINT fk_students_state
        　   FOREIGN KEY (state) REFERENCES state_lookup (state);
     6. 約束
      Alter table table_name add constrants BID primary key (bookno);
      ALERT TABLE table_name MODIFY( column1 PRIMARY KEY);
   

   1、創(chuàng)建表的同時創(chuàng)建主鍵約束
   （1）無命名 create table student (studentid  int  primary key not null,
           studentname varchar(8),age int);
    (2）有命名 create table students (studentid int ,studentname varchar(8),
       age int,constraint yy primary key(studentid));
 

     2、刪除表中已有的主鍵約束
   （1）有命名 alter table students drop constraint yy;
   （2）無命名 可用 SELECT *　from user_cons_columns　where ..;
         查找表中主鍵名稱得student表中的主鍵名為SYS_C002715
           alter table student drop constraint SYS_C002715;
    （３) 使約束失效:
            alter table tbl_employee disable constraint fk_emp;
          刪除約束:
             alter table tbl_department drop constraint pk_dept;
          查詢約束:
select CONSTRAINT_NAME from user_constraints where table_name='TBL_EMPLOYEE';
select CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns where table_name='TBL_EMPLOYEE';
 

4. 刪除表.
      Drop　table table_name;

5、操作表數(shù)據(jù)
           插入表記錄：
            ①. a. insert into table_name col1,col2 values (val1,val2);
例：Insrt into xs(xh,xm,) values (‘09’,to_date(‘19860210’,’yyyymmdd’));
                    insert into    depto values('100','xieyunchao','m','22',to_date('19861104','yyyy-mm-dd'),10000)
                 b.從一個表中向另一個表中插入數(shù)據(jù)
　　　　　　　　　　　　　Insert into table1(col1,col2,col3) select (col1,col2,col3)
　　　　　　　　　　　　　　　　　　　from othertable
                 c.使用子查詢插入數(shù)據(jù)
                 　   insert into employee (empno,ename,sal,deptno)
                              select empno,ename,sal,depto from emp;
                d.INSERT INTO EMP (ENAME,HIREDATE) VALUE(‘AA’,TO_DATE(‘1980-12-09’,’YYYY-MM-DD’))
　　　　　　③. 刪除表數(shù)據(jù)：
　　　　　　　　　Delete from table_name where condition;
　　　　　�、�. 修改表記錄
　　　　　　　　　Update table_name set column_name=expression,…where condition.
                基于一張表修改另一張表的數(shù)據(jù)
                  UPDATE EMPLEE SET DEPTNO=(SELECT DENPNO FROM EMP WHERE EMPNO=7788)
                                 WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788)
　　　　　�、�. 刪除所有記錄但保留表結(jié)構(gòu).
　　　　　　　　　Truncate table table_name;
　　　　　　⑥.查詢數(shù)據(jù)
               查詢表結(jié)構(gòu):DESC table_name
　　　　　　　　　Select ename,sal,12*sal+100 from emp
               注:select count(dinstinct(deptno)) from emp
               a.查詢大于平均的：
                  select empno from emp a,(select avg(sal) as sal_sal from emp) b
                         where a.sal>b.sal_sal;
　　　　　　　如果列中有空值時,則結(jié)果也為空（關(guān)于null值的處理（p４７））.
　　　　　　　　　如：select ename,name,12*sal+comm如果comm的值為null，結(jié)果也為null;
                      如上所示,comm為null時.則12*sal+comm也為null;解決方法是用nvl方法替換.
              在兩個表中查詢:以下兩種方式都是一樣的.
    a.select t_phone_operation.operation_name from t_phone_operation ,t_phone_operation_charge
where t_phone_operation.operation_id=t_phone_operation_charge.operation_id and t_phone_operation_charge.phone_num=’159..’;
    b.select operation_name from t_phone_operation where operation_id in (select operation_id from t_phone_operation_charge where phone_num='159...'
使用日期格式顯示日期：
select ename,to_char(hiredate,’yyyy—mm---dd’) from emp
select ename where hiredate>to_date(‘1999-12-31’,’yyyy-mm-dd’);
              
              使用別名的三種方式:
                  a.    select ename as name,sal salary,from emp
                  b.    select ename ” name”, sal*12 ”annual salary”
              使用連接操作符:
                  Select ename || job as “employees” from emp
              用連接字符:
                  Select ename ||’ ’||’ is a ’||’ ’||job as “employee details”
              限制重復(fù)的行：
                  Select distinct deptno from emp
              注意大小寫:
                  Select ename,job,deptno from emp where job=’CLERK’
              使用between ….and 運算符
                  Select ename,sal from emp where sal between 1000 and 1500;
              使用in 運算符
                  Select empno,ename,sal,mgr from emp where mgr in(23,231,2345); 
              Like運算符：(模糊查詢)
                  %代表至多任意字符
　                ＿代表一個任意字符
                        如：select ename from emp where ename like ’s%’;
                  顯示第三個字符為大寫A的所有信息
                        SELECT ENAME ,SAL FROM EMP WHERE ENAME LIKE '__A%';
                  顯示雇員名包含"_"的雇員信息(其中ESCAPE后的字符a為轉(zhuǎn)義字符)
                        SELECT ENAME,SAL WHERE ENAME LIKE '%a_% ESCAPE 'a';
             Null運算符(關(guān)于null值的處理（p４７）：)
　               測試一個值是否為空：
                        Select ename,mgr from emp where mgr is null (注意是is null ,不是=null)
             使用NVL函數(shù)處理NULL值
                 NVL函數(shù)用于將NULL轉(zhuǎn)換為實際值,其語法為NVL(exp1,exp2).如果是exp1 是null,則返回
                 exp2,如果exp1不為null,則返回exp1,參數(shù)exp1和exp2是任意相同的數(shù)據(jù)類型.
                 如:
                 SELECT ENAME, SAL,SAL+NVL(COM,0) AS "月收入" from EMP;
             使用NVL2函數(shù)處理NULL值
                語法為NVL(exp1,exp2,exp3),如果exp1不是null,則返回exp2,如果exp1是null,則返回exp3;
             用WHERE語句限制日期
                示例一.符合默認(rèn)日期格式
                  select * from emp where hiredate>'01-1月-82'
                示例2:不符合默認(rèn)日期格式(需要用TO_DATE函數(shù)轉(zhuǎn)換)
                  SELECT * FROM EMP WHERE  HIREDATE>TO_DATE('1994-9-24','yyyy-mm-dd');
            邏輯操作符：And or not
            Order by 子句
                       a.   使用order by 子句對查詢數(shù)據(jù)排序
                             ―――asc：升序，默認(rèn)
                             ―――desc：降序：
 
                       依據(jù)多列進(jìn)行排序：
                             Select ename,deptno,sal from emp order by deptno,sal desc
                總結(jié)：
                SELECT    [DISTINCT] {*,COLUMN[ALIAS],…..}    //DISTINCT不重復(fù)
                FROM      tablename
                WHERE     condition
                ORDER BY   {column,expr,alias} [ASC|DESC]
            分組查詢：
           常用分組函數(shù)：
                MAX , MIN ,AVG ,SUM,COUNT,VARIANCE(取方差),STDDEV（取標(biāo)準(zhǔn)差）
●     當(dāng)使用分組函數(shù)時，分組函數(shù)只能出現(xiàn)在選擇列表，order by ,和having子句中，而不能出現(xiàn)在where 和group by子句中.
●     當(dāng)執(zhí)行SELECT 語句時，如果選擇列表同時包含列，表達(dá)式，和分組函數(shù)，那么這些列和表達(dá)式必須出現(xiàn)在GROUP BY子句中. 
select deptno,avg(sal),count(*) from emp group by deptno;
        having avg(sal)<2000;
●如果在select 語句中同時包含group by ,    having ,以及order by子句，則必須將order by子句放在最后.
●當(dāng)限制分組結(jié)果時，必須要使用having ,而不能在WHERE 子句中使用分函數(shù)限制分組顯示結(jié)果.
子查詢：
    子查詢的作用：
●     在insert 或create table 語句中使用子查詢，可以將表數(shù)據(jù)插入到目標(biāo)表中.
●     通過update 子句中使用子查詢，可以修改一列事多列數(shù)據(jù).
●     通過在where ,having,start with 子句中使用子查詢，可以提供條件值.
單行子查詢：
select ename ,sal,deptno from emp where deptno=(select deptno from emp where ename=’scott’)
多行子查詢：
select ename ,job,sal,deptno from emp where job in
 (select distinct job from emp where deptno=10)
使用比較符：
IN:匹配子查詢結(jié)果中的任何一個就可以（見上例）.
ALL:必須要符合子查詢結(jié)果中的所有值.
ANY:只要符合子查詢結(jié)果中的任何一個就可以.
ALL的使用：
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL>ALL
       (SELECT SAL FROM EMP WHERE DEPTNO=30)
ANY 的使用：
   SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL>ANY
(SELECT SAL FROM EMP WHERE DEPT=’30’)
                多列子查詢：
                    SELECT ENAME ,JOB,SAL,DEPTNO FROM EMP WHERE (DEPTNO,JOB) =
                        (SELECT DEPTNO,JOB FROM EMP WHERE ENAME =’SMITH’)
SELECT ENAME,SAL ,COMM,DEPTNO FROM EMP WHERE (SAL,NVL(COMM,-1)) IN (SELECT     SAL,NVL(COMM,-1)FROM EMP WHERE DEPTNO=30)
            ⑦.刪除表數(shù)據(jù)：
                    Delete from table_name where condition;
                    DELETE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)
            ⑧. 修改表記錄
                    Update table_name set column_name=expression,…where condition.
                    UPDATE TABLE_NAME SET COL1=A*1.1,COL2=B*2 WHERE
                    更新日期：
                    UPDATE TABLE_NAME SET HIREDATE =TO_DATE(‘1987/12/1’,’YYYY/MM/DD’);
                    更新關(guān)連數(shù)據(jù)
                    UPDATE EMP SET (ENAME,SAL,COMM)=(SELECT ENAME,SAL,COMM FROM EMP WHERE ENAME=’XX’)WHERE ENAME=’YY’
                    復(fù)制其他表數(shù)據(jù)
                    UPDATE EMPLEYEE SET DEPTNO=(SELECT DEPT FROM EMP WHERE EMPNO=7788)WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788)
            ⑨.刪除所有記錄但保留表結(jié)構(gòu).
                    Truncate table table_name;
              ⑩.事務(wù)
             a. 當(dāng)出現(xiàn)下面的事件時,事務(wù)便結(jié)束
                  ①. commit or rollback 被執(zhí)行時.
                  ②. Ddl or dcl被執(zhí)行時.create or drop 等
                  ③. 用戶退出.
                  ④. 系統(tǒng)中止或當(dāng)機(jī).
             b. 事務(wù)保存點.
    　　　        update ....
                  savepoint update_done
                  insert ....
                  rollback TO update_done
 
注：收集自網(wǎng)上，若有冒犯，請發(fā)信息給我，我立即刪除