49 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)
);
默认情况下,数据库将自动删除每个事务结束时的所有行。所以一旦你提交