node.js学习笔记5 MySQL数据库的基本使用、使用mysql模块在项目中操作MySQL

---

1. 数据库的基本概念

数据库是用来组织、存储和管理数据的仓库

1.1 常见的数据库及分类

市面上的数据库有很多种，最常见的数据库有如下几个：

• MySQL数据库(目前使用最广泛，流行度最高的开源免费数据库)
• Oracle数据库(收费)
• SQL Server数据库(收费)
• Mongodb数据库(免费Community版、收费Enterprise版)

其中，MySQL、Oracle、SQL Server属于传统型数据库(又叫关系型数据库)
而Mongodb属于新型数据库(又叫非关系型数据库)

1.2 传统型数据库的数据组织结构

传统型数据库中，数据的组织结构分为数据库、数据表、数据行、字段

1.3 实际开发中库、表、行、字段的关系

1. 在实际项目开发中，一般情况下，每个项目都对应独立的数据库
2. 不同的数据，要存储到数据库的不同表中，例如：用户数据存储到user表中，图书数据存储到books表中
3. 每个表中具体存储哪些信息，由字段来决定，例如：我们可以为user表设计id、username、password这三个字段
4. 表中的行，代表每一条具体的数据

2. 安装并配置MySQL

对于开发人员，只需要安装 MySQL Server和 MySQL Workbench 这两个软件，就能满足开发的需要了

• MySQL Server：专门用来提供数据存储和服务的软件
• MySQL Workbench：可视化的 MySQL 管理工具，通过它可以方便地操作存储在 MySQL Server 中的数据

具体安装过程在这不记录了，教程也很多可以自己搜

3. MySQL的基本使用

3.1 使用MySQL Workbench管理数据库

1. 连接数据库
   
   
2. 创建数据库
   
3. 创建数据表
   
   
   DataType数据类型：int整数、varchar字符串、tinyint(1)布尔值
   字段的特殊标识：
   • PK(Primary Key)：主键、唯一标识（这里的id就要勾上PK）
   • NN(Not Null)：值不允许为空
   • UQ(Unique)：值唯一
   • AI(Auto Increment)：值自动增长(一般id的值勾上AI，这样不用人为修改可以自动递增)
4. 向表中写入数据
   
   

3.2 使用SQL管理数据库

3.2.1 SQL是什么

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

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

3.2.2 SQL能做什么

• 从数据库中查询数据
• 向数据库中插入新的数据
• 更新数据库中的数据
• 从数据库删除数据

3.2.3 SQL的 SELECT 语句

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

-- 从 FROM 指定的表中，查询出所有的数据，*表示所有列
SELECT * FROM 表名

-- 从 FROM 指定的表中，查询出指定字段的数据
SELECT 字段名 FROM 表名

⚠️：SQL语句中的关键字对大小写不敏感
例：在users表中，获取名为username和password
的字段内容

SELECT username, password FROM users

3.2.4 SQL的 INSERT INTO 语句

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

-- 注意：字段和值要一一对应，多个字段和多个值之间，使用英文的逗号分隔
INSERT INTO table_name (字段1,字段2...) VALUES（值1,值2...）

例：向users表中，插入一条username为Tom，password为111111的用户数据

INSERT INTO users (username, password) values ('Tom', '111111')

3.2.5 SQL的 UPDATE 语句

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

-- UPDATE 指定要更新哪个表中的数据
-- SET 指定字段对应的新值
-- WHERE 指定更新的条件
UPDATE 表名 SET 字段名 = 新值 WHERE 字段名 = 某值

例：将users表中id为1的用户密码，更新为888888

UPDATE users SET password='888888' WHERE id=1

例：将users表中id为2的用户密码更新为888888，用户状态更新为1

-- 更新多个字段用英文逗号分割
-- ⚠️：不要忘记写WHERE条件，否则会导致整张表的数据都被更新
UPDATE users SET password='888888', status=1 WHERE id=2

3.2.6 SQL的 DELETE 语句

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

-- 从指定表中，根据WHERE条件，删除对应的数据行
DELETE FROM 表名 WHERE 字段名 = 某值

例：从users表中删除id为3的用户

-- ⚠️：不要忘记写WHERE条件，否则会导致误删整张表的数据
DELETE FROM users WHERE id=3

3.2.7 SQL的 WHERE 子句

语法：
WHERE子句用于限定选择的标准

-- 查询users表中 id大于2 的所有用户
SELECT * FROM users WHERE id>2
-- 查询users表中 username不等于admin 的所有用户
SELECT * FROM users WHERE username<>'admin'

3.2.8 SQL的AND和OR运算符

AND和OR可以在WHERE子语句中把两个或多个条件结合起来
AND相当于js中的&&运算符
OR相当于js中的||运算符

-- 查找所有状态为0且id小于3的用户
SELECT * FROM users WHERE status=0 AND id<3

-- 查找所有状态为1或username为AIpoem的用户
SELECT * FROM users WHERE status=1 OR username='AIpoem'

3.2.9 SQL的ORDER BY 子句

语法：
ORDER BY语句用于根据指定的字段对结果集进行排序
默认按照升序对记录进行排序(使用ASC关键字也能达到升序效果)
如果希望按照降序，可使用DESC关键字

-- 对users表中的数据，按照id字段进行升序排序
SELECT * FROM users ORDER BY id;

-- 对users表中的数据，按照id字段进行降序排序
SELECT * FROM users ORDER BY id DESC;

多重排序：

-- 对users表中的数据，先按照status字段进行降序排序，再按照username的字母排序进行升序排序
SELECT * FROM users ORDER BY status DESC, username ASC

3.2.10 SQL的 COUNT(*) 子句

语法：
COUNT(*)函数用于返回查询结果的总数据条数

-- 查询users表中status为0的总数据条数
SELECT COUNT(*) FROM users WHERE status=0

-- 使用AS设置别名
SELECT COUNT(*) AS total FROM users WHERE status=0

4. 在项目中操作MySQL

4.1 在项目中操作数据库的步骤

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

4.2 安装与配置mysql模块

4.2.1 安装mysql模块

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

 npm install mysql

4.2.2 配置mysql模块

配置步骤：

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

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

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

db.query('SELECT 1', (err,results) => {
  // mysql模块工作期间报错
  if (err) return console.log(err.message)
  // 打印出来[ RowDataPacket { '1': 1 } ]证明数据库连接正常
  console.log(results)
})

4.3 使用mysql模块操作MySQL数据库

4.3.1 查询数据

// 查询users表中所有的用户数据
db.query('SELECT * FROM users', (err,results) => {
  // 查询失败
  if (err) return console.log(err.message)
  // 查询成功
  console.log(results)
})

4.3.2 插入数据

向users表中新增数据，其中username为Tom，password为123456

// 1.要插入到users表中的数据对象
const userObj = { username: 'Tom', password: '123456'}
// 2.待执行的SQL语句，其中英文的 ? 表示占位符
const sqlStr = 'INSERT INTO users (username, password) VALUES (?,?)'
// 3.使用数组的形式，依次为 ? 占位符指定具体的值
db.query(sqlStr, [userObj.username,userObj.password],(err,results) => {
  if (err) return console.log(err.message)	// 插入失败
  // 插入成功
  // 可以通过results对象的affectedRows属性判断是否插入数据成功
  if (results.affectedRows === 1) {
    console.log('插入数据成功！')
  }
})

插入数据的便捷方式：
要注意数据表的字段名和数据对象的属性名一一对应

// 1.要插入到users表中的数据对象
const userObj = { username: 'Tom', password: '123456'}
// 2.待执行的SQL语句，其中英文的 ? 表示占位符
const sqlStr = 'INSERT INTO users SET ?'
// 3.直接将数据对象当作占位符的值
db.query(sqlStr, userObj, (err,results) => {
  if (err) return console.log(err.message)	// 插入失败
  // 插入成功
  if (results.affectedRows === 1) {
    console.log('插入数据成功！')
  }
})

4.3.3 更新数据

// 1.要更新的数据对象
const upUserObj = {id: 1, username: 'Pat', password: '000000'}
// 2.要执行的sql语句
const sqlStr = 'UPDATE users SET username=?, password=? WHERE id=?'
// 3.使用数组依次为占位符制定具体的值
db.query(sqlStr, [upUserObj.username,upUserObj.password,upUserObj.id], (err,results) => {
  if (err) return console.log(err.message) // 更新失败
  if (results.affectedRows === 1) {
    console.log('更新数据成功！');
  }
})

更新数据的便捷方式：

// 1.要更新的数据对象
const upUserObj = {id: 1, username: 'Pat', password: '000000'}
// 2.要执行的sql语句
const sqlStr = 'UPDATE users SET ? WHERE id=?'
// 3.使用数组依次为占位符制定具体的值
db.query(sqlStr, [upUserObj,upUserObj.id], (err,results) => {
  if (err) return console.log(err.message) // 更新失败
  if (results.affectedRows === 1) {
    console.log('更新数据成功！');
  }
})

4.3.4 删除数据

在删除数据时，推荐根据id这样的唯一标识来删除对应的数据

// 1.要执行的sql语句
const sqlStr = 'DELETE FROM users WHERE id=?'
// 2.为占位符制定具体的值
// 删除id为1的数据
db.query(sqlStr, 1, (err,results) => {
  if (err) return console.log(err.message) // 更新失败
  if (results.affectedRows === 1) {
    console.log('删除数据成功！');
  }
})

⚠️：如果sql语句中有多个占位符，则必须使用数组来为每个占位符制定具体的值
如果sql语句中只有一个占位符，则可以省略数组

4.3.5 标记删除

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

所谓标记删除，就是在表中设置类似于status这样的状态字段，来标记当前这条数据是否被删除
当用户执行了删除的动作时，我们并没有执行DELETE语句把数据删除掉，而是执行了UPDATE语句将这条数据的status字段标记为删除

// 使用UPDATE代替DELETE
db.query('UPDATE users SET status=1 WHERE id=?',1,(err,results) => {
  if (err) return console.log(err.message) // 更新失败
  if (results.affectedRows === 1) {
    console.log('删除数据成功！');
  }
})

---

完
新年偷了几天懒没学习嘿嘿