ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【TABLE】oracle的表操作(转载)

【TABLE】oracle的表操作(转载)

原创 Linux操作系统 作者:chicheng_cn421 时间:2011-05-10 16:40:42 0 删除 编辑

【TABLE】oracle的表操作(转载)

本文原载地址: http://blog.sina.com.cn/s/blog_5dd9ce4e0100dtr7.html

1.创建表:
  a. 创建xs表中计算机专业学生的备份
     Create table xs_jsj as select * from xs where zym=’计算机’;
  b.完整的例子:
   Create table test (xm char(20) not null,zy varchar(30) default (‘计算机’));
     -- 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.规则
    Alter table 命令有许多选项,一个记住语法的方法是Oracle执行这个操作需要的信息:
     1)、你不得不告诉Oracle你准备alter什么表:
              Alter table table_name
     2)、然后,你准备做什么?Add 一个约束
              ALTER TABLE table_name ADD CONSTRAINT
     3)、强烈建议但不要求为约束定义个名字。约束名不需要放在引号里,但会以大写字母形式存储在数据字典里,
              ALTER TABLE temp ADD CONSTRAINT pk_temp
     4)、表示约束类型将是Primary Key,Unique,Foreign Key,或Check约束
              ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY
     5)、跟在约束类型后面有几种特殊选项,Primary Key和Unique约束,需要指定约束的列;Check约束需要指定约束规则。
             ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID);
     6)、Primary Key和Unique约束应当指定索引的表空间名,使用USING INDEX TABLESPACE子句.
             ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE student_index;

     创建表及索引:
         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
           );
    
2.修改表.
   Alter table table_name
   Add column_name type [default expression]    --增加新列
   Modify datatype default expression           --修改已有列和属性
  Storage storage_clause                 --修改存储特征
  Drop drop_clause                             --删除约束条件

   例:
    ①在表xs中添加两列.jsj,djsm
        Alter table xs
   add(jxj number(1),
       djsm varchar(40) default ‘奖金1000’
      );
    
  ②在表xs中修改名为djsm的列的默认值
    Alter talbe xs modify(djsm default ‘奖金800’)

     ③在表中删除名为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;
 
     ⑩改变表所在的表空间
       alter   table   name   move   tablespace   newtablespace
    
3.约束
  Oracle数据库的完整性约束规则
  唯一性约束(Unique constraint)
  唯一性约束可以保护表中多个数据列,保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建,在唯一性约束创建后,可以使用ALTER TABLE语句修改。
   语法:
     column_name data_type CONSTRAINT constraint_name UNIQUE
 
  如果唯一性约束保护多个数据列,那么唯一性约束要作为表约束增加。语法如下:
            CONSTRAINT constraint_name (column) UNIQUE
            USING INDEX TABLESPACE (tablespace_name)
            STORAGE (stored clause)
   
   唯一性约束由一个B-tree索引增强,所以可以在USING子串中为索引使用特殊特征,比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一索引
   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)。  
    删除或禁用唯一性约束通常同时删除相关联的唯一索引,因而降低了数据库性能。
    经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。
   
    要避免这样错误,可以采取下面的步骤
      1、在唯一性约束保护的数据列上创建非唯一性索引。
        2、添加唯一性约束
   
 主键(Primary Key)约束
    表有唯一的主键约束。表的主键可以保护一个或多个列,主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样,主键由B-tree索引增强。 创建主键约束使用CREATE TABLE语句与表一起创建,如果表已经创建了,可以使用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 );
 
   与唯一性约束一样,如果主键约束保护多个数据列,那么必须作为一个表创建约束。
      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、创建表
         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、创建主键
     ALTER TABLE students
         ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
         USING INDEX TABLESPACE student_index;
  
    3、创建Unique约束
        ALTER TABLE students
          ADD CONSTRAINT uk_students_license
          UNIQUE (state, license_no)
          USING INDEX TABLESPACE student_index;
  
    4、创建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、创建外键约束
         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、创建表的同时创建主键约束
   (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;
   (3) 使约束失效:
            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、操作表数据
     
     ①.插入表记录:
      a. insert into table_name col1,col2 values (val1,val2);
            例:Insert 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.从一个表中向另一个表中插入数据
        Insert into table1(col1,col2,col3)
                  select (col1,col2,col3)
         from othertable
        
   c.使用子查询插入数据
                insert into employee (empno,ename,sal,deptno)
                    select empno,ename,sal,depto
     from emp;
         
      ②. 删除表数据:
         Delete from table_name where condition; COMMIT;
     
   ③. 修改表记录
        Update table_name set column_name=expression,…where condition.
             
                基于一张表修改另一张表的数据
                UPDATE EMPLEE SET DEPTNO=(SELECT DENPNO
                              FROM EMP
            WHERE EMPNO=7788)
                WHERE JOB=(SELECT JOB
               FROM EMP
         WHERE EMPNO=7788
         );
        
   ④. 删除所有记录但保留表结构.
         Truncate table table_name;

   ⑤.查询数据
            查询表结构: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;
       
              如果列中有空值时,则运算结果也为空.解决方法是用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 from EMP
     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”
     
              限制重复的行:
                  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为转义字符)
                        SELECT ENAME,SAL WHERE ENAME LIKE '%a_% ESCAPE 'a'';
            
   
    Null运算符
                测试一个值是否为空:
                        Select ename,mgr from emp where mgr is null (注意是is null ,不是=null)
             使用NVL函数处理NULL值
                 NVL函数用于将NULL转换为实际值,其语法为NVL(exp1,exp2).如果是exp1 是null,则返回
                 exp2,如果exp1不为null,则返回exp1,参数exp1和exp2是任意相同的数据类型.
                 如:
                 SELECT ENAME, SAL,SAL+NVL(COM,0) AS "月收入" from EMP;
             使用NVL2函数处理NULL值
                语法为NVL(exp1,exp2,exp3),如果exp1不是null,则返回exp2,如果exp1是null,则返回exp3;
             用WHERE语句限制日期
                示例一.符合默认日期格式
                  select * from emp where hiredate>'01-1月-82'
                示例2:不符合默认日期格式(需要用TO_DATE函数转换)
                  SELECT * FROM EMP WHERE  HIREDATE>TO_DATE('1994-9-24','yyyy-mm-dd');
            逻辑操作符:And or not
            Order by 子句
                       a.   使用order by 子句对查询数据排序
                             ―――asc:升序,默认
                             ―――desc:降序:
 
                       依据多列进行排序:
                             Select ename,deptno,sal from emp order by deptno,sal desc
                总结:
                SELECT    [DISTINCT] {*,COLUMN[ALIAS],…..}    //DISTINCT不重复
                FROM      tablename
                WHERE     condition
                ORDER BY   {column,expr,alias} [ASC|DESC]
            分组查询:
           常用分组函数:
                MAX , MIN ,AVG ,SUM,COUNT,VARIANCE(取方差),STDDEV(取标准差)
●     当使用分组函数时,分组函数只能出现在选择列表,order by ,和having子句中,而不能出现在where 和group by子句中.
●     当执行SELECT 语句时,如果选择列表同时包含列,表达式,和分组函数,那么这些列和表达式必须出现在GROUP BY子句中. 
select deptno,avg(sal),count(*) from emp group by deptno;
        having avg(sal)<2000;
●如果在select 语句中同时包含group by ,    having ,以及order by子句,则必须将order by子句放在最后.
●当限制分组结果时,必须要使用having ,而不能在WHERE 子句中使用分函数限制分组显示结果.
子查询:
    子查询的作用:
●     在insert 或create table 语句中使用子查询,可以将表数据插入到目标表中.
●     通过update 子句中使用子查询,可以修改一列事多列数据.
●     通过在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:匹配子查询结果中的任何一个就可以(见上例).
ALL:必须要符合子查询结果中的所有值.
ANY:只要符合子查询结果中的任何一个就可以.
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)
            ⑦.删除表数据:
                    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’);
                    更新关连数据
                    UPDATE EMP SET (ENAME,SAL,COMM)=(SELECT ENAME,SAL,COMM FROM EMP WHERE ENAME=’XX’)WHERE ENAME=’YY’
                    复制其他表数据
                    UPDATE EMPLEYEE SET DEPTNO=(SELECT DEPT FROM EMP WHERE EMPNO=7788)WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788)
            ⑨.删除所有记录但保留表结构.
                    Truncate table table_name;
              ⑩.事务
             a. 当出现下面的事件时,事务便结束
                  ①. commit or rollback 被执行时.
                  ②. Ddl or dcl被执行时.create or drop 等
                  ③. 用户退出.
                  ④. 系统中止或当机.
             b. 事务保存点.
               update ....
                  savepoint update_done
                  insert ....
                  rollback TO update_done

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20335819/viewspace-694930/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-05

  • 博文量
    261
  • 访问量
    378060