SQL Реализация в ACCESS. Сравнение Microsoft Access SQL и ANSI SQL Microsoft Access SQL в основном отвечает стандарту ANSI-89 (уровень 1) некоторые средства. - презентация

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

2 Сравнение Microsoft Access SQL и ANSI SQL Microsoft Access SQL в основном отвечает стандарту ANSI-89 (уровень 1) некоторые средства ANSI SQL не используются в Microsoft Access SQL Microsoft Access SQL использует зарезервированные слова и средства, не поддерживаемые ANSI SQL В Access 2000 (MS Jet 4.0) внесены расширения, приближающие язык к стандарту ANSI SQL-92 - режим доступен только при использовании MS OLE DB Provider для Jet

3 Синтаксис команды SELECT (Microsoft Access) SELECT [ предикат ] { * | таблица.* | [ таблица.] поле_1 [AS псевдоним_1 ] [, [ таблица.] поле_2 [AS псевдоним_2 ] [,...]]} FROM выражение [,...] [IN внешняя БазаДанных ] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION] Предложение FROM SELECT список Полей FROM выражение [IN внешняя БазаДанных] Выражение - выражение, определяющее одну или несколько таблиц, откуда извлекаются данные. Это выражение может быть именем отдельной таблицы, именем сохраненного запроса или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN. INNER JOIN,LEFT JOIN RIGHT JOIN.

4 Аргументы инструкции SELECT Предикат (ALL, DISTINCT, DISTINCTROW или TOP) - одно из условий отбора. Предикаты используются для ограничения числа возвращаемых записей. Если они отсутствуют, по умолчанию используется предикат ALL (отбираются все записи, соответствующие условиям, заданным в инструкции SQL).инструкции SQL DISTINCT - исключает записи, которые содержат повторяющиеся значения в отобранных полях. DISTINCTROW - опускает данные, основанные на целиком повторяющихся записях, а не отдельных повторяющихся полях. Предикат DISTINCTROW игнорируется, если запрос содержит только одну таблицу или все поля всех таблиц. TOP n [PERCENT] - возвращает определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY. SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]] FROM таблица 5 самых многочисленных кафедр: SELECT TOP 5 сотрудник.[Код кафедры], Count(сотрудник.ФИО) AS число_сотрудников FROM сотрудник GROUP BY сотрудник.[Код кафедры] ORDER BY Count(сотрудник.ФИО) DESC;

5 Аргументы инструкции SELECT Таблица - имя таблицы, из которой должны быть отобраны записи. WITH OWNERACCESS OPTION - используется в многопользовательской среде с составе защищенной рабочей группы, для предоставления пользователю, работающему с запросом, разрешений, соответствующих разрешениям владельца запроса. поле_1, поле_2 - имена полей, из которых должны быть отобраны данные. Если включить несколько полей, они будут извлекаться в указанном порядке. Псевдоним_1, псевдоним_2 - имена, которые станут заголовками столбцов вместо исходных названий столбцов в таблице. Выражение -имена одной или нескольких таблиц, которые содержат отбираемые данные Внешняя БазаДанных - имя базы данных, которая содержит таблицы, указанные с помощью аргумента выражение, если они не находятся в текущей базе данных. SELECT сотрудник.ФИО, [оклад]*0.5 AS Премия FROM сотрудник; SELECT Avg(сотрудник.оклад) AS Средний_оклад FROM сотрудник;

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

7 Совместная обработка таблиц (левое соединение) Список сотрудников, не имеющих детей: SELECT DISTINCTROW сотрудник.ФИО FROM сотрудник LEFT JOIN дети ON сотрудник.Код = дети.Код_сотрудника WHERE (((дети.Код_сотрудника) Is Null));

8 Конструкция Between...And выражение 1 [NOT] BETWEEN выражение 2 AND выражение 3 (в Microsoft Access SQL выражение 2 может быть больше, чем выражение 3, а в ANSI SQL – нет). SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Between 1000 And 2000)); Запрос: SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Between 2000 And 1000)); не выдает ошибки и дает тот же ответ SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) 15000)); SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Not Between 1000 And 2000));

9 Символы шаблона Разные символы шаблона используются с предикатом Like. Символ шаблона MS Access SQL ANSI SQL один символ ? _ (подчеркивание) группа символов * % одиночный знак, входящий в список_знаков [ список_знаков] отсутствует одиночный знак, не входящий в список_знаков [! список_знаков] отсутствует Две последние возможности - только для Access В Access 2000 в режиме ANSI SQL-92 возможно использование подстановочных знаков ANSI. В одном запросе смешивать знаки нельзя SELECT сотрудник.ФИО FROM сотрудник WHERE (((сотрудник.ФИО) Like "Д*"));

10 Предложение GROUP BY SELECT список Полей FROM таблица WHERE условие Отбора [GROUP BY группируемые Поля] где группируемые Поля - имена полей (до 10), которые используются для группировки записей. Порядок имен полей в аргументе группируемые Поля определяет уровень группировки для каждого из этих полей. Используйте предложение WHERE для исключения записей из группировки, а предложение HAVING для применения фильтра к записям после группировки. HAVING При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL. Кафедры, на которых работает более 5 сотрудников: SELECT сотр.[Код кафедры], Count(сотр.ФИО) AS [Число_сотрудников] FROM сотр GROUP BY сотр.[Код кафедры] HAVING (((Count(сотр.ФИО))>5));

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

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

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

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

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

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

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

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

19 Инструкция DELETE (SQL) Синтаксис команды DELETE [таблица.*] FROM таблица WHERE условие_отбора

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

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

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

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

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

25 Объекты: Таблица индекс представление создаются командой CREATE удаляются командой DROP

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

27 Создание таблицы. Синтаксис команды CREATE [TEMPORARY] TABLE таблица ( поле_1 тип [( размер )] [NOT NULL] [WITH COMPRESSION | WITH COMP] [ индекс_1 ] [, поле_2 тип [( размер )] [NOT NULL] [ индекс_2 ] [,...]] [, CONSTRAINT составной Индекс [,...]])

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

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

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

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

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

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

34 Примеры создания индексов Пример 1. CREATE INDEX NewIndex ON Employees (HomePhone, _ Extension); Пример 2. CREATE UNIQUE INDEX CustID ON Customers (CustomerID) _ WITH DISALLOW NULL;

35 Создание представлений

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

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

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

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

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

41 Инструкция TRANSFORM, предназначенная для создания перекрестных запросов Дополнительные групповые функции, например, StDev и VarP Описание PARAMETERS, предназначенное для создания запросов с параметрами

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