1 of 66

Устройство ИНТЕРНЕТА,

HTTP, HTML, верстка

Веб-сервер�Лекции 1 и 2�ЛР 1

Работа с данными�PHP, MySQL, веб-формы, фильтрация данных�Лекции 3 и 4�ЛР 2

2 of 66

Лекция 4: �Введение в базы данных. CRUD на SQL. Join'ы и агрегирующие функции

intervolga.ru/school/

Степан Александрович Овчинников,

к.т.н., ген.директор, MVP 1C-Битрикс

3 of 66

Зачем нужны БД?

как вообще хранить данные?

чем плохи файлики?

приоритеты

нагрузка
доступность
распределенность

Изучим

Базовые операции CRUD

Create (insert)
Read (select)
Update
Delete

Конструкции

where, order by
join с left

Функции sum, avg, count

4 of 66

Видео про БД от mail.ru

https://www.youtube.com/watch?v=SfYaAQ9-RnE&t=1s

https://www.youtube.com/watch?v=dGwkG2VyDTY&t=1s

https://www.youtube.com/watch?v=nbmEBvoVAp0&t=1s

https://www.youtube.com/watch?v=XkS3937Xn8M&t=1s

https://www.youtube.com/watch?v=4Tgvd6NPufs&t=1s

https://www.youtube.com/watch?v=9yWZ-LIsAII&t=1s

https://www.youtube.com/watch?v=HYg6O_YaFEk&t=1s

https://www.youtube.com/watch?v=ocmHxM52CLo&t=1s

5 of 66

Лидеры рынка реляционных БД

Есть три явных лидера по числу успешных кейсов в мире СУРБД.

MS, Oracle, MySQL и ее форки

MySQL — бесплатная open-source промышленная СУРБД, сопоставимая с конкурентами по возможностям.

В web-проектах занимает более 50% рынка.

6 of 66

MySQL – почти 50% веб-сайтов

7 of 66

MySQL – почти 50% веб-сайтов

8 of 66

А в РФ - 89%

MySQL

89%

MS “проспал” веб, Россия предпочитает бесплатный софт

В целом для веб-разработки MySQL и его форки— стандарт “де-факто”�
Догоняет Postgre, используются noSQL

9 of 66

В 2008 Sun Microsystems приобрела MySQL AB за 1 млрд долларов

В 2010 Oracle приобрела Sun Microsystems за 7,4 млрд долларов

10 of 66

В 2008 Sun Microsystems приобрела MySQL AB за 1 млрд долларов

В 2010 Oracle приобрела Sun Microsystems за 7,4 млрд долларов

11 of 66

Что должен знать профессионал про MySQL

12 of 66

Берем матрицу компетенций веб-разработчика

Стажер

Основы Mysql

Типы таблиц Mysql

Типы данных

типы join-ов

Junior

Select со всеми конструкциями, включая Join

Применение Alter

Отношение многие ко многим

Индексы, оптимизация

Работа с СУБД в консоли

Экспорт/Импорт данных

13 of 66

Middle и Senior

Middle

Настройка прав доступа

Знание основных функций в операторах SQL

Кэширование в MySQL

Триггеры

Проектирование структуры БД

Хранимые процедуры

Модели (4 шт) хранения деревьев

Нормализация/Денормализация

Senior

Транзакции, уровни изоляции

Репликации

14 of 66

MySQL - Стажер

Основы Mysql

http://www.mysql.ru/docs/ilyin/

http://www.mysql.ru/docs/man/Data_Definition.html

http://www.mysql.ru/docs/man/Data_Manipulation.html

http://www.mysql.ru/docs/man/Functions.html

Типы таблиц Mysql

https://habrahabr.ru/post/64851/

http://itif.ru/otlichiya-myisam-innodb/

http://www.mysql.ru/docs/man/Table_types.html

https://habrahabr.ru/post/108104/

https://ruhighload.com/%d0%a1%d1%80%d0%b0%d0%b2%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5+innodb+%d0%b8+myisam

Типы данных

https://vk.com/@websvirep-tipy-dannyh-v-mysql

http://www.mysql.ru/docs/man/Column_types.html

https://dev.mysql.com/doc/refman/5.7/en/data-types.html

https://ruhighload.com/%D0%92%D1%8B%D0%B1%D0%BE%D1%80+%D1%82%D0%B8%D0%BF%D0%BE%D0%B2+%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85+%D0%B2+mysql

Типы join-ов

https://vk.com/@websvirep-tipy-joinov-v-mysql

https://ru.wikipedia.org/wiki/Join_%28SQL%29

http://www.skillz.ru/dev/php/article-Obyasnenie_SQL_obedinenii_JOIN_INNER_OUTER.html

В описании формата запросов https://dev.mysql.com/doc/refman/5.7/en/join.html обратить внимание на возможные сочетания слов left/right/inner/outer

15 of 66

MySQL - Junior

Select со всеми конструкциями, включая Join

http://www.mysql.ru/docs/man/SELECT.html

Применение Alter

http://www.mysql.ru/docs/man/ALTER_TABLE.html

http://webew.ru/articles/2125.webew

http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

http://oracleplsql.ru/alter-table.html

Отношение многие ко многим

https://vk.com/@websvirep-mysql-otnoshenie-mnogie-ko-mnogim

Индексы, оптимизация

http://ruhighload.com/post/%D0%A0%D0%B0%D0%B1%D0%BE%D1%82%D0%B0+%D1%81+%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81%D0%B0%D0%BC%D0%B8+%D0%B2+MySQL

http://www.mysql.ru/docs/man/MySQL_indexes.html

Работа с СУБД в консоли

Экспорт/Импорт данных

https://rusadmin.biz/bloknot/import-i-eksport-bd-mysql-pri-pomoshhi-konsoli/

16 of 66

MySQL - Middle

Настройка прав доступа

https://geeksmagazine.org/post/7/kak-sozdat-polzovatelya-i-dat-emu-prava-v-mysql

Знание основных функций в операторах SQL

Кэширование в MySQL

http://adminunix.ru/ke-shirovanie-zaprosov-v-mysql/

https://habr.com/post/41166/

Триггеры

https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

Проектирование структуры БД

Хранимые процедуры

https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html

Модели (4 шт) хранения деревьев

https://www.slideshare.net/billkarwin/models-for-hierarchical-data

https://cpiv.bitrix24.ru/~fEu49

Нормализация/Денормализация

17 of 66

Разберем как это работает по шагам

18 of 66

Рекомендую sql-academy.org

19 of 66

Далее будем разбирать сквозной пример

Преподаватели

Занятия

Студенты

Группы

20 of 66

Таблица и select

21 of 66

Select c фильтром

22 of 66

Select c фильтром и сортировкой

23 of 66

Фильтрация по 2 полям

24 of 66

Связь двух таблиц

25 of 66

Связь двух таблиц

26 of 66

Разберем подробнее как работает join

27 of 66

Это условие объединения

28 of 66

Теоретически условие объединения может быть любым

29 of 66

А если НЕ написать условие объединения?

30 of 66

Декартово произведение – ”каждый с каждым”

Допустим есть таблицы А, В, С.

В каждой по 10 записей.

Сколько записей вернет запрос�

Select * from A

inner join B

inner join C�

31 of 66

Декартово произведение – ”каждый с каждым”

Допустим есть таблицы А, В, С.

В каждой по 10 записей.

Сколько записей вернет запрос�

Select * from A

inner join B

inner join C�не 10

и не 30

10*10*10=1000

32 of 66

Объединение и фильтрация

33 of 66

Структура БД. Понятие “Урок” и связи с другими таблицами

34 of 66

Собираем человеко-понятную запись об уроке. Урок+преподаватель

35 of 66

Собираем человеко-понятную запись об уроке. Урок+преподаватель+класс

36 of 66

Запись об уроке. Добавим фильтр и сортировку

37 of 66

Добавление �записи

39 of 66

Left join с условием

40 of 66

Select c подсчетом количества

Сколько уроков в расписании проводится в кабинете 13?

41 of 66

Агрегирующие функции

42 of 66

Агрегирующие функции

43 of 66

Общая стоимость заказов

44 of 66

Операция удаления

45 of 66

Удаление без where опасно!

46 of 66

Update c фильтром

47 of 66

Update без фильтра – опасен!

48 of 66

Решаем задачи и практикуемся

49 of 66

Учебная БД

1. Компьютерная фирма

Схема БД состоит из четырех отношений:

Product(maker, model, ctype)

PC(code, model, speed, ram, hd, cd, price)

Laptop(code, model, speed, ram, hd, screen, price)

Printer(code, model, color, ptype, price)

Отношение Product представляет производителя (maker), номер модели (model) и тип (PC - ПК, Laptop - ПК-блокнот или Printer - принтер). Предполагается, что номера моделей уникальны для всех производителей и типов продуктов. В отношении PC для каждого номера модели, обозначающего ПК, указаны скорость -speed (процессора в мегагерцах), общий объем RAM (в мегабайтах), размер диска -hd (в гигабайтах), скорость считывающего устройства CD (например, 4х) и цена - price. Отношение Laptop аналогично отношению РС за исключением того, что вместо скорости CD содержится размер экрана -screen (в дюймах). В отношении Printer для каждой модели принтера указывается, является ли он цветным - color (\'y\', если цветной), тип принтера - ptype (лазерный - Laser, струйный - Jet или матричный - Matrix) и цена.

50 of 66

Схема данных

51 of 66

Задачи

1. Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd

52 of 66

Ответ

Select model , speed, hd From pc Where price < 500

53 of 66

Задачи

54 of 66

Задачи

Select model , speed, hd From pc Where price < 500

55 of 66

Задачи

3. Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол.

56 of 66

Задачи

3. Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол.

select model, ram, screen from laptop where price > 1000

57 of 66

Задачи