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);
- Create a single table.
- 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;
- View the CREATE TABLE statement that is used to create a single table.
- Delete a table.
- Delete a single table.
DROP TABLE single_tbl;
- Delete a hash-partitioned table.
DROP TABLE multi_db_single_tbl;
- Delete a single table.
- 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;