nodejs中关于mysql数据库的操作_node.js_

CREATE TABLE user(     id INT auto_increment PRIMARY KEY,     name VARCHAR(255) NOT NULL,     age INT ,     gender VARCHAR(4),     content VARCHAR(255) )

INSERT INTO user (name, age, gender, content) VALUES ('胡聪聪', 18, '男', '哈哈哈，哈哈哈') -- 如果省略列名，那么必须要和字段一一对应 INSERT INTO user VALUES (null, '胡聪聪', 18, '男', '哈哈哈，哈哈哈')   INSERT INTO user SET name='hcc', age=18, gender='男', content='嘻嘻嘻'

// 修改所有的数据 UPDATE USER SET name='胡西西' // 根据条件修改 UPDATE USER set name='胡聪聪', content="这是内容" WHERE id = 2

// 删除所有的数据 DELETE FROM USER   // 删除id为5的数据 DELETE FROM USER WHERE id = 5

-- 查询所有数据 SELECT * FROM user   -- 查询指定列 SELECT id, name,age from user

--- 并且 SELECT * from user where name='胡聪聪' AND age=21   --- 或者 SELECT * from user where name='胡聪聪' or age=21   -- 范围查询   -- 模糊查询  %表示通配  _表示单个字符 SELECT * from user where name LIKE '胡%'   -- in语句 SELECT * from user where name in ('胡聪聪', 'hcc')   -- order by -- 排序需要写在最后面，，asc升序  desc：降序 SELECT * from user ORDER BY id desc   -- limit分页 SELECT * from user ORDER BY id desc limit 3 SELECT * from user ORDER BY id desc limit 3,3     -- 获取总条数 SELECT count(*) as total FROM user

npm install mysql

// 导入第三方包 const mysql = require('mysql') // 创建连接 var connection = mysql.createConnection({   // 本地   host: 'localhost',   user: 'root',   password: 'root',   // 数据库名称   database: 'mydb',   port: 3306 })   // 连接数据库 connection.connect()   // 执行sql语句 connection.query('select * from user where id = 8', (err, result) => {   if (err) return console.log('查询失败', err)   // result返回的是数组， 数组中是一个对象   console.log(result) })   // 关闭连接 connection.end()

var name = '胡聪聪' // 使用?表示占位，可以防止sql注入 connect.query(`select * from user where name=?`, name, (err, result) => {   if (err) return console.log('错误了', err)   console.log(result) })

connect.query(   'insert into user (name, age, gender, content) values (?, ?, ?, ?)',   ['胡嘻嘻', 18, '男', '哈哈哈哈'],   err => {     if (err) return console.log('错误', err)     console.log('添加成功了')   } )   // 方式2 connect.query(   'insert into user set ?',   {     name: '胡洗洗',     age: 30,     gender: '男',     content: '哈哈哈'   },   (err, result) => {     if (err) return console.log('错误', err)     console.log('添加成功了', result)   } )

connect.query(   'update user set ? where id = ?',   [     {       name: '胡洗洗',       age: 30,       gender: '男',       content: '哈哈哈'     },     10   ],   (err, result) => {     if (err) return console.log('错误', err)     console.log('添加成功了', result)   } )

connect.query('delete from user where id = ?', [10], (err, result) => {   if (err) return console.log('失败', err)   console.log(result) })

// 导入mysql const mysql = require('mysql')   // 创建连接对象 const connect = mysql.createConnection({   host: 'localhost',   port: 3306,   user: 'root',   password: 'root',   database: 'mydb' })   exports.query = function(sql, params, callback) {   connect.connect()   connect.query(sql, params, (err, data) => {     callback &&  callback(err, data)   })   connect.end() }