Basic SQL operations - Quick Start| Alibaba Cloud Documentation Center

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

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;