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非关系型数据库对比

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

2.6、数据库初始化

cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

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

2.8、账号密码管理

设置root密码：

mysqladmin -u root -p password "123456"

登录：

mysql -u root -p123456

三、MySQL数据库管理

3.1 数据库基本操作

3.1.1 查看数据库信息

SHOW DATABASES;

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

3.1.2 切换数据库

USE 数据库名;

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

3.1.3 查看数据库中的表

SHOW TABLES;
SHOW TABLES IN 数据库名;

用途：显示当前数据库或指定数据库的所有表。

3.1.4 查看表结构

DESCRIBE 表名;

输出说明：

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

3.2 常用数据类型

注意事项：

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

3.3 数据库文件存储结构

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

3.4 SQL语句分类

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;

字段约束：

• NOT NULL：不能为空
• DEFAULT：默认值
• PRIMARY KEY：主键
• UNIQUE KEY：唯一键
• AUTO_INCREMENT：自增长字段

3.5.3 删除表

DROP TABLE 表名;
DROP TABLE 数据库名.表名; -- 不切换数据库时使用

3.5.4 删除数据库

DROP DATABASE 数据库名;

3.5.5 修改表结构(ALTER)

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

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 表名; -- 查询所有字段

3.7.2 限制查询结果

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

3.7.3 竖向显示结果

SELECT * FROM 表名\G

3.8 表高级操作

3.8.1 临时表

CREATE TEMPORARY TABLE 表名(...);

• 临时表只存在于当前连接，会在连接关闭时自动销毁
• 可进行增删改查操作

3.8.2 克隆表

-- 仅复制表结构
CREATE TABLE 新表 LIKE 旧表;

-- 复制表结构和数据
CREATE TABLE 新表 AS SELECT * FROM 旧表;

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 权限说明

结语

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