Explore SQL with TiDB
TiDB is compatible with MySQL, you can use MySQL statements directly in most of the cases. For unsupported features, see Compatibility with MySQL.
To experiment with SQL and test out TiDB compatibility with MySQL queries, you can run TiDB directly in your web browser without installing it. 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, see TiDB SQL Syntax Diagram.
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 are
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 the
SHOW DATABASES statement:
To use the database named
mysql, use the following statement:
To show all the tables in a database, use the
SHOW TABLES statement:
SHOW TABLES FROM mysql;
To create a database, use the
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;
IF NOT EXISTS to prevent an error if the database exists.
To delete a database, use the
DROP DATABASE statement:
DROP DATABASE samp_db;
Create, show, and drop a table
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 which includes fields such as number, name, and birthday, use the following statement:
CREATE TABLE person ( id INT(11), name VARCHAR(255), birthday DATE );
To view the statement that creates the table (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 drop an index
Indexes are used to speed up queries on indexed columns. To create an index for the column whose value is not unique, use the
CREATE INDEX statement:
CREATE INDEX person_id ON person (id);
Or use the
ALTER TABLE statement:
ALTER TABLE person ADD INDEX person_id (id);
To create a unique index for the column whose value is unique, use the
CREATE UNIQUE INDEX statement:
CREATE UNIQUE INDEX person_unique_id ON person (id);
Or use the
ALTER TABLE statement:
ALTER TABLE person ADD UNIQUE person_unique_id (id);
To show all the indexes in a table, use the
SHOW INDEX statement:
SHOW INDEX FROM person;
To delete an index, use the
DROP INDEX or
ALTER TABLE statement.
DROP INDEX can be nested in
DROP INDEX person_id ON person;
ALTER TABLE person DROP INDEX person_unique_id;
Insert, update, and delete data
Common DML features are adding, modifying, and deleting table records. The corresponding commands are
To insert data into a table, use the
INSERT INTO person VALUES(1,'tom','20170912');
To insert a record containing data of some fields into a table, use the
INSERT INTO person(id,name) VALUES('2','bob');
To update some fields of a record in a table, use the
UPDATE person SET birthday='20180808' WHERE id=2;
To delete the data in a table, use the
DELETE FROM person WHERE id=2;
DQL is used to retrieve the desired data rows from a table or multiple tables.
To view the data in a table, use the
SELECT * FROM person;
To query a specific column, add the column name after the
SELECT name FROM person;
+------+ | name | +------+ | tom | +------+ 1 rows in set (0.00 sec)
WHERE clause to filter all records that match the conditions and then return the result:
SELECT * FROM person where id<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 the
CREATE USER statement. The following example creates a user named
tiuser with the password
CREATE USER 'tiuser'@'localhost' IDENTIFIED BY '123456';
tiuser the privilege to retrieve the tables in the
GRANT SELECT ON samp_db.* TO 'tiuser'@'localhost';
To check the privileges of
SHOW GRANTS for tiuser@localhost;
DROP USER 'tiuser'@'localhost';