Summary of basic operation examples for database MySQL [add, delete, modify and search]

#Query all data in the entire tableselect * from users

#Query all data in the specified columnselect username,password from users

#Specify a column to add datainsert into users(username,password) values('Xiao Ji','1234')

#Specify a column to modify dataupdate users set username="Hello a",password="1234567",status=1 where id=2

#Delete rows according to iddelete from users where id=4

#Query all users whose status is 1SELECT *FROM users WHERE status=1

#Query all users with id greater than 2SELECT *FROM users WHERE id>2

#Query all users whose username does not equal adminSELECT *FROM users WHERE username<>'admin'

#Use AND to display all users whose status is 0 and whose id is less than 3:SELECT * FROM users WHERE status=0 AND id<3

#Use OR to display all users whose status is 1 or username is zsSELECT* FROM users WHERE status=1 OR username='zs'

#Sort the data in the users table ascending order according to the status fieldSELECT * FROM users ORDER BY status;（Sort ascending order instatusAdd afterwardsASCThe effect is equivalent）
select * from users order by status asc

#Sort according to id descending order, use desc keywordsselect * from users order by id desc

#Multiple sorting For the data in the users table, first sort in descending order according to the status field, and then sort in ascending order according to the alphabetical order of usernameSELECT * FROM users ORDER BY status DESC,username asc

#Query the total number of digits returned by data with id 1select count(*) from users where id=1

#Modify the column name from COUNT(*) to totalSELECT COUNT(*) AS total FROM users WHERE id=1

#Add an uname alias to the username column and an upwd alias to the password columnselect username as uname,password as upwd from users

npm init -y  (File name is English，No spaces、Special characters or Chinese，Otherwise, an error will be reported)

npm install mysql   or  npm i mysql

//Import MySQL moduleconst mysql = require("mysql")
// Establish a connection to MySQL databaseconst db = ({
    host: "127.0.0.1", //The IP address of the database    user: "root",  //Login the database account    password: "admin",  //Login the password for the database    database: "xiaoji"  //Specify which database to operate on})

("select 1", function (err, results) {
    //The module reports an error message to return    if (err) return ();
    //Successful    (results);
})

Query datauserAll user data in the table
const sqlStr = "select * from users"
(sqlStr, function (err, results) {
    //The query failed    if (err) return ();
    //The query is successful    //Note that if the execution is a select query statement, the execution result is an array    (results);
})

//Insert data//Add new data to the users table, where username is Spider-Man and password is pcc321//The data object to be inserted into the users tableconst user = { username: "Spider-Man", password: "pcc321" }
//The SQL statement to be executed, among them?  Indicates placeholdersconst sqlStr = "insert into student(student,card) values(?,?)"
//Use the form of an array, in order?  Placeholder specific value (the number of rows affected)(sqlStr, [, ], function (err, results) {
    if (err) return ();
    if ( == 1) {
        ("Insert successfully");
    }
})
// When adding new data to the table, if each attribute of the data object corresponds one by one to the field of the data table, you can quickly insert data in the following way://The data object to be inserted into the users tableconst user = { username: "Spider2-Man", password: "pcc321" }
//The SQL statement to be executed, among them?  Indicates placeholdersconst sqlStr = "insert into users set ?"
(sqlStr, user, function (err, results) {
    if (err) return ();
    if ( == 1) {
        ("Insert successfully");
    }
})

//Modify the data in the table//The data updated to the users table, where username is Spider-Man, password is pcc321, and id is 5const user = { id: 7, username: "xiao1jiao", password: "111222" }
//The sql statement to be executed, among them?  Indicates placeholdersconst sqlStr = "update users set username=?,password=? where id=?"
//Use the form of an array, in order?  Placeholder specific value (the number of rows affected)(sqlStr, [, , ], function (err, results) {
    if (err) return ();
    if ( == 1) {
        ("Revise", , "Column Success");
    }
})
//When modifying table data, if each attribute of the data object corresponds one by one to the field of the data table, you can quickly modify the table data by following the following method//The data updated to the users table, where username is aaaa, password is 1111, and id is 5const user = { id: 5, username: "aaaa", password: "1111" }
//The sql statement to be executed, among them?  Indicates placeholdersconst sqlStr = "update users set ? where id=?"
//Use the form of an array, in order?  Placeholder specific value (the number of rows affected)(sqlStr, [user, ], function (err, results) {
if (err) return ();
if ( == 1) {
    ("Revise", , "Column Success");
}
})

//When deleting data, it is recommended to delete the corresponding data based on a unique identifier such as id.  Examples are as follows:const sqlStr = "delete from users where id=?"
//While calling (O) to execute SQL statements, specify specific values ​​for the placeholder//Note: If there are multiple placeholders in the SQL statement, you must use an array to specify specific values ​​for each placeholder//If there is only one placeholder in the SQL statement, the array can be omitted(sqlStr, 5, function (err, results) {
    if (err) return ();
    //Note: After executing the delete statement, the result is also an object, which will also contain affectedRows attributes    if ( == 1) {
        ("Delete successfully");
    }
})

//Tag Delete//Using the DELETE statement will delete the real data from the table.  To be on the safe side, it is recommended to use the form of mark deletion to simulate the deletion action.//The so-called mark deletion means setting a status field similar to status in the table to mark whether the current data has been deleted.//When the user performs the delete action, we do not execute the DELETE statement to delete the data, but execute the UPDATE statement and mark the status field corresponding to this data as deleted.//Tag Deletion: Use the UPDATE statement instead of the DELETE statement; only update the status of the data, and does not really delete itconst sqlStr = "update users set status=? where id=?"
(sqlStr, [0, 7], function (err, results) {
    if (err) return ();
    if ( == 1) {
        ("Tag delete successfully");
    }
})