Node.js+Mysql

通过express框架使用MySql

 

一、安装MySql

 在windows环境下安装MySql，使用到的安装文件为 MySql Server 和 MySql Workbench。

二、MySql的基本使用

2.1 使用MySql Workbench管理数据库

        在Workbench中点击MySql Connections，默认User是root，输入自定义的password进入数据库。在所有操作前要先进入左边的schemas选项！！！进入界面后点击工具栏的新建数据库（create new schema），在主界面中输入数据库名称，选择apply进行创建。 

        在创建后双击左边状态栏中的数据库，可以看到生成的列表。在列表中找到table，右击选择create table创建数据表，在建表界面添加字段，设置字段名称、类型、备注、默认值以及特殊标识。下图介绍数据类型以及特殊标识。                                                                                         

       在状态栏中创建好的表上右击选择select rows -1000，可以在操作界面的表格中填写字段数据。

       本文通过workbench创建了一个users表，接下来将在该表中进行操作演示。

2.2 使用Sql语句的crud管理数据库

       通过Sql语句操作数据库，需要点击工具栏中的creative sql page创建用来编写sql语句的页面。在编写完后点击该页面顶部的闪电标签执行语句。执行的结果表格会显示在屏幕中。常见的方法又查询数据（select）、插入数据（insert into）、更新数据（update）、删除数据（delete）。在这些语句方法中还需要使用到where条件、and/or运算符、order by排序、count(*)函数。

where的运算符有 =、!=(<>)、>、<等，用于对sql语句进行条件限制

and/or类似于&&和||

order by用于对指定的列进行排序，默认情况是升序(asc)，也可以手动设置降序(desc)。在order by中还可以设置多重排序，需要在语句的后面对具体某个字段设置升序/降序，不同字段之间用逗号分隔开，如：

select * from users order by id asc, grade desc

count(*)函数用于返回查询结果的总数据条数，如：

select count(*) from table_name where status = 0

--在使用了count(*)获取数据条数时，表格中条数那一栏名称就是count(*)
--这里可以通过使用as为列设置别名。

select count(*) as total from table_name where status = 0

2.2.1  select

--通过 * 把某个表中的所有数据查询出来
select * from users

--通过列名称 获取具体某个或某几个字段的内容
select username, password from users

2.2.2  insert into

--向某个数据表中插入新的数据行，列的值通过values一一对应
--insert into table_name(列1,列2,...) values(值1,值2,...)

--示例: 向users表中插入username为 Policat_lzz password为1902的用户信息
--需要注意插入数据为字符串，需要使用引号''
insert into users(username, password) values('Policat_lzz', '1902')

2.2.3  update

--通过update选择要更新哪个表中的数据
--通过set设置新的值
--通过where设置更新的条件
--update 表名称 set 列名称 = 新值 where 条件（用于设置某个具体行）

--示例：把users表中id为7的用户密码更新为1905
update users set password = '1905' where id = 7

--示例：把users表中id为7的用户密码更新为1905，状态设置为1
update users set password = '1905', status = 1 where id = 7

2.2.4  delete

--delete from table_name where 具体条件
--通过from指出要删除的数据在哪个表内，通过where表明要删除该数据

--示例： 从users表内删除id为4的用户
delete from users where id = 4

--需要注意的是 id具有唯一性，在删除掉id=4的数据后如果要新增数据，那么新增数据的id=5，不为4

三、在express中使用MySql

3.1 操作数据库的步骤

        通过安装好的mysql模块建立与MySql的连接，在连接完成后执行SQL语句操作数据库：

        1.安装MySql数据库的第三方模块mysql

        2.通过mysql模块连接到数据库

        3.通过mysql模块执行SQL语句

//导入mysql模块
const mysql = require('mysql')

//建立与mysql之间的连接关系 需要提供以下四个配置项
const db = mysql.createPool({
    host:'127.0.0.1',    //本机IP地址
    user:'root',    //登录数据库的账号
    password:'123456',    //登录数据库的密码
    database:'my_blog' //选择的具体数据库名称
})

3.2 使用mysql模块操作MySql数据库

3.2.1 查询数据

//示例： 查询users表中的用户数据
const selectStr = 'select * from users'
db.query(selectStr, (err, results)=>{
    if(err) 
        return console.log(err.message)
    //成功查询结果
    //注意： 如果执行的是select查询语句，则执行的结果是一个数组
    console.log(results)
})

3.2.2 插入数据

//示例： 向users表中新增数据，其中username为iron-man,password为tony


//定义插入到users表中的数据对象
const user = {username: 'iron-man', password: 'tony'}

//定义需要执行的sql语句，其中英文的 ? 表示占位符
const insertStr = `insert into users (username, password) values (?, ?)`

db.query(insertStr, [user.username, user.password],(err, results)=>{
    if(err) return console.log(err.message)
    //数据插入成功
    //注意： 如果执行的是insert into，则results是一个对象
    if(results.affectedRows === 1){
        console.log('数据插入成功！')
    }
})

//这里如果表中的字段值过多的话，就需要手写很多的属性以及 ? 占位符
//因此可以采用简单的方法
const insertStrEasy =  `insert into users set ?`
db.query(insertStr, user, (err, results)=>{
    if(err) return console.log(err.message)
    if(results.affectedRows === 1){
        console.log('数据插入成功！')
    }
})

3.2.3 更新数据

//示例： 向users表中更新数据，其中username为iron-man,password为tony

//定义插入到users表中的数据对象
const newUser = {id: 3, username: 'iron-man', password: 'tony'}

//定义需要执行的sql语句，其中英文的 ? 表示占位符，通过where语句指明是在哪更新了
const updateStr = `update users set username = ?, password = ? where id = ?`

db.query(updateStr, [newUser.username, newUser.password, newUser.id],(err, results)=>{
    if(err) return console.log(err.message)
    if(results.affectedRows === 1){
        console.log('数据更新成功！')
    }
})

//同样，更新数据也有简便方法
const updateStr = `update users set ? where id = ?`
db.query(updateStr, [user, user.id],(err, results)=>{
    if(err) return console.log(err.message)
    if(results.affectedRows === 1){
        console.log('数据更新成功！')
    }
})

切记！！!

1.在query函数中，填充占位符的属性需要放在 [ ] 中，而不是 { } 

2.这里在插入数据的时候，如果每项数据有多个属性值，在属性值和占位符 ? 之间一定要加逗号！！不然就会出现报错：

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password='tony' where id=3' at line 1

3.2.4 删除数据

const delStr = 'delete from users where id=?'
db.query(delStr, 2 ,(err, results)=>{
    if(err) 
        return console.log(err.message)
    if(results.affectedRows === 1){
        console.log('数据删除成功！')
    }
})

使用delete语句，会把真正的数据从表中删除，为了保险起见，推荐使用标记删除的形式，来模拟删除的动作。标记删除类似于添加status这样的字段来标记是否删除，也就是说：在用户进行删除操作的时候，并没有真正执行delete语句，而是update语句，修改当前数据的status表示删除。

const delStr = 'update users set status=? where id=?'
db.query(delStr, [1, 6] ,(err, results)=>{
    if(err) 
        return console.log(err.message)
    if(results.affectedRows === 1){
        console.log('数据删除成功！')
    }
})