how2j.cn

步骤 1 : 创建数据库   
步骤 2 : 创建表结构   
步骤 3 : 准备数据   
步骤 4 : 准备项目目录   
步骤 5 : 安装模块   
步骤 6 : 查询所有   
步骤 7 : 增加   
步骤 8 : 删除   
步骤 9 : 获取   
步骤 10 : 修改   

create database nodejs
create database nodejs
use nodejs; CREATE TABLE category_ ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(30) , PRIMARY KEY (id) ) DEFAULT CHARSET=UTF8;
use nodejs;
  
CREATE TABLE category_ (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(30) ,
  PRIMARY KEY (id)
) DEFAULT CHARSET=UTF8;
use nodejs; delete from category_; insert into category_ values(null,"category1"); insert into category_ values(null,"category2"); insert into category_ values(null,"category3"); insert into category_ values(null,"category4"); insert into category_ values(null,"category5");
use nodejs;
delete from category_;
insert into category_ values(null,"category1");
insert into category_ values(null,"category2");
insert into category_ values(null,"category3");
insert into category_ values(null,"category4");
insert into category_ values(null,"category5");
步骤 4 :

准备项目目录

edit
还是在这个目录下开始项目:

e:\project\nodejs
准备项目目录

npm install mysql --save
安装模块
db.js
1. 准备函数openConnection, 其中填写数据库链接的地址,账号,密码,数据库名称
2. 准备函数 closeConnection, 用于关闭链接
3. 准备 showAll 查询所有的数据
app.js
调用 db.js 的 showAll函数
运行测试

node app.js
查询所有
var mysql = require("mysql"); var connection; function openConnection(){ connection = mysql.createConnection({ host : "127.0.0.1", user : "root", password : "admin", database : "nodejs" }); connection.connect(); } function closeConnection(){ connection.end(); } function showAll(){ openConnection(); var sql = "SELECT * FROM category_ order by id asc"; connection.query(sql,function (err, results) { if(err){ console.log("[SELECT ERROR] - ",err.message); return; } if(results) { for(var i = 0; i < results.length; i++) { console.log("%d\t%s", results[i].id, results[i].name); } } }); closeConnection(); }
var db = require("./db"); db.showAll();
修改 db.js 和 app.js 为如下代码,然后重新运行测试

node app.js
增加
var mysql = require("mysql"); var connection; function openConnection(){ connection = mysql.createConnection({ host : "127.0.0.1", user : "root", password : "admin", database : "nodejs" }); connection.connect(); } function closeConnection(){ connection.end(); } function showAll(){ openConnection(); var sql = "SELECT * FROM category_ order by id asc"; connection.query(sql,function (err, results) { if(err){ console.log("[SELECT ERROR] - ",err.message); return; } if(results) { for(var i = 0; i < results.length; i++) { console.log("%d\t%s", results[i].id, results[i].name); } } }); closeConnection(); } function add(name){ openConnection(); var params = [null,name]; var sql = "insert into category_ values (?,?)"; connection.query(sql,params,function (err, result) { if(err){ console.log("[INSERT ERROR] - ",err.message); return; } console.log("insert success, the generated id is:",result.insertId); }); closeConnection(); }
var db = require("./db"); db.add("category test"); db.showAll();
修改 node.js 和 app.js 为如下代码,然后重新运行测试

node app.js
删除
var mysql = require("mysql"); var connection; function openConnection(){ connection = mysql.createConnection({ host : "127.0.0.1", user : "root", password : "admin", database : "nodejs" }); connection.connect(); } function closeConnection(){ connection.end(); } function showAll(){ openConnection(); var sql = "SELECT * FROM category_ order by id asc"; connection.query(sql,function (err, results) { if(err){ console.log("[SELECT ERROR] - ",err.message); return; } if(results) { for(var i = 0; i < results.length; i++) { console.log("%d\t%s", results[i].id, results[i].name); } } }); closeConnection(); } function add(name){ openConnection(); var params = [null,name]; var sql = "insert into category_ values (?,?)"; connection.query(sql,params,function (err, result) { if(err){ console.log("[INSERT ERROR] - ",err.message); return; } console.log("insert success, the generated id is:",result.insertId); }); closeConnection(); } function remove(id){ openConnection(); var params = [id]; var sql = "delete from category_ where id = ?"; connection.query(sql,params,function (err, result) { if(err){ console.log("[REMOVE ERROR] - ",err.message); return; } console.log("remove id=%d success ",id); }); closeConnection(); }
var db = require("./db"); db.remove(1); db.showAll();
修改 node.js 和 app.js 为如下代码,然后重新运行测试

node app.js
获取
var mysql = require("mysql"); var connection; function openConnection(){ connection = mysql.createConnection({ host : "127.0.0.1", user : "root", password : "admin", database : "nodejs" }); connection.connect(); } function closeConnection(){ connection.end(); } function showAll(){ openConnection(); var sql = "SELECT * FROM category_ order by id asc"; connection.query(sql,function (err, results) { if(err){ console.log("[SELECT ERROR] - ",err.message); return; } if(results) { for(var i = 0; i < results.length; i++) { console.log("%d\t%s", results[i].id, results[i].name); } } }); closeConnection(); } function add(name){ openConnection(); var params = [null,name]; var sql = "insert into category_ values (?,?)"; connection.query(sql,params,function (err, result) { if(err){ console.log("[INSERT ERROR] - ",err.message); return; } console.log("insert success, the generated id is:",result.insertId); }); closeConnection(); } function remove(id){ openConnection(); var params = [id]; var sql = "delete from category_ where id = ?"; connection.query(sql,params,function (err, result) { if(err){ console.log("[REMOVE ERROR] - ",err.message); return; } console.log("remove id=%d success ",id); }); closeConnection(); } function get(id){ openConnection(); var params = [id]; var sql = "select * from category_ where id = ?"; connection.query(sql,params,function (err, result) { if(err){ console.log("[GET ERROR] - ",err.message); return; } if(result.length !=0){ var category = {id:result[0].id,name:result[0].name}; console.log("get category:"+ JSON.stringify(result)) } else{ console.log("not found with id :" + id) } }); closeConnection(); }
var db = require("./db"); db.get(2);
修改 node.js 和 app.js 为如下代码,然后重新运行测试

node app.js
修改
var mysql = require("mysql"); var connection; function openConnection(){ connection = mysql.createConnection({ host : "127.0.0.1", user : "root", password : "admin", database : "nodejs" }); connection.connect(); } function closeConnection(){ connection.end(); } function showAll(){ openConnection(); var sql = "SELECT * FROM category_ order by id asc"; connection.query(sql,function (err, results) { if(err){ console.log("[SELECT ERROR] - ",err.message); return; } if(results) { for(var i = 0; i < results.length; i++) { console.log("%d\t%s", results[i].id, results[i].name); } } }); closeConnection(); } function add(name){ openConnection(); var params = [null,name]; var sql = "insert into category_ values (?,?)"; connection.query(sql,params,function (err, result) { if(err){ console.log("[INSERT ERROR] - ",err.message); return; } console.log("insert success, the generated id is:",result.insertId); }); closeConnection(); } function remove(id){ openConnection(); var params = [id]; var sql = "delete from category_ where id = ?"; connection.query(sql,params,function (err, result) { if(err){ console.log("[REMOVE ERROR] - ",err.message); return; } console.log("remove id=%d success ",id); }); closeConnection(); } function get(id){ openConnection(); var params = [id]; var sql = "select * from category_ where id = ?"; connection.query(sql,params,function (err, result) { if(err){ console.log("[GET ERROR] - ",err.message); return; } if(result.length !=0){ var category = {id:result[0].id,name:result[0].name}; console.log("get category:"+ JSON.stringify(result)) } else{ console.log("not found with id :" + id) } }); closeConnection(); } function update(id,name){ openConnection(); var params = [name,id]; var sql = "update category_ set name = ? where id = ?"; connection.query(sql,params,function (err, result) { if(err){ console.log("[UPDATE ERROR] - ",err.message); return; } console.log("update success"+result.affectedRows); }); closeConnection(); }
var db = require("./db"); db.update(6,"category 666"); db.showAll();


HOW2J公众号,关注后实时获知最新的教程和优惠活动,谢谢。


问答区域    
2024-06-17 新版本nodejs可能会和老版本SQL不兼容
thaishanfox




解决方法: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ‘密码'; FLUSH PRIVILEGES;

							
[SELECT ERROR]- ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication
protocol requested by server; consider upgrading MySQL client





回答已经提交成功,正在审核。 请于 我的回答 处查看回答记录,谢谢
答案 或者 代码至少填写一项, 如果是自己有问题,请重新提问,否则站长有可能看不到





2022-12-28 建表异常
月之书

我真是无语了,报了这个错误, [select error] - ER_BAD_FIELD_ERROR: Unknown column 'id' in 'order clause' 排查几遍后发现mysql建表时建立的是' id'而不是'id',这谁想得到啊!




1 个答案

12345678zZ
答案时间:2023-02-09
他那时是固定写法



回答已经提交成功,正在审核。 请于 我的回答 处查看回答记录,谢谢
答案 或者 代码至少填写一项, 如果是自己有问题,请重新提问,否则站长有可能看不到




2021-12-31 db.js里的crud都没有加exports呀,得加上呀
2021-09-10 数据库连接端口修改
2020-11-05 安装的时候就开始error了


提问太多,页面渲染太慢,为了加快渲染速度,本页最多只显示几条提问。还有 14 条以前的提问,请 点击查看

提问之前请登陆
提问已经提交成功,正在审核。 请于 我的提问 处查看提问记录,谢谢
关于 前端部分-node.js-mysql 的提问

尽量提供截图代码异常信息,有助于分析和解决问题。 也可进本站QQ群交流: 578362961
提问尽量提供完整的代码,环境描述,越是有利于问题的重现,您的问题越能更快得到解答。
对教程中代码有疑问,请提供是哪个步骤,哪一行有疑问,这样便于快速定位问题,提高问题得到解答的速度
在已经存在的几千个提问里,有相当大的比例,是因为使用了和站长不同版本的开发环境导致的,比如 jdk, eclpise, idea, mysql,tomcat 等等软件的版本不一致。
请使用和站长一样的版本,可以节约自己大量的学习时间。 站长把教学中用的软件版本整理了,都统一放在了这里, 方便大家下载: https://how2j.cn/k/helloworld/helloworld-version/1718.html

上传截图