Database programming (MySQL) of node, adding, deleting, modifying and querying

Catalog

Operate in the project MySQL Database steps

Perform database operations

Query data

insert data

Modifying data

Delete data  

Operate in the project MySQL Database steps

1、 Installation operation MySQL Third party module of database （mysql）   

mysql Modules are hosted in npm Third party modules on . It is provided in Node.js Connection and operation in the project MySQL Database capabilities . Want to use it in a project , You need to run the following command first , take mysql Install as a dependent package for the project ： npm install mysql 

2、 adopt mysql The module is connected to MySQL database

To configure mysql modular

// introduce mysql2 modular 
const mysql = require('mysql2');
//2、 Create a connection to the database 
const connection = mysql.createConnection({
    host:'localhost',   // Database server address 
    port:3306,    //mysql The port number of the database 
    user:'root',  // Connect mysql Username 
    password:'******',  // Connect mysql Password 
    database:'dbms'   // Database to connect to 
})

3、 adopt mysql Module execution SQL sentence

Perform database operations

In my database dbms There is one in student The table is ：

Query data

A、 Common query ：query(sql, Callback function )

B、 Conditions of the query ：query(sqlString,values, Callback function ), Parameters values The corresponding is sqlString Placeholder in

Example ： Inquire about student In the table cno by 3 Student information （? Represents a placeholder ）

// Query the data of tables in the database , In the callback function err Parameters store information about query errors , If the query is successful result The result set of the query is placed in the parameter 
// Conditions of the query 
connection.query({
    sql:'select * from student where cno = ?',
    values:['3']
},(err,result)=>{
    if(err){
        console.log(err);
        return;
    }
    console.log(result);
})

// Common query 
connection.query('select * from indent',(err,result)=>{
    if(err){
        console.log(err);
        return;
    }
    console.log(result);
})

  The result of condition query is ：

insert data

Insert into Table name ( Name …) values( value …)

Example ： To watch student Insert a row of data into

var addsql = "insert into stu(sno,cno,sname,sgender,sage,saddress) values(?,?,?,?,?,?)";
var addsql_params = ['12','3',' Liu bei ',' male ','29',' shaanxi '];
connection.query({
    sql:addsql,
    values:addsql_params
},(err,result)=>{
    if(err){
        console.log(err);
        return;
    }
    console.log(' The insertion result is ：'+result);
})

The result after insertion is ：

Modifying data

Update Table name set Name = value ,… where Conditions

Be careful ： No addition where The condition will update all the data

Example ： Change the name in the data just added to ‘ Balala little devil fairy ’, Change the age to 27

var updatesql = "update student set sname=?,sage=? where sno=?";
var updatesql_params = [' Balala little devil fairy ',27,'12'];
connection.query({
    sql:updatesql,
    values:updatesql_params
},(err,result)=>{
    console.log(" Rows affected ："+result.affectedRows);
    console.log(" Number of rows changed ："+result.changedRows);
})

The result of the modification ：

Delete data  

Delete from Table name [where Conditions ]

Example ： Delete the just added sno by 12 Student information

var delsql = "delete from student where sno=?";
connection.query(delsql,'12',(err,result)=>{
    if(err){
        console.log(err);
        return;
    }
    console.log(" Number of rows deleted ："+result.affectedRows);
})

Results after deletion ：

  Be careful ： prevent sql Injection attack method ：

Injection attack ： If someone intercepts and adds a statement that is always satisfied when adding, deleting, modifying, and querying statements, all data information will be queried

select from stu where Sid=’S_1001’ ( Injection attack, such as adding or 1=1)

（1） Use placeholders ：？（ Precompile ahead of time ）

（2） Use connection.escape([ Parameter fields ]) Escape the value