Простые запросы

Для наспростым запросом будет запрос, который обращается только к одной таблице базы данных. Простые запросы помогут нам проиллюстриро­вать основную структуру SQL.

Простой запрос. Запрос, который обращается только к одной таблице базы данных.

Запрос: Кто работает штукатурами?

SELECT NAME

FROM WORKER

WHERE SKILL_TYPE = 'Штукатур'

Результат:

NAME

К.Немо

Г.Риковер

 

Этот запрос иллюстрирует три наиболее часто встречающиеся фразы SQL: SELECT, FROM и WHERE. Хотя в нашем примере мы поместили их на разные строки, они все могут стоять в одной строке. Они также могут поме­щаться с разными отступами, а слова внутри фраз могут разделяться произ­вольным числом пробелов. Рассмотрим характеристики каждой фразы.

Select. Фраза SELECT перечисляет столбцы, которые должны войти в результирующую таблицу. Это всегда столбцы некоторой реляционной таб­лицы. В нашем примере результирующая таблица состоит из одного столбца (NAME), но в общем случае она может содержать несколько столбцов; она также может содержать вычисленные значения или константы. Мы приве­дем примеры каждого из этих вариантов. Если результирующая таблица должна содержать более одного столбца, то все нужные столбцы перечисля­ются после команды SELECT через запятую. Например, фраза SELECT WORKER_ID, NAME выдаст в результате таблицу, состоящую из столбцов WORKER_ID и NAME.

Фраза SELECT. Задает столбцы результирующей таблицы.

From. Фраза FROM задает одну или более таблиц, к которым обраща­ется запрос. Все столбцы, перечисленные во фразах SELECT и WHERE, должны существовать в одной из таблиц, перечисленных в команде FROM. В SQL2 эти таблицы могут быть напрямую определены в схеме как базовые таблицы или представления данных, или же они сами могут быть не имею­щими имен таблицами, полученными в результате запросов SQL. В послед­нем случае запрос явно приводится в команде FROM.

Фраза FROM. Задает существующие таблицы, к которым обращается запрос.

Where. Фраза WHERE содержит условие. на основании которого выби­раются строки таблицы (таблиц). В нашем примере условие состоит в том, что столбец SKILL_TYPE должен содержать константу 'Штукатур', заклю­ченную в апострофы, как это всегда делается с текстовыми константами в SQL. Фраза WHERE — наиболее изменчивая команда SQL; она может со­держать множество разнообразных условий. Большая часть нашего изложе­ния будет посвящена иллюстрации различных конструкций, разрешенных в команде WHERE.

Фраза WHERE.Задает условие, на основании которого выбираются строки из заданных таблиц.

Приведенный выше запрос SQL обрабатывается системой в следующем порядке: FROM, WHERE, SELECT. To есть строки таблицы, указанной в ко­манде FROM, помещаются в рабочую область для обработки. Затем к каждой строке последовательно применяется фраза WHERE. Все строки, не удовле­творяющие условию WHERE, исключаются из рассмотрения. Затем те строки, которые удовлетворяют условию WHERE, обрабатываются командой SELECT. В нашем примере из каждой такой строки выбирается NAME, и все выбранные значения выводятся в качестве результатов запроса.

Запрос: Привести все данные о зданиях офисов.

SELECT *

FROM BUILDING

WHERE TYPE = 'Офис'

Результат:

BUILDING

BLDG IDАДРЕСTYPEQLTY LEVELSTATUS

312 Ул.Вязов, 123 Офис 2 2

210 Березовая ул. 1011 Офис З 1

111 Осиновая ул. 1213 Офис 4 1

Звездочка (*) в команде SELECT означает «строка целиком». Это удобное сокращение, которым мы будем часто пользоваться.

Запрос: Какова недельная зарплата каждого электрика?

 

SELECT NAME, 'Недельная зарплата = ', 40 * HRLY_RATE

FROM WORKER

WHERE SKILL_TYPE = 'Электрик'

ORDER BY NAME

Результат:

NAME

М.Фарадей Недельная зарплата = 500.00

Х.Колумб Недельная зарплата = 620.00

Этот запрос иллюстрирует употребление и символьных констант (в на­шем примере 'Недельная зарплата = '), и вычислений в команде SELECT, Внутри команды SELECT можно производить вычисления, в которых ис­пользуются числовые столбцы и числовые константы, а также стандартные арифметические операторы (+, -, *, /), сгруппированные по мере необходи­мости с помощью скобок. Мы также включили новую команду ORDER BY, которая сортирует результат запроса в возрастающем алфавитно-числовом порядке по указанному столбцу. Если вы хотите упорядочивать результаты по убыванию, то к команде нужно добавить DESC. Фраза ORDER BY может сортировать результаты по нескольким столбцам, по одним - в порядке возрастания, по другим - в порядке убывания. Первым указывается столбец первичного ключа сортировки.

Символьная константа. Константа, состоящая из букв, цифр и «специальных» символов.

Запрос: У кого почасовая ставка от 10 до 12 долларов?

SELECT *

FROM WORKER

WHERE HRLY_RATE > = 10 AND HRLY_RATE < - 12

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

2920 Р.Гаррет 10.00 Кровельщик 2920

1520 Г.Риковер 11.75 Штукатур 1520

Этот запрос иллюстрирует некоторые дополнительные возможности ко­манды WHERE: операторы сравнения и булеву операцию AND (И). Для сравнения столбцов с другими столбцами или с константами могут исполь­зоваться шесть операторов сравнения (=, <> (не равно), <, >, <=, >=). Для создания составных условий или для отрицания условия могут использо­ваться булевы операции AND (И), OR (ИЛИ) и NOT (HE). Для группировки условий, как обычно в языках программирования, могут использоваться скобки.

Операторы сравнения =, <>, <, >, <=, >=.

Булевы операцииAND (И), OR (ИЛИ) и NOT (HE).

 

Для формулировки этого запроса также можно былоиспользоватьоператор BETWEEN (между):

SELECT *

FROM WORKER

WHERE HRLY_RATE BETWEEN 10 AND 12

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

 

 

Запрос: Перечислить штукатуров, кровельщиков и электриков.

SELECT *

FROM WORKER

WHERE SKILL_TYPE IN ('Штукатур', 'Кровельщик', 'Электрик')

 

Результат:

WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Электрик 1311

1412 К.Немо 13.75 Штукатур 1520

2920 Р.Гаррет 10.00 Кровельщик 2920

1520 Г.Риковер 11.75 Штукатур 1520

1311 Х.Колумб 15.50 Электрик 1311

Этот запрос поясняет использование оператора сравнения IN (В). Усло­вие WHERE считается истинным, если тип специальности строки располо­жен внутри множества, указанного в скобках, то есть если тип специаль­ности - штукатур, кровельщик или электрик. Мы еще встретимся с опера­тором IN в подзапросах.

Предположим, что мы не можем точно вспомнить написание специаль­ности: «электрик» или «электронщик» или еще как-то. Символы шаблона, которые замещают неопределенные строки символов, облегчают поиск не­точного написания в запросе.

Символы шаблона.Символы, замещающие неопределенные строки символов.

Запрос: Перечислить работников, чей тип специальности начинается с «Элек».

SELECT *

FROM WORKER

WHERE SKILL_TYPE LIKE ('Элек%')

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Электрик 1311

1311 Х.Колумб 15.50 Электрик 1311

В SQL есть два символа шаблона: % (процент) и _ (подчеркивание). Подчеркивание замещает ровно один неопределенный символ. Процент за­мещает произвольное число символов, начиная с нуля. Когда используются символы шаблона, для сравнения символьных переменных с константами требуется оператор LIKE (как). Другие примеры:

NAME LIKE '__Колумб'

NAME LIKE '__K%'

Условие в первом примере истинно, если NAME состоит из двух симво­лов, за которыми следует 'Колумб'. В таблице WORKER все имена начина­ются с первого инициала и точки. Таким образом, при помощи этого усло­вия мы. найдем всех работников по фамилии «Колумб». Условие второго примера позволяет найти всех работников, чьи фамилии начинаются на бу­кву «К».

Запрос:Найти все работы, которые начинаются в течение ближайших двух недель.

SELECT *

FROM ASSIGNMENT

WHERE START _DATE BETWEEN CURRENT_DATE AND

CURRENT_DATE + INTERVAL '14' DAY

Результат: (Предположим, что текущая дата CURRENT DATE = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Этот запрос иллюстрирует употребление оператора BETWEEN (между) со значениями типа date (дата) и interval (промежуток). CURRENT_DATE — это функция, всегда возвращающая значение сегодняшней даты. Выражение

CURRENT_DATE + INTERVAL '14' DAY

 

прибавляет двухнедельный промежуток к текущей дате. Таким образом, ASSIGNMENT выбирается (в предположении, что сегодня 10.10) в том слу­чае, если в ней значение столбца START_DATE лежит между 10.10 и 24.10. Из этого видно, что мы можем прибавлять к полям дат величины типа interval. Более того, мы можем умножать значения промежутков на целые величины. Например, предположим, что мы хотим выяснить, какое число будет через определенное количество недель (обозначенное переменной NUM_WEEKS (ЧИСЛО НЕДЕЛЬ)). Мы можем это сделать так:

CURRENT_DATE + INTERVAL '7' DAY * NUM_WEEKS

Задание 1. Выполните следующие запросы к базе данных «Перевозки».

1. Название клиента 680.

2. Какой пункт назначения груза № 3244?

3. Перечислить номера грузовиков, перевозивших грузы весом более 100 фунтов

4. Выдать все данные о грузах более 20 фунтов весом.

5. Создать алфавитный список клиентов с годовым доходом более 100000 долларов.

6. Какой ИД клиента у «Братьев Уилсон»?

7. Выдать названия и средний месячный доход клиентов, имеющих годовой доход свыше 10 миллионов долларов, но менее 50 миллионов долларов.

8. Выдать ИД клиентов, отправлявших грузы в Атланту, Сент-Луис или Балтимор.

9. Выдать названия клиентов, отправлявших грузы в города, названия которых начинаются на «С».

10. Выдать названия клиентов, отправлявших грузы в города, названия которых заканчиваются «-Сити».

11. Выдать названия клиентов, в названии которых третья буква - заглавная «М».

12. Выдать названия всех клиентов - торговых фирм.

 

2. Многотабличные запросы

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

Запрос: Каковы специальности рабочих, назначенных на здание 435?

Данные, необходимые для ответа, находятся в двух таблицах: WORKER и ASSIGNMENT. Для решения в SQL требуется перечислить обе таблицы в команде FROM и задать специальный тип условия WHERE:

 

SELECT SKILL_TYPE

FROM WORKER, ASSIGNMENT

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID

AND BLDG_ID = 435

Что здесь происходит? Мы должны рассмотреть два этапа обработки сис­темой данного запроса.

1. Как обычно, сначала обрабатывается фраза FROM. Однако в этом слу­чае, поскольку в команде указаны две таблицы, система создает декар­тово произведение строк этих таблиц. Это означает, что создается (логически) одна большая таблица, состоящая из столбцов обеих таб­лиц, в которой каждая строка одной таблицы спарена с каждой стро­кой другой таблицы. В нашем примере, поскольку в таблице WORKER пять столбцов, а в таблице ASSIGNMENT четыре столбца, в декартовом произведении, созданном командой FROM, будет девять столбцов. Общее число строк декартова произведения равно m * n, где m - число строк таблицы WORKER; а n - число строк таблицы ASSIGNMENT. По­скольку в таблице WORKER 7 строк, а в таблице ASSIGNMENT 19 строк, то декартово произведение будет содержать 7х19 или 133 строки. Если в команде FROM перечислено более двух таблиц, то создается декартово произве­дение всех таблиц, указанных в команде.

Декартово произведение. Результат объединения каждой строки одной таблицы с каждой строкой другой таблицы.

2. После создания гигантской реляционной таблицы система, как и пре­жде, применяет команду WHERE. Каждая строка таблицы, созданной командой FROM. проверяется на выполнение условия WHERE. Строки, не удовлетворяющие условию, исключаются из рассмотрения. Затем к оставшимся строкам применяется фраза SELECT.

Фраза WHERE в нашем запросе содержит два условия:

1. WORKER. WORKER_ID = ASSIGNMENT.WORKER_ID

2. BLDG_ID = 435

Первое из этих условий - условие соединения. Обратите внимание, что поскольку обе таблицы WORKER и ASSIGNMENT содержат столбец с име­нем WORKER_ID, их декартово произведение будет содержать два столбца с таким именем. Для того чтобы различать их, мы помещаем перед именем столбца имя исходной таблицы, отделяя его точкой.

Первое условие означает, что в любой выбранной строке значение столбца WORKER_ID из таблицы WORKER должно совпадать со значением столбца WORKER_ID из таблицы ASSIGNMENT. В действительности мы со­единяем две таблицы по WORKER_ID. Все строки, в которых значения этих двух столбцов не равны, исключаются из таблицы произведения. В точности то же самое происходит при выполнении операции естественного соединения реляционной алгебры. (Однако, некоторое отличие от естественного соедине­ния все же есть: язык SQL автоматически не удаляет лишний столбец WORKER_ID). Полное соединение этих двух таблиц с дополнительным усло­вием BLDG_ID = 435 представлено на рис. 1. Применение команды SELECT даст, в конце концов, следующий результат запроса:

SKILL TYPE

Штукатур

Кровельщик

Электрик

Рис. 1. Соединение таблиц WORKER и ASSIGNMENT

Теперь мы покажем, как в SQL присоединить таблицу к ней самой.

Запрос: Перечислить работников, указав имена их менеджеров.

SELECT А.WORKER_NAME, B.WORKER_NAME

FROM WORKER A, WORKER В

WHERE B.WORKER_ID = A.SUPV_ID

Фраза FROM в этом примере создает две «копии» таблицы WORKER, давая им псевдонимы А и В. Псевдоним - это альтернативное имя, данное таблице. Затем копии А и В таблицы WORKER соединяются командой WHERE на основании условия равенства WORKER_ID в В и SUPV_ID в А. Таким образом, каждая строка из А присоединяется к строке В, содержащей информацию о менеджере строки А (рис.2).

Рис. 2. Соединение двух копий таблицы WORKER

Выбирая из каждой строки два имени работника, мы получим требуемый список:

А.NAMEВ.NAME

М.Фарадей Х.Колумб

К.Немо Г.Риковер Р.Гаррет Р.Гаррет

П.Мэйсон П.Мэйсон Г.Риковер Г.Риковер Х.Колумб Х.Колумб Дж.Барристер П.Мэйсон

Псевдоним. Альтернативное имя, данное таблице.

A.WORKER_NAME представляет работника, a B.WORKER_NAME пред­ставляет менеджера. Обратите внимание, что некоторые работники - сами себе менеджеры, что следует из выполненного в их строках равенства WORKER_ID - SUPV_ID.

В SQL можно за один раз связать более двух таблиц:

Запрос: Перечислить работников, назначенных на здания офисов.

SELECT WORKER_NAME

FROM WORKER, ASSIGNMENT, BUILDING

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

TYPE = 'Офис'

 

Результат:

NAME

М.Фарадей

К.Немо

Р.Гаррет

П.Мэйсон

Г.Риковер

Дж.Барристер

 

Обратите внимание, что если имя столбца (например, WORKER_ID или BLDG_ID) встречается более, чем в одной таблице, то для избежания неопределенности мы должны перед именем столбца указать имя исходной таблицы. Но если имя столбца встречается только в одной таблице, как TYPE в нашем примере, то никакой неопределенности нет, поэтому имя таблицы указывать не нужно.

Команды SQL этого запроса создают одну таблицу из трех реляционных таблиц базы данных. Первые две таблицы соединяются по WORKER_ID, после чего к полученной таблице присоединяется третья таблица по BLDG_ID. Условие

TYPE = 'Офис'

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

Задание 2. Выполните следующие запросы к базе данных «Перевозки».

1. Каковы названия клиентов, отправлявших грузы в Солт-Лейк-Сити?

2. В какие пункты назначения компании с годовым доходом менее од­ного миллиона долларов отправляли грузы?

3. Названия и население городов, получавших грузы весом более 100 фунтов.

4. Кто из клиентов с годовым доходом более 10 миллионов долларов от­правлял грузы весом менее 100 фунта?

5. Кто из клиентов с годовым доходом более 10 миллионов долларов от­правлял грузы весом менее 100 фунта или отправлял грузы в Балтимор?

6. Кто из водителей доставлял грузы для клиентов с годовым доходом более 20 миллионов долларов в города с населением свыше одного миллиона человек?

 

3. Подзапросы

Подзапрос.Запрос внутри запроса

Подзапрос может помещаться в команду WHERE запроса, в результате чего возможности команды WHERE расширяются. Рассмотрим пример.

Запрос: Каковы специальности рабочих, назначенных на здание 435?

SELECT SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(SELECT WORKER_ID

FROM ASSIGNMENT

WHERE BLDG_ID = 435)

Подзапрос в этом примере

(SELECT WORKER_ID

FROM ASSIGNMENT

WHERE BLDG_ID = 435)

 

Запрос, в котором содержится подзапрос, называется внешним запросом или главным запросом. Подзапрос приводит к созданию следующего множе­ства ИД (идентификаторов) работников:

WORKER ID

Внешний запрос. Главный запрос, в котором содержатся все подзапросы.

Затем это множество ИД занимает место подзапроса во внешнем запросе. С этого момента выполняется внешний запрос, использующий множество, созданное подзапросом. Внешний запрос обрабатывает каждую строку таб­лицы WORKER в соответствии с условием WHERE. Если WORKER_ID строки лежит в (IN) множестве, созданном подзапросом, то SKILL_TYPE строки выбирается и выводится в результирующей таблице:

SKILL TYPE

Штукатур

Кровельщик

Электрик

Очень важно, что фраза SELECT подзапроса содержит WORKER_ID и только WORKER_ID. В противном случае фраза WHERE внешнего запроса, означающая, что WORKER_ID лежит в множестве ИД работников, не имела бы смысла.

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

Некоррелированный подзапрос.Подзапрос, значение которого не зависит ни от какого внешнего запроса.

Приведем пример подзапроса внутри подзапроса.

Запрос: Перечислить работников, назначенных на здания офисов.

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

SELECT WORKER_MAME

FROM WORKER

WHERE WORKER_ID IN

(SELECT WORKER_ID

FROM ASSIGNMENT

WHERE BLDG_ID IN

(SELECT BLDG_ID

FROM BUILDING

WHERE TYPE = 'Офис'))

Результат:

NAME

М.Фарадей

К.Немо

Р.Гаррет

П.Мэйсон

Г.Риковер

Дж.Барристер

 

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

Выполнение запроса происходит в порядке изнутри наружу. То есть са­мый внутренний запрос (или «самый нижний») выполняется первым, затем выполняется содержащий его подзапрос, а затем внешний запрос.

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

Коррелированный подзапрос. Подзапрос, результат которого зависит от строки, рассматриваемой главным запросом.

Запрос: Перечислить работников, чьи почасовые ставки выше, чем ставки их менеджеров.

SELECT WORKER_NAME

FROM WORKER A

WHERE A.HRLY_RATE >

(SELECT B.HRLY_RATE

FROM WORKER В

WHERE B.WORKER_ID = A.SUPV_ID)

 

Результат:

NAME

К. Немо

Логические этапы выполнения этого запроса таковы:

1. Система создает две копии таблицы WORKER: копию А и копию В. В соответствии с тем, как мы их определили, А относится к работнику, В - к менеджеру.

2. Затем система рассматривает каждую строку А. Данная строка выбира­ется, если она удовлетворяет условию WHERE. Это условие означает, что строка будет выбрана, если величина HRLY_RATE в ней больше, чем HRLY_RATE, порожденная подзапросом.

3. Подзапрос выбирает величину HRLY_RATE из строки В, WORKER_ID которой равен SUPV_ID строки А, в данный момент рассматриваемой главным запросом. Это HRLY_RATE менеджера.

Обратите внимание, что поскольку A.HRLY_RATE может сравниваться только с одной величиной, подзапрос должен выдавать только одну вели­чину. Эта величина меняется в зависимости от того, какая строка А рас­сматривается. Таким образом, подзапрос коррелирует с главным запросом. Мы встретимся с другими примерами коррелированных подзапросов позже, когда будем изучать встроенные функции.

Задание 3. Выполните следующие запросы к базе данных «Перевозки».

1. Перечислить города, получавшие грузы от клиентов с годовым дохо­дом свыше 15 миллионов долларов.
2. Перечислить имена водителей, доставлявших грузы весом более 100 фунтов.
3. Перечислить названия и годовые доходы клиентов, отправлявших грузы весом более 100 фунтов.
4. Перечислить названия и годовые доходы клиентов, чьи грузы достав­лял водитель Дженсен.