Базовые команды SQL

SQL — добавление данных в таблицу

От автора: инструкция SQL INSERT INTO используется для того, чтобы произвести добавление данных в таблицу SQL, а точнее, для добавления новых строк.

Синтаксис

Существуют два основных синтаксиса инструкции INSERT INTO:

INSERT INTO ИМЯ_ТАБЛИЦЫ (столбец1, столбец2, столбец3,... столбец N)  

VALUES (значение1, значение2, значение3,... значение N);

Здесь столбец1, столбец2, столбец3,… столбец N — это названия столбцов в таблице, в которые вы хотите вставить данные. Вы можете не указывать имя столбца в SQL-запросе, если добавляете значения для всех столбцов таблицы. Но убедитесь, что порядок значений соответствует порядку столбцов в таблице.

Базовые команды SQL

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля

Получить курс сейчас!

В таком случае синтаксис инструкции SQL INSERT INTO будет следующим:

INSERT INTO TABLE_NAME VALUES (значение1, значение2, значение3,... значение N);

Пример

Следующие инструкции создадут шесть записей в таблице CUSTOMERS.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, ‘Ramesh’, 32, ‘Ahmedabad’, 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, ‘Khilan’, 25, ‘Delhi’, 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, ‘kaushik’, 23, ‘Kota’, 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4, ‘Chaitali’, 25, ‘Mumbai’, 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5, ‘Hardik’, 27, ‘Bhopal’, 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (6, ‘Komal’, 22, ‘MP’, 4500.00 );

Вы можете создать записи в таблице CUSTOMERS, используя второй синтаксис, как показано ниже.

INSERT INTO CUSTOMERS

VALUES (7, ‘Muffy’, 24, ‘Indore’, 10000.00 );

Все приведенные выше инструкции создадут те же записи в таблице CUSTOMERS, как показано ниже.

SQL — добавление данных в таблицу

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

Вы можете заполнить данные таблицы через инструкцию select для другой таблицы; если в другой таблице есть тот же набор полей, которые должны быть заполнены в первой. Синтаксис:

INSERT INTO имя_первой_таблицы [(столбец1, столбец2,... столбец N)]

   SELECT столбец1, столбец2,... столбец N

   FROM имя_второй_таблицы

   [WHERE условие];

Источник: //www.tutorialspoint.com/

Редакция: Команда webformyself.

Базовые команды SQL

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля

Получить курс сейчас!

Базовые команды SQL

Основные команды SQL не ограничиваются стандартными CREATE, UPDATE и DELETE. Данная статья будет полезна тем, кто хочет освежить свои знания по SQL перед собеседованием на работу.

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

Примечание Вы читаете улучшенную версию некогда выпущенной нами статьи.

Настройка базы данных

mysql -u root -p

Затем введите пароль и выполните следующую команду. Назовём базу данных «university»:

CREATE DATABASE university;
USE university;
SOURCE <path_of_DLL.sql_file>;
SOURCE <path_of_InsertStatements.sql_file>;

SHOW DATABASES

SQL-команда, которая отвечает за просмотр доступных баз данных.

CREATE DATABASE

Команда для создания новой базы данных.

USE

С помощью этой SQL-команды USE выбирается база данных, необходимая для дальнейшей работы с ней.

SOURCE

А SOURCE позволит выполнить сразу несколько SQL-команд, содержащихся в файле с расширением .sql.

DROP DATABASE

Стандартная SQL-команда для удаления целой базы данных.

SHOW TABLES

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

CREATE TABLE

SQL-команда для создания новой таблицы:

CREATE TABLE <table_name1> (
  <col_name1><col_type1>,
  <col_name2><col_type2>,
  <col_name3><col_type3>
  PRIMARY KEY(<col_name1>),
  FOREIGN KEY(<col_name2>) REFERENCES <table_name2>(<col_name2>)
);

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

  • ячейка таблицы не может иметь значение NULL;
  • первичный ключ — PRIMARY KEY(col_name1, col_name2, …);
  • внешний ключ — FOREIGN KEY(col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).

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

Пример

Создайте таблицу «instructor»:

CREATE TABLE instructor (
  ID CHAR(5),
  name VARCHAR(20) NOT NULL,
  dept_name VARCHAR(20),
  salary NUMERIC(8,2),
  PRIMARY KEY (ID),
  FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

DESCRIBE

С помощью DESCRIBE можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы.

INSERT

Команда INSERT INTO в SQL отвечает за добавление данных в таблицу:

INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, …)
  VALUES (<value1>, <value2>, <value3>, …); 

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

INSERT INTO <table_name>
  VALUES (<value1>, <value2>, <value3>, …);

UPDATE

SQL-команда для обновления данных таблицы:

UPDATE <table_name>
  SET <col_name1> = <value1>, <col_name2> = <value2>, ...
  WHERE <condition>;

DELETE

SQL-команда DELETE FROM используется для удаления данных из таблицы.

DROP TABLE

А так можно удалить всю таблицу целиком.

SELECT

Далее мы рассмотрим основные команды SQL, которые позволяют работать непосредственно с данными. К одной из таких SQL-команд относится SELECT для получения данных из выбранной таблицы:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>; 

Следующей командой можно вывести все данные из таблицы:

SELECT * FROM <table_name>;

SELECT DISTINCT

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

SELECT DISTINCT <col_name1>, <col_name2>, …
  FROM <table_name>; 

WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>; 

В запросе можно задавать следующие условия:

  • сравнение текста;
  • сравнение численных значений;
  • логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE:

SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3; 

SQL-команды: пример вывода с WHERE

GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT, MAX, MIN, SUM и AVG, для группировки выходных значений.

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  GROUP BY <col_namex>; 

Пример

Выведем количество курсов для каждого факультета:

SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name; 

SQL-команды: пример вывода с GROUP BY

HAVING

Ключевое слово HAVING было добавлено в SQL по той причине, что WHERE не может использоваться для работы с агрегатными функциями.

SELECT <col_name1>, <col_name2>, ...
  FROM <table_name>
  GROUP BY <column_namex>
  HAVING <condition> 

Пример

Выведем список факультетов, у которых более одного курса:

SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name
  HAVING COUNT(course_id)>1; 

SQL-команды: пример вывода с HAVING

ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  ORDER BY <col_name1>, <col_name2>, … ASC|DESC;

Пример

Выведем список курсов по возрастанию и убыванию количества кредитов:

SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;

BETWEEN

BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> BETWEEN <value1> AND <value2>;

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

SELECT * FROM instructor
  WHERE salary BETWEEN 50000 AND 100000; 

LIKE

Оператор LIKE используется в WHERE, чтобы задать шаблон поиска похожего значения.

Есть два свободных оператора, которые используются в LIKE:

  • % (ни одного, один или несколько символов);
  • _ (один символ).
SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> LIKE <pattern>; 

Пример

Выведем список курсов, в имени которых содержится «to», и список курсов, название которых начинается с «CS-»:

SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___';

SQL-команды: пример вывода с LIKE

С помощью IN можно указать несколько значений для оператора WHERE:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namen> IN (<value1>, <value2>, …);

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

SELECT * FROM student
  WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);

JOIN

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

SQL-команды: схема использования JOIN

SELECT <col_name1>, <col_name2>, …
  FROM <table_name1>
  JOIN <table_name2>
  ON <table_name1.col_namex> = <table2.col_namex>;

Пример

Выведем список всех обязательных курсов и детали о них:

SELECT prereq.course_id, title, dept_name, credits, prereq_id
  FROM prereq
  LEFT OUTER JOIN course
  ON prereq.course_id=course.course_id;

SQL-команды: пример вывода с JOIN

VIEW

VIEW — это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. VIEW всегда показывает самую свежую информацию из базы данных.

Создание

CREATE VIEW <view_name> AS
  SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>;

Удаление

DROP VIEW <view_name>;

Агрегатные функции

Это не совсем основные команды SQL, однако знать их тоже желательно. Агрегатные функции используются для получения совокупного результата, относящегося к рассматриваемым данным:

  • COUNT(col_name) — возвращает количество строк;
  • SUM(col_name) — возвращает сумму значений в данном столбце;
  • AVG(col_name) — возвращает среднее значение данного столбца;
  • MIN(col_name) — возвращает наименьшее значение данного столбца;
  • MAX(col_name) — возвращает наибольшее значение данного столбца.

Вложенные подзапросы

Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT, FROM и WHERE, вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

SELECT DISTINCT course_id
  FROM section
  WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (
    SELECT course_id
    FROM section
    WHERE semester = ‘Spring’ AND year= 2010
  ); 

Перевод статьи «SQL Recap for Interviews»

SQL оператор INSERT используется для вставки записей в существующую таблицу.

Синтаксис этого оператора следующий:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
      [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...

или 

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...

или 

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression, ...

Создадим тестовую таблицу

Давайте создадим таблицу table1 со столбцами a, b, c в нашей MySQL базе данных:

CREATE TABLE `table1` (
      `a` INT(11),
      `b` INT(11),
      `c` INT(11)
);

Запрос на вставку строки

Простой запрос, который вставляет строку со столбцами 111, 222 и 333 выглядит так:

INSERT INTO table1 (a, b, c) VALUES (111, 222, 333);

Еще один способ сделать то же самое:

INSERT INTO table1 SET a=111, b=222, c=333;

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

У таблиц обычно есть поле id с первичным ключом (PRIMARY KEY) таблицы. Если этому полю установлено значение AUTOINCREMENT т.е. оно заполняется автоматически, то в таком случае вы не должны его перечислять в списке столбцов оператора INSERT.

Вставка без перечисления столбцов

Если количество значений, которые мы вставляем = количеству столбцов в таблице, то можно не перечислять столбцы, и наш запрос может выглядеть так:

INSERT INTO table1 VALUES (111, 222, 333);

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

Если нам нужно вставить несколько строк, то мы просто перечисляем группы значений через запятую выглядит это так:

INSERT INTO table1 (a, b, c) VALUES 
    (1, 222, 333), 
    (2, 555, 666),
    (3, 555, 333);

Таким образом мы вставили 3 строки в нашу таблицу table1. Их может быть и больше. В MySQL четкого предела нет, однако он все таки существует и зависит от параметра max_allowed_packet который ограничивает размер запроса. Если вы установите SET GLOBAL max_allowed_packet=524288000; то размер запроса будет ограничен 500MB но делайте это в очень крайнем случае. Обычно всегда можно найти решение и разделить 1 большой запрос, на несколько более мелких и вставлять например не больше 1000 строк за один цикл.

Допустим у нас есть еще одна таблица table2 которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.

Вставляем значения из table1 в таблицу table2:

INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1;

Вам следует позаботиться об уникальности ключей, если они есть в таблице, в которую мы вставляем. Например при дублировании PRIMARY KEY мы получим следующее сообщение об ошибке:

/* ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY' */

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

Как не рекомендуется делать (без перечисления столбцов):

INSERT INTO table2 SELECT * FROM table1;

Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:

/* Ошибка SQL (1136): Column count doesn't match value count at row 1 */

Либо еще хуже: значения вставятся не в те столбцы.

А теперь представим, что нам нужно вставить только те строки из table1, у которых столбец «c» равен 333. Тогда наш запрос будет выглядеть так

INSERT INTO table2 (a, b, c)
    SELECT a, b, c FROM table1 
    WHERE c = 333;

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

Теперь представим, что у нас в таблице table2 — 4 столбца, а в table1 — 3. При этом четвертый столбец в table2 обязательный. Чтобы выйти из этой ситуации, нужно передать какое-нибудь подходящее значение в этот лишний столбец. У нас чисто абстрактная задача, поэтому давайте передадим туда просто единицу.

INSERT INTO table2 (a, b, c, d)
    SELECT a, b, c, 1 FROM table1 
    WHERE c = 333;

Теперь в столбец d у нас записалась единица и проблема решена.

Если вам нужно вставить строки в определенный раздел таблицы, то нужно после таблицы указать PARTITION (название раздела), например так:

INSERT INTO table1 PARTITION (p1) VALUES(1, 2, 3);

Вставка в несколько разделов. Первая строка вставляется в раздел p1, а вторая в p2

INSERT INTO table1 PARTITION (p1, p2) VALUES(1, 2, 3), (4, 5, 6);

Вставка строк, некоторые из которых уже существуют в целевой таблице

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

Игнорирование INSERT IGNORE INTO

Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:

/* ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' */

Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:

INSERT IGNORE INTO table1 (a, b, c) VALUES 
    (1, 222, 333), 
    (2, 555, 666),
    (3, 555, 333);

Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться

Вставка с заменой существующих значений REPLACE INTO

REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.

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

REPLACE INTO table1 (a, b, c) VALUES 
    (1, 222, 333), 
    (2, 555, 666),
    (3, 555, 333);

Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE

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

Наш запрос будет выглядеть так:

INSERT INTO table1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=VALUES(c);

В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу «c» значения, которое у нас перечислено в VALUES.

Иными словами, если ключ совпадает, то мы просто обновим данные столбца «с» а остальные столбцы трогать не будем.

Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:

INSERT INTO table1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c);

При обновлении столбцов мы также можем использовать разные выражения, например:

INSERT INTO table1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Подробнее про ON DUPLICATE KEY UPDATE

Выражения для вставляемых значений в VALUES

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

Пример использования выражений:

INSERT INTO table1 (a, b, c) VALUES 
    (1, 222, 333 + a), 
    (2, 555, 666 + b);

Таким образом мы для формирования столбца «c» использовали столбцы «a» и «b».

Приоритет вставки INSERT LOW_PRIORITY / HIGH_PRIORITY

Установление приоритета нужно для решение проблем с конкурентными вставками. При вставках происходит блокировка строк и если 2 INSERT запроса требуют блокировки одних и тех же строк, для своего выполнения, то иногда может потребоваться повысить или понизить приоритет некоторых запросов, по отношению к другим. Это можно сделать указав приоритет LOW_PRIORITY или HIGH_PRIORITY

Наш запрос будет выглядеть так для LOW_PRIORITY:

INSERT LOW_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);
INSERT HIGH_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);

Нашли опечатку или ошибку? Выделите её и нажмите Ctrl+Enter

<!—Базовые команды SQL—>

Помогла ли Вам эта статья?

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

Основной элемент из которого состоит реляционная база данных – это таблицы. Для создания таблицы используется команда CREATE TABLE <имя таблицы> (<имя колонки> <тип колонки>, <имя колонки> <тип колонки>, …). Например создадим таблицу users для хранения списка пользователей, которых мы обслуживаем. В эту таблицу будем заносить ФИО пользователя, название его компании, его номер телефона и электронную почту:

# CREATE TABLE users
(fio text,
company text,
phone varchar(11),
email text
);

Типы данных, которые можно применять, рассмотрю позже.

Наполнение таблицы строками

Строки вставляются в таблицу с помощью команды SQL INSERT INTO <имя таблицы> (перечисляем поля)
VALUES (перечисляем значения)
. При этом можно вставлять по одной строке или по несколько. Вставим три строки в нашу таблицу users:

# INSERT INTO users (fio, company, phone, email)
VALUES ('Иванов Иван Алексеевич', 'ООО "Ромашка"', '89057362761', 'ivanov@mail.ru'),
('Донченко Иван Андреевич', 'ООО "Ромашка"', '89038276494', 'dota@yandex.ru'),
('Девин Алексей Владимирович', 'ООО "Начало"', '89069384782', 'test@yandex.ru');

Как видно из примера выше, мы вначале определяем порядок колонок (fio, company, phone, email), а затем в этом порядке вставляем значения.

При вставке строковых значений, строка всегда берётся в одинарные кавычки.

Просмотр таблицы

Посмотреть на созданную таблицу можно с помощью команды SELECT * FROM &lt;название таблицы&gt;. Например:

# SELECT * FROM users;
            fio             |    company    |    phone    |     email
----------------------------+---------------+-------------+----------------
 Иванов Иван Алексеевич     | ООО "Ромашка" | 89057362761 | ivanov@mail.ru
 Донченко Иван Андреевич    | ООО "Ромашка" | 89038276494 | dota@yandex.ru
 Девин Алексей Владимирович | ООО "Начало"  | 89069384782 | test@yandex.ru
(3 rows)

Можно вывести определённые колонки указав их вместо звездочки:

# SELECT fio, phone FROM users;
            fio             |    phone
----------------------------+-------------
 Иванов Иван Алексеевич     | 89057362761
 Донченко Иван Андреевич    | 89038276494
 Девин Алексей Владимирович | 89069384782
(3 rows)

Можно вывести определённые строки с помощью WHERE &lt;условие&gt;:

# SELECT * FROM users WHERE company = 'ООО "Ромашка"';
           fio           |    company    |    phone    |     email
-------------------------+---------------+-------------+----------------
 Иванов Иван Алексеевич  | ООО "Ромашка" | 89057362761 | ivanov@mail.ru
 Донченко Иван Андреевич | ООО "Ромашка" | 89038276494 | dota@yandex.ru
(2 rows)

В качестве условия можем указать, что значение в определённой колонке должно:

  • чему-то равняется;
  • быть больше или меньше определённого значения;
  • содержать что-то;
  • и другое.

Можем комбинировать эти методы, например:

# SELECT fio, phone FROM users WHERE company = 'ООО "Ромашка"';
           fio           |    phone
-------------------------+-------------
 Иванов Иван Алексеевич  | 89057362761
 Донченко Иван Андреевич | 89038276494
(2 rows)

А ещё можем отсортировать таблицу по какой-нибудь строке:

# SELECT fio, phone FROM users ORDER BY fio;
            fio             |    phone
----------------------------+-------------
 Девин Алексей Владимирович | 89069384782
 Донченко Иван Андреевич    | 89038276494
 Иванов Иван Алексеевич     | 89057362761

Условий может быть несколько, например ФИО должно содержать Иван, а телефон должен заканчиваться на 94:

# SELECT * FROM users WHERE fio ~ 'Иван' AND phone ~ '.*94$';
           fio           |    company    |    phone    |     email
-------------------------+---------------+-------------+----------------
 Донченко Иван Андреевич | ООО "Ромашка" | 89038276494 | dota@yandex.ru

Знак “~” – означает что значение должно содержать, а не равняться.

Также в примере выше я показал, что в условии можно использовать регулярные выражения.

Комбинировать условия можно с помощью AND и OR.

Изменение строк

Строки в SQL таблице можно обновить, или другими словами изменить. Изменяют строки с помощью команды UPDATE &lt;имя таблицы&gt; SET &lt;колонка = значение&gt; WHERE &lt;условие&gt;. Например изменим номер телефона Иванову Ивану Алексеевичу:

# UPDATE users SET phone = '89057362744' WHERE fio = 'Иванов Иван Алексеевич';
UPDATE 1
it_outsourcing=# SELECT fio, phone FROM users WHERE fio = 'Иванов Иван Алексеевич';
          fio           |    phone
------------------------+-------------
 Иванов Иван Алексеевич | 89057362744
(1 row)

Если не указать условие, то обновятся все строки:

# UPDATE users SET phone = '89057362744';
UPDATE 3

it_outsourcing=# select fio, phone FROM users;
            fio             |    phone
----------------------------+-------------
 Донченко Иван Андреевич    | 89057362744
 Девин Алексей Владимирович | 89057362744
 Иванов Иван Алексеевич     | 89057362744
(3 rows)

Удаление строк

Для удаления строк используют команду DELETE FROM &lt;имя таблицы&gt; WHERE &lt;условие&gt;. Например:

# DELETE FROM users WHERE fio = 'Донченко Иван Андреевич';
DELETE 1

# select fio, phone FROM users;
            fio             |    phone
----------------------------+-------------
 Девин Алексей Владимирович | 89057362744
 Иванов Иван Алексеевич     | 89057362744
(2 rows)

Если не указать блок WHERE, то удалятся все строки! Но очищать таблицу лучше другой командой.

Повторим изученные команды

  • CREATE TABLE <имя таблицы> (<имя колонки> <тип колонки>, <имя колонки> <тип колонки>, …);
  • UPDATE <имя таблицы> SET <колонка = значение> WHERE <условие>
  • TRUNCATE <имя таблицы>;
  • DROP DATABASE <имя базы данных>.

Сводка

Базовые команды SQL

Имя статьи

Базовые команды SQL

Описание

Разберём команды SQL, которые помогут вам создать базу данных, создать в ней таблицу. Во-вторых научимся работать с этой таблицей

Теория

Первичный ключ это одно или несколько полей в таблице. Он необходим для уникальной идентификации любой строки. Первичный ключ накладывает некоторые ограничения:

  • Все записи относящиеся к первичному ключу должны быть уникальны. Это означает, что если первичный ключ состоит из одного поля, то все записи в нём должны быть уникальными. А если первичный ключ состоит из нескольких полей, то комбинация этих записей должна быть уникальна, но в отдельных полях допускаются повторения.
  • Записи в полях относящихся к первичному ключу не могут быть пустыми. Это ограничение в PostgreSQL называется not null.
  • В каждой таблице может присутствовать только один первичный ключ.

К первичному ключу предъявляют следующее требование:

  • Первичный ключ должен быть минимально достаточным. То есть в нем не должно быть полей, удаление которых из первичного ключа не отразится на его уникальности. Это не обязательное требование но желательно его соблюдать.

Первичный ключ может быть:

  • естественным – существует в реальном мире, например ФИО, или номер и серия паспорта;
  • суррогатным – не существует в реальном мире, например какой-то порядковый номер, который существует только в базе данных.

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

Связь между таблицами

Первостепенная задача первичного ключа – это уникальная идентификация каждой строки. Но первичный ключ может решить ещё одну задачу. В базе данных есть возможность связывания нескольких таблиц. Для такой связи используют первичный и внешний ключ sql. В одной из таблиц создают внешний ключ, который ссылается на поля другой таблицы. Но внешний ключ не может ссылаться на любые поля другой таблицы, а может ссылаться только на определённые:

  • эти поля должны присутствовать и в ссылающейся таблице и в той таблице на которую он ссылается;
  • ссылается внешний ключ из одной таблицы обычно на первичный ключ другой таблицы.

Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:

Таблица pupils

И есть таблица “Успеваемость” (evaluations):

Таблица evaluations

В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.

Первичным ключом в нашем случае может выступать поле “ФИО” в таблице “Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.

Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:

  • составной первичный ключ – например, в качестве первичного ключа взять два поля: ФИО и Класс;
  • суррогатный первичный ключ – в таблице “Ученики” добавить поле “№ Ученика” и сделать это поле первичным ключом;
  • добавить более уникальное поле – например, можно использовать уникальный номер зачетной книжки и использовать новое поле в качестве первичного ключа;

Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.

Практика

Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:

postgres=# CREATE DATABASE school;
CREATE DATABASE

postgres=# \c school
You are now connected to database "school" as user "postgres".

school=# CREATE TABLE pupils
(full_name text,
age integer,
class varchar(3),
PRIMARY KEY (full_name)
);
CREATE TABLE

school=# \dt pupils
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | pupils | table | postgres
(1 row)

school=# \d pupils
                       Table "public.pupils"
  Column   |         Type         | Collation | Nullable | Default
-----------+----------------------+-----------+----------+---------
 full_name | text                 |           | not null |
 age       | integer              |           |          |
 class     | character varying(3) |           |          |
Indexes:
    "pupils_pkey" PRIMARY KEY, btree (full_name)

Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.

Вывод команды \d нам показал, что у нас в таблице есть первичный ключ. А также первичный ключ сделал два ограничения:

  • поле full_name, к которому относится первичный ключ не может быть пустым, это видно в колонки Nullablenot null;
  • для поля full_name был создан индекс pupils_pkey с типом btree. Про типы индексов и про сами индексы расскажу в другой статье.

Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.

Следующим шагом создадим таблицу evaluations:

school=# CREATE TABLE evaluations
(item text,
full_name text,
evaluation integer,
FOREIGN KEY (full_name) REFERENCES pupils ON DELETE CASCADE
);
CREATE TABLE

school=# \d evaluations
              Table "public.evaluations"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 item       | text    |           |          |
 full_name  | text    |           |          |
 evaluation | integer |           |          |
Foreign-key constraints:
    "evaluations_full_name_fkey" FOREIGN KEY (full_name) REFERENCES pupils(full_name) ON DELETE CASCADE

В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.

Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.

Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.

Заполнение таблиц и работа с ними

Заполним таблицу “pupils“:

school=# INSERT into pupils (full_name, age, class)
VALUES ('Иванов Иван Иванович', 15, '9A'),
('Сумкин Фёдор Андреевич', 15, '9A'),
('Петров Алексей Николаевич', 14, '8B'),
('Булгаков Александр Геннадьевич', 14, '8B');
INSERT 0 4

Заполним таблицу “evaluations“:

school=# INSERT into evaluations (item, full_name, evaluation)
VALUES ('Русский язык', 'Иванов Иван Иванович', 4),
('Русский язык', 'Петров Алексей Николаевич', 5),
('Математика', 'Булгаков Александр Геннадьевич', 3),
('Литература', 'Сумкин Фёдор Андреевич', 5);
INSERT 0 4

А теперь попробуем поставить оценку не существующему ученику:

school=# INSERT into evaluations (item, full_name, evaluation)
VALUES ('Русский язык', 'Угаров Виктор Михайлович', 3);
ERROR:  insert or update on table "evaluations" violates foreign key constraint "evaluations_full_name_fkey"
DETAIL:  Key (full_name)=(Угаров Виктор Михайлович) is not present in table "pupils".

Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.

Теперь удалим какого-нибудь ученика из таблицы pupils:

school=# delete from pupils WHERE full_name = 'Иванов Иван Иванович';
DELETE 1

И посмотрим на строки в таблице evaluations:

school=# SELECT * FROM evaluations;
     item     |           full_name            | evaluation
--------------+--------------------------------+------------
 Русский язык | Петров Алексей Николаевич      |          5
 Математика   | Булгаков Александр Геннадьевич |          3
 Литература   | Сумкин Фёдор Андреевич         |          5
(3 rows)

Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.

Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:

school=# INSERT into pupils (full_name, age, class)
VALUES ('Петров Алексей Николаевич',15, '5B');

ERROR:  duplicate key value violates unique constraint "pupils_pkey"
DETAIL:  Key (full_name)=(Петров Алексей Николаевич) already exists.

Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.

Составной первичный ключ

Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.

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

school=# DROP table evaluations;
DROP TABLE

school=# DROP table pupils;
DROP TABLE

school=# CREATE TABLE pupils
(full_name text,
age integer,
class varchar(3),
PRIMARY KEY (full_name, class)
);
CREATE TABLE

school=# CREATE TABLE evaluations
(item text,
full_name text,
class varchar(3),
evaluation integer,
FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE
);
CREATE TABLE

Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.

Теперь посмотрим на структуры этих таблиц:

school=# \d pupils
                       Table "public.pupils"
  Column   |         Type         | Collation | Nullable | Default
-----------+----------------------+-----------+----------+---------
 full_name | text                 |           | not null |
 age       | integer              |           |          |
 class     | character varying(3) |           | not null |
Indexes:
    "pupils_pkey" PRIMARY KEY, btree (full_name, class)
Referenced by:
    TABLE "evaluations" CONSTRAINT "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE

school=# \d evaluations
                     Table "public.evaluations"
   Column   |         Type         | Collation | Nullable | Default
------------+----------------------+-----------+----------+---------
 item       | text                 |           |          |
 full_name  | text                 |           |          |
 class      | character varying(3) |           |          |
 evaluation | integer              |           |          |
Foreign-key constraints:
    "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE

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

Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:

school=# INSERT INTO pupils (full_name, age, class)
VALUES ('Гришина Ольга Константиновна', 12, '5A'),
('Гришина Ольга Константиновна', 14, '7B');
INSERT 0 2

school=# SELECT * FROM pupils;
          full_name           | age | class
------------------------------+-----+-------
 Гришина Ольга Константиновна |  12 | 5A
 Гришина Ольга Константиновна |  14 | 7B
(2 rows)

И также по второй таблице:

school=# INSERT INTO evaluations (item, full_name, class, evaluation)
VALUES ('Русский язык', 'Гришина Ольга Константиновна', '5A', 5),
('Русский язык', 'Гришина Ольга Константиновна', '7B', 3);
INSERT 0 2

school=# SELECT * FROM evaluations;
     item     |          full_name           | class | evaluation
--------------+------------------------------+-------+------------
 Русский язык | Гришина Ольга Константиновна | 5A    |          5
 Русский язык | Гришина Ольга Константиновна | 7B    |          3
(2 rows)

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

Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:

school=# DROP table pupils;
ERROR:  cannot drop table pupils because other objects depend on it
DETAIL:  constraint evaluations_full_name_class_fkey on table evaluations depends on table pupils
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Поэтому удалим наши таблицы в следующем порядке:

school=# DROP table evaluations;
DROP TABLE

school=# DROP table pupils;
DROP TABLE

Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:

school=# CREATE TABLE pupils
(full_name text,
age integer,
class varchar(3),
PRIMARY KEY (full_name, class)
);
CREATE TABLE

school=# CREATE TABLE evaluations
(item text,
full_name text,
class varchar(3),
evaluation integer,
FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE
);

school=# DROP TABLE pupils CASCADE;
NOTICE:  drop cascades to constraint evaluations_full_name_class_fkey on table evaluations
DROP TABLE

school=# \d
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | evaluations | table | postgres
(1 row)

school=# \d evaluations
                     Table "public.evaluations"
   Column   |         Type         | Collation | Nullable | Default
------------+----------------------+-----------+----------+---------
 item       | text                 |           |          |
 full_name  | text                 |           |          |
 class      | character varying(3) |           |          |
 evaluation | integer              |           |          |

Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.

Создание связи в уже существующих таблицах

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

Вначале удалим оставшуюся таблицу:

school=# DROP table evaluations;
DROP TABLE

И сделаем таблицы без ключей:

school=# CREATE TABLE pupils
(full_name text,
age integer,
class varchar(3)
);
CREATE TABLE

school=# CREATE TABLE evaluations
(item text,
full_name text,
class varchar(3),
evaluation integer
);
CREATE TABLE

Теперь создадим первичный ключ в таблице pupils:

school=# ALTER TABLE pupils ADD PRIMARY KEY (full_name, class);
ALTER TABLE

И создадим внешний ключ в таблице evaluations:

school=# ALTER TABLE evaluations ADD FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE;
ALTER TABLE

Посмотрим что у нас получилось:

school=# \d pupils
                       Table "public.pupils"
  Column   |         Type         | Collation | Nullable | Default
-----------+----------------------+-----------+----------+---------
 full_name | text                 |           | not null |
 age       | integer              |           |          |
 class     | character varying(3) |           | not null |
Indexes:
    "pupils_pkey" PRIMARY KEY, btree (full_name, class)
Referenced by:
    TABLE "evaluations" CONSTRAINT "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE

school=# \d evaluations
                     Table "public.evaluations"
   Column   |         Type         | Collation | Nullable | Default
------------+----------------------+-----------+----------+---------
 item       | text                 |           |          |
 full_name  | text                 |           |          |
 class      | character varying(3) |           |          |
 evaluation | integer              |           |          |
Foreign-key constraints:
    "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE

Установка

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

Первый шаг – установить SQL

Мы будем использовать PostgreSQL (Postgres) достаточно распространенный SQL диалект. Для этого откроем страницу загрузки, выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.

Следующий шаг – установка pgAdmin

pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки, выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).

После установки обоих компонентов открываем pgAdmin и нажимаем Add new server. На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.

Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).

Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home Databases (1) postgres и далее выбираем Query Tool.

🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами

Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).

        DROP TABLE IF EXISTS classrooms CASCADE;

CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);
    

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

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

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).

Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id)
);
    

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

Невозможно вставить данные, поскольку в таблице классов нет записи с id = 1
        INSERT INTO students
    (name, classroom_id)
VALUES
    ('Matt', 1);

/*
ERROR: insert or update on table "students" violates foreign
    key constraint "fk_classrooms"
DETAIL: Key (classroom_id)=(1) is not present in table
    "classrooms".
SQL state: 23503
*/
    

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

        INSERT INTO classrooms
    (teacher)
VALUES
    ('Mary'),
    ('Jonah');

SELECT * FROM classrooms;

/*
 id | teacher
 -- | -------
  1 | Mary
  2 | Jonah
*/
    

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

        INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

Но что же случится, если у нас появится новый ученик, которому ещё не назначили класс? Неужели нам придется ждать, пока станет известно в каком он классе, и только после этого добавить его запись в базу данных?

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

        -- явно определим значение NULL
INSERT INTO students
    (name, classroom_id)
VALUES
    ('Dina', NULL);

-- неявно определим значение NULL
INSERT INTO students
    (name)
VALUES
    ('Evan');

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
  4 | Dina     |       [null]
  5 | Evan     |       [null]
*/
    

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

        DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;

CREATE TABLE assignments (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    category VARCHAR(20),
    name VARCHAR(200),
    due_date DATE,
    weight FLOAT
);

CREATE TABLE grades (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    assignment_id INT,
    score INT,
    student_id INT,
    CONSTRAINT fk_assignments
        FOREIGN KEY(assignment_id)
        REFERENCES assignments(id),
    CONSTRAINT fk_students
        FOREIGN KEY(student_id)
        REFERENCES students(id)
);
    

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data).

        COPY assignments(category, name, due_date, weight)
FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 5
Query returned successfully in 118 msec.
*/

COPY grades(assignment_id, score, student_id)
FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 25
Query returned successfully in 64 msec.
*/
    

Теперь давайте проверим, что мы всё сделали верно. Напишем запрос, который покажет среднюю оценку, по каждому виду заданий с группировкой по учителям.

        SELECT
    c.teacher,
    a.category,
    ROUND(AVG(g.score), 1) AS avg_score
FROM
    students AS s
INNER JOIN classrooms AS c
    ON c.id = s.classroom_id
INNER JOIN grades AS g
    ON s.id = g.student_id
INNER JOIN assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    1,
    2
ORDER BY
    3 DESC;

/*
 teacher | category  | avg_score
 ------- | --------- | ---------
 Jonah   |  project  |     100.0
 Jonah   |  homework |      94.0
 Jonah   |  exam     |      92.5
 Mary    |  homework |      78.3
 Mary    |  exam     |      76.0
 Mary    |  project  |      69.5
*/
    

Отлично! Мы установили, настроили и наполнили базу данных.

***

Итак, в этой статье мы научились:

  • создавать базу данных;
  • наполнять таблицы данными;
  • устанавливать связи между таблицами;

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

В следующей части мы разберем:

  • виды фильтраций в запросах;
  • запросы с условиями типа if-else;
  • новые виды соединений таблиц;
  • функции для работы с массивами;

Итог

В этой статье я рассказал про первичный и внешний ключ sql. А также продемонстрировал, как можно создать связанные между собой таблицы и как создать связь между уже существующими таблицами. Вы узнали, какие ограничения накладывает первичный ключ и какие задачи он решает. И вдобавок, какие требования предъявляются к нему. Вместе с тем я показал вам как работать с составным первичным ключом.

Дополнительно про первичный и внешний ключ sql можете почитать тут.