Опишем минимальное подмножество языка SQL, опираясь на его реализацию в стандартном интерфейсе ODBC (Open Database Connectivity – совместимость открытых баз данных) фирмы Microsoft.
Операторы языка SQL можно условно разделить на два подъязыка: язык определения данных (Data Definition Language – DDL) и язык манипулирования данными (Data Manipulation Language – DML). Основные операторы языка SQL представлены в табл. 3.
Таблица 3
Рассмотрим формат и основные возможности важнейших операторов, за исключением специфических операторов, отмеченных в таблице символом «*». Несущественные операнды и элементы синтаксиса (например, принятое во многих системах программирования правило ставить «;» в конце оператора) будем опускать.
1. Оператор создания таблицы имеет формат вида:
CREATE TABLE <имя таблицы>
(<имя столбца> <тип данных> [NOT NULL]
[,<имя столбца> <тип данных> [NOT NULL]]... )
Обязательными операндами оператора являются имя создаваемой таблицы и имя хотя бы одного столбца (поля) с указанием типа данных, хранимых в этом столбце.
При создании таблицы для отдельных полей могут указываться некоторые дополнительные правила контроля вводимых в них значений. Конструкция NOT NULL (не пустое) служит именно таким целям и для столбца таблицы означает, что в этом столбце должно быть определено значение.
В общем случае в разных СУБД могут использоваться различные типы данных. В интерфейсе ODBC поддерживаются свои стандартные типы данных, например, символьные (SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR) и др. При работе с БД некоторой СУБД посредством интерфейса ODBC выполняется автоматическое преобразование стандартных типов данных, поддерживаемых интерфейсом, в типы данных источников и обратно. При необходимости обмен данными между программой и источником данных может вестись без преобразования – во внутреннем формате данных источника.
Пример создания таблицы.
Пусть требуется создать таблицу goods описания товаров, имеющую поля: type – вид товара, comp_id – идетификатор компании-производителя, name – название товара и price – цена товара. Оператор определения таблицы может иметь следующий вид:
CREATE TABLE goods (type SQL_CHAR(8) NOT NULL, comp_id SQL_CHAR(10) NOT NULL, name SQL_VARCHAR(20), price SQL_DECIMAL(8,2)).
2. Оператор изменения структуры таблицы имеет формат вида:
Изменение структуры таблицы может состоять в добавлении (ADD), изменении (MODIFY) или удалении (DROP) одного или нескольких столбцов таблицы. Правила записи оператора ALTER TABLE такие же, как и оператора CREATE TABLE. При удалении столбца указывать <тип данных> не нужно.
Пример добавления поля таблицы.
Пусть в созданной ранее таблице goods необходимо добавить поле number, отводимое для хранения величины запаса товара. Для этого следует записать оператор вида:
ALTER TABLE goods (ADD number SQLINTEGER).
3. Оператор удаления таблицы имеет формат вида:
DROP TABLE <имя таблицы>
Оператор позволяет удалить имеющуюся таблицу. Например, для удаления таблицы с именем items достаточно записать оператор вида:
DROP TABLE items.
4. Оператор создания индекса имеет формат вида:
CREATE [UNIQUE] INDEX <имя индекса>
ON <имя таблицы>
(<имя столбца> [ ASC | DESC ]
[,<имя столбца> [ ASC | DESC ]... )
Оператор позволяет создать индекс для одного или нескольких столбцов заданной таблицы с целью ускорения выполнение запросных и поисковых операций с таблицей. Для одной таблицы можно создать несколько индексов.
Задав необязательную опцию UNIQUE можно обеспечить уникальность значений во всех указанных в операторе столбцах. По существу, создание индекса с указанием признака UNIQUE означает определение ключа в созданной ранее таблице.
При создании индекса можно задать порядок автоматической сортировки значений в столбцах – в порядке возрастания ASC (по умолчанию), или в порядке убывания DESC. Для разных столбцов можно задавать различный порядок сортировки.
Пример создания индекса.
Пусть для таблицы ЕМР, имеющей поля: NAME (имя), SAL (зарплата), MGR (руководитель) и DEPT (отдел), нужно создать индекс main_indx для сортировки имен в алфавитном порядке и убыванию размеров зарплаты. Оператор создания индекса может иметь вид:
CREATE INDEX main_indx ON emp (name, sal DESC).
5. Оператор удаления индекса имеет формат вида:
DROP INDEX <имя индекса>
Этот оператор позволяет удалять созданный ранее индекс с соответствующим именем. Так, например, для уничтожения индекса main_indx к таблице emp достаточно записать оператор DROP INDEX main_indx.
6. Оператор создания представления имеет формат вида:
CREATE VIEW <имя представления>
[(<имя столбца> [,<имя столбца> ] ... )]
AS<оператор SELECT>
Данный оператор позволяет создать представление. Если имена столбцов в представлении не указываются, то будут использоваться имена столбцов из запроса, описываемого соответствующим оператором SELECT.
Пример создания представления.
Пусть имеется таблица companies описания производителей товаров с полями: comp_id (идентификатор компании), comp_name (название организации), comp_address (адрес) и phone (телефон), а также таблица goods производимых товаров с полями: type (вид товара), comp_id (идентификатор компании), name (название товара) и price (цена товара). Таблицы связаны между собой по полю comp_id. Требуется создать представление repr с краткой информацией о товарах и их производителях: вид товара, название производителя и цена товара. Оператор определения представления может иметь следующий вид:
CREATE VIEW repr
AS
SELECT
goods.type, companies.comp name, goods.price
FROM
goods, companies
WHERE
goods.comp_id = companies.comp_id
7. Оператор удаления представления имеет формат вида:
DROP VIEW <имя представления>
Оператор позволяет удалить созданное ранее представление. Заметим, что при удалении представления таблицы, участвующие в запросе, удалению не подлежат. Удаление представления repr производится оператором вида:
DROP VIEW repr.
8.1. Оператор выборки записей имеет формат вида:
SELECT [ALL | DISTINCT] <список данных>
FROM <список таблиц>
[WHERE <условие выборки>]
[GROUP BY <имя столбца> [,<имя столбца>]... ]
[HAVING <условие поиска>]
[ORDER BY <спецификация> [,<спецификация>]...]
Это наиболее важный оператор из всех операторов SQL. Функциональные возможности его огромны. Рассмотрим основные из них. Оператор SELECT позволяет производить выборку и вычисления над данными из одной или нескольких таблиц. Результатом выполнения оператора является ответная таблица, которая может иметь (ALL), или не иметь (DISTINCT) повторяющиеся строки. По умолчанию в ответную таблицу включаются все строки, в том числе и повторяющиеся. В отборе данных участвуют записи одной или нескольких таблиц, перечисленных в списке операнда FROM.
Список данных может содержать имена столбцов, участвующих в запросе, а также выражения над столбцами. В простейшем случае в выражениях можно записывать имена столбцов, знаки арифметических операций (+, — , *, /), константы и круглые скобки. Если в списке данных записано выражение, то наряду с выборкой данных выполняются вычисления, результаты которого попадают в новый (создаваемый) столбец ответной таблицы.
При использовании в списках данных имен столбцов нескольких таблиц для указания принадлежности столбца некоторой таблице применяют конструкцию вида: <имя таблицы>.<имя столбца>.
Операнд WHERE задает условия, которым должны удовлетворять записи в результирующей таблице. Выражение <условие выборки> является логическим. Его элементами могут быть имена столбцов, операции сравнения, арифметические операции, логические связки (И, ИЛИ, НЕТ), скобки, специальные функции LIKE, NULL, IN и т. д.
Операнд GROUP BY позволяет выделять в результирующем множестве записей группы. Группой являются записи с совпадающими значениями в столбцах, перечисленных за ключевыми словами GROUP BY. Выделение групп требуется для использования в логических выражениях операндов WHERE и HAVING, а также для выполнения операций (вычислений) над группами.
В логических и арифметических выражениях можно использовать следующие групповые операции (функции): AVG (среднее значение в группе), МАХ (максимальное значение в группе), MIN (минимальное значение в группе), SUM (сумма значений в группе), COUNT (число значений в группе).
Операнд HAVING действует совместно с операндом GROUP BY и используется для дополнительной селекции записей во время определения групп. Правила записи <условия поиска> аналогичны правилам формирования <условия выборки> операнда WHERE.
Операнд ORDER BY задает порядок сортировки результирующего множества. Обычно каждая <спецификация> аналогична соответствующей конструкции оператора CREATE INDEX и представляет собой пару вида: <имя столбца> [ASC | DESC ].
Оператор SELECT может иметь и другие более сложные синтаксические конструкции.
Одной из таких конструкций, например, являются так называемые подзапросы. Они позволяют формулировать вложенные запросы, когда результаты одного оператора SELECT используются в логическом выражении условия выборки операнда WHERE другого оператора SELECT.
Вторым примером более сложной формы оператора SELECT является оператор, в котором отобранные записи в дальнейшем предполагается модифицировать (конструкция FOR UPDATE OF). СУБД после выполнения такого оператора обычно блокирует (защищает) отобранные записи от модификации их другими пользователями.
Еще один случай специфического использования оператора SELECT – выполнение объединений результирующих таблиц при выполнении нескольких операторов SELECT (операнд UNION).
Пример выбора записей.
Для таблицы ЕМР, имеющей поля: NAME (имя), SAL (зарплата), MGR (руководитель) и DEPT (отдел), требуется вывести имена сотрудников и размер их зарплаты, увеличенный на 100 единиц. Оператор выбора можно записать следующим образом:
SELECT name, sal+100 FROM emp.
Пример выбора с условием.
Вывести названия таких отделов таблицы ЕМР, в которых в данный момент отсутствуют руководители. Оператор SELECT для этого запроса можно записать так:
SELECT dept
FROM emp
WHERE mgr is NULL
Пример выбора с группированием.
Пусть требуется найти минимальную и максимальную зарплаты для каждого из отделов (по таблице ЕМР). Оператор SELECT для этого запроса имеет вид:
SELECT dept, MIN(sal), MAX(sal) FROM emp GROUP BY dept.
8.2. Соединение записей из двух таблиц базы данных выполняется оператором JOIN.. Он входит в раздел FROM оператора SELECT и отдельно от него не используется.
SELECT <список данных>
FROM <имя основной таблицы>
{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN <имя присоединяемой таблицы> ON <условие>
В большинстве СУБД при указании слов left, right, full слово outer можно опустить. Слово INNER также в большинстве СУБД можно опустить.
В общем случае СУБД проверяет условие, на соответствие в присоединяемой таблице. Для cross join условие не указывается.
INNER JOIN – Объединяет две таблицы, где каждая строка обеих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор.
OUTER JOIN – Присоединение таблицы с необязательным присутствием записи в таблице. Также как и в случае с inner join, условие по индексированным полям и первичному ключу ускоряет все виды outer join'ов.
LEFT OUTER JOIN – К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.
RIGHT OUTER JOIN – Аналогично left outer join, но применяется для правой таблицы. К правой таблице присоединяются все записи из левой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL.
FULL OUTER JOIN – К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL и плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.
CROSS JOIN – Все возможные сочетания из обеих таблиц. Этот вид соединения нужно использовать с осторожностью, время выполнения растет пропорционально произведению количества записей соединяемых таблиц и зачастую содержит избыточную информацию.
Пример соединения записей из двух таблиц базы данных.
SELECT *
FROM Person
INNER JOIN City ON Person.CityId = City.Id
9. Оператор изменения записей имеет формат вида:
UPDATE <имя таблицы>
SET <имя столбца> = {<выражение> , NULL}
[, SET <имя столбца> = {<вьфажение>, NULL}... ]
[WHERE <условие>]
Выполнение оператора UPDATE состоит в изменении значений в определенных операндом SET столбцах таблицы для тех записей, которые удовлетворяют условию, заданному операндом WHERE.
Новые значения полей в записях могут быть пустыми (NULL), либо вычисляться в соответствии с арифметическим выражением. Правила записи арифметических и логических выражений аналогичны соответствующим правилам оператора SELECT
Пример изменения записей.
Пусть необходимо увеличить на 500 единиц зарплату тем служащим, которые получают не более 6000 (по таблице ЕМР). Запрос, сформулирова¬ный с помощью оператора SELECT, может выглядеть так:
UPDATE emp
SET sal = 6500 WHERE sal <- 6000.
10. Оператор вставки новых записей имеет форматы двух видов:
INSERT INTO <имя таблицы>
[(<список столбцов>)]
VALUES (<список значений>)
и
INSERT INTO <имя таблицы>
[(<список столбцов>)]
<предложение SELECT>
В первом формате оператор INSERT предназначен для ввода новых записей с заданными значениями в столбцах. Порядок перечисления имен столбцов должен соответствовать порядку значений, перечисленных в списке операнда VALUES. Если <список столбцов> опущен, то в <списке значений> должны быть перечислены все значения в порядке столбцов структуры таблицы.
Во втором формате оператор INSERT предназначен для ввода в заданную таблицу новых строк, отобранных из другой таблицы с помощью предложения SELECT.
Пример ввода записей.
Ввести в таблицу ЕМР запись о новом сотруднике. Для этого можно записать такой оператор вида:
INSERT INTO emp
VALUES («Ivanov», 7500, «Lees», «cosmetics»)
11. Оператор удаления записей имеет формат вида:
DELETE FROM <имя таблицы>
[WHERE <условие>]
Результатом выполнения оператора DELETE является удаление из указанной таблицы строк, которые удовлетворяют условию, определенному операндом WHERE. Если необязательный операнд WHERE опущен, то есть условие отбора удаляемых записей отсутствует, удалению подлежат все записи таблицы.
Пример удаления записей.
В связи с ликвидацией отдела игрушек (toy), требуется удалить из таблицы ЕМР всех сотрудников этого отдела. Оператор DELETE для этой задачи будет выглядеть так:
DELETE FROM emp
WHERE dept = «toy».
Работа с базами данных Microsoft Access в среде программирования Delphi 7.
Не вдаваясь в подробности организации доступа к базам данных в Delphi, скажем, что для этого удобнее всего использовать созданный компанией Microsoft интерфейс высокого уровня, называемый ActiveX Data Object (ADO), ориентированный на применение в прикладных программах.
Примером работы с базами данных Microsoft Access может служить программа «Оптимизация трех стадий токарной обработки». В ней база данных используется для ввода технических характеристик металлорежущих станков. Схема данных базы данных приведена на рис. 2.9.
Рис. 2.9. Схема данных
Для работы с базами данных Microsoft Access посредством ADO в предложении uses раздела текста программы interface включаются модули DB и ADODB.
Работа с базой данных Microsoft Access начинается с подключения таблицы, данные из которой будут использоваться. Для этого на форму наносится объект ADOTable, класса TADOTable (см. рис. 2.10).
Рис. 2.10. Фрагмент формы с расположенными на нем объектами для работы с базой данных Microsoft Access (1 – ADOTable1; 2 – ADOQuery1; 3 – ADOQuery2)
Затем необходимо при помощи Инспектора объектов заполнить некоторые свойства объекта ADOTable.
Во-первых – свойство ConnectionString. Щелкнув левой кнопкой мыши по кнопке с троеточием справа от интересующего свойства можно вызвать мастер его заполнения. В появившемся окне необходимо выбрать опцию Use Connection String и нажать клавишу Build.
а
б
Рис. 2.11. Заполнение свойства ConnectionString объекта ADOTable
На первой вкладке мастера выбирается «поставщик данных» (см. рис. 2.11.а), т.е. тип подключаемой базы данных. Для подключения к базе данных Microsoft Access используется Microsoft Jet 4.0 OLE DB Provider. На вкладке «Подключение» требуется указать файл базы данных и учетные данные для подключения (см. рис. 2.11.б). По умолчанию для баз данных Microsoft Access используется имя пользователя Admin и пустой пароль.
Также существует возможность осуществить выбор пути к файлу базы данных непосредственно во время выполнения программы. Для этого на форму необходимо добавить объект ADOConnection, заполнив его свойство ConnectionString как показано выше для объекта ADOTable. Следует учесть, что после добавления объекта ADOConnection отпадает необходимость заполнения свойства ConnectionString у объектов ADOTable, ADOQuery и т.п. Вместо него свойству Connection указанных объектов присваивается имя объекта ADOConnection. Затем, для выбора пути к файлу базы данных можно использовать, например, следующий программный код:
а
б