新建表

新建内部表

本章节以创建表为例，介绍如何在 SQL 窗口中创建表 employee 到数据库 odc_test 中，员工表中包含员工工号（emp_no）、员工生日（birthday）、员工姓名（name）和员工性别（gender）。

步骤一：指定基本信息

1. 登录数据库并进入 SQL 开发窗口后，在左侧导航栏中单击 表 标签可查看表列表。在表列表的右上角，单击 + 以创建表对象。

2. 在 基本信息 中，输入 表名称、选择 存储模式 和表的 描述。

   

   说明

   • 在 MySQL 模式下，同时需要选择默认字符集和默认排序规则。

   • 完成指定基本信息并切换至设置列步骤后，表示确认提交基本信息。

步骤二：设置列

如下图所示，添加一列时需指定以下信息：

设置列页面提供 3 种功能操作：

步骤三：设置索引

当表中包含大量数据，可以使用索引更快速的查询数据。索引是表中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以直接定位到符合条件的记录。

如下图所示，需指定以下信息：

步骤四：设置约束

约束用于规定表中的数据规则。如果存在违反约束的数据行为，该行为会被约束终止。

ODC 中支持设置的表级约束包括以下 4 种：

• 主键约束：定义一个主键来唯一标识表中的每一行数据。主键约束可为一个字段或是一组字段，一张表中只能设置一个主键约束，且设置完成后不支持编辑。

• 唯一约束：保证在一个字段或者一组字段里的数据在表中是唯一的，一张表中可设置多个唯一约束。

• 外键约束：在两个表的数据之间建立连接（可为一列或多列），旨在保持关联表之间数据的一致性、完整性。设置完成后不支持新增和编辑。

• 检查约束：在编辑数据库数据时，按照设置的检查规则进行校验，校验通过才允许数据修改操作。

步骤五：设置分区规则

当表中包含大量数据时，可以对表进行分区。表进行分区后，表中的数据会存放到多个表空间，每次查询数据时不会扫描整张表。

• OceanBase 中 MySQL 模式支持六种分区方法：key、Hash、Range、Range Columns、List 和 List Columns。

• Oracle 模式支持三种分区方法：List、Range 和 Hash。

步骤六：确认 SQL

单击 提交 后，在 SQL 确认页面查看语句，支持 SQL 检查 和 格式化 语句以方便查看。

语法格式如下：

CREATE TABLE table_name (column_name column_type, column_name column_type,.......);

参数说明：

步骤七：完成新建表

单击 执行，完成新建表后，会在左侧导航栏的表列表中出现新建的 employee 表。

支持使用关键字SELECT查询新建的表数据。

语法格式：

SELECT
 column_name,
 column_name
FROM
 table_name [WHERE Clause] [LIMIT N] [ OFFSET M]

参数说明：

示例：

SELECT `emp_no`, `birthdate`, `name`, `gender` FROM `employee`;

新建外表

本章节以创建存储服务 OSS 外部表 employee 为例，介绍如何在 SQL 窗口中创建外部表 employee 到数据库 odc_test 中，您可以将 OSS 上指定的文件路径映射到 ODC 数据库 odc_test 中。

1. 准备外部文件。

   1. 在 使用阿里云账号登录OSS管理控制台上新建目录 external table。

      

   2. 上传文件 employee.csv 到目录 external table 中。

      

   3. 获取外表 employee 存放路径：https://ob*********.oss-cn-shanghai.aliyuncs.com/doc/img/odc/433/external%20table/employee.csv。

      

2. 在 ODC SQL 窗口中，执行以下 SQL 命令创建外表 employee。

   

   CREATE EXTERNAL TABLE `employee` (`emp_no` int(120),`birthday` date,`name` varchar(120))
      LOCATION = 'ob*********.oss-cn-shanghai.aliyuncs.com/doc/img/odc/433/500.sql-development/700.database-objects/100.web-odc-table-objects/employee/employee.csv'
      FORMAT = (TYPE = 'CSV'
         FIELD_DELIMITER = ','
         FIELD_OPTIONALLY_ENCLOSED_BY ='"'
         ENCODING = 'utf8mb4')
      PATTERN = 'employee.csv';

   • LOCATION 选项用于指定外表文件存放的路径。

     • 本地 Location 格式：LOCATION = '[file://] local_file_path'。对于使用本地 Location 格式的场景，需设置系统变量 secure_file_priv 配置可以访问的路径。更多信息，请参见 secure_file_priv。

     • 远程 Location 格式：LOCATION = '{oss|cos|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'，$ACCESS_ID、$ACCESS_KEY 和 $HOST 为访问阿里云 OSS、腾讯云 COS 和 S3 所需配置的访问信息。

   • FORMAT = ( TYPE = 'CSV'... ) 用于指定外部文件的格式为 CSV 类型。参数如下：

     • TYPE：指定外部文件的类型。

     • LINE_DELIMITER：指定 CSV 文件的行分隔符。默认值为 LINE_DELIMITER='\n'。

     • FIELD_DELIMITER：指定 CSV 文件的列分隔符。默认值为 FIELD_DELIMITER='\t'。

     • ESCAPE：指定 CSV 文件的转义符号，只能为 1 个字节。默认值为 ESCAPE ='\'。

     • FIELD_OPTIONALLY_ENCLOSED_BY：指定 CSV 文件中包裹字段值的符号。默认值为空。

     • ENCODING：指定文件的字符集编码格式，当前 MySQL 模式支持的所有字符集请参见 字符集。如果不指定，默认值为 UTF8MB4。

     • NULL_IF：指定被当作 NULL 处理的字符串。默认值为空。

     • SKIP_HEADER：跳过文件头，并指定跳过的行数。

     • SKIP_BLANK_LINES：指定是否跳过空白行。默认值为 FALSE，表示不跳过空白的行。

     • TRIM_SPACE：指定是否删除文件中字段的头部和尾部空格。默认值为 FALSE，表示不删除文件中字段头尾的空格。

     • EMPTY_FIELD_AS_NULL：指定是否将空字符串当作 NULL 处理。默认值为 FALSE，表示不将空字符串当做 NULL 处理。

   • PATTERN 选项用于指定一个正则模式串，用于过滤 LOCATION 目录下的文件。对于每个 LOCATION 目录下的文件路径，如果能够匹配该模式串，外表会访问这个文件，否则外表会跳过这个文件。如果不指定该参数，则默认可以访问 LOCATION 目录下的所有文件。外表会将 LOCATION 指定路径下满足 PATTERN 的文件列表保存在数据库系统表中，外表扫描时会根据这个列表来访问外部的文件。

3. 在左侧导航栏的外表列表中查看 employee 表。