Basic SQL operations - PolarDB - Alibaba Cloud Documentation Center

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

View databases
Create, view, and delete a table
Add a column, delete a column, and modify the data type of a column
Create, view, and delete a local index
Add, delete, modify, and query table data

View databases

Execute the following statement to view databases:

SHOW DATABASES;

Create, view, and delete a table

Note In the following examples, databases that are partitioned in Distributed Relational Database Service (DRDS) mode are used. For more information about the DRDS partitioning mode, see Databases in AUTO mode and DRDS mode.

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 is executed to create 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.
Warning Service interruptions may occur if you delete a table. Exercise caution when you perform this operation.
- 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 a local index.
```
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;