NodeJs 第二十一章 (建库建表)

可视化工具(链接建库建表)

navicat下载和使用,这里我就不带着大家下载了因为很简单。 MySQL & NaviCat 安装及配置教程（Windows）【安装】_navicat下载安装和配置-CSDN博客

这里使用的是navicat可视化工具进行操作的

新建链接

新建库

创建表

由于不是专业后端这里先用豆包Ai进行表生成

-- 设置编码格式为 UTF-8
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建角色表
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

-- 创建用户 - 角色关联表
CREATE TABLE IF NOT EXISTS user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

-- 创建菜单表
CREATE TABLE IF NOT EXISTS menus (
    id INT AUTO_INCREMENT PRIMARY KEY,
    menu_name VARCHAR(100) NOT NULL,
    menu_url VARCHAR(255),
    parent_id INT,
    menu_order INT,
    FOREIGN KEY (parent_id) REFERENCES menus(id) ON DELETE SET NULL
);

-- 创建权限表
CREATE TABLE IF NOT EXISTS permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

-- 创建角色 - 权限关联表
CREATE TABLE IF NOT EXISTS role_permissions (
    role_id INT,
    permission_id INT,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- 创建菜单 - 权限关联表
CREATE TABLE IF NOT EXISTS menu_permissions (
    menu_id INT,
    permission_id INT,
    PRIMARY KEY (menu_id, permission_id),
    FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- 插入默认用户数据
INSERT INTO users (username, password, email) VALUES
('admin', '$2y$10$y9a4p7f2g8h6j5k3l4m5n6o7p8q9r0t1u2v3w4x5y6z7a8b9c', 'admin@example.com'),
('user1', '$2y$10$y9a4p7f2g8h6j5k3l4m5n6o7p8q9r0t1u2v3w4x5y6z7a8b9c', 'user1@example.com');

-- 插入默认角色数据
INSERT INTO roles (role_name, description) VALUES
('Administrator', '拥有系统所有权限的管理员角色'),
('Regular User', '普通用户角色，只有部分权限');

-- 插入用户 - 角色关联数据
INSERT INTO user_roles (user_id, role_id) VALUES
((SELECT id FROM users WHERE username = 'admin'), (SELECT id FROM roles WHERE role_name = 'Administrator')),
((SELECT id FROM users WHERE username = 'user1'), (SELECT id FROM roles WHERE role_name = 'Regular User'));

-- 插入默认菜单数据
INSERT INTO menus (menu_name, menu_url, parent_id, menu_order) VALUES
('系统管理', NULL, NULL, 1),
('用户管理', '/user-management', 1, 1),
('角色管理', '/role-management', 1, 2),
('内容管理', NULL, NULL, 2),
('文章管理', '/article-management', 4, 1);

-- 插入默认权限数据
INSERT INTO permissions (permission_name, description) VALUES
('user_view', '查看用户信息的权限'),
('user_edit', '编辑用户信息的权限'),
('role_view', '查看角色信息的权限'),
('role_edit', '编辑角色信息的权限'),
('article_view', '查看文章信息的权限'),
('article_edit', '编辑文章信息的权限');

-- 插入角色 - 权限关联数据
-- 管理员角色拥有所有权限
INSERT INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE role_name = 'Administrator'), id FROM permissions;

-- 普通用户角色拥有部分权限
INSERT INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE role_name = 'Regular User'), id FROM permissions
WHERE permission_name IN ('user_view', 'article_view');

-- 插入菜单 - 权限关联数据
INSERT INTO menu_permissions (menu_id, permission_id) VALUES
((SELECT id FROM menus WHERE menu_name = '用户管理'), (SELECT id FROM permissions WHERE permission_name = 'user_view')),
((SELECT id FROM menus WHERE menu_name = '用户管理'), (SELECT id FROM permissions WHERE permission_name = 'user_edit')),
((SELECT id FROM menus WHERE menu_name = '角色管理'), (SELECT id FROM permissions WHERE permission_name = 'role_view')),
((SELECT id FROM menus WHERE menu_name = '角色管理'), (SELECT id FROM permissions WHERE permission_name = 'role_edit')),
((SELECT id FROM menus WHERE menu_name = '文章管理'), (SELECT id FROM permissions WHERE permission_name = 'article_view')),
((SELECT id FROM menus WHERE menu_name = '文章管理'), (SELECT id FROM permissions WHERE permission_name = 'article_edit'));

运行一下脚本 可以看到表和数据都有了下一步使用express链接数据库

express链接数据库

初始化package.json

安装依赖

npm i express mysql2 js-yaml

创建app.js 并初始化结构

const express = require('express');
const app = express();
const port = 3000;

app.get('/', (req, res) => {
  res.send('Hello, World!');
});

app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});

学习一下mysql2再进行下一步吧

基本使用步骤

1. 连接到数据库

以下是一个简单的示例，展示如何连接到 MySQL 数据库：

const mysql = require('mysql2');

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

// 获取一个连接
pool.getConnection((err, connection) => {
    if (err) {
        console.error('连接数据库时出错: ', err);
        return;
    }
    console.log('成功连接到数据库');
    // 释放连接
    connection.release();
});

// 也可以使用 Promise 风格的连接池
const promisePool = pool.promise();

2. 执行查询语句

连接成功后，就可以执行各种 SQL 查询语句了。以下是几种常见的查询示例：

2.1 查询数据

// 使用回调风格
pool.query('SELECT * FROM users', (err, results) => {
    if (err) {
        console.error('查询数据时出错: ', err);
        return;
    }
    console.log('查询结果: ', results);
});

// 使用 Promise 风格
promisePool.execute('SELECT * FROM users')
   .then(([rows, fields]) => {
        console.log('查询结果: ', rows);
    })
   .catch((err) => {
        console.error('查询数据时出错: ', err);
    });

2.2 插入数据

// 插入单条记录
const newUser = { username: 'john_doe', email: 'john@example.com' };
pool.query('INSERT INTO users SET ?', newUser, (err, result) => {
    if (err) {
        console.error('插入数据时出错: ', err);
        return;
    }
    console.log('插入成功，插入的 ID 是: ', result.insertId);
});

// 使用 Promise 风格插入多条记录
const users = [
    { username: 'jane_smith', email: 'jane@example.com' },
    { username: 'bob_johnson', email: 'bob@example.com' }
];
const sql = 'INSERT INTO users (username, email) VALUES ?';
const values = users.map(user => [user.username, user.email]);
promisePool.execute(sql, [values])
   .then(([result]) => {
        console.log('插入成功，插入的行数: ', result.affectedRows);
    })
   .catch((err) => {
        console.error('插入数据时出错: ', err);
    });

2.3 更新数据

// 更新记录
const updateData = { email: 'new_email@example.com' };
const condition = { username: 'john_doe' };
pool.query('UPDATE users SET ? WHERE ?', [updateData, condition], (err, result) => {
    if (err) {
        console.error('更新数据时出错: ', err);
        return;
    }
    console.log('更新成功，受影响的行数: ', result.affectedRows);
});

// 使用 Promise 风格更新数据
promisePool.execute('UPDATE users SET email = ? WHERE username = ?', ['new_email2@example.com', 'jane_smith'])
   .then(([result]) => {
        console.log('更新成功，受影响的行数: ', result.affectedRows);
    })
   .catch((err) => {
        console.error('更新数据时出错: ', err);
    });

2.4 删除数据

// 删除记录
const deleteCondition = { username: 'bob_johnson' };
pool.query('DELETE FROM users WHERE ?', deleteCondition, (err, result) => {
    if (err) {
        console.error('删除数据时出错: ', err);
        return;
    }
    console.log('删除成功，受影响的行数: ', result.affectedRows);
});

// 使用 Promise 风格删除数据
promisePool.execute('DELETE FROM users WHERE username = ?', ['jane_smith'])
   .then(([result]) => {
        console.log('删除成功，受影响的行数: ', result.affectedRows);
    })
   .catch((err) => {
        console.error('删除数据时出错: ', err);
    });

3. 处理事务

在需要保证数据一致性的场景下，可以使用事务来处理一系列的数据库操作。以下是一个使用事务的示例：

async function performTransaction() {
    const connection = await promisePool.getConnection();
    try {
        await connection.beginTransaction();
        // 执行一系列操作
        await connection.execute('INSERT INTO users (username, email) VALUES (?, ?)', ['transaction_user', 'transaction@example.com']);
        await connection.execute('UPDATE users SET email = ? WHERE username = ?', ['new_transaction_email@example.com', 'transaction_user']);
        await connection.commit();
        console.log('事务执行成功');
    } catch (err) {
        await connection.rollback();
        console.error('事务执行失败: ', err);
    } finally {
        connection.release();
    }
}

performTransaction();

js-yaml的使用

1. 解析 YAML 数据

js-yaml 提供了 load 方法用于将 YAML 字符串解析为 JavaScript 对象。

示例代码：

const yaml = require('js-yaml');
const fs = require('fs');

// 从文件中读取 YAML 数据
try {
    const yamlData = fs.readFileSync('example.yaml', 'utf8');
    // 解析 YAML 数据为 JavaScript 对象
    const obj = yaml.load(yamlData);
    console.log(obj);
} catch (e) {
    console.log(e);
}

假设 example.yaml 文件内容如下：

name: John Doe
age: 30
email: john.doe@example.com

运行上述代码后，控制台将输出解析后的 JavaScript 对象：

{ name: 'John Doe', age: 30, email: 'john.doe@example.com' }

2. 生成 YAML 数据

js-yaml 提供了 dump 方法用于将 JavaScript 对象转换为 YAML 字符串。

示例代码：

const yaml = require('js-yaml');
const fs = require('fs');

// 定义一个 JavaScript 对象
const data = {
    name: 'Jane Smith',
    age: 25,
    email: 'jane.smith@example.com'
};

// 将 JavaScript 对象转换为 YAML 字符串
const yamlStr = yaml.dump(data);
console.log(yamlStr);

// 将 YAML 字符串写入文件
fs.writeFileSync('output.yaml', yamlStr, 'utf8');

运行上述代码后，控制台将输出生成的 YAML 字符串，并且会在项目目录下生成一个 output.yaml 文件，其内容如下：

age: 25
email: jane.smith@example.com
name: Jane Smith

3. 处理复杂数据结构

js-yaml 可以处理更复杂的数据结构，如数组、嵌套对象等。

示例代码：

const yaml = require('js-yaml');

// 定义一个复杂的 JavaScript 对象
const complexData = {
    users: [
        {
            name: 'Alice',
            age: 28,
            hobbies: ['reading', 'swimming']
        },
        {
            name: 'Bob',
            age: 32,
            hobbies: ['running', 'painting']
        }
    ]
};

// 将复杂对象转换为 YAML 字符串
const complexYamlStr = yaml.dump(complexData);
console.log(complexYamlStr);

// 解析复杂的 YAML 字符串
const parsedComplexData = yaml.load(complexYamlStr);
console.log(parsedComplexData);

上述代码展示了如何处理包含数组和嵌套对象的复杂数据结构，js-yaml 能够正确地将其转换为 YAML 字符串并解析回来。

4. 安全加载

为了避免潜在的安全风险，如执行恶意代码，js-yaml 提供了 safeLoad 方法（在较新版本中推荐使用 load 并通过选项控制安全加载）。安全加载模式只会解析基本的数据类型，如字符串、数字、布尔值、数组和对象。

示例代码：

const yaml = require('js-yaml');
const yamlData = '!js/function >\n  function() { return "Hello, World!"; }';

try {
    // 安全加载
    const safeObj = yaml.load(yamlData, { schema: yaml.SAFE_SCHEMA });
    console.log(safeObj);
} catch (e) {
    console.log('安全加载时出错:', e);
}

在上述代码中，由于使用了安全加载模式，当遇到非基本数据类型（如 JavaScript 函数）时会抛出错误。

使用mysql2读取db.yaml配置链接数据库

const express = require('express');
const app = express();
const port = 3000;
const yaml = require('js-yaml')
const mysql = require("mysql2");
const fs = require('fs')

const dbConfig = yaml.load(fs.readFileSync('./db.yaml', 'utf8'))
// 创建连接
const connection = mysql.createConnection({
    host: dbConfig.host,  // 主机地址
    port: dbConfig.port,  // 端口  默认为 3306
    database: dbConfig.database,  // 数据库名称
    user: dbConfig.user,  // 用户名
    password: dbConfig.password  // 密码
});

// 连接到数据库
connection.connect((err) => {
    if (err) {
        console.error('连接到数据库时出错:', err);
        return;
    }
    console.log('成功连接到数据库');
});


app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});

    host: "localhost"
    port: "3306"
    database: "test"
    user: "root"
    password: "password"

结尾

本章学习了怎么使用可视化工具navicat创建数据库 创建数据表,使用mysql2 + js-yaml 链接数据库,下一章会讲解管理台系统的注册登录模块。