远方の博客

数据库相关概念 概念 区分 结构化查询语言（SQL） SQL 是一种标准化的关系型数据库语言，用于管理和操作数据库 数据库（DB） DB 存储数据的“仓库”，用来存储和管理大量结构化数据的仓库 数据库管理系统（DBMS） DBMS 是一种操纵和管理数据库的大型软件，用于创建、维护、管理和查询数据库 结构化查询语言（SQL）SQL简介结构化查询语言（Structured Query Language）简称SQL，是一种专门用来管理和操控关系型数据库的编程语言，被广泛应用于各种不同的关系型数据库管理系统（如MySQL、Oracle、SQL Server等），主要作用是对关系型数据进行查询、插入、更新、删除等操作。 SQL组成DDL（数据定义语言）：用于创建、修改和删除数据库对象（数据库、表、视图、索引、存储过程、函数等） 涉及语句 简介 CREATE 创建对象（如数据库、表、视图、索引、存储过程、函数等） DROP 删除对象（如数据库、表、视图、索引、存储过程、函数等） ALTER 修改表结构 DML（数据操作语言）：用来对数据库中表的数据进 ...

数据库操作创建数据库12345create database [if not exists] 数据库名称 [default character set 默认字符集名称] [default collate 默认排序规则名称] [character set 字符集名称] [collate 排序规则名称]; 参数 简介 [default character set 默认字符集名称] 创建表时指定的默认字符集如果未指定字符集，则使用数据库的默认字符集。 [default collate 默认排序规则名称] 创建表时指定的默认排序规则如果未指定排序规则，则使用默认排序规则。 [if not exists] 只有当数据库不存在时才会创建新的数据库如果已经存在该数据库，则不执行任何操作。 [character set 字符集名称] 指定数据库的默认字符集如果未指定字符集，则使用服务器的默认字符集。 [collate 排序规则名称] 指定与数据库及其对象关联的默认排序规则(例如字符串排序)如果未指定排序规则，则使用默认排序规则。 使用数 ...

数据库设计三范式第一范式（1NF）：属性不可分割第一范式要求每个属性都是原子性的，即不能再细分成更小的数据项。 例如，一个“地址”字段不能被分成“省份”、“城市”、“县/区”，因为这些数据可以分开存储。 第二范式（2NF）：消除部分依赖第二范式要求每个非主键列都必须完全依赖于主键，不能部分依赖于主键的任何一部分。 例如，如果一个表的主键包含“订单号”和“产品编号”，那么关于某个订单的产品信息就应该储存在同一个表中，而不是分散在多个表中。 第三范式（3NF）：消除传递依赖第三范式要求每个非主键列都不能依赖于其他非主键列，而只能依赖于主键。 例如，如果一个表的主键是“客户编号”，那么“订单表”中的“客户姓名”就应该在“客户表”中存储而不是在“订单表”中存储，否则就会出现数据冗余和更新异常等问题 ER模型ER模型简介ER模型（Entity-Relationship Model）是一种表示实体、属性和它们之间关系的图形化工具，主要用于数据库设计。 ER模型通过使用实体、关系和属性等概念来描述现实世界中的事物及其相互关系，从而帮助开发人员设计出适合业务需求的数据库 ER模型三个主要部分 ...

用户管理创建用户主机名没指定默认是 % ，不指定密码用户可以直接登录，但是不安全，不推荐使用。 1234# mysql5.7create user 用户名@主机名 identified by '密码';# mysql8create user 用户名@主机名 identified with mysql_native_password by '密码'; 案例：在本地localhost创建一个用户名为 test，密码为 123456 的用户 1create user test@localhost identified by '123456'; 查看用户1show grants for 用户名@主机名; 修改用户修改用户名1234# 方式一rename user 旧用户名@主机名 to 新用户名@主机名;# 方式二update mysql.user set user ='新用户名' where user = '旧用户名' and host ='旧主机名'; 修改主机名 ...

变量系统变量系统变量分类MySQL 中的系统变量以 两个“@” 开头，分为全局系统变量（ global ）以及会话系统变量（ session），如果不写，默认会话级别 系统变量 简介 全局系统变量 @@global仅用于标记全局系统变量，针对于所有会话（连接）有效，但不能跨重启 会话系统变量 @@session仅用于标记会话系统变量，仅针对于当前会话（连接）有效。 常用的系统变量 变量 概括 @@error 上一条SQL错误号 @@identity 最后一次插入的标识值 @@rowcount 受上一个SQL语句影响的行数 @@serviceName 该计算机上的SQL服务名称 @@Version SQLServer的版本信息 全局系统变量（global）（1）查看所有或部分全局系统变量 123456# 查看所有全局系统变量show global variables;# 查看满足条件的部分全局系统变量show global variables like '%标识符%';# 查看指定全局系统变量select @@glob ...

物理备份与逻辑备份简介 物理备份与逻辑备份 简介 物理备份 直接复制数据库文件，备份数据文件，转储数据库物理文件到某一目录 逻辑备份 备份的是建表、建库、插入等操作所执行SQL语句 ，在恢复的时候执行备份的sql语句实现数据库数据的重现 物理备份与逻辑备份对比 对比 物理备份 逻辑备份 速度 备份恢复速度比较快，但占用空间比较大 备份恢复速度慢，但占用空间小，更灵活 工具 xtrabackup工具 mysqldump工具 适用 大型数据库环境，但不能恢复到异构系统中，如Windows 中小型数据库，效率相对较低 逻辑备份备份一个数据库1mysqldump -u用户名 -h 主机名 -p密码 待备份的数据库名称[表名, [表名...]]> 备份文件名称.sql 案例：使用root用户备份test数据库 1234# 备份在当前目录下mysqldump -uroot -p test > test.sql# 备份在其他目录mysqldump -uroot -p test > /opt/test.sql 备份多个数据库（-B）使用 ...

事务（Transaction）事务的定义数据库的事务是一种机制，它包含了一组相关的数据库操作命令，并把这些命令看做一个整体，要么全部执行成功，要么全部失败回滚。 事务的四大特征（ACID）ACID 是指事务的四个关键特性，分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）： 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部回滚，不允许部分执行。 一致性（Consistency）：事务在执行之前和执行之后，数据库都必须保持一致的状态。 隔离性（Isolation）：多个事务同时执行时，每个事务都应该被隔离开来，互不干扰。一个事务的执行不应该受到其他事务的影响，以此来避免并发执行时可能出现的问题，例如脏读、不可重复读和幻读等。 持久性（Durability）：一旦事务提交成功，对数据库的修改就是永久性的，即使系统发生故障或重启，数据也不会丢失。 事务的五种状态 状态 描述 活动的（active） 事务已经开始执行，但尚未提交，仍在执行中。 部分提交的（partially ...

MySQL逻辑架构 结构 作用 客户端连接器 提供与MySQL服务器建立的支持。 连接池 负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接 系统管理和控制工具 例如备份恢复、安全管理、集群管理等 SQL接口 用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。 连接器 处理连接、认证授权、安全管理 查询缓存 缓存查询结果，MySQL8.0 已废弃 解析器 词法分析、语法分析 优化器 优化SQL语句、规定执行流程 执行器 执行查询语句，获取数据 存储引擎 负责数据的存储和检索，与底层系统文件进行交互 系统文件层 配置文件、数据文件、日志文件等 MySQL运行机制 连接器→查询缓存→解析器→优化器→执行器→存储引擎 连接器建立连接：当客户端请求连接时，连接器会进行身份验证，并建立与服务器的连接。 查询缓存：如果查询被缓存，那么直接返回缓存结果集，避免了后面的解析、优化和执行步骤。 解析器生成执行计划：如果查询没有被缓存或者缓存被清空，那么解析器会对查询进行语法 ...

常见日志（Log）重做日志（Redo Log）Redo Log简介MySQL的重做日志（Redo Log）保证了事务的持久性，记录了对数据库中数据的修改操作，以便在数据库崩溃或故障切换时进行数据恢复。 Redo Log组成 组成 简介 重做记录（Redo Record） 表示数据库中进行一次变更操作（如插入、更新、删除等）所生成的记录 重做记录集（Redo Records） 表示一组Redo Record的集合，通常是指事务执行期间产生的所有Redo Record 重做日志缓冲区（Redo Log Buffer） 存储在内存中，用于暂存未持久化的重做记录集（Redo Records）Redo Log Buffer 中的内容会写入到 Redo Log Files中，以确保数据的持久性 重做日志文件（Redo Log Files） 存储在磁盘上，用于实际保存重做记录集（Redo Records）Redo Log Files由多个重做日志文件（通常是ib_logfile0和ib_logfile1）组成如果在数据库发生故障时，MySQL可以通过Redo Log Files ...

SQL性能分析查看SQL执行信息（Profile）Profile简介在MySQL中，”Profile”是一个用于性能分析和调优的特殊功能。Profile允许捕获并分析查询语句的执行情况，以便确定潜在的性能问题和瓶颈。Profile通常提供以下信息： 查询执行时间：SQL查询的总执行时间以及每个操作阶段的执行时间。 资源使用情况：CPU、内存和磁盘等资源在执行过程中的使用情况。 操作顺序：SQL查询中每个操作的执行顺序。 开启ProfileProfile是MySQL数据库提供的一种用于查看SQL查询执行信息的功能，默认默认是关闭的，可以执行以下命令来开启Profile功能： 12345678# 查看当前的 MySQL 版本是否支持 profileselect @@have_profiling# 查询 MySQL Server 的 profiling 功能是否已经开启，返回的值为 1，则表示 profiling 已经启用，否则表示未启用select @@profiling# 开启 profiling 记录执行时间超过一定阈值的语句的执行统计信息set profiling = 1; 使 ...

主从复制主从复制的概念MySQL主从复制是一种常用的数据库复制技术，通过复制主服务器（Master）上的数据复制到另一个或多个从服务器（Slave）上，实现数据的实时同步和备份。 主从复制的作用主从复制在数据库系统中有以下几个主要作用： 读写分离：主服务器（Master）负责处理写操作（写入数据），从服务器（Slave）负责处理读操作（查询数据），可以有效分担主服务器的负载，提高系统性能和扩展性。 高可用性：从服务器（Slave）作为主服务器的备份，可以在主服务器（Master）发生故障时，提供数据的备份和恢复功能。 主从复制的原理主从复制中的每个连接通常会涉及三个线程： Binlog输出线程：在主库上，为每个连接到主库的从库创建一个binlog输出线程。这个线程负责将主库上的数据更改操作记录（binlog）发送给相应的从库。它将binlog事件推送到对应的从库的I/O线程。 I/O线程：在从库上，每个连接到主库的从库都有一个独立的I/O线程。这个线程负责与主库建立连接，接收并读取主库上的binlog事件，并将其存储到从库的本地中继日志（relay ...

建表规约 【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint ( 1表示是，0表示否)。 任何字段如果为非负数，必须是 unsigned。 正例: 表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只 出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。 MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库 名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。 正例: 1aliyun_admin，rdc_config，level3_name 反例: 1AliyunAdmin，rdcConfig，level_3_name 【强制】表名不使用复数名词。 表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数 形式，符合表达习惯。 【强制】禁用保留字，如 desc、range、match、delayed ...