Let's talk about how node operates the MySQL database (add, delete, modify, check)

How does node operate the MySQL database? The following article will take you through the methods of adding, deleting, modifying and querying the MySQL database in the node project. I hope it will be helpful to you!

Download and install mysql, check whether the installation is successful

---

net start mysql

• Start mysql
• You can right-click My Computer on the desktop and enter Computer Management to check whether mysql has been successfully run [Related tutorial recommendations: nodejs video tutorial]

Download and install navicat

---

Function: Provide us with the function of connecting and operating mysql database

Download

##www.navicat.com.cn/products#na…

Installation

Double-click, all the way to next

Use

to find the application, click to start

If the connection test passes, continue When you get down, you can click the OK button to officially connect to mysql.

The effect after connecting is as follows:

Database Introduction

---

What is a database

English:

database The warehouse that saves and manages data is the database.

What is data? Files, pictures, videos, orders, usernames, passwords and more.

These data need a special place to save and manage.

Before we learned database technology, the data we used were all saved in the file system (db.json). We need a

specialized software to manage our data, which is a database.

Two camps-database classification

##Relational database, representative product:

• MySQL
  • Oracle
  • Sql server
  • DB2
non Relational database

• redis
  • Key-value storage databaseHBaise column storage database
  mongodb
  • Document-oriented databaseneo4j graph database
  • Elasticsearch search engine storage
Reference: Database usage ranking

db-engines.com/en/ranking

Understanding relational database

In a relational database, there are three-level relationships:

Database

• Data table
• Field
Analogy

excel

:

Each column is a type of data---

Field
• Each row represents a piece of data---
Record

Database##Databaseexcel fileData tableA certain sheet in the excel fileTable structure: FieldIn the sheet Header: Column

MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品 。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

• 体积小、速度快、总体拥有成本低，一般中小型网站的开发都选择 MySQL 作为网站数据库。
• 搭配 PHP 和 Apache 可组成良好的开发环境。

3p技术：php,asp,jsp

操作数据库-用navicat

---

新建数据库

在弹出的窗口中填写数据库名即可。

点击确定之后，会在左侧的数据库列中看到上面创建成功的数据库。

新建数据表

点击 "新建表"， 然后在开始设置字段

主键: 作用是区别一条数据和其它数据。（它相当于人的身份证号）

设置字段完成之后，点击上图左上角所示的保存按钮，就会进一步弹出对话框，让填写表的名字。

我们填入user

编辑字段

添加数据

一条内容输入完成后，按下tab，会自动进入下一条记录的输入

学习使用SQL语句

---

结构化查询语言(Structured Query Language)简称SQL，用来操作关系型数据库：

• 是一种数据库查询和程序设计语言，用来存取数据以及查询、更新、和管理关系型数据库。
• .sql是数据库脚本文件的扩展名。

最常用的用于数据操作的sql语句有四类，分别对应对数据的四种操作：

• 增(create）(例如：用户注册)
• 删(delete) （例如：删除订单）
• 改(update) （例如：修改密码）
• 查(select , read) （例如：信息搜索）

在navicat中运行sql - 添加数据

---

打开sql编辑区

然后：

学习 sql中的insert into 命令

格式:

 insert into 表名(字段名1，字段名2,....)  values (值1，值2，....)

注意:

• 字段的顺序要和值的顺序是完全匹配的

• 字段列表可以不与真实数据表中的字段完全相等，

  • 可以省略一些不必要的字段
  • 顺序与不需要与定义表时的顺序一致

• 如果是字符串类型的字段，其值要加""，如果是数值类型的字符串，其值不需要加“”

示例:

insert into stu (sex, weight, name) values ('男', 60, '庞凯')

sql-delete语句-删除数据

---

格式

 delete  from 表名  where 删除条件复制代码

注意：不指定条件将删除所有数据

示例

-- 删除id为14的同学
delete from stu where id=14

-- 删除的时候，不加条件，将删除stu表中的全部记录
delete from stu

sql-update语句-修改数据

---

格式

update 表名 set 字段1=值1, 字段2=值2,...  where 修改条件

注意：

- 要修改的值使用键值对来表示 
- 多个字段用,分隔
- 不指定条件，将修改当前表中全部的记录

示例

-- 修改id为1的同学的年龄为53
update stu set age=53 where id = 1

-- 修改id为1的同学的年龄为35，身高为160
update stu set age=35,height=160 where id = 1

-- 如果修改的时候，不加条件，则会修改全部的数据
update stu set weight = 60

sql-select-语句-数据查询

---

作用

把数据从数据库查出来

格式

SELECT  字段名1, 字段名2, .....  FROM 表名  WHERE <条件表达式>

示例

# 查询部分字段SELECT id,name,age FROM stu
# 查询所有字段SELECT * FROM stu
# 带条件的查询SELECT * FROM 表名 WHERE 条件1 and 条件2

where子句

---

select field1, field2... from 表名 查询表中的所有数据

where 可以使用条件来筛选查询出的结果

-- 查询所有的学生
select * from stu

-- 查询所有学生的id，name，height
select id,name,height from stu

-- 带条件的查询
select * from stu where 条件

-- 查询所有的男同学
select * from stu where sex='男'

-- 查询id为2的男同学
select * from stu where id=2

-- 查询年龄大于50的同学
select * from stu where age > 50

-- 查询年龄大于50岁的男同学
select * from stu where age>50 and sex='男'

-- 查询年龄在30~60之间的同学，包括30和60
select * from stu where age>=30 and age<=60
select * from stu where age between 30 and 60

node.js操作mysql

---

通过mysql这个包来操作mysql数据库。

安装包

mysql模块是一个第三方模块，专门用来操作MySQL数据库。

# 安装
npm i mysql

参考：www.npmjs.com/package/mys…

使用步骤

要想用这个包连接数据库，首先要确保在电脑有mysql（phpstudy 还要启动mysql服务）

一共需要4个步骤：

• 加载 MySQL 模块

• 创建 MySQL 连接对象

• 连接 MySQL 服务器

• 执行SQL语句

var mysql = require('mysql');

var connection = mysql.createConnection({
  host     : 'localhost',   // 你要连接的数据库服务器的地址
  port     : 3306,// 端口号
  user     : 'root',        // 连接数据库服务器需要的用户名
  password : 'root',        // 连接数据库服务器需要的密码
  database : 'gz61'      //你要连接的数据库的名字
});

connection.connect((err) => {
  // 如果有错误对象，表示连接失败
  if (err) return console.log('数据库连接失败')
  // 没有错误对象提示连接成功
  console.log('mysql数据库连接成功')
});

参考地址： www.npmjs.com/package/mys…

node操作mysql-查询操作

---

执行查询类型的SQL语句，查询结果（result）是一个数组，每个单元是对象，对象的属性是数据表的字段名。

// 1. 加载mysql
const mysql = require('mysql');
// 2. 创建连接对象
const conn = mysql.createConnection({
    // 对象的属性名字不能改变
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root',
    database: 'gz61'
});
// 3. 连接到MySQL服务器
connection.connect((err) => {
  // 如果有错误对象，表示连接失败
  if (err) return console.log('数据库连接失败')
  // 没有错误对象提示连接成功
  console.log('mysql数据库连接成功')
});
// 4. 执行SQL语句
let sql = 'select id,name,age from stu';
connection.query(sql, (err, result, fields) => {
    if (err) throw err; // throw err 相当于 return console.log(err);
    console.log(result); // result就是查询结果
});

node操作mysql-添加操作

---

执行添加类型的SQL语句，查询结果（result）是一个对象，该对象中有两个属性要关注：

• affectedRows： 受影响行数
• insertID： 查询数据的主键值

// 1. 加载mysql
const mysql = require('mysql');
// 2. 创建连接对象
const conn = mysql.createConnection({
    // 对象的属性名字不能改变
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root',
    database: 'gz61'
});
// 3. 连接到MySQL服务器
connection.connect((err) => {
  // 如果有错误对象，表示连接失败
  if (err) return console.log('数据库连接失败')
  // 没有错误对象提示连接成功
  console.log('mysql数据库连接成功')
});

let sql = 'insert into users (name,password) values("小王","snv")'
connection.query(sql, (err, result) => {
    if (result.affectedRows > 0) {
        console.log('添加成功，新数据的id为：' + result.insertId);
    } else {
        console.log('添加失败');
    }
});

node操作mysql-修改操作

---

执行修改类型的SQL语句，查询结果（result）是一个对象，该对象中有 affectedRows 属性，表示本次修改操作影响到的行数。

// 1. 加载mysql
const mysql = require('mysql');
// 2. 创建连接对象
const conn = mysql.createConnection({
    // 对象的属性名字不能改变
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root',
    database: 'gz61'
});
// 3. 连接到MySQL服务器
connection.connect((err) => {
  // 如果有错误对象，表示连接失败
  if (err) return console.log('数据库连接失败')
  // 没有错误对象提示连接成功
  console.log('mysql数据库连接成功')
});
// 更新
// update stu set 字段=值,字段=值 where id=11
let sql = 'update users set password="123" where name="小王"';

conn.query(sql, (err, result) => {
    if (err) throw err;
    if (result.affectedRows > 0) {
        console.log('修改成功');
    } else {
        console.log('修改失败');
    }
})

node操作mysql-删除操作

---

执行删除类型的SQL语句，查询结果（result）是一个对象，该对象中有 affectedRows 属性

// 1. 加载mysql
const mysql = require('mysql');
// 2. 创建连接对象
const conn = mysql.createConnection({
    // 对象的属性名字不能改变
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root',
    database: 'gz61'
});
// 3. 连接到MySQL服务器
connection.connect((err) => {
  // 如果有错误对象，表示连接失败
  if (err) return console.log('数据库连接失败')
  // 没有错误对象提示连接成功
  console.log('mysql数据库连接成功')
});
// 删除
let sql = 'delete from stu where id=1';

connection.query(sql,(err, result) => {
    if (err) throw err;
    if (result.affectedRows > 0) {
        console.log('删除成功');
    } else {
        console.log('删除失败');
    }
});

了解一下软删除

---

做删除 ： delete from 表名 条件 会把数据直接从数据库中删除掉！

思路：

• 不是真的删除，而是设置一个特殊的字段表示当前的状态：正常还是已经删除

# 目标：把id=16的软删除掉
update stu set isDelete=1 where id=16

模块化封装

---

分析上面几个单独的功能点，它们基本的语法格式是一致的，只是要执行的sql语句不同而已，所以，我们可以对它们进行一个简单的封装。然后再写测试文件对其进行测试。

涉及两个文件：

• sql.js
• sqltest.js

封装模块

模块名：sql.js

// 由于四项（insert,delete,update,select）操作只是sql语句不同

// 1. 加载mysql
const mysql = require('mysql');
// 2. 创建连接对象
const conn = mysql.createConnection({
    // 对象的属性名字不能改变
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root',
    database: 'gz61'
});
// 3. 连接到MySQL服务器
connection.connect((err) => {
  // 如果有错误对象，表示连接失败
  if (err) return console.log('数据库连接失败')
  // 没有错误对象提示连接成功
  console.log('mysql数据库连接成功')
});

module.exports = connection

测试

sqltest.js

const conn = require('./sql');

conn.query('select * from users where username="小美1" and userpassword="666"', (err, data) => {
  console.log(err);
  console.log(data);
  if (data.length > 0) {
    console.log('用户名密码Ok');
  } else {
    console.log('用户名密码error');
  }
});

更多node相关知识，请访问：nodejs 教程！

The above is the detailed content of Let's talk about how node operates the MySQL database (add, delete, modify, check). For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Show More

Hot Article

How to fix KB5055523 fails to install in Windows 11?

3 weeks ago By DDD

How to fix KB5055518 fails to install in Windows 10?

3 weeks ago By DDD

Roblox: Dead Rails - How To Tame Wolves

4 weeks ago By DDD

Strength Levels for Every Enemy & Monster in R.E.P.O.

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Roblox: Grow A Garden - Complete Mutation Guide

2 weeks ago By DDD

Show More

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Show More

Hot Topics

Java Tutorial

1655

14

CakePHP Tutorial

1414

52

Laravel Tutorial

1307

25

PHP Tutorial

1254

29

C# Tutorial

1228

24

Show More

Related knowledge

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

Oracle's Role in the Business World Apr 23, 2025 am 12:01 AM

Oracle is not only a database company, but also a leader in cloud computing and ERP systems. 1. Oracle provides comprehensive solutions from database to cloud services and ERP systems. 2. OracleCloud challenges AWS and Azure, providing IaaS, PaaS and SaaS services. 3. Oracle's ERP systems such as E-BusinessSuite and FusionApplications help enterprises optimize operations.

MySQL for Beginners: Getting Started with Database Management Apr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

MySQL: Structured Data and Relational Databases Apr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

See all articles

Public welfare online PHP training，Help PHP learners grow quickly！

About us Disclaimer Sitemap

© php.cn All rights reserved

excel file