Oracle 19C学习 - 13. 创建与管理表 - LeoZhangJing

Oracle 19C学习 - 13. 创建与管理表

数据库中的对象

表TABLE	基本的存储单元由行【数据】和列【字段】组成。
视图VIEW	逻辑上代表一个和多个表中的数据的子集。
序列	数字值的生成器
索引	提高某些查询的性能
同义词	给出对象的替代名称

数据表

表明和列明的命名规则：

必须以字母开头
长度必须是1-30个字符
只能包含A-Z a-z 0-9 _ $ #等字符和特殊字符
不能与同一个用户拥有的其他对象重名同一个用户或者同一个schema下只能有一张这个名字表
不能是ORACLE的保留字

CREATE TABLE 建表语句

必须具有以下条件才能使用此命令：

CREATE TABLE权限
一个可以使用的表空间存储区

CREATE TABLE [SCHEMA.] Table_Name (

column_name_1 data_type default value column_constraint,

column_name_2 data_type default value column_constraint,

..., table_constraint );

SCHEMA.TABLE_NAME：绝对名称完整名称比如说hr.employees。

DEFAULT选项：指定列的默认值，默认数据类型必须与列数据类型一致。

CREATE TABLE emp (
                 id number(10) primary key,
                 name varchar2(20),
                 property varchar2(20) default 'SLQ');

数据库中的表的分类：
用户表：是由用户创建和维护的表的集合，这种表包含了用户维护的数据。
数据字典表：是由ORACLE SERVER创建和维护的表的集合，包含数据库信息。

ORACLE中常见的数据字典表：

USER_XXXX 包含了当前数据库用户所拥有的对象的信息。
ALL_XXXX 包含了当前数据库用户可以访问的所有的模式对象的信息。
DBA_XXXX 包含了所有数据库对象信息，只有具有DBA角色的用户才嫩而过访问的这些视图

例如：

　　SELECT TABLE_NAME FROM USER_TABLES;/SELECT TNAME FROM TAB; 查询当前用户所有用的表名。

COL TABLE_NAME FOR A10
SELECT table_name FROM user_tables;

TABLE_NAME
----------
EMP

V$ 动态性能视图：反映数据库服务器的性能、内存和锁等相关的动态。

SELECT instance_name, status, startup_time
FROM v$instance;

INSTANCE_NAME    STATUS       STARTUP_TIME
---------------- ------------ -------------------
orcl             OPEN         2021-05-24 21:55:43

ORACLE主要常见的数据类型
char(x)：定长字符串，会用空格填充来达到其最大长度。最多可以存储2,000字节的信息。如果创建表时，不指定CHAR长度，则默认为1。
VARCHAR2(x) 变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。
LONG 可变长度字符串，最大大小为2GB-1。
RAW / LONG RAW：原始二进制数据 2000bytes or 2GB 的原始二进制数据
NUMBER(P,S)是最常见的数字类型。P是Precison的英文缩写，即精度缩写，表示有效数字的位数，最多不能超过38个有效数字。S是Scale的英文缩写，可以使用的范围为-84~127。
INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。
FLOAT(n),数 n 指示位的精度，可以存储的值的数目。N 值的范围可以从 1 到 126。
BINARY_FLOAT 是 32 位、单精度浮点数字数据类型。
BINARY_DOUBLE 是为 64 位，双精度浮点数字数据类型。

CLOB 存放较长的字符串数据最大大小为(4GB-1)*DB_BLOCK_SIZE的参数[数据块的大小]（8-128TB）
BLOB 它存储非结构化的二进制数据大对象，它可以被认为是没有字符集语义的比特流，一般是图像、声音、视频等文件。BLOB对象最多存储(4 gigabytes-1) * (database block size)的二进制数据。（8-128TB）
BFILE 二进制文件，存储在数据库外的系统文件，只读的，数据库会将该文件当二进制文件处理。

DATE：DATE是最常用的数据类型，日期数据类型存储日期和时间信息。Oracle 存储以下信息：世纪、年、月、日期、小时、分钟和秒。一般占用7个字节的存储空间。
TIMESTAMP 时间戳：这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒，带小数秒的TIMESTAMP在小数点右边最多可以保留9位。
TIMESTAMP WITH TIME ZONE 这是TIMESTAMP类型的变种，它包含了时区偏移量的值准
TIMESTAMP WITH LOCAL TIME ZONE 包含了时区的偏移量不过ORACLE按用户所在本地会话时间返回数据。

INTERVAL YEAR TO MONTH 这种数据类型通过使用YEAR 和 MONTH日期时间字段存储一个时段。默认的精度为2位数年数，如果间隔超过99年，定义字段的时候，需要INTERVAL YEAR(3) TO MONTH。
INTERVAL DAY TO SECOND 这种数据类型用于按照日、小时、分、秒存储时间段。默认的精度为2位数天数，如果间隔超过99天，定义字段的时候，需要INTERVAL YEAR(3) TO MONTH。

关于三种Timestamp数据类型的实验：

CREATE TABLE date_test (
               time1 timestamp,
               time2 timestamp with time zone,
               time3 timestamp with local time zone);

INSERT INTO date_test values (sysdate, sysdate, sysdate);

select * from date_test;

26-MAY-21 07.43.24.000000 PM  --timestamp
26-MAY-21 07.43.24.000000 PM +08:00   --timestamp with time zone
26-MAY-21 07.43.24.000000 PM  --timestamp with local time zone

--timestamp与timestamp with local time zone的结果相同

关于INTERVAL YEAR TO MONTH 的实验：

CREATE TABLE emp_retire (
                        id number(10) primary key,
                        hire_date date,
                        retire_date INTERVAL YEAR TO MONTH);

--插入INTERVAL为30年
INSERT INTO emp_retire VALUES (1, TO_DATE('2002-11-20', 'YYYY-MM-DD'), INTERVAL '30' YEAR);

--插入INTERVAL为300月
INSERT INTO emp_retire VALUES (2, TO_DATE('2000-5-20', 'YYYY-MM-DD'), INTERVAL  '300'  MONTH);

--插入INTERVAL为一年6个月
INSERT INTO emp_retire VALUES (3, TO_DATE('2000-1-1', 'YYYY-MM-DD'), INTERVAL  '1-6' YEAR TO  MONTH);

--查询数据结果

ID HIRE_DATE                        RETIRE_DATE
---------- -------------------- --------------------
         1 2002-11-20 00:00:00  +30-00
         2 2000-05-20 00:00:00  +25-00
         3 2000-01-01 00:00:00  +01-06

--使用date与interval合并
SELECT 'EMPLOYEE ID' || ID || ' WILL RETIRE ON ' || 
TO_CHAR(hire_date + retire_date, 'YYYY-MM-DD') AS "DESC" 
FROM EMP_RETIRE;


DESC
-----------------------------------------
EMPLOYEE ID1 WILL RETIRE ON 2032-11-20
EMPLOYEE ID2 WILL RETIRE ON 2025-05-20
EMPLOYEE ID3 WILL RETIRE ON 2001-07-01

关于INTERVAL DAY TO SECOND的实验

CREATE TABLE test_time (
                    current_time date,
                    time_interval INTERVAL DAY(3) TO SECOND);


INSERT INTO test_time VALUES (sysdate, INTERVAL '999' DAY(3));  --超过99天，需要DAY(3)
INSERT INTO test VALUES (sysdate, INTERVAL '5 5:00' DAY TO MINUTE);
INSERT INTO test_time VALUES (sysdate, INTERVAL '100 12:00:00' DAY(3) TO SECOND);


--查询数据
CURRENT_TIME         TIME_INTERVAL
-------------------- --------------------
2021-05-26 20:21:34  +199 00:00:00.000000
2021-05-26 20:22:38  +999 00:00:00.000000
2021-05-26 20:33:23  +100 12:00:00.000000

--合并时间与INTERVAL DAY TO SECOND
SELECT (CURRENT_TIME + TIME_INTERVAL) AS "DESC" FROM test_time;

DESC
-------------------
2021-12-11 20:21:34
2024-02-19 20:22:38
2021-09-04 08:33:23

CTAS创建表 CREATE TABLE AS SELECT

CREATE TABLE T2 AS SELECT * FROM T1; ---复制表结构和表数据
CREATE TABLE T2_1 AS SELECT * FROM T1 WHERE 1 = 2; ---只复制表结构
CREATE TABLE T2_2 AS SELECT * FROM T1 WHERE 1 = 1; ---复制表结构和表数据

ALTER TABLE语句
1. 添加新列
2. 修改现有列
3 为新列定义默认值
4 删除列

ALTER TABLE (表名) ADD (列名数据类型);

--增加两列数据
ALTER TABLE emp ADD (
                  age number(3),
                  comments varchar2(200) );

ALTER TABLE (表名) MODIFY (列名数据类型);

修改列的时候注意如下几点：
1 可以增大数字的宽度和精度
2 可以增大数字列或字符列的宽度
3 只有在列只包含空值或表没有行时才可以减少列的宽度
4 只有在列只包含空值时才能更改数据类型
5 只有列只包含空值或没有减少列的大小时，才可以将CHAR该成VARCHAR2。或将VARCHAR2改成CHAR
6 对列的默认值的更改影响以后对表的插入操作

SQL> alter table emp modify (
                    id char(12));

SQL> alter table emp modify (
                    hire_date date default To_date('2021-05-01', 'YYYY-MM-DD'

ALTER TABLE (表名) DROP COLUMN (列名);

删除列的时候注意如下几点：
1 要删除的可以包含数据也可以是空或者NULL
2 使用ALTER TABLE语句时每次尽量删除一个列
3 必须至少还剩下一个列
4 列一旦被删除就不能恢复

ALTER TABLE employees DROP COLUMN age;

SET UNUSED / DROP UNUSED COLUMNS
可以使用SET UNUSED选项讲一个或多个列标记为不适用
可以使用DROP UNUSED COLUMNS 删除标记为不可用的列

SQL> ALTER TABLE employees SET UNUSED (AGE);

Table altered.

SQL> ALTER TABLE employees DROP UNUSED COLUMNS;

Table altered.

ALTER TABLE (当前表名) RENAME TO (新表名);

改名时注意：
1 确认符合命名规范
2 名字不能重复
3 必须是该对象的所有者

ALTER TABLE emp RENAME TO employees;

TRUNCATE TABLE语句

DDL删除表的内容的方法

1 删除表的所有内容且不可再恢复
2 释放该表所有的存储空间降低高水位线

TRUNCATE TABLE employees;

Table truncated.

DROP TABLE语句

DROP TABLE不是把表真正的删除了而是把表改个名放到了回收站里。

DROP TABLE 表名 PURGE 真正的删除表，不进入回收站。

回收站内操作：

PURGE TABLE 表名彻底清空回收站内的表

PURGE RECYCLEBIN 彻底清空回收站

--DROP TABLE + PURGE TABLE

SQL> DROP TABLE test;

Table dropped.

SQL> PURGE TABLE test;

Table purged.

--DROP TABLE PURGE

DROP TABLE employees PURGE;

Table dropped.

COMMENT ON 给表名和列名添加备注

COMMENT ON TABLE employees IS 'This is a employee table';

COMMENT ON COLUMN employees.id IS 'This column records identity';

posted on 2021-06-01 10:49 LeoZhangJing 阅读(1363) 评论(0) 编辑收藏举报

刷新页面返回顶部

Oracle 19C学习 - 13. 创建与管理表

导航