ММИТ Проф. А. К. Иванов-Шиц Базы данных Профессор Алексей Кириллович Иванов-Шиц
ММИТ Проф. А. К. Иванов-Шиц Основы SQL - Structured Query Language - структурированный язык запросов; SQL — это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных язык SQL используется для работы с данными. На языке SQL описываются наборы данных, помогающие получить ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис — это набор правил, позволяющих правильно сочетать элементы языка. Язык SQL в настоящее время является промышленным стандартом, который в большей или меньшей степени поддерживает любая СУБД, претендующая на звание "реляционной". Из истории SQL: В начале 70 -х годов в компании IBM была разработана экспериментальная СУБД System R на основе языка SEQUEL (Structured English Qeury Language - структурированный английский язык запросов), который можно считать непосредственным предшественником SQL. Целью разработки было создание простого непроцедурного языка, которым мог воспользоваться любой пользователь, даже не имеющий навыков программирования. Первый стандарт языка SQL был принят Американским национальным институтом стандартизации (ANSI) в 1987. Дальнейшее развитие языка поставщиками СУБД потребовало принятия в 1992 нового расширенного стандарта (ANSI SQL-92 или просто SQL-2). В настоящее время ведется работа по подготовке третьего стандарта SQL, который должен включать элементы объектоориентрованного доступа к данным. Базы данных Использование SQL Слайд 2
ММИТ Проф. А. К. Иванов-Шиц Основы SQL В SQL определены два подмножества языка: SQL-DDL (Data Definition Language) - язык определения структур и ограничений целостности баз данных. Сюда относятся команды: • создания и удаления баз данных; • создания, изменения и удаления таблиц; • управления пользователями и т. д. SQL-DML (Data Manipulation Language) - язык манипулирования данными: добавление, изменение, удаление и извлечение данных, управления транзакциями. Мы рассмотрим некое подмножество языка, которое дает общее представление о его специфике и возможностях. В то же время, этого подмножества достаточно, чтобы начать самостоятельную работу с любой СУБД. Следует также отметить, что в отличие от "теоретической" терминологии, используемой при описании реляционной модели (отношение, атрибут, кортеж), в литературе при описании SQL часто используется терминология "практическая" (соответственно - таблица, столбец, строка). Базы данных Использование SQL Слайд 3
Проф. А. К. Иванов-Шиц Основы SQL ММИТ Типы данных SQL. 1. Символьные типы данных - содержат буквы, цифры и специальные символы. CHAR или CHAR(n) - символьные строки фиксированной длины. VARCHAR(n) - символьная строка переменной длины. 2. Целые типы данных - поддерживают только целые числа (дробные части и десятичные точки не допускаются). Над этими типами разрешается выполнять арифметические операции и применять к ним агрегирующие функции (определение максимального, минимального, среднего и суммарного значения столбца реляционной таблицы). INTEGER или INT- целое, для хранения которого отводится, как правило, 4 байта. SMALLINT - короткое целое (2 байта) 3. Вещественные типы данных - описывают числа с дробной частью. FLOAT и SMALLFLOAT - числа с плавающей точкой (для хранения отводится обычно 8 и 4 байта соответственно). DECIMAL(p) - тип данных, аналогичный FLOAT с числом значащих цифр p. DECIMAL(p, n) - аналогично предыдущему, p - общее количество десятичных цифр, n - количество цифр после десятичной запятой. 4. Денежные типы данных - описывают, естественно, денежные величины. Если в ваша система такого типа данных не поддерживает, то используйте DECIMAL(p, n). MONEY(p, n) - все аналогично типу DECIMAL(p, n). Вводится только потому, что некоторые СУБД предусматривают для него специальные методы форматирования. 5. Дата и время - используются для хранения даты, времени и их комбинаций. Большинство СУБД умеет определять интервал между двумя датами, а также уменьшать или увеличивать дату на определенное количество времени. DATE - тип данных для хранения даты. TIME - тип данных для хранения времени. INTERVAL - тип данных для хранения временного интервала. DATETIME - тип данных для хранения моментов времени (год + месяц + день + часы + минуты + секунды + доли секунд). 6. Двоичные типы данных - позволяют хранить данные любого объема в двоичном коде (оцифрованные изображения, исполняемые файлы и т. д. ). Определения этих типов наиболее сильно различаются от системы к системе, часто используются ключевые слова: BINARY BYTE Базы данных Использование SQL Слайд 4
Проф. А. К. Иванов-Шиц Основы SQL ММИТ Типы данных SQL. 7. Последовательные типы данных - используются для представления возрастающих числовых последовательностей. SERIAL - тип данных на основе INTEGER, позволяющий сформировать уникальное значение (например, для первичного ключа). При добавлении записи СУБД автоматически присваивает полю данного типа значение, получаемое из возрастающей последовательности целых чисел. В заключение следует сказать, что для всех типов данных имеется общее значение NULL - "не определено". Это значение имеет каждый элемент столбца до тех пор, пока в него не будут введены данные. При создании таблицы можно явно указать СУБД, чтобы выяснить, могут ли элементы того или иного столбца иметь значения NULL (это не допустимо, например, для столбца, являющего первичным ключом). Базы данных Использование SQL Слайд 5
Проф. А. К. Иванов-Шиц ММИТ Основы SQL Инструкции SELECT Инструкция SELECT служит для описания набора данных на языке SQL. Она содержит полное описание набора данных, которые необходимо получить из базы данных, включая следующее: • таблицы, в которых содержатся данные; • связи между данными из разных источников; • поля или вычисления, на основе которых отбираются данные; • условия отбора, которым должны соответствовать данные, включаемые в результат запроса; • необходимость и способ сортировки. Предложения SQL Инструкция SQL состоит из нескольких частей, называемых предложениями. Каждое предложение в инструкции SQL имеет свое назначение. Некоторые предложения являются обязательными. Базы данных Использование SQL Слайд 6
Проф. А. К. Иванов-Шиц ММИТ Основы SQL Термины SQL Каждое предложение SQL состоит из терминов, которые можно сравнить с частями речи. В приведенной ниже таблице указаны типы терминов SQL. ТЕРМИН SQL идентификатор оператор константа выражение Базы данных ОПРЕДЕЛЕНИЕ ПРИМЕР Имя, используемое для Клиенты. [Номер. Телефона] идентификации объекта базы данных, например имя поля. Ключевое слово, которое AS представляет действие или изменяет его. Значение, которое не 42 изменяется, например число или NULL. Сочетание >= Товары. [Цена] идентификаторов, операторов, констант и функций, предназначенное для вычисления одного значения. Использование SQL Слайд 7
Проф. А. К. Иванов-Шиц ММИТ Основы SQL Предложения SQL Инструкция SQL состоит из нескольких частей, называемых предложениями. Каждое предложение в инструкции SQL имеет свое назначение. Некоторые предложения являются обязательными. В приведенной ниже таблице указаны предложения SQL, используемые чаще всего. ПРЕДЛОЖЕНИЕ SQL ОПИСАНИЕ SELECT Определяет поля, которые содержат нужные данные. FROM Определяет таблицы, которые содержат поля, указанные в предложении SELECT. WHERE Определяет условия отбора полей, которым должны соответствовать все записи, включаемые в результаты. ORDER BY Определяет порядок сортировки результатов. GROUP BY В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение. HAVING В инструкции SQL, которая содержит статистические функции, определяет условия, применяемые к полям, для которых в предложении SELECT вычисляется сводное значение. Базы данных Использование SQL ОБЯЗАТЕЛЬНОЕ? Да Да Нет Только при наличии таких полей Нет Слайд 8
ММИТ Проф. А. К. Иванов-Шиц Основы SQL SELECT – этим ключевым словом начинается оператор SQL, извлекающий данные из таблиц. После слова SELECT (Выбрать) расположены имена столбцов результирующего набора записей и директивы, определяющие, какие данные и каким образом нужно извлечь из таблиц FROM - Директива FROM определяет, из каких столбцов таблицы должны быть извлечены данные; эта директива обязательно должна присутствовать в каждом операторе SELECT, другие директивы необязательные; если данные извлекаются из нескольких таблиц, они должны быть объединены ключевым словом JOIN, сообщающим драйверу Access, как должны быть связаны извлекаемые данные WHERE - В директиве WHERE задаются условия фильтрации записей, извлеченных из таблиц; эта директива необязательная; если записи не фильтруются, директиву WHERE можно не включать в оператор SELECT Директива ORDER BY задает способ сортировки результирующих записей; если директива ORDER BY отсутствует, записи возвращаются в неупорядоченной, непредсказуемой последовательности С помощью приведенных выше четырех базовых ключевых слов можно создавать операторы SQL для использования в формах и отчетах. Согласно общепринятому соглашению, ключевые слова SQL вводятся буквами верхнего регистра (однако драйвер Access не чувствителен к регистру букв). Оператор SQL может занимать много строк. Согласно правилам синтаксиса SQL, его можно разместить в одной строке, для драйвера Access это не имеет значения, однако оператор не будет виден в окне одновременно и с ним будет неудобно работать. Базы данных Использование SQL Слайд 9
ММИТ Проф. А. К. Иванов-Шиц Основы SQL Оператор SELECT имеет следующую структуру: SELECT [DISTINCT | DISTINCTROW | ALL] select_expression, . . . [FROM table_references] [WHERE where_definition] [GROUP BY {unsigned_integer | col_name | formula}] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], . . . ] Базы данных Использование SQL Слайд 10
ММИТ Проф. А. К. Иванов-Шиц Основы SQL Ключевое СЛОВО SELECT Оператор SELECT используется в запросах двух типов: извлекающих данные из таблиц и создающих результирующую таблицу запроса. Часто обе эти задачи решаются в одном запросе. В операторе SELECT задаются поля, выводимые в результирующем наборе. После ключевого слова SELECT нужно перечислить столбцы, которые должны выведены запросом. Синтаксис директивы SELECT имеет следующий вид: SELECT столбец 1, столбец 2, столбец 3 [, . . . ] Через запятые перечислены имена столбцов, извлеченные из таблиц и специфицированные именами таблиц. В оператор SELECT можно включить произвольное количество столбцов. Базы данных Использование SQL Слайд 11
Проф. А. К. Иванов-Шиц ММИТ Основы SQL Если столбцы извлекаются из нескольких таблиц, их имена должны быть специфицированы именами таблиц, в которых они расположены. Например, если оператор SELECT извлекает поля из таблиц Контакты и Продажи, то поля задаются следующим образом SELECT Контакты. Компании, Контакты. Город, Продажи. Даты. Продаж, Продажи. Номер. Счета Точка между именами — это оператор, обозначающий позицию, в которой заканчивается одно имя и начинается другое. В данном примере точка разделяет имена таблицы и столбца. В других местах Access точка может разделять другие сущности, например имя таблицы и ее свойство или имя элемента управления и его свойство. При создании запроса в режиме конструктора драйвер Access автоматически добавляет имя таблицы перед именем столбца. Фактически имя таблицы не обязательное. Обязательным оно является, только если в одном операторе SQL несколько столбцов, принадлежащих разным таблицам, имеют одинаковые имена. Например, столбцы Номер. Счета могут присутствовать в таблицах Продажи и Элементы. Продаж. В операторе SELECT нужно каким-либо образом указать, из какого столбца следует извлекать данные; для этого имя столбца специфицируется именем таблицы. Базы данных Использование SQL Слайд 12
Проф. А. К. Иванов-Шиц ММИТ Основы SQL Если имя поля содержит пробелы, оно обязательно должно быть заключено в скобки. Без них синтаксический анализатор SQL не сможет понять, на какое поле ссылается оператор SQL. Если имя поля не содержит пробелы, квадратные скобки не обязательны. Драйвер Access может добавить их автоматически, однако фактически они в этом случае не играют никакой роли. В квадратные скобки должно быть заключено только имя поля (Моя. Таблица. [Мое. Имя. Поля]), а не имя поля и таблицы ([Моя. Таблица. Мое. Имя. Поля]). Представляйте себе квадратные скобки как маркеры, отмечающие начало и конец имени. Если имена полей не дублируются в операторе SQL, специфицировать их именами таблиц не обязательно, однако рекомендуется всегда делать это, поскольку при первом взгляде на оператор SQL будет понятно, каким таблицам принадлежат поля. Метасимвол * используется для выбора всех полей таблицы. Если все столбцы нужно выбрать из нескольких таблиц, звездочки нужно разместить после точек: SELECT Контакты. *, Продажи. * Использовать звездочку для вывода всех полей таблицы не рекомендуется. В этом случае запрос будет выполняться медленно и займет много места по горизонтали. Извлекайте только необходимые данные. Лишние данные загружают каналы связи и уменьшают быстродействие приложения. Учитывайте, что запрос извлекает из базы данных всю информацию, заданную в операторе SQL, независимо от того, используется ли она в форме или в отчете, спроектированном на основе запроса. Базы данных Использование SQL Слайд 13
ММИТ Проф. А. К. Иванов-Шиц Предикаты оператора SELECT В операторе SELECT можно задавать следующие предикаты: ALL; DISTINCTROW; TOP. Предикат ограничивает количество возвращаемых записей. В операторе SQL он работает совместно с директивой WHERE. Предикат ALL задает выбор всех записей, удовлетворяющих условиям директивы WHERE. По умолчанию предикат ALL считается присутствующим. Если в оператор SQL не добавить ключевое слово ALL, то все записи, выбранные директивой WHERE, будут возвращены запросом. Предикат DISTINCT задает отбрасывание записей, содержащих дублированные данные в полях, указанных в операторе SELECT. Предположим, что запрос должен вернуть категории купленных продуктов и название компании, не детализируя количество продуктов по каждой категории. Оператор SELECT имеет следующий вид: SELECT DISTINCT Контакты. Компании, Продукты. Категории FROM (Контакты INNER JOIN Продажи ON Контакты. IDКонтакты = Продажи. Покупатель) INNER JOIN (Продукты INNER JOIN Продажи. По_пунктам ON Продажи. IDПродаж = Продажи. По_пунктам. IDПродукта) ON Продажи. Номер. Счета = Продажи. По_пунктам. Номер. Счета ORDER BY Контакты. Компании; Базы данных Использование SQL Слайд 14
ММИТ Проф. А. К. Иванов-Шиц Предикаты оператора SELECT Рассмотрим первую строку запроса, игнорируя остальные строки. После директивы SELECT расположен предикат DISTINCT, существенно влияющий на выбор возвращаемых записей. DISTINCT сообщает драйверу Access о том, что, если значения выбранных полей (например, категория продукта или имя компании) дублируются, запрос должен вернуть только одну запись. Другие поля нижележащих записей могут быть разными. Следовательно, даже если в таблице Продажи. По_пунктам есть две разные записи, возвращена будет только одна. Предикат DISTINCT устраняет дублирование полей, выбранных запросом. SELECT DISTINCT Контакты. Компании, Продукты. Категории FROM (Контакты INNER JOIN Продажи ON Контакты. IDКонтакты = Продажи. Покупатель) INNER JOIN (Продукты INNER JHOIN Продажи. По_пунктам ON Продажи. IDПродаж = Продажи. По_пунктам. IDПродукта) ON Продажи. Номер. Счета = Продажи. По_пунктам. Номер. Счета ORDER BY Контакты. Компании; Для добавления предиката DISTINCT в запрос нужно присвоить свойству Уникальные значения (Unique Values) запроса значение Да. Для этого откройте запрос в режиме конструктора, щелкните правой кнопкой мыши на верхней панели запроса, на которой расположены окна таблиц, и выберите в контекстном меню команду Свойства (Properties). В открывшемся окне свойств щелкните в поле значений свойства Уникальные значения и выберите в раскрывающемся списке элемент Да. Предикат DISTINCT будет автоматически добавлен в код SQL. Базы данных Использование SQL Слайд 15
ММИТ Проф. А. К. Иванов-Шиц Предикаты оператора SELECT Запрос, содержащий предикат DISTINCT, возвращает необновляемые записи. Каждая запись, возвращенная запросом, представляет одну или большее количество записей, хранящихся в нижележащей таблице. Запрос не содержит указаний для драйвера Access, какие записи в таблицах нужно обновлять при изменении записи в результирующем наборе. Следовательно, запрос, содержащий предикат DISTINCT, возвращает данные в режиме только для чтения. Предикат DISTINCTROW используется только в Access, но не в базах данных других типов. Он похож на предикат DISTINCT за одним большим исключением: он выявляет дубликаты на основе всех полей таблицы (таблиц), лежащей в основе запроса, а не только полей, выбранных запросом. Базы данных Использование SQL Слайд 16
Проф. А. К. Иванов-Шиц Предикаты оператора SELECT ММИТ Предикат TOP ограничивает количество возвращаемых записей. Обычно предикат TOP используется совместно с директивой сортировки ORDER BY (иначе будут возвращены случайные записи, а не наиболее подходящие), например SELECT TOP 5 Компания FROM Контакты ORDER BY Последние. Продажи. Дата DESC Этот запрос возвращает пять компаний, имеющих наиболее поздние даты покупки. Сначала запрос извлекает все компании, потом сортирует их по датам покупки в порядке убывания, а затем выбирает пять первых записей. Поскольку записи отсортированы, отобраны будут компании, с которыми ассоциированы наиболее поздние даты покупки. С предикатом TOP может использоваться необязательное ключевое слово PERCENT. Оно задает отбор не определенного количества записей, а процента от всех записей. Например, вывести 10% клиентов, совершивших покупки в самое последнее время, можно с помощью запроса SELECT TOP 10 PERCENT Компания FROM Контакты ORDER BY Последние. Продажи. Дата DESC; Базы данных Использование SQL Слайд 17
Проф. А. К. Иванов-Шиц Директива FROM ММИТ В директиве FROM задаются таблицы или запросы, содержащие поля, перечисленные в операторе SELECT. Директива FROM является обязательной. Она сообщает оператору SQL, где искать записи. Если не включить директиву FROM в оператор SQL, будет возвращено сообщение об ошибке. Поскольку директива FROM обязательная, оператор SELECT иногда называют оператором SELECT. . . FROM. При работе с одной таблицей директива FROM всего лишь определяет ее имя: SELECT Компания, Город FROM Контакты Если в запросе используется несколько таблиц, то в директиве FROM можно задать табличное выражение, определяющее способ извлечения данных из многих таблиц. В директиве FROM устанавливаются отношения между двумя или большим количеством таблиц, используемых в операторе SELECT. Табличное выражение может быть одного из трех типов. INNER JOIN. . . ON RIGHT JOIN. . . ON LEFT JOIN. . . ON Директива INNER JOIN . . . ON (Внутреннее объединение) создает объединение по равенству. При ее использовании две таблицы объединяются на основе полей, которые в обеих таблицах имеют одинаковые значения. Базы данных Использование SQL Слайд 18
Проф. А. К. Иванов-Шиц Директива FROM ММИТ Предположим, что таблицы Контакты и Продажи содержат ассоциированные поля, идентифицирующие покупателей. Тогда объединить таблицы клиентов и продаж можно с помощью следующего оператора: SELECT Продажи. Дата. Счета, Продажи. Номер. Счета, Контакты. Компания FROM Контакты INNER JOIN Продажи ON Контакты. IDКонтакты=Продажи. Покупатель; В этом примере директива FROM задает использование таблиц Контакты и Продажи, связанных объединением INNER JOIN. Директива ON задает поля (IDКонтакты в таблице Контакты и Покупатель в таблице Продажи), используемые для объединения таблиц В случае объединения INNER JOIN задание таблиц в директиве FROM фактически не играет никакой роли. Запись выбирается, только если значения существуют на обеих сторонах объединения, поэтому запрос извлекает данные из обеих таблиц, независимо от того, какие таблицы заданы в директиве FROM. Базы данных Использование SQL Слайд 19
Проф. А. К. Иванов-Шиц Директива FROM ММИТ Директивы LEFT JOIN (Левое объединение) и RIGHT JOIN (Правое объединение) аналогичны директиве INNER JOIN (Внутреннее объединение) за исключением того, что в этом случае создается не внутреннее объединение, а внешнее. Внешнее объединение используется, когда нужно возвратить запись родительской таблицы, даже если дочерняя таблица не содержит записей со значениями родительской таблицы, заданных в директиве ON. В приведенном ниже запросе используется внешнее объединение. SELECT Продажи. Номер. Счета, Контакты. Компания FROM Контакты LEFT JOIN Продажи ON Контакты. IDКонтакты=Продажи. Покупатель; Запрос возвращает названия компаний и номера счетов, ассоциированные с покупками. Если запрос не находит для компании записи в таблице Продажи, компания все равно выводится в результирующий набор. В этом случае поле Номер. Счета остается пустым, поскольку компания еще не купила ничего. Базы данных Использование SQL Слайд 20
Проф. А. К. Иванов-Шиц ММИТ Директива FROM Если нужно вывести только клиентов, ничего пока что не купивших, добавьте в конструкцию запроса простой фильтр, задающий выбор только записей, в которых поле Invoice. Number содержит значение Null. SELECT Продажи. Номер. Счета, Контакты. Компания FROM Контакты LEFT JOIN Продажи ON Контакты. IDКонтакты=Продажи. Покупатель WHERE Продажи. Номер. Счета IS NULL; В этом примере включать поле Номер. Счета в результат запроса не обязательно, поскольку оно всегда будет пустым. Однако в таких случаях его иногда все же включают в результат запроса, чтобы сделать его нагляднее. Базы данных Использование SQL Слайд 21
Проф. А. К. Иванов-Шиц Директива WHERE ММИТ Директива WHERE используется в операторе SQL, только если нужно задать условие выбора записей. В отличие от директив SELECT и FROM она необязательная. В операторе SQL, показанном ниже, используется следующая директива WHERE (Контакты. Город= «NY» ) В качестве условия в директиве WHERE можно применить любое правильное булево выражение, принимающее значение True или False. В нем могут использоваться значения одного столбца, как в предыдущем примере, или сложные критерии на основе нескольких выражений. Базы данных Использование SQL Слайд 22
Проф. А. К. Иванов-Шиц Директива ORDER BY ММИТ Эта директива задает сортировку возвращаемых записей. Записи сортируются по значениям полей (или одного поля), заданных после ключевых слов ORDER BY. В примере, показанном ниже, записи сортируются по значениям трех полей. ORDER BY tbl. Sales. Line. Items. Description, tbl. Contacts. State, tbl. Contacts. Company; В директиве ORDER BY не обязательно задавать те же поля, что и в директиве SELECT (т. е. выводимые в результат запроса). Сортировать записи можно по значениям любых полей таблиц, заданных в директиве FROM. Завершение оператора SQL Оператор SQL может содержать до 64 000 символов. Конец оператора SQL обозначается точкой с запятой (; ). Точка с запятой в конце оператора SQL не обязательна. Если не добавить ее в конец оператора, драйвер Access предположит, что она там находится, и выполнит оператор. Однако, если ошибочно вставить точку с запятой в операторе, Access возвратит сообщение об ошибке и попытается указать пользователю, где она произошла. Базы данных Использование SQL Слайд 23
ММИТ Проф. А. К. Иванов-Шиц Примеры. Рассмотрим 3 отношения (эти отношения даны в материалах к занятиям Таблицы_Лекция 8. doc) Создадим 3 отношения в своей новой базе данных SQL_Студент_Ваша. Фамилия). ( Новую базуможно создать с помощью мастеров, а можно, тем, кто умеет, создать с помощью DDL: Операторов создания схемы базы данных) Запись Название. Предмета Порядковый. Номер 150 БА 200 1 400 БА 200 2 450 БА 200 3 Создадим (новый) запрос через конструктор и перейдем в нем через правую кнопку в режим Студент SQL. 100 БД 445 1 200 БД 445 2 400 БФ 410 1 Номер. Сту дента 300 СС 150 1 200 СС 250 1 400 СС 250 2 Фамилия Специальность Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС 250 Голубев история С 4 Номер. Студента Занятия Название. Предмета Время Аудитория БД 445 ПСЯ 3 С 213 БФ 410 ПСЯ 8 С 213 300 Баранов бухучет С 4 350 Родионов математика С 3 ВА 200 П-Я 9 С 110 400 Рытов Бухучет С 1 СС 150 ПСЯ 3 Е 304 450 Данилов история С 4 СС 250 ПСЯ 1 Е 210 Базы данных Использование SQL Слайд 24
ММИТ Проф. А. К. Иванов-Шиц Сделаем операцию проекции столбца Специальность из таблицы Студент SELECT Специальность FROM Студент; (не забывать про ; ) (компилятор не требует заглавных букв и многострочная запись не обязательна) Студент Номер Студен та Запрос1 Специальность Фамилия Специально сть Курс 100 Данилов история АС бухучет 150 Петров бухучет С 2 история 200 Баранов математика АС история 250 Голубев история С 4 история 300 Баранов бухучет С 4 бухучет 350 Родионов математика С 3 400 Рытов Бухучет С 1 450 Данилов история С 4 В результате получим таблицу, в которой есть повторяющиеся строки. Как убрать повторы, изменив запрос? математика Бухучет SELECT DISTINCT Специальность FROM Студент; Запрос2 Специальность бухучет история математика Базы данных Использование SQL Слайд 25
Проф. А. К. Иванов-Шиц ММИТ Операция выборки SELECT … FROM…WHERE Найти всех студентов, изучающих Математику SELECT * FROM Студент WHERE Специальность = ‘Математика’ (звездочка означает, что нужно получить все столбцы таблицы) Студент Номер Студен та Выборка_1 Фамилия Специально сть Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС 250 Голубев история С 4 300 Баранов бухучет С 4 350 Родионов математика С 3 400 Рытов Бухучет С 1 450 Данилов история С 4 Номер. Ст удента Фамилия Специальность Курс 200 Баранов математика АС 350 Родионов математика С 3 Выборка_2 Найти всех студентов, изучающих Математику и узнать, на каком курсе они учатся SELECT Фамилия, Курс FROM Студент WHERE (((Студент. Специальность)='Математика')); Базы данных Использование SQL Фамилия Курс Баранов АС Родионов С 3 Слайд 26
Проф. А. К. Иванов-Шиц ММИТ Операция выборки SELECT … FROM…WHERE Найти всех студентов, изучающих Математику и обучающихся на курсе АС SELECT Фамилия, Курс FROM Студент WHERE (((Студент. Специальность)='Математика' And Курс='АС')); Студент Номер Студен та Фамилия Специально сть Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС Фамилия Курс 250 Голубев история С 4 Баранов АС 300 Баранов бухучет С 4 350 Родионов математика С 3 400 Рытов Бухучет С 1 450 Данилов история С 4 Найти фамилии студентов, имеющих номера между 200 и 300 и указать, какие курсы они изучают SELECT Фамилия, Специальность FROM Студент WHERE Номер. Студента>=200 And Номер. Студента<=300; (можно использовать WHERE Номер. Студента BETWEEN 200 AND 300) Использование SQL Базы данных Выборка_3 Выборка_4 Фамилия Специальность Баранов математика Баранов бухучет Голубев история Слайд 27
ММИТ Проф. А. К. Иванов-Шиц Операция выборки SELECT … FROM…WHERE Для выборки по шаблону используется оператор LIKE. Символ ? (или _ ) представляет произвольный одиночный символ. Символ * (или %) представляет последовательность из одного или более произвольных символов. Найти всех студентов, учащихся на курсе, название которого начинается с буквы С SELECT Фамилия, Курс FROM Студент WHERE Курс LIKE 'С? '; Студент Номер Студен та Выборка_5 Фамилия Специально сть Курс 100 Данилов история АС Фамилия Курс 150 Петров бухучет С 2 Баранов С 4 200 Баранов математика АС Голубев С 4 250 Голубев история С 4 Данилов С 4 300 Баранов бухучет С 4 Петров С 2 350 Родионов математика С 3 Родионов С 3 400 Рытов Бухучет С 1 Рытов С 1 450 Данилов история С 4 Базы данных Использование SQL Слайд 28
ММИТ Проф. А. К. Иванов-Шиц Операция выборки SELECT … FROM…WHERE Для выборки по шаблону используется оператор LIKE. Символ ? (для Access) (или _ ) представляет произвольный одиночный символ. Символ * (для Access) (или %) представляет последовательность из одного или более произвольных символов. Найти всех студентов, фамилии которых заканчиваются на «ев» SELECT Фамилия FROM Студент WHERE Фамилия LIKE '*ев'; Студент Номер Студен та Фамилия Специально сть Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС 250 Голубев история С 4 300 Баранов бухучет С 4 Фамилия 350 Родионов математика С 3 Голубев 400 Рытов Бухучет С 1 450 Данилов история С 4 Базы данных Выборка_6 Использование SQL Слайд 29
ММИТ Проф. А. К. Иванов-Шиц Операция сортировки ORDER BY Перечислить в алфавитном порядке студентов, специализирующихся на бухучете. SELECT Фамилия, Специальность, Курс FROM Студент WHERE Специальность='Бухучет‘ ORDER BY Фамилия; Студент Номер Студен та Фамилия Специально сть Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС 250 Голубев история С 4 Фамилия Специальность Курс 300 Баранов бухучет С 4 350 Родионов математика С 3 400 Рытов Бухучет С 1 Петров бухучет С 2 450 Данилов история С 4 Рытов Бухучет С 1 Базы данных Сортировка_1 Использование SQL Слайд 30
ММИТ Проф. А. К. Иванов-Шиц Операция сортировки ORDER BY Перечислить в алфавитном порядке студентов, специализирующихся на бухучете и расставленных по возрастанию курса SELECT Фамилия, Специальность, Курс FROM Студент WHERE Специальность='Бухучет‘ ORDER BY Фамилия DESC Студент Номер Студен та Фамилия Специально сть Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС 250 Голубев история С 4 300 Баранов бухучет С 4 350 Родионов математика 400 Рытов 450 Данилов Базы данных Сортировка_2 Фамилия Специальность Курс Рытов Бухучет С 1 С 3 Петров бухучет С 2 Бухучет С 1 Баранов бухучет С 4 история С 4 Использование SQL Слайд 31
ММИТ Проф. А. К. Иванов-Шиц Встроенные функции SQL COUNT - вычисляет количество строк в таблице, SUM – подсчитывает количество числовых столбцов, AVG , MAX и MIN работают с числовыми столбцами. Найти общее количество специальностей в таблице SELECT COUNT ( Специальность) FROM Студент; Студент Номер Студен та Фамилия Специальность Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС 250 Голубев история С 4 300 Баранов бухучет С 4 350 Родионов математика С 3 400 Рытов Бухучет С 1 450 Данилов история С 4 Базы данных Функции Expr 1000 8 Использование SQL Слайд 32
ММИТ Проф. А. К. Иванов-Шиц Встроенные функции и группировка GROUP BY Найти количество студентов по каждой специальности SELECT Специальность, COUNT(Фамилия) FROM Студент GROUP BY Специальность Студент Номер Студен та Фамилия Специально сть Курс 100 Данилов история АС 150 Петров бухучет С 2 200 Баранов математика АС 250 Голубев история С 4 300 Баранов бухучет С 4 350 Родионов математика С 3 400 Рытов Бухучет С 1 450 Данилов история С 4 Базы данных Использование SQL Слайд 33
ММИТ Проф. А. К. Иванов-Шиц ЗАПРОС из НЕСКОЛЬКИХ ТАБЛИЦ Хотим узнать фамилии студентов , записанных на предмет под шифром БД 445 Запись Порядковый. Номер 150 БА 200 1 БА 200 2 450 БА 200 3 100 Номер. Сту дента Название. Предмета 400 Студент Номер. Студента БД 445 1 200 БД 445 2 400 БФ 410 1 300 СС 150 1 Фамилия Специальность Курс 100 Данилов история АС 150 Петров бухучет С 2 200 СС 250 1 200 Баранов математика АС 400 СС 250 2 250 Голубев история С 4 300 Баранов бухучет С 4 350 Родионов математика С 3 Занятия Бухучет С 1 450 Данилов история С 4 Базы данных Аудитория БД 445 ПСЯ 3 С 213 ПСЯ 8 С 213 ВА 200 П-Я 9 С 110 СС 150 ПСЯ 3 Е 304 СС 250 Рытов Время БФ 410 400 Название. Предмета ПСЯ 1 Е 210 Использование SQL Слайд 34
Проф. А. К. Иванов-Шиц ММИТ ЗАПРОС из НЕСКОЛЬКИХ ТАБЛИЦ Хотим узнать фамилии студентов , записанных на предмет под шифром БД 445 Сначала можно узнать номера студентов, записанных на предмет SELECT Номер. Студента FROM Запись WHERE Название. Предмета='БД 445‘ Вложенный. Запрос_1 Номер. Студента 100 200 Базы данных Хотим узнать фамилии студентов , записанных на предмет под шифром БД 445 Теперь SELECT Фамилия FROM Студент WHERE Номер. Студента IN (SELECT Номер. Студента FROM Запись WHERE Название. Предмета='БД 445‘) Использование SQL Слайд 35
ММИТ Проф. А. К. Иванов-Шиц ЗАПРОС из НЕСКОЛЬКИХ ТАБЛИЦ (соединение) Хотим узнать номера и фамилии студентов , и какие предметы (шифры) они изучают. Применяем более сложное соединение. SELECT Студент. Номер. Студента, Студент. Фамилия, Запись. Название. Предмета FROM Студент, Запись WHERE Студент. Номер. Студента =Запись. Номер. Студента; Соединение_1 Номер. Студента Фамилия Название. Предмета БД 445 150 Петров БА 200 Баранов БД 445 200 Баранов СС 250 300 Баранов СС 150 400 Рытов БА 200 400 Рытов БФ 410 400 Рытов СС 250 450 Данилов Базы данных 100 Данилов БА 200 Использование SQL Слайд 36
ММИТ Проф. А. К. Иванов-Шиц Создание сложных запросов Вычисляемые поля В запросе можно создавать поля, содержащие вычисляемые значения Это можно сделать: 1. Конкатенацией двух (и более) полей текстового типа с помощью символов амперсанта (&) 2. Вычисляя числовые значения с помощью формул 3. Используя встроенные и пользовательские функции Пример Создайте вычисляемый столбец Цена после скидки на основе знаний столбца Прейскурант, хранящихся в таблице Книги_Издание. Базы данных Использование SQL Слайд 37
Проф. А. К. Иванов-Шиц ММИТ Создание сложных запросов Вычисляемые поля Для этого: 1. Откройте базу данных Lection_8 2. Активизируйте вкладку Создание. В разделе Другие щелкните на кнопке Мастер запросов. 3. Для таблицы Книги_Издание, выберите поля ISBN и Прейскурант. Щелкните по кнопке Далее 4. Установите переключатель подробный и щелкните на кнопке Далее. 5. Установите переключатель Изменить макет запроса и щелкните на кнопке Готово. 6. В решетке запроса щелкните в новом поле и введите выражение Цена_после_скидки: Прейскурант*0, 75 В новом столбце будет выведена цена с учетом скидки (т. е. цена, уменьшенная на 25%. ) Вводить имя таблицы перед именем каждого поля не обязательно, поскольку в запросе используется только одна таблица. Однако для большей наглядности структуры запроса рекомендуется вводить имена таблиц, поэтому введите выражение Цена_после_скидки: Книги_Издание. Прейскурант*0, 75 7. На ленте щелкните на кнопке Выполнить. На языке SQL SELECT Книги_Издание. [ISBN], Книги_Издание. [Прейскурант], [Прейскурант]*0. 75 AS Цена_после_скидки FROM Книги_Издание; Базы данных Использование SQL Слайд 38
ММИТ Проф. А. К. Иванов-Шиц Создание сложных запросов В среду Access встроен построитель выражений, облегчающий создание сложных выражений для форм, отчетов и вычисляемых столбцов запросов. Чтобы применить построитель выражений, выполните следующие действия. 1. Переключите запрос, созданный в предыдущем упражнении, в режим конструктора. 2. Откройте окно построителя выражений. Для этого щелкните правой кнопкой мыши в пустой верхней ячейке решетки справа от выражения Цена_после_скидки. 3. Выберите в контекстном меню команду Построить. Откроется диалоговое окно Построитель выражений. 4. На левой панели построителя разверните узел Функции. Щелкните в узле Встроенные функции. 5. На правой панели построителя найдите функцию IIf и дважды щелкните на ее имени. Функция появится на верхней панели. Встроенная условная функция IIf принимает три параметра: IIf (условие, выражение 1, выражение 2). Если условие равно True, выполняется формула выражение 1, в противном случае выполняется выражение 2. Функция возвращает результат выполненного выражения. 6. С помощью функций IIf и Is. Null создайте выражение Iif(Is. Null([Страницы]); [Прейскурант]*0, 75*([Страницы]/500)) Щелкните на кнопке ОК. Выражение будет вставлено в ячейку решетки. Базы данных Использование SQL Слайд 39
ММИТ Проф. А. К. Иванов-Шиц Создание сложных запросов n Встроенная функция Is. Null выясняет, содержит ли поле (или переменная) какое-либо значение. Если поле пустое (т. е. содержит Null), функция Is. Null возвращает значение True, в противном случае — False. Обычно в базах данных функция Is. Null используется для устранения пустых полей. Если в любом месте выражения встречается значение Null , результат тоже равен Null , независимо от других значений, входящих в выражение. Для устранения этого эффекта нужно проверять входные значения выражений с помощью функции Is. Null. 7. На ленте выберите команду Вид->Режим SQL. Запрос будет выведен в режиме SQL. Оператор SQL должен иметь вид: SELECT Книги_Издание. [ISBN], Книги_Издание. [Прейскурант], [Книги_Издание]. [Прейскурант]*0. 75 AS Цена_после_скидки, IIf (Is. Null ([Страницы]), [Прейскурант]*0. 75, n [Прейскурант]*0. 75* ([Страницы]/500)) AS Выражение 1 FROM Книги_Издание; Если поле Страницы содержит значение Null, то использовать его в выражении нельзя. В этом случае нужно взять значение из таблицы Книги_Издание. Чтобы визуально проконтролировать значения Страницы, переключите запрос в режим конструктора и добавьте в результирующий набор запроса столбец Страницы. 8. Переключите запрос в режим таблицы. Базы данных Использование SQL Слайд 40
ММИТ Проф. А. К. Иванов-Шиц Вычисление количества записей в таблице или запросе Для выяснения общего количества записей в существующей таблице или результирующем наборе запроса используется встроенная функция Count (*) (после слова – пробел (!? )) . Звездочка — это специальный параметр функции. Чтобы выяснить общее количество записей, хранящихся в таблице Книги_Издание, выполните следующие действия. 1. Создайте новый запрос на основе таблицы Книги_Издание. 2. Переключите запрос в режим конструктора. Щелкните в ячейке Поле первого столбца. 3. Введите в ячейке выражение Count (*). Переключите запрос в режим таблицы. Запрос должен возвратить значение 17, поскольку таблица Книги_Издание содержит 17 записей (если, экспериментируя с таблицей, вы не изменили количество записей). Базы данных Использование SQL Слайд 41
ММИТ Проф. А. К. Иванов-Шиц Вычисление количества записей в таблице или запросе Функцию Count можно использовать для подсчета количества записей, удовлетворяющих заданному критерию. Предположим, что нужно подсчитать, сколько книг, упомянутых в таблице Книги_Издание, имеют количество страниц больше 300. Для этого выполните следующие действия. 1. Создайте новый запрос на основе таблицы Книги_Издание. 2. В режиме конструктора щелкните в пустой ячейке Поле. 3. Введите в ячейку выражение Count (*). 4. На верхней панели конструктора дважды щелкните на имени поля Страницы, чтобы добавить его в запрос. 5. В столбце Страницы снимите флажок Вывод на экран. (Это нужно потому, что в результирующий набор запроса должен быть выведен только столбец, содержащий функцию Count (*), поскольку запрос выводит обобщенное значение, а не индивидуальные записи. Если попытаться вывести еще какой-либо столбец, Access возвратит сообщение об ошибке). 6. В ячейку Условие Отбора столбца Страницы введите выражение >300. 7. Вместо слова Выражение 1 введите фразу Количество книг объемом более 300 страниц. 8. Выполните запрос. Запрос должен вернуть число 10. Вручную подсчитайте в таблице Книги_Издание количество книг, содержащих более 300 страниц, чтобы убедиться в том, что запрос выполнен правильно. Базы данных Использование SQL Слайд 42
ММИТ Проф. А. К. Иванов-Шиц Вывод первых N записей В среде Access можно создать запрос, находящий по заданному критерию и возвращающий первые N записей. Кроме того, запрос может возвращать заданный процент первых записей, например первые 5% записей. Предположим, что в таблице Книги_Издание нужно отсортировать книги по названиям и вывести первые 10 книг (названия книг приведены в столбце Названия таблицы Книги_Публикация). Для решения этой задачи выполните следующие действия. 1. Создайте новый запрос на основе таблиц Книги_Издание и Книги_Публикация. Эти две таблицы должны быть объединены на основе значений столбца Публикация_ИД. (Обратите внимание на использование термина "публикация" в английском языке и, соответственно, в именах столбцов. Публикацией считается одно название. Одной публикации могут соответствовать несколько книг, изданных разными предприятиями на разных носителях - например, в твердом переплете, в мягкой обложке, на компакт-диске). В режиме конструктора установите отношение между таблицами на основе значений Публикация_ИД. Для этого наведите указатель на имя поля Публикация_ИД в одной таблице и перетащите его на это же поле в другой таблице. 3. Щелкните правой кнопкой мыши на линии, обозначающей отношение. 4. Выберите в контекстном меню команду Параметры объединения. Откроется диалоговое окно Параметры объединения, в котором видно, что две таблицы объединены на основе общего поля Публикация_ИД. Щелкнув на кнопке Режим SQL, выведите код SQL запроса, приведенный ниже. 2. Базы данных Использование SQL Слайд 43
ММИТ Проф. А. К. Иванов-Шиц Вывод первых N записей Здесь полужирным шрифтом выделены параметры объединения. SELECT FROM Книги_Издание INNER JOIN Книги_Публикация ON Книги_Издание. Публикация_ИД= Книги_Публикация_ИД; 5. Добавьте в запрос столбцы Название, ISBN, Печать_Дата и ФОРМАТ. Задайте сортировку записей по возрастанию на основе значений столбца Название. 6. Щелкните на кнопке Режим таблицы (Table View), чтобы увидеть результирующий набор запроса. Как видите, запрос возвращает 17 записей. 7. Переключите запрос в режим конструктора. 8. Задайте вывод первых 10 записей. Для этого в разделе Настройка запроса (Query Setup) ленты наведите указатель на раскрывающийся список Возврат (Return). Через секунду появится всплывающая подсказка, сообщающая о назначении раскрывающегося списка Дважды щелкните на списке и вместо слова Все введите значение 10. 9. Если щелкнуть на стрелочке, будет выведен список значений, включая значения в процентах. Можете выбрать один из элементов списка или вручную ввести нужное значение. Выполните запрос. Запрос возвратит первые 10 записей из 17. Если в раскрывающемся списке Возврат выбрать значение 5, запрос все равно возвратит 10 записей. Это объясняется тем, что запрос возвращает первые 5 уникальных записей плюс все дублированные. Базы данных Использование SQL Слайд 44
ММИТ Проф. А. К. Иванов-Шиц Создание сложных запросов Как запрос сохраняет выбранные поля При открытии запроса в режиме конструктора обратите внимание на то, что со времени последнего сохранения запроса его конструкция в некоторых случаях автоматически изменяется. Это объясняется тем, что при сохранении запроса Access переупорядочивает (иногда даже удаляет) поля на основе следующих правил. • Если флажок Вывод на экран (Show) данного поля снят, а условие отбора задано, Acсess перемещает поле в последний правый столбец решетки. • Если флажок Вывод на экран снят и поле не имеет условия отбора или директивы сортировки, Access удаляет поле из решетки запроса. o После создания обобщенного выражения с оператором Sum в обобщающем запросе Access заменяет его выражением с функцией Sum. Следовательно, после сохранения и повторного открытия запроса его конструкция может немного иначе, чем перед выполнением этих операций. В следующих разделах вопрос рассматривается подробнее. Базы данных Использование SQL Слайд 45
ММИТ Проф. А. К. Иванов-Шиц Создание сложных запросов Сокрытие полей В некоторых случаях нужно отменить вывод одного или нескольких полей в результирующий набор запроса. Ранее рассматривался запрос, возвращающий количество книг объемом более 300 страниц. Запрос возвращает одно число (количество книг), а не данные по каждой книге. Поэтому запрос должен вывести только столбец, содержащий обобщенное значение, остальные столбцы должны быть скрыты. Чтобы скрыть или удалить столбец из результирующего набора, нужно снять флажок Вывод на экран, принадлежащий данному столбцу в решетке запроса. Если с помощью флажка Вывод на экран скрыть столбец, не используемый в критериях сортировки или фильтрации, Access автоматически удалит его при сохранении запроса. После этого удаленный столбец можно будет использовать в запросе, только вновь добавив его в решетку путем перетаскивания с верхней панели. Если запрос используется в отчете или форме, он должен вернуть все столбцы, связанные с элементами управления, включая скрытые столбцы. Базы данных Использование SQL Слайд 46
ММИТ Проф. А. К. Иванов-Шиц Переименование полей запроса Переименование полей результирующего набора запроса используется для того, чтобы их назначение было более очевидным. Новое имя отображается при выводе результирующего набора запроса в режиме таблицы. В предыдущих разделах мы уже встречались с переименованием выражения, лежащего в основе вычисляемого столбца. Переименование обычных полей выполняется аналогичным образом. Поле получает новое имя только в результирующем наборе запроса, но не в таблице. Рассмотрим запрос, возвращающий первые 10 записей. Этот запрос мы создали, выполняя упражнение ранее. Откройте его в режиме конструктора. В решетке перед именем столбца Печать_Дата введите фразу Дата публикации: . Тогда в результирующем наборе запроса эта фраза будет выведена в заголовке столбца вместо мало информативного имени Печать_Дата. Код SQL запроса имеет следующий вид: SELECT T 0 P 5 Книги_Публикация. Название, Книги_Издание. ISBN, Книги_Издание. Печать_Дата AS [Дата публикации], Книги_Издание. Формат FROM Книги_Издание INNER JOIN Книги_Публикация ON Книги_Издание. Публикация_ИД = Книги_Публикация_ИД ORDER BY Книги_Публикация. Название ; В коде SQL переименование выполняется директивой AS, размещенной после имени столбца таблицы. Базы данных Использование SQL Слайд 47
ММИТ Проф. А. К. Иванов-Шиц Переименование полей запроса Итак, напомним: Заголовок, заданный для поля таблицы в режиме конструктора, используется в запросе в качестве имени поля таблицы. После переименования поля в качестве заголовка столбца, расположенного в результирующем наборе запроса, используется только новое имя. Это же имя используется и в любом элементе управления формы или отчета, источником для которого служит запрос. В каждой новой форме или отчете, которые созданы на основе запроса, используется новое имя поля. Изменение имени поля в запросе не затрагивает имя поля в нижележащей таблице, однако во всех последующих процессах, использующих запрос, применяется измененное имя поля. Имя выражения или поля запроса (т. е. новое имя, после которого стоит двоеточие ) можно использовать в других выражениях, входящих в этот же запрос. Предположим, что запрос содержит вычисляемый столбец Full. Name, в котором имя и фамилия сотрудника объединяются с помощью встроенной функции. Тогда имя выражения Full. Name можно использовать в других ячейках решетки. Имя выражения нельзя использовать в критериях, ссылающихся на поля, используемые выражением. Иными словами, в запросе не должно быть цикличного использования имен. Базы данных Использование SQL Слайд 48
ММИТ Проф. А. К. Иванов-Шиц Создание сложных запросов Сокрытие и вывод столбца в режиме конструктора Как уже говорилось, скрыть столбец запроса можно в режиме конструктора, сняв флажок Вывод на экран. Кроме того, скрыть столбец можно также в режиме таблицы путем перетаскивания правой границы столбца. Если перетащить ее влево до совпадения с левой границей, то ширина столбца станет равной нулю и он будет невидим. Свойства запроса Существует несколько способов установки свойств запроса при его создании. Откройте запрос в режиме конструктора, активизируйте на ленте вкладку Конструктор и щелкните на кнопке Страница свойств (Properties), расположенной в разделе Показать и скрыть (Show and Hide). Или установите курсор в решетку запроса, щелкните правой кнопкой мыши в пустой ячейке решетки и выберите в контекстном меню команду Свойства. В обоих случаях будет открыто окно свойств запроса. Набор свойств на уровне запроса зависит от его типа и от таблиц и полей, используемых в запросе. Базы данных Использование SQL Слайд 49
ММИТ Проф. А. К. Иванов-Шиц Обобщающие запросы Для создания обобщающих запросов (другое их название — итоговые) используется специализированный мастер. Кроме того, обобщенные (итоговые) значения можно создавать в режиме конструктора с помощью кнопки Итоги (Totals), на которой изображен символ суммирования. Мастер обобщающих запросов Одно из окон мастера обобщающих запросов предоставляет возможность выбрать тип обобщения, например суммирование, подсчет, поиск минимального или максимального значений и т, д. В детализирующем отчете выводится каждая запись, а в обобщающем — обобщенное значение. Откройте запрос в режиме конструктора. Во вкладке Создание (Create) щелкните на кнопке Мастер запросов (Query Wizard). Добавьте в новый запрос все поля запроса. Установите флажок итоговый (Total) и щелкните на кнопке Итоги (Totals). Будет открыто окно, в котором можно задать параметры обобщения. (Термин "обобщающая" означает, что информация собирается из многих мест и концентрируется в одном значении. Обобщающая (aggregate) функция извлекает из таблицы много записей, выполняет над ними математические вычисления и возвращает обобщенно значение. Результатом ее работы может быть усредненное значение, количество записей, максимальное значение и т. д. ) Базы данных Использование SQL Слайд 50
ММИТ Проф. А. К. Иванов-Шиц Обобщающие запросы в режиме конструктора Чтобы создать запрос, выполняющий обобщающие вычисления, создайте запрос на выборку и щелкните на кнопке Итоги, расположенной во вкладке Конструктор (Design). В решетке запроса появится новая строка Групповая операция (Group Ву). Выберите поля, по значениям которых будет выполняться обобщение, и скройте поля, которые не должны выводиться в результирующий набор. Создайте запрос на выборку на основе таблицы Книги_Издание. Добавьте в него поля (Формат) и (Страницы). Щелкните на кнопке Итоги. В строке Групповая операция столбца Количество_страниц выберите значение Sum. Запрос сгруппирует книги по форматам (твердый переплет, мягкая обложка и т. д. ) и для каждой группы вычислит общее количество страниц. Код SQL запроса приведен ниже. SELECT Книги_Издание. Формат, Sum(Книги_Издание_Страницы) AS [Сумма страниц] FROM Книги_Издание GROUP BY Книги_Издание. Формат; Базы данных Использование SQL Слайд 51
Проф. А. К. Иванов-Шиц ММИТ Обобщающие запросы в режиме конструктора Раскрывающийся список ячейки Групповая операция содержит двенадцать элементов, принадлежащих четырем категориям. В категорию обобщающих функций входит девять элементов списка, в другие категории — по одному элементу. Категория группировки Столбец, в ячейке которого расположено значение Группировка, используется для группирования записей. Например, если значение Группировка расположено в столбце Формат, то каждая группа будет состоять из записей, в которых поле Формат имеет одно и то же значение. Категория выражения Значение Выражение, расположенное в ячейке Групповая операция, сообщает Access о том, что ячейка Поле содержит произвольное обобщающее выражение, вычисляемое для каждой группы. Категория условия Значение Условие, расположенное в ячейке „Групповая операция, сообщает Access о том, что в столбце решетки задан критерий, ограничивающий количество извлекаемых и обрабатываемых записей. Фактически критерий является фильтром записей. Записи проверяются на соответствие критерию до выполнения любых групповых операций. Категория обобщающих функций Эта категория состоит из девяти обобщающих функций: Sum, Avg, Min, Nax, Count, St. Dev, Var, First, Last. Каждая функция выполняет операции над значениями столбца, принадлежащими одной группе, и выводит вычисленное обобщенное значение в результирующий набор запроса. Таким образом, в каждой группе обобщающая функция получает много значений, а возвращает одно. Каждое возвращенное значение принадлежит одной группе. Например, с помощью обобщающих функций можно вычислить и вывести в результирующий набор запроса максимальное, минимальное и среднее количество страниц для к значения Формат таблицы Книги_Издание. Базы данных Использование SQL Слайд 52
Проф. А. К. Иванов-Шиц ММИТ Обобщающие запросы в режиме конструктора Частные обобщения Вычислять обобщенные значения можно не только для всех записей, но и для записей, входящих в группу. Предположим, что запрос объединяет три таблицы, как показано на рисунке (для этого на ленте пройдите по цепочке: создание->создание нового пустого запроса, перенесите требуемые таблицы и далее по рисунку) Запрос подсчитывает количество значений ФОРМАТ в каждой группе Базы данных Использование SQL Слайд 53
Проф. А. К. Иванов-Шиц ММИТ Обобщающие запросы Код SQL запроса, показанного на рисунке, имеет следующий вид. SELECT Книги_Автор. ФИО, Книги_Публикация. Название, Count(Книги_Издание. Формат) AS [Количество форматов] FROM (Книги_Автор INNER JOIN Книги_Публикация ON Книги_Автор_ИД = Книги_Публикация. Автор_ИД) INNER JOIN Книги_Издание ON Книги_Публикация. [Публикация _ИД] = Книги_Издание. Публикация_ИД GROUP BY Книги_Автор. ФИО, Книги_Публикация. Название; Базы данных Использование SQL Слайд 54
Проф. А. К. Иванов-Шиц ММИТ Обобщающие запросы Можно задать другой уровень обобщения и группировки. Предположим, группа должна состоять из записей, содержащих информацию об одном авторе. Тогда для каждой группы (т. е. для каждого автора) можно отдельно подсчитать количество названий и форматов книг. Запрос подсчитывает количество названий и форматов для каждого автора Базы данных Использование SQL Слайд 55
Проф. А. К. Иванов-Шиц ММИТ Обобщающие запросы Группирование выполняется по значениям одного поля, а не двух, как в предыдущем запросе. Код SQL запроса, показанного на рисунке, имеет следующий вид. SELECT Книги_Автор. ФИО, Count(Книги_Публикация. Название) AS [Количество названий], Count(Книги_Издание. Формат) AS [Количество форматов] FROM (Книги_Автор INNER JOIN Книги_Публикация ON Книги_Автор_ИД = Книги_Публикация. Автор_ИД) INNER JOIN Книги_Издание ON Книги_Публикация. [Публикация _ИД] = Книги_Издание. Издательство_ИД GROUP BY Книги_Автор. ФИО; Базы данных Использование SQL Слайд 56
Проф. А. К. Иванов-Шиц Обобщающие запросы ММИТ Фильтрация обобщаемых записей Кроме группирования записей, в решетке запроса можно задать критерии, ограничивающие количество обрабатываемых или выводимых записей. Критерий может быть установлен для полей следующих типов: • используемое для группирования; • используемое для обобщения; • не используемое ни для группирования, ни для обобщения. Рассмотрим запрос, конструкция которого показана на рисунке. Фильтрация записей выполняется на основе значений полей ФИО, Прейскурант , Страницы Базы данных Использование SQL Слайд 57
Проф. А. К. Иванов-Шиц Обобщающие запросы ММИТ Ниже приведен код SQL запроса SELECT Книги_Автор. ФИО, Книги_Издание. Формат, Sum(Книги_Издание. Прейскурант) AS [Сумма стоимостей], Книги_Издание. Страницы FROM (Книги_Автор INNER JOIN Книги_Публикация ON Книги_Автор_ИД = Книги_Публикация. Автор_ИД) INNER JOIN Книги_Издание ON Книги_Публикация. [Публикация _ИД] = Книги_Издание. Публикация_ИД GROUP BY Книги_Автор. ФИО, Книги_Издание. Формат, Книги_Издание. Страницы HAVING (((Книги_Автор. ФИО)>"A") AND ((Sum(Книги_Издание. Прейскурант))>10) AND ((Книги_Издание. Страницы)>200)); Существенный недостаток этого запроса состоит в том, что фильтрация задана в директиве HAVING, расположенной после группирующей директивы GROUP BY. Фильтры, применяемые не к группам, а записям, следует размещать в директиве WHERE. Она выполняется перед директивами ORDER BY и GROUP BY. Обобщать данные, не возвращаемые запросом, не имеет смысла. Если запрос возвращает 10 записей, это не создаст проблем, однако, если перед группированием нужно отфильтровать миллион исходных записей, оставив только 1000, нерациональное размещение фильтра существенно ухудшит быстродействие запроса. Базы данных Использование SQL Слайд 58
Проф. А. К. Иванов-Шиц Перекрестные запросы ММИТ Перекрестные запросы — гибкий инструмент анализа информации. В рабочей среде Access можно создавать запросы и отчеты, в которых данные классифицируются, смешиваются, сравниваются и обобщаются по заданным правилам. Перекрестные запросы полезны для обобщения и поиска закономерностей в данных. Чем-то они похожи на электронные таблицы. В простейшем перекрестном запросе значения полей сравниваются друг с другом. Сравнение может выполняться для данных одной таблицы. Предположим, что нужно создать перекрестный запрос для сравнения разных параметров книг. В режиме конструктора создайте простой запрос на основе объединения таблиц Книги_автор, Книги_Издание, Книги_Публикация (Простой запрос можно создать, написав его код SQL вручную, либо с помощью решетки в окне конструктора. Второй способ намного легче и быстрее. ) Базы данных Использование SQL Слайд 59
Проф. А. К. Иванов-Шиц ММИТ Перекрестные запросы Конструкция запроса показана на рисунке. Выбраны три таблицы. Для объединения таблиц перетащите общие имена полей из первой таблицы во вторую, а из второй - в третью. 1. Переименуйте созданный запрос, присвоив ему имя КНИГИ. 2. Запустите мастер запросов и выберите перекрестный запрос. В качестве основы создаваемого перекрестного запроса выберите запрос КНИГИ. 3. В следующем окне мастера задайте заголовки строк, как показано на рисунке. Для этого переместите поле Формат в список выбранных полей. 4. В следующем окне выберите для заголовков столбцов поле ФИО. В следующем окне выберите обобщающую функцию Число (Count). Щелкните на кнопках Далее (Next) и Готово (Finish). Базы данных Использование SQL Слайд 60
Проф. А. К. Иванов-Шиц Перекрестные запросы ММИТ Результирующий набор запроса должен выглядеть так же, как на рисунке. Запрос подчитал количество книг каждого формата для всех авторов и для каждого автора отдельно. Перекрестные запросы и лежащие в их основе запросы на объединение в реальных задачах намного сложнее, чем в рассмотренном выше примере. Сложность запроса зависит от количества уровней группирования и обобщения. Если уровней много как по вертикали, так и по горизонтали, то результирующий набор запроса неудобен для восприятия вследствие огромного количества строк и столбцов. Если перекрестный запрос возвращает более нескольких десятков строк и столбцов, работать с ним практически невозможно. Базы данных Использование SQL Слайд 61
ММИТ Проф. А. К. Иванов-Шиц Домашнее задание Учить язык SQL (знать основные операторы и правила построения запросов Базы данных Использование SQL Слайд 62
Скачать презентацию ММИТ Проф А К Иванов-Шиц Базы данных Профессор
БД_Лекция_8_SQL.ppt