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 DATABASES
statement:
SHOWDATABASES;
To use the database namedmysql
, use the following statement:
USE mysql;
To show all the tables in a database, use theSHOW TABLES
statement:
SHOWTABLESFROMmysql;
To create a database, use theCREATE DATABASE
statement:
CREATEDATABASE db_name [options];
To create a database namedsamp_db
, use the following statement:
CREATEDATABASE IFNOT
EXISTSsamp_db;
AddIF NOT EXISTS
to prevent an error if the database exists.
To delete a database, use theDROP DATABASE
statement:
DROPDATABASE samp_db;
Create, show, and drop a table
To create a table, use theCREATE TABLE
statement:
CREATE
TABLEtable_name column_name data_typeconstraint;
For example, to create a table namedperson
which 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 CREATE
statement:
SHOW
CREATE
tableperson;
To delete a table, use theDROP TABLE
statement:
DROP
TABLEperson;
Create, show, and drop an index
索引是用来加快查询索引有限公司lumns. To create an index for the column whose value is not unique, use theCREATE INDEX
statement:
CREATEINDEX person_id在person (id);
Or use theALTER TABLE
statement:
ALTER
TABLEpersonADDINDEX person_id (id);
To create a unique index for the column whose value is unique, use theCREATE UNIQUE INDEX
statement:
CREATE
UNIQUEINDEX person_unique_id在person (id);
Or use theALTER TABLE
statement:
ALTER
TABLEpersonADD
UNIQUEperson_unique_id (id);
To show all the indexes in a table, use theSHOW INDEX
statement:
SHOWINDEXFROMperson;
To delete an index, use theDROP INDEX
或ALTER TABLE
statement.DROP INDEX
can be nested inALTER TABLE
:
DROPINDEX person_id在person;
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 theINSERT
statement:
INSERT
INTOpersonVALUES(1,“汤姆”,'20170912');
To insert a record containing data of some fields into a table, use theINSERT
statement:
INSERT
INTOperson(id,name)VALUES('2',“鲍勃”);
To update some fields of a record in a table, use theUPDATE
statement:
UPDATEpersonSETbirthday=
'20180808'
WHEREid=
2;
To delete the data in a table, use theDELETE
statement:
DELETE
FROMpersonWHEREid=
2;
Query data
DQL is used to retrieve the desired data rows from a table or multiple tables.
To view the data in a table, use theSELECT
statement:
SELECT
*
FROMperson;
To query a specific column, add the column name after theSELECT
keyword:
SELECTnameFROMperson;
+
------+
|name|
+
------+
|tom|
+
------+
1
行
in
set(0.00sec)
Use theWHERE
clause 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 USER
statement. The following example creates a user namedtiuser
with the password123456
:
CREATE
用户
'tiuser'@'localhost'IDENTIFIEDBY
'123456';
To granttiuser
the privilege to retrieve the tables in thesamp_db
database:
GRANT
SELECT
在samp_db.*
TO
'tiuser'@'localhost';
To check the privileges oftiuser
:
SHOWGRANTSfortiuser@localhost;
To deletetiuser
:
DROP
用户
'tiuser'@'localhost';