后端-MySQL基础知识

阿鑫_996
6 阅读9分钟

MySQL 基础知识

一、MySQL 概述

1.1 什么是 MySQL

MySQL 是一个开源的关系型数据库管理系统（RDBMS），使用 SQL（结构化查询语言）进行数据库管理。

核心特点：

  • 开源免费
  • 性能优秀
  • 易于使用
  • 跨平台支持
  • 丰富的功能

1.2 适用场景

场景说明
Web 应用网站后台数据存储
内容管理CMS、博客系统
电商系统商品、订单管理
数据分析数据仓库、报表
企业应用ERP、CRM 系统

二、数据类型

2.1 数值类型

类型说明范围
TINYINT1 字节整数-128 到 127
SMALLINT2 字节整数-32768 到 32767
INT4 字节整数-2147483648 到 2147483647
BIGINT8 字节整数极大整数
FLOAT单精度浮点数约 7 位小数
DOUBLE双精度浮点数约 15 位小数
DECIMAL(M,D)精确小数M 总位数，D 小数位

2.2 字符串类型

类型说明长度
CHAR(n)固定长度字符串0-255 字符
VARCHAR(n)可变长度字符串0-65535 字符
TEXT长文本最大 65535 字符
MEDIUMTEXT中等文本最大 16MB
LONGTEXT超长文本最大 4GB

2.3 日期时间类型

类型说明格式
DATE日期YYYY-MM-DD
TIME时间HH:MM:SS
DATETIME日期时间YYYY-MM-DD HH:MM:SS
TIMESTAMP时间戳自动更新
YEAR年份YYYY

2.4 其他类型

  • BOOLEAN：布尔值（实际是 TINYINT(1)）
  • ENUM：枚举类型
  • SET：集合类型
  • JSON：JSON 数据（MySQL 5.7+）
  • BLOB：二进制大对象

三、数据库操作

3.1 创建数据库

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库（指定字符集）
CREATE DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

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

-- 使用数据库
USE mydb;

-- 删除数据库
DROP DATABASE mydb;

3.2 创建表

-- 创建表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 查看表结构
DESCRIBE users;
DESC users;

-- 查看所有表
SHOW TABLES;

-- 删除表
DROP TABLE users;

3.3 修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改列
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 重命名列
ALTER TABLE users CHANGE COLUMN username name VARCHAR(50);

-- 添加索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 删除索引
ALTER TABLE users DROP INDEX idx_email;

四、约束

4.1 主键约束

-- 单列主键
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- 自增主键
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

-- 复合主键
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

4.2 外键约束

-- 创建外键
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  total DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 外键约束选项
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE      -- 级联删除
    ON UPDATE CASCADE      -- 级联更新
);

4.3 其他约束

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,        -- 非空约束
  email VARCHAR(100) UNIQUE,             -- 唯一约束
  age INT CHECK (age >= 0 AND age <= 150), -- 检查约束（MySQL 8.0+）
  status ENUM('active', 'inactive') DEFAULT 'active' -- 默认值
);

五、数据操作（CRUD）

5.1 插入数据（INSERT）

-- 插入单条记录
INSERT INTO users (username, email, password) 
VALUES ('zhangsan', 'zhangsan@example.com', 'password123');

-- 插入多条记录
INSERT INTO users (username, email, password) 
VALUES 
  ('lisi', 'lisi@example.com', 'password123'),
  ('wangwu', 'wangwu@example.com', 'password123');

-- 插入查询结果
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE age > 18;

5.2 查询数据（SELECT）

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

-- 查询指定列
SELECT id, username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 18;

-- 多条件查询
SELECT * FROM users 
WHERE age > 18 AND status = 'active';

-- 模糊查询
SELECT * FROM users WHERE username LIKE 'zhang%';

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 限制结果数量
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 分页

-- 去重
SELECT DISTINCT status FROM users;

5.3 更新数据（UPDATE）

-- 更新单条记录
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- 更新多条记录
UPDATE users 
SET status = 'inactive' 
WHERE age < 18;

-- 更新多个字段
UPDATE users 
SET email = 'newemail@example.com', age = 25 
WHERE id = 1;

5.4 删除数据（DELETE）

-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 删除多条记录
DELETE FROM users WHERE status = 'inactive';

-- 清空表（保留表结构）
TRUNCATE TABLE users;

-- 删除所有记录
DELETE FROM users;

六、查询进阶

6.1 聚合函数

-- COUNT：计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM users;

-- SUM：求和
SELECT SUM(price) FROM products;

-- AVG：平均值
SELECT AVG(age) FROM users;

-- MAX/MIN：最大值/最小值
SELECT MAX(price) FROM products;
SELECT MIN(age) FROM users;

-- GROUP BY：分组
SELECT status, COUNT(*) 
FROM users 
GROUP BY status;

-- HAVING：分组后过滤
SELECT status, COUNT(*) as count
FROM users 
GROUP BY status
HAVING count > 10;

6.2 连接查询

-- 内连接（INNER JOIN）
SELECT u.username, o.order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接（LEFT JOIN）
SELECT u.username, o.order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接（RIGHT JOIN）
SELECT u.username, o.order_id, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 全连接（FULL OUTER JOIN）- MySQL 不支持，用 UNION 实现
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 自连接
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

6.3 子查询

-- 标量子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- 列子查询（IN）
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders);

-- 行子查询
SELECT * FROM users 
WHERE (age, status) = (SELECT MAX(age), 'active' FROM users);

-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id
);

-- 相关子查询
SELECT u.*, 
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

6.4 联合查询

-- UNION：合并结果（去重）
SELECT username FROM users
UNION
SELECT name FROM admins;

-- UNION ALL：合并结果（不去重）
SELECT username FROM users
UNION ALL
SELECT name FROM admins;

七、索引

7.1 创建索引

-- 普通索引
CREATE INDEX idx_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 全文索引（MyISAM 或 InnoDB 5.6+）
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 查看索引
SHOW INDEX FROM users;

7.2 索引优化

索引使用原则：

  • 经常用于 WHERE 条件的列
  • 经常用于 JOIN 的列
  • 经常用于 ORDER BY 的列
  • 避免在小表上建索引
  • 避免在频繁更新的列上建索引

索引失效场景：

-- 使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 索引失效

-- 使用 LIKE '%xxx'
SELECT * FROM users WHERE username LIKE '%zhang';  -- 索引失效

-- 类型转换
SELECT * FROM users WHERE id = '1';  -- 可能失效

-- OR 条件（部分列无索引）
SELECT * FROM users WHERE id = 1 OR email = 'xxx';  -- 可能失效

八、事务

8.1 事务特性（ACID）

  • 原子性（Atomicity）：事务要么全部成功，要么全部失败
  • 一致性（Consistency）：事务前后数据保持一致
  • 隔离性（Isolation）：并发事务互不干扰
  • 持久性（Durability）：事务提交后数据永久保存

8.2 事务操作

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
-- 回滚到保存点
ROLLBACK TO sp1;

8.3 隔离级别

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

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
隔离级别脏读不可重复读幻读
READ UNCOMMITTED✅✅✅
READ COMMITTED❌✅✅
REPEATABLE READ❌❌✅
SERIALIZABLE❌❌❌

九、视图

9.1 创建视图

-- 创建视图
CREATE VIEW user_orders AS
SELECT 
  u.username,
  u.email,
  o.order_id,
  o.total,
  o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 使用视图
SELECT * FROM user_orders WHERE username = 'zhangsan';

-- 查看视图
SHOW CREATE VIEW user_orders;

-- 删除视图
DROP VIEW user_orders;

9.2 更新视图

-- 可更新视图（满足条件）
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE status = 'active';

-- 通过视图更新数据
UPDATE active_users SET email = 'new@example.com' WHERE id = 1;

十、存储过程与函数

10.1 存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
  SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserById(1);

-- 带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;

CALL GetUserCount(@count);
SELECT @count;

10.2 函数

-- 创建函数
DELIMITER //
CREATE FUNCTION GetUserName(user_id INT)
RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE user_name VARCHAR(50);
  SELECT username INTO user_name FROM users WHERE id = user_id;
  RETURN user_name;
END //
DELIMITER ;

-- 使用函数
SELECT GetUserName(1);

十一、触发器

-- 创建触发器
DELIMITER //
CREATE TRIGGER update_user_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.updated_at = CURRENT_TIMESTAMP;
END //
DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER update_user_timestamp;

十二、Node.js 连接 MySQL

12.1 使用 mysql2

const mysql = require('mysql2/promise');

// 创建连接
const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

// 查询
const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [1]);

// 插入
const [result] = await connection.execute(
  'INSERT INTO users (username, email) VALUES (?, ?)',
  ['zhangsan', 'zhangsan@example.com']
);

// 关闭连接
await connection.end();

12.2 使用连接池

const mysql = require('mysql2/promise');

// 创建连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 使用连接池
async function queryUsers() {
  const [rows] = await pool.execute('SELECT * FROM users');
  return rows;
}

// 关闭连接池
await pool.end();

12.3 使用 ORM（Sequelize）

const { Sequelize, DataTypes } = require('sequelize');

// 创建 Sequelize 实例
const sequelize = new Sequelize('mydb', 'root', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

// 定义模型
const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false
  }
});

// 使用模型
const users = await User.findAll();
const user = await User.findByPk(1);
await User.create({ username: 'zhangsan', email: 'zhangsan@example.com' });

十三、性能优化

13.1 查询优化

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 避免 SELECT *
SELECT id, username FROM users;  -- 而不是 SELECT *

-- 使用 LIMIT
SELECT * FROM users LIMIT 10;

-- 合理使用索引
CREATE INDEX idx_email ON users(email);

13.2 表优化

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 修复表
REPAIR TABLE users;

13.3 配置优化

# my.cnf 配置示例
[mysqld]
# 连接数
max_connections = 200

# 缓冲区大小
innodb_buffer_pool_size = 1G

# 查询缓存（MySQL 8.0 已移除）
# query_cache_size = 64M

# 慢查询日志
slow_query_log = 1
long_query_time = 2

十四、备份与恢复

14.1 备份

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

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

# 备份表结构
mysqldump -u root -p --no-data mydb > schema.sql

# 备份数据
mysqldump -u root -p --no-create-info mydb > data.sql

14.2 恢复

# 恢复数据库
mysql -u root -p mydb < backup.sql

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

十五、安全建议

  1. 使用强密码：设置复杂的数据库密码
  2. 限制权限：为应用创建专用用户，只授予必要权限
  3. 防止 SQL 注入：使用参数化查询
  4. 定期备份：建立备份机制
  5. 更新版本：及时更新 MySQL 版本
  6. 网络安全：限制数据库访问 IP

十六、常用命令速查

-- 数据库操作
SHOW DATABASES;
USE database_name;
CREATE DATABASE db_name;
DROP DATABASE db_name;

-- 表操作
SHOW TABLES;
DESC table_name;
CREATE TABLE table_name (...);
DROP TABLE table_name;
ALTER TABLE table_name ...;

-- 数据操作
SELECT ... FROM table_name WHERE ...;
INSERT INTO table_name VALUES (...);
UPDATE table_name SET ... WHERE ...;
DELETE FROM table_name WHERE ...;

-- 索引操作
CREATE INDEX idx_name ON table_name(column);
DROP INDEX idx_name ON table_name;
SHOW INDEX FROM table_name;

-- 用户权限
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

十七、推荐资源

官方文档：

学习资源：

十八、总结

MySQL 核心要点：

关系型数据库 + SQL 语言 + 事务支持 + 高性能 = 可靠的数据存储

核心心法：

掌握 SQL 是数据库开发的基础。 合理设计表结构和索引是性能优化的关键。

📝 文档信息

  • 作者: 阿鑫
  • 更新日期: 2026.2
avatar
文章
阅读
粉丝