one , preparation
1. stay MySQL Workbench Create a new one called my_db_01 Library and users surface
2. In the built library newly build users surface
2.1 This interface will be displayed on the right ：
2.2 Fill in the following data in the table
2.3 Then click the in the lower right corner apply It's ready
two , Import before adding, deleting, modifying and querying the database mysql modular , And testing mysql Is the module working properly
1, download mysql modular
// Open the terminal for writing files Enter the following commands in sequence npm init -y // Project initialization npm i mysql // download mysql modular
2, Establishment and mysql Connection relationship of database
// 2. Establishment and MySQL Connection relationship of database const db = mysql.createPool({ host: '127.0.0.1', //
Database IP address user: 'root', // Account to log in to the database password: 'admin123', // Password to log in to the database
database: 'my_db_01', // Specify which database to operate on })
3, test mysql Can the module work normally
db.query('select 1', (err, results) => { // mysql Error reported during module operation if (err) return
console.log(err.message) // Able to execute successfully SQL sentence console.log(results) })
4. Input in the terminal node Command execution , The successful test is displayed on the terminal ：
[ RowDataPacket { '1': 1 } ]
three , After the test is successful, add, delete, modify and query the database
Add data
// towards users In the table , Add a new piece of data const user = { username: ' Wang Wu ', password: '123456' } //
Define to be executed SQL sentence const sqlStr = 'insert into users (username, password) values (?
?)' // implement SQL sentence db.query(sqlStr, [user.username, user.password], (err,
results) => { // implement SQL The statement failed if (err) return console.log(err.message) // succeed
// be careful ： If yes insert into Insert statement , be results Is an object // Can pass affectedRows
attribute , To determine whether the data is successfully inserted if (results.affectedRows === 1) { console.log(' Insert data successfully !') }else{
console.log(' Insert failed ') } })
After adding successfully , Terminal display ：
stay MySQL Workbench View in users surface ：
Delete data
1. View the data before deletion first
2. delete Id by 6 User
// delete id by 6 User const sqlStr = 'delete from users where id=?'
db.query(sqlStr, 6, (err, results) => { if (err) return
console.log(err.message) // be careful ： implement delete After statement , The result is also an object , Will also include affectedRows attribute if
(results.affectedRows === 1) { console.log(' Data deleted successfully ') } else {
console.log(' Delete failed !!') } })
3. Run command line If the terminal displays that the deletion is successful, it means that the data has been deleted
4. stay MySQL Workbench View in users surface ：
id by 6 Your data has been deleted
Change data ——》 Update data
1. View now users Data in table ：
2. yes id by 7 Data Make modifications
// A convenient way to update data const user = { id: 7, username: ' Pikachu ', password: '123456' } // definition
SQL sentence const sqlStr = 'update users set ? where id=?' // implement SQL sentence
db.query(sqlStr, [user, user.id], (err, results) => { if (err) return
console.log(err.message) if (results.affectedRows === 1) {
console.log(' Update data succeeded ') } })
3.. Terminal input command view
4. stay MySQL Workbench View in users surface ：
id by 7 Your data has been updated
View data
1. Query code
// query users All data in the table const sqlStr = 'select * from users' db.query(sqlStr,
(err, results) => { // Failed to query data if (err) return console.log(err.message) //
Query data succeeded // be careful ： If yes select Query statement , The result of execution is an array console.log(results) })
2. Terminal operation command If the displayed data is displayed in the form of array , Then it proves that we query the data successfully
Point a favor to support it ~
Technology
Daily Recommendation