Exploring SQL in TiDB

TiDB is compatible with MySQL, and in most cases you can use MySQL statements directly. For details about unsupported features, see MySQL Compatibility.

If you want to experiment with SQL and test TiDB compatibility with MySQL queries, you can run it directly in a web browser without installing TiDB. You can also deploy a TiDB cluster first and then run SQL statements.

This page explains basic TiDB SQL statements such as DDL, DML, and CRUD operations. For the complete list of TiDB statements, see the TiDB SQL syntax diagrams.

Categories

SQL can be divided into the following four types by function.

• DDL, or Data Definition Language: used to define database objects such as databases, tables, views, and indexes.
• DML, or Data Manipulation Language: used to manipulate application-related records.
• DQL, or Data Query Language: used to query records after conditional filtering.
• DCL, or Data Control Language: used to define access permissions and security levels.

Common DDL functions are creating, modifying, and deleting objects such as tables or indexes. The corresponding commands are CREATE, ALTER, and DROP.

View, Create, and Delete Databases

A TiDB database can be viewed as a collection of objects such as tables and indexes.

To display the list of databases, use the SHOW DATABASES statement.

SHOW DATABASES;

To use the database named mysql, use the following statement.

USE mysql;

To display all tables in a database, use the SHOW TABLES statement.

SHOW TABLES FROM mysql;

To create a database, use the following CREATE DATABASE statement.

CREATE DATABASE db_name [options];

To create a database named samp_db, use the following statement.

CREATE DATABASE IF NOT EXISTS samp_db;

Add IF NOT EXISTS to avoid an error if the database already exists.

To delete a database, use the following DROP DATABASE statement.

DROP DATABASE samp_db;

Create, Show, and Delete Tables

To create a table, use the CREATE TABLE statement.

CREATE TABLE table_name column_name data_type constraint;

For example, to create a table named person that contains fields such as number, name, and birthday, use the following statement.

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

To display the statement that creates a table, or DDL, use the SHOW CREATE statement.

SHOW CREATE table person;

To delete a table, use the DROP TABLE statement.

DROP TABLE person;

Create, Show, and Delete Indexes

Indexes are used to speed up queries on indexed columns. To create an index on a column whose values are not unique, use the following CREATE INDEX statement.

CREATE INDEX person_id ON person (id);

Alternatively, use the ALTER TABLE statement as follows.

ALTER TABLE person ADD INDEX person_id (id);

To create a unique index on a column whose values are unique, use the CREATE UNIQUE INDEX statement as follows.

CREATE UNIQUE INDEX person_unique_id ON person (id);

Alternatively, use the ALTER TABLE statement as follows.

ALTER TABLE person ADD UNIQUE person_unique_id (id);

To display all indexes in a table, use the SHOW INDEX statement as follows.

SHOW INDEX FROM person;

To delete an index, use the DROP INDEX or ALTER TABLE statement. You can also nest ALTER TABLE in DROP INDEX.

DROP INDEX person_id ON person;

ALTER TABLE person DROP INDEX person_unique_id;

Insert, Update, and Delete Data

Common DML functions are adding, modifying, and deleting table records. The corresponding commands are INSERT, UPDATE, and DELETE.

To insert data into a table, use the INSERT statement as follows.

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

To insert a record containing data for multiple fields into a table, use the INSERT statement.

INSERT INTO person(id,name) VALUES('2','bob');

To update some fields of a record in a table, use the UPDATE statement.

UPDATE person SET birthday='20180808' WHERE id=2;

To delete data from a table, use the DELETE statement as follows.

DELETE FROM person WHERE id=2;

Query Data

DQL is used to retrieve desired rows of data from one or more tables.

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

SELECT * FROM person;

To query a specific column, add the column name after the SELECT keyword.

SELECT name FROM person;
+------+
| name |
+------+
| tom  |
+------+
1 rows in set (0.00 sec)

Use the WHERE clause to filter all records that match a condition and return the result.

SELECT * FROM person where id<5;

Create, Grant, and Delete Users

DCL is generally used to create or delete users and manage user privileges.

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

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

To grant tiuser permission to search 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;

To delete tiuser:

DROP USER 'tiuser'@'localhost';