Russian Belarusian English German Japanese Ukrainian

SQL

SQL (от англ. Structured Query Language - «Структурированный язык запросов») - универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных.

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

Результаты, возвращаемые оператором Select, можно использовать в другом операторе Select. Причем это относится и к операторам, возвращающим совокупные характеристики, и к операторам, возвращающим множество значений. Ниже приведен пример с помощью вложенных запросов:
SELECT Fam,Year_b FROM Pers WHERE Year_b=(SELECT max(Year_b) FROM Pers)
В этом операторе второй вложенный оператор SELECT max(Year_b) FROM Pers возвращает максимальный год рождения, который используется в элементе WHERE основного оператора Select для поиска сотрудника (или сотрудников), чей год рождения совпадает с максимальным.
 
Вложенные запросы могут обращаться к разным таблицам. Пусть, например, имеем две аналогичных по структуре таблицы Pers и Pers1, относящиеся к разным организациям, и хотим в таблице Pers найти всех однофамильцев сотрудников другой организации. Чтобы проверить работу с несколькими таблицами, можно открыть в Database Desktop свою таблицу Pers, выполнить команду Table | Restructure, ничего не меняя в структуре, щелкнуть на кнопке Save as и сохранить ту же таблицу в том же каталоге, где была исходная таблица Pers, но под новым именем Pers1. Если хотите, можно в ней внести записи, отличные от таблицы Pers, чтобы эти таблицы чем-то различались. Впрочем, можно все это и не делать, заменив в приведенных ниже примерах таблицу Pers1 таблицей Pers, т.е. работая с одной таблицей. Смысл операторов все равно будет понятен.
 
Итак, вернемся к задаче определения всех однофамильцев в этих двух таблицах. Это можно сделать оператором:
SELECT * FROM Pers WHERE Fam IN(SELECT Fam FROM Pers1)
Вложенный оператор Select Fam from Pers1 возвращает множество фамилий из таблицы Pers1, а конструкция WHERE основного оператора Select отбирает из фамилий в таблице Pers те, которые имеются в множестве фамилий из Pers1.
 
При работе в условии WHERE с множествами записей можно использовать ключевые слова: All и Any. All означает, что условие выполняется для всех записей, a Any хотя бы для одной записи. Например, оператор:
SELECT * FROM Pers WHERE Year_b >= ALL (SELECT Year_b FROM Pers1)
ищет сотрудников в Pers, которые не старше любого сотрудника в Pers1. Кстати, если в этом операторе заменить Pers1 на Pers, то получим список самых молодых сотрудников организации, который мы получали ранее другим способом. А оператор:
SELECT * FROM Pers WHERE Year_b > ANY (SELECT Year_b FROM Pers1)
ищет сотрудников в Pers, которые моложе хотя бы одного сотрудника в Pers1.

Вставка записей

Вставка записей осуществляется с помощью оператора INSERT, который позволяет добавлять к таблицам одну или несколько записей. При добавлении одной записи оператор INSERT имеет формат:
INSERT INTO <Имя таблицы> [(<Список полей>)] VALUES (<Список значений>);
В результате выполнения этого оператора к таблице, имя которой указано после слова INTO, добавляется одна запись. Для добавленной записи заполняются поля, перечисленные в списке. Значения полей берутся из списка, расположенного после слова VALUES. Список полей и список значений должны соответствовать друг другу по числу элементов и по типу. При присваивании значений для первого поля берется первое значение, для второго второе и т.д. При этом порядок полей и значений может отличаться от порядка полей в таблице. Пример запроса на добавление записи:
INSERT INTO store (S_Name, S_Price, S_Quantity) VALUES ("Торшер", 499.9, 10);
Здесь в таблицу склада Store добавляется новая запись, в которой присваиваются значения полям названия товара, его цены и количества.
 
Список полей в инструкции INSERT может отсутствовать, в этом случае необходимо указать значения для всех полей таблицы. Порядок и тип этих значений должны соответствовать порядку и типу полей таблицы. При добавлении к таблице сразу нескольких записей оператор INSERT имеет формат:
INSERT INTO <ИМЯ таблицы> (<Список полей>) Оператор SELECT;
В данном случае значения полей новых записей определяются через значения полей записей, отобранных с помощью оператора SELECT. Число добавленных записей равно числу отобранных записей. Список значений полей, возвращаемых инструкцией SELECT, должен соответствовать списку оператора INSERT по числу и типу полей.
 
С помощью вставки группы записей можно скопировать данные из одной таблицы в другую, например, при резервном копировании или архивировании записей. При этом обе таблицы обычно имеют одинаковую структуру или их структуры частично совпадают. Ниже запрос на добавление нескольких записей:
INSERT INTO CardsArchives (Code, Move, Date) SELECT C_Code, C_Move, C_Date FROM Cards WHERE C_Date BETWEEN 1.1.02 AND 31.12.02
В архивную таблицу CardsArchives добавляется группа записей из таблицы Cards движения товара. Для записей, сделанных в 2005 г., в архив копируются код товара, приход или расход и дата. Если необходимо выполнить не копирование, а перемещение записей в архив, то после успешного копирования можно удалить записи в исходной таблице с помощью оператора DELETE.
Перемещение записей целесообразно оформлять в рамках транзакции, чтобы обеспечить надежность выполнения операции и сохранение целостности БД.

Группирование записей

Записи набора данных могут быть сгруппированы по некоторому признаку. Группу образуют записи с одинаковыми значениями в полях, перечисленных в списке операнда GROUP BY. При группировании записей их проще анализировать и обрабатывать, например, с помощью статистических функций. Группирование записей автоматически исключает повтор значений в полях, заданных для группирования, т.к. записи с совпадающими значениями этих полей объединяются в одну группу. Пример запроса с группированием записей:
SELECT Post, COUNT(Post) FROM Employee WHERE Birthday BETWEEN "1.7.1950" AND "31.7.1967" GROUP BY Post
Для каждой даты из указанного периода выводится количество записей, в которых она встречается. Если не выполнить группирование, то в набор данных попадут все записи, а при использовании группирования все даты для полученного набора данных уникальны. Функция COUNT выводит для каждой группы (сформированной по полю даты) число записей в группе. Полученный набор данных будет иметь следующий вид:
Post COUNT (Post)
бухгалтер 1
вед. нс 1
зав. лаб. 1
мл. нс 2
нс 1
Совместно с операндом GROUP BY можно использовать операнд HAVING, с помощью которого задаются дополнительные условия группирования записей.
Пример запроса:
SELECT Post, COUNT(Post) FROM Employee WHERE Birthday BETWEEN "1.7.1950" AND "31.7.1967" GROUP BY Post HAVING COUNT(Post) > 1
Полученный набор данных будет иметь следующий вид:
Post COUNT (Post)
мл. нс 2
 
Здесь отбираются данные для должностей, занимаемых сотрудниками с датами рождения, находящимися в заданном диапазоне, причем на этих должностях находится более одного сотрудника.

Доступ к данным с помощью запросов

Доступ к данным с помощью запросов (реляционный способ) основан на операциях с группами записей. Для задания операций используются средства языка структурированных запросов SQL (Structured Query Language), поэтому реляционный способ доступа называют также SQL-ориентированным. Для его реализации в приложениях С++ Builder при использовании механизма BDE в качестве набора данных должны применяться такие компоненты, как Query или StoredProc, позволяющие выполнить SQL-запрос. При использовании других механизмов доступа к данным также можно использовать реляционный способ доступа. Так, для механизма ADO должны применяться компоненты ADOQuery и ADOStoredProc.
 
Средства SQL применимы для выполнения операций с локальными и удаленными БД. Наиболее полно преимущества реляционного способа доступа и языка SQL проявляются при работе с удаленными БД. Основным достоинством реляционного способа доступа является небольшая загрузка сети, поскольку передаются только запросы и результат их выполнения.
 
Применение реляционного способа доступа для локальных БД не дает существенного преимущества, но и в этом случае с помощью SQL-запроса можно:
  • формировать состав полей набора данных при выполнении приложения;
  • включать в набор данных поля и записи из нескольких таблиц;
  • отбирать записи по сложным критериям;
  • сортировать набор данных по любому полю, в том числе неиндексированному;
  • осуществлять поиск данных по частичному совпадению со значениями в поле.
Многие из названных действий неприменимы к набору данных Table.
Для компонента Query реляционный способ доступа реализуется в случае, когда используются только средства SQL-запросов. Если дополнительно применять методы, ориентированные на операции с отдельными записями, например, Next или Edit, то будет реализован навигационный способ доступа со всеми его недостатками.
При работе с удаленными БД можно также использовать навигационный способ доступа, но только для небольших сетей, чтобы не создавать большой загрузки.

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

Для настройки статического SQL-запроса во время выполнения приложения в его тексте можно использовать параметры. Параметр - это специальная переменная, перед именем которой ставится двоеточие в тексте запроса. Двоеточие не является частью имени параметра и ставится только в тексте запроса. Как и для обычных переменных программы, в процессе выполнения приложения вместо параметра подставляется его значение. Параметры удобно использовать для передачи в текст SQL-запроса внешних значений. Например, если необходимо вывести фамилии и должности сотрудников с датой рождения более поздней, чем указанная в редакторе Edit1, то организовать формирование и выполнение динамического запроса можно так:
void __fastcall TForm1::Button3Click(TObject *Sender)
{
Query1->Close();
Query1->SQL->Clear();
Query1->SQL->Add("SELECT Name, Post FROM SEmployee.db");
Query1->SQL->Add("WHERE Birthday > " + Edit1->Text);
Query1->Open();
}
Здесь дата рождения в редакторе Edit1 должна быть указана в одиночных кавычках.
 
С помощью параметров эту задачу можно решить проще, например, через включение в текст запроса параметра prmSalary:
SELECT Name, Post, Birthday FROM SEmployee.db WHERE Birthday >= : Birthday
Система С++ Builder автоматически учитывает все указанные в SQL-запросе параметры в специальном списке параметров, являющемся для набора данных Query значением свойства Params типа TParams, представляющим собой массив. Это свойство позволяет получить доступ к каждому параметру как при разработке, так и при выполнении приложения. Чтобы обратиться к параметру во время выполнения приложения, следует указать его номер (индекс) в списке параметров или обратиться по имени параметра. Например, для обращения ко второму параметру указывается params[1]. На этапе разработки приложения можно вызвать Редактор параметров в Инспекторе объектов. Тип каждого параметра целесообразно указать в свойстве DataType типа TFieldType.
 
В последующем перед выполнением запроса вместо параметра необходимо подставить его значение, в данном случае из редактора Edit1. Далее приведен код обработчика события нажатия кнопки Button1, выполняющий указанное действие.
void __fastcall TForm1::Button2Click(TObject *Sender)
{
Query1->Close();
// доступ к параметру по индексу Query1->Params->Iteiris[0]->AsDate = Edit1->Text;
Query1->ParamByName("Birthday")->AsDate = (Edit1->Text);
Query1->Open();
}
Для доступа к параметру во время выполнения приложения используется метод ParamByName, отличающийся от аналогичного метода FieldByName тем, что вместо имени поля указывается имя параметра.
 
При использовании параметров можно не изменять текст SQL-запроса во время выполнения приложения и, тем не менее, передавать в него различные значения. То есть статический запрос как бы превращается в динамический.
Статические запросы, в которых использованы параметры, иногда также называют изменяющимися (т. е. фактически динамическими).
Обычно текст SQL-запроса проверяется и выполняется при каждом открытии набора данных Query. Если текст запроса при выполнении приложения не изменяется, то его можно предварительно подготовить, а после этого только использовать такой подготовленный к выполнению запрос. Это позволяет ускорить обработку статических запросов, в том числе имеющих параметры. Например:
if(!Query1->Prepared)
{
Query1->Close{);
Query1->Prepare();
Query1->Open();
}
Если текст подготовленного к выполнению запроса изменился (к изменению значений параметров это не относится), то автоматически вызывается метод UnPrepare, и свойству Prepared устанавливается значение false.

Изменение состава полей таблицы

Изменение состава полей таблицы заключается в добавлении или удалении полей и приводит к изменению ее структуры, при этом таблицу не должны использовать другие приложения. Изменение состава полей таблицы выполняется инструкцией ALTER TABLE:
ALTER TABLE <Имя таблицы>
ADD <Имя поля1> <Тип данных1>,
DROP <Имя поля1>,
...
ADD <Имя поляN> <Тип данныхN>,
DROP <Имя поляN>;
Операнд ADD добавляет к таблице новое поле, имя и тип которого задаются так же, как и в операторе CREATE TABLE, а операнд DROP удаляет из таблицы поле с заданным именем. Операнды ADD и DROP не зависят друг от друга и могут следовать в произвольном порядке.
 
При попытке удалить отсутствующее поле или добавить поле с существующим именем генерируется исключение.
 
Пример изменения структуры таблицы:
ALTER TABLE Employee.db
ADD Section SMALLINT,
ADD Note CHAR(30),
DROP Post;
К таблице Employee добавляются целочисленное поле номера отдела Section и символьное поле примечаний Note, поле post удаляется.

Объединение таблиц

В запросе можно объединить данные двух или более таблиц. Пусть, например, нужно получить список сотрудников всех производственных подразделений. В таблице Pers есть список сотрудников с указанием в поле Dep подразделений, в которых они работают. А в таблице Dep есть список всех подразделений в поле Dep и характеристику каждого подразделения в поле Prosv (true, если подразделение производственное). Тогда получить список сотрудников всех производственных подразделений можно оператором:
SELECT Pers.* FROM Pers, Dep WHERE (Pers.Dep=Dep.Dep) AND (Dep.Proisv=true)
В нем идет обращение сразу к двум таблицам Pers и Dep, которые перечислены после ключевого слова FROM. Поэтому каждое имя поля предваряется ссылкой на таблицу, к которой оно относится. Впрочем, это надо делать только для полей, имя которых повторяется в разных таблицах (поле Dep). Перед полем Proisv ссылку на таблицу можно опустить. В конструкции WHERE условие Pers.Dep=Dep.Dep ищет запись в таблице Dep, в которой поле Dep совпадает с полем Dep текущей записи таблицы Pers. А условие Dep.Proisv=true отбирает те записи, в которых в таблице Dep найденному подразделению соответствует поле Proisv = true.
 
В операторах, работающих с несколькими таблицами, обычно каждой таблице дается псевдоним, сокращающий ссылки на таблицы, а иногда придающий им некоторый смысл, вытекающий из данного применения. Псевдоним таблицы может записываться в списке таблиц после слова FROM, отделяясь от имени таблицы пробелом. Например, приведенный выше оператор может быть переписан следующим образом:
SELECT P.* FROM Pers Р, Dep D WHERE (P.Dep=D.Dep) AND (D.Proisv=true)
В этом примере таблице Pers дан псевдоним Р, а таблице Dep D. Конечно, эти псевдонимы действуют только в данном операторе и не имеют никакого отношения к псевдонимам баз данных.
 
Возможно самообъединение таблицы. В этом случае одной таблице даются два псевдонима. Пусть, например, нужно найти всех ровесников в организации. Это можно сделать оператором:
SELECT p1.fam, р2.fam, p1.year_b FROM Pers p1, Pers p2 WHERE (p1.year_b = p2.year_b) AND (p1.fam != p2.fam)
В этом примере для таблицы Pers указаны два псевдонима: p1 и р2. В конструкции WHERE идет поиск в этих якобы разных таблицах записи с одинаковым годом рождения. Второе условие p1.fam != p2.fam нужно, чтобы сотрудник не отображался в результатах как ровесник сам себя. Правда, приведенный оператор выдает в результате по две записи на каждую пару ровесников, сначала, например, «Николаев - Андреев», а потом «Андреев - Николаев». Чтобы исключить такое дублирование можно добавить еще одно условие p1.Fam < p2.Fam:
SELECT p1.fam, p2.fam, p1.year_b FROM Pers p1, Pers p2 WHERE (p1.year_b = p2.year_b) AND (p1.fam != p2.fam) and (p1.Fam < p2.Fam)
Дополнительное условие упорядочивает появление фамилий в p1 и p2 и исключает дублирование результатов.
 
Выше рассматривались примеры объединения, основанные на однозначном соответствии записей двух таблиц, когда каждой записи в первой таблице находилась соответствующая ей запись во второй таблице. Возможны и другие виды объединений, которые выдают записи независимо от того, есть ли соответствующее поле во второй таблице. Это внешние объединения (outer join). Их три типа: левое, правое и полное. Левое объединение (обозначается ключевыми словами LEFT OUTER JOIN ... ON) включает в результат все записи первой таблицы, даже те, для которых не имеется соответствия во второй. Правое объединение (обозначается ключевыми словами RIGHT OUTER JOIN ... ON) включает в результат все записи второй таблицы, даже если им нет соответствия в записях первой. Полное объединение (обозначается ключевыми словами FULL OUTER JOIN ... ON) включает в результат объединение записей обеих таблиц, независимо от их соответствия.
 
Пусть, например, есть таблица сотрудников некоей компании Pers и есть таблица Chef, в которой занесены данные на членов совета директоров этой компании. В число членов совета входят и сотрудники компании, и посторонние лица. Для определенности положим, что в таблице Pers имеются записи на сотрудников «Иванов» и «Петров», причем Петров является членом совета, а Иванов нет. В таблице Chef имеются записи на членов совета «Петров» и «Сидоров», причем Сидоров не сотрудник компании. Тогда оператор:
SELECT * FROM Pers LEFT OUTER JOIN Chef ON Pers.Fam = Chef.Fam
Оператор задал левое объединение таблицы Pers (она указана после ключевого слова FROM) с таблицей Chef (она указана после ключевых слов LEFT OUTER JOIN). Условие объединения указано после ключевого слова ON и заключается в совпадении фамилий. Как показано, результат включает все поля и таблицы Pers, и таблицы Chef. Число строк соответствует числу записей таблицы Pers. В строках, относящихся к записям, для которых в Chef не нашлось соответствие, поля таблицы Chef остаются пустые.
 
Оператор правого объединения:
SELECT * FROM Pers RIGHT OUTER JOIN Chef ON Pers.Fam = Chef.Fam
Число строк соответствует числу записей таблицы Chef. В строках, относящихся к записям, для которых в Pers не нашлось соответствие, поля таблицы Pers остаются пустые.
 
Оператор полного объединения:
SELECT * FROM Pers FULL OUTER JOIN Chef ON Pers.Fam = Chef.Fam выдаст результат вида:
В нем к строкам, относящимся к таблице Pers, добавлены строки, относящиеся к таблице Chef, для которых не нашлось соответствия в таблице Pers.

Операции с записями

Вставка новой записи в таблицу осуществляется оператором Insert, который может иметь вид:
INSERT INTO <имя таблицы> (<список полей>) VALUES (<список значений>)
В списке перечисляются только те поля, значения которых известны. Остальные могут опускаться. Для пропущенных полей значения берутся по умолчанию (если значения по умолчанию заданы) или поля остаются пустыми. Например:
INSERT INTO Pers (Fam, Nam, Par, Sex) VALUES ('Иванов', 'Андрей', 'Андреевич', true)
В этом примере не указан год рождения. Он подставится по умолчанию и в дальнейшем может быть уточнен.
 
Другая форма оператора Insert использует множество значений, возвращаемых оператором Select. Этот оператор может выбирать записи из какой-то другой таблицы и вставлять их в данную. Синтаксис этой формы Insert:
INSERT INTO <имя таблицы> <оператор Select>
Пусть, например, нужно создать таблицу Old_Pers пожилых людей организации и необходимо заполнить ее соответствующими записями из таблицы Pers. Это можно сделать одним оператором:
INSERT INTO Old_Pers SELECT * FROM Pers WHERE Year_b < 1939
Таблица Old_Pers сразу заполнится множеством соответствующих записей из Pers.
 
Приведенную форму оператора Insert можно использовать для копирования всех данных одной таблицы в другую, причем эти таблицы могут быть созданы разными СУБД.
 
Редактирование записей осуществляется оператором Update:
UPDATE <имя таблицы> SET <список вида <поле>=<выражение>> WHERE <условие>
Наличие в этом операторе условия позволяет редактировать не только одну запись, но сразу множество их. Например, если при очередной реорганизации предприятия решили слить «Цех 1» и «Цех 2» в один «Цех 1», то исправление всех записей в таблице можно сделать одним оператором:
UPDATE Pers SET Dep = 'Цех 1' WHERE Dep = 'Цех 2'
Удаление записей осуществляется оператором Delete:
DELETE FROM <имя таблицы> WHERE <условие>
Наличие в операторе условия позволяет удалять не только одну, но сразу множество записей. Например, если при реорганизации предприятия подразделение «Цех 1» ликвидировали и всех его сотрудников уволили из штата данной организации, то удалить из таблицы все соответствующие записи можно оператором:
DELETE FROM Pers WHERE Dep = 'Цех 1'

Операции с индексами

Индексы существенно ускоряют процесс поиска и упорядочивания записей таблицы. Если в операторе Select содержится элемент упорядочивания ORDER BY и перечисляемые поля совпадают с определенными в индексе, упорядочивание будет использовать этот индекс и произойдет с малыми затратами времени. В противном случае индекс использоваться не будет и упорядочивание потребует большего времени.
 
Создание нового индекса осуществляется оператором Create Index:
CREATE INDEX <имя индекса> ON <имя таблицы> <список полей>
Например:
CREATE INDEX depyear ON Pers Dep, Year_b
Удаление существующего индекса осуществляется оператором Drop Index:
DROP INDEX <имя таблицы >.<имя индекса>
Например:
DROP Index Pers.depyear
Если таблица многократно изменяется и в нее вносится много новых записей, индексы могут оказаться разбалансированы и их эффективность при выполнении запросов уменьшается. В этом случае полезно проводить повторное создание и балансировку индекса последовательным применением операторов деактивации и активации:
ALTER INDEX <имя индекса> DEACTIVATE
ALTER INDEX <имя индекса> ACTIVATE

Операции с полями

Через объект типа TFieid разработчик может:
  • обратиться к полю и его значению;
  • проверить тип и значение поля;
  • отформатировать значение поля, отображаемое или редактируемое в визуальных компонентах.
При этом динамические и статические поля имеют одинаковые свойства, события и методы, с помощью которых можно управлять этими объектами при выполнении приложения. В связи с тем, что статические поля определяются на этапе разработки, многие их свойства доступны с помощью Инспектора объектов.