Использование языков программирования при создании приложений в среде СУБД ACCESS

Использование языков программирования при создании приложений в среде СУБД ACCESS. Создание запроса на языке SQL

Задание: Создать запросы, используя методические рекомендации, на языке SQL в файле Основа_База для SQL.mdb.

access данные база запрос Методические рекомендации Прежде всего, нам необходимо договориться о смысле, который мы будем вкладывать в понятие «Access». С одной стороны, под Access мы будем понимать, непосредственно, базу данных (к примеру, MDB файл сохраненный на диске). С другой стороны под Access будем понимать систему управления реляционной базой данных (сокращенно БД), которая включает в себя все необходимые инструментальные средства для создания БД.

Обычно, сама по себе БД это некий файл (или группа файлов), записанный в специальном формате. Диспетчером данных, выполняющим загрузку и сохранение данных в БД пользователя и системных БД, является ядро базы данных Microsoft Jet. В варианте Access2000 используется ядро Microsoft Jet версии 4.0. Новая версия Jet поддерживает двухбайтовое представление символов (формат Unicode), улучшена поддержка языка SQL спецификации ANSI SQL 92 (тем не менее, она еще далека от классической реализации SQL), реализована встроенная поддержка интерфейсов OLE DB, благодаря которой Microsoft Access 2000 может быть использован для разработки клиентских приложений, взаимодействующих с Microsoft SQL Server. Как показывает история развития Microsoft, она всегда стремилась к интеграции своих программных продуктов. Хорошо это или плохо — решать Вам…

Что необходимо сказать в пользу Access? Кончено, это визуальность. Действительно, Access всегда был одним из «самых визуальных в мире». Во второх Access позволит нам легко познакомиться с БД. Однако, с помощью только одного Access мы не сможем рассмотреть вопросы связанные с клиент-серверными БД. Зато мы сможем использовать Access как трамплин, для прыжка в мир реляционных БД. Сразу замечу, что в наших занятиях Access будет рассмотрен только в той степени, в которой он необходим Вам, программистам, для изучения теории и практики БД.

Microsoft Access — Работа с запросами Итак, Вы уже знаете, хотя и приблизительно, что такое запросы. Действительно, трудно представить себе работу с БД без использования запросов. Также, Вы знаете, что Access предоставляет свой собтсвенный вариант создания запросов — так называймый Query By Example (QBE). Спешу обрадывать — мы его не будем рассматривать. Почему? Вы забыли о нашей священной миссии… Structured Query Language (SQL)!!!

Как создать запрос в Access 2000? Для этого необходим в окне БД выбрать объект Запросы и вариант Создание запроса в режиме конструктора.

Перед Вами, появиться окно Добавление таблицы с предложением выбрать таблицу или запрос. Можете смело отказаться от данного предложения, нажав кнопку отмена. Почему отказаться? Потому что данный вариант предлагает начать работу с QBE.

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

Все!!! Теперь остается только вводить запросы и проверять результаты. На рисунке, приведенном ниже, Вы можете увидеть текстовое поле для ввода запроса и кнопку на панели инструментов, содержащую восклицательный знак. После того, как Вы запишите SQL запрос, для его запуска необходмио будет воспользоваться указанной ранее кнопкой с восклицательным знаком.

Введение

в SQL.

Итак, в данном разделе вам впервые предстоит познакомиться с языком структурированных запросов SQL (Structure Query Language).

SQL является инструментом для выборки и обработки информации, которая содержится в компьютерной базе данных. Уникальность этого языка состоит в том, что он является единственным стандартным языком баз данных. Язык SQL поддерживают свыше 100 СУБД (Системы Управления Базами Данных), работающих как на мэйнфреймах, так и на обычных ПК. И самое главное: несмотря на то, что каждая СУБД по-своему извращается над стандартом (вносит какие-то свои дополнения), стандартный SQL поддерживают все базы данных.

Как же работает SQL? Когда пользователю необходимо получить информацию из базы данных, он он запрашивает эту информацию у СУБД с помощью SQL. СУБД обрабатывает запрос, находит (или не находит) требуемые данные и возвращает результат пользователю.

Что такое запрос?

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

Функциональные возможности SQL? SQL используется для реализации всех функциональных возможностей, предоставляемых СУБД. К ним относятся:

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

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

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

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

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

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

Думаю, что после всего вышеперечисленного вам будет интересно узнать, что SQL не является полноценным компьютерным языком. SQL — это слабо структурированный язык, инструкции которого обычно встраиваются в базовый язык, например C++, и дают возможность получать доступ к базам данных. SQL является неотъемлемой частью СУБД и работает только с реляционными базами данных.

После доброго глотка теории (и пива, если имеется), можно переходить к следующей главе, в которой мы познакомимся с некоторыми операторами SQL.

Операторы языка SQL. Часть I.

Команды языка SQL можно поделить на три категории:

DDL — Data Definition Language (Язык Определения Данных) — состоит из команд, которые создают объекты (таблицы, индексы, представления, и так далее) в базе данных.

DML — Data Manipulation Language (Язык Манипулирования Данными) — это набор команд, которые определяют какие значения представлены в таблицах в любой момент времени.

DCL — Data Control Language (Язык Управления Данными) — состоит из средств, которые определяют, разрешить ли пользователю выполнять определенные действия или нет.

Рассмотрим основные операторы DDL.

Выборка данных — оператор SELECT.

Замечание: SQL является регистронезависимым языком, то есть различия между большими и малыми буквами не существует.

Предположим у нас есть таблица, которая хранит информацию об имеющихся книгах и их атрибутах (см. рисунок).

Запрос, позволяющий отобразить всю таблицу (она называется Books) целиком, выглядит таким образом:

Select * From Books.

Здесь: Select — оператор выборки данных; * - указывает, что должны выбираться все столбцы в том же порядке, как они определены в базе данных; ключевое слово From указывает откуда должны выбираться данные (имя таблицы).

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

Select Name, Author, Press, Pages.

From Books.

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

Пользователь также имеет возможность форматировать вывод результата запроса на экран. Например, результат запроса.

Select 'Book = ', Name, 'Press = ', Press.

From Books.

дает следующие результаты (см. рисунок).

Как можно заметить, у текстовых столбцов не очень понятное название (Expr2000 и т. п.). Так происходит потому, что таких столбцов в базе не существует и Access дает им собственные названия. Чтобы этого избежать, можно дать этим столбцам псевдонимы.

Select 'Book = ' as BookName, Name, 'Press = 'as PressName, Press.

From Books.

Результат представлен на рисунке.

Кстати, такой псевдоним можно назначить даже существующему в таблице столбцу.

SQL также позволяет создавать «на лету» столбцы, значения в которых строятся на основе какого-либо выражения, в котором участвуют значения «реальных» столбцов, в каждой строке соответственно. Такой столбец называется вычислимым.

Например, вычислим общее количество страниц по имеющимся в наличии книгам.

Select Name, Quantity * Pages as TotalPages.

From Books.

Результат выполнения запроса представлен на рисунке.

Бывают случаи, когда в результирующем наборе строк появляются дубликаты (идентичные строки). Например, рассмотрим следующий запрос:

Select Author From Books.

Результат выполнения запроса представлен на рисунке.

В данном случае мы получаем список всех авторов в таблице, но так как некоторые из них написали более одной книги, то в результирующем наборе встречаются одинаковые строки. Если нам это не нужно, то существует возможность исключить из результирующего набора строк дубликаты с помощью ключевого слова Distinct. Перепишем запрос:

Select Distinct Author From Books.

Результат выполнения запроса представлен на рисунке.

Как видите, строки-дубликаты больше не появляются пред наши светлы очи.

Однако, далеко не всегда нужно выбирать все строки таблицы. Если пользователю нужна какая-то конкретная информация, то он может ограничить результирующий набор строк, используя дополнительные условия (уточняя запрос).

Для установки условия используется ключевое слово Where и набор логических операторов:

> - больше.

>= - больше либо равно.

< - меньше.

<= - меньше либо равно.

= - равно.

— не равно (в некоторых базах используется знак ≠).

and — логическое «И» (умножение).

or — логическое «или» (сложение).

not — логическое «не» (отрицание).

between — принадлежность диапазону.

in — проверка на членство в множестве.

like — проверка на соответствие шаблону.

is null — проверка на равенство значению NULL.

Под значением NULL понимают неопределенное значение. Результатом логического выражения в SQL может быть либо истина, либо ложь, либо NULL (когда любая из частей выражения равна NULL). Строка будет включаться в результирующий набор только в том случае, если результат проверки условий отбора равен истине.

Приведем несколько примеров:

Запрос: необходимо вывести книги по программированию издательств «Питер» и «BHV» .

Select Name as Название, Themes as Тематика, Press as Издательство.

From Books.

Where Themes = 'Программирование' and (Press = 'BHV' or Press = 'Питер').

Результат выполнения запроса представлен на рисунке.

Замечание: строки в SQL берутся в одинарные кавычки.

Запрос: отобразить все книги, у которых количество страниц лежит в пределах от 200 до 600.

Select Name as Название, Pages as Страницы.

From Books.

Where Pages Between 200 And 600.

Результат выполнения запроса представлен на рисунке.

Запрос: отобразить все книги, имена авторов которых лежат в диапазоне от буквы 'В' до 'О'.

Select Name as Название, Author as Автор

From Books.

Where Name Between 'В' And 'О'.

Результат выполнения запроса представлен на рисунке.

Замечание: при сравнении с текстом, отбор осуществляется согласно ASCII-кодам символов.

Запрос: выбрать книги, относящиеся к программированию или к базам данных, и издательства которых не 'Питер'и не 'Бином'.

Select Name as Название, Themes as Тематика, Press as Издательство.

From Books.

Where Press not in ('Питер','Бином').

and.

Themes in ('Программирование','Базы данных').

Результат выполнения запроса представлен на рисунке.

Запрос: выбрать из таблицы тех авторов, у которых в имени и фамилии не менее трех букв 'а'.

Select Name as Автор

From Books.

Where Name Like '*а*а*а*'.

Результат выполнения запроса представлен на рисунке.

Оператор Like реализует поиск по шаблону:

• * - означает, что данной позиции может присутствовать 0 или более любых символов (в других СУБД применяется символ %);
• ? — означает, что в данной позиции обязан присутствовать 1 любой символ (в других СУБД применяется символ _);

# - означает, что в данной позиции обязана присутствовать 1 любая цифра;

[a-z] - означает, что в данной позиции обязан присутствовать 1 символ из указанного диапазона;

[dfaf] - означает, что в данной позиции обязан присутствовать 1 символ из указанного множества;

[!safgwe] - означает, что в данной позиции обязан присутствовать 1 символ, не входящий в указанный диапазон.

Для сортировки результирующих строк используется оператор Order By с необязательным параметром Asc (стоит по умолчанию) — сортировка по возрастанию (по алфавиту для строк), или Desc — сортировка по убыванию.

Запрос: отобразить всех авторов и их книги, авторов отсортировать по возрастанию, а названия книг (по авторам) по убыванию (вторичная сортировка).

Select Author as Автор, Name as Название.

From Books.

Order By Author, Name Desc.

Результат выполнения запроса представлен на рисунке.

Итог:

Запрос на выборку данных подчиняется строгой структуре:

Cначала указывается что выбрать и как отобразить (Select).

Затем откуда выбрать (From).

Как выбрать (Where).

Как сортировать (Order By).

Вставка данных — оператор INSERT.

Инструкция вставки работает по следующим правилам:

Insert Into Books.

Values (100, 'Колобок', 10, 2001, 'Сказки', 'Народ', 'Москва', 1).

в данном случае значения вставляются во все столбцы таблицы в соответствии с их физическим порядком. Если необходимо вставить значения только в некоторые столбцы, то необходимо их явно указывать:

Insert Into Books (Id, Name, Themes, Quantity).

Values (1000, 'Колобок', 'Сказки', 1).

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

Модификация данных — оператор UPDATE.

Эта инструкция позволяет модифицировать существующие значения столбцов.

Например:

Update Books.

Set Quantity = 0.

Where Themes = 'Web-дизайн'.

данный запрос устанавливает количество книг по дизайну в 0.

В инструкции Set можно обновлять сразу несколько столбцов, перечислив их через запятую:

Update Books.

Set Quantity = Quantity + 1, YearsPress = 2002.

Where Themes = 'Web-дизайн'.

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

Примечание: не забывайте ставить условие для обновления данных (Where), иначе инструкция Update установит новое значение для всего столбца.

Удаление данных — оператор DELETE.

А вот эту инструкцию следует использовать аккуратно. Например, запрос.

Delete From Books.

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

Опять-таки не забывайте ставить условия. Например, необходимо удалить из таблицы все записи для авторов с именем, которое начинается на букву 'А'.

Delete.

From Books.

Where Author Like 'А*'.

Примечание: инструкции Insert, Update, Delete не возвращают набора строк — они просто выполняются.

Контрольное задание Для выполнения домашнего задания будем использовать таблицу Books из файла Books. mdb, который находится в папке ./Program/ (разобраться со структурой таблицы вам предстоит самостоятельно). Домашнее задание выполнять в этом же файле.

Вытащить название учебников, которые издавались не издательством 'BHV', и тираж которых >= 3000 экземпляров.

Вытащить книги, со дня издания которых прошло не более года. Tекущую дату можно получить с помощью функции Date () (Например, Select Date ()). Сама дата в Access’вской версии SQL записывается в виде #месяц/день/год# (например, 12.10.2001 будет выглядеть как #10/12/2001#).

Вытащить книги, о дате издания которых ничего не известно.

Вытащить все книги-новинки, цена которых ниже 30р.

Вытащить книги, в названиях которых есть слово Microsoft, но нет слова Windows.

Вытащить книги, у которых цена одной страницы < 10 копеек.

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

Вытащить книги, в названиях которых присутствует не менее трех цифр.

Вытащить книги, в названиях которых присутствует ровно пять цифр.

Удалить книги, в коде которых присутствует цифры 6 или 7.

Проставить текущую дату (функция Date ()) для тех книг, у которых дата издания отсутствует.