oracle菜鳥學習之 表操作

首發(fā)：http://www.arppinging.com/wordpress/?p=96

oracle菜鳥學習之 表操作

1.創(chuàng)建表

在oracle中，創(chuàng)建表使用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.常規(guī)插入,所有的列都插入

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;

完