oracle中修改一条记录,Oracle中的表操作-创建、修改，增减列

在oracle中，表是很重要的，我们在工作中经常要对表进行一些操作，如创建，复制，查询，修改，增减列，截断表，改表名，加注释，删除等，下面我将这些常用的表操作作一个简单的归纳。

我以oracle中的scott用户为例。

1.创建表

SQL> create table t3(id number,name char(15),age date);

Table created.

SQL> insert into t3 values(1,'aa',to_date('1984-01-01','yyyy-mm-dd'));

1 row created.

SQL> select * from t3;

ID NAME            AGE

---------- --------------- ---------

1 aa              01-JAN-84

SQL> select to_char(age,'yyyy-mm-dd') from t3;

TO_CHAR(AGE)

----------

1984-01-01

2.查看表字段

SQL> desc t3

Name                    Null?    Type

----------------------- -------- ----------------

ID                               NUMBER

NAME                             CHAR(15)

AGE                              DATE

3.复制表(包括表中的数据)

SQL> create table t31 as select * from t3;

Table created.

SQL> desc t31

Name                    Null?    Type

----------------------- -------- ----------------

ID                               NUMBER

NAME                             CHAR(15)

AGE                              DATE

SQL> select * from t31;

ID NAME            AGE

---------- --------------- ---------

1 aa              01-JAN-84

4.复制表(不包括表中的数据)

SQL> create table t32 as select * from t3 where 1=0;

Table created.

SQL> desc t32;

Name                    Null?    Type

----------------------- -------- ----------------

ID                               NUMBER

NAME                             CHAR(15)

AGE                              DATE

SQL> select * from t32;

no rows selected

5.查看scott用户拥有哪些表

查看视图user_tables即可。

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

DEPT

EMP

BONUS

SALGRADE

CESHI

TT

T2

T31

T3

T32

6.查看表是用什么语句创建的

SQL> select dbms_metadata.get_ddl('TABLE','T32') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T32')

--------------------------------------------------

CREATE TABLE "SCOTT"."T32"

(    "ID" NUMBER,

"NAME" CHAR(15),

"AGE" DATE

7.查看用户的对象

SQL> select distinct object_type from user_objects;

OBJECT_TYPE

-------------------

SEQUENCE

TABLE

INDEX

好了，上面的语句可以查看表的一些信息，下面对表进行相关的操作(t3为例，上面以创建好)

8.增加列(为表增加一个address字段)

SQL> alter table t3 add(address varchar2(20));

Table altered.

SQL> desc t3;

Name                    Null?    Type

----------------------- -------- ----------------

ID                               NUMBER

NAME                             CHAR(15)

AGE                              DATE

ADDRESS                          VARCHAR2(20)

插入数据

SQL> insert into t3 values(2,'bb',to_date('1985-01-01','yyyy-mm-dd'),'BEIJING');

1 row created.

SQL> select * from t3;

ID NAME            AGE       ADDRESS

---------- --------------- --------- -------------

1 aa              01-JAN-84

2 bb              01-JAN-85 BEIJING

9.修改列定义

SQL> alter table t3 modify(address char(15));

Table altered.

SQL> desc t3

Name                    Null?    Type

----------------------- -------- ----------------

ID                               NUMBER

NAME                             CHAR(15)

AGE                              DATE

ADDRESS                          CHAR(15)

10.修改列名

SQL> alter table t3 rename column address to address1;

Table altered.

SQL> desc t3

Name                    Null?    Type

----------------------- -------- ----------------

ID                               NUMBER

NAME                             CHAR(15)

AGE                              DATE

ADDRESS1                         CHAR(15)

11.加注释

SQL> comment on table t3 is 'biao t3';

Comment created.

SQL> SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME='T3';

COMMENTS

--------------------------------------------------

biao t3

12.改表名

SQL> alter table t3 rename to t4;

Table altered.

SQL> select * from t4;

ID NAME            AGE       ADDRESS1

---------- --------------- --------- -------------

1 aa              01-JAN-84

2 bb              01-JAN-85 BEIJING

13.截断表(truncate)

SQL> truncate table t4;

Table truncated.

SQL> select * from t4;

no rows selected

14.删除表

SQL> drop table t4;

Table dropped.

SQL> desc t4

ERROR:

ORA-04043: object t4 does not exist

表的大部分操作就写到这里，作为oracle管理的新手，这些都是必须的，必须牢记。