История развития пистолетов-пулеметов: Предпосылкой для возникновения пистолетов-пулеметов послужила давняя тенденция тяготения винтовок...
Двойное оплодотворение у цветковых растений: Оплодотворение - это процесс слияния мужской и женской половых клеток с образованием зиготы...
Топ:
Оценка эффективности инструментов коммуникационной политики: Внешние коммуникации - обмен информацией между организацией и её внешней средой...
Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов...
Определение места расположения распределительного центра: Фирма реализует продукцию на рынках сбыта и имеет постоянных поставщиков в разных регионах. Увеличение объема продаж...
Интересное:
Наиболее распространенные виды рака: Раковая опухоль — это самостоятельное новообразование, которое может возникнуть и от повышенного давления...
Мероприятия для защиты от морозного пучения грунтов: Инженерная защита от морозного (криогенного) пучения грунтов необходима для легких малоэтажных зданий и других сооружений...
Искусственное повышение поверхности территории: Варианты искусственного повышения поверхности территории необходимо выбирать на основе анализа следующих характеристик защищаемой территории...
Дисциплины:
2020-03-31 | 150 |
5.00
из
|
Заказать работу |
|
|
Организация Web -доступа к базам
Данных с использованием SQL- запросов.
Исполнитель: ВОЛКОВ Константин Владимирович
ученик 11Б класса МСОШ № 175
Руководители: ФЕДОРОВ Леонид Николаевич
директор Информационно-методического центра
Управления образования администрации Ленинского района
МОКРЯНСКИЙ Дмитрий Георгиевич
методист Информационно-методического центра
Управления образования администрации Ленинского района
Екатеринбург
2000
C одержание
Введение. 1. Причины и история создания языка запросов SQL. 1.1. Реляционные базы данных. Общие понятия. 1.2. Взаимодействие SQL и СУБД. 1.3. Стандарты SQL. Сегодняшнее состояние. 2. Технологии, обеспечивающие, web доступ к базам данных. 2.1. Принципы работы SQL-сервера. 2.2. Таблицы SQL. 2.2.1. Структура запросов SQL. 2.2.2. Запросы с использованием единственной таблицы SQL. 2.2.3. Запросы с использованием нескольких таблиц SQL. 2.2.4 Модификация данных в таблицах SQL. 2.3. Обзор основных SQL-серверов. 2.3.1. SQL-сервер Oralce. 2.3.2. Microsoft SQL сервер. 2.3.3. MySQL – сервер. 2.4. Принципы работы web-серверов. 2.4.1. Web-сервер. Понятие, функции, характеристики. 2.4.2. Трехзвенная архитектура клиент-сервер. 2.4.3. Архитектура Internet/Intranet. 2.4.4. Обзор серверных программ для различных ОС. 2.4.5. Стандарты, облегчающие создание Web-узлов. 2.4.6. Web-технологии. 2.4.7. Web-сервер Apache. 2.4.8. Web-сервер Jigsaw. 2.4.9. Web-сервер Netscape Enterprise. 2.4.10. Microsoft Internet Information Server. 2.5. Организация пользовательского интерфейса для доступа к базам данных. 3. База данных Информационно-методического центра "Сведения об образовательных учреждениях". 4. Вопросы безопасности и санкционирования доступа к базам данных. 5. Перспективы развития сетевых баз данных. 6. Список литературы. Приложения (Листинг программ). | 3 6 6 8 8 13 14 15 16 20 35 55 64 67 70 72 74 74 74 75 77 78 79 80 81 82 87 89 95 100 104 106 |
Введение
|
Базы данных выполняют функцию систематизации знаний. На основе этой систематизации могут создаваться новые знания. Так или иначе, любая база данных служит человеку именно для описания происшедших в прошлом событий и на основе знания этих событий помогает принять то или иное решение на будущее. База знаний может быть построена как мультимедийный справочник или как набор текстов и файлов другого формата, проиндексированных по определенным признакам в базе данных.
База данных – это, прежде всего, хранилище объектов данных, т.е. набора возможных понятий или событий, описываемых базой данных, с возможностью поиска этих объектов по признакам. Неотъемлемой чертой базы данных является возможность связывания объектов между собой. Базой данных можно считать не только таблицы, индексирующие файлы со знаниями разных форматов, но и сами эти файлы, потому, что они являются не типизированными хранилищами знаний в такой базе данных.
Итак, в базах знаний мы накапливаем опыт прошлого. Потом человек может сам принять решение на основе этого опыта (типичный случай с мультимедийным справочником) или поставить задачу перед базой данных по поиску решения согласно сложившейся ситуации (найти закон, поясняющий правило оформления таможенной декларации и т.п.). Так происходит в программах справочного характера. Как частный случай баз данных, можно рассматривать различные структурированные файлы, например, словари для переводчиков, форматы файлов RTF, DOC, книги Microsoft Excel, файлы с письмами для почтовых Internet-программ и т.д., жизненно важные функции баз данных, в которых реализуются за счет внутренних функций программ работающих с ними. Базы данных могут применяться как вспомогательное средство, позволяющее реализовать какую-то полезную функцию. Например, хранение настроек программы, Internet-адресов для рассылки рекламы и т.д.
|
Причины и история создания языка запросов SQL.
Взаимодействие SQL и СУБД.
Увеличение объема и структурной сложности хранимых данных, расширение круга пользователей информационных систем привели к широкому распространению наиболее удобных и сравнительно простых для понимания реляционных (табличных) СУБД. Для обеспечения одновременного доступа к данным множества пользователей, нередко расположенных достаточно далеко друг от друга и от места хранения баз данных, созданы сетевые мультипользовательские версии СУБД. В них тем или иным путем решаются специфические проблемы параллельных процессов, целостности (правильности) и безопасности данных, а также санкционирования доступа.
SQL стал унифицированным средством общения и стандартным языком манипулирования с базами данных, обладающим средствами для реализации перечисленных выше возможностей. После появления на рынке двух пионерских СУБД – SQL/DS (1981 год) и DB2 (1983 год) – он приобрел статус стандарта де-факто для профессиональных реляционных СУБД. В 1987 году SQL стал официальным международным стандартом языка баз данных, а в 1992 году вышла вторая версия этого стандарта.
Важной отличительной чертой SQL является его независимость от компьютерной среды (операционной системы и архитектуры). Такой язык назвали SQL – это аббревиатура структурированного языка запросов (Structured Query Language). SQL является инструментом, предназначенным для обработки и чтения информации, содержащейся в компьютерной базе данных.
При создании языка запросов нового поколения разработчики старались сделать его простым и легким в освоении инструментом для обращения к БД. В итоге SQL стал слабо структурированным языком, особенно по сравнению с такими языками, как С или Pascal, и в то же время достаточно мощным и относительно легким для изучения.
Название
Статус
Адрес
|
Рисунок 2.2
Исключение дубликатов
В предыдущем примере был выдан правильный, но не совсем удачный перечень основных продуктов: из него не были исключены дубликаты. Для исключения дубликатов и одновременного упорядочения перечня необходимо дополнить запрос ключевым словом DISTINCT (различный, различные), как показано в следующем примере:
SELECT DISTINCT Основа
FROM Блюда;
Результат приведен на рис. 2.2,в.
Выборка вычисляемых значений
Из синтаксиса фразы SELECT видно, что в ней может содержаться не только перечень столбцов таблицы или символ *, но и выражения.
Например, если нужно получить значение калорийности всех продуктов, то можно учесть, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров – 9.3 ккал, и выдать запрос:
SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)
FROM Продукты;
результат которого приведен на рис. 2.3,а.
а) | Б) | в) | |||||
Продукт | Продукт | Продукт | |||||
Говядина | 1928.1 | Говядина | Калорий = | 1928.1 | Зелень | 118.9 | |
Судак | 1523. | Судак | Калорий = | 1523. | Помидоры | 196.8 | |
Масло | 8287.5 | Масло | Калорий = | 8287.5 | Морковь | 349.6 | |
Майонез | 6464.7 | Майонез | Калорий = | 6464.7 | Лук | 459.2 | |
Яйца | 1618.9 | Яйца | Калорий = | 1618.9 | Яблоки | 479.7 | |
Сметана | 3011.4 | Сметана | Калорий = | 3011.4 | Молоко | 605.1 | |
Молоко | 605.1 | Молоко | Калорий = | 605.1 | Кофе | 892.4 | |
Творог | 1575. | Творог | Калорий = | 1575. | Судак | 1523. | |
Морковь | 349.6 | Морковь | Калорий = | 349.6 | Творог | 1575. | |
Лук | 459.2 | Лук | Калорий = | 459.2 | Яйца | 1618.9 | |
Помидоры | 196.8 | Помидоры | Калорий = | 196.8 | Говядина | 1928.1 | |
Зелень | 118.9 | Зелень | Калорий = | 118.9 | Сметана | 3011.4 | |
Рис | 3512.1 | Рис | Калорий = | 3512.1 | Рис | 3512.1 | |
Мука | 3556.7 | Мука | Калорий = | 3556.7 | Мука | 3556.7 | |
Яблоки | 479.7 | Яблоки | Калорий = | 479.7 | Сахар | 4091.8 | |
Сахар | 4091.8 | Сахар | Калорий = | 4091.8 | Майонез | 6464.7 | |
Кофе | 892.4 | Кофе | Калорий = | 892.4 | Масло | 8287.5 | |
Рисунок 2.3
|
Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Следует отметить, что текстовые константы должны заключаться в апострофы ('). На рис. 2.3,б приведен результат запроса:
SELECT Продукт, 'Калорий =', ((Белки+Углев)*4.1+Жиры *9.3)
FROM Продукты;
А что произойдет, если какой-либо член выражения не определен, т.е. имеет значение NULL и каким образом появилось такое значение?
Если при загрузке строк таблицы в какой-либо из вводимых строк отсутствует значение для какого-либо столбца, то СУБД введет в такое поле NULL-значение. NULL-значение «придумано» для того, чтобы представить единым образом «неизвестные значения» для любых типов данных. Действительно, так как при вводе данных в столбец или их изменении СУБД запрещает ввод значений не соответствующих описанию данных этого столбца, то, например, нельзя использовать пробел для отсутствующего значения числа. Нельзя для этих целей использовать и ноль: нет месяца или дня недели равного нулю, да и для чисел ноль не может рассматриваться как неизвестное значение в одном месте и как известное – в другом. При выводе же NULL-значения на экран или печатающее устройство его код воспроизводится каким-либо специально заданным символом или набором символов: например, пробелом (если его нельзя перепутать с текстовым значением пробела) или сочетанием –0-.
С помощью специальной команды можно установить в СУБД один из режимов представления NULL-значений при выполнении числовых расчетов: запрет или разрешение замены NULL-значения нулем. В первом случае любое арифметическое выражение, содержащее неопределенный операнд, будет также иметь неопределенное значение. Во втором случае результат вычислений будет иметь численное значение (если это значение попадает в диапазон представления соответствующего типа данных).
Например, при выполнении запроса
SELECT ПР, Цена, К_во, (Цена * К_во)
FROM Поставки;
и разных «настройках» СУБД могут быть получены разные результаты:
ПР | Цена | К_во | (Цена*К_во) | ПР | Цена | К_во | (Цена*К_во) |
9 | -0- | -0- | -0- | 9 | -0- | -0- | 0. |
11 | 1.5 | 50 | 75. | 11 | 1.5 | 50 | 75. |
12 | 3. | 10 | 30. | 12 | 3. | 10 | 30. |
15 | 2. | 170 | 340. | 15 | 2. | 170 | 340. |
Использование BETWEEN
С помощью BETWEEN … AND … (находится в интервале от … до …) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне.
Например, выдать перечень продуктов, в которых значение содержания белка находится в диапазоне от 10 до 50:
Результат: | ||
SELECT Продукт, Белки FROM Продукты WHERE Белки BETWEEN 10 AND 50; | ||
Продукт | Белки | |
Майонез | 31. | |
Сметана | 26. | |
Молоко | 28. | |
Морковь | 13. | |
Лук | 17. |
Можно задать и NOT BETWEEN (не принадлежит диапазону между), например:
|
Результат: | |||
SELECT Продукт, Белки, Жиры FROM Продукты WHERE Белки NOT BETWEEN 10 AND 50 AND Жиры 100; | |||
Продукт | Белки | Жиры | |
Говядина | 189. | 124. | |
Масло | 60. | 825. | |
Яйца | 127. | 115. |
BETWEEN особенно удобен при работе с данными, задаваемыми интервалами, начало и конец которых расположен в разных столбцах.
Для примера воспользуемся таблицей «минимальных окладов» (табл. 2.4), величина которых непосредственно связана со студенческой стипендией. В этой таблице для текущего значения минимального оклада установлена запредельная дата окончания 9 сентября 9999 года.
Миноклад | Начало | Конец |
2250 | 01-01-1993 | 31-03-1993 |
4275 | 01-04-1993 | 30-06-1993 |
7740 | 01-07-1993 | 30-11-1993 |
14620 | 01-12-1993 | 30-06-1994 |
20500 | 01-07-1994 | 09-09-9999 |
Рисунок 2.4
Если, например, потребовалось узнать, какие изменения минимальных окладов производились в 1993/94 учебном году, то можно выдать запрос
SELECT Начало, Миноклад
FROM Миноклады
WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'
и получить результат:
Начало | Миноклад |
01-12-1993 | 14620 |
01-07-1994 | 20500 |
Отметим, что при формировании запросов значения дат следует заключать в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать из 31 значение 8, а затем 1994.
Для выявления всех значений минимальных окладов, которые существовали в 1993/94 учебном году, можно сформировать запрос
SELECT *
FROM Миноклады
WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'
OR Конец BETWEEN '1-9-1993' AND '31-8-1994'
Миноклад | Начало | Конец |
7740 | 01/07/1993 | 30/11/1993 |
14620 | 01/12/1993 | 30/06/1994 |
20500 | 01/07/1994 | 09/09/9999 |
Наконец, для получения минимального оклада на 15-5-1994:
Результат: | |
SELECT Миноклад FROM Миноклады WHERE '15-05-1994' BETWEEN Начало AND Конец | Миноклад |
14620 |
Использование IN
Выдать сведения о блюдах на основе яиц, крупы и овощей
SELECT *
FROM Блюда
WHERE Основа IN (Яйца Крупа Овощи);
Результат:
БЛ | Блюдо | В | Основа | Выход | Труд |
1 | Салат летний | З | Овощи | 200. | 3 |
3 | Салат витаминный | З | Овощи | 200. | 4 |
16 | Драчена | Г | Яйца | 180. | 4 |
17 | Морковь с рисом | Г | Овощи | 260. | 3 |
19 | Омлет с луком | Г | Яйца | 200. | 5 |
20 | Каша рисовая | Г | Крупа | 210. | 4 |
21 | Пудинг рисовый | Г | Крупа | 160. | 6 |
23 | Помидоры с луком | Г | Овощи | 260. | 4 |
Рассмотренная форма IN является в действительности просто краткой записью последовательности отдельных сравнений, соединенных операторами OR. Предыдущее предложение эквивалентно такому:
SELECT *
FROM Блюда
WHERE Основа=Яйца OR Основа=Крупа OR Основа=Овощи;
Использование LIKE
Выдать перечень салатов
Результат: | ||
SELECT Блюдо FROM Блюда WHERE Блюдо LIKE 'Салат%'; | Блюдо | |
Салат летний | ||
Салат мясной | ||
Салат витаминный | ||
Салат рыбный |
Обычная форма «имя_столбца LIKE текстовая_константа» для столбца текстового типа позволяет отыскать все значения указанного столбца, соответствующие образцу, заданному «текстовой_константой». Символы этой константы интерпретируются следующим образом:
· символ _ (подчеркивание) – заменяет любой одиночный символ,
· символ % (процент) – заменяет любую последовательность из N символов (где N может быть нулем),
· все другие символы означают просто сами себя.
Следовательно, в приведенном примере SELECT будет осуществлять выборку записей из таблицы Блюда, для которых значение в столбце Блюдо начинается сочетанием 'Салат' и содержит любую последовательность из нуля или более символов, следующих за сочетанием 'Салат'. Если бы среди блюд были «Луковый салат», «Фруктовый салат» и т.п., то они не были бы найдены. Для их отыскания надо изменить фразу WHERE:
WHERE Блюдо LIKE '%салат%'
или при отсутствии различий между малыми и большими буквами (такую настройку допускают некоторые СУБД):
WHERE Блюдо LIKE '%Салат%'
Это позволит отыскать все салаты.
Вовлечение неопределенного значения (NULL-значения)
Если при загрузке данных не введено значение в какое-либо поле таблицы, то СУБД поместит в него NULL-значение. Аналогичное значение можно ввести в поле таблицы, выполняя операцию изменения данных. Так, при отсутствии сведений о наличии у поставщиков судака и моркови в столбцы Цена и К_во соответствующих строк таблицы Поставки вводится NULL и там будет храниться код NULL-значения, а не 0, 0. Или пробел. (Отметим, что в распечатке таблицы Поставки в этих местах расположен пробел, установленный в СУБД для представления NULL-значения при выводе на печать).
В этом случае для выявления названий продуктов, отсутствующих в кладовой, шеф-повар может дать запрос
Результат: | ПР | ||
SELECT DISTINCT ПР FROM Наличие WHERE К_во IS NULL; | 2 | ||
Естественно, что для выявления продуктов, существующих в кладовой, следует дать запрос
SELECT DISTINCT ПР
FROM Наличие
WHERE К_во IS NOT NULL;
Использование условий
столбец IS NULL и столбец IS NOT NULL
вместо, например,
столбец = NULL и столбец < NULL
связано с тем, что ничто – и даже само NULL-значение – не считается равным другому NULL-значению. (Несмотря на это, два неопределенных значения рассматриваются, однако, как дубликаты друг друга при исключении дубликатов, и предложение SELECT DISTINCT даст в результате не более одного NULL-значения.)
Выборка с упорядочением
Простейший вариант этой фразы – упорядочение строк результата по значению одного из столбцов с указанием порядка сортировки или без такого указания. (По умолчанию строки будут сортироваться в порядке возрастания значений в указанном столбце.)
Например, выдать перечень продуктов и содержание в них основных веществ в порядке убывания содержания белка
SELECT Продукт, Белки, Жиры, Углев FROM Продукты ORDER BY Белки DESC; | Продукт | Белки | Жиры | Углев |
Судак | 190. | 80. | 0. | |
Говядина | 189. | 124. | 0. | |
Творог | 167. | 90. | 13. | |
Яйца | 127. | 115. | 7. | |
Кофе | 127. | 36. | 9. | |
Мука | 106. | 13. | 732. |
При включении в список ORDER BY нескольких столбцов СУБД сортирует строки результата по значениям первого столбца списка пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по значениям следующего столбца из списка ORDER BY и т.д.
Например, выдать содержимое таблицы Блюда, отсортировав ее строки по видам блюд и основе:
Результат: | ||||||
SELECT * FROM Блюда ORDER BY В Основа; | БЛ | Блюдо | В | Основа | Выход | Труд |
21 | Пудинг рисовый | Г | Крупа | 160. | 6 | |
20 | Каша рисовая | Г | Крупа | 210. | 4 | |
18 | Сырники | Г | Молоко | 220. | 4 | |
... | ||||||
16 | Драчена | Г | Яйца | 180. | 4 | |
28 | Крем творожный | Д | Молоко | 160. | 4 | |
... | ||||||
26 | Яблоки печеные | Д | Фрукты | 160. | 3 | |
7 | Сметана | З | Молоко | 140. | 1 | |
8 | Творог | З | Молоко | 140. | 2 | |
2 | Салат мясной | З | Мясо | 200. | 4 | |
6 | Мясо с гарниром | З | Мясо | 250. | 3 | |
1 | Салат летний | З | Овощи | 200. | 3 | |
... |
Кроме того, в список ORDER BY можно включать не только имя столбца, а его порядковую позицию в перечне SELECT. Благодаря этому возможно упорядочение результатов на основе вычисляемых столбцов, не имеющих имен.
Например, запрос
SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)
FROM Продукты
ORDER BY 2;
позволит получить список продуктов, показанный на рис.2.3,в – переупорядоченный по возрастанию значений калорийности список рис.2.3,а.
Агрегирование данных
SQL-функции
В SQL существует ряд специальных стандартных функций (SQL-функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так:
COUNT
7. число значений в столбце,
SUM
8. сумма значений в столбце,
AVG
9. среднее значение в столбце,
MAX
10. самое большое значение в столбце,
MIN
11. самое малое значение в столбце.
Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.
Следует отметить, что здесь столбец – это столбец виртуальной таблицы, в которой могут содержаться данные не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования и (или) связывания символами арифметических операций значений из одного или нескольких столбцов. При этом выражение, определяющее столбец такой таблицы, может быть сколь угодно сложным, но не должно содержать SQL-функций (вложенность SQL-функций не допускается). Однако из SQL-функций можно составлять любые выражения.
Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Специальная же функция COUNT(*) служит для подсчета всех без исключения строк в таблице (включая дубликаты).
Функции без использования фразы GROUP BY
Если не используется фраза GROUP BY, то в перечень элементов_SELECT можно включать лишь SQL-функции или выражения, содержащие такие функции. Другими словами, нельзя иметь в списке столбцы, не являющихся аргументами SQL-функций.
Например, выдать данные о массе лука (ПР=10), проданного поставщиками, и указать количество этих поставщиков:
Результат: | ||
SELECT SUM(К_во),COUNT(К_во) FROM Поставки WHERE ПР = 10; | ||
SUM(К_во) | COUNT(К_во) | |
220 | 2 |
Если бы для вывода в результат еще и номера продукта был сформирован запрос
SELECT ПР,SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для «свободного» столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой.
Правда, никто не запрещает дать запрос
SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
Результат: | ||
'Кол-во лука =' | SUM(К_во) | COUNT(К_во) |
Кол-во лука = | 220 | 2 |
Отметим также, что в столбце-аргументе перед применением любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент – пустое множество, функция COUNT принимает значение 0, а остальные – NULL.
Например, для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос
SELECT SUM(Цена),AVG(Цена),COUNT(Цена),
COUNT(DISTINCT Цена),COUNT(*)
FROM Поставки
WHERE ПС = 5;
и получить
SUM(Цена) | AVG(Цена) | COUNT(Цена) | COUNT(DISTINCT Цена) | COUNT (*) |
6.2 | 1.24 | 5 | 4 | 7 |
В другом примере, где надо узнать «Сколько поставлено моркови и сколько поставщиков ее поставляют?»:
SELECT SUM(К_во),COUNT(К_во)
FROM Поставки
WHER ПР = 2;
будет получен ответ:
SUM(К_во) | COUNT (К_во) |
-0- | 0 |
Наконец, попробуем получить сумму массы поставленного лука с его средней ценой («Сапоги с яичницей»):
Результат: | |
SELECT (SUM(К_во) +AVG(Цена)) FROM Поставки WHERE ПР = 10; | |
SUM(К_во)+AVG(Цена) | |
220.6 |
Фраза GROUP BY
Мы показали, как можно вычислить массу определенного продукта, поставляемого поставщиками. Предположим, что теперь требуется вычислить общую массу каждого из продуктов, поставляемых в настоящее время поставщиками. Это можно легко сделать с помощью предложения
SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР;
Результат показан на рис. 2.5,а.
а) | б) | в) | г) | |||||||||||||||
ПР | ПС | ПР | Цена | К_во | ПР | ПР | ||||||||||||
9 | 0 | 1 | 9 | -0- | -0- | 1 | 370 | 9 | 0 | |||||||||
11 | 150 | 3 | 9 | -0- | -0- | 2 | 0 | 11 | 150 | |||||||||
12 | 30 | 5 | 9 | -0- | -0- | 3 | 250 | 12 | 30 | |||||||||
15 | 370 | 1 | 11 | 1.50 | 50 | 4 | 100 | 15 | 70 | |||||||||
1 | 370 | 5 | 11 | -0- | -0- | 5 | 170 | 1 | 370 | |||||||||
3 | 250 | 6 | 11 | -0- | -0- | 6 | 220 | 3 | 250 | |||||||||
5 | 170 | 8 | 11 | 1.00 | 100 | 7 | 200 | 5 | 70 | |||||||||
6 | 220 | 1 | 12 | 3.00 | 10 | 8 | 150 | 6 | 140 | |||||||||
8 | 150 | 3 | 12 | 2.50 | 20 | 9 | 0 | 8 | 150 | |||||||||
7 | 200 | 6 | 12 | -0- | -0- | 10 | 220 | 7 | 200 | |||||||||
2 | 0 | 1 | 15 | 2.00 | 170 | 11 | 150 | 2 | 0 | |||||||||
4 | 100 | 3 | 15 | 1.50 | 200 | 12 | 30 | 4 | 100 | |||||||||
13 | 190 | 2 | 1 | 3.60 | 300 | 13 | 190 | 13 | 190 | |||||||||
14 | 70 | 7 | 1 | 4.20 | 70 | 14 | 70 | 14 | 70 | |||||||||
16 | 250 | 2 | 3 | -0- | -0- | 15 | 370 | 16 | 250 | |||||||||
17 | 50 | 7 | 3 | 4.00 | 250 | 16 | 250 | 17 | 50 | |||||||||
10 | 220 |
... | 17 | 50 | 10 | 220 | ||||||||||||
Рисунок 2.5
Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР = 1, в другой – для продукта с ПР = 2 и т.д. (см. рис. 2.5,б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).
Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера (рис. 2.5,в) следует дать запрос
SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР
ORDER BY ПР;
Наконец, отметим, что строки таблицы можно группировать по любой комбинации ее столбцов. Так, по запросу
SELECT Т, БЛ, COUNT(БЛ)
FROM Заказ
GROUP BY Т, БЛ;
можно узнать коды и количество порций блюд, заказанных отдыхающими пансионата (32 человека) на каждую из трапез следующего дня:
Т | БЛ | COUNT(БЛ) |
1 | 3 | 18 |
1 | 6 | 14 |
1 | 19 | 17 |
1 | 21 | 15 |
… |
Использование фразы HAVING
Фраза HAVING (рис. 2.3) играет такую же роль для групп, что и фраза WHERE для строк: она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы.
Например, выдать коды продуктов, поставляемых более чем двумя поставщиками:
SELECT FROM Поставки GROUP BY ПС HAVING COUNT(*) 2; | Результат: | ПР |
9 | ||
11 | ||
12 | ||
Меню
Трапезы
Вид_блюд
Блюда
...
...
...
Рисунок 2.6
Очевидно, что отбор актуальных строк обеспечивается вводом в запрос WHERE фразы, в которой устанавливается соответствие между:
· кодами трапез (Т) в таблицах Меню и Трапезы (Меню.Т = Трапезы.Т),
· кодами видов блюд (В) в таблицах Меню и Вид_блюд (Меню.В = Вид_блюд.В),
· номерами блюд (БЛ) в таблицах Меню и Блюда (Меню.БЛ = Блюда.БЛ).
Такой скорректированный запрос
SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
позволит получить эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда:
Т | В | БЛ | Т | Трапеза | В | Вид | БЛ | Блюдо | В | Основа | Выход | Труд |
1 | З | 3 | 1 | Завтрак | З | Закуска | 3 | Салат витаминный | З | Овощи | 200. | 4 |
1 | З | 6 | 1 | Завтрак | З | Закуска | 6 | Мясо с гарниром | З | Мясо | 250. | 3 |
1 | Г | 19 | 1 | Завтрак | Г | Горячее | 19 | Омлет с луком | Г | Яйца | 200. | 5 |
... | ||||||||||||
3 | Г | 16 | 3 | Ужин | Г | Горячее | 16 | Драче
Состав сооружений: решетки и песколовки: Решетки – это первое устройство в схеме очистных сооружений. Они представляют... Историки об Елизавете Петровне: Елизавета попала между двумя встречными культурными течениями, воспитывалась среди новых европейских веяний и преданий... История создания датчика движения: Первый прибор для обнаружения движения был изобретен немецким физиком Генрихом Герцем... Индивидуальные очистные сооружения: К классу индивидуальных очистных сооружений относят сооружения, пропускная способность которых... © cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста. |