mysql sql
sql
发布:  
更新:  
字数:   2.3k
阅时:   10 分
  

MySQL 是全球最流行的开源关系型数据库管理系统，广泛应用于 Web 应用开发。本教程将全面介绍 MySQL 的安装配置、核心操作、高级功能及最佳实践。

📦 一、MySQL 安装与配置

安装 MySQL

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server

# CentOS/RHEL
sudo yum install mysql-server

# macOS (Homebrew)
brew install mysql

# Windows
下载官方安装包：https://dev.mysql.com/downloads/mysql/

初始配置

# 安全配置向导（设置root密码等）
sudo mysql_secure_installation

# 启动/停止服务
sudo systemctl start mysql
sudo systemctl stop mysql

# 设置开机启动
sudo systemctl enable mysql

配置文件详解（my.cnf）

[mysqld]
# 数据存储目录
datadir=/var/lib/mysql

# 允许最大连接数
max_connections=200

# 查询缓存大小
query_cache_size=128M

# 字符集设置
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# InnoDB 缓冲池大小 (推荐70-80%内存)
innodb_buffer_pool_size=1G

# 错误日志路径
log-error=/var/log/mysql/error.log

🔌 二、MySQL 基础操作

连接 MySQL

# 本地连接
mysql -u root -p

# 远程连接
mysql -h 192.168.1.100 -P 3306 -u username -p

数据库操作

-- 创建数据库
CREATE DATABASE company_db;

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE company_db;

-- 删除数据库
DROP DATABASE legacy_db;

表操作

-- 创建员工表
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE DEFAULT (CURRENT_DATE)
);

-- 查看表结构
DESCRIBE employees;

-- 修改表结构
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees MODIFY COLUMN salary FLOAT;

-- 删除表
DROP TABLE temp_table;

📊 三、数据操作（CRUD）

插入数据

-- 单条插入
INSERT INTO employees (name, email, department, salary)
VALUES ('张三', 'zhang@company.com', '技术部', 15000);

-- 批量插入
INSERT INTO employees (name, email, department, salary) VALUES
('李四', 'li@company.com', '市场部', 12000),
('王五', 'wang@company.com', '财务部', 13000),
('赵六', 'zhao@company.com', '技术部', 16000);

查询数据

-- 基本查询
SELECT * FROM employees;

-- 条件查询
SELECT name, salary
FROM employees
WHERE department = '技术部' AND salary > 14000;

-- 排序与分页
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 5 OFFSET 0;

-- 模糊查询
SELECT * FROM employees
WHERE name LIKE '张%' OR email LIKE '%@company.com';

更新数据

-- 更新单条记录
UPDATE employees
SET salary = salary * 1.1
WHERE id = 1;

-- 批量更新
UPDATE employees
SET department = '研发部'
WHERE department = '技术部';

删除数据

-- 删除单条记录
DELETE FROM employees
WHERE id = 5;

-- 清空表数据
TRUNCATE TABLE temp_employees;

🔗 四、高级查询技巧

多表连接

-- 创建部门表
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
location VARCHAR(100)
);

-- 内连接
SELECT e.name, e.salary, d.name AS dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 左连接
SELECT e.name, d.name AS dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

聚合函数

-- 基本聚合
SELECT
COUNT(*) AS total_employees,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
MIN(hire_date) AS oldest_hire
FROM employees;

-- 分组统计
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

子查询

-- 单行子查询
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- IN子查询
SELECT name
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE location = '北京'
);

-- EXISTS子查询
SELECT d.name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);

⚡ 五、索引与性能优化

索引类型

-- 创建普通索引
CREATE INDEX idx_email ON employees(email);

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

-- 创建复合索引
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- 查看索引
SHOW INDEX FROM employees;

-- 删除索引
DROP INDEX idx_email ON employees;

查询优化技巧

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM employees WHERE department = '技术部';

-- 避免SELECT *
SELECT id, name, department FROM employees;

-- 合理使用LIMIT
SELECT * FROM large_table LIMIT 100;

-- 避免在WHERE子句中使用函数
-- 不推荐
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- 推荐
SELECT * FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

👥 六、用户与权限管理

用户管理

-- 创建用户
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'secure_password';

-- 修改密码
ALTER USER 'dev_user'@'localhost' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'old_user'@'%';

权限管理

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'dev_user'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

-- 查看权限
SHOW GRANTS FOR 'dev_user'@'localhost';

-- 撤销权限
REVOKE DELETE ON company_db.* FROM 'dev_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

💾 七、备份与恢复

备份数据库

# 备份单个数据库
mysqldump -u root -p company_db > company_db_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_dbs_backup.sql

# 压缩备份
mysqldump -u root -p company_db | gzip > company_db_backup.sql.gz

恢复数据库

# 恢复数据库
mysql -u root -p company_db < company_db_backup.sql

# 从压缩文件恢复
gunzip < company_db_backup.sql.gz | mysql -u root -p company_db

# 单表恢复
mysql -u root -p company_db employees < employees_backup.sql

定时备份（Cron Job）

# 添加定时任务
crontab -e

# 每天凌晨2点备份
0 2 * * * /usr/bin/mysqldump -u root -pPASSWORD company_db | gzip > /backups/company_db_$(date +\%F).sql.gz

🔄 八、事务与锁机制

事务处理

-- 开始事务
START TRANSACTION;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

锁机制

-- 手动加锁
SELECT * FROM orders WHERE id = 100 FOR UPDATE;

-- 查看当前锁
SHOW OPEN TABLES WHERE In_use > 0;

🧩 九、存储过程与函数

创建存储过程

DELIMITER //

CREATE PROCEDURE IncreaseSalaries(IN dept_name VARCHAR(50), IN increase_percent FLOAT)
BEGIN
UPDATE employees
SET salary = salary * (1 + increase_percent/100)
WHERE department = dept_name;
END //

DELIMITER ;

-- 调用存储过程
CALL IncreaseSalaries('技术部', 10);

创建函数

DELIMITER //

CREATE FUNCTION GetEmployeeCount(dept_name VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE emp_count INT;
SELECT COUNT(*) INTO emp_count
FROM employees
WHERE department = dept_name;
RETURN emp_count;
END //

DELIMITER ;

-- 使用函数
SELECT GetEmployeeCount('市场部');

⏰ 十、触发器与事件调度

创建触发器

-- 审计日志表
CREATE TABLE audit_log (
id INT AUTO_INCREMENT PRIMARY KEY,
action VARCHAR(20),
table_name VARCHAR(50),
record_id INT,
change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器
DELIMITER //

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (action, table_name, record_id)
VALUES ('UPDATE', 'employees', NEW.id);
END //

DELIMITER ;

事件调度

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建定期清理事件
CREATE EVENT daily_audit_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
DELETE FROM audit_log WHERE change_time < NOW() - INTERVAL 30 DAY;

🐍 十一、Python 中使用 MySQL

import mysql.connector
from mysql.connector import Error

try:
# 创建连接
connection = mysql.connector.connect(
host='localhost',
user='python_user',
password='secure_pass',
database='company_db'
)

if connection.is_connected():
cursor = connection.cursor()

# 执行查询
cursor.execute("SELECT * FROM employees")
employees = cursor.fetchall()

# 插入数据
insert_query = """
INSERT INTO employees (name, email, department, salary)
VALUES (%s, %s, %s, %s)
"""
employee_data = ('钱七', 'qian@company.com', '人事部', 11000)
cursor.execute(insert_query, employee_data)
connection.commit()

# 更新数据
update_query = "UPDATE employees SET salary = salary * 1.05 WHERE department = %s"
cursor.execute(update_query, ('技术部',))
connection.commit()

except Error as e:
print("数据库错误:", e)

finally:
if connection.is_connected():
cursor.close()
connection.close()

🚨 十二、常见问题解决

忘记 root 密码

# 停止MySQL服务
sudo systemctl stop mysql

# 启动安全模式
sudo mysqld_safe --skip-grant-tables &

# 登录并修改密码
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
exit;

# 重启MySQL
sudo systemctl restart mysql

性能问题诊断

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看进程列表
SHOW PROCESSLIST;

-- 优化表
OPTIMIZE TABLE large_table;

-- 分析表
ANALYZE TABLE employees;

连接问题排查

# 错误：ERROR 1045 (28000)
# 解决方案：检查用户名/密码，确保主机权限

# 错误：ERROR 2002 (HY000)
# 解决方案：确认MySQL服务运行中，检查socket路径

# 错误：ERROR 2013 (HY000)
# 解决方案：增加连接超时时间 wait_timeout

📚 学习资源推荐

  1. MySQL 官方文档 - 最权威的参考资料
  2. MySQL Tutorial - 免费教程网站
  3. LeetCode 数据库题库 - 实战练习
  4. MySQL Workbench - 官方图形化管理工具
  5. Percona Toolkit - 高级管理工具集
graph TD
A[安装MySQL] --> B[创建数据库]
B --> C[设计表结构]
C --> D[数据操作CRUD]
D --> E[高级查询]
E --> F[性能优化]
F --> G[备份恢复]
G --> H[事务管理]
H --> I[程序集成]

MySQL 作为最流行的关系型数据库之一，掌握其核心功能对开发者和DBA至关重要。本教程覆盖了从基础到高级的全面知识体系，建议结合实际项目实践加深理解。记住，数据库设计优化往往比硬件升级更能提升性能！

参考&致谢

系列教程

全部文章RSS订阅

数据库系列

作者: 夜法之书
文章链接: https://blog.17lai.site/posts/622f0f0f/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 夜法之书 !
mysql sql
赏
评论
数据加载中 ...
 上一篇

阅读全文

PostgreSQL 使用全面指南：从入门到企业级应用
PostgreSQL 使用全面指南：从入门到企业级应用 PostgreSQL 使用全面指南：从入门到企业级应用
PostgreSQL 是世界上最先进的开源关系型数据库，以其强大的功能、高度的扩展性和严格的 SQL 标准兼容性著称。本教程将带你从基础到高级全面掌握 PostgreSQL 的使用技巧，涵盖安装配置、核心操作、高级特性和性能优化等各个方面。
2025-11-22 sql
sql postgresql
下一篇 

阅读全文

SQLite使用全面教程：轻量级数据库的终极指南
SQLite使用全面教程：轻量级数据库的终极指南 SQLite使用全面教程：轻量级数据库的终极指南
SQLite是世界上最广泛部署的数据库引擎，以其轻量、零配置和嵌入式特性著称。本教程将带你从基础到高级全面掌握SQLite的使用技巧，特别强化了数据库连接和命令行操作部分。
2025-11-22 sql
sql sqlite
  目录