79319

ПРОСТЫЕ ВЫБОРКИ ДАННЫХ

Лекция

Информатика, кибернетика и программирование

При этом возможно вы захотите получить не все записи а лишь те которые удовлетворяют заданным условиям. Основное SQLвыражение для выборки данных Чтобы выбрать из таблицы базы данных требуемые записи следует по крайней мере указать столбцы и имя этой таблицы. Это требование было бы естественно сформулировать так: ВЫБРАТЬ такието столбцы ИЗ такойто таблицы; Разумеется вам может потребоваться выбрать не все записи таблицы а лишь те которые отвечают некоторому условию. В результате выполнения этого запроса создается виртуальная...

Русский

2015-02-10

257.5 KB

2 чел.

ПРОСТЫЕ ВЫБОРКИ ДАННЫХ

Предположим, что реляционная база данных, состоящая из одной или нескольких таблиц, создана и вы к ней уже подключились. В этом случае типичной практической задачей является получение (извлечение) нужных данных. Например, может потребоваться просто просмотреть все содержимое какой-либо таблицы из базы данных или некоторых ее полей. При этом возможно, вы захотите получить не все записи, а лишь те, которые удовлетворяют заданным условиям. Однако чаще возникает более интересная и сложная задача извлечения данных сразу из нескольких таблиц. Данные из двух и более таблиц необходимо скомпоновать в одну таблицу, чтобы представить ее для обозрения, анализа или последующей обработки. Язык SQL предоставляет для этого широкие возможности, которые мы и рассмотрим.

В результате выполнения выражения на языке SQL (SQL-выражения) создается таблица, которая либо содержит запрошенные данные, либо пуста, если данных, соответствующих запросу, не нашлось. Эта таблица, называемая еще результатной, существует только во время сеанса работы с базой данных и не присоединяется к числу таблиц, входящих в базу данных. Иначе говоря, она не хранится на жестком диске компьютера подобно исходным таблицам базы данных, и поэтому ее еще называют виртуальной.

Выборка данных из нескольких таблиц, их обработка, а также использование подзапросов (запросов, которые нужны в качестве промежуточных для получения окончательного результата) относятся к теме сложных запросов. Здесь мы остановимся на задаче выборки данных из одной таблицы при относительно простых условиях отбора, группировки и сортировки записей. Тем не менее, операторы SQL, применяемые в простых запросах на выборку данных, используются и в сложных запросах, направленных не только на получение, но и на изменение данных. Начните с простого, чтобы потом было легко понять сложное. Материал данной главы относится к фундаментальным темам SQL, хотя многие пользователи баз данных могут им и ограничиться. Последнее вполне вероятно, поскольку материал этой главы сам по себе исключительно практичен.

Все SQL-выражения, предназначенные для выборки данных из существующих таблиц базы данных, начинаются с ключевого слова (оператора) SELECT (выбрать). Для уточнения запроса служат дополнительные операторы, такие как FROM (из), WHERE (где) и др. Сейчас важно понять и запомнить, что результатом выполнения запроса, сформулированного в виде SQL-выражения, является таблица, содержащая запрошенные данные. Эта таблица виртуальна в том смысле, что только представляет результаты запроса и не принадлежит к базе данных. SQL позволяет изменять существующую базу данных — создавать и добавлять к ней новые таблицы, а также модифицировать и удалять уже существующие.

Основное SQL-выражение для выборки данных

Чтобы выбрать из таблицы базы данных требуемые записи, следует, по крайней мере, указать столбцы и имя этой таблицы. Это требование было бы естественно сформулировать так:

ВЫБРАТЬ такие-то столбцы ИЗ такой-то таблицы;

Разумеется, вам может потребоваться выбрать не все записи таблицы, а лишь те, которые отвечают некоторому условию. На практике именно так и бывает. Отложим пока рассмотрение формирования условий отбора записей, а сконцентрируем внимание на выборке всех записей из заданной таблицы. SQL-запрос к базе данных, результатом которого является таблица, полученная из указанной в запросе, но отличающаяся от нее тем, что содержит лишь указанные столбцы, выглядит так:

SELECT списокСтолбцов FROM список Таблиц;

Операторы SELECT (выбрать) и FROM (из) в SQL-выражении, определяющем выборку данных, являются обязательными, т. е. ни один из них нельзя пропустить. SQL-выражение, содержащее только эти операторы, является основным выражением, определяющим запрос к базе данных на выборку данных. В результате выполнения этого запроса создается виртуальная таблица, содержащая указанные столбцы и все записи исходной таблицы.

Оператор SELECT осуществляет проекцию отношения, указанного в выражении FROM, на заданное множество атрибутов (столбцов), указанное в выражении SELECT.

В выражении FROM указывается список имен таблиц базы данных, из которых требуется выбрать данные. В простейшем случае списокТаблиц содержит лишь одно имя таблицы. Если же таблиц несколько, то их имена в списке разделяются запятыми. Если в выражении FROM указано более одной таблицы, то результатная таблица получается из декартового произведения перечисленных в списке таблиц. Иногда это используется для специальных целей, но чаще всего в выражении FROM указывается только одна таблица.

Список столбцов — это перечень имен столбцов, разделенных запятой, как они определены в таблице, указанной в выражении FROM. Разумеется, можно указать все или только некоторые столбцы. Если вы хотите получить все столбцы таблицы, то вместо списка столбцов достаточно указать символ (*). Если в выражении FROM указано несколько таблиц, то в выражении SELECT имена столбцов должны содержать префиксы, указывающие, к какой именно таблице они относятся. Префикс отделяется от имени столбца точкой. Например, выражение Клиенты.Адрес означает столбец Адрес из таблицы Клиенты.

Тривиальный запрос, возвращающий все данные (все столбцы и все записи) из одной таблицы, формулируется так:

SELECT * FROM имяТаблицы;

Основное SQL-выражение может быть дополнено другими операторами, уточняющими запрос. Чаще всего употребляется оператор WHERE (где), с помощью которого можно задать условие выборки записей (строк таблицы). Таким образом, если выражение SELECT задает столбцы таблицы, указанной в операторе FROM, то выражение WHERE определяет записи (строки) из этой таблицы. Выражение, определяющее запрос на выборку данных, находящихся в некоторой таблице, имеет следующий вид:

SELECT * FROM имяТаблицы WHERE условиеПоиска;

Условие, указанное в выражении WHERE, принимает одно из двух логических значений: true (ИСТИНА) или false (ЛОЖЬ). Другими словами, это логическое выражение. При обработке запроса условие проверяется для каждой записи таблицы. Если оно истинно для данной записи, то она выбирается и будет представлена в результатной таблице. В противном случае запись не выбирается и в результатную таблицу не попадает. Если выражение WHERE не указано в SQL-выражении, то результатная таблица будет содержать все записи из таблицы, заданной в выражении FROM. Таким образом, выражение WHERE определяет фильтр записей. Фильтр что-то пропускает в результатную таблицу, а что-то отбрасывает.

Фильтр — одно из основных понятий в области работы с базами данных. В литературе иногда можно встретить различные его трактовки. Так, "отфильтровать записи" может означать "получить записи", а может наоборот — "отбраковать записи". Здесь понятия "отфильтровать", "пропустить через фильтр" или "наложить фильтр" всегда означают "выбрать записи, удовлетворяющие условию фильтра".

Сразу за оператором SELECT до списка столбцов можно применять ключевые слова ALL (все) и DISTINCT (отличающиеся), которые указывают, какие записи представлять в результатной таблице. Если эти ключевые слова не используются, то подразумевается, что следует выбрать все записи, что также соответствует применению ключевого слова ALL. В случае использования DISTINCT в результатной таблице представляются только уникальные записи. При этом если в исходной таблице находятся несколько идентичных записей, то из них выбирается только первая.

Примечание

В Microsoft Access кроме ключевых слов ALL и DISTINCT после SELECT можно использовать ключевое слово ТОР с дополнительными параметрами. Выражение ТОР n требует, чтобы в выборку данных попали только первые n записей, удовлетворяющих заданному условию запроса. Это ограничение условия поиска нужных записей, формулируемого в выражении WHERE. Если исходная таблица очень большая, то DISTINCT может ускорить получение ответа.

В Access можно использовать и выражение ТОР n PERCENT, чтобы указать, что n выражается в процентах от общего количества записей. Не трудно понять, что использование такого выражения направлено не на ускорение поиска, а на получение таблицы, избавленной от лишних данных.

Заголовки столбцов в результатной таблице можно переопределить по своему усмотрению, назначив для них так называемые псевдонимы. Для этого в списке столбцов после соответствующего столбца следует написать выражение вида: AS заголовок_столбца 

Например:

SELECT ClientName AS Клиент,  Address AS Адрес FROM Клиенты;

Псевдонимы также можно задать и для каждой таблицы после ключевого слова FROM. Для этого достаточно указать псевдоним через пробел сразу после имени соответствующей таблицы. Псевдонимы таблиц, более короткие, чем их имена, удобно использовать в сложных запросах. Например:

SELECT Т1.Имя, Т2.Адрес FROM Клиенты Т1, Контакты Т2;

Уточнения запроса

Основное SQL-выражение для выборки данных имеет вид:

SELECT списокСтолбцов FROM списокТаблиц;

Такой запрос возвращает таблицу, полученную из указанной в операторе FROM путем выделения в ней только тех столбцов, которые определены в операторе SELECT. Для выделения требуемых записей (строк) исходной таблицы используется выражение, следующее за ключевым словом (оператором) WHERE. Оператор WHERE является наиболее часто используемым, хотя и не обязательным в SQL-выражении. Именно из-за популярности его можно считать основным компонентом SQL-выражения. Кроме WHERE, в SQL-выражениях используются и другие операторы, позволяющие уточнить запрос.

Для уточнения запроса на выборку данных служит ряд дополнительных операторов:

  •  WHERE (где) — указывает записи, которые должны войти в результатную таблицу (фильтр записей);
  •  GROUP BY (группировать по) — группирует записи по значениям определенных столбцов;
  •  HAVING (имеющие, при условии) — указывает группы записей, которые должны войти в результатную таблицу (фильтр групп);
  •  ORDER BY (сортировать по) — сортирует (упорядочивает) записи.

Эти операторы не являются обязательными. Их можно совсем не использовать, или использовать лишь некоторые из них, или все сразу. Если применяются несколько операторов, то в SQL-выражении они используются в указанном в списке порядке. Таким образом, запрос данных из таблицы с применением всех перечисленных операторов уточнения запроса имеет следующий вид:

SELECT списокСтолбцов FROM имяТаблицы

WHERE условиеПоиска

GROUP BY столбецГруппировки

HAVING условиеПоиска

ORDER BY условиеСортировки;

Порядок перечисления операторов в SQL-выражении не совпадает с порядком их выполнения. Однако знание порядка выполнения операторов поможет вам избежать многих недоразумений. Итак, перечисленные операторы SQL-выражения выполняются в следующем порядке, передавая друг другу результат в виде таблицы:

  1.  FROM — выбирает таблицу из базы данных; если указано несколько таблиц, то выполняется их декартово произведение и результирующая таблица передается для обработки следующему оператору.
  2.  WHERE — из таблицы выбираются записи, отвечающие условию поиска, и отбрасываются все остальные.
  3.  GROUP BY — создаются группы записей, отобранных с помощью оператора WHERE (если он присутствует в SQL-выражении); каждая группа соответствует какому-нибудь значению столбца группирования. Столбец группирования может быть любым столбцом таблицы, заданной в операторе from, а не только тем, который указан в SELECT.
  4.  HAVING — обрабатывает каждую из созданных групп записей, оставляя только те из них, которые удовлетворяют условию поиска; этот оператор используется только вместе с оператором GROUP BY.
  5.  SELECT — выбирает из таблицы, полученной в результате применения перечисленных операторов, только указанные столбцы.
  6.  ORDER BY — сортирует записи таблицы. При этом в условии сортировки можно обращаться лишь к тем столбцам, которые указаны в операторе SELECT.

Допустим, среди таблиц вашей базы данных имеется таблица Клиенты, которая содержит столбцы с именами: имя, Адрес, Сумма_заказа и, возможно, какие-то другие. Семантика этой таблицы тривиальна. В ней фиксируются данные о клиентах и денежные суммы, которые они заплатили вашей фирме, пользуясь ее услугами.

Рис. 1. Таблица Клиенты

Предположим, нас интересуют не все данные этой таблицы, а только те, которые касаются клиентов, заплативших фирме более 500 (сейчас не важно, в какой валюте производились оплаты). Точнее, нам нужны имена и адреса клиентов, которые заплатили фирме более 500 денежных единиц. Таким образом, нам необходимо получить не все, что содержится в таблице Клиенты, а лишь некоторую ее часть, как по столбцам, так и по записям. Для этой цели подойдет следующее SQL-выражение:

SELECT Имя, Адрес FROM Клиенты WHERE Сумма_заказа > 500;

Это SQL-выражение представляет собой запрос, который на естественном языке выглядит приблизительно так:

ВЫБРАТЬ СТОЛБЦЫ имя, Адрес ИЗ ТАБЛИЦЫ клиенты ГДЕ Сумма_заказа > 500;

Здесь из таблицы клиенты выбираются записи, в которых значение столбца Сумма_заказа превышает 500. При этом в результатной таблице будут представлены только два столбца таблицы Клиенты: Имя и Адрес.

Столбец Сумма_заказа, имеющийся в исходной таблице, в данном случае не выводится (отсутствует в результирующей таблице). Впрочем, если бы вам потребовалось увидеть конкретные суммы, превышающие 500, то для этого было бы достаточно указать в списке столбцов, следующем за оператором SELECT, еще и столбец Сумма_заказа.

Рис. 2. Результат запроса 'WHERE Сумма_заказа  >  500'

Следующее SQL-выражение создает виртуальную таблицу, содержащую три Столбца: Регион, Имя и Адрес. Из таблицы Клиенты выбираются только те записи, в которых Сумма__заказа превышает 500, и они группируются по значениям столбцов Регион, Имя и Адрес. Это означает, что в результатной таблице записи, имеющие одинаковые значения в столбце Регион, будут расположены рядом друг с другом. Наконец, все записи в результатной таблице упорядочиваются по значениям столбца Имя (рис. 3).

SELECT Регион, Имя, Адрес FROM Клиенты

WHERE Сумма_заказа > 500

GROUP BY Регион, Имя, Адрес 

ORDER BY Имя;

Рис. 3. Результат запроса 'WHERE Сумма_заказа > 500'
с группировкой '
GROUP BY Регион, Имя, Адрес' и сортировкой 'ORDER BY Имя'

Оператор выборки записей из исходной таблицы может соседствовать с другими SQL-операторами.

Оператор WHERE

Условия поиска в операторе WHERE (где) являются логическими выражениями, т. е. принимающими одно из двух возможных значений — true (ИСТИНА) или false (ЛОЖЬ). Например, выражение Сумма_заказа > 500 является истинным (имеет значение true), если в текущей записи таблицы значение столбца Сумма_заказа превышает 500. В противном случае это выражение ложно (имеет значение false). Одно и то же логическое выражение может быть истинным для одних записей и ложным для других. Вообще говоря, в SQL логические выражения могут принимать еще и неопределенное значение. Это происходит тогда, когда в выражении некоторые элементы имеют значение NULL. Тaким образом, в SQL мы имеем дело не с классической двузначной, а с трехзначной логикой.

Выражение, следующее за оператором WHERE, возвращает одно из трех значений: true, false или NULL. При выполнении запроса (SQL-выражения) логическое выражение WHERE применяется ко всем записям исходной таблицы. Если оно истинно для данной записи исходной Таблицы, то эта запись выбирается и будет представлена в результатной таблице; в противном случае запись не попадет в результатную таблицу.

При составлении логических выражений используются специальные ключевые слова и символы операций сравнения, которые называют предикатами. Предикат впервые появился в SQL: 1999. Например, в выражении Сумма_заказа > 500 применен предикат сравнения (>).

Выражения с оператором WHERE используются не только при выборке данных (т.е. с оператором SELECT), но и при вставке, модификации и удалении записей. Таким образом, материал данного раздела имеет значение, выходящее за границы рассматриваемой темы о выборке данных.

Наиболее часто используются предикаты сравнения, такие как (=), (<),(>), (<>), (<=) и (>=). Однако имеются и другие. Далее приведен список всех предикатов:

  •  предикаты сравнения:

(=) - равно, (<) - меньше, (>) - больше, (< >) – не равно,

(<=) – меньше или равно (не больше), (>=) - больше или равно (не меньше);

  •  BETWEEN;
  •  IN, NOT IN;
  •  LIKE, NOT LIKE;
  •  IS NULL;
  •  ALL, SOME, ANY;
  •  EXISTS;
  •  UNIQUE;
  •  DISTINCT;
  •  OVERLAPS;
  •  MATCH;
  •  SIMILAR.

Примечание

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

BETWEEN

Предикат BETWEEN (между) позволяет задать выражение проверки вхождения какого-либо значения в диапазон, определяемый граничными значениями. Например:

WHERE Сумма_заказа BETWEEN 100 AND 750

Здесь ключевое слово AND представляет собой логический союз И. Граничные значения (в примере это 100 и 750) входят в диапазон. Причем первое граничное значение должно быть не больше второго.

Эквивалентным приведенному является выражение с предикатами сравнения:

WHERE Сумма_заказа >= 100 AND Сумма_заказа <= 750

Кроме данных числового типа, в выражениях с BEETWEEN можно использовать данные следующих типов: символьные, битовые, даты-времени. Так например, чтобы выбрать записи, в которых имена клиентов находятся в диапазоне от А до Ж, можно использовать такое выражение:

SELECT Имя, Адрес FROM Клиенты 

WHERE Имя BETWEEN 'А' AND ' Ж' ;

IN и NOT IN

Предикаты IN (в) и NOT IN (не в) применяются для проверки вхождения какого-либо значения в заданный список значений. Например, для выборки записей о клиентах из некоторых регионов можно использовать такое выражение:

SELECT Имя, Адрес FROM Клиенты

WHERE Регион IN ('Северо-запад', 'Ставропольский край',

'Иркутская область');

Если требуется получить данные о всех клиентах не из Москвы и Северо-Запада, то можно использовать предикат NOT IN:

SELECT Имя, Адрес FROM Клиенты

WHERE Регион NOT IN ('Москва', 'Санкт-Петербург');

LIKE и NOT LIKE

Предикаты LIKE (похожий) и NOT LIKE (не похожий) применяются для проверки частичного соответствия символьных строк. Например, столбец Телефон в некоторой таблице содержит полные номера телефонов, а вам требуется выбрать лишь те записи, в которых номера телефонов начинаются с 348 или содержат такое сочетание цифр.

Критерий частичного соответствия задается с помощью двух символов-масок: знака процента (%) и подчеркивания (_). Знак процента означает любой набор символов, в том числе и пустой, а символ подчеркивания — любой одиночный символ. Например, чтобы выбрать записи о клиентах, у которых номера телефонов начинаются с 348, можно использовать такое выражение:

SELECT Имя, Адрес, Телефон FROM Клиенты

WHERE Телефон LIKE '348%';

Допустим, столбец Адрес содержит полный почтовый адрес (индекс, название города, улицы и т. д.). Если вам требуется выбрать записи о клиентах, проживающих в Санкт-Петербурге, то для этого подойдет следующее выражение:

SELECT Имя, Адрес, Телефон FROM Клиенты

WHERE Адрес LIKE '%Санкт-Петербург%';

Если вы хотите исключить всех клиентов, проживающих в Москве, то воспользуйтесь таким выражением:

SELECT Имя, Адрес, Телефон FROM Клиенты

WHERE Адрес NOT LIKE '%Москва%';

Возможно, вам потребуется выбрать записи, срдержащие символы процента и/или подчеркивания. Тогда необходимо, чтобы такие символы воспринимались интерпретатором SQL не как символы-маски. Чтобы знак процента или подчеркивания воспринимался буквально, перед ним необходимо указать специальный управляющий символ. Этот символ можно определить произвольно, лишь бы он не встречался в качестве элемента данных. В следующем примере показано, как это можно сделать:

SELECT- Имя, Адрес, Процент_скидки FROM Клиенты

WHERE Процент_скидки LIKE '20#%'

ESCAPE '#';

Здесь за ключевым словом ESCAPE указывается символ, который используется в качестве управляющего. Таким же способом можно отключить и сам управляющий символ.

IS NULL

Предикат IS NULL применяется для выявления записей, в которых тот или иной столбец не имеет значения. Например, для получения записей о клиентах, для которых не указан адрес, можно использовать следующее выражение:

SELECT Имя,  Адрес,   Регион FROM Клиенты

WHERE Адрес IS NULL;

Для получения записей, в которых столбец Адрес содержит некоторые определенные значения (т. е. отличные от NULL), можно использовать аналогичное выражение, но с логическим оператором NOT (не):

SELECT Имя,  Адрес,   Регион FROM Клиенты 

WHERE Адрес  IS NOT NULL;

Не следует использовать предикаты сравнения с NULL, такие как Адрес   =  NULL.

Предикаты для вложенных запросов

В выражении WHERE, кроме перечисленных предикатов, могут также использоваться выражения с оператором SELECT. Любое выражение, начинающееся с оператора SELECT, является запросом к базе данных. Если в выражении встречается еще хотя бы один оператор SELECT, то он задает запрос, вложенный в первый. Вложенные запросы также называют подзапросами.

Вложенный запрос является обычным запросом, таким же, как и рассмотренные ранее. Он возвращает таблицу (набор записей), которая, так или иначе, используется для формирования ответа на основной запрос. Так, например, подзапрос используется, когда для выборки данных в одной таблице необходимо выполнить проверки по другой таблице. Для этой цели подходят перечисленные далее специальные предикаты.

ALL, SOME, ANY

Предикаты ALL (все), SOME (некоторый), ANY (любой) в действительности представляют собой кванторы, известные в математической логике как кванторы всеобщности и существования. ALL — квантор всеобщности, a SOME и ANY, являющиеся синонимами в SQL, — кванторы существования. Заметим, что в переводе на русский слово ANY следовало бы понимать как квантор всеобщности ("любой" означает "все"), однако в английском языке есть различные варианты значений этого слова. Применение ключевого слова ALL следует понимать как "для всех" или "для каждого". Ключевые слова SOME и ANY следует понимать как "хотя бы какой-нибудь один". Как бы то ни было, в языке SQL ключевые слова SOME и ANY имеют одинаковый смысл, отличающийся от ALL.

Примечание. Выражения с ключевыми словами ALL, SOME (ANY) соответствуют логическим выражениям с кванторами и, как таковые, могут называться предикатами.

EXISTS

Обработка данных часто состоит из нескольких этапов. Так, сначала производится некоторая выборка данных, а затем выполняются какие-то манипуляции с ней. Однако, выполняя запрос на выборку, мы далеко не всегда можем быть уверенными, что ответ содержит хотя бы одну непустую строку. Если ответ на запрос пуст, то бессмысленно производить дальнейшую обработку данных. Таким образом, полезно знать, содержит ли ответ на запрос какие-либо данные. Для этого предназначен предикат EXISTS (существует). Он становится истинным только тогда, кода результатная таблица, полученная в ответ на запрос, содержит хотя бы одну запись.

UNIQUE

Предикат UNIQUE (уникальный) имеет такой же смысл, как и EXISTS, но при этом для его истинности требуется, чтобы все записи в результатной таблице не только существовали, но и были уникальны (т. е. не повторялись).

DISTINCT

Предикат DISTINCT (отличающийся, особый) почти такой же, как UNIQUE. Отличие этих предикатов обнаруживается применителъно к значениям NULL. Так, если в результатной таблице все записи уникальны (предикат UNIQUE истинен), то и предикат DISTINCT тоже истинен (т. е. если все записи уникальны, то они и отличающиеся). С другой стороны, если в результатной таблице имеются, хотя бы две неопределенные записи, то предикат DISTINCT ложен, хотя предикат UNIQUE истинен.

OVERLAPS

Предикат OVERLAPS (перекрывает) используется для определения, перекрываются ли два интервала времени. Интервал времени можно задать двумя способами: в виде начального и конечного моментов или в виде начального момента и длительности. Примеры задания интервала времени:

  •  (TIME    '12:25:30',    TIME    '14:30:00') —интервал, заданный начальным и конечным моментами;
  •  (TIME '12:45:00', INTERVAL '2' HOUR)—интервал, заданный начальным моментом и длительностью в часах.

Выражение с предикатом OVERLAPS можно записать, например, так:

(TIME '12:25:30', TIME '14:30:00') OVERLAPS (TIME 42:45:00', INTERVAL '2' HOUR)

Поскольку временные интервалы в данном примере пересекаются, то предикат OVERLAPS возвращает значение true.

MATCH

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

SIMILAR

Предикат SIMILAR (подобный) применяется для проверки частичного соответствия символьных строк. Эту же задачу можно ре шить и с помощью предиката LIKE, однако в ряде случаев SIMILAR более эффективен.

Предположим, что в некоторой таблице имеется столбец ОС, содержащий названия операционных систем. Нужно выбрать записи, соответствующие Windows NT, Windows XP и Windows 98. Тогда в выражении запроса можно использовать такой оператор WHERE:

WHERE ОС SIMILAR TO '(Windows (NT|XP|98))';

Оператор GROUP BY

Оператор GROUP BY (группировать по) служит для группировки записей по значениям одного или нескольких столбцов. Если в SQL-выражении используется оператор WHERE, задающий фильтр записей, то оператор GROUP BY находится и выполняется после него. Для определения, какие записи должны войти в группы, служит оператор HAVING, используемый совместно с GROUP BY. Если оператор HAVING не применяется, то группировке подлежат все записи, отфильтрованные оператором WHERE. Если WHERE не используется, то группируются все записи исходной таблицы.

Допустим, что на основе таблицы о клиентах требуется сгруппировать данные о суммах заказов клиентов по регионам. Для этого можно воспользоваться следующим SQL-выражением:

SELECT Регион, Сумма_заказа FROM Клиенты GROUP BY Регион;

На рис. 4 показана результатная таблица на фоне исходной таблицы Клиенты. Обратите внимание, что записи с одинаковыми названиями регионов расположены рядом друг с другом (в одной группе).

Рис. 4. Результат запроса сумм заказов с группировкой по регионам

Рис. 5. Результат запроса итоговых сумм заказов по регионам

Чтобы получить таблицу, в которой суммы заказов подытожены по регионам, потребуется использовать итоговую функцию SUM () и группировку по регионам:

SELECT Регион, SUM(Сумма_заказа) FROM Клиенты

GROUP BY Регион;

Здесь в выражении SELECT указаны обычный столбец таблицы Клиенты и итоговая функция SUM(), вычисляющая сумму значений столбца Сумма_заказа. Поскольку группировка задана по столбцу "Регион, то функция SUM (Сумма_заказа) вычисляет Суммы значений столбца Сумма_заказа для каждого значения столбца Регион. На рис. 5 показана результатная таблица на фоне исходной таблицы Клиенты. Обратите внимание, что в этой таблице названия регионов не повторяются.

Оператор GROUP BY собирает записи в группы и упорядочивает (сортирует) группы по алфавиту (точнее, по ASCII-кодам символов). Это обстоятельство следует иметь в виду перед тем, как принять решение об использовании оператора сортировки ORDER BY.

Оператор HAVING

Оператор HAVING (имеющие, при условии) обычно применяется совместно с оператором группировки GROUP BY и задает фильтр записей в группах. Правила его формирования такие же, что и для оператора WHERE.

Предположим, что из таблицы Клиенты требуется выбрать данные о регионах и суммах заказов, сгруппированные по регионам и такие, в которых сумма заказа превышает 500. Иначе говоря, требуется сгруппировать данные с ограничением записей, входящих в группы. Запрос, выполняющий это задание, имеет вид:

SELECT Регион, Сумма_заказа FROM Клиенты

GROUP BY Регион, Сумма_заказа

HAVING Сумма_заказа > 500;

Рис. 6. Результат запроса с группировкой по регионам
и ограничением по суммам заказов

Если в SQL-выражении оператора GROUP BY нет, то оператор HAVING применяется ко всем записям, возвращаемым оператором WHERE. Если же отсутствует и WHERE, то HAVING действует на все записи таблицы.

Оператор ORDER BY

Оператор ORDER BY (сортировать по) применяется для упорядочивания (сортировки) записей. Если он используется в запросе, то в самом конце запроса. Этот оператор сортирует строки всей таблицы или отдельных ее групп (в случае применения оператора GROUP BY). Если в выражении запроса оператора GROUP BY нет, то оператор ORDER BY рассматривает все записи таблицы как одну группу.

Вслед за ключевым словом ORDER BY указывается столбец, по значениям которого следует произвести сортировку. После имени столбца можно указать ключевое слово, задающее порядок (режим) сортировки:

  •  ASCпо возрастанию (ascending). Это значение принято по умолчанию, поэтому если необходима сортировка, например, в алфавитном порядке, то специально указывать порядок не требуется;
  •  DESCпо убыванию (descending).

Если в выражении ORDER BY указаны несколько столбцов сортировки, то сначала записи упорядочиваются по значениям первого столбца, затем для каждого значения первого столбца записи упорядочиваются по значениям второго столбца и т. д. Столбцы в списке разделяются, как обычно, запятыми. Таким образом, создается иерархическая система сортировки записей результатной таблицы.

В следующем примере данные исходной таблицы клиенты сортируются по регионам и по именам клиентов (рис. 7). При этом сортировка по именам клиентов производится по убыванию, т. е. в порядке, противоположном алфавитному.

SELECT * FROM Клиенты 

ORDER BY Регион, Имя DESC;

Рис. 7. Результат сортировки по регионам в алфавитном порядке
и по именам клиентов в обратном порядке

Логические операторы

Логические выражения в операторах WHERE и HAVING могут быть сложными, т. е. состоять из двух и более простых выражений, соединенных между собой логическими операторами (союзами) AND и/или OR. Оператор AND выполняет роль логического союза И, а оператор OR — союза ИЛИ. Так, если х и у — два логических выражения, то составное выражение х AND у принимает значение true (ИСТИНА) только тогда, когда х и у одновременно истинны; в противном случае выражение х and у принимает значение false (ЛОЖЬ). Выражение х OR у истинно, если хотя бы одно из выражений, х или у, истинно; если х и у одновременно ложны, то составное выражение х OR у ложно.

Логический оператор NOT применяется к одному выражению (возможно и к сложному), расположенному справа от него. Этот оператор меняет значение выражения на противоположное. Так, если выражение х имеет значение true, то выражение NOT х имеет значение false, и, наоборот, если х ложно, то NOT х истинно.

Предположим, из таблицы Клиенты (см. рис. 1) требуется выдать записи о клиентах из Москвы и Северо-запада. Соответствующий запрос имеет вид:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE Регион='Москва' OR Регион='Северо-запад';

Обратите внимание, что здесь используется логический оператор OR (ИЛИ), а не AND (И), поскольку нам нужны клиенты, проживающие или в Москве, или на Северо-Западе. Если бы вместо оператора OR мы применили AND, то получили бы пустую таблицу, т. к. в исходной таблице нет ни одной записи, в которой один и тот же столбец имел бы различные значения.

Внимание. Будьте внимательны при формулировке запроса на естественном языке и при его переводе на SQL.

Следующее SQL-выражение эквивалентно рассмотренному ранее. Оно основано на применении оператора IN:

SELECT Регион,  Имя,  Сумма_заказа FROM Клиенты

WHERE Регион IN  ('Москва',  'Северо-запад');

Если требуется получить данные обо всех клиентах, которые не проживают ни в Москве, ни на Северо-западе, то можно использовать такое SQL-выражение:

SELECT Регион,  Имя,  Сумма_заказа FROM Клиенты

WHERE NOT  (Регион='Москва' OR Регион='Северо-Запад');

Это выражение эквивалентно следующим двум:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE Регион < > 'Москва' AND Регион < > 'Северо-Запад';

и:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE Регион NOT IN ('Москва', 'Северо-Запад');

Задачи

Выберите в качестве исходной таблицу Клиенты, показанную на рис. 1. Вы можете не копировать ее содержимое в точности, а создать похожую таблицу самостоятельно. Важно, чтобы в таблице имелись символьные (текстовые) и числовые столбцы. Хорошо, если некоторые столбцы имели бы одинаковые значения, например, столбец Регион. В предлагаемых далее задачах требуется сформировать SQL-выражения, обеспечивающие некоторую выборку записей.

При формировании условий поиска (выборки) записей старайтесь абстрагироваться от конкретных данных в имеющейся таблице, поскольку пользователь может добавлять новые записи и модифицировать уже имеющиеся, делая это так, как ему хочется и как позволяют ограничения для данной таблицы. Например, столбец Адрес имеет символьный тип. Это означает, что он содержит произвольные символьные данные, структура которых поддерживается пользователем на семантическом уровне. В полях данного столбца может присутствовать или нет почтовый индекс, перед названием улицы может быть указано "ул." или "улица", или может быть ничего не указано, номер телефона может иметь различную значность, содержать или не содержать код региона, дефисы и т. п. При формулировании запроса в подобных ситуациях следует учесть как можно больше семантических нюансов.

Задача 1

Выберите записи, сгруппированные по регионам и исключающие Северо-Западный регион. Попробуйте сделать это по крайней мере двумя способами (с использованием WHERE и HAVING).

Задача 2

Выберите записи о клиентах, проживающих в городах, название которых оканчивается на "бург", а сумма заказа превышает 2000.

Задача 3

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

PAGE  13


 

А также другие работы, которые могут Вас заинтересовать

15210. ТАРЛАН ТАЛАНТ ТАҒДЫРЫ 75 KB
  ТАРЛАН ТАЛАНТ ТАҒДЫРЫ Мұқанов Сәбит Мұқанұлы 1900-1973 – қазақтың әйгілі жазушысы қоғам қайраткері Қазақ КСР Ғылым академиясының академигі. Туған жері қазіргі Солтүстік Қазақстан облысының Жамбыл ауданындағы Жаманшұбар деген жер. Әке шешеден жастай жетім қа
15211. Сәкен Иманасовтың өлеңдерiн оқудан өрбiген ой 47.5 KB
  Екi тумас ер ақын Сәкен Иманасовтың өлеңдерiн оқудан өрбiген ой Әлемдi билейтiн сөз сөздi билейтiн ақын. Талантпен талғаммен қоса табандылықты азаматтықты қажет ететiн ақын болу қиынның қиыны. Азаматтық жоқ жерде ақындық та жоқ. Төлеген Айбергенов: Ақын болу оң
15212. Сүйінбай Аронұлы 170 KB
  Сүйінбай Аронұлы 1815-1898 Сүйінбай ақын Алматы облысының Жамбыл ауданы Қарақыстақ ауылында дүниеге келген сонда қайтыс болды. Қазақ халқының ақыны айтыс өнерінің шебері. Аронұлы Сүйінбай 1815 1898 – қазақтың әйгілі ақыны айтыс өнерінің майталман жүйрігі. Туып өске...
15213. Торайғыров Сұлтанмахмұт 59 KB
  Торайғыров Сұлтанмахмұт 1893-1920 Торайғыров Сұлтанмахмұт– қазақ ақыны ағартушы қоғам қайраткері ойшыл. Солтүстік Қазақстан облысының Уәлиханов ауданында туған. Торайғыровтың 3 жасында шешесі қайтыс болып 6 жасына дейін әжесінің тәрбиесінде болды. Кейін әкес
15214. Төлеген Айбергенов 62 KB
  Төлеген Айбергенов Сағындым жаным мен сені Көркіңді жүрген қуаныш қылып мендей ме екен бар ағаң Шын інім болсаң бас бұрма жаным өсек – ғайбатқа бораған. Қажет жерінде қатыгездік пен қаталдық керек десек те Адамның заңғар ұлылығын сен сағынышымен есепте. ...
15215. Ұлтымыздың Ұстазы - Ахмет Байтұрсынов 314.5 KB
  Ұлттың ұлы ұстазы: А.Байтұрсынұлының 130 жылдығына арналған әдістемелік құрал / ҚМРБК Құраст. А.Байжұманова. Алматы 2003. 46 б. Биылғы жыл халқымыздың көрнекті қоғам қайраткері кешегі Абай Ыбырай Шоқан салған ағартушылық демократтық бағытты ілгері жалғастырушы ір...
15216. Халел Досмұхамедұлы - Мұрат ақын шығармаларын жинаушы һәм зерттеуші 42 KB
  Алаш қайраткерлері ұлтымыздың рухани мұрасын жинауда, жариялауда және зерттеуде маңызды істер атқарғаны белгілі. Олардың бұл саладағы аса нәтижелі жұмыстары – осы халық үшін маңызды мәселенің бастауында тұрғандығымен де бағалы. Айталық, Әлихан Бөкейхан, Ахмет Байтұрсынұлы халық ауыз әдебиеті үлгілерін, Мағжан Жұмабаев Базар
15217. ҚАЛАМ ҚҰДІРЕТІ 69.5 KB
  ҚАЛАМ ҚҰДІРЕТІ Алла тағала берген ақылымен теңіздей терең білімімен уақыттың өзінен озған ғұлама фәниден бақиға аттанарда өсиет айтыпты: €œДауыл ма жауын ба сең бе сел ме өрт пе дерт пе... қандай қысылтаяң қиын шақ болса да ең алдымен халықтың қазынасын құтқарыңд
15218. Хамит Ерғалиев 53.5 KB
  Қайран Хамаң Дүниеден Хамаң – Хамит ақын Ерғалиев озғалы да бірнеше жылдың мұғдары болыптыау. Кейде өзіңненөзің отырып таңғаласың: біртуар тұлғаларды күнде көріп олардың лебізін тыңдап жүргенде ондай адамдар ешқашан өмірден өтпейтіндей көресің. Оның үстіне Хамит ...