http://sulfurzona.com/
News
Service
Magazine
Software (Battle City Game, Wallpaper manager, Superpad, VG-NOW, Puzzle Game, Netler Internet Browser, ..)
Dune Game (Dune III, Dune IV, Cheats, Forum, ..)
Games free
Turbo Pascal (Assembler, Docs, Sources, Debbugers, ..)
Books (Docs for developers)
Forum
Guest book
Компьютерная диагностика двигателя автомобиля (адаптер К-линии)Компьютерная диагностика двигателя автомобиля (адаптер К-линии)
 
 
Корректор осанки купить
Спортивные товары. Онлайн-консультации по выбору товара.
formex.ru

PHP и WEB для новичков (часть 12):

 

Ключи, индексы и сложные запросы

 
PHP и WEB для новичков
На этот раз будет достаточно много теории, но понимание её очень необходимо для эффективной работы с БД. Поэтому советую пройти данный материал вдумчиво и неторопливо.
До этих пор мы имели дело с одной таблицей в БД. Таблица называлась mp3 и содержала данные об MP3-файлах. Я специально не стал делать в этой таблице поля для хранения имени исполнителя или названия альбома.
 
Дело в том, что таблица БД должна представлять собой сущность реального мира. Такой сущностью есть песня в файле MP3, исполнитель или альбом. Ведь исполнитель уникален и может иметь в своём репертуаре разные песни.
 
К тому же и альбомы у исполнителя тоже могут быть многочисленны, а уж песни разбиты по альбомам. Всё это можно структурировать для упрощения каталогизации и работы с БД.
Поэтому имеет смысл исполнителей и альбомы организовать в форме отдельных таблиц и связать с таблицей песен через ссылку. Схематически это можно изобразить так (рис. 1).
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 1
 
Обычно так строят схемы БД. Я поясню значение элементов этой схемы.
Каждый большой прямоугольник представляет собой таблицу. На данной схеме их три: mp3 (песни в файлах MP3); album (перечень альбомов); performer (все имеющиеся в БД исполнители).
Для компактности иллюстрации таблица mp3 показана не полностью, но как видно, в неё добавлены новые поля: id, perf_id, album_id.
 
Поскольку таблицы в какой-то мере представляют совокупность предметов и явлений реального мира, то объекты, хранящихся в них, т.е. записи должны быть уникальными.
Действительно, какие бы два предмета или явления мы не рассматривали, между ними всегда есть разница, иначе это были бы не два объекта, а один. Аналогично нет смысла в двух одинаковых записях таблицы, хотя, практически это можно сделать.
 

Ключи

 
Для обеспечения уникальности в таблице выделяют одно поле, что гарантированно является уникальным для таблицы. Это поле называют первичным ключом (primary key, PK).
В большинстве случаев для таблицы сложно или невозможно выделить первичный ключ из информационных полей. Как пример можно рассмотреть БД сотрудников какого-то учреждения.
Если таблица содержит ФИО работника, дату рождения, отдел, специальность, должность, то, какое бы поле не взяли, оно не будет уникальным, ведь существует вероятность, что в одном отделе будут работать двое сотрудников с одинаковыми данными должностями, специальностями, годами рождения или даже однофамильцы.
 
В таком случае каждой записи таблицы присваивают идентификатор - некоторое число, уникальное для таблицы (не повторяющееся внутри таблицы) и делают его первичным ключом. Так мы и сделали, добавив для таблицы mp3 ещё одно поле id и сделали его первичным ключом. О полях perf_id и album_id чуточку позже.
 
Аналогичным образом мы создали ещё две таблицы: performer (исполнители) с тем же полем id и именем или названием коллектива в поле name; album (альбомы) с полем id, названием альбома title и годом выхода year.
На схеме также видно стрелки, показывающие связи (зависимости) между таблицами. Разумеется, если есть песня, то она принадлежит какому-то альбому. Аналогично она принадлежит какому-то исполнителю.
Для упрощения задачи, будем считать, что песня принадлежит лишь какому-то одному альбому и одному исполнителю. Поэтому для каждой песни (в записи таблицы mp3) можем добавить ссылки на запись в таблице performer и ссылку на запись в таблице album. Этими ссылками и будут поля perf_id и album_id.
 
Если поле одной таблицы является ссылкой на запись другой таблицы, то такое поле называется внешним ключом (foreign key, FK).
 

Индексы

 
С ключами разобрались. Теперь речь пойдёт об индексах. Представьте себе, что вам необходимо работать с некоторой энциклопедией. Энциклопедия представляет собой набор статей. Вам необходимо быстро находить необходимую статью.
В таком случае имеет смысл разместить статьи в алфавитном порядке. Если необходимо найти некоторое слово внутри статьи, то логично иметь в энциклопедии алфавитный указатель.
 
Такие самые указатели существуют и в таблицах БД и называются индексами. Их действие прозрачно для пользователя - индекс следует создать в таблице только один раз, а дальше сервер сам решает, как и когда ими пользоваться.
Рассмотрим пример с таблицей mp3. Для начала приведём её к новому виду, который мы уже обсудили выше. Добавим в таблицу поля id, album_id и perf_id в phpMyAdmin во вкладке Структура (рис. 2)
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 2
 
далее добавляем эти поля (рис. 3).
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 3
 
После добавления новых полей удалим все записи из таблицы mp3 (закладка Обзор > Очистить).
После чего задумаемся, какие же индексы нам нужны. Когда решают такой вопрос, то обычно размышляют, какие операции по выборке будут проводиться над таблицей.
Во-первых, поле id является первичным ключом, а по ключевому полю всегда строится индекс, причём это особый тип индекса - уникальный индекс. Если в таблице есть поле с уникальным индексом, то сервер не позволит вставить в таблицу запись с уже существующим индексом.
 
Первичный ключ создаётся в phpMyAdmin кликом на ссылке Первичный в списке полей в закладке Структура.
Как только поле id стал первичным ключом, в списке полей он стал подчёркнутым, а в списке индексов под списком полей видим индекс по этому полю.
Поскольку мы должны связывать эту таблицу по полям album_id и perf_id с таблицами album и performer, то нам необходимо будет делать выборки по этим полям. Значит, необходимы индексы для этих полей. Создание индекса по полю выполняется в phpMyAdmin кликом на ссылке Индекс в списке полей в закладке Структура (рис. 4).
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 4
 
Создание индекса ощутимо повышает скорость выборки из таблицы. Поэтому, на первый взгляд кажется, что можно создать индексы сразу для всех полей. На самом деле это не очень удачная идея.
Если рассматривать пример с энциклопедией, то индексация (т.е. алфавитный указатель или размещение статей в алфавитном порядке) безусловно, увеличит скорость поиска.
 
Но если стоит задача добавления в энциклопедию новых статей, их удаление или редактирование, то львиная часть времени этих операций будет тратиться именно на обновление индекса - например, добавление номера страницы во многие позиции алфавитного указателя.
 
То же самое происходит и с таблицами БД. Чем больше индексов имеет таблица, тем больше скорость поиска в ней, но и меньше скорость добавления, удаления и изменения записей.
Поэтому имеет смысл создавать индексы только по тем полям, по которым непосредственно будет вестись поиск, а именно по тем, которые будут указываться в условии WHERE SQL-запросов. На самом деле скорость выборки из таблицы по проиндексированному полю почти не зависит от количества записей в ней.
 

Выборка из нескольких таблиц

 
 
 
 
 
Теперь рассмотрим запросы не по одной таблице, а по двум или больше. Для этого создадим уже описанные таблицы album и performer, перейдя непосредственно к базе multimedia и, создав их так же, как создавали таблицу mp3. Также создадим первичные ключи из полей id в этих таблицах.
 
Для того, чтобы было на чём тренироваться, добавим пару записей в каждую таблицу. Обратите внимание, что ключ id у нас уникальный, поэтому повторения значений в этом поле исключены. В поле id будем записывать числа по порядку - 1, 2, и т.д. Например, у меня получилась такая таблица album (рис. 5)
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 5
 
и таблица performer (рис. 6)
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 6
 
Обратите внимание, что хотя тут и есть некоторое соответствие - альбом с id=1 принадлежит исполнителю с id=1, а альбом с id=2 принадлежит исполнителю с id=2, но это просто случайность из-за малого количества песен в примере и не является правилом. Теперь таблица mp3 выглядит как на рис. 7.
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 7
 
Тут обратите внимание, что значения полей album_id и perf_id указывают на те id таблиц album и performer, которым принадлежат соответствующие песни, например, первая запись имеет album_id=1, что значит, что она принадлежит альбому с id=1, т.е. N3XT.
 
Теперь, когда у нас есть кое-какой материал для практики, рассмотрим, какие SQL-запросы мы можем выполнять для этих данных. Давайте вспомним, как мы делали выборку (SELECT) из одной таблицы. Это было приблизительно так:
 
SELECT filename FROM mp3
 
Такой запрос означал выборку всех полей filename из таблицы mp3. Теперь поставим перед собой задачу вывести названия песен и их исполнителей. Я покажу формирование запроса по шагам. Во-первых, нам необходимо объединить в FROM две таблицы:
 
... FROM mp3, performer ...
 
Теперь следует указать какие поля нужно выбирать:
 
SELECT title, name FROM mp3, performer ...
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 8
 
Если выполнить данный запрос, то вы заметите, что он выдаст все песни и исполнителей, но без соответствия одного другому (рис. 8), что нас совершенно не устраивает, поэтому нужно связать эти две таблицы условием:
 
SELECT title, name FROM mp3, performer WHERE mp3.perf_id=performer.id
 
Данный запрос можно расценивать так: "выбрать поля title и name из таблиц mp3 и performer так, чтобы поле perf_id записи таблицы mp3 равнялось полю id таблицы performer". Теперь результат будет правильный (рис. 9).
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 9
 
На самом деле условие mp3.perf_id=performer.id реализует внешнее связывание (внешний ключ) - связь между таблицами mp3 и performer по полям id=perf_id. Что собственно нам и нужно.
Теперь рассмотрим выборку из трёх таблиц. Выберем все названия песен с именами исполнителей и альбомами, которым они принадлежат:
 
SELECT mp3.title, performer.name, album.title
FROM mp3, performer, album
WHERE mp3.album_id=album.id AND mp3.perf_id=performer.id
 
Кое-какая сложность состоит в том, что некоторые поля (например, id или title) существуют в разных таблицах. Поэтому, чтобы чётко указать принадлежность поля к таблице, следует указывать имя таблицы, и её поле через точку. Тем самым мы даём знать серверу, из какой именно таблицы поле мы имеем в виду. Полученный результат можно видеть на рис. 10.
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 10
 
Обратите внимание, что в результате имеются два столбца title. Если мы хотим, чтобы столбцы в таблице результата имели необходимое нам название, то можно воспользоваться так называемыми алиасами (alias). Алиасы указываются после названия поля или таблицы со служебным словом AS:
 
SELECT mp3.title AS song, performer.name AS performer, album.title AS album
FROM mp3, performer, album
WHERE mp3.album_id=album.id AND mp3.perf_id=performer.id
 
что даст результат на рис. 11.
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 11
 
Разумеется, можно добавить и другие условия в этот запрос. Например, выберем то же самое, но чтобы песни были новее 2000 года:
 
SELECT mp3.title AS song, performer.name AS performer, album.title AS album
FROM mp3, performer, album
WHERE mp3.album_id=album.id AND mp3.perf_id=performer.id AND mp3.year>2000
 
Если необходимо получить данные из одной таблицы, когда выборка выполняется из двух и более таблиц, но при этом не хочется перечислять все поля нужной таблицы, то достаточно указать принадлежность полей к таблице при помощи звёздочки:
 
SELECT performer.* FROM mp3, performer, album …
 

Вспомогательные функции

 
Думаю, имеет смысл порекомендовать функцию CONCAT, которая позволяет "сшить" в единую строку несколько значений, перечисленных через запятую:
 
SELECT CONCAT(performer.name, ' - ', mp3.title) AS title
FROM mp3, performer, album
WHERE mp3.album_id=album.id AND mp3.perf_id=performer.id
 
При этом, данную функцию допустимо указывать не только в секции SELECT, но и в условии WHERE. Получим результат на рис. 12.
 
PHP и WEB для новичков: Ключи, индексы и сложные запросы
Рис. 12
 
Ещё одна функция, которая может оказаться полезной - это LPAD, дополняющая слева строку str до длины count символом char. Формат функции:
 
LPAD( str, count, char )
 
Допустим, имеется таблица с полями целочисленных компонент даты рождения birth_year, birth_month, birth_day и надо не просто выдать даты в формате вида ГГГГ/ММ/ДД, а чтобы в случае коротких чисел месяца или дня они всё равно занимали по два символа, например, 1961/04/12, т.е. были строго форматированны.
 
SELECT CONCAT( birth_year, '/', LPAD( birth_month, 2, '0' ) , '/', LPAD( birth_day, 2, '0' ) ) AS birth
FROM …
 
Подробнее о базовых функциях языка SQL можно почитать, скачав книгу Мартина Грубера.
 
Продолжение следует…
 
© Владислав Демьянишин
 
 
На нашем сайте можно не только бесплатно скачать игры, но и документацию и книги по программированию на MIDLetPascal, Turbo Pascal 6, Turbo Pascal 7, Borland Pascal, по программированию устройств Sound Blaster, Adlib, VESA BIOS, справочник Norton Guide и много другой полезной информации для программистов, включая примеры решения реальных задач по созданию резидентных программ. Предлагаю также посетить Марья искусница - сайт о рукоделии (http://mariya-iskusnica.ru).
 

Журнал > Программирование > PHP и WEB для новичков (HTML, JavaScript, PHP, MySQL) > PHP и WEB для новичков (часть 12): Ключи, индексы и сложные запросы
 
 
 
641
 
ВКонтакте
Facebook
 
 
 
На главную страницу На предыдущую страницу На начало страницы
 
 
Украинский портАл Украина онлайн Рейтинг@Mail.ru Рейтинг Сайтов YandeG Rambler's Top100