Объекты ms Access

1. формы

2. таблицы

3. запросы

4. отчеты

5. макросы

6. модули

Фомы помогаю вводить, просматривать и модифицировать информацию. Запросы и отчеты помогают выбирать, группировать, представлять и печатать информацию.

Режим работы: 1) оперативный ( все выполняется)

2) режим конструктора (только здесь можно изменять структуру, создавать отчеты и запросы).

Схема данных – связь между таблицами. Эти связи позволяют контролировать данные. Связи могут создаваться, просматриваться, изменяться и разрываться.

Типы данных: текстовый, числовой, денежный, дата/ время, логический, МЕМО – поле, OLE – объект.

Выражения: арифметические, условные, логические.

Операндами выражений могут быть [имена полей], числовые литералы, текстовые литералы, функции.

Пример: [цена]*[количество], [номер] <=25 and [номер]=>10

ЛЕКЦИЯ 9 ЯЗЫКИ ЗАПРОСОВ. ЯЗЫК ЗАПРОСОВ SQL В MS ACCESS

Структурированный язык запросов SQL.

Появился в 70-х годах, стандартизировался в 1968г.

Отличия от других языков

1. Непроцедурный

2. SQL дает указания СУБД, но не дает алгоритм выполнения

3. Существует TRUE-FALSE-NULL

4. Операции осуществляются над целыми наборами данных, а не над отдельными элементами.

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

Возможности MS Access для проектирования запросов.

1. Выбор поля.

2. Выбор записи.

3. Сортировка записей.

4. Запрос данных из нескольких таблиц (при этом можно обрабатывать их вместе или совместить данные).

5. Также можно запросить из БД, созданных в других программах:

MS FoxPro

Paradox

dBase

Brieve

MS SQL Server

Sy base SQL Server

6. Использование запроса в качестве источника данных для отчетов, других запросов и формуляров.

7. Изменение данных в таблице.

8. Создание новой таблицы на базе существующих.

Выделяют два типа запросов:

1. QBE-запрос – запрос по образцу. Пользователь дает определение, в окне проектирования с использованием образцов.

2. SQL-запрос – структурный запрос. Пользователь формулирует запросы с использованием инструкций и функций.

QBE-запрос легко трансформируется в SQL-запрос. Обратная операция тоже возможна.

Создание SQL-запросов в Access

1 . Запросы Объекты Создать

2 . Новый запрос Конструктор ОК

3 . Не добавляя таблицы, Запросы Закрыть

4 . Меню Запрос Запрос SQL Управление

5. Вводим инструкцию SQL для управляющего запроса и запускаем.

SQL может также управлять обработкой запроса, выражениями, вызывать агрегатные функции.

Запрос действия – пользователь может изменять или переносить данные таблицы, а также удалять, добавлять блоки данных и изготовлять новые таблицы из наборов.

Типы запросов действия:

- Запрос добавления

- Запрос удаления

- Запрос актуализации

- Запрос создания таблицы

Запрос с параметром

Можно получить данные, незначительно изменяя варианты однажды подготовленного базового набора.

Запрос кросс-таблиц

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

Запросы позволяют более компактно идентифицировать данные и объединять однотипные данные.

Структуризация SQL

Язык SQL был разработан компанией IBM в 1970гг.

Используется в различных системах данных:

Oracle, INGRES, Informix, Sy Base, SQL Base, MS SQL Server, DB2 фирмы IBM;

разными продуктами: SQL/PC, Paradox, Access, Approach и др.

Уже разрабатывается интерфейс связи, типа SQL.

В вычислительной среде клиент-сервер СУБД обычно располагается на сервере. Таким образом, это непроцедурный продукт, то есть SQL погружен в некую среду, но используется автономно.

Схема работы SQL

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

- функция организации данных (пользователь может изменить структуру)

- чтение данных

- обработка данных (возможность изменить БД)

- управление доступом (ограничить возможности пользователя)

- совместное использование данных

- целостность данных

SQL неполноценный компьютерный язык. Нет основных конструкций if, go to, for и т.д. Всего 30 основных операторов, остальные добавляются каждой системой. SQL слабо структурированный язык. Операторы SQL напоминают выражения на английском языке, содержат слова-«пустышки», облегчающие чтение.SQL на сегодня единственный стандартный язык для работы с БД.

Стандарты

а) 1986г. - SQL-86 (SQL -87) был принят ANSI, одобрен в 1987г. ISO. Очень прост, мало операторов, окритикован Коддом.

б) 1989г. – SQL-89 (FIPS 127-1) доработанный

в) 1992г. – SQL-92 (SQL2, FIPS 127-2)

г) 1999г. – SQL-99 (SQL3), добавлена поддержка выражений релявиционных выражений и т.д.

д) 2003г. – SQL-2003, введены возможности для работы с XML, с OLAP-БД, введены генераторы последовательностей. OLAP – получение запросов в online-режиме.

е) 2006г. - SQL-2006

ж) 2008г. – SQL-2008

В Access используются SQL-92, SQL-89, действующий стандарт SQL-92.

Записи операторов SQL.

SQL нечувствителен к регистру, но чувствителен к литерам.

Будем использовать расширенную форму систем обозначений BNF (Backus Nour Form)

1. Зарезервированные слова прописными буквами.

2. Пользовательские слова строчными буквами.

3. «/» - необходимость выбора между a и b. a/b/c

4. {a} – обязательный элемент.

5. [а] – необязательный элемент.

6. … - для указания необязательной возможности повторения конструкции от 0 до нескольких раз.

SQL разделяется на несколько частей (языков):

Язык описания данных DDL (Data Definition Language).

Позволяет создавать, изменять, уничтожать БД и объекты внутри них (таблицы, представления ит.д.).

Оператор	Описание
CREATE TABLE	Создание таблицы в БД
DROP TABLE	Удаление таблицы из БД
ALTER TABLE	Изменение структуры имеющейся таблицы
CREAT(E) VIEW	Создание нового представления
ALTER VIEW	Изменение представления
DROP VIEW	Удаление представления из БД
CREAT(E) INDEX	Создание индекса для данного поля
DROP INDEX	Удаление индекса
CREATE SCHEMA	Создание новой схемы в БД
DROP SCHEMA	Удаление схемы из БД
ALTER DOMAIN	Изменение домена
ALTER DOMAIN	Изменение домена
DROP DOMAIN	Удаление домена из БД

Язык манипулирования данными DML (Data Manipulation Language)

Предназначен для выбора, поддержки БД, удаления и изменения данных.

Оператор	Описание
SELECT	Выбор данных
INSERT	Добавить строку в таблицу
DELETE	Удалить строку из таблицы
UPDATE	Изменение данных

Язык управления данными DCL (Data Control Language)

Предназначен для административных функций присвоения права

Оператор	Описание
CRANT	Присвоение привилегии
REVOKE	Отмена присвоения привилегий

Средства управления транзакциями DQL

Оператор	Описание
COMMIT	Завершить транзакцию
ROLLBACK	Откатить транзакцию
SAVEPOINT	Сохранить промежуточную точку выполнения транзакции. Сохраняется промежуточное состояние БД, чтобы вернуться к этой точке

Средства администрирования данными

Сюда входят два оператора DCL

CRANT	Присвоение привилегии
REVOKE	Отмена присвоения привилегий

а также

Оператор	Описание
ALTER DATABASE	Изменить БД (изменить набор данных)
ALTER DBAREA	Изменить область хранения БД
ALTER PASSWORD	Изменить пароль
CREAT(E) DBAREA	Создать область хранения БД
CREAT(E) DATABASE	Создать БД
DROP DATABASE	Удалить БД
DROP DBAREA	Удалить область хранения БД

Программный SQL

Оператор	Описание
DECLARE	Создает некоторое имя и определяет связанный с ним запрос
OPEN	Открыть курсор. Формирует виртуальный набор данных
FETCH	Считать строку из множества строк, определяется курсором
CLOSE	Прекращает доступ к виртуальному набору данных
PREPARE	Подготовить оператор SQL к выполнению
EXECUTE	Выполнить оператор SQL

Типы данных SQL.

1. Символьный тип данных

Содержит буквы, цифры, символы.

CHAR≈CHAR(1)

Каждый символ содержит 1 байт.

Символьная строка

VARCHAR(n)

2. Целый тип данных

Содержит целые числа.

INTEGER или INT содержит 4 байта

Числа от -2147483647 до +2147483647

SMALLINT содержит 2 байта

Числа от -32767 до +32767

3. Вещественный тип данных

Содержит числа с плавающей точкой

FLOAT содержит 8 байт

SMALLFLOAT содержит 4 байта

DECIMAL(p)

DECIMAL(p,n) – р – общее число цифр, n – количество после запятой.

4. Денежный тип данных

MONEY(p,n) - р – общее число цифр, n – количество после запятой.

5. Дата и время

DATE

TIME

INTERVAL – тип данных для хранения временного интервала

DATETIME – год + месяц + день + час + минута + секунда + доли секунды

6. Двоичные типы данных

Позволяют хранить данные любого объема в двоичной форме

Чаще всего используются BINARY, BYTE, BLOB

7. Последовательные типы данных

Используются для представления возрастающих числовых последовательностей

SERIAL – на основе INTEGER

Позволяет сформировать уникальное значение (например, для первичного ключа).

Автоматически присваивается данных типа значение, получаемых из возрастающей последовательности целых чисел.

NULL- значение не определено. Может использоваться для всех кроме первичного ключа.

Синтаксис операторов SQL.

SELECT [ALL\DISTINCT] <список данных> /ALL – все, DISTINCT - кроме повторяющихся/

FROM<список таблиц>

[WHERE<имя поля><условие выборки>]

[GROUP BY<имя поля>[.<имя поля>…]]

[HAVING<условие поиска>]

[ORDER BY<спецификация сортировки>[<спецификация сортировки>…]]

[порядок сортировки] ASC - по возрастанию /используется по умолчанию/

DESC – по убыванию

Соединение данных из нескольких таблиц.

Внешнее соединение двух таблиц

SELECT <список данных>

FROM<таблица 1><тип соединения>

JOIN<таблица 2>

Типы соединения

INNER

LEFT OUTER

RIGHT OUTER

FULL OUTER

Примеры

Создание БД

CREATE DATABASE PUB;

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

CREATE TABLE autor

(au_id INT PRIMARY KEY,

aut VARCHAR(25) NOT NULL);

CREATE TABLE publ

(pub_id INT PRIMARY KEY,

publ VARCHAR(25) NOT NULL,

url VARCHAR(255));

Удаление таблицы

DROP TABLE publ;

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

Структура запроса:

CREATE [UNIQUE] INDEX <имя индекса> ON <таблица>(<имя столбца>);

Пример

CREATE INDEX ID ON publ (publ1);

Удаление индекса

DROP INDEX id;

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

INSERT INTO publ (pub_id, publ, url)

VALUES (16, “Публ1”, http://www.ms.com);

Изменение записи

UPDATE publ

SET Lastname = Фамилия

WHERE Id=1;

В запросах SQL используются следующие операции:

Операции отношения

>, <, >=, <=, =, !=

Проверка значения поля на NULL

IS NULL

IS NOT NULL

Проверка на вхождение в диапазон

BETWEEN

IN

LIKE

NOT BETWEEN

NOT IN

NOT LIKE

AND, OR, NOT.

Пример

UPDATE publ SET

url=”url not found” WHERE url IS NULL;

Примеры запросов на основе БД «Библиотека»

Структура БД «Библиотека»

Authors (au_id, author)

Titles (title_id, title, yearpub, pub_id)

Titleauthors (title_id, au_id)

Publishers (pub_id, publisher, publ_url)

Wwwsiteauthors (au_id, site_id)

Wwwsites (site_id, site_url)

Язык манипулирования данными DML

1. Выборка всех авторов БД.

SELECT author FROM authors;

2. Получить список всех полей

SELECT * FROM authors;

3. Найти книги, опубликованные после 1996 года

SELECT title FROM titles

WHERE yearpub>1996;

4. Найти все книги в интервале 1995-1997 гг издания

a) SELECT title FROM titles

WHERE yearpub>=1995 AND yearpub<=1997;

b) SELECT title FROM titles

WHERE yearpub BETWEEN 1995 AND 1997;

Замечание: Все кроме 1995-1997 – NOT BETWEEN c) SELECT title FROM titles

WHERE yearpub IN (1995,1996,1997);

Замечание: Все кроме 1995-1997 – NOT IN

5. Выборка книг определенного издательства, например,«Oracle Press»

SELECT title FROM titles

WHERE pub_id IN

(SELECT pub_id FROM publishers WHERE publisher=’Oracle Press’);

Использование оператора сравнения

WHERE<имя столбца> LIKE<’образец’>

[ESCAPE<ключевой символ>]

% - заменяет любое количество символов

_ - заменяет один символ

Примеры

1. Найти веб-сайт издательства Wiley

SELECT publisher, url FROM publishers

WHERE publisher LIKE ‘%wiley%’;

2. Найти все книги, название которых начинается со слова SQL

SELECT title FROM titles

WHERE title LIKE ‘SQL%’;

3. Найти страницу, если известно, что его URL содержит подстроку ‘my-works’

SELECT site, url FROM wwwsites

WHERE url LIKE’%my-works%’

ESCAPE ‘@’;

Выборка из нескольких таблиц нужных столбцов

SELECT <список полей> FROM <список таблиц>

WHERE <условие, по которому происходит слияние>;

Примечание: так как иногда встречаются одинаковые имена полей, обращение к полю рекомендуется писать следующим образом: <имя таблицы>.<имя поля>

Пример

Напечатать название книги, год выпуска, издательство

SELECT titles.title, titles.yearpub, publishers.publisher

FROM titles, publishers

WHERE titles.pub_id=publishers.pub_id;

Добавим в предыдущий пример условие выборки «все издания после 1996 года»

SELECT titles.title, titles.yearpub, publishers.publisher

FROM titles, publishers

WHERE titles.pub_id=publishers.pub_id AND titles.yearpub>1996;

Точно так же производится слияние большого количества таблиц

SELECT authors.author, titles.title, titles.yearpub, publishers.publisher

FROM authors, titles, publishers

WHERE authors.au_id=titleautors.au_id, titles.pub_id=publishers.pub_id AND titles.yearpub>1996;

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

Пример

Узнать, сколько лет прошло с 1992 года до публикации той или иной книги

SELECT title, yearpub-1992

FROM titles

WHERE yearpub>1992

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

Допускаются знаки: +, -, /; функции cos, sin, abs.

Пример

SELECT ‘the title of the book is’, title, yearpub-1992

FROM titles WHERE yearpub>1992;

Агрегатные функции, существующие в SQL

Агрегатные функции – функции, которые осуществляют действия над совокупностью одинаковых полей в группе записей. Каждая агрегатная функция возвращает одно единственное значение.

AVG(<имя поля>) – среднее значение поля

COUNT(<имя поля >)-число записей

MAX(<имя поля >)

MIN(<имя поля >)

SUM(<имя поля >)

Примеры

1. Напечатать, какая книга самая древняя

SELECT MIN (yearpub) FROM titles;

2. Подсчитать количество книг в базе

SELECT COUNT(*) FROM titles;

3. Подсчитать количество книг, в названии которых есть SQL

SELECT COUNT(*) FROM titles

WHERE title LIKE ‘%SQL%’;

Группировка данных

GROUP BY – разбивает записи на группы.

HAVING – ключевое слово, с помощью которого создается разбиение записей на группы и накладывается условие.

Примеры

1. Определить количество книг каждого издательства в нашей базе

SELECT publishers.pulisher, COUNT(titles.title)

FROM titles, publishers

WHERE titles.pub_id=publishers.pub_id

GROUP BY publisher;

2. Вывести издательства, количество выпущенных книг которых больше 1.

SELECT publishers.pulisher, COUNT(titles.title)

FROM titles, publishers

WHERE titles.pub_id=publishers.pub_id

GROUP BY publisher

HAVING COUNT(*)>1;

3. Вывести издательства, в названии которых последнее слово Press

SELECT publishers.pulisher, COUNT(titles.title)

FROM titles, publishers

WHERE titles.pub_id=publishers.pub_id

GROUP BY publisher

HAVING publisher LIKE ‘%Press’;

Сортировка данных

По убыванию DESC

По возрастанию ASC

Примеры

1. Отсортировать имена авторов по алфавиту

SELECT author FROM authors

ORDER BY author;

2. Отсортировать книги от более поздних изданий к ранним

SELECT authors.author, titles.title, titles.yearpub

FROM authors, titles

WHERE titleautors.au_id=authors.au_id

ORDER BY authors.author ASC, titles.yearpub DESC;

38