What are the basic operation methods of node.js for database MySQL?

Basic operations of database MySQL (add, delete, modify, query)

The unified table structure of the entire blog is:
The users table has four fields id username password status, and the four fields represent four columns, among which id It is an auto-increment column, the default value of status is 0, optional value 0, 1
id auto-increment, username is zs, ls, wu password respectively: 123456 abcdef 123abc status is 0, 1, 1

#查询整张表的所有数据
select * from users

#查询指定列的所有数据
select username,password from users

#指定某列添加数据
insert into users(username,password) values('萧寂','1234')

#指定某列修改数据
update users set username="你好a",password="1234567",status=1 where id=2

#根据id删除行
delete from users where id=4

#查询status为1的所有用户
SELECT *FROM users WHERE status=1

#查询id 大于2的所有用户
SELECT *FROM users WHERE id>2

#查询username不等于admin的所有用户
SELECT *FROM users WHERE username<>&#39;admin&#39;

#使用AND来显示所有status为0，并且id 小于3的用户:
SELECT * FROM users WHERE status=0 AND id<3

#使用OR来显示所有status为1，或者username为zs的用户
SELECT* FROM users WHERE status=1 OR username=&#39;zs&#39;

#对users表中的数据，按照status字段进行升序排序
SELECT * FROM users ORDER BY status;（升序排序在status后加上ASC效果等同）
select * from users order by status asc

#根据id降序排序,降序排序使用desc关键字
select * from users order by id desc

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

#查询id为1的数据返回的总条数
select count(*) from users where id=1

#将列名称从COUNT(*)修改为total
SELECT COUNT(*) AS total FROM users WHERE id=1

#给username列添加uname别名，给password列添加upwd别名
select username as uname,password as upwd from users

Add, delete, modify and check in node.js project

First execute the command to initialize the package.json package

npm init -y  (文件名为英文，不能有空格、特殊字符或中文，否则报错)

mysql module is managed Third-party modules on npm. It provides the ability to connect and operate MySQL database in Node.js projects.
If you want to use it in the project, you need to run the following command first to install mysql as a dependency package of the project:

npm install mysql   或者  npm i mysql

After the above operation is completed, start configuring the MySQL module

Configuring the MySQL module

Before using the mysql module to operate the MySQL database, you must first perform the necessary configuration on the mysql module. The main configuration steps are as follows:

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

Test whether the module can Whether the connection works normally (execute the run command node file name or nodemon file name)

Call the db.query() function, specify the SQL statement to be executed, and get it through the callback function The execution result

db.query("select 1", function (err, results) {
    //模块报错返回错误信息
    if (err) return console.log(err.message);
    //运行成功
    console.log(results);
})

The return result of a successful test is: [ RowDataPacket { ‘1’: 1 } ]

The SQL code of the query table ( See the first line for table name and structure)

查询数据user表中所有的用户数据
const sqlStr = "select * from users"
db.query(sqlStr, function (err, results) {
    //查询失败
    if (err) return console.log(err.message);
    //查询成功
    //注意如果执行的是select查询语句，则执行的结果是数组
    console.log(results);
})

SQL statement to add data (two methods)

//插入数据
//向users表中新增数据，其中username为Spider-Man，password为pcc321
//要插入到users表中的数据对象
const user = { username: "Spider-Man", password: "pcc321" }
//待执行的SQL语句，其中的？表示占位符
const sqlStr = "insert into student(student,card) values(?,?)"
//使用数组的形式，依次为？占位符具体的值（result.affectedRows为影响的行数）
db.query(sqlStr, [user.username, user.password], function (err, results) {
    if (err) return console.log(err.message);
    if (results.affectedRows == 1) {
        console.log("插入成功");
    }
})
//向表中新增数据时，如果数据对象的每个属性和数据表的字段一一对应，则可以通过如下方式快速插入数据:
//要插入到users表中的数据对象
const user = { username: "Spider2-Man", password: "pcc321" }
//待执行的SQL语句，其中的？表示占位符
const sqlStr = "insert into users set ?"
db.query(sqlStr, user, function (err, results) {
    if (err) return console.log(err.message);
    if (results.affectedRows == 1) {
        console.log("插入成功");
    }
})

SQL statement to modify data (two methods)

//修改表中的数据
//向users表中更新的数据，其中username为Spider-Man，password为pcc321，id为5
const user = { id: 7, username: "xiao1jiao", password: "111222" }
//待执行的sql语句，其中的？表示占位符
const sqlStr = "update users set username=?,password=? where id=?"
//使用数组的形式，依次为？占位符具体的值（result.affectedRows为影响的行数）
db.query(sqlStr, [user.username, user.password, user.id], function (err, results) {
    if (err) return console.log(err.message);
    if (results.affectedRows == 1) {
        console.log("修改", user.id, "列成功");
    }
})
//修改表数据时，如果数据对象的每个属性和数据表的字段一一对应，则可以通过如下方式快速修改表数据
//向users表中更新的数据，其中username为aaaa，password为1111，id为5
const user = { id: 5, username: "aaaa", password: "1111" }
//待执行的sql语句，其中的？表示占位符
const sqlStr = "update users set ? where id=?"
//使用数组的形式，依次为？占位符具体的值（result.affectedRows为影响的行数）
db.query(sqlStr, [user, user.id], function (err, results) {
if (err) return console.log(err.message);
if (results.affectedRows == 1) {
    console.log("修改", user.id, "列成功");
}
})

Delete SQL statement of data

//在删除数据时，推荐根据id这样的唯一标识，来删除对应的数据。示例如下:
const sqlStr = "delete from users where id=?"
//调用db.query(O)执行SQL语句的同时，为占位符指定具体的值
//注意:如果SQL语句中有多个占位符，则必须使用数组为每个占位符指定具体的值
//如果SQL语句中只有一个占位符，则可以省略数组
db.query(sqlStr, 5, function (err, results) {
    if (err) return console.log(err.message);
    //注意:执行 delete语句之后，结果也是一个对象，也会包含 affectedRows属性
    if (results.affectedRows == 1) {
        console.log("删除成功");
    }
})

Mark deletion situation

//标记删除
//使用DELETE语句，会把真正的把数据从表中删除掉。为了保险起见，推荐使用标记删除的形式，来模拟删除的动作。
//所谓的标记删除，就是在表中设置类似于status这样的状态字段，来标记当前这条数据是否被删除。
//当用户执行了删除的动作时，我们并没有执行DELETE语句把数据删除掉，而是执行了UPDATE语句，将这条数据对应的status字段标记为删除即可。
//标记删除:使用 UPDATE语句替代 DELETE语句;只更新数据的状态，并没有真正删除
const sqlStr = "update users set status=? where id=?"
db.query(sqlStr, [0, 7], function (err, results) {
    if (err) return console.log(err.message);
    if (results.affectedRows == 1) {
        console.log("标记删除成功");
    }
})

Note: The placeholder marking method mentioned in the article has better compatibility. The author has done tests and used native SQL After the statement is spliced ​​into fields, it is directly executed using the db.query statement. As a result, an error is reported when processing rich text data, and character escaping is required. This will not happen when using ? placeholder.

The above is the detailed content of What are the basic operation methods of node.js for database MySQL?. For more information, please follow other related articles on the PHP Chinese website!