TiDB SQL操作详解：从基础到实践

前言

作为一款兼容MySQL的分布式数据库，TiDB提供了完整的SQL支持，使开发者能够轻松迁移和使用。本文将全面介绍TiDB中的SQL基本操作，涵盖DDL、DML、DQL和DCL四大类语句，帮助快速掌握TiDB的SQL使用技巧。

一、数据库管理（DDL）

dbeaver 管理工具连接：

安装 MySQL 客户端。如果已安装，则跳过这一步骤。

yum -y install mysql

使用 MySQL 客户端访问 TiDB 数据库，密码为空：

mysql -h 192.168.234.10 -P 4000 -u root

1.1 查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 示例输出：
-- +--------------------+
-- | Database           |
-- +--------------------+
-- | INFORMATION_SCHEMA |
-- | mysql              |
-- | performance_schema |
-- | test               |
-- +--------------------+

1.2 使用数据库

-- 切换当前数据库
USE mysql;

-- 查看当前数据库中的表
SHOW TABLES;

1.3 创建和删除数据库

-- 创建数据库（安全方式）
CREATE DATABASE IF NOT EXISTS samp_db;

USE samp_db;

-- 删除数据库
DROP DATABASE samp_db;

-- 查看数据库创建语句
SHOW CREATE DATABASE samp_db;

二、表操作（DDL）

2.1 创建表

-- 创建包含基本字段的表
CREATE TABLE person (
    id INT,
    name VARCHAR(255),
    birthday DATE,
    email VARCHAR(255),
    PRIMARY KEY (id)
);

-- 查看表结构
DESC person;

-- 查看建表语句
SHOW CREATE TABLE person;

2.2 修改表结构

-- 添加新列
ALTER TABLE person ADD COLUMN phone VARCHAR(20);

-- 修改列类型
ALTER TABLE person MODIFY COLUMN name VARCHAR(100);

-- 删除列
ALTER TABLE person DROP COLUMN phone;

2.3 索引管理

-- 创建普通索引
CREATE INDEX idx_name ON person(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON person(email);

-- 查看索引
SHOW INDEX FROM person;

-- 删除索引
DROP INDEX idx_name ON person;

三、数据操作（DML）

3.1 插入数据

-- 完整插入
INSERT INTO person VALUES(1, '张三', '1990-01-15', 'zhangsan@example.com', NULL);

-- 部分插入
INSERT INTO person(id, name) VALUES(2, '李四');

-- 批量插入
INSERT INTO person VALUES
    (3, '王五', '1985-05-20', 'wangwu@example.com', NULL),
    (4, '赵六', '1992-11-30', 'zhaoliu@example.com', NULL);

3.2 更新数据

-- 单条更新
UPDATE person SET email='lisi@example.com' WHERE id=2;

-- 批量更新
UPDATE person SET birthday='2000-01-01' WHERE id>2;

3.3 删除数据

-- 条件删除
DELETE FROM person WHERE id=4;

-- 清空表（危险操作）
DELETE FROM person;

四、数据查询（DQL）

4.1 基础查询

-- 查询所有列
SELECT * FROM person;

-- 查询特定列
SELECT id, name FROM person;

-- 条件查询
SELECT * FROM person WHERE birthday < '1990-01-01';

4.2 高级查询

-- 排序
SELECT * FROM person ORDER BY birthday DESC;

-- 分页
SELECT * FROM person LIMIT 2 OFFSET 1;

-- 聚合函数
SELECT COUNT(*) as total FROM person;
SELECT MAX(birthday) as latest FROM person;

-- 分组查询
SELECT YEAR(birthday) as birth_year, COUNT(*) 
FROM person 
GROUP BY birth_year;

五、权限管理（DCL）

5.1 用户管理

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';

-- 修改密码
ALTER USER 'app_user'@'%' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'app_user'@'%';

5.2 权限控制

-- 授予权限
GRANT SELECT, INSERT ON samp_db.* TO 'app_user'@'%';

-- 授予所有权限
GRANT ALL PRIVILEGES ON samp_db.* TO 'app_user'@'%';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'%';

-- 撤销权限
REVOKE INSERT ON samp_db.* FROM 'app_user'@'%';

六、TiDB特有功能

6.1 分布式事务

-- 显式事务
BEGIN;
INSERT INTO person VALUES(5, '钱七', '1988-07-07', 'qianqi@example.com');
UPDATE account SET balance = balance - 100 WHERE user_id = 5;
COMMIT;

6.2 分区表

-- 创建范围分区表
CREATE TABLE orders (
    id BIGINT,
    order_date DATE,
    amount DECIMAL(10,2)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

最佳实践

1. 索引策略：

   • 为查询条件中的列创建索引
   • 避免过度索引，影响写入性能
   • 对区分度高的列创建索引

2. 事务优化：

   • 减小事务范围
   • 避免长事务
   • 合理设置事务隔离级别

3. 性能调优：

   -- 使用EXPLAIN分析查询计划
   EXPLAIN SELECT * FROM person WHERE name LIKE '张%';
   

总结

本文详细介绍了TiDB中的SQL操作，从基础的数据库、表管理到复杂查询和权限控制。TiDB的MySQL兼容性使得开发者可以几乎零成本地从MySQL迁移到TiDB，同时享受分布式数据库的扩展性和高可用性优势。

希望这篇指南能帮助您快速上手TiDB的SQL操作。如需了解更多高级功能，请参考TiDB官方文档。