Создание таблиц с помощью SQL-запроса

⇐ ПредыдущаяСтр 5 из 6Следующая ⇒

Предположим, необходимо создать таблицу по следующему SQL-запросу:

CREATE TABLE Продажи

(

ДатаОперации DATETIME NOT NULL,

НомерПродажи INTEGER NOT NULL,

НаименованиеКлиента VARCHAR(50),

НаименованиеТовара VARCHAR(30),

Количество NUMERIC(6, 2),

Цена NUMERIC(6, 2),

Сумма NUMERIC(10, 2),

PRIMARY KEY (НомерПродажи));

Для выполнения запроса на создание таблицы необходимо:

1. В главном окне базы данных перейти на вкладку « Запросы» и
выполнить команду « Создание запроса в режиме конструктора».

2. Отказаться от создания запроса на основе другого запроса или таблицы в следующем диалоге. Для этого необходимо нажать кнопку «Закрыть»

3. Выполнить команду Вид ® Режим SQL.

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

5. Выполнить команду Файл ® Сохранить и установить для нового запроса имя «Создание таблицы Продажи».

6. Закрыть окно запроса.

7. Как видно из следующего рисунка, в базе данных появился новый объект – запрос «Создание таблицы Продажи».

Теперь для создания таблицы базы данных посредством данного запроса необходимо просто выполнить двойной щелчок мышью на запросе «Создание таблицы продажи» и ответить « Да» в следующем диалоге.

В результате на вкладке « Таблицы» главного окна базы данных появится новая строка – строка « Продажи». Это означает, что в результате выполнения запроса была создана таблица базы данных.

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

ВНИМАНИЕ! Создание любой современной базы данных и информационной системы на ее основе должно основываться на использовании стандартов языка SQL при работе с данными: как при создании структуры базы данных, так и при чтении или записи данных в базу данных. Это означает, что для любых операций с данными в первую очередь должен использоваться язык SQL.

Создание запросов на объединение таблиц

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

Пример 1.

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

Дата Операции	Код Товара	Единица Измерения	Поступление	Отпуск	Возврат	Списание
…	…	…	…	…	…	…

Графа Поступление формируется из поля «Количество» таблицы «Поступление» и «ПоступлениеСостав».

Графа Отпуск формируется из поля «Количество» таблицы
«Отпуск» и «ОтпускСостав».

Графа Возврат формируется из поля «Количество» таблицы «Возврат» и «ВозвратСостав».

Графа Списание формируется из поля «Количество» таблицы «Списание» и «СписаниеСостав».

Графа ДатаОперации формируется из полей Поступление.ДатаПоступления, Отпуск.ДатаОтпуска, Возврат.ДатаВозврата, Списание.ДатаСписания.

Графа КодТовара формируется из полей Поступление.КодТовара, Отпуск.КодТовара, Возврат. КодТовара, Списание. КодТовара.

Опуская процесс создания детальных запросов[5], предположим, что детальные запросы для граф «Поступление», «Отпуск», «Возврат» и «Списание» уже созданы.

Наименование запроса	Назначение
ОперацииПоступления	Детальный запрос для получения списка операций по поступлению товара на основе таблиц «Поступление» и «ПоступлениеСостав»
ОперацииОтпуска	Детальный запрос для получения списка операций по отпуску товара на основе таблиц «Отпуск» и «ОтпускСостав»
ОперацииВозврата	Детальный запрос для получения списка операций по возврату товара от покупателей на основе таблиц «Возврат» и «ВозвратСостав»
ОперацииСписания	Детальный запрос для получения списка операций по списанию товара на основе таблиц «Списание» и «СписаниеСостав»

Приведем результаты выполнения запросов.

Результат выполнения запроса « ОперацииПоступления » представлен на следующем рисунке:

Результат выполнения запроса « ОперацииОтпуска » представлен на следующем рисунке:

Результат выполнения запроса « ОперацииВозврата » представлен на следующем рисунке:

Результат выполнения запроса « ОперацииСписания » представлен на следующем рисунке:

Решение

1. Создадим запрос, приводящий запрос «ОперацииПоступления» к структуре таблицы объединения. Для этого произведем следующие действия.

1.1. Выполним команду Создание запроса с помощью конструктора.

1.2. Добавим в конструктор запрос «ОперацииПоступления».

1.3. Перенесем поля «ДатаПоступления», «КодТовара», «ЕдиницаИзмерения», «Количество» в область свойств запроса. Получим:

1.4. Переименуем поле «ДатаПоступления» в «ДатаОперации».

1.5. Переименуем поле «Количество» в «Поступление».

1.6. Добавим в запрос виртуальное поле «Отпуск», установив для него нулевое значение[6]. Для этого необходимо:

- перейти в свободную графу области свойств запроса;

- в строке Поле ввести текст «Отпуск: 0»;

1.7. Добавим в запрос виртуальное поле «Возврат», установив для него нулевое значение. Для этого необходимо:

- перейти в свободную графу области свойств запроса;

- в строке Поле ввести текст «Возврат: 0».

1.8. Добавим в запрос виртуальное поле «Списание», установив для него нулевое значение. Для этого необходимо:

- перейти в свободную графу области свойств запроса;

- в строке поля ввести текст «Списание: 0».

В результате окно конструктора будет выглядеть так:

1.9. Выполним команду Файл ® Сохранить и установим для нового запроса имя «Форма1_ОперацииПоступления».

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

Как видно из рисунка, полученная структура запроса « Форма1_ОперацииПоступления » по составу полей полностью соответствует таблице, которую необходимо получить в результате объединения.

Приведем к такой же структуре запросы «ОперацииОтпуска», «ОперацииВозврата», «ОперацииСписания».

2. Приведем запрос «ОперацииОтпуска» к структуре, аналогичной таблице объединения.

Приведение запроса «ОперацииОтпуска» к единой структуре полностью идентично, процессу рассмотренному в п.1. Отличие заключается в том, что поле «Количество» переименовывается в поле «Отпуск», а значения полей «Поступление», «Возврат» и «Списание» устанавливаются равными нулевым значениям. В результате мы получим запрос «Форма1 _ ОперацииОтпуска».

Выполнив этот запрос, получим:

3. Приведем запрос «ОперацииВозврата» к структуре, аналогичной таблице объединения.

Приведение запроса «ОперацииВозврата» к единой структуре полностью идентично процессу, рассмотренному в п.1. Отличие заключается в том, что поле «Количество» переименовывается в поле «Возврат», а значения полей «Поступление», «Отпуск» и «Списание» устанавливаются равными нулевым значениям. В результате мы получим запрос «Форма1_ОперацииВозврата».

4. Приведем запрос «ОперацииСписания» к структуре, аналогичной таблице объединения.

Приведение запроса «ОперацииСписания» к единой структуре полностью идентично процессу, рассмотренному в п.1. Отличие заключается в том, что поле «Количество» переименовывается в поле «Списание», а значения полей «Поступление», «Отпуск» и Возврат» устанавливаются равными нулевым значениям. В результате мы получим запрос «Форма1_ОперацииСписания».

5. Создадим запрос, объединяющий результаты запросов «Форма1_ОперацииПоступления», «Форма1_ОперацииОтпуска», «Форма1_ ОперацииВозврата», «Форма1_ОперацииСписания» в единую таблицу. Для этого произведем следующие действия.

5.1. Выполним команду Создание запроса с помощью конструктора.

5.2. Выполним команду Вид ® Режим SQL.

5.3. Наберем текст запроса на языке SQL, как это показано на рисунке:

5.4. Выполним команду Файл ® Сохранить и присвоим запросу имя «Форма1».

Просмотрим результаты запроса, выполнив команду Вид ® Режим таблицы.

ПРИМЕЧАНИЕ:

Чтобы вместо поля «КодТовара» получить поле «НаименованиеТовара», как уже известно из построения детального запроса, необходимо:

- создать новый запрос;

- перенести туда запрос «Форма1» и таблицу «Товар»;

- установить связь между таблицей «Товар» и запросом «Форма1» по полю «КодТовара» (параметры объединения – INNER JOIN);

- перенести в результирующую таблицу (область свойств запроса) все поля
запроса «Форма1»;

- из таблицы «Товар» перенести поле «НаименованиеТовара» в результирующую таблицу;

- сохранить запрос.

Как видно из таблицы, в результате выполнения шагов 1-5 данные нескольких таблиц были собраны в одну. Теперь можно выполнять с этими данными любые другие операции, такие как отбор, группировка и расчет вычисляемых полей.

Например, рассчитать остаток товара на складе на определенную дату. Для этого необходимо:

1. Отобрать данные из запроса «Форма1» за период, т.е. по условию:

ДатаОперации < [ДатаРасчетаОстатка]

1.1. Выполнить команду Создание запроса с помощью конструктора;

1.2. Добавить в конструктор запрос «Форма1»;

1.3. Перенести все поля в область свойств запроса – таблицу результатов;

1.4. В графе ДатаОперации в строке Условие отбора ввести текст условия:

< [ДатаРасчетаОстатка].

В результате получим:

1.5. выполнить команду Файл ® Сохранить и в качестве имени для этого запроса текст: «ОстатокТоваровНаДату1».

2. На основе запроса, полученного в п.1. («ОстатокТовараНаДату1»), провести группировку по полю «КодТовара» с расчетом сумм по полям «Поступление», «Отпуск», «Возврат», «Списание».

2.1. Выполнить команду «Создание запроса c помощью конструктора».

2.2. Добавить в конструктор запрос «ОстатокТоваровНаДату1».

2.3. Перенести в таблицу результатов запроса поля «КодТовара», «Поступление», «Отпуск», «Возврат», «Списание».

2.4. Выполнить команду Вид ® Групповые операции.

2.5. Установить в графах «Поступиление», «Отпуск», «Возврат», «Остаток» в строке «Группировка» выражение «SUM», для расчета сумма по указанным полям.

В результате окно конструктора будет выглядеть так:

Зная, что Access автоматически переименует поля, по которым рассчитывается сумма, установим имена для полей запроса.

2.6. В графу «Поступление» в строку «Поле: » введем текст «Поступление: Поступление».

2.7. В графу «Отпуск» в строку « Поле: » введем текст «Отпуск:
Отпуск».

2.8. В графу «Возврат » в строку «Поле: » введем текст «Возврат: Возврат».

2.8. В графу «Списание » в строку «Поле: » введем текст «Списание: Списание».

2.9. Выполним команду Файл ® Сохранить и введем имя запроса «ОстатокТоваровНаДату2».

3. На основе запроса, полученного в п.2, провести создание вычисляемого поля Остаток по формуле:

Остаток = Поступление – Отпуск + Возврат – Списание

3.1. Выполнить команду Создание запроса с помощью конструктора.

3.2. Добавить в конструктор запрос «ОстатокТоваровНаДату2».

3.3. Перенести все поля запроса в таблицу результатов запроса.

3.4. Установить курсор в свободную колонку таблицы результатов запроса, как это показано на рисунке:

3.6. Нажать Shift + F2 для перехода в диалоговую область ввода.

3.7. В области ввода ввести текст расчета значения вычисляемого поля «Остаток», как это показано на следующем рисунке:

3.8. Нажать « OК».

3.9. Выполнить команду Файл ® Сохранить и ввести имя этого запроса: «ОстатокТоваровНаДату3».

Просмотрим результаты запроса, выполнив команду Вид ® Режим таблицы. В качестве начальной даты введем «29.02.2004». Получим:

Просмотрим текст запроса, сгенерированный конструктором, выполнив команду Вид ® Режим SQL. Получим:

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

Для большей наглядности полученных результатов в запросе «ОстатокТоваровНаДату3» результаты сведем к следующей таблице:

НаименованиеТовара

Остаток

4. На основе запроса «ОстатокТоваровНаДату3» получим таблицу указанной выше формы. Для этого необходимо:

4.1. Выполнить команду «Создание запроса с помощью конструктора».

4.2. Добавить в конструктор запрос «ОстатокТоваровНаДату3» и таблицу «Товар».

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

Если связь не установилась, нужно установить ее вручную, перетащив поле «КодТовара» из таблицы «Товар» на поле «КодТовара» в запросе «ОстатокТоваровНаДату3».

4.3. Добавить поле «НаименованиеТовара» из таблицы «Товар» в таблицу результатов запроса.

4.4. Добавить поле «Остаток» из запроса «ОстатокТоваровНаДату3».

4.5. В графе НаименованиеТовара в строке Сортировка установить значение по возрастанию.

В результате выполненных действий 4.1- 4.5 получим:

4.6. Выполнить команду Файл ® Сохранить и ввести текст названия запроса «ОстатокТоваровНаДату4».

Просмотрим результаты запроса, выполнив команду Вид ® Режим таблицы. В качестве даты расчета остатков введем «29.02.2004». Получим:

⇐ Предыдущая 1 2 3 456 Следующая ⇒