Steps to operate MYSQL in your project

  • 1. Install the third-party mysql database module (mysql)
  • 2. Connect to the mysql database through the mysql module
  • 3. Use the mysql module to execute mysql statements

NPM I mysql Install the mysql module

/ / import mysql
cons mysql=require('mysql');
// create a link with the Mysql library
const db=mysql.createPool({
    host:'127.0.0.1'.// Database IP address
    user:'root', the account for logging in to the databasepassword:'admin123', password for logging in to the databasedatabase:'my_db_01'// Specify which database to operate on
})
Copy the code

Query data

const sqlStr="select* from users";// Query all data in the database

db.query(sqlStr,(err,result) = >{
    // Failed to query data
    if(err) return console.log('Failed to query information')
    // Data query succeeded
    // Note: If you execute a select query, the result is data
    console.log(result)
})
Copy the code

Insert statement

Add data to the user table where the username is spider-man and the password is 123456789

const user={username:'spider-man'.password:123456789};
SQL statement to be executed, where English? Represents a placeholder
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(result.affectedRows===1) {console.log('Inserted successfully')}})Copy the code

Convenient Way to Insert Data When adding data to a table, you can insert data in either of the following ways if each attribute of a data object corresponds to a field in the table:

const user={username:'spider-man'.password:123456789};
// SQL statement to be executed
const sqlStr="inser into users set ?";
db.query(sqlStr,user,(err,results) = >{
    if(err) return console.log(err.message)
    
    if(results.affectedRows===1) {console.log('Inserted successfully')}})Copy the code

Update user information

const user={username:'jack'.password:123456}
// SQL statement to be executed
const sqlStr="update users set username=? ,password=? where id=?"
// Execute the SQL statement
db.query(sqlStr,[user.username,user.password],(err,results) = >{
       if(err) return console.log(err.message)
       
       if(results.affectdRows===1) {console.log('User information updated successfully')}})Copy the code

When updating a data table, if each property of the data object corresponds to the field of the data table one by one, you can update the data table by casting:

const user={username:'jack'.password:'123456'}
const sqlStr='update users set ? where id=? ';
db.query(sqlStr,user,(err,results) = >{
    if(err) return console.log(err.message)
    
    if(results.affectedRows===1){
        consoel.log('User information updated successfully')}})Copy the code

Delete the data

When deleting data, delete the corresponding data according to the unique identifier such as the recommendation ID

// The SQL statement to execute
const sqlStr='delete from user where id=? ';
db.query(sqlStr,6.(err,result) = >{
 if(err) return console.log(err.message)
 if(result.affectedRow===1){
   consoel.log('User deleted successfully')}})Copy the code

Tag deletion Using the DELETE statement deletes the entire statement from the table. To be on the safe side, it is recommended to use tag deletion to simulate deletion

Tag deletion is the setting of a field such as Status in the table to indicate whether the current data has been deleted

When the user performs the deletion action, we do not execute the delete statement to delete the data, but execute the UPDATE statement to mark the status field corresponding to the data as deleted

db.query('update users set status=1where id=? '.6.(err,results) = >{
  if(err) return console.log(err.message)
  
  if(result.affectedRow===1){
      consoel.log('Deleted successfully')}})Copy the code