30 views

如何在SQL中创建,更改和删除表

By | 2019年2月5日

您正在构建一个新的应用程序。因此,您需要在某处存储数据。是时候在数据库中创建一个表了!

在这篇文章中,您将了解如何:

创建新表
更改(更改)现有表格
删除(删除)一个表
如何创建表
基本的create table语句采用以下形式:

create table <table_name> (
<column1> <data type>,
<column2> <data type>,
<column3> <data type>,

);
因此,要创建一个名为toys的表,其中包含toy_name,weight和color列,请运行:

create table toys (
toy_name varchar2(10),
weight number,
colour varchar2(10)
);
Oracle数据库有许多数据类型 可供选择。常见数据类型有:

数字 – 存储数字数据:价格,重量,距离等。
日期 – 保存日期和时间信息
Varchar2 – 用于通用文本; 名称,描述等
为您将存储在列中的值选择最合适的类型。选择错误的类型可能会导致查询速度慢,结果错误以及安全漏洞。

注意:Create table是数据定义语言(DDL)语句的一种形式。这些更改了数据库中的对象。Oracle数据库在DDL之前和之后运行提交。因此,如果创建有效,它将保存到您的数据库中。

您还可以基于select语句创建表。这使得表与源查询具有相同的列和行。此操作称为create-table-as-select(CTAS)。

这是将一个表复制到另一个表的便捷方法。例如,以下内容从玩具中创建toys_clone:

create table toys_clone as
select * from toys;
容易,对吗?

是。但是,和往常一样,它还有更多。您需要为表添加一些约束。Oracle数据库中有许多类型的表,包括:

表组织:

指数
外部
临时表
分区
表集群
这些会影响数据库物理存储数据的方式。这会对性能产生重大影响。

数据库表往往会持续很长时间。更改存储数百万行的表的类型很棘手。所以值得花几分钟时间来决定你需要什么。

有关这些类型的概述,请观看此视频,该视频取自Databases for Developers的第一个模块:基础:

堆组织表
这是Oracle数据库中表的缺省值。但是如果你想要明确,最后添加“组织堆”子句:

create table toys (
toy_name varchar2(10),
weight number,
colour varchar2(10)
) organization heap;
堆是很好的通用表。它们是您在Oracle数据库安装中看到的最常见类型。

有了这些,数据库可以在有空间的地方自由存储新行。因此,如果您阅读十行,它们可能位于磁盘上的任何位置。

如果你很幸运,他们都在同一个地方。因此查询可以在一次磁盘中获取它们。

但它不能保证。每行可以位于不同的位置。这意味着您需要十次I / O操作才能全部读取它们。

如果您希望查询尽可能快,这是个坏消息。SQL执行的磁盘读取越多,它就越慢。即使行缓存在内存中,访问十个内存地址也比访问一个要慢。

幸运的是,您可以强制数据库在同一位置存储具有相似值的行。这可以减少查询为获取它们所做的工作。让你的SQL更快!

要强制执行此物理订单,您需要更改表的属性。我们要看的第一个是索引组织表。

索引组织表(IOT)
索引是有序数据结构。因此,IOT存储根据其主键物理排序的行。

注意:主键(PK)是约束。其列中的每组值只能出现一次。所以你不能有重复。它也有一个非空约束。并在后台创建一个独特的索引。

要创建一个,请将组织索引子句添加到表定义中:

create table toys (
toy_name varchar2(10) primary key,
weight number,
colour varchar2(10)
) organization index;
那么为什么要使用它而不是默认的堆表呢?

有几个原因。

首先,当使用堆组织时,表及其主键索引是单独的数据结构。物联网将这些结合为一体。

这可以减少一些开销。

您不再需要主键的额外索引。这可以为您节省一些空间。

而使用主键访问行的SQL只需要访问一个结构。而不是两个。所以这些查询的速度要快一点。

但最大的优势(IMO)来自具有多列主键的表。例如,多对多连接表。如客户到他们的地址:

create table customer_addresses (
customer_id integer,
address_id integer,
primary key ( customer_id, address_id )
) organization index;
这将存储按customer_id排序的值,然后存储address_id。因此,客户1的所有地址都在磁盘上彼此相邻。

因此,如果您搜索此客户的所有地址,请执行以下操作:

select *
from customers
where customer_id = 1;
您知道所有行都位于相同的几个位置。让你的SQL更快一点。

而对于堆表,数据库可以将它们存储在任何地方。

您可以将此原则扩展到具有复合PK的任何表。并且您(几乎总是)搜索PK的第一列等于某个值的行。

这对于主 – 细节关系中的表是常见的。例如:

订单和订单商品
发票和发票行
航班行程及其航班
在这里,您通常从详细信息表中获取与父级中的行匹配的行。如订单中的所有产品。

请记住,要使用IOT,表必须具有主键。因此,您要排序的值必须是唯一的。如果他们不是,你可以通过创建一个假的主键来解决这个问题。但这是一种利基技术。只有在你确定自己在做什么的时候才使用!

您还可以使用分区或表群集对数据执行订单。但是,让我们首先讨论组织条款。

外部表格

组织条款的最终选项是外部的。您可以使用它来读取存储在数据库文件系统中的文本文件。这使您可以使用SQL将CSV或其他格式化文件读入数据库。

要创建一个,您必须具有目录对象。这指向文件所在的文件夹:

create or replace directory ext_files as ‘/path/to/files’;
要读取/ path / to / files中的toys.csv文件,请使用此目录并定义文件,如下所示:

create table toys_csv (
toy_name varchar2(10),
weight number,
colour varchar2(10)
) organization external (
default directory ext_files
location ( ‘toys.csv’ )
);
现在,当您查询toys_csv时,您正在读取toys.csv文件中的记录。

创建外部表有很多选项。如果您正在阅读的文件不是简单的CSV,请根据需要设置外部子句。

外部表非常适合使用SQL加载文件。但有时您可能需要按摩数据,然后将其保存在真实的表格中。或者需要在加载期间多次读取相同的记录。

这样做时,将数据放在私人工作区域是很方便的。在某处你可以存储只有你能看到的行。

输入:

临时表
临时表中的行对您的会话是私有的。只有你可以查看它们。并且,一旦断开连接,数据库将删除您添加的所有行。

Oracle数据库有两种类型的临时表:全局(GTT)和私有(PTT)。

全球临时表(GTT)
创建全局临时表的语法是:

create global temporary table toys_gtt (
toy_name varchar2(10),
weight number,
colour varchar2(10)
);
默认情况下,数据库将自动删除每个事务结束时的所有行。所以一旦你提交,它就是空的!

如果需要跨事务保留行,请设置on commit子句以保留行:

create global temporary table toys_gtt_keep (
toy_name varchar2(10),
weight number,
colour varchar2(10)
) on commit preserve rows;
全局临时表在整个数据库中具有标准定义。您创建一次并将其保留在您的应用程序中。这与以下形成鲜明对比:

私人临时表(PTT)
PTT是Oracle Database 18c中的新增功能。有了这些,不仅将行隐藏在其他会话中,表本身也是如此!

以下创建一个名为ora $ ptt_toys的PTT:

create private temporary table ora$ptt_toys (
toy_name varchar2(10),
weight number,
colour varchar2(10)
);
注意:表名的含义ora $ ptt_前缀。这必须匹配数据库的private_temp_table_prefix参数设置的任何内容。否则它将无法正常工作!此外,与所有其他形式的DDL不同,创建私有临时表不会提交!

默认情况下,这些仅在事务持续时间内持续。一旦提交(或回滚),表就会消失。

如果需要它持续连接的长度,请设置on commit子句以保留定义:

create private temporary table ora$ptt_toys_keep (
toy_name varchar2(10),
weight number,
colour varchar2(10)
) on commit preserve definition;
与其他所有类型的表不同,两个人可以使用相同的名称同时创建PTT。但是不同的专栏!如果您需要从动态数据源中暂存值,则非常方便。

分区

随着表中行数的增加,管理它们变得更加困难。查询可能变得迟缓,添加索引需要更长时间,并且旧数据的归档速度很慢。

分区通过允许您在逻辑上将表拆分为几个较小的表来解决这些问题。但仍然可以将这些作为一个表访问。

您甚至可以使用子分区进一步细分每个分区。

注意:要对表进行分区,您必须购买分区选项。潜水前检查你有这个!

要对表进行分区,请选择分区列和方法。支持的方式包括:

范围 – 每个分区都有一个上限。值小于此且大于或等于前一个边界的行将进入此分区
列表 – 准确说明每个分区中的值
散列 – 使用内部函数来选择放置行的分区
向表中添加行时,它们将进入相关分区。

例如,您可能希望按颜色分割玩具。为此,请按此列列出对其进行分区。并定义每个分区中的值:

create table toys_partitioned (
toy_name varchar2(10),
weight number,
colour varchar2(10)
) partition by list ( colour ) partitions (
partition p_green values ( ‘green’ ),
partition p_red values ( ‘red’ ),
partition p_blue values ( ‘blue’ )
);
每个分区仅存储行的颜色值与分区值匹配的行。搜索给定颜色的查询现在只能访问该分区。

因此,如果您搜索所有红色行,则数据库知道它们都位于同一位置。这可以让你的SQL更快一点。

您还可以使用分区交换从另一个表快速将行加载到分区中。但是要使用它,这两个表必须具有相同的结构。这可能很难验证。

因此,Oracle Database 12.2引入了一个新的create table选项。创建表,用于交换:

create table toys_stage
for exchange with table toys_partitioned;
这使得非分区表具有与源相同的结构。确保您可以使用分区表进行交换。

分区可以带来巨大的好处。但是弄错了,你可以让你的数据库变得无法维护!在进入之前阅读优点和缺点。

要了解更多信息,请阅读我的同事Connor 为开发人员分区的指南。

到目前为止,我们已经处理了对一个表更快的查询。但是如果你想同时从两个表中获取行呢?

考虑:

表集群
连接表是数据库中最常见的操作之一。为此,首先数据库从一个表中读取行。然后在另一个中找到与连接条件匹配的行。

因此,您需要至少两次读取才能获取数据。每张桌子一个。在实践中,连接可以做更多。

表簇通过在同一位置存储来自不同表的行来避免这种“连接惩罚”。来自每个表的群集键具有相同值的行位于相同位置。这意味着您可以获得“两个价格合理”的访问权限。您可以查询每个表的I / O操作,而不是一次性获取所有行。

假设您有一个颜色查找表。而且你经常将颜色加入玩具桌。按颜色对这些表进行聚类可确保数据库在同一位置存储具有相同颜色的行。

要使用表群集,首先需要创建群集。这可以是哈希或索引群集。以下内容创建了一个哈希集群:

create cluster colour_clus (
colour varchar2(10)
) hashkeys 1024;
群集的列是它的关键。通过将cluster子句添加到create table来将表放入此表中。在此处说明群集列。它们必须具有与群集中相同的数据类型。
以下内容将colours_clustered和toys_clustered添加到colour_clus:

create table colours_clustered (
colour varchar2(10),
rgb_hex_value varchar2(6)
) cluster colour_clus ( colour );

create table toys_clustered (
toy_name varchar2(10),
weight number,
colour varchar2(10)
) cluster colour_clus ( colour );
现在,如果在colours_clustered和toys_clustered中插入颜色为红色的行,数据库会将它们粘贴在同一位置。

表群集是一项高级功能。虽然他们可以更快地加入,但他们有几个警告。在潜入之前阅读它们!

那么我应该创建哪种类型?!
唷!

那是很多选择!而且还有其他一些更专业的类型。有关选项的完整列表,请阅读“概念指南”中的表和表群集。或者查看完整的create table语法。

所以现在你可能想知道:我应该使用哪种类型?

在大多数情况下,堆表是可行的方法。这些是最通用的。

但想想你将如何访问表中的行。您是否有一两个需要尽可能快的查询?如果是这样,索引组织或分区表可能是要走的路。

或者它是否需要支持不同列上的许多查询?在这种情况下,默认堆表是更好的选择。

请记住,您可以结合一些选项。例如,您可以拥有分区的IOT。知道使用哪个将来自经验。但更重要的是测试!

因此,请熟悉可用的表类型。和他们一起玩,看看他们是如何工作的。构建新功能时,请尝试不同的表设置。并测试他们的表现。

但是创建新表只是数据库开发的一小部分。通常,您需要更改现有表格。现在是时候找出如何:

如何改变表格

所以你创造了一个闪亮的新桌子。但是你的大部分开发机会都是针对现有的表。您需要扩展的表以存储新信息。所以你需要添加额外的列。

如何添加列
要向表中添加列,需要alter table语句。这将列出您要更改的表,以及要添加的列的名称和数据类型。因此,要为玩具添加价格列,请运行:

alter table toys add ( price number );
如果要添加多个列,可以在add子句中列出它们:

alter table toys add (
cuddliness_factor integer,
quantity_of_stuffing integer
);
所以这很容易。但是你不再需要的列呢?你怎么摆脱它们?

如何删除列
您可能偶尔会从表中删除列。也许业务已经意识到每个玩具都有很多颜色。所以没有必要将它存放在玩具桌上。因此,您要删除此列。

您可以使用alter table drop命令执行此操作:

alter table toys drop ( weight );
但要小心!

这是一项昂贵的操作。在存储数百万行的表上,运行需要很长时间。它阻止了对表的其他更改。因此,运行时您的应用程序可能无法使用!

更好的选择是将列设置为unused:

alter table toys set unused column weight;
这是一个即时操作。无论表中有多少行,都需要相同的时间。

这是因为它没有从数据库中物理删除列。它标志着它们不可用。数据仍然存在。你无法达到它!

如果您希望回收这些列使用的空间,则需要从表中擦除它们。使用以下命令执行此操作:

alter table toys drop ununsed columns;
由于您现在正在进行删除数据的工作,这可能需要很长时间。这里的关键区别是丢弃未使用的列是非阻塞的。您的应用程序可以继续正常运行。

无论使用哪种方法,在删除列时都要小心。删除列或将其设置为未使用都是单向操作。没有“undrop column”命令。如果你犯了一个错误,你将不得不从备份恢复表!

因此,如果您要删除一些列,这会有所帮助。但是如果你想废弃整张桌子怎么办?

是时候放弃了!

如何删除表
有时您可能想要删除整个表。为此,请删除表格,如下所示:

drop table toys;
这将删除整个表及其数据。就像删除列一样,这是一个单向操作。

但是,幸运的是,如果您启用了Oracle数据库的闪回功能,则可以取消删除表格!所以如果你不小心在生产中运行你的回滚脚本,你可以再次开始:)

发表评论

电子邮件地址不会被公开。 必填项已用*标注