CRUD SQL in TiDB
This document briefly introduces how to use TiDB's CURD SQL.
Before you start
Please make sure you are connected to a TiDB cluster. If not, refer to Build a TiDB Cluster in TiDB Cloud (Serverless Tier) to create a Serverless Tier cluster.
Explore SQL with TiDB
TiDB is compatible with MySQL, you can use MySQL statements directly in most 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.
The following mainly introduces DML and DQL. For more information about DDL and DCL, see Explore SQL with TiDB or TiDB SQL syntax detailed explanation.
Data Manipulation Language
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;
Data Query Language
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;
The result is as follows:
+------+ | 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;