Сергей Николаевич Талипов
Базы данных на Delphi 7
2. ИСПОЛЬЗОВАНИЕ SQL-ФУНКЦИЙ ДЛЯ РАБОТЫ С БД
1. Сроковые функции SQL:
UPPER(Str)
LOWER(Str)
TRIM(Str)
SUBSTRING(Str FROM n1 TO n2)
CAST(<Expression> AS <Type>) – приведение выражения Expression к типу Type
|| – конкатенация строк
Например:
SQL.Add('Select cast(ff.nomer as numeric) as nomer, kk.famil,');
SQL.Add('cast((SUBSTRING(kk.ima FROM 1 FOR 1)||');
SQL.Add('SUBSTRING(kk.otch FROM 1 FOR 1)) as character(2)) as io');
2. Создание таблицы:
CREATE TABLE NewTable.db
(Number INTEGER,
Name CHAR(20),
Birthday DATE);
3. Удаление таблицы БД:
DROP TABLE NewTable.db
4. Изменение структуры таблицы:
ALTER TABLE Pers.db
ADD Section SMALLINT, //добавление поля
ADD Note CHAR(30),
DROP Post // удаление поля
5. Создание и удаление индекса:
CREATE INDEX indName ON Personnel.db (Name)
6. Удаление индекса:
DROP INDEX “personnel.db”.indName
7. Задание вычисляемого поля:
SELECT “– ” || Name, Salary, Salary*1.1
FROM Personnel;
Выводятся старые значения окладов сотрудников и новые, увеличенные на 10%. К каждой фамилии с помощью операции конкатенации добавляется символ “-”.
8. Отбор записей с уникальными значениями поля:
SELECT DISTINCT Post FROM Personnel
9. Проверка частичного совпадения значения поля:
SELECT Name
FROM Perssonel
WHERE Name LIKE “Ав”
10. Проверка частичного совпадения с помощью шаблона:
SELECT Name
FROM Goods
WHERE Name LIKE “%” || “п_р” || “%”
% – замещение любого кол-ва символов, в том числе и нулевого
_ – замещение одного символа
11. Проверка нулевых значений поля
SELECT *
FROM Store
WHERE S_Price IS NULL
12. Проверка вхождения записи в список:
SELECT Name, Salary
FROM Perssonel
WHERE Post IN (“Менеджер”,”Ст. менеджер”)
13. Проверка вхождения записей в диапазон:
SELECT *
FROM Cards
WHERE C_Date BETWEEN “13.4.99” AND “15.4.99”
14. Группирование записей. При группировании записей автоматически исключается повтор значений группируемых полей:
SELECT C_Date, COUNT (C_Date)
FROM Cards
GROUP BY C_Date
HAVING COUNT(C_Date)>50
Выводятся данные для тех периодов времени, когда движение товара было интенсивным, т.е. общее число записей в таблицу превышало 50. В группировке могут использоваться следующие функции:
AVG() – среднее значение
MAX()
MIN()
SUM()
COUNT() – кол-во значений
COUNT(*) кол-во ненулевых значений
15. Сортировка по двум полям:
SELECT Name, Post, Salary
FROM Pers.db
ORDER BY Post, Salary DESC
16. При внешнем соединении таблиц можно указать, какая из таблиц будет главной, а какая – подчиненной. В этом случае формат операнда FROM имеет вид:
FROM <Таб-ца1> [<Вид соединения>] JOIN <Таб-ца2> ON <Условие отбора>
Вид соединения, какая из 2 таблиц будет главной:
LEFT – слева
RIGHT – справа
{ Left join – таблицы объединяются, левая таблица – основная }
{ Пример ниже выводит номера людей и тех, у кого нет телефона }
SELECT distinct p.Fam, p.Name, pNumber
FROM “c:\mydb\people.dbf” as p LEFT JOIN “c:\mydb\tel.dbf” as t
ON (p.ID_People=t.ID_People)
{ Right join – таблицы объединяются, правая таблица – основная }
{ Если в предыдущем примере заменить LEFT JOIN на RIGHT JOIN, то программа выведет все номера телефонов из базы «Tel», и соответствующую телефону фамилию, если она имеется }
{ Full join – таблицы объединяются с дополнениями по каждой таблице }
{ Если в предыдущем примере заменить LEFT JOIN на FULL JOIN, то программа выведет все номера телефонов и все фамилии, причем какая-либо фамилия может быть без телефона, и какой-нибудь телефон – без фамилии }
ЛЕКЦИЯ № 9
1. МЕХАНИЗМ ТРАНЗАКЦИЙ ПРИ ОБРАБОТКЕ БАЗ ДАННЫХ
1. Транзакция – это действия на таблицами баз данных, которые должны быть выполнены полностью, от начала до конца. Если окажется, что действия над таблицами БД в транзакции не могут быть полностью и нормально выполнены, то необходимо их (проделанные действия) полностью отменить и вернуть таблицы данных в исходное состояние (до начала действий транзакции).
2. В Delphi работа с транзакциями обычно организуется через компонент типа «TDatabase». Для транзакции существует команда начала транзакции «StartTransaction», подтверждения транзакции «Commit» и отмены (отката) транзакции «Rollback» (в случае ошибки транзакции).
3. Алгоритм работы с транзакциями для таблиц типа Paradox:
Закрываем все таблицы из каталога транзакций, иначе старт транзакции будет невозможен;
Настраиваем режим транзакции на таблицы типа Paradox;
Указываем каталог транзакции компоненту DataBase;
Открываем нужные таблицы для работы;
Стартуем транзакцию;
Делаем нужную работу с БД (например, каскадное удаление);
Подтверждение транзакции;
Отмена транзакции при проблеме (при каком-либо сбое в пунктах 6 или 7);
Обновляем таблицы данных, задействованных в транзакции.
4. Рассмотрим пример работы с транзакциями:
procedure TForm1.Button1Click(Sender: TObject);
var i: word;
begin
try
// Закрываем все таблицы из каталога транзакций
// иначе старт транзакции будет невозможен
try Table1.Close; Table2.Close; except end;
Database1.TransIsolation:=tiDirtyRead; // Настраиваем режим транзакции на таблицы типа Paradox
Database1.DataBaseName:='C:\Student\Db'; // Указываем каталог транзакции
Table1.Open; Table2.Open; // Открываем нужные таблицы для работы
Database1.StartTransaction; // Стартуем транзакцию
// Делаем работу с БД –
Table1.Delete; Table1.Delete; Table2.Delete; Table2.Delete; showmessage('!!!');
abort; // Имитируем проблему
// Конец работы с БД –
Database1.Commit; // Подтверждение транзакции
except
Database1.Rollback; // Отмена транзакции при проблеме
end;
// Обновляем таблицы данных
// Вместо «Table1.Refresh; Table2.Refresh;» лучше так:
for i:=0 to Database1.DataSetCount-1 do Database1.DataSets[i].Refresh;
end;
2. ТРАНЗАКЦИИ В ТЕХНОЛОГИИ ADO
procedure TForm1.Button1Click(Sender: TObject);
var i: integer;
begin
ADOConnection1.BeginTrans; // Начало транзакции
try
ADOTable1.Delete;
ADOTable2.Delete; ADOTable2.Delete;
showmessage('А сейчас будет имитация сбоя и откат транзакции!');
abort; // Генерация сбоя
ADOConnection1.CommitTrans; // Подтверждение транзакции
except
ADOConnection1.RollbackTrans; // Откат транзакции
end;
// ADOTable1.Requery; ADOTable2.Requery; // Обновление данных из таблицы
// Обновление данных из всех таблиц БД (так лучше)
for i:=0 to ADOConnection1.DataSetCount-1 do
ADOConnection1.DataSets[i].Requery;
end;
