Временное хранилище SQLite - Создание и Использование

Введение

В этой лабораторной работе мы изучим временное хранилище SQLite, сосредоточившись на создании и использовании временных таблиц (temporary tables). Временные таблицы предоставляют механизм для хранения промежуточных результатов или данных, необходимых только на короткий период времени в рамках соединения с базой данных.

В этой лабораторной работе вы научитесь создавать временную таблицу с именем temp_employees с помощью оператора CREATE TEMP TABLE. Вы узнаете об области видимости (scope), времени жизни (lifetime) и соглашениях об именах временных таблиц, понимая, чем они отличаются от обычных таблиц. Последующие шаги будут включать заполнение временной таблицы данными, запросы к данным и подтверждение автоматической очистки временной таблицы после закрытия соединения с базой данных.

Создание временной таблицы

На этом шаге вы узнаете, как создать временную таблицу в SQLite. Временные таблицы полезны для хранения промежуточных результатов во время сложных запросов или для хранения данных, которые необходимы только на короткий период времени. Они автоматически удаляются при закрытии соединения с базой данных.

Сначала откройте свой терминал в LabEx VM. Ваш путь по умолчанию - /home/labex/project.

Теперь давайте создадим базу данных SQLite с именем employees.db. Выполните следующую команду, чтобы создать файл базы данных и открыть инструмент командной строки SQLite:

sqlite3 employees.db

Вы увидите приглашение, указывающее на то, что вы находитесь внутри оболочки SQLite:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Чтобы создать временную таблицу, вы используете оператор CREATE TEMP TABLE. Синтаксис аналогичен созданию обычной таблицы:

CREATE TEMP TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Давайте создадим временную таблицу с именем temp_employees для хранения информации о сотрудниках. Введите следующую SQL-команду в командной строке sqlite> и нажмите Enter:

CREATE TEMP TABLE temp_employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    department TEXT
);

Эта команда создает временную таблицу с именем temp_employees с тремя столбцами: id (целочисленный первичный ключ, который автоматически увеличивается), name (текст) и department (текст).

CREATE TEMP TABLE temp_employees: Этот SQL-оператор создает временную таблицу с именем temp_employees. Ключевое слово TEMP указывает, что эта таблица является временной и будет автоматически удалена при закрытии соединения с базой данных.

id INTEGER PRIMARY KEY AUTOINCREMENT: Это определяет столбец с именем id как целое число, устанавливает его в качестве первичного ключа для таблицы и настраивает его на автоматическое увеличение с каждой новой вставленной строкой.

name TEXT: Это определяет столбец с именем name как текстовое поле для хранения имен сотрудников.

department TEXT: Это определяет столбец с именем department как текстовое поле для хранения отдела, к которому принадлежит каждый сотрудник.

Вставка данных во временную таблицу

Теперь, когда вы создали таблицу temp_employees, давайте добавим в нее некоторые данные. Мы вставим несколько записей о сотрудниках в таблицу.

Вы можете вставлять данные во временную таблицу с помощью оператора INSERT INTO, как и в обычную таблицу.

Вставьте следующие записи о сотрудниках в таблицу temp_employees, выполняя эти команды одну за другой в командной строке sqlite>:

INSERT INTO temp_employees (name, department) VALUES ('Alice', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Bob', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Charlie', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('David', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Eve', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Frank', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('Grace', 'HR');
INSERT INTO temp_employees (name, department) VALUES ('Henry', 'Sales');

Эти команды добавляют восемь строк в таблицу temp_employees.

INSERT INTO temp_employees (name, department): Это указывает, что вы вставляете данные в столбцы name и department таблицы temp_employees.

VALUES ('Alice', 'Sales'): Это предоставляет значения для вставки для каждой записи.

Чтобы убедиться, что данные были добавлены правильно, выполните эту команду, чтобы просмотреть все записи в таблице:

SELECT * FROM temp_employees;

Ожидаемый результат:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

Этот вывод показывает id, name и department для каждой записи. Команда SELECT * извлекает все столбцы из указанной таблицы.

Запрос данных из временной таблицы

На этом шаге вы узнаете, как извлекать данные из вашей временной таблицы с помощью оператора SELECT с различными предложениями (clauses). Запрос данных является основным навыком для работы с базами данных, поскольку он позволяет просматривать и анализировать хранящуюся информацию.

В командной строке sqlite> давайте сначала извлечем все данные из таблицы temp_employees. Выполните эту команду:

SELECT * FROM temp_employees;

Ожидаемый результат:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

Символ * в SELECT * означает "все столбцы", поэтому эта команда показывает каждый столбец и строку в таблице temp_employees.

Далее, отфильтруйте данные, используя условие с предложением WHERE. Чтобы просмотреть только записи для сотрудников в отделе 'Sales', выполните:

SELECT * FROM temp_employees WHERE department = 'Sales';

Ожидаемый результат:

1|Alice|Sales
4|David|Sales
8|Henry|Sales

Предложение WHERE сужает результаты до строк, которые соответствуют условию, в данном случае, где department равен 'Sales'.

SELECT * FROM temp_employees выбирает все столбцы из таблицы temp_employees.
WHERE department = 'Sales' фильтрует результаты, чтобы включить только строки, где столбец department равен 'Sales'.

Теперь отсортируйте данные по имени, используя предложение ORDER BY:

SELECT * FROM temp_employees ORDER BY name;

Ожидаемый результат:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

Предложение ORDER BY сортирует результаты на основе указанного столбца, в данном случае столбца name.

Наконец, ограничьте количество результатов, используя предложение LIMIT. Чтобы просмотреть только первые 3 записи, выполните:

SELECT * FROM temp_employees LIMIT 3;

Ожидаемый результат:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering

Предложение LIMIT ограничивает количество строк, возвращаемых запросом.

Подтверждение автоматической очистки временной таблицы

На этом шаге вы убедитесь, что временная таблица temp_employees автоматически удаляется при закрытии соединения с базой данных. Это ключевая характеристика временных таблиц.

Сначала выйдите из оболочки SQLite, набрав:

.exit

Это закроет соединение с базой данных employees.db.

Теперь снова откройте оболочку SQLite, выполнив:

sqlite3 employees.db

Попробуйте запросить таблицу temp_employees:

SELECT * FROM temp_employees;

Вы должны увидеть сообщение об ошибке, указывающее на то, что таблица не существует:

Error: no such table: temp_employees

Это подтверждает, что временная таблица была автоматически удалена при закрытии предыдущего соединения с базой данных.

SELECT * FROM temp_employees: Этот SQL-запрос пытается выбрать все столбцы и строки из таблицы temp_employees.

Error: no such table: temp_employees: Это сообщение об ошибке указывает на то, что таблица temp_employees не существует в базе данных, подтверждая, что она была автоматически удалена при закрытии соединения с базой данных.

Резюме

В этой лабораторной работе вы узнали, как создавать и использовать временные таблицы (temporary tables) в SQLite. Вы создали временную таблицу с именем temp_employees, вставили в нее данные, запросили данные, используя различные операторы SELECT, и убедились, что временная таблица автоматически удаляется при закрытии соединения с базой данных. Эти практические упражнения предоставили всестороннее введение во временные таблицы, вооружив вас фундаментальными знаниями для эффективной обработки временных данных с использованием SQLite.