首发:http://www.arppinging.com/wordpress/?p=96
在oracle中,创建表使用create table来实现
SQL> create table student(sno number(6),sname varchar2(12),address varchar2(20));
Table created.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(12)
ADDRESS VARCHAR2(20)
SQL>
1.增加一列
SQL> alter table student add phone varchar(11);
Table altered.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(12)
ADDRESS VARCHAR2(20)
PHONE VARCHAR2(11)
SQL>
2.修改列属性,需要注意的是,如果修改的属性为长度,那么已存在的数据长度不能超过修改过后的数据长度。比如现存在一条信息,sname长度为6,如果你将sname的长度修改为5,那么将会出错。
# 这里使用到了modify(修改)
SQL> alter table student modify sname varchar2(5);
Table altered.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(5)
ADDRESS VARCHAR2(20)
PHONE VARCHAR2(11)
SQL>
在oracle中,删除列需要用column(列)
SQL> alter table student drop column phone;
Table altered.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(5)
ADDRESS VARCHAR2(20)
SQL>
3.插入数据
1.常规插入,所有的列都插入
SQL> insert into student values(1,'A','BJ');
1 row created.
SQL> select * from student;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2.插入空值
SQL> insert into student values(2,'B',null);
1 row created.
SQL> select * from student;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
SQL>
3.指定列插入数据
SQL> insert into student(sno,address) values(3,'SH');
1 row created.
SQL> select * from student;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
3 SH
SQL>
1.复制表的所有内容
SQL> create table student2 as select * from student;
Table created.
SQL> desc student2;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(5)
ADDRESS VARCHAR2(12)
SQL> select * from student2;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
3 SH
SQL>
2.只复制表结构,不复制内容
SQL> create table student3 as select * from student where 1>2;
Table created.
SQL> desc student3;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(5)
ADDRESS VARCHAR2(12)
SQL> select * from student3;
no rows selected
SQL>
3.将student表的信息全部插入student3表中(也可以用where筛选)
SQL> select * from student3;
no rows selected
SQL> insert into student3 select * from student;
3 rows created.
SQL> select * from student3;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
3 SH
SQL>
更新表中的内容
SQL> update student2 set sname='C' where sno=3;
1 row updated.
SQL> select * from student2;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
3 C SH
SQL>
1.删除某条信息,使用delete需要commit提交
SQL> delete student2 where sname='C';
1 row deleted.
SQL> select * from student2;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
SQL> commit;
Commit complete.
2.清空表中的内容,保留表结构
使用delete的方式清除,需要提交,delete方式清除的内容会写入日志,可以恢复。
SQL> delete student3;
3 rows deleted.
SQL> select * from student3;
no rows selected
SQL> desc student3;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(5)
ADDRESS VARCHAR2(12)
SQL> commit;
Commit complete.
3.使用truncate table 删除的内容不会写入日志,不可恢复,不需要提交
SQL> truncate table student2;
Table truncated.
SQL> select * from student2;
no rows selected
SQL> desc student2;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(5)
ADDRESS VARCHAR2(12)
SQL>
SQL> drop table student3;
Table dropped.
SQL>
SQL>
SQL> desc student3;
ERROR:
ORA-04043: object student3 does not exist
1.表的重命名
格式:rename a to b;
SQL> rename student2 to newstudent;
Table renamed.
SQL> select * from newstudent;
SNO SNAME AGE
---------- ------------------------------ ----------
1 ZhangSan 21
2 FeiFei 22
3 WangWu 23
4 ZhaoYun 24
SQL>
2.重命名列
格式:alter table table_name rename column a to b;
SQL> alter table newstudent rename column age to sage;
Table altered.
SQL>
SQL> desc newstudent;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(6)
SNAME VARCHAR2(10)
SAGE NUMBER(38)
SQL>
select * from tab;
完
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。