MySQL

Структура базы данных

databases

 ┌{ university }
 ├
 │
 │
 │
 │
 ├
 │
 │
     

databases

CREATE DATABASE `university`;
    

USE `university`;
    

tables

 ┌{ university }
 ├─┬{ students }
 │ ├
 │ ├
 │ ├
 │ ├
 ├─┬{ groups }
 │ ├
 │ ├
     

tables

CREATE TABLE `students` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `course` INT(11) NOT NULL,
  `birthday` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)
);
    

tables

CREATE TABLE `groups` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
);
    

fields

 ┌{ university }
 ├─┬{ students }
 │ ├──{ id:Integer }
 │ ├──{ name:String }
 │ ├──{ course:Integer }
 │ ├──{ birthday:Date }
 ├─┬{ groups }
 │ ├──{ id:Integer }
 │ ├──{ name:String }
     

Типы данных

Строковые

Строковые

Числовые

Числовые. UNSIGNED

Числовые

Бинарные

Бинарные

Даты

Другие

GEOMETRY

CREATE TABLE `students` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `address` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`id`)
);
    

GEOMETRY

INSERT INTO `students`
(`id`, `name`, `address`)
VALUES (1, 'Anna', POINT(-120.626, 50.831));
    

INSERT INTO `students`
(`id`, `name`, `address`)
VALUES (2, 'Polina', POINT(-34.245, 23.934));
    

GEOMETRY

SELECT `id`, `name` FROM `students`
ORDER BY ST_Distance_Sphere(
    `address`,
    POINT(-23.45, 12.35)
) ASC
LIMIT 1;
    

+----+--------+
| id | name   |
+----+--------+
|  2 | Polina |
+----+--------+
    

CRUD

от англ. create, read, update, delete — «создать, прочесть, обновить, удалить»

Create

INSERT INTO `students`
(`id`, `name`, `course`, `birthday`)
VALUES
(1, 'Anna', '4', '1995-02-25');
    

Create. Auto increment

INSERT INTO `students`
(`id`, `name`, `course`, `birthday`)
VALUES
(0, 'Anna', '4', '1995-02-25');
    

Read

SELECT `id`, `name`, `course`,
       `birthday` AS `secret_date`
FROM `students`;
    

+----+------+--------+-------------+
| id | name | course | secret_date |
+----+------+--------+-------------+
|  1 | Anna |      4 | 1995-02-25  |
+----+------+--------+-------------+
    

Read

SELECT * FROM `students`;
    

+----+------+--------+------------+
| id | name | course | birthday   |
+----+------+--------+------------+
|  1 | Anna |      4 | 1995-02-25 |
+----+------+--------+------------+
    

Update

UPDATE `students`
SET `course` = 5
WHERE `id` = 1;
    

Delete

DELETE FROM `students`
WHERE `id` = 1;
    

JOIN

Внешние ключи

FOREIGN KEY

CREATE TABLE `students` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` NVARCHAR(255) NOT NULL,
  `group_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_st_gr` FOREIGN KEY (`group_id`)
      REFERENCES `groups` (`id`)
);
    

FOREIGN KEY

ALTER TABLE `students`
ADD CONSTRAINT `fk_st_gr` FOREIGN KEY (`group_id`)
    REFERENCES `groups` (`id`);
    

Виды JOIN

• INNER
• FULL OUTER
• LEFT OUTER
• RIGHT OUTER

Данные

      students              |      groups
                            |
id name         group_id    |     id  name
-- ----         --------    |     --  ----
1  Фёдор         1          |     1    ФТ
2  Константин    2          |     2    КН
3  Александр     NULL       |     3    ПИ
4  Михаил        4          |     4    МТ
5  Максим        4          |
    

INNER JOIN

SELECT `students`.`id` AS `s_id`,
       `students`.`name` AS `s_name`,
       `groups`.`name` AS `g_name`
FROM `students`
INNER JOIN `groups`
ON `students`.`group_id` = `groups`.`id`
    

INNER JOIN

s_id  s_name     g_name
----  --------   ------
1     Фёдор       ФТ
2     Константин  КН
4     Михаил      МТ
5     Максим      МТ
    

OUTER JOIN

• LEFT OUTER JOIN
• RIGHT OUTER JOIN

LEFT JOIN

SELECT `students`.`id` AS `s_id`,
       `students`.`name` AS `s_name`,
       `groups`.`name` AS `g_name`
FROM `students`
LEFT JOIN `groups`
ON `students`.`group_id` = `groups`.`id`
    

LEFT JOIN

s_id   s_name    g_name
----  --------   ------
1     Фёдор       ФТ
2     Константин  КН
3     Александр   NULL
4     Михаил      МТ
5     Максим      МТ
    

RIGHT JOIN

SELECT `students`.`id`,
       `students`.`name`,
       `groups`.`name` AS `g_name`
FROM `students`
RIGHT JOIN `groups`
ON `students`.`group_id` = `groups`.`id`
    

RIGHT JOIN

id    name       g_name
--    --------   ------
1     Фёдор       ФТ
2     Константин  КН
NULL  NULL        ПИ
4     Михаил      МТ
5     Максим      МТ
    

Индексы

Первичный ключ

CREATE TABLE `students` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` NVARCHAR(255) NOT NULL,
  `group_id` INT(11) NOT NULL,
  PRIMARY KEY (id)
);
    

Поиск данных

SELECT * FROM `students` WHERE `name` = 'Anna';
    

EXPLAIN

mysql> EXPLAIN SELECT * FROM `students` WHERE `name` = 'Anna';
+----+-------------+----------+------+---------------+------+---------+------+------+------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+----------+------+---------------+------+---------+------+------+------------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL | 336  | Using where|
+----+-------------+----------+------+---------------+------+---------+------+------+------------+
    

CREATE INDEX

CREATE INDEX `name_idx` ON `students`(`name`);
    

CREATE INDEX

mysql> EXPLAIN SELECT * FROM `students` WHERE `name` = 'Anna';
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | ALL  | name          | NULL | NULL    | NULL |   28 |    8.33  | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
    

Уникальный индекс

SELECT * FROM `students`
WHERE `email` = 'example@gmail.com';
    

CREATE UNIQUE INDEX `email` ON `students`(`email`);
    

Уникальный индекс

EXPLAIN SELECT * FROM `students` WHERE `email` = 'example@gmail.com';
+----+-------------+----------+-------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table    | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+-------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | const | email         | email | 32      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+-------+---------------+-------+---------+-------+------+----------+-------+
    

Составной индекс

SELECT * FROM `students`
WHERE `group_id` = 1 AND `gender` = 'female';
    

CREATE INDEX `group_id_gender`
ON `students`(`group_id`, `gender`);
    

Составной индекс

SELECT * FROM `students`
WHERE `group_id` <= 3 AND `gender` = 'female';
    

Плюсы индексов

• высокая скорость поиска
• консистентность данных засчет уникальных индексов

Минусы индексов

• расходуют память
• замедление операции вставки и обновления

ORM

Установка

npm install sequelize
    

var Sequelize = require('sequelize');
    

Подключение к DB

var sequelize = new Sequelize('db','user','pass', {
  host: 'localhost',
  dialect: 'mysql' // 'mariadb','sqlite',
                   // 'postgres','mssql'
});
    

Подключение к DB

var uri = 'mysql://user:pass@localhost:3306/dbname';
    

var sequelize = new Sequelize(uri, {
  dialectOptions: { charset: 'utf8' }
});
    

Типы данных

Строковые

Числовые

Числовые. UNSIGNED

INTEGER.UNSIGNED
    

Бинарные

Даты

Пространственные

Другие

Модели

Модели. Объявление

sequelize.define('name', {attributes}, {options});
    

Модели. Атрибуты

var Student = sequelize.define('student', {
  id: {
    type: Sequelize.INTEGER.UNSIGNED,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true
  },
  name: {...},
  course: {...}
}, {...});
    

Именование

var Student = sequelize.define('student', {
  id: {...},
  name: {...},
  groupId: {
    type: Sequelize.INTEGER,
    field: 'group_id'
  }
}, {...});
    

Модели. Getters & setters

var Student = sequelize.define('student', {
  name: {
    get: function () {
      return this.getDataValue('name').toLowerCase();
    },
    set: function (value) {
      this.setDataValue('name', value.toUpperCase());
    }
  }
}, {...});
    

Модели. Валидаторы

var Student = sequelize.define('student', {
  id: {...},
  email: {
    type: Sequelize.STRING,
    validate: {
      isEmail: true
    }
  }
}, {...});
    

Модели. Конфигурация

var Student = sequelize.define('student', {...}, {
  timestamps: true,
  underscored: true,
  freezeTableName: true,
  tableName: 'students'
});
    

Создание таблицы

Student.sync({ force: true });
    

Student.sync()
  .then(function () {
    // :)
  })
  .catch(function (err) {
    // :(
  });
    

Удаление таблицы

Student.drop()
  .then(function () {})
  .catch(function (err) {});
    

CRUD

Create

Student.create({
  name: 'Anna',
  course: 4,
  birthday: '1995-02-25'
});
    

Read

Student.findOne({
  where: {
    name: 'Anna',
    course: 4
  }
}).then(function (studentModel) {
  return studentModel.get('email');
});
    

Read. Projection

Student.findOne({
  where: { name: 'Anna' },
  attributes: ['id', 'name', 'course']
});
    

Read. Операторы

Student.findOne({
  where: {
    name: {
      $like: 'A%'
    },
    birthday: {
      $gt: '1995-01-01'
    }
  }
});
    

Read

Student.findOne({
  where: { id: 23 }
});
    

Student.findById(23);
    

Read

Student.findAll({
  where: {
    name: 'Anna',
    course: 4
  }
});
    

Update

Student.update(
  { course: 5 },
  {
    where: { id: 23 }
  }
);
    

Delete

Student.destroy({
    where: { id: 23 }
});
    

Комбинации. findOrCreate

Student.findOrCreate({
  where: { name: 'Anna' },
  defaults: {
    name: 'Anna',
    course: 4,
    birthday: '1995-02-25'
  }
});
    

Комбинации. upsert

ALTER TABLE `students`
ADD UNIQUE KEY `idx_students_name_birthday` (`name`, `birthday`);
    

Student.upsert({
  name: 'Anna',
  course: 5,
  birthday: '1995-02-25'
});
    

JOIN

Внешние связи. belongsTo

Student.belongsTo(Group);
    

Внешние связи. belongsTo

Student.belongsTo(Group, { as: 'student_group' });
    

Внешние связи. hasOne

Curator.hasOne(Group);
    

Внешние связи. hasMany

Group.hasMany(Student);
    

Внешние связи. foreignKey

Student.belongsTo(Group, {
  foreignKey: 'fk_student_group'
});
    

Внешние связи

Include

Student.findOne({
  where: { name: 'Anna' },
  include: [
     {
       model: Group,
       attributes: ['name']
     }
  ]
});
    

Include

Student.findOne({
  where: { name: 'Anna' },
  include: [
    {
      model: Group,
      include: [
        { model: Curator }
      ]
    }
  ]
});
    

Include. required

Student.findOne({
  where: { name: 'Anna' },
  include: [
     {
       model: Group,
       required: false
     }
  ]
});
    

Индексы

Уникальный индекс

sequelize.define('student', {...}, {
  indexes: [
    {
      unique: true,
      fields: ['email']
    }
  ]
});
    

Составной индекс

sequelize.define('student', {...}, {
  indexes: [
    {
      name: 'name_course',
      fields: ['name', 'course'],
    }
  ]
});
    

Транзакции

sequelize.transaction(function (t) {
  return Promise.all([
    Student.findById(3)
      .then(function (student1) {
          Student.update(
            { scholarship: student1.get('scholarship') + 1000 }
            { where: { id: 3 }, transaction: t }
          )
      }),
    Student.findById(4)
      .then(function (student2) {
          Student.update(
            { scholarship: student2.get('scholarship') - 1000 }
            { where: { id: 4 }, transaction: t }
          )
      })
  ]);
});
    

CAP теорема

Теорема CAP - эвристическое утверждение о том, что в любой реализации распределённых вычислений возможно обеспечить не более двух из трёх следующих свойств: согласованность данных, доступность, устойчивость к разделению.

Cогласованность данных

(англ. consistency) — во всех вычислительных узлах в один момент времени данные не противоречат друг другу

Доступность

(англ. availability) — любой запрос к распределённой системе завершается корректным откликом, однако без гарантии, что ответы всех узлов системы совпадают

Устойчивость к разделению

(англ. partition tolerance) — расщепление распределённой системы на несколько изолированных секций не приводит к некорректности отклика от каждой из секций

NoSQL

MongoDB

Установка

Типы данных

CRUD

Create. database

use university
    

db === university
    

Create. collection

db.students.insert({
    name: 'Aнна',
    group: 'ФИИТ-401'
})
    

Create

db.students.insert([
  {
      name: 'Aнна',
      group: 'ФИИТ-401'
  },
  {
      name: 'Екатерина',
      group: 'КН-402'
  }
])
    

Read

db.students.find()
    

{
    _id : ObjectId('56cc30e2e52c943bf62fff72'),
    name : 'Анна',
    group : 'ФИИТ-401'
}
    

ObjectId('56cc30e2e52c943bf62fff72')

Read

db.students.find({ name: 'Анна' })
    

db.students.find({ name: 'Анна' }).toArray()
    

[{
    _id : ObjectId('56cc30e2e52c943bf62fff72'),
    name : 'Анна',
    group : 'ФИИТ-401'
},
{
    _id : ObjectId('56cc30e2e52c943bf62fff73'),
    name : 'Анна',
    group : 'КН-402'
}]
    

Read

db.students.findOne({ name: 'Анна' })
    

{
    _id : ObjectId('56cc30e2e52c943bf62fff72'),
    name : 'Анна',
    group : 'ФИИТ-401'
}
    

Read. Projection

db.students.find({ name: 'Анна' }, { _id: 0, group: 1 }).toArray()
    

[
  { group : 'ФИИТ-401' },
  { group : 'КН-401' }
]
    

Read. Операторы

db.students.find({
  course: { $gt : 2 }
})
    

Read. Регулярные выражения

db.students.find({
    group: /ФИИТ/
})
    

Read. Поиск по вложенным документам

{
    name: 'Анна',
    grades: {
        javascript: [1,1,1,1,1,1,1,1,1,1],
        verstka: [1,1,1,1,1,1,0.5,1,1,1,0]
    }
}
    

db.students.find({
    'grades.javascript.9': 1
})
    

Update

db.students.update({
    group: /4\d{2}/
}, {
    $set: { course: 4 }
}, {
    multi: true
})
    

Update. Increment

db.students.update({
}, {
    $inc: { course: 1 }
}, {
    multi: true
})
    

Delete

db.students.remove({ course: 1 })
    

Delete. justOne

db.students.remove({ course: 1 }, 1)
    

Delete. collection

db.students.drop()
    

JOIN

JOIN

Индексы

ensureIndex

db.students.ensureIndex({ 'name' : 1 })
    

db.students.ensureIndex(
  { name : 1 },
  { unique: true }
)
    

ensureIndex. Несколько полей

db.students.ensureIndex({
  name : 1,
  course: -1
})
    

Транзакции

Транзакции

ODM

Установка

npm install mongoose
    

var mongoose = require('mongoose');
    

Подключение к DB

var uri = 'mongodb://user:pass@localhost:port/database';
    

mongoose.connect(uri, function (err) {
    // обработка ошибки
});
    

Модели

Модели. Объявление

var Schema = mongoose.Schema;
    

var studentSchema = new Schema({
  name: String,
  course: {
    type: Number,
    default: 1
  },
  group: String
});
    

var Student = mongoose.model('Student', studentSchema);
    

Типы данных

Модели. Getters & setters

var studentSchema = new Schema({
  name: {
    get: function(value) {
        return value.toLowerCase();
    },
    set: function(value) {
        return value.toUpperCase();
    }
  }
});
    

Модели. Валидаторы

var studentSchema = new Schema({
  name: {
    type: String,
    required: true
  },
  course: {
    type: Number,
    min: 1,
    max: 6
  }
});
    

CRUD

Create

var student = new Student({
    name: 'Анна',
    course: 4,
    group: 'ФИИТ-401'
});
    

student.save(function (err, savedStudent) {
    if (err) {
      // обработка ошибки
    }
});
    

Read. findOne

var query = Student.findOne({ group: 'ФИИТ-401' });
    

query.where('name').equals('Анна');
    

query.exec(function (err, student) {
  console.log(student);
});
    

{
  _id: '58c044b044e17cf64a20a9aa',
  name: 'Анна',
  course: 4,
  group: 'ФИИТ-401'
}
    

Read. findOne

Student.findOne({ group: 'ФИИТ-401', name: 'Анна' },
  function (err, student) {
    console.log(student);
  }
);
    

Read. findById

Student.findOne({ _id: id }, callback);
    

Student.findById(id, callback);
    

Read. Projection

Student
  .findOne({ name: 'Анна' })
  .select({ name: 1, group: 1 })
  .exec(callback);
    

Student
  .findOne({ name: 'Анна' })
  .select('name group')
  .exec(callback);
    

{
  _id: '58c04c5a05b513f855fe1319',
  name: 'Анна',
  group: 'ФИИТ-401'
}
    

Read. Find all

Students.find({ name: 'Анна' }, function (err, students) {
  console.log(students);
})
    

[{
  _id: '58c04c5a05b513f855fe1319',
  name: 'Анна',
  group: 'ФИИТ-401'
}]
    

Update

Student.findById(id, function (error, student) {
  student.course = 5;
  student.save(function (err, updatedStudent) {
    if (err) {
      // обработка ошибки
    }
  });
});
    

Update

Student.update(
  { _id: id },
  {
    $set: { course: 5 }
  },
  function (err, student) {}
);
    

Find and update

Student.findByIdAndUpdate(id,
  {
    $set: { course: 5 }
  },
  { upsert: true },
  function (err, student) {}
);
    

Delete

Student.findOne({ name: 'Анна' }, function (error, student) {
  student.remove();
});
    

Student.findByIdAndRemove(id, function (error, deletedStudent) {
  console.log(deletedStudent._id);
});
    

Pseudo-join

var group = new Group({ _id: 2, name: 'ФИИТ-401' });
    

group.save(function (err) {
  if (err) {...}
  var student = new Student({
    name: 'Анна',
    _group: group._id
  });
  student.save(function (err) {...});
});
    

Pseudo-join

Student
  .findOne({ name: 'Анна' })
  .populate('_group')
  .exec(function (err, student) {
    console.log(student._group.name);
  });
    

// 'ФИИТ-401'
    

Индексы

На определенное поле

var studentSchema = new Schema({
  name: {
    type: String,
    index: true
  }
});
    

Уникальный индекс

var studentSchema = new Schema({
  name: {
    type: String,
    index: true,
    unique: true
  }
});
    

Составной индекс

var studentSchema = new Schema({
  name: String,
  course: Number,
  birthday: Date
});
    

studentSchema.index({ name: 1, course: -1 });