一、使用NodeDB

1. mysql模块提供了常用数据库系统的原生代码接口,包括与MySQL的接口

2. 安装

npm install mysql --save

3. 插入数据到MySQL中

const mysql = require('mysql')

const db = mysql.createConnection({
    host: '127.0.0.1',
    user: 'test',
    password: '*3!0CcEf',
    database: 'upandrunning',
})

db.connect()

db.query('INSERT INTO users (user_login) VALUES ("angus")', (error, results, fields)=>{
    if(error){
        throw error
    }else{
        console.log(results)
    }
})

db.end()    

4. 从MySQL查询数据

const mysql = require('mysql')

const db = mysql.createConnection({
    host: '127.0.0.1',
    user: 'test',
    password: '*3!0CcEf',
    database: 'upandrunning',
})

db.connect()

db.query('SELECT id, user_login FROM users', (error, results, fields)=>{
    if(error){
        throw error
    }else{
        console.log(results)
    }
})

db.end()    

5. 在MySQL中更新数据

const mysql = require('mysql')

const db = mysql.createConnection({
    host: '127.0.0.1',
    user: 'test',
    password: '*3!0CcEf',
    database: 'upandrunning',
})

db.connect()

db.query('UPDATE users SET user_nicename="nice name" WHERE user_login="angus"', (error, results, fields)=>{
    if(error){
        throw error
    }else{
        console.log(results)
    }
})

db.end()    

6. 从MySQL中删除数据

const mysql = require('mysql')

const db = mysql.createConnection({
    host: '127.0.0.1',
    user: 'test',
    password: '*3!0CcEf',
    database: 'upandrunning',
})

db.connect()

db.query('DELETE FROM users WHERE user_login="angus"', (error, results, fields)=>{
    if(error){
        throw error
    }else{
        console.log(results)
    }
})

db.end()    

二、使用Sequelize

1. Sequelize是一个对象关系映射(ORM),可以使用Sequelize来定义数据库与程序间共享的对象,这样就不需要为每个操作写查询语句,而是直接通过操作这些对象来写入或读取数据库

2. 安装

npm install sequelize    

3. 使用Sequelize

const Sequelize = require('sequelize')

const db = new Sequelize('upandrunning', 'test', '*3!0CcEf', {
    host: 'localhost',
    dialect: 'mysql',
})

//  定义author模型
//  包含name字段和biograph字段
//  Sequelize自动添加了一个自增的主键字段、createdAt字段和updatedAt字段
const Author = db.define('author', {
    name: Sequelize.STRING,
    biography: Sequelize.TEXT,
})

//  同步真实数据库
Author
.sync()
.then(()=>{
    console.log('Author table was created')
    db.close()
})
.catch((error)=>{
    console.log(error)
})    
const Sequelize = require('sequelize')

const db = new Sequelize('upandrunning', 'test', '*3!0CcEf', {
    host: 'localhost',
    dialect: 'mysql',
})

// 定义author模型
const Author = db.define('author', {
    name: Sequelize.STRING,
    biography: Sequelize.TEXT,
})

// 定义book模型
const Book = db.define('book', {
    name: Sequelize.STRING,
})

//  定义author和book之间一对多的关系
Author.hasMany(Book)

// 将模型与真实的数据库进行同步
db
.sync({
    force: true,
})
.then(()=>{
    // 创建并保存一个Book实例
    return Book
    .create({
        name: 'Through the Storm',
    })
})
.then((book)=>{
    // 创建并保存一个Author实例
    return Author
    .create({
        name: 'Lynne Spears',
        biography: 'Author and mother of Britney',
    })
    .then((record)=>{
        //  建立author和book之间的关系
        record.setBooks([book])
    })
})
.catch((error)=>{
    console.log(error)
})