| Запросы к базе данных по команде SELECT |
|
Чаще всего в приложениях Joomla применяются запросы по команде SELECT. Они служат для выборки данных из таблиц в базе данных. Ниже приведена простейшая структура запроса по команде SELECT. SELECT <список столбцов и выражений> FROM <главная таблица> JOIN <список операторов соединения> WHERE <условия отбора> ORDER BY <упорядочиваемые столбцы> LIMIT <смещение, число строк> Все перечисленные выше операторы совсем не обязательно включать в запрос по команде SELECT. Но уж если они включаются в запрос, то должны быть непременно перечислены в указанном выше порядке. Как поясняется далее в этой главе, преимущество класса JDatabaseQuery, применяемого для составления запросов базы данных SQL в коде приложений Joomla, заключается, в частности, в том, что в запрос можно вводить операторы в любом порядке. Но при выполнении запросов непосредственно в базе данных (например, средствами phpMyAdmin) необходимо строго придерживаться порядка следования, установленного для разных операторов SQL. Список столбцов и выражений В первой части запроса по команде SELECT указывается список столбцов и выражений, отображаемых в результатах запроса. Это должны быть заголовки столбцов в строках, возвращаемых из таблицы. Для обозначения таблицы, из которой извлекается столбец, служит псевдоним. Например, в запросе SELECT a.id, a.name FROM jos_content AS a таблица jos_content обозначается псевдонимом а. Следует иметь в виду, что все ключевые слова языка SQL принято указывать прописными буквами. И хотя в запросах SQL регистр не учитывается, тем не менее данное правило упрощает чтение запросов. Псевдонимы можно создавать и для имен столбцов таблицы. По умолчанию имя столбца в запросе совпадает с его именем в столбце, чего, как правило, достаточно. Но в некоторых случаях требуется указывать в запросе другое имя столбца (например, при наличии в двух таблицах столбцов с одинаковыми именами или при использовании выражения для обращения к столбцу). В подобных случаях для имени столбца создается псевдоним с помощью ключевого слова AS, как показано ниже. SELECT a.id AS article_id, a.name FROM jos_content AS a В данном примере столбец id будет отображаться как articleid. Следует, однако, иметь в виду, что знаки точки нельзя употреблять в именах столбцов и псевдонимов (разве что заключив их в кавычки), поскольку точками псевдонимы таблиц отделяются от имен столбцов. По желанию ключевое слово AS можно опустить, и тогда оно будет подразумеваться. Например, следующий запрос равнозначен приведенному выше: SELECT a.id article_id, a.name FROM jos_content a Но, как правило, ключевое слово AS желательно указывать в запросе явным образом, чтобы упростить чтение самого запроса. Для указания в запросе всех столбцов таблицы служит знак звездочки (*). Например, по запросу SELECT a.* FROM jos_content AS а из таблицы будут возвращены все строки. В этом случае указать псевдонимы столбцов нельзя. И наконец, вместо столбцов в запрос можно включить выражения. Это нередко делается в операторе GROUP BY, о котором речь пойдет далее. Оператор from. В операторе FROM перечисляются таблицы, из которых предстоит извлечь данные. Синтаксис языка SQL допускает два равнозначных способа соединения таблиц, что нередко приводит к путанице. Один из этих способов состоит в перечислении всех таблиц в операторе FROM и включении условий их соединения в операторе WHERE. А другой, более предпочтительный способ (по крайней мере, для Joomla) состоит в применении команды JOIN. Этот способ оказывается более предпочтительным потому, что он делает более ясным назначение запроса для того, кто читает исходный код. Когда оператор JOIN используется для соединения вторичных таблиц, в оператор FROM для запроса включается только главная таблица. А какая таблица является главной, или первичной? Как правило, это таблица, из которой требуется извлечь нечто главное: сведения о статье или пользователе. Поэтому для извлечения сведений об искомом элементе запрос обычно начинается с главной таблицы, а затем в него вводятся другие, связанные с ней таблицы, где хранятся дополнительные сведения о данном элементе. Таким образом, главная таблица является первичной для запроса. Как упоминалось ранее, с помощью ключевого слова AS обычно указывается короткий запрос, как, например, FROM jos_content AS а. Оператор JOIN Операторы JOIN служат для объединения столбцов из двух или более таблиц, что зачастую и делается в большинстве запросов. При построении баз данных SQL данные разных типов рекомендуется размещаться в отдельных таблицах. А это означает, что в одном запросе приходится извлекать информацию из нескольких таблиц. Так, в Joomla данные категории обычно хранятся в отдельной таблице (например, jos_categories), тогда как в связанных с ней таблицах (например, jos_content) — лишь идентификатор категории (в виде внешнего ключа). При составлении запроса на отображение статей из таблицы j os_content нередко требуется отображать имя категории или другие сведения о ней, а не только ее идентификатор. Именно для этого таблица категорий и присоединяется к запросу. Это дает возможность отображать имя категории или любой другой столбец данной категории. При освоении запросов SQL могут возникнуть определенные неясности в отношении операторов JOIN. Чаще всего применяются операторы внутреннего соединения (INNER JOIN) и левого соединения (LEFT JOIN), иногда еще называемого левым внешним соединением (LEFT OUTER JOIN). По умолчанию предполагается внутреннее соединение, если тип соединения не указан явно. Рассмотрим следующий простой пример запроса на отображения статей и их категорий: SELECT a.title, с.title AS category_title FROM jos_content AS a INNER JOIN jos_categories AS с ON a.catid = c.id В данном примере отображается название каждой статьи и ее категории. Для указания псевдонимов каждой таблицы и названия категории в данном запросе используется ключевое слово AS, иначе в запросе оказались бы два столбца с одинаковым именем title. Обратите также внимание на применение в этом запросе оператора INNER JOIN. Для того чтобы добиться аналогичного результата, можно было бы просто указать оператор JOIN, поскольку операция внутреннего соединения (INNER) подразумевается в нем по умолчанию. В последней строке рассматриваемого здесь запроса указан оператор ON, что очень важно. Ведь в нем, по существу, устанавливается взаимосвязь обеих таблиц для выполнения данного запроса. Оператор ON должен всегда сопутствовать оператору JOIN, иначе предполагаемый результат не будет получен. Так, если опустить в данном примере оператор ON, то в конечном итоге будут получены строки, состоящие из комбинации строк, извлекаемых из двух соединяемых таблиц. Например, в выборочных данных для версии Joomla 2.5 насчитывается 66 строк в таблице jos_content и 69 строк в таблице jos_categories, и если опустить в данном запросе оператор ON, то в результате его выполнения получится 4554 строки! В операторе ON нередко указывается внешний ключ для установления связи между двумя таблицами. В данном примере указан внешний ключ a.catid к таблице jos_categories. Имеется немало других примеров установления связи между двумя таблицами по внешнему ключу в базе данных Joomla, включая связи между таблицами jos_banners и jos_categories, jos_banners и jos_banner_clients, jos_ contact_details и jos_users и т.д. Рассмотрим далее отличие внутреннего соединения (INNER JOIN) от левого (LEFT JOIN). При внутреннем соединении требуется, чтобы запрашиваемые строки существовали в обеих таблицах. В предыдущем примере у каждой статьи, перечисляемой в запросе, имеется своя категория, что всегда справедливо для обычной базы данных в Joomla, где каждая статья относится только к одной категории. Поэтому в данном примере внутреннее и левое соединения дают один и тот же результат. Обратимся теперь к другому примеру. Допустим, требуется получить список статей, независимо от того, помечены ли они для блога по избранным статьям на первой странице веб-сайта. В таблице jos_content_f rontpage содержится строка для каждой избранной статьи, включая ее идентификатор и порядковый номер. Ниже приведен первый вариант запроса к базе данных. SELECT a.title, f.ordering FROM jos_content AS a INNER JOIN jos_content_frontpage AS f ON a.id = f.content_id Это запрос на отображение названия статьи и порядка ее расположения на первой странице. Указав в запросе только внутреннее соединение, мы включаем в запрос только статьи со строкой из таблицы jos_content_frontpage. Используя выборочные данные для версии Joomla 2.5, мы в итоге получим следующие строки: А теперь посмотрим, что произойдет, если воспользоваться в том же самом запросе левым соединением, как показано ниже. SELECT a.title, f.ordering FROM jos_content AS a LEFT JOIN jos_content_frontpage AS f ON a.id = f.content id В результате выполнения этого запроса будут получены все статьи из таблицы jos_ content, даже если в таблице jos_content_frontpage для них отсутствует строка. В этом случае в столбце f. ordering окажется пустое значение (NULL), означающее отсутствие статьи. Данный запрос можно изменить таким образом, чтобы указать, помечена ли статья для отображения на первой странице, воспользовавшись оператором CASE языка SQL, как показано ниже. SELECT a.title, CASE WHEN (f.content_id IS NULL) THEN 'No' ELSE 'Yes' END AS front_page FROM jos_content AS a LEFT JOIN jos_content_frontpage AS f ON a.id = f.content_id Во второй строке этого запроса используется выражение, введенное в команду SELECT для составления списка статей. Оператор CASE позволяет отображать различные значения в зависимости от результатов проверок разных логических условий. В данном случае проверяется, является ли пустым значение в столбце f. content_id. Если оно окажется пустым, значит, статья отсутствует в таблице j os_content_f rontpage, а следовательно, в результатах данного запроса напротив статьи отображается значение "No ". В противном случае статья присутствует в таблице j os_content_f rontpage, и поэтому в результатах данного запроса напротив статьи отображается значение "Yes". Ниже приведены пять первых строк, возвращаемых из базы данных в результате выполнения данного запроса. Title front_page Administrator Components No Beginners Yes ArchiveModule No Banner Module No Article CategoriesModule No Для извлечения данных из каждой требующейся таблицы в запросе можно указать сколько угодно операторов JOIN. При этом псевдоним каждой таблицы должен быть однозначным. Оператор where С помощью этого оператора можно отфильтровать строки таблицы, чтобы включить в запрос только те из них, которые удовлетворяют заданным условиям. Операторы WHERE могут быть как простыми, так и сложными. В приведенном ниже примере оператор WHERE введен в запрос, чтобы включить в него только опубликованные статьи. SELECT a.title, с.title AS category_title FROM jos_content AS a INNER JOIN jos_categories AS с ON a.catid = c.id a.state = 1 Несколько выражений в операторе WHERE можно связать логическим оператором AND или OR. Так, в следующем примере по запросу отображаются лишь те статьи, которые опубликованы и имеют ненулевую дату публикации в столбце publish_down: SELECT a.title, с.title AS category_title FROM jos_content AS a INNER JOIN jos_categories AS с ON a.catid = с id WHERE a.state = 1 AND a.publish_down != '0000-00-00 00:00:00' Напомним, что по умолчанию в столбце publish_down устанавливается значение '0000-00-00 00:00:00'. Это означает, что статья вообще не снята с публикации. В выборочных данных ни одна из статей не удовлетворяет этому условию, поэтому для проверки данного запроса следует перейти к компоненту Article Manager и ввести дату окончания публикации статьи. При повторном выполнении запроса эта статья должна быть отображена. Если выражения связываются в операторе WHERE логическим оператором OR, то по запросу из таблицы извлекаются любые строки, удовлетворяющие заданным условиям. Так, в приведенном ниже примере по запросу получаются строки таблицы с псевдонимом создания "Parks Webmaster" «ли названием категории "Plugins". SELECT a.title, с.title AS category_title, a.created_by_alias FROM jos_content AS a INNER JOIN jos_categories AS с ON a.catid = c.id WHERE a.created_by_alias = 'Paries Webmaster' OR c.title = 'Plugins* Следует иметь в виду, что в операторе WHERE могут быть указаны столбцы из любой таблицы. Если же выражения в операторе WHERE требуется связать обоими логическими операторами, AND и OR, то они заключаются в круглые скобки. Так, в следующем примере в запрос включены строки таблицы из предыдущего примера, но при условии, что название статьи должно начинаться с букв "Ed" или "Аи": SELECT a.title, с.title AS category_title, a.created_by_alias FROM jos_content AS a INNER JOIN jos_categories AS с ON a.catid = c.id WHERE (a.created_by__alias = 'Parks webmaster' OR с.title = 'Plugins') AND (a.title LIKE 'Ed%' OR a.title LIKE 'Au%') Следует заметить, что выражение LIKE допускает сопоставление с метасимвольным шаблоном, где знак процента обозначает совпадение с любым символом. Если бы в данный запрос было введено выражение a. title LIKE ' %abc% ', то сопоставление было бы сделано с любым столбцом, содержащим буквы ' abc' где угодно в тексте, тогда как выражение a. title LIKE ' %abc* означало бы сопоставление с любым текстом, оканчивающимся буквами ' abc'. Оператор ORDER BY Оператор ORDER BY служит в запросе для сортировки строк таблицы в нужном порядке. С помощью выражений ASC и DESC можно указать обычный (по восходящей от А до Z) или обратный (по нисходящей от Z до А) порядок сортировки соответственно. Для сортировки можно указать несколько столбцов, разделив их запятыми. Например, в следующем операторе ORDER BY: ORDER BY a.created_by_alias ASC, d.created DESC строки, извлекаемые из таблицы по запросу, сортируются в алфавитном порядке по столбцу created_by_alias. Если же у двух или более статей окажется одинаковое значение в столбце created_by_alias, они будут упорядочены по дате и времени создания, причем первой будет отображена самая свежая статья. Оператор limit В базе данных MySQL имеется возможность ограничить строки, возвращаемые по запросу из таблицы, с помощью оператора LIMIT. Синтаксис LIMIT хх, у у этого оператора позволяет ограничить запрос первыми строками у у, начиная со строки хх + 1. Так, если требуется ограничить запрос возвращаемыми из таблицы строками 6-15, в него следует ввести оператор LIMIT 5, 10. В этом случае пять первых строк пропускаются, а по запросу из таблицы возвращаются следующие десять строк. А если ввести в запрос оператор LIMIT 0, 10, то из таблицы будут возвращены первые десять строк. Если же строки пропускать не нужно, то достаточно опустить первый аргумент в данном операторе, написав его следующим образом: LIMIT 10. Формирование составных запросов с помощью оператора GROUP BY В обычных запросах по команде SELECT строки возвращаются из таблиц на том уровне детализации, на каком запрашиваются таблицы. Если же требуется подытожить данные, то для этой цели служит оператор GROUP BY. Запрос, включающий в себя оператор GROUP BY, называется составным. Если оператор GROUP BY присутствует в запросе, он должен следовать после оператора WHERE и перед оператором ORDER BY. Применение оператора GROUP BY оказывает влияние на перечисление столбцов в списке. В частности, перечислить в списке можно, во-первых, столбцы, используемые для группирования, а во-вторых, столбцы, указываемые в составных выражениях. И в этом есть свой смысл. Ведь в составном запросе несколько строк таблицы сводятся в одну, и поэтому в базе данных должно быть известно, каким образом подытоживать значения. Именно это и указывается в составных выражениях. Обратимся к конкретному примеру. Допустим, требуется получить число статей по каждому значению в столбце created_by_alias. Для этой цели служит приведенный ниже запрос. SELECT a.created_by_alias/ COUNT(*) AS article_count FROM jos_content AS a GROUP BY a.created_by_alias По этому запросу возвращается следующий результат:
В данном запросе указаны два столбца: createdbyalias и обобщающая функция COUNT. В качестве аргумента этой функции указывается знак звездочки ("*")• Эта функция подсчитывает количество строк в таблице для каждого значения из столбца created_ by_alias в отдельности. Обратите внимание на то, что в 12 статьях искомое значение в столбце created_by_alias не обнаружено, и поэтому они отображаются как пустые. В MySQL и других базах данных SQL предоставляется целый ряд полезных обобщающих функций, в том числе MIN (), МАХ (), AVG (), SUM (), COUNT () и COUNT (DISTINCT). Допустим, требуется видоизменить запрос таким образом, чтобы показать самые последние по дате написания статьи каждого автора. Для этой цели можно было бы ввести в данный запрос следующий столбец: MAX(created) as last_create_date А если, например, требуется исключить из запроса статьи, где отсутствует значение в столбце createdbyalias, то в запрос можно ввести оператор WHERE, чтобы проверить наличие какого-нибудь значения в этом столбце. И здесь приходится вдаваться в тонкости обработки данных в столбцах таблиц по запросам SQL. Если обратиться к определению данного столбца в базе данных, то можно обнаружить, что пустые значения NULL в нем не допускаются и что по умолчанию в нем сохраняется пустая символьная строка, обозначаемая двумя одиночными кавычками без пробела между ними (' '). В силу этого проверять наличие пустых значений NULL в данном столбце не имеет никакого смысла — они просто в нем отсутствуют. Но в то же время можно проверить наличие в нем значения, которое больше пустой символьной строки, как показано в приведенном ниже запросе. SELECT a.created_by_alias, COUNT(*) as article_count, MAX(a.created) as last_create_date FROM jos_content AS a JOIN jos_categories AS с ON a.catid = c.id WHERE a.created_by_alias > •' GROUP BY a.created_by_alias В этом запросе пропускаются 12 статей, где искомое значение в столбце created_by_ alias не обнаружено. Но что, если требуется показать только те строки таблицы, где имеются хотя бы две статьи? Такое условие не удастся задать в операторе WHERE, поскольку каждая строка проверяется в таблице по очереди. Ведь неизвестно, сколько статей может быть в конечном итоге получено по данному запросу для каждого значения в столбце created_by_alias. Для выхода из подобного затруднительного положения в языке SQL предоставляется оператор HAVING, который может быть введен в запрос непосредственно после оператора GROUP BY и до оператора ORDER BY. Этот оператор похож на оператор WHERE, за исключением того, что в нем проверяются значения обобщающих функций. И для того чтобы ограничить предыдущий запрос только теми строками таблицы, в которых имеются две и более статьи, его можно видоизменить следующим образом: SELECT a.created_by_alias, COUNT(*) as article_count, MAX(a.created) as last_create_date FROM jos_content AS a JOIN jos_categories AS с ON a.catid = c.id WHERE a.created_by_alias > '' GROUP BY a.created_by_alias HAVING COUNT(*) >= 2 Теперь по данному запросу будут показаны только те строки таблицы, где число статей равно двум и больше. В операторе GROUP BY можно также указывать обобщающие функции. Допустим, требуется упорядочить строки, извлекаемые из таблицы, указав сначала в списке строки с наибольшим числом статей. Для этого достаточно ввести в конце запроса следующий оператор: ORDER BY COUNT(*) DESC В итоге из таблицы будут извлечены строки, упорядоченные в списке от наибольшего до наименьшего числа статей.
Понравился материал? Пригодилась информация? Плюсани в социалки!
|
| Похожие новости | |
|
|