Метасимволы:
% - символ процента соответствует строке из нескльких символов (в том числе пустой строке и строке из одного символа);
_ - символ подчёркивания соответствует любому одному символу;
[ ] - метасимвол диапазона соответствует любому одному символу;
[^ ] – метасимвол «не в диапазоне» соответствует любому одному символу, не входящему в диапазон или набор символов. Например, [^ m-p] или [^mnop] соответствует любому из символов, кроме символов m, n, o или p.
Пример:
SELECT автор, [год рождения], [место рождения]
FROM авторы
WHERE автор LIKE ‘П%’
Запрос возвратит ФИО, год рождения и место рождения авторов, фамилия которых начинается с буквы «П».
Пример:
SELECT автор, [год рождения], [место рождения]
FROM авторы
WHERE автор LIKE ‘[А-П]%’
Запрос возвратит ФИО, год рождения и место рождения авторов, фамилия которых начинается с буквы от «А» до «П».
Пример:
SELECT автор, [год рождения], [место рождения]
FROM авторы
WHERE автор NOT LIKE ‘П%’
Запрос возвратит ФИО, год рождения и место рождения авторов, фамилия которых начинается с любой буквы, кроме «П».
BETWEEN. Проверяемое_выражение BETWEEN начальное_значение AND конечное_значение. Применяется всегда в сочетании с ключевым словом AND и задаёт диапазон для поиска.
Пример:
SELECT автор, [год рождения], [место рождения], [число произведений]
FROM авторы
WHERE [число произведений] BETWEEN 1 AND 50
Запрос возвратит ФИО, год рождения, место рождения и число произведений авторов, написавших не более 50 произведений.
IS NULL. Выражение IS NULL. Применяется для поиска строк, содержащих null значения в заданной колонке. Чтобы найти значения не являющиеся null следует использовать конструкцию IS NOT NULL.
Пример:
SELECT автор, [год рождения], [место рождения]
FROM авторы
WHERE [место рождения] IS NOT NULL
Запрос возвратит ФИО, год рождения и место рождения авторов, у которых известно место рождения.
EXISTS. EXISTS подзапрос. Используется для проверки существования строк в выводе подзапроса, указанного после него.
Пример:
SELECT авторы.автор
FROM авторы
WHERE EXISTS (SELECT книги.издательство
FROM книги
WHERE книги.издательство='Питер' AND авторы.автор=книги.автор)
Запрос вернёт ФИО авторов, книги которых были выпушены издательством «Питер» .
2.3.2 Подзапрос.
Подзапрос - очень мощное средство языка SQL. Он позволяет строить сложные иерархии запросов, многократно выполняемые в процессе построения результирующего набора или выполнения одного из операторов изменения данных (DELETE, INSERT, UPDATE).
Подзапрос позволяет решать следующие задачи:
определять набор строк, добавляемый в таблицу на одно выполнение оператора INSERT;
определять данные, включаемые в представление, создаваемое оператором CREATE VIEW ;
определять значения, модифицируемые оператором UPDATE;
указывать одно или несколько значений во фразах WHERE и HAVING оператора SELECT;
определять во фразе FROM таблицу как результат выполнения подзапроса;
применять коррелированные подзапросы. Подзапрос называется коррелированным, если запрос, содержащийся в предикате, имеет ссылку на значение из таблицы (внешней к данному запросу), которая проверяется посредством данного предиката.
Структура подзапроса:
SELECT [ALL | DISTINCT] [TOP n [PERCENT] ] список_полей,
FROM имена_таблиц
WHERE поле [= > < IN] (SELECT [ALL | DISTINCT] [TOP n [PERCENT] ]
список_полей,
FROM имена_таблиц
[WHERE критерий_отбора])
В круглых скобках обозначен подзапрос. Для установления соответствия между запросом и подзапросом могут применяться операции сравнения (< = >), либо оператор IN. В первом случае оператор подзапрос всегда должен возвращать единственное значение, которое будет проверяться в предикате. Если подзапрос вернет более одного значения, то СУБД выдаст сообщение об ошибке выполнения SQL-оператора. Если результатом подзапроса становится группа строк (это случается всегда, когда условие не гарантирует уникальности значения проверяемого предикатом внутреннего запроса), то следует использовать оператор IN, осуществляющий выбор одного значения из указываемого множества.
Пример:
IN. Проверяемое_выражение IN подзапрос или
Проверяемое_выражение IN список_значений
Используется как условие поиска, проверяющее, не соответствует проверяемое выражение какому-либо из значений в подзапросе или в списке значений. Если соответствие найдено, то возвращается значение TRUE. NOT IN возвращает отрицание того, что получилось бы при применении IN, поэтому NOT IN возвратит значение TRUE, если проверяемое выражение не найдено в подзапросе или в списке значений.
Пример:
SELECT авторы.автор, книги.название
FROM авторы, книги
WHERE книги.издательство IN (SELECT издательства.издательство
FROM издательства
WHERE DATEPART(year, [дата основания]) < 1900)
Запрос вернёт ФИО авторов и их книги которых были выпущены издательствами, основанными до начала 20 века.
2.4 Использование агрегатных (статических) функций.
Агрегатные функции выполняют вычисления над набором значений и возвращают одно значение. Агрегатные функции могут быть заданы в списке выборки и чаще всего применяются в случаях, когда оператор содержит предложение GROUP BY. Ниже идет перечисление функций.
Структура запроса:
SELECT статическая_функция (имя_поля) AS заголовок
FROM имя_таблицы
AVG - возвращает среднее арифметическое для значений выражения; null-значения игнорируются.
COUNT - возвращает количество элементов в выражении (равное количеству строк).
COUNT_BIG - то же самое, что и COUNT, но результат имеет тип данных bigint, a не int.
GROUPING – возвращает специальную дополнительную колонку; применяется, только когда предложение GROUP BY содержит операцию CUBE или ROLLUP.
MAX – возвращает максимальное значение из значений выражения.
MIN – возвращает минимальное значение из значений выражения.
STDEV – возвращает статистическое стандартное отклонение (statistical standard deviation) для всех величин выражения. Эта функция предполагает, что выражения, используемые в расчете, являются образцом всей совокупности данных.
STDEVP – возвращает статистическое стандартное отклонение для всех величин выражения. Эта функция предполагает, что выражения, используемые в расчете, являются всей совокупностью данных.
SUM – возвращает сумму всех значений выражения.
VAR – возвращает статистическое отклонение (statistical variance) для всех значений из выражения. Эта функция предполагает, что выражения, используемые в расчете, являются образцом всей совокупности данных.
VARP – возвращает статистическое отклонение для всех значений из выражения. Эта функция предполагает, что выражения, используемые в расчете, являются всей совокупностью данных.
Функция COUNT применяется специальным образом: она подсчитывает все строки таблицы. Для этого нужно после COUNT поместить символ-звёздочку в скобках.
Пример:
SELECT COUNT(*) AS количество
FROM издательства
Функции AVG, COUNT, MAX, MIN и SUM могут применяться с необязательными ключевыми словами ALL или DISTINCT. Для каждой из этих функций ALL означает, что функция должна применяться ко всем значениям выражения, а DISTINCT означает, что повторяющиеся значения должны участвовать в расчете только по одному разу. По умолчанию применяется опция ALL.
Пример:
SELECT MAX(рейтинг) - MIN(рейтинг) AS Разница
FROM издательства
Запрос возвратит разницу между высшим и низшим рейтингами.
Пример:
SELECT SUM([число произведений]) AS [общее число произведений]
FROM авторы
Запрос возвратит сумму произведений всех авторов.
2.5 Определение связей между таблицами.
Существуют два способа определения связей между таблицами. Сначала рассмотрим применение команд JOIN.
Внутреннее соединение (inner join) является типом соединений, принятым по умолчанию. Внутреннее соединение задает набор результатов, в который будут включены лишь те строки таблиц, которые соответствуют условию ON, а все несоответствующие строки будут отброшены. Чтобы задать соединение, применяйте ключевое слово JOIN. Для задания условия поиска, на котором основывается соединение, применяется ключевое слово ON.
Структура запроса:
SELECT [ALL | DISTINCT] список_полей
FROM имя_таблицы1 INNER JOIN имя_таблицы2 ON условие_обьединения
[INNER JOIN имя_таблицы3 ON условие_обьединения]
[INNER JOIN имя_таблицы4 ON условие_обьединения]
……....
[WHERE критерий_отбора]
[ORDER BY столбцы_сортировки [ASC | DESC] ]
Полное внешнее соединение (full outer join) задает набор результатов, состоящий как из строк, соответствующих условию ON, так и из строк, не соответствующих условию ON. Для строк, не соответствующих условию ON, значением колонки, несоответствующей условию, станет NULL. Структура запроса схожа с представленным выше, но команда INNER JOIN заменяется на FULL OUTER JOIN.
Левое внешнее соединение (left outer join) возвращает строки, в которых произошло соответствие условию поиска, плюс все строки из таблицы, заданной слева от ключевого слова JOIN. Структура запроса схожа с представленным выше, но команда INNER JOIN заменяется на LEFT OUTER JOIN.
Правое внешнее соединение (right outer join) противоположно левому внешнему соединению: в него войдут строки, соответствующие условию поиска, плюс все строки из таблицы, заданной справа от ключевого слова JOIN. Структура запроса схожа с представленным выше, но команда INNER JOIN заменяется на RIGHT OUTER JOIN.
Перекрестное соединение (cross join) — это произведение двух таблиц, в котором не задано предложение WHERE, т.е. не задано условие объединения. Без предложения WHERE будет возвращаться такой результат: каждая строка из первой таблицы сопоставляется с каждой строкой из второй таблицы, поэтому размер набора результатов будет равен числу строк первой таблицы, умноженному на число строк второй таблицы.
Структура запроса:
SELECT список_полей
FROM таблица1 CROSS JOIN таблица2
Способ 2. Применением команды WHERE.
Структура запроса:
SELECT [ALL | DISTINCT] список_полей
FROM имена_таблиц
WHERE (условие_объединения1) AND (условие_объединения2) AND (условие_объединения3) …..
Условия объединения должны иметь вид таблица1.поле1 = таблица2.поле2.
Структура с применением команды WHERE поддерживает только внутреннее соединение (аналогичное INNER JOIN).
2.6 Операция UNION
UNION используется для объединения результатов двух или нескольких запросов в один набор результатов. Применяя UNION, вы должны соблюдать два следующих правила:
• все запросы должны иметь одинаковое количество колонок;
• типы данных соответственных колонок из запросов должны быть совместимыми.
Колонки, перечисленные в операторах SELECT, объединенные при помощи UNION, сопоставляются друг с другом следующим образом: первая колонка из первого оператора SELECT будет соответствовать первым колонкам из всех последующих операторов SELECT, вторая колонка будет соответствовать вторым колонками всех последующих операторов SELECT, и т.д. Поэтому во всех операторах SELECT объединенных при помощи UNION, должно быть одинаковое количество колонок, что гарантирует однозначное сопоставление.
Кроме того, соответственные колонки должны иметь совместимые типы данных. Это значит, что соответственные колонки должны иметь либо одинаковые типы данных, либо SQL Server сможет выполнить неявное преобразование одного типа данных в другой.
Структура запроса:
SELECT [ALL] список_полей
FROM имя_таблицы1
[GROUP BY группируемые_поля]
[HAVING условие_для_результата ]
UNION SELECT [ALL] список_полей
FROM имя_таблицы2
[GROUP BY группируемые_поля]
[HAVING условие_для_результата ]
UNION SELECT [ALL] список_полей
FROM имя_таблицы3
[GROUP BY группируемые_поля]
[HAVING условие_для_результата ]
……………………………………………..
[ORDER BY столбцы_сортировки [ASC | DESC] ]
Единственное ключевое слово, которое можно применять вместе с UNION - это необязательное ключевое слово ALL. Если вы примените его, то в набор результатов будут включены также все повторяющиеся строки (другими словами, в набор результатов будут включены полностью все строки). Если ключевое слово ALL не применяется, то по умолчанию из набора результатов будут исключены все дублирующиеся строки.
ORDER BY можно применять не в каждом операторе SELECT объединения, а только в самом последнем. Благодаря этому ограничению, итоговый набор результатов будет отсортирован только один раз сразу для всех результатов. С другой стороны, вы можете применять GROUP BY и HAVING в отдельных операторах, так как они влияют только на отдельные наборы результатов, а не на итоговый набор результатов.
Пример:
SELECT Издательство, 'GOOD' As [Рейтинг издательства]
FROM Издательства
WHERE Рейтинг > 50
UNION
SELECT Издательство, 'BAD' As [Рейтинг издательства]
FROM Издательства
WHERE Рейтинг < 50
Запрос выведет название издательства и оценку в зависимости от рейтинга – GOOD если рейтинг больше 50, BAD если рейтинг меньше 50.
3 ДОМАШНЕЕ ЗАДАНИЕ
Изучить теоретический материал, подготовиться к выполнению лабораторной работы.
4 МЕТОДИЧЕСКИЕ УКАЗАНИЯ
ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНОЙ РАБОТЫ
1. Создать запросы на выборку из нескольких таблиц на языке SQL заданными критериями отбора в соответствии с заданием.
2. Создать запрос на выборку на языке SQL, содержащий статические (агрегатные функции) в соответствии с заданием.
5 КОНТРОЛЬНЫЕ ВОПРОСЫ
Назовите две основных категории команд в языке Transact SQL. Их назначение.
Что делают команды SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY? Особенности их применения.
Какие критерии можно задать для отбора данных?
Особенности применения подзапросов.
Агрегатные функции. Их назначение.
Способы определения связей между таблицами.