【MySQL】基础篇

目录

1数据库的概念

1.1 常见数据库

1.2 MySQL相关软件

1.3 初识SQL

2 SQL常用语法

2.1 SELECT（查询）

2.2 INSERT INTO（插入）

2.3 UPDATE（更新）

2.4 DELETE（删除）

2.5 WHERE 子语句（条件筛选）

2.5.1 语法

2.5.2 条件运算符

2.5.3 逻辑运算符

2.6 ORDER BY 子语句（排序）

2.7 COUNT() （统计数据数量）

2.8 AS（设置别名）

3 MySQL

3.1 安装MySQL模块

3.2 连接数据库

3.3 操作MySQL

3.3.1 查询数据

3.3.2 插入数据

3.3.3 更新数据

3.3.4 删除数据

4 Web开发模式

4.1 服务端渲染

4.2 前后端分离

4.3 如何选择？

5 身份认证

5.1 初识Session

5.1.1 HTTP协议的无状态性

5.1.2 Cookie

5.1.3 运行机制

5.1.4 局限性

5.2 Session的使用

5.2.1 安装express-session 中间件

5.2.2 配置express-session 中间件

5.2.3 存数据

5.2.4 取数据

5.2.5 清空数据

5.3 初识JWT

5.3.1 组成部分 

 5.3.2 运行机制

5.4 JWT的使用

5.4.1 安装JWT 相关的包

5.4.2 导入JWT 相关的包

5.4.3 定义密钥

5.4.3 使用jsonwebtoken生成token

5.4.4 客户端传token

5.4.5 使用express-jwt解析token

5.4.6 捕获JWT验证失败的错误

---

1数据库的概念

1.1 常见数据库

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

MySQL数据库（目前使用最广泛、流行度最高的开源免费数据库；Community + Enterprise）

Oracle 数据库（收费）

SQL Server 数据库（收费）

Mongodb数据库（Community + Enterprise）

其中，MySQL、Oracle、SQL Server 属于传统型数据库（又叫做：关系型数据库或SQL 数据库），这三者的 设计理念相同，用法比较类似

而Mongodb属于新型数据库（又叫做：非关系型数据库或NoSQL 数据库），它在一定程度上弥补了传统型数据库的缺陷

---

1.2 MySQL相关软件

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

MySQL Server：专门用来提供数据存储和服务的软件

MySQL Workbench：可视化的MySQL 管理工具，通过它，可以方便的操作存储在MySQL Server 中的数据 

---

1.3 初识SQL

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

三个关键点：

① SQL 是一门数据库编程语言

② 使用SQL 语言编写出来的代码，叫做SQL 语句

③ SQL 语言只能在关系型数据库中使用（例如MySQL、Oracle、SQL Server）

非关系型数据库（例如Mongodb） 不支持SQL 语言

---

2 SQL常用语法

2.1 SELECT（查询）

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

-- 从 FORM 指定的表中，查询出所有的数据 *表示所有列
SELECT * FROM 表名称-- 从 FROM 指定的表中，查询出指定列名称（字段）的数据
SELECT 列名称 FROM 表名称

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

但是表名称、列（字段）名称严格区分大小写

举例：

如需获取名为"username" 和"password" 的列的内容（从名为"users" 的数据库表）

多个列之间用英文逗号隔开

SELECT username, password FROM users

---

2.2 INSERT INTO（插入）

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

INSERT INTO table_name (列1, 列2, ...) VALUES (值1, 值2, ...)

注意：列和值之间要一一对应，多个列、多个值之间用英文逗号隔开

值是否加引号取决于其数据类型

举例：

向users表中插入一条username=user_a001，password=123456的值数据

INSERT INTO users (username, password) VALUES ("user_a001", "123456")

---

2.3 UPDATE（更新）

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

UPDATE 表名称 SET 列名称 = 新值 

上述命令会将表中所有的相应列都修改为新值，如果要指定哪一条数据，需要用WHERE子句，如下：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

举例：

将users表中的username=user_a001数据的password值修改为654321

UPDATE users SET password = 654321 WHERE username = user_a001

---

2.4 DELETE（删除）

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

DELETE FROM 表名称

上述命令会直接删除整个表的数据（不包含表的列名称等信息），因此我们也需要使用WHERE子语句来指定数据行，如下：

DELETE FROM 表名称 WHERE 列名称 = 值

举例：

删除users表中username=user_a001的数据行

DELETE FROM users WHERE username = user_a001

注意：

1. id字段一般会设置自增，由系统自动分配，当某一条数据，如id=4的数据被删除时，再插入一条新的数据，该数据的id会从5开始，而不会使用4 

2.一般不直接删除数据，而是使用标记删除的方法，即设置某一字段的值为特定值来区分是否被删除

---

2.5 WHERE 子语句（条件筛选）

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

2.5.1 语法

-- 在SELECT中使用
SELECT 列名称 FROM 表名称 WHERE 列名称 = 某值-- 在UPDATE中使用
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值-- 在DELETE中使用
DELETE FROM 表名称 WHERE 列名称 = 某值

2.5.2 条件运算符

上述的 = 可以替换为其他符号

操作符	描述
=	等于
<>   或   !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

注意：如MYSQL部分版本中，也可以使用!=作为不等号

---

2.5.3 逻辑运算符

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

AND 表示必须同时满足多个条件

OR 表示只要满足任意一个条件即可

举例：

查询users表中username=user_a001并且password=123456的数据

SELECT * FORM users WHERE username = user_a001 AND password = 123456

---

2.6 ORDER BY 子语句（排序）

ORDER BY 语句用于根据指定的列对结果集进行排序

ORDER BY 语句默认按照升序对记录进行排序，也可以使用ASC关键字

 如果希望按照降序对记录进行排序，可以使用DESC关键字

举例：

将users中的所有数据依照id进行升序排序 

SELECT * FROM users ORDER BY idSELECT * FROM users ORDER BY id ASC

将users中的所有数据依照id进行降序排序

SELECT * FROM users ORDER BY id DESC

ORDER BY也支持多重排序，如下：

将users表中的数据先按照id字段进行降序排序，再按照status字段进行升序排序，ASC同样可以省略

SELECT * FROM users ORDER BY id DESC, status ASC

---

2.7 COUNT() （统计数据数量）

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

-- 统计指定列中非 NULL 值的数量
SELECT COUNT(列名称) FORM 表名称-- 统计表中的行数（包含 NULL 值）
SELECT COUNT(*) FORM 表名称

---

2.8 AS（设置别名）

如果希望给查询出来的列名称设置别名，可以使用AS关键字，示例如下：

SELECT 列表名 AS 别名 FORM 表名称

---

3 MySQL

接下来将会演示如何在node.js中使用MySQL

3.1 安装MySQL模块

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

npm i mysql

---

3.2 连接数据库

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

可以通过下列代码检测是否成功连接：

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

---

3.3 操作MySQL

下列为基本代码模板，可以通过query方法与MySQL进行交互 

db.query("SQL语句", (err, results) => {//执行成功时，err值为空，否则为错误信息})

---

3.3.1 查询数据

db.query("SELECT * FORM users", (err, results) => {//查询失败if(err) return console.log(err.message)//查询成功console.log(results)
})

results输出类型是一个数组，如下：

[{ id: 1, name: 'John', age: 25 },{ id: 2, name: 'Jane', age: 30 }
]

除此之外，以下三种操作的返回值类型都是对象 ：

 {fieldCount: 0,affectedRows: 1,}

---

3.3.2 插入数据

可以使用英文的?表示占位符，

当只有一个占位符时，在指定具体值的时候，可以不用数组进行传值（在删除数据章节有体现）

//要插入数据库的数据
const user = { username: "user_a001", password: "123456" }
//待执行的SQL语句，其中英文的 ? 表示占位符
const sqlStr = "INSERT INTO users (username, password) VALUES (?, ?)"
//使用数组的形式，依次为 ? 占位符指定具体的值
db.query(sqlStr, [user.username, user.password], (err, results) => {//失败if(err) return console.log(err.message) //成功if(results.affectedRows === 1) console.log("插入数据成功！") 
})

当用户数据字段太多时，上述方法过于繁琐，可以采取下列写法：

前提：数据对象的每个属性和数据表的字段一一对应

//要插入数据库的数据
const user = { username: "user_a001", password: "123456" }
//待执行的SQL语句，其中英文的 ? 表示占位符
const sqlStr = "INSERT INTO users SET ?"
//直接将数据对象当做占位符
db.query(sqlStr, user, (err, results) => {//...})

---

3.3.3 更新数据

//要更新的数据对象
const user = { id: 7, username: "user_a001", password: "123456" }
//要执行的SQL语句
const sqlStr = "UPDATE users SET username = ? , password = ? WHERE id = ?"
//调用query()
db.query(sqlStr, [user.username, user.password, user.id], (err, results) => {//失败if(err) reyurn console.log(err.message) //成功if(results.affectedRows === 1) console.log("更新数据成功！") })

更新数据也可以进行简化

//要更新的数据对象
const user = { id: 7, username: "user_a001", password: "123456" }
//要执行的SQL语句
const sqlStr = "UPDATE users SET ? WHERE id = ?"
//调用query()
db.query(sqlStr, [user, user.id], (err, results) => {//...})

---

3.3.4 删除数据

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

标记删除：

使用DELETE 语句，会把真正的把数据从表中删除掉

为了保险起见，推荐使用标记删除的形式，来模拟删除的动作

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

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

---

4 Web开发模式

目前主流的Web 开发模式有两种，分别是：

① 基于服务端渲染的传统Web 开发模式

② 基于前后端分离的新型Web 开发模式

4.1 服务端渲染

服务器发送给客户端的HTML 页面，是在服务器通过字符串的拼接，动态生成的

因此，客户端不 需要使用Ajax 这样的技术额外请求页面的数据

优点：

① 前端耗时少，因为服务器端负责动态生成HTML 内容，浏览器只需要直接渲染页面即可，尤其是移动端，更省电

② 有利于SEO，因为服务器端响应的是完整的HTML 页面内容，所以爬虫更容易爬取获得信息，更有利于SEO

缺点： ① 占用服务器端资源，即服务器端完成HTML 页面内容的拼接，如果请求较多，会对服务器造成一定的访问压力

② 不利于前后端分离，开发效率低，使用服务器端渲染，则无法进行分工合作，尤其对于前端复杂度高的项目，不利于项目高效开发

---

4.2 前后端分离

前后端分离的开发模式，依赖于Ajax 技术的广泛应用

简而言之，前后端分离的Web 开发模式， 就是后端只负责提供API 接口，前端使用Ajax 调用接口的开发模式

优点：

① 开发体验好，前端专注于UI 页面的开发，后端专注于api的开发，且前端有更多的选择性

② 用户体验好，Ajax 技术的广泛应用，极大的提高了用户的体验，可以轻松实现页面的局部刷新

③ 减轻了服务器端的渲染压力，因为页面最终是在每个用户的浏览器中生成的

缺点：

① 不利于SEO，因为完整的HTML 页面需要在客户端动态拼接完成，所以爬虫对无法爬取页面的有效信息（解决方案：利用Vue、React 等前端框架的SSR（server side render）技术能够很好的解决SEO问题！）

---

4.3 如何选择？

不谈业务场景而盲目选择使用何种开发模式都是耍流氓

• 比如企业级网站，主要功能是展示而没有复杂的交互，并且需要良好的SEO，则这时我们就需要使用服务器端渲染
• 而类似后台管理项目，交互性比较强，不需要考虑SEO，那么就可以使用前后端分离的开发模式
• 另外，具体使用何种开发模式并不是绝对的，为了同时兼顾了首页的渲染速度和前后端分离的开发效率，一些网站采用了 首屏服务器端渲染+ 其他页面前后端分离的开发模式

---

5 身份认证

对于服务端渲染和前后端分离这两种开发模式来说，分别有着不同的身份认证方案：

①服务端渲染推荐使用Session认证机制

②前后端分离推荐使用JWT认证机制

5.1 初识Session

5.1.1 HTTP协议的无状态性

客户端的每次HTTP 请求都是独立的，连续多个请求之间没有直接的关系，服务器不会主动保留每次HTTP 请求的状态

注意：现实生活中的会员卡身份认证方式，在Web 开发中的专业术语叫做Cookie

---

5.1.2 Cookie

Cookie 是存储在用户浏览器中的一段不超过4 KB 的字符串

它由一个名称（Name）、一个值（Value）和其它几个用于控制Cookie 有效期、安全性、使用范围的可选属性组成

不同域名下的Cookie 各自独立，每当客户端发起请求时，会自动把当前域名下所有未过期的Cookie 一同发送到服务器

Cookie的几大特性： ① 自动发送 ② 域名独立 ③ 过期时限 ④ 4KB 限制

---

5.1.3 运行机制

我对于session运行机制的理解：

客户端第一次访问服务器时，服务器会给客户端一个cookie，并且基于这个cookie，服务器中会专门生成一块区域来存储一定的信息，而cookie相当于门牌号对应这个区域，这个cookie会保存在客户端，每次用户访问数据时，cookie会自动发给服务器进行匹配，获取相应区域内容

AI的答案：

你的理解基本正确。以下是更详细准确的关于Session运行机制的阐述，以帮助你进一步深化理解：

1. 首次访问：当客户端第一次访问服务器时，服务器确实会创建一个唯一的Session标识符（类似你所说的“门牌号”）。这个标识符通常以加密的字符串形式存在。

2. Cookie生成与发送：服务器将这个Session标识符通过Set - Cookie头部信息发送给客户端。客户端收到后，会将这个包含Session标识符的Cookie保存起来。这里的Cookie是一种由服务器发送到客户端，并由客户端存储在本地（通常是浏览器）的数据，它主要用于在后续的请求中携带特定信息。

3. 服务器端存储：与此同时，服务器在自身的内存（或其他存储介质，如数据库）中开辟一块区域，用于存储与该Session相关的信息。这些信息可以是用户的登录状态、购物车内容等。这块区域通过刚才生成的Session标识符来索引，就像你理解的门牌号对应特定区域一样。

4. 后续访问：在客户端后续对服务器的每次请求中，浏览器会自动将包含Session标识符的Cookie发送给服务器。服务器接收到请求后，从Cookie中提取出Session标识符，然后根据这个标识符找到服务器端对应的Session存储区域，从而获取并更新相关的用户信息。 需要注意的是，虽然大部分情况下客户端通过Cookie来传递Session标识符，但并不是唯一方式。例如，在禁用Cookie的情况下，也可以通过URL重写等技术将Session标识符附加在URL中传递给服务器。

---

5.1.4 局限性

Session 认证机制需要配合Cookie才能实现

由于Cookie 默认不支持跨域访问，所以，当涉及到前端跨域请求后端接口的时候，需要做很多额外的配置，才能实现跨域Session 认证

注意：

当前端请求后端接口不存在跨域问题的时候，推荐使用Session 身份认证机制

当前端需要跨域请求后端接口的时候，不推荐使用Session 身份认证机制，推荐使用JWT 认证机制

---

5.2 Session的使用

5.2.1 安装express-session 中间件

在Express 项目中，只需要安装express-session中间件，即可在项目中使用Session 认证：

npm i express-session

---

5.2.2 配置express-session 中间件

express-session 中间件安装成功后，需要通过app.use() 来注册 session 中间件，示例代码如下：

const session = require("express-session")app.use(session({secret: "user_a001",    //secret属性的值可以为任意字符串（用来加密，越复杂越好）resave: false,          //固定写法saveUninitialized: true //固定写法
}))

以下了解： 

resave 选项控制是否在每次请求时都强制将会话重新保存到会话存储中，即使会话数据在本次请求中没有发生变化

• 当 resave 设置为 true 时，无论会话数据是否有修改，每次请求都会把会话数据重新保存到存储中，这可能会导致不必要的性能开销，因为频繁地读写会话存储（如数据库）会增加服务器的负担
• 当 resave 设置为 false 时，只有在会话数据确实发生改变时，才会将其保存到存储中。这样可以减少不必要的存储操作，提高性能
• 推荐设置在大多数情况下，推荐将 resave 设置为 false，因为现代的会话存储通常会自行处理会话数据的过期和清理，不需要每次请求都强制保存

saveUninitialized 选项控制是否保存新创建但还未修改的会话

• 当 saveUninitialized 设置为 true 时，即使会话还没有被修改（例如，用户刚访问网站，还没有进行任何登录或其他操作），也会在会话存储中创建一个新的会话记录，并将对应的会话 ID 发送给客户端
• 当 saveUninitialized 设置为 false 时，只有在会话数据被修改后，才会在会话存储中创建会话记录并发送会话 ID 给客户端

---

5.2.3 存数据

当express-session 中间件配置成功后，即可通过req.session 来访问和使用 session 对象，从而存储用户的关键信息：

注意：user与islogin属性名为自定义设置的，session本身只提供一个挂载到req的session对象

---

5.2.4 取数据

可以直接从req.session 对象上获取之前存储的数据，示例代码如下：

---

5.2.5 清空数据

调用req.session.destroy() 函数，即可清空服务器保存的session 信息

---

5.3 初识JWT

5.3.1 组成部分 

JWT 通常由三部分组成，分别是Header（头部）、Payload（有效荷载）、Signature（签名）。 三者之间使用英文的“.”分隔，格式如下： 

下面是JWT 字符串的示例： 

其中：

Payload部分才是真正的用户信息，它是用户信息经过加密之后生成的字符串

Header 和Signature 是安全性相关的部分，只是为了保证Token 的安全性

---

 5.3.2 运行机制

用户的信息通过Token 字符串的形式，保存在客户端浏览器中，服务器通过还原Token 字符串的形式来认证用户的身份 

---

5.4 JWT的使用

5.4.1 安装JWT 相关的包

npm i jsonwebtoken express-jwt

其中：

jsonwebtoken 用于生成JWT 字符串

express-jwt 用于将 JWT 字符串解析还原成JSON 对象

---

5.4.2 导入JWT 相关的包

const jwt = require("jsonwebtoken")const expressJWT = require("express-jwt")

---

5.4.3 定义密钥

为了保证JWT 字符串的安全性，防止JWT 字符串在网络传输过程中被别人破解，我们需要专门定义一个用于加密和解密 的secret 密钥：

① 当生成JWT 字符串的时候，需要使用secret 密钥对用户的信息进行加密，最终得到加密好的JWT 字符串

② 当把JWT 字符串解析还原成JSON 对象的时候，需要使用secret 密钥进行解密

const secretKey = "USER_A001"    //本质上就是一个字符串，越复杂越好

---

 5.4.3 使用jsonwebtoken生成token

调用jsonwebtoken包提供的sign() 方法，将用户的信息加密成JWT 字符串，响应给客户端：

注意：

用户信息对象中不要放入用户的隐私信息，如密码

配置对象中的expiresIn值为token有效时间

---

5.4.4 客户端传token

客户端收到服务器返回的JWT 之后，通常会将它储存在localStorage或sessionStorage中。

此后，客户端每次与服务器通信，都要带上这个JWT 的字符串，从而进行身份认证

推荐的做法是把JWT 放在HTTP 请求头的Authorization 字段中，格式如下：

---

5.4.5 使用express-jwt解析token

服务器通过express-jwt这个中间件，自动将客户端发送过来的Token 解析还原成JSON 对象：

//使用app.use()注册中间件
app.use(expressJWT({ secret: secretKey }).unless({ path: [/^\/api\//] }))

expressJWT()用来解析token

unless用来指定哪些接口不需要JWT验证就可以访问

解析完成后，数据会自动挂载到req.user对象中

---

5.4.6 捕获JWT验证失败的错误

当使用express-jwt 解析 Token 字符串时，如果客户端发送过来的Token 字符串过期或不合法，会产生一个解析失败的错误，影响项目的正常运行

我们可以通过Express 的错误中间件，捕获这个错误并进行相关的处理，示例代码如下：

当错误名称为UnauthorizedError时，代表token已超出可用时间，已失效