Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Epic PHP #4

101b5adab18a468a3dfe63f77980ccb9?s=47 Roma
March 10, 2014

Epic PHP #4

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

101b5adab18a468a3dfe63f77980ccb9?s=128

Roma

March 10, 2014
Tweet

Transcript

  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 нормальных форм, уровней, до которых нормализуют

    данные • Всех их помнят только задроты и маньяки • В вебе обычно используют третью нормальную форму
  24. None
  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
  61. None
  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