Как правильно связать две таблицы?
У меня есть две таблицы связанные отношением один-к-одному. Я правильно понимаю, что при добавлении нового пользователя, я сначала должен добавить его в таблицу Authenticztion`, узнать под каким LoginId он добавился и лишь потом добавлять запись в таблицу Clients, где и укажу полученный LoginId? Исправьте меня, пожалуйста, если я делаю что то не так или же можно сделать более разумно и правильнее.
Отслеживать
задан 11 окт 2021 в 19:48
Объединение таблиц с помощью операторов Join и Keep
Объединение — операция объединения двух таблиц в одну. Записи результирующей таблицы представляют собой комбинации записей в исходных таблицах. При этом две такие записи, составляющие одну комбинацию в результирующей таблице, как правило, имеют общее значение одного или нескольких общих полей. Такое объединение называется естественным. В программе Qlik Sense объединение может выполняться в скрипте, создавая логическую таблицу.
Таблицы, которые находятся в скрипте, можно объединять. Логика Qlik Sense будет распознавать не отдельные таблицы, а результаты объединения, которые будут представлены в одной внутренней таблице. В некоторых случаях это требуется, однако существуют недостатки:
- Загруженные таблицы часто становятся больше, и программа Qlik Sense работает медленнее.
- Некоторая информация может быть потеряна: частота (количество записей) в исходной таблице может быть больше недоступна.
Функция Keep , которая позволяет уменьшить одну или обе таблицы до пересечения данных таблиц перед сохранением таблиц в программу Qlik Sense , предназначена для уменьшения количества случаев, когда необходимо использовать явные объединения.
Примечание к информации В данном руководстве термин «объединение» обычно используется для объединений, выполненных до создания внутренних таблиц. Однако ассоциация, выполненная после создания внутренних таблиц, по сути, также является объединением.
Объединения внутри оператора SQL SELECT
При использовании некоторых драйверов ODBC можно выполнять объединение внутри оператора SELECT . Это практически эквивалентно созданию объединения с помощью префикса Join .
Однако большинство драйверов ODBC не позволяют сделать полное внешнее объединение (двунаправленное). Они позволяют сделать только левостороннее или правостороннее внешнее объединение. Левостороннее (правостороннее) внешнее объединение включает только сочетания, в которых в левой (правой) таблице существует ключ объединения. Полное внешнее объединение включает все сочетания. Программа Qlik Sense автоматически создает полное внешнее объединение.
Более того, создание объединений в операторах SELECT значительно сложнее, чем создание объединений в программе Qlik Sense .
[Order Details].ProductID, [Order Details].
UnitPrice, Orders.OrderID, Orders.OrderDate, Orders.CustomerID
RIGHT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;
Этот оператор SELECT позволяет объединить таблицу, содержащую заказы несуществующей компании, и таблицу, содержащую сведения о заказах. Это правостороннее внешнее объединение, то есть будут включены все записи OrderDetails и записи со значением OrderID , которое отсутствует в таблице Orders . Однако заказы, содержащиеся в таблице Orders , но не содержащиеся в OrderDetails , не будут включены.
Join
Самым простым способом создания объединения является использование префикса Join в скрипте, который позволяет объединять внутреннюю таблицу с другой именованной таблицей или последней созданной таблицей. Объединение будет внешним и позволит создать все возможные сочетания значений из двух таблиц.
LOAD a, b, c from table1.csv;
join LOAD a, d from table2.csv;
Результирующая внутренняя таблица имеет поля a , b , c и d . Количество записей различается в зависимости от значений полей этих двух таблиц.
Примечание к информации Имена объединяемых полей должны совпадать. Количество объединяемых полей может быть любым. Обычно в таблицах должно быть одно или несколько общих полей. При отсутствии общих полей будет рассматриваться декартово произведение таблиц. В принципе все поля могут быть общими, однако обычно в этом нет смысла. Пока имя ранее загруженной таблицы не будет указано в операторе Join , префиксом Join будет использоваться последняя созданная таблица. Поэтому порядок двух операторов не является произвольным.
Для получения дополнительной информации см. Join.
Keep
Явный префикс Join в скрипте загрузки данных выполняет полное объединение двух таблиц. В результате получается одна таблица. Во многих случаях такие объединения приводят к созданию очень больших таблиц. Одной из основных функций программы Qlik Sense является способность к связыванию таблиц вместо их объединения, что позволяет сократить использование памяти, повысить скорость обработки и гибкость. Функция keep предназначена для сокращения числа случаев необходимого использования явных объединений.
Префикс Keep между двумя операторами LOAD или SELECT приводит к уменьшению одной или обеих таблиц до пересечения их данных перед сохранением таблиц в программе Qlik Sense . Перед префиксом Keep следует задать одно из ключевых слов: Inner , Left или Right . Выборка записей из таблицы осуществляется так же, как и при соответствующем объединении. Однако две таблицы не объединяются и сохраняются в программе Qlik Sense в виде двух отдельных именованных таблиц.
Для получения дополнительной информации см. Keep.
Inner
Перед префиксами Join и Keep в скрипте загрузки данных можно использовать префикс Inner .
При использовании этого префикса перед префиксом Join объединение двух таблиц будет внутренним. Полученная таблица содержит только сочетания из двух таблиц, включающие полный набор данных с обеих сторон.
Если этот префикс используется перед Keep , он указывает, что две таблицы следует уменьшить до области взаимного пересечения, прежде чем они смогут быть сохранены в программе Qlik Sense .
В этих таблицах используются исходные таблицы Table1 и Table2 :
| A | B |
|---|---|
| 1 | aa |
| 2 | cc |
| 3 | ee |
| A | C |
|---|---|
| 1 | xx |
| 4 | yy |
Inner Join
Сначала выполняется Inner Join в отношении таблиц, в результате чего образуется таблица VTable , содержащая только одну строку, только одну запись, существующую в обеих таблицах, с данными из обеих таблиц.
SELECT * from Table1;
inner join SELECT * from Table2;
| A | B | C |
|---|---|---|
| 1 | aa | xx |
Inner Keep
Если вместо этого выполняется Inner Keep , таблиц все равно будет две. Две таблицы связаны посредством общего поля A .
SELECT * from Table1;
inner keep SELECT * from Table2;
| A | B |
|---|---|
| 1 | aa |
| A | C |
|---|---|
| 1 | xx |
Для получения дополнительной информации см. Inner.
Left
Перед префиксами Join и Keep в скрипте загрузки данных можно использовать префикс left .
При использовании этого префикса перед префиксом Join объединение двух таблиц будет левосторонним. Полученная таблица содержит только сочетания из двух таблиц, включающие полный набор данных из первой таблицы.
Если этот префикс используется перед префиксом Keep , он указывает, что вторую таблицу следует уменьшить до области взаимного пересечения с первой таблицей перед сохранением в программе Qlik Sense .
В этих таблицах используются исходные таблицы Table1 и Table2 :
| A | B |
|---|---|
| 1 | aa |
| 2 | cc |
| 3 | ee |
| A | C |
|---|---|
| 1 | xx |
| 4 | yy |
Сначала выполняется Left Join в отношении таблиц, в результате чего образуется таблица VTable , содержащая все строки из таблицы Table1 , совмещенные с полями из совпадающих строк в таблице Table2 .
SELECT * from Table1;
left join SELECT * from Table2;
| A | B | C |
|---|---|---|
| 1 | aa | xx |
| 2 | cc | — |
| 3 | ee | — |
Если вместо этого выполняется Left Keep , таблиц все равно будет две. Две таблицы связаны посредством общего поля A .
SELECT * from Table1;
left keep SELECT * from Table2;
| A | B |
|---|---|
| 1 | aa |
| 2 | cc |
| 3 | ee |
| A | C |
|---|---|
| 1 | xx |
Для получения дополнительной информации см. Left.
Right
Перед префиксами Join и Keep в скрипте загрузки данных можно использовать префикс right .
При использовании этого префикса перед префиксом Join объединение двух таблиц будет правосторонним. Полученная таблица содержит только сочетания из двух таблиц, включающие полный набор данных из второй таблицы.
Если этот префикс используется перед префиксом Keep , он указывает, что первую таблицу следует уменьшить до области взаимного пересечения со второй таблицей перед сохранением в программе Qlik Sense .
В этих таблицах используются исходные таблицы Table1 и Table2 :
| A | B |
|---|---|
| 1 | aa |
| 2 | cc |
| 3 | ee |
| A | C |
|---|---|
| 1 | xx |
| 4 | yy |
Сначала выполняется Right Join в отношении таблиц, в результате чего образуется таблица VTable , содержащая все строки из таблицы Table2 , совмещенные с полями из совпадающих строк в таблице Table1 .
SELECT * from Table1;
right join SELECT * from Table2;
| A | B | C |
|---|---|---|
| 1 | aa | xx |
| 4 | — | yy |
Если вместо этого выполняется Right Keep , таблиц все равно будет две. Две таблицы связаны посредством общего поля A .
SELECT * from Table1;
right keep SELECT * from Table2;
| A | B |
|---|---|
| 1 | aa |
| A | C |
|---|---|
| 1 | xx |
| 4 | yy |
Для получения дополнительной информации см. Right.
SQL-Урок 10. Сочетание таблиц (INNER JOIN)
Наиболее мощной особенностью языка SQL является возможность сочетания различных таблиц в оперативной памяти СУБД при выполнении запросов. Сочетания очень часто используются для анализа данных. Как правило, данные находятся в разных таблицах, что позволяет их более эффективно хранить (поскольку информация не дублируется), упрощает обработку данных и позволяет масштабировать базу данных (возможно добавлять новые таблицы с дополнительной информацией).
Таблицы баз данных, используемых в СУБД MS Access являются реляционными таблицами, то есть все таблицы можно связать между собой по общим полям.
1. Создание сочетания таблиц (JOINS)
Сочетание таблиц очень простая процедура. Следует указать все таблицы, которые будут включены в сочетание и «объяснить» СУБД, как они будут связаны между собой. Соединение производится с помощью слова WHERE, например:
Run SQLSELECT DISTINCT Seller_name, Product FROM Sellers, Sumproduct WHERE Sellers.City = Sumproduct.City
Try it Yourself
Соединив две таблицы, мы смогли увидеть какие товары реализует каждый продавец. Рассмотрим код запроса более подробно, поскольку он немного отличается от обычного запроса. Оператор SELECT начинается с указанием столбцов, которые мы хотим вывести, однако эти поля находятся в разных таблицах, предложение FROM содержит две таблицы, которые мы хотим соединить в операторе SELECT, таблицы сочетаются с помощью слова WHERE. Обязательно нужно указывать полное название поля (Таблица.Поле), поскольку поле City имеется в обеих таблицах.
2. Внутреннее соединение (INNER JOIN)
В предыдущем примере для сочетания таблиц мы использовали слово WHERE, которое проверяет на основе эквивалентности двух таблиц. Сочетание такого типа называется также «внутренним сочетанием». Существует также и другой способ сочетания таблиц, явно указывающий на тип сочетания. Рассмотрим следующий пример:
Run SQLSELECT DISTINCT Seller_name, Product FROM Sellers INNER JOIN Sumproduct ON Sellers.City = Sumproduct.City
Try it Yourself
В этом запросе вместо WHERE мы использовали конструкцию INNER JOIN. ON. которая дала аналогичный результат. Несмотря на то, что сочетание с предложением WHERE является короче, все же лучше использовать INNER JOIN, поскольку она является более гибкой, о чем будет подробнее рассказано в следующих разделах.
- Изменение регистра букв в тексте
- Сумма прописью на украинском языке
- Поиск латиницы в кириллице и наоборот
- Транслитерация с украинского на английский
Глава 8. ЗАПРАШИВАНИЕ НЕСКОЛЬКИХ ТАБЛИЦ
ТАК ЖЕ, КАК ОДНОЙ
До этого каждый запрос, который мы рассматривали, основывался на одиночной таблице. В этой главе вы узн́аете, как сделать запрос любого числа таблиц с помощью одной команды. Это чрезвычайно мощное средство, потому что оно не только объединяет вывод из многочисленных таблиц, но и определяет связи между ними. Вы обучитесь различным формам, использующим эти связи, а также их настройке и использованию, чтобы удовлетворять возможным специальным требованиям.
ОБЪЕДИНЕНИЕ ТАБЛИЦ
Одна из наиболее важных особенностей запросов SQL — их способность определять связи между многочисленными таблицами и выводить информацию из них, в терминах этих связей, всю внутри одной команды. Этот вид операции называется объединением, которое является одним из видов операций в реляционных базах данных. Как установлено в Главе 1, главное в реляционном подходе это связи, которые можно создавать между позициями данных в таблицах. Используя объединения, мы непосредственно связываем информацию с любым числом таблиц и таким образом способны создавать связи между сравнимыми фрагментами данных. При объединении, таблицы, представленные списком в предложении FROM, отделяются запятыми. Предикат запроса может ссылаться к любому столбцу любой связанной таблицы и, следовательно, может использоваться для связи между ими. Обычно предикат сравнивает значения в столбцах различных таблиц, чтобы определить, удовлетворяет ли WHERE установленному условию.
ИМЕНА ТАБЛИЦ И СТОЛБЦОВ
Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого точкой, и затем имени столбца. Вот несколько примеров имён:
Salespeople.snum Salespeople.city Orders.odate
До этого вы могли опускать имена таблиц, потому что вы запрашивали единовременно только одну таблицу, а SQL достаточно интеллектуален, чтобы присвоить соответствующий префикс имени таблицы. Даже когда вы делаете запрос нескольких таблиц, вы ещё можете опускать имена таблиц, если все их столбцы имеют различные имена. Но так бывает не всегда. Например, мы имеем две типовые таблицы со столбцами, называемыми city. Если мы должны связать эти столбцы (кратковременно), мы должны будем указать их с именами Salespeople.city или Customers.city, чтобы SQL мог их различать.
СОЗДАНИЕ ОБЪЕДИНЕНИЯ
Предположим, что вы хотите поставить в соответствие вашему продавцу ваших заказчиков в городе, в котором они живут, поэтому вы увидите все комбинации продавцов и заказчиков для этого города. Вы должны будете брать каждого продавца и искать в таблице Заказчиков всех заказчиков того же самого города. Вы могли бы сделать это, введя следующую команду (вывод показан на Рисунке 8.1):
SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city; =============== SQL Execution Log ============ | SELECT Customers.cname, Salespeople.sname, | | Salespeople.city | | FROM Salespeople, Customers | | WHERE Salespeople.city = Customers.city | | ============================================= | | cname cname city | | ------- -------- ---- | | Hoffman Peel London | | Hoffman Peel London | | Liu Serres San Jose | | Cisneros Serres San Jose | | Hoffman Motika London | | Clemens Motika London | ============================================= Рисунок 8.1 Объединение двух таблиц
Так как это city имеется и в таблице Продавцов, и таблице Заказчиков, имена таблиц должны использоваться как префиксы. Хотя это необходимо, только когда два или более полей имеют одно и то же имя, в любом случае это хорошая идея: включать имя таблицы в объединение для лучшего понимания и непротиворечивости. Несмотря на это, мы будем в наших примерах далее использовать имена таблиц только тогда, когда необходимо, так что будет ясно, когда они необходимы, а когда нет. Что SQL в основном делает в объединении, так это исследует каждую комбинацию строк двух или более возможных таблиц и проверяет эти комбинации по их предикатам. В предыдущем примере требовалась строка продавца Peel из таблицы Продавцов и объединение её с каждой строкой таблицы Пользователей, по одной в каждый момент времени. Если комбинация производит значение, которое делает предикат верным, и если поле city из строк таблиц Заказчика равно London, то Peel — это то запрашиваемое значение, которое комбинация выберет для вывода. То же самое будет затем выполнено для каждого продавца в таблице Продавцов (у некоторых из которых не было никаких заказчиков в этих городах).
ОБЪЕДИНЕНИЕ ТАБЛИЦ ЧЕРЕЗ СПРАВОЧНУЮ ЦЕЛОСТНОСТЬ
Эта особенность часто используется просто для эксплуатации связей, встроенных в БД. В предыдущем примере мы установили связь между двумя таблицами в объединении. Это прекрасно. Но эти таблицы уже были соединены через snum-поле. Эта связь называется состоянием справочной целостности, как мы уже говорили в Главе 1. Используя объединение, можно извлекать данные в терминах этой связи. Например, чтобы показать имена всех заказчиков, соответствующих продавцам, которые их обслуживают, мы будем использовать такой запрос:
SELECT Customers.cname, Salespeople.sname FROM Customers, Salespeople WHERE Salespeople.snum = Customers.snum;
Вывод этого запроса показан на Рисунке 8.2. Это пример объединения, в котором столбцы используются для определения предиката запроса, и в этом случае snum-столбцы из обеих таблиц удалены из вывода. И это прекрасно. Вывод показывает, какие заказчики каким продавцом обслуживаются; значения поля snum, которые устанавливают связь, отсутствуют. Однако, если вы введёте их в вывод, то вы должны или удостовериться, что вывод понятен сам по себе, или должны обеспечить комментарий данных при выводе.
=============== SQL Execution Log ============ | SELECT Customers.cname, Salespeople.sname, | | FROM Salespeople, Customers | | WHERE Salespeople.snum = Customers.snum | | ============================================= | | cname sname | | ------- -------- | | Hoffman Peel | | Giovanni Axelrod | | Liu Serres | | Grass Serres | | Clemens Peel | | Cisneros Rifkin | | Pereira Motika | ============================================= Рисунок 8.2 Объединение продавцов с их заказчикам
ОБЪЕДИНЕНИЕ ТАБЛИЦ ПО РАВЕНСТВУ ЗНАЧЕНИЙ
В СТОЛБЦАХ И ДРУГИЕ ВИДЫ ОБЪЕДИНЕНИЙ
Объединения, которые используют предикаты, основанные на равенствах, называются объединениями по равенству. Все наши примеры в этой главе до настоящего времени относились именно к этой категории, потому что все условия в предложениях WHERE базировались на математических выражениях, использующих знак равенства (=). Строки ‘city = ‘London’ и ‘Salespeople.snum = Orders.snum ‘ — примеры таких типов равенств, найденных в предикатах. Объединения по равенству это, вероятно, наиболее общий вид объединения, но имеются и другие. Вы можете использовать практически любую реляционную операцию в объединении. Здесь дан пример другого вида объединения (вывод показан на Рисунке 8.3):
SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200; =============== SQL Execution Log ============ | SELECT sname, cname | | FROM Salespeople, Customers | | WHERE sname < cname | | AND rating < 200; | | ============================================= | | sname cname | | -------- ------- | | Peel Pereira | | Motika Pereira | | Axelrod Hoffman | | Axelrod Clemens | | Axelrod Pereira | | | ============================================= Рисунок 8.3 Объединение, основанное на неравенстве
Эта команда не часто бывает полезна. Она воспроизводит все комбинации имени продавца и имени заказчика так, что первый предшествует последнему в алфавитном порядке, а последний имеет оценку, меньше чем 200. Обычно вы не создаёте сложных связей, подобно этой, и по этой причине вы, вероятно, будете строить наиболее общие объединения по равенству, но вы должны хорошо знать и другие возможности.
ОБЪЕДИНЕНИЕ БОЛЕЕ ДВУХ ТАБЛИЦ
Вы можете также создавать запросы, объединяющие более двух таблиц. Предположим, что мы хотим найти все заказы заказчиков, не находящихся в тех городах, где находятся их продавцы. Для этого необходимо связать все три наши типовые таблицы (вывод показан на Рисунке 8.4):
SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Customers.city < >Salespeople.city AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum; =============== SQL Execution Log ============== | | | SELECT onum, cname, Orders.cnum, Orders.snum | | FROM Salespeople, Customers, Orders | | WHERE Customers.city < >Salespeople.city | | AND Orders.cnum = Customers.cnum | | AND Orders.snum = Salespeople.snum; | | =============================================== | | onum cname cnum snum | | ------ ------- ----- ----- | | 3001 Cisneros 2008 1007 | | 3002 Pereira 2007 1004 | | 3006 Cisneros 2008 1007 | | 3009 Giovanni 2002 1003 | | 3007 Grass 2004 1002 | | 3010 Grass 2004 1002 | =============================================== Рисунок 8.4 Объединение трёх таблиц
Хотя эта команда выглядит скорее как комплексная, вы можете следовать за логикой, просто проверяя, что заказчики не размещены в тех городах, где размещены их продавцы (совпадение двух snum полей), и что перечисленные заказы выполнены с помощью этих заказчиков (совпадение заказов с полями cnum и snum в таблице Заказов).
РЕЗЮМЕ
Теперь вы больше не ограничиваетесь просмотром одной таблицы в каждый момент времени. Кроме того, вы можете делать сложные сравнения между любыми полями любого количества таблиц и использовать полученные результаты, чтобы решать, какую информацию вы хотели бы видеть. Фактически эта методика настолько полезна для построения связей, что она часто используется для создания их внутри одиночной таблицы. Это будет правильным: вы сможете объединить таблицу с собой, а это очень удобна вещь. Это будет темой Главы 9.
РАБОТА СО SQL
Напишите запрос, который вывел бы список номеров заказов сопровождающихся именем заказчика, который создавал эти заказы.
Напишите запрос, который выдавал бы имена продавца и заказчика для каждого заказа после номера заказа.
Напишите запрос, который выводил бы всех заказчиков, обслуживаемых продавцом с комиссионными выше 12%. Выведите имя заказчика, имя продавца и ставку комиссионных продавца.
Напишите запрос, который вычислил бы сумму комиссионных продавца для каждого заказа заказчика с оценкой выше 100.