Oracle中对数据表的各种操作_oracle操作表中的数据-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40961980/article/details/97263342

本文详细介绍Oracle数据库中的表创建、字段说明、数据插入、主键添加、字段重命名、数据类型修改、字段添加、表重命名及删除等操作，是进行数据库管理与维护的重要参考资料。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、建表语句

-- Create table
create table FB_CURRENCY
(
  CURRENCY_CODE   VARCHAR2(40) not null,
  CURRENCY_NAME   VARCHAR2(40),
  S_CMPY      VARCHAR2(40),
  S_TDEPT      VARCHAR2(40),
  S_TNAME      VARCHAR2(100),
  S_DEPT      VARCHAR2(40),
  S_ODEPT      VARCHAR2(40),
  S_DNAME      VARCHAR2(100),
  S_USER      VARCHAR2(40),
  S_UNAME      VARCHAR2(40),
  S_FLAG    NUMBER(4),
  S_ATIME      VARCHAR2(30),
  S_MTIME      VARCHAR2(40)
)

2、添加字段说明

-- Add comments to the table 
comment on table FB_CURRENCY
  is '币种表';
-- Add comments to the columns 
comment on column FB_CURRENCY.CURRENCY_CODE
  is '币种编码';
comment on column FB_CURRENCY.CURRENCY_NAME
  is '币种名称';
comment on column FB_CURRENCY.S_CMPY
  is '所属公司';
comment on column FB_CURRENCY.S_TDEPT
  is '拟稿部门';
comment on column FB_CURRENCY.S_TNAME
  is '部门名称';
comment on column FB_CURRENCY.S_DEPT
  is '所属处室';
comment on column FB_CURRENCY.S_ODEPT
  is '申请单位';
comment on column FB_CURRENCY.S_DNAME
  is '拟稿处室名称';
comment on column FB_CURRENCY.S_USER
  is '申请人';
  comment on column FB_CURRENCY.S_UNAME
  is '申请人名称';
  comment on column FB_CURRENCY.S_FLAG
  is '删除标志';
  comment on column FB_CURRENCY.S_ATIME
  is '拟稿时间';
  comment on column FB_CURRENCY.S_MTIME
  is '修改时间';

3、插入数据

	INSERT INTO FB_CURRENCY(CURRENCY_CODE, CURRENCY_NAME) VALUES('CNY', 'CNY-人民币');
	INSERT INTO FB_CURRENCY(CURRENCY_CODE, CURRENCY_NAME) VALUES('HKD', 'HKD-港币');

4、添加主键

	ALTER TABLE FB_CURRENCY ADD CONSTRAINT PK_CURRENCY_CODE PRIMARY KEY (CURRENCY_CODE);

5、重命名字段

ALTER TABLE FB_CURRENCY RENAME COLUMN S_TDEPT TO S_DEPT

6、修改字段数据类型

ALTER TABLE FB_CURRENCY MODIFY (S_CMPY CHAR(4));

7、添加字段

	ALTER TABLE FB_CURRENCY ADD (S_MSG VARCHAR2(400));

8、重命名数据表

RENAME FB_CURRENCY TO CURRENCY;

9、删除表

DROP TABLE FB_CURRENCY;
TRUNCATE TABLE FB_CURRENCY;

10、删除表数据

DELETE FROM FB_CURRENCY WHERE CURRENCY_CODE = 'HKD';