Green-sell.info

Новые технологии
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Sql запросы в access

Создание запросов с использованием языка SQL

На самом деле любой запрос в MS Access реализуется с помощью языка SQL. Большинство запросов можно построить, пользуясь средствами, которые Access предоставляет в режиме конструктора, и в этом случае они будут храниться в виде инструкций SQL. Но некоторые типы запросов можно построить, только используя язык SQL.

Синтаксис инструкции SELECT в MS Access. Ядром языка SQL является инструкция SELECT. Она используется для отбора строк и столбцов из таблиц базы данных и содержит пять основных предложений. В общем случае ее синтаксис можно представить в следующем виде:

[WHERE ] [2] [GROUP BY ]

В MS Access реализованы важные средства расширения языка: инструкция TRANSFORM, позволяющая строить перекрестные запросы; предложение IN, с помощью которого задается связь с удаленной базой данных или указываются имена столбцов перекрестного запроса, и др. Синтаксис:

Имя-таблицы, имя-запроса-на-выборку и псевдоним, уточняющий имя поля, обязательно должны присутствовать в предложении FROM запроса. Если таблица или запрос имеет псевдоним, необходимо использовать именно его, а не реальное имя таблицы или запроса.

Первую часть имени (включая точку) можно опустить, если поле имеется только в одной из таблиц предложения FROM.

Имена, содержащие пробелы, обязательно должны заключаться в квадратные скобки.

При определении списка полей использование символа «*» вместо имени поля указывает, что нужно отобразить все столбцы данной таблицы. Если в качестве списка полей использован символ «*», то отбираются все столбцы всех таблиц, указанных в предложении FROM.

Пример 10.5. Следующее выражение задает ноле из таблицы [Товары], имя которого Наименование:

Предложение FROM. Задает таблицы или запросы, служащие источниками данных для создаваемого запроса. Синтаксис:

имя-запроса-на-выборку [[AS] псевдоним] I

[IN сспецификация источника>]

имя-запроса-на-выборку [[AS] псевдоним] I

имя-запроса-на-выборку [[AS] псевдоним] I

ON

Для каждой таблицы и запроса можно определить альтернативное имя. Оно используется как псевдоним вместо полного имени таблицы при задании имен столбцов в списке полей, условии объединения и предложении WHERE.

Пример 10.6. Следующий запрос отображает все поля таблицы Товары:

Пример 10.7. Следующий запрос отображает товары, цена которых больше 100 долл, и гарантийный срок которых 36 мес:

SELECT Товары.Наименование, Товары.Цена

WHERE Товары.Цена > 100 AND Товары.[Гарантийный срок] = 36;

Большие возможности языка SQL во многом основаны на его способности объединять информацию из нескольких таблиц или запросов.

Для задания типа объединения таблиц в логический набор записей, из которого будет выбираться необходимая информация, в предложении FROM используется операция JOIN.

Операция INNER JOIN используется для получения всех строк из обеих логических таблиц, удовлетворяющих условию объединения.

Операция LEFT JOIN возвращает все строки из первой логической таблицы, объединенные с теми строками из второй, для которых выполняется условие объединения.

Аналогично, операция RIGHT JOIN возвращает все строки из второй логической таблицы, объединенные с теми строками из первой таблицы, для которых выполняется условие объединения.

– выражение, в котором поля первой таблицы сравниваются с полями второй таблицы. В бланке запроса QBE в условии объединения используется только оператор равно (=). Запрос на основе объединения таблиц по неравенству ( , <>, =) можно создать только в режиме SQL.

Пример 10.8. Следующий запрос отображает сведения о складах фирмы Citii i nk:

SELECT Склады.Номерсклада, Склады.Адрес,

FROM Фирмы INNER JOIN Склады

ON Фирмы.Кодфирмы = Склады.Кодфирмы

WHERE Фирмы.Название = «Citilink»;

Пример 10.9. Запрос отображает список товаров, которые еще не продавались, с указанием наименования товаров и номеров складов, на которых они хранятся:

SELECT Товары.Наименование, Хранение.Номерсклада

FROM (Товары INNER JOIN Хранение

ON Товары.КодТовара = Хранение.КодТовара)

LEFT JOIN Продажи ON Хранение.ID = Продажи.ID

WHERE ((Продажи.Количество) Is Null);

Пример 10.10. Следующая операция возвратит все строки из таблиц «Товары» и «Хранение», для которых совпадают значения в поле КодТовара:

Товары INNER JOIN Хранение ON Товары.КодТовара =

Операция LEFT JOIN возвратит все строки из таблицы, полученной в результате предыдущей операции INNER JOIN, объединенные с теми строками из таблицы «Продажи», для которых выполняется условие объединения. Тогда в с троке товара, который не продавался, в поле [Количество проданного товара] будет значение Null, что будет являться условием отбора для искомого списка.

Предложение GROUP BY. В инструкции SELECT задает столбцы, используемые для формирования групп из выбранных строк. Синтаксис:

GROUP BY имя-столбца, .

Оператор GROUP BY разделяет рассматриваемую таблицу па такие группы, что внутри любой из этих групп все строки содержат одинаковые значения в указанном столбце.

Пример 10.11. Следующий запрос отображает общее количество проданного товара каждого наименования:

SELECT Товары.Наименование, Sum(Продажи.Количество)

FROM (Товары INNER JOIN Хранение

ON Товары.КодТовара = Хранение.КодТовара)

INNER JOIN Продажи ON Хранение.Ю=Продажи.ID

GROUP BY Товары.Наименование;

Предложение HAVING. Это специальная форма фразы WHERE. Она относится пе к отдельным строкам, а к группам: предикат во фразе HAVING всегда ссылается (посредством специальных библиотечных функций, таких как, например, SET) на свойства групп, а не строк, и на основе этого предиката группы целиком либо выбираются, либо отбрасываются. Синтаксис:

HAVING «условие отбора>

В случае отсутствия предложения GROUP BY условие отбора применяется ко всей логической таблице, определенной инструкцией SELECT.

Пример 10.12. Следующий запрос отображает общее количество проданного товара по дням с 10 по 20 декабря 2003 г.:

SELECT Продажи.Дата, Эит(Продажи.Количество)

GROUP BY Продажи.Дата

HAVING ((Продажи.Дата) Between #10.12.03# AND

Предложение ORDER BY. Задает порядок расположения строк, возвращаемых инструкцией SELECT. Синтаксис:

Оператор ORDER BY определяет сортировку результата выборки в порядке возрастания (ASC) или убывания (DESC) значений атрибута. В предложении ORDER BY можно указать несколько столбцов. Список сортируется сначала по значениям столбца, имя которого указано первым.

Пример 10.13. Следующий запрос отображает список товаров, упорядоченных по цене:

SELECT Товары.Наименование, Товары.Цена

ORDER BY Товары.Цена DESC;

Инструкция SELECT. Выполняет операции выбора и объединения для создания логической таблицы (набора записей) на базе других таблиц или запросов. Выше был рассмотрен синтаксис инструкции SELECT в общем виде. Рассмотрим его более подробно:

SELECT [ALL I DISTINCT I DISTINCTROW I TOP число

Предикаты ALL, DISTINCT, DISTINCTROW, TOP n или TOP n PERCENT уточняют окончательный набор записей запроса.

По умолчанию действует предикат ALL, при котором в набор записей включаются все строки, удовлетворяющие условиям отбора, в том числе дубликаты.

Предикат DISTINCT требует, чтобы запрос возвратил только строки, отличающиеся от всех остальных. Если инструкция SELECT содержит предикат DISTINCTROW, то в набор записей включаются только те строки, в которых конкатенация первичных ключей из всех таблиц, участвующих в формировании возвращаемых столбцов, является уникальной. В зависимости от того, какие столбцы представлены в наборе записей, иногда можно увидеть повторяющиеся строки, но даже в этом случае каждая строка запроса образована из уникальной (DISTINCT) комбинации строк (ROWS) базовых таблиц.

Чтобы результирующий набор содержал только первые п или первые п процентов записей, используйте предикат ТОР п или TOP п PERCENT. Параметр п должен быть целым числом, не превышающим 100, если используется ключевое слово PERCENT.

Пример 10.14. Следующий запрос отображает список 10 самых дорогих товаров:

SELECT ТОР 10 Товары.Наименование, Товары.Цена

ORDER BY Товары.Цена DESC;

Подчиненный запрос (вложенная выборка). Язык SQL позволяет использовать в условии отбора результаты другой выборки. Уровней вложенности может быть несколько.

Пример 10.15. Следующий запрос отображает список процессоров, цена которых ниже средней цены процессоров:

SELECT Товары.Наименование, Товары.Цена

WHERE ((Товары.Наименование) Like «проц*») AND

  • [1] В угловые скобки заключается обязательный элемент синтаксиса.
  • [2] В квадратные скобки заключаются один или несколько необязательных элементов, разделенных символом «вертикальная черта» (|).
  • [3] В фигурные скобки заключаются один или несколько элементов, разделенных символом «вертикальная черта». Следует выбрать один из перечисленных элементов.
  • [4] Запрос должен заканчиваться символом «точка с запятой».

Использование языка SQL MS ACCESS;

Язык структурированных запросов (SQL) является стандартным средством для работы с базами данных. Может использоваться как для интерактивной работы с БД, так и включаться в языки программирования. В CУБД Access SQL позволяет:

· создавать, модифицировать или удалять таблицы в базе данных Access;

· создавать или удалять ключи, индексы для таблиц в базе данных Access;

· вставлять, удалять или модифицировать записи таблиц;

· получать сводную информацию о данных в таблицах (число записей, суммы, средние, минимальные, максимальные значения и др.);

Читать еще:  Как активировать мс офис 2020

· осуществлять поиск и извлекать данные из одной или более таблиц по запросу.

Рассмотрим основные операторы языка SQL для поиска и извлечения информации из базы данных. Для этого используется оператор SELECT, синтаксис которого следующий:

SELECT список имен полей 1

FROM список имен таблиц

[WHERE условие]

[ORDER BY список имен полей 2]

[GROUP BY список имен полей 3]

SELECT, FROM, WHERE, ORDER BY, GROUP BY — ключевые слова;

список имен полей 1 набор имен полей, которые выбираются из одной или нескольких таблиц, указанных в списке имен таблиц, вычисляемых полей, агрегированных функций (например, count, sum, min, max, avg и т.д.). Для выбора всех полей из списка таблиц вместо перечисления их имен можно использовать символ «*». Если имя поля таблицы содержит пробел, т.е. состоит из нескольких слов, то это имя должно заключаться в квадратные скобки;

список имен таблиц — набор имен таблиц, из которых производится выбор информации.

Имена полей и таблиц нечувствительны к регистру клавиатуры и указываются в списках через запятую.

Все приведенные далее примеры основаны на использовании БД по библиографии, логическая структура которой приведена на рисунке 2.1.

Примеры:

SELECT * FROM Книга — выбор всех полей всех книг из таблицы «Книга» базы данных по библиографии.

SELECT Название, [Год издания] FROM Книга — выбор полей заголовков (Название ) и года издания (Год издания) всех книг из таблицы «Книга». Имя поля года издания указано в квадратных скобках (имеет пробел).

Таблица «Издательство» Таблица «Книга» Таблица «Автор»

Рис. 2.1. Структура и схема БД по библиографии

Для поиска информации, удовлетворяющей некоторому условию, в операторе SELECT используется раздел WHERE, который имеет следующий синтаксис:

SELECT список имен полей FROM список имен таблиц WHERE условие,

где условиеопределяет критерий поиска информации.

В условии используются имена полей, операции сравнения ( , >=, <>) и специальные операции сравнения IN, LIKE, BETWEЕN. Эти операции могут объединяться с помощью логических операций (And, Or) и задавать сложные условия поиска информации.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] > 1991 определяет выбор названий книг, год выпуска которых позже 1991.

SELECT Фамилия, Имя FROM Автор

WHERE Фамилия = ”Иванов” определяет выбор полей имен и фамилий авторов из таблицы «Автор», фамилии которых совпадают с фамилией Иванов. Здесь символ “ используется для задания текстовых констант.

Операция IN сравнивает содержимое поля со списком заданных значений, определяющих критерий поиска информации.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] IN (1995, 1996) определяет выбор названий и года издания книг, опубликованных в 1995 или в 1996 гг.

SELECT Фамилия, Имя, Город FROM Автор

WHERE Город IN (“Омск”, “Москва”, “Киев”) определяет выбор авторов, живущих в городах Омск, Москва или Киев.

Операция LIKE сравнивает содержимое поля со значением образца. Для записи образца используются строковые константы, символы шаблона и списки диапазона символов.

Символами шаблона являются: *, ?, #. Символ * соответствует цепочке символов, ? — соответствует одному символу, # — соответствует одной цифре.

Примеры:

Р* — возможные результаты поиска рука, Роза.

КИ? — возможные результаты поиска кий, кит.

12345# — возможные результаты поиска 123455, 123457.

Список диапазона заключается в квадратные скобки, первый и последний символы диапазона отделяются дефисом (-). Диапазон задается в возрастающем порядке.

Примеры:

SELECT Фамилия, Имя, Отчество FROM Автор

WHERE Фамилия LIKE ‘И*’ определяет выбор авторов, фамилии которых начинаются с буквы И.

SELECT Фамилия FROM Автор WHERE Фамилия LIKE [A-K] определяет выбор авторов, фамилии которых начинаются с букв от А до К.

SELECT Название FROM Книга

WHERE Название LIKE “*база данных*” AND [Год издания] = 1996 определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных».

Операция BETWEEN проверяет принадлежность значения поля диапазону значений и является включающим значением (выбираются записи, содержащие поле со значением, равным границе диапазона). Границы значений объединяются операцией AND.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] Between #01.01.1994# And #30.06.1994# определяет выбор книг, изданных в первой половине 1994 года. Здесь символ # используется для задания констант типа дата.

SELECT [Название издат], адрес, телефон FROM Издательство

WHERE [Название издат] Between ‘Нау’ And ‘Фин’ определяет выбор информации об издательских фирмах, начальные буквы названий которых, расположенные в алфавитном порядке, находятся в диапазоне между ‘Нау’ и ‘Фин’.

Для определения порядка, в котором представляются результаты поиска записей, используется раздел ORDER BY, синтаксис которого следующий:

SELECT список имен полей FROM список имен таблиц [WHERE условие] ORDER BY имя поля 1 [ASC | DESC][, имя поля 2[ASC | DESC]] ,

где имя поля1, имя поля2 поля (из списка имен полей), по которым производится упорядочение. Опция ASCустанавливает порядок сортировкипо возрастанию (данный порядок установлен по умолчанию), DESC устанавливает порядок сортировки по убыванию. Упорядочение может вестись по нескольким полям (например, по фамилии, а затем по имени)

Пример:

SELECT Название FROM Книга

WHERE Название LIKE “*база данных*” AND [Год издания] = 1996

ORDER BY Название определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных», и упорядочивает названия в алфавитном порядке.

Для группировки записей используется раздел GROUP BY, синтаксис которого следующий:

SELECT список имен полей FROM список имен таблиц [WHERE условие] GROUP BY имя поля 1 [, имя поля 2],

где имя поля1, имя поля2 поля (из списка имен полей), по которым производится группировка записей. Порядок следования полей в этом предложении определяет уровни их группировки. Группировку можно производить для выражений (например, вычисляемых полей), тогда в списке полей этому выражению должно быть присвоено какое-нибудь имя. Для агрегированных функций (count, sum, min, max, avg и т.д.) группировка обязательна.

Пример:

SELECT Фамилия, [Год издания], Sum (Цена) AS [Стоимость]

FROM Книга, Автор

WHERE Автор.[Код автора]= Книга.[Код автора]

GROUPBY Фамилия, [Год издания]

ORDER BY Фамилия, [Год издания] определяет суммарную цену (Стоимость) книг года издания для каждого автора.

При работе с несколькими таблицами, каждое из рассмотренных условий выбора может быть применено для любой из таблиц. В общем случае при формировании запроса с использованием нескольких таблиц в разделе WHERE указывается связь между таблицами. Синтаксис запроса для нескольких таблиц следующий:

SELECT список имен полей FROM список имен таблиц WHERE список связей,

где список связей определяет, как таблицы в списке имен таблиц связаны между собой. Например, имя_таблицы1.имя_поля1 = имя_таблицы2.имя_поля2.

Примеры:

SELECT Книга.Название, Издательство. [Название издат]

FROM Книга, Издательство

WHERE Книга. [Код издат]= Издательство. [Код издательства] And

Издательство. [Название издат]=”Наука”

Этот запрос определяет выбор названий книг (Название) из таблицы «Книга» и названий издательств (Название издат) из таблицы издательств (Издательство). Раздел WHERE осуществляет следующее:

· определяет связь между таблицами (выбирается название книги и номер издательства «Код издат» из таблицы «Книга», по номеру издательства «Код издательства» в таблице издательств «Издательство» находится название издательства);

· задает условие для выбора книг, изданных только в издательстве «Наука».

Access SQL. Основные понятия, лексика и синтаксис

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Для извлечения данных из базы данных используется язык SQL. SQL — это язык программирования, который очень напоминает английский, но предназначен для программ управления базами данных. SQL используется в каждом запросе в Access.

Понимание принципов работы SQL помогает создавать более точные запросы и упрощает исправление запросов, которые возвращают неправильные результаты.

Это статья из цикла статей о языке SQL для Access. В ней описаны основы использования SQL для выборки данных и приведены примеры синтаксиса SQL.

Читать еще:  Как поменять язык в офисе 2020

В этой статье

Что такое SQL?

SQL — это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования, SQL удобочитаем и понятен даже новичкам. Как и многие языки программирования, SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO и ANSI.

На языке SQL описываются наборы данных, помогающие получать ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис — это набор правил, позволяющих правильно сочетать элементы языка. Синтаксис SQL основан на синтаксисе английского языка и имеет много общих элементов с синтаксисом языка Visual Basic для приложений (VBA).

Например, простая инструкция SQL, извлекающая список фамилий контактов с именем Mary, может выглядеть следующим образом:

Примечание: Язык SQL используется не только для выполнения операций над данными, но еще и для создания и изменения структуры объектов базы данных, например таблиц. Та часть SQL, которая используется для создания и изменения объектов базы данных, называется языком описания данных DDL. Язык DDL не рассматривается в этой статье. Дополнительные сведения см. в статье Создание и изменение таблиц или индексов с помощью запроса определения данных.

Инструкции SELECT

Инструкция SELECT служит для описания набора данных на языке SQL. Она содержит полное описание набора данных, которые необходимо получить из базы данных, включая следующее:

таблицы, в которых содержатся данные;

связи между данными из разных источников;

поля или вычисления, на основе которых отбираются данные;

условия отбора, которым должны соответствовать данные, включаемые в результат запроса;

необходимость и способ сортировки.

Предложения SQL

Инструкция SQL состоит из нескольких частей, называемых предложениями. Каждое предложение в инструкции SQL имеет свое назначение. Некоторые предложения являются обязательными. В приведенной ниже таблице указаны предложения SQL, используемые чаще всего.

Определяет поля, которые содержат нужные данные.

Определяет таблицы, которые содержат поля, указанные в предложении SELECT.

Определяет условия отбора полей, которым должны соответствовать все записи, включаемые в результаты.

Определяет порядок сортировки результатов.

В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение.

Только при наличии таких полей

В инструкции SQL, которая содержит статистические функции, определяет условия, применяемые к полям, для которых в предложении SELECT вычисляется сводное значение.

Термины SQL

Каждое предложение SQL состоит из терминов, которые можно сравнить с частями речи. В приведенной ниже таблице указаны типы терминов SQL.

Сопоставимая часть речи

Имя, используемое для идентификации объекта базы данных, например имя поля.

глагол или наречие

Ключевое слово, которое представляет действие или изменяет его.

Значение, которое не изменяется, например число или NULL.

Сочетание идентификаторов, операторов, констант и функций, предназначенное для вычисления одного значения.

Основные предложения SQL: SELECT, FROM и WHERE

Общий формат инструкций SQL:

Access не учитывает разрывы строк в инструкции SQL. Несмотря на это, каждое предложение рекомендуется начинать с новой строки, чтобы инструкцию SQL было удобно читать как тому, кто ее написал, так и всем остальным.

Каждая инструкция SELECT заканчивается точкой с запятой (;). Точка с запятой может стоять как в конце последнего предложения, так и на отдельной строке в конце инструкции SQL.

Пример в Access

В приведенном ниже примере показано, как в Access может выглядеть инструкция SQL для простого запроса на выборку.

1. Предложение SELECT

2. Предложение FROM

3. Предложение WHERE

Эту инструкцию SQL следует читать так: «Выбрать данные из полей «Адрес электронной почты» и «Компания» таблицы «Контакты», а именно те записи, в которых поле «Город» имеет значение «Ростов».

Разберем пример по предложениям, чтобы понять, как работает синтаксис SQL.

Предложение SELECT

SELECT [E-mail Address], Company

Это предложение SELECT. Оно содержит оператор (SELECT), за которым следуют два идентификатора («[Адрес электронной почты]» и «Компания»).

Если идентификатор содержит пробелы или специальные знаки (например, «Адрес электронной почты»), он должен быть заключен в прямоугольные скобки.

В предложении SELECT не нужно указывать таблицы, в которых содержатся поля, и нельзя задать условия отбора, которым должны соответствовать данные, включаемые в результаты.

В инструкции SELECT предложение SELECT всегда стоит перед предложением FROM.

Предложение FROM

Это предложение FROM. Оно содержит оператор (FROM), за которым следует идентификатор (Контакты).

В предложении FROM не указываются поля для выборки.

Предложение WHERE

Это предложение WHERE. Оно содержит оператор (WHERE), за которым следует выражение (Город=»Ростов»).

Примечание: В отличие от предложений SELECT и FROM, предложение WHERE является необязательным элементом инструкции SELECT.

С помощью предложений SELECT, FROM и WHERE можно выполнять множество действий. Дополнительные сведения об использовании этих предложений см. в следующих статьях:

Сортировка результатов: ORDER BY

Как и в Microsoft Excel, в Access можно сортировать результаты запроса в таблице. Используя предложение ORDER BY, вы также можете указать способ сортировки результатов при выполнении запроса. Если используется предложение ORDER BY, оно должно находиться в конце инструкции SQL.

Предложение ORDER BY содержит список полей, для которых нужно выполнить сортировку, в том же порядке, в котором будут применена сортировка.

Предположим, например, что результаты сначала нужно отсортировать по полю «Компания» в порядке убывания, а затем, если присутствуют записи с одинаковым значением поля «Компания», — отсортировать их по полю «Адрес электронной почты» в порядке возрастания. Предложение ORDER BY будет выглядеть следующим образом:

ORDER BY Company DESC, [E-mail Address]

Примечание: По умолчанию Access сортирует значения по возрастанию (от А до Я, от наименьшего к наибольшему). Чтобы вместо этого выполнить сортировку значений по убыванию, необходимо указать ключевое слово DESC.

Дополнительные сведения о предложении ORDER BY см. в статье Предложение ORDER BY.

Работа со сводными данными: предложения GROUP BY и HAVING

Иногда возникает необходимость работы со сводными данными, такими как итоговые продажи за месяц или самые дорогие товары на складе. Для этого в предложении SELECT к полю применяется агрегатная функция. Например, если в результате выполнения запроса нужно получить количество адресов электронной почты каждой компании, предложение SELECT может выглядеть следующим образом:

SELECT COUNT([E-mail Address]), Company

Возможность использования той или иной агрегатной функции зависит от типа данных в поле и нужного выражения. Дополнительные сведения о доступных агрегатных функциях см. в статье Статистические функции SQL.

Задание полей, которые не используются в агрегатной функции: предложение GROUP BY

При использовании агрегатных функций обычно необходимо создать предложение GROUP BY. В предложении GROUP BY указываются все поля, к которым не применяется агрегатная функция. Если агрегатные функции применяются ко всем полям в запросе, предложение GROUP BY создавать не нужно.

Предложение GROUP BY должно следовать сразу же за предложением WHERE или FROM, если предложение WHERE отсутствует. В предложении GROUP BY поля указываются в том же порядке, что и в предложении SELECT.

Продолжим предыдущий пример. Пусть в предложении SELECT агрегатная функция применяется только к полю [Адрес электронной почты], тогда предложение GROUP BY будет выглядеть следующим образом:

GROUP BY Company

Дополнительные сведения о предложении GROUP BY см. в статье Предложение GROUP BY.

Ограничение агрегированных значений с помощью условий группировки: предложение HAVING

Если необходимо указать условия для ограничения результатов, но поле, к которому их требуется применить, используется в агрегированной функции, предложение WHERE использовать нельзя. Вместо него следует использовать предложение HAVING. Предложение HAVING работает так же, как и WHERE, но используется для агрегированных данных.

Предположим, например, что к первому полю в предложении SELECT применяется функция AVG (которая вычисляет среднее значение):

SELECT COUNT([E-mail Address]), Company

Если вы хотите ограничить результаты запроса на основе значения функции COUNT, к этому полю нельзя применить условие отбора в предложении WHERE. Вместо него условие следует поместить в предложение HAVING. Например, если нужно, чтобы запрос возвращал строки только в том случае, если у компании есть несколько адресов электронной почты, можно использовать следующее предложение HAVING:

Читать еще:  Активировать офис 365 бесплатно

HAVING COUNT([E-mail Address])>1

Примечание: Запрос может включать и предложение WHERE, и предложение HAVING, при этом условия отбора для полей, которые не используются в статистических функциях, указываются в предложении WHERE, а условия для полей, которые используются в статистических функциях, — в предложении HAVING.

Дополнительные сведения о предложении HAVING см. в статье Предложение HAVING.

Объединение результатов запроса: оператор UNION

Оператор UNION используется для одновременного просмотра всех данных, возвращаемых несколькими сходными запросами на выборку, в виде объединенного набора.

Оператор UNION позволяет объединить две инструкции SELECT в одну. Объединяемые инструкции SELECT должны иметь одинаковое число и порядок выходных полей с такими же или совместимыми типами данных. При выполнении запроса данные из каждого набора соответствующих полей объединяются в одно выходное поле, поэтому выходные данные запроса имеют столько же полей, сколько и каждая инструкция SELECT по отдельности.

Примечание: В запросах на объединение числовой и текстовый типы данных являются совместимыми.

Используя оператор UNION, можно указать, должны ли в результаты запроса включаться повторяющиеся строки, если таковые имеются. Для этого следует использовать ключевое слово ALL.

Запрос на объединение двух инструкций SELECT имеет следующий базовый синтаксис:

Предположим, например, что имеется две таблицы, которые называются «Товары» и «Услуги». Обе таблицы содержат поля с названием товара или услуги, ценой и сведениями о гарантии, а также поле, в котором указывается эксклюзивность предлагаемого товара или услуги. Несмотря на то, что в таблицах «Продукты» и «Услуги» предусмотрены разные типы гарантий, основная информация одна и та же (предоставляется ли на отдельные продукты или услуги гарантия качества). Для объединения четырех полей из двух таблиц можно использовать следующий запрос на объединение:

Дополнительные сведения об объединении инструкций SELECT с помощью оператора UNION см. в статье Просмотр объединенных результатов нескольких запросов с помощью запроса на объединение.

Sql запросы в access

Примеры запросов SQL могут использованы для изучения и практикума написания запросов SQL в MS Access.

Запрос Q021. Следующий запрос SQL вычисляет на какую сумму было продано товаров, имеющих код 4 или 6:

Запрос Q022. Вычисляется на какую сумму было продано 12 июня 2011 года товаров, имеющих код 4 или 6:

Запрос Q023. Задача такова. Вычислить на какую общую сумму было оприходовано товаров категории «Хлебобулочные изделия».

Для решения этой задачи нужно оперировать тремя таблицами: m_income, m_product и m_category, потому что:
— количество и цена оприходованных товаров хранятся в таблице m_income;
— код категории каждого товара хранится в таблице m_product;
— название категории title хранится в таблице m_category.

Для решения данной задачи воспользуемся следующим алгоритмом:
— определение кода категории «Хлебобулочные изделия» из таблицы m_category посредством подзапроса;
— соединение таблиц m_income и m_product для определения категории каждого оприходованного товара;
— вычисление суммы прихода( = количество*цена) для товаров, код категории которых равен коду, определенному вышеуказанным подзапросом.

Запрос Q024. Задачу вычисления общей суммы оприходованных товаров категории «Хлебобулочные изделия» решим следующим алгоритмом:
— каждой записи таблицы m_income, в зависимости от значения его product_id, из таблицы m_category, сопоставить название категории;
— выделить записи, для которых категория равна «Хлебобулочные изделия»;
— вычислить сумму прихода = количество*цена.

Запрос Q025. В этом примере вычисляется сколько наименований товаров было израсходовано:

Запрос Q026. Инструкция GROUP BY используется для группировки записей. Обычно записи группируются по значению одного или нескольких полей, и относительно каждой группы применяется какая-либо агрегатная операция. Например, следующий запрос состявляет отчет о продаже товаров. То есть генерируется таблица, в которой будут названия товаров и сумма, на которую они проданы:

Запрос Q027. Отчет о продажах по категориям. То есть генерируется таблица, в которой будут названия категорий товаров, общая сумма, на которую проданы товары данных категорий, и средняя сумма продаж. Функция ROUND использована для округления среднего значения до сотой доли (второй знак после разделителя целой и дробной частей):

Запрос Q028. Вычисляется для каждого товара общее и среднее количество его поступлений и выводит информацию о товарах, общее количество поступления которых не менее 500:

Запрос Q029. В этом запросе вычисляется для каждого товара сумма и среднее значение его поступлений, осуществленных во втором квартале 2011 года. Если общая сумма прихода товара не менее 1000, то отображается информация об этом товаре:

Запрос Q030. В некоторых случаях нужно сопоставлять каждой записи некоторой таблицы каждую запись другой таблицы; что называется декартовым произведением. Таблица, образующаяся в результате такого соединения, называется таблицей Декарта. Например, если некоторая таблица А имеет 100 записей и таблица В имеет 15 записей, то их таблица Декарта будет состоять из 100*15=1500 записей. Следующий запрос соединяет каждую запись таблицы m_income с каждой записью таблицы m_outcome:

Sql запросы в access

Рассмотрим основное отношение, соответствующее теме «Деятельность музея»:Код экспоната, Наименование, Код зала, Дата поступления, Автор, Материал, Техника, Наименование зала, Код ответственного, Код сотрудника, ФИО, Оклад, Должность, Код экскурсии, Время проведения, График, Срок действия. Данное отношение находится в первой нормальной форме, поскольку все его атрибуты являются неделимыми и имеет составной ключ: Код экспоната, Код зала, Код сотрудника, Код экскурсии.

Приведение отношения к третьей нормальной форме

Некоторые атрибуты зависят только от части составного ключа. Устраним частичную зависимость и переведем это отношение во вторую нормальную форму путем декомпозиции основного отношения на следующие отношения:
1) Код экспоната, Наименование, Код зала, Дата поступления, Автор, Материал, Техника
2) Код зала, Наименование зала, Код ответственного
3) Код сотрудника, ФИО, Оклад, Должность
4) Код экскурсии, Время проведения, График, Срок действия, Код ответственного, Стоимость.
Отношения 1,2,3 и 4 находятся в третьей нормальной форме, поскольку они находятся во второй нормальной форме, и каждый неключевой атрибут нетранзитивно зависит от первичного ключа.
Итак, спроектировали 4 таблицы.

Определение ключей и создание связей схемы связей.

Определим первичные и внешние ключи в таблицах и необходимые связи между атрибутами таблиц для обеспечения целостности БД.
В 1-ой таблице первичный ключ — Код экспоната. Атрибут Код зала является внешним ключом к таблице 2. Во 2-ой таблице первичный ключ- Код зала. Атрибут Код ответственного является внешним ключом по отношению к таблице 3. В 3-ей таблице первичный ключ- Код сотрудника. В 4-ой таблице первичный ключ — Код экскурсии, атрибут Код ответственного является внешним ключом по отношению к таблице 3.
Создадим схему связей между атрибутами таблиц для обеспечения целостности БД.

Создание файла базы данных в СУБД Microsoft Access 2003 .

Создадим файл спроектированной БД в СУБД Microsoft Access 2003. Для этого запустим программу Microsoft Access 2003, выберем в окне команду Создать файл, затем в окне Создание файла выберем пункт Новая база данных и укажем диск, каталог и имя «Деятельность музея».
Создадим таблицы, для этого выберем объект Таблицы и кнопку Создать. В режиме Конструктор создадим таблицы со следующей структурой:

Изменение структуры таблиц с помощью SQL запроса

С помощью SQL-запроса изменим структуру таблицы Залы. Добавим атрибут Площадь, тип данных – числовой.
ALTER TABLE Залы ADD COLUMN Площадь FLOAT;

Внесение данных в таблицы.

В окне базы данных выберем объект Таблицы, установим курсор на нужную таблицу и нажмем кнопку Открыть. В режиме Таблицы введем записи в таблицы:

Изменение данных с помощью SQL запросов в MS Access

Запрос на добавление данных в таблицу Залы:
INSERT INTO Залы ( [Код зала], Наименование, [Код ответственного], Площадь )
VALUES (6, «Серебряный», 1, 145);

Запрос на удаление данных из таблицы Сотрудники Удалим записи с фамилией сотрудника, содержащей « Мечникова»:

DELETE ФИО
FROM Сотрудники
WHERE ФИО LIKE “Мечникова”;

Запрос на обновление данных. В таблице Издания увеличим стоимость объявлений изданий с кодами 1711 и 1712.

UPDATE Сотрудники SET Оклад = Оклад*1.2
WHERE (Сотрудники.Должность Like «*контроллер»);

Запрос на создание таблицы Расписание:

SELECT Экскурсии.[Время проведения], Экскурсии.График, Сотрудники.ФИО INTO Расписание
FROM Сотрудники INNER JOIN Экскурсии ON Сотрудники.[Код сотрудника] = Экскурсии.[Код ответственного];

Ссылка на основную публикацию
Adblock
detector