node.js不能连接mysqlNodejs连接mysql的增删改

导读一，创建数据库SourceDatabase:my_news_testSETFOREIGN_KEY_CHECKS=0;--------------------....

一，创建数据库

Source Database : my_news_testSET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for node_user-- ----------------------------DROP TABLE IF EXISTS `node_user`;CREATE TABLE `node_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `age` int(8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ------------------------------ Records of node_user-- ----------------------------INSERT INTO `node_user` VALUES (1, admin, 32);INSERT INTO `node_user` VALUES (2, dans88, 45);INSERT INTO `node_user` VALUES (3, 张三, 35);INSERT INTO `node_user` VALUES (4, ABCDEF, 88);INSERT INTO `node_user` VALUES (5, 李小二, 65);

二，连接

var connection = mysql.createConnection({ host : 127.0.0.1, user : root, password : root123, port: 3306, database: my_news_test,});

三，简单查询

注意要记得安装node_mysql,直接使用npm install mysql

//mysql.js

//首先需要安装nodejs 的mysql包

//npm install mysql

//编写nodejs与mysql交互的代码

var mysql = require(mysql); var TEST_DATABASE = my_news_test; var TEST_TABLE = node_user; //创建连接 var client = mysql.createConnection({ host:127.0.0.1, user: root, password: root123, database: my_news_test,});client.connect();client.query("use " TEST_DATABASE);client.query( SELECT * FROM TEST_TABLE, function selectCb(err, results, fields) { if (err) { throw err; } if(results) { for(var i = 0; i < results.length; i ) { console.log("%d\t%s\t%s", results[i].id, results[i].name, results[i].age); } } client.end(); } );

四、Node.js结合MySQL的增、删、改、查操作

1、增

var mysql = require(mysql); var connection = mysql.createConnection({ host : 127.0.0.1, user : root, password : root123, port: 3306, database: my_news_test,}); connection.connect(); var userAddSql = INSERT INTO node_user(id,name,age) VALUES(0,?,?);var userAddSql_Params = [Wilson, 55];//增 addconnection.query(userAddSql,userAddSql_Params,function (err, result) { if(err){ console.log([INSERT ERROR] - ,err.message); return; } console.log(-------INSERT----------); //console.log(INSERT ID:,result.insertId); console.log(INSERT ID:,result); console.log(#######################); });connection.end();

2、改

var mysql = require(mysql); var connection = mysql.createConnection({ host : 127.0.0.1, user : root, password : root123, port: 3306, database: my_news_test,}); connection.connect(); var userModSql = UPDATE node_user SET name = ?,age = ? WHERE id = ?;var userModSql_Params = [Hello World,99,7];//改 upconnection.query(userModSql,userModSql_Params,function (err, result) { if(err){ console.log([UPDATE ERROR] - ,err.message); return; } console.log(----------UPDATE-------------); console.log(UPDATE affectedRows,result.affectedRows); console.log(******************************);}); connection.end();

3、查操作

var mysql = require(mysql); var connection = mysql.createConnection({ host : 127.0.0.1, user : root, password : root123, port: 3306, database: my_news_test,}); connection.connect(); var userGetSql = SELECT * FROM node_user;//查 queryconnection.query(userGetSql,function (err, result) { if(err){ console.log([SELECT ERROR] - ,err.message); return; } console.log(---------------SELECT----------------); console.log(result); console.log(&&&&&&&&&&&&&&&&&&&&&&&&&&&&&); }); connection.end();

4、删除操作

var mysql = require(mysql); var connection = mysql.createConnection({ host : 127.0.0.1, user : root, password : root123, port: 3306, database: my_news_test,}); connection.connect(); var userDelSql = DELETE FROM node_user WHERE id = 7;//ɾconnection.query(userDelSql,function (err, result) { if(err){ console.log([DELETE ERROR] - ,err.message); return; } console.log(-------------DELETE--------------); console.log(DELETE affectedRows,result.affectedRows); console.log(&&&&&&&&&&&&&&&&&); }); connection.end();