Epic PHP #4

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

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