nodejs 连接 MySQL(增删改查)

MySQL 增删改查

• 创建删除数据库 create/drop database xxx;
• 创建表 create table xxx(id int primary key,name varchar(8));
• 删除表 deop table xxx;
• 查询数据 select * from xxx or select * from xxx where id = 1;
• 插入数据 insert into xxx(id,name) value (xx,xx) or insert into xxx set xxx
• 条件删除数据 delete from xxx where id = 1;
• 修改数据 update xxx set name=1,password=2 where id = 1;

nodejs 连接 MySQL

var express = require('express');
var mysql = require('mysql');
var bodyParser = require('body-parser');
var connection = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: 'vaegin',
	database: 'test'
})
connection.connect();

开启端口服务
var server = app.listen(3000, function() {
	var host = server.address().address
	var port = server.address().port
	// console.log("应用实例，访问地址为 http://%s:%s", host, port)
})

//设置允许跨域访问该服务.
app.all('*', function(req, res, next) {
	res.header('Access-Control-Allow-Origin', '*');
	res.header('Access-Control-Allow-Headers', 'Content-Type');
	res.header('Access-Control-Allow-Methods', '*');
	res.header('Content-Type', 'application/json;charset=utf-8');
	next();
});
//需要use的
app.use(bodyParser.json()); // for parsing application/json
app.use(bodyParser.urlencoded({
	extended: true
})); // for parsing application/x-www-form-urlencoded
// 请求静态资源
app.use(express.static(path.join(__dirname, 'public'))); //加载public 里面的资源

// 请求列表
app.get('/list', function(req, res) {
	var sql = 'SELECT * FROM student';
	connection.query(sql, function(err, result) {
		if (err) {
			console.log('[SELECT ERROR] - ', err.message);
			return;
		}
		res.json(result)
	});
})

// 插入数据
app.post('/insert', function(req, res) {
	console.log(req.body);
	let {
		name,
		password
	} = req.body;
	var post = {
		name: name,
		password: password,
	}
	var sql = `insert into student set ?`;
	console.log(sql);
	connection.query(sql, post,function(err, result) {
		if (err) {
			console.log('[SELECT ERROR] - ', err.message);
			res.json({msg: '添加失败'})
			return;
		}
	});
	res.json({msg: '添加成功'})
})

// 更新数据
app.post('/update', function(req, res) {
	console.log(req.body);
	let {
		id,
		name,
		password
	} = req.body;
	var post = {
		name: name,
		password: password,
	}
	var sql = `update student set ? where id= ${id}`;
	console.log(sql);
	connection.query(sql, post,function(err, result) {
		if (err) {
			console.log('[SELECT ERROR] - ', err.message);
			res.json({msg: '更新失败'})
			return;
		}
	});
	res.json({msg: '更新成功'})
})

// 删除数据
app.get('/delete', function(req, res) {
	let id = req.query.id || 0;
	var sql = `delete FROM student where id =${id}`;
	connection.query(sql, function(err, result) {
		if (err) {
			console.log('[SELECT ERROR] - ', err.message);
			return;
		}
		res.json({msg: '删除成功'})
	});
})

// 请求详情
app.get('/detail', function(req, res) {
	let id = req.query.id || 0;
	var sql = 'SELECT * FROM student where id =' + id;
	connection.query(sql, function(err, result) {
		if (err) {
			console.log('[SELECT ERROR] - ', err.message);
			return;
		}
		res.json(result[0])
	});
})

// 放置静态页面
var express = require('express');
var app = express()

app.get('/',function(req,res) {
	res.sendFile(__dirname + "/" +"index.html" );
})

var server = app.listen(3001,function() {
	var host = server.address().address
	var port = server.address().port
	 // console.log("应用实例，访问地址为 http://%s:%s", host, port)
})