Explore SQL with TiDB

TiDB is compatible with MySQL, you can use MySQL statements directly in most of the cases. For unsupported features, seeCompatibility with MySQL.

To experiment with SQL and test out TiDB compatibility with MySQL queries, you can tryTiDB Playground. You can also first deploy a TiDB cluster and then run SQL statements in it.

This page walks you through the basic TiDB SQL statements such as DDL, DML and CRUD operations. For a complete list of TiDB statements, seeTiDB SQL Syntax Diagram.

Category

SQL is divided into the following 4 types according to their functions:

• DDL (Data Definition Language): It is used to define database objects, including databases, tables, views, and indexes.

• DML (Data Manipulation Language): It is used to manipulate application related records.

• DQL (Data Query Language): It is used to query the records after conditional filtering.

• DCL (Data Control Language): It is used to define access privileges and security levels.

Common DDL features are creating, modifying, and deleting objects (such as tables and indexes). The corresponding commands areCREATE,ALTER, andDROP.

Show, create and drop a database

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

To show the list of databases, use theSHOW DATABASESstatement:

              

               SHOWDATABASES;

               
               

To use the database namedmysql, use the following statement:

              

               USE mysql;

               
               

To show all the tables in a database, use theSHOW TABLESstatement:

              

               SHOWTABLESFROMmysql;

               
               

To create a database, use theCREATE DATABASEstatement:

              

               CREATEDATABASE db_name [options];

               
               

To create a database namedsamp_db, use the following statement:

              

               CREATEDATABASE IFNOT
               EXISTSsamp_db;

               
               

AddIF NOT EXISTSto prevent an error if the database exists.

To delete a database, use theDROP DATABASEstatement:

              

               DROPDATABASE samp_db;

               
               

Create, show, and drop a table

To create a table, use theCREATE TABLEstatement:

              

               CREATE
               TABLEtable_name column_name data_typeconstraint;

               
               

For example, to create a table namedpersonwhich includes fields such as number, name, and birthday, use the following statement:

              

               CREATE
               TABLEperson ( idINT(11), nameVARCHAR(255), birthdayDATE);

               
               

To view the statement that creates the table (DDL), use theSHOW CREATEstatement:

              

               SHOW
               CREATE
               tableperson;

               
               

To delete a table, use theDROP TABLEstatement:

              

               DROP
               TABLEperson;

               
               

Create, show, and drop an index

索引是用来加速queries on indexed columns. To create an index for the column whose value is not unique, use theCREATE INDEXstatement:

              

               CREATEINDEX person_idONperson (id);

               
               

Or use theALTER TABLEstatement:

              

               ALTER
               TABLEpersonADDINDEX person_id (id);

               
               

To create a unique index for the column whose value is unique, use theCREATE UNIQUE INDEXstatement:

              

               CREATE
               UNIQUEINDEX person_unique_idONperson (id);

               
               

Or use theALTER TABLEstatement:

              

               ALTER
               TABLEpersonADD
               UNIQUEperson_unique_id (id);

               
               

To show all the indexes in a table, use theSHOW INDEXstatement:

              

               SHOWINDEXFROMperson;

               
               

To delete an index, use theDROP INDEXorALTER TABLEstatement.DROP INDEXcan be nested inALTER TABLE:

              

               DROPINDEX person_idONperson;

               
               

              

               ALTER
               TABLEpersonDROPINDEX person_unique_id;

               
               

Insert, update, and delete data

Common DML features are adding, modifying, and deleting table records. The corresponding commands areINSERT,UPDATE, andDELETE.

To insert data into a table, use theINSERTstatement:

              

               INSERT
               INTOpersonVALUES(1,'tom','20170912');

               
               

To insert a record containing data of some fields into a table, use theINSERTstatement:

              

               INSERT
               INTOperson(id,name)VALUES('2','bob');

               
               

To update some fields of a record in a table, use theUPDATEstatement:

              

               UPDATEpersonSETbirthday=
               '20180808'
               WHEREid=
               2;

               
               

To delete the data in a table, use theDELETEstatement:

              

               DELETE
               FROMpersonWHEREid=
               2;

               
               

Query data

DQL用于检索所需的数据行a table or multiple tables.

To view the data in a table, use theSELECTstatement:

              

               SELECT
               *
               FROMperson;

               
               

To query a specific column, add the column name after theSELECTkeyword:

              

               SELECTnameFROMperson;

               
               

              

               +
               ------+
               |name|
               +
               ------+
               |tom|
               +
               ------+
               1
               rows
               in
               set(0.00sec)

               
               

Use theWHEREclause to filter all records that match the conditions and then return the result:

              

               SELECT
               *
               FROMpersonwhereid<
               5;

               
               

Create, authorize, and delete a user

DCL are usually used to create or delete users, and manage user privileges.

To create a user, use theCREATE USERstatement. The following example creates a user namedtiuserwith the password123456:

              

               CREATE
               USER
               'tiuser'@'localhost'IDENTIFIEDBY
               '123456';

               
               

To granttiuserthe privilege to retrieve the tables in thesamp_dbdatabase:

              

               GRANT
               SELECT
               ONsamp_db.*
               TO
               'tiuser'@'localhost';

               
               

To check the privileges oftiuser:

              

               SHOWGRANTSfortiuser@localhost;

               
               

To deletetiuser:

              

               DROP
               USER
               'tiuser'@'localhost';