All Products
Search
Document Center

:Create a table

更新時間:Feb 24, 2023

Overview

OceanBase Developer Center (ODC) allows you to create a table on a GUI. This topic describes how to create a table in the ODC console.

Create%20a%20table

As shown in the preceding figure, you can create a table in the following seven steps:

  1. Specify the basic information.

  2. Set columns.

  3. Set the index.

  4. Set constraints.

  5. Set partitioning rules.

  6. Confirm the SQL statement.

  7. Complete the table creation.

Procedure

In the following example, a table named employee is created in the ODC console. The table contains the emp_no, birthdate, name, and gender columns. Perform the following operations:

Step 1: Specify the basic information

Log on to the ODC console and click the name of the desired connection to go to the corresponding connection management page. You can click Table in the left-side navigation pane to get a list of tables. To create a table, click + in the upper-right corner of the table list or click Create in the top navigation bar.

In the Basic Info section, specify Table Name and Description.

Step%201
Note

  • In MySQL mode, you also need to specify Default Character Set and Default Sorting Rule.

  • After you specify the basic information and switch to the Column step, the basic information is submitted.

Step 2: Set columns

The following figure and table show the information that you need to specify when you add a column.

image

Parameter

Description

Name

The name of the column.

Type

The data type of the column.

For more information about data types, see OceanBase Database Developer Guide.

Length

The length of the data type.

Scale

The number of decimal places of the data type.

Non-empty

Specifies whether the value of the column must be specified.

Auto-increment

Specifies whether to set the column to an auto-increment column. This parameter is valid in MySQL mode.

Note

Only one auto-increment column is allowed for each table.

After a column is set to an auto-increment column, the auto-increment checkbox of other columns cannot be selected.

Virtual Column

Specifies whether to set the column to a virtual column.

Note

When you set a virtual column, you must specify its expression.

When you create a virtual column, you must define an expression that the virtual column depends on. A virtual column is a generated column. A generated column can be a virtual generated column or a stored generated column. The value of a virtual column is calculated based on the expression only when the column is used. Therefore, when you insert data into the table, you cannot specify values for the virtual column.

Default Value/Expression

The default value or expression of the column.

Comment

The additional information about the column.

The following table describes operations that you can perform on the column setting page.

Button

Description

Toolbar operations

You can add or delete columns by using the top toolbar of the page.

Click a row ID

  • You can click a row ID to select the row and display the quick access toolbar that allows you to add or delete a row.

  • You can click a row ID to select the row, and then drag the row to adjust its order.

Right-click a row

You can right-click a row to select it, and then select Copy or Move Down from the context menu that appears.

Note
  • After you copy a row, you can paste the row by using the Command + V or Ctrl + V shortcut keys.

  • The auxiliary editing section at the bottom of the page displays additional information about the selected column.

  • Settings in the Basic Information and Column steps are required. Settings in other configuration steps are optional. After you specify settings in the Basic Information and Column steps, you can click Commit and Confirm SQL Statement to create a table.

Step 3: Set indexes

If a table contains a large amount of data, you can use indexes to accelerate data queries. An index is a data structure that pre-sorts the values of one or more columns in a table. By using indexes, you can directly locate records that meet the conditions.

The following figure and table show the information that you need to specify when you set the index.

image

Field

Description

Index Name

The name of the index.

Scope

The default value is GLOBAL. Valid values: GLOBAL and LOCAL.

Method

The default value is BTREE, which is applicable to a global index. Valid values: Empty, BTREE, and HASH.

  • A BTREE index stores data in a B+ tree structure, and is suitable for searching for SQL statements in the specified index scope.

  • A HASH index is implemented based on a HASH table, and is used only when the query conditions exactly match all columns in the HASH index. A HASH index can be used only for query conditions that use the equal operator (=), IN operator, or NULL-safe equal operator (<=>). A HASH index cannot be used for sorting and is not suitable for columns with less distinctive values, such as the gender column.

Index Type

The default value is NORMAL. Valid values: NORMAL, UNIQUE, and FULLTEXT.

  • NORMAL indicates a normal index.

  • UNIQUE indicates a unique index that does not allow duplicate values, such as the emp_no column.

  • FULLTEXT indicates a full-text index, which is used to search a table that contains a large amount of data.

Columns

The columns to be indexed. Pay attention to the order of the indexed columns.

Invisible

Specify whether the index is invisible.

The index setting page provides the following icons.

Icon

Description

Create

Click this icon to add an index.

Delete

Click this icon to delete the selected index.

Step 4: Set constraints

Constraints are used to specify data rules for a table. A data operation that violates the constraints will be terminated.

image

ODC supports the following four table constraints:

  • Primary Key Constraint: defines a primary key to uniquely identify each row of data in the table. A primary key constraint can be a field or a group of fields. You can set only one primary key constraint for a table, and you cannot edit the constraint after it is configured.

  • Unique Constraint: ensures that the data in a field or a group of fields is unique in the table. You can set multiple unique constraints in one table.

  • Foreign Key Constraint: associates one or more columns in two tables. A foreign key constraint is used to maintain the data consistency and integrity between associated tables. After you complete the setting of foreign key constraints, you cannot create new constraints or edit existing constraints.

  • Check Constraints: checks the data in the database based on the configured check rules when you edit the data. Data modification is allowed only after the check is passed.

OceanBase Database supports different constraints in MySQL and Oracle modes, and different constraints require different information. Therefore, you need to specify the required information based on the constraint you selected and the requirements on the page.

Field

Description

Constraint Name

The name of the constraint.

Column Information

The field or a group of fields specified as the constraint.

Associated Schema

The schema where the associated table is located when Foreign Key Constraint is used. The associated table is the parent table. This field is valid only in Oracle mode.

Associated Database

The database where the associated table is located when Foreign Key Constraint is used. The associated table is the parent table. This field is valid only in MySQL mode.

Associated Table

The associated table when Foreign Key Constraint is used. The associated table is the parent table.

Associated Field

The associated field when Foreign Key Constraint is used. The associated field is in the parent table.

Delete

Specifies the action to be performed on the current table when the data in the associated table is deleted. The current table is the child table, and the associated table is the parent table.

The following four types of action are supported: CASCADE, NO ACTION, RESTRICT, and SET NULL. OceanBase Database supports different foreign key actions in MySQL and Oracle modes.

  • In MySQL mode, OceanBase Database allows you to specify CASCADE, NO ACTION, RESTRICT, and SET NULL.

  • In Oracle mode, OceanBase Database allows you to specify CASCADE, NO ACTION, and SET NULL.

Important

At present, OceanBase Database does not support SET NULL.

For more information about the syntax, see Create a table, or the official documentation of MySQL or Oracle.

Update

Specifies the action to be performed on the current table when the data in the associated table is updated. The current table is the child table, and the associated table is the parent table.

The following four types of action are supported: CASCADE, NO ACTION, RESTRICT, and SET NULL.

OceanBase Database supports different foreign key actions in MySQL and Oracle modes.

  • In MySQL mode, OceanBase Database allows you to specify CASCADE, NO ACTION, RESTRICT, and SET NULL.

  • In Oracle mode, OceanBase Database allows you to specify NO ACTION.

Important

At present, OceanBase Database does not support SET NULL.

For more information about the syntax, see Create a table, or the official documentation of MySQL or Oracle.

Check Condition

Specifies the check rules for data verification when Check Constraint is enabled.

The constraint setting page provides the following icons.

Icon

Description

Create

Click this icon to add a constraint.

Delete

Click this icon to delete the selected constraint.

Step 5: Set partitioning rules

image

You can partition a table that contains a large amount of data. After a table is partitioned, data in the table is stored in multiple tablespaces. The database does not scan the entire table for a query.

  • In MySQL mode, OceanBase Database supports the following six partitioning methods: Key, Hash, Range, Range Columns, List, and List Columns.

  • In Oracle mode, OceanBase Database supports the following three partitioning methods: List, Range, and Hash.

    The definition of a partition in MySQL mode is different from that in Oracle mode. So, the values of the following fields vary in different modes. You need to specify the following fields based on the selected partitioning method.

    Field

    Description

    Partitioning Method

    Specifies the partitioning method. The partitioning methods supported in MySQL mode are different from those in Oracle mode.

    • Range partitioning: Multiple rows with column values in an ordered, continuous, and non-overlapping range are assigned to a partition.

    • List partitioning: A table is partitioned based on the enumeration values.

    • Hash partitioning: A table is partitioned based on a given number of partitions. For a table that stores data without a clear pattern or range, you can use hash partitioning to randomly distribute values of the partition column to different partitions based on the HASH algorithm.

    • Key partitioning: This partitioning method is similar to HASH partitioning. The difference is that key partitioning only supports computation of one or more columns, and the MySQL server provides its own hash function. At least one INT type column is required for Key partitioning.

    For more information about partitioning, see Overview.

    Field

    The column that is used as the partition key.

    Expression

    Partitions are divided based on the return value of the expression. The partition expression is not supported in Oracle mode.

    Partition

    Based on the value specified for Partitioning Method, you may need to specify information such as Partition Name, Partition Quantity, Upper Limit, and Value Enumeration. You can add multiple partitions and drag selected fields to adjust their order.

Step 6: Confirm the SQL statement

image

After you click Submit, you can view the statement on the SQL Confirmation page. To view lengthy clauses, click SQL Check and Format in the lower-right corner of the page.

Syntax:

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

Parameters

Parameter

Description

CREATE TABLE

The key word that indicates the creation ofa table with a specified name. You must have the privileges to create tables.

table_name

The name of the table. The table name must conform to the identifier naming rules.

column_name column_type

The name and data type of the column in the table. To create multiple columns, separate the tuple with commas (,).

Step 7: Complete the table creation

Click Execute. After the table is created, the employee table appears in the table list in the left-side navigation pane.

Step%207.1
Note

To manage a table, right-click the table name in the left-side navigation pane, and select the required operation from the context menu, which provides the following options: View Table Structure, View DDL, View Table Data, CreateTable, Import, Export, Download, Mock Data, Open SQL Window, Copy, Rename, Delete, and Refresh.

For more information, see Manage table data.

You can use the SELECT statement to query data in the new table.

Syntax:

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

Parameters

Parameter

Description

SELECT

The key word that indicates the name of the statement. You can use the SELECT statement to read one or more records.

column_name

The name of the column to be queried. You can use an asterisk (*) to query all columns

WHERE

The key word of the condition clause.

LIMIT

The number of records to be returned.

OFFSET

The data offset from which the SELECT statement starts to query. The default offset is 0.

Example:

SELECT `emp_no`, `birthdate`, `name`, `gender` FROM `employee`;
Step%207.2