Внешнее объединение
В SQL внешнее объединение определяется в предложении FROM, операцией Left или Right, которая указывает, из какой таблицы брать все записи. Если операция Left, то нужно брать все записи таблицы, расположенной слева от JOIN, если Right, то тогда таблица справа.
Пример 13b. Учитывая схему данных, необходимо найти количество поступивших товаров, на определенную дату.
SELECT ТОВАРЫ.[наименование товара], [ПОСТУПЛЕНИЕ ТОВАРА].поступление, [ПОСТУПЛЕНИЕ ТОВАРА].[дата поступления]
FROM ТОВАРЫ RIGHT JOIN [ПОСТУПЛЕНИЕ ТОВАРА] ON ТОВАРЫ.[код товара] = [ПОСТУПЛЕНИЕ ТОВАРА].[код товара];
Результат выполнения запроса
Вложение запросов Специальные операторы any и all Оператор any
Пример 14. Выбрать все поступления товаров, превосходящие цену товара, поступившего 13.03.2007 (т.е. 200 евро)
SELECT *
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
WHERE цена > ANY
(SELECT цена
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
WHERE [ПОСТУПЛЕНИЕ ТОВАРА].[Дата поступления]=#03/13/07#);
Результат выполнения запроса
Оператор All
Пример 15. Выбрать все поступления товаров, превосходящие цену товара, поступившего 14.03.2007
SELECT *
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
WHERE цена > ALL
(SELECT цена
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
WHERE [ПОСТУПЛЕНИЕ ТОВАРА].[Дата поступления]=#03/14/07#);
Результат выполнения запроса
Использование оператора UNION (объединение запросов)
Пример 16. Получить сведения о наибольших и наименьших поступлениях товаров
SELECT [ПОСТУПЛЕНИЕ ТОВАРА].[Код товара], MAX(Поступление) as [Кол-во], "Наибольшее поступление" as [Примечание]
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
GROUP BY [ПОСТУПЛЕНИЕ ТОВАРА].[Код товара]
HAVING MAX(Поступление)>1000
UNION
SELECT [ПОСТУПЛЕНИЕ ТОВАРА].[Код товара], MIN(Поступление) as [Кол-во], "Наименьшее поступление"
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
GROUP BY [ПОСТУПЛЕНИЕ ТОВАРА].[Код товара]
HAVING MIN(Поступление)<1000;
Результат выполнения запроса
Операторы манипулирования данными
DELETE - удаление записей
UPDATE - изменение (обновление записей)
INSERT - добавление или ввод новых записей
Оператор DELETE
Синтаксис оператора DELETE:
DELETE FROM имя_таблицы [WHERE условия_отбора]
Пример 17. Удалить записи из таблицы Поступление товара на какую-либо введенную дату, у которых цена товаров меньше 100 евро:
DELETE *
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
WHERE [ПОСТУПЛЕНИЕ ТОВАРА].[дата поступления]=[Введите дату]AND цена<100;
Оператор UPDATE
Синтаксис оператора UPDATE:
UPDATE имя_таблицы
SET имя_столбца = новое_значение [WHERE условие_отбора]
Пример 18. Обновить поле Цена на 5% в таблице Поступление товара на какую-либо введенную дату поступления:
UPDATE [ПОСТУПЛЕНИЕ ТОВАРА]
SET [ПОСТУПЛЕНИЕ ТОВАРА].цена = [цена]*1.05
WHERE [ПОСТУПЛЕНИЕ ТОВАРА].[дата поступления]=[Введите дату];
Оператор INSERT
Оператор ввода данных INSERT имеет следующий синтаксис:
INSERT INTO имя_таблицы [(<список столбцов>) ]
VALUES (<список значений>)
Пример 19. Добавить новую запись в таблицу Поступление товара
Insert into [поступление товара]
VALUES ("003", 99, "23.04.2007", 150, 3915);
Создание новых таблиц
Существует два способа создания таблиц:
копирование полей и записей из одной или нескольких существующих таблиц в новую;
создание новой пустой таблицы.
Создание новой таблицы с использованием уже существующей
Синтаксис запроса на создание новой таблицы:
SELECT[ALL |DICTINCT| [TOP n]] <список полей>
INTO <новая таблица>
FROM <таблица-источник>;
Пример 20. Создать таблицу Архив, состоящую из записей таблицы Поступление товара.
SELECT [ПОСТУПЛЕНИЕ ТОВАРА].* INTO АРХИВ
FROM [ПОСТУПЛЕНИЕ ТОВАРА];
Пример 21.Добавить новую запись в таблицу Поступление товара, затем добавить эту запись по коду товара и дате поступления в таблицу АРХИВ.
INSERT INTO [ПОСТУПЛЕНИЕ ТОВАРА]
VALUES ("001", 100, "23.04.2007", 180, 4698);
INSERT INTO АРХИВ
SELECT *
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
WHERE [ПОСТУПЛЕНИЕ ТОВАРА].[код товара]=[Введите код товара] AND [ПОСТУПЛЕНИЕ ТОВАРА].[дата поступления]=[Введите дату];
Создание новой таблицы, отличной от существующей таблицы
CREATE TABLE <имя новой таблицы> ( <имя столбца 1> <тип данных> [(<размер>)],<имя столбца 2> <тип данных> [(<размер>)]…);
Пример 22. Создать пустую таблицуПоступлениеМесяц
CREATE TABLE ПоступленияМесяц (КодТовара text(3), Кол integer, Месяц short);
Удаление существующей таблицы
Синтаксис команды:
DROP TABLE<имя таблицы>;
Пример 23. Удалить таблицуПоступлениеМесяц
DROP TABLE ПоступлениеМесяц;
Изменение существующей таблицы
Синтаксис команды:
добавление поля
ALTER TABLE <имя таблицы>
ADD COLUMN <имя поля><тип данных>[(размер)];
удаление поля
ALTER TABLE <имя таблицы>
DROP COLUMN <имя поля>;
Пример 24. В таблицеПоступлениядобавить поле добавить полеКодПоставщика
ALTER TABLE Поступления
ADD COLUMN КодПоставщика Text(2)
Результат выполнения запроса
Пример 25. Из таблицыПоступленияудалить поле КодПоставщика
ALTER TABLE Поступления
DROP COLUMN КодПоставщика
Результат выполнения запроса
Индексы INDEX
Синтаксис команды:
CREATE INDEX <имя индекса>
ON <имя таблицы>(<имя столбца1>,< имя столбца2>…);
Пример 26. Создать индексКодТоварав таблицеПоступления
CREATE INDEX КодТовара
ON Поступления (КодТовара)
Результат выполнения запроса
Уникальный индекс UNIQUE INDEX. Ключ PRIMARY
Синтаксис команды:
CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [ASC|DESC][, поле [ASC|DESC], ...]) [WITH {PRIMARY|DISALLOW NULL| IGNORE NULL}]
UNIQUE – уникальный индекс
PRIMARY- назначить индексированное поле ключевым
DISALLOW NULL – запрет пустых значений в индексивованных полях новых записей
IGNORE NULL – включение в индекс записей, имеющих пустые значения в индексивованных полях
Пример 27. В таблицеПоступлениеМесяцсоздать уникальный индексКодТовара
CREATE UNIQUE INDEX КодТовара
ON ПоступлениеМесяц (КодТовара)
WITH PRIMARY
Результат выполнения запроса
Удаление индекса
Синтаксис команды удаления индекса:
DROP INDEX <имя индекса>
ON <имя таблицы>;
Пример 28. Удалить индексКодТоварав таблицеПоступлениеМесяц.
DROP INDEX КодТовара
ON ПоступлениеМесяц
Результат выполнения запроса
Инструкция Drop Table
Удаление таблицы
Пример:
CurrentDb.Execute ("Drop Table " & "Поступления;")
Инструкция Drop Index
Удаление индекса
Drop Index Код товара ON Поступления;
CurrentDb.Execute ("Drop Index 'Код товара' On Поступления;")
Инструкция SELECT...INTO
Создание запроса на создание таблицы
Синтаксис:
SELECT поле1[, поле2[, ...]] INTO новая_таблица FROM источник
'создание новой таблицы
CurrentDb.Execute (" Select '999' as КодТовара, 9999 as Кол, 99 as Месяц INTO " & "Поступления" & ";")
MsgBox "Таблица 'Поступления' создана!!!"
Инструкция CREATE INDEX
Создает новый индекс к существующей таблице.
Синтаксис
CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [ASC|DESC][, поле [ASC|DESC], ...]) [WITH {PRIMARY|DISALLOW NULL| IGNORE NULL}]
UNIQUE – уникальный индекс
PRIMARY- назначить индексированное поле ключевым
DISALLOW NULL – запрет пустых значений в индексивованных полях новых записей
IGNORE NULL – включение в индекс записей, имеющих пустые значения в индексивованных полях
Пример:
CurrentDb.Execute ("Create UNIQUE Index 'Код товара' On Поступления (Код товара);")
SELECT [ПОСТУПЛЕНИЕ ТОВАРА].[код товара], [ПОСТУПЛЕНИЕ ТОВАРА].поступление, Month([дата поступления]) AS месяц
FROM [ПОСТУПЛЕНИЕ ТОВАРА]
ORDER BY [ПОСТУПЛЕНИЕ ТОВАРА].[код товара];
Соединение таблиц
JOIN – операция соединения таблиц
SELECT ЗапросОт.[Код товара], Sum(ЗапросОт.Отпущено) AS [Sum-Отпущено], ЗапросОт.Месяц
FROM ЗапросОт
GROUP BY ЗапросОт.[Код товара], ЗапросОт.Месяц;
SELECT ЗапросПТ.[код товара], Sum(ЗапросПТ.поступление) AS [Sum-поступление], ЗапросПТ.месяц
FROM ЗапросПТ
GROUP BY ЗапросПТ.[код товара], ЗапросПТ.месяц
ORDER BY ЗапросПТ.месяц;
SELECT ТОВАРЫ.[наименование товара], ПОСТАВЩИКИ.Поставщик, ПОСТАВЩИКИ.Страна, [ПОСТУПЛЕНИЕ ТОВАРА].поступление, [ПОСТУПЛЕНИЕ ТОВАРА].цена, [цена]*[курс] AS [Стоимость в руб]
FROM (ПОСТАВЩИКИ
INNER JOIN ТОВАРЫ ON ПОСТАВЩИКИ.[Код поставщика] = ТОВАРЫ.[код поставщика])