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)
})