Use common SQL statements for database management - PolarDB

This topic covers common SQL statements for PolarDB-X 1.0, including database and table management, column and index operations, data manipulation, and user management.

SQL statements fall into four categories:

DDL (Data Definition Language): Define and modify database structures — databases, tables, columns, and indexes. Key statements: CREATE, ALTER, DROP, SHOW.
DML (Data Manipulation Language): Insert, update, and delete records. Key statements: INSERT, UPDATE, DELETE.
DQL (Data Query Language): Query records. Key statement: SELECT.
DCL (Data Control Language): Manage user access and permissions. Key statements: CREATE USER, GRANT, DROP USER.

View databases

Run SHOW DATABASES to list all databases:

SHOW DATABASES;

The output is similar to:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample_db          |
+--------------------+

Manage tables

Create a table

PolarDB-X supports two table types:

Partitioned table — distributes data across multiple database shards using a hash function. Use this for large tables that benefit from horizontal scaling.

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

The dbpartition by hash(id) clause partitions the table by hashing the id column across database shards.

Single table — stores all data in one shard. Use this for small reference tables or tables that don't require partitioning.

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

View a table definition

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

-- View a partitioned table
SHOW CREATE TABLE multi_db_single_tbl;

-- View a single table
SHOW CREATE TABLE single_tbl;

List all tables

Run SHOW TABLES to list all tables in the current database:

SHOW TABLES;

The output is similar to:

+----------------------+
| Tables_in_sample_db  |
+----------------------+
| multi_db_single_tbl  |
| single_tbl           |
+----------------------+

Drop a table

Run DROP TABLE to delete a table and all its data:

-- Drop a partitioned table
DROP TABLE multi_db_single_tbl;

-- Drop a single table
DROP TABLE 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;

Modify 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:

CREATE INDEX idx_name ON multi_db_single_tbl (name);

ALTER TABLE multi_db_single_tbl ADD INDEX idx_name(name);

View indexes on a table

SHOW INDEX FROM multi_db_single_tbl;

The output is similar to:

+---------------------+------------+----------+--------------+-------------+-----------+
| Table               | Non_unique | Key_name | Seq_in_index | Column_name | Null      |
+---------------------+------------+----------+--------------+-------------+-----------+
| multi_db_single_tbl |          0 | PRIMARY  |            1 | id          |           |
| multi_db_single_tbl |          1 | idx_name |            1 | name        | YES       |
+---------------------+------------+----------+--------------+-------------+-----------+

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;

Insert, query, update, and delete data

Insert data

Use INSERT INTO to add rows to a table. Insert a single row or multiple rows in one statement:

-- Insert one row
INSERT INTO multi_db_single_tbl (name) VALUES ('test_name');
INSERT INTO multi_db_single_tbl (name) VALUES ('test_name');

-- Insert multiple rows at once
INSERT INTO multi_db_single_tbl (name) VALUES ('test_name'), ('test_namexx');

Query data

Use SELECT to retrieve rows:

SELECT * FROM multi_db_single_tbl;

Update data

Use UPDATE to modify existing rows:

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

Warning

An UPDATE statement without a WHERE clause updates every row in the table. Always include a WHERE clause to target specific rows.

Delete data

Use DELETE FROM to remove rows:

DELETE FROM multi_db_single_tbl WHERE id = 100002;

Warning

A DELETE statement without a WHERE clause deletes every row in the table. Always include a WHERE clause to target specific rows.

Manage users and permissions

Create a user

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Parameter	Description
`username`	The account name to create
`host`	The host from which the account can connect. Use `%` to allow connections from any host
`password`	The account password

Example: Create an account named drdsuser with password Drds123456 that can connect from any host:

CREATE USER drdsuser@'%' IDENTIFIED BY 'Drds123456';

Grant permissions

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

Parameter	Description
`privileges`	The permissions to grant, such as `SELECT`, `INSERT`, or `UPDATE`. Use `ALL` to grant all permissions
`databasename`	The target database. Use `*` to target all databases
`tablename`	The target table. Use `*` to target all tables in the database
`username`	The account to grant permissions to
`host`	The host from which the account can connect. Use `%` to allow connections from any host
`WITH GRANT OPTION`	(Optional) Grants the account permission to run `GRANT` statements

Example: Grant all permissions on all tables in sample_db to drdsuser connecting from any host:

GRANT ALL ON sample_db.* TO drdsuser@'%';

View user permissions

SHOW GRANTS FOR 'username'@'host';

Drop a user

DROP USER 'username'@'host';