关系型数据库MySQL（一）：数据库基础

MySQL数据库基础教程一、 数据库基础概念什么是数据库 (Database, DB)?简单来说数据库就是一个有组织、可持久化存储的数据集合。你可以把它想象成一个电子化的文件柜里面存放着大量结构化的信息。数据库管理系统 (Database Management System, DBMS) 是管理和操作数据库的软件。MySQL 就是其中一种非常流行的关系型DBMS。关系型数据库 (Relational Database)核心思想数据存储在表 (Table)中表由行 (Row)和列 (Column)组成。关系不同表之间可以通过共同字段 (通常是主键和外键)建立联系形成关系模型。优势数据结构清晰数据一致性高支持复杂的查询 (SQL)。关键术语表 (Table)存储特定主题数据的结构。例如学生表、课程表、成绩表。行 (Row) / 记录 (Record)表中的一条数据代表一个具体的实体。例如一个学生的信息学号、姓名、年龄等。列 (Column) / 字段 (Field)表中的一列代表实体的某个属性。例如姓名、年龄。主键 (Primary Key)表中能唯一标识一条记录的列或列组合。例如学号在学生表中通常是主键。主键值不能重复且不能为NULL。外键 (Foreign Key)一个表中的字段它引用另一个表的主键用于建立表之间的关系。例如成绩表中的学号字段是外键它引用学生表的主键学号课程号字段也是外键引用课程表的主键课程号。外键用于维护数据的引用完整性Referential Integrity。数据库关系示例假设我们有三个表学生表 (students)学号 (sid)姓名 (sname)年龄 (sage)性别 (sgender)2023001张三20男2023002李四19女课程表 (courses)课程号 (cid)课程名 (cname)学分 (credit)C001数据库原理3C002操作系统4成绩表 (scores)学号 (sid)课程号 (cid)成绩 (score)2023001C001852023001C002782023002C00192关系分析students表的主键是sid。courses表的主键是cid。scores表的主键通常是 (sid,cid) 组合因为一个学生一门课只有一个成绩。scores表中的sid是外键指向students表的sid。scores表中的cid是外键指向courses表的cid。通过scores表我们可以知道 “张三(2023001)” 选修了 “数据库原理(C001)” 并得了85分以及 “操作系统(C002)” 得了78分同时知道 “李四(2023002)” 选修了 “数据库原理(C001)” 得了92分。二、 MySQL 数据类型MySQL 支持多种数据类型用于定义表中每一列可以存储的数据种类和范围。选择合适的数据类型对存储效率和数据完整性至关重要。主要类别如下数值类型用于存储数字。整数类型TINYINT小整数有符号-128 ~ 127无符号0 ~ 255。SMALLINT较小整数有符号-32768 ~ 32767无符号0 ~ 65535。MEDIUMINT中等整数有符号-8388608 ~ 8388607无符号0 ~ 16777215。INT/INTEGER常用整数有符号-2147483648 ~ 2147483647无符号0 ~ 4294967295。BIGINT大整数有符号-9223372036854775808 ~ 9223372036854775807无符号0 ~ 18446744073709551615。浮点数类型用于存储近似数值小数。FLOAT单精度浮点数。DOUBLE双精度浮点数精度更高。定点数类型用于存储精确数值小数。DECIMAL(M, D)精确小数。M是总位数精度D是小数位数标度。例如DECIMAL(5,2)可存储123.45。字符串类型用于存储文本。定长字符串CHAR(N)固定长度字符串长度范围 0 ~ 255 字符。存储时总是占用N个字节MySQL默认字符集下。适合存储长度基本固定的数据如国家代码、性别(M/F)。变长字符串VARCHAR(N)可变长度字符串长度范围 0 ~ 65535 字符实际受行最大字节限制。存储时只占用实际字符长度 1~2字节记录长度。适合存储长度变化较大的数据如姓名、地址。文本类型用于存储大段文本。TINYTEXT最大约 255 字节。TEXT最大约 64KB (65,535 字节)。MEDIUMTEXT最大约 16MB。LONGTEXT最大约 4GB。二进制字符串BINARY(N)类似CHAR(N)但存储二进制字节。VARBINARY(N)类似VARCHAR(N)但存储二进制字节。BLOB(Binary Large Object) 类型TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB用于存储图片、音频等二进制大对象。日期和时间类型DATE日期格式 YYYY-MM-DD (如 2023-10-27)。TIME时间格式 HH:MM:SS (如 14:30:00)。DATETIME日期和时间组合格式 YYYY-MM-DD HH:MM:SS (如 2023-10-27 14:30:00)。TIMESTAMP时间戳存储自 1970-01-01 00:00:00 UTC 以来的秒数。范围较小1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。插入或更新记录时如果没有指定值通常会自动设置为当前时间取决于版本和设置。YEAR年份通常使用YEAR(4)格式存储 1901 到 2155 年。其他类型ENUM枚举类型列值只能是预定义列表中的一个。例如ENUM(small, medium, large)。SET集合类型列值可以是预定义列表中的零个或多个值的组合。例如SET(a, b, c, d)。数据类型选择建议根据数据特性选择是整数、小数、字符串还是日期考虑存储空间能用TINYINT就不用INT能用VARCHAR(50)就不用VARCHAR(255)除非确实需要。考虑数据完整性ENUM可以限制输入值范围。考虑性能CHAR在长度固定时检索可能更快INT比VARCHAR存储数字更快。三、 SQL 基础语法增删改查 (CRUD)SQL (Structured Query Language) 是与关系型数据库交互的标准语言。核心操作包括创建、读取、更新和删除数据。数据定义语言 (DDL) - 操作数据库和表结构创建数据库CREATE DATABASE database_name; -- 创建一个名为 database_name 的数据库 USE database_name; -- 选择使用该数据库创建表CREATE TABLE table_name ( column1 datatype [constraints], -- 列名 数据类型 [约束] column2 datatype [constraints], ... [table_constraints] -- 表级约束如主键、外键定义 );示例 (创建学生表)CREATE TABLE students ( sid INT PRIMARY KEY AUTO_INCREMENT, -- 学号主键自增长 sname VARCHAR(50) NOT NULL, -- 姓名变长字符串非空 sage TINYINT UNSIGNED, -- 年龄小整数非负 sgender ENUM(男, 女) DEFAULT 男 -- 性别枚举类型默认男 );常见约束PRIMARY KEY主键约束。FOREIGN KEY (column) REFERENCES parent_table(parent_column)外键约束。NOT NULL非空约束该列必须有值。UNIQUE唯一约束该列值不能重复。DEFAULT value默认值约束插入时若未指定则用此值。AUTO_INCREMENT自动增长通常用于整数主键。CHECK (condition)检查约束MySQL 8.0 开始较好支持。删除表DROP TABLE table_name; -- 删除表结构和数据修改表结构 (ALTER TABLE)ALTER TABLE table_name ADD column_name datatype; -- 添加新列 ALTER TABLE table_name DROP COLUMN column_name; -- 删除列 ALTER TABLE table_name MODIFY COLUMN column_name new_datatype; -- 修改列数据类型 ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition; -- 添加约束 ALTER TABLE table_name DROP CONSTRAINT constraint_name; -- 删除约束 (MySQL 中约束名有时需用索引名)数据操作语言 (DML) - 操作表中的数据插入数据 (INSERT)-- 方式1指定列名插入 (推荐) INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); -- 方式2不指定列名按表结构顺序插入所有列 (需确保值顺序和数量完全匹配) INSERT INTO table_name VALUES (value1, value2, ...); -- 方式3一次插入多行 INSERT INTO table_name (column1, column2, ...) VALUES (value1a, value2a, ...), (value1b, value2b, ...), ...;示例 (向学生表插入数据)INSERT INTO students (sname, sage, sgender) VALUES (张三, 20, 男), (李四, 19, 女); -- 注意 sid 是自增主键不需要手动插入值查询数据 (SELECT)这是SQL中最常用、最灵活的部分。基本结构SELECT column1, column2, ... -- 选择要显示的列 (* 代表所有列) FROM table_name -- 指定数据来源的表 [WHERE condition] -- 筛选条件 [GROUP BY column_name] -- 分组依据 [HAVING group_condition] -- 分组后的筛选条件 [ORDER BY column_name [ASC | DESC]] -- 排序 [LIMIT number]; -- 限制返回行数示例-- 1. 查询所有学生信息 SELECT * FROM students; -- 2. 查询所有女生的姓名和年龄 SELECT sname, sage FROM students WHERE sgender 女; -- 3. 查询年龄大于18岁的学生按年龄降序排列 SELECT * FROM students WHERE sage 18 ORDER BY sage DESC; -- 4. 查询年龄最大的前3位学生姓名 SELECT sname FROM students ORDER BY sage DESC LIMIT 3; -- 5. 统计男女生人数 SELECT sgender, COUNT(*) AS num FROM students GROUP BY sgender; -- 6. 查询选修了课程C001的学生姓名和成绩 (需要连接 students 和 scores 表) SELECT s.sname, sc.score FROM students s JOIN scores sc ON s.sid sc.sid -- 连接条件学生表的学号 成绩表的学号 WHERE sc.cid C001;连接 (JOIN) 类型INNER JOIN(或JOIN)返回两个表连接条件匹配的记录。LEFT JOIN(或LEFT OUTER JOIN)返回左表所有记录即使右表没有匹配右表字段用NULL填充。RIGHT JOIN(或RIGHT OUTER JOIN)返回右表所有记录即使左表没有匹配左表字段用NULL填充。FULL JOIN(或FULL OUTER JOIN)返回左右表所有记录不匹配的用NULL填充 (MySQL 需用UNION模拟)。更新数据 (UPDATE)UPDATE table_name SET column1 value1, column2 value2, ... [WHERE condition]; -- 非常重要没有 WHERE 会更新所有行示例-- 将学号为 2023001 的学生的年龄改为 21 UPDATE students SET sage 21 WHERE sid 2023001; -- 将所有性别为男的学生的年龄增加1岁 UPDATE students SET sage sage 1 WHERE sgender 男;删除数据 (DELETE)DELETE FROM table_name [WHERE condition]; -- 非常重要没有 WHERE 会删除所有行示例-- 删除学号为 2023002 的学生记录 DELETE FROM students WHERE sid 2023002; -- 删除所有年龄小于18岁的学生记录 DELETE FROM students WHERE sage 18;四、 数据库备份与恢复定期备份数据库是防止数据丢失硬件故障、人为误操作、软件故障等的关键措施。逻辑备份 (Logical Backup)原理使用 SQL 语句导出数据库的结构和数据如CREATE TABLE,INSERT。工具mysqldump(MySQL 自带命令行工具)。特点备份文件是文本文件SQL脚本可读性强便于在不同MySQL版本间迁移通常向下兼容。备份和恢复速度相对较慢尤其是大数据库。OpenEuler 下使用mysqldump备份整个数据库mysqldump -u username -p database_name backup_file.sqlusername 有权限访问该数据库的MySQL用户名。-p 提示输入密码。database_name 要备份的数据库名。backup_file.sql 备份输出的SQL文件路径和名称。备份特定表mysqldump -u username -p database_name table1 table2 backup_tables.sql备份所有数据库mysqldump -u username -p --all-databases all_databases_backup.sql常用选项--single-transaction 对支持事务的表如InnoDB进行一致性备份不影响线上写操作。--routines 包含存储过程和函数。--triggers 包含触发器。--events 包含事件调度器事件。--add-drop-database/--add-drop-table 在CREATE DATABASE/CREATE TABLE前添加DROP DATABASE/DROP TABLE语句恢复时会先删除旧库/表。恢复逻辑备份mysql -u username -p database_name backup_file.sql需要先创建好目标数据库 (database_name) 或使用--all-databases备份时自动包含创建数据库语句。物理备份 (Physical Backup)原理直接复制数据库的物理文件数据文件、日志文件等。工具文件系统命令 (cp,rsync), 第三方工具 (Percona XtraBackup - 支持热备份)。特点备份和恢复速度快。备份文件是二进制格式不可读。通常要求备份时数据库停止服务冷备份或使用支持热备份的工具如 XtraBackup。恢复时要求 MySQL 版本和配置如存储引擎、文件路径与原环境一致。OpenEuler 下冷备份 (需停止MySQL服务)停止 MySQL 服务systemctl stop mysqld复制 MySQL 数据目录通常是/var/lib/mysql到备份位置cp -r /var/lib/mysql /path/to/backup_dir/mysql_backup启动 MySQL 服务systemctl start mysqld恢复物理备份停止 MySQL 服务。清空或移除当前数据目录。将备份的数据文件复制回 MySQL 数据目录。确保文件权限和所有权正确 (通常属于mysql:mysql)。启动 MySQL 服务。备份策略建议定期备份根据业务重要性设置备份频率每天、每周。多副本备份文件应存储在不同于数据库服务器的位置另一台服务器、NAS、云存储。测试恢复定期进行恢复演练确保备份有效。结合使用可以结合逻辑备份用于迁移、小规模恢复和物理备份用于快速恢复。总结本教程详细介绍了关系型数据库的核心概念表、行、列、主键、外键、MySQL中常用的数据类型及其选择原则、SQL的基础语法DDL建表删表、DML增删改查特别是SELECT查询和JOIN连接、以及在OpenEuler系统下进行MySQL数据库逻辑备份和物理备份的方法与注意事项。掌握这些基础知识是进一步深入学习MySQL数据库管理、开发和优化的基石。请务必多加练习理解表之间的关系设计熟练掌握增删改查操作并养成定期备份的良好习惯。