Nodejs uses mysql

1. Install

npm install mysql
Copy the code

2. The import

const mysql = require('mysql')
Copy the code

3. Write the matching entries in the database

// Database configuration options
const options = {
    host: 'localhost'./ / host name
    user: 'root'./ / user
    password: '123456'./ / password
    port: 3306./ / the port number
    database: 'student'// The database to be operated
}
Copy the code

For details about parameter configuration, see

4. Link the instance to the database object

// Create the connected database object
const objCon = mysql.createConnection(options)
Copy the code

5. Connect to the database

// Connect to the database
objCon.connect((a)= > {
    console.log('Connection successful')})Copy the code

6. The query

// Form is followed by the name of the table
const  selectSql = 'select * from student';
/ / query
objCon.query(selectSql,function (err, result) {
        if(err){
          console.log(err.message);
          return;
        }
       console.log(result);
});
Copy the code

Delete the table

/ / drop table form
// Delete the table
const dropSql = 'drop table student'

objCon.query(dropSql, (err, result) => {
    if(err) {
        console.log(err)
    }else {
        console.log(result)
    }
})
Copy the code

8. Delete the database

// Drop Database Name of the database
// Delete the database
const dropDataSql = 'drop database zh'

objCon.query(dropDataSql, (err, result) => {
    if(err) {
        console.log(err)
    }else {
        console.log(result)
    }
})
Copy the code

9. Add a database

//create database Name of the database
// Add the database
const createDataSql = 'create database zh'

objCon.query(createDataSql, (err, result) => {
    if(err) {
        console.log(err)
    }else {
        console.log(result)
    }
})
Copy the code

10. Add the table

// Create a new table and assign the SQL preview code to it
/ / add the table
const createSql = "CREATE TABLE user(id int AUTO_INCREMENT,username VARCHAR(255),password VARCHAR(255),PRIMARY KEY(id))"

objCon.query(createSql, (err, result) => {
	if (err) {
			console.log(err)
	} else {
			console.log(result)
	}
})

objCon.query(createSql, (err, result) => {
    if(err) {
        console.log(err)
    }else {
        console.log(result)
    }
})
Copy the code

11. Add

Insert into students(id, name, gender) values(3, "zheng", "male"
/ / way
const insertSql = Insert into students(id, name, gender) values(3, "zheng", "male ")

objCon.query(insertSql, (err, result) => {
    if(err) {
        console.log(err)
    }else {
        console.log(result)
    }
})

2 / / way
//insert into students(id, name, gender) values(? ,? ,?) Will the parameter use? Instead, then write the data (as an array) to the second parameter of query
const insertSql = 'insert into students(id, name, gender) values(? ,? ,?) '

objCon.query(insertSql, [4.'jcl'.'male'], (err, result) => {
    if(err) {
        console.log(err)
    }else {
        console.log(result)
    }
})
Copy the code