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

Epic PHP #4

Roma
March 10, 2014

Epic PHP #4

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

Roma

March 10, 2014
Tweet

More Decks by Roma

Other Decks in Programming

Transcript

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

    View Slide

  2. БД

    View Slide

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

    View Slide

  4. База данных

    View Slide

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

    View Slide

  6. СУБД

    View Slide

  7. СУБД или БД?
    Если по-простому:

    БД — данные в специальном виде

    СУБД — инструмент для управления

    !
    Но работать вы будете по сути со всем вместе.

    View Slide

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

    View Slide

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

    View Slide

  10. Почему не файлы?
    • Медленный поиск нужной информации

    • Нет адекватных механизмов
    одновременной работы

    • И еще множество разных мелочей

    View Slide

  11. СУБД для веба
    • MySQL

    • PostgresSQL

    • SQLite

    • MongoDB

    • CouchDB

    View Slide

  12. СУБД для веба
    • MySQL

    • PostgresSQL

    • SQLite

    • MongoDB

    • CouchDB

    View Slide

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

    View Slide

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

    !
    Стандартизирован, но отличается между разными
    СУБД.

    View Slide

  15. Реляционные БД
    • Данные — набор простых таблиц
    (двумерных массивов), разбитых на
    строки и столбцы

    • У каждого столбца есть имя, уникальное
    в пределах таблицы

    • Все значения в одном столбце имеют
    один тип.

    View Slide

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

    View Slide

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

    View Slide

  18. Реляционные БД
    • Имя таблицы, имя столбца и первичный
    ключ однозначно определяют хранимый
    элемент данных

    • Результаты запросов к БД — таблицы
    данных (строки)

    • Последние могут быть объектом для
    последующих запросов

    View Slide

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

    View Slide

  20. А где же relations?

    View Slide

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

    View Slide

  22. Нормализация
    • исключение избыточности

    • устранение аномалий обновления

    • упрощение процедуры применения
    необходимых ограничений целостности

    • как правило, состоит в выделении
    избыточных данных в отдельные
    таблицы

    View Slide

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

    • Всех их помнят только задроты и
    маньяки

    • В вебе обычно используют третью
    нормальную форму

    View Slide

  24. View Slide

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

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

    • 3: 2 + каждый неключевой атрибут
    зависит только от ключа

    View Slide

  26. 1 нормальная форма
    • Устраните повторяющиеся группы в
    отдельных таблицах.

    • Создайте отдельную таблицу для
    каждого набора связанных данных.

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

    View Slide

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

    • Свяжите эти таблицы с помощью
    внешнего ключа.

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  32. Полезно понять
    • Теория БД — штука нетривиальная

    • Но для повседневного использования
    все нюансы знать не надо

    • Здравого смысла и интуиции часто
    хватает

    • А если есть еще и опыт...

    View Slide

  33. Задание
    Составить нормализованную схему к базе данных
    вашего приложения-идеи. Минимум 3 таблицы там
    должно быть.

    !
    Если нет идеи, то попробуйте составить схему БД
    для классического форума: пользователи, разделы,
    темы, сообщения и т.п.

    View Slide

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

    View Slide

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

    View Slide

  36. Инструменты
    • mysql — да, в консоли

    • phpMyAdmin

    • Sequel Pro (Mac)

    • Workbench (all OS)

    View Slide

  37. SQL

    View Slide

  38. Группы команд
    • создание и изменение структуры самой
    базы

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

    View Slide

  39. Короткая жизнь
    одной базы
    Создаем базу:

    !
    CREATE DATABASE epicphp CHARACTER SET utf8;

    !
    !
    Удаляем базу:

    !
    DROP DATABASE epicphp;

    View Slide

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

    View Slide

  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;

    View Slide

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

    View Slide

  43. Развитие таблицы
    ALTER TABLE pages ADD created_at datetime
    DEFAULT NULL;

    !
    ALTER TABLE pages CHANGE title title
    VARCHAR(150);

    !
    ALTER TABLE pages DROP slug;

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  47. Операции с данными
    • INSERT

    • SELECT

    • UPDATE

    • DELETE

    View Slide

  48. CRUD
    • INSERT – Create

    • SELECT – Read

    • UPDATE – Update

    • DELETE – Delete

    View Slide

  49. INSERT
    INSERT INTO cities (id, name) VALUES

    (2, "Ленинград")

    !
    Добавляем в таблицу cities новую строку.

    View Slide

  50. Задание
    Используя запрос INSERT, вставьте в таблицы в
    epicphp несколько строк данных.

    !
    Hint: запросы сохраняйте в отдельный текстовый
    файл для последующего использования

    View Slide

  51. UPDATE
    UPDATE cities SET name = «Санкт-Петербург»
    WHERE id = 2

    !
    Обновляем название в города в таблице cities для id
    равного 2.

    View Slide

  52. DELETE
    DELETE FROM cities WHERE name = ‘Москва’

    !
    Обновляем название в города в таблице cities для id
    равного 2.

    View Slide

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

    View Slide

  54. SELECT
    SELECT * FROM goods ORDER BY name

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

    View Slide

  55. SELECT
    SELECT name, price FROM goods

    !
    Уточняем поля.

    View Slide

  56. SELECT
    SELECT name, price FROM goods WHERE price<30
    ORDER BY price DESC

    !
    Добавляем условие, что поле price должно быть
    меньше 30.

    View Slide

  57. Задание
    Выберите всю информацию по всем студентам.

    !
    Выберите только имена и фамилии.

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  61. View Slide

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

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

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

    View Slide

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

    !
    SELECT COUNT(id) AS total FROM countries;

    View Slide

  64. Меряемся
    SELECT MAX(price) AS max_price, MIN(price) AS
    min_price

    FROM goods WHERE unit_id=2

    View Slide

  65. Задание
    Добавьте в таблицу учеников поля: рост, размер
    обуви.

    !
    Найдите самого выского, самого низкого, самого
    большеногого и наоборот.

    !
    Посчитайте количество мест работы и учебы у
    любого ученика.

    View Slide

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

    FROM goods GROUP BY country_id

    View Slide

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

    View Slide

  68. PHP + MySQL

    View Slide

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

    • А есть PDO

    • абстрактная библиотека для работы с
    кучей разных баз

    View Slide

  70. Как работать с MySQL
    • mysql

    • mysqli

    • PDO

    View Slide

  71. mysql_
    • Устарело

    • Удалено из языка в последних версиях

    • не ООП

    • не умеет Prepared Statements

    • ограниченный API

    View Slide

  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']);

    View Slide

  73. mysqli
    • ООП + функции

    • Prepared Statements

    • Транзакции

    View Slide

  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']);

    View Slide

  75. PDO
    • Почти все хорошее, что есть в mysqli

    • Абстрактный интерфейс для разных БД

    View Slide

  76. PDO

    View Slide

  77. PDO. Стыковка

    View Slide

  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']);

    View Slide

  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();

    }

    View Slide

  80. PDO. Ошибки
    • PDO::ERRMODE_SILENT

    • PDO::ERRMODE_WARNING

    • PDO::ERRMODE_EXCEPTION

    • $conn->errorCode()

    • $conn->errorInfo()

    View Slide

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

    View Slide

  82. PDO. Query
    $name = $_GET[‘name’];

    !
    $data = $conn->query(

    'SELECT * FROM myTable WHERE name = '

    . $conn->quote($name)

    );



    foreach($data as $row) {

    print_r($row);

    }

    View Slide

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

    View Slide

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

    View Slide

  85. Prepared Statements

    $pdo = new PDO('sqlite:users.db');

    $pdo->query("SELECT name FROM users WHERE id = " . $_GET['id']);

    View Slide

  86. Prepared Statements

    $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

    View Slide

  87. Prepared Statements

    View Slide

  88. Prepared Statements

    $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();

    View Slide

  89. Prepared Statements
    $stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);

    $stmt->execute();

    !
    аналогично

    !
    $stmt->execute(array('id' => $_GET[‘id’]));

    View Slide

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

    View Slide

  91. Fetch
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    print_r($row);

    }

    !
    !
    По умолчанию возвращает строки таблицы
    результатов в виде ассоциативного массива.

    View Slide

  92. Fetch modes
    • PDO::FETCH_ASSOC

    • PDO::FETCH_BOTH

    • PDO::FETCH_BOUND

    • PDO::FETCH_CLASS

    • PDO::FETCH_OBJ

    View Slide

  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.";

    }

    View Slide

  94. Задание
    Используя prepared statements, напишите скрипт,
    который будет выводить из базы информацию о
    студенте в зависимости от параметра id в url.

    !
    Усложнение: передачу id сделайте с помощью
    формы с select полем, в котором должны
    выводиться имя и фамилия всех студентов.

    View Slide

  95. Multiple Executions
    $stmt = $conn->prepare('INSERT INTO someTable VALUES(:name)');

    $stmt->bindParam(':name', $name);



    $name = 'Keith';

    $stmt->execute();



    $name = 'Steven';

    $stmt->execute();

    !
    // Запрос приготовлен и скомпилирован один раз. Параметры мы
    можем менять.

    View Slide

  96. INSERT
    $stmt = $pdo->prepare('INSERT INTO someTable VALUES(:name)');

    $stmt->execute(array(

    ':name' => 'Justin Bieber'

    ));



    // Affected Rows?

    echo $stmt->rowCount(); // 1

    View Slide

  97. UPDATE
    $stmt = $pdo->prepare(

    'UPDATE someTable SET name = :name WHERE id = :id'

    );

    $stmt->execute(array(

    ':id' => $id,

    ':name' => $name

    ));



    echo $stmt->rowCount(); // 1

    View Slide

  98. DELETE
    $stmt = $pdo->prepare(

    'DELETE FROM someTable WHERE id = :id'

    );

    $stmt->bindParam(':id', $id);

    $stmt->execute();



    echo $stmt->rowCount(); // 1

    View Slide

  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();

    }

    View Slide

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

    View Slide

  101. Задание
    Сделайте простое CRUD приложение для нашей
    базы студентов.

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

    View Slide

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

    View Slide

  103. Индексы
    Оптимизируют поиск и запросы по выбранным
    полям.

    !
    CREATE INDEX idx_pages_slug ON pages(slug);

    !
    !
    DROP INDEX idx_goods_country;

    View Slide

  104. Транзакции
    Cо счета Петра на счет Ромы переводится сумма в
    $100. Счета находятся в одной базе данных.

    !
    Одна команда UPDATE уменьшает счет Петра,
    вторая — увеличивает счет Ромы.

    !
    Представьте, что первая команда прошла, а вторая
    нет. Причины? Счет заблокирован, после первой
    команды завис компьютер, произошел разрыв связи.

    View Slide

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

    View Slide

  106. ACID
    • Atomicity

    • Consistency

    • Isolation

    • Durability

    View Slide

  107. Транзакции
    BEGIN TRANSACTION;

    !
    COMMIT;

    !
    ROLLBACK;

    View Slide

  108. SQLite

    View Slide

  109. SQLite
    • БД без БД (не требует процесса или
    сервера)

    • Быстрая

    • Бесплатная

    • Простая

    • Функциональная

    View Slide

  110. SQLite
    • sqlite_ функции

    • отлично работает с PDO

    • $pdo = new PDO('sqlite:users.db');

    • Часто используется для локальной
    разработки

    View Slide

  111. Абстракции
    • Doctrine2 DBAL

    • Doctrine2 ORM

    • ZF2 Db

    • ZF1 Db

    • Yii Active Record

    • Propel

    View Slide