MySQL基础学习笔记：从入门到多表设计实战

作为Web后端开发的核心技术之一，MySQL数据库的掌握是必备技能。本文整理了MySQL基础语法、数据库设计、数据操作及多表关联等核心知识点，适合新手入门学习，也可作为日常开发参考笔记。

一、MySQL核心概念

1. 数据库相关定义

数据库（DB）：存储和管理数据的仓库，是结构化数据的集合。
数据库管理系统（DBMS）：操纵和管理数据库的大型软件，常见的有MySQL、Oracle、SQL Server等。
SQL：结构化查询语言，定义了操作关系型数据库的统一标准，语法不区分大小写，语句以分号结尾。

2. 主流数据库产品对比

产品	特点	适用场景
MySQL	开源免费、轻量高效	中小型项目、Web应用
Oracle	收费、功能强大	大型企业级应用
SQL Server	收费、微软生态适配	.NET技术栈项目
SQLite	嵌入式微型数据库	移动应用、本地存储
PostgreSQL	开源免费、扩展性强	对功能有高阶需求的项目

3. MySQL版本选择

社区版（MySQL Community Server）：免费，无官方技术支持，适合学习和中小型项目。
商业版（MySQL Enterprise Edition）：收费，提供30天试用和官方支持，适合企业级应用。
本文使用版本：MySQL Community Server 8.0.31

二、MySQL安装与连接

1. 安装步骤

官网下载：https://dev.mysql.com/downloads/mysql/
选择对应系统版本（Windows建议下载ZIP Archive或Installer）
参考官方文档或安装手册完成配置（注意环境变量设置）

2. 连接语法

mysql -u用户名 -p密码 [-h数据库服务器IP地址 -P端口号]

示例：连接本地MySQL服务器

mysql -uroot -p123456 -hlocalhost -P3306

3. 图形化工具推荐

DataGrip：JetBrains旗下工具，支持多数据库类型，功能强大。
Navicat Premium：界面友好，操作便捷，适合快速开发。
SQLyog：轻量级工具，专注于MySQL，启动速度快。

三、SQL语法核心

1. SQL通用规则

语句可单行或多行书写，以分号结尾。
支持空格/缩进增强可读性。
注释方式：
- 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
- 多行注释：/* 注释内容 */

2. SQL分类及核心用途

分类	全称	核心功能	代表语句
DDL	数据定义语言	定义数据库、表结构	CREATE、ALTER、DROP
DML	数据操作语言	增删改表数据	INSERT、UPDATE、DELETE
DQL	数据查询语言	查询表数据	SELECT
DCL	数据控制语言	管理用户、权限控制	GRANT、REVOKE

四、DDL：数据定义语言

1. 数据库操作

创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名;
查询所有数据库：SHOW DATABASES;
使用数据库：USE 数据库名;
删除数据库：DROP DATABASE [IF EXISTS] 数据库名;

2. 表结构操作

（1）创建表语法

CREATE TABLE 表名(
    字段1 字段类型 [约束] [COMMENT 字段注释],
    字段2 字段类型 [约束] [COMMENT 字段注释],
    ...
    字段n 字段类型 [约束] [COMMENT 字段注释]
) [COMMENT 表注释];

（2）常用约束

约束类型	关键字	功能
非空约束	NOT NULL	限制字段值不能为NULL
唯一约束	UNIQUE	保证字段值唯一不重复
主键约束	PRIMARY KEY	唯一标识一行数据（非空+唯一）
默认约束	DEFAULT	未指定值时使用默认值
外键约束	FOREIGN KEY	建立表之间的关联

（3）常用数据类型

数值类型：INT（整数）、TINYINT（小整数）、DECIMAL（小数，适合金额）
字符串类型：VARCHAR(长度)（可变长度）、CHAR(长度)（固定长度）
日期时间类型：DATE（日期）、DATETIME（日期+时间）

（4）表结构操作示例

-- 创建员工表
CREATE TABLE emp (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(32) DEFAULT '123456' COMMENT '密码',
    name VARCHAR(10) NOT NULL COMMENT '姓名',
    gender TINYINT UNSIGNED NOT NULL COMMENT '性别：1男，2女',
    entrydate DATE COMMENT '入职时间',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '修改时间'
) COMMENT '员工表';

-- 添加字段
ALTER TABLE emp ADD image VARCHAR(300) COMMENT '图像';

-- 修改字段类型
ALTER TABLE emp MODIFY image VARCHAR(500) COMMENT '图像URL';

-- 删除字段
ALTER TABLE emp DROP COLUMN image;

-- 修改表名
RENAME TABLE emp TO tb_emp;

-- 删除表
DROP TABLE IF EXISTS tb_emp;

五、DML：数据操作语言

1. 插入数据（INSERT）

-- 指定字段插入
INSERT INTO tb_emp (username, name, gender) VALUES ('zhaomin', '赵敏', 2);

-- 批量插入
INSERT INTO tb_emp (username, name, gender) 
VALUES ('fengqingyang', '风清扬', 1), ('xiaozhao', '小昭', 2);

注意：字符串和日期类型需用引号包裹，字段顺序与值顺序一致。

2. 修改数据（UPDATE）

-- 修改指定条件数据
UPDATE tb_emp SET password = '654321', update_time = NOW() WHERE username = 'zhaomin';

-- 注意：无WHERE条件会修改整张表数据

3. 删除数据（DELETE）

-- 删除指定条件数据
DELETE FROM tb_emp WHERE username = 'xiaozhao';

-- 注意：无WHERE条件会删除整张表数据，且无法恢复

六、DQL：数据查询语言

1. 基本语法

SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数;

2. 基础查询

-- 查询指定字段
SELECT name, gender, entrydate FROM tb_emp;

-- 查询所有字段（不推荐）
SELECT * FROM tb_emp;

-- 设置别名
SELECT name AS 姓名, gender AS 性别 FROM tb_emp;

-- 去重查询
SELECT DISTINCT gender FROM tb_emp;

3. 条件查询（WHERE）

（1）比较运算符

范围查询：BETWEEN ... AND ...、IN(...)
模糊查询：LIKE（_匹配单个字符，%匹配任意字符）
空值查询：IS NULL、IS NOT NULL

（2）逻辑运算符

并且：AND 或 &&
或者：OR 或 ||
非：NOT 或 !

（3）示例

-- 查询2010年及以后入职的男性员工
SELECT * FROM tb_emp WHERE gender = 1 AND entrydate >= '2010-01-01';

-- 查询姓名包含"清"字的员工
SELECT * FROM tb_emp WHERE name LIKE '%清%';

-- 查询职位为1或3的员工
SELECT * FROM tb_emp WHERE job IN (1, 3);

4. 分组查询（GROUP BY）

（1）聚合函数

函数	功能
COUNT()	统计记录数
MAX()	求最大值
MIN()	求最小值
AVG()	求平均值
SUM()	求和

（2）示例

-- 按性别分组统计员工数
SELECT gender, COUNT(*) AS 人数 FROM tb_emp GROUP BY gender;

-- 按职位分组，查询平均入职年限（需配合函数计算）
SELECT job, AVG(TIMESTAMPDIFF(YEAR, entrydate, NOW())) AS 平均年限 
FROM tb_emp GROUP BY job HAVING 平均年限 > 5;

注意：WHERE过滤分组前数据，HAVING过滤分组后数据（支持聚合函数）。

5. 排序查询（ORDER BY）

-- 按入职时间降序排序
SELECT * FROM tb_emp ORDER BY entrydate DESC;

-- 多字段排序：先按性别升序，再按入职时间降序
SELECT * FROM tb_emp ORDER BY gender ASC, entrydate DESC;

6. 分页查询（LIMIT）

-- 分页查询：每页10条，查询第2页（起始索引从0开始）
SELECT * FROM tb_emp LIMIT 10, 10;

-- 简化写法（第一页）
SELECT * FROM tb_emp LIMIT 10;

公式：起始索引 =（查询页码 - 1）× 每页显示条数

七、多表设计

1. 表关系分类

（1）一对多（最常见）

关系示例：部门与员工（一个部门包含多个员工，一个员工归属一个部门）
实现方式：在"多"的一方添加外键，关联"一"的一方主键

-- 部门表（一的一方）
CREATE TABLE tb_dept(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '部门名称',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '修改时间'
) COMMENT '部门表';

-- 员工表（多的一方）添加外键
ALTER TABLE tb_emp ADD dept_id INT UNSIGNED COMMENT '部门ID';
ALTER TABLE tb_emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES tb_dept(id);

（2）一对一

关系示例：用户与身份证信息（一个用户对应一个身份证）
实现方式：任意一方添加外键，关联另一方主键并设置唯一约束

（3）多对多

关系示例：学生与课程（一个学生可选多门课程，一门课程可被多个学生选择）
实现方式：创建中间表，包含两个外键分别关联双方主键

-- 学生表
CREATE TABLE tb_student(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    name VARCHAR(10) NOT NULL COMMENT '学生姓名'
) COMMENT '学生表';

-- 课程表
CREATE TABLE tb_course(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID',
    name VARCHAR(20) NOT NULL COMMENT '课程名称'
) COMMENT '课程表';

-- 中间表（维护多对多关系）
CREATE TABLE tb_student_course(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    student_id INT UNSIGNED NOT NULL COMMENT '学生ID',
    course_id INT UNSIGNED NOT NULL COMMENT '课程ID',
    FOREIGN KEY (student_id) REFERENCES tb_student(id),
    FOREIGN KEY (course_id) REFERENCES tb_course(id)
) COMMENT '学生课程关系表';

2. 外键约束说明

物理外键：使用FOREIGN KEY关键字定义，强约束保证数据一致性，但影响性能。
逻辑外键：仅在业务层维护关联关系，不设置数据库级外键，适合分布式、高并发场景。
推荐方案：中小型项目可用物理外键，大型项目优先使用逻辑外键。

3. 实战案例：菜品与套餐管理

-- 分类表（菜品分类+套餐分类）
CREATE TABLE category(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '分类名称',
    type TINYINT UNSIGNED NOT NULL COMMENT '类型：1菜品分类，2套餐分类',
    status TINYINT UNSIGNED DEFAULT 0 COMMENT '状态：0禁用，1启用',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '更新时间'
) COMMENT '分类表';

-- 菜品表
CREATE TABLE dish(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '菜品名称',
    category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
    price DECIMAL(8,2) NOT NULL COMMENT '价格',
    image VARCHAR(300) NOT NULL COMMENT '图片',
    status TINYINT UNSIGNED DEFAULT 0 COMMENT '状态：0停售，1启售',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '更新时间',
    FOREIGN KEY (category_id) REFERENCES category(id)
) COMMENT '菜品表';

-- 套餐表
CREATE TABLE setmeal(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '套餐名称',
    category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
    price DECIMAL(8,2) NOT NULL COMMENT '套餐价格',
    image VARCHAR(300) NOT NULL COMMENT '图片',
    status TINYINT UNSIGNED DEFAULT 0 COMMENT '状态：0禁用，1启用',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '更新时间',
    FOREIGN KEY (category_id) REFERENCES category(id)
) COMMENT '套餐表';

-- 套餐菜品中间表（多对多关系）
CREATE TABLE setmeal_dish(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    setmeal_id INT UNSIGNED NOT NULL COMMENT '套餐ID',
    dish_id INT UNSIGNED NOT NULL COMMENT '菜品ID',
    copies TINYINT UNSIGNED NOT NULL COMMENT '份数',
    FOREIGN KEY (setmeal_id) REFERENCES setmeal(id),
    FOREIGN KEY (dish_id) REFERENCES dish(id)
) COMMENT '套餐菜品关系表';

八、学习总结与实践建议

基础优先：熟练掌握DDL、DML、DQL核心语法，多做单表查询练习。
设计思维：理解表关系设计原则，学会根据业务需求拆分表结构。
工具使用：熟练运用图形化工具（如DataGrip）提高开发效率。
实战练习：基于员工管理、菜品套餐管理等案例，完整实现"设计-创建-操作"流程。
避坑指南：
- 避免使用SELECT *查询，明确指定需要的字段。
- 操作UPDATE/DELETE时务必添加WHERE条件。
- 多表设计时合理选择外键策略，平衡数据一致性与性能。

想要获取本文知识点对应的SQL脚本文件？我可以帮你整理一份MySQL基础语法实战脚本，包含文中所有示例代码，方便直接在数据库中执行练习，需要吗？

MySQL 黑马基础学习笔记