4.2SQL Query Analyzer
Команды T-SQL удобнее всего исполнять в графической утилите SQL Query Analyzer, которую, например, можно запустить из программного меню MS SQL Server. Затем необходимо подключиться к серверу, набрав в поле SQL Server имя сервера, откроется окно SQL Query Analyzer (рис. 4.1).
Рис. 4.1. Окно SQL Query Analyzer
4.3Описание базы данных Pubs
База данных Pubs (Publisher) является учебной базой данных MS SQL Server. Ее основное назначение - обеспечить наибольший набор интересных данных для выполнения основных команд T-SQL.
Диаграмма базы данных Pubs представлена на рис 4.2.
Каждый прямоугольник на диаграмме представляет таблицу и содержит название таблицы и названия полей. Линии между прямоугольниками представляют связи между таблицами.
База данных содержит информацию о деятельности трех филиалов издательской компании.
Таблица Publishers содержит информацию о трех издательствах: их идентификационные номера, названия и адреса.
Информация о каждом авторе, имеющем контракт с издателем, содержится в таблице Abthors: с номером карточки социального страхования, именем, фамилией и адресными данными.
Аналогичную информацию о каждом редакторе содержит таблица Editors. Кроме того, в ней имеется дополнительный столбец, описывающий вид выполняемой редактором работы (подбор информации или управление всем проектом).
По вышедшим и готовящимся к печати книгам таблица Titles содержит следующую информацию: идентификационный номер, название, тема, идентификационный номер издательства, цена, расходы, количество проданных экземпляров, состояние контракта, дополнительные заметки и дата выхода. Числа в столбце ytd_sales должны изменяться по мере увеличения количества проданных книг одним из следующих способов:
с помощью команд модификации данных,
из приложений, которые будут автоматически изменять значения в столбце ytd_sales, как только будут вводиться новые данные в таблице SalesDetails,
с помощью инструкций SQL для определения триггеров, выполняющих автоматическое обновление.
Книги и авторы представлены в разных таблицах, но могут быть связаны с помощью третьей таблицы - TitleAuthors. Для каждой книги таблица TitleAuthors содержит строку с значениями идентификатора книги, идентификатора автора, позиции автора в списке авторов книги и информацию по разделению гонорара.
Таблица TitlEditors аналогично связывает книги с их редакторами. Кроме того, она описывает порядок редактирования, т.е. можно узнать, кто был первым или последним редактором.
Таблица Royshed описывает зависимость между количеством проданных книг и размером авторского гонорара. Гонорар составляет определенную часть суммы, полученной за проданные книги.
Таблица Sales содержит общую информацию о заказах, полученных от книжных магазинов, номер квитанции на продажу, полученный от издателя, идентификатор магазина, номер заказа на покупку, получаемый от книжного магазина, и дату выполнения заказа.
Таблица SalesDetails содержит информацию о каждом пункте заказа на покупку, предполагая, что сразу может быть заказано несколько книг: название, количество заказанных книг, количество отправленных книг и дата отправления.
Рис. 4.2. Диаграмма базы данных Pubs
4.4Манипулирование данными: операции выборки данных
В этом разделе рассматривается команда или инструкция SELECT, без преувеличения являющаяся сердцем SQL, ее синтаксис и варианты использования. Все примеры основаны на базе данных Pubs, описание которой рассмотрено выше. Некоторые примеры могут показаться несколько сложными. Но из этого впечатления не следует делать вывод, что сложен сам язык SQL. Дело скорее в том, что не просто проиллюстрировать полную мощность языка SQL в локальных примерах.
При работе с таблицами основная цель всегда заключается в том, чтобы получить только ту информацию, которая требуется пользователям. Запрос к базе данных представляет собой операцию выборки, которая сужает диапазон считываемой информации и возвращает только определенный набор столбцов или строк, удовлетворяющих заданным критериям, из таблиц вашей базы данных.
Выбранные строки и столбцы базы данных всегда собираются во временную таблицу. В большинстве случаев эта таблица существует ровно столько времени, сколько нужно, чтобы данные были переданы запрашивающему клиенту.
4.4.1Синтаксис инструкции SELECT
SELECT [ALL | DISTINCT] список__выбора
[INTO [имя_новой_таблицы]]
FROM {имя_таблицы_1 | имя_представления_1} [(режим оптимизатора)]
[[,{имя_таблицы_2 | имя_представления_2} [(режим оптимизатора)]
[...,{имя_таблицы_N | имя_представления_N} [(режим оптимизатора)]]
[WHERE предложение]
[GROUP BY предложение]
[HAVING предложение]
[ORDER BY предложение]
[COMPUTE предложение]
[FOR BROUSE]
Аргументы инструкции:
список_ выбора. Список названий столбцов (или полей, или атрибутов) таблицы или таблиц базы данных, которые войдут в результирующую таблицу запроса.
Пример 1. Простая выборка. Выбрать (просмотреть) имена и фамилии авторов, которые указаны в таблице Authors. Порядок перечисления имен столбцов может быть произвольным:
SELECT au_fname, au_iname
FROM Authors
Данная инструкция определит для клиента все записи из таблицы Authors, содержащие только два поля au_fname, au_iname.
Особую роль в списке выбора играет символ “звездочка” (*), который означает выбор всех имен столбцов таблицы или таблиц базы данных, указанных в списке_таблиц, стоящем после зарезервированного слова FROM:
SELECT *
FROM список_таблиц
При этом, столбцы отображаются в том порядке, в котором они были определены в инструкции (или инструкциях) CREATE TABLE.
Пример 2. Простая выборка
Выбрать (просмотреть) все данные, которые находятся в таблице Authors:
SELECT *
FROM Authors
Результат выполнения запроса представлен на рис. 4.3.
Рис. 4.3. Результат выполнения запроса
список_таблиц. Список имен таблиц, имена столбцов которых указаны в аргументе список_выбора.
Пример 3. Простое эквисоединение
Просмотреть данные двух таблиц:
SELECT *
FROM Authors, Titles
Или
SELECT Authors.*, Titles.*
FROM Authors, Titles
Возможно также выбрать таблицы из различных баз данных. В этом случае список таблиц состоит из элементов вида:
имя_базы_данных.имя_владельца_базы_данных.имя_таблицы
4.4.2Примеры использования инструкции SELECT
Далее будут рассмотрены примеры, в которых последовательно продемонстрировано использование в запросах ключевых слов инструкции SELECT.
Пример 4. Ограниченная выборка (ограничение строк)
Определить имя, фамилию, номер телефона каждого автора, проживающего в штате Калифорния.
SELECT au_fname, au_lname, state, phone
FROM Аuthos
WHERE state=‘ca’
Данная инструкция будет посылать запрос к серверу и выводить на экран поля (имя, фамилию, штат проживания и номер телефона), указанные в строке SELECT, из таблицы, имя которой указано в строке FROM. При этом выбранные записи должны удовлетворять условию (авторы, проживающие в штате Калифорния), указанному в строке WHERE.
В условии ключевого слова WHERE допустимо использовать другие операции сравнения и логические операторы, которые приведены ниже соответственно в таблице 1 и таблице 2.
Таблица 1. Операторы сравнения
Символ Значение
= Равно
!= Не равно
<> Не равно
< Меньше чем
> Больше чем
<= Меньше или равно
>= Больше или равно
LIKE Равно фрагменту значения
Таблица 2. Логические операторы
Название Значение
OR Логическое ИЛИ
END Логическое И
NOT Логическое отрицание
(аналогичен оператору сравнения != (или <>))
BETWEEN Выбор из диапазона значений
IN Задание списка значений
В Transact-SQL логические операторы позволяют сформировать несколько критериев считывания строк.
Пример 5. Выборка строк, удовлетворяющих заданным условиям
Выбрать авторов, имена которых начинаются с латинской буквы “B” и которые живут в штате Калифорния
SELECT *
FROM Abthors
WHERE au_lname LIKE ‘B%’
AND state=‘ca’
Пример 6. Использование оператора BETWEEN
Определить названия книг, значение цены которых находится в заданном диапазоне.
SELECT *
FROM Titles
WHERE price BETWEEN 15 AND 21
Пример 7. Использование оператора IN
Выбрать названия книг с заданной ценой (из указанного списка, стоящего после оператора IN).
SELECT *
FROM Titles
WHERE price IN (31.0, 20, 55.0)
Для представления неизвестной информации в полях таблиц базы данных используется значение NULL. В T-SQL имеются специальные операторы для выполнения сравнений со значением NULL:
WHERE имя_столбца IS [NOT] NULL
Пример 8. Выборка значений NULL
Получить информацию о книгах, для которых авансовые выплаты еще не определены.
SELECT title, advance
FROM Titles
WHERE advance IS NULL
Для удобства чтения полученной информации в результате произведенной выборки и более удобного ее анализа используется возможность представления этой информации в отсортированном виде:
ORDER BY имя_столбца ASC [DESC]
Здесь
ASC - возрастающий порядок сортировки,
DESC - убывающий порядок сортировки.
Пример 9. Сортировка результирующего набора
Выбрать названия книг, их цены и издательства, в которых они вышли. Результирующий список строк представить отсортированным по возрастанию цены.
SELECT title, price, pub_in
FROM Title
ORDER BY price ASC
В таблице в одном столбце могут храниться повторяющиеся значения. При выборке таких столбцов в результирующей таблице часто требуется просмотреть только уникальные значения. Это можно сделать с помощью ключевого слова DISTINCT.
Пример 10. Выборка уникальных значений
Получить список издательств.
SELECT DISTINCT pub_in
FROM Titles
SQL Server позволяет не только выбирать данные из таблиц, но и в списке_выбора выполнять математические действия над числовыми данными и константами, использовать специальные функции и символьные строки.
Таблица 3. Арифметические операторы Transact-SQL
Символ Действие
+ Сложение
* Умножение
/ Деление
% Модуль (oстаток от деления
целых значений)
Таблица 4. Базовые (агрегатные) функции Transact-SQL
Функция Действие
AVG Подсчет среднего (в столбце)
SUM Суммирование (по столбцу)
MIN Минимальное значение (в столбце)
MAX Максимальное значение (в столбце)
COUNT Подсчет числа выбранных строк
Пример 11. Переопределение имен столбцов при выводе и применение арифметических выражений.
Увеличить цену всех книг на 10%.
SELECT title ‘Название’, price ‘Цена’,
price+price*0.1 AS ‘Новая цена’
FROM Titles
В результирующей таблице исходные имена столбцов получат новые названия и появится новый столбец с именем “Новая цена”.
Пример 12. Использование базовой функции MAX (определение максимального значения в столбце).
Определить самую дорогую книгу
SELECT MAX(price)
FROM Books
Функция возвратит результат в виде столбца (с одним значением цены самой дорогой книги).
Для выполнения объединения данных по некоторому критерию используется предложение GROUP BY, которое делит таблицу на группы строк с одним и тем же значением в заданном столбце.
Пример 13. Использование предложения GROUP BY
Определить количество книг, выпущенных каждым издательством.
SELECT pub_id, “Число книг”=COUNT(*)
FROM Titles
GROUP BY pub_id
При выполнении данного запроса строки с одинаковыми идентификаторами издательств объединяются в группы, для каждой группы вычисляется количество строк с помощью базовой функции COUNT. Результирующая таблица будет содержать столбец с идентификаторами издательств (по одной строке на каждую группу) и столбец с числом книг, выпущенных в каждом из издательств.
Если выделенная группа удовлетворяет значению, указанному в предложении HAVING, то можно выбрать только определенные группы. Другими словами, предложение HAVING применяется к группам и работает аналогично предложению WHERE (но только для групп).
Пример 14. Использование предложения HAVING
Выбрать список идентификаторов издательств, отсортированный по возрастанию, сумму авансовых выплат и среднюю цену книг, не учитывая книг, цена которых меньше или равна 5, при этом значение идентификаторов должно быть больше ‘0800’, авансовые выплаты - больше 15000 и средняя цена книги - меньше 20.
SELECT pub_id, SUM(advance), AVG(price)
FROM Titles
WHERE price > 5
GROUP BY pub_id
HAVING SUM(advance) > 15000
AND AVG(price) < 20
AND pub_id > ‘0800’
ORDER BY pub_id
При выполнении запроса сначала будут выбраны строки, в которых цены книг больше 5 (WHERE price > 5), затем выбранные строки будут собраны в группы по значению идентификатора издательства (GROUP BY pub_id). В полученных группах будет подсчитана сумма выплаченных авансов и средняя цена книги. Промежуточная таблица будет содержать три столбца: идентификатор издательства, сумма выплаченных авансов и средняя цена книги в этом издательстве. В итоговую таблицу будет включен список строк из промежуточной таблицы, которые удовлетворяют условиям предложения HAVING.
|
