• SQL语句中ALTER的用法


    http://blog.sina.com.cn/s/blog_43fd5f8701000aht.html

    The ALTER TABLE command allows you to add, modify, or drop a column from an existing table.

    Adding column(s) to a table

    Syntax #1

    To add a column to an existing table, the ALTER TABLE syntax is:

    ALTER TABLE table_name
    ADD column_name column-definition;

    For example:

    ALTER TABLE supplier
    ADD supplier_name varchar2(50);

    This will add a column called supplier_name to the supplier table.

    Syntax #2

    To add multiple columns to an existing table, the ALTER TABLE syntax is:

    ALTER TABLE table_name
    ADD ( column_1 column-definition,
      column_2 column-definition,
      ...  
      column_n column_definition );

    For example:

    ALTER TABLE supplier
    ADD ( supplier_name varchar2(50),
      city varchar2(45) );

    This will add two columns (supplier_name and city) to the supplier table.

    Modifying column(s) in a table

    Syntax #1

    To modify a column in an existing table, the ALTER TABLE syntax is:

    ALTER TABLE table_name
    MODIFY column_name column_type;

    For example:

    ALTER TABLE supplier
    MODIFY supplier_name   varchar2(100)     not null;

    This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

    Syntax #2

    To modify multiple columns in an existing table, the ALTER TABLE syntax is:

    ALTER TABLE table_name
    MODIFY ( column_1 column_type,
      column_2 column_type,
      ...  
      column_n column_type );

    For example:

    ALTER TABLE supplier
    MODIFY ( supplier_name varchar2(100) not null,
      city varchar2(75)   );

    This will modify both the supplier_name and city columns.

    Drop column(s) in a table

    Syntax #1

    To drop a column in an existing table, the ALTER TABLE syntax is:

    ALTER TABLE table_name
    DROP COLUMN column_name;

    For example:

    ALTER TABLE supplier
    DROP COLUMN supplier_name;

    This will drop the column called supplier_name from the table called supplier.

    Rename column(s) in a table
    (NEW in Oracle 9i Release 2)

    Syntax #1

    Starting in Oracle 9i Release 2, you can now rename a column.

    To rename a column in an existing table, the ALTER TABLE syntax is:

    ALTER TABLE table_name
    RENAME COLUMN old_name to new_name;

    For example:

    ALTER TABLE supplier
    RENAME COLUMN supplier_name to sname;

    This will rename the column called supplier_name to sname.

  • 相关阅读:
    【DATAGUARD】物理dg配置客户端无缝切换 (八.2)--Fast-Start Failover 的配置
    【DATAGUARD】物理dg配置客户端无缝切换 (八.1)--Data Guard Broker 的配置
    我的oracle 健康检查报告
    【DATAGUARD】物理dg在主库丢失归档文件的情况下的恢复(七)
    【DATAGUARD】物理dg的failover切换(六)
    【DATAGUARD】物理dg的switchover切换(五)
    【故障处理】ORA-12162 错误的处理
    【RAC】 RAC For W2K8R2 安装--结尾篇(十)
    【RAC】 RAC For W2K8R2 安装--安装过程中碰到的问题(九)
    【RAC】 RAC For W2K8R2 安装--卸载(八)
  • 原文地址:https://www.cnblogs.com/perfy/p/3009528.html
Copyright © 2020-2023  润新知