安裝 MySQL庫

npm i -S mysql

配置

新建db目錄，內(nèi)含index.js 和 config.js

// config.js
module.exports={
    host:'localhost', // 主機名
    user:'root', // 連接數(shù)據(jù)庫的用戶名
    password:'root', // 密碼
    database:'book' // 表
}

基本使用

連接 - 增刪改查 - 釋放連接

const mysql=require('mysql')
const {host,user,password,database}=require('./config')

function connect(){
    return mysql.createConnection({
        host,
        user,
        password,
        database,
        multipleStatements:true
        //multipleStatements：允許每條 mysql 語句有多條查詢.
        //使用它時要非常注意，因為它很容易引起 sql 注入（默認：false）
    })
}

function querySql(sql) {
    const conn = connect()
    return new Promise((resolve, reject) => {
        try {
            conn.query(sql, (err, results) => {
                if (err) {
                  
                } else {
                    resolve(results)
                }
            })
        } catch (e) {
            reject(e)
        } finally {
            conn.end() //調(diào)用conn.end()釋放連接
        }
    })
}

module.exports={querySql}

• 增
  字段最好加上反引號，避免和某些固定的關(guān)鍵字沖突，比如form等

INSERT INTO `表名` (`key1`,`key2`) VALUES ('value1','value2')

• 刪

delete from book where fileName='xxxxx'

• 改

UPDATE book SET author='xxx' WHERE fileName='xxxxx'
UPDATE book SET author='xxx',language='en' WHERE fileName='xxxxx'

• 查

select * from contents where fileName='xxxxxxxx' order by `order`

//詳細查詢 key = value，模糊查詢 key like value ，value加上通配符 %
select * from book where `category`='99' and `author` like '%Nylund%' limit 20 offset 0

//返回表student的第10、11、12、13行
select * from student limit 9,4
select * from student limit 4 offset 9

//排序 order by
select * from book order by `id` asc limit 20 offset 0
select * from book order by `id` desc limit 20 offset 0