Home > Article > Web Front-end > Let's talk about how to operate MySQL in Node project
Let's talk about how to operate MySQL in Node project
- 青灯夜游forward
- 2023-01-09 10:36:331769browse
How to operate MySQL in Node project? The following article will talk to you about several SQL statements for managing databases, and introduce how to operate MySQL in Express projects. I hope it will be helpful to you!
Database
Database is used to organize, A warehouse that stores and manages data. Today's world is an Internet world full of data, full of a large amount of data. There are many sources of data, such as travel records, consumption records, web pages browsed, messages sent, etc. In addition to text type data, images, music, and sounds are all data. In order to facilitate the management of data in the Internet world, there is the concept of database management system (referred to as: database). Users can perform operations such as adding, querying, updating, and deleting data in the database.
Classification of database:
MySQL database(currently The most widely used and popular open source free database; Community Enterprise)
Oracle Database(Paid)
SQL Server database (charged)
Mongodb database(Community Enterprise)
Comparison: MySQL, Oracle, and SQL Server are traditional databases (also called: relational databases or SQL databases). These three have the same design concept and similar usage; while MongoDB is a new type of database (also called: non-relational databases or NoSQL databases). ), which makes up for the shortcomings of traditional databases to a certain extent.
Data organization structure of traditional database: In traditional database, the data organization structure is divided into database (database), data table (table) ), data row (row), and field (field).
The relationship between libraries, tables, rows, and fields in actual development: In actual project development, generally, each project corresponds to an independent database;Different data should be stored in different tables of the database, for example: user data is stored in the users table, and book data is stored in the books table;The specific information stored in each table is determined by the fields. For example: we can design the three fields of id, username, and password for the users table; table The rows in represent each specific piece of data.
MySQL installation and configuration
For developers, they only need to install MySQL Server and Navicat to meet their development needs.
MySQL Server: Software specially used to provide data storage and services
Navicat: A visual MySQL management tool through which you can conveniently operate the data stored in MySQL Server
For specific installation tutorials, please refer to my previous article: MySQL Installation. With visualization tools, it becomes extremely easy for us to create tables and edit table data.
##SQL management database
SQL (full English name: Structured Query Language) isStructured Query Language, a programming language specifically used to access and process databases. It allows us to manipulate the data in the database in the form of programming.
Note:
1) SQL is a database programming language2) Written in SQL language The code that comes out is called a SQL statement 3) SQL language can only be used in relational databases (for example: MySQL, Oracle, SQL server). Non-relational databases (such as Mongodb) do not support SQL language.
SELECT statement
is used to query data from the table. The results of the execution are stored in a results table (called a result set). The syntax is as follows: (Note: Keywords in SQL statements are not case-sensitive, SELECT is equivalent to select, and FROM is equivalent to from).
-- 这是注释 -- 从 FROM 指定的【表中】，查询出【所有的】数据，* 表示【所有列】 SELECT * FROM 表名称 -- 从 FROM 指定的【表中】，查询出指定 列名称 (字段) 的数据 SELECT 列名称 FROM 表名称
INSERT INTO statement
is used to insert data Insert new data rows into the table.
-- 向指定表中插入数据，列的值通过 values 一一指定 -- 列和值要一一对应，多个列和多个值之间，要使用英文逗号分隔 insert into table_name (列1,列2...) values (值1,值2,值3)
Update statement
is used to modify the data in the table
-- 用 UPDATE 指定要更新哪个表中的数据，用 SET 指定列对应的新值，用 WHERE 指定更新的条件 update 表名称 set 列名称 = 新值 where 列名称 = 某值
DELETE statement
is used to delete rows in the table
-- 从指定的表中，根据 WHERE 条件，删除对应的数据行 delete from 表名称 where 列名称 = 值
WHERE clause
is used to limit the selection criteria. In SELECT, UPDATE, and DELETE statements, the WHERE clause can be used to limit the selection criteria.
-- 查询语句中的 WHERE 条件 select 列名称 from 表名称 where 列 运算符 值 -- 更新语句中的 WHERE 条件 update 表名称 set 列=新值 where 列 运算符 值 -- 删除语句中的 WHERE 条件 delete from 表名称 where 列 运算符 值
The following operators can be used in the WHERE clause to limit the selection criteria:
| Operator | Description |
|---|---|
| = | is equal to |
| is not equal to | |
| is greater than | |
| Less than | |
| Greater than or equal to | |
| Less than Equal | |
| Search for a certain pattern within a certain range | ##LIKE |
| # 例如：可以通过 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'<h3 id="AND%E5%92%8COR"><strong>AND和OR</strong></h3> <p>AND和OR可在WHERE子语句中把两个或多个条件结合起来。</p> <p>AND表示必须同时满足多个条件，相当于JS中的 && 运算符</p> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/3cc06dc44f0b2b5dc86f5fb3b6881a23-7.png" style="max-width:90%"></p> <p>OR表示只要满足任意一个条件即可，相当于JS中的 || 运算符</p> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/6a919fc9198576ce31b07caef123af2d-8.png" style="max-width:90%"></p> <h3 id="ORDER%20BY%E5%AD%90%E5%8F%A5"><strong>ORDER BY子句</strong></h3> <p>order by语句用于根据指定的列对结果集进行排序。order by语句默认按照升序对记录进行排序，如果想按照降序对记录进行排序，可以使用<span style="color:#be191c;"><strong> DESC </strong></span>关键字。</p> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/4e55a89f9c966a3ffd3ee77fe6029c34-9.png" style="max-width:90%"></p> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/4e55a89f9c966a3ffd3ee77fe6029c34-10.png" style="max-width:90%"></p> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/2f7c5c3574ca21ce011ca58d2e53e002-11.png" style="max-width:90%"></p> <h3 id="COUNT(*)%E5%87%BD%E6%95%B0"><strong>COUNT(*)函数</strong></h3> <p><span style="color:#be191c;"><strong>COUNT(*)</strong></span>函数用于<span style="color:#b95514;"><strong>返回查询结果的总数据条数</strong></span>，语法格式如下：</p> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/2f7c5c3574ca21ce011ca58d2e53e002-12.png" style="max-width:90%"></p> <p>使用AS为列设置别名：如果希望给查询出来的列名设置别名，可以使用 AS 关键字：</p> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/9b76d8432b5c57738c0bf5ccbac1cd83-13.png" style="max-width:90%"></p> <h2 id="%E5%9C%A8Express%E9%A1%B9%E7%9B%AE%E4%B8%AD%E6%93%8D%E4%BD%9CMySQL"><strong>在Express项目中操作MySQL</strong></h2> <p><img alt="" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/9b76d8432b5c57738c0bf5ccbac1cd83-14.png" style="max-width:90%"></p> <p><span style="color:#be191c;"><strong>安装操作MySQL数据库的第三方模块(mysql)</strong></span>：</p> <p>mysql模块是托管于npm上的第三方模块。它提供了在 Node.js项目中连接和操作MySQL数据库的能力。想要在项目中使用它，需要先运行如下命令，将mysql安装为项目的依赖包:（<span style="color:#be191c;"><strong>注意</strong></span>：如果数据库是8.0以后的，需安装mysql2，因为8.0+版本的加密方式改变，node目前还不支持！）【相关教程推荐：<a href="https://www.php.cn/course/list/24.html" target="_blank">nodejs视频教程</a>、<a href="https://www.php.cn/course.html" target="_blank" textvalue="编程教学">编程教学</a>】</p> <pre class="brush:php;toolbar:false"># 我的数据库是8.0+版本，安装如下命令即可 npm install mysql2 通过mysql模块连接到MySQL数据库： 在使用mysql模块操作 MySQL数据库之前，必须先对 mysql模块进行必要的配置，主要的配置步骤如下： // 导入 mysql 模块
const mysql = require('mysql2')
// 建立与MySQL数据库的连接
const db = mysql.createPool({
host:'127.0.0.1', // 数据库的 IP 地址
port:'3306' // 数据库的端口号
user:'root', // 登录数据库的账号
password:'123456', // 登录数据库的密码
database:'mysql_test'// 指定要操作哪个数据库
})
通过mysql模块执行SQL语句： 调用 db.query() 函数，指定要执行的SQL语句，通过回调函数拿到执行的结果： // 导入 mysql 模块
const mysql = require('mysql2')
// 建立与MySQL数据库的连接
const db = mysql.createPool({
host:'127.0.0.1', // 数据库的 IP 地址
port:'3306',
user:'root', // 登录数据库的账号
password:'123456', // 登录数据库的密码
database:'mysql_test'// 指定要操作哪个数据库
})
// 检测 mysql 模块能否正常执行
db.query('select * from users',(err,results)=>{
if(err) return console.log(err.message);
// 只要能打印出 [RowDataPacket {'1','1'} ]的结果，就证明数据库连接正常
console.log(results);
})
向 users 表中新增数据，其中username为张三，password为123000，代码如下： // 向数据库中添加数据
const thing = {username:'hh',password:123000}
// 待执行的SQL语句，其中英文 ? 表示占位符s
// const dataStr = 'insert into users (username,password) values (?,?)'
// 向表中增添数据时，如果数据对象的每个属性和数据表字段一一对应，则可以通过以下方式简单快速插入语句
const dataStr = 'insert into users set ?'
// 使用数组形式，为 ? 占位符指定具体的值
// db.query(dataStr,[thing.username,thing.password],(err,results)=>{
db.query(dataStr,thing,(err,results)=>{
if(err) return console.log(err.message); // 失败
// 注意:如果执行的是insert into插入语句，则results是一个对象
// 可以通过affectedRows属性，来判断是否插入数据成功
if(results.affectedRows === 1){
console.log('数据插入成功！');
}
})
因为 id 具有唯一性，即使你把某条id的记录删掉，它的id下一条数据是用不了的，只能自增。
更新数据对象，可以通过以下方式进行： // 向数据库中更新数据
const thing = {id:3,username:'aaa',password:123000}
// 待执行的SQL语句，其中英文 ? 表示占位符s
const dataStr = 'update users set username=?,password=? where id=?'
// 调用db. query()执行SQL语句的同时，使用数组依次为占位符指定具体的值
db.query(dataStr,[thing.username,thing.password,thing.id],(err,results)=>{
if(err) return console.log(err.message); // 失败
// 可以通过affectedRows属性，来判断是否更新数据成功
if(results.affectedRows === 1){
console.log('数据更新成功！');
}
})
删除数据时，推荐用唯一标识 id 去删除。 // 向数据库中删除数据
const dataStr = 'delete from users where id=?'
db.query(dataStr,4,(err,results)=>{
if(err) return console.log(err.message); // 失败
// 可以通过affectedRows属性，来判断是否删除数据成功
if(results.affectedRows === 1){
console.log('数据删除成功！');
}
})
标记删除：使用DELETE语句，会把真正的把数据从表中删除掉。为了保险起见，推荐使用标记删除的形式，来模拟删除的动作。所谓的标记删除，就是在表中设置类似于status这样的状态字段，来标记当前这条数据是否被删除。当用户执行了删除的动作时，我们并没有执行DELETE语句把数据删除掉，而是执行了UPDATE语句，将这条数据对应的status字段标记为删除即可。 // 标记删除：使用 update 语句来替代 delete 语句，只更新数据的状态，并没有真正删除
const dataStr = 'update users set status=1 where id=?'
db.query(dataStr,11,(err,results)=>{
if(err) return console.log(err.message); // 失败
// 可以通过affectedRows属性，来判断是否删除数据成功
if(results.affectedRows === 1){
console.log('数据删除成功！');
}
})
更多node相关知识，请访问：nodejs 教程！ |
The above is the detailed content of Let's talk about how to operate MySQL in Node project. For more information, please follow other related articles on the PHP Chinese website!