数据库相关概念

概念区分
结构化查询语言（SQL）SQL 是一种标准化的关系型数据库语言，用于管理和操作数据库
数据库（DB）DB 存储数据的“仓库”，用来存储和管理大量结构化数据的仓库
数据库管理系统（DBMS）DBMS 是一种操纵和管理数据库的大型软件，用于创建、维护、管理和查询数据库

结构化查询语言（SQL）

SQL简介

结构化查询语言（Structured Query Language）简称SQL，是一种专门用来管理和操控关系型数据库的编程语言，被广泛应用于各种不同的关系型数据库管理系统（如MySQL、Oracle、SQL Server等），主要作用是对关系型数据进行查询、插入、更新、删除等操作。

SQL组成

DDL（数据定义语言）：用于创建、修改和删除数据库对象（数据库、表、视图、索引、存储过程、函数等）

涉及语句简介
CREATE创建对象（如数据库、表、视图、索引、存储过程、函数等）
DROP删除对象（如数据库、表、视图、索引、存储过程、函数等）
ALTER修改表结构

DML（数据操作语言）：用来对数据库中表的数据进行插入、更新、删除

涉及语句简介
INSERT添加数据到表中
UPDATE更新表中已有的数据
DELETE删除表中的数据

DQL（数据查询语言）：用于从数据库中查询数据

涉及语句简介
SELECT查询表中的数据

DCL（数据控制语言）：用来管理数据库的访问权限和安全性

涉及语句简介
GRANT授权用户访问数据库中的数据
REVOKE取消用户对数据库中的数据的访问权限
COMMIT提交事务
ROLLBACK回滚事务

数据库（DB）

DB简介

数据库（Database）是一种用于存储、管理和组织数据的系统，用于存储和处理结构化数据。

DB组成

数据库的结构一般由这四个部分组成：

  1. 数据库：一个数据库可以包含多个表格，用于存储和管理不同类型的数据。
  2. 表格：表格用于保存和管理特定类型的数据。每个表格都具有自己的字段，以及可能包含的数据记录
  3. 字段：字段是表格中数据列的名称，每个字段对应一个特定的数据类型，如字符串、整数、日期等。
  4. 记录：记录是表格中的每一行数据，每个记录包含若干个字段的值，代表该条数据的实际内容。

数据库管理系统（DBMS）

DBMS简介

DBMS（Database Management System）是指数据库管理系统，是一种用于管理和操作数据库的软件系统。它提供了一系列功能和工具，用于创建、维护、访问和管理数据库。数据库管理系统（DBMS）可以分为两种主要类型：

  • 关系型数据库管理系统（RDBMS）：关系型数据库管理系统使用关系模型来组织和管理数据。它将数据存储在表中，表之间通过键值（主键和外键）建立关联关系。RDBMS使用结构化查询语言（SQL）进行数据操作和查询。常见的关系型数据库管理系统有Oracle、MySQL、Microsoft SQL Server、PostgreSQL等。
  • 非关系型数据库管理系统（NoSQL DBMS）：非关系型数据库管理系统不使用固定的表结构和关系模型，而是采用灵活的数据模型来存储和组织数据。非关系型数据库可以根据需要存储不同类型的数据，如文档、键值对、列族、图等。非关系型数据库管理系统适用于大数据、高并发和非结构化数据的场景。常见的非关系型数据库管理系统有MongoDB、Cassandra、Redis、Elasticsearch等。

常见RDBMS

数据库管理软件功能简介
MySQL开源的关系型数据库管理系统 支持大多数SQL标准，有其自己的一些扩展和限制 具有易用性、可扩展性和高性能等特点。
Oracle商业的关系型数据库管理系统，适用于大型企业级应用程序 具有高可靠性、可扩展性和安全性等特点
Microsoft SQL Server基于Windows操作系统的商业关系型数据库管理系统 为Microsoft提供数据管理和分析的解决方案。
PostgreSQLPostgreSQL是一个开源的关系型数据库管理系统 具有可扩展性、强大的事务管理和多版本控制等特点 在企业应用领域越来越受欢迎。
IBM DB2IBM DB2是IBM公司的一种商业关系型数据库管理系统 被广泛应用于大型企业应用程序和数据仓库。
Microsoft AccessMicrosoft Access是微软公司的桌面关系型数据库管理系统 常用于小型业务和个人应用程序的数据管理。
SybaseSybase是一种商业的关系型数据库管理系统 具有高可用性、可伸缩性和安全性等特点。
MariaDB开源的关系型数据库管理系统，由MySQL的原始开发者创建 保持与MySQL高度兼容，并添加了新的功能。
SQLiteSQLite是一个轻量级的关系型数据库管理系统 专注于嵌入式应用和小型设备。
InformixInformix是一种商业的关系型数据库管理系统 适用于企业级事务处理和分析应用程序。

常见NoSQL DBMS

分类存储的数据应用场景典型产品
键值型数据库Key-Value 键值对的数据缓存、计数器、排行榜、会话管理、消息队列Redis、Tair、Memcached
列式数据库行和列形式数据分布式文件系统、数据仓库、数据分析HBase、Cassandra、Vertica
搜索引擎数据库文本数据、全文索引搜索、日志分析、推荐系统Elasticsearch、Solr、Sphinx
文档型数据库半结构化文档内容管理、博客平台、社交网络、物联网MongoDB、Couchbase
图形数据库图形结构数据社交网络、推荐系统、地图服务等Neo4j、OrientDB
对象存储数据库对象二进制数据存储和管理二进制大对象（Blob）Aliyun OSS、Amazon S3
时间序列数据库时间序列数据金融、电信、物联网、日志处理等InfluxDB、OpenTSDB、QuestDB

MySQL基础知识

MySQL概括

MySQL是一种开源的关系型数据库管理系统（RDBMS），使用SQL语言进行数据访问和操作

MySQL版本

版本描述功能特点
MySQL Community Server社区版本，开源免费提供基本的MySQL数据库管理功能，包括数据存储、备份、恢复、性能优化等
MySQL Enterprise Edition企业版本，收费专为企业级应用设计，提供更高级别的数据可靠性、安全性和性能，并包含额外的工具和技术支持
MySQL Cluster集群版本，收费提供高可用性、高可扩展性的数据库解决方案
MySQL Cluster CGE高级集群版本，收费比MySQL Cluster提供更高级别的功能，如自动故障转移、在线变更、实时扩展等

MySQL对比Oracle

特征MySQLOracle
开发公司Oracle公司收购Sun Microsystems公司Oracle公司
授权类型开源免费版和商业版商业授权版
安装和使用相对简单，基于SQL语言操作相对复杂，需要熟悉Oracle专用的SQL语法和配置
性能性能较好，适合小型到中型应用性能较好，适合大型企业级应用
扩展性支持高并发访问和分布式集群扩展支持高并发访问和分布式集群扩展
应用范围中小型企业、小型网站或独立开发者大型企业、高性能应用系统等
数据库引擎InnoDB、MyISAM等Oracle Database
界面命令行工具和GUI工具如MySQL Workbench命令行工具和GUI工具如SQL Developer

MySQL安装目录介绍

目录介绍
bin目录放置一些可执行文件，如mysql.exe，mysqlld
data目录用于存放一些日志文件以及数据库
include目录用于放置一些头文件，如mysql.h、mysqld_ername.h等
lib目录用于放置一系列的库文件
share目录用于存放字符集、语言等信息
my.ini是mysql数据库中使用的配置文件
my-hug.ini适合超大型数据库的配置文件
my-large.ini适合超大型数据库的配置文件
my-medium.ini适合中型数据库的配置文件
my-small.ini适合小型数据库的配置文件
my-template.ini是配置文件的模板，mysql配置向导将该配置文件中选择项写入到my.ini;
my-innodb-heavy-4G.ini表示该配置文件只对于Innodb存储引擎有效，而且服务器的内存不能小于4GB

MySQL自带的数据库

数据库名作用
information_schema记录mysql中有哪些库、表等信息
mysql存储mysql核心的信息，如权限、安全等
performance_schema存放性能相关的信息
sys存放系统相关信息

MySQL CLI连接MySQL

Windows打开cmd、Linux打开终端，MySQL CLI的连接命令格式如下

1
mysql -h主机名/IP地址 -P端口号 -u用户名 -p密码
参数简介
-h / –host指定主机名/ IP 地址，默认为 localhost
-P / –port指定端口号，默认为 3306
-u / –user指定用户名，默认使用当前登录用户的用户名
-p / –password指定密码，如果不指定密码，则系统将提示用户手动输入密码
-D / –database指定数据库，如果不指定数据库，则连接成功后不会自动选择任何数据库
–default-character-set指定字符集，默认使用 utf8 字符集

常见的数据库管理工具

工具名称工具简介支持的数据库是否免费
MySQL CLIMySQL官方提供的命令行工具，用于连接MySQL服务器和执行MySQL命令和脚本MySQL免费
Navicat可视化的数据库管理工具，支持多种数据库类型MySQL、Oracle等收费
DataGrip图形化界面工具，功能更加强大，界面提示更加友好MySQL、Oracle等收费
SQLyog商业的MySQL数据库管理工具，支持可视化、命令行等多种管理方式MySQL社区版免费、商业版付费
DBeaver通用的数据库管理工具，支持多种数据库类型，提供完整的数据库管理功能MySQL、Oracle等免费
HeidiSQL免费的开源数据库管理工具，能够连接MySQL并进行数据库管理操作MySQL、MariaDB免费
Oracle SQL DeveloperOracle公司提供的免费数据库管理工具，提供专业级别的数据建模和分析功能Oracle、MySQL免费
MySQL WorkbenchMySQL官方提供的一款数据库设计和管理工具MySQL免费

运算符

运算符的优先级（从高到低）

优先级高的运算符先进行计算，使 用“()”括起来的表达式的优先级最高

优先级运算符
1（）
2!
3负号-和按位取反~
4^
5* / DIV % MOD
6+和-
7<<与>>
8&
9|
10>,<,=,>=,<=,<=>,<>,!=,is,like,regexp,in
11between,case,when,then,else
12not
13&&,AND
14||,OR,XOR
15:= 赋值=

算术运算符

运算符名称作用
+加计算两个值或表达式的和
-减计算两个值或表达式的差
*乘计算两个值或表达式的乘积
/或DIV除计算两个值或表达式的商
%或MOD求模计算两个值或表达式的余数

比较运算符

运算符名称作用
=等于判断是否相等
<=>安全等于安全判断是否相等， 与等于=的作用是相似的唯一的区别是<=>可以用来对NULL进行判断。 在两个操作数均为NULL时，其返回值为1，而不为NULL； 当一个操作数为NULL 时，其返回值为0，而不为NULL。
<> 或 !=不等于判断是否不相等
<小于判断前面的值是否小于后面的值
<=小于等于判断前面的值是否小于等于后面的值
>大于判断前面的值是否大于后面的值
>=大于等于判断前面的值是否大于等于后面的值

逻辑运算符

运算符作用
AND 或 &&逻辑与
OR 或 ||逻辑或
NOT 或 !逻辑非
XOR逻辑异或

位运算符

运算符作用
&按位与
|按位或
^按位异或
~按位取反
<<左移，每向左移动一位，相当于乘以2
>>右移，每向右移动一位，相当于除以2

非符号类型的运算符

运算符名称
is null为空运算符
is not null不为空运算符
least(值,值..)最小值运算符
greatest(值,值..)最大值运算符
between … and …两值之间运算符
not between … and …不在两值之间运算符
in(值,值..)属于运算符
not in(值,值..)不属于运算符
like模糊匹配运算符
regexp正则表达式运算符

数据类型

类型类型举例作用
整数类型tinyint、smallint、mediumint、int(或integer)、bigint用于存储整数值
浮点类型float、double用于存储带有小数点的数字值
字符串类型char、varchar、tinytext、text、mediumtext、longtext用于存储文本字符串
日期时间类型year、time、date、datetime、timestamp用于存储日期和时间值
位类型bit用于存储位字段值
枚举类型enum用于在一组预定义的值
定点数类型decimal用于存储精确的数字值
二进制字符串类型binary、varbinary、tinyblob、blob、mediumblob、longblob用于存储二进制数据
JSON类型json对象、json数组用于存储 JSON 对象和数组
空间数据类型单值：geometry、point、linestring、polygon
集合：multipoint、multilinestring、multipolygon、geometrycollection		用于存储空间数据

整数类型

类型存储需求范围适应场景
tinyint1 字节-128 到 127
无符号的范围是 0 到 255		存储小整数值，如年龄、状态等。
smallint2 字节-32768 到 32767
无符号的范围是 0 到 65535		存储较大的整数值，如订单号、组件排序等。
mediumint3 字节-8388608 到 8388607
无符号的范围是 0 到 16777215		存储较大的整数值，在数据存储量和性能之间寻求平衡。
int/integer4 字节-2147483648 到 2147483647
无符号的范围是 0 到 4294967295		存储中等大小的整数值，如用户 ID、访问计数器等。
bigint8 字节-9223372036854775808 到 9223372036854775807
无符号的范围是 0 到 18446744073709551615		存储大整数值，如订单编号等

浮点类型

类型存储需求范围适用场景
float4字节约 -3.40E+38 ~ 3.40E+38适用于存储较大的浮点数，但精度可能会因为舍入误差而降低
double8字节约 -1.79E+308 ~ 1.79E+308适用于存储非常大的浮点数，但精度可能会因为舍入误差而降低

字符串类型

类型存储需求范围适应场景
char固定长度，最多可存储255个字符0~255个字符存储长度固定、空间占用小且长度较短的字符串
例如国家、性别等信息字段。
varchar可变长度，最大长度为65535个字符。0~65535个字符存储长度不固定、空间占用相对较小且长度较长的字符串
例如电子邮件地址、网站URL等信息字段。
tinytext可变长度，最大长度为255个字符。0~255个字符存储长度不固定、空间占用相对较小且长度较短的字符串
例如备注、简介等信息字段。
text可变长度，最大长度为65535个字符。0~65535个字符存储长度不固定、空间占用较大且长度较长的字符串
例如文章、说明等文本型信息字段。
mediumtext可变长度，最大长度为16,777,215个字符。0~16,777,215个字符存储长度不固定、空间占用更大且长度更长的字符串
例如论坛帖子、新闻报道等较长文本型信息字段。
longtext可变长度，最大长度为4,294,967,295个字符0~4,294,967,295个字符存储长度不固定、空间占用极大且长度极长的字符串
例如博客内容、用户评论等大文本型信息字段

日期时间类型

类型存储需求范围适应场景
year1个字节1901年至2155年年份等仅需要年份信息的场景。
time3~5个字节‘-838:59:59.000000’ 到 ‘838:59:59.000000’存储时、分、秒信息的场景。
date3个字节‘1000-01-01’ 到 ‘9999-12-31’存储年、月、日信息的场景。
datetime8个字节‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’存储年、月、日、时、分、秒信息的场景
timestamp4或8个字节，与操作系统有关‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’存储自1970-01-01 00:00:00以来的时间戳信息

位类型

类型存储需求范围适应场景
bit1 bit0或1存储仅需使用0或1的标志位

枚举类型

类型存储需求范围适应场景
enum1或2个字节，根据枚举值数量而定最多可有65535个枚举值存储枚举值较少、取值有限且需要附加别名的信息字段

定点数类型

数据类型存储需求范围适用场景
decimal可变约 ± 10^38 ~ 10^38适用于需要高精度计算、货币运算等场景，但占用空间相对较大，在比较和排序时的处理速度相对较慢

二进制字符串类型

类型存储需求范围适应场景
BinaryN bytes (0 <= N)固定长度，最大长度为255字节存储定长、小尺寸的二进制数据，如文件头、固定长度的图像等
VarBinaryL + 1 or L + 2可变长度，最大长度为65535字节存储可变长度的二进制数据，如BLOB对象、视频、音频等
TinyBlobL + 1可变长度，最大长度为255字节存储可变长度的小型二进制数据，如短字符串或少量图片等
BlobL + 2可变长度，最大长度为65k字节存储可变长度的二进制数据，如图像、声音等
MediumBlobL + 3可变长度，最大长度为16M字节存储可变长度的大型二进制数据，如高清图片、长音频等
LongBlobL + 4可变长度，最大长度为4G字节存储可变长度的超大型二进制数据，如高清视频等

JSON类型

类型存储需求范围适应场景
JSONL + 1可变长度，最大长度为4GB存储包含复杂结构数据的JSON对象或JSON数组，如存储配置文件、日志数据等

空间数据类型

类型存储需求范围适应场景
GEOMETRYL + 4可变长度，最大长度为65k字节存储简单的几何对象，如点、线、面等
POINT21 bytes存储点对象存储包含经纬度坐标的点对象，可用于地图应用等
LINESTRINGvariable可变长度，最大长度为当前存储引擎的限制存储一组有序的点，用于表示折线、路径等
POLYGONvariable可变长度，最大长度为当前存储引擎的限制存储一个封闭的多边形，用于表示区域、地图上的国家、省份等
MULTIPOINTvariable可变长度，最大长度为当前存储引擎的限制存储多个点对象，可用于表示城市的位置、地震发生点等
MULTILINESTRINGvariable可变长度，最大长度为当前存储引擎的限制存储多条折线或路径，可用于表示道路、河流等
MULTIPOLYGONvariable可变长度，最大长度为当前存储引擎的限制存储多个多边形，可用于表示省份、国家等
GEOMETRYCOLLECTIONvariable可变长度，最大长度为当前存储引擎的限制存储包含不同类型几何对象的集合，可用于地图应用、GIS等

字符集

字符集概括

字符集，也称编码集，是指用于存储和显示各种字符及符号的编码系统。

在计算机中，字符集决定了一个字符在内存或硬盘中所占据的二进制数值。

不同的字符集对应的二进制数值不同，因此能够表示的字符范围也不同

常见的字符集

字符集名称简介优点缺点
ASCII用于纯英文环境的编码方式简单、易于处理不支持非英文字符和扩展字符集
ISO-8859-1用于欧洲语言环境的编码方式比 ASCII 多支持了欧洲语言中的重音字符只适用于欧洲语言
GB2312用于简体中文环境的编码方式支持简体中文、兼容 ASCII 编码不支持繁体中文、不兼容 ISO-8859-1
BIG5用于繁体中文环境的编码方式支持繁体中文、兼容 ASCII 编码不支持简体中文、不兼容 ISO-8859-1
Unicode通用的跨语言编码方式，包括所有语言的字符集兼容各种编码方式，支持多语言存储空间较大、读写速度相对慢
UTF-8Unicode 的一种实现方式，在 Web 开发中使用广泛存储空间小、兼容 ASCII 编码读写速度相对慢、处理复杂度较大
utf8mb3MySQL 中的一种字符集，UTF-8 的一种实现方式兼容性好，适用于低版本 MySQL不支持 Emoji 表情等超出 3 字节的字符
utf8mb4MySQL 中的一种字符集，UTF-8 的一种实现方式支持 Emoji 表情等超出 3 字节的字符某些旧版软件可能不支持

MySQL默认字符集变化

MySQL版本简介
MySQL 8.0之前默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3
MySQL 8.0之后数据库的默认编码将改为 utf8mb4 ，从而避免乱码的问题

MySQL字符集相关操作

查看字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 查看MYSQL所支持的字符集
show charset;

# 查看特定字符集编码下字符的十六进制表示
select hex("字符集")

# 查看客户端连接使用的字符集
show variables like '%character_set_client%';

# 查看服务器的默认字符集
show variables like  '%character_set_server%';

# 查看数据库的字符集
select default_character_set_name 
from information_schema.SCHEMATA 
where schema_name = '数据库名称';

# 查看表的字符集
select CCSA.character_set_name 
from 
	information_schema.TABLES T,
	information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA 
where CCSA.collation_name = T.table_collation 
and T.table_schema = "数据库名称" 
and T.table_name = "表名称";

# 查看表中所有列的字符集
select column_name, character_set_name 
from information_schema.columns 
where table_schema = '数据库名称' 
and table_name = '表名称';

设置字符集（通过配置文件）

Windows操作系统

进入mysql安装目录找到my.ini，在[mysqld]后添加配置，修改后需要重新启动MySQL服务

1
2
3
[mysqld]
# 指定默认的字符集
default-character-set=字符集名称

Linux操作系统

使用vim编辑器打开/etc/mysql/my.cnf，在[mysqld]后添加配置，修改后需要重新启动MySQL服务

1
2
3
[mysqld]
# 指定默认的字符集
character_set_server=字符集名称

设置字符集（通过命令）

创建时指定字符集

1
2
3
4
5
6
7
8
9
#（1）创建库时指定字符集和比较规则 
create database 数据库名 [default] character set='字符集';
#（2）创建表的时候指定字符集
create table 表名(列信息) [default] character set='字符集';
#（3）创建表的时候指定列字符集
create table 表名( 
	列名 数据类型 character set='字符集名称', 
	其他列... 
);

已有库&表字符集的变更

1
2
3
4
5
6
7
8
#（1）修改已创建数据库的字符集
alter database 数据库名 [default] character set '字符集名称';	
#（2）修改已创建数据表的字符集（只对新创建的表有用）
alter table 表名 [default] character set '字符集名称';
#（3）修改字段的字符集
alter table 表名 modify 字段 字段类型 character set 字符集名称;
#（4）修改表中所有列的字符集
alter table 表名 convert to character set 字符集名称;