Epic PHP #4

Слайды к четвертому занятию курса Epic PHP (http://epixx.ru/epic-php/).
Темы: базы данных, SQL, PDO

1. Epic PHP #4 Рома Лапин, Evercode Lab
2. БД
3. База данных Cовокупность материалов, систематизированных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью компьютера.
4. База данных
5. СУБД Система управления базами данных — совокупность программных и лингвистических средств общего или специального назначения, обеспечивающих управление созданием и использованием баз данных.
6. СУБД
7. СУБД или БД? Если по-простому: БД — данные в специальном виде СУБД — инструмент для управления ! Но работать вы будете по сути со всем вместе.
8. СУБД или БД? Да и всем пофиг на эту терминологию.
9. Почему не файлы?
10. Почему не файлы? • Медленный поиск нужной информации • Нет адекватных механизмов одновременной работы • И еще множество разных мелочей
11. СУБД для веба • MySQL • PostgresSQL • SQLite • MongoDB • CouchDB
12. СУБД для веба • MySQL • PostgresSQL • SQLite • MongoDB • CouchDB
13. Реляционные БД Само понятие (англ. relation — отношение) связано с разработками известного английского специалиста в области систем баз данных Эдгара Кодда (Edgar Codd), сотрудника фирмы IBM. В 1970 году им был создан формальный аппарат реляционной алгебры для обработки данных. Позже он сформулировал 12 правил, которым должна соответствовать любая система по управлению реляционными базами данных(RDBMS — Relation Database Management System).
14. SQL SQL (ˈɛsˈkjuˈɛl; англ. Structured Query Language — «язык структурированных запросов») — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. ! Стандартизирован, но отличается между разными СУБД.
15. Реляционные БД • Данные — набор простых таблиц (двумерных массивов), разбитых на строки и столбцы • У каждого столбца есть имя, уникальное в пределах таблицы • Все значения в одном столбце имеют один тип.
16. Primary Key Каждая строка имеет одно или несколько полей, набор значений в которых уникален в пределах таблицы. Этот набор называется первичным ключом (primary key) и служит для идентификации строки. Этот принцип не допускает, в частности, хранение в таблице совершенно одинаковых строк.
17. Primary Key Как правило в качестве Primary Key используют искусственное поле — числовой уникальный идентификатор записи (id).
18. Реляционные БД • Имя таблицы, имя столбца и первичный ключ однозначно определяют хранимый элемент данных • Результаты запросов к БД — таблицы данных (строки) • Последние могут быть объектом для последующих запросов
19. Пример в студию id name country 1 Иван Россия 2 Ким Япония 3 Жан Франция 4 Джон США
20. А где же relations?
21. Пример посложнее id name price country unit 1 рис 10 Китай кг 2 картошка 2 Россия кг 3 чай 6 Китай упак 4 хлеб 5 Россия шт 5 бананы 7 Коста-Рика кг 6 кола 9 США шт
22. Нормализация • исключение избыточности • устранение аномалий обновления • упрощение процедуры применения необходимых ограничений целостности • как правило, состоит в выделении избыточных данных в отдельные таблицы
23. Нормализация • Существует 8 нормальных форм, уровней, до которых нормализуют данные • Всех их помнят только задроты и маньяки • В вебе обычно используют третью нормальную форму
25. Нормальные формы • 1: одно значение для одного аттрибута в каждой строке • 2: 1 + каждый неключевой атрибут неприводимо зависит от первичного ключа • 3: 2 + каждый неключевой атрибут зависит только от ключа
26. 1 нормальная форма • Устраните повторяющиеся группы в отдельных таблицах. • Создайте отдельную таблицу для каждого набора связанных данных. • Идентифицируйте каждый набор связанных данных с помощью первичного ключа.
27. 2 нормальная форма • Создайте отдельные таблицы для наборов значений, относящихся к нескольким записям. • Свяжите эти таблицы с помощью внешнего ключа.
28. 3 нормальная форма • Устраните поля, не зависящие от ключа.
29. Декомпозируем id name short_name 1 Килограмм кг 2 Упаковка упак 3 Штука шт
30. Задание Как будет выглядеть таблица стран?
31. Пример посложнее в новом виде 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
32. Полезно понять • Теория БД — штука нетривиальная • Но для повседневного использования все нюансы знать не надо • Здравого смысла и интуиции часто хватает • А если есть еще и опыт...
33. Задание Составить нормализованную схему к базе данных вашего приложения-идеи. Минимум 3 таблицы там должно быть. ! Если нет идеи, то попробуйте составить схему БД для классического форума: пользователи, разделы, темы, сообщения и т.п.
34. Задание Составить схему БД для нашей группы epicphp. Необходимо предусмотреть хранение информации о студентах: имена, фамилии, даты рождения, интересы, родной город, места работы (название фирмы, должность, дата начала, дата конца), места учебы (название заведения, дата начала, дата конца).
35. MySQL Cвободная реляционная СУБД. Разработку и поддержку осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems, которая ранее приобрела шведскую компаниюMySQL AB. Продукт распространяется как под GNU General Public License, так и под собственной коммерческой лицензией.
36. Инструменты • mysql — да, в консоли • phpMyAdmin • Sequel Pro (Mac) • Workbench (all OS)
37. SQL
38. Группы команд • создание и изменение структуры самой базы • операции с данными (добавление, выбор, обновление, удаление и т.п.)
39. Короткая жизнь одной базы Создаем базу: ! CREATE DATABASE epicphp CHARACTER SET utf8; ! ! Удаляем базу: ! DROP DATABASE epicphp;
40. Задание Создайте базу данных epicphp для работы и экспериментов на сегодняшнем занятии.
41. Рождение таблицы 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;
42. Задание Создайте в базе таблицы, соответствующие сделанной ранее схемы для нашей группы.
43. Развитие таблицы ALTER TABLE pages ADD created_at datetime DEFAULT NULL; ! ALTER TABLE pages CHANGE title title VARCHAR(150); ! ALTER TABLE pages DROP slug;
44. Задание Ко всем таблицам в epicphp добавьте поля created_at и updated_at. Они будут указывать на даты создания и обновления записей.
45. Смерть таблицы DROP TABLE pages;
46. Смерть таблицы DROP TABLE pages;
47. Операции с данными • INSERT • SELECT • UPDATE • DELETE
48. CRUD • INSERT – Create • SELECT – Read • UPDATE – Update • DELETE – Delete
49. INSERT INSERT INTO cities (id, name) VALUES (2, "Ленинград") ! Добавляем в таблицу cities новую строку.
50. Задание Используя запрос INSERT, вставьте в таблицы в epicphp несколько строк данных. ! Hint: запросы сохраняйте в отдельный текстовый файл для последующего использования
51. UPDATE UPDATE cities SET name = «Санкт-Петербург» WHERE id = 2 ! Обновляем название в города в таблице cities для id равного 2.
52. DELETE DELETE FROM cities WHERE name = ‘Москва’ ! Обновляем название в города в таблице cities для id равного 2.
53. Задание Поэкспериментируйте с запросами UPDATE и DELETE. Например: удалите во всех таблицах записи с id между 2 и 4 включительно. Обновите у всех учеников имя на Иван или сделайте это только для 1, 3 и 5.
54. SELECT SELECT * FROM goods ORDER BY name ! Выбираем все строки со всеми полями из таблицы goods, упорядочивая строки по полю name.
55. SELECT SELECT name, price FROM goods ! Уточняем поля.
56. SELECT SELECT name, price FROM goods WHERE price<30 ORDER BY price DESC ! Добавляем условие, что поле price должно быть меньше 30.
57. Задание Выберите всю информацию по всем студентам. ! Выберите только имена и фамилии. ! Выберите только тех студентов, которые родились в Санкт-Петербурге.
58. Беспорядочные связи
59. Беспорядочные связи 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
60. Беспорядочные связи 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
62. Задание Добавьте в выборки студентов инфомацию по местам работы и учебы. Если мест работы у ученика нет, то информацию о нем выводим все равно. Если мест учебы у ученика нет, то информацию о нем не выводим.
63. Считаем SELECT COUNT(*) AS total FROM countries; ! SELECT COUNT(id) AS total FROM countries;
64. Меряемся SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM goods WHERE unit_id=2
65. Задание Добавьте в таблицу учеников поля: рост, размер обуви. ! Найдите самого выского, самого низкого, самого большеногого и наоборот. ! Посчитайте количество мест работы и учебы у любого ученика.
66. Создаем группировки SELECT country_id, MAX(price) AS max_price, MIN(price) AS min_price FROM goods GROUP BY country_id
67. Задание Посчитайте количество мест работы и учебы у всех учеников в одном запросе.
68. PHP + MySQL
69. Нативные драйверы • Для каждой БД в PHP есть свой модуль для работы с ней (mysql, mysqli, pgsql, mssql) • А есть PDO • абстрактная библиотека для работы с кучей разных баз
70. Как работать с MySQL • mysql • mysqli • PDO
71. mysql_ • Устарело • Удалено из языка в последних версиях • не ООП • не умеет Prepared Statements • ограниченный API
72. 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']);
73. mysqli • ООП + функции • Prepared Statements • Транзакции
74. 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']);
75. PDO • Почти все хорошее, что есть в mysqli • Абстрактный интерфейс для разных БД
76. PDO
77. PDO. Стыковка
78. 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']);
79. 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(); }
80. PDO. Ошибки • PDO::ERRMODE_SILENT • PDO::ERRMODE_WARNING • PDO::ERRMODE_EXCEPTION • $conn->errorCode() • $conn->errorInfo()
81. Задание Создайте новый скрипт, в котором подключитесь к созданной выше базе epicphp. Обязательно используйте обработку ошибок.
82. PDO. Query $name = $_GET[‘name’]; ! $data = $conn->query( 'SELECT * FROM myTable WHERE name = ' . $conn->quote($name) ); foreach($data as $row) { print_r($row); }
83. PDO. Query Query — простой и быстрый способ отправить запрос через PDO, но требует аккуратности и дополнительных действий при использовании в запросах пользовательских данных. Поэтому не рекомендуется к использованию в этом случае.
84. Задание Получите из базы записи всех студентов, используя query.
85. Prepared Statements <?php $pdo = new PDO('sqlite:users.db'); $pdo->query("SELECT name FROM users WHERE id = " . $_GET['id']);
86. Prepared Statements <?php $pdo = new PDO('sqlite:users.db'); $pdo->query("SELECT name FROM users WHERE id = " . $_GET['id']); за такой код можно и убить ! http://domain.com/?id=1%3BDELETE+FROM+users ! В $_GET['id'] будет 1;DELETE FROM users
87. Prepared Statements
88. Prepared Statements <?php $pdo = new PDO('sqlite:users.db'); $stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id'); $stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT); $stmt->execute();
89. Prepared Statements $stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT); $stmt->execute(); ! аналогично ! $stmt->execute(array('id' => $_GET[‘id’]));
90. Prepared Statements Вместо :id можно использовать неименованные параметры с помощью символа знака вопроса ‘?’, но это менее читаемо и не так практично, поэтому рекомендуется использовать именованные параметры.
91. Fetch while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { print_r($row); } ! ! По умолчанию возвращает строки таблицы результатов в виде ассоциативного массива.
92. Fetch modes • PDO::FETCH_ASSOC • PDO::FETCH_BOTH • PDO::FETCH_BOUND • PDO::FETCH_CLASS • PDO::FETCH_OBJ
93. FetchAll $stmt->execute(array('id' => $id)); $result = $stmt->fetchAll(); if ( count($result) ) { foreach($result as $row) { print_r($row); } } else { echo "No rows returned."; }
94. Задание Используя prepared statements, напишите скрипт, который будет выводить из базы информацию о студенте в зависимости от параметра id в url. ! Усложнение: передачу id сделайте с помощью формы с select полем, в котором должны выводиться имя и фамилия всех студентов.
95. Multiple Executions $stmt = $conn->prepare('INSERT INTO someTable VALUES(:name)'); $stmt->bindParam(':name', $name); $name = 'Keith'; $stmt->execute(); $name = 'Steven'; $stmt->execute(); ! // Запрос приготовлен и скомпилирован один раз. Параметры мы можем менять.
96. INSERT $stmt = $pdo->prepare('INSERT INTO someTable VALUES(:name)'); $stmt->execute(array( ':name' => 'Justin Bieber' )); // Affected Rows? echo $stmt->rowCount(); // 1
97. UPDATE $stmt = $pdo->prepare( 'UPDATE someTable SET name = :name WHERE id = :id' ); $stmt->execute(array( ':id' => $id, ':name' => $name )); echo $stmt->rowCount(); // 1
98. DELETE $stmt = $pdo->prepare( 'DELETE FROM someTable WHERE id = :id' ); $stmt->bindParam(':id', $id); $stmt->execute(); echo $stmt->rowCount(); // 1
99. 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(); }
100. Задание Берем праобраз блога и фигачим его на базе данных через PDO.
101. Задание Сделайте простое CRUD приложение для нашей базы студентов. Возможности: список, просмотр всей информации о каждом студенте, добавление, редактирование, удаление, фильтрация по значению поля.
102. Задание Попробуйте переписать приложения из предыдущих заданий, используя mysqli.
103. Индексы Оптимизируют поиск и запросы по выбранным полям. ! CREATE INDEX idx_pages_slug ON pages(slug); ! ! DROP INDEX idx_goods_country;
104. Транзакции Cо счета Петра на счет Ромы переводится сумма в $100. Счета находятся в одной базе данных. ! Одна команда UPDATE уменьшает счет Петра, вторая — увеличивает счет Ромы. ! Представьте, что первая команда прошла, а вторая нет. Причины? Счет заблокирован, после первой команды завис компьютер, произошел разрыв связи.
105. Транзакции Единый неделимый логический блок команд.
106. ACID • Atomicity • Consistency • Isolation • Durability
107. Транзакции BEGIN TRANSACTION; ! COMMIT; ! ROLLBACK;
108. SQLite
109. SQLite • БД без БД (не требует процесса или сервера) • Быстрая • Бесплатная • Простая • Функциональная
110. SQLite • sqlite_ функции • отлично работает с PDO • $pdo = new PDO('sqlite:users.db'); • Часто используется для локальной разработки
111. Абстракции • Doctrine2 DBAL • Doctrine2 ORM • ZF2 Db • ZF1 Db • Yii Active Record • Propel