oracle学习笔记(十六)：oracle 中 表的创建 和 删除，以及 表结构的修改

---

1、mysql 中创建表：

id 为主键；mysql 中数字类型为 int；auto_increment 表示自增长；

name 为字符串类型；mysql 中用 varchar 表示字符串类型；最大 4 个字节长度，非空；

birthday 日期类型，默认值为 "2015-4-27"；

mysql> create table users(
    ->    id int auto_increment primary key,
    ->    name varchar(4) not null,
    ->    birthday date default '2015-4-27'
    -> );
Query OK, 0 rows affected (0.01 sec)

 

2、oracle 中创建表：

id 为主键；oracle 中数字类型用 number 表示；注意：oracle 中没有自增长关键字 auto_increment；

name 为字符串类型；oracle 中用 varchar2 表示字符串；非空，且唯一；

sal 为数字类型，非空；

birthday 为日期类型，默认值为 当前日期（oracle 中用 sysdba 表示当前日期）；

SQL> create table users(
  2     id number(5) primary key,
  3     name varchar2(8) not null unique,
  4     sal number(6,2) not null,
  5     birthday date default sysdate
  6  );

表已创建。

上面创建表的语句中，通过 数据类型 对字段进行约束：

1）、id number(5) primary key,  其中的 number(5) 表示向表中插入数据时，id 的长度最多为 5位；

2）、sal number(6,2) not null, 其中的 number(6,2) 表示向表中插入数据时，小数部分不管有多少位，都只保留2位小数；

        比如插入数据为 66.66666，最终表中的数据为 66.67；

        并且 2 位小数 加上 整数的总长度不能大于 6 位数字，即整数部分 最多为4位；

        比如插入数据为 66666.6，就会报错；因为表中最终保留 2 位小数的结果为 66666.60，长度超过 6 了；

SQL> insert into users(id,name,sal) values(1,'A',66666.6);
insert into users(id,name,sal) values(1,'A',66666.6)
                                            *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

        注意：查看数据的时候，显示的结果 还要根据 column ... format ... 设置的宽度进行显示；

        比如插入数据：insert into users(id,name,sal) values(11,'AA',66.666); 虽然表中 sal 的值为 66.67，

        但是如果设置 column sal format 9999; 表示设置 sal 显示的列宽为 4 个整数数字，最终显示结果如下：

SQL> select * from users;

        ID NAME               SAL BIRTHDAY
---------- ---------------- ----- --------------
        11 AA                  67 12-10月-19

        如果想显示 2 位小数，需要进行如下设置： column sal format 9999.99；显示结果如下：

SQL> column sal format 9999.99;
SQL>
SQL> select * from users;

        ID NAME                  SAL BIRTHDAY
---------- ---------------- -------- --------------
        11 AA                  66.67 12-10月-19

3）、name varchar2(8) not null unique, 其中 varchar2(8) 表示向表中插入数据时，最多插入 8 个字节；

        注意：GBK 编码 1 个汉字占 2 个字节；UTF-8 编码 1 个汉字占 3 个字节；

        我的环境是 UTF-8 编码，如下所示：

SQL> insert into users(id,name,sal) values(5,'哈哈哈',66.66);
insert into users(id,name,sal) values(5,'哈哈哈',66.66)
                                        *
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."USERS"."NAME" 的值太大 (实际值: 9, 最大值: 8)

 

3、oracle 中删除表：

在 mysql 中，通过语句 drop table users; 删除表时就是真的把表从数据库中删掉了；

但是在 oracle 中，通过语句 drop table users; 删除表时，只是把表放入了回收站中，并没有真正删除；

通过 show recyclebin 语句可以 查看回收站 里的对象：

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
USERS            BIN$HdfSo2p8QfSXqS31riJgqw==$0 TABLE        2019-10-11:16:26:48

通过下面语句可以将回收站里的表 找回来：flashback table users to before drop;

SQL> flashback table users to before drop;

闪回完成。

如果 先后 把两个 users 表都放入了回收站，那么使用 flashback 将表从回收站还原时，遵循一个原则：后删除的先还原；

从回收站中 DROP TIME（删除时间）可以看出；

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
USERS            BIN$V06hDPHdT/uoVHGGgNHLSQ==$0 TABLE        2019-10-11:16:52:43
USERS            BIN$BW6q6hDRSzu56ScpzBORlQ==$0 TABLE        2019-10-11:16:51:25
SQL>
SQL>
SQL> flashback table users to before drop;

闪回完成。

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
USERS            BIN$BW6q6hDRSzu56ScpzBORlQ==$0 TABLE        2019-10-11:16:51:25

如果已经还原了一个 users 表，再还原第二个 users 表时，会报错（表名重复）；

SQL> flashback table users to before drop;
flashback table users to before drop
*
第 1 行出现错误:
ORA-38312: 原始名称已被现有对象使用

可以通过下面语句 给还原的 users 表指定新的表名：flashback table users to before drop rename to new_users;

SQL> flashback table users to before drop rename to new_users;

闪回完成。

可以通过下面语句 彻底删除表，不放入回收站：drop table users purge;（使用关键字 purge）

SQL> drop table users purge;

表已删除。

清空回收站：purge recyclebin;

SQL> purge recyclebin;

回收站已清空。

 

4、修改表结构：

1、为 users 表增加一个 nation 列：

SQL> alter table users add nation varchar2(10);

表已更改。

2、修改 nation 列的长度为 20 个字节：

SQL> alter table users modify nation varchar2(20);

表已更改。

3、删除 nation 列：

SQL> alter table users drop column nation;

表已更改。

4、重命名列名 name 为 username：

SQL> alter table users rename column name to username;

表已更改。

5、将 users 表重命名为 t_users：

SQL> rename users to t_users;

表已重命名。

 

笔试题：如果 users 表中有 10亿 条记录，如何用最高效的方式将 sal 字段清零，其他字段内容不变？

步骤一：从 users 表中把 sal 字段删除；

alter table users drop column sal;

步骤二：向 users 表中添加 sal 字段，默认值为 0；

alter table users add sal number(6,2) default 0 not null ;