Basic SQL operations - PolarDB - Alibaba Cloud Documentation Center

View databases

You can execute the following SHOW DATABASES statement to view databases:

SHOW DATABASES;

Create, view, and delete a table

You can execute the following CREATE TABLE statement to create a table.
- Create a table in which the HASH function is used to partition a specific column, such as id:
```
CREATE TABLE multi_db_single_tbl(
  id int auto_increment, 
  name varchar(30), 
  primary key(id)
) dbpartition by hash(id);
```
- Create a single table:
```
CREATE TABLE single_tbl(
 id int, 
 name varchar(30), 
 primary key(id)
);
```
You can execute the following SHOW CREATE TABLE statement to view the statement that is used to create a table.
- View the statement that is used to create a table in which the HASH function is used for partitioning:
```
SHOW CREATE TABLE multi_db_single_tbl;
```
- View the statement that is used to create a single table:
```
SHOW CREATE TABLE single_tbl;
```
You can execute the following DROP TABLE statement to delete a table.
- Delete a table in which the HASH function is used for partitioning:
```
DROP TABLE multi_db_single_tbl;
```
- Delete a single table:
```
DROP TABLE single_tbl;
```
You can execute the following SHOW TABLES statement to view all the tables in a database:
```
SHOW TABLES;
```

Add and delete a column, and modify a data type

You can execute the following ALTER TABLE statement to add a column:

ALTER TABLE multi_db_single_tbl ADD COLUMN textcol text;

You can execute the following ALTER TABLE statement to modify a column:

ALTER TABLE multi_db_single_tbl MODIFY COLUMN textcol varchar(40);

You can execute the following ALTER TABLE statement to 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 two 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);

You can execute the following statement to view a local index:
```
SHOW INDEX FROM multi_db_single_tbl;
```

You can execute one of the following two 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;

Insert, retrieve, modify, and delete data

You can execute the following INSERT statements to 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');

You can execute the following SELECT statement to retrieve data from a table:
```
SELECT * FROM multi_db_single_tbl;
```

You can execute the following UPDATE statement to modify data in a table:

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

You can execute the following DELETE statement to delete data from a table:
```
DELETE FROM multi_db_single_tbl WHERE id = 100002;
```

Create, authorize, and delete a user

You can execute the following CREATE USER statement to create a user:
```
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
```
Note
- username: the account to be created.
- host: the host that allows logon with the account. To allow the account to log on to the database from all hosts, set this parameter to a percent sign (%).
- password: the password of the account.
For example, you can run the following command to create an account whose name is drdsuser and password is Drds123456. The account can log on to the database from all hosts.
```
CREATE USER drdsuser@'%' IDENTIFIED BY 'Drds123456';
```
You can execute the following statement to authorize a specific user to retrieve tables from a specific database:
```
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
```
Note
- privileges: the operation permissions to be granted to the account, such as SELECT, INSERT, and UPDATE. To grant all permissions to the account, set this parameter to ALL.
- databasename: the name of the database. To grant the operation permissions of all databases to the account, set this parameter to an asterisk (*).
- tablename: the name of the table. To grant the operation permissions of all tables to the account, set this parameter to an asterisk (*).
- username: the account to which you want to grant permissions.
- host: the host that allows logon with the account. To allow the account to log on from all hosts, set this parameter to a percent sign (%).
- WITH GRANT OPTION: grants the account the permission to run the GRANT command. This parameter is optional.
For example, you can run the following command to grant all permissions on the sample_db database and all tables to the drdsuser account. The account can log on to the database from all hosts.
```
GRANT ALL ON sample_db. * TO drdsuser@'%';
```
You can execute the following statement to query the permissions of a specific user:
```
SHOW GRANTS FOR 'username'@'host'
```
You can execute the following statement to delete a specific user:
```
DROP USER 'username'@'host'
```