SQL Реализация в ACCESS Сравнение Microsoft Access

SQL Реализация в ACCESS

Сравнение Microsoft Access SQL и ANSI SQL

Сравнение Microsoft Access SQL и ANSI SQL z. Microsoft Access SQL в основном отвечает стандарту ANSI-89 (уровень 1) z. Некоторые средства ANSI SQL не используются в Microsoft Access SQL z. Microsoft Access SQL использует зарезервированные слова и средства, не поддерживаемые ANSI SQL

Расширенный синтаксис z. В Access 2000 (MS Jet 4. 0) внесены расширения, приближающие язык к стандарту ANSI SQL-92 - режим доступен только при использовании MS OLE DB Provider для Jet

Другие правила применяются для конструкции Between. . . And, которая имеет следующий синтаксис: выражение 1 [NOT] Between значение 1 And значение 2 В языке Microsoft Access SQL значение 1 может быть больше, чем значение 2; в языке ANSI SQL значение 1 должно быть меньше значения 2 или равно ему.

подстановочные знаки z В языке Microsoft Access SQL при использовании оператора Like поддерживаются как подстановочные знаки языка ANSI SQL, так и подстановочные знаки (Подстановочные знаки. Эти знаки используют в запросах и выражениях для включения всех записей, имен файлов или других элементов, которые содержат определенные знаки или отвечают определенному образцу. ), относящиеся к Microsoft Access. Одновременное использование подстановочных знаков ANSI и Microsoft Access невозможно. Допускается использование только одного набора знаков, их нельзя смешивать.

подстановочные знаки

Возможности языка ANSI SQL, не поддерживаемые в языке Microsoft Access SQL z. Инструкция TRANSFORM обеспечивает поддержку перекрестных запросов z (Перекрестный запрос. Запрос, в котором подсчитывается сумма, среднее, число значений или выполняются другие статистические расчеты, после чего результаты группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк. ).

Возможности языка ANSI SQL, не поддерживаемые в языке Microsoft Access SQL z. Использование предложения LIMIT TO nn ROWS для ограничения числа строк, возвращаемых запросом. z. Предусмотрены дополнительные статистические функции SQL, такие как St. Dev и Var. P

Возможности языка ANSI SQL, не поддерживаемые в языке Microsoft Access SQL z. Для определения параметров запроса (Запрос с параметрами. Запрос, в котором одно или несколько значений, определяющих условия отбора, вводятся в интерактивном режиме пользователем. Запрос с параметрами не является отдельным типом запроса; это функциональное расширение запросов на выборку. ) используется объявление PARAMETERS.

Синтаксис команды SELECT [предикат] { * | таблица. * | [таблица. ]поле_1 [AS псевдоним_1] [, [таблица. ]поле_2 [AS псевдоним_2] [, . . . ]]} FROM выражение [, . . . ] [IN внешняя. База. Данных] [WHERE. . . ] [GROUP BY. . . ] [HAVING. . . ] [ORDER BY. . . ] [WITH OWNERACCESS OPTION]

Аргументы инструкции SELECT Предикат Один из следующих предикатов отбора: ALL, DISTINCTROW или TOP. Предикаты используются для ограничения числа возвращаемых записей. Если они отсутствуют, по умолчанию используется предикат ALL.

Предикаты ALL, DISTINCTROW, TOP z. SELECT [ALL | DISTINCTROW | [TOP n [PERCENT]]] FROM таблица z. ALL - Отбираются все записи, соответствующие условиям, заданным в инструкции SQL.

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

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

Пример. Содержание таблицы «Расписание»

DISTINCTROW z. SELECT DISTINCTROW сотрудник. фио, расписание. код_предмета z. FROM сотрудник INNER JOIN расписание z. ON сотрудник. код_сотрудника = расписание. код_сотрудника;

Результат

DISTINCT SELECT DISTINCT СОТРУДНИК. ФИО, расписание. код_предмета FROM СОТРУДНИК INNER JOIN расписание ON СОТРУДНИК. Код_сотрудника = расписание. код_сотрудника;

Результат

Если пользоваться табличным языком как построителем запросов на SQL, то для включения в запрос DISTINCT надо для свойства запроса «уникальные значения» выбрать значение «да» , а для включения в запрос DISTINCTROW надо выбрать значение «да» для свойства запроса «уникальные записи» .

Предикат TOP z. TOP n [PERCENT] - Возвращает определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY.

Пример. Отобрать 5 самых многочисленных кафедр SELECT TOP 5 кафедра. [Наименование_ кафедры_полное], Count(сотрудники. Код_сотрудника) AS [Число сотрудников] FROM кафедра INNER JOIN сотрудники ON кафедра. Код_кафедры = сотрудники. Код_кафедры GROUP BY кафедра. [Наименование_ кафедры_полное] ORDER BY Count(сотрудники. Код_сотрудника) DESC;

Аргументы инструкции SELECT Таблица - имя таблицы, из которой должны быть отобраны записи.

WITH OWNERACCESS OPTION Используется в многопользовательской среде с составе защищенной рабочей группы, для предоставления пользователю, работающему с запросом, разрешений, соответствующих разрешениям владельца запроса.

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

Аргументы инструкции SELECT Псевдоним_1, псевдоним_2 - имена, которые станут заголовками столбцов вместо исходных названий столбцов в таблице.

Примеры использования псевдонима для задания имени вычисляемого поля Пример 1 SELECT сотрудник. ФИО, [оклад]*0. 5 AS Премия FROM сотрудник; Пример 2 SELECT Avg(сотрудник. оклад) AS Средний_оклад FROM сотрудник;

Аргументы инструкции SELECT Внешняя. База. Данных имя базы данных, которая содержит таблицы, указанные с помощью аргумента выражение, если они не находятся в текущей базе данных.

Предложение FROM SELECT список. Полей FROM выражение [IN внешняя. База. Данных] z Выражение - выражение, определяющее одну или несколько таблиц, откуда извлекаются данные. Это выражение может быть именем отдельной таблицы, именем сохраненного запроса или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN.

Совместная обработка нескольких таблиц Использование встроенного JOIN

Совместная обработка нескольких таблиц

Совместная обработка нескольких (3 -х) таблиц SELECT сотрудник. ФИО, предмет. [наименование предмета краткое] FROM сотрудник INNER JOIN (предмет INNER JOIN [владение предметами] ON предмет. [Код предмета] = [владение предметами]. [код дисциплины]) ON сотрудник. Код = [владение предметами]. [код сотрудника];

Совместная обработка нескольких таблиц (левое соединение) Запрос: «Список сотрудников, не имеющих детей»

Левое «объединение»

Табличный запрос

Запрос на SQL SELECT DISTINCTROW сотрудник. ФИО FROM сотрудник LEFT JOIN дети ON сотрудник. Код = дети. Код_сотрудника WHERE (((дети. Код_сотрудника) Is Null));

WHERE

конструкция Between. . . And выражение 1 [NOT] BETWEEN выражение 2 AND выражение 3 z. В Microsoft Access SQL выражение 2 может быть больше, чем выражение 3, а в ANSI SQL - нет.

Пример. Закрытый диапазон SELECT сотрудник. ФИО, сотрудник. оклад FROM сотрудник WHERE (((сотрудник. оклад) Between 1000 And 2000));

Ответ

Запрос: SELECT сотрудник. ФИО, сотрудник. оклад FROM сотрудник WHERE (((сотрудник. оклад) Between 2000 And 1000)); не выдает ошибки, но дает тот же ответ

Пример. Открытый диапазон SELECT сотрудник. ФИО, сотрудник. оклад FROM сотрудник WHERE (((сотрудник. оклад)<1000 Or (сотрудник. оклад)>15000));

Ответ

Пример. Открытый диапазон(вариант 2) SELECT сотрудник. ФИО, сотрудник. оклад FROM сотрудник WHERE (((сотрудник. оклад) Not Between 1000 And 2000));

Предикат Like Символы шаблона Разные символы шаблона используются с предикатом Like. Символ шаблона ANSI SQL MS Access SQL z Любой один символ ? _ (подчеркивание) z Любая группа любых символов * % z Любой одиночный знак, входящий в список_знаков [список_знаков] отсутствует z Любой одиночный знак, не входящий в список_знаков [!список_знаков] отсутствует

z. Две последние возможности - только для Access 2000 z. В Access 2000 в режиме ANSI SQL-92 возможно использование подстановочных знаков ANSI z. В одном запросе смешивать знаки нельзя

Пример SELECT сотрудник. ФИО FROM сотрудник WHERE (((сотрудник. ФИО) Like "Д*"));

Параметрический запрос

Параметрический запрос SELECT СОТРУДНИК. ФИО FROM КАФЕДРА INNER JOIN СОТРУДНИК ON КАФЕДРА. Код_кафедры = СОТРУДНИК. Код_кафедры WHERE (((КАФЕДРА. Наименование_кафедры_кр аткое)=[Введите краткое название кафедры]));

Предложение GROUP BY z. SELECT список. Полей FROM таблица WHERE условие. Отбора [GROUP BY группируемые. Поля] z группируемые. Поля - имена полей (до 10), которые используются для группировки записей. Порядок имен полей в аргументе группируемые. Поля определяет уровень группировки для каждого из этих полей.

Предложение GROUP BY z Используйте предложение WHERE для исключения записей из группировки, а предложение HAVING для применения фильтра к записям после группировки. z При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL.

Пример. Кафедры, на которых работает более 5 сотрудников. SELECT сотрудник. [Код кафедры], Count(сотрудник. ФИО) AS [Число_сотрудников] FROM сотрудник GROUP BY сотрудник. [Код кафедры] HAVING (((Count(сотрудник. ФИО))>5));

Задача Есть таблицы «фирмы» , «сотрудники» и «аттестации» . Надо определить, сколько аттестованных сотрудников есть на каждой фирме (один сотрудник может быть аттестован по нескольким ПП).

Вложенный запрос. Предикат EXISTS. SELECT сотр. фирма, Count(сотр) AS число_аттест_сотр FROM сотр WHERE (((EXISTS (SELECT DISTINCT сотр. фирма, сотр FROM сотр INNER JOIN экз ON сотр = экз. сотр))<>False)) GROUP BY сотр. фирма;

Инструкция SELECT. . . INTO Синтаксис z. Создает запрос на создание таблицы. SELECT поле_1[, поле_2[, . . . ]] INTO новая. Таблица [IN внешняя. База. Данных] FROM источник

Пример SELECT СОТРУДНИК. ФИО, СОТРУДНИК. Оклад INTO сотрудники_низкооплачиваемые FROM СОТРУДНИК WHERE (((СОТРУДНИК. Оклад) Between 1000 And 2000));

Запрос на объединение (пример 1) SELECT Название, Город FROM Поставщики UNION SELECT Название, Город FROM Клиенты ORDER BY Город;

Запрос на объединение (пример 2) SELECT Название, Город FROM Поставщики UNION ALL SELECT Название, Город FROM Клиенты; - UNION ALL обеспечивает возвращение всех записей, в том числе повторяющихся

Создание подчиненного запроса с использованием построителя запросов QBE Если подчиненный запрос используется для определения условий для поля, введите инструкцию SELECT в ячейку строки Условие отбора в столбце этого поля. Инструкцию SELECT необходимо заключить в круглые скобки.

Корректирующие запросы

УДАЛЕНИЕ ЗАПИСЕЙ

Удаление записей. Запрос на QBE

Удаление записей. Запрос на SQL (сгенерированный) DELETE DISTINCTROW студент. ФИО FROM студент WHERE (((студент. ФИО)="Бурлак Г. Н. "));

Удаление записей. Запрос на SQL Запрос: DELETE * FROM студент WHERE студент. ФИО="Бурлак Г. Н. "; дает аналогичный результат.

Синтаксис DELETE [table. *] FROM table WHERE criteria

Инструкция DELETE z Запрос на удаление удаляет записи целиком, а не только содержимое указанных полей. Чтобы удалить данные конкретного поля, создайте запрос на обновление записей, который заменяет имеющиеся значения на значения Null z Запрос на удаление без заданных условий поиска удалит все записи из таблицы. В отличии от команды DROP структура таблицы и все свойства сохраняются

z. Если задано «каскадное удаление» , то удалятся все связанные записи z. Удаленные записи нельзя восстановить

ИЗМЕНЕНИЕ ДАННЫХ

UPDATE Синтаксис UPDATE таблица SET новое_значение WHERE условия_отбора; Пример (сгенерированный) UPDATE СОТРУДНИК SET СОТРУДНИК. Оклад = 32000 WHERE (((СОТРУДНИК. ФИО)="Диго С. М. "));

Управляющие операторы

Создание объектов

Объекты: Таблица индекс представление создаются командой CREATE удаляются командой DROP СОЗДАНИЕ ОБЪЕКТОВ

Создание таблицы

Создание таблицы. Синтаксис команды CREATE [TEMPORARY] TABLE таблица (поле 1 тип [(размер)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [индекс1] [, поле 2 тип [(размер)] [NOT NULL] [индекс2] [, . . . ]] [, CONSTRAINT индекс_нескольких_полей [, . . . ]])

Создание таблицы. Синтаксис команды в Access Существенно отличается от стандарта: zреализованы не все возможности стандарта SQL-92 zвключены новые конструкции zотличается синтаксис

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

WITH COMPRESSION z. Использование атрибута WITH COMPRESSION допускается только для типов данных CHARACTER и MEMO. z. Компенсирует последствия перехода к формату представления знаков Юникод

Корректировка структуры таблицы

Корректировка структуры таблицы ALTER TABLE таблица {ADD {COLUMN тип поля[(размер)] [NOT NULL] [CONSTRAINT индекс] | ALTER COLUMN тип поля[(размер)] | CONSTRAINT индекс_набора_полей} | DROP {COLUMN поле I CONSTRAINT имя_индекса} }

z Размер поля в знаках задается только для полей с типом данных TEXT и BINARY z ADD COLUMN - для добавления в таблицу нового поля z ALTER COLUMN - для изменения типа данных существующего поля z DROP COLUMN - для удаления поля. z ADD CONSTRAINT - для добавления индекса z DROP CONSTRAINT - для удаления индекса z Невозможно одновременно добавить или удалить несколько полей или индексов

Создание индекса

Способы создание индекса z. CREATE TABLE z. CREATE INDEX z. ALTER TABLE

Создание индекса при создании таблицы CREATE [TEMPORARY] TABLE table (field 1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index 1] [, field 2 type [(size)] [NOT NULL] [index 2] [, …]] [, CONSTRAINT multifieldindex [, …]])

Создание индекса. Синтаксис команды CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [ASC|DESC][, поле [ASC|DESC], . . . ]) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL}]

Создание индекса. Синтаксис команды CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [ASC|DESC][, поле [ASC|DESC], . . . ]) [WITH {PRIMARY | DISALLOW NULL | IGNORE NULL }]

Создание индекса z. DISALLOW NULL - запрещает наличие значений Null в индексированных полях новых записей z. IGNORE NULL запрещает включение в индекс записей, имеющих значения Null в индексированных полях z. PRIMARY - назначить индексированные поля ключом

Примеры создания индексов Пример1. CREATE INDEX New. Index ON Employees (Home. Phone, Extension); Пример 2. CREATE UNIQUE INDEX Cust. ID ON Customers (Customer. ID) WITH DISALLOW NULL;

Использование ALTER TABLE для создания индекса ALTER TABLE таблица {ADD {COLUMN тип поля[(размер)] [NOT NULL] [CONSTRAINT индекс] | ALTER COLUMN тип поля[(размер)] | CONSTRAINT индекс_набора_полей} | DROP {COLUMN поле I CONSTRAINT имя_индекса} }

СОЗДАНИЕ ПРЕДСТАВЛЕНИЙ

Создание представлений. Синтаксис команды CREATE VIEW представление [(поле_1[, поле_2[, . . . ]])] AS инструкция. Select

Создание представлений. (пример 1)

Изменение структуры таблицы ALTER TABLE таблица {ADD {COLUMN тип поля[(размер)] [NOT NULL] [CONSTRAINT индекс] | ALTER COLUMN тип поля[(размер)] | CONSTRAINT составной. Индекс} | DROP {COLUMN поле I CONSTRAINT имя. Индекса} }

Удаление объектов DROP {TABLE таблица | INDEX индекс ON таблица | PROCEDURE процедура | VIEW представление}

Создание пользователей и групп

CREATE USER or GROUP CREATE USER user password pid [, user password pid, …] - Password – пароль - pid – личный идентификатор - CREATE GROUP group pid[, group pid, …]

z. Пользователи и группы не могут иметь одинаковых имен z. Для каждого создаваемого пользователя и группы должен быть задан пароль

ALTER USER or DATABASE z. ALTER DATABASE PASSWORD newpassword oldpassword z. ALTER USER user PASSWORD newpassword oldpassword

Синактсис GRANT {privilege[, privilege, …]} ON {TABLE table | OBJECT object| CONTAINER container } TO {authorizationname[, authorizationname, …]}

Privilege (привилегия) z SELECT z DELETE z INSERT z UPDATE z DROP z SELECTSECURITY z UPDATESECURITY z DBPASSWORD z UPDATEIDENTITY z CREATE z SELECTSCHEMA z UPDATEOWNER

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

ADD USER user[, user, …] TO group Добавление существующего(их) пользователей к существующей группе. Пользователи будут обладать всеми правами, переданными группе

DROP USER or GROUP z. DROP USER user[, user, …] [FROM group] DROP USER выводит пользователя из группы, но не уничтожает пользоватля z. DROP GROUP group[, group, …] DROP GROUP удаляет группу, но не затрагивает пользователей группы; они просто перстают быть членами группы

REVOKE – отмена заданных ограничений REVOKE {privilege[, privilege, …]} ON {TABLE table | OBJECT object| CONTAINTER container} FROM {authorizationname[, authorizationname, …]}

Дополнительные возможности MS Acces SQL

Дополнительные возможности MS Acces SQL z. Инструкция TRANSFORM, предназначенная для создания перекрестных запросов z Дополнительные групповые функции, например, St. Dev и Var. P z Описание PARAMETERS, предназначенное для создания запросов с параметрами

Инструкция SELECT. . . INTO SELECT поле 1[, поле 2[, . . . ]] INTO новая_таблица [IN внешняя_база_данных] FROM источник