 |
Раздел 2. Структура запроса
|
|
|
|
Ч.1
материалы к лекциям
Ростов-на-Дону
2008 г.
Автор: Заставной Д.А., преподаватель кафедры информатики и информационных таможенных технологий Ростовского филиала РТА, к.т.н.
Рецензенты
Букатов А.А. – заместитель директора ЮГИНФО ЮФУ, к.т.н., доц.
Крицкий С.П. – заведующий кафедрой информатики и информационных таможенных технологий, к.т.н., доц.
Ответственный за выпуск
Крицкий С.П. – заведующий кафедрой информатики и информационных таможенных технологий, к.т.н., доц.
Утверждено на заседании кафедры информатики и ИТТ 21 октября 2008 года (протокол №3)
Данное издание адресовано студентам четвертого курса Ростовского филиала Российской таможенной академии, специальность 080115 («Таможенное дело»), специализация «Информационные таможенные технологии». В нем содержится описание синтаксиса команд языка баз данных Oracle SQL, относящихся к запросам и манипулированию данными, и примеры, иллюстрирующие их использование и практическое применение. Представленный в пособии материал полностью соответствует разделу программы учебной дисциплины «Системы управления базами данных».
Издание также может быть использовано в качестве справочного материала при выполнении практической работы с системой Oracle SQL Server и для самостоятельного углубленного изучения курса.
© Российская таможенная академия,
Ростовский филиал, 2008
© Заставной Д.А.
Оглавление
Введение. 4
Раздел 1. Общие сведения о синтаксисе языке SQL. 5
Раздел 2. Структура запроса. 6
Раздел 3. Использование встроенных функций и преобразование типов. 14
Раздел 4. Условия выборки, условные выражения, логические предикаты…22
|
|
|
Раздел 5. Группировка и агрегатные функции. 28
Раздел 6. Пустые значения. 34
Раздел 7. Запросы с соединением. 37
Раздел 8. Подзапросы.. 48
8.1. Подзапросы в логических выражениях WHERE и HAVING.. 48
8.2. Подзапросы после FROM.. 58
Раздел 9. Оператор CASE. 60
Раздел 10.Множественные операции. 62
Раздел 11. Команды манипулирования данными. 67
11.1 Добавление записей. 67
11.2 Удаление строк. 71
11.3 Изменение строк. 72
Заключение. 75
Список литературы.. 76
Указатели. 77
Введение
В данном издании описаны команды языка SQL для манипулирования данными и построения запросов на основе Oracle SQL Server 10g.
При изложении материала приняты следующие лексические соглашения. Стандартные и зарезервированные слова языка SQL записываются прописными буквами, а имена таблиц, полей и других объектов – малыми; данное соглашение, однако, не является требованием синтаксиса языка, и используется в методических целях. Вводимые новые понятия (а также текст комментариев к запросам) выделяются курсивом.
Тексты приводимых примеров SQL-запросов сопровождаются описанием его содержательного смысла и его формальным значением в виде таблицы; ее заголовок может опускаться, если названия колонок очевидны или не представляют интереса. Все приводимые в издании запросы имеют номера, состоящие из номера раздела и порядкового номера внутри раздела.
При описании SQL-команд автор счел возможным не проводить полный синтаксис, а вводить новые варианты по мере изложения материала, отсылая читателя при необходимости к соответствующим разделам системной документации и источникам в приводимой литературе.
Раздел 1. Общие сведения о синтаксисе языке SQL
Текст, написанный на языке SQL, представляет собой набор команд, исполняемых по порядку; исполнение (интерпретация) каждой команды обычно не зависит от исполнения предыдущих команд. Файл с SQL-командами часто называют скриптом. Для разделения нескольких команд в файле необходимо использовать символ “;”. Каждую команду можно записывать на нескольких строках.
|
|
|
Команда языка SQL содержит:
1. Служебные и зарезервированные слова, например: CREATE, SELECT. SQL-команда всегда начинается с зарезервированного слова. Регистр букв при написании служебных слов значения не имеет.
2. Имена таблиц, полей и других объектов базы данных. Традиционно такое имя – набор символов – букв латинского алфавита и цифр, начинающееся с буквы, которая не может содержать специальные символы и разделители (например, пробелы) и не может совпадать с зарезервированными словами; регистр букв также значения не имеет. Однако имена можно помещать в двойные кавычки и в этом случае приведенные выше ограничения на состав имен не применяются; использование таких имен полезно, например, при определении объектов с именами на русском языке.
3. Литеральные константы (см. таблицу 1). Строки, в отличие от имен объектов, записываются в одиночных кавычках. Следует иметь в виду, что формат вывода вещественных чисел и дат зависит от региональных настроек.
4. Операторы (например, арифметические), используемые для построения выражений.
5. Дополнительные символы, например, скобки “(“ и “)”.
Таблица 1. Литеральные значения
| целые числа | -4 |
| вещественные числа | -2.4 (десятичное представление) 1.3E+01 (научная запись) $199.99 (денежная запись) |
| строки | ‘abc’ ‘Строка’ N’Строка из символов национальных алфавитов’ |
| даты | ‘10-JUL-08’ ‘22, Friday August 2008’ ‘22 08 2008’ |
Внутри SQL-команды слова должны разделяться стандартными разделителями, например пробелами и табуляцией; операторы обычно также считаются разделителями.
Внутри одной команды или между командами можно вставлять комментарии, записываемые одним из следующих способов:
- -- текст комментария на одной строке (до конца строки);
- /* текст комментария на одной или на нескольких строках. После комментария запрос может продолжиться на той же строке */.
Раздел 2. Структура запроса
SQL–база данных состоит из нескольких таблиц. Каждую таблицу можно представить как множество записей или строк; все строки в таблице имеют одинаковую структуру, которая определяется при создании таблицы. Для создания таблицы используется команда CREATE TABLE, которая будет подробно описываться во второй части издания. Ниже приведен пример создания таблицы.
|
|
|
CREATE TABLE acceptedorders -- принятые заказы
(item CHAR(20),
price FLOAT,
quantity INT,
customer_address CHAR(100),
customer_name CHAR(20),
orderdate DATE);
Здесь item, price – имена полей таблицы, называемые также иногда колонками таблицы, а FLOAT, INT, CHAR – типы значений, хранящиеся в этих полях.
Для извлечения данных из SQL-таблиц используется команда SELECT. Ниже приведен простейший запрос в таблице acceptedorders:
SELECT * FROM acceptedorders; (2.1)
Этот запрос извлекает из указанной таблицы все строки и помещает их в результирующее множество, или выборку. Если SQL-запрос исполняется в среде интерпретатора, результирующее множество (всё или только ее первые строчки) печатается на экране в виде таблицы:
| ITEM | PRICE | QUANTITY | CUSTOMER_ADDRESS | CUSTOMER_NAME | ORDER_DATE |
| Watch | 199.99 | Green str. 2 | Mr.Pundleberry | 02-AUG-08 | |
| Book | 12.5 | Green str. 2 | Mr.Pundleberry | 15-AUG-08 | |
| DVD Disk | 6.35 | Docks | Mr.O'Raily | 03-AUG-08 | |
| Book | 12.5 | Pall Mall, 3 | Ms.Magpie | 11-AUG-08 | |
| Pizza | 5.5 | Pall Mall, 3 | Ms.Magpie | 02-AUG-08 | |
| PS3 | Pall Mall, 3 | Ms.Magpie | 01-AUG-08 | ||
| Pizza | 5.5 | Docks | Mr.O'Raily | 02-AUG-08 |
В предыдущем запросе символ “*” использовался для обозначения всех полей таблицы. Более типичными являются запросы с явно указанными именами полей:
SELECT item, price, customer_address
FROM acceptedorders; (2.2)
| ITEM | PRICE | CUSTOMER_ADDRESS |
| Watch | 199.99 | Green str. 2 |
| DVD Disk | 6.35 | Docks |
| Book | 12.5 | Pall Mall, 3 |
| Pizza | 5.5 | Pall Mall, 3 |
| Pizza | 5.5 | Docks |
| Book | 12.5 | Green str. 2 |
| PS3 | 199.99 | Pall Mall, 3 |
Заголовки колонок результирующего множества, как видно, совпадают с именами извлекаемых полей. Если имена полей некоторой таблицы пользователю неизвестны, можно воспользоваться командой DESCR, которая печатает имена полей, их типы и другую информацию о структуре таблицы.
DESCR acceptedorders; (2.3)
В общем случае после слова SELECT в тексте запроса указывается список выражений, разделяемых запятой. В эти выражения могут входить:
- имена полей таблиц (эти таблицы должны быть перечислены после слова FROM текста запроса);
- литеральные константы;
- операторы (арифметические “+”, “-“, “*”. “/”, операция конкатенации (слияния) строк “||”),
|
|
|
- функции.
Специальным случаем выражения является списочное выражение – набор выражений, перечисляемых через запятую и заключенных в скобки, например:
- (‘Ada’, ‘Polie’, ‘Bill’);
- (123, 23).
Использование списочных выражений рассматривается в последующих разделах.
В следующем запросе вычисляется стоимость товара с учетом количества заказанных единиц:
SELECT item, price*quantity, customer_address
FROM acceptedorders; (2.4)
| ITEM | PRICE*QUANTITY | CUSTOMER_ADDRESS |
| Watch | 199.99 | Green str. 2 |
| DVD Disk | 12.7 | Docks |
| Book | 12.5 | Pall Mall, 3 |
| Pizza | 16.5 | Pall Mall, 3 |
| izza | 16.5 | Docks |
| Book | Green str. 2 | |
| PS3 | 199.99 | Pall Mall, 3 |
В результирующем множестве запроса в качестве имени колонки используется текст соответствующего выражения из группы SELECT, который в частном случае совпадает с именем поля; во второй колонке выборки предыдущего запроса в качестве ее имени помещен текст выражения price*quantity. Для каждого выражения можно указать новое имя (записываемое в двойных кавычках), которое получит соответствующая колонка, например:
SELECT item AS "Товар", price*quantity AS “Общая стоимость”,
customer_address AS "Клиент" FROM acceptedorders; (2.5)
| Товар | Общая стоимость | Клиент |
| Watch | 199.99 | Green str. 2 |
| DVD Disk | 12.7 | Docks |
| Book | 12.5 | Pall Mall, 3 |
| Pizza | 16.5 | Pall Mall, 3 |
| Pizza | 16.5 | Docks |
| Book | Green str. 2 | |
| PS3 | 199.99 | Pall Mall, 3 |
Служебное слово AS является необязательными и может опускаться. Переименование колонок выборки является полезным, например, для того чтобы присвоить колонке имя на русском языке.
Для того чтобы определить количество записей в таблице, можно использовать функцию COUNT():
SELECT COUNT(*) FROM acceptedorders; (2.6)
| COUNT(*) |
Рассмотрим следующий запрос:
SELECT customer_name FROM acceptedorders; (2.7)
| CUSTOMER_NAME |
| Mr.Pundleberry |
| Ms.Magpie |
| Ms.Magpie |
| Ms.Magpie |
| Mr.O'Raily |
| Mr.Pundleberry |
| Mr.O'Raily |
В выборке каждое имя повторяется столько раз, сколько существует таких строк в исходной таблице. Для удаления дубликатов из результирующего множества используется конструкция DISTINCT, применение которой иллюстрируется в следующем примере:
SELECT DISTINCT customer_name FROM acceptedorders; (2.8)
| CUSTOMER_NAME |
| Mr.Pundleberry |
| Mr.O'Raily |
| Ms.Magpie |
Для селекции (отбора) строк исходной таблицы по различным условиям используется конструкция WHERE, после которой специфицируется (указывается) логическое выражение. Например, следующий запрос помещает в результирующее множество только записи с ценой товара более 100 за единицу:
SELECT item, price, customer_address FROM acceptedorders
WHERE price > 100; (2.9)
| ITEM | PRICE | CUSTOMER_ADDRESS |
| Watch | 199.99 | Green str. 2 |
| PS3 | Pall Mall, 3 |
Подробнее логические выражения рассматриваются в следующем подразделе.
Следует иметь в виду, что порядок записей в результирующем множестве запроса будет в общем случае совершенно отличным от порядка записей в исходной таблице (точнее говоря, от порядка добавления записей в таблицу). Для явного упорядочивания предназначена конструкция ORDER BY, после которой указывается список выражений (например, имен полей), по которым происходит упорядочивание результирующего множества.
|
|
|
SELECT customer_name, item FROM acceptedorders
ORDER BY customer_name; (2.10)
| CUSTOMER_NAME | ITEM |
| Mr.O'Raily | Pizza |
| Mr.O'Raily | DVD Disk |
| Mr.Pundleberry | Book |
| Mr.Pundleberry | Watch |
| Ms.Magpie | Pizza |
| Ms.Magpie | Book |
| Ms.Magpie | PS3 |
SELECT customer_name, item FROM acceptedorders
ORDER BY customer_name, item; (2.11)
| CUSTOMER_NAME | ITEM |
| Mr.O'Raily | DVD Disk |
| Mr.O'Raily | Pizza |
| Mr.Pundleberry | Book |
| Mr.Pundleberry | Watch |
| Ms.Magpie | Book |
| Ms.Magpie | PS3 |
| Ms.Magpie | Pizza |
Теперь более точно объясним, что такое значение SQL-запроса. Значение запроса можно определить с двух точек зрения – с формальной и с неформальной. Формальным значением запроса всегда является таблица (возможно пустая), структура и содержание которой определяется в соответствии с семантикой языка SQL. Неформальное значение описывает смысл запроса с точки зрения предметной области. Сформулируем правило вычисления формального значения SQL-запросов при извлечении данных из одной таблицы.
1. Выполнение проверки каждой строки исходной таблицы на соответствие логическому условию конструкции WHERE, если оно указано.
2. Вычисление всех выражений, перечисленных после слова SELECT для каждой строки таблицы, оставшейся после проверки логического условия, и формирование результирующего множества, в котором будет столько же строк, сколько осталось после проверки логических условий, и столько колонок, сколько выражений перечислено после SELECT.
3. Удаление дубликатов из результирующего множества, если используется DISTINCT.
4. Упорядочивание строк в результирующем множества в соответствии с выражениями конструкции ORDER BY.
Рассмотрим два примера.
SELECT 'Покупатель ', customer_name, ' из ', customer_address,
' заказал ', item FROM acceptedorders; (2.12)
| 'ПОКУПАТЕЛЬ' | CUSTOMER_NAME | 'ИЗ' | CUSTOMER_ADDRESS | 'ЗАКАЗАЛ' | ITEM |
| Покупатель | Mr.Pundleberry | из | Green str. 2 | заказал | Watch |
| Покупатель | Ms.Magpie | из | Pall Mall, 3 | заказал | Book |
| Покупатель | Ms.Magpie | из | Pall Mall, 3 | заказал | Pizza |
| Покупатель | Ms.Magpie | из | Pall Mall, 3 | заказал | PS3 |
| Покупатель | Mr.O'Raily | из | Docks | заказал | Pizza |
| Покупатель | Mr.Pundleberry | из | Green str. 2 | заказал | Book |
| Покупатель | Mr.O'Raily | из | Docks | заказал | DVD Disk |
SELECT 'Покупатель ' || customer_name || ' из ' || customer_address || ' заказал ' || item FROM acceptedorders; (2.13)
| 'ПОКУПАТЕЛЬ'||CUSTOMER_NAME||'ИЗ'||CUSTOMER_ADDRESS||'ЗАКАЗАЛ'||ITEM |
| Покупатель Mr.Pundleberry из Green str. 2 заказал Watch |
| Покупатель Ms.Magpie из Pall Mall, 3 заказал Book |
| Покупатель Ms.Magpie из Pall Mall, 3 заказал Pizza |
| Покупатель Ms.Magpie из Pall Mall, 3 заказал PS3 |
| Покупатель Mr.O'Raily из Docks заказал Pizza |
| Покупатель Mr.Pundleberry из Green str. 2 заказал Book |
| Покупатель Mr.O'Raily из Docks заказал DVD Disk |
Несмотря на внешнюю схожесть, результирующее множество запроса 2.12 состоит из шести колонок, тогда как запроса 2.13 – из одной.
|
|
|
