Тема № 13. Извлечение данных из базы данных

Цель. Практическое использование конструкций SQL.

Задание 1. Оператор выборки данных SELECT

Оператор SELECT языка SQL извлекает данные из базы данных, выбирая из таблицы строки, которые отвечают заданному критерию поиска.

Основная форма оператора SELECT следующая (полное описание и использование языка SQL приведено в [7], в этой теме будут рассматриваться наиболее употребимые):

SELECT items

FROM tables

[ WHERE condition ]

[ GROUP BY group_type ]

[ HAVING wnere_definition ]

[ ORDER BY order_type ]

[ LIMIT Iimit_criteria ];

Рассмотрим каждую конструкцию, используя схему базы данных представленную на рис. 3.214. Выполнение данной темы предполагает, что MySQL запущен .

Простейшим вариантом выборки данных Вы уже неоднократно пользовались, используя конструкцию SELECT * FROM tables (см. например рис. 3.220). Символ “ * ” использовался для вывода всех столбцов искомой таблицы tables.

Если взамен символа “*” использовать (через запятую) имена столбцов, то в результате подобного запроса Вы получите информацию только по тем столбцам, которые указаны в качестве элементов items.

Например, запрос (поиск):

SELECT name, city FROM customers;

запрашивает данные столбцов name (имя) и city (город) таблицы Customers:

Если воспользоваться монитором MySQL (вызов через пакетный файл CmySql.bat - рис. 3.188), то ввод необходимых команд даст следующий результат, показанный на рис. 3.227.

Если воспользоваться средствами PhpMyAdmin для реализации этого запроса, то результат будет такой же, но изображение естественно будет другим (рис. 3.229).

Можно указывать столько столбцов таблицы, сколько необходимо, главное — вписать их по порядку после ключевого слова select. Кроме того, можно указывать и другие элементы, уточняющие выборку данных.

Рис. 3.227. Результат поиска (выборки) через монитор MySQL.

Рис. 3.228. Подготовка запроса в PhpMyAdmin через кнопку SQL.

Нажатие кнопки даст следующий результат:

Рис. 3.229. Результат поиска через PhpMyAdmin.

В следующих заданиях по данной теме результаты использования средств PhpMyAdmin в целях экономии места приводиться не будут. Также не будут указываться команды set names cp866 и use books при использовании монитора.

Задание 2.Извлечение данных по определенному критерию

Чтобы получить доступ к подмножеству строк в таблице, следует указать критерий выбора с помощью конструкции WHERE. Например,

SELECT * FROM orders WHERE customerid = 3;

выбирает все столбцы из таблицы заказов, но только из строк с customerid, равным 3.

Рис. 3.230. Результат поиска через монитор MySQL.

Конструкция WHERE устанавливает критерий выбора определенных строк. В нашем случае выбраны строки с customerid, равным 3. Одиночный знак равенства используется для проверки на равенство — обратите внимание, что это немного отличается от РНР, и если работать и с тем, и с другим, вполне можно запутаться.

Вдобавок к равенству, MySQL поддерживает целое семейство операторов и регулярных выражений. Наиболее употребимые в конструкции WHERE перечислены в таблице приведенной на рис. 3.231.

Примечание. Обратите внимание, что список далеко не полон, и если понадобится что-нибудь, отсутствующее в нем, обратитесь к руководству по MySQL.

Знаки операции сравнения для конструкции WHERE (рис. 3.231):

Операция (терм сравнения)	Название (если применимо)	Пример	Описание
=	равенство	custumerid = 3	Проверяет, являются ли два значения равными
>	больше	amount>60.00	Проверяет, больше ли одно значение другого
<	меньше	amount<60.00	Проверяет, меньше ли одно значение другого
>=	больше или равно	amount>=60.00	Проверяет, больше или равно одно значение по отношению к другому
<=	меньше или равно	amount<=60.00	Проверяет, меньше или равно одно значение по отношению к другому
!= или <>	не равно	quantity!=0	Проверяет, не равны ли два значения. Пробел между! и = не допустим
IS NOT NULL	адрес не равен нулю		Проверяет, имеет ли поле значение
IS NULL	адрес равен нулю		Проверяет, не имеет ли поле значения
BETWEEN		величина между 0 и 60.00	Проверяет, значение больше или равно минимальному и меньше или равно максимальному
IN		город содержится	Проверяет, содержится ли значение в определенном множестве
NOT IN		город не содержится	Проверяет, не содержится ли значение в определенном множестве
LIKE	соответствие	name like ("%Fred %")	Проверяет, отвечает ли зачение образцу, используя простые механизмы соответствия SQL
NOT LIKE	соответствие	name not like ("%Fred %")	Проверяет, не соответствует ли значение образцу
REGEXP	регулярное	name regexp	Проверяет, соответствует ли выражение значение регулярному выражению

Рис. 3.231. Операции сравнения для конструкции WHERE.

Три последних строки таблицы относятся к LIKE и REGEXP. Это формы соответствия образцу.

Операция LIKE использует простой механизм соответствия SQL. Образец может состоять из обычного текста плюс % (знак процента) для указания совпадения с любым количеством символов и _ (символ подчеркивания) для указания совпадения с одним символом. В MySQL соответствия не чувствительны к регистру. Например, 'Fred %' найдет любое значение, которое начинается с 'fred '.

Ключевое слово REGEXP используется для соответствия регулярных выражений. MySQL использует регулярные выражения в стиле POSIX. Вместо REGEXP можно применять и RLIKE, что является синонимом. Регулярные выражения POSIX также применяются и в РНР.

Можно проверять несколько критериев сразу, объединяя их операциями AND или OR. Например,

Рис. 3.232. Операции сравнения для конструкции WHERE.

Следующий запрос показывает выборку с использованием операции BETWEEN.

Рис. 3.233. Операции сравнения для конструкции WHERE.

Запрос на совпадение с операцией LIKE

Рис. 3.234. Операции сравнения для конструкции WHERE.

Обратите внимание на следующий запрос:

Рис. 3.235. Результат с пустым ответом.

Задание 3.Извлечение данных из нескольких таблиц

Часто для получения ответа от базы данных на заданный вопрос могут потребоваться данные нескольких таблиц. Например, если необходимо узнать, кто из клиентов осуществлял в этом месяце заказы, придется просмотреть таблицы Customers (Клиент) и Orders (Заказ). Если нужно узнать, что конкретно они заказали, нельзя обойти вниманием и таблицу Order_Items (Покупка).

Примечание. Эти данные находятся в разных таблицах, поскольку относятся к разным реальным объектам. Это один из принципов хорошей разработки базы данных.

Для объединения этой информации в SQL потребуется выполнить операцию, называемую соединением (join). Подразумевается соединение двух и больше таблиц с тем, чтобы сохранялись отношения между данными. Если, например, необходимо посмотреть, какие заказы сделал клиент Ян Дрибас, то сначала потребуется просмотреть таблицу Customers и найти в ней CustomerlD для клиента, после чего — таблицу Orders на предмет заказов, сделанных этим CustomerlD.

Хотя на первый взгляд операция соединения достаточно проста, на самом деле это один из наиболее сложных и тонких разделов SQL. В MySQL существует несколько разных типов соединения, каждый из которых предназначен для определенной цели.

Простое соединение двух таблиц

Начнем с поиска Ян Дрибас, которого мы уже упоминали:

Рис. 3.236. Результат запроса с использованием соединения таблиц.

Здесь стоит отметить несколько моментов.

Во-первых, для ответа на этот запрос необходима информация из двух таблиц, поэтому в списке перечислены обе.

Также мы определили тип соединения, возможно даже не зная его. Запятая между названиями таблиц эквивалентна словам INNER JOIN (внутреннее соединение) или CROSS JOIN (перекрестное соединение). Такой тип соединения еще называют полным объединением или Декартовым произведением таблиц. Это означает: "Возьми указанные таблицы и сделай из них одну большую. В большой таблице должна быть строка для любой возможной комбинации строк из каждой таблицы, указанной в списке, имеют они смысл или нет". Другими словами, получаем таблицу, в которой каждая строка таблицы Customers сопоставляется каждой строке таблицы Orders независимо от того, какой клиент сделал какой заказ.

В большинстве случаев смысла в этом немного. Как правило, нам нужны строки, которые в самом деле совпадают, т.е. когда конкретные заказы совпадают с теми клиентами, которые их производили.

Это достигается путем помещения в конструкцию WHERE условия соединения. Это особый тип условного оператора, который объясняет, какие атрибуты показывают отношения между двумя таблицами. В данном случае наше условие соединения было таким:

customers.customerid = orders.customerid

что предписывает MySQL выводить в таблицу с результатами только соответствия Customerid из таблицы Customers с CustomerlD из таблицы Orders.

Внеся это условие в запрос, мы получили объединение другого типа — соединение по равенству (equi-join - эквисоединение).

Обратите внимание на точечную нотацию, которой мы воспользовались для уточнения конкретного столбца конкретной таблицы. Так, customers.customerid относится к столбцу customerid из таблицы Customers, a orders.customerid — к столбцу customerid из таблицы Orders.

Точечная нотация необходима, когда имена столбцов неоднозначны, что случается, если одни и те же имена встречается в нескольких таблицах.

Как расширение его можно использовать для различения имен столбцов из разных баз данных. В нашем примере обозначение выглядит как table.column (таблица.столбец). Можно указать и иначе — database.table.column (база_данных.таблица.столбец), например, для проверки условия наподобие

books.orders.customerid = other_db.orders.customerid

С другой стороны, точечную нотацию можно применять и для всех ссылок на столбцы в запросе. Это частенько избавляет от лишней головной боли, особенно когда запросы становятся все более сложными. MySQL этого не требует, но удобочитабельные запросы — это не так уж и плохо. Если вы заметили, мы придерживаемся этого принципа во всех наших примерах, взять хотя бы вот такое условие customers.name = 'Ян Дрибас'.

Столбец name присутствует только в таблице customers, поэтому его необязательно указывать, но так, в общем-то, понятнее.

Соединение трех и более таблиц

Объединение более двух таблиц не сложнее объединения двух. Главное правило таково — таблицы нужно объединять попарно, учитывая условия объединения. Это можно представить в виде отношений данных между первой таблицей, второй и третьей.

Например, если требуется узнать, кто из клиентов заказал книги по Java, необходимо отследить эти отношения в рамках небольшого количества таблиц.

Необходимо будет найти клиентов, разместивших, по крайней мере, один заказ, который выражен в orderjtems книгой по Java. Из таблицы Customers перебираемся в таблицу Orders, используя customerid, как и в предыдущих случаях. Из таблицы Orders в таблицу Order_Items, используя orderid. Из Order_Items — в таблицу Books за нужной книгой, руководствуясь номером ISBN. После того как все связи установлены, можем запросить книги со словом Java в названии и получить в результате имена клиентов, которые купили какую-либо из этих книг.

Посмотрим на запрос, который приведет все это в исполнение:

Дата добавления: 2014-12-07