Node.js 基础（4）- 数据库 与 MySQL 基础(下)

三、使用 SQL 管理数据库

1. 什么是 SQL

SQL(英文全称: Structured Query Language) 是结构化查询语言，专门用来访问和处理数据库的编程语言。能够让我们以编程的形式，操作数据库里面的数据。 注意:

• SQL 是一门数据库编程语言
• 使用 SQL 语言编写出来的代码，叫做 SQL 语句
• SQL 语言只能在关系型数据库中使用 (例 MySQL、Oracle、 SQL Server) ，非关系型数据库(例如 Mongodb)不支持 SQL 语言

2. SQL 能做什么

• 从数据库中查询数据
• 向数据库中插入新的数据
• 更新数据库中的数据
• 从数据库删除数据
• 可以创建新数据库
• 可在数据库中创建新表
• 可在数据库中创建存储过程、视图
• Etc...

3. SELECT 语句

1.语法 SELECT 语句用于从表中查询数据。执行的结果被存储在一个结果表中(称为结果集)。语法格式如下:

-- 这是注释
-- 从 FROM 指定的[表中]，查询出[所有的] 数据。 * 表示[所有列] 
SELECT * FROM 表名称
-- 从 FROM 指定的[表中]，查询出指定 列名称(字段) 的数据
SELECT 列名称 FROM 表名称

注意:SQL语句中的关键字对大小写不敏感。SELECT 等效于 select，FROM 等效于 from。

2.示例

4. INSERTINTO 语句

1.语法

INSERT INTO 语句用于向数据表中插入新的数据行，语法格式如下:

-- 语法解读: 向指定的表中，插入如下几列数据，列的值通过 values 
-- 一指定1--2 -- 注意: 列和值婴一一对应，多个列和多个值之间，使用英文的逗号分隔
INSERT INTO table_name (列1，列2,...) VALUES (值1，值2,....)

2.示例

5. UPDATE 语句

1.语法

Update 语句用于修改表中的数据。语法格式如下:

-- 语法解读:
-- 1，用 UPDATE 指定要更新哪个表中的数据
-- 2.用 SET 指定列对应的新值
-- 3.用 WHERE 指定更新的条件
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

2.示例

更新某一行中的一个列

把 users 表中id 为3 的用户密码，更新为 888888。示例如下:

UPDATE users set userPass=888888 WHERE id=3

更新某一行中的多个列 把 users 表中 id 为2 的用户名和密码，分别更新为 xh 和 xh88。示例如下:

UPDATE users set userName="xh", userPass="xh88" where id=2;

6. DELETE 语句

1.语法

DELETE 语句用于删除表中的行。语法格式如下:

-- 语法解读:
-- 从指定的表中，根据 WHERE 条件，删除对应的数据行
DELETE FROM 表名称 WHERE 列名称 = 值

2.示例

7. WHERE 子句

1.语法

WHERE 子句用于限定选择的标准。在 SELECT、UPDATE、DELETE 语句中，皆可使用 WHERE 子句来限定选择的标准

-- 查询语句中的 WHERE 条件
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
-- 更新语句中的 WHERE 条件
UPDATE 表名称 SET 列=新值 WHERE 列 运符 值
-- 删除语句中的 WHERE 条件
DELETE FROM 表名称 WHERE 列 运算符 值

可在WHERE子句中使用的运算符

注意:在某些版本的SQL 中，操作符 <> 可以写为 != 。

2.示例

可以通过WHERE子来限定SELECT的查询条件:

-- 查询 status 为 1 的所有用户
SELECT * FROM users WHERE status=1
-- 查询 id 大于 2 的所有用户
SELECT * FROM users WHERE id>2
-- 查询 username 不等于 admin 的所有用户
SELECT * FROM users WHERE username <> "admin"

8. AND 和 OR 运算符

1.语法

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

AND 表示 必须同时满足多个条件，相当于 JavaScript 中的 && 运算符，例如 if (a !== 10 && a !== 20)

OR 表示只要满足任意一个条件即可，相当于 JavaScript 中的 || 运算符，例如 if(a !== 10 || a !== 20)

2.示例 使用 AND 来显示所有 status 为 0，并且id 小于 2 的用户:

select * from users where id<2 && status=1;

使用 OR 来显示所有 userName='xh'，或者 id 小于 2 的用户:

select * from users where id<2 || userName="xh";

9. ORDER BY 语句

对 users 表中的数据，按照 status 字段进行排序，示例如下:

-- 升序排列 默认
SELECT * FROM users ORDER BY status;
SELECT * FROM users ORDER BY status ASC;
-- 降序排序
SELECT * FROM users ORDER BY status DESC;

多重排序 对 users 表中的数据，先按照 status 字段进行降序排序，再按照 userName 的字母顺序，进行升序排序，示例如下:

SELECT * FROM users ORDER BY status DESC, userName ASC

10. COUNT(*)函数

1.语法

COUNT() 函数用于返回查询结果的总数据条数，语法格式如下:

SELECT COUNT(*) FROM 表名称

2.示例：

查询数据中 status 为 1 的数据条数

SELECT COUNT(*) FROM users WHERE status=1;

11. AS 关键字

使用 AS 关键字为列起别名

SELECT COUNT(*) AS total FROM users WHERE status=1;

四、项目中操作 MySQL

1. 步骤

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

2. 安装配置 mysql

2.1 安装 mysql

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

npm install mysql

2.2 配置 mysql 模块

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

1 // 1.导入 mysql 横块
2 const mysql = require( 'mysql')
3 // 2.建立与 MySQL 数据库的连接
4 const db = mysql.createPool({
    host: "127.0.0.1",     // 数据库的 IP 地址
    user: "root",         // 登录数据库的账号
    password:"admin123456", // 登录数据阵的密码
    database: "my_db_test"  // 指定要提作哪个数起库
})

2.3 测试 mysql 模块能否正常工作

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

// 检测 mysql 块能否正工作
db.query('SELECT 1', (err, results) => {
    if (err) return console.log(err.message)
    // 只要能打印出 [ RowDataPacket { '1': 1 } ] 的结果，就证明数据库连接正常
    console.log(results)
})

3. 查询、插入操作

3.1 查询

查询 users 表中所有的数据

const sqlStr = 'select * from users';
db.query(sqlStr, (err, results) => {
    // 查询数据失败
    if(err) return console.log(err.message)
    // 查询数据成功
    // 如果执行的是 select 语句，则查询结果为数组
    console.log(results)
})

3.2 插入

向 users 表中新增数据，其中 userName 为 Spider-Man ，userPass 为 pcc321。示例代码如下:

// 1，要插入到 users 表中的数据对象
const user = { userName: 'Spider-Man' , userPass: 'pcc321' }
// 2.待执行的 SQL 语句，其中英文的 ? 表示占位符
const sqlstr = "INSERT INTO users (userName,userPass) VALUES (?,?)"
// 3，使用数组的形式，依次为 ? 占位符指定具体的值
db.query(sqlstr, [user.userName, user.userPass], (err, results) => {
    if (err) return console.log(err.message) // 失败
    if(results.affectedRows === 1) {  // 成功
        console.log('插入数据成功') 
    }
})

插入数据的便捷方式

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

// <!-- 要插入到 users 表中的数据对象 -->
const user = { userName: 'user12' , userPass: 'user111' }
// 2.待执行的 SQL 语句，其中英文的 ? 表示占位符
const sqlStr = "INSERT INTO users set ?"
// 3，直接将数据对象当作占位符的值
db.query(sqlStr, user, (err, results) => {
  if (err) return console.log(err.message) // 失败
  if(results.affectedRows === 1) {  // 成功
    console.log('插入数据成功') 
  } 
})

4. 更新、删除操作

4.1 更新

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

// 1，要更新的数据对象
const user = {id: 1, userName: "aaa", userPass: '000' }
// 2.要执行的 SQL 语句
const sqlstr = "UPDATE users SET username=?, userPass=? WHERE id=?;"
// 3.调用 db.query() 执行 SQL 语的时，使用数组次为占位符指定具体的值
db.query(sqlstr, [user.userName, user.userPass, user.id], (err, results) => {
   if(err) return console.log(err.message) // 失败
   if (results.affectedRows === 1) {  // 成功
      console.log("更新数据成功!")
   } 
})

更新数据的便捷方式

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

// 1，要更新的数据对象
const user = {id: 7, userName: "aaaa", userPass: '0000' }
// 2.要执行的 SQL 语句
const sqlStr = "UPDATE users SET ? WHERE id=?"
// 3.调用 db.query() 执行 SQL 语句的同时，使用数组依次为占位符指定具体的值
db.query(sqlStr, [user, user.id], (err, results) => {
  if (err) return console.log(err.message) // 失败
  if (results.affectedRows === 1) {  // 成功
    console.log("更新数据功!")
  } 
})

4.2 删除

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

// 1.要执行的 SQL 语句
const sqlstr = "DELETE FROM users WHERE id=?"
// 2.调用 db.query() 执行 SQL 语句的同时，为占位符指定具体的值
// 注意:如果 SL 语句中有多个占位符，则必须使用数组为每个占位符指定具体的值
// 如果 SQL 语句中只有一个占位符，则可以省略数组
db.query(sqlstr, 7, (err, results) => {
    if (err) return console.log(err.message) // 失败
    if (results.affectedRows == 1) {  // 成功
      console.log("删除数据成功!")
    }
})

标记删除

使用 DELETE 语句，会把真正的把数据从表中删除掉。为了保险起见，推荐使用标记删除的形式，来模拟删除的动作。

所谓的标记删除，就是在表中设置类似于 status 这样的状态字段，来标记当前这条数据是否被删除。

当用户执行了删除的动作时，我们并没有执行 DELETE 语句把数据删除掉，而是执行了UPDATE 语句，将这条数据对应的 status 字段标记为删除即可。

// 标记删除:使用 UPDATE 语句替代 DELETE 语句，只更新数据的状态，并没有真正删除
db.query("UPDATE users SET status=0 WHERE id=?", 4, (err, results) =>{
    if (err) return console.log(err.message)
    if (results.affectedRows === 1){
        console.log('删除数据成功!')
    } // 成功
})