MySQL使用示例

首页 > 了不起的Nodejs应用示例 > MySQL使用示例

本文共--字阅读约--分钟 | 浏览: -- Last Updated: 2021-07-03

MySQL是关系型数据库，优点是免费、性能好、适合中小网站；缺点是集群、容灾能力稍微弱一些。

Oracle也是关系型数据库，优点是性能好，集群、容灾非常强，缺点就是贵。

SQL语句：增删查改

1、增 INSERT：INSERT INTO 表名 (字段列表) VALUES (值列表)，例：

INSERT `user_table` (`ID`, `username`, `password`) VALUES (0, 'jack', '123456');

2、删 DELETE：DELETE FORM 表 WHERE 条件

3、查 SELECT：SELECT * FROM 表 WHERE 条件

4、改 UPDATE：UPDATE 表名 SET 字段=值 WHERE ID=xxx, 例：

UPDATE `user_table` SET username='newname' WHERE ID="0"

WHERE 条件

WHERE name="jack"

WHERE age>18

WHERE age<18

WHERE age>=18 AND score<60

WHERE cash>=100 OR score>10000

ORDER 排序

ORDER BY age ASC/DESC 根据年龄排序，ASC 升序，DESC 降序

多条件排序

OEDER BY price ASC, sales DESC // 先让价格升序排，再让销量降序排

GROUP 聚类-合并相同的

stu_table数据如下：

ID	class	name	score
1	1	小强	21
2	2	小刚	35
3	1	小明	56
4	1	小华	76
5	3	小红	99
6	3	阿中	98
7	1	小刘	35
8	2	小陈	56

SELECT COUNT(*) FROM stu_table;
-- 查询stu_table的数据总数 COUNT:8

-- 以班级作为依据进行合并
-- 去重，每一个class的值只留一条，被合并了
SELECT * FROM stu_table GROUP BY class;
-- 1     1   小强  
-- 2     2   小刚  
-- 5     3   小红

-- 统计每个班的人数
SELECT class,COUNT(class) FROM stu_table GROUP BY class;

-- class COUNT
-- 1     4     
-- 2     2     
-- 3     2

除了COUNT函数，还有MIN、MAX、AVG（求平均）、SUM（求和）

-- 统计每个班的平均分
SELECT class,AVG(score) FROM stu_table GROUP BY class;

-- class AVG(score)
-- 1      47  
-- 2     45.5  
-- 3     98.5

-- 统计每个班的最高分和最低分
SELECT class,MAX(score),MIN(score) FROM stu_table GROUP BY class;

-- class MAX(score) MIN(score)
-- 1      76            21
-- 2      56            35
-- 3      99            98

-- 统计每个班的总分
SELECT class,SUM(score) FROM stu_table GROUP BY class;

-- class SUM(score) 
-- 1      188           
-- 2      91            
-- 3      197

-- 统计1班的得分降序
SELECT name,score FROM stu_table WHERE class="1" ORDER BY score DESC;

-- name score
-- 小华	76
-- 小明	56
-- 小刘	35
-- 小强	21

子句之间是有顺序的 WHERE GROUP ORDER LIMIT 筛选合并排序限制

LIMIT 限制输出

LIMIT 10; 前10条

LIMIT 5,20; 从哪条开始，需要多少条。例如：第一页 0，20 、第二页 20，20、第三页 40，20

-- 统计一班的前3名
SELECT name,score FROM stu_table WHERE class="1" ORDER BY score DESC LIMIT 0,3;

-- name score
-- 小华	76
-- 小明	56
-- 小刘	35

-- 查询名字长度大于3的同学 一个汉字utf8 占3个字节
SELECT * FROM stu_table WHERE LENGTH(name)>=9

在node中使用MySQL

下面的示例的前提需要安装MySQL，并建好了一张简单的user_table表。

安装node中的mysql库用来操作mysql: npm install mysql

const mysql = require('mysql');
const express = require('express');
const consolidate =require('consolidate');

var server = express();

server.set('view engine', 'html');
server.set('views', './')
server.engine('html', consolidate.ejs);

// mysql.createConnection 建立一个普通连接
// 使用数据库连接池 保持20个连接 避免频繁连接查询导致性能不好，一致保持固定个数的连接，优化性能
const db = mysql.createPool({
  // port:'3306', 默认3306，如果没改可以不传
  host: 'localhost',
  user: 'root',
  password: 'mysqlhalo',
  database: '20191105'
})

server.get('/', function (req, res) {
  db.query("SELECT * FROM `user_table`;", function (err, data) {
    if (err) {
      res.status(500).send('database error').end();
    } else {
      res.render('./demo.ejs', {userData: data})
    }
  })
})

server.listen(8081)
// data 从数据库读出的数据直接就是数组类型
// [
//   {"ID":1,"username":"xiaoming","password":"123321"},
//   {"ID":2,"username":"zhangsan","password":"987654"},
//   {"ID":3,"username":"chen","password":"haha"},
//   {"ID":4,"username":"test","password":"pass"},
//   {"ID":5,"username":"jack","password":"ma"},
//   {"ID":6,"username":"222","password":"333"}
// ]

将MySQL应用在web中：

const express = require('express');
const consolidate = require('consolidate');
const cookieParser = require('cookie-parser');
const cookieSession = require('cookie-session');
const bodyParser = require('body-parser');
const crypto = require('crypto');
const mysql = require('mysql');
const MD5_SUFFIX = "jdpasjdan&#@#@@￥@LHJLKJNM";

const db = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'mysqlhalo',
  database: '20191105'
})

var app = express();

app.use(bodyParser.urlencoded({extended: true}));

// 处理session
// 1.解析cookie
app.use(cookieParser('adkajidqjwodjqoiw'));

// 2.使用session 基于cookie
var arr = [];
for (let index = 0; index < 100; index++) {
  arr.push('keys_' + Math.random())
}
app.use(cookieSession({name: 'admin_id', keys: arr, maxAge: 20 * 3600 * 1000}))

// 处理模板引擎
app.set('view engine', 'html');
app.set('views', './');
app.engine('html', consolidate.ejs);


// 加密密码
function md5(str) {
  var obj = crypto.createHash('md5');
  obj.update(str + MD5_SUFFIX);
  return obj.digest("hex") // 返回16机制
}

var adminRouter = express.Router();
var webRouter = express.Router();

webRouter.use('/', function (req, res) {
  res.send('我是首页').end();
})

adminRouter.use(function (req, res,next) {
  // req.url 经过了处理 访问/admin/login的时候，会将父路由截掉 所以req.url是 /login
  if(!req.session['admin_id'] && req.url !== '/login') { 
    // 没有session 并且不是在login页面就重定向到login
    // redirect根据根目录来 所以不能直接写 /login
    res.redirect('/admin/login'); 
  } else {
    next();
  }
})

// 登录页面
adminRouter.get('/login', function (req, res) {
  res.render('./login.ejs');
})

// 处理用户的登录操作
adminRouter.post('/login', function (req, res) {
  let {username, password} = req.body;
  // 从数据库查出数据 判断用户名和密码是否正确
  db.query(`SELECT * FROM user_table WHERE username='${username}'`, (err, data) => {
    if(err) {
      console.log(err);
      res.status(500).send('database error').end()
    } else {
      if(data.length ==0) {
        res.status(400).send('no this admin').end();
      } else {
        // 数据库的密码进行过md5加密 所以要将用户传来的密码 进行md5加密后再进行比较
        // 实际应该是在前段进行密码的md5加密之后 再传输到后端
        if(data[0].password == md5(password)){
          // 设置session
          req.session['admin_id'] =  data[0].ID;
          res.redirect('/admin/'); // 回到首页
        } else {
          res.status(400).send('this password is incorrect').end();
        }
      }
    }
  })
})

adminRouter.use('/', function (req, res) {
  console.log(req.url);
  if (req.url === '/') {
    res.send('我是管理员页面').end();
  } else {
    res.status(404).send('404').end();
  }
})

app.use('/admin', adminRouter)
app.use('/', webRouter)

app.listen(8081);