1.13 操作MySQL 🎉

学习本章节之前，先看这里！！！

如果你还没有学习数据库，请点击MySql进行学习，之后再看本章节

安装mysql依赖

npm install mysql --save

node.js连接MySQL

var mysql = require('mysql');
//创建跟数据库的连接
var connection = mysql.createConnection({
    host: 'wwlj-yunfuwu.mysql.huhehaote.rds.aliyuncs.com',
    user: 'jiamei0429',
    password: 'Aa1472583690',
    database: 'jiameiwlkjgzs'
});
//启动连接
connection.connect();
//执行查询
connection.query('select * from learn', function (error, results) {
    if (error) { throw error; }
    console.log(results);
});

查询 💎

var mysql = require('mysql');
//创建跟数据库的连接
var connection = mysql.createConnection({
    host: 'wwlj-yunfuwu.mysql.huhehaote.rds.aliyuncs.com',
    user: 'jiamei0429',
    password: 'Aa1472583690',
    database: 'jiameiwlkjgzs'
});
//启动连接
connection.connect();
//执行查询
connection.query('select * from lea where id=8', function (error, results) {
    if (error) { throw error; }
    console.log(results);
});

增加 💎

var mysql = require('mysql');
//创建跟数据库的连接
var connection = mysql.createConnection({
    host: 'wwlj-yunfuwu.mysql.huhehaote.rds.aliyuncs.com',
    user: 'jiamei0429',
    password: 'Aa1472583690',
    database: 'jiameiwlkjgzs'
});
//启动连接
connection.connect();
//执行增加
connection.query('insert into lea (title,main,path) values (?,?,?)', ['ha', 'haha', 'hahaha'], function (error, results) {
    if (error) { throw error; }
    if (results.affectedRows) {
        console.log('插入成功')
    }
});

修改 💎

var mysql = require('mysql');
//创建跟数据库的连接
var connection = mysql.createConnection({
    host: 'wwlj-yunfuwu.mysql.huhehaote.rds.aliyuncs.com',
    user: 'jiamei0429',
    password: 'Aa1472583690',
    database: 'jiameiwlkjgzs'
});
//启动连接
connection.connect();
//执行修改
connection.query('update lea set title=? where path=?', ['111', 'hahaha'], function (error, results) {
    if (error) { throw error; }
    if (results.affectedRows) {
        console.log('修改成功')
    }
});

删除 💎

var mysql = require('mysql');
//创建跟数据库的连接
var connection = mysql.createConnection({
    host: 'wwlj-yunfuwu.mysql.huhehaote.rds.aliyuncs.com',
    user: 'jiamei0429',
    password: 'Aa1472583690',
    database: 'jiameiwlkjgzs'
});
//启动连接
connection.connect();
//执行删除
connection.query('delete from lea where path=?', ['hahaha'], function (error, results) {
    if (error) { throw error; }
    if (results.affectedRows) {
        console.log('删除成功')
    }
});

整理

var mysql = require('mysql');
const sqlConfig = {
    host: 'wwlj-yunfuwu.mysql.huhehaote.rds.aliyuncs.com',
    user: 'jiamei0429',
    password: 'Aa1472583690',
    database: 'jiameiwlkjgzs'
}
let connection = mysql.createConnection(sqlConfig)
const sqlFn = function (sql, arr, callback) {
    connection.query(sql, arr, function (err, result) {   
        if (err) {
            console.log(err)   
            return          
        }
        callback(result)
    })
}
module.exports = sqlFn

const sqlFn = require('./mysql')
var sql = "insert into test(id,name,value) values (null,?,?)"
sqlFn(sql, ['name', 'value'], (results) => {
    if (results.affectedRows) {
        res.writeHead(200, { "Content-Type": 'application/json;charset=utf-8' })
        res.end(JSON.stringify({ code: 0, message: '操作成功' }))
    }
})