1.mysql模块安装
2.连接数据库：db_config.js
npm install mysql --save
const mysql = require('mysql')
//创建连接
const conn = mysql.createConnection({
host:'112.126.63.18',
user:'lijing',
password:'lijing123',
port:'3306',
database:'zhifu'
})
//建立连接
conn.connect()
let sql = 'select * from user'
//执行sql语句
conn.query(sql,(err,result)=>{
if(err) throw err
console.log(result)
})
//关闭连接
conn.end()结果：
3.通过占位符实现传参,query方法第二参数就是会填充sql语句里的？
const mysql = require('mysql')
//创建连接
const conn = mysql.createConnection({
host:'112.126.63.18',
user:'lijing',
password:'lijing123',
port:'3306',
database:'zhifu'
})
//建立连接
conn.connect()
let sql = 'select * from user where name = ? and id = ?'
//执行sql语句
conn.query(sql,['fff','1'],(err,result)=>{
if(err) throw err
console.log(result)
})
//关闭连接
conn.end()
4. mysql连接池与普通连接的区别以及它的使用方法
4.1 频繁的创建、关闭连接会减低系统的性能，提高系统的开销
4.2 连接池可以有效的管理连接，达到连接复用的效果
4.3 连接池的使用
const mysql = require('mysql')
//创建连接池
const pool = mysql.createPool({
host:'112.126.63.18',
user:'lijing',
password:'lijing123',
port:'3306',
database:'zhifu'
})
//获取连接
pool.getConnection((err, conn) => {
if (err) throw err
let sql = 'select * from user'
//执行sql语句
conn.query(sql,(err, result) => {
conn.release()
if (err) throw err
console.log(result)
})
})
5.数据库增删改查用户
5.1 数据库配置
let dbOption
dbOption = {
connectionLimit: 10,
host:'112.126.63.18',
user:'lijing',
password:'lijing123',
port:'3306',
database:'zhifu'
}
module.exports = dbOption5.2 数据库连接，以及query方法封装:config.js
const mysql = require('mysql')
const dbOption = require('../config/db_config')
//创建连接池
const pool = mysql.createPool(dbOption)
function query (sql,params) {
return new Promise((resolve, reject) => {
//获取连接
pool.getConnection((err, conn) => {
if (err){
reject(err)
return
}
//执行sql语句
conn.query(sql, params, (err, result) => {
conn.release()
if (err) {
reject(err)
return
}
resolve(result)
})
})
})
}
module.exports = query
server:
const http = require('http');
const routerModal = require('./router/index')
const url = require('url')
const getPostData = (req) => {
return new Promise((resolve, reject) => {
if (req.method !== 'POST') {
resolve({})
return
}
let postData = '';
req.on('data', chunk => {
postData += chunk;
})
req.on('end', () => {
// console.log(postData)
if(postData){
resolve(JSON.parse(postData))
}else{
resolve({})
}
})
})
}
const server = http.createServer((req, res) => {
//设置允许跨域的域名，*代表允许任意域名跨域
res.setHeader("Access-Control-Allow-Origin","http://127.0.0.1:5501");
res.writeHead(200, { 'content-type': 'application/json;charset=UTF-8' })
getPostData(req).then((data) => {
req.body = data
let result = routerModal(req, res);
if (result) {
result.then(resultData =>{
res.end(JSON.stringify(resultData))
})
} else {
res.writeHead(404, { 'content-type': 'text/html' })
res.end('404 not found')
}
})
})
server.listen(3000, () => {
console.log('监听3000端口')
})5.3 增加：server通过获取的promise结果来获取数据，并且返回结果
controller:引用的query方法
module.exports = {
async addUser(userObj){
console.log(userObj);
let {name,city,sex} = userObj
let sql = 'insert into user (name,city,sex) values (?,?,?)'
let resultData = await query(sql,[name,city,sex])
if(resultData){
return {
msg:'新增成功'
}
}else{
return {
msg:'新增失败'
}
}
}
}
路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
if(urlObj.pathname === '/api/addUser'&&req.method === 'POST'){
let resultData = addUser(req.body);
console.log(resultData,'index.js')
return resultData;
}
}
module.exports = handleRequest5.4 更新用户接口
controller:引用的query方法
module.exports = {
async updateUser(id,userObj){
// console.log(id,userObj);
let {name,city,sex} = userObj
let sql = 'update user set name = ?,city = ?,sex = ? where id = ?'
let resultData = await query(sql,[name,city,sex,id])
if(resultData.affectedRows > 0){
return {
msg:'更新成功'
}
}else{
return {
msg:'更新失败'
}
}
}
路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
let urlObj = url.parse(req.url,true);
if(urlObj.pathname === '/api/updateUser'&&req.method === 'POST'){
let resultData = updateUser(urlObj.query.id,req.body);
return resultData;
}
}
module.exports = handleRequest5.5 删除用户接口
controller:引用的query方法
module.exports = {
async delectUser(id){
let sql = 'delete from user where id = ?'
let resultData = await query(sql,[id])
if(resultData.affectedRows > 0){
return {
msg:'删除成功'
}
}else{
return {
msg:'删除失败'
}
}
}
}
路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
let urlObj = url.parse(req.url,true);
if(urlObj.pathname === '/api/delectUser'&&req.method === 'POST'){
let resultData = delectUser(urlObj.query.id);
return resultData;
}
}
module.exports = handleRequest5.6 通过sql语句操作数据库实现动态查询
controller:引用的query方法
module.exports = {
async getUserList(urlParams){
let {name,city} = urlParams
let sql = 'select * from user where 1=1 '
if(name){
sql += 'and name = ?'
}
if(city){
sql += 'and city = ?'
}
let resultData = await query(sql,[name,city])
return resultData
}
}
路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
let urlObj = url.parse(req.url,true);
if(urlObj.pathname === '/api/getUserList'&&req.method === 'GET'){
let resultData = getUserList(urlObj.query)
return resultData;
}
}
module.exports = handleRequest
