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

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

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           SH

SQL>

6.删除内容

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> 

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

9.查看所有表名

select * from tab;

完