老程序员博客

编程生涯的最后一舞

当前位置：首页 > 技术 > 正文内容

MySQL 数据库核心操作与 SQL 进阶实战指南

访客 技术 2026年7月3日 1

环境初始化与字符集配置

在部署 MySQL 数据库时，早期版本（如 5.5 系列）的默认字符集通常为 latin1（等同于 ISO-8859-1）。为了完美支持多语言及特殊字符（如 Emoji），建议在安装配置阶段或修改配置文件（my.ini / my.cnf）时，将默认字符集更改为 utf8mb4。配置完成后，若服务启动异常，可检查系统权限是否以管理员身份运行，或通过管理工具（如 SQLyog、Navicat）进行连接测试，必要时清理残留注册表并重新安装。

数据库与表结构管理 (DDL)

数据库级操作

创建数据库时，应显式指定字符集与排序规则，以确保数据存储的一致性。

-- 创建数据库并指定 utf8mb4 字符集
CREATE DATABASE tech_blog_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看当前实例下的所有数据库
SHOW DATABASES;

-- 切换当前工作数据库
USE tech_blog_db;

-- 安全删除数据库（若存在）
DROP DATABASE IF EXISTS tech_blog_db;

-- 修改现有数据库的字符集
ALTER DATABASE tech_blog_db CHARACTER SET gbk;

表级操作与约束设计

表结构设计是数据库性能与数据完整性的基石。MySQL 提供了多种约束机制，包括主键（Primary Key）、非空（Not Null）、唯一（Unique）等。

-- 创建包含多种约束的门店表
CREATE TABLE store_branches (
    branch_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键，自增',
    branch_name VARCHAR(100) NOT NULL COMMENT '门店名称，非空',
    contact_phone VARCHAR(50) UNIQUE COMMENT '联系电话，唯一',
    monthly_revenue DECIMAL(10, 2) DEFAULT 0.00 COMMENT '月营业额，精度控制'
);

-- 创建用户表，演示联合约束
CREATE TABLE system_users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL
);

约束特性解析：

  • 主键与自增：主键确保记录的唯一性。配合 AUTO_INCREMENT，数据库会自动维护一个递增的序列值，插入数据时无需手动指定该字段。
  • 非空约束：强制字段必须包含有效值，拒绝 NULL 插入。
  • 唯一约束：保证列中的数据不重复，但允许存在多个 NULL 值（因为 NULL 不参与唯一性比较）。

表结构变更

-- 重命名表
RENAME TABLE store_branches TO retail_branches;

-- 添加新列
ALTER TABLE retail_branches ADD COLUMN manager_name VARCHAR(50) AFTER branch_name;

-- 修改列名及数据类型
ALTER TABLE retail_branches CHANGE manager_name branch_manager VARCHAR(100);

-- 删除列
ALTER TABLE retail_branches DROP COLUMN branch_manager;

-- 清空表数据（保留表结构，重置自增ID，比 DELETE 更高效）
TRUNCATE TABLE retail_branches;

-- 删除表结构
DROP TABLE IF EXISTS retail_branches;

数据操作 (DML)

数据插入与更新

-- 标准插入（省略自增主键）
INSERT INTO store_branches (branch_name, contact_phone, monthly_revenue) 
VALUES ('市中心旗舰店', '010-88889999', 150000.50);

-- 批量插入
INSERT INTO store_branches (branch_name, contact_phone) 
VALUES ('科技园分店', '0755-12345678'), ('大学城分店', '020-87654321');

-- 使用 REPLACE 插入或替换（基于主键或唯一索引冲突）
REPLACE INTO store_branches (branch_id, branch_name, contact_phone) 
VALUES (1, '市中心旗舰店(重装)', '010-88880000');

-- 条件更新
UPDATE store_branches 
SET monthly_revenue = monthly_revenue * 1.1, contact_phone = '010-99998888' 
WHERE branch_id = 1;

数据删除

-- 条件删除（记录日志，可回滚）
DELETE FROM store_branches WHERE branch_id = 2;

-- 清空全表（不记录单行删除日志，速度快）
TRUNCATE TABLE store_branches;

数据检索与条件过滤 (DQL)

基础查询与去重

-- 查询所有列
SELECT * FROM store_branches;

-- 查询特定列并使用 DISTINCT 去重
SELECT DISTINCT contact_phone FROM store_branches;

条件过滤 (WHERE)

-- 逻辑运算符 (AND, OR)
SELECT * FROM store_branches WHERE monthly_revenue > 100000 AND branch_name LIKE '%旗舰店';

-- 范围与集合 (IN, BETWEEN)
SELECT * FROM store_branches WHERE branch_id IN (1, 3, 5);
SELECT * FROM store_branches WHERE monthly_revenue BETWEEN 50000 AND 100000;

-- 模糊匹配 (LIKE)
SELECT * FROM store_branches WHERE branch_name LIKE '科技园%'; -- 以"科技园"开头

-- 空值判断 (IS NULL / IS NOT NULL)
SELECT * FROM store_branches WHERE contact_phone IS NOT NULL;

排序与分页

-- 多字段排序（先按营业额降序，再按ID升序）
SELECT * FROM store_branches ORDER BY monthly_revenue DESC, branch_id ASC;

-- 分页查询（Limit offset, row_count）
SELECT * FROM store_branches LIMIT 10, 5; -- 跳过前10条，取5条（常用于第3页，每页5条）

内置函数应用

字符串与数学函数

SELECT 
    LOWER('MySQL') AS lower_str,          -- 转小写: mysql
    UPPER('data') AS upper_str,           -- 转大写: DATA
    LENGTH('数据库') AS str_length,       -- 字节长度
    CHAR_LENGTH('数据库') AS char_len,    -- 字符长度
    SUBSTRING('Hello World', 1, 5) AS sub_str, -- 截取: Hello
    CONCAT(branch_name, ' - ', contact_phone) AS full_info, -- 拼接
    REPLACE(branch_name, '店', '中心') AS replaced_name,    -- 替换
    IFNULL(monthly_revenue, 0) AS safe_revenue              -- 空值处理
FROM store_branches;

-- 数学计算
SELECT 
    ROUND(123.456, 2) AS rounded_val,  -- 四舍五入保留2位: 123.46
    CEIL(123.1) AS ceil_val,           -- 向上取整: 124
    FLOOR(123.9) AS floor_val;         -- 向下取整: 123

日期与时间函数

SELECT 
    NOW() AS current_datetime,
    CURDATE() AS current_date,
    CURTIME() AS current_time,
    YEAR(NOW()) AS current_year,
    MONTH(NOW()) AS current_month,
    DAY(NOW()) AS current_day,
    HOUR(NOW()) AS current_hour;

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_time;

-- 日期加减计算
SELECT 
    DATE_ADD(hire_date, INTERVAL 3 MONTH) AS probation_end, -- 增加3个月
    DATE_SUB(hire_date, INTERVAL 7 DAY) AS pre_week         -- 减少7天
FROM employees;

-- 日期差值计算
SELECT 
    DATEDIFF(CURDATE(), hire_date) AS days_diff,            -- 相差天数
    TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_diff -- 相差月数
FROM employees;

聚合分析与分组

聚合函数

聚合函数用于对一组值执行计算并返回单个值。常见的有 COUNT(), MAX(), MIN(), SUM(), AVG()。

SELECT 
    COUNT(*) AS total_records,
    COUNT(contact_phone) AS valid_phones, -- 不统计 NULL 值
    MAX(monthly_revenue) AS max_revenue,
    MIN(monthly_revenue) AS min_revenue,
    SUM(monthly_revenue) AS total_revenue,
    AVG(monthly_revenue) AS avg_revenue
FROM store_branches;

分组查询 (GROUP BY & HAVING)

GROUP BY 用于将结果集划分为多个逻辑组，HAVING 则用于在分组后对聚合结果进行过滤（WHERE 无法过滤聚合函数）。

-- 统计每个部门各职位的最高和平均薪资，并过滤出平均薪资大于 8000 的组合
SELECT 
    dept_id, 
    job_title, 
    MAX(salary) AS max_sal, 
    AVG(salary) AS avg_sal 
FROM employees 
GROUP BY dept_id, job_title
HAVING AVG(salary) > 8000
ORDER BY avg_sal DESC;

索引设计与优化

索引是提升数据库查询性能的关键数据结构（通常为 B+ 树）。

索引的创建与管理

-- 查看表索引
SHOW INDEX FROM employees;

-- 创建普通索引
CREATE INDEX idx_job_title ON employees(job_title);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_emp_email ON employees(email);

-- 创建复合索引
CREATE INDEX idx_dept_job ON employees(dept_id, job_title);

-- 删除索引
ALTER TABLE employees DROP INDEX idx_job_title;
-- 或者
DROP INDEX uk_emp_email ON employees;

最左前缀匹配原则

对于复合索引（如 (A, B, C)），查询条件必须从索引的最左列开始连续匹配。可以使用 EXPLAIN 关键字来分析 SQL 的执行计划与索引命中情况。

-- 命中索引 (A, B, C)
EXPLAIN SELECT * FROM employees WHERE dept_id = 10 AND job_title = 'Engineer';

-- 命中索引 (A)
EXPLAIN SELECT * FROM employees WHERE dept_id = 10;

-- 索引失效 (跳过了 A，直接查询 B)
EXPLAIN SELECT * FROM employees WHERE job_title = 'Engineer';

多表关联查询 (JOIN)

在关系型数据库中，数据通常分散在多个表中。通过 JOIN 操作可以基于关联条件将多表数据组合在一起。

-- 隐式内连接（不推荐，可读性差）
SELECT d.dept_name, e.emp_name, e.salary 
FROM departments d, employees e 
WHERE d.dept_id = e.dept_id;

-- 显式内连接 (INNER JOIN)：仅返回两表中匹配的记录
SELECT 
    d.dept_name, 
    e.emp_name, 
    e.job_title
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Research';

-- 左外连接 (LEFT JOIN)：返回左表所有记录，右表无匹配则补 NULL
SELECT 
    e.emp_name, 
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 右外连接 (RIGHT JOIN)：返回右表所有记录，左表无匹配则补 NULL
SELECT 
    e.emp_name, 
    d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

外键约束 (Foreign Key)

外键用于在表级别维护引用完整性，确保子表中的关联字段必须在父表中存在。

-- 在创建表时添加外键
CREATE TABLE project_assignments (
    assignment_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    project_code VARCHAR(50),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON DELETE CASCADE ON UPDATE CASCADE
);

注：在实际的高并发互联网架构中，为了保证数据库的扩展性与写入性能，通常会放弃物理外键约束，转而通过应用层代码逻辑来保证数据的一致性。

标签: MySQL
返回列表

上一篇：React应用中的路由权限管理

下一篇：基于字符模式的图形生成与矩阵变换技术

相关文章

Linux crontab 详解

1) crontab 是什么cron 是 Linux 的定时任务守护进程；crontab 是用来编辑/查看“按时间周期执行命令”的表（cron table）。常见两类：用户 crontab：每个用户一份（crontab -e 编辑）系统级 crontab / cron.d：可指定执行用户（/etc/crontab、/etc/cron.d/*）2) crontab 时间...

富文本里可以允许的 HTML 属性

一、所有标签默认允许的安全属性（极少）class        （可选）id           （通常建议禁用）title️ 注意：id 容易被滥用做锚点注入，很多系统直接禁用class 允许的话最好只允许固定前缀（如 editor-*）二、a 标签允许属性<a href="" t...

Mac 安装 Node.js 指南

方法一：通过官网安装包（最简单，适合初学者）如果你只是想快速安装并开始使用，这是最直接的方法。访问 Node.js 官网。页面会显示两个版本：LTS (Recommended For Most Users)：长期支持版，最稳定。建议选这个。Current：最新特性版，包含最新功能但可能不够稳定。下载 .pkg 安装包并运行。按照安装向导点击“下一步”即可完成。方法二：使用 Homebrew 安装（...

Dom\HTML_NO_DEFAULT_NS 的副作用：自动加闭合标签

在使用Dom\HTMLDocument时，Dom\HTML_NO_DEFAULT_NS 将禁止在解析过程中设置元素的命名空间, 此设置是为了与DOMDocument向后兼容而存在的。当使用它时，已知的一个副作用就是：自动加闭合标签例如 </img> 为什么会这样？当你使用：Dom\HTML_NO_DEFAULT_NS文档会变成 无命名空间模式，此时内部更接近 XML...

Laravel 事件和监听器创建

在 Laravel 中，使用 Artisan 命令创建 Events（事件） 和 Listeners（监听器） 是非常高效的。你可以通过以下几种方式来实现：1. 手动创建单个 Event如果你只想创建一个事件类，可以使用 make:event 命令：Bashphp artisan make:event UserRegistered执行后，文件将生成在 app/Even...

自定义域名解析神器 dnsmasq

什么是 dnsmasq？dnsmasq 是一个轻量级、功能强大的网络服务工具，专为小型和中等规模网络设计。它是一个综合的网络基础设施解决方案[1]。dnsmasq 能做什么？功能说明应用场景DNS 转发与缓存将 DNS 查询转发到上游服务器（ISP、Google DNS 等），并在本地缓存结果加快 DNS 查询速度，减少外部 DNS 流量本地 DNS解析本地网络设备的主机名，无需编辑&n...

发表评论

访客

◎欢迎参与讨论，请在这里发表您的看法和观点。

Copyright © agingcoder.cn

Powered By Z-BlogPHP. Theme by TOYEAN.