Создание таблицы students и её связи с таблицей courses

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

Давайте еще раз пройдемся по нашему примеру со студентами и курсами. С самого начала. От создания таблиц, до разбора что происходит на каждом этапе. Готовы?

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

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

Таблица students будет хранить информацию о студентах: их уникальный идентификатор, имя и дату рождения. Мы создадим её с использованием команды CREATE TABLE.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Уникальный ключ для каждой записи
    name TEXT NOT NULL,            -- Имя студента (обязательное поле)
    birth_date DATE                -- Дата рождения студента
);

Что здесь происходит?

1. student_id SERIAL PRIMARY KEY: Это уникальный идентификатор для каждого студента. Тип SERIAL создаёт автоинкрементируемое поле, а PRIMARY KEY гарантирует, что идентификатор будет уникальным для каждой строки.
2. name TEXT NOT NULL: Поле для имени студента. Мы указали NOT NULL, чтобы ни одна запись не могла быть вставлена без указания имени.
3. birth_date DATE: Поле для хранения даты рождения. Здесь тип данных DATE поможет нам работать с датами.

Если бы таблица была как человек, то student_id — это её паспорт, name — очевидное имя, а birth_date — то, что мы иногда скрываем подальше, но для базы данных это важно.

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

Теперь создадим таблицу, которая будет хранить информацию о курсах. Она будет включать идентификатор курса, его название и описание.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,  -- Уникальный идентификатор курса
    title TEXT NOT NULL,           -- Название курса (обязательное поле)
    description TEXT               -- Описание курса
);

Что здесь происходит?

1. course_id SERIAL PRIMARY KEY: Аналогично, это автоинкрементируемое поле, которое создаёт уникальный идентификатор для каждого курса.
2. title TEXT NOT NULL: Поле для названия курса. Мы указали NOT NULL, так как логично предположить, что у любого курса должно быть имя.
3. description TEXT: Поле для краткого описания курса. Это уже дополнительное поле, которое может быть пустым (без ограничения NOT NULL).

Курс без названия — это как книга без обложки. Но база данных этого не допустит!

Связывание таблицы students с таблицей courses

Теперь представим, что каждый студент может быть записан только на один курс. Для этого мы добавим внешний ключ в таблицу courses, который будет ссылаться на идентификатор студента из таблицы students.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,  -- Уникальный идентификатор курса
    title TEXT NOT NULL,           -- Название курса (обязательное поле)
    description TEXT,              -- Описание курса
    student_id INT REFERENCES students(student_id) -- Внешний ключ, связывающий с таблицей students
);

Что делает student_id INT REFERENCES students(student_id)?

1. Мы создаём поле student_id, которое будет ссылкой на уникальный идентификатор студента (student_id) из таблицы students.
2. Связь между таблицами обеспечивается ключевым словом REFERENCES.
3. Это означает, что при каждой вставке данных в courses значение student_id должно существовать в таблице students. Если, например, вы попытаетесь записать на курс студента с несуществующим student_id, вы получите ошибку.

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

Когда таблицы созданы, давайте добавим немного студентов и курсов. Ведь это скучно — сидеть в пустой аудитории, верно?

Добавляем студентов

INSERT INTO students (name, birth_date) VALUES
('Alex Lin', '2000-05-10'),
('Maria Chi', '1998-02-15'),
('Otto Song', '2001-09-25');

Добавляем курсы

INSERT INTO courses (title, description, student_id) VALUES
('Основы SQL', 'Изучение базового синтаксиса SQL', 1),
('Реляционные базы данных', 'Понимание реляционных моделей', 2),
('PostgreSQL для начинающих', 'Установка и настройка PostgreSQL', 3);

Мы указали student_id для каждого курса, связывая его с идентификатором студента в таблице students.

Проверяем связь между таблицами

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

SELECT
    courses.title AS course_title,
    courses.description AS course_description,
    students.name AS student_name
FROM 
    courses
JOIN 
    students ON courses.student_id = students.student_id;

Пример результата: Результат запроса:

Мы связали курсы со студентами через внешний ключ — и теперь можем получать связанные данные в одном запросе. Вот так работает реляционная модель!

Резюме

Я надеюсь, что все, что мы только что разобрали было для вас понятно и очевидно. Потому что скоро мы пойдем дальше, и хотелось бы чтобы вы были уверены в своих знания.

2

Задача

SQL SELF, 20 уровень, 0 лекция

Недоступна

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

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

Открыть

2

Задача

SQL SELF, 20 уровень, 0 лекция

Недоступна

Создание таблицы `courses` и внешнего ключа

Создание таблицы `courses` и внешнего ключа

Открыть