Лекции SQL. Лекции по sql учебное пособие для студентов

• Название: Лекции по sql учебное пособие для студентов
• Анкор: Лекции SQL
• Дата: 16.10.2019
• Размер: 0.84 Mb.
• Имя файла: Lekcii_po_SQL_CHupinNA.pdf
• Тип: Лекции; #90508
• страница: 3 из 6

!
Пример 1.19. Выражение для текстовых полей в СУБД InterBase
(FireBird).
SELECT fam||’ ‘||im||’ ‘||ot FROM spisok
Этот запроса выдает в качестве выходных данных записи, которые состоят из объединенных в единое целое фамилии, имени, отчества с пробелами- разделителями. Именно два знака | указывают на соединение строковых значений, что достаточно нетрадиционно.
1.11.Упорядочение выходных данных.
ORDER BY.
?
Представим себе, что нам нужно иметь список студентов- первокурсников, отсортированный определенным образом, например, по фамилии, а в случае совпадения фамилий – по имени.
!
Пример 1.20
SELECT fam, im, ot, kod_fakulteta, kurs
FROM spisok
WHERE kurs=1
ORDER BY fam,im
√ Итак, предложение ORDER BY определяет порядок сортировки выходных данных.
√ Если после имени поля записать служебное слово DESCENDING или сокращенный вариант DESC, то порядок сортировки по этому полю будет обратным.
!
Пример 1.21
SELECT kurs, fam, im, ot
FROM spisok
ORDER BY kurs DESC, fam, im, ot
Выходные данные будут отсортированы в первую очередь по курсу, начиная со старшего, то есть по убыванию, а внутри курса по фамилии, имени, отчеству сортировка будет обычным образом.

— 23 —
√ Вообще-то есть и служебное слово ASCENDING, или, сокращенно, ASC, обозначающее сортировку в прямом порядке, но его не используют за ненадобностью – если в ORDER BY не указывать перед названием поля ничего, будет как раз прямой порядок сортировки по данному полю.
√ Сортировать можно и по тому полю, которое не перечислено в SELECT !
√ После ORDER BY можно указывать не имена полей, а их номера
Пример 1.22
SELECT kod_fakulteta, fam, im, ot
FROM spisok
WHERE kurs=1
ORDER BY 2,3
Выходные данные будут отсортированы по фамилии и по имени. Нумерация начинается с 1, то есть поле, которое записано сразу после SELECT имеет номер 1.
В простых запросах это вряд ли полезно – явное указание имен полей делает текст запроса более понятным, однако позже мы увидим пример, когда этому варианту ORDER BY нет альтернативы.
√ Раздел ORDER BY следует последним в тексте запроса.
√ Современные СУБД в серверной части имеют встроенный оптимизатор запросов. Он определяет наиболее эффективный порядок перебора записей при выполнении запроса. В частности, если для таблицы имеется подходящий индекс, то оптимизатор запроса будет его использовать.
√ Если поле сортировки имеет значение NULL, то такие записи будут располагаться либо в начале результирующих данных, либо, наоборот, в конце. Стандарт SQL это не регламентирует, и все зависит от конкретной
СУБД.
1.12. Итоги по первой главе.
К данному моменту мы освоили синтаксис SQL в следующих пределах:
SELECT {*|[DISTINCT]<список полей>}
FROM <имя таблицы>
[ WHERE <условие выбора> ]
[ ORDER BY <порядок сортировки> ]
Знак | означает, что либо записывается * (что означает выбрать все поля из таблицы), либо перечисляются нужные поля.

— 24 —
Квадратные скобки указывают элементы, которые могут отсутствовать.
В дальнейшем мы изучим более сложные варианты SQL-запросов.
2. Агрегатные функции
2.0. Введение.
Агрегатные функции обеспечивают подведение итогов по многим записям таблиц.
2.1. Использование агрегатных функций.
?
Требуется подсчитать итоговые значения, например, общее количество студентов.
!
Пример 2.1
SELECT COUNT( id )
FROM spisok
В качестве выходных данных выдает только одну строку, в которой записано общее количество записей в таблице spisok. Исключением является только случай, когда в таблице spisok вообще нет записей. В этом случае и этот запрос также не выдаст ни одной строки (зависит от СУБД, некоторые все- таки возвращают запись, в которой записано 0) .
√ Агрегатные функции дают обобщенные значения для целой группы записей.
√ Список агрегатных функций
Функция
Назначение
COUNT
Вычисляет количество значений данного поля, выбранных посредством запроса и не являющихся NULL-значениями
SUM
Вычисляет арифметическую сумму всех выбранных значений данного поля
AVG
Вычисляет среднее арифметическое всех выбранных значений данного поля. (англ. AVERAGE – среднее). В запросах можно записывать и полностью AVERAGE.

— 25 —
MAX
Вычисляет максимальное среди всех выбранных значений данного поля
MIN
Вычисляет минимальное среди всех выбранных значений данного поля
Примечание. Слова «данного поля» означают, что речь идет о поле, записанном в скобках после имени агрегатной функции.
Примечание. В Interbase имеется одна неприятная особенность у функции
AVG. Если ее применить к полю целочисленного типа, то результат тоже будет целочисленным, и поэтому почти всегда неправильным! Для устранения этого следует использовать малоизвестную возможность приведения поля к нужному типу. Прочтите об этом в параграфе 2.5 !
2.2. Название поля агрегатной функции.
?
Как будет названо поле, в котором выводится ответ агрегатной функции?
!
Это зависит от конкретной СУБД. Некоторые СУБД не называют его никак, оставляя заголовок в ответе пустым, другие СУБД используют свою оригинальную систему именования таких полей, например, count_shortname или count_for_shortname. Однако при записи запроса можно и даже полезно применить способ переименования полей, упомянутый в 1.9.
Пример 2.2
SELECT COUNT( shortname ) AS vsego_fakultetov
FROM fakultet
Теперь поле агрегатной функции получит в выходных данных имя
(заголовок) vsego_fakultetov.
2.3. Использование * в COUNT.
!
Пример 2.3
SELECT COUNT( * ) FROM spisok
В функции COUNT в скобках можно ставить не имя поля, а символ
«звездочка». В этом случае подсчитываются все строки, в том числе и те, у которых в отдельных полях могут быть значения NULL, которые в обычном случае пропускаются. Поэтому указанный вариант лучше, чем SELECT
COUNT( ot ) FROM spisok – вдруг поле ОТЧЕСТВО еще не заполнено у некоторых студентов! Конечно, если есть поле, которое должно иметь

— 26 — ненулевое значение, то COUNT по такому полю даст тот же результат, что и
COUNT(*), но COUNT(*) предпочтительнее, так как не дает повода читателю программного кода для какой-либо двусмысленности.
2.4. Использование DISTINCT в агрегатных
функциях.
!
Пример 2.4
SELECT COUNT( DISTINCT kod_fakulteta)
FROM spisok
Такой вариант позволяет подсчитать только неповторяющиеся значения.
Поэтому результат будет показывать количество различных факультетов, студенты с которых фигурируют в таблице spisok. Результат может не совпадать с результатом запроса
SELECT COUNT( shortname ) FROM fakultet
√ Теоретически DISTINCT можно записывать и в других агрегатных функциях, но такие варианты вряд ли будут полезны. Для MAX или MIN это нисколько не изменит результат, а для AVG результату трудно придать смысловую интерпретацию.
2.5. Использование выражений в агрегатных
функциях.
!
Пример 2.5
SELECT SUM( pole1*pole2 )
FROM spisok
В агрегатных функциях можно использовать не только одно поле, но и выражение из полей. (Этот пример абстрактный – просто предполагается, что в таблице такие числовые поля имеются)
?
В Interbase(FireBird) имеется одна неприятная особенность у функции
AVG. Если ее применить к полю целочисленного типа, то результат тоже будет целочисленным, и поэтому почти всегда неправильным! Например, средняя оценка будет ровно 3,4 или 5, среднее число детей в семье – 1,2 или 3, хотя тому, от кого исходит запрос, скорее всего, важна именно дробная часть соответствующей величины.

— 27 —
!
Для устранения этого следует использовать малоизвестную для начинающих возможность приведения поля к нужному типу.
Пример 2.6
Представим себе, что в таблице spisok имеется поле biblioteka, в котором хранится информация, сколько библиотечных книг использовал студент за учебный год. Требуется определить, сколько в среднем книг за год приходится на одного студента
SELECT AVG( CAST(biblioteka AS DOUBLE PRECISION))
FROM spisok
√ Формат функции CAST следующий CAST(<выражение> AS <тип>).
Выражение чаще всего есть название поля, значение которого следует преобразовать в нужный тип, но иногда используются и «настоящие» выражения. <Тип> - один из возможных типов Interbase, к которому следует привести выражение. В примере выше вместо DOUBLE PRECISION можно было бы использовать любой вещественный тип.
√ Типичная ошибка начинающих – переставить местами AVG и CAST.
Результат будет неверным!
2.6. Предложение GROUP BY
?
Требуется определить количество студентов на каждом факультете.
!
Пример 2.7
SELECT kod_fakulteta, COUNT( id )
FROM spisok
GROUP BY kod_fakulteta
Выдаст примерно такой результат (в правом столбце получим количество студентов на каждом факультете) kod_fakulteta
ЕГФ
2
ФМФ
14
√ Данный пример ясно показывает действие GROUP BY. В данном случае
COUNT будет подсчитывать все записи в группе строк, у которых поле kod_fakulteta имеет значение ЕГФ, а затем и для других аналогичных групп записей.

— 28 —
√ Предложение GROUP BY используется вместе с агрегатными функциями.
Оно позволяет определять группу записей, для которой применяется агрегатная функция.
!
GROUP BY может производить группировку и по нескольким полям.
Пример 2.8
SELECT kod_fakulteta, kurs, COUNT( id )
FROM spisok
GROUP BY kod_fakulteta, kurs
Запрос будет подсчитывать количество студентов на каждом факультете на каждом курсе. Пустые группы в выходных данных не будут представлены.
Побочным эффектом использования GROUP BY является то, что выходные данные будут отсортированы по тем полям, которые перечислены после
GROUP BY.
√ Внимание. После GROUP BY должны быть обязательно перечислены все поля, которые фигурируют в SELECT, кроме агрегатной функции. При этом их можно перечислить не в том порядке, в каком они записаны после
SELECT, например, можно записать
Пример 2.9
SELECT kurs, kod_fakulteta, COUNT( id )
FROM spisok
GROUP BY kod_fakulteta, kurs
2.7. Использование WHERE в запросах с
агрегатными функциями
?
Требуется определить количество первокурсников на каждом факультете.
!
Пример 2.10
SELECT kod_fakulteta, COUNT( id )
FROM spisok
WHERE kurs=1
GROUP BY kod_fakulteta
√ Для правильного использования WHERE в агрегатных функциях следует разобраться с использованием HAVING (см.следующий параграф)

— 29 —
2.8. Предложение HAVING
?
Требуется выбрать количество студентов только для тех факультетов, где количество студентов более 200.
Запрос
SELECT kod_fakulteta, COUNT( id )
FROM spisok
WHERE COUNT( * )>200
GROUP BY kod_fakulteta
будет неверным по синтаксису, то есть не будет выполняться. Дело в том, что в WHERE предикат должен оцениваться в терминах единственной строки
(кроме случая использования подзапросов, который будет рассмотрен позже), а функция COUNT подсчитывается для группы строк.
!
Правильный вариант запроса.
Пример 2.11
SELECT kod_fakulteta, COUNT( id )
FROM spisok
GROUP BY kod_fakulteta
HAVING COUNT( * )>200
√ Предложение HAVING используется с агрегатными функциями и предназначается для формулировки тех условий, которые формулируются в терминах групп записей.
√ Условие в HAVING должно быть сформулировано так, чтобы внутри группы оно должно иметь только одно значение. Поэтому запрос
SELECT kod_fakulteta, COUNT( * )
FROM spisok
GROUP BY kod_fakulteta
HAVING kurs>1
будет синтаксически неверным.
Правильным вариантом будет
SELECT kod_fakulteta, COUNT( id )
FROM spisok
WHERE kurs>1
GROUP BY kod_fakulteta

— 30 —
!
Пример 2.12
SELECT kod_fakulteta, COUNT( id )
FROM spisok
GROUP BY kod_fakulteta
HAVING kod_fakulteta IN (’ЕГФ’,’ФМФ’)
Запрос будет синтаксически правильным и определять количество студентов на каждом из этих двух факультетов.
3. Запросы к нескольким таблицам
В этой главе рассматриваются запросы, в каждом из которых участвуют две или более таблиц.
√ Обычно соединение двух таблиц производят по тем полям, которые используются в ссылочной целостности FOREIGN KEY, такое соединение называется естественным.
√ В SQL существует два вида соединения таблиц: эквисоединения и JOIN- соединения, хотя часто для этих двух видов соединений используются другие названия.
3.1. Эквисоединения
?
Нужно получить выходные данные в следующем виде: фамилия, имя студента, полное название факультета.
√ Очевидно, что нужная информация находится в двух таблицах, поэтому запрос должен обращаться также к двум таблицам, причем таблицы должны быть согласованы, иначе говоря, связаны друг с другом.
!
Пример 3.1. Эквисоеднинения.
SELECT spisok.fam, spisok.im, fakultets.fullname
FROM spisok, fakultets
WHERE spisok.kod_fakulteta=fakultets.shortname
√ Эквисоединения используют оператор WHERE и предикат равенства.
√ Обратите внимание на то, что в SELECT и WHERE поле слева предварено указанием таблицы, из которой оно взято. Особенно это полезно, если в двух таблицах некоторые поля совпадают по названию. Часто для этого используют так называемые алиасы. Объясним их на примере.

— 31 —
!
Пример 3.2. Использование алиасов
SELECT s.fam, s.im, f.fullname
FROM spisok s, fakultets f
WHERE s.kod_fakulteta=f.shortname
Здесь использованы алиасы s и f. В данном случае выигрыш от их использования вместо имен таблиц состоит в более краткой записи. Однако в дальнейшем мы увидим примеры запросов, когда без алиасов не обойтись.
√ Пример 3.3. Связь таблиц в WHERE не препятствует использованию в этом же WHERE и обычных условий.
SELECT spisok.fam, spisok.im, fakultets.fullname
FROM spisok, fakultets
WHERE spisok.kod_fakulteta=fakultets.shortname AND spisok.kurs=2
√ В одном запросе можно соединять также и более двух таблиц.
3.2. Соединение таблицы со своей копией
?
Требуется выбрать из списка студентов однофамильцев
!
Пример 3.4. Соединения таблицы со своей копией.
SELECT s1.fam, s1.im, s1.kod_fakulteta, s1.id
FROM spisok s1, spisok s2
WHERE s1.fam=s2.fam AND s1.id<>s2.id
ORDER BY s1.fam, s1.im, s1.kod_fakulteta
√ Это тот случай, когда без алиасов не обойтись, ведь названия таблиц совпадают, алиасы как раз позволяют пояснить о какой из двух копий таблицы идет речь при упоминании полей.
√ У вышеуказанного примера есть недостаток – строки будут многократно повторяться.
3.3. JOIN-соединения
?
Нужно получить выходные данные в следующем виде: фамилия, имя студента, полное название факультета.
!
Пример 3.5. Запрос с JOIN-соединением.
SELECT s.fam, s.im, f.fullname

— 32 —
FROM spisok s
INNER JOIN fakultet f ON s.kod_fakulteta=f.shortname
√ Левой таблицей в соединении называется та, которая указана перед
(слева) ключевым словом JOIN, а правой таблицей та, которая указана после
(справа) от него.
JOIN-соединения бывают внутренним (INNER) и внешним (OUTER). Тип соединения задается ключевым словом INNER или OUTER; если ни одно из них не указано, то принимается INNER.
Тип соединения INNER задает "внутреннее соединение", когда в выводимых данных используется только те случаи, когда и в левой и в правой таблицах найдутся записи, подходящие для условия связи.
Если указано одно из слов (LEFT, RIGHT, FULL), то соединение является внешним. При этом может быть добавлено ключевое слово OUTER
(внешний), которое, впрочем, не является обязательным, и только делает текст запроса более «читаемым».
LEFT (OUTER) - тип соединения "левое (внешнее)". Левое соединение таблиц включает в себя все строки из левой таблицы и те строки из правой таблицы, для которых выполняется условие соединения. Для строк из левой таблицы, для которых не найдено соответствия в правой таблице, в столбцы, извлекаемые из правой таблицы, заносятся значения NULL.
RIGHT (OUTER) - тип соединения "правое (внешнее)". Правое соединение таблиц включает в себя все строки из правой таблицы и те строки из левой таблицы, для которых выполняется условие соединения. Для строк из правой таблицы, для которых не найдено соответствия в левой таблице, в столбцы, извлекаемые из левой таблицы, заносятся значения NULL.
FULL (OUTER) - тип соединения "полное (внешнее)". Это комбинация левого и правого соединений. В полное соединение включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений.
Использование JOIN-соединения в запросах часто облегчает восприятие оператора SELECT, особенно, когда используется естественное соединение.
Дело в том, что в запросах с эквисоединениями в разделе WHERE смешиваются такие семантически различные понятия, как условия связи таблиц и условия отбора строк. Каких-либо иных дополнительных преимуществ конструкция JOIN перед конструкцией WHERE не имеет.

— 33 —