База данных. ЭУМК Базы данных. Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск
 Скачать 2.33 Mb.
|
|
Оператор выбора SELECT Язык запросов в SQL состоит из единственного оператора SELECT. Синтаксис оператора SELECT имеет следующий вид: SELECT [ ALL| DISTINCT] <Список полей>|* FROM <Список таблиц> [WHERE <Предикат-условие выборки или соединения>] [GROUP BY <Список полей результата>] [HAVING <Предикат-условие для группы>] [ORDER BY <Список полей, по которым упорядочить вывод>]; SELECT – ключевое слово, которое сообщает СУБД, что эта команда – запрос. Все запросы начинаются этим словом с последующим пробелом. За ним может следовать способ выборки. Здесь ключевое слово ALL означает, что в результирующий набор строк включаются все строки, удовлетворяющие условиям запроса. Значит в результирующий набор могут попасть одинаковые строки. Это нарушение принципов теории отношений (в отличие от реляционной алгебры, где по умолчанию предполагается отсутствие дубликатов в каждом результирующем отношении). Ключевое слово DISTINCT означает, что в результирующий набор включаются только различные строки, то есть дубликаты строк результата не включаются в набор. Список полей – это список перечисленных через запятую столбцов, которые выбираются запросом из таблиц. Символ * (звездочка) означает, что в результирующий набор включаются все столбцы из исходных таблиц запроса. В разделе FROM задается перечень исходных отношений (таблиц) запроса. В случае, если указано более одного имени таблицы, неявно подразумевается, что над перечисленными таблицами осуществляется операция декартова произведения. Разделы SELECT и FROM являются обязательными, все другие разделы являются необязательными. Примеры. Выражение SELECT * FROM СТУДЕНТЫ ; означает выбор всех полей из таблицы СТУДЕНТЫ. Выражение SELECT NOM_ZACH, FIO FROM СТУДЕНТЫ ; означает выбор двух полей из таблицы СТУДЕНТЫ. Выражение SELECT * FROM СТУДЕНТЫ, ЭКЗАМЕН; соответствует декартову произведению таблиц СТУДЕНТЫ и ЭКЗАМЕН. Выражение SELECT СТУДЕНТЫ.NOM_ZACH, ЭКЗАМЕН.OCENKA FROM СТУДЕНТЫ, ЭКЗАМЕН; соответствует проекции декартова произведения двух таблиц на два столбца NOM_ZACH из таблицы СТУДЕНТЫ и OCENKA из таблицы ЭКЗАМЕН, при этом дубликаты всех строк сохранены, в отличие от данной операции в реляционной алгебре, где по умолчанию все дубликаты кортежей уничтожаются. В разделе WHERE задаются условия отбора строк результата или условия соединения кортежей исходных таблиц, подобно операции условного соединения в реляционной алгебре. В выражении условий раздела WHERE могут быть использованы следующие предикаты: Предикаты сравнения (=, <>, >, >=, <, <=), которые имеют традиционный смысл. Пример 1. Выбрать из таблицы Продажа все поля для записей, где поле Количество больше 10: Select * from Продажа where Количество > 10; Пример 2. Выбрать из таблицы Экзамен все поля для записей, где оценка 5: Select * from Экзамен where Оценка = 5; Предикат Between A and B – принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and B, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал, включая его границы. Пример 1. Выбрать из таблицы Продажа все поля для записей, где поле Количество попадает в интервал от 10 до 50: Select * from Продажа where Количество between 10 and 50; Пример 2. Выбрать из таблицы Продажа все поля для записей, где поле Дата продажи попадает в интервал от 1.01.06 до 31.01.06: Select * from Продажа where [Дата продажи] between #01/01/06# and #31/01/06#; Предикат вхождения в множество IN (множество) истинен тогда, когда сравниваемое значение входит в множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество. Пример 1. Выбрать из таблицы Группы все поля для записей, где поле Шифр группы имеет значения АИ51, АИ52, АИ53: Select * from Группы where [Шифр группы] in (“АИ51”, “АИ52”, “АИ53”); Пример 2. Выбрать из таблицы Экзамен все поля для записей, где поле Оценка принимает значения 4 или 5: Select * from Экзамен where Оценка in (4, 5); Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл. Шаблон может содержать % (* для Access) для обозначения любого числа любых символов; _ (? для Access) для обозначения любого одного символа. Пример. Выбрать из таблицы Студенты все поля для записей, где поле Фамилия начинается с буквы «С» или «М»: Для СУБД Access Select * from Студенты Where Фамилия = like “С*” or Фамилия = like “М*”; Для других СУБД Select * from Студенты Where Фамилия = like “С%” or Фамилия = like “М%”; Предикат сравнения с неопределенным значением IS NULL. Для выявления равенства значения некоторого атрибута неопределенному значению применяют специальные стандартные предикаты: <имя атрибута> IS NULL и <имя атрибута> IS NOT NULL Пример. Выбрать из таблицы Сотрудники все поля для записей, где поле Домашний телефон не пусто: Select * from Сотрудники where [Домашний телефон] is not null; Предикат существования EXIST и не существования NOT EXIST. Применяется во вложенных запросах для определения непустого или пустого множества, являющегося результатом выборки. В условиях поиска могут быть использованы все рассмотренные предикаты. Рассмотрим базу данных, которая моделирует сдачу сессии в некотором учебном заведении. Пусть она состоит из трех отношений R1, R2, R3, представленных таблицами R1, R2, R3 соответственно. R1 = (ФИО, Дисциплина, Оценка); R2 = (ФИО, Группа); R3 = (Группа, Дисциплина). R1 ФИО Дисциплина Оценка Петров Ф.И. Базы данных 5 Сидоров К.А. Базы данных 4 Миронов А.В. Базы данных 2 Петров Ф.И. Моделирование 5 Сидоров К.А. Моделирование 4 Миронов А.В. Моделирование Null Трофимов П.А. Сети ЭВМ 4 Иванова Е.А. Сети ЭВМ 5 Уткина Н.В. Сети ЭВМ 5 R2 ФИО Группа Петров Ф.И. АИ21 Сидоров К.А. АИ21 Миронов А.В. АИ21 Трофимов П.А. АИ22 Иванова Е.А. АИ22 Уткина Н.В. АИ22 R3 Группа Дисциплина АИ21 Базы данных АИ21 Моделирование АИ22 Сети ЭВМ Приведем несколько примеров использования оператора SELECT. Вывести список всех групп (без повторений), где должны пройти экзамены. SELECT DISTINCT Группа FROM R3; Результат: Группа АИ21 АИ22 Вывести список студентов, которые сдали экзамен по дисциплине «Базы данных» на «отлично». SELECT ФИО FROM R1 WHERE Дисциплина = «Базы данных» AND Оценка = 5; Результат: ФИО Петров Ф.И. Найти студентов, пришедших на экзамен, но не сдавших его с указанием названия дисциплины. Оператор SELECT будет выглядеть следующим образом: SELECT ФИО, Дисциплина FROM R1 WHERE Оценка IS NULL; Результат: ФИО Дисциплина Миронов А.В. Моделирование Вывести список всех студентов, которым надо сдавать экзамены с указанием названий дисциплин, по которым должны проводиться эти экзамены. SELECT ФИО, Дисциплина FROM R2, R3 WHERE R2.Группа = R3.Группа; Здесь WHERE задает условие соединения отношений R2 и R3, при отсутствии условий соединения результат будет эквивалентен декартову произведению, и в этом случае каждому студенту были бы приписаны все дисциплины из отношения R3, а не те, которые должна сдавать каждая группа. Результат: ФИО Дисциплина Петров Ф.И. Базы данных Сидоров К.А. Базы данных Миронов А.В. Базы данных Петров Ф.И. Моделирование Сидоров К.А. Моделирование Миронов А.В. Моделирование Трофимов П.А. Сети ЭВМ Иванова Е.А. Сети ЭВМ Уткина Н.В. Сети ЭВМ Оператор Select может содержать вычисляемые поля. Для вычисляемого поля можно задать имя после AS. Пример 1. Вывести все поля из таблицы Продажа и добавить вычисляемое поле Стоимость покупки: Select *, [Цена за единицу] * [Количество] as [Стоимость покупки] from Продажа; Пример 2. Даны две таблицы Товары (Код, Название, Розничная цена), Продажа (Чек, Код, Дата продажи, Количество). Выбрать поля Название и Розничная цена из таблицы Товары и поля Чек, Дата продажи и Количество из таблицы Продажа. Добавить вычисляемое поле Стоимость покупки. Select Товары.Название, Товары.[Розничная цена], Продажа.Чек, Продажа.[Дата продажи], Продажа.Количество, Товары.[Розничная цена] * Продажа.Количество as [Стоимость покупки] from Товары, Продажа where Товары.Код = Продажа.Код; При выборе полей из разных таблиц необходимо: указывать имя таблицы, затем ставить точку и указывать имя поля; в where указывать условие соединения двух таблиц (в примере – это равенство полей Код из двух таблиц). Пример 3. Даны две таблицы Knigi (Shifr, Avtor, Nazv, Cena) и Postavka (Nomer_posr, Shifr, Data_post, Kol). Выбрать все поля из двух таблиц и добавить вычисляемое поле Stoim_post (стоимость поставки). Select K.*, P.*, K.Cena * P.Kol as Stoim_post From Knigi K, Postavka P Where K.Shifr = P.Shifr; В данном примере К и Р – это алиасные (вторые) имена таблиц Knigi и Postavka соответственно. В разделе GROUP BY задается список полей группировки. GROUP BY группирует записи данных и объединяет в одну запись все записи данных, которые содержат идентичные значения в указанном поле (или полях). WHERE определяет, какие записи должны участвовать в группировании, т.е. фильтрует до группирования. В разделе HAVING задаются предикаты-условия, накладываемые на каждую группу. HAVING используется для фильтрации записей, полученных в результате группировки. WHERE определяет, какие записи должны участвовать в группировании, т.е. фильтрует до группирования. HAVING определяет, какие из получившихся в результате группировки записей будут включены в результирующую выборку, т.е. фильтрует записи после группирования. В части ORDER BY задается список полей упорядочения результата, то есть список полей, который определяет порядок сортировки в результирующем отношении. Например, если первым полем списка будет указан Шифр группы, а вторым Фамилия, то в результирующем отношении записи сначала будут расположены в порядке возрастания шифра группы, а затем в рамках одной группы записи будут отсортированы по фамилии в алфавитном порядке. В SQL добавлены дополнительные функции, которые позволяют вычислять обобщенные групповые значения. Для применения агрегатных функций предполагается предварительная операция группировки. При группировке все множество кортежей отношения разбивается на группы, в которых объединяются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки. Например, сгруппируем отношение R1 по значению столбца Дисциплина. Мы получим 3 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка. Это осуществляется с помощью агрегатных функций. Агрегатные функции вычисляют одиночные значения для каждой группы таблицы. Список агрегатных функций представлен в табл. 3.2. Таблица 3.2 Агрегатные функции Функция Результат COUNT Количество строк или непустых значений полей, которые выбрал запрос SUM Сумма всех выбранных значений данного поля AVG Среднеарифметическое значение всех выбранных значений данного поля MIN Наименьшее из всех выбранных значений данного поля MAX Наибольшее из всех выбранных значений данного поля Агрегатные функции применяются подобно именам полей в операторе SELECT, но они используют имя поля как аргумент. С функциями SUM И AVG могут использоваться только числовые поля. С функциями COUNT, MAX, MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Например, можно вычислить количество студентов, сдавших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю «Дисциплина» и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе. SELECT R1.Дисциплина, COUNT(*) FROM R1 GROUP BY R1.Дисциплина; Результат: Дисциплина COUNT(*) Базы данных 3 Моделирование 3 Сети ЭВМ 3 Если же необходимо определить количество сдавших экзамен по каждой дисциплине, то необходимо исключить неопределенные значения из исходного отношения перед группировкой. В этом случае запрос будет выглядеть следующим образом: SELECT R1.Дисциплина, COUNT(*) FROM R1 WHERE R1.Оценка IS NOT NULL GROUP BY R1.Дисциплина; Результат: Дисциплина COUNT(*) Базы данных 3 Моделирование 2 Сети ЭВМ 3 В этом случае строка со студентом Миронов А.В. Моделирование Null не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины «Моделирование» будет на 1 меньше. Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу. Найдем количество студентов, успешно сдавших экзамены: SELECT COUNT(*) FROM R1 WHERE Оценка > 2; Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Но для того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца. Вычислим количество различных оценок, полученных по каждой дисциплине: SELECT R1.Дисциплина, COUNT (DISTINCT R1.Оценка) FROM R1 WHERE R1.Оценка IS NOT NULL GROUP BY R1.Дисциплина; Результат: Дисциплина COUNT(DISTINCT R1.Оценка) Базы данных 3 Моделирование 2 Сети ЭВМ 2 В результат можно включить значение поля группировки и несколько агрегатных функций, а в условиях группировки можно использовать несколько полей. При этом группы образуются по набору заданных полей группировки. Операции с агрегатными функциями могут быть применимы к нескольким исходным таблицам. Например, определить для каждой группы и каждой дисциплины количество успешно сдавших экзамен и средний балл по дисциплине. SELECT R2.Группа, R1. Дисциплина, COUNT(*), AVG(Оценка) FROM R1, R2 WHERE R1.ФИО = R2.ФИО AND R1.Оценка IS NOT NULL AND R1.Оценка >2 GROUP BY R2.Группа, R1.Дисциплина; Результат: Группа Дисциплина COUNT(*) AVG(Оценка) АИ21 Базы данных 2 3,67 АИ21 Моделирование 2 4,5 АИ22 Сети ЭВМ 3 4,67 Рассмотрим в качестве другого примера отношения-таблицы F и Q из базы данных «Банк», в которой содержится информация о счетах в филиалах некоторого банка: F = (N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток); Q = (Филиал, Город). Определим суммарный остаток на счетах в филиалах: SELECT Филиал, SUM(Остаток) FROM F GROUP BY Филиал; GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, то есть суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, как и результат работы агрегатной функции. Поэтому мы можем совместить в одном запросе агрегат и поле. Определить суммарные значения остатков на счетах, которые превышают 5000. Чтобы увидеть суммарные остатки свыше 5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же, как предложение WHERE делает это для индивидуальных записей. SELECT Филиал, SUM(Остаток) FROM F GROUP BY Филиал HAVING SUM(Остаток) > 5000; Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода. Определить суммарные остатки на счетах филиалов в Ессентуках, Азове, Таганроге: SELECT Филиал, SUM(Остаток) FROM F, Q WHERE F.Филиал – Q.Филиал GROUP BY Филиал HAVING Город IN («Ессентуки», «Азов», «Таганрог»); Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE. Раздел определяет порядок сортировки записей, включенных в выборку: …ORDER BY поле1 [ASC / DESC] [, поле2 [ASC / DESC] [,…]] Особенности: Раздел не является обязательным, однако он обязательно используется с предикатом TOP. По умолчанию сортировка идет по возрастанию, можно явно указать возрастающую сортировку, записав ASC. Ключ DESC задает сортировку по убыванию. Порядок перечисления полей задает иерархию уровней сортировки. ORDER BY – последняя директива в запросе. Примеры. Отсортировать записи по полю ФИО в алфавитном порядке: SELECT * FROM R1 ORDER BY ФИО; Отсортировать записи в порядке убывания значений в поле Оценка: SELECT * FROM R1 ORDER BY Оценка DESC; Ключевое слово |