10月14, 2018

mysql笔记

记录一下mysql相关的内容。

mysql特点

  • 数据以表格的形式出现
  • 表格中每一行表示一组数据
  • 表格中每一列表示某组数据对应的字段(属性)
  • 若干这样的行和列就组成了一张表
  • 若干个表格组成一个库

安装

brew install mysql

详细一点的,可以参照该文:Mac下MySQL 5.7安装与配置

关于配置这一块:

alt

alt

在window下是my.ini,通常可能会涉及改的配置有以下:

  • port 端口号
  • basedir 安装目录
  • datadir 数据存放访目录
  • charcter-set-server 字符集
  • default-storage-engine 存储引擎
  • sql-mode 语法模式
  • max-connections 最大连接数

启动与连接

mysql.server start
mysql -u root -p

数据库相关命令

  • 查看所有数据库
show databases;
  • 选择要操作的数据库
use <database>;

SQL

结构化查询语言(Structured Query Language)

SQL组成

  • DDL(Data Definition Language)数据库定义语言
  • DML(Data Manipulation Language)数据操纵语言
  • DCL(Data Control Language)数据库控制语言
  • TCL(Transaction Control Language)事务控制语言

SQL运算符

  • 算术运算符
运算符 说明
+ 加运算,求两个数或表达式相加的和,如1+1
- 减少减运算,求两个数或表达式相减的差,如4-1
* 乘运算,求两个数或表达式相乘的积,如2*2
/ 除运算,求两个数或表达式相除的商,如6/4的值为1
% 取模运算,求两个数或表达式相除的余数,如:6%4的值为2
  • 逻辑运算符
运算符 说明
AND 当且仅当两个布尔表达式都为true时,返回TRUE
OR 当且仅当两个布尔表达式都为false,返回FALSE
NOT 布尔表达式的值取反
  • 比较运算符

创建数据库及表

CREATE DATABASE 数据库名称 

CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin
CREATE TABLE 数据库表名称 (
    字段名称 字段属性...,
    PRIMARY KEY (主键字段名称),
    INDEX 索引名称(索引字段...)...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

其实一般会图形化操作即可,但要能看懂上面的含义。

这里还有如何删除数据库、表,修改表字段,就略过了。

表内容增删改查(基本操作)

(略)

函数

聚合函数

计算从列中取得的值,返回一个单一的值,如:COUNT、SUM、MAX、MIN

标量函数

基于输入值,返回一个单一的值,如:UCASE、LCASE、NOW、MID、LENGTH

标量函数往下细分,还有很多种类:

  • 字符函数
  • 数学函数
  • 日期函数

甚至我们也可以自定义函数。

(一般在程序代码中比较少,即使用到了,查一下API,也比较快的,所以就不展开了)。

流程控制函数

  • IF
  • CASE
SELECT IF(1>0,'A','B');
SELECT 
CASE 
WHEN grade<60 then '不及格'
WHEN grade>=60 then '及格'
ELSE '未知'
END
FROM score;

模糊查询

通配符

代替一个或多个真正的字符,与LIKE 关键字一起使用

alt

BETWEEN AND

查询某一列在指定的规范内的记录,包括两个边界

select * from score where grade between 80 and 100

IN

查询某一列中的值在列出的内容列表中

select * from student where city in ('北京','上海','广东')

IS NUL

  • 查询没有邮箱的 IS NUL
  • 查询有邮箱的 IS NOT NULL

分组

分组查询就是按某列的值进行分组,相同的值分成一组,然后可以对此组内进行求平均、求和等计算

alt

SELECT 列名,查询表达式
FROM  <表名>  
WHERE  <条件>
GROUP BY <分组字段>
HAVING 分组后的过滤条件
ORDER BY 列名 [ASC,DESC]
LIMIT 偏移量,条数

子查询

  • 子查询就是指出现在其它SQL语句中的SELECT语句
  • Outer Query/ Inner Query
  • 子查询指嵌套在查询内部,且必须始终出现在圆括号中
  • 子查询可以包含多个关键字或条件
  • 子查询的外层查询可以是: SELECT INSERT UPDATE SET等
  • 子查询可以返回常量、一行数据、一列数据或其它子查询

example:

# 查询年龄大于平均年龄的学生
SELECT * from student WHERE age > (SELECT ROUND(AVG(age),2) FROM student)

表连接

连接类型

  • JOIN=INNER JOIN=CROSS JOIN 内连接
  • LEFT [OUTER] JOIN 左外连接
  • RIGHT [OUTER] JOIN 右外连接
  • ON 连接条件

alt

连接条件

使用ON关键字来设定连接条件,也可以使用WHERE来代替

  • ON来设定连接条件
  • 也可以使用WHERE来对结果进行过滤

内连接

显示左表和右表中符合条件的

SELECT * FROM student INNER JOIN score ON student.id = score.student_id;

左外连接

显示左表的全部和右表符合条件的

SELECT * FROM student LEFT JOIN score ON student.id = score.student_id;

右外连接

显示右表的全部和左表符合条件的

SELECT * FROM student RIGHT JOIN score ON student.id = score.student_id;

更多表连接

SELECT student.name,course.name,score.grade FROM score 
INNER JOIN student ON student.id = score.student_id
INNER JOIN course ON course.id = score.course_id;

在node中的使用

基本使用

npm install mysql2
(async function() {

    const mysql = require('mysql2/promise');

    const connection = await mysql.createConnection({
        host: '127.0.0.1',
        user: 'root',
        password: '你的密码',
        database: '你自己的数据库'
    });

   /**
      * arr返回一个数组,第一个数组是记录的值,第二个数组是记录中包含的字段的信息
     * 解构赋值
     */
    let [users] = await connection.query("SELECT * FROM user");
    console.log(users);

    users.forEach(user => {
        console.log(user.username);
    });

})();

上面的查询是比较容易,那我们将SQL变为insert,它就得这样来写了:

const [rs] = await connection.query("INSERT INTO user (name, age) VALUES ('"+ name +"', '"+ age +"')");

我们可以通过返回的rs,通过它来判断是否插入成功:

// 影响行数
if (rs.affectedRows > 0) {
    // 添加成功
} else {
    // 添加失败
}

不过上面的SQL语句这样写起来是不舒服的,我们可以用es6来优化一下:

const [rs] = await connection.query(`INSERT INTO user (name, age) VALUES ('${name}', '${age}')`);

当然还有更优雅的使用占位符。example:

connection.query('SELECT ??,?? FROM ?? WHERE ?? = ?', ['id','username','users','id',1])
  • ??:字段名、表名
  • ?:值

sequelize

sequelize在我之前的博客里面有提到过,它就是一个ORM,有了它之后,我们只要写好Model,然后调用方法就好了,不需要再写sql语句了。

它支持很多种数据库:

  • Postgres
  • MySQL
  • SQLite
  • Microsoft SQL Server

如果想用mysql的话,必须这样来写:

const Sequelize = require('sequelize');

const sequelize = new Sequelize('你的数据库名', 'root', '你的密码', {
    host: '127.0.0.1',
    dialect: 'mysql'
});
// 测试有没有连接通
sequelize.authenticate().then(() => {
    console.log('连接成功')
}).catch(err => {
    console.log('连接失败')
});

在运行时,还得自己手动安装一下mysql2的依赖。

alt

包是将它放在了devDependencies里面了,因为前文有说了:不同的人可能会选择不同的数据库,不一定是mysql

alt

const User = sequelize.define('User', {
    id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
    },
    username: {
        type: Sequelize.STRING(50),
        allowNull: false
    },
    age: {
        type: Sequelize.TINYINT,
        allowNull: false
    },
    gender: {
        type: Sequelize.ENUM('男', '女'),
        allowNull: false
    }
}, {

    timestamps: false,
    tableName: 'user'
});

User.findAll().then( users => {
    users.forEach(user => {
        console.log(user.get('username'));
    })
} )

我们在查到数据之后,也可以更新数据

User.findById(1).then(user => {
    // console.log(user);
    user.set('username', 'zpu');
    user.save();
})

时区问题

在连接数据库的配置里面,可能需要设置一个timezone的属性,它的值可以通过所支持的时区列表来得到。

{
    host:'127.0.0.1'
    port:'3306'
    dialect:'mysql'
    timezone:'Asia/Shanghai'
}

定义模型

public define(modelName: String, attributes: Object, options: Object): Model
  • modelName:模型名称,自定义
  • attributes:模型中包含都数据,每一个数据映射对应表中都每一个字段
  • options:模型(表)的设置

attributes字段值描述:

  • type:字段类型,String|DataTypes|...
  • allowNull:是否允许为空,默认为true
  • defaultValue:默认值,默认为null
  • unique:值唯一,默认为false
  • primaryKey:是否为主键,默认为false
  • field:数据库中字段的实际名称
  • autoIncrement:是否自增,默认false

options:模型(表)的设置

  • timestamps:是否给每条记录添加 createdAt 和 updatedAt 字段,并在添加新数据和更新数据的时候自动设置这两个字段的值,默认为true
  • paranoid:设置 deletedAt 字段,当删除一条记录的时候,并不是真的销毁记录,而是通过该字段来标示,即保留数据,进行假删除,默认为false
  • freezeTableName:禁用修改表名; 默认情况下,sequelize将自动将所有传递的模型名称(define的第一个参数)转换为复数。 默认为false
  • tableName:手动设置表的实际名称
  • indexs: 索引,数组对象,如[{ name: 'uname',fields: ['username'] }]

创建模型实例对象

两种方式:

  • new 模型对象()
  • 模型对象.build

模型对象实例操作:

  • 模型对象.get(key: String) 获取某个属性(字段)的值
  • 模型对象.set(key: String, value: any) 设置某个属性(字段)的值
  • 模型对象.validate() 验证模型数据
  • 模型对象.save() 验证该实例,如果通过验证,则持久化到数据库中
  • 模型对象.update(updates: Object) updates:要更新的字段,调用该方法等同于调用.set()然后.save()
  • 模型对象.destroy() 销毁该实例(假删除或真删除)

除了通过模型创建出来的实例对单条数据进行操作,也可以通过模型类对整个对应的表进行操作

  • 模型.findById(id: Number | String | Buffer)

  • 模型.findOne(options: Object) options.where:搜索条件

  • 模型.findOrCreate(options: Object)

搜索特定记录或创建它(如果没有对应记录)

  • 模型.findAll(findOptions: Object)

在数据库中搜索多个记录,返回数据和总计数 findOptions.where:搜索条件 findOptions.limit:记录条数限制 findOptions.offset:记录偏移 findOptions.order:记录排序方式

  • 模型.findAndCountAll(findOptions: Object)

在数据库中搜索多个记录,返回数据和总计数 findOptions.where:搜索条件 findOptions.limit:记录条数限制 findOptions.offset:记录偏移 findOptions.order:记录排序方式 与findAll类似,但是返回值包含 count 属性 - 返回数据与总计数

复合过滤 / OR / NOT 查询

通过Sequelize.Op,example:

.findOne({
    where: {
        field: val,
        field: {[Op.eq]: val}
        [Op.or]: [
            {field: [1,2,3]},
            {field: {[Op.gt]: 10}}
        ]
    }
})

限制,偏移,顺序和分组操作数据集

.findOne({
    where:...,
    limit: n,
    offset: n,
    order: [ [field, type], [field, type]... ],
    group: [field, field,...]
})

统计等操作

.count({
    where: {}
})
.max(field, {where:{}})
.min(field, {where:{}})
.sum(field, {where: {}})

关联查询与预加载

  • HasOne :model1.hasOne(model2)
  • HasMany :model1.hasMany(model2)
  • BelongsTo :model1.belongsTo(model2)
  • BelongsToMany : model1.belongsToMany(model2)

步骤:

  1. 首先给关联的字段定义外键关系
  2. 在调用hasOne或hasMany等方法的时候,通过第二个参数设置对象:{foreignKey: 当前关联表的字段,如uid}
  3. 在查询中使用 include 去设置关联的外键表模型,如:include: [MessageModel]
// 定义外键关系
references: {
    model: 关联的外键表,如User
    key: 关联的外键表的字段,如id
}

上面说的可能会有一些抽象,下面举一个例子吧,留言版的modal有一个字段会关联用户。(用户和留言是一对多的关系)

uid: {  // 其他的表的字段,把当前字段定义为外键
    type: Sequelize.INTEGER(10),
    defaultValue: 0,
    references: {
        model: UserModel,
        key: 'id'
    }
},

上面的uid是定义在MessageModel下面的,现在假设要查指定的留言,并带出用户名:

MessageModel.belongsTo(UserModel, {
    foreignKey: 'uid'
});

const data = await MessageModel.findById(1, {
    include: [UserModel]
});

//可以通过data.User.username取到用户名 (说明User是define UserModel时的名称)

如果是要寻找指定的用户,且带出留言:

UserModel.hasMany(MessageModel, {
    foreignKey: 'uid'
});

const data = await UserModel.findById(4, {
    include: [MessageModel]
});

数据库迁移

就像git一样,我们可以使用Sequelize迁移来帮助我们跟踪数据库的更改,并在各个不同时期的数据库状态之间进行切换 使用Sequelize迁移,需要安装 sequelize-cli 工具

alt

目录初始化

npx sequelize init

alt

  • config:包含配置文件,它告诉CLI如何连接数据库(它有好几个环境,是根据NODE_ENV来映射的)
  • models:包含您的项目的所有模型
  • migrations:包含所有迁移文件
  • seeders:包含所有种子文件

model初始化

一般在做项目时,先写好Model,再通过命令来创建数据库。

npx sequelize model:create --name User --attributes id:INTEGER

运行 model:generate / model:create 命令以后,会:

  • 在 models 文件夹中创建了一个 user 模型文件(供程序使用)
  • 在 migrations 文件夹中创建了一个名字像 XXXXXXXXXXXXXX-create-user.js 的迁移文件(供迁移使用)

执行迁移

所谓迁移,就是对数据库进行结构的创建,升级(修改)等操作。

运行db:migrate

  • 会在数据库中创建一个 SequelizeMeta 表,用于记录每次的迁移记录
  • 执行 migrations 文件下的满足条件(SequelizeMeta表)的脚本

alt

需要注意的是db:migrate不会帮你创建数据库,如果要创建,就运行db:create

迁移脚本

alt

如上图所示,需要有两个方法:updown。返回Promise。

在这个里面,我们能做的不仅仅是createTable,还有像修改列或者添加列等。

这里有一些API,能做的事太多了。

alt

撤销迁移

db:migrate:undo
    - 撤销上一次的迁移操作
db:migrate:undo:all
    - 撤销所有的迁移操作
db:migrate:undo --name 具体迁移脚本

种子文件

顾名思义,就是初始化的数据

npx sequelize seed:generate --name demo-user

alt

db:seed 指定种子文件
    运行指定种子文件
db:seed:all
    运行所有种子文件
db:seed:undo --seed 指定种子文件
    撤销指定种子文件
db:seed:undo:all
    撤销所有种子文件
存储记录
    默认情况下seed不记录过程,如果需要记录则需要单独设置,在配置文件中增加
    seederStorage
        存储引擎:none、json、mongodb、sequelize
    seederStoragePath
        存储路径(json有效)
    seederStorageTableName
        存储表名,mongodb和sequelize有效

话说个人觉得存储记录,并不是那么地重要。

项目实战

通过上面的这些命令,我们就可以自己手写Models和迁移脚本。然后写完之后,一键生成数据库、表和初始数据。

然后引入model这个文件夹下的index.js,假如我们有一个User的模型。那么直接使用:

const Models = require('../models');
Models.User  // 这样就可以得到模型对象,然后就很方便了。

eggjs里面,也有一个egg-sequelize的插件,可以用来做这样的事。

总结

结合数据库迁移这一块,其实我在去年搞的一个DPL项目能够有一个更加好的方案。

当然也许有人会说,为什么不直接用eggjs这个方案呢。我个人是觉得如果项目不大,就自己整一下。在整的过程,慢慢地去理解eggjs为什么要那么做,这样一来,对我的帮助还是比较大的。

本文链接:www.my-fe.pub/post/mysql-note.html

-- EOF --

Comments

评论加载中...

注:如果长时间无法加载,请针对 disq.us | disquscdn.com | disqus.com 启用代理。