Создание и управление базами данных SQLite

Введение

В этой лабораторной работе вы изучите основы работы с базами данных SQLite с помощью инструмента командной строки sqlite3. SQLite — это легковесная файловая система баз данных, идеально подходящая для начинающих. Вы создадите базу данных, создадите таблицы, вставите данные, запросите данные, добавите столбцы, обновите данные и, наконец, удалите файл базы данных. Давайте начнем!

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/link_db("Connect To Database") sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/append_col("Add New Column") sqlite/SQLiteGroup -.-> sqlite/end_db("Close Database Connection") subgraph Lab Skills sqlite/init_db -.-> lab-552337{{"Создание и управление базами данных SQLite"}} sqlite/link_db -.-> lab-552337{{"Создание и управление базами данных SQLite"}} sqlite/make_table -.-> lab-552337{{"Создание и управление базами данных SQLite"}} sqlite/add_rows -.-> lab-552337{{"Создание и управление базами данных SQLite"}} sqlite/get_all -.-> lab-552337{{"Создание и управление базами данных SQLite"}} sqlite/query_where -.-> lab-552337{{"Создание и управление базами данных SQLite"}} sqlite/append_col -.-> lab-552337{{"Создание и управление базами данных SQLite"}} sqlite/end_db -.-> lab-552337{{"Создание и управление базами данных SQLite"}} end

Создание и подключение к базе данных SQLite

В этом шаге вы создадите новый файл базы данных SQLite и подключитесь к нему с помощью инструмента командной строки sqlite3.

Откройте терминал в среде виртуальной машины LabEx VM. По умолчанию вы должны находиться в каталоге /home/labex/project. Подтвердите это, выполнив следующую команду:
```
pwd
```
Вывод должен быть следующим:
```
/home/labex/project
```
Если вы не находитесь в этом каталоге, перейдите в него, выполнив команду:
```
cd /home/labex/project
```
Создайте новую базу данных SQLite с именем mydatabase.db и подключитесь к ней с помощью команды sqlite3:
```
sqlite3 mydatabase.db
```
Эта команда создает файл mydatabase.db, если он не существует, и открывает соединение с ним. Вы увидите приглашение SQLite:
```
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>
```

Команда sqlite3 используется как для создания, так и для подключения к базам данных SQLite. Если указанный файл базы данных не существует, SQLite создаст его. Теперь вы готовы выполнять SQL-команды в среде SQLite.

Создание таблицы `users`

В этом шаге вы создадите таблицу с именем users внутри базы данных mydatabase.db.

Убедитесь, что вы все еще подключены к базе данных mydatabase.db с предыдущего шага. Если нет, переподключитесь, используя:
```
sqlite3 mydatabase.db
```
Создайте таблицу с именем users с помощью следующей SQL-команды:
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);
```
Эта команда определяет структуру таблицы users. Столбец id является целым числом и первичным ключом (primary key), гарантируя, что у каждого пользователя есть уникальный идентификатор. Столбец name является текстовым полем, которое не может быть пустым (NOT NULL), а столбец age является целым числом.

Команда CREATE TABLE является фундаментальной SQL-командой, используемой для определения структуры таблицы в базе данных. Ограничение PRIMARY KEY гарантирует, что столбец id содержит уникальные значения для каждой строки, а ограничение NOT NULL гарантирует, что столбец name не может быть пустым.

Вставка данных в таблицу `users`

Теперь, когда вы создали таблицу users, вы можете вставлять в нее данные. Это включает в себя добавление строк (записей) в таблицу, где каждая строка представляет пользователя и соответствующую информацию о нем.

Убедитесь, что вы все еще подключены к базе данных mydatabase.db. Если нет, переподключитесь, используя:
```
sqlite3 mydatabase.db
```
Вставьте следующие записи в таблицу users, используя команду INSERT INTO:
```
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 22);
```
Эти команды добавляют три новые строки в таблицу users. Каждая строка включает id, name и age.

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

Запрос данных из таблицы `users`

В этом шаге вы узнаете, как запрашивать данные из таблицы users с помощью команды SELECT. Это позволит вам извлекать определенную информацию из базы данных.

Убедитесь, что вы все еще подключены к базе данных mydatabase.db. Если нет, переподключитесь, используя:
```
sqlite3 mydatabase.db
```
Извлеките все данные из таблицы users, используя следующую SQL-команду:
```
SELECT * FROM users;
```
Эта команда выбирает все столбцы (*) из всех строк в таблице users. В выходных данных будут отображены данные, которые вы вставили на предыдущем шаге. Вы должны увидеть что-то вроде этого:
```
1|Alice|25
2|Bob|30
3|Charlie|22
```

Команда SELECT — это мощный инструмент для извлечения данных из базы данных. Символ подстановки * указывает, что должны быть выбраны все столбцы. Вы также можете выбрать определенные столбцы, перечислив их имена через запятую (например, SELECT name, age FROM users;).

Добавление нового столбца и обновление данных

В этом шаге вы добавите новый столбец с именем email в таблицу users, а затем обновите существующие записи адресами электронной почты.

Убедитесь, что вы все еще подключены к базе данных mydatabase.db. Если нет, переподключитесь, используя:
```
sqlite3 mydatabase.db
```
Добавьте новый столбец с именем email в таблицу users, используя команду ALTER TABLE:
```
ALTER TABLE users ADD COLUMN email TEXT;
```
Эта команда добавляет новый столбец с именем email типа TEXT в таблицу users.
Обновите столбец email для каждого пользователя, используя команду UPDATE:
```
UPDATE users SET email = '[email protected]' WHERE id = 1;
UPDATE users SET email = '[email protected]' WHERE id = 2;
UPDATE users SET email = '[email protected]' WHERE id = 3;
```
Эти команды обновляют столбец email для каждого пользователя соответствующим адресом электронной почты.

Проверьте обновленные данные, запросив таблицу users:

SELECT * FROM users;

1|Alice|25|[email protected]
2|Bob|30|[email protected]
3|Charlie|22|[email protected]

Команда ALTER TABLE используется для изменения структуры существующей таблицы. В этом случае мы добавляем новый столбец. Команда UPDATE используется для изменения существующих данных в таблице. Предложение SET указывает, какой столбец следует обновить и какое новое значение присвоить. Предложение WHERE указывает, какие строки следует обновить на основе условия.

Выход из SQLite и удаление файла базы данных

В этом шаге вы выйдете из интерфейса SQLite, а затем удалите файл базы данных. Поскольку SQLite хранит все данные в одном файле, удаление базы данных так же просто, как удаление файла с помощью команды rm. Будьте осторожны, так как это действие необратимо!

Выйдите из командной строки SQLite:
```
.quit
```
Убедитесь, что вы находитесь в каталоге /home/labex/project. Подтвердите с помощью:
```
pwd
```
Вывод должен быть следующим:
```
/home/labex/project
```
Если это не так, перейдите в него, используя:
```
cd /home/labex/project
```
Удалите файл mydatabase.db с помощью команды rm:
```
rm mydatabase.db
```
В случае успешного удаления вывода не будет.
Убедитесь, что файл был удален, снова перечислив содержимое каталога:
```
ls
```
Вы больше не должны видеть mydatabase.db в выводе, что подтверждает удаление файла.

Команда .quit выходит из оболочки SQLite и возвращает вас в терминал Linux. Команда rm — это стандартная команда Linux, используемая для удаления файлов. В этом случае она навсегда удаляет файл mydatabase.db, который содержит все данные и структуру вашей базы данных SQLite.

Итог

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