Заключение.

Введение

Занятие 1. Построение простых запросов на SQL к одной таблице

1.1. Построение запроса в QBE и просмотр его в режиме SQL

1.2. Задание списка имен столбцов и изменение имен столбцов в запросе.

1.3. Построение запросов с комплексными условиям

1.4. Вставка текстовых столбцов.

1.5. Вычисляемые столбцы

Занятие 2. Построение запросов со сложными условиями.

2.1. Использование в запросах специальных слов

2.2. Построение запросов на агрегирование

2.3. Включение сортировки строк в запросы

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

Занятие 3. Построение запросов к нескольким таблицам.

3.1. Объединение таблиц в запросах и выполнение операции JOIN (объединение)

3.2. Объединение копий таблиц

3.3.Вложенные запросы

Занятие 4. Запросы на ввод и корректировку данных в базе.

4.1. Запросы на ввод данных в таблицы

4.2. Запросы на изменение данных в таблицах

4.3. Запросы на удаление данных из таблиц

Занятие 5. Запросы на создание и изменение базы данных.

5.1. Запросы на создание таблиц базы данных

5.2. Запросы на изменение таблиц базы данных

5.3. Запросы на удаление таблиц базы данных

Занятие 6. Создание приложений с использованием запросов на языке SQL.

6.1. Приложение в среде VBA

6.2. Разработка локальных Web-приложений (на VBScript).

6.3. Некоторые приемы ASP-технологии.

Заключение.

Литература.

Приложения:

Приложение 1. Описание схемы учебной базы данных «Автомобильный салон» (Avto.mdb).

Приложение 2. Разработка VBA приложения для обработки SQL-запросов.

Введение

В настоящий момент можно твердо утверждать, что наиболее распространенный способ организации больших объемов информации основан на методологии реляционных баз данных. В основе концепции реляционных баз данных и языка запросов SQL (Structured Query Language), лежит реляционная алгебра (или алгебра над отношениями), которая является развитием фундаментальных понятий теории множеств, известных нам из базового курса математики «теория множеств»[1].

Определения. Под множеством в математике понимается собрание определенных и различных между собой объектов (или элементов), представляемых как единое целое. Например, мы говорим «множество людей», «множество автомобилей», «множество предметов» и т.п. Изучая множества, математика не интересует ни предметная область, к которой относятся элементы множества, ни физические их свойства.

Иначе говоря, множество (A) состоит из элементов (a₁, a₂, a₃,…,a_N), которые объединены по некоторым правилам или признакам. Это обстоятельство обычно записывается так: A={ a₁, a₂, a₃,…,a_N }. Запись a₁Î A обозначает, что элемент a₁ принадлежит множеству A (противоположное утверждение записывается следующим образом- a₁Ï A). Пустое множество, которое не содержит элементов, обозначается символом Æ.

Существует два способа определения конкретного множества:

- прямой (или явный) метод, когда перечисляются все элементы, входящие в множество;

- неявный (интенсиональный), основанный на использовании некоторой функции P_A(a_i), которая может принимать одно из двух значений {True,False} («ИСТИНА» или «ЛОЖЬ»). Значение «True» означает, что элемент a_iÎA, «False» - в противном случае (a₁Ï A). Например, пусть P_A(a_i), определяет A- множество «легковых автомобилей», тогда P_A(«Жигули»)=True, а P_A(«ЗИЛ-130»)=False.

Функция P_A(x), определенная над множеством X, называется предикатом и представляет собой двухзначную функцию, принимающую в качестве аргумента значение x_i из некоторого множества-универсума X (некоторого более общего множества по отношению к множеству A). Например, пусть X множество всех автомобилей, а A – множество легковых автомобилей, тогда P_A(x) есть предикат, определяющий A, принимает значение True только тогда, когда x_i является легковым автомобилем. Отношение между множествами X и A можно отобразить схемой, как показано на рис.1.

Рис.1. Схема отношений универсума X, множества A и предиката P_A(x)

Таким образом, в нашем примере предикат P_A(x) определяет отношение включения множества A в множества X, которое обозначается как AÍX. Например множество A – «легковые автомобили» включается в множество X - «все автомобили».

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

Объединение множеств: C=AÈB. Например, пусть A={a,b,c,d} и B={a,c,d,x}, тогда C={a,b,c,d,x}. Заметим, AÈÆ=A.

Пересечение множеств: C=AÇB. Пусть A и B такие же как и в примере выше, тогда C={a,c,d}. Заметим, AÇÆ=Æ.

Разность: C=A – B. Множество Cсодержит все элементы, принадлежащие множество A, но не содержатся в B. Пусть A={a,b,c,d,x,y} и B={a,b,x,y,z}, тогда разность C={c,d}.

Заметим, чтоA - Æ=A.

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

Часто предикат P_A(x) интерпретируется как двухзначная функция P(x) над множеством X={x₁, x₂, x_n}, которая определяет для каждого x_i истинность утверждения: «обладает или нет этот элемент некоторым свойством x_i». Например, пусть P(x₁) определяет, является ли легковой автомобиль красным, тогда выражение P(«Жигули»)=True, обозначает, что конкретный легковой автомобиль марки «Жигули» является красным и его можно отнести к множеству B «красные легковые автомобили» (очевидно, что в данном случае выполняется условие BÍA).

Рассмотрим случай, когда для множества объектов Z («множество автомобилей») задано несколько свойств (или множество свойств X={x₁, x₂, x_n}), каждое из которых задано одним из множеств X1, X2,…,Xn. Например, если Z множество всех автомобилей, и его объекты имеют свойства x₁, x₂ и x₃ , то X1={«красный», «синий», «белый»} – множество, определяющее цвет автомобилей, X2={1999,2000,2001} – множество, определяющее год выпуска, X3={«Мерседес», «Опель», «БМВ»} - множество, определяющее фирму производителя. Тогда, если нам необходимо отобрать все автомобили со свойствами: x1=(«красный»), x2=(2000) и x3=(«Опель»), нам надо построить сложный предикат следующего вида:

P_A(x)=P_Z(x₁=(«красный»)) Ç P_Z(x₂=(2000))Ç P_Z(x₃=(«Опель»)).

Применив, сконструированный предикат ко всем объектам множества Z, мы получим все автомобили, удовлетворяющие сформулированному в задаче запросу.

Формальные языки (в том числе и язык SQL) позволяют строить такие предикаты, не прибегая к программированию, а используя непроцедурные формальные конструкции языка похожие на предложения в обычном естественном языке. Например, выше описанный предикат P_A(x) на формализованном языке (близком к естественному) можно представить так:

ВЫБЕРИ автомобили,

у которых цвет= «Красный» И год= «2000» И фирма= «Опель».

В этом выражении слова ВЫБЕРИ и И являются ключевыми словами формального языка запроса, а цвет, год и фирма - именами множеств свойств объектов из универсума «автомобили». В рассмотренном запросе (или предикате P_A(x)) свойства принимают конкретные значения.

В общем случае функцию предиката можно построить над несколькими переменными P_A(x₁, x₂, x_n). Предикаты такого вида определяют множества из элементов вида (x₁, x₂,…,x_n), которые называются кортежами. Например, пусть X- множество всех автомобилей, у которых x₁- свойство, определяющее год выпуска, а x₂ – фирму изготовителя. Например, построим кортежи: x₁=(«красный»,2000, «Опель»), x₂ =(«белый»,2001, «БМВ»), или x₃ =(«белый»,2001, «Мерседес») и т.п.. Теперь, если мы хотим построить новое множество A, которое содержит легковые автомобили года выпуска 2000 и производства фирмы «Мерседес», то сначала надо построить предикат вида P_A(x₂, x₃ ), который принимает значение True при x₂=2000, x₃=«Мерседес». Затем сформировать множество X всех кортежей (x₂, x₃), и после этого, применив, полученный предикат P_A(x₂, x₃) ко всем кортежам множества X, мы получим множество А из объектов (т.е. автомобилей)с интересующими нас свойствами. На формализованном языке запросов данную процедуру можно записать так:

ВЫБЕРИ ВСЕ объекты ИЗмножества автомобили,

у которых год= «2000» И фирма= «Опель».

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

Следует отметить, что переменные x₁, x₂, x_n, определяющие кортежи, могут определяться из различных универсумов. Множество, которое получается в результате применения многозначного предиката к нескольким универсам, называется отношение. Иными словами, отношение это множество, построенное из кортежей, которые в свою очередь формируются из элементов других множеств («цвет», «год выпуска», «пробег» и т.п.), описывающие свойства исходного универсума («автомобили»). Взаимосвязь понятий «множество» (универсум), «кортежи», многозначный «предикат» и «отношение» показано на Рис.2.

Рис.2. Схема связи основных понятий: универсум, кортежи, предикат и отношение

На рис.2 показана схема решения задачи синтеза кортежей из исходных множеств, которые описывают свойства объектов X_i («прямая» задача). Однако, на практике часто возникает «обратная» задача, которая заключается в том, что необходимо выполнить анализ исходных кортежей и провести их декомпозицию, с целью получения оптимальной структуры исходных множеств. Решению этих задач посвящено много теоретических работ, с которыми можно познакомиться в литературе, посвященной вопросам «реляционной алгебры»[].

Базы данных, построенные на отношениях, называются реляционными базами данных.

Отношения обозначаются либо как множества R={r₁, r₂,…,r_m }, где r_iÎ R не что иное как i-й объект, свойства которого определяется кортежем (x₁, x₂,…,x_n), либо как множество R={(x₁, x₂,…,x_n)_i}, где (x₁, x₂, ,x_n)_i – i-й кортеж, однозначно определяющий свойства объекта r_i Î R.

На практике при создании реляционных баз данных многомерные отношения R={(x₁, x₂,…,x_n)_i} обычно представляются в виде двухмерных таблиц R, которые удовлетворяют следующим основным свойствам:

- строки таблицы R содержат кортежи (x₁, x₂,…,x_n)_i, которые описывают свойства одного объекта r_i Î R. Строка таблицы R называется записью.

- столбец – содержит данные, принадлежащие к одному свойству (x_i). Столбец называется полем, которое имеет имя и тип данных;

- на пересечении записи и поля лежит атрибут, который является атомарным (т.е. «неделимым») и однозначным[1];

- порядок записей и полей в таблице R не имеет значения и не влияет на кортежи;

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

Ниже приведен пример реляционной таблицы R=(Номер, Марка, Цвет, Год выпуска, Цена), которая содержит описания свойств выставленных на продажу в автосалоне автомобилей :

Таблица 1

Пример отношения R=(Номер, Марка, Цвет, Год выпуска, Цена)

Над отношениями R справедливы все те операции, которые применимы над множествами. Некоторые операции, которые описаны выше: объединение, пересечение, вычитание, здесь интерпретируются следующим образом.

Под операцией «объединение» двух отношений R1 и R2, понимается выборка всех записей, которые либо содержатся в одном из двух отношений, либо являются общими для двух отношений (в языке SQL соответствует операции UNION).

Под операцией «пересечение» двух отношений R1 и R2, понимается выборка всех строк, которые являются общими для двух отношений (в языке SQL соответствует операции INTERSECT).

Под операцией «разность» двух отношений R1 и R2, понимается выборка строк, которые содержатся только в одном из двух отношений (в R1) (в языке SQL соответствует операции EXPECT)..

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

- «проекция», которая определяет подмножество полей из заданного отношения (или набора отношений). Иначе эту операцию называют «вертикальной выборкой». Пример, рассмотрим в качестве исходного отношения R=(Номер, Марка, Цвет, Год выпуска, Цена). На базе исходного отношения построим его проекцию R1, которая является новым отношением R1=(Год выпуска, Марка). Проекция R1 в табличной форме будет иметь вид:

Таблица 2

Пример проекции отношения R в виде нового отношения
R1=(Год выпуска, Марка).

Таблица 2
Проекция R1 на отношением R

Год выпуска	Марка
	«Мерседес»
	«БМВ»
	«Опель»

 

Построение проекции R1 над отношением R соответствует фразе на языке SQL: SELECT [Год выпуска],[ Марка] FROM R.

- селекция (или ограничение), - это операция выбора строк, удовлетворяющих определенным условиям (иначе эту операцию называют «горизонтальной выборкой»). Например, выберем из исходного отношения R (см. описание выше), автомобили красного цвета. Это соответствует фразе на языке SQL: SELECT * FROM R WHERE[Цвет]=«красный». В результате получается новое отношение R2, которое показано в таблице 3 ниже:

 

Таблица 3

Пример селекции отношения R и получение нового отношения R2=(Номер, Марка,[Цвет]= «красный», Год выпуска, Цена.).

Номер	Марка	Цвет	Год выпуска	Цена
	«БМВ»	красный
	«Опель»	красный

 

Оператор WHERE выполняет функцию предиката P(x_i=a), фильтрующего нужные строки из исходного отношения R.

 

- естественное соединение – выполняет операцию объединения отношений. Здесь выбираются только те кортежи, у которых в указанном поле есть совпадения. Например, есть два отношение R1=([Имя],[Отдел]) и R2=([Отдел],[Название]), и требуется построить новое отношение R3=([Имя],[Отдел],[Название]). В табличном виде решение этой задачи показано на рис.3. ниже. На языке SQL эта операция выглядит так:

SELECT R1.[Имя], R1.[Отдел],R2[Название] FROM R1, R2 WHERE R1.[Отдел]= R2.[Отдел].

Иллюстрация процесса выполнения операции соединения показана на рис.3. ниже.

- произведение – результат соединения двух отношений, каждая запись одного отношения соединяется с записью второго отношения ( аналогично операции соединении без условия). На языке SQL эта операция выглядит так:

 

SELECT R1.[Имя], R1.[Отдел], R2[Название] FROM R1, R2.

 

 

Рис.3. Пример выполнения операции «соединения» (R3= R1Å R2)

 

В случае примера отношений R1 и R2, которые рассмотрены выше, результатом операции произведение будет новое отношение R3, строки которого показаны в таблице 3.

Таблица 3

Пример произведения отношений R1 и R2 и получение нового отношения R3= R1хR2 .

 

Имя	Отдел	Наименование
Иванов		Бухгалтерия
Петров		Бухгалтерия
Сидоров		Бухгалтерия
Иванов		Отдел кадров
Петров		Отдел кадров
Сидоров		Отдел кадров

 

Кроме обоснования структуры предложений формального языка запросов SQL, реляционная алгебра играет большую роль в описании методологии синтеза новых отношений и декомпозиции исходных отношений, полученных в результате анализа реальной объектной среды. Применение формальной реляционной теории помогает в построении оптимальной структуры базовых отношений, составляющих основу для проектирования реляционной базы. Решение этой задачи сводится к нахождению рациональной процедуры построения нормализованных отношений (или как принято говорить «нормальных форм»), т.е. таких отношений, которые удовлетворяют определенным требованиям полноты, целостности, отсутствия избыточности, непротиворечивости и т.п. Однако эти вопросы выходят за рамки данной методической работы и здесь не рассматриваются. В предлагаемых примерах используются базы данных, построенные на уже нормализованных отношениях.

Для построения реляционных баз данных используются специальные программные комплексы, которые называются СУБД (системы управления базами данных). Известно много СУБД различных фирм производителей программного обеспечения: Informix (IBM), FireBird (Borland), SQL Server 2000 (MicroSoft), Oracle (Oracle), MySql и т.п.

Одной из популярных СУБД для создания небольших офисных приложений является СУБД Access (продукт компании MicroSoft).

Процесс создания реляционной базы данных с использованием СУБД MicroSoft в упрощенном виде можно описать следующей последовательностью процедур:

- проектирование логической схемы базы данных (определения состава таблиц (базовых отношений), описание полей, выбор ключей и связей между таблицами);

- создание базы данных в среде СУБД;

- создание описаний таблиц (описание столбцов, назначение ключей, присвоение имени таблице);

- создание схемы базы данных ;

- ввод данных в таблицы;

- подготовка и выполнение запросов;

- разработка диалоговых форм для работы с базой данных;

- подготовка и выполнение отчетов, подключение отчетов к диалоговым формам.

Для выполнения упражнений на занятиях необходимо создать в СУБД Access учебную базу данных AVTO (учет заказов в автомобильном салоне) по описанию, которое представлено в Приложении 1. Эта база данных состоит из четырех таблиц:

 

Salespeople(snum, sname, saddress, comm., stel, semail) – список продавцов автомобилей;

Customers (cnum, cname, caddress, rating., ctel, cemail)- список покупателей (клиентов) автомобилей;

Price(pnun, pname, peddim, price, pdate) – прайс-лист, продаваемых автомобилей;

Orders (onum, odate, amount, snum, cnum, pnun) – журнал учета продаж автомобилей.

 

База данных AVTO используется для выполнения ежемесячных расчетов, определения доходов и расходов по автосалону в различных разрезах. Например, владельца салона интересует, кто из продавцов продал автомобилей больше чем другие, или кто из покупателей купил самый дорогой автомобиль, какие марки автомобилей за отчетный период пользовались большим спросом, какова общая сумма проданных автомобилей и т.д. Для получения ответов на эти и другие вопрос необходимо иметь возможность без утомительного программирования формулировать запросы к базе данных на языке, который похож на естественный язык. Иначе говоря, запросы к базе данных должны записываться на непроцедурном языке, с помощью которого пользователь формулирует «что он хочет получить» от базы данных, а компьютер сам реализует этот запрос в программу поиска нужной информации и представляет пользователю результаты запроса в удобной для анализа форме. Для большинства реляционных СУБД таким языком является язык структурированных запросов SQL, который был разработан в середине 70 годов прошлого столетия фирмой IBM. Затем в 1979 году фирма Oracle разработала коммерческую версию этого языка, который широко внедряла в различных приложениях.

Благодаря своим достоинствам язык SQL стал общепринятым языком для реляционных баз данных. В 1986 году Американский институт стандартов (ANSI – American Nation Standard Institute) присвоил языку SQL статус национального стандарта для реляционных СУБД. Позже этот статус был подтвержден Международной организацией стандартов (ISO – International Standard Organization). В настоящее время практически все разработчики реляционных СУБД придерживаются стандарта SQL ANSI.

		SQL (Structured Query Language)

 

 

Рис.4. Схема группировки команд языка SQL

 

Особенности языка SQL следующие:

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

· SQL- это непроцедурный язык, используя который пользователь формулирует какая информация ему нужна, и не определяет алгоритма ее поиска;

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

- извлечения данных (DQL- Data Query Language);

- вставка, изменение или удаление строк и столбцовDML -Data Manipulation Language) таблицы;

- создание, модификация и уничтожение объектов базы данных (DDL - Data Definition Language) ;

- обеспечение целостности базы данных и доступа к данным (DML - Data Control Language)

Обычно команды языка SQL группируют по функциям как показано на рис. 4.

Очень широко конструкции языка SQL используются в приложениях, которые создаются при помощи языков современных программирования (например, VBA, VB, Delphi, С++ и т.п.). Появляются специальные инструментарии, с помощью которых конструируются диалоговые приложения на базе реляционных систем и языка SQL. В последние годы все больше полезных и эффективных экономических приложений разрабатывается самими пользователями, без привлечения специалистов в области программирования. Поэтому курс по изучению основ языка SQL входит в состав базовых курсов информатики для студентов экономических направлений.

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