Определение данных
Определение данных — это манипулирование целыми таблицами. Сюда включаются операции:
□ создания новой таблицы;
□ удаления таблицы;
240
Часть II. Технологии доступа к данным
П изменения состава полей таблицы;
□ создания и удаления индекса.
Эти действия выполняются с помощью подмножества инструкций определения данных языка SQL.
Создание и удаление таблицы
Для создания таблицы служит инструкция create table, который имеет следующий формат:
CREATE TABLE <Имя таблицы>
(<Имя поля> <Тип данных>,
<Имя поля> <Тип данных>);
Обязательными операндами являются имя создаваемой таблицы и имя как минимум одного поля с соответствующим типом данных.
(Замечание^
d действительности вместо имени таблицы указывается имя главного файла таблицы.
Для локальной таблицы ее формат автоматически определяется по расширению файла: db для таблицы Paradox и dbf для таблицы dBase. Если расширение файла не указано, то тип таблицы определяется драйвером, заданным в BDE для локальных БД (см. главу 12). По умолчанию установлен драйвер Paradox.
Файлы таблицы размещаются в каталоге БД, на который указывает псевдоним БД. Для компонента Query псевдоним задается свойством DatabaseName.
Порядок следования строк с описаниями полей определяет порядок расположения полей создаваемой таблицы. Отметим, что описания полей могут располагаться подряд, а не занимать отдельные строки инструкции.
Типы данных языка SQL и соответствующие им типы данных для таблиц dBase и Paradox приведены в табл. 8.1.
В приведенной таблице n обозначает длину поля в байтах, х — число цифр, y — число цифр после десятичной точки. Для типа character допускается сокращение char. Отметим, что в стандарте SQL-92 число допустимых для полей типов данных намного меньше, например, нет автоинкрементного типа.
Таблица 8.1. Типы данных для таблиц БД
SQL dBase Paradox
SMALLINT Number (6,10) Short
INTEGER Number (20,4) Long Integer
DECIMAL(X,Y) p BCD
Глава 8. Реляционный доступ к данным с помощью BDE
241
Пример создания таблицы средствами языка SQL:
CREATE TABLE NewTable.dbf (Number INTEGER, Name CHAR(20), BirthDay DATE);
В каталоге БД создается новая таблица NewTable формата dBase, для которой определены целочисленное поле Number, символьное поле Name и поле даты BirthDay.
Если таблица с заданным именем уже существует, то при выполнении инструкции создания таблицы генерируется исключение.
Для таблицы Paradox можно определить ключ (главный, или первичный), указав описатель primary key и перечислив в скобках после него поля, образующие этот ключ. Ключевые поля должны быть в списке полей первыми. Вот пример создания таблицы с построением главного ключа:
CREATE TABLE Personnel.db
(Code AUTOINC,
Name CHAR (20),
242
Часть II. Технологии доступа к данным
Position CHAR(15), Salary NUMERIC(10,2), PRIMARY KEY(Code));
Новая таблица Personnel имеет формат Paradox, и для нее определены автоинкрементное поле кода code, символьные поля фамилии Name и должности Position, а также числовое поле оклада salary. По полю кода построен главный ключ.
Для удаления таблицы предназначена инструкция:
DROP TABLE <Имя таблицы>;
Например:
DROP TABLE NewTable.dbf;
В результате выполнения этой инструкции с диска удаляются все файлы, относящиеся к таблице с именем NewTabie. Если таблица не существует или с ней работает другое приложение, то генерируется исключение.
Изменение состава полей таблицы
Изменение состава полей таблицы заключается в добавлении или удалении полей и приводит к изменению ее структуры, при этом таблицу не должны использовать другие приложения. Изменение состава полей таблицы выполняется инструкцией alter table:
ALTER TABLE <имя таблицы>
ADD <имя поля1> <тип данных1>, DROP <имя поля1>,
ADD <имя поляЫ> <тип данныхЫ>, DROP <имя поляЫ>;
Операнд add добавляет к таблице новое поле, имя и тип которого задаются так же, как и в инструкции create table, а операнд drop удаляет из таблицы поле с заданным именем. Операнды add и drop не зависят друг от друга и могут следовать в произвольном порядке.
При попытке удалить отсутствующее поле или добавить поле с существующим именем генерируется исключение.
Пример изменения структуры таблицы:
ALTER TABLE Personnel.db
ADD Section SMALLINT, ADD Note CHAR (30), DROP Position;
К таблице Personnel добавляются целочисленное поле номера отдела section и символьное поле примечаний Note, поле Position удаляется.
Глава 8. Реляционный доступ к данным с помощью BDE
243
Создание и удаление индекса
Напомним, что индекс обеспечивает быстрый доступ к данным, хранимым в поле, для которого он создан. Для ускорения операций с таблицей индексными следует делать поля, по которым часто производятся поиск и отбор записей. Индекс создается инструкцией create index следующего формата:
CREATE INDEX
<Имя индекса> ON <Имя таблицы> (<Имя поля1>,..., [<Имя поляЫ>]);
Одной инструкцией можно создать один индекс, при этом одно поле может входить в состав нескольких индексов. Кроме того, не требуется, чтобы значения составляющих индекс полей были уникальными. При сортировке по индексу записи упорядочиваются в порядке возрастания значений индексных полей.
С помощью инструкции create index для таблиц dBase создаются индексы, а для таблиц Paradox — вторичные индексы. Напомним, что первичным индексом таблиц Paradox является ключ, описываемый непосредственно при создании таблицы. Использование инструкции create index является единственным способом определения индекса для таблиц dBase.
Так можно создать индекс по одному полю:
CREATE INDEX
indName ON Personnel.db (Name)
А так по двум:
CREATE INDEX
indNamePosition ON Personnel.db (Name, Position)
Для удаления индекса используется инструкция drop index формата
DROP INDEX
<Имя таблицы>.<Имя индекса> ИЛИ
DROP INDEX
<Имя таблицы>.PRIMARY
Во время удаления индекса таблица не должна использоваться другими приложениями. При выполнении инструкции drop index можно удалить один индекс, обозначив его составным именем, состоящим из имени таблицы и имени собственно индекса. Если удаляется первичный индекс (ключ) таблицы Paradox, то вместо имени индекса указывается описатель primary, поскольку главный ключ не имеет имени.
Например, в инструкции
DROP INDEX
"Personnel.db".indNamePosition
ИЗ таблицы Personnel удаляется ИНДеКС indNamePost, созданный ПО ПОЛЯМ Name И Position.
244
Часть II. Технологии доступа к данным
Первичный ключ удаляется так:
DROP INDEX
"Personnel.db".PRIMARY
Если удаляемый индекс отсутствует или таблица используется другим приложением, то генерируется исключение.
Отбор данных из таблиц
Отбор данных из таблиц заключается в получении из них полей и записей, удовлетворяющих заданным условиям. Результат выполнения запроса, на основании которого отбираются записи, называют выборкой. Данные можно выбирать из одной или нескольких таблиц с помощью инструкции select.
Описание инструкции SELECT
Инструкция select — важнейшая инструкция языка SQL. Она используется для отбора записей, удовлетворяющих сложным критериям поиска, и имеет следующий формат:
SELECT [DISTINCT] {* | <Список полей>} FROM <Список таблиц> [WHERE <Условия отбора>] [ORDER BY <Список полей для сортировки>] [GROUP BY <Список полей для группирования^ [HAVING <Условия группирования^ [UNION <Вложенная инструкция SELECT>]
Результат выполнения SQL-запроса, заданного инструкцией select, представляет собой выборку записей, отвечающих заданным в нем условиям. При рассмотрении инструкции select будем предполагать, что SQL-запрос набран и выполнен с помощью компонента Query. В этом случае результатом выполнения запроса является соответствующий этому компоненту набор данных.
В таком результирующем наборе данных могут быть разрешены или запрещены повторяющиеся записи (т. е. имеющие одинаковые значения всех полей). Этим режимом управляет описатель distinct. Если он отсутствует, то в наборе данных разрешаются повторяющиеся записи.
В инструкцию select обязательно включается список полей и операнд from, остальные операнды могут отсутствовать. В списке операнда from перечисляются имена таблиц, для которых отбираются записи. Список должен содержать как минимум одну таблицу.
Список полей определяет состав полей результирующего набора данных, эти поля могут принадлежать разным таблицам. В списке должно быть задано хотя бы одно поле. Если в набор данных требуется включить все поля таблицы (таблиц), то вместо перечисления имен полей можно указать символ *. Если список содержит поля нескольких таблиц, то для указания принадлежности поля к той
Глава 8. Реляционный доступ к данным с помощью BDE
245
или иной таблице используют составное имя, включающее в себя имя таблицы и имя поля, разделенные точкой: <имя таблицы>.<имя полях
Операнд where задает условия (критерии) отбора, которым должны удовлетворять записи в результирующем наборе данных. Выражение, описывающее условия отбора, является логическим. Его элементами могут быть имена полей, операции сравнения, арифметические и логические операции, скобки, специальные
ФУНКЦИИ LIKE, NULL, IN И Др.
Операнд group by позволяет выделять группы записей в результирующем наборе данных. Группой являются записи с одинаковыми значениями в полях, перечисленных за операндом group by. Выделение групп требуется для выполнения групповых операций над записями, например, для определения количества какого-либо товара на складе.
Операнд having действует совместно с операндом group by и используется для отбора записей внутри групп. Правила записи условий группирования аналогичны правилам формирования условий отбора в операнде where.
Операнд order by содержит список полей, определяющих порядок сортировки записей результирующего набора данных. По умолчанию сортировка по каждому полю выполняется в порядке возрастания значений; если необходимо задать для поля сортировку по убыванию, то после имени этого поля указывается описатель DESC.
Инструкции select могут иметь сложную структуру и быть вложенными друг в друга. Для объединения инструкций используется операнд union, в котором располагается вложенная инструкция select, называемая также подзапросом. Результирующий набор данных представляют записи, отобранные с учетом выполнения условий отбора, заданных операндами where обеих инструкций.
Инструкция select используется также внутри других инструкций, например, инструкций модификации записей, обеспечивая для их выполнения требуемый отбор записей.
Управление полями
Управление полями состоит в указании полей таблицы (таблиц), включаемых в результирующий набор данных. Как отмечалось выше, при отборе всех записей таблицы условия отбора записей не указываются: если вместо списка полей указать *, то в наборе данных оказываются все поля записей. При этом можно не задумываться о названиях полей. Порядок следования полей в наборе данных соответствует порядку физических полей таблицы, определенному при ее создании.
Пример отбора всех полей в таблице:
SELECT * FROM Personnel.db
В результате выполнения этого запроса из таблицы Personnel в набор данных попадают все поля и все записи, и набор данных имеет вид:
Code Name Position Salary
1 Иванов P.O. Директор 6700
246
Часть II. Технологии доступа к данным
2 Петров А.П. Менеджер 5200
3 Семенова И.И. Менеджер 5200
4 Кузнецов П.А. Секретарь 3 600
5 Попов А.Л. Водитель 2400
6 Васин Н.Е. Водитель 2500
При необходимости получения данных из нескольких полей таблицы после слова select через запятую перечисляются в нужном порядке названия этих полей. Порядок полей в наборе данных будет соответствовать порядку полей в списке. Если имя поля указано в списке неоднократно, то в наборе данных оказывается несколько столбцов с одинаковыми именами и данными. Например:
SELECT Name, Salary
FROM Personnel.db
В набор данных, формируемый в результате выполнения этого SQL-запроса, включаются ПОЛЯ Name И Salary всех записей ИЗ таблицы Personnel. Набор данных получает вид:
Name | Salary |
Иванов P.O. | |
Петров А.П. | |
Семенова И.И. | |
Кузнецов П.А. | |
Попов А.Л. | |
Васин Н.Е. |
Можно указать все поля таблицы — такой список аналогичен использованию знака *, но отличается тем, что при явном указании имен полей мы можем управлять порядком их следования в наборе данных.
Приведенный достаточно простой запрос демонстрирует определенные преимущества компонента Query, использующего возможности языка SQL, по сравнению с компонентом Table. Для компонента Table изменить порядок или ограничить состав полей набора данных можно только на этапе разработки приложения, задав с помощью Редактора полей статические поля. Компонент Query позволяет осуществлять эти операции и для динамических полей, что достигается небольшим изменением SQL-запроса как при разработке, так и при выполнении приложения.
Кроме физических полей таблиц, в набор данных можно включать вычисляемые поля. Для получения вычисляемого поля в списке полей указывается не имя этого поля, а выражение, по которому рассчитывается его значение. Выражение составляется по обычным правилам, при этом в него могут не входить имена полей. Например:
SELECT "- " || Name, Salary, Salary * 1.1 FROM Personnel;
Глава 8. Реляционный доступ к данным с помощью BDE
247
Для сотрудников организации (таблица Personnel) выводятся старое значение оклада и новое, увеличенное на 10%. К каждой фамилии с помощью операции конкатенации (и) добавляются дефис и пробел. Результирующий набор будет таким:
Name Salary Salary * 1.1
Иванов P.O. | ||
Петров А.П. | ||
Семенова И.И. | ||
Кузнецов П.А. | ||
Попов А.Л. | ||
Васин Н.Е. |
Записи могут иметь одинаковые значения некоторых полей. Для того чтобы включить в набор данных только записи с уникальными (неповторяющимися) значениями, перед списком полей указывается описатель distinct. Например:
SELECT DISTINCT Position FROM Personnel
Сформированный набор данных содержит список занимаемых штатных должностей организации. Записи выбираются из таблицы сотрудников организации, при этом в набор данных каждая должность включается только один раз (поле
Position):
Position
Директор
Менеджер
Секретарь
Водитель
А так задается выборка записей с уникальными значениями двух полей:
SELECT DISTINCT Position, Salary FROM Personnel
Сформированный набор данных содержит список занимаемых штатных должностей и окладов. В набор данных попадают записи с уникальной комбинацией значений двух полей (position и salary):
Position | Salary |
Директор | |
Менеджер | |
Секретарь | |
Водитель | |
Водитель |
Еще одним достоинством языка SQL является простота объединения в результирующем наборе данных, содержащихся в нескольких таблицах. Для этого после слова from перечисляются имена таблиц, из записей которых формируется
248
Часть II. Технологии доступа к данным
набор данных. Такое использование данных из различных таблиц называется соединением.
Пример запроса на отбор записей из двух таблиц:
SELECT * FROM Personnel, Info
ИЛИ
SELECT Personnel.*, Info.* FROM Personnel, Info
Результирующий набор данных состоит из всех полей и всех записей таблиц Personnel и info. Первыми располагаются поля первой таблицы, далее следуют поля второй таблицы. Имена полей набора данных являются составными и включают в себя имена таблиц.
При выполнении запроса к нескольким таблицам в набор данных отбираются записи этих таблиц, удовлетворяющие заданным условиям. В данном случае условия отбора не заданы, поэтому в набор данных попадают все записи из обеих таблиц. Обычно таким образом отбираются записи из таблиц, связанных по определенным полям. Если же сформировать набор данных из таблиц, содержащих не связанные между собой данные, например, список сотрудников организации и список товаров на складе, то получившиеся записи могут содержать такие поля, как фамилия сотрудника и цена товара, что вряд ли имеет какой-либо смысл.
Еще один пример на отбор полей из разных таблиц:
SELECT Personnel.Name, Info.I_Code
FROM Personnel, Info В случае если имя таблицы, приводимое в операнде from, указывает формат файла таблицы, то в обозначении поля имя этой таблицы заключается в кавычки, например, "Personnel.db".Name.
Вместо имени таблицы в тексте SQL-запроса можно задать псевдоним, упрощающий указание имени таблицы, например, при обозначении ее полей. После определения псевдонима его можно использовать вместо имени таблицы. Псевдоним задается с помощью описателя as, указываемого после имени таблицы. Например:
SELECT P.Name, I.I_Code
FROM "Personnel.db" As P, "Info.db" As I
Для таблицы Personnel определен псевдоним р, а для таблицы info — псевдоним i, которые используются при обозначении полей этих таблиц. Отметим, что в данном примере использование псевдонимов встречается раньше, чем их определение. Язык SQL не является алгоритмическим, и в нем допускается такой порядок описания и использования псевдонимов.
(_____ ЗамечаниеJ
Псевдоним таблицы применяется в инструкции SQL вместо имени таблицы и никак не связан с псевдонимом БД, который используется для определения значения свойства DatabaseName набора данных Query и задает расположение БД.
Глава 8. Реляционный доступ к данным с помощью BDE
249
Если в составе таблиц, из которых отбираются записи, имя некоторого поля является уникальным, то имя содержащей это поле таблицы можно не указывать. Например:
SELECT Name, I_Code
FROM Personnel, Info
Имена полей Name И I_Code уникальны ДЛЯ таблиц Personnel И Info, поэтому имена таблиц в обозначении полей можно опустить.
Простое условие отбора записей
В предыдущем разделе в набор данных попадали все записи из указанных таблиц, при этом разработчик мог управлять составом полей этих записей. На практике набор данных обычно ограничивается записями, удовлетворяющими каким-либо определенным условиям (критериям) отбора, задаваемым с помощью операнда where. Критерий отбора может варьироваться от простейшего, в котором сравниваются два значения, до сложного, когда учитывается много факторов. При этом в набор данных попадают записи, для которых выполняется условие отбора.
Критерий отбора представляет собой логическое выражение, в котором можно использовать операции, перечисленные ниже.
□ Сравнение; возможные операторы:
• = (равно); • <= (меньше или равно);
• > (больше); • о или!= (не равно);
• < (меньше); •! > (не больше);
• >= (больше или равно); •! < (не меньше).
□ like (сравнение по шаблону).
□ is null (проверка на нулевое значение).
□ in (проверка на вхождение).
□ between (проверка на вхождение в диапазон).
В простом критерии отбора используется одна операция. Для операций сравнения и сравнения по шаблону критерий отбора имеет следующий формат:
<Выражение1> <Оператор> <Выражение2>
Выражение состоит из имен полей, функций, констант, значений, знаков операций и круглых скобок. В простейшем случае — из имени поля или значения. Например:
SELECT Name
FROM Personnel
WHERE Salary >= 4000;
250
Часть II. Технологии доступа к данным
Эта инструкция задает получение следующего списка сотрудников, имеющих оклад не менее 4000 (рублей):
Name
Иванов P.O. Петров А.П. Семенова И. И.
Пример отбора записей по значениям символьного поля:
SELECT Name
FROM Personnel
WHERE Position = 'Водитель';
В результате получим выборку с фамилиями водителей:
Name
Попов А.Л. Васин Н.Е.
Поля, входящие в набор данных, и поля, используемые в критерии отбора, могут отличаться друг от друга. Так, в приведенном примере в наборе данных присутствует поле фамилии Name, в то время как в критерии отбора записей используется поле оклада salary.
В последнем примере в операции сравнения учитывается регистр символов, и слова водитель и водитель не равны друг другу. Поэтому различия в регистре символов или наличие ведущих и конечных пробелов приводят к ошибкам отбора записей. В данной ситуации критерий отбора лучше записать в следующем виде:
WHERE UPPER(TRIM(Post)) = 'ВОДИТЕЛЬ'
Функция trim удаляет из строкового значения ведущие и конечные пробелы, а функция upper приводит символы полученной строки к верхнему регистру. В результате значение должности водитель независимо от наличия ведущих и конечных пробелов, а также регистра букв будет приведено к значению
ВОДИТЕЛЬ.
Для сравнения строк вместо операций =,! = и о можно использовать операцию like, выполняющую сравнение по частичному совпадению. Частичное совпадение значений целесообразно проверять, например, в случаях, когда известны только начальная часть фамилии или названия предмета. Вот образец соответствующего запроса:
SELECT Name
FROM Personnel WHERE Name LIKE "Ав"
Здесь мы получаем список фамилий, начинающихся на Ав.
В выражениях операции like допускается использование шаблона, в котором разрешены все алфавитно-цифровые символы (с учетом регистра).
Глава 8. Реляционный доступ к данным с помощью BDE
251
При этом два символа имеют специальное назначение:
□ % (замещение любого количества символов, в том числе и нулевого);
□ _ (замещение одного символа).
С помощью шаблона можно выполнить проверку на частичное совпадение не только начальных символов строки, но и найти вхождение заданного фрагмента в любую часть строкового значения. Например:
SELECT Name
FROM Goods
WHERE Name LIKE "%" | | "ка" I I "%"
В приведенном запросе происходит отбор всех товаров, в названия которых входят символы ка. Набор данных, полученный при таком отборе, может иметь вид:
Name ручка карандаш замазка
Перед операцией like можно использовать описатель not, который изменяет результат выполнения операции на противоположное значение и проверяет значения выражений на несовпадение.
Для проверки нулевого значения выражения служит операция is null, которая имеет следующий формат:
<Выражение> IS [NOT] NULL
Так, в запросе:
SELECT *
FROM Store
WHERE S_Price IS NULL
выполняется отбор всех полей записей таблицы склада (store), для которых не определена цена (s_Price) товара.
Проверка на вхождение значения выражения в список выполняется с помощью операции in следующего формата:
<Выражение> [NOT] IN <Список значений>
Эту операцию удобно использовать, если выражение может принимать относительно небольшое количество различных значений. Вот пример соответствующего запроса:
SELECT Name, Salary FROM Personnel WHERE LOWER(Post) IN ("менеджер", "водитель")
252
Часть II. Технологии доступа к данным
В результате его выполнения получим выборку фамилий и окладов всех менеджеров и водителей:
Name Salary
Петров А.П. 5200
Семенова И.И. 5200
Попов А.Л. 2400
Васин Н.Е. 2500
Операция between выполняет проверку вхождения значения в диапазон и имеет формат:
<Выражение> [NOT] BETWEEN
<Минимальное значение> AND <Максимальное значение>
При использовании этой операции в набор данных включаются записи, для которых значение выражения больше или равно минимальному, а также меньше или равно максимальному значениям.
СЗамечание^
Описатель not изменяет значение результата операции на противоположное. Рассмотрим запрос:
SELECT *
FROM Cards
WHERE C_Date BETWEEN "21.5.02" AND "27.5.2002"
В результате его выполнения получим набор записей, для которых дата (поле c_Date) находится в диапазоне с 21 по 27 мая 2002 года:
C_Number C_Code C_Move C_Date
3 3 -150 22.05.02
4 3 -30 22.05.02
5 3 270 24.05.02
6 4 200 27.05.02
Значение даты заключается в кавычки, в качестве разделителя используется точка. В некоторых других реализациях языка SQL в качестве разделителей допускается использовать символы - и /. Значение года можно задавать как двумя (21.5.02), так и четырьмя (27.5.2002) цифрами. В значениях дня и месяца можно опускать незначащий ноль.
Сложные критерии отбора записей
При отборе можно использовать несколько операций, задавая тем самым сложные критерии отбора записей.
Глава 8. Реляционный доступ к данным с помощью BDE
253
Сложный критерий (логическое выражение) состоит из:
□ простых условий;
□ логических операций:
• and (логическое и);
• or (логическое или);
• not (логическое не);
□ круглых скобок.
(Замечание^
d языке SQL приоритет операций сравнения выше приоритета логических операций. Для изменения порядка выполнения операций используются круглые скобки.
Пример запроса со сложным критерием отбора:
SELECT Cards.*
FROM Store,Cards
WHERE (S_Quantity > 250) AND
(C_Date BETWEEN "21.5.2002" AND "27.5.2002")
Здесь происходит отбор всех записей о движении товара, количество которого на складе превышает 250 единиц, в течение заданного периода. В данном логическом выражении простые условия заключены в скобки, что не обязательно, т. к. приоритет операций сравнения выше приоритета логических операций. Набор данных, полученный при таком отборе записей, может иметь вид:
С Number | С | Code | С Move | С Date |
25.05.02 | ||||
26.05.02 | ||||
27.05.02 | ||||
27.05.02 |
Отметим, что рассмотренные выше операции between и in также реализуют логические операции: between — логическое и, a in — логическое или.
Группирование записей
Записи набора данных могут быть сгруппированы по некоторому признаку. Группу образуют записи с одинаковыми значениями в полях, перечисленных в списке операнда group by. При группировании записей их проще анализировать и обрабатывать, например, с помощью статистических функций.
Группирование записей автоматически исключает повтор значений в полях, заданных для группирования, т. к. записи с совпадающими значениями этих полей объединяются в одну группу.
254
Часть II. Технологии доступа к данным
Пример запроса с группированием записей:
SELECT C_Date, COUNT(C_Date) FROM Cards