Язык SQL. Оператор выбора SELECT (вопрос 14). Предикат Null
Предикат NULL. Иногда записи в таблице имеют значения не для каждого поля, т.к. ввод инф-и не завершен или т.к. это поле просто не заполнялось. SQL учитывает такой вариант с помощью Null-значений (пустых знач-й). Когда знач-е поля равно Null, это означ-т, что оно отмечено как не имеющее никакого значения. Это отличается от назначения полю нуля или пробела, которые БД будет обрабатывать также как и др люб. знач-е. Знач-е Null не имеет типа и может помещаться в поле люб. типа. При сравнении неопред. знач-й не действуют обычн правила сравнения, т.е. одно неопред знач-е не считается равным другому неопред знач-ю. для выявления отсутствующих знач-й в столбце применяют спец предикат:
<имя столбца> Is [Not] Null.
Если указанный столбец имеет неопр знач-е, то предикат Is Null принимает знач-е Истина, а Is Not Null – Ложь, и наоборот.
Пример:
1) выделить поставщиков, кот. назначен статус
Select *
From P
Where Status Is Not Null
Или
Where Not Status Is Null
2) выделить поставщиков, кот еще не назначен статус
Select *
From P
Where Status Is Like Null
18. Язык SQL. Применение итоговых функций в операторе выбора (+ примеры)
В SQL добавлены дополнит функции, называемые итоговыми, кот позволяют вычислить общие знач-я для группы строк.
Для выполнения группировки использ-ся раздел Group by, в кот задается список столбцов для группировки. При этом все множество строк таблицы разбив-ся на группы строк, имеющие одинаковые знач-я атрибутов, заданное в списке группировки. Полученная в рез-те таблица наз-ся сгруппированной.
Список итоговых функций:
1) Count – кол-во строк или непустых знач-й полей, кот выбрал запрос
2) Sum – сумма всех выбранных знач-й дан. поля
3) Avg – средн арифм всех выбранных знач-й дан. поля.
4) Min – наименьш из всех выбранных знач-й дан. поля.
5) Max – наибольш из всех выбранных знач-й дан. поля.
Итоговые функции указыв-ся в разделе Select и использ-т имена столбцов в кач-ве аргументов.
С функциями Sum и Аvg могут использ-ся только числов поля, а с функциями Min, Max, Count могут использ-ся как числов, так и символьн поля.
При использовании с символьными полями функции Min, Max работают с их кодовыми знач-ми и обрабат-ют их в алфавитн порядке.
Синтаксис вызова итоговых функций:
Count (*) |
{Avg | Max | Min | Sum | Count}
(Distinct <столбец>)
{Avg | Max | Min | Sum }
(Distinct <столбец>)
([All] <значение выражения>)
При вычислении всех итоговых функций кроме Count (*) сначала на основании аргументов функции из заданного множ-ва строк получ-ся список знач-й, затем по этому списку производ-ся вычисление функции. Если список оказ-ся пустым, то знач-е функции Count для него равно нулю, а значения всех остальных функций – Null. Count (*) означает подсчет всех строк в группе, при этом Null-значения обрабат-ся также, как обычные знач-я.
Если исп-ся ключ слово distinct, то из полученного списка знач-й удаляются Null-значения и дубликаты, а затем вычисл-ся знач-е функции. В этом случае не допуск-ся вычисл-е арифметич выражений.
Если итог. функция использ-ся без ключ слова Distinct (или с ключ словом All), то список знач-й формируется из знач-й вычисл-ого выражения для каждой строки. Далее из списка удаляются Null-значения и произв-ся вычисление итогов функции. В этом случае не допуск-ся применение функции Count.
Примеры:
1) вычислить кол-во студ., сдававших экзамен по каждой дисциплине.
Select Discipl As Дисципл, Count (*) As Колич
From R1
Group by Discipl П1.
Можно применять итоговые функции также без операций предварит группировки. В данном случае все таблицы – одна группа строк.
2) вычислить кол-во успешно сданных экзаменов по всем дисциплинам:
Select Count (*) As Колич
From R1
Where Mark > 3
В рез-т можно включить знач-я столбца группировки и несколько итоговых функций, а в условии группировки можно использовать неск. столбцов.
Операции с итоговыми функциями м.б. применены к объединению множества таблиц.
3) определить для кажд группы и кажд дисциплины кол-во успешно сдавших экзамен и ср балл по дисциплине.
Select Gr, Discipl, Count(*), Avg (Mark)
From R1, R2
Where R1.FIO = R2.FIO and Mark >3
Group by Gr, Discipl П2.
Итоговые функции нельзя исп-ть в разделе Where, т.к. там указ-ся условия отбора отд строк, а итоговые функции работ-т с группами строк. Если требуется отобрать строки результирующей таблицы с учетом вычисленных итогов, то такие условия отбора записываются в разделе Having.
Раздел Having проявляется в запросе, как правило, с разделом Group by. Однако формально он может присутствовать и без раздела Group by. В этом случае предполагается, что рез-т вычисления предыдущих разделов запроса – сгруппированная таблица, состоящая из 1 группы. Условия в разделе Having строятся по тем же правилам, что и в разделе Wherе. Однако имеется спец ограничение. В условиях выборки в разделе Having можно прямо использовать только имена столбцов, указанных в разделе Group By. Остальные столбцы можно использовать только в кач-ве аргументов итог-вых функций, кот вычисляют итог для группы строк.
4) определить группы и дисциплины, в кот на экзаменах получено больше 1 двойки.
Select Gr, Discipl
From R1, R2
Where R1.FIO = R2.FIO and Mark <=3
Group By Gr, Discipl
Having Count (*)>1Результ: Gr Discipl
Или Having Avg (Mark)>6 111 Экономика
5) выбрать номера деталей, поставленных более чем одним поставщиком
Select Num_D
From PD
Group By Num_D
Having Count (Distinct Num_P)>1
В разделе Order By задается список полей для упорядочивания рез-та, т.е. порядок сортировки строк в результирующей табл.
Синтаксис оператора:
Order By {<беззнаковое целое> | <cтолбец>} [Asc | Desc]
Здесь для кажд столбца указ-ся порядок просмотра строк результирующей табл в завис-ти от знач-й указанного столбца.
Asc – сортировка по возраст (по умолчанию)
Desc – сортировка по убыванию
Столбцы могут задавать по именам только тогда, когда:
1. Выражения запроса не содержит ключ слов Union или Union All
2. В списке отбора оператора Select столбцы заданы своими именами, а не выражениями
Во всех ост случаях в разделе Order By должен указыв-ся порядков номер столбца в результирующ табл.
Order By может также использ-ся с разделами Group By для сортировки групп, и он располаг-ся после раздела Group By.
6) вывести инф-ю о текущ успев-ти на сессии, отсортировать список по группам, по дисципл, по фамилии
Select Gr As Группа, Discipl As Дисципл, R1.FIO As ФИО, Mark As Оценка
From R1, R2
Where R1.FIO=R2.FIO
Order By Gr, Discipl, R1.FIO П3.
7) вычислить кол-во студентов, сдававших экзамен по кажд дисципл и выполнить сортировку по кол-ву:
Select Discipl As Дисципл, Count(*) As Колич
From R1
Group By Discipl
Order By 2 П4.