Perform common SQL operations on tables and data - PolarDB

This topic covers common SQL statements for managing databases, tables, indexes, and data on a PolarDB-X instance.

Operations fall into two categories:

DDL (Data Definition Language) — defines database structures: CREATE, ALTER, and DROP for tables and indexes.
DML (Data Manipulation Language) — manipulates data records: INSERT, SELECT, UPDATE, and DELETE.

Note

The examples in this topic use databases in Distributed Relational Database Service (DRDS) mode. For details about DRDS mode and AUTO mode, see Databases in AUTO mode and DRDS mode.

View databases

Run the following statement to list all databases in the current PolarDB-X instance:

SHOW DATABASES;

Manage tables

Create a table

PolarDB-X supports single tables and hash-partitioned tables.

Create a single table

CREATE TABLE single_tbl(
  id int,
  name varchar(30),
  primary key(id)
);

Create a hash-partitioned table

The following statement creates a hash-partitioned table that distributes data across database shards based on the id column:

CREATE TABLE multi_db_single_tbl(
  id int auto_increment,
  name varchar(30),
  primary key(id)
) dbpartition by hash(id);

View a table definition

Run SHOW CREATE TABLE to inspect the DDL statement used to create a table:

-- View definition of a single table
SHOW CREATE TABLE single_tbl;

-- View definition of a hash-partitioned table
SHOW CREATE TABLE multi_db_single_tbl;

List all tables

SHOW TABLES;

Delete a table

Warning

Dropping a table may cause service interruptions. Proceed with caution.

-- Drop a single table
DROP TABLE single_tbl;

-- Drop a hash-partitioned table
DROP TABLE multi_db_single_tbl;

Manage columns

Use ALTER TABLE to add, modify, or drop columns.

Add a column

ALTER TABLE multi_db_single_tbl ADD COLUMN textcol text;

Change a column's data type

ALTER TABLE multi_db_single_tbl MODIFY COLUMN textcol varchar(40);

Drop a column

ALTER TABLE multi_db_single_tbl DROP COLUMN textcol;

Manage local indexes

Create a local index

Use either of the following statements to create a local index on the name column:

CREATE INDEX idx_name ON multi_db_single_tbl (name);

ALTER TABLE multi_db_single_tbl ADD INDEX idx_name(name);

View indexes

SHOW INDEX FROM multi_db_single_tbl;

Drop a local index

Use either of the following statements:

DROP INDEX idx_name ON multi_db_single_tbl;

ALTER TABLE multi_db_single_tbl DROP INDEX idx_name;

Work with table data

Insert data

Insert a single row

INSERT INTO multi_db_single_tbl (name) VALUES ('test_name');

Insert multiple rows in one statement

INSERT INTO multi_db_single_tbl (name) VALUES ('test_name'), ('test_namexx');

Query data

SELECT * FROM multi_db_single_tbl;

Update data

UPDATE multi_db_single_tbl SET name = 'zzz' WHERE id IN (100001, 100002, 100003, 100004);

Delete data

DELETE FROM multi_db_single_tbl WHERE id = 100002;

Warning

An UPDATE or DELETE statement without a WHERE clause affects every row in the table. Always include a WHERE clause unless you intend a full-table operation.