MySQL

mysql

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

下载

MySQL :: Download MySQL Community Server

下载完整安装板，在后续页面点击No thanks, just start my download来开始下载。

安装

这里注意，选择密码的加密方式，一定选择下面一项，因为默认的是新的加密方式，nodejs的mysql模块不支持这种模式。

图形界面管理工具

推荐使用Navicat ，官方网站： http://www.navicat.com.cn/

新建数据库

使用Navicat新建或使用命令行：

create database 数据库名;

删除数据库

使用Navicat删除或使用命令行：

drop database 数据库名;

数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

数值类型

整数型包括：

类型	字节	有符号范围	无符号范围	用途
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-2^63~2^63-1	0~2^64-1

定点类型：

DECIMAL(M,D)

• M是最大位数（精度）。范围是 1 到 65。

• D是小数点右边的位数（小数位）。范围是 0 到 30，并且不能大于 M。

• 如果D省略，则默认值为0。如果 M省略，则默认值为10。

不建议使用浮点类型：float

日期时间类型

类型	字节	格式	用途
DATE	3	YYYY-MM-DD
TIME	3	HH:MM:SS
YEAR	1	YYYY
DATETIME	8	YYYY-MM-DD hh:mm:ss
TIMESTAMP	4	YYYY-MM-DD hh:mm:ss

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

主键

关系型数据库，面试很可能会问到有关数据库优化的问题。

1、合理的设置数据表。

2、合理的设置主键。

一张表至少有一个主键。主键是数据库查询数据的参照物。

例如：如果需要对这张表作统计，统计男女学生分别有多少位。为了增加速度，可以将性别作为主键（索引）。

express使用

要让express能够访问mysql，需要安装模块，可选模块有很多，我们选用老牌的mysql，

安装

首先在项目中安装mysql模块，要注意，它不是mysql，只是连接、操作mysql的驱动模块，只是名字叫做mysql

npm install mysql

使用

在express的路由页面引入、配置后就可以使用，我们修改router/index.js：

const mysql = require('mysql');//引入mysql模块

//创建一个get请求的api，名字叫做mysql
router.get('mysql', function (req, res) {
  //创建一个连接，配置相关参数
  const connect = mysql.createConnection({
    host: '127.0.0.1',
    user: 'root',
    password: '12345678',
    database: 'demo'
  })
  connection.connect();//连接mysql
  //执行查询
  connect.query('select * from users', function (err, result) {
    if (err) {
      throw err
    } else {
      res.send(results)
    }
  })
  //结束连接
  connection.end();
})

连接池

每个用户连接都会新建连接、执行查询、结束连接，连接的新建会大量消耗服务器资源。

因此，mysql模块引入了连接池的概念，连接池会自动管理连接。用户需要使用查询时，向连接池提出获取连接的请求，此时如果连接池内有已经建立好的连接，则直接拿给用户使用，连接在连接池内长时间无人使用时会被销毁以节约资源。

只有当用户向连接池请求连接，而连接池内没有空闲的连接时才会新建连接

const mysql = require('mysql');//引入mysql模块
//创建连接池
const pool = mysql.createConnection({
  host: '127.0.0.1',
  user: 'root',
  password: '12345678',
  database: 'demo',
  connectionLimit: 10//是指连接池允许创建的最大连接数,默认为10
})

router.get('/', function (req, res) {
  //执行查询
  pool.query('select * from users', function (err, result) {
    if (err) {
      throw err
    } else {
      res.send(result)
    }
  })
})

封装

为了更加方便的使用，我们采用promise对象对其进行封装。

我们建立mysql.js文件，内容如下：

const mysql = require('mysql');//引入mysql模块
//创建连接池
const pool = mysql.createPool({
  connectionLimit: 10,
  host: '127.0.0.1',
  user: 'root',
  password: '12345678',
  database: 'demo'
})
//设置query函数
let query = (sql, params) => {
  //返回promise对象
  return new Promise((resolve, reject) => {
    //从连接池中获取连接
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err)
      } else {
        //执行查询
        connection.query(sql, params, (err, result) => {
          //释放连接，放回连接池
          connection.release();
          if (err) {
            reject(err)
          } else {
            resolve(result)
          }

        })
      }
    })
  })
}

module.exports = query

sql语言

SQL 是用于访问和处理数据库的标准的计算机语言。可以用来操作MySQL、SQL Server、Access、Oracle、Sybase、DB2 等数据库。

插入

INSERT into users (username,`password`,createdTime,updatedTime) 
VALUES('小小',123456,NOW(),NOW())

sql: insert into <表名> (字段名1,字段名2,.....字段名n) values (值1,值2,....值n);

注意：凡是字符串类型，必须加引号。

为了避免 sql注入的风险，mysql的query方法做个参数过滤。：

  query(
    'INSERT INTO users (username,password,createdTime,updatedTime) VALUES (?,?,?,?)',
    [username, password, new Date(), new Date()]
  )

将sql中的参数用?表示，具体的参数以数组的形式附加。

sql注入

sql注入是可能存在的风险，黑客们可以使用sql注入来破坏数据库。

正常的sql语句是：

'SELECT * FROM users where id = 1'

但是1是参数，来源于请求（客户端）

'SELECT * FROM users where id = '+req.query.id

但是，如果客户端传递的参数id的值不是数字，而是一个特殊字符串，例如：

'(delete * from users)'

拼接后的结果就是

'SELECT * FROM users where id = (delete * from users)'

在sql语言中，()代表优先执行，所以，数据库的所有内容就被删除了。

查询

select * from <表名>

'select * from users'

select 字段名1,字段名2...字段名n from <表名>

'select username,passowd from users'

DISTINCT

不重复

select DISTINCT username from users

别名

select 字段名 as 别名 from <表名>

'select username as uid,password as pwd from users'

统计记录数

'select count(*) as count from users'

count()是sql内置函数

求平均值

'select avg(age) from users'

where字句

where字句也叫作条件字句，可以用来确定查询条件

'select * from users where username = "小小"'
'select * from users where id>=2'

where条件如果有多个，可以使用and 或者 or 来表示并且和或者

'SELECT * FROM users WHERE username = '小小' OR username = '李四''

like

在where字句中，可以使用%来作为通配符使用

'SELECT * FROM users WHERE username LIKE '%小'

表示模糊查询，like表示 形式类似 %表示任意字符出现任意多次。

in

原理类似于for in循环：

'SELECT * FROM users where id = 1 OR id = 3'

in可以改写为

'SELECT * FROM users where id IN (1,3)'

BETWEEN

介于两者之间

'SELECT * FROM users where id BETWEEN 2 AND 3'

id>=2 and id<=3

not

not 表示除了

'SELECT * FROM users where id not IN (1,3)'
'select * from users where id <> 1 and id <>3'
​
​
'SELECT * FROM users where id not BETWEEN 2 AND 3'

orderby子句

用来排序的

'select * from users ORDER BY id asc'
'select * from users ORDER BY id desc'

不写排序规则，默认是asc,表示从小到大。

desc 表示从大到小。

'select * from users order by id desc,username asc'

先使用id倒序排列，如果发现有数据的id一致，则使用username正序排列。

更新

where

UPDATE users SET password = '123',updatedTime = now()  where username = '张三'

删除

DELETE from users WHERE username = '小小'

多表联查

mysql支持多表联查的连接：内连接、左连接、又连接。

假设有表a和表b。

表a存储张三、李四的id、用户名和密码

表b存储李四和王五的姓名、性别、用户id

select * from users as a left join userinfo as b on a.id=b.userid

进行连接查询时，可以对数据表用as起别名，必须有 on字句来表示两个表的关联字段。

左连接：left join 是以表a为准，输出内容按表a，找到表b对应的数据填充。

右连接：right join 是以表b为准，输出内容按表b，找到表a对应的数据填充。

内连接:inner join 是以表a和表b的交集为准。

分组查询

group by语句可以对数据库字段进行分组

select count(*) from users group by username

表示对数据进行分组，按不同的username作为分组条件。

进行分组后，不能再查询除分组条件外的自他字段，例如上面的sql只能查询username字段。

除了username之外，还可以使用聚合函数来查询组内数据：

count(字段名称) 组内成员数量

avg(字段名称) 求组内成员指定的字段名称的平均值。

sum(字段名称) 求组内成员指定的字段名称的值之和。

max() min() 求组内成员指定的字段名称中的最大值或最小值。

如果需要对分组后的结果再进行删选，则使用having字句

select username,MAX(`password`) as max from users GROUP BY username
HAVING max>222