Node.js 连接 MySQL
安装驱动
$ npm install mysql
常用mysql语句
// 查询表websites中所有内容(select后面的*代表输出所有字段,可设置需要显示的字段,逗号分隔)
'SELECT * FROM websites';
// 添加数据(id可以不填,默认自增长)
'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
// 修改内容
'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
// 删除数据
'DELETE FROM websites where id=6';
// 连表查询,排序,分页(as定义表别名,语句可调用,asc升序,desc降序,默认asc)
'SELECT * FROM `user` as u left JOIN grade as g on g.userid = u.id where u.age = 98 ORDER BY u.id, u.age DESC limit 0,2';
// 模糊查询(like替换=,%代表0个或多个字符)
'SELECT * FROM `user` WHERE username like "%hui%"'
// 查询表数据的数量
'SELECT count(0) FROM `user`
连接数据库
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'test'
});
connection.connect();
查询数据
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
port: '3306',
database: 'test',
});
connection.connect();
var sql = 'SELECT * FROM websites';
connection.query(sql, function(err, result) {
if (err) {
console.log('[SELECT ERROR] - ', err.message);
return;
}
console.log('--------------------------SELECT----------------------------');
console.log(result);
console.log('------------------------------------------------------------\n\n');
});
connection.end();
添加数据
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
port: '3306',
database: 'test',
});
connection.connect();
var addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
var addSqlParams = ['菜鸟工具', 'https://c.runoob.com', '23453', 'CN'];
connection.query(addSql, addSqlParams, function(err, result) {
if (err) {
console.log('[INSERT ERROR] - ', err.message);
return;
}
console.log('--------------------------INSERT----------------------------');
console.log('INSERT ID:', result);
console.log('-----------------------------------------------------------------\n\n');
});
connection.end();
修改更新数据
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
port: '3306',
database: 'test',
});
connection.connect();
var modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
var modSqlParams = ['菜鸟移动站', 'https://m.runoob.com', 6];
connection.query(modSql, modSqlParams, function(err, result) {
if (err) {
console.log('[UPDATE ERROR] - ', err.message);
return;
}
console.log('--------------------------UPDATE----------------------------');
console.log('UPDATE affectedRows', result.affectedRows);
console.log('-----------------------------------------------------------------\n\n');
});
connection.end();
删除数据
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
port: '3306',
database: 'test',
});
connection.connect();
var delSql = 'DELETE FROM websites where id=6';
connection.query(delSql, function(err, result) {
if (err) {
console.log('[DELETE ERROR] - ', err.message);
return;
}
console.log('--------------------------DELETE----------------------------');
console.log('DELETE affectedRows', result.affectedRows);
console.log('-----------------------------------------------------------------\n\n');
});
connection.end();