利用node对数据实现增删改查

1.连接数据库

var mysql = require('mysql')
var connection = mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'*****',
    database:'user',
    port:3306,//可选可不选，默认端口3306
});
//建立连接
connection.connect((err) => {
    if(err){
        console.log('连接失败' +err);
    }else{
        console.log('数据库连接成功!');
    }
});

2.创建库

let sqlGetup = 'create database testadd'; //创建testadd库

connection.query(sqlGetup,(err,results) => {
    if(err) throw err;//错误信息
    console.log(results); //结果
    console.log('创建库成功!');
})

3.创建表

let sqlGetup2 = `CREATE TABLE user.user (
     id int NOT NULL AUTO_INCREMENT,
     name varchar(255) NULL,
     pwd varchar(255) NULL,
     PRIMARY KEY (id)
     );`
    
    connection.query(sqlGetup2, (err, results) => {
      if (err) throw err; //错误信息
      console.log(results); //结果
      console.log('创建表成功!');
    });

4.插入数据

let sqlAdd = 'insert into user (id,name,pwd) values (3,"admin","123456")';

connection.query(sqlAdd, (err, results) => {
    if (err) throw err; //错误信息
    console.log(results); //结果
    console.log('插入数据成功!');
});

5.删除表

let sqlDel = 'drop table userlist';//删除test1表

connection.query(sqlDel, (err, results) => {
    if (err) throw err; //错误信息
    console.log('删除表成功!'+results); //结果
});

let delSql = 'delete from user where id = 3'; //删除user表id为3的数据

connection.query(delSql,(err,results) => {
    if(err) throw err;//错误信息
    console.log(results);
    console.log('删除数据成功')
})

6.更新表

let sqlUpdate = 'update user set name = ? ,pwd = ? where id = ?';
let updateTxt = ['hjg','222222',1];

connection.query(sqlUpdate,updateTxt, (err, results) => {
    if (err) throw err; //错误信息
    console.log(results); //结果
    console.log('更新数据成功!');
});

7.查询表数据

let sqlQuery = 'select * from user'; //查询所有内容从user表中

connection.query(sqlQuery, (err, results, fields) => {

 if (err) throw err; //错误信息
 console.log(results); //查询结果
 console.log(fields); //字段信息
    
});