Explore SQL with TiDB

After you successfully deploy a TiDB cluster, you can run SQL statements in TiDB. Because TiDB is compatible with MySQL, you can use THE MySQL client to connect to TiDB and run MySQL statements directly in most of the cases. For more information, see Compatibility with MySQL.

This page includes some basic SQL statements such as CRUD operations. For a complete list of the statements, see TiDB SQL Syntax Diagram.

Create, show, and drop a database

Create a database

To create a database, use the CREATE DATABASE statement:

CREATE DATABASE db_name [options];

For example, to create a database named samp_db:

CREATE DATABASE IF NOT EXISTS samp_db;

Show the databases

To show the databases, use the SHOW DATABASES statement:

SHOW DATABASES;

Delete a database

To delete a database, use the DROP DATABASE statement:

DROP DATABASE samp_db;

Create, show, and drop a table

Create a table

• To create a table, use the CREATE TABLE statement:

  CREATE TABLE table_name column_name data_type constraint;

  For example:

  CREATE TABLE person (
      number INT(11),
      name VARCHAR(255),
      birthday DATE
  );

• Add IF NOT EXISTS to prevent an error if the table exists:

  CREATE TABLE IF NOT EXISTS person (
      number INT(11),
      name VARCHAR(255),
      birthday DATE
  );

• To view the statement that creates the table, use the SHOW CREATE statement:

  SHOW CREATE table person;

Show the tables

• To show all the tables in a database, use the SHOW TABLES statement:

  SHOW TABLES FROM samp_db;

• To show all the columns in a table, use the SHOW FULL COLUMNS statement:

  SHOW FULL COLUMNS FROM person;

Delete a table

To delete a table, use the DROP TABLE statement:

DROP TABLE person;

or

DROP TABLE IF EXISTS person;

Create, show, and drop an index

Create an index

• To create an index for the column whose value is not unique, use the CREATE INDEX or ALTER TABLE statement:

  CREATE INDEX person_num ON person (number);

  or

  ALTER TABLE person ADD INDEX person_num (number);

• To create a unique index for the column whose value is unique, use the CREATE UNIQUE INDEX or ALTER TABLE statement:

  CREATE UNIQUE INDEX person_num ON person (number);

  or

  ALTER TABLE person ADD UNIQUE person_num (number);

Show the indexes

To show all the indexes in a table, use the SHOW INDEX statement:

SHOW INDEX from person;

Delete an index

To delete an index, use the DROP INDEX or ALTER TABLE statement:

DROP INDEX person_num ON person;

ALTER TABLE person DROP INDEX person_num;

Insert, select, update, and delete data

Insert data

To insert data into a table, use the INSERT statement:

INSERT INTO person VALUES("1","tom","20170912");

Select data

To view the data in a table, use the SELECT statement:

SELECT * FROM person;

+--------+------+------------+
| number | name | birthday   |
+--------+------+------------+
|      1 | tom  | 2017-09-12 |
+--------+------+------------+

Update data

To update the data in a table, use the UPDATE statement:

UPDATE person SET birthday='20171010' WHERE name='tom';

SELECT * FROM person;

+--------+------+------------+
| number | name | birthday   |
+--------+------+------------+
|      1 | tom  | 2017-10-10 |
+--------+------+------------+

Delete data

To delete the data in a table, use the DELETE statement:

DELETE FROM person WHERE number=1;

SELECT * FROM person;

Empty set (0.00 sec)

Create, authorize, and delete a user

Create a user

To create a user, use the CREATE USER statement. The following example creates a user named tiuser with the password 123456:

CREATE USER 'tiuser'@'localhost' IDENTIFIED BY '123456';

Authorize a user

• To grant tiuser the privilege to retrieve the tables in the samp_db database:

  GRANT SELECT ON samp_db.* TO 'tiuser'@'localhost';

• To check the privileges of tiuser:

  SHOW GRANTS for tiuser@localhost;

Delete a user

To delete tiuser:

DROP USER 'tiuser'@'localhost';