TiDB 中的基本 SQL 操作

本是古典何须时尚 2023-06-16 07:50 14阅读 0赞

# TiDB 中的基本 SQL 操作 #

因为 TiDB 兼容 MySQL，你可以使用 MySQL 客户端连接 TiDB，并且[大多数情况下][Link 1]可以直接执行 MySQL 语句。

本文介绍 CRUD 操作等基本的 SQL 语句。完整的 SQL 语句列表，参见 [TiDB SQL 语法详解][TiDB SQL]。

# 创建、查看和删除数据库 #

使用 CREATE DATABASE 语句创建数据库。语法如下：

CREATE DATABASE db_name [options];

例如，要创建一个名为 samp\_db 的数据库，可使用一下语句：

CREATE DATABASE IF NOT EXISTS samp_db;

使用 SHOW DATABASES 语句查看数据库：

SHOW DATABASES;

使用 DROP DATABASE 语句删除数据库，例如：

DROP DATABASE samp_db;

# 创建、查看和删除表 #

使用 CREATE TABLE 语句创建表。语法如下：

CREATE TABLE table_name column_name data_type constraint;

例如：

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

如果表已存在，添加 IF NOT EXISTS 可防止发生错误：

CREATE TABLE IF NOT EXISTS person(
    	number INT(11),
    	name VARCHAR(255),
    	birthday DATE
    	);

使用 SHOW CREATE 语句查看建表语句。例如：

SHOW CREATE table person;

使用 SHOW FULL COLUMNS 语句查看表的列。例如：

SHOW FULL COLUMNS FROM person;

使用 DROP TABLE 语句删除表。 例如：

DROP TABLE person;

或者

DROP TABLE IF EXISTS person;

使用 SHOW TABLES 语句查看数据库中的所有表。例如：

SHOW TABLES FROM samp_db;

# 创建、查看和删除索引 #

对于值不唯一的列，可使用 CREATE INDEX 或 ALTER TABLE 语句。例如：

CREATE INDEX person_num ON person (number);

或者

ALTER TABLE person ADD INDEX person_num (number);

对于值唯一的列，可以创建唯一索引。例如：

CREATE UNIQUE INDEX person_num ON person (number);

或者

ALTER TABLE person ADD UNIQUE person_num (number);

使用 SHOW INDEX 语句查看表内所有索引：

SHOW INDEX FROM person;

使用 ALTER TABLE 或 DROP INDEX 语句来删除索引。与 CREATE INDEX 语句类似， DROP INDEX 也可以嵌入 ALTER TABLE 语句。 例如：

DROP INDEX person_num ON person;

ALTER TABLE person DROP INDEX person_num ;

# 增删改查数据 #

使用 INSERT 语句向表内插入数据。例如：

INSERT INTO person VALUES("1","TOM","20170912");

或者

INSERT INTO person VALUES
    	("2", "cat", "20170912"),
    	("3", "lee", "20170912"),
    	("4", "honhg", "20170912"),
    	("5", "en", "20170912"),
    	("6", "hahha", "20170912");

插入多条数据。  
使用 SELECT 语句检索表内数据。例如：

SELECT * FROM person;

+--------+------+------------+
    | number | name | birthday   |
    +--------+------+------------+
    |      1 | tom  | 2017-09-12 |
    +--------+------+------------+

根据条件检索语句。例如：

select * from person where name='TOM';

+--------+------+------------+
    | number | name | birthday   |
    +--------+------+------------+
    |      1 | TOM  | 2017-09-12 |
    +--------+------+------------+
    1 row in set

使用 DELETE 语句删除表内数据：

DELETE FROM person WHERE NUMBER=1;

SELECT * FROM PERSON;

Empty set(0.00 sec);

使用 UPDATE语句修改表内数据。例如：

UPDATE person SET birthday='20191124' WHERE name='TOM';

使用LIMIT 查询表内数据固定条数。例如：

SELECT * FROM person LIMIT 3;

+--------+------+------------+
    | number | name | birthday   |
    +--------+------+------------+
    |      1 | TOM  | 2019-11-24 |
    |      2 | cat  | 2017-09-12 |
    |      3 | lee  | 2017-09-12 |
    +--------+------+------------+
    3 rows in set

# 创建、授权和删除用户 #

使用 CREATE USER 语句创建一个用户 tiuser， 密码为 123445：

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

授权用户 tiuser 可检索数据库 samp\_db 内的表：

GRANT SELECT ON samp_db.* TO 'tiuser'@'localhost';

查询用户 tiuser 的权限：

SHOW GRANTS FOR tiuser@localhost;

删除用户 tiuser ：

DROP USER 'tiuser'@'localhost';

# 读取历史数据 #

## 历史数据保留策略 ##

TiDB 使用 MVCC 管理版本，当更新/删除数据时，不会做真正的数据删除，只会添加一个新版本数据，所以可以保留历史数据。历史数据不会全部保留，超过一定时间的历史数据会被彻底删除，以减小空间占用以及避免历史版本过多引入的性能开销。

TiDB 使用周期性运行的 GC（Garbage Collection，垃圾回收）来进行清理，关于 GC 的详细介绍参见 TiDB 垃圾回收 (GC)。

这里需要重点关注的是 tikv\_gc\_life\_time 和 tikv\_gc\_safe\_point 这条。tikv\_gc\_life\_time 用于配置历史版本保留时间，可以手动修改；tikv\_gc\_safe\_point 记录了当前的 safePoint，用户可以安全地使用大于 safePoint 的时间戳创建 snapshot 读取历史版本。safePoint 在每次 GC 开始运行时自动更新。

## 示例 ##

1.  初始化阶段，创建一个表，并插入几行数据：

create table user (id int,name varchar(20));

Query OK, 0 rows affected

插入数据：

insert into user values (1,"张三"), (2,"李四"), (3,"王二麻子");

Query OK, 3 rows affected
    Records: 3  Duplicates: 0  Warnings: 0

1.  查看表中的数据：

select * from user;

+----+----------+
    | id | name     |
    +----+----------+
    |  1 | 张三     |
    |  2 | 李四     |
    |  3 | 王二麻子 |
    +----+----------+
    3 rows in set

1.  查看当前时间：

select now();

+---------------------+
    | now()               |
    +---------------------+
    | 2019-11-25 03:00:18 |
    +---------------------+
    1 row in set

1.  更新某一行数据：

update user set name="张全蛋" where id=1;

Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0

1.  确认数据已经被更新：

select * from user;

+----+----------+
    | id | name     |
    +----+----------+
    |  1 | 张全蛋   |
    |  2 | 李四     |
    |  3 | 王二麻子 |
    +----+----------+
    3 rows in set

1.  设置一个特殊的环境变量，这个是一个 session scope 的变量，其意义为读取这个时间之前的最新的一个版本。

set @@tidb_snapshot="2019-11-25 03:00:18";

Query OK, 0 rows affected

注意：

*  这里的时间设置的是 update 语句之前的那个时间。
 *  在 tidb\_snapshot 前须使用 @@ 而非 @，因为 @@ 表示系统变量，@ 表示用户变量。

这里读取到的内容即为 update 之前的内容，也就是历史版本：

select * from user;

+----+----------+
    | id | name     |
    +----+----------+
    |  1 | 张三     |
    |  2 | 李四     |
    |  3 | 王二麻子 |
    +----+----------+
    3 rows in set

1.  清空这个变量后，即可读取最新版本数据：

set @@tidb_snapshot="";

Query OK, 0 rows affected

select * from user;

+----+----------+
    | id | name     |
    +----+----------+
    |  1 | 张全蛋   |
    |  2 | 李四     |
    |  3 | 王二麻子 |
    +----+----------+
    3 rows in set

[Link 1]: https://blog.csdn.net/Itissohardtog/article/details/103218246
[TiDB SQL]: https://pingcap.github.io/sqlgram/