likes
comments
collection
share

跟着小满zs学 nodejs —— 总结篇（四）

作者站长头像
站长
· 阅读数 5

本篇主要介绍数据库相关，以 mysql 举例

mysql安装

创建数据库、表

-- 创建数据库
create database if not exists `库名`
default character set = 'utf8mb4';

-- 创建表
CREATE TABLE `user` (
   id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(100) COMMENT '名字',
   age int COMMENT '年龄',
   address varchar(255) COMMENT '地址',
   create_time timestamp DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间'
) COMMENT '用户表'

-- 修改表名
ALTER TABLE `user` RENAME `user2`;

-- 增加列
ALTER TABLE `user` Add COLUMN `hobby` VARCHAR(200) ;

-- 删除列
ALTER TABLE `user` DROP `hobby`;

--编辑列
ALTER TABLE `user` MODIFY `age` VARCHAR(255) NULL COMMENT '年龄2';
  • NOT NULL ：不为空
  • AUTO_INCREMENT ：自动填充
  • PRIMARY KEY ： 主键，唯一标识
  • COMMENT ：名字提示

查询操作

-- 查询单个列
SELECT `name` FROM `user`;
-- 查询多个列
SELECT `name`,`age` FROM `user`;
-- 查询所有列
SELECT * FROM `user`;
-- 给查询的列添加别名
SELECT `name` as `user_name`,`id` as `user_id` FROM `user`;
-- 排序查询
-- ORDER BY [字段名称] `desc`降序(从大到小) `asc` 升序(从小到大)
SELECT *  FROM `user` ORDER BY id DESC;
-- 限制查询结果 
-- limit [开始行] [限制条数]
-- 使用 limit 的时候是从0开始的，跟数组一样
SELECT * FROM `user` LIMIT 1,3;
-- 条件查询  where
SELECT *  FROM `user` WHERE name = "大满";
-- 多个条件联合查询
SELECT * FROM `user` WHERE name = '小满' AND age <= 20;
SELECT * FROM `user` WHERE name = '小满' OR age <= 22;
-- 模糊查询
-- LIKE 操作符用于模糊匹配字符串
-- 百分号（%）是用作通配符，表示任意字符（包括零个字符或多个字符）的占位符
SELECT * FROM `user` WHERE name LIKE '%满%';

增删改操作

-- 新增
INSERT INTO user(`name`,`hobby`,`age`) VALUES('xiaoman','basketball',18)
INSERT INTO user(`name`,`hobby`,`age`) VALUES(NULL,NULL,NULL)
INSERT INTO user(`name`,`hobby`,`age`) VALUES(NULL,NULL,NULL),('xiaoman','basketball',18)
-- 删除
DELETE FROM `user` WHERE id = 11; 
-- 批量删除
DELETE FROM `user` WHERE id IN (8,9,10);
-- 更新
UPDATE `user` SET name='麒麟哥',age=30,hobby='篮球' WHERE id = 12;

表达式和函数

子查询和连表

  • 子查询（Subquery），也被称为嵌套查询（Nested Query），是指在一个查询语句中嵌套使用另一个完整的查询语句
SELECT * FROM `photo` WHERE `user_id` = (SELECT id FROM `user` WHERE name = '小满')
  • 连表分为内连接、外连接、交叉连接，外连接又分为左连接、右连接
-- 内连接
SELECT * FROM `user`, `photo` WHERE `user`.`id` = `photo`.`user_id`
-- 外连接 左连接
-- 语法规则 LEFT JOIN [连接的表] ON [连接的条件]
SELECT * FROM `user` LEFT JOIN `table` ON `user`.`id` = `table`.`user_id`
-- 外连接 右连接
SELECT * FROM `user` RIGHT JOIN `table` ON `user`.`id` = `table`.`user_id`

服务器与数据库结合

  • 安装依赖
npm install mysql2 express js-yaml
  • 连接 mysql 数据库配置 db.config.yaml
db:
   host: localhost #主机
   port: 3306 #端口
   user: root #账号
   password: '123456' #密码 一定要字符串
   database: test # 库
  • nodejs 文件
import mysql2 from 'mysql2/promise'
import fs from 'node:fs'
import jsyaml from 'js-yaml'
import express from 'express'
const yaml = fs.readFileSync('./db.config.yaml', 'utf8')
const config = jsyaml.load(yaml)
const sql = await mysql2.createConnection({
   ...config.db
})
const app = express()
app.use(express.json())
//查询接口 全部
app.get('/',async (req,res)=>{
   const [data] = await sql.query('select * from user')
   res.send(data)
})
//单个查询 params
app.get('/user/:id',async (req,res)=>{
    const [row] = await sql.query(`select * from user where id = ?`,[req.params.id])
    res.send(row)
})

//新增接口
app.post('/create',async (req,res)=>{
    const {name,age,hobby} = req.body
    await sql.query(`insert into user(name,age,hobby) values(?,?,?)`,[name,age,hobby])
    res.send({ok:1})
})

//编辑
app.post('/update',async (req,res)=>{
    const {name,age,hobby,id} = req.body
    await sql.query(`update user set name = ?,age = ?,hobby = ? where id = ?`,[name,age,hobby,id])
    res.send({ok:1})
})
//删除
app.post('/delete',async (req,res)=>{
    await sql.query(`delete from user where id = ?`,[req.body.id])
    res.send({ok:1})
})
const port = 3000

app.listen(port, () => {
   console.log(`Example app listening on port ${port}`)
})

ORM框架「 knex 」

ORM框架（Object-Relational Mapping）是对象-关系映射框架，它是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。可以把关系数据库映射到对象上，使得我们在操作数据库时，就不需要使用SQL语句，而是像操作对象一样来操作数据库的数据

Knex 是一个基于JavaScript的查询生成器，它允许你使用JavaScript代码来生成和执行SQL语句

  • 安装
npm install knex --save
  • 连接数据库
import knex from 'knex'
const db = knex({
    client: "mysql2",
    connection: config.db
})
  • 定义表的结构
db.schema.createTable('list', (table) => {
    table.increments('id') //id自增
    table.integer('age') //age 整数
    table.string('name') //name 字符串
    table.string('hobby') //hobby 字符串
    table.timestamps(true,true) //创建时间和更新时间
}).then(() => {
    console.log('创建成功')
})
  • 实现增删改查
import mysql2 from 'mysql2/promise'
import fs from 'node:fs'
import jsyaml from 'js-yaml'
import express from 'express'
import knex from 'knex'
const yaml = fs.readFileSync('./db.config.yaml', 'utf8')
const config = jsyaml.load(yaml)
// const sql = await mysql2.createConnection({
//    ...config.db
// })
const db = knex({
    client: "mysql2",
    connection: config.db
})

const app = express()
app.use(express.json())
//查询接口 全部
app.get('/', async (req, res) => {
    const data = await db('list').select().orderBy('id', 'desc')
    const total = await db('list').count('* as total')
    res.json({
        code: 200,
        data,
        total: total[0].total,
    })
})
//单个查询 params
app.get('/user/:id', async (req, res) => {
    const row = await db('list').select().where({ id: req.params.id })
    res.json({
        code: 200,
        data: row
    })
})

//新增接口
app.post('/create', async (req, res) => {
    const { name, age, hobby } = req.body
    const detail = await db('list').insert({ name, age, hobby })
    res.send({
        code: 200,
        data: detail
    })
})

//编辑
app.post('/update', async (req, res) => {
    const { name, age, hobby, id } = req.body
    const info = await db('list').update({ name, age, hobby }).where({ id })
    res.json({
        code: 200,
        data: info
    })
})
//删除
app.post('/delete', async (req, res) => {
    const info = await db('list').delete().where({ id: req.body.id })
    res.json({
        code: 200,
        data: info
    })
})
const port = 3000

app.listen(port, () => {
    console.log(`Example app listening on port ${port}`)
})
  • 事务：确保一组数据库操作的原子性，即要么全部成功提交，要么全部回滚
//伪代码
db.transaction(async (trx) => {
    try {
        await trx('list').update({money: -100}).where({ id: 1 }) //A
        await trx('list').update({money: +100}).where({ id: 2 }) //B
        await trx.commit() //提交事务
    }
    catch (err) {
        await trx.rollback() //回滚事务
    }
   
})

ORM框架「 prisma 」