国产数据库TiDB从入门到放弃教程

国家层面战略，安全的角度，硬件、软件国产化是趋势，鸿蒙电脑操作系统、鸿蒙手机操作系统…数据库也会慢慢国产化，国产数据库TiDB用起来比OceanBase丝滑，本身没有那么重。

1. 介绍

TiDB 是一个开源的分布式 NewSQL 数据库，兼容 MySQL 协议。它结合了传统关系型数据库（RDBMS）和 NoSQL 数据库的优点，旨在提供水平扩展、强一致性和高可用性。TiDB 由 PingCAP 公司开发，并得到了社区的广泛支持。

1.1 TiDB 的主要特点

1. MySQL 兼容：

   • TiDB 完全兼容 MySQL 协议，使得现有的 MySQL 应用可以无缝迁移到 TiDB，而无需修改代码。
   • 支持大部分 MySQL 特性，如事务、存储过程、触发器等。

2. 水平扩展：

   • TiDB 设计为分布式架构，可以轻松地通过添加更多节点来实现水平扩展，以应对不断增长的数据量和查询负载。
   • 分布式架构还允许 TiDB 处理大规模数据集，同时保持高性能。

3. 强一致性：

   • TiDB 使用 Raft 共识算法来保证分布式系统中的强一致性。
   • 这意味着所有成功的写入操作在集群中的一致副本上都是一致的，从而避免了数据不一致的问题。

4. 高可用性：

   • TiDB 架构中的各个组件（如 TiKV、PD 和 TiDB Server）都是无单点故障设计，确保系统的高可用性。
   • 数据自动复制和故障转移机制进一步增强了系统的可靠性。

5. HTAP (Hybrid Transactional/Analytical Processing)：

   • TiDB 支持混合事务/分析处理，即可以在同一个集群中同时高效地执行 OLTP（在线事务处理）和 OLAP（在线分析处理）工作负载。
   • 这减少了维护多个不同类型的数据库系统的需求，简化了架构并降低了成本。

6. 云原生：

   • TiDB 可以部署在物理机、虚拟机、容器或云端环境中，具有良好的云原生特性。
   • 它与 Kubernetes 等容器编排工具集成良好，便于管理和自动化运维。

7. 生态丰富：

   • TiDB 拥有活跃的开源社区，提供了丰富的插件和工具，例如备份恢复工具 BR、数据同步工具 DM、监控工具 Prometheus + Grafana 等。
   • 社区和官方文档资源丰富，易于学习和使用。

8. 多数据中心部署：

   • TiDB 支持跨多个数据中心部署，这有助于提高灾难恢复能力和地理冗余度。

1.2 TiDB 架构概览

• TiDB Server：负责处理 SQL 请求、优化查询计划和与客户端通信。它是无状态的，可以横向扩展。
• PD (Placement Driver)：管理元数据、调度任务和分配全局唯一 ID。PD 是整个集群的大脑，确保数据分布合理且高效。
• TiKV：是一个分布式键值存储引擎，负责持久化存储数据，并实现了 MVCC（多版本并发控制）以支持 ACID 事务。
• TiFlash：是 TiDB 的列式存储引擎，主要用于加速 OLAP 查询。它与 TiKV 结合使用，提供 HTAP 能力。

1.3 使用场景

• 金融行业：需要高可用性和强一致性的交易系统。
• 互联网应用：面对大量用户请求，要求快速响应和灵活扩展的应用。
• 数据分析：实时处理海量数据并进行复杂查询分析的场景。
• 物联网 (IoT)：处理来自众多设备的数据流，要求高吞吐量和低延迟。

2. 安装

下载tidb镜像或者导入tidb镜像

# 下载tidb镜像
docker pull xuxuclassmate/tidb# 导入tidb镜像
# docker load -i 文件名.tar.gz
# cat 文件名.tar.gz | docker load
docker load -i tidb.tar.gz

启动容器

docker run --name=tidb -d --privileged=true -p 4000:4000 xuxuclassmate/tidb

3. SQL语句

SQL的英文是Structured Query Language，简称SQL，是一种操作关系型数据库的结构化查询语言。操作数据库，最经常使用就是查询操作。

1、TiDB数据库的SQL语句不区分大小写，关键字建议使用大写；

2、SQL语句可以使用单行或多行书写，以分号结尾

3、注释

单行注释

- 单行注释# 单行注释

多行注释

/*
多行注释
多行注释
*/

4. SQL分类

分类	说明	作用	备注
DDL	数据定义语言	用来对数据库、表、列的定义	data definition language
DML	数据操作语言	用来对数据库中表的数据进行增、删、改操作	data manipulation language
DQL	数据查询语言	用来对数据库中表的数据进行查询	data query language
DCL	数据控制语言	用来对数据库的控制（用户创建、权限控制）	data control language

5. DDL

5.1 库的DDL

一个功能，一般都是四个动作，增删改查。

展示所有的数据库

show databases;

创建数据库

create database 数据库名称 charset=utf8;
create database if not exists 数据库名称 charset=utf8; # 使得SQL语句更加强健、健壮。

示例：

create database db1 charset=utf8;
create database if not exists db1 charset=utf8;

删除数据库

drop database 数据库名称;
drop database if exists 数据库名称;

示例：

drop database test;
drop database if exists test;

使用数据库

use 数据库名称;

查询正在使用的数据库

select database();

小结：

命令	作用	示例
show databases;	查看所有数据库	show databases;
create database if not exists 数据库名 charset=utf8;	创建数据库	create database if not exists db1 charset=utf8;
use 数据库名;	使用数据库	use db1;
select database();	查看当前使用的数据库	select database();
drop database if exists 数据库名;	删除数据库	drop database if exists db1;

5.2 表、列的DDL

列的内容可以是数字、字符串、时间等，由数据类型约束

在满足业务需求的请求下，数据类型范围选择越小的。

数值数据类型

数据类型	字节数	有符号	无符号
tinyint	1个字节	-128~127	0~255
smallint	2个字节	-32768~32767	0~65535
mediumint	3个字节	-8388608~8388607	0~16777215
int	4个字节	-2147483648~2147483647	0~4294967295
bigint	8个字节	-263~263-1	0~2^64-1
float	单精度，4个字节	-231~231-1	0~2^32-1
double	双精度，8个字节
decimal[M, D]	双精度，8个字节

decimal(5,2)，表示共5位数字，其中2位是小数，比如：888.88

字符串数据类型

数据类型	长度	用途
char(size)	最大255个字符	数据是定长，如md5的密码，邮编，手机号，身份证号等
varchar(size)	最大65535个字节	存放一般内容长度
tinytext	最大255个字节
text	最大65535个字节
mediumtext	最大16777215个字节	大段文本时，如新闻、文章、论文等
longtext	最大4294967295个字节	大段文本时，如新闻、文章、论文等

1、VARCHAR(size)：0~65535字节 可变长度字符串，最大65532字节，1-3个字节用于记录大小【utf8编码size最大21844字符，gbk编码最大32766字符 】

2、查询速度：char > varchar

3、text与char、varchar不同的是，text不可以有默认值，能用varchar的地方不用text

日期+时间数据类型

数据类型	格式
date	年-月-日，如：2024-4-8
datetime	年-月-日 时:分:秒，如：2024-4-8 16:17:40
timestamp	年-月-日 时:分:秒，如：2024-4-8 16:17:40
time	时:分:秒，如：16:17:40
year	年，如：2024

datetime保存时间的范围： 1000-01-01 00:00:00 到 9999-12-31 23:59:59

timestamp保存时间的范围： 1970-01-01 00:00:01 到 2038-01-19 03:14:07

展示当前数据库所有表

show tables;

描述表结构

desc 表名;

查看表的创建语句

show create table 表名;show create table user;

创建表

create table 表名(字段名1 数据类型,字段名2 数据类型,...字段名n 数据类型  # 最后一个不需要逗号(,)
);示例：
create table if not exists user(id tinyint,username varchar(32),age tinyint
);

删除表

drop table 表名称;
drop table if exists 表名称;

修改表、列

# 修改表名
alter table 原来的表名 rename to 新的表名;示例：
alter table user rename to users;# 增加一列
alter table 表名 add 列名 数据类型;示例：
alter table users add height float;# 修改列数据类型
alter table 表名 modify 列名 新数据类型;示例：
alter table users modify id int;# 修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型;示例：
alter table users change id no tinyint;# 删除列
alter table 表名 drop 列名;示例：
alter table users drop height;

小结：

命令	作用
show tables;	查看当前数据库中所有表
desc 表名;	查看表结构
show create table 表名;	查看表的创建语句
alter table 表名 rename to 新的表名;	修改表名
alter table 表名 add 列名 类型;	添加字段
alter table 表名 modify 列名 类型及约束;	修改字段数据类型
alter table 表名 change 原名 新名 类型及约束;	修改字段名和数据类型
alter table 表名 drop 列名;	删除字段
drop table 表名;	删除表

6. DML

6.1 添加数据

添加指定列数据

insert into 表名(列名1,列名2...) values(值1,值2...);

添加全部列数据

insert into 表名 values(值1,值2...);示例：
insert into users values(1, 'zhangsan', 18);

批量添加指定列数据

insert into 表名(列名1,列名2...) values(值1,值2...),(值1,值2...)...;

批量添加全部列数据

insert into 表名 values(值1,值2...),(值1,值2...)...;示例：
insert into users values(1, 'lisi', 18),(1, 'wanger', 19);

6.2 修改数据

修改表数据

update 表名 set 列名1=值1 列名2=值2...[where条件];示例：
update users set no=2 where username='lisi';

注：如果不加where条件，就是对该表所有行内容进行修改

6.3 删除数据

删除表数据

delete from 表名 [where条件]示例：
delete from users where username='wanger';

注：如果不加where条件，就是对该表所有行内容进行删除

7. DQL

完整语法

SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段
HAVING分组后条件
ORDER BY排序字段
LIMIT分页限定

- 创建数据库
create database if not exists db1- 创建数据库表
use db1;create table if not exists stu(id int(11),username varchar(32),sex tinyint(1),age tinyint(3),math tinyint(3),chinese tinyint(3)
);- 添加数据
insert into stu values(1,'小张',1,16,75,86),(2,'小李',1,16,76,86),(3,'小王',0,17,76,87),(4,'小胡',0,17,77,87),(5,'小丘',0,18,77,88),(6,'小刘',0,18,80,90);

7.1 基础查询

- 查询指定字段
select id,username from stu;- 查询表所有字段
select * from stu;- 去除重复记录
select distinct sex from stu;- 字段取别名
select id,sex as gender,math shuxue from stu;

7.2 条件查询

select 字段列表 from 表名 where条件

where后面支持多种运算符

运算符	功能描述
比较运算符	=、>、>=、<、<=、!=、<>
逻辑运算符	and、&&、or、||、not
模糊查询	like
范围查询	between…and…、in(…)
空判断	is null、is not null

模糊查询使用like关键字，可以使用通配符进行占位

_：代表单个任意字符

%：代表任意个字符

正则表达式

select * from stu where class_id REGEXP '1|7';

7.3 排序查询

select 字段列表 from 表名 order by 排序字段名1 [,排序字段2]...;

注：排序方式有上序ASC，降序DESC，默认情况下是升序ASC

select * from stu order by math desc,chinese desc;

7.4 聚合函数

在进行查询操作时，往往需要对一整列进行运算，例如成绩的平均分

函数名	功能
count(列名)	统计数量(一般选用不为null的列)
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

select 聚合函数 from 表名;

注：NULL值不参与聚合函数运算

7.5 分组查询

select 字段列表 from 表名 [where分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤];

注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

select sex,max(math),max(chinese) from stu group by sex;

7.6 分页查询

select 字段列表 from 表名 limit 查询起始索引,查询条目数

注：limit关键字中，查询起始索引是从0开始的

8. 约束

约束是作用于数据库表中列上的规则，用于限制添加数据的行为。从而保证数据库中数据的正确性、有效性和完整性。

8.1 约束分类

约束类型	关键字	功能
非空约束	NOT NULL	保证列中所有数据不能有NULL值
唯一约束	UNIQUE	保证列中所有数据各不相同
主键约束	PRIMARY KEY	主键是一行数据的唯一标识，要求非空且唯一
默认约束	DEFAULT	保存数据时，未指定值则采用默认值
外键约束	FOREIGN KEY	外键用来让两个表数据建立关联，保证数据的一致性和完整性
检查约束	CHECK	保证列中的值满足某一条件

create table if not exists stu(id int(11) unsigned auto_increment primary key not null,username varchar(32) not null unique,sex tinyint(1) default 1 not null,age tinyint(3) not null,math tinyint(3) default 0 not null,chinese tinyint(3) default 0 not null
);

9. 多表查询

一次性从多张表中查询需要的数据

create table class(class_id int(11) unsigned auto_increment primary key not null,class_name varchar(32) not null unique
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;create table stu(stu_id int(11) unsigned auto_increment primary key not null,class_id int(11) not null,stu_name varchar(32) not null unique
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;insert into class(class_name) values('一班'),('二班');insert into stu(class_id,stu_name) values(1,'小张'),(1,'小李'),(2,'小孙'),(2,'小杨');

在MySQL中多表查询分为连接查询和子查询，连接查询又分为内连接和外连接，内连接又分为隐式内连接和显示内连接，外连接又分为左外连接和右外连接。

9.1 内连接查询

- 隐式内连接查询
select 字段列表 from 表1,表2... where 条件;- 显式内连接查询
select 字段列表 from 表1 [inner] join 表2 on 条件;

示例

- 隐式内连接查询
select * from class,stu where class.class_id=stu.class_id;- 显式内连接查询
select * from class inner join stu on class.class_id=stu.class_id;

9.2 外连接查询

左外连接查询：相当于查询A表所有数据和交集部分数据

右外连接查询：相当于查询B表所有数据和交集部分数据

- 左外连接查询
select 字段列表 from 表1 left [outer] join 表2 on 条件;- 右外连接查询
select 字段列表 from 表1 right [outer] join 表2 on 条件;

示例

select * from stu left outer join class on stu.class_id=class.class_id;select * from stu right outer join class on stu.class_id=class.class_id;

9.3 子查询

指查询中嵌套有查询

子查询语句结果是单行单列，子查询语句作为条件值，使用>、>=、<、<=、=、!=等进行条件判断。

# class_id 大于 1班的class_id的学生
select * from stu where class_id > (select class_id from class where class_name='一班');

子查询语句结果是多行单列，子查询语句作为条件值，使用in等关键字进行条件判断

# 查询存在班级的学生
select * from stu where class_id in (select class_id from class);

子查询语句结果是多行多列，子查询语句作为虚拟表

select * from stu,(select * from class) c where stu.class_id=c.class_id;

10. 事务

使用场景：转账、下单扣库存

语法：

# 开启事务
start transaction;
或
begin;-- 执行各种操作# 回滚事务
rollback;# 提交事务
commit;

事务特征：

1、原子性，事务是不可分割最小操作单位，要么同时成功，要么同时失败

2、一致性，事务完成时，必须使所有数据都保持一致状态

3、隔离性，多个事务之间，操作可见性

4、持久性，事务一旦提交或回滚，它对数据库中数据的改变是永久的

10.1 隔离级别

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。在事务的并发操作中可能会出现脏读，不可重复读，幻读。

读未提交(Read uncommitted)：

解释：

一个事务读到了另一个事务还没有提交的数据。

例如：

A给B转账，
A转给B，10万，点转账，但未点确认
B查账户看到10万
A及时发现，点撤回，修改为1万，再点转账，再点确认

分析：

A给B最终转账是1万，但是在过程中，B能看到过程数据。这就是脏读。

读已提交(Read committed)：
解释：

一个事务要等另一个事务提交后才能读取数据。

例如：

父亲的银行卡有10万
儿子拿着这个银行卡去买单，同时父亲准备转款(开启事务)。此时儿子看到银行卡里有10万
等儿子看完有10万后，钱被父亲转走，并提交
收费系统准备在这个银行卡扣款，再次检测，发现卡里没有钱了

分析：

这就是读已提交，若有事务对数据进行更新操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。
这个例子中，有读两次卡里的钱，一次是10万，一次是没有钱了，这就是不可重复读。

可重复读(Repeatable read)：
解释：

同一事务下，事务在执行期间，多次读取同一数据时，能够保证读取到的数据是一致的。

例如：

父亲的银行卡有10万
儿子拿着这个银行卡去买单，此时儿子看到银行卡里有10万，这个时候父亲想转账，发现转不了
等儿子买完单，父亲才能够实行转账行为

分析：

读数据的时候，不允许该数据有写数据的事务。因为写数据会改变数据。这样子就解决了不可重复读的问题。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

解释：

一个事务读取到了另一个事务新增的数据

例如：

儿子某一天去消费，花了8千元，然后他的父亲去查看他今天的消费记录（全表扫描，儿子事务开启），看到确实是花了8千元，就在这个时候，儿子花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当父亲打印儿子的消费记录清单时（儿子事务提交），发现花了1.8万元，似乎出现了幻觉，这就是幻读。

串行化(Serializable)：

解释：

它是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率最低，比较耗费数据库性能，一般不推荐使用。

小结：

隔离级别	脏读	不可重复读	幻读
读未提交	可能出现	可能出现	可能出现
读已提交	不会出现	可能出现	可能出现
可重复读	不会出现	不会出现	可能出现
串行化	不会出现	不会出现	不会出现

隔离级别查询

-- MySQL8以前
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;-- MySQL8开始
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

修改隔离级别

-- 建议开发者在修改时，仅修改当前session隔离级别即可
-- REPEATABLE-READ,MySQL默认级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE-READ

11. 函数

11.1 数学函数

函数	含义
abs(x)	返回x的绝对值
rand()	返回0到1的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回x的y次方
round(x)	返回离x最近的整数
round(x,y）	保留x的y位小数四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x,y)	返回数字 x 截断为 y 位小数的值
ceil(x)	返回大于或等于 x 的最小整数
floor(x)	返回小于或等于 x 的最大整数
greatest(x1,x2…)	返回返回集合中最大的值
least(x1,x2…)	返回返回集合中最小的值

11.2 字符串函数

函数	含义
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为z 的字符串
length(x)	返回字符串 x 的长度
replace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
reverse(x)	将字符串 x 反转

11.3 日期函数

函数	含义
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳

12. DCL

12.1 用户管理

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 实例：
CREATE USER 'java'@'%' IDENTIFIED BY 'asd123crl';

删除用户

drop user 用户名@IP;
-- 实例：
drop user 'java'@'%';

修改密码

UPDATE `user` SET `Password` = PASSWORD('新密码') WHERE `User` = '用户名';
-- 实例：
UPDATE `user` SET `Password` = PASSWORD('asd123') WHERE `User` = 'java';

12.2 权限管理

常用权限：

• 表数据: select, update, delete, insert
• 表结构: create, alert, drop
• 外键: references
• 创建临时表: create temporary tables
• 操作索引: index
• 视图: create view, show view
• 存储过程: create routine, alert routine, execute
• 所有权限: all

查看用户权限命令

SHOW GRANTS FOR '用户名'@'主机名';
-- 实例：
SHOW GRANTS FOR 'root'@'%';

给用户授予权限

在MySQL中使用GRANT命令给用户授权，如果用户不存在，GRANT会自动创建用户，并进行授权。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';  -- 权限列表中如果有多个权限则用逗号,隔开-- 授权所有库的所有表的所有权限
GRANT ALL ON *.* TO '用户名'@'主机名';  
-- 示例：给张三赋予db_test数据库students表的查询权限
GRANT SELECT ON db_test.students TO 'zhangsan'@'localhost';
-- 刷新权限
flush privileges

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-- 示例：撤销张三在db_test.students表的查询权限
REVOKE SELECT ON db_test.students FROM 'zhangsan'@'localhost';
-- 刷新权限
flush privileges

13. 备份与还原

备份语法：

mysqldump -h 服务器 -u 用户名 -p -B 数据库名1 数据库2 数据库n > 备份文件.sql

示例：

mysqldump -u root -p -B test > /backup/123.sql

还原语法：

mysqldump -h 服务器 -u 用户名 -p密码 --databases 数据库名 < 备份文件.sql# 进入数据库后
source 备份文件.sql;

示例：

mysqldump -u root -p --databases test < /backup/123.sql

一般系统推荐的字符集和排序规则

# 字符集
utf8mb4# 排序规则，不区分大小写
utf8mb4_general_cli

字符集(Character Set)：utf8mb4

排序规则 | 字符序(Collate)：utf8mb4_general_cli 或 utf8mb4_bin