Tweet
Tweet

Slide 1

Slide 1 text

Epic PHP #4 Рома Лапин, Evercode Lab

Slide 2

Slide 2 text

БД

Slide 3

Slide 3 text

База данных Cовокупность материалов, систематизированных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью компьютера.

Slide 4

Slide 4 text

База данных

Slide 5

Slide 5 text

СУБД Система управления базами данных — совокупность программных и лингвистических средств общего или специального назначения, обеспечивающих управление созданием и использованием баз данных.

Slide 6

Slide 6 text

СУБД

Slide 7

Slide 7 text

СУБД или БД? Если по-простому: БД — данные в специальном виде СУБД — инструмент для управления ! Но работать вы будете по сути со всем вместе.

Slide 8

Slide 8 text

СУБД или БД? Да и всем пофиг на эту терминологию.

Slide 9

Slide 9 text

Почему не файлы?

Slide 10

Slide 10 text

Почему не файлы? • Медленный поиск нужной информации • Нет адекватных механизмов одновременной работы • И еще множество разных мелочей

Slide 11

Slide 11 text

СУБД для веба • MySQL • PostgresSQL • SQLite • MongoDB • CouchDB

Slide 12

Slide 12 text

СУБД для веба • MySQL • PostgresSQL • SQLite • MongoDB • CouchDB

Slide 13

Slide 13 text

Реляционные БД Само понятие (англ. relation — отношение) связано с разработками известного английского специалиста в области систем баз данных Эдгара Кодда (Edgar Codd), сотрудника фирмы IBM. В 1970 году им был создан формальный аппарат реляционной алгебры для обработки данных. Позже он сформулировал 12 правил, которым должна соответствовать любая система по управлению реляционными базами данных(RDBMS — Relation Database Management System).

Slide 14

Slide 14 text

SQL SQL (ˈɛsˈkjuˈɛl; англ. Structured Query Language — «язык структурированных запросов») — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. ! Стандартизирован, но отличается между разными СУБД.

Slide 15

Slide 15 text

Реляционные БД • Данные — набор простых таблиц (двумерных массивов), разбитых на строки и столбцы • У каждого столбца есть имя, уникальное в пределах таблицы • Все значения в одном столбце имеют один тип.

Slide 16

Slide 16 text

Primary Key Каждая строка имеет одно или несколько полей, набор значений в которых уникален в пределах таблицы. Этот набор называется первичным ключом (primary key) и служит для идентификации строки. Этот принцип не допускает, в частности, хранение в таблице совершенно одинаковых строк.

Slide 17

Slide 17 text

Primary Key Как правило в качестве Primary Key используют искусственное поле — числовой уникальный идентификатор записи (id).

Slide 18

Slide 18 text

Реляционные БД • Имя таблицы, имя столбца и первичный ключ однозначно определяют хранимый элемент данных • Результаты запросов к БД — таблицы данных (строки) • Последние могут быть объектом для последующих запросов

Slide 19

Slide 19 text

Пример в студию id name country 1 Иван Россия 2 Ким Япония 3 Жан Франция 4 Джон США

Slide 20

Slide 20 text

А где же relations?

Slide 21

Slide 21 text

Пример посложнее id name price country unit 1 рис 10 Китай кг 2 картошка 2 Россия кг 3 чай 6 Китай упак 4 хлеб 5 Россия шт 5 бананы 7 Коста-Рика кг 6 кола 9 США шт

Slide 22

Slide 22 text

Нормализация • исключение избыточности • устранение аномалий обновления • упрощение процедуры применения необходимых ограничений целостности • как правило, состоит в выделении избыточных данных в отдельные таблицы

Slide 23

Slide 23 text

Нормализация • Существует 8 нормальных форм, уровней, до которых нормализуют данные • Всех их помнят только задроты и маньяки • В вебе обычно используют третью нормальную форму

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

Нормальные формы • 1: одно значение для одного аттрибута в каждой строке • 2: 1 + каждый неключевой атрибут неприводимо зависит от первичного ключа • 3: 2 + каждый неключевой атрибут зависит только от ключа

Slide 26

Slide 26 text

1 нормальная форма • Устраните повторяющиеся группы в отдельных таблицах. • Создайте отдельную таблицу для каждого набора связанных данных. • Идентифицируйте каждый набор связанных данных с помощью первичного ключа.

Slide 27

Slide 27 text

2 нормальная форма • Создайте отдельные таблицы для наборов значений, относящихся к нескольким записям. • Свяжите эти таблицы с помощью внешнего ключа.

Slide 28

Slide 28 text

3 нормальная форма • Устраните поля, не зависящие от ключа.

Slide 29

Slide 29 text

Декомпозируем id name short_name 1 Килограмм кг 2 Упаковка упак 3 Штука шт

Slide 30

Slide 30 text

Задание Как будет выглядеть таблица стран?

Slide 31

Slide 31 text

Пример посложнее в новом виде id name price country_id unit_id 1 рис 10 1 1 2 картошка 2 2 1 3 чай 6 1 2 4 хлеб 5 2 3 5 бананы 7 3 1 6 кола 9 4 3

Slide 32

Slide 32 text

Полезно понять • Теория БД — штука нетривиальная • Но для повседневного использования все нюансы знать не надо • Здравого смысла и интуиции часто хватает • А если есть еще и опыт...

Slide 33

Slide 33 text

Задание Составить нормализованную схему к базе данных вашего приложения-идеи. Минимум 3 таблицы там должно быть. ! Если нет идеи, то попробуйте составить схему БД для классического форума: пользователи, разделы, темы, сообщения и т.п.

Slide 34

Slide 34 text

Задание Составить схему БД для нашей группы epicphp. Необходимо предусмотреть хранение информации о студентах: имена, фамилии, даты рождения, интересы, родной город, места работы (название фирмы, должность, дата начала, дата конца), места учебы (название заведения, дата начала, дата конца).

Slide 35

Slide 35 text

MySQL Cвободная реляционная СУБД. Разработку и поддержку осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems, которая ранее приобрела шведскую компаниюMySQL AB. Продукт распространяется как под GNU General Public License, так и под собственной коммерческой лицензией.

Slide 36

Slide 36 text

Инструменты • mysql — да, в консоли • phpMyAdmin • Sequel Pro (Mac) • Workbench (all OS)

Slide 37

Slide 37 text

SQL

Slide 38

Slide 38 text

Группы команд • создание и изменение структуры самой базы • операции с данными (добавление, выбор, обновление, удаление и т.п.)

Slide 39

Slide 39 text

Короткая жизнь одной базы Создаем базу: ! CREATE DATABASE epicphp CHARACTER SET utf8; ! ! Удаляем базу: ! DROP DATABASE epicphp;

Slide 40

Slide 40 text

Задание Создайте базу данных epicphp для работы и экспериментов на сегодняшнем занятии.

Slide 41

Slide 41 text

Рождение таблицы CREATE TABLE `pages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `content` longtext NOT NULL, `slug` varchar(255) NOT NULL UNIQUE, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

Slide 42

Slide 42 text

Задание Создайте в базе таблицы, соответствующие сделанной ранее схемы для нашей группы.

Slide 43

Slide 43 text

Развитие таблицы ALTER TABLE pages ADD created_at datetime DEFAULT NULL; ! ALTER TABLE pages CHANGE title title VARCHAR(150); ! ALTER TABLE pages DROP slug;

Slide 44

Slide 44 text

Задание Ко всем таблицам в epicphp добавьте поля created_at и updated_at. Они будут указывать на даты создания и обновления записей.

Slide 45

Slide 45 text

Смерть таблицы DROP TABLE pages;

Slide 46

Slide 46 text

Смерть таблицы DROP TABLE pages;

Slide 47

Slide 47 text

Операции с данными • INSERT • SELECT • UPDATE • DELETE

Slide 48

Slide 48 text

CRUD • INSERT – Create • SELECT – Read • UPDATE – Update • DELETE – Delete

Slide 49

Slide 49 text

INSERT INSERT INTO cities (id, name) VALUES (2, "Ленинград") ! Добавляем в таблицу cities новую строку.

Slide 50

Slide 50 text

Задание Используя запрос INSERT, вставьте в таблицы в epicphp несколько строк данных. ! Hint: запросы сохраняйте в отдельный текстовый файл для последующего использования

Slide 51

Slide 51 text

UPDATE UPDATE cities SET name = «Санкт-Петербург» WHERE id = 2 ! Обновляем название в города в таблице cities для id равного 2.

Slide 52

Slide 52 text

DELETE DELETE FROM cities WHERE name = ‘Москва’ ! Обновляем название в города в таблице cities для id равного 2.

Slide 53

Slide 53 text

Задание Поэкспериментируйте с запросами UPDATE и DELETE. Например: удалите во всех таблицах записи с id между 2 и 4 включительно. Обновите у всех учеников имя на Иван или сделайте это только для 1, 3 и 5.

Slide 54

Slide 54 text

SELECT SELECT * FROM goods ORDER BY name ! Выбираем все строки со всеми полями из таблицы goods, упорядочивая строки по полю name.

Slide 55

Slide 55 text

SELECT SELECT name, price FROM goods ! Уточняем поля.

Slide 56

Slide 56 text

SELECT SELECT name, price FROM goods WHERE price<30 ORDER BY price DESC ! Добавляем условие, что поле price должно быть меньше 30.

Slide 57

Slide 57 text

Задание Выберите всю информацию по всем студентам. ! Выберите только имена и фамилии. ! Выберите только тех студентов, которые родились в Санкт-Петербурге.

Slide 58

Slide 58 text

Беспорядочные связи

Slide 59

Slide 59 text

Беспорядочные связи SELECT g.id, g.name, g.price, u.name AS unit, c.name AS country FROM units u, countries c, goods g WHERE u.id = g.unit_id AND c.id = g.country_id ORDER BY g.id

Slide 60

Slide 60 text

Беспорядочные связи SELECT g.id, g.name, g.price, u.name AS unit, c.name AS country FROM goods g INNER JOIN units u ON u.id=g.unit_id LEFT JOIN countries c ON c.id=g.country_id ORDER BY g.id

Slide 61

Slide 61 text

No content

Slide 62

Slide 62 text

Задание Добавьте в выборки студентов инфомацию по местам работы и учебы. Если мест работы у ученика нет, то информацию о нем выводим все равно. Если мест учебы у ученика нет, то информацию о нем не выводим.

Slide 63

Slide 63 text

Считаем SELECT COUNT(*) AS total FROM countries; ! SELECT COUNT(id) AS total FROM countries;

Slide 64

Slide 64 text

Меряемся SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM goods WHERE unit_id=2

Slide 65

Slide 65 text

Задание Добавьте в таблицу учеников поля: рост, размер обуви. ! Найдите самого выского, самого низкого, самого большеногого и наоборот. ! Посчитайте количество мест работы и учебы у любого ученика.

Slide 66

Slide 66 text

Создаем группировки SELECT country_id, MAX(price) AS max_price, MIN(price) AS min_price FROM goods GROUP BY country_id

Slide 67

Slide 67 text

Задание Посчитайте количество мест работы и учебы у всех учеников в одном запросе.

Slide 68

Slide 68 text

PHP + MySQL

Slide 69

Slide 69 text

Нативные драйверы • Для каждой БД в PHP есть свой модуль для работы с ней (mysql, mysqli, pgsql, mssql) • А есть PDO • абстрактная библиотека для работы с кучей разных баз

Slide 70

Slide 70 text

Как работать с MySQL • mysql • mysqli • PDO

Slide 71

Slide 71 text

mysql_ • Устарело • Удалено из языка в последних версиях • не ООП • не умеет Prepared Statements • ограниченный API

Slide 72

Slide 72 text

mysql_ $c = mysql_connect("example.com", "user", "password"); mysql_select_db("database"); $result = mysql_query("SELECT 'Hello!' AS _message FROM DUAL"); $row = mysql_fetch_assoc($result); echo htmlentities($row['_message']);

Slide 73

Slide 73 text

mysqli • ООП + функции • Prepared Statements • Транзакции

Slide 74

Slide 74 text

mysqli $mysqli = new mysqli("example.com", "user", "password", "database"); $result = $mysqli ->query( "SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL" ); $row = $result->fetch_assoc(); echo htmlentities($row['_message']);

Slide 75

Slide 75 text

PDO • Почти все хорошее, что есть в mysqli • Абстрактный интерфейс для разных БД

Slide 76

Slide 76 text

PDO

Slide 77

Slide 77 text

PDO. Стыковка

Slide 78

Slide 78 text

PDO. Стыковка $pdo = new PDO( 'mysql:host=example.com;dbname=database', 'user', 'password' ); $statement = $pdo ->query( "SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL" ); $row = $statement->fetch(PDO::FETCH_ASSOC); echo htmlentities($row['_message']);

Slide 79

Slide 79 text

PDO. Ошибки try { $conn = new PDO( 'mysql:host=localhost;dbname=myDatabase', $username, $password ); $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); }

Slide 80

Slide 80 text

PDO. Ошибки • PDO::ERRMODE_SILENT • PDO::ERRMODE_WARNING • PDO::ERRMODE_EXCEPTION • $conn->errorCode() • $conn->errorInfo()

Slide 81

Slide 81 text

Задание Создайте новый скрипт, в котором подключитесь к созданной выше базе epicphp. Обязательно используйте обработку ошибок.

Slide 82

Slide 82 text

PDO. Query $name = $_GET[‘name’]; ! $data = $conn->query( 'SELECT * FROM myTable WHERE name = ' . $conn->quote($name) ); foreach($data as $row) { print_r($row); }

Slide 83

Slide 83 text

PDO. Query Query — простой и быстрый способ отправить запрос через PDO, но требует аккуратности и дополнительных действий при использовании в запросах пользовательских данных. Поэтому не рекомендуется к использованию в этом случае.

Slide 84

Slide 84 text

Задание Получите из базы записи всех студентов, используя query.

Slide 85

Slide 85 text

Prepared Statements query("SELECT name FROM users WHERE id = " . $_GET['id']);

Slide 86

Slide 86 text

Prepared Statements query("SELECT name FROM users WHERE id = " . $_GET['id']); за такой код можно и убить ! http://domain.com/?id=1%3BDELETE+FROM+users ! В $_GET['id'] будет 1;DELETE FROM users

Slide 87

Slide 87 text

Prepared Statements

Slide 88

Slide 88 text

Prepared Statements prepare('SELECT name FROM users WHERE id = :id'); $stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT); $stmt->execute();

Slide 89

Slide 89 text

Prepared Statements $stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT); $stmt->execute(); ! аналогично ! $stmt->execute(array('id' => $_GET[‘id’]));

Slide 90

Slide 90 text

Prepared Statements Вместо :id можно использовать неименованные параметры с помощью символа знака вопроса ‘?’, но это менее читаемо и не так практично, поэтому рекомендуется использовать именованные параметры.

Slide 91

Slide 91 text

Fetch while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { print_r($row); } ! ! По умолчанию возвращает строки таблицы результатов в виде ассоциативного массива.

Slide 92

Slide 92 text

Fetch modes • PDO::FETCH_ASSOC • PDO::FETCH_BOTH • PDO::FETCH_BOUND • PDO::FETCH_CLASS • PDO::FETCH_OBJ

Slide 93

Slide 93 text

FetchAll $stmt->execute(array('id' => $id)); $result = $stmt->fetchAll(); if ( count($result) ) { foreach($result as $row) { print_r($row); } } else { echo "No rows returned."; }

Slide 94

Slide 94 text

Задание Используя prepared statements, напишите скрипт, который будет выводить из базы информацию о студенте в зависимости от параметра id в url. ! Усложнение: передачу id сделайте с помощью формы с select полем, в котором должны выводиться имя и фамилия всех студентов.

Slide 95

Slide 95 text

Multiple Executions $stmt = $conn->prepare('INSERT INTO someTable VALUES(:name)'); $stmt->bindParam(':name', $name); $name = 'Keith'; $stmt->execute(); $name = 'Steven'; $stmt->execute(); ! // Запрос приготовлен и скомпилирован один раз. Параметры мы можем менять.

Slide 96

Slide 96 text

INSERT $stmt = $pdo->prepare('INSERT INTO someTable VALUES(:name)'); $stmt->execute(array( ':name' => 'Justin Bieber' )); // Affected Rows? echo $stmt->rowCount(); // 1

Slide 97

Slide 97 text

UPDATE $stmt = $pdo->prepare( 'UPDATE someTable SET name = :name WHERE id = :id' ); $stmt->execute(array( ':id' => $id, ':name' => $name )); echo $stmt->rowCount(); // 1

Slide 98

Slide 98 text

DELETE $stmt = $pdo->prepare( 'DELETE FROM someTable WHERE id = :id' ); $stmt->bindParam(':id', $id); $stmt->execute(); echo $stmt->rowCount(); // 1

Slide 99

Slide 99 text

Object Mapping class User { public $first_name; public $last_name; public function full_name() { return $this->first_name . ' ' . $this->last_name; } } ! $result = $pdo->query('SELECT * FROM someTable'); $result->setFetchMode(PDO::FETCH_CLASS, 'User'); while($user = $result->fetch()) { echo $user->full_name(); }

Slide 100

Slide 100 text

Задание Берем праобраз блога и фигачим его на базе данных через PDO.

Slide 101

Slide 101 text

Задание Сделайте простое CRUD приложение для нашей базы студентов. Возможности: список, просмотр всей информации о каждом студенте, добавление, редактирование, удаление, фильтрация по значению поля.

Slide 102

Slide 102 text

Задание Попробуйте переписать приложения из предыдущих заданий, используя mysqli.

Slide 103

Slide 103 text

Индексы Оптимизируют поиск и запросы по выбранным полям. ! CREATE INDEX idx_pages_slug ON pages(slug); ! ! DROP INDEX idx_goods_country;

Slide 104

Slide 104 text

Транзакции Cо счета Петра на счет Ромы переводится сумма в $100. Счета находятся в одной базе данных. ! Одна команда UPDATE уменьшает счет Петра, вторая — увеличивает счет Ромы. ! Представьте, что первая команда прошла, а вторая нет. Причины? Счет заблокирован, после первой команды завис компьютер, произошел разрыв связи.

Slide 105

Slide 105 text

Транзакции Единый неделимый логический блок команд.

Slide 106

Slide 106 text

ACID • Atomicity • Consistency • Isolation • Durability

Slide 107

Slide 107 text

Транзакции BEGIN TRANSACTION; ! COMMIT; ! ROLLBACK;

Slide 108

Slide 108 text

SQLite

Slide 109

Slide 109 text

SQLite • БД без БД (не требует процесса или сервера) • Быстрая • Бесплатная • Простая • Функциональная

Slide 110

Slide 110 text

SQLite • sqlite_ функции • отлично работает с PDO • $pdo = new PDO('sqlite:users.db'); • Часто используется для локальной разработки

Slide 111

Slide 111 text

Абстракции • Doctrine2 DBAL • Doctrine2 ORM • ZF2 Db • ZF1 Db • Yii Active Record • Propel