oracle菜鸟学习之 表操作

首发：http://www.arppinging.com/wordpress/?p=96

oracle菜鸟学习之 表操作

1.创建表

在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> 

2.修改表的列

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> 

3.删除列

在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           BJ2.插入空值SQL> insert into student values(2,'B',null);1 row created.SQL> select * from student;       SNO SNAME       ADDRESS---------- --------------- ------------------------------------     1 A           BJ     2 BSQL> 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         SHSQL> 

4.复制表

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         SHSQL> 

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 selectedSQL> 

3.将student表的信息全部插入student3表中（也可以用where筛选）

SQL> select * from student3;no rows selectedSQL> insert into student3 select * from student;3 rows created.SQL> select * from student3;       SNO SNAME       ADDRESS---------- --------------- ------------------------------------     1 A           BJ     2 B     3         SHSQL> 

5.更新表

更新表中的内容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           SHSQL>

6.删除内容

1.删除某条信息,使用delete需要commit提交

SQL> delete student2 where sname='C';1 row deleted.SQL> select * from student2;       SNO SNAME       ADDRESS---------- --------------- ------------------------------------     1 A           BJ     2 BSQL> commit;Commit complete.

2.清空表中的内容，保留表结构
使用delete的方式清除，需要提交，delete方式清除的内容会写入日志，可以恢复。

SQL> delete student3;3 rows deleted.SQL> select * from student3;no rows selectedSQL> 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 selectedSQL> desc student2; Name                       Null?    Type ----------------------------------------- -------- ---------------------------- SNO                            NUMBER(6) SNAME                            VARCHAR2(5) ADDRESS                        VARCHAR2(12)SQL> 

7.删除表

SQL> drop table student3;Table dropped.SQL> SQL> SQL> desc student3;ERROR:ORA-04043: object student3 does not exist

8.重命名

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                24SQL> 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> 

9.查看所有表名

select * from tab;

完