Язык SQL

 

 

Цель работы: изучить принципы работы с базой данных в архитектуре клиент-сервер, изучить спецификации запроса языка баз данных SQL, получить практические навыки составления и содержательной интерпретации запросов выборки данных (операторов SELECT), а также их выполнения на SQL-сервере с использованием клиентских утилит

Задачи:

• изучить структуру и элементы SQL-запроса выборки, в том числе разделы FROM, WHERE, GROUP BY, HAVING, ORDER BY, а также предикаты условия поиска и агрегатные функции;
• изучить состав базы данных книготорговой компании (база данных pubs), структуру и семантику ее таблиц;
• в соответствии с вариантом задания произвести содержательную интерпретацию заданных SQL-запросов, выполнить их на SQL-сервере с использованием клиентских утилит SQL Query Analyzer или SQL Enterprise Manager (SQL-EM), проинтерпретировать результаты выполнения запросов.

 

Теоретическая часть

Язык SQL

Первый международный стандарт языка SQL был принят в 1989 г. (SQL/89). В конце 1992 г. Был принят новый международный стандарт SQL/92. “Родным” языком Microsoft SQL Server является язык Transact-SQL (T-SQL), являющийся диалектом стандартного языка SQL. T-SQL поддерживает большинство возможностей языков SQL/89 и SQL/92, а также ряд расширений, увеличивающих возможность программирования и гибкость языка. В частности, в язык T-SQL добавлены конструкции для задания последовательности операций управления в программе (например, if и while), локальных переменных и других конструкций, позволяющих писать более сложные запросы и строить программные объекты, хранящиеся на сервере, в том числе процедуры и триггеры.

Язык SQL включает следующие языки:

• язык определения данных (Data Definition Language или DDL), предназначенный для добавления, модификации и удаления данных в таблицах;
• язык модификации данных (Data Modification Language или DML), предназначенный для добавления, модификации и удаления данных в таблицах.

В синтаксических конструкциях при описании языка будут использоваться следующие соглашения. Нетерминальные элементы заключаются в угловые скобки <>. Необязательная конструкция заключается в квадратные скобки []. Запись вида {A}… означает повторение конструкции А произвольное число раз (включая нулевое). Вертикальные разделители | читаются как “ИЛИ” и служат для выбора одной из конструкций, заключенных в скобки.  

 

Оператор SELECT

Оператор SELECT используется для запросов к базе данных и выборки результатов. Синтаксис оператора SELECT следующий:

<оператор SELECT>::=

SELECT [ALL | DISTINCT] <список выборки>

<табличное выражение>

ORDER BY <спецификация сортировки>]

<табличное выражение>::=

FROM <имя таблицы>[{,<имя  таблицы>}…]

[WHERE <условие  поиска>]

[GROUP BY <имя столбца> [{,<имя столбца>}…]

[HAVING <условие  поиска>] 

 

Если задано ключевое слово DISTINCT, то из результирующей таблицы удаляются повторяющиеся строки. Список выборки определяет, какие столбцы должны быть возвращены в результирующую таблицу. Данный список представляет список арифметических выражений над значениями столбцов таблиц из раздела FROM и констант. В простейшем случае он может быть, например, списком имен некоторых столбцов таблиц из раздела FROM. В случае, если вместо списка выборки стоит звездочка (*), то выбираются все столбцы таблиц из раздела FROM.

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

Раздел WHERE служит своего рода фильтром при отборе данных.

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

Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска является истинным. Условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки, поэтому в данном случае прямо можно использовать только столбцы, указанные в качестве столбцов группирования в разделе GROUP BY.

Раздел ORDER BY позволяет установить желаемый порядок просмотра результирующей таблицы. Спецификация сортировки имеет следующий синтаксис:

<спецификация  сортировки>::= {<целое без знака> | <имя столбца>} [ASC | DESC]

Как видно, фактически задается список столбцов, и для каждого столбца указывается порядок просмотра строк результирующей таблицы в зависимости от значений этого столбца (ASC - по возрастанию (умолчание), DESC - по убыванию). Указывать сортируемый столбец можно по имени или по порядковому номеру в результирующей таблице.  

 

Предикаты условия поиска

В условии поиска могут использоваться следующие предикаты: предикат сравнения, предикат BETWEEN , предикат IN, предикат LIKE, предикат NULL, предикат с квантором и предикат EXISTS.

Предикат IN определяется следующим образом:

<предикат IN>::= <выражение> [NOT] IN (<значение> [,<значение>...] | .<подзапрос>)

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

Подзапрос- это запрос, используемый в предикате условия поиска. Результатом выполнения подзапроса является единственный столбец.

Предикат BETWEEN определяется следующим образом:

<предикат BETWEEN>::= <выражение> [NOT] BETWEEN <выражение> AND <выражение>

По определению результат x BETWEEN y AND z тот же самый, что результат логического выражения x>=y AND x<=z.

Предикат LIKE имеет следующий синтаксис:

<предикат LIKE>::= <имя столбца> [NOT] LIKE <шаблон>[ESCAPE <escape-символ>]

Значение предиката LIKE является истинным, если шаблон является подстрокой заданного столбца. При этом, если раздел ESCAPE отсутствует, то при составлении шаблона со строкой производится специальная интерпретация символов-заместителей шаблона: символ подчеркивания ("_") обозначает любой одиночный символ, символ процента ("%") обозначает последовательность произвольных символов произвольной длины (может быть нулевой), парные квадратные скобки представляют любой символ, записанный в скобках. Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов "x_" и "x%" представляют одиночные символы "_" и "%" соответственно.

Предикат NULL описывается синтаксическим правилом:

<предикат NULL>::= <имя столбца> IS [NOT] NULL

Значение "x IS NULL" является истинным, когда значение x неопределено.

Предикат EXISTS имеет следующий синтаксис:

<предикат EXISTS>::= EXISTS <подзапрос>

Значение предиката является истинным, когда результат вычисления подзапроса не пуст. 

 

Агрегатные функции

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

• AVG - функция определения среднего значения;
• MAX - функция определения максимального значения;
• MIN - функция определения минимального значения;
• SUM - функция суммирования значений;
• COUNT - функция для подсчета числа строк или значений.

Грамматика агрегатных функций следующая:

<агрегатная  функция>::= COUNT(*) | <distinct-функция> | <all-функция>

<distinct-функция>::= {AVG | COUNT | MAX | MIN | SUM} (DISTINCT <имя столбца>)

<all-функция>::= {AVG | MAX | MIN | SUM} ([ALL]<выражение>)

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Функция типа distinct выполняет вычисления только над одним столбцом, а в вычислениях используются только уникальные значения столбца. При использовании функции типа all список значений формируется из значений арифметического выражения, вычисляемого для каждой строки заданного множества.  

 

Операции реляционной алгебры

Большинство SQL-запросов требует одновременного обращения к нескольким таблицам. Часто такого рода запросы основываются на операциях реляционной алгебры, в частности, соединения, декартова произведения, объединения, пересечения и разности.

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

<раздел FROM>::= FROM <имя таблицы> [JOIN <имя таблицы> ON <условие соединения> ...]

При выполнении декартова произведения двух таблиц производится таблица, строки которой являются конкатенацией строк первой и второй таблиц. Операцию декартова произведения можно реализовать с использованием SQL-запроса типа SELECT-FROM. По стандарту ANSI операция декартова произведения может указываться явно в разделе FROM с использованием ключевой фразы CROSS JOIN.

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

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

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

Описание задания

База данных книготорговой компании

Рассмотрим простую предметную область жизнедеятельности, связанную с книгоизданием и маркетингом. В рамках данной предметной области существуют издатели, которые публикуют книги, авторы, которые книги пишут, и издания (сами книги). Разработана база данных pubs, определяющая описанную выше предметную область. Инфологическая модель предметной области с использованием диаграмм “сущность-связь” (ER-диаграмм)), разработанных Ченом, представлена на рис. 1.

На данном рисунке прямоугольниками обозначены типы сущностей (объектов), а ромбами - типы связей между сущностями. Атрибуты сущностей указаны мелким шрифтом в том же прямоугольнике, который отображает типы сущностей. Имя типа сущности отмечено в верхней части прямоугольника жирным шрифтом. Атрибуты связей в данном случае обозначены овалами. Как видно из рис. 1 у связи “Написана” имеется два атрибута: первый атрибут определяет порядок автора в названии книги, второй атрибут - гонорар автора книги.

Рис. 1

База данных книготорговой компании (база данных pubs) включает три таблицы, определяющие сущности: таблица authors определяет авторов, таблица publishers - издателей, а таблица titles - сами книги. Четвертая таблица titleauthor задает отношение между таблицами titles и authors. Она показывает, какие авторы написали какие книги. Связь между таблицами titiles и publishers определяется столбцом pub_id в данных таблицах.

Ниже представлены структуры используемых таблиц.

 

Имя столбца	Тип данных	Размерность	Возможность значений null	Содержательное описание
au_id	varchar	11	Нет	Идентификатор автора
au_lname	varchar	40	Нет	Фамилия автора
au_fname	varchar	20	Нет	Имя автора
phone	char	12	Нет	Номер телефона
address	varchar	40	Да	Адрес (улица, дом, квартира)
city	varchar	20	Да	Город проживания
state	char	2	Да	Штат проживания
zip	char	5	Да	Энергичность
contract	bit	1	Нет	Наличие контракта

Структура таблицы authors 

 

Структура таблицы publishers