Oracle常用数据库表操作
配置数据库: ?user:orcl.passward:71911.Hao 配置主键和唯一性约束:
2?? ALTER?TABLE??books?ADD?CONSTRAINT?FK_Book_categoryid?FOREIGN?KEY(categoryId?)?REFERENCES?Category(id); 3? CONSTRAINT?FK_LOCATION_ZONE?FOREIGN?KEY(ZONE_CODE)?REFERENCESCALM_ZONE_INFORMATION(ZONE_CODE) ? 联合主键: alter table zcz add constraints zcz_name_age primary key (name,age); alter table AdItem drop constraint AdOrder_AdItem_FK1
CREATE UNIQUE INDEX zcz_name_age ON zcz(NVL2(name,age,NULL),NVL2(name,name,NULL)); ALTER TABLE DM_ADMISSION_PERMIT_USER ADD CONSTRAINT UQ_DAPU_CHECK_ORG UNIQUE(CHECK_ORG,TYPE); ? 修改表名:?alter?table?gld_STA_INFLOW_REPORT?rename?to?STA_INFLOW_REPORT; 重命名字段和修改字段属性: ?ALTER TABLE CALM_BUSINESS_UNIT RENAME COLUMN BU_REMARK TO REMARK; ? 增加和删除列: ALTER TABLE table_name?ADD column_name datatype ALTER TABLE table_name?DROP COLUMN column_name ? 创建序列: CREATE SEQUENCE 序列名 ? 可见在Windows默认情况下,VARCHAR2(2) 就等于是 VARCHAR2(2 BYTE)? 一个汉字占2个字节 可见在Linux默认情况下,VARCHAR2(2) 就等于是 VARCHAR2(2 BYTE)? 一个汉字占3个字节 INSERT INTO TEST VALUES(‘测试‘);分别需要4个字节和6个字节。 ? --备份或者修改表字段: create table contract_basis_bakas as select * from contract_basis; DELETE from contract_basis; ALTER TABLE contract_basis modify cont_name nvarchar2(300); ALTER TABLE contract_basis modify cont_relative_name nvarchar2(300); insert into contract_basis select * from contract_basis_bakas2; DROP TABLE contract_basis_bakas; ? --备份表结构: create table MP_APP_ROLE_R_WELL as select * from MP_APP_ROLE_R_RESOURCE where 1=2; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |