数据库基础操作详解

发表于 分类于
本文字数： 6.4k 阅读时长 ≈ 6 分钟

一、数据库基础概念

数据库是结构化存储和管理数据的系统。前端工程师需要掌握数据库操作以更好地理解全栈架构、调试接口问题以及编写简单的后端服务。

关系型 vs 非关系型

特性关系型（MySQL、PostgreSQL）非关系型（MongoDB、Redis）
数据模型表（Table）、行（Row）、列（Column）文档（Document）、集合（Collection）
模式固定 Schema，需预先定义动态 Schema，灵活
关联外键 + JOIN嵌入或引用
事务支持 ACID基础支持
适用场景强一致性、复杂查询灵活数据结构、高并发

二、MySQL 基础操作

连接与数据库

1
2
3
4
5
6
7
8
9
10
11
-- 连接
mysql -u root -p

-- 查看数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE myapp;

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建表
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESCRIBE users;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN age TINYINT;

-- 删除表
DROP TABLE users;

增删改查（CRUD）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 插入
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);
INSERT INTO users (name, email, age) VALUES 
  ('Bob', 'bob@example.com', 30),
  ('Charlie', 'charlie@example.com', 28);

-- 查询
SELECT * FROM users;                              -- 全部
SELECT name, email FROM users;                    -- 指定列
SELECT * FROM users WHERE age > 25;               -- 条件
SELECT * FROM users WHERE name LIKE 'A%';         -- 模糊匹配
SELECT * FROM users ORDER BY age DESC LIMIT 10;   -- 排序 + 分页

-- 聚合
SELECT COUNT(*) FROM users;                        -- 计数
SELECT AVG(age) FROM users;                        -- 平均值
SELECT age, COUNT(*) FROM users GROUP BY age;      -- 分组

-- 更新
UPDATE users SET age = 26 WHERE name = 'Alice';

-- 删除
DELETE FROM users WHERE id = 3;

联表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product VARCHAR(100),
  amount DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- INNER JOIN
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（包含没有订单的用户）
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 分组统计
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

索引

1
2
3
4
5
6
7
8
-- 创建索引（加速查询）
CREATE INDEX idx_email ON users(email);

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

-- 查看索引
SHOW INDEX FROM users;

三、MongoDB 基础操作

连接与数据库

1
2
3
4
5
6
7
8
# 连接
mongosh

# 查看数据库
show dbs;

# 切换/创建数据库（use 会自动创建）
use myapp;

集合操作

1
2
3
4
5
// 创建集合
db.createCollection('users');

// 查看集合
show collections;

增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// 插入
db.users.insertOne({ name: 'Alice', email: 'alice@example.com', age: 25 });
db.users.insertMany([
  { name: 'Bob', email: 'bob@example.com', age: 30 },
  { name: 'Charlie', email: 'charlie@example.com', age: 28 },
]);

// 查询
db.users.find();                                      // 全部
db.users.find({ age: { $gt: 25 } });                 // 条件 age > 25
db.users.find({}, { name: 1, email: 1 });            // 指定列
db.users.find({ name: /^A/ });                       // 正则匹配
db.users.find().sort({ age: -1 }).limit(10);          // 排序 + 分页

// 聚合
db.users.aggregate([
  { $group: { _id: '$age', count: { $sum: 1 } } },
]);

// 更新
db.users.updateOne(
  { name: 'Alice' },
  { $set: { age: 26 } }
);
db.users.updateMany(
  { age: { $lt: 20 } },
  { $set: { status: 'minor' } }
);

// 删除
db.users.deleteOne({ name: 'Charlie' });
db.users.deleteMany({ age: { $lt: 18 } });

索引

1
2
3
db.users.createIndex({ email: 1 });              // 单字段索引（1 升序，-1 降序）
db.users.createIndex({ name: 1, age: -1 });      // 复合索引
db.users.getIndexes();

关联查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 方式一：引用（类似外键）
// orders 集合中通过 userId 引用 users
db.orders.insertOne({ userId: ObjectId('...'), product: 'Widget', amount: 99.9 });

// 聚合查询（$lookup 类似 LEFT JOIN）
db.users.aggregate([
  {
    $lookup: {
      from: 'orders',
      localField: '_id',
      foreignField: 'userId',
      as: 'orders',
    },
  },
]);

四、Node.js 操作 MySQL

1
npm install mysql2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
const mysql = require('mysql2/promise');

async function main() {
  const conn = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'myapp',
  });

  // 查询
  const [rows] = await conn.execute('SELECT * FROM users WHERE age > ?', [25]);
  console.log(rows);

  // 插入
  const [result] = await conn.execute(
    'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
    ['David', 'david@example.com', 22]
  );
  console.log('Insert ID:', result.insertId);

  await conn.end();
}

使用连接池：

1
2
3
4
5
6
7
8
9
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
});

const [rows] = await pool.execute('SELECT * FROM users');

五、Node.js 操作 MongoDB

1
npm install mongoose
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
const mongoose = require('mongoose');

mongoose.connect('mongodb://localhost:27017/myapp');

// 定义 Schema
const userSchema = new mongoose.Schema({
  name: { type: String, required: true },
  email: { type: String, unique: true },
  age: { type: Number, default: 0 },
  createdAt: { type: Date, default: Date.now },
});

// 创建 Model
const User = mongoose.model('User', userSchema);

// CRUD
const user = await User.create({ name: 'Alice', email: 'alice@example.com', age: 25 });
const users = await User.find({ age: { $gt: 20 } }).sort({ age: -1 }).limit(10);
const updated = await User.findByIdAndUpdate(user._id, { age: 26 }, { new: true });
await User.findByIdAndDelete(user._id);

六、Redis 基础

Redis 是基于内存的键值数据库，常用于缓存和会话管理。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 连接
redis-cli

# 字符串
SET name "Alice"
GET name

# 设置过期时间（秒）
SET token "abc123" EX 3600
TTL token

# 列表
LPUSH queue task1
RPOP queue

# 哈希
HSET user:1 name "Alice" age 25
HGET user:1 name
HGETALL user:1
1
2
3
4
5
6
// Node.js 操作 Redis
const Redis = require('ioredis');
const redis = new Redis();

await redis.set('key', 'value', 'EX', 60);
const val = await redis.get('key');

七、常见问题

Q1: SQL 注入是什么

1
2
3
4
5
6
// ❌ 拼接字符串会导致 SQL 注入
const sql = `SELECT * FROM users WHERE name = '${userInput}'`;
// 输入 "'; DROP TABLE users; --" 会删除表

// ✅ 使用参数化查询
conn.execute('SELECT * FROM users WHERE name = ?', [userInput]);

Q2: N + 1 查询问题

1
2
3
4
5
6
7
// ❌ N+1：循环中查询数据库
const users = await User.find();
for (const user of users) {
  const orders = await Order.find({ userId: user.id });  // N 次查询
}

// ✅ 使用 JOIN 或 $lookup 一次查完

Q3: 如何选择 MySQL 和 MongoDB

  • 数据结构固定、需要复杂 JOIN、强一致性 → MySQL
  • 数据结构频繁变化、高并发写入、快速迭代 → MongoDB
  • 缓存、会话、计数器 → Redis

八、推荐学习路径

  1. 掌握 SQL CRUD 和基础查询（WHERE、ORDER BY、LIMIT）
  2. 学习 JOIN 和聚合
  3. 理解索引原理和 EXPLAIN 分析慢查询
  4. 对比学习 MongoDB 的 CRUD 和聚合管道
  5. 在 Node.js 中操作数据库，注意 SQL 注入防范