技术博客
首页 所有文章 标签 分类 关于
技术博客

目录

MySQL数据库

 收录于 未分类
   约 6862 字   预计阅读 14 分钟 
https://bing.ee123.net/img/rand?artid=151611508

MySQL数据库

前言

  在当今数据驱动的时代，数据库技术已成为信息技术领域的核心组成部分。无论是社交媒体平台、电子商务网站还是企业管理系统，都离不开高效可靠的数据库支持。MySQL作为世界上最流行的开源关系型数据库管理系统，以其稳定性、高性能和易用性赢得了广大开发者的青睐。
  本文将全面介绍MySQL数据库的相关知识，从基础概念到安装配置，再到高级管理技巧，为读者提供一个系统性的学习指南。无论您是数据库初学者还是有一定经验的开发者，都能从本文中获得有价值的知识。

一、数据库概述

前置知识

LAMP/LNMP架构
  • LAMP = Linux + Apache + MySQL/MariaDB + PHP
  • LNMP = Linux + Nginx + MySQL/MariaDB + PHP
数据库的定位

在企业架构中，数据库用于存储和管理核心数据。例如：

  • 微信：聊天文字、语音、视频、图片
  • 外卖平台(饿了么/美团)：订单、支付、商家数据
  • 游戏(绝地求生、王者荣耀)：玩家信息、装备、战绩

1、数据库基本概念

1.1 数据

定义：描述客观事物的符号记录(Data)，包括数字、文字、图像、声音等。
存储方式：

  • 以记录形式存储，统一格式
  • 相同类型的数据存放在一起(如"学生"数据不会和"图书"混在一起)
    示例：班级同学 → 一名同学是一条记录，每条记录有多个属性(学号、姓名、性别)
    数据库的保存形式：
    MySQL服务 → 数据库 → 数据表 → 行(记录) + 列(字段)
1.2 表

表结构 = 行(记录) + 列(字段)
特点：

  • 记录由字段组成，字段由字符/数字组成
  • 可供不同用户共享
  • 具有较小的冗余度和较高的数据独立性
1.3 数据库

数据库就是表的集合，是按照一定组织方式存储的相关数据集合。

1.4 数据库管理系统(DBMS)

定义：操作和管理数据库的软件。
主要功能：

  1. 建库与维护(建表、转储与恢复)
  2. 数据定义(数据结构、存储结构、保密模式)
  3. 数据操作(查询、统计、更新)
  4. 运行管理(并发控制、存取控制、维护)
  5. 通信(与其他软件交互，如Access和Office组件)
1.5 数据库系统(DBS)

组成：硬件 + 操作系统 + 数据库 + DBMS + 应用软件 + 用户
DBA(数据库管理员)：负责备份恢复、维护、用户管理。
数据库应用软件：Navicat、人事管理系统、财务系统、图书管理等。
DBMS工作模式：

  1. 接收请求
  2. 转换为底层指令
  3. 执行数据库操作
  4. 获取结果
  5. 格式化结果
  6. 返回给用户

2、数据库发展史

2.1 第一阶段(层次型/网状型)
  • IMS(IBM, 1969)
2.2 第二阶段(关系型)

关系模型(1970)：

  • 一位叫E.F.Codd的IBM研究员提出了"关系模型(Relational Model)"
  • 核心思想：
    • 把数据放在二维表(行、列)里，而不是像层次型或网状型那样用复杂的指针去连接
    • 用数学集合和关系代数来描述和操作数据(比如选择、投影、连接)
    • 这样数据就更直观、更容易查询和维护

代表产品：

  • System R(IBM开发)：第一个真正实现关系模型的实验性数据库系统
  • Ingres(加州大学伯克利)：另一个早期的关系型数据库系统，后来衍生出了PostgreSQL

意义：

  • 关系型数据库的出现，让SQL(结构化查询语言)成为主流
  • 现在常见的MySQL、Oracle、SQLServer都是关系型数据库的后代
2.3 第三阶段(新型数据库)

特点：面向对象、开放性、多平台

  • SQL + NoSQL混合使用
  • 新兴数据库：MariaDB、PostgreSQL、时序数据库

3、关系型数据库

  • 数据模型：E-R模型(实体-关系)
    • 行(记录) = 实体
    • 列(字段) = 属性
    • 表与表之间存在关系(1对1，1对多，多对多)
  • 主键：唯一、非空，类比学号/身份证号
  • 常见产品：MySQL、Oracle、SQLServer、DB2、PostgreSQL等

4、非关系型数据库(NoSQL)

定义：Not Only SQL，存储方式不限于表，可存储键值对、文档、列式数据等。
典型产品：Redis、Memcached、MongoDB、HBase
应用场景：高并发、缓存、大数据实时计算
优点：

  1. 格式灵活(Key-Value、文档、图片等)
  2. 速度快(基于内存存储)
  3. 高扩展性
  4. 成本低(开源，部署简单)

缺点：

  1. 不支持SQL
  2. 缺少事务处理
  3. 复杂查询支持不足
  4. 大部分数据在内存，成本较高
对比Redis和Memcached
  • 相同点：都存储热点数据，内存运行，速度快
  • 不同点：Redis支持持久化，数据可落盘；Memcached纯内存，断电丢失

5、关系型vs非关系型数据库对比

特点关系型数据库非关系型数据库
数据结构表(二维表)KV、文档、列式、图形
优点结构清晰,SQL通用,支持复杂查询格式灵活,速度快,高并发
缺点表结构固定,I/O瓶颈无SQL、事务差、复杂查询弱
代表产品MySQL、Oracle、PostgreSQLRedis、MongoDB、HBase

6、小结

  1. 主流数据库系统：关系型(MySQL/Oracle/SQLServer) + 非关系型(Redis/MongoDB)
  2. DBMS的作用：桥梁，负责接收请求、转化指令、执行数据库操作并返回结果
  3. 选型思路：
    • 数据关系清晰、结构化 → 关系型数据库
    • 高并发、非结构化、大数据分析 → 非关系型数据库
    • 实际应用常常SQL+NoSQL混合使用(如：Redis缓存+MySQL持久化)

数据库是数据的有序集合，DBMS负责管理，发展历程从关系型到非关系型，实际应用常常结合使用来兼顾数据持久化和高并发性能。

二、数据库安装

MySQL 5.7源码编译安装

2.1、环境准备
yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake
  • gcc/gcc-c++：编译工具
  • ncurses/ncurses-devel：字符终端交互库
  • bison：语法分析器
  • cmake：MySQL编译工具

创建MySQL专用用户：

useradd -s /sbin/nologin mysql
2.2、源码解压与依赖
tar zxvf mysql-5.7.17.tar.gz -C /opt
tar zxvf boost_1_59_0.tar.gz -C /usr/local/
mv /usr/local/boost_1_59_0 /usr/local/boost
2.3、CMake编译配置

进入源码目录：

cd /opt/mysql-5.7.17/

执行cmake(关键参数)：

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1

编译安装：

make -j 4 && make install   # 根据CPU选择核数

注意：如果CMake出错，解决后需删除CMakeCache.txt再重新执行。
参数说明：

  • -DCMAKE_INSTALL_PREFIX=/usr/local/mysql：指定mysql的安装路径
  • -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock：指定mysql进程监听套接字文件(数据库连接文件)的存储路径
  • -DSYSCONFDIR=/etc：指定配置文件的存储路径
  • -DSYSTEMD_PID_DIR=/usr/local/mysql：指定进程文件的存储路径
  • -DDEFAULT_CHARSET=utf8：指定默认使用的字符集编码，如utf8
  • -DDEFAULT_COLLATION=utf8_general_ci：指定默认使用的字符集校对规则
  • -DWITH_EXTRA_CHARSETS=all：指定支持其他字符集编码
  • -DWITH_INNOBASE_STORAGE_ENGINE=1：安装INNOBASE存储引擎
  • -DWITH_ARCHIVE_STORAGE_ENGINE=1：安装ARCHIVE存储引擎
  • -DWITH_BLACKHOLE_STORAGE_ENGINE=1：安装BLACKHOLE存储引擎
  • -DWITH_PERFSCHEMA_STORAGE_ENGINE=1：安装FEDERATED存储引擎
  • -DMYSQL_DATADIR=/usr/local/mysql/data：指定数据库文件的存储路径
  • -DWITH_BOOST=/usr/local/boost：指定boost的路径，若使用mysql-boost集成包安装则-DWITH_BOOST=boost
  • -DWITH_SYSTEMD=1：生成便于systemctl管理的文件

存储引擎选项：
MYISAM、MERGE、MEMORY和CSV引擎是默认编译到服务器中，并不需要明确地安装。静态编译一个存储引擎到服务器，使用-DWITH_engine_STORAGE_ENGINE=1
可用的存储引擎值有：ARCHIVE、BLACKHOLE、EXAMPLE、FEDERATED、INNOBASE(InnoDB)、PARTITION(partitioning support)和PERFSCHEMA(Performance schema)

2.4、配置MySQL

权限管理：

chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf

修改配置文件/etc/my.cnf：

[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
default-character-set = utf8

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character-set-server = utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections = 2048
default-storage-engine = INNODB
max_allowed_packet = 16M
server-id = 1
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

[mysql]
port = 3306
default-character-set = utf8
socket = /usr/local/mysql/mysql.sock
auto-rehash

详细说明：

  • **[client]**客户端配置

    • port=3306：客户端连接MySQL的端口，默认是3306
    • socket=/usr/local/mysql/mysql.sock：客户端通过Unix socket文件连接MySQL(Linux/Unix环境)
    • default-character-set=utf8：默认字符集为UTF-8

  • **[mysqld]**服务器端配置

    • user=mysql：MySQL服务以’mysql’用户身份运行
    • basedir=/usr/local/mysql：MySQL安装目录
    • datadir=/usr/local/mysql/data：MySQL数据库文件存放目录
    • port=3306：MySQL服务监听端口
    • character-set-server=utf8：服务器默认字符集UTF-8
    • pid-file=/usr/local/mysql/mysqld.pid：存放MySQL进程ID的文件路径
    • socket=/usr/local/mysql/mysql.sock：服务器端socket文件路径
    • bind-address=0.0.0.0：允许任意IP连接MySQL(0.0.0.0表示监听所有网卡)
    • skip-name-resolve：关闭DNS解析，使用IP地址验证用户，提高连接速度
    • max_connections=2048：最大允许同时连接数
    • default-storage-engine=INNODB：默认存储引擎为InnoDB
    • max_allowed_packet=16M：最大允许传输的数据包大小
    • server-id=1：MySQL唯一ID，常用于主从复制
    • sql_mode=...：设置SQL模式，控制SQL语法和数据校验规则，如严格模式、禁止零日期、ANSI引号模式等

  • **[mysql]**客户端工具配置

    • port=3306：客户端连接端口
    • default-character-set=utf8：客户端默认字符集UTF-8
    • socket=/usr/local/mysql/mysql.sock：使用Unix socket文件连接
    • auto-rehash：开启自动补全功能(方便在mysql命令行中自动补全数据库和表名)
2.5、环境变量设置
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile

https://i-blog.csdnimg.cn/direct/818a2736d9eb4a1aaf9bdcd30abe4733.png

2.6、数据库初始化
cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

https://i-blog.csdnimg.cn/direct/8d21df37c88d4be3987e174e15426b00.png

2.7、服务管理
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld
netstat -anpt | grep 3306

https://i-blog.csdnimg.cn/direct/4635ca3ada804458b1321d952e9d5532.png

2.8、账号密码管理

设置root密码：

mysqladmin -u root -p password "123456"

登录：

mysql -u root -p123456

https://i-blog.csdnimg.cn/direct/0783d0edb5c84b0d970f27f4c5dfb9b9.png

三、MySQL数据库管理

3.1 数据库基本操作

3.1.1 查看数据库信息
SHOW DATABASES;

用途：显示当前MySQL服务器上所有数据库列表。

3.1.2 切换数据库
USE 数据库名;

用途：切换当前操作的数据库。

3.1.3 查看数据库中的表
SHOW TABLES;
SHOW TABLES IN 数据库名;

用途：显示当前数据库或指定数据库的所有表。
https://i-blog.csdnimg.cn/direct/1855fae8d08d416b9edf95318d9285cd.png

3.1.4 查看表结构
DESCRIBE 表名;

输出说明：

  • Field：字段名称
  • Type：字段类型
  • Null：是否允许为空
  • Key：是否是主键(PRI)、唯一键(UNI)或索引(MUL)
  • Default：默认值
  • Extra：额外属性(如auto_increment)

3.2 常用数据类型

类型说明示例
INT整型，存储整数id INT
FLOAT单精度浮点，4字节，可表示小数score FLOAT
DOUBLE双精度浮点，8字节price DOUBLE
CHAR(n)固定长度字符类型，长度n，不够补空格name CHAR(10)
VARCHAR(n)可变长度字符类型，最大n个字符address VARCHAR(50)
TEXT文本类型description TEXT
IMAGE图片类型photo IMAGE
DECIMAL(p,s)精确数值类型，p=总长度，s=小数位salary DECIMAL(5,2)

注意事项：

  • CHAR存入长度不足时补空格，超出指定长度低版本截取，高版本报错
  • 主键唯一，可由多个字段组成(联合主键)

3.3 数据库文件存储结构

  • MySQL数据目录：/usr/local/mysql/data
  • 每个数据库对应一个子目录，每个表对应若干文件：
    • MyISAM：.frm(表结构)、.MYD(数据)、.MYI(索引)
    • InnoDB：
      • 独享表空间：.ibd文件(每表一个文件)
      • 共享表空间：ibdata文件(多个表共用)

3.4 SQL语句分类

分类功能常用语句
DDL(数据定义语言)创建/修改/删除数据库对象CREATE, DROP, ALTER
DML(数据操纵语言)增删改表数据INSERT, UPDATE, DELETE
DQL(数据查询语言)查询数据SELECT
DCL(数据控制语言)用户权限管理GRANT, REVOKE, COMMIT, ROLLBACK

3.5 DDL操作(数据库和表管理)

3.5.1 创建数据库
CREATE DATABASE 数据库名;
3.5.2 创建数据表
CREATE TABLE 表名(
  字段1 数据类型 [约束条件],
  字段2 数据类型 [约束条件],
  PRIMARY KEY (主键字段)
);

示例：

CREATE DATABASE szsx_yjs0805;
USE szsx_yjs0805;
create table yjs0805(
     id int primary key auto_increment not null,
     name char(10) not null,
     score decimal(5,2),
     passwd char(50) default ''
);
DESC KY13;

https://i-blog.csdnimg.cn/direct/0222a3dee9554c718fb3fbc5ab77e13f.png

  • NOT NULL：不能为空
  • DEFAULT：默认值
  • PRIMARY KEY：主键
  • UNIQUE KEY：唯一键
  • AUTO_INCREMENT：自增长字段
3.5.3 删除表
DROP TABLE 表名;
DROP TABLE 数据库名.表名; -- 不切换数据库时使用
3.5.4 删除数据库
DROP DATABASE 数据库名;

https://i-blog.csdnimg.cn/direct/da93daf75eb3463a95f6b04748c2a30c.png

3.5.5 修改表结构(ALTER)
-- 修改表名
ALTER TABLE 旧表名 RENAME 新表名;
-- 增加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [约束];
-- 修改字段
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 约束;
ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型;
-- 删除字段
ALTER TABLE 表名 DROP 字段名;

https://i-blog.csdnimg.cn/direct/0827c037bb474b359ab378a8a7110604.png
https://i-blog.csdnimg.cn/direct/54276efa0c374055963b57c74c4fe5d8.png
https://i-blog.csdnimg.cn/direct/cf61acf430d94a8eaccd6effe6908a41.png
https://i-blog.csdnimg.cn/direct/2316378f633b44948d0ed24786b96f05.png
https://i-blog.csdnimg.cn/direct/9c40bd2575ba400d9c17748c5469a9b9.png

3.6 DML操作(表数据管理)

3.6.1 插入数据
INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...);

示例：

INSERT INTO KY13(id, name, score, passwd) VALUES(1, 'zhangsan', 70.5, PASSWORD('123456'));
3.6.2 更新数据
UPDATE 表名 SET 字段1=值1[, 字段2=值2] WHERE 条件;

示例：

UPDATE KY13 SET passwd=PASSWORD('') WHERE name='zhangsan';
3.6.3 删除数据
DELETE FROM 表名 WHERE 条件;
3.6.4 清空表
-- 方法1: DELETE
DELETE FROM 表名;

-- 方法2: TRUNCATE
TRUNCATE TABLE 表名;
  • DELETE可带WHERE条件，可回滚，速度慢
  • TRUNCATE不可回滚，速度快，ID自增重置

3.7 DQL操作(数据查询)

3.7.1 查询数据
SELECT 字段1, 字段2 FROM 表名 WHERE 条件;
SELECT * FROM 表名; -- 查询所有字段

https://i-blog.csdnimg.cn/direct/e91248994f9a443598d9a632c0987188.png

3.7.2 限制查询结果
SELECT * FROM 表名 LIMIT 起始行, 查询条数;
SELECT * FROM 表名 LIMIT 2; -- 显示前2行
SELECT * FROM 表名 LIMIT 2, 3; -- 从第2行开始显示3行

https://i-blog.csdnimg.cn/direct/3089bd5e00564341bbcfb293990df6fb.png

3.7.3 竖向显示结果
SELECT * FROM 表名\G

https://i-blog.csdnimg.cn/direct/47bef4896a4f46b6b8dcf642de25c39f.png

3.8 表高级操作

3.8.1 临时表
CREATE TEMPORARY TABLE 表名(...);
  • 临时表只存在于当前连接，会在连接关闭时自动销毁
  • 可进行增删改查操作
    https://i-blog.csdnimg.cn/direct/a43d7f18cd11402a8c96917526775c50.png
3.8.2 克隆表
-- 仅复制表结构
CREATE TABLE 新表 LIKE 旧表;
-- 复制表结构和数据
CREATE TABLE 新表 AS SELECT * FROM 旧表;

https://i-blog.csdnimg.cn/direct/b08ca403096642b994d999d439c48619.png
https://i-blog.csdnimg.cn/direct/66f246343a1d41fe97a2aa314fda9bde.png

3.9 用户管理

3.9.1 创建用户
CREATE USER '用户名'@'来源地址' IDENTIFIED BY '密码';
  • localhost：本地登录
  • %：允许任意IP登录
  • 密码可使用明文或加密方式
3.9.2 查看用户
USE mysql;
SELECT User, Host, authentication_string FROM user;
3.9.3 删除用户
DROP USER '用户名'@'来源地址';
3.9.4 修改密码
SET PASSWORD = PASSWORD('新密码'); -- 当前用户
SET PASSWORD FOR '用户'@'来源地址' = PASSWORD('新密码'); -- 其他用户
3.9.5 忘记root密码处理

  1. 修改/etc/my.cnf添加：

    [mysqld]
skip-grant-tables

  2. 重启MySQL服务并直接登录

  3. 修改密码：

    UPDATE mysql.user SET AUTHENTICATION_STRING=PASSWORD('新密码') WHERE user='root';
FLUSH PRIVILEGES;

  4. 删除skip-grant-tables并重启MySQL

3.10 用户授权管理

3.10.1 授权用户
GRANT 权限列表 ON 数据库.表 TO '用户名'@'来源地址' IDENTIFIED BY '密码';
  • 权限示例：SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, EXECUTE, ALL
  • *.*表示所有数据库和表

示例：

GRANT SELECT ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';
GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
3.10.2 查看授权
SHOW GRANTS FOR '用户名'@'来源地址';
3.10.3 撤销权限
REVOKE 权限列表 ON 数据库.表 FROM '用户名'@'来源地址';
REVOKE ALL ON *.* FROM 'lisi'@'%';

3.11 权限说明

权限功能
SELECT查询数据
INSERT插入数据
UPDATE修改数据
DELETE删除数据
CREATE创建数据库/表
DROP删除数据库/表
INDEX建立索引
ALTER修改表结构
EXECUTE执行存储过程
CREATE VIEW创建视图
SHOW VIEW查看视图
CREATE ROUTINE创建存储过程
ALTER ROUTINE修改存储过程
EVENT创建事件
TRIGGER创建触发器
ALL PRIVILEGES所有权限

https://i-blog.csdnimg.cn/direct/403ce29c43e443dc8e2e405cdf8040ef.png

结语

  通过本文的全面介绍，相信您已经对MySQL数据库有了系统的了解。从数据库的基本概念、发展历史到具体的安装配置和管理操作，我们涵盖了MySQL使用的各个方面。
  数据库技术是信息时代的基石，掌握MySQL数据库管理技能对于任何IT从业者都是非常有价值的。无论是开发Web应用、构建企业系统还是进行数据分析，MySQL都能提供稳定可靠的数据存储和管理解决方案。
  希望本文能成为您学习MySQL数据库的实用指南，在实际工作中发挥重要作用。数据库技术不断发展，建议您持续关注MySQL的新特性和最佳实践，不断提升自己的技能水平。
  记住，理论知识需要通过实践来巩固，建议您在自己的环境中尝试本文介绍的各种操作和配置，这样才能真正掌握MySQL数据库管理的精髓。

更新于 2025-09-12
 数据库, Mysql
返回 | 主页