Работа с базой данных из графического интерфейса
Введение
SQLite (http://www.sqlite.org) – встраиваемая библиотека баз данных, реализующая большую часть стандарта SQL92.
В отличие от других известных СУБД, таких как MySQL, PostgreSQL и других, SQLite не запускается отдельным процессом, с которым происходит взаимодействие, а встраивается в приложение в виде библиотеки.
База данных SQLite представляет собой одиночный файл в специальном формате.
SQLite имеет интерфейсные библиотеки к большинству современных языков программирования, включая Python, C, C++, PHP, Ruby, Delphi, Java, Haskell, Smalltalk и другие.
Графический интерфейс пользователя для доступа к базам данных SQLite для операционной системы Windows предоставляет утилита SQLiteSpy, скачать которую можно здесь:
http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index
Программа не требует инсталляции. В комплекте с исполняемым файлом поставляется демонстрационная база данных World.db3, на которой можно поупражняться.
Как уже отмечалось ранее, SQLite – встраиваемый движок, поэтому для работы с SQLiteSpy скачивать саму библиотеку SQLite или утилиту командной строки необязательно, поскольку движок SQLite уже статически скомпонован с исполняемым файлом SQLiteSpy.
Работа с базой данных из графического интерфейса
После запуска программы мы видим главное окно.
Рисунок 1 - Главное окно программы SQLiteSpy
Откроем базу данных World.db3 (File -> Open Database…)
В левой части главного окна отобразится структура открытой базы данных.
Рисунок 2 – Структура базы данных
Мы видим, что в базе данных main 3 таблицы («Город», «Страна», и «Язык Страны») и 3 представления (вирт таблица)(«Столица Страны», «Языки Страны» и «Официальные Языки Страны»). Сопоставления (Collations) рассмотрены в дальнейшем.
Рассмотрим возможности SQLiteSpy по работе с таблицами и представлениями.
Для того, чтобы просмотреть все содержимое таблицы, необходимо выполнить двойной щелчок по ее имени или выбрать из контекстного меню пункт Show Data. При этом в правой части окна отобразится содержимое выбранной таблицы. Таблицы можно сортировать по любой колонке по возрастанию или убыванию, щелкая по заголовкам колонок.
Рисунок 3 – Просмотр содержимого таблицы
В строке состояния отображается время, потраченное на выполнения запроса в миллисекундах, а также количество записей в результате его выполнения.
При выделении отдельных ячеек в таблице в нижней правой части окна можно просмотреть их содержимое, в случае, если оно не вмещается в табличное представление.
Мы можем вручную изменять содержимое ячеек таблицы. Для этого нужно щелкнуть второй раз по уже выделенной ячейке, либо воспользоваться пунктом меню Edit -> Edit Cell, либо клавишей F2. В появившееся окно вводится новое содержимое ячейки.
Рисунок 4 – Окно редактирования содержимого ячейки таблицы
Представления являются перегруппированными наборами данных, содержащихся в таблицах.
Их просмотр выполняется так же, как и просмотр таблиц, но в них отключена возможность редактирования ячеек и сортировки.
Для каждой таблицы и представления можно увидеть SQL-скрипты, использовавшиеся для его создания (пункт контекстного меню Show CREATE Statement) и вывода его содержимого на экран в табличной форме (пункт контекстного меню Show SELECT Statement). Скрипты можно просматривать и редактировать в верхней правой части окна.
Так, для таблицы City имеем:
Рисунок 5 – Скрипт на выборку данных из таблицы
Рисунок 6 – Скрипт на создание таблицы
Таблицы можно удалять из базы данных (пункт контекстного меню DROP TABLE имя_таблицы).
КАВЫЧКИ!!!
При щелчке на плюсик рядом в именем таблицы или представления нам открываются дополнительные возможности SQLiteSpy.
Так, мы можем настраивать вывод таблиц и представлений не полностью, а только отдельных их колонок (полей). Для этого нужно нажать на плюсик рядом с надписью Columns и поставить галочки напротив тех колонок, которые мы хотим видеть в результате запроса, а потом 2 раза щелкнуть по надписи Columns
Рисунок 7 – Вывод произвольных колонок
При этом поменяется и соответствующий SQL-скрипт:
Рисунок 8 – Модифицированный скрипт на выборку данных из таблицы
Рассмотрим список полей подробнее. Он представляет собой таблицу, в которой есть следующие сведения о колонке (слева направо):
· Тип значений, хранящихся в поле
· Является ли поле обязательным для заполнения (есть синяя галочка) или же допускает NULL-значения
· Является ли поле ключом или частью ключа (есть красная галочка)
· Значение в поле по умолчанию (если пользователь не заносит в него значения явно)
Рисунок 9 – Список полей таблицы
Создание базы данных
Чтобы создать базу данных в SQLiteSpy, необходимо выбрать пункт меню File -> New Database… После указания имени файла, в котором будет располагаться база данных, открывается окно с пустой структурой БД.
Пусть наша база будет содержать сведения об исполнителях и их альбомах. В ней будут 2 таблицы: «Исполнители» и «Альбомы». Создадим их. Для этого нам нужно написать для них генерирующий скрипт в окне редактирования (верхней правой части главного окна).
В языке SQL таблицы создаются с помощью оператора create table, после которого идет имя новой таблицы, а далее в скобках через запятую указывается список колонок, которые мы хотим видеть в таблице, с указанием типов данных, которые будут в них храниться. Помимо этого для каждой колонки можно задать следующие ограничения:
NOT NULL – колонка является обязательной (не может содержать пустых ячеек);
PRIMARY KEY – колонка будет первичным ключом (при этом автоматически накладывается ограничение NOT NULL). В таблице не может быть двух строк с одинаковым значением первичного ключа, таким образом, зная первичный ключ, можно однозначно найти строку в таблице;
UNIQUE – колонка не может содержать повторяющихся значений (является дополнительным ключом);
FOREIGN KEY…REFERENCES – колонка может содержать только те значения, которые находятся в некоторой колонке другой таблицы (внешний ключ);
DEFAULT – значение по умолчанию.
create table Исполнители (
IDИсполнителя INTEGER PRIMARY KEY,
ИмяИсполнителя VARCHAR(30));
Так мы создаем таблицу с именем «Исполнители», в которой будет 2 колонки: «IDИсполнителя» типа INTEGER (целое число) и «ИмяИсполнителя» типа VARCHAR(30) (текстовое значение). Первая колонка будет первичным ключом (PRIMARY KEY).
Иногда может потребоваться, чтобы первичным ключом была не одна колонка, а несколько (т.н. составной ключ). Для этого необходимо использовать такой синтаксис:
CREATE TABLE T1 (
A INT,
B INT,
C INT,
PRIMARY KEY (A, B));
Здесь мы создаем таблицу T1 с тремя колонками (A, B, C), две из которых (A, B) являются частью первичного ключа.
Все скрипты нечувствительны к регистру, т.е. строчные и прописные буквы неразличимы.
Выполнить скрипт можно нажав клавишу F9 или выбрав пункт меню Execute -> Execute SQL. Если в скрипте есть ошибка, то SQLiteSpy выведет окно с ее описанием. При успешном выполнении скрипта ничего выведено не будет, а в структуре БД появится новая таблица:
Рисунок 11 – Результат выполнения скрипта
Красный минусик у иконки рядом с названием таблицы означает, что таблица пуста, т.е. там еще нет реальных данных.
Сохраним скрипт (File -> Save SQL).
Теперь займемся альбомами. Допишем в наш скрипт следующие строки:
create table Альбомы ( IDАльбома INTEGER PRIMARY KEY, FOREIGN KEY IDИсполнителя INTEGER NOT NULL REFERENCES Исполнители(IDИсполнителя), Название VARCHAR(30) NOT NULL, Год INT);Здесь мы создаем таблицу, в которой будут содержаться альбомы тех (и только тех) исполнителей, которые занесены в таблицу «Исполнители». Для этого мы указываем, что поле «IDИсполнителя» является ссылкой (внешним ключом), ссылающейся на поле IDИсполнителя таблицы «Исполнители».
При попытке выполнить скрипт, мы увидим сообщение об ошибке, говорящее, что таблица уже существует. Так SQLite реагирует на попытку создания таблицы, чье имя совпадает с уже существующей. В нашем случае это таблица «Исполнители», которая создается в этом же скрипте.
Чтобы выполнить только ту часть скрипта, которая создает таблицу «Альбомы», минуя создание таблицы «Исполнители», выделим вторую половину скрипта и нажмем Ctrl-F9. Для избежания дальнейших неудобств подобного рода, в случае, если придется создавать таблицы заново, добавим в начало скрипта следующие строки:
drop table if exists Исполнители;drop table if exists Альбомы;Они будут удалять таблицы (если они уже существуют) перед их созданием.
В SQLite можно переименовывать таблицы и добавлять к уже созданным таблицам новые колонки. И то, и другое делается с помощью команды ALTER TABLE.
Рассмотрим примеры для созданной ранее таблицы T1. Переименуем ее и добавим обязательную для заполнения колонку D:
alter table T1 rename to TABLE1;alter table TABLE1 add D INT NOT NULL;Надо наполнить таблицы «Исполнители» и «Альбомы» данными. Это делается построчно.
Для добавления данных в существующую таблицу используется оператор INSERT INTO. После него указывается ключевое слово values, а затем, опять в скобках, - список значений, которые подлежат добавлению. Порядок значений должен строго соответствовать порядку колонок в таблице.
Допишем в конец скрипта:
insert into Исполнители values (NULL,'Peter Gabriel');
Мы добавили в таблицу «Исполнители» в колонки «IDИсполнителя» и «ИмяИсполнителя» значения соответственно NULL и 'Peter Gabriel'. При этом, поскольку колонка «IDИсполнителя» указана как целочисленный первичный ключ (который не может содержать значения NULL), в это поле автоматически запишется значение, равное инкрементированному (увеличенному на 1) максимальному значению IDИсполнителя в таблице «Исполнители». Поскольку данных в таблице пока нет, в колонку, скорее всего, будет записано значение 1.
В принципе, указывать в запросе на добавление значение NULL каждый раз, когда мы хотим, чтобы произошел автоинкремент, необязательно. Достаточно просто ничего не писать в качестве значения, добавляемого в это поле. Но для этого надо явно указать, в какое именно поле мы хотим записать строковое имя исполнителя. Это делается так:
insert into Исполнители (ИмяИсполнителя)
values ('Bruce Hornsby');
Здесь мы указали имя таблицы, затем в скобках – список колонок таблицы, куда будут вставлены значения (в данном случае это одна колонка), затем – добавляемые туда значения ('Bruce Hornsby'). В поле «IDИсполнителя» окажется значение 2.
Продолжим заполнение таблиц.
insert into Исполнители (ИмяИсполнителя)
values ('Lyle Lovett');
insert into Исполнители (ИмяИсполнителя)
values ('Beach Boys');
Заполним теперь таблицу «Альбомы». В ней будет автоматически присваиваться значение полю «IDАльбома», а все остальные значения мы указываем явно.
insert into Альбомы (IDИсполнителя,Название,Дата)
values (1,'So',1984);
insert into Альбомы (IDИсполнителя,Название,Дата)
values (1,'Us',1992);
insert into Альбомы (IDИсполнителя,Название,Дата)
values (2,'The Way It Is',1986);
insert into Альбомы (IDИсполнителя,Название,Дата)
values (2,'Scenes from the Southside',1990);
insert into Альбомы (IDИсполнителя,Название,Дата)
values (1,'Security',1990);
insert into Альбомы (IDИсполнителя,Название,Дата)
values (3,'Joshua Judges Ruth',1992);
insert into Альбомы (IDИсполнителя,Название,Дата)
values (4,'Pet Sounds',1966);
Как видно, текстовые значения (VARCHAR(30)) оформляются в одинарные кавычки. Значения полей IDИсполнителя и IDАльбома автоматически инкрементируются.
Необходимо отметить, что SQLite не поддерживает ограничения по внешнему ключу FOREIGN KEY. То есть если мы внесем в таблицу Альбомы значение IDИсполнителя, которого нет в связанном поле IDИсполнителя таблицы Исполнители, то это будет произведено без ошибок и предупреждений. Исправить положение, включив поддержку ограничений по внешнему ключу, позволяет использование триггеров. Подробнее этот метод описан здесь:
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
Отличительной особенностью SQLite также является чрезвычайно слабая типизация. Фактически, любое значение можно хранить в поле любого типа. Проблема соответствия типов поля и значение лежит целиком на совести программиста.
Теперь «нечаянно» внесем в таблицу некорректную запись.
insert into Исполнители (ИмяИсполнителя) values ('Supernatural');Мы перепутали имя исполнителя с его альбомом. Теперь необходимо изменить значение, записанное в таблицу. Для этого используется оператор UPDATE. При его использовании после ключевого слова UPDATE указывается имя таблицы, подлежащей изменению, затем – ключевое слово SET, после которого через запятую названия изменяемых колонок и после знака «=» значения, которые в них записываются. Если нам надо изменить только некоторые (например, какую-то конкретную строчку), то мы указываем условие отбора строк после слова WHERE. В противном случае изменяются все строчки в таблице.
Исправим ошибку.
UPDATE Исполнители SET ИмяИсполнителя = 'Santana' WHERE ArtistID=5;Теперь можно добавить и соответствующий альбом.
insert into Альбомы (IDАльбома,IDИсполнителя,Название,Дата) values (NULL,5,'Supernatural','1999');Теперь нам надо удалить из таблицы какую-то информацию. Например, из таблицы «Альбомы» - сведения обо всех альбомах, название которых начинается с «Super».
Для удаления строк из таблицы используется оператор DELETE. Если не указано слово WHERE и условие отбора удаляемых строк, то удаляются все строки таблицы. В противном случае удаляются только строки, соответствующие указанному условию.
Выполним:
DELETE FROM Альбомы WHERE Название = 'Supernatural';Теперь попробуем создать представление (view). Для этого служит оператор CREATE VIEW. Представление создается как виртуальная таблица, предоставляющая результаты некоторого запроса. В скрипте, создающем представление, необходимо указать запрос на выборку (оператор SELECT), результаты которого будет возвращать представление. Подробнее сам оператор SELECT, рассмотрены в следующей части.
Пусть представление «НазванияАльбомов» возвращает только список названий альбомов, информация о которых есть в базе данных, без дополнительной информации.
CREATE VIEW НазванияАльбомов AS
SELECT Название AS НазваниеАльбома FROM Альбомы;
Удаление представлений выполняется аналогично удалению таблиц.
DROP VIEW НазванияАльбомов;