Как связать таблицы sql

Как связать таблицы sql

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

Общий синтаксис установки внешнего ключа на уровне столбца:

[FOREIGN KEY] REFERENCES главная_таблица (столбец_главной_таблицы) [ON DELETE ] [ON UPDATE ]

Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY , но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE .

Общий синтаксис установки внешнего ключа на уровне таблицы:

FOREIGN KEY (стобец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE ] [ON UPDATE ]

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date );

Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

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

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса «FK_»:

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

В данном случае ограничение внешнего ключа CustomerId называется «FK_Orders_To_Customers».

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции:

• CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
• NO ACTION : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
• SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
• SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.

Каскадное удаление

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

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE )

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

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );

Установка значения по умолчанию

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT )

Как связать таблицы естественным соединением и вывести только строки по условию?

Нужно вывести те продукты, которые покупались 2-мя или более клиентами. Пробую связать таблицы, это продукты (ном. продукта, наименование, описание) и клиенты (ном. кл., имя). Надо так же учесть таблицы: заказы (дата зак., дата дост., ном сч.) и позиции (ном заказа, цена покуп., ном. продукта). По заданию надо использовать естественное соединение таблиц. т.е. без указания колонок.
Пробую так:

SELECT "НАИМЕНОВАНИЕ", COUNT("НОМ_КЛ") "КОЛИЧЕСТВО" FROM STUD."КЛИЕНТЫ", STUD."ПРОДУКТЫ", STUD."ЗАКАЗЫ", STUD."ПОЗИЦИИ" GROUP BY "НАИМЕНОВАНИЕ" HAVING COUNT("НОМ_КЛ")>=2; 

но не получается, выводится много строк. Вот структура таблиц и данные
Отслеживать
51.6k 200 200 золотых знаков 61 61 серебряный знак 242 242 бронзовых знака
задан 11 ноя 2019 в 8:57
Prroll Jaguar Prroll Jaguar
57 8 8 бронзовых знаков
Уточни: откуда вывести, какая бд, что ты уже сделал, что нужно вывести.
11 ноя 2019 в 9:02
Что молодой человек, совсем никак? Добавте в вопрос структуру таблиц и тестовые данные.
11 ноя 2019 в 20:57

Можете добавить данные на db<>fiddle. Таблицы я там уже создал, дополните или поправте, если что-то не так.

11 ноя 2019 в 21:59

Нет, не сохранилось — ORA-00972: identifier is too long, потому, что кириллица. Сделайте сначало на латинице, как показал по ссылке выше, там только данные осталось добавитъ: 3-и продукта, три клиента, и по заказам раскидатъ. Потом переделаете на кириллицу, если потребуют, для решения это никакой роли не играет.

12 ноя 2019 в 11:15
Вот таблицы и данные к вопросу. — ссылка
14 ноя 2019 в 14:35

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

В non-ANSI синтаксисе ( table1, table2 ) нет естественного соединения таблиц, надо всегда указывать колонки для соединения явно. Используете ANSI синтаксис (natural join).

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

select * from ( select nom_kl, name, nom_zak, data_zak, nom_prod, naimenovanie, kol_vo, count (distinct nom_kl) over (partition by nom_prod) kol_kl from klient k natural join zakaz z natural join position p natural join produkt) where kol_kl >= 2; 

Рабочий пример с данными на db<>fiddle.

Строка для подсчёта клиентов, которые купили продукт, работает так:
Аналитическая (или оконная) ф-я count посчитает в наборе строк для каждого продукта ( partition by ), кол-во клиентов ( nom_kl ) без повторений ( distinct ), которые в этом наборе строк встречаются.

Как связать таблицы sql

Есть локальная база Access и удалённая MS SQL Server. Необходимо получить таблицу которая — результат связывания таблиц из этих баз данных.

09.01.06 11:17: Перенесено модератором из ‘.NET’ — TK
Re: Как связать таблицы из разных баз данных

От:	TK	кывт.рф
Дата:	08.01.06 21:17
Оценка:

Hello, «bunches»
> Есть локальная база Access и удалённая MS SQL Server. Необходимо получить таблицу которая — результат связывания таблиц из этих баз данных.

Как SQL Server так и Access, позволяют линковать к базе данных данные из разных источников. Достаточно сделать такую связь в Access или в SQL а дальше, получить нужную информацию используя SQL

Posted via RSDN NNTP Server 2.0
Если у Вас нет паранойи, то это еще не значит, что они за Вами не следят.
Re[2]: Как связать таблицы из разных баз данных

От:	bunches
Дата:	08.01.06 21:38
Оценка:

Здравствуйте, TK, Вы писали:

TK>Hello, «bunches»
>> Есть локальная база Access и удалённая MS SQL Server. Необходимо получить таблицу которая — результат связывания таблиц из этих баз данных.

TK>Как SQL Server так и Access, позволяют линковать к базе данных данные из разных источников. Достаточно сделать такую связь в Access или в SQL а дальше, получить нужную информацию используя SQL

Возможно ли поконкретнее.

Re[3]: Как связать таблицы из разных баз данных

От:	TK	кывт.рф
Дата:	08.01.06 21:41
Оценка:

Hello, «bunches»

> TK>Как SQL Server так и Access, позволяют линковать к базе данных данные из разных источников. Достаточно сделать такую связь в Access или в SQL а дальше, получить нужную информацию используя SQL
>
> Возможно ли поконкретнее.

Для SQL надо смотреть на OPENQUERY для Access в меню File

Posted via RSDN NNTP Server 2.0
Если у Вас нет паранойи, то это еще не значит, что они за Вами не следят.
Re: Как связать таблицы из разных баз данных

От:	U-4X-96
Дата:	08.01.06 22:13
Оценка:	6 (1)

Здравствуйте, bunches, Вы писали:

B>Есть локальная база Access и удалённая MS SQL Server. Необходимо получить таблицу которая — результат связывания таблиц из этих баз данных.

Если я правильно понял задачю:
Есть таблица А в SQL
Есть цвязаная таблица B в Access
Нужно получить Dataset с обоими таблицами, впричем со связю.
Решение:
Создаем Dataset, обе таблизы и сбязь в нем.
Создаем два Конекта и два Адаптера.
Последовательно заполняем Dataset, вначале Главную таблицу потом связаную.
Ели имеются перекресные связи лудше всего временно отключить проверку связий

Если нужно получит таблицу вроде INNER JOIN, то создаем третью таблицу в Dataset и перебором в начали главной потом потчиненой заполняем ее.

Если речь идет о связовании на стороне сервера, то это явно не тот форум.

Re[2]: Как связать таблицы из разных баз данных

От:	Аноним
Дата:	08.01.06 23:38
Оценка:

Здравствуйте, U-4X-96, Вы писали:

U49>Здравствуйте, bunches, Вы писали:

B>>Есть локальная база Access и удалённая MS SQL Server. Необходимо получить таблицу которая — результат связывания таблиц из этих баз данных.

U49>Если я правильно понял задачю:
U49>Есть таблица А в SQL
U49>Есть цвязаная таблица B в Access
U49>Нужно получить Dataset с обоими таблицами, впричем со связю.
U49>Решение:
U49>Создаем Dataset, обе таблизы и сбязь в нем.
U49>Создаем два Конекта и два Адаптера.
U49>Последовательно заполняем Dataset, вначале Главную таблицу потом связаную.
U49>Ели имеются перекресные связи лудше всего временно отключить проверку связий

U49>Если нужно получит таблицу вроде INNER JOIN, то создаем третью таблицу в Dataset и перебором в начали главной потом потчиненой заполняем ее.

U49>Вот и делов то.

Дела именно так и обстоят.
Нужно связывание типа OUTER JOIN. Понял всё, кроме заполнения третей таблицы. Можно разъяснить?

Re[3]: Как связать таблицы из разных баз данных

От:	U-4X-96
Дата:	09.01.06 00:14
Оценка:

Здравствуйте, Аноним, Вы писали:

А>Дела именно так и обстоят.
А>Нужно связывание типа OUTER JOIN. Понял всё, кроме заполнения третей таблицы. Можно разъяснить?

1. Пишеш на бумаге предполагаеммый запросс(чем проже тем лудше).
2. Создаеш в Dataset таблицу совпадаию по полям с результатом запроса.
3. Пишеш програму которая берет данные из двух первых таблиц, обрабатывает согласно запросу написаному на бумаге и помещает результат в третью.
Если предполагается сложный запрос, спрашиваеш(на другом форуме) людей как из MS-SQL получить доступ к MsJet(Access) и делаеш все на сервере.

Re: Как связать таблицы из разных баз данных

От:	RegisteredUser
Дата:	09.01.06 04:36
Оценка:

Здравствуйте, bunches, Вы писали:

B>Есть локальная база Access и удалённая MS SQL Server. Необходимо получить таблицу которая — результат связывания таблиц из этих баз данных.

Из MSSQL можно сделать примерно так

Re[4]: Как связать таблицы из разных баз данных

От:	bunches
Дата:	10.01.06 20:29
Оценка:

Здравствуйте, U-4X-96, Вы писали:

U49>Здравствуйте, Аноним, Вы писали:

А>>Дела именно так и обстоят.
А>>Нужно связывание типа OUTER JOIN. Понял всё, кроме заполнения третей таблицы. Можно разъяснить?

U49>1. Пишеш на бумаге предполагаеммый запросс(чем проже тем лудше).
U49>2. Создаеш в Dataset таблицу совпадаию по полям с результатом запроса.
U49>3. Пишеш програму которая берет данные из двух первых таблиц, обрабатывает согласно запросу написаному на бумаге и помещает результат в третью.
U49>Если предполагается сложный запрос, спрашиваеш(на другом форуме) людей как из MS-SQL получить доступ к MsJet(Access) и делаеш все на сервере.
Спасибо, всё получилось.

Как связать таблицы из разных баз данных

От:	Аноним
Дата:	10.01.06 20:38
Оценка:

>Есть локальная база Access и удалённая MS SQL Server. Необходимо получить таблицу которая — результат связывания таблиц из этих баз данных.

Ну раз все получилось, я рад за вас

Как связать таблицы sql

Начнем с создания таблицы классов ( 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 и больше.

Невозможно вставить данные, поскольку в таблице классов нет записи с >

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;
• новые виды соединений таблиц;
• функции для работы с массивами;

Материалы по теме

• �� 8 лучших GUI клиентов PostgreSQL в 2021 году
• ���� Python и MySQL: практическое введение
• ����️ Управление данными с помощью Python, SQLite и SQLAlchemy