Oracle数据库使用ALTER TABLE MODIFY语法,修改列定义。
语法
alter table table_name modify(column_name 字段类型);
Oracle允许执行多种操作,但主要有以下常用的操作:
- 修改数据列的可见性(可见或不可见)
- 修改字段允许或不允许null
- 修改字段长度
- 修改列的默认值
修改多个列语法如下:
ALTER TABLE table_name MODIFY ( column_name_1 字段类型, column_name_2 字段类型, ... );
举例,仍然使用之前建立的数据库表及数据进行测试:Oracle中新建数据表的两种方法(点击前边文字跳转),不了解的自行跳转链接查看,这里就在列下建表及插入数据语句如下:
--实例建表stuinfo_01 create table JT_CS.stuinfo_01 ( stuid varchar2(11) not null,--学号:'S'+班号(7位数)+学生序号(3位数)(不能为空)SC200101001 stuname varchar2(50) not null,--学生姓名(不能为空) sex char(1) not null,--性别(不能为空)1(男)、2(女) age number(2) not null,--年龄(不能为空) classno varchar2(7) not null,--班号:'C'+年级(4位数)+班级序号(2位数)(不能为空)C200101 stuaddress varchar2(100) default '地址未录入',--地址 (不填或为空时默认填入‘地址未录入‘) grade char(4) not null,--年级(不能为空) enroldate date,--入学时间 idnumber varchar2(18) default '身份证未采集' not null--身份证(不能为空) ) --插入测试数据 INSERT INTO JT_CS.STUINFO VALUES ('SC200101001','路飞','1','19','C200101','东海哥亚王国风车村','2001','2001/09/01','411428199602569201'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101002','索隆','1','21','C200101','东海霜月村','2001','2001/09/01','411428199602569202'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101003','娜美','2','20','C200101','可可西亚村','2001','2001/09/01','411428199602569203'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101004','乌索普','1','19','C200101','东海西罗布村','2001','2001/09/01','411428199602569204'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101005','山治','1','21','C200101','北海杰尔玛王国','2001','2001/09/01','411428199602569205'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101006','乔巴','1','15','C200101','磁鼓岛','2001','2001/09/01','411428199602569206'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101007','罗宾','2','30','C200101','奥哈拉','2001','2001/09/01','411428199602569207'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101008','弗兰奇','1','34','C200101','南海','2001','2001/09/01','411428199602569208'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101009','布鲁克','1','63','C200101','西海','2001','2001/09/01','411428199602569209'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101010','甚平','1','46','C200101','鱼人岛','2001','2001/09/01','411428199602569210');
查询结果数据如下:
SELECT * FROM JT_CS.STUINFO_01;
1、修改数据列的可见性(可见或不可见)
通常默认情况下,新建表在未有任何定义的情况下表列都是可见的。我们可以在创建表或使用ALTER TABLE MODIFY
语句定义某些列不可见。
--以下语句将SEX列修改为不可见(适用于Oracle 12c) ALTER TABLE JT_CS.STUINFO_01 MODIFY SEX INVISIBLE; --将不可见字段修改为可见(适用于Oracle 12c) ALTER TABLE JT_CS.STUINFO_01 MODIFY SEX VISIBLE;
2. 修改字段允许或不允许null
准备测试数据(先制造出一个小问题)
--测试需要,先将原来的enroldate字段路飞的入学时间置空 UPDATE JT_CS.STUINFO_01 SET enroldate='' WHERE STUID='SC200101001';
修改enroldate字段不予许为空 not null
--修改enroldate字段不予许为空 not null ALTER TABLE JT_CS.STUINFO_01 MODIFY enroldate NOT NULL;
因为刚刚我们清空了“路飞”先生的入学时间,所以执行NOTNULL时系统提示错误,有空值存在,也就是说必须确保现有数据符合新约束(也就是说,如果原来数据中存在NULL是不行的)。
为了解决这个问题,首先我们需要将enroldate字段所有为空的列进行填充
--更新填充字段 UPDATE JT_CS.STUINFO_01 SET enroldate='2001-09-30' WHERE STUID='SC200101001' ;
然后改变enroldate列的约束:
--修改约束不允许为空(not null) ALTER TABLE JT_CS.STUINFO_01 MODIFY enroldate NOT NULL;
3. 修改字段长度
--将身份证号字段长度大小扩充到20 ALTER TABLE JT_CS.STUINFO_01 MODIFY IDNUMBER VARCHAR2(20);
注意:要缩短或扩充列的大小,请确保列中的所有数据都符合新的大小,否则语句将无法进行执行。
4. 修改列的默认值
添加一个名为status的新列,默认值为1到JT_CS.STUINFO_01表中
--添加一个新列,然后默认值设置成 1 ALTER TABLE JT_CS.STUINFO_01 ADD status NUMBER( 1, 0 ) DEFAULT 1 NOT NULL ;
将添加的新列status的默认值修改为0
--设置成默认值0 ALTER TABLE JT_CS.STUINFO_01 MODIFY status DEFAULT 0;
插入一条新数据进行测试
--按指定的字段插入数据 INSERT INTO JT_CS.STUINFO_01 ( STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) VALUES ('SC200101011','萨博','1','46','C200101','鱼人岛','2000','2001/09/01','411428199602569211');
结果查询