Базовые операции с данными в PostgreSQL

Введение

В этой лабораторной работе мы изучим основные операции с данными в PostgreSQL. Основная цель - научиться манипулировать данными в базе данных PostgreSQL с помощью основных команд SQL.

Мы начнем с подключения к базе данных и создания таблицы employees со столбцами для ID, имени и отдела. Затем мы научимся вставлять данные в таблицу с помощью оператора INSERT INTO. После этого мы рассмотрим запросы данных с помощью SELECT, обновление существующих записей и удаление записей. Эта лабораторная работа представляет собой практическое введение в основные методы управления данными в PostgreSQL.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/data_sort("Sort Query Results") postgresql/PostgreSQLGroup -.-> postgresql/row_edit("Update Single Row") postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row") subgraph Lab Skills postgresql/table_init -.-> lab-550897{{"Базовые операции с данными в PostgreSQL"}} postgresql/row_add -.-> lab-550897{{"Базовые операции с данными в PostgreSQL"}} postgresql/data_all -.-> lab-550897{{"Базовые операции с данными в PostgreSQL"}} postgresql/data_where -.-> lab-550897{{"Базовые операции с данными в PostgreSQL"}} postgresql/data_sort -.-> lab-550897{{"Базовые операции с данными в PostgreSQL"}} postgresql/row_edit -.-> lab-550897{{"Базовые операции с данными в PostgreSQL"}} postgresql/row_drop -.-> lab-550897{{"Базовые операции с данными в PostgreSQL"}} end

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

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

Прежде чем мы начнем, убедитесь, что у вас установлен и запущен PostgreSQL. Вы можете подключиться к вашему PostgreSQL серверу с помощью инструмента командной строки psql.

Сначала давайте подключимся к базе данных. Откройте ваш терминал и выполните следующую команду:

sudo -u postgres psql

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

CREATE TABLE IF NOT EXISTS employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

Этот SQL запрос создает таблицу с именем employees с тремя столбцами:

id: Целое число, которое автоматически увеличивается с каждой новой записью (используя SERIAL). Это также первичный ключ (primary key) для таблицы.
name: Строка (до 50 символов) для хранения имени сотрудника.
department: Строка (до 50 символов) для хранения отдела сотрудника.

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

INSERT INTO employees (name, department) VALUES ('Alice Smith', 'Sales');
INSERT INTO employees (name, department) VALUES ('Bob Johnson', 'Marketing');
INSERT INTO employees (name, department) VALUES ('Charlie Brown', 'Engineering');

Каждый оператор INSERT INTO добавляет новую строку в таблицу employees. Мы указываем имя таблицы (employees) и столбцы, в которые мы вставляем данные (name, department). За ключевым словом VALUES следуют фактические данные, которые мы хотим вставить, заключенные в круглые скобки.

Чтобы убедиться, что данные были вставлены правильно, мы можем запросить таблицу employees с помощью оператора SELECT.

SELECT * FROM employees;

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

 id |     name      | department
----+---------------+------------
  1 | Alice Smith   | Sales
  2 | Bob Johnson   | Marketing
  3 | Charlie Brown | Engineering
(3 rows)

Теперь вы успешно вставили данные в таблицу PostgreSQL. Вы можете продолжать вставлять больше записей по мере необходимости.

Наконец, выйдите из оболочки psql, набрав:

\q

Это вернет вас в терминал.

Запрос данных с помощью SELECT

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

Сначала убедитесь, что вы подключены к базе данных. Если вы еще не подключены, откройте свой терминал и выполните следующую команду:

sudo -u postgres psql

Давайте научимся выбирать определенные столбцы. Предположим, мы хотим получить только столбцы name (имя) и department (отдел).

SELECT name, department FROM employees;

Этот оператор вернет только столбцы name и department для всех строк в таблице employees. Вывод должен выглядеть примерно так:

     name      | department
---------------+------------
 Alice Smith   | Sales
 Bob Johnson   | Marketing
 Charlie Brown | Engineering
(3 rows)

Теперь давайте добавим предложение WHERE для фильтрации результатов. Предположим, мы хотим получить только тех сотрудников, которые работают в отделе 'Sales' (продажи).

SELECT name, department FROM employees WHERE department = 'Sales';

Предложение WHERE указывает условие, которое должно быть выполнено, чтобы строка была включена в результирующий набор (result set). В этом случае мы выбираем только те строки, где столбец department равен 'Sales'. Вывод должен быть:

     name      | department
---------------+------------
 Alice Smith   | Sales
(1 row)

Наконец, давайте научимся сортировать результаты с помощью предложения ORDER BY. Предположим, мы хотим получить всех сотрудников, отсортированных по их имени в порядке возрастания.

SELECT name, department FROM employees ORDER BY name;

Этот оператор вернет все строки из таблицы employees, но результаты будут отсортированы в алфавитном порядке по столбцу name. Вывод должен быть:

     name      | department
---------------+------------
 Alice Smith   | Sales
 Bob Johnson   | Marketing
 Charlie Brown | Engineering
(3 rows)

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

SELECT name, department FROM employees ORDER BY name DESC;

Это отсортирует результаты в обратном алфавитном порядке.

Теперь вы узнали, как запрашивать данные из таблицы PostgreSQL с помощью оператора SELECT, включая выбор определенных столбцов, фильтрацию результатов с помощью предложения WHERE и сортировку результатов с помощью предложения ORDER BY.

Illustration for querying data in PostgreSQL

Наконец, выйдите из оболочки psql, набрав:

\q

Обновление существующих записей

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

sudo -u postgres psql

Давайте обновим отдел 'Alice Smith' на 'Human Resources' (Отдел кадров).

UPDATE employees SET department = 'Human Resources' WHERE name = 'Alice Smith';

Оператор UPDATE изменяет существующие строки в таблице. Предложение SET указывает, какие столбцы следует обновить и их новые значения. Предложение WHERE указывает, какие строки следует обновить. В этом случае мы обновляем столбец department на 'Human Resources' для строки, где столбец name равен 'Alice Smith'.

Чтобы убедиться, что обновление прошло успешно, мы можем запросить таблицу employees.

SELECT * FROM employees WHERE name = 'Alice Smith';

Это отобразит строку для 'Alice Smith', и вы увидите, что столбец department был обновлен до 'Human Resources'. Вывод должен быть примерно таким:

 id |     name      |   department
----+---------------+------------------
  1 | Alice Smith   | Human Resources
(1 row)

Вы также можете обновить несколько столбцов одновременно. Например, давайте обновим отдел Боба Джонсона на 'Sales' (Продажи) и его имя на 'Robert Johnson'.

UPDATE employees SET department = 'Sales', name = 'Robert Johnson' WHERE name = 'Bob Johnson';

Этот оператор обновляет как столбец department, так и столбец name для строки, где столбец name равен 'Bob Johnson'.

Чтобы проверить обновление, снова запросите таблицу employees.

SELECT * FROM employees WHERE name = 'Robert Johnson';

Вывод должен быть:

 id |      name      | department
----+----------------+------------
  2 | Robert Johnson | Sales
(1 row)

Важно быть осторожным при использовании оператора UPDATE, особенно при пропуске предложения WHERE. Если вы опустите предложение WHERE, оператор UPDATE обновит все строки в таблице. Например:

UPDATE employees SET department = 'Unknown';

Этот оператор установит столбец department в 'Unknown' для каждой строки в таблице employees. Будьте очень осторожны при выполнении такого оператора.

Illustration for updating records in PostgreSQL

Теперь вы узнали, как обновлять существующие записи в таблице PostgreSQL с помощью оператора UPDATE.

Наконец, выйдите из оболочки psql, набрав:

\q

Удаление записей

На этом шаге мы научимся удалять записи из таблицы PostgreSQL с помощью оператора DELETE. Удаление данных является важной операцией для удаления устаревшей или неверной информации из вашей базы данных. Мы рассмотрим базовый синтаксис и предоставим примеры, которые помогут вам понять этот процесс.

sudo -u postgres psql

Давайте удалим запись для 'Charlie Brown' из таблицы employees.

DELETE FROM employees WHERE name = 'Charlie Brown';

Оператор DELETE FROM удаляет строки из таблицы. Предложение WHERE указывает, какие строки следует удалить. В этом случае мы удаляем строку, где столбец name равен 'Charlie Brown'.

Чтобы убедиться, что удаление прошло успешно, мы можем запросить таблицу employees.

SELECT * FROM employees WHERE name = 'Charlie Brown';

Это не должно вернуть никаких строк, что указывает на то, что запись для 'Charlie Brown' была удалена. Вывод должен быть:

 id | name | department
----+------+------------
(0 rows)

Вы также можете удалить несколько строк одновременно, используя более сложное предложение WHERE. Например, если у вас было несколько сотрудников в отделе 'Marketing' (Маркетинг), вы могли бы удалить их всех одним оператором:

DELETE FROM employees WHERE department = 'Marketing';

Крайне важно быть предельно осторожным при использовании оператора DELETE, особенно при пропуске предложения WHERE. Если вы опустите предложение WHERE, оператор DELETE удалит все строки из таблицы. Например:

DELETE FROM employees;

Этот оператор удалит каждую строку в таблице employees, фактически очистив таблицу. Будьте абсолютно уверены, что хотите удалить все строки, прежде чем выполнять такой оператор.

Теперь вы узнали, как удалять записи из таблицы PostgreSQL с помощью оператора DELETE.

Illustration for record deletion in PostgreSQL

Проверьте таблицу employees, чтобы убедиться, что удаление прошло успешно.

SELECT * FROM employees;

Вывод должен быть:

 id | name | department
----+------+------------
(0 rows)

Наконец, выйдите из оболочки psql, набрав:

\q

Итог

В этой лабораторной работе мы рассмотрели основные операции с данными в PostgreSQL, сосредоточившись на вставке данных в таблицы. Мы начали с подключения к базе данных с помощью инструмента командной строки psql. Затем мы создали таблицу employees со столбцами id, name и department, указав id в качестве первичного ключа (primary key) с функциональностью автоматического увеличения (auto-incrementing functionality).

Наконец, мы вставили три записи о сотрудниках в таблицу employees с помощью оператора INSERT INTO, указав имя таблицы и столбцы для вставки данных, а также соответствующие значения для каждой записи. Это продемонстрировало базовый синтаксис для заполнения таблицы PostgreSQL данными.