Выпуск #5/10/03/2006

Что такое SQL?

Structured Query Language (структурированный язык запросов) – язык построения запросов для работы с данными, хранящимися в виде таблиц. Он позволяет:

1. Создавать и удалять таблицы и индексы;

2. Менять структуру таблиц;

3. Вводить и удалять данные;

4. Выбирать и группировать имеющиеся данные.

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

Запрос на выборку.

 Оператор SELECT

Возвращает набор записей (работает через Microsoft Jet). Формат:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

Оператор SELECT имеет следующие части:

Предикаты ALL, DISTINCT, DISTINCTROW, TOP

Указывают способ отбора записей в SQL запросе. Формат

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]] FROM table

Выражение FROM

Указывает таблицы или запросы, содержащие поля списка SELECT. Формат

SELECT fieldlist FROM tableexpression [IN externaldatabase]

Оператор SELECT с FROM имеет следующие части:

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

Выражение WHERE

Указывает, на какие записи таблиц, перечисленных в выражении FROM действуют операторы SELECT, UPDATE или DELETE. Формат

SELECT fieldlist FROM tableexpression WHERE criteria

Оператор SELECT, содержащий выражение WHERE, имеет следующие части:

WHERE не обязательно, но если указано, следует за FROM. Microsoft Jet отбирает записи, удовлетворяющие условию в выражении WHERE. Например, можно выбрать всех сотрудников отдела продаж (WHERE Dept = 'Sales') или всех клиентов возрастом от 18 до 30 (WHERE Age Between 18 And 30). Если оно не указано, выбираются все записи. Если указано несколько таблиц и нет выражения WHERE или JOIN, формируется декартово произведение таблиц.

Операции LEFT JOIN, RIGHT JOIN

Комбинируют записи из таблиц-источников в выражении FROM. В результате создаются записи с большим количеством полей (таблица «расширяется»). Формат

FROM table1 [ LEFT | RIGHT ] JOIN table2 ON table1.field1 compopr table2.field2

Операции LEFT JOIN и RIGHT JOIN имеют следующие части:

Операция LEFT JOIN объединяет каждую запись из первой (левой) таблицы с теми записями из второй (правой), которые удовлетворяют критерию compopr. При этом из первой таблицы в набор включаются все записи – левое внешнее объединение.

Операция RIGHT JOIN объединяет каждую запись из второй (правой) таблицы с теми записями из первой (левой), которые удовлетворяют критерию compopr. При этом из второй таблицы в набор включаются все записи – правое внешнее объединение.

Операция INNER JOIN

Комбинирует записи из таблиц-источников в выражении FROM. Формат

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

Операция INNER JOIN имеет следующие части:

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

Выражение IN

Указывает таблицу во внешней базе данных, с которой Microsoft Jet может установить соединение (например, dBASE, Paradox или внешняя Microsoft Jet). Формат указания целевой таблицы

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

формат указания таблицы-источника:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

Оператор SELECT, содержащий выражение WHERE, имеет следующие части:

IN позволяет одновременно подсоединиться только к одной внешней базе.

Аргумент указывает директорию, содержащую данные. Например, в случае dBASE, FoxPro или Paradox – директорию с .dbf или .db файлами. Имя таблицы извлекается из destination или tableexpression аргументов.

Для не Microsoft Jet баз тип базызаключается в апострофы или кавычки, а в конец добавляется точка с запятой: 'dBASE IV;' или "dBASE IV;". Можно использовать служебное слово DATABASE. Следующие выражения эквивалентны:

... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];

... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"

Выражение ORDER BY

Сортирует результат запроса по указанному полю/полям в порядке возрастания (по умолчанию) или убывания. Формат

SELECT fieldlist FROM table WHERE selectcriteria [ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][, ...]]]

Оператор SELECT, содержащий выражение ORDER BY, имеет следующие части:

Выражение ORDER BY не обязательно, но если нужно упорядочить результат – необходимо. ORDER BY обычно последний элемент оператора SQL.По умолчанию результат сортируется по возрастанию (от A до Z, от 0 до 9). Следующие два оператора сортируют фамилии по возрастанию:

SELECT LastName, FirstName FROM Employees ORDER BY LastName;

SELECT LastName, FirstName FROM Employees ORDER BY LastName ASC;

Для сортировки по убыванию (от Z до A, от 9 до 0) после имени поля yказывается служебное слово DESC.

Выражение GROUP BY

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

SELECT fieldlist FROM table WHERE criteria [GROUP BY groupfieldlist]

Оператор SELECT, содержащий выражение GROUP BY, имеет следующие части:

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

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

Выражение HAVING

Указывает, какие сгруппированные записи выводятся оператором SELECT, содержащим выражение GROUP BY. После того, как GROUP BY сгруппирует записи, HAVING позволяет вывести только удовлетворяющие условию. Формат

SELECT fieldlist FROM table WHERE selectcriteria GROUP BY groupfieldlist
[HAVING groupcriteria]

Оператор SELECT, содержащий выражение HAVING, имеет следующие части:

Выражение HAVING не обязательно, но если указано, следует за GROUP BY. Аналогично WHERE. Например,

SELECT CategoryID, Sum(UnitsInStock) FROM Products GROUP BY CategoryID HAVING Sum(UnitsInStock) > 100 And Like "BOS*";

Может содержать до 40 логических выражений, объединенных операторами And или Or.

Оператор UNION

Объединяет результаты нескольких независимых запросов или таблиц («удлиняет» таблицу). Формат

[TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]]

query1 ... queryn – операторы SELECT, имена сохраненных запросов или имена таблиц (перед именами таблиц должно стоять TABLE). Можно объединять в любой комбинации. Все запросы в объединении должны возвращать одинаковое число полей, хотя поля могут различаться типами и размерами. Например:

TABLE [New Accounts] UNION ALL SELECT * FROM Customers
WHERE OrderAmount > 1000;

–                   объединяет существующую таблицу New Accounts и результаты запроса (SELECT).

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

Автор статьи: Leonid Maximov (AKA Leon).

 Продолжение в следующем выпуске….

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

              1. Ингредиенты от поставщиков поступают на склад сырья №1, в населенный пункт А.

              2. Ингредиенты авто транспортом перевозят из населенного пункта А, в населенный пункт B, на склад сырья №2.

              3. Со склада №2 ингредиенты передаются на производство.

              4. На производстве ингредиенты, условно говоря, перемешиваются (термин смешивание чисто условный, в действительности это гораздо более сложный тех процесс, и правильнее наверно будет назвать его переработкой ингредиентов), получается совершенно новый готовый продукт, с новым названием (итого 4 наименования). На каждый готовый продукт имеется рецептура, в которой указаны доли ингредиентов.

              5. Готовая продукция помещается на склад готовой продукции (склад №3).

              6 . Со склада №3 готовая продукция перевозиться в населенный пункт А, на склад №1.

              7. На складе №1 собираются заказы от клиентов.

              8. В соответствии с заказами, готовая продукция автотранспортом развозиться клиентам.

Прислал: Андрей Василенко.

-------------------------------------------------------------------------------------------------------------------------

Ответ №1.1

Примерный список таблиц и полей.

1. Основная таблица

<Predpr>

Kod - long     код предприятия/склада

FullName - c100

ShortName - c20

Town - c20

Подразумевается, что в каждом городе один склад, номер склада совпадает с номером предприятия. Если более одного - алгоритм сложнее, надо добавлять таблицу складов.

2. Ингридиенты и товары

<Tovar>

Kod - long     код товара/ингридиента

FullName - c50

ShortName - c10

Vid - byte     1-ингридиент

                   2-товар и др.

3. Рецептура. В связи с тем, что рецептура может меняться, целесообразно хранить все рецепты. Количество храниться в целочисленных полях, т.к. такие данные легче обрабатывать. В случае дробных частей, необходимо поделить на соответствующий множитель, например, учет ведется в граммах, тогда в поле можно хранить значения до 2 тыс.т. В теории можно обрабатывать числа до 10^28 грамм.

<Recept>

Kod  - long     код рецепта

Name - c100

Tovar  - long     код товара

Quantity- long     выход товара

можно добавить поле времени действия рецепта (дата).

4. Состав рецепта на указанный выход товара.

<Sostav>

Recept - long     код рецепта

Tovar  -  long     код ингридиента

Quantity - long     количество на рецепт

5. Текущие остатки на складах

<Ostatok>

Sklad  - long     код склада

Tovar - long     код товара/ингридиента

Quantity - long     количество на складе

6. Движение по складам

При перемещении со склада на склад создаются две записи - расход и приход. Возможно использование этих данных для печати транспортных накладных.

<Dvizenie>

Sklad - long     код склада

DataDviz - smalldate дата движения

Tovar -  long     код товара/ингридиента

Quantity -  long     количество

Vid  -  byte     1 - приход на склад

                     2 - перемещение на другой склад

                     3 - передано в переработку

                     4 - отгрузка заказчику

                     5 - естественная убыль (усушка и т.п.)

7. Клиенты

<Klient>

Kod   -  long     код клиента

FullName - c50

ShortName - c10

Vid  - byte     1 - поставщик

                     2 - покупатель

                     3 - поставщик/покупатель

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

8. Заказы

<Zakaz>

Kod - long     код заказа

DataZakaz - smalldate дата заказа

Klient - long     код клиента

Tovar -  long     код товара

Quantity - long     количество

Otgruzka - smalldate дата отгрузки

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

Желательно предусмотреть архивирование и отмену последних операций.

Прислал: Евгений

Оценка за ответ: 10

-------------------------------------------------------------------------------------------------------------------------

Ответ №2.1

Вот примерный перечень баз, а связи здесь явные

база «Ингредиенты» (Код, название, количество)

база «Поставщики»

база «склад1_приход» (поставщик, ингредиент, количество, цена, дата, партия)

база «склад1_расход» (поставщик, ингредиент, количество, цена, дата, партия)

база «склад2_приход» (поставщик, ингредиент, количество, цена, дата, партия)

база «склад2_расход» (поставщик, ингредиент, количество, цена, дата, партия) (передача в производство)

база «Готовый продукт» (код, ингредиент1, доля1, ингредиент2, доля2,…, объем)

база «склад3_приход» (поставщик, продукт, количество, цена, дата, партия)

база «склад3_расход» (поставщик, продукт, количество, цена, дата, партия)

база «заказы» (заказчик, продукт, количество, цена, дата)

база «заказы_ выполнение» (заказчик, продукт, количество, цена, дата_доставки, подтверждение)

Прислала: Nataly

Оценка за ответ: 7

-------------------------------------------------------------------------------------------------------------------------

Вопрос №2  Я пользуюсь Access-ом для печати на конвертах адресов клиентов из базы на основе

определенного запроса по признакам. Пользуюсь Label Wizard-ом. Для создания нового Label

просто копирую существующий и меняю название. Но тут проблема - не могу поменять связь Report с

Query (в новом Report остается прежний запрос (Query) который не могу поменять). Может поможете

как поменять.

Прислал: Сергей.

-------------------------------------------------------------------------------------------------------------------------

Ответ №1.2

Я сталкивался с такой необходимостью.

Открываем Report в режиме редактирования.

В точке пресечения вертикальной и горизонтальной линеек есть квадратик. Становимся на него и щелкаем второй кнопкой мыши, в появившемся меню выбираем "свойства" (properties), далее закладка data - record soures.

Думаю дальше понятно.

Прислал: Владимир

Оценка за ответ: 10

Вопрос №3

Не могли бы Вы подсказать, хотя бы схематично набор таблиц и связи

между ними для следующей задачи:

1.Имеется два массива данных "ТОВАРЫ"  и "КОНТАКТЫ"

2. В массиве "КОНТАКТЫ" - три категории типа контактов

(производители, поставщики, потребители, причем категория  "потребители" состоит из подкатегорий)

3. Массив "ТОВАР" также содержит категории и подкатегории  типа: Вид товара (АВТОМОБИЛЬ, ПРИЦЕП,и т.д.)

Вид изделия ( ЛЕГКОВОЙ, ГРУЗОВОЙ, ПИКАП, ДЖИП, ...)

Вид узла (ДВИГАТЕЛЬ, ШАССИ, КУЗОВ,...)

Вид подузла (СИСТЕМА ОХЛАЖДЕНИЯ, СИСТЕМА СМАЗКИ,  КАРБЮРАТОР, ИНЖЕКТОР и т.д.)

Вид детали (БОЛТ, ГАЙКА, КНОПКА, ...)

4. Можно ли не разрывая таблицы ТОВАРЫ и КОНТАКТЫ на мелкие  таблицы по подкатегориям решить следующие задачи:

 по каждому контакту - с какими товарами связан

- по каждому товару -  1. с какими контактами связан

2. c какими товарами связан как  вверх так и вниз (комплектующие и  применяемость)

- ввод данных через формы  "ТОВАРЫ"  и "КОНТАКТЫ".

Прислал: Алексей. [Ответить]

-------------------------------------------------------------------------------------------------------------------------

Вопрос №4

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

Прислал: Максим. [Ответить]

-------------------------------------------------------------------------------------------------------------------------

Вопрос №5

Есть приложение, которое работает в нескольких филиалах одной фирмы, оно разбито на два mdb файла:

1. В первом файле находятся таблицы с данными, назовем его base.mdb

2. Во втором файле запросы, формы, отчеты, назовем его prog.mdb. Кроме того в нем находятся связанные таблизы из первого файла.

Так было сделано, чтобы при добавлении каких либо функций можно было разослать новый файл prog.mdb при этом данные в филиалах сохраняются.

Но неожиданно возникла необходимость изменить структуру самой таблицы данных. Попытался создать в файле prog.mdb кнопку по которой запросом изменяется структура присоединнной таблице, но не смог.

Пришлось посылать отдельный mdb файл, где была только форма с одной кнопкой выполняющей запрос:

Private Sub Eiiiea0_Click()
Dim appAccess As New Access.Application
Set appAccess = CreateObject("Access.Application.8")
appAccess.OpenCurrentDatabase "s:\spip\257r\base.mdb"
appAccess.DoCmd.RunSQL "ALTER TABLE main ADD COLUMN Mria DOUBLE;"
appAccess.DoCmd.RunSQL "UPDATE main SET main.Mria = 0;"
appAccess.CloseCurrentDatabase
Application.Quit  
End Sub

Всё получилось, но хотелось бы узнать существует ли способ изменить структуру присоединной таблицы из самого приложения?

Прислал: Дмитрий [Ответить]

[<<<Задать вопрос>>>]

Сайт рассылки

Функции даты и времени

Функции для данных типа String

База данных «Домашняя библиотека»

Если у кого-то есть интересные статьи и ссылки на тему программирования на Microsoft Access, присылайте! Давайте вместе делать рассылку интересной и полезной и для  начинающих  и для профессионалов.

© 2006 Виктория Колдбер. Victoria_koldber@mail.ru  Сайт: http://myaccess.boom.ru/