# mysql的增删改查

> 基于前面模块化的思想，将增删改查封装成单独的函数，并且每个数据类型有对应的sql函数

## 1. 查询所有

对应sql语句的函数

```javascript
exports.findAll = function (callback) {
    let sql = 'SELECT * FROM websites ';
    mysql_conn.query({
        sql,
        timeout,
    }, function (err, result) {
        if (err) {
            return callback(err)
        }
        return callback(null, result)
    })
}
```

在路由中使用对应的数据模型调用对应的函数

```javascript
const test_mysql = require(path.join(__dirname, "../database/include/test_mysql.js"))

//查询所有item的接口
router.get("/test", function (req, rep, next) {
    test_mysql.findAll(function (err, result) {
        if (err) {
            return next(err)
        }
        rep.status(200).send(result)
    })
})
```

## 2.添加单个条目

sql语句在数据表设计好的情况下

* ? 表示占位符
* query 函数第二个参数是一个要插入数据的数组
* 使用回调函数处理查询数据库的事件

```javascript
const timeout = 4000 //设置数据库查询时间上限

//插入单个数据
exports.insertOne = function (insertData, callback) {
    let sql = "INSERT INTO websites(ID,name,url,alexa,country) VALUES(?,?,?,?,?)"
    mysql_conn.query({
        sql,
        timeout,
    }, [insertData.id, insertData.name, insertData.url, insertData.alexa, insertData.country], function (err, result) {
        if (err) {
            return callback(err)
        }
        return callback(null, result)
    })
}
```

## 3. 更新条目

sql语句

```javascript
//更新单个数据
exports.update = function (UpdateData, callback) {
    let sql = 'UPDATE websites SET name=?,url=?,alexa=?,country=? WHERE ID=?'
    mysql_conn.query({
        sql,
        timeout,
    }, [UpdateData.name, UpdateData.url, UpdateData.alexa, UpdateData.country, UpdateData.id], function (err, result) {
        if (err) {
            return callback(err)
        }
        return callback(null, result)
    })
}
```

路由中需要多加几个判断

* 判断post请求数据是否为空，感觉这可以交给前端完成
* 判断更新的id是否存在，通过sql语句的返回结果可以验证

```javascript
//更新接口
router.post("/test/edit", function (req, rep, next) {
    if (Object.keys(req.body).length == 0) {
        return next({
            code: err.code,
            message: "请求参数为空",
        })
    }
    test_mysql.update(req.body, function (err, result) {
        if (err) {
            return next({
                code: err.code,
                errorMessage: err.sqlMessage,
            })
        }
         //检查id是否存在；
        if(result.affectedRows=='0'){
            return(next({
                code:2,
                errorMessage:"Do'nt exist such items in databases!"
            }))
        }
        //即使提交没有更新的数据也不会报错；这个交给前端验证
        rep.send("ok")
    })
})
```

## 4. 删除一个条目

sql语句

```javascript
//删除数据
exports.delete=function(deleteID,callback){
    let sql='DELETE FROM websites where ID=?';
    mysql_conn.query({
        sql,
        timeout,
    },[deleteID],function(err,result){
        if(err){
            return callback(err)
        }
        return callback(null,result)
    })
}
```

路由方面同样需要判断是否，成功的删除，或者删除的条目根部就不存在

```javascript
//删除单个数据的接口
router.get("/test/delete", function (req, rep, next) {
    test_mysql.delete(req.query.id,function(err,result){
        if(err){
            return(next({
                code: err.code,
                errorMessage: err.sqlMessage,
            }))
        }else if(result.affectedRows == '0'){
           return(next({
               code:2,
               errorMessage:"Do'nt exist such items in databases!"
           }))
       }
       rep.send("删除成功！")
    })
})
```

## 参考

1.在mysql回调时出现的错误

<http://www.dongcoder.com/detail-1091197.html>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://zpliu.gitbook.io/booknote/node/mysql/02-zeng-shan-gai-cha.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.