Overview
A table is a data structure in a database and stores data in rows and columns.
As shown in the preceding figure, you can create a table in the following seven steps:
Specify the basic information.
Set columns.
Set partitioning rules.
Set the index.
Set constraints.
Verify the SQL statement.
Complete the table creation.
Procedure
In the following example, a table named employee is created in the OceanBase Developer Center (ODC) console. The table contains the emp_no, birthdate, name, and gender columns. Procedure:
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.
Note
In MySQL mode, you also need to specify Default Character Set and Default Sorting Rule.
Step 2: Set columns.
The following figure and table show the information that you need to specify when you add a column.
| Field | Description |
|---|---|
| Field Name | The name of the column. |
| Data Type | The data type of the column. For more information about data types, see OceanBase Database Developer Guide. |
| 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. |
| Default Value | The default value of the column. |
| Comment | The additional information about the column. |
The column setting section also provides three buttons.
| Button | Description |
|---|---|
| Create | Click this button to add a field. |
| Edit | Click this button to edit the selected field. You can also directly double-click a cell to edit it. |
| Delete | Click this button to delete the selected field. |
Step 3: Set partitioning rules.
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. |
| 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 of the range, and Enumeration Value. Note - When selecting the Range partitioning method, if the table contains a large amount of data, you can fill in MAXVALUE in Upper limit of the range, which means that the upper limit of the range is not set. - You can add multiple partitions and drag the partitions to adjust their order. |
Step 4: Set the index.
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.
| Field | Description |
|---|---|
| Index Name | The name of the index. |
| Index Range | The default value is GLOBAL, which indicates a global index. You can set this parameter to LOCAL only for tables with partitions. The value LOCAL indicates a local index. |
| Index Type | Only BTree is supported. |
| Optional Field | The columns to be indexed. Pay attention to the order of the indexed columns. |
| Unique | Specifies whether the index is unique. In other words, the index is used to ensure that the constraint is unique. |
The index setting section also provides three buttons.
| Button | Description |
|---|---|
| Create | Click this button to add an index. |
| Edit | Click this button to edit the selected index. You can also directly double-click a cell to edit it. |
| Delete | Click this button to delete the selected index. |
Step 5: Set constraints.
Constraints are used to specify data rules for a table. A data operation that violates the constraints will be terminated.
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 Constraint: 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 Check Constraint only in Oracle mode.
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. |
| Trigger State | Specifies whether to enable the created constraint. In some cases, you may need to temporarily disable the constraints. For example, when you import a large amount of data, you can disable constraints to improve the import efficiency. This field is valid only in Oracle mode. |
| Deferrable | Supports three types of delay status. Valid values: Verify Now , Non-deferrable , and Delayed Verification. This field is valid only in Oracle mode. |
| 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. Notice At present, OceanBase Database does not support SET NULL. For more information about the syntax, see CREATE 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. Notice At present, OceanBase Database does not support SET NULL. For more information about the syntax, see CREATE TABLE, or the official documentation of MySQL or Oracle. |
| Check Condition | Specifies the check rules for data verification when Check Constraint is enabled. This field is valid only in Oracle mode. |
The constraint setting section also provides three buttons.
| Button | Description |
|---|---|
| Create | Click this button to add a constraint. |
| Edit | Click this button to edit the selected constraint. You can also directly double-click a cell to edit it. |
| Delete | Click this button to delete the selected constraint. |
Step 6: Verify the SQL statement.
Click Submit to go to the SQL Confirmation page.
You can edit the SQL statement on the SQL Confirmation page. Syntax:
CREATE TABLE table_name (column_name column_type, column_name column_type,.......);
| 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 or field in the table. To create multiple columns, separate the tuple with commas (,). |
Step 7: Complete the table creation.
Click Run. After the table is created, the employee table appears in the table list in the left-side navigation pane.
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, Create, **Single Table Import **, Single Table 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]
| 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`;
