接着教程二
进入myApp项目下新建文件mysql.js并键入如下代码：
- 创建
var mysql = require('mysql'); //调用MySQL模块
//创建一个connection
var connection = mysql.createConnection({
connectionLimit: 50,
host: 'localhost',
user: 'root',
password: '123',
database: 'nodesample'
});
//创建一个connection
connection.connect(function(err){
if(err){
console.log('[query] - :'+err);
return;
}
console.log('[connection connect] succeed!');
});
//执行SQL语句
connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
if (err) {
console.log('[query] - :'+err);
return;
}
console.log('The solution is: ', rows[0].solution);
});
//关闭connection
connection.end(function(err){
if(err){
return;
}
console.log('[connection end] succeed!');
});
确保安装了mysql模块的情况下（npm install mysql --save），接着执行命令
node mysql
结果返回：
image
- 增加
这里需要打开mysql链接数据库新建一个表名为mytable的数据表，表结构如下：
、、、、、、、、、、、、、
| id | name | age | address |其中id为主键。将mysql.js里面修改为如下并在命令行执行命令node mysql：
var mysql = require('mysql');
var connection = mysql.createConnection({
connectionLimit: 50,
host: 'localhost',
user: 'root',
password: '123',
database: 'nodesample'
});
connection.connect();
var addVip = 'insert into mytable(name, age, address) values(?,?,?)';
var param = ['测试',100,'测试'];
connection.query(addVip, param, function(error, result){
if(error)
{
console.log(error.message);
}else{
console.log('insert id: '+result.insertId);
}
});
connection.end();
结果返回 ：
image
刷新数据库查看已经增加了一条数据：
image
- 删除
将mysql.js文件修改为如下并在命令行执行node mysql
var mysql = require('mysql');
var connection = mysql.createConnection({
connectionLimit: 50,
host: 'localhost',
user: 'root',
password: '123',
database: 'nodesample'
});
connection.connect();
var addVip = 'delete from mytable where id = 2';
connection.query(addVip, function(error, result){
if(error)
{
console.log(error.message);
}else{
console.log('affectedRows: '+result.affectedRows);
}
});
connection.end();
返回结果如下：
image
image
- 查找
将mysql.js文件修改为如下并在命令行执行node mysql
ql = require("mysql");
var connection = mysql.createConnection({
connectionLimit: 50,
host: 'localhost',
user: 'root',
password: '123',
database: 'nodesample'
});
connection.connect();
connection.query('select * from mytable', function(error, results, fields){
if (error) {
throw error;
}
if (results) {
for(var i = 0; i < results.length; i++)
{
console.log('%s\t%s',results[i].name,results[i].id);
}
}
});
connection.end();
结果返回如下：
image
image
- 修改
将mysql.js文件修改为如下并在命令行执行node mysql
= require('mysql');
var connection = mysql.createConnection({
connectionLimit: 50,
host: 'localhost',
user: 'root',
password: '123',
database: 'nodesample'
});
connection.connect();
var userSql = "update mytable set age = age-1 where id = ?";
var param = [1000, 2];
connection.query(userSql, param, function (error, result) {
if(error)
{
console.log(error.message);
}else{
console.log('affectedRows: '+result.affectedRows);
}
});
connection.end();
结果返回如下
image
image
image
