STUDENT_ID STUDENT_NAME STUDENT_AGE
---------- -------------------- -----------
3 marry 23
4 jay 16
1 jimmy 18
1.删除id为3和4的内容
SQL> delete from student where student_id=3;
1 row deleted.
SQL> delete from student where student_id=4;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from student;
STUDENT_ID STUDENT_NAME STUDENT_AGE
---------- -------------------- -----------
1 jimmy 18
删除成功。
2.更改id为1的age为20
SQL> update student set student_age=20;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from student;
STUDENT_ID STUDENT_NAME STUDENT_AGE
---------- -------------------- -----------
1 jimmy 20
更改成功。
3.增加列
SQL> alter table student add gender varchar2(10);
Table altered.
SQL> commit;
Commit complete.
SQL> select * from student;
STUDENT_ID STUDENT_NAME STUDENT_AGE GENDER
---------- -------------------- ----------- ----------
1 jimmy 20
增加了gender一列。
4.修改列
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NOT NULL NUMBER
STUDENT_NAME VARCHAR2(20)
STUDENT_AGE NUMBER
GENDER VARCHAR2(10)
修改gender的varchar2长度为20
SQL> alter table student modify gender varchar2(20);
Table altered.
SQL> commit;
Commit complete.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NOT NULL NUMBER
STUDENT_NAME VARCHAR2(20)
STUDENT_AGE NUMBER
GENDER VARCHAR2(20)
修改成功。
5.删除列
SQL> alter table student drop column gender;
Table altered.
SQL> commit;
Commit complete.
SQL> select * from student;
STUDENT_ID STUDENT_NAME STUDENT_AGE
---------- -------------------- -----------
1 jimmy 20
删除成功。
6.更改表名
SQL> alter table test rename to test01; //把表test改名为test01
Table altered.
7.更改列名
SQL> alter table test01 rename column name to fullname; //把列名name修改为fullname
Table altered.
参考资料:https://www.cnblogs.com/yugen/archive/2010/08/03/1791533.html
这里要注意几个地方,首先,增加和修改列是不需要加关键字COLUMN,否则会报错ora-00905。
其次,对删除单列的话,一定要加COLUMN,然后记住,删除是不需要加列类型的。
增加多列:
alter table emp4 add (test varchar2(10),test2 number);
修改多列:
alter table emp4 modify (test varchar2(20),test2 varchar2(20));
删除多列:
alter table emp4 drop (test,test2);
很奇怪的现象,再单列中要加关键字COLUMN,然而再删除多列的时候,不能加COLUMN关键字。