[大数据]Node.js学习十五（Node操作MySQL数据库）

一、在Express项目中操作数据库的步骤

①安装操作MySQL数据库的第三方模块
②通过mysql模块连接到MySQL数据库
③通过mysql模块执行SQL语句操作数据库

1、安装mysql模块

??????mysql模块是托管与npm上的第三方模块，它提供了在Node.js项目中连接和操作MySQL数据库的能力。
??????想要在项目中使用它，需要先运行如下命令，将mysql安装为项目的依赖包：

npm install mysql

2、配置mysql模块

??????在使用mysql模块操作MySQL数据库之前，必须先对mysql模块进行必要的配置，主要的配置方式如下：

（1）mysql.createConnection()

??????建立连接的方式，主要格式如下：

//导入mysql模块
const mysql = require('mysql')
const connection = mysql.createConnection({})
//建立连接
connection.connect();

??????在建立连接时，可以设置以下选项：
host：连接到的数据库的主机名。(默认值:localhost)
port：要连接的端口号。(默认:3306)
user：需要认证的MySQL用户。
password：MySQL用户的密码。
database：用于此连接的数据库名称(可选)。

（2）mysql.createPool()

??????这个模块不是一个一个地创建和管理连接，而是使用mysql.createPool(config)提供内置的连接池，主要格式如下：

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'qianduan',//指定要操作哪个数据库
    connectionLimit:10//一次创建的最大连接数(默认值:10)
})

3、测试mysql模块能否正常工作

??????调用db.query()函数，指定要执行的SQL语句，通过回调函数拿到执行的结果：

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd123',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//检测mysql模块能否正常工作
db.query('select 1',(err,results)=>{
    if(err){
        return console.log(err.message)
    }
    //只要能打印出[ RowDataPacket {'1':1} ]的结果，就证明数据库连接正常
    console.log(results)
})

??????结果如下：

4、终止连接

??????有两种方法可以结束连接。

（1）调用end()方法终止连接

connection.end(function(err) {
  	// The connection is terminated now
});

??????如果在发送COM_QUIT包之前发生了一个致命的错误，一个err参数将被提供给回调函数，但是无论如何连接将被终止。

（2）调用destroy()方法

connection.destroy();

??????与end()不同，destroy()方法不接受回调参数。

（3）关闭连接池中的所有连接

??????当完成使用池，必须结束所有的连接，否则Node.js事件循环将保持活动，直到连接被MySQL服务器关闭。如果要结束池中的所有连接，可以使用池中的end方法：

var mysql = require('mysql')
var pool = mysql.createPool(...)
...
pool.end(function (err) {
  // all connections in the pool have ended
});

??????end方法接受一个可选的回调函数，可以使用它来知道所有连接何时结束。

5、使用mysql模块操作MySQL数据库

??????可以在对象（如connection、pool）上调用query()方法，格式如下：

.query('SQL语句', function (error, results, fields) {
});

error：发生的错误；
results：结果；
fields：字段，包含关于返回结果字段的信息(可选)。

//在使用占位符值时出现
.query(sqlString, values, function (error, results, fields){})

sqlString：SQL语句，包含占位符？
values：如果只有一个替换字符(?)，且值不是null、undefined或数组，它可以直接作为.query的第二个参数传递；
callback：回调函数
注意：使用占位符可以过滤传入的值，防止SQL注入。
??????如我们info数据库下有一个表course，数据如下：

??????还有一个表student，数据如下：

（1）查询数据

①mysql.createConnection()

??????查询student表中的数据。注意：我们查询得到的每条数据都是一个对象，每个对象最后组成一个数组。

//导入mysql模块
const mysql = require('mysql')
const connection = mysql.createConnection({
    host:'127.0.0.1',//数据库的IP地址
    port:3306,//端口号
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//建立连接
connection.connect();
//查询数据
connection.query('SELECT * FROM student',(err,result)=>{
    //err：可能产生的错误
    //result：SQL语句的结果
    if(err) throw err;
    console.log(result)
})

②mysql.createPool()

??????查询course表中的数据。

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//查询course表中的所有数据
const sqlStr = 'select * from course'
db.query(sqlStr,(err,results)=>{
    //查询数据失败
    if(err){
        return console.log(err.message)
    }
    //查询数据成功
    console.log(results)
})

??????结果如下：

注意：如果执行的是select查询语句，则执行的结果是数组。

（2）插入数据

①mysql.createConnection()

??????向student表中新增数据，s_name为小小，s_BIRTHDAY为2020-09-09，s_gender为女。

//导入mysql模块
const mysql = require('mysql')
const connection = mysql.createConnection({
    host:'127.0.0.1',//数据库的IP地址
    port:3306,//端口号
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//建立连接
connection.connect();
//插入数据
connection.query(`INSERT INTO student VALUES('09','小小','2020-09-09','女')`,(err,result)=>{
    //err：可能产生的错误
    //result：SQL语句的结果
    if(err) throw err;
    console.log(result)
})

②mysql.createPool()

??????向course表中新增数据，其中c_id为06，c_name为体育：

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})

//要插入到course表中的数据对象
const user = {c_id:'06',c_name:'体育'}
//待执行的SQL语句，其中英文的？代表占位符
const sqlStr = 'INSERT INTO course (c_id,c_name) VALUES (?,?)'
//使用数组的形式，依次为？占位符指定具体的值
db.query(sqlStr,[user.c_id,user.c_name],(err,results)=>{
    if(err){
        return console.log(err.message)
    }
    if(results.affectedRows === 1){
        console.log('插入数据成功')
    }
})

??????结果如下：


注意：如果执行的是insert into插入语句，则results是一个对象，可以通过affectedRows属性，如果它等于1，则证明插入数据成功。

（3）插入数据的便捷方式

??????向表中新增数据时，如果数据对象的每个属性和数据表的字段一一对应，则可以通过如下方式快速插入数据：

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//要插入到course表中的数据对象
const user = {c_id:'07',c_name:'音乐'}
//待执行的SQL语句，其中英文的？表示占位符
const sqlStr = 'INSERT INTO course SET ?'
//直接将数据对象当作占位符的值
db.query(sqlStr,user,(err,results)=>{
    if(err){
        return console.log(err.message)
    }
    if(results.affectedRows === 1){
        console.log('插入数据成功')
    }
})

??????结果如下：

（4）更新数据

??????可以通过如下方式，更新表中的数据：

①mysql.createConnection()

??????更新student表中的数据。

//导入mysql模块
const mysql = require('mysql')
const connection = mysql.createConnection({
    host:'127.0.0.1',//数据库的IP地址
    port:3306,//端口号
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//建立连接
connection.connect();
//更新数据
connection.query('UPDATE student SET s_name="lucky" WHERE s_id=07',(err,result)=>{
    //err：可能产生的错误
    //result：SQL语句的结果
    if(err) throw err;
    console.log(result)
})

②mysql.createPool()

??????更新course表中的数据。

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//要更新的数据对象
const user = {c_id:'07',c_name:'形势与政策'}
//要执行的SQL语句
const sqlStr = 'UPDATE course SET c_name=? WHERE c_id=?'
//调用db.query()执行SQL语句的同时，使用数组依次为占位符指定具体的值
db.query(sqlStr,[user.c_name,user.c_id],(err,results)=>{
    if(err){
        return console.log(err.message)
    }
    if(results.affectedRows === 1){
        console.log('更新数据成功')
    }
})

注意：如果执行的是update语句，则执行的结果results也是一个对象，可以通过affectedRows属性，如果它等于1，则证明更新数据成功。

（5）更新数据的便捷方式

??????更新表数据时，如果数据对象的每个属性和数据表的字段一一对应，则可以通过如下方式快速更新表数据：

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//要更新的数据对象
const user = {c_id:'06',c_name:'马克思主义原理'}
//要执行的SQL语句
const sqlStr = 'UPDATE course SET ? WHERE c_id=?'
//调用db.query()执行SQL语句的同时，使用数组依次为占位符指定具体的值
db.query(sqlStr,[user,user.c_id],(err,results)=>{
    if(err){
        return console.log(err.message)
    }
    if(results.affectedRows === 1){
        console.log('更新数据成功')
    }
})

（6）删除数据

??????在删除数据时，推荐根据id这样的唯一标识，来删除对应的数据。示例如下：

①mysql.createConnection()

??????删除student表中的数据。

//导入mysql模块
const mysql = require('mysql')
const connection = mysql.createConnection({
    host:'127.0.0.1',//数据库的IP地址
    port:3306,//端口号
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//建立连接
connection.connect();
connection.query('DELETE FROM student WHERE s_id=08',(err,result)=>{
    //err：可能产生的错误
    //result：SQL语句的结果
    if(err) throw err;
    console.log(result)
})

②mysql.createPool()

??????删除course表中的数据。

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//要执行的SQL语句
const sqlStr = 'DELETE FROM course WHERE c_id=?'
//调用db.query()执行SQL语句的同时，为占位符指定具体的值
db.query(sqlStr,'07',(err,results)=>{
    if(err){
        return console.log(err.message)
    }
    if(results.affectedRows === 1){
        console.log('删除数据成功')
    }
})

注意：执行delete语句之后，结果也是一个对象，也会包含affectedRows属性，如果SQL语句中有多个占位符，则必须使用数组为每个占位符指定具体的值；如果SQL语句中只有一个占位符，则可以省略数组。

（7）标记删除

??????使用 DELETE 语句，会把真正的把数据从表中删除掉。为了保险起见，推荐使用标记删除的形式，来模拟删除的动作。
??????所谓的标记删除，就是在表中设置类似于status这样的状态字段，来标记当前这条数据是否被删除。
??????当用户执行了删除的动作时，我们并没有执行 DELETE 语句把数据删除掉，而是执行了 UPDATE 语句，将这条数据对应的status字段标记为删除即可。
?????&nbsp举例如下，我们给course表增加一个status字段，status为0时，代表它没有被删除。

//导入mysql模块
const mysql = require('mysql')
//建立与MySQL数据库的连接
const db = mysql.createPool({
    host:'127.0.0.1',//数据库的IP地址
    user:'root',//登录数据库的账号
    password:'iampwd',//登录数据库的密码
    database:'info'//指定要操作哪个数据库
})
//要执行的SQL语句
const sqlStr = 'UPDATE course SET status=? WHERE c_id=?'
//调用db.query()执行SQL语句的同时，为占位符指定具体的值
db.query(sqlStr,[1,'06'],(err,results)=>{
    if(err){
        return console.log(err.message)
    }
    if(results.affectedRows === 1){
        console.log('标记删除成功')
    }
})

总结：执行删除、更新、插入返回对象，如果返回对象的属性中的affectedRows大于0，说明执行成功；执行查询返回数组。