DDL语句的使用
常用的数据类型
查询一:查询数据字典,大约58种数据类型 select type_name from dba_types where OWNER isnull |
数据库的表
一个完整的建表sql -- Create table create table AP_MESSAGE ( message_id VARCHAR2(32) notnull, message_title VARCHAR2(500), message_content VARCHAR2(300), send_begin_time DATE, message_type CHAR(1), message_status CHAR(1), send_time DATE, create_by VARCHAR2(50), create_date DATE, update_by VARCHAR2(50), update_date DATE, b_enable CHAR(1), remark VARCHAR2(200) ) tablespace APP pctfree10 initrans1 maxtrans255 storage ( initial64K next1M minextents1 maxextentsunlimited ); -- Add comments to the columns commentoncolumn AP_MESSAGE.message_id is'主键ID'; commentoncolumn AP_MESSAGE.message_title is'消息标题'; commentoncolumn AP_MESSAGE.message_content is'消息内容'; commentoncolumn AP_MESSAGE.send_begin_time is'发送开始时间'; commentoncolumn AP_MESSAGE.message_type is'消息类型:1.工单 2.认证 3.添加好友'; commentoncolumn AP_MESSAGE.message_status is'消息状态:0.未发送 1.已发送 2.发送失败'; commentoncolumn AP_MESSAGE.send_time is'发送时间'; commentoncolumn AP_MESSAGE.create_by is'创建人'; commentoncolumn AP_MESSAGE.create_date is'创建时间'; commentoncolumn AP_MESSAGE.update_by is'更新人'; commentoncolumn AP_MESSAGE.update_date is'更新时间'; commentoncolumn AP_MESSAGE.b_enable is'是否启用:0.未启用 1.启用'; commentoncolumn AP_MESSAGE.remark is'备注当MESSAGE_TYPE为3时,0:对方同意;1:请求;2:请求接受;'; -- Create/Recreate primary, unique and foreign key constraints altertable AP_MESSAGE addconstraint MESSAGE_ID_PK primarykey (MESSAGE_ID) usingindex tablespace APP pctfree10 initrans2 maxtrans255 storage ( initial64K next1M minextents1 maxextentsunlimited ); |
主键约束
主键的特点:值的唯一性且不为null,一个表一旦创建一个主键,oracle就会自动为该表创建一个唯一索引。
案例一:添加主键 create table mytable ( country_id char(2,byte) notnull, country_name varchar2(40,byte) notnull, primary key(country_id) );
案例二:删除主键 alter table mytable drop constraint PK_ORDER_ID alter table mytable drop primarykey
案例三:修改主键 alter table mytable add constraint PK_ORDER_ID primarykey(ORDER_ID) |
外键约束
建表:定义主键,供外键使用 create table dept ( depnum number(2), dname varchar(9), loc varchar2(10), constraint pk_dept primarykey(depnum) )
设置外键 create table emp ( depnum number(2), dname varchar(9), loc varchar2(10), depno, constraint fk_depno foreignkey (depno) reference dept ondeletecase )
设置外键无效 alter table emp disable constraint fk_depno
删除外键 alter table emp drop constraint fk_depno |
约束
check和not null约束
create table worker ( empid number(4) primarykey, ename varchar2(10) notnull, sex char(2) check (sex='男'or sex='女'), age number(2) check (age between18and25), ) |
索引
create table good ( g_id number(8), g_desc varchar2(40), g_cost number(10,2), g_price number(10,2) ) --创建索引 create uniqueindex u_good on good(g_id); --删除索引 drop index good.u_good |