Создание таблиц в SQLite

Введение

В этой лабораторной работе вы изучите основные навыки создания и управления таблицами в SQLite, легковесном и бессерверном движке баз данных, идеально подходящем для небольших приложений. Вы изучите типы данных SQLite, создадите таблицы с первичными ключами, примените ограничения, такие как NOT NULL и UNIQUE, проверите структуры таблиц с помощью команды .schema и безопасно удалите таблицы. Благодаря пошаговым упражнениям в среде LabEx VM вы получите практический опыт использования инструмента командной строки 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/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_row("Insert Single Row") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") sqlite/SQLiteGroup -.-> sqlite/end_db("Close Database Connection") subgraph Lab Skills sqlite/init_db -.-> lab-552336{{"Создание таблиц в SQLite"}} sqlite/make_table -.-> lab-552336{{"Создание таблиц в SQLite"}} sqlite/add_row -.-> lab-552336{{"Создание таблиц в SQLite"}} sqlite/add_rows -.-> lab-552336{{"Создание таблиц в SQLite"}} sqlite/get_all -.-> lab-552336{{"Создание таблиц в SQLite"}} sqlite/verify_table -.-> lab-552336{{"Создание таблиц в SQLite"}} sqlite/end_db -.-> lab-552336{{"Создание таблиц в SQLite"}} end

Подключение к SQLite и создание таблицы `contacts`

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

Откройте терминал в среде LabEx VM. Убедитесь, что вы находитесь в рабочем каталоге по умолчанию /home/labex/project.
Подключитесь к базе данных SQLite с именем test.db с помощью инструмента sqlite3. Эта команда создаст файл базы данных, если он еще не существует.
```
sqlite3 test.db
```
Эта команда открывает оболочку SQLite, и вы увидите приглашение, похожее на sqlite>, указывающее, что вы находитесь в среде SQLite.
Создайте таблицу с именем contacts со столбцами для id, name и phone. Введите следующую команду SQL в командной строке sqlite>:
```
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone TEXT
);
```
Эта команда создает таблицу с именем contacts. Столбец id является целым числом и обозначен как PRIMARY KEY (первичный ключ), что означает, что он будет однозначно идентифицировать каждую строку в таблице. Столбцы name и phone имеют тип TEXT (текст), который может хранить любые строковые данные.

Давайте разберем команду:
- CREATE TABLE contacts: Это указывает SQLite создать новую таблицу с именем contacts.
- id INTEGER PRIMARY KEY: Это определяет столбец с именем id с типом данных INTEGER (целое число). Ограничение PRIMARY KEY (первичный ключ) гарантирует, что каждое значение в этом столбце является уникальным и служит основным идентификатором для каждой строки.
- name TEXT: Это определяет столбец с именем name с типом данных TEXT (текст), который может хранить любые строковые данные.
- phone TEXT: Это определяет столбец с именем phone с типом данных TEXT (текст), который может хранить любые строковые данные.

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

Теперь, когда вы создали таблицу contacts, давайте вставим в нее некоторые данные. Вы будете использовать оператор INSERT INTO для добавления новых строк в таблицу.

Вставьте новый контакт в таблицу contacts. Выполните эту команду в командной строке sqlite>:
```
INSERT INTO contacts (name, phone) VALUES ('John Doe', '123-456-7890');
```
Эта команда вставляет новую строку в таблицу contacts с именем 'John Doe' и номером телефона '123-456-7890'. Столбцу id будет автоматически присвоено уникальное значение, поскольку он является первичным ключом (primary key).

Давайте разберем команду:
- INSERT INTO contacts: Это указывает SQLite вставить данные в таблицу contacts.
- (name, phone): Это указывает столбцы, в которые вы хотите вставить данные.
- VALUES ('John Doe', '123-456-7890'): Это указывает значения, которые будут вставлены в соответствующие столбцы.
Вставьте еще один контакт в таблицу contacts:
```
INSERT INTO contacts (name, phone) VALUES ('Jane Smith', '987-654-3210');
```
Эта команда вставляет еще одну строку в таблицу contacts с именем 'Jane Smith' и номером телефона '987-654-3210'.

Извлечение данных из таблицы `contacts`

Теперь, когда вы вставили данные в таблицу contacts, давайте извлечем их. Вы будете использовать оператор SELECT для запроса к таблице и отображения данных.

Извлеките все данные из таблицы contacts. Выполните эту команду в командной строке sqlite>:
```
SELECT * FROM contacts;
```
Эта команда выбирает все столбцы (*) из таблицы contacts и отображает результаты.

Ожидаемый вывод (Expected Output):
```
1|John Doe|123-456-7890
2|Jane Smith|987-654-3210
```
Вывод показывает два контакта, которые вы вставили, вместе с их автоматически назначенными значениями id.

Давайте разберем команду:
- SELECT *: Это указывает SQLite выбрать все столбцы из таблицы.
- FROM contacts: Это указывает таблицу, из которой вы хотите извлечь данные.
Извлеките только столбцы name и phone из таблицы contacts:
```
SELECT name, phone FROM contacts;
```
Эта команда выбирает только столбцы name и phone из таблицы contacts и отображает результаты.

Ожидаемый вывод (Expected Output):
```
John Doe|123-456-7890
Jane Smith|987-654-3210
```

Создание таблицы `users` с ограничениями (Constraints)

Ограничения (constraints) — это правила, которые обеспечивают целостность данных в таблице. Давайте добавим ограничения NOT NULL и UNIQUE к новой таблице под названием users.

Создайте таблицу с именем users с ограничениями. Выполните эту команду в командной строке sqlite>:
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
Эта команда создает таблицу с именем users со следующими ограничениями:
- username не может быть NULL и должен быть UNIQUE.
- email не может быть NULL.
Давайте разберем команду:
- CREATE TABLE users: Это указывает SQLite создать новую таблицу с именем users.
- id INTEGER PRIMARY KEY: Это определяет столбец с именем id с типом данных INTEGER. Ограничение PRIMARY KEY гарантирует, что каждое значение в этом столбце является уникальным и служит первичным идентификатором для каждой строки.
- username TEXT NOT NULL UNIQUE: Это определяет столбец с именем username с типом данных TEXT. Ограничение NOT NULL гарантирует, что этот столбец не может быть пустым, а ограничение UNIQUE гарантирует, что все значения в этом столбце различны.
- email TEXT NOT NULL: Это определяет столбец с именем email с типом данных TEXT. Ограничение NOT NULL гарантирует, что этот столбец не может быть пустым.
- age INTEGER: Это определяет столбец с именем age с типом данных INTEGER.
Теперь давайте попробуем вставить некоторые данные, которые нарушают эти ограничения, чтобы увидеть, как они работают. Сначала попробуйте вставить пользователя без имени пользователя (username):
```
INSERT INTO users (email, age) VALUES ('[email protected]', 25);
```
Вы увидите ошибку, потому что столбец username определен как NOT NULL.

Ожидаемый вывод (Expected Output):
```
Error: NOT NULL constraint failed: users.username
```
Эта ошибка показывает, что SQLite применяет ограничение NOT NULL к username, предотвращая вставку.
Далее попробуйте вставить пользователя с повторяющимся именем пользователя (username). Сначала вставьте допустимого пользователя:
```
INSERT INTO users (username, email, age) VALUES ('johndoe', '[email protected]', 30);
```
Затем попробуйте вставить другого пользователя с тем же именем пользователя:
```
INSERT INTO users (username, email, age) VALUES ('johndoe', '[email protected]', 25);
```
Вы увидите ошибку, потому что столбец username определен как UNIQUE.

Ожидаемый вывод (Expected Output):
```
Error: UNIQUE constraint failed: users.username
```
Эта ошибка демонстрирует, что SQLite применяет ограничение UNIQUE к username, предотвращая повторяющиеся значения.

Просмотр структуры таблицы с помощью `.schema`

Вы можете просмотреть структуру таблицы с помощью команды .schema. Эта команда отображает SQL-запрос, использованный для создания таблицы, включая имена столбцов, типы данных и ограничения (constraints).

Просмотрите структуру таблицы users. Выполните эту команду в командной строке sqlite>:
```
.schema users
```
Ожидаемый вывод (Expected Output):
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
Вывод показывает оператор CREATE TABLE для таблицы users, включая ограничения NOT NULL и UNIQUE для столбца username.

Команда .schema — полезный инструмент для понимания структуры ваших таблиц и проверки правильности определения ограничений.
Выйдите из оболочки SQLite:
```
.quit
```
Эта команда выйдет из оболочки SQLite и вернет вас в терминал Linux.

ВАЖНО (IMPORTANT): Убедитесь, что вы выполнили .exit перед нажатием кнопки "Continue". В противном случае этот шаг не может быть проверен, поскольку история sqlite не будет записана.

Удаление таблицы `contacts`

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

Подключитесь к базе данных test.db:
```
sqlite3 test.db
```
Удалите таблицу contacts. Выполните эту команду в командной строке sqlite>:
```
DROP TABLE contacts;
```
Эта команда удаляет таблицу contacts и все ее данные из базы данных.

Предупреждение (Warning): Это действие необратимо. После удаления таблицы вы не сможете восстановить ее или ее данные.
Убедитесь, что таблица contacts была удалена, попытавшись выбрать из нее данные:
```
SELECT * FROM contacts;
```
Вы увидите ошибку, потому что таблица contacts больше не существует.
Выйдите из оболочки SQLite:
```
.quit
```

Итог (Summary)

В этой лабораторной работе вы изучили основные навыки создания и управления таблицами в SQLite. Вы изучили типы данных SQLite, создали таблицы с первичными ключами (primary keys), применили ограничения (constraints), такие как NOT NULL и UNIQUE, просмотрели структуры таблиц с помощью команды .schema и безопасно удалили таблицы. Эти навыки необходимы для работы с базами данных SQLite и создания приложений, управляемых данными (data-driven applications).

Создание таблиц в SQLite

Введение

Skills Graph

Подключение к SQLite и создание таблицы contacts

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

Извлечение данных из таблицы contacts

Создание таблицы users с ограничениями (Constraints)

Просмотр структуры таблицы с помощью .schema

Удаление таблицы contacts