База данных "фруктовый сад" (стр. 3 из 4)

Рисунок 4.1 - главная форма, где присутствуют кнопки печати

5. Выборочный доступ к данным

Язык манипулирования данными (DML) является сердцем SQL. Для каждого добавления, изменения или удаления данных из базы данных выполняется команда DML. Совокупность команд DML, результаты действия которых еще не стали постоянными, организуют транзакцию.

Рассмотрим следующие операторы языка SQL DML:

1) SELECT - выборка данных из базы;

2) INSERT - вставка данных в таблицу;

3) UPDATE - обновление данных в таблице;

4) DELETE - удаление данных из таблицы.

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

SELECT [DISTINCT| ALL] {* I [columnExpression [AS newName]] [,…] }

FROM TableName [alias] [,...]

[WHERE condition]

[GROUP BY columnList] [HAVING condition]

[QRDERBYcolumnList]

Здесь параметр columnExpressionпредставляет собой имя столбца или выражение из нескольких имен. Параметр TableNameявляется именем существующей в базе данных таблицы (или представления), к которой необходимо получить доступ. Необязательный параметр alias - это сокращение, устанавливаемое для имени таблицы TableName. Обработка элементов оператора SELECT выполняется в следующей последовательности:

1) FROM - определяются имена используемой таблицы или нескольких таблиц;

2) WHERE - выполняется фильтрация строк объекта в соответствии с заданными условиями;

3) GROUP BY - образуются группы строк, имеющих одно и то же значение в указанном столбце;

4) HAVING - фильтруются группы строк объекта в соответствии с указанным условием;

5) SELECT - устанавливается, какие столбцы должны присутствовать в выходных данных;

6) ORDER BY - определяется упорядоченность результатов выполнения оператора.

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

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

INSERTINTOTableName [ (columnList)]

VALUES (dataValueList)

Здесь параметр TableNameможет представлять либо имя таблицы базы данных, либо имя обновляемого представления. Параметр colunmListпредставляет собой список, состоящий из имен одного или более столбцов, разделенных запятыми. Параметр coIumnList является необязательным. Если он опущен, то предполагается использование списка из имен всех столбцов таблицы, указанных в том порядке, в котором они были описаны в операторе CREATE TABLE. Если в операторе INSERT указывается конкретный список имен столбцов, то любые опущенные в нем столбцы должны быть объявлены при создании таблицы как допускающие значение NULL - за исключением случаев, когда при описании столбца использовался параметр DEFAULT. Параметр dataValueListдолжен следующим образом соответствовать параметру columnList:

1) количество элементов в обоих списках должно быть одинаковым;

2) должно существовать прямое соответствие между позицией одного и того же элемента в обоих списках, поэтому первый элемент списка dataValuelist считается относящимся к первому элементу списка columnList, второй элемент списка dataValuelist - ко второму элементу списка columnListи т.д.;

3) типы данных элементов списка dataValueListдолжны быть совместимы с типом данных соответствующих столбцов таблицы.

Вторая форма оператора INSERT позволяет скопировать множество строк одной таблицы в другую. Этот оператор имеет следующий формат:

INSERTINTOTableName [ (columnList)]

SELECT …

Здесь параметры TableName и columnListимеют тот же формат и смысл, что и при вставке в таблицу одной строки. Конструкция SELECT может представлять собой любой допустимый оператор SELECT. Строки, вставляемые в указанную таблицу, в точности соответствуют строкам результирующей таблицы, созданной при выполнении вложенного запроса. Все ограничения, указанные выше для первой формы оператора INSERT, применимы и в этом случае.

Оператор UPDATE позволяет изменять содержимое уже существующих строк указанной таблицы. Этот оператор имеет следующий формат:

UPDATETableName

SETcolumnName1= dataValue1 [,columnName2= dataValue2…]

[WHEREsearchCondition]

Здесь параметр TableNameпредставляет либо имя таблицы базы данных, либо имя обновляемого представления. В конструкции SET указываются имена одного или более столбцов, данные в которых необходимо изменить. Конструкция WHERE является необязательной. Если она опущена, значения указанных столбцов будут изменены во всехстроках таблицы. Если конструкция WHERE присутствует, то обновлены будут только те строки, которые удовлетворяют условию поиска, заданному в параметре searchCondition. Параметры dataValue1, dataValue2... представляют новые значения соответствующих столбцов и должны быть совместимы с ними по типу данных.

Оператор DELETE позволяет удалять строки данных из указанной таблицы. Этот оператор имеет следующий формат:

DELETE FROM TableName

[WHERE searchCondition]

Как и в случае операторов INSERT и UPDATE, параметр TableNameможет представлять собой либо имя таблицы базы данных, либо имя обновляемого представления. Параметр searchConditionявляется необязательным - если он опущен, из таблицы будут удалены всесуществующие в ней строки. Однако сама по себе таблица удалена не будет. Если необходимо удалить не только содержимое таблицы, но и ее определение, следует использовать оператор DROP TABLE. Если конструкция WHERE присутствует, из таблицы будут удалены только те строки, которые удовлетворяют условию отбора, заданному параметром searchCondition

В данном курсовом проекте для удобства доступа к данным также созданы запросы:

Запрос на баланс:

SELECT shelfs. quantity-selling. quantity AS quantity

FROM shelfs INNER JOIN selling ON shelfs. ID_shelf=selling. ID_shelf

WHERE selling. ID_selling = (SELECT MAX (ID_selling) FROM selling);

Запрос на Города с покупателями:

SELECT cities. cityName AS Город, COUNT (ID_client) AS [Количество клиентов]

FROM clients INNER JOIN cities ON clients. clientCity=cities. ID_city

GROUP BY clients. ID_client, cities. cityName;

Запрос на города с поставщиками:

SELECT cities. cityName AS Город, COUNT (ID_diler) AS [Количество поставщиков]

FROM dilers INNER JOIN cities ON dilers. dilerCity=cities. ID_city

GROUP BY dilers. ID_diler, cities. cityName;

Запрос на дату покупки товара:

SELECT ID_shelf AS Стеллаж, products. name AS Товар, shelfs. quantity AS Количество, shelfs. price AS Стоимость, shelfs. date AS [Дата покупки]

FROM shelfs INNER JOIN products ON products. ID_product=shelfs. ID_product;

Запрос на клиентов без покупки:

SELECT clients. clientName AS [Покупатели, не совершавшие покупки]

FROM clients

WHERE ID_client NOT IN (SELECT DISTINCT ID_client FROM Selling)

ORDER BY clientName;

Запрос на отчет по покупкам:

SELECT dilers. dilerName AS Поставщик, products. name AS Товар, buying. quantity AS [Количество товара], buying. sum*buying. quantity AS [Сумма сделки], buying. date AS [Дата сделки]

FROM (buying INNER JOIN dilers ON buying. ID_diler=dilers. ID_diler) INNER JOIN products ON buying. ID_product=products. ID_product;

Запрос на отчет по продажам:

SELECT clients. clientName AS Клиент, products. name AS Товар, selling. quantity AS [Количество товара], selling. sum*selling. quantity AS [Сумма сделки], selling. date AS [Дата сделки]

FROM ( (selling INNER JOIN clients ON selling. ID_client=clients. ID_client) INNER JOIN shelfs ON shelfs. ID_shelf=selling. ID_shelf) INNER JOIN products ON shelfs. ID_product=products. ID_product;

Запрос на поставщиков и закупки:

SELECT dilers. dilerName AS Поставщик, COUNT (ID_buying) AS [Количество сделок], SUM (buying. quantity*buying. sum) AS [Сумма всех сделок]

FROM dilers INNER JOIN buying ON dilers. ID_diler=buying. ID_diler

GROUP BY dilers. ID_diler, dilers. dilerName;

Запрос на поставщиков без поставок:

SELECT dilers. dilerName AS [Поставщики, не совершавшие поставок]

FROM dilers

WHERE ID_diler NOT IN (SELECT DISTINCT ID_diler FROM buying)

ORDER BY dilerName;

Запрос на продажи с клиентами:

SELECT clients. clientName AS Покупатель, COUNT (ID_selling) AS [Количество сделок], SUM (selling. quantity*selling. sum) AS [Сумма всех сделок]

FROM clients INNER JOIN selling ON clients. ID_client=selling. ID_client

GROUP BY clients. ID_client, clients. clientName;

Запрос на стоимость товаров на складе:

SELECT SUM (price*quantity) AS [Стоимость всех товаров на складе]

FROM shelfs;

Запрос на вставку обновлений продажи:

INSERT INTO selling (ID_client, ID_shelf, quantity, [sum])

SELECT ID_client, (SELECT ID_shelf FROM shelfs WHERE ID_shelf= (SELECT MAX (ID_shelf) FROM shelfs)), quantity, sum

FROM selling

WHERE ID_selling = (SELECT MAX (ID_selling) FROM selling);

Запрос на обновление товара (добавление):

INSERT INTO shelfs (ID_product, price, quantity)

SELECT ID_product, price, (SELECT shelfs. quantity-selling. quantity AS quantity FROM shelfs INNER JOIN selling ON shelfs. ID_shelf=selling. ID_shelf WHERE selling. ID_selling = (SELECT MAX (ID_selling) FROM selling))

FROM shelfs

WHERE ID_shelf = (SELECT ID_shelf FROM selling WHERE ID_selling = (SELECT MAX (ID_selling) FROM selling));

Запрос на обновление товара (удаление):

DELETE *

FROM shelfs

WHERE ID_shelf = (SELECT ID_shelf FROM selling WHERE ID_selling = (SELECT MAX (ID_selling) FROM selling));

Запрос на откат продажи:

DELETE *

FROM selling

WHERE ID_selling = (SELECT MAX (ID_selling) FROM selling);

Запрос на покупку товара:

INSERT INTO shelfs (ID_product, quantity, price)

SELECT ID_product, quantity, sum

FROM buying

WHERE ID_buying =

(SELECT MAX (ID_buying) FROM buying);

Запрос на удаление товара:

DELETE *

FROM shelfs

WHERE ID_shelf = (SELECT ID_shelf FROM selling WHERE ID_selling = (SELECT MAX (ID_selling) FROM selling));

Запрос на удаление старой продажи:

DELETE *

FROM selling

WHERE ID_selling = (SELECT MAX (ID_selling) - 1 FROM selling);

Почти все эти запросы можно увидеть на главной форме в виде таблиц при нажатии кнопки.

6. Инструкция администратору, инструкция пользователю

6.1 Инструкция администратору

Работать с нашей базой данных достаточно легко, зная основные свойства пакета MicrosoftOfficeAccess. Запуская наш курсовой проект, мы видим главную форму, на которой расположены различные кнопки, отвечающие за функции, указанные на них. Таким образом, можно достаточно легко работать с таблицами, отчетами и т.д. Если же необходимо что-то изменить в корне таблиц, то открываем их с помощью конструктора. Также в этом режиме можно создавать новые таблицы. При этом указываем имя поля, тип данных, и устанавливаем ключевой атрибут. Чтоб внести какие-либо данные в новую таблицу, необходимо открыть ее уже в режиме таблицы и заносить данные. Затем таблицу сохраняют, после чего успешно можно выполнять работу. Можно создавать также запросы, формы, отчеты, макросы для удобства работы с базой данных. Это все объекты базы данных, которыми можно с легкостью манипулировать. Желательно, чтоб в главной форме были кнопки всех таблиц, чтоб был кратчайший доступ к ним, но также, чтоб эти таблицы были в виде формы, чтоб можно было, используя кнопки, добавлять запись или удалять ее, что предусмотрено в курсовом проекте. Чтоб создать кнопку на форме, мы выбираем ее, отме6чаем на форме и назначаем в процессе ей какие-то функции. Запросы же пишем с помощью SQL.