Основным оператором языка DML является оператор select — извлечение данных из таблиц. Результатом выполнения оператора select всегда является таблица. В простейшем виде оператор имеет такой формат:
select *
from <имя таблицы>
Например,
select * (1)
from student
Данный оператор извлекает все строки из указанной таблице, в примере - из таблицы student. Столбцы будут следовать в том же порядке, что и в соответствующей таблице. В указанном примере сначала будет следовать фамилия, затем имя, отчество и, наконец, номер студента и номер группы.
Пример 7 При рассмотрении примеров мы будем считать что в таблице student содержатся следующие сведения:
| 1_ пате | f_ name | m_ пате | потеr | gr_ nomer |
| Иванов | Иван | Иванович | 010001 | 11 |
| Иванов | Александр | Петрович | 010002 | 11 |
| Федоров | Николай | Андреевич | 011005 | 12 |
| Сидоров | Павел | Алексеевич | 011003 | 12 |
| Сорокин | Михаил | Анатольевич | 011004 | 12 |
| Сорокин | Николай | Сергеевич | 011101 | 13 |
В результате выполнения оператора (1) эта таблица и получится.
Чаще всего нужна лишь информация из нескольких столбцов таблицы. Чтобы ограничить извлечение информации только необходимыми столбцами используется такой синтаксис:
select <список столбцов>
from <имя таблицы>
Пример 8 Например, если мы хотим извлечь только номера и фамилии студентов, то следует написать:
select nomer, l_nate
from student
В данном случае, столбцы в извлекаемых строках будут, следовать в том же порядке, в каком они перечислены в операторе select. To есть получим такой результат:
| потеr | 1_ пате |
| 011004 | Сорокин |
| 010001 | Иванов |
| 011005 | Федоров |
| 011001 | Сорокин |
| 010002 | Иванов |
| 011003 | Сидоров |
Столбцы в извлекаемых строках можно переименовать. Для этого после имени столбца следует написать as и новое имя.
Пример 9 Например, оператор
select nomer, l_name as last_name
from student
извлекает из таблицы student me же данные, но столбец фамилий будет иметь имя last_name:
| nomer | last_name name |
| 011004 | Сорокин |
| 010001 | Иванов |
| 011005 | Федоров |
| 011001 | Сорокин |
| 010002 | Иванов |
| 011003 | Сидоров |
Кроме имен столбцов в списке после select можно писать произвольные выражения.
Пример 10 Например,
select 'студент ' + f_name + l_name as full_name from student
выдаст таблицу
| full_ name |
| студент Иван Иванов |
| студент Александр Иванов |
| студент Павел Сидоров |
| студент Михаил Сорокин |
| студент Николай Федоров |
| студент Николай Сорокин |
Столбцы выражений не имеют имен, поэтому обычно имена им требуется присваивать при помощи as.
Задача 8 (Университет) Напишите SQL-запрос, который извлекает полное имя преподавателя, с указанием должности и степени (см. задачу 3), например,
| доцент кандидат физ.мат. наук Дудаков Сергей Михайлович |
| профессор доктор физ.мат. наук Климок Виктор Иванович |
Как мы уже говорили, порядок строк в таблицах не определён. Следовательно, приведенные выше операторы select выдадут нам информацию в случайном порядке, что и продемонстрировано в предыдущем примере (порядок в каждом случае будет определяться внутренними особенностями реализации). Чтобы в точности указать порядок следования извлеченных строк используется инструкция order by:
Select...
From...
order by <способ сортировки>
Способ сортировки представляет собой список полей (или выражений), разделенных запятыми после каждого из которых может стоять одно из слов: ASC - сортировка по данному полю осуществляется по возрастанию, DESC - по убыванию. Если после имени поля не стоит ни одно из этих слов, то подразумевается сортировка по возрастанию. Сортировка строк осуществляется сначала по первому полю из списка, если две строки имеют равные значения первого поля, то - по второму и т.д.
Пример 11 Например, чтобы вывести список номеров и фамилий отсортированный в порядке возрастания номеров, следует записать:
select nomer,
1 _ пате from student
order by nomer
тогда получим,
| nomer | l_ name |
| 010001 | Иванов |
| 010002 | Иванов |
| 011003 | Сидоров |
| 011004 | Сорокин |
| 011005 | Федоров |
| 011101 | Сорокин |
Если хотим вывести фамилии, имена и отчества студентов, в убывающем порядке:
select l _ name, f _ name, m _ name
from student
order by l _ name desc, f _ name desc, m _ name desc
Задача 9 (Банк) Выведите список номеров счетов, суммы на них и даты открытия
1. в порядке возрастания номеров счетов;
2. в порядке убывания сумм наших, а в случае одинаковых сумм - в порядке возрастания номеров.
Задача 10 (CD) Выведите список компакт-дисков (см. задачу 5) в порядке возрастания фирмы производителя, а в случае одинаковой фирмы в порядке возрастания названий.
Следующий шаг - выборка не всех строк, а только нескольких. Простейший случай - когда нужно исключить повторяющиеся строки. В таблице с первичным ключом таких строк, конечно, нет, но если из таблицы выбираются не все столбцы, а только их часть, то они могут появиться.
Пример 12 Например, в результате выполнения оператора
select l name
from student
появятся несколько строк с фамилиями 'Иванов' и 'Сорокин'
| l_ пате |
| Иванов |
| Иванов |
| Сидоров |
| Федоров |
| Сорокин |
| Сорокин |
Строки в исходной таблице student различались, например, из-за наличия поля потег, но так как это поле не выбирается, то строки становятся одинаковыми.
Для исключения повторов в этом и любом другом случае используется слово distinct, которое ставится после select:
select distinct...
from...
[order by...]
Здесь и далее в квадратных скобках мы будем писать необязательные конструкции.
Пример 13 В нашем случае, чтобы, просто вывести список различных фамилий, упорядочив его по возрастанию, следует, написать:
select distinct l _ name
from student
order by l _ name
и получим
| l_ name |
| Иванов |
| Сидоров |
| Cорокин |
| Федоров |
Задача 11 (Университет) Выведите список групп, без повторений, упорядочив его по возрастанию номера группы.
Задача 12 (Супермаркет) Выведите список фирм, товары которых имеются в продаже, без повторений.
Задача 13 (CD) Выведите список названий фирм-производителей компакт-дисков без повторений.
Задача 14 (Библиотека) Выведите список авторов, книги которых имеются в библиотеке, без повторений.
Если мы хотим наложить более сложное условие на извлекаемые строки, понадобиться инструкция where:
select...
from...
where <условие>
[order by... ]
Условие может принимать одно из трех значений: TRUE (истина), FALSE (ложь) и UNKNOWN (неизвестно). В результате выполнения такого оператора выбираются в точности те строки, для которых условие истинно, строки для которых условие ложно или неизвестно отбрасываются.
Условие в простейшем случае представляет собой сравнения полей с константами и другими полями. Простейшие условия можно соединять логическими операциями and, or и not по правилам трехзначной логики:
| and | T | U | F | or | T | U | F | not | |||
| T | T | U | F | T | T | T | T | T | F | ||
| U | U | U | F | U | T | U | U | U | U | ||
| F | F | F | F | F | T | U | F | F | T |
Для некоторых условий (like, in, between) можно ставить not не перед всем условием, а перед одним из указанным слов, что более соответствует естественному языку. Например, вместо
not (a like b)
можно писать
a not like b
Точно так же вместо
not (a in (...))
можно написать
a not in (...)
В качестве сравнений используются равенство (=), неравенство (<>), отношения порядка (<, >, <=, >=). Если одно из сравниваемых значений есть NULL, то результат сравнения — UNKNOWN (это же относится и к любым другим условиям). В противном случае результат всегда является истинным или ложным.
Пример 14 Например, если мы хотим извлечь информация о всех студентах, фамилии которых начинаются на букву 'С’ нужно написать:
select *
from student
where l_name >= 'С and l_name < 'T'
order by nomer
Получится
| I name | f name | т пате | по тег | gr nomer |
| Сидоров | Павел | Алексеевич | 011003 | 12 |
| Сорокин | Михаил | Анатольевич | 011004 | 12 |
| Сорокин | Николай | Сергеевич | 011101 | 13 |
Пример 15 Чтобы получить список студентов 12 группы, следует выполнить:
select *
from student
where gr _ nomer = 12
| Федоров | Николай | Андреевич, | 011005 | 12 |
| Сидоров | Павел | Алексеевич | 011003 | 12 |
| Сорокин | Михаил | Анатольевич | 011004 | 12 |
Пример 16 Если требует список студентов с номерами от, '011000' до '011099', то соответствующий оператор:
select *
from student
where nomer >= '011000' and nomer <= '011099'
order by nomer
Задача 15 (Супермаркет) Выберите список товаров, цена которых превосходит 100.
Задача 16 (Супермаркет) Выберите список товаров, производимых фирмой «Рога и копыта».
Задача 17 (Банк) Выведите список счетов, которые были открыты ранее 2003 года и на которых при этом содержится не менее 1000000.
Задача 18 (Банк) Выведите список счетов, которые были открыты, в 2002 году.
Задача 19 (Университет) Выберите преподавателей, которые занимают должности доцентов или профессоров.
Задача 20 (CD) Выберите без повторений исполнителей, который исполняли партию Фигаро, упорядочите список по возрастанию.
Последнее условие принадлежности значения поля некоторому отрезку значений (в нашем случае это отрезок от '011000' до '011099') можно написать с помощью сравнения between... and:
|<выражение> between <выражение1> and <выраженже2>
Пример 17
select *
from student
where nomer between '011000' and '011099' order
by nomer
Данный оператор делает то же самое, что и в примере 16.
Задача 21 (Банк) С помощью between выберите номера счетов, на которых находится сумма от 10000 до 100000.
Для проверки на равенство одному из нескольких значений используется in:
|<выражение> in (<список выражений>)
Такое условие истинно, если значение выражения равняется значению одного из элементов списка.
Пример 18 Например, если нас интересуют студенты только 11 и 13 групп, то их можно выбрать так:
select *
from student
where gr_nomer in (11, 13)
order by nomer
Задача 22 (CD) Выберите без повторений всех исполнителей, которые пели хотя бы одну из партий: Фигаро, Риголетто, Альфио, Скар-пиа, Дои Жуан.
Задача 23 (Супермаркет) Выберите без повторений всех фирм-производителей таких, что в продаже имеется их продукция, попадающая хотя бы в одну из групп: хлеб, кондитерские изделия, молочные продукты, консервы, соки, соусы.
Для строк предусмотрена проверка соответствия шаблону - like:
|<строка> like <шаблон>
Шаблон тоже является строкой, в которой символы подчеркивания ('_'), процента ('%') и квадратные скобки ('[', ']') интерпретируются особым образом. Строка считается соответствующей шаблону, если ее можно получить из шаблона с помощью следующих действий, выполненных одновременно:
• каждый символ ' _' заменить каким-либо (в точности одним символом),
• каждый символ '%' заменить какой-либо (возможно, пустой) строкой,
• на место каждой строки, заключенной в квадратные скобки поставить один из символов этой строки.
Пример 19 Например, чтобы записать условие, что фамилия начинается с буквы 'С, можно использовать такой синтаксис:
l_name like 'С%,'
Если требуется извлечь информацию о студентах, фамилии которых начинаются на буквы 'С’, 'Т' или 'И', можно выполнить следующий оператор:
select *
from student
where l_name like >[СТИ]%’
Чтобы получить информацию о студентах, у которых третья цифра номера — единица:
select *
from student
where nomer like ’ –1%
Задача 24 (Супермаркет) Выведите список всех товаров, название которых начинается на «Со».
Задача 25 (Супермаркет) Выведите список всех товаров, название которых содержит в себе слово «молочный», а цена находится в диапазоне от, 20 до 50.
Задача 26 (Библиотека) Выберите всех читателей, которых зовут «Николай», и электронный адрес которых заканчивается на «@tversu.ru».
Задача 27 (Банк) Выберите счета, в номере которых присутствует хотя бы одна из цифр: 3, 5, или 9.
Задача 28 (CD) Выберите список всех треков, в названии которых присутствуют слова «amore» и «core» именно в таком порядке и длительность которых превышает 5 минут.
3.2 Группировка данных и агрегатные функции.
Теперь рассмотрим возможности SQL по обработке табличных данных. Один из основных способов анализа данных связан с использованием инструкции group by в операторе select:
select...
from...
[where... ]
group by <список группировке>
[order by...]
Список группировки является списком полей, разделенных запятыми. Семантика оператора group by такая: все строки объединяются в несколько групп таким образом, что каждую группу образуют строки, у которых значения всех столбцов их списка группировки совпадают. Затем из каждой группы формируется одна строка.
Например, если в операторе select присутствует инструкция вида
select l_name...
group by l_name...
то в результате выполнения этого оператора мы получим множество строк, в котором разные строки будут иметь различные значения поля l_name.
Из описанной семантики group by следует, в списке выбираемых полей после select нельзя использовать поля, не входящие в список группировки. В самом деле, если написать
select l_name, f_name
from student
group by l_name
то непонятно, чему должно быть равно значение второго столбца. В таблице есть два Ивановых, и значение f _name может быть равно и 'Иван', и 'Александр'.
Для того, чтобы из каждой группы выбрать одно значение для какого либо поля или сгенерировать новое значение, используются агрегатные функции: max — для выбора максимального значения, min — минимального, avg — среднего арифметического в группе, sum - для нахождения суммы в группе. Последние две функции могут использоваться только для столбцов числового типа. Еще одна агрегатная функция - count (*) - предназначена для подсчета количества строк в группе.
Рассмотрим несколько примеров.
Пример 20
select gr_nomer, count(*) as gr_count
from student
group by gr_nomer
| gr nomer | fr count |
| 11 | 2 |
| 12 | 3 |
| 13 | 1 |
В результате выполнения данного оператора будет, получен набор строк, каждая из которых будет содержать два поля: первое — номер группы, второе — количество студентов в этой группе.
Пример 21
select gr_nomer, min(nomer) as min_nomer,
max(nomer) as max_nomer
from student
group by gr _ nomer
| gr nomer | min nomer | max nomer |
| 11 | 010001 | 010002 |
| 12 | 011003 | 011005 |
| 13 | 011101 | 011101 |
Этот оператор для каждой группы вычисляет минимальный и максимальный номера студенческих билетов студентов из этой группы.
Заметим, что столбцы, формируемые с помощью агрегатных функций, не имеют имени, поэтому обычно им требуется явно присваивать имя при помощи as.
Пример 22
select dis, avg(res) as res
from ball
group by dis
Будем считать, что таблица ball заполнена следующим образом:
| stud nomer | dis | dat | form | res |
| 010001 | Алгебра | 04.01.2004 | Зачет | 100 |
| 010001 | Анализ | 08.01.2004 | Экзамен | 80 |
| 010001 | Информатика | Ц.01.2004 | Экзамен | 75 |
| 010002 | Алгебра | 04-01.2004 | Зачет | 50 |
| 010002 | Анализ | 08.01.20 04 | Экзамен | 10 |
| 010002 | Инфюрматика | Ц.01.2004 | Экзамен | 80 |
| 011003 | Алгебра | 09.01.2004 | Экзамен | 30 |
| 011003 | Анализ | 04.01.2004 | Экзамен | 10 |
| 011003 | Инфюрматика | i4.Ol.2OO4 | Зачет | 90 |
| 01Ю04 | Алгебра | 09.01.2004 | Экзамен | 60 |
| 01Ю04 | Анализ | 04-01.2004 | Экзамен | 60 |
| 01Ю04 | Информатика | Ц.01.2004 | Зачет. | 10 |
| 011005 | Алгебра | 09.01.2004 | Экзамен | 80 |
| 011005 | Анализ | 04.01.2004 | Экзамен | 10 |
| 011005 | Информатика | 14-01.2004 | Зачет | 80 |
Тогда приведенный оператор выдаст
| dis | res |
| Алгебра | 64 |
| Анализ | 10 |
| Информатика | 19 |
Этот, оператор для каждой дисциплины вычисляет средний балл, полученный при ее сдаче. Того же можно добиться, записав:
select dis, sum(res) / count(*) as res
from ball
group by dis
Результат будет, тем, же самым, здесь только явно записан способ вычисления среднего арифметического: вычислить сумму и разделить на количество слагаемых.
Задача 29 (Супермаркет) Найти какое количество различных товаров каждого производителя имеется в продаже.
Задача 30 (Супермаркет) Найти, сколько различных товаров каждого вида имеется в продаже.
Задача 31 (Супермаркет) Найти среднюю цену для каждого вида товаров.
Задача 32 (Супермаркет) Найти общее количество единиц товара каждого вида из имеющихся в магазине.
Задача 33 (Супермаркет) Найти наименьшую и наибольшую цены для каждого вида товара.
Инструкции group by и where можно использовать вместе. В этом случае сначала осуществляется отбор строк при помощи where, а затем - группировка.
Пример 23 Например, если мы хотим получить средний балл по каждой дисциплине, полученный при сдаче экзаменов, то следует, написать:
select dis, avg(res) as res
from ball
where form='Экзамен’
group by dis
При выполнении этого оператора сначала будут отобраны строки, в которых значение поля form равняется ‘Экзамен’, а потом среднее арифметическое будет вычисляться только среди значений поля res в этих строках.
| dis | res |
| Алгебра | 56,6666 |
| Анализ | 70 |
| Информатика | 77,5 |
Если бы по каким-то дисциплинам экзамены вообще не проводились, то они вообще не попали бы в результирующий набор. На этом примере видно, что в инструкции where можно использовать имена тех столбцов, по которым группировка не проводиться (в нашем случае — form). Это как раз и связано с тем, что фильтрация строк в where осуществляется до группировки.
Задача 34 (Супермаркет) Найти среднюю цепу товаров фирмы «Рога и копыта» из имеющихся в продаже.
Задача 35 (Супермаркет) Найти количество каждого товара, имеющего цену от 1000 до 10000.
Агрегатные функции можно использовать, даже если инструкции group by в операторе select нет. В этом случае считается, что все строки образуют одну группу.
Пример 24 Например, оператор
select max(nomer) as max _ nomer
from student
вернет таблицу
| тах_ потеr |
| 011101 |
выбрав максимальный номер студенческого билета из всех, содержащихся в таблице student.
Пример 25 Точно так же оператор
select avg(res) as res
from ball where form = 'Экзамен'
подсчитает среднее количество баллов набираемых студентами в ходе сдачи экзаменов:
| res |
| 67 |
Результатом в обоих примерах является одна строка.
Задача 36 (Супермаркет) Найти среднюю цену товара в магазине.
Задача 37 (Библиотека) Найти общее количество книг в библиотеке.
Задача 38 (Супермаркет) Найти наименьшую и наибольшую цены товара в магазине.
Как мы уже сказали, отбор строк с помощью инструкции where осуществляется до формирования групп. Для того, чтобы отбросить ненужные строки после группировки, используется инструкция having:
select...
from...
[where...]
group by...
having <условие>
[order by... ]
Поскольку having выполняется после формирования групп, то в условии после having можно использовать агрегатные функции, но нельзя использовать столбцы, не вошедшие в список группировки.
Пример 26 Например, чтобы вывести средний балл по дисциплинам,, для которых максимальный балл, полученный когда-либо, равен 100, следует выполнить
select dis, avg(res)
from ball
group by dis
having max(res) = 100
Результат:
| dis | |
| Алгебра | 64 |
Сортировка результата выполняется тоже после группировки, поэтому в списке сортировки можно использовать агрегатные функции, но нельзя столбцы, не участвующие в группировке.
Пример 27 Например, можно вывести номера групп в порядке убывания их численности:
select gr_nomer, count(*) as cnt
from student
group by gr_nomer
order by count(*) DESC
| gr nomer | cnt |
| 12 | 3 |
| 11 | 2 |
| 13 | 1 |
Задача 39 (Супермаркет) Найти среднюю цену каждого из видов товаров, общее количество единиц которого в магазине превосходит 1000. Результат отсортировать по количеству единиц, т,овара по убыванию.
Задача 40 (Супермаркет) Найти максимальную и минимальную
цену каждого из видов товаров, средняя цена которых лежит в диапазоне от 100 до 200. Результат отсортировать по количеству единиц товара по возрастанию.
Задача 41 (Супермаркет) Найти общее количество различных товаров фирм, в названии которых есть слово «молоко», и средняя цена продукции которых не превосходит 10. Результат отсортировать по названию фирмы.
Задача 42 (Супермаркет) Найтм максимальную и минимальную цену товаров каждой фирмы, общее количество единиц товаров которых не, меньше 10000. результат отсортировать по средней цене товаров фирмы.
Соединение таблиц
До сих пор мы использовали оператор select для извлечения данных из одной таблицы.
Для того, чтобы извлечь данные из нескольких таблиц сразу, используется соединение таблиц (не путать с рассматриваемым ниже объединением). Под соединением подразумевается операция, при которой к каждой строке одной таблицы приписываются все строки другой, удовлетворяющие некоторым условиям. Результатом соединения является новая таблица, содержащая все столбцы первой таблицы и все столбцы второй.
Соединения делят на внутренние и внешние. При внутреннем соединении все данные получаемой таблицы берутся из исходных таблиц. При внешних соединениях часть данных новой таблицы не соответствует данным исходных.
Самый простой способ внутреннего соединения декартово произ
ведение исходных таблиц. Декартово произведение записывается двумя
способами: соединяя имена таблиц словами cross join или запятой:
[from <имя таблицы> cross join <имя таблицы>
или
|from <имя таблицы>, <имя таблицы>
Пример 28 Например,
select *
from student cross join ball
или
select *
from student, ball
Оба оператора выдадут один и тот же результат.
Простое декартово произведение используется редко. Гораздо чаще требуется только его часть. В нашем примере, нужно чтобы каждая строка таблицы student соединялась лишь с теми строками таблицы ball, у которых поля содержащие номер студенческого билета совпадают. Этого можно достичь двумя способами. Первый — в один из предыдущих операторов вставить инструкцию where с соответствующим условием:
select...
from <имя таблицы>, <имя таблицы>
where <условие соединения>
Пример 29
select *
from student, ball
where nomer = stud_nomer
Второй способ — использовать соединение при помощи inner join... on:
select...
from <имя таблицы> inner join <имя таблицы>
on <условие соединения>
Пример 30 Для нашего примера:
select *
from student inner join ball (2)
on nomer = stud_nomer
Оба этих оператора дают один и тот оке результат, показанный на рис. 1.
Как видим, таблица получилась достаточно громоздкой. С одной стороны она содержит много столбцов, с другой — эти столбцы повторяются. В самом деле, столбцы nomer и stud_nomer по условию соединения содержат одно и то же. Поэтому если при соединении получается достаточно много столбцов, разумнее в операторе select явно перечислить лишь те, которые действительно нужны.
| l _name | f_ name | m_name | nomer | gr_nomer | stud_nomer | dis | dat | form | res |
| Иванов | Иван | Иванович | Алгебра | 04.01.2004 | Зачет | ||||
| Иванов | Иван | Иванович | Анализ | 08.01.2004 | Экзамен | ||||
| Иванов | Иван | Иванович | Информатика | 14.01.2004 | Экзамен | ||||
| Иванов | Александр | Петрович | Алгебра | 04.01.2004 | Зачет | ||||
| Иванов | Александр | Петрович | Анализ | 08.01.2004 | Экзамен | ||||
| Иванов | Александр | Петрович | Информатика | 14.01.2004 | Экзамен | SO | |||
| Федоров | Николай | Андреевич | Алгебра | 09.01.2004 | Экзамен | ||||
| Федоров | Николай | Андреевич | Анализ | 04.01.2004 | Экзамен | ||||
| Федоров | Николай | Андреевич | Информатика | 14.01.2004 | Зачет | ||||
| Сидоров | Павел | Алексеевич | Алгебра | 09.01.2004 | Экзамен | ||||
| Сидоров | Павел | Алексеевич | Анализ | 04.01.2004 | Экзамен | ||||
| Сидоров | Павел | Алексеевич | Информатика | 14.01.2004 | Зачет | ||||
| Сорокин | Михаил | Анатольевич | Алгебра | 09.01.2004 | Экзамен | ||||
| Сорокин | Михаил | Анатольевич | Анализ | 04.01.2004 | Экзамен | ||||
| Сорокин | Михаил | Анатольевич | Информатика | 14.01.2004 | Зачет |
Рис.1 Результат внутреннего объединения
Пример 31 Если, например, нас интересует для каждого студента лишь балл по каждой дисциплине, то для выборки можно использовать такой запрос:
select student.*, dis, res
from student inner join ball
on nomer = stud_nomer
Результат его выполнения:
| l_ name | f_name | m-пате | потer | gr_ nomer | dis | res |
| Иванов | Иван | Иванович | 010001 | 11 | Алгебра | 100 |
| Иванов | Иван | Иванович | 010001 | 11 | Анализ | 80 |
| Иванов | Иван | Иванович | 010001 | 11 | Информатика | 75 |
| Иванов | А лекеандр | Петрович | 010002 | 11 | Алгебра | 50 |
| Иванов | Александр | Петрович | 010002 | 11 | Анализ | 70 |
| Иванов | Александр | Петрович | 010002 | 11 | Информатика | 80 |
| Федоров | Николай | Андреевич | 011005 | 12 | Алгебра | 80 |
| Федоров | Николай | Андреевич | 011005 | 12 | Анализ | 70 |
| Федоров | Николай | Андреевич | 011005 | 12 | Информатика | 80 |
| Сидоров | Павел | Алексеевич | 011003 | 12 | Алгебра | 30 |
| Сидоров | Павел | Алексеевич | 011003 | 12 | Анализ | 70 |
| Сидоров | Павел | Алексеевич | 011003 | 12 | Информатика | 90 |
| Сорокин | Михаил | Анатольевич | 0U004 | 12 | Алгебра | 60 |
| Сорокин | Михаил | Анатольевич | 0П0О4 | 12 | Анализ | 60 |
| Сорокин | Михаил | Анатольевич | 011004 | 12 | Информатика | 70 |
Запись
|<имя таблицы>. *
означает выбор всех столбцов из указанной таблицы, в нашем случае — из таблицы student.
Задача 43 (Библиотека) Постройте оператор, соединяющий таблицу книг и таблицу авторов, и выводягций название книги и имена авторов.
Задача 44 (CD) Постройте оператор, соединяющий таблицу компакт-дисков и таблицу треков, и выводящий название альбома, номер и название трека.
Таблицу можно соединить саму с собой.
Пример 32 Допустим, мы хотим получить все пары студентов, которые учатся в одной и той же группе. Это можно сделать следующим образом:
select s1.nomer as first, s2. nomer as second
from student s1 inner join student s2
on s1.gr_nomer = s2.gr_nomer
Здесь к каждой строке таблицы student последовательно будут присоединяться строки той же таблицы, у которых значение поля gr_nomer такое же. Результат:
| first | second |
| 010002 | 010001 |
| 010001 | 010001 |
| 010002 | 010002 |
| 010001 | 010002 |
| 011004 | 011005 |
| 011003 | 011005 |
| 011005 | 011005 |
| 011004 | 011003 |
| 011003 | 011003 |
| 011005 | 011003 |
| 011004 | 011004 |
| 011003 | 011004 |
| 011101 | 011101 |
| 011005 | 011004 |
На этом примере также продемонстрирован способ корреляции (временного переименования) исходных таблиц внутри оператора select: после имени соответствующей таблицы нужно написать новое (коррелированное) имя:
|<имя таблицы> <новое имя>
Корреляция действует только на время выполнения оператора select. В данном случае в качестве коррелированных имен используются si и s2. Корреляция в данном операторе необходима, так как мы соединяем две таблицы, имеющие одно и то же имя, и если их не переименовывать, то будет непонятно, к какой из таблиц относятся поля gr_nomer или nomer. Для того чтобы указать, к какой таблице относится то или иное поле, используется точка:
|<имя таблицы>. <имя поля>
В качестве имени таблицы используется коррелированное имя, или непосредственное имя таблицы, если корреляция отсутствует. Это бывает нужно не только при соединении таблицы с собой же, но и в случае, если несколько таблиц имеют столбцы с одинаковыми названиями.
Пример 33 Если бы в таблице ball мы бы назвали первый столбец потеr вместо stud_nomer, то оператор (2) потребовалось бы переписать следующим образом:
select *
from student inner join ball
on student.nomer = ball.nomer
Задача 45 (CD) Соедините таблицу исполнителей саму с собой так, чтобы получить список пар, которые имеют записи в одном и том же альбоме.
«Улучшим» наш запрос из примера 32. В самом деле, с одной стороны не нужно включать в таблицу пары студента с самим собой, с другой - каждая пара студентов присутствует дважды - в разных порядках.
Пример 34 Сделаем так, что в паре студенты были различны, и каждая пара встречалась единожды:
select s1. nomer as first, s2. nomer as second
from student s1 inner join student s2
on s1. gr_nomer = s2. gr_nomer
where s1.nomer < s2.nomer
Получим:
| first | second |
| 010001 | 010002 |
| 011003 | 011004 |
| 011003 | 011005 |
| 011004 | 011005 |
Задача 46 (CD) Исправьте таким же образом запрос из задачи 45.
Задача 47 (Библиотека) Постройте запрос, выводящий для каждой книги название, первого (по алфавиту) автора и общее количество авторов книги.
Задача 48 (Библиотека) Постройте запрос, выводящий книги, имеющие в точности одного автора.
Задача 49 (CD) Построить запрос, выводящий для каждого компакт-диска, название и общее количество треков.
Задача 50 (Университет) Построить запрос, выводящий для каждого студента количество его оценок меньших 50.
Чтобы соединить больше чем две таблицы, их нужно соединять последовательно:
<имя таблицы> inner join <имя таблицы>
on <условие>
inner join <имя таблицы>
on <условие>...
Пример 35 Предположим, что таблица professor заполнена следующим образом:
| dis name | gr | prof |
| Анализ | 11 | Климок |
| Алгебра | 11 | Колдунов |
| Информатика | 11 | Дудаков |
| Анализ | 12 | Михно |
| Алгебра | 12 | Колдунов |
| Информатика | 12 | Волушкова |
| Практикум на ЭВМ | 11 | Дудаков |
| Практикум на ЭВМ | 12 | Сорокин |
В этом случае можно соединить все три таблицы для того, чтобы узнать, у каких преподавателей учился каждый из студентов:
select nomer, prof
from student inner join ball (3)
on nomer = stud_nomer
inner join professor
on dis = dis_name and gr_nomer gr
| nomer | prof |
| 010001 | Колдунов |
| 010001 | Климок |
| 010001 | Дудаков |
| 010002 | Колдунов |
| 010002 | Климок |
| 010002 | Дудаков |
| 011005 | Колдунов |
| 011005 | Михпо |
| 011005 | Волугикова |
| 011003 | Колдунов |
| 011003 | Михно |
| 011003 | Волушкова |
| 011004 | Колдунов |
| 011004 | Михно |
| 011004 | Волугикова |
Того же можно добиться, записав:
select nomer, prof
from student, ball, professor (4)
where nomer = stud_nomer and
dis = dis_name and gr_nomer = gr
При соединении как первая так и вторая таблица может, в свою очередь, быть соединением. То есть предыдущий запрос можно переписать еще и так:
select nomer, prof
from student inner join
ball inner join professor on dis = dis_name
on nomer = stud_nomer and gr_nomer = gr
В этом случае сначала соединяются таблицы, ball и professor, а затем к ним присоединяется таблица student. В запросе (3) происходит, наоборот: сначала соединяются таблицы student и ball, а затем к результату присоединяется professor. Для запроса (4) последовательность соединения выбирает исполнитель запроса (планировщик).
Задача 51 (Банк) Построить запрос, соединяющий таблицы клиентов и счетов.
Задача 52 (Банк) Построить запрос, определяющий количество владельцев каждого счета.
Задача 53 (Банк) Построить запрос, определяющий для каждого клиента коли'чество счетов, владельцем или совладельцем которых он является.
Задача 54 (Библиотека) Построить запрос, определяющий для каждой книги, сколько раз ее брали.
Задача 55 (Библиотека) Построить запрос, определяющий для каждого читателя, сколько книг он брал за все время.
Задача 56 (Библиотека) Построите запросы, определяющий для каждого читателя, сколько книг он взял в настоящий момент и сколько из них он уже должен был вернуть.
Ввиду того, что декартово произведение ассоциативно, ассоциативно и внутреннее соединение. То есть, неважно, в какой последовательности выполняются операторы внутреннего соединения: слева направо или наоборот.
При выполнении внутреннего соединения может оказаться так, что для каких-то строк одной таблицы не нашлось соответствующих строк в другой. Такие строки при внутреннем соединении отбрасываются.
Внешнее соединение предназначено для того, что сохранить все строки одной из таблиц или обеих сразу. Синтаксис конструкций: левое внешнее соединение —
<имя таблицы> left outer join <имя таблицы>
on <условие>
правое внешнее соединение —
<имя таблицы> right outer join <имя таблицы>
on <условие>
полное внешнее соединение —
<имя таблицы> full outer join <имя таблицы>
on <условие>
В первом случае все строки первой таблицы попадут в результат соединения, во втором - все строки второй таблицы, в третьем - все строки и той и другой будут присутствовать.
Рассмотрим левое соединение, при котором первая таблица попадает в результат полностью. Если для какой-то строки первой таблицы нашлась хоть одна строка во второй, которая удовлетворяет условию соединения, то внешнее соединение ведет себя для этой строки так же, как внутреннее. Если же для строки первой таблицы пары из второй не нашлось, то такая строка тоже включается в результат, но все поля второй таблицы для этой строки принимают значение NULL, означающее отсутствие данных.
Пример 36 Например, можно найти для преподавателей средний балл, который получают у них студенты:
select prof, avg(res)
from professor left outer join
(ball inner join student
on nomer = stud_nomer)
on dis_name=dis and gr_nomer = gr
group by prof
В этом примере с помощью левого внешнего соединения соединяются две таблицы: первая — professor, а вторая получена внутренним соединением, таблиц ball и student. Результат:
| prof | |
| Волушкова | 80 |
| Дудаков | 11 |
| Климок | 15 |
| Колдунов | 64 |
| Сорокин | NULL |
| Михпо | 66 |
Любые действия с NULL в результате дают NULL, в частности, при нахождении среднего арифметического avg результатом будет NULL, если NULL встречается хотя бы в одной строке группы.
Задача 57 (Банк) Для каждого счета определить, сколько операций с ним было проделано после его открытия 01.01.2004-01.02.2004-
Задача 58 (Супермаркет) Для каждого наименования товара подсчитать, скольким покупателям он был продан (если товар не продавался, то напротив него должен стоять ноль) в период 01.01.2004-01.02.2004-
Для проверки, имеет ли некоторое выражение значение NULL, используется предикат
|is null
Пример 37 Например, чтобы найти все предметы, оценки за которые еще не внесены в базу данных:
select dis_name, gr, prof
from professor left outer join
(ball inner join student
on nomer = stud_nomer)
on dis_name=dis and gr_nomer = gr
where res is null
В результате выполнения этого оператора в столбце res окажется значения NULL в тех строках, для которых отсутствуют строки в таблице ball.
| dis name | gr | prof |
| Практикум на ЭВМ | 11 | Дудаков |
| Практикум на ЭВМ | 12 | Сорокин |
Задача 59 (Университет) Найти преподавателей, которые за период 01.01.2000 01.01.2005 не написали ни одной статьи.
Задача 60 (Библиотека) Найти ключевые слова, которые не встречаются в книгах, выпущенных ранее 2000 года.
Задача 61 (Библиотека) Найти авторов, которые не написали ни одной книги, в названии которых есть хотя, бы одно из слов «математика», «математический».
Левое внешнее соединение не ассоциативно ни с самим собой, ни с другими видами соединений.
Пример 38 Рассмотрим, например, следующие три таблицы:
| Таблица а | Таблица b | Таблица с | ||
| a1 | b1 | b2 | c1 | с2 |
| 1 | 1 | 11 | 1 | 12 |
При выполнении соединения
a left outer join b on a1 =b1
left outer join с on a1 = c1 and b2 = c2
получим таблицу вида
| a1 | b1 | b2 | c1 | c2 |
| 1 | 1 | 11 | NULL | NULL |
Если же порядок соединения поменять:
a left outer join
Ъ left outer join с on Ъ2 = c2 on al = cl and al = Ы
то получается другой результат:
| al | Ы | Ъ2 | cl | c2 |
| 1 | NULL | NULL | NULL | NULL |
Таким образом, если при соединении более чем двух таблиц используется внешнее соединение, то на порядок выполнения соединений нужно обращать особое внимание.
Правое внешнее соединение отличается от левого только тем, что в результат включаются все строки из правой таблицы, а значение NULL записывается в столбцы левой таблицы, если соответствующие строки в ней отсутствуют. Полное внешнее соединение сохраняет все строки и той и другой таблицы.
3.4 Подзапросы.
Самое мощное средство языка SQL — вложенные запросы (или подзапросы). Самой общей формой использования подзапросов является использование его в качестве условия в инструкциях where или having:
(exists(<SQL-3anpoc>)
Это условие будет истинным, если таблица, сгенерированная в результате выполнения SQL-запроса в скобках, содержит хотя бы одну строку, в противном случае условие ложно. Частные случаи использования подзапросов — сравнения:
<выражение> <знак сравнения> any (<SQL-3anpoc>)
и
<выражение> <знак сравнения> all
(<SQL-3anpoc>)
В обоих случаях SQL-запрос должен возвращать таблицу с одним столбцом, значения которого можно сравнивать с выражением. Первое условие будет истинным, если сравнение выполняется хотя бы для одной строки сгенерированной таблицы, второе — если условие выполняется для всех строк. В частности, если вложенный запрос вернет пустую таблицу, то первое условие ложно, а второе истинно. Если вложенный запрос возвращает ровно одну строку, то оба условия эквивалентны. Рассмотрим несколько примеров.
Пример 39 Найдем студента, который получил самый высокий балл:
select l_name, gr_nomer, nomer, dis, res from student inner join ball
on nomer = stud_nomer where res = all(select max(res) from ball)
| l_ name | gr nomer | nomer | dis | res |
| Иванов | 11 | 010001 | Алгебра | 100 |
В данном случае для выполнения подзапроса не используется никакая информация из таблиц основного запроса. Такие подзапросы называют несвязанными.
Пример 40 Изменим запрос, чтобы найти студентов, получивших самый высокий балл, по каждому из предметов:
select l_name, gr_nomer, nomer, dis, res from student inner join ball on nomer = stud_nomer where res = all(select max(res) from ball b where ball.dis = b.dis)
| I name | gr nomer | nomer | dis | res |
| Сидоров | 12 | 011003 | Информатика | 90 |
| Иванов | 11 | 010001 | Анализ | 80 |
| Иванов | 11 | 010001 | Алгебра | 100 |
Здесь при выполнении подзапроса используется поле dis из таблицы ball основного запроса. Такие подзапросы называют, связанными. В связи с использованием таблицы ball и во внешнем запросе, и в подзапросе возникает, необходимость переименования таблицы вложенного запроса, чтобы отличать ее от таблицы основного запроса, и необходимость явного указания таблиц для поля dis.
Пример 41 Чтобы вывести все оценки по каждому предмету, кроме самых низких:
select l_name, gr_nomer, nomer, dis, res from student inner join ball
on nomer = stud_nomer where res > any(select min(res) from ball b where ball.dis = b.dis)
| I name | gr nomer | nomer | dis | res |
| Иванов | 11 | 010002 | Алгебра | 50 |
| Сорокин | 12 | 011004 | Алгебра | 60 |
| Федоров | 12 | 011005 | Алгебра | 80 |
| Иванов | 11 | 010001 | Алгебра | 100 |
| Иванов | 11 | 010002 | Анализ | 70 |
| Сидоров | 12 | 011003 | Анализ | 70 |
| Федоров | 12 | 011005 | Анализ | 70 |
| Иванов | 11 | 010001 | Анализ | 80 |
| Иванов | 11 | 010001 | Информатика | 75 |
| Иванов | 11 | 010002 | Информатика | 80 |
| Федоров | 12 | 011005 | Информатика | 80 |
| Сидоров | 12 | 011003 | Информатика | 90 |
Пример 42 Каждый из предыдущих запросов можно записать при помоги^ exists:
on nomer = stud_nomer where not exists (select * from ball b where b.res > ball.res)
select * from student inner join ball
on nomer = stud_nomer where not exists(select * from ball b where ball.dis = b.dis and b.res > ball.res)
select * from student inner join ball
on nomer = stud_nomer where exists (select * from ball b where ball.dis = b.dis and b.res < ball.res)
Задача 62 (Супермаркет) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найти товары, который имеет самую высокую цену (среди всех тюваров).
Задача 63 (Супермаркет) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найти тювары, который имеет самую высокую цену (среди товаров того же вида).
Задача 64 (Супермаркет) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите фирмы-производители, количество наименований товаров которых является самым большим.
Задача 65 (Библиотека) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите книги, имеющие самый большой авторский коллектив.
Задача 66 (Библиотека) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите авторов, которые написали (одни или в соавторстве) наибольшее количество книг.
Задача 67 (Библиотека) Запигиит,е следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите автюров, которые написали самое большое количество книг без соавторов.
Задача 68 (Банк) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите клиентов, общая сумма на. счетах (с учетом общих с другими клиентами счетов) которых является наибольшей.
Задача 69 (Банк) Запишите следующей запрос при помощи подзапросов-сравнений и при помощи exists: найдите клиентов, общая сумма на счетах (без учета общих с другими клиентами счетов) которых является наиб
