搜索菜单
当前位置: 首页 > SQL > MySQL 基础核心概念与关键语句速查

MySQL 基础核心概念与关键语句速查

码头散步
码头散步
发布时间：2026-10-14 | 3人浏览过
原创

本文详细解析关系型数据库（以 MySQL 为主，部分特性结合 SQL Server 示例）的基础知识体系，包括数据库结构定义、完整的数据操作语句（CRUD）、复杂查询技巧（排序/分页/连接），以及高级功能如存储过程与事务控制，帮助开发者快速掌握数据库设计的核心逻辑。

1. 结构化查询语言 (SQL) 概览

SQL（Structured Query Language）是用于管理关系数据库的标准语言。理解其基本语法特征是编写正确语句的前提：

  • 解释型执行：SQL 语句通常逐句解释执行，无需整体编译。
  • 字符串与符号：字符串值必须使用单引号 ' ' 包含；不存在专用的布尔类型，但在某些视图中支持 true/false。
  • 运算符：赋值与逻辑相等均使用 =；比较运算符包括 >, <, =, <> 等；逻辑运算符为 NOT (!), AND (&&), OR (||)。
  • 大小写敏感性：SQL 关键字不区分大小写。

2. 创建数据库与物理文件配置

在创建数据库时，通常需要指定主数据文件、辅助数据文件和日志文件的逻辑名称、初始大小、增长率及物理路径。以下展示了一个包含文件组配置的详细语法示例（适用于 SQL Server 环境）：

USE master;
IF EXISTS(SELECT * FROM sysdatabases WHERE name='School')
    DROP DATABASE School;
GO

CREATE DATABASE School
ON PRIMARY 
(
    NAME = 'School_data',
    SIZE = 3MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%,
    FILENAME = 'f:\project\School_data.mdf'
)
FILEGROUP mygroup
(
    NAME = 'School_data1',
    SIZE = 3MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%,
    FILENAME = 'F:\qiyi\School_data1.ndf'
)
LOG ON
(
    NAME = 'School_log',
    SIZE = 3MB,
    FILEGROWTH = 10%,
    FILENAME = 'f:\project\School_log.ldf'
);

第1步：启用高级配置工具

若需通过脚本创建外部目录或执行系统级命令，需启用 xp_cmdshell。

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

3. 创建数据表与完整性约束

表是数据存储的基本单元，定义时需确立字段类型及约束条件。数据完整性分为四类：

  1. 实体完整性：确保每行记录唯一，通过主键（PK）和标识列（IDENTITY）实现。
  2. 域完整性：限制列输入值的合法性，通过数据类型、NOT NULL、DEFAULT、CHECK 实现。
  3. 引用完整性：维持外键（FK）与被引用表主键的一致性。
  4. 自定义完整性：通过触发器或存储过程实现复杂的业务规则。

第2步：定义表结构与约束

使用 ALTER TABLE ... ADD CONSTRAINT 语句可灵活添加各类约束：

-- 创建班级表（含主键）
CREATE TABLE Classes
(
    ClassID INT IDENTITY(1,1) PRIMARY KEY,
    ClassName NVARCHAR(50) NOT NULL
);

-- 创建教师表（含多种约束）
CREATE TABLE Teacher
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    ClassId INT NOT NULL,
    Gender BIT NOT NULL,
    Age INT CHECK(Age > 0 AND Age <= 100),
    Salary MONEY DEFAULT(5000),
    Birthday DATETIME
);

-- 显式添加主键、唯一键、外键约束
ALTER TABLE Teacher
ADD CONSTRAINT UQ_Teacher_Name UNIQUE(Name),
CONSTRAINT DF_Teacher_Salary DEFAULT(5000) FOR Salary,
CONSTRAINT FK_Teacher_Classes_Classid FOREIGN KEY(ClassId) REFERENCES Classes(ClassId);

注意：建立主外键关系时，应先插入主表数据，再插入从表数据；删除时反之。建议使用级联操作（CASCADE, SET NULL）需谨慎评估对数据的影响。

4. 字符类型辨析

了解字符类型的存储差异有助于优化存储空间：

  • CHAR: 定长。存储内容不足长度时不收缩空间。例如分配 CHAR(10) 存入 'ab'，占用字节数为 10。
  • VARCHAR: 变长。自动收缩多余空间。存入 'ab' 仅占用相应字节数。
  • NCHAR/NVARCHAR: Unicode 编码，固定或可变长度，每个字符占 2 个字节，适合存储中文等多字节字符。

5. 核心 CRUD 操作规范

第3步：数据插入 (INSERT)

遵循“三对应”原则：类型、数量、顺序。注意标识列默认不能手动赋值。

-- 指定列名插入
INSERT INTO Teacher(Name, ClassId, Gender, Age, Salary)
VALUES ('张三', 1, 1, 30, 8000);

-- 利用默认值和空值
INSERT INTO Teacher(Name, ClassId, Gender, Age, Salary, Birthday)
VALUES ('李四', 1, 0, 28, DEFAULT, NULL);

第4步：数据删除 (DELETE vs TRUNCATE)

两者区别显著：DELETE 逐行删除，记录日志，可触发 DELETE 触发器，不重置自增种子；TRUNCATE 一次性清空所有数据，最小化日志，会重置自增种子，且不可回滚。

第5步：数据更新 (UPDATE)

务必包含 WHERE 子句以防全表更新。支持表达式计算：

-- 给特定条件的教师涨薪
UPDATE Teacher 
SET Salary = Salary + 500 
WHERE ClassId = 1 AND Age > 30;

6. 高级查询与数据处理

第6步：多表查询与聚合

使用 JOIN 关联多表，结合 GROUP BY 和 HAVING 进行分组统计。聚合函数（AVG, COUNT, SUM, MIN, MAX）常用于汇总数据。

第7步：子查询与 EXISTS

将查询结果作为另一查询的条件。若子查询返回多行多列，建议使用 EXISTS 进行存在性测试：

SELECT * FROM Student 
WHERE EXISTS (
    SELECT 1 FROM Grade WHERE Grade.Name = 'Math' AND Student.ClassId = Grade.ID
);

第8步：动态显示状态 (CASE WHEN)

类似于编程中的 Switch-Case，用于分类显示：

SELECT StudentNo, Name,
       CASE 
           WHEN Score >= 90 THEN 'A'
           WHEN Score >= 80 THEN 'B'
           ELSE 'C'
       END AS GradeLevel
FROM Results;

7. 逻辑控制与自动化处理

第9步：流程控制 (IF...ELSE / WHILE)

通过声明变量并使用 BEGIN...END 块来包裹多行逻辑。结合 WHILE 循环可实现批量处理任务。

第10步：事务控制 (TRANSACTION)

保证原子性（ACID）。一旦开启，要么全部提交（COMMIT），要么出现错误时全部回滚（ROLLBACK）：

BEGIN TRAN;
BEGIN TRY
    UPDATE Account SET Balance = Balance - 100 WHERE ID = 1;
    UPDATE Account SET Balance = Balance + 100 WHERE ID = 2;
    COMMIT TRAN;
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
END CATCH;

第11步：视图与触发器

视图是一种虚拟表，简化复杂查询，但修改视图涉及多表通常受限。
触发器（TRIGGER）是在表发生 INSERT、UPDATE 或 DELETE 时自动执行的程序块，常用于审计日志或级联维护。

8. 存储过程与参数化

存储过程是将一组 SQL 语句封装在服务器端，提高执行效率并增强安全性。

第12步：创建与调用带输出参数的存储过程

定义 OUTPUT 参数可将内部计算结果返回给外部调用者：

CREATE PROCEDURE usp_GetStats
    @TotalCount INT OUTPUT,
    @AvgScore DECIMAL(5,2) OUTPUT
AS
BEGIN
    SELECT @TotalCount = COUNT(*) FROM Results;
    SELECT @AvgScore = AVG(Score) FROM Results;
END;

-- 调用
DECLARE @cnt INT, @avg DECIMAL(5,2);
EXEC usp_GetStats @TotalCount = @cnt OUTPUT, @AvgScore = @avg OUTPUT;
SELECT @cnt AS TotalRows, @avg AS Average;

第13步：索引与性能优化

合理使用聚集索引（CLUSTERED）和非聚集索引（NONCLUSTERED）加速检索。主键默认为聚集索引。临时表（#table）可用于缓存中间数据。

总结

掌握 SQL 的基础在于理解数据结构的设计（约束）与数据的流转（CRUD）。在实际开发中，建议将复杂的业务逻辑封装到存储过程中，并利用事务机制保障数据的一致性与安全性。熟练运用 JOIN、子查询和聚合函数是应对复杂报表需求的关键。以上就是 MySQL 基础知识点汇总的详细内容，更多关于 MySQL 进阶的资料请关注本站其它相关文章！

相关文章

相关标签: SQL MySQL 数据库设计 后端开发

本站声明

本文内容仅供参考，不代表本站观点。转载或引用请注明出处。如发现内容问题，请通过联系我们页面反馈。

作者最新文章

全部内容

Python 相关产品和应用

全部下载
Python Selenium
Selenium

Selenium

4.51.0 · Windows

用于浏览器自动化和 Web 测试的 Python 工具。
Python 机器学习
scikit-learn

scikit-learn

1.9.1 · Windows

常用 Python 机器学习算法与模型评估工具库。
Python 代码质量
Black

Black

26.10.1 · Windows

强调一致性的 Python 自动代码格式化工具。
Python 代码质量
Ruff

Ruff

0.17.1 · macOS

由 Rust 编写的高速 Python 代码检查和格式化工具。
Python pytest
pytest

pytest

9.1.1 · Windows

简洁而强大的 Python 自动化测试框架。
Python FastAPI API
FastAPI

FastAPI

0.146.0 · Windows

高性能 Python API 框架，支持类型提示和自动接口文档。
Python Flask Web开发
Flask

Flask

3.1.3 · Windows

轻量灵活的 Python Web 应用框架。
Python Django Web开发
Django

Django

6.1.2 · Windows

成熟的 Python Web 框架，适合构建内容站点和业务系统。
Python Spyder
Spyder

Spyder

6.1.7 · Windows

面向科学计算与数据分析的 Python 集成开发环境。
Python SciPy
SciPy

SciPy

1.18.1 · Windows

面向科学、工程和技术计算的 Python 算法库。
Python Pandas 数据分析
pandas

pandas

3.0.6 · Windows

Python 数据分析库，提供 DataFrame 与丰富的数据处理能力。
Python NumPy
NumPy

NumPy

2.5.4 · Windows

Python 科学计算基础库，提供高性能多维数组与数值运算。

文章分类

专题列表

ipython.cn 学习网
人工智能 Java AI行业服务