温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

oracle菜鸟学习之 表操作

发布时间:2020-07-14 01:40:21 来源:网络 阅读:502 作者:运维少年 栏目:关系型数据库

首发: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;

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI