This topic describes the common SQL statements that can be executed on a PolarDB-X instance.

View databases

Execute the following statement to view databases:

SHOW DATABASES;

Create, view, and delete a table

  • Create a table.
    • 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 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 the CREATE TABLE statement.
    • View the CREATE TABLE statement that is used to create a single table.
      SHOW CREATE TABLE single_tbl;
    • View the CREATE TABLE statement that is used to create a hash-partitioned table.
      SHOW CREATE TABLE multi_db_single_tbl;
  • Delete a table.
    • Delete a single table.
      DROP TABLE single_tbl;
    • Delete a hash-partitioned table.
      DROP TABLE multi_db_single_tbl;
  • View all tables in the current database.
    SHOW TABLES;

Add a column, delete a column, and modify the data type of a column

  • Add a column.
    ALTER TABLE multi_db_single_tbl ADD COLUMN textcol text;           
  • Modify the data type of a column.
    ALTER TABLE multi_db_single_tbl MODIFY COLUMN textcol varchar(40);           
  • Delete a column.
    ALTER TABLE multi_db_single_tbl DROP COLUMN textcol;

Create, view, and delete a local index

  • You can execute one of the following statements to create a local index:
    • CREATE INDEX idx_name ON multi_db_single_tbl (name);
    • ALTER TABLE multi_db_single_tbl ADD INDEX idx_name(name);
  • View local indexes.
    SHOW INDEX FROM multi_db_single_tbl;
  • You can execute one of the following statements to delete a local index:
    • DROP INDEX idx_name ON multi_db_single_tbl;
    • ALTER TABLE multi_db_single_tbl DROP INDEX idx_name;

Add, delete, modify, and query table data

  • Insert data into a table.
    INSERT INTO multi_db_single_tbl (name) VALUES ('test_name');
    INSERT INTO multi_db_single_tbl (name) VALUES ('test_name');
    INSERT INTO multi_db_single_tbl (name) VALUES ('test_name'),('test_namexx');
  • Query data from a table.
    SELECT * FROM multi_db_single_tbl;
  • Modify the data in a table.
    UPDATE multi_db_single_tbl set name='zzz' WHERE id in (100001,100002,100003,100004);
  • Delete data from a table.
    DELETE FROM multi_db_single_tbl WHERE id = 100002;