KoderLine
KoderLine
Обслуговування i продаж
програмного забезпечення

Статті експертів

Корисна інформація

Основи SQL. Урок №1

0
130
01.08.2021

1. Бази даних Підприємства 8.3

2. З'єднання двох таблиць

3. Кілька простих прикладів в системі Підприємство 8


1.   Бази даних Підприємства 8.3

Ця стаття написана з метою на простих доступних прикладах показати, що являє собою SQL (Structured Query Language) - структурована мова запитів.

 

База даних - це структурований набір постійно збережених даних.

У реляційній базі даних інформація зберігається у вигляді двовимірних таблиць.

 

В якості ілюстрації бази даних Підприємства 8.3 розглянемо адресну книгу. У ній міститься безліч записів, які відповідають окремим співробітникам. Для кожного співробітника визначені дані: ім'я, номер телефону, адреса. Формат адресної книги - таблиця з рядками і стовпцями. Рядки відповідають співробітникам, а стовпці містять ім'я, номер телефону та адресу.

 

Співробітники

 

Ім’я

Адреса

Місто

Бельдиєв

3-я вул. Будівельників

Москва

Васіна

вул. Виноградна

Петербург

Петренко

вул. Вишнева

Тула

 

Але як бути, якщо є два співробітника з однаковим ім'ям? А в базі даних можуть міститися тисячі і мільйони записів.

Для вирішення даного завдання необхідно присвоїти кожному співробітнику унікальний ідентифікатор. Таке значення, яке має бути унікальним для кожного співробітника, називається первинним ключем в базі (primary key). Кожна створювана таблиця бази даних повинна мати первинний ключ. Він служить для логічної ідентифікації окремих рядків. Додамо стовпець для первинного ключа в нашу таблицю.

 

Співробітники

 

ID_Num

Ім’я

Адреса

Місто

1007

Бельдиєв

3-я вул. Будівельників

Москва

1008

Васіна

вул. Виноградна

Петербург

1010

Петренко

вул. Вишнева

Тула

 

Створивши кілька таблиць з взаємозалежною інформацією, ви зможете виконувати над своїми даними більш складні операції.

 

Припустимо, в таблиці Співробітники потрібно додати стовпець з номерами телефонів. Більшість співробітників мають як мінімум два телефонних номери - домашній і робочий, але ж їх може бути і більше.

Рішенням є побудова іншої таблиці, назвемо її Телефони. Перший стовпець міститиме номер телефону, другий - опис типу номера (домашній, робочий і т.д.). Зрозуміло, при цьому потрібно «зв'язати» номер телефону в його власником (з першої таблиці) Тому необхідно знайти спосіб встановлення зв'язку між таблицями.

Для цього можна помістити в таблицю Телефони первинний ключ з таблиці Співробітники. Ці номери є унікальними, тому можна точно визначити, яким співробітнику відповідає даний номер.

 

Телефони

 

Id_Num

Телефон

Тип

1007

4156479772

Домашній

1008

7074568232

Робочий

1008

7079402092

Домашній

 

Стовпець id_num в таблиці Телефони називається зовнішнім ключем (foreign key). Кажуть, що він посилається на первинний ключ таблиці Співробітники. Ключі, на які посилаються зовнішні ключі, називаються батьківськими (parent) ключами. Якщо всі значення зовнішнього ключа таблиці Телефони посилаються на значення, які дійсно присутні в таблиці Співробітники, то система володіє посилальною цілісністю (referential integrity).

Для таблиці Телефони також необхідний первинний ключ - його повинна мати кожна таблиця. Ми не можемо використовувати id_num, оскільки він не є унікальним (повторюється для телефонних номерів одного співробітника, як показано в таблиці для співробітника 1008 по імені Васіна).

Первинний або зовнішній ключ необов'язково повинен складатися з одного стовпчика. Можна скомбінувати стовпці id_num і phone. Така комбінація буде унікальною. Отже, комбінація id_num і phone є логічним первинним ключем для таблиці Телефони. Ключ, що складається більш ніж з одного стовпчика, в різних СУБД називається складовим (composite).

 

2.   З’єднання двох таблиць

 

Тепер потрібно зв'язати другу таблицю з першою так, щоб номерам телефонів відповідала інформація про клієнтів. Наявність в одній з таблиць зовнішнього ключа, значення якого збігаються зі значеннями первинного ключа іншої таблиці, є достатньою умовою для визначення того, які рядки таблиць пов'язані (відносяться до одного співробітника). При цьому обов'язково тип даних зовнішнього ключа вихідної таблиці повинен збігатися з типом даних первинного ключа таблиці, з якої відбувається з'єднання при з'єднанні двох таблиць.

Операція вилучення інформації з бази даних називається запитом (query).

 

У 70-х роках XX століття, була створена спеціальна мова SEQUEL, що дозволяла відносно просто управляти даними в цій СУБД. Абревіатура SEQUEL розшифровувалась як Structured English QUEry Language - «структурована англійська мова запитів».

 

Метою розробки було створення простої непроцедурної мови, що дозволяє будь-якому користувачеві, що навіть не має навичок програмування, вилучати дані з бази.

 

Пізніше вона була перейменована на SQL - Structured Query Language - структурована мова запитів. Пізніше, з прийняттям стандартів, SQL перетворилася на самостійну мову, застосовувану в розробці баз даних.

 

В основному SQL-запити реалізуються за допомогою оператора SELECT. Запит, що вилучає дані з більш ніж однієї таблиці шляхом зіставлення стовпців однієї таблиці стовпцям інших таблиць, називається з'єднанням (join).

У загальному вигляді синтаксис мінімальної команди такий:

 

SELECT імена полів таблиць FROM імена таблиць

 

В Підприємстві 8 зазвичай пишуть запити Підприємства 8.3 в російськомовному синтаксисі:

 

ВИБРАТИ імена полів таблиць З імена таблиць

 

Таким чином у результаті ми отримуємо таблицю, але таку, що вже складається з тих рядків і стовпців вихідних таблиць, які нам необхідні. Група взаємопов'язаних таблиць називається схемою (schema).

 

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 

3.   Кілька простих прикладів в системі Підприємство 8

 

Система Підприємство 8 Створимо довідники Співробітники та Міста. Заповнимо їх даними.

 

Співробітники

ID_E

Найменування

ID_C

1

Бельдиєв

3

2

Васіна

1

3

Петренко

2

4

Петров

2

5

Рахімов

3

 

Міста

ID_C

Найменування

1

Москва

2

Тула

3

Білгород

 

 

Тут зв'язок між таблицями Співробітники - Міста, характеризується як Багато до Одного (m: 1), тобто кожен співробітник може жити тільки в одному місті.

Якби співробітник міг жити в декількох містах, то зв'язок був би Багато до Багатьох (m: m).

Якщо в одному місті може проживати не більше одного співробітника, то зв'язок виходить Один до Одного (1: 1)

 

 

Відкриємо консоль запитів. Створимо за допомогою Конструктора запиту наступний запит: виберемо Найменування співробітників з довідника Співробітники.



ВИБРАТИ

               Співробітники.Найменування ЯК Найменування

З

               Довідник. Співробітники ЯК Співробітники

 

Найменування

Бельдиєв

Васіна

Петров

Рахімов

Петренко

 

Замінимо службові слова в запиті на оригінал мови SQL:

 

SELECT

Співробітники.Найменування AS Найменування

FROM

Довідник. Співробітники AS Співробітники

 

Результат отримуємо той же.

 

А що станеться, якщо ми виберемо всі поля з перших двох таблиць, але не з'єднаємо їх по ключовим полям. Тобто напишемо наступний запит:

 

ВИБРАТИ

Співробітники.Найменування ЯК Співробітник,

Міста.Найменування ЯК Місто

З

Довідник.Співробітники ЯК Співробітники,

Довідник.Міста ЯК Міста

 

Якщо вибрати всі поля послідовно з обох таблиць, то отримаємо 5 * 3 = 15 рядків.

 

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

 

Спочатку буде вибрано поле Найменування першого запису таблиці Співробітники

 

Бельдыев

 

Потім поруч, в цьому ж рядку, буде виведено поле Найменування таблиці Місто

 

Бельдиєв

Москва

 

Здавалося б, наступний рядок (запис) повинен починатись з наступного співробітника таблиці Співробітники, але рядки (записи) другої таблиці ще не закінчилися. А оскільки руху по таблиці Співробітники ще немає, новий рядок почнеться з вибраного раніше значення поля Найменування таблиці Співробітники

 

Бельдиєв

Москва

Бельдиєв

 

Рух же по таблиці Міста триває, тому буде вибрано поле Найменування наступного рядка (запису) цієї таблиці

Бельдиєв

Москва

Бельдиєв

Тула

 

Алгоритм визначений. Тепер стає зрозумілим наступний рядок результуючої таблиці:

 

Бельдиєв

Москва

Бельдиєв

Тула

Бельдиєв

Білгород

 

Записи таблиці Міста закінчилися. Відбувається повернення до таблиці Співробітники, де записи ще не закінчилися, тому вибирається наступний рядок, поле Найменування

 

Бельдиєв

Москва

Бельдиєв

Тула

Бельдиєв

Білгород

Васіна

 

Оскільки другим полем йдуть міста, а всі значення вже пройдені, то покажчик повертається до першого запису таблиці Міста

 

Бельдиєв

Москва

Бельдиєв

Тула

Бельдиєв

Білгород

Васіна

Москва

 

Для значення Васіна, вибираються всі міста, що залишилися

 

Бельдиєв

Москва

Бельдиєв

Тула

Бельдиєв

Білгород

Васіна

Москва

Васіна

Тула

Васіна

Білгород

 

Тепер виведемо всю результуючу таблицю:

 

Співробітники

Города

Бельдиєв

Москва

Бельдиєв

Тула

Бельдиєв

Білгород

Васіна

Москва

Васіна

Тула

Васіна

Білгород

Петров

Москва

Петров

Тула

Петров

Білгород

Рахімов

Москва

Рахімов

Тула

Рахімов

Білгород

Петренко

Москва

Петренко

Тула

Петренко

Білгород

 

Для кожного співробітника (5 співробітників) виводяться всі міста (3 міста), в результаті виходить 5 * 3 = 15 записів в результуючій таблиці.

З цієї таблиці зовсім незрозуміло, хто в якому місті живе.

 

Якщо вибрати спочатку міста, то на кожне місто виводяться всі співробітники з другої таблиці (Співробітники).

 

ВИБРАТИ

Міста.Найменування ЯК Місто,

Співробітники.Найменування ЯК Співробітник

З

Довідник.Міста ЯК Міста,

Довідник.Співробітники ЯК Співробітники

 

Місто

Співробітник

Москва

Бельдиєв

Москва

Васіна

Москва

Петров

Москва

Рахімов

Москва

Петренко

Тула

Бельдиєв

Тула

Васіна

Тула

Петров

Тула

Рахімов

Тула

Петренко

Білгород

Бельдиєв

Білгород

Васіна

Білгород

Петров

Білгород

Рахімов

Білгород

Петренко

 

Кількість записів в результаті 3 * 5 = 15. Від зміни розташування таблиць результат не змінюється!

 

Тепер з'єднаємо таблиці по ключовому полю, завбачливо для цього створеному: Код міста = ID_C

 

ВИБРАТИ

Співробітники.Найменування ЯК Співробітник,

Міста.Найменування ЯК Місто

З

Довідник.Міста ЯК Міста,

ЛІВЕ З'ЄДНАННЯ Довідник.Співробітники ЯК Співробітники

ПО (Співробітники.ID_C = Міста.ID_C)

 

Співробітник

Місто

Васіна

Москва

Петров

Тула

Петренко

Тула

Бельдиєв

Білгород

Рахімов

Білгород

 

Звідси відразу стає видно, хто де проживає.

 

Додамо ще двох співробітників в таблицю Співробітники і задамо їм коди міст, яких немає в таблиці Міста.

 

ID_E

Співробітник

ID_C

1

Бельдиєв

3

2

Васіна

1

3

Петренко

2

4

Петров

2

5

Рахімов

3

6

Мансуров

5

7

Хабібулін

7




Звернемо увагу на зв'язок. Рядок "Міста Ліве" означає, що з таблицею Міста відбувається ліве з'єднання іншої таблиці. У даному випадку вона знаходиться на рядок вище і є більш пріоритетною. Це таблиця Співробітники. Пріоритетність цієї таблиці виражається в більш близькому розташуванні до кореневого елементу Таблиці. А менш пріоритетна таблиця Міста розкривається з елемента Співробітники. Таким чином виділений рядок означає, що таблиця Міста розташована ... справа.

 

Обов'язково! в умові з'єднання

 

Співробітники.ID_C = Міста.ID_С

 

типи порівнюваних полів повинні бути повністю ідентичні!

 

Якщо зліва у нас тип Рядок (10), то справа теж повинен бути тип Рядок (10).

Наприклад, якщо зліва тип Рядок (10), а справа стоїть тип Рядок (9), то це з'єднання некоректне, навіть якщо в порівнюваних полях по 9 символів!

 

Яке б з'єднання ми не використовували, з'єднуються завжди дві таблиці. Будь навіть справа вкладена таблиця з ще декількома таблицями. Це виконується для запиту будь-якої складності.

 

ЛІВЕ З'ЄДНАННЯ найбільш поширене серед усіх видів з'єднань. Пояснюється це тим, що текст ми читаємо зліва направо, і першу таблицю ми розташовуємо на початку, а потім розташовуємо наступну таблицю. Нам же не прийде в голову розташувати першу таблицю справа, це незручно.

 

Нам не важливо, чи є відповідність міста якомусь співробітнику. Нам принципово важливо, щоб з таблиці Співробітники були обрані всі співробітники, незалежно від того, чи є у них відповідність поля з кодом міста в таблиці Міста.

 

ВИБРАТИ

Співробітники.Найменування ЯК Найменування,

Міста.Найменування ЯК Найменування1

З

Довідник.Співробітники ЯК Співробітники

ЛІВЕ З'ЄДНАННЯ Довідник.Міста ЯК Міста

ПО (Співробітники.ID_C = Міста.ID_С)

 

Найменування

Найменування1

Бельдиєв

Білгород

Васіна

Москва

Петров

Тула

Рахімов

Білгород

Петренко

Тула

Мансуров

Хабібулін

 

У результаті видно, що для нових двох співробітників немає міст, тобто в таблиці Міста немає міст з кодом ID_C рівним 5 і 7. Який же тип полів, у яких немає міст? Типу у цих полів немає. Тип у них - невизначений. У SQL цей тип позначається як NULL.

 

Найменування

Найменування1

Бельдиєв

Білгород

Васіна

Москва

Петров

Тула

Рахімов

Білгород

Петренко

Тула

Мансуров

NULL

Хабібулін

NULL

 

Виконаємо таку дію: у запиті в умові з'єднання поміняємо місцями порівнювані поля Міста.ID_С = Співробітники.ID_C

 

ВИБРАТИ

Співробітники.Найменування ЯК Найменування,

Міста.Найменування ЯК Найменування1

З

Довідник.Співробітники ЯК Співробітники

ЛІВЕ З'ЄДНАННЯ Довідник.Міста ЯК Міста

ПО (Міста.ID_С = Співробітники.ID_C)

 

Як бачимо, результат запиту не змінився.

 

Тепер створимо запит, де нам важливо вивести всі міста.




ВИБРАТИ

Міста.Найменування ЯК Найменування1,

Співробітники.Найменування ЯК Найменування

З

Довідник.Міста ЯК Міста

ЛІВЕ З'ЄДНАННЯ Довідник.Співробітники ЯК Співробітники

ПО (Міста.ID_С = Співробітники.ID_C)

 

Найменування1

Найменування

Москва

Васіна

Тула

Петров

Тула

Петренко

Білгород

Бельдиєв

Білгород

Рахімов

 

У результаті виводяться всі міста, а співробітники, у яких код міста відсутній у таблиці Міста, не виводяться. Оскільки містам Тула і Білгород відповідає більше одного співробітника - вони дублюються. При цьому умова виконується - кожне місто виводиться мінімум 1 раз.

 

Перевіримо це наступним чином: перепризначимо для співробітників Петров і Петренко код міста = 1 (Москва). Тобто місту Тула не відповідає жоден співробітник. І виконаємо запит ще раз.

Найменування1

Найменування

Москва

Васіна

Москва

Петров

Москва

Петренко

Тула

Білгород

Бельдиєв

Білгород

Рахімов

 

Умова виконується! Кожне місто виводиться мінімум один раз, а оскільки місту Тула ніхто не відповідає, то йому відповідає поле з типом NULL.

 

Поміняємо ЛІВЕ З'ЄДНАННЯ на ПРАВЕ З'ЄДНАННЯ

 

ВИБРАТИ

Співробітники.Найменування ЯК Найменування,

Міста.Найменування ЯК Найменування1

 

З

Довідник.Міста ЯК Міста

ПРАВЕ З'ЄДНАННЯ Довідник.Співробітники ЯК Співробітники

ПО (Міста.ID_С = Співробітники.ID_C)

 

Найменування

Найменування 1

Бельдиєв

Білгород

Васіна

Москва

Петров

Москва

Рахімов

Білгород

Петренко

Москва

Мансуров

Хабібулін

 

Співробітники виводяться всі, місто Тула не виводиться - йому не відповідає жоден співробітник, а пріоритет у таблиці Співробітники.

 

Розглянемо зв'язок таблиць запиту під час правому з'єднанні.

 



Виділений рядок означає, що ми з’єднуємося з таблицею Співробітники правим з'єднанням. Тобто інша таблиця (в нашому випадку таблиця Міста) розташовується тепер справа! А зліва розташовується таблиця Співробітники. Таким експериментальним шляхом дізнаємося, що пріоритет віддається таблиці, що розташовується все одно зліва. Це видно по результату запиту.

 

ПРАВЕ З'ЄДНАННЯ зручно використовувати при налагодженні запиту, коли треба поміняти пріоритет таблиць. Це набагато швидше, ніж переписувати все з'єднання. Також воно використовується в інших окремих випадках (це тут ми розглядати не будемо).

 

////////////////////////////////////////////////// ////////////////////////////////////////////////// //////////

 

Ще один приклад

 

Є три таблиці A (3 записи), B (4 записи) і C (5 записів). Якщо з'єднуємо таблиці A і B, то немає необхідності з'єднувати A з B і одночасно A з C, оскільки після з'єднання таблиць A і B результат можна розглядати як таблицю. І досить одного з'єднання таблиці C з таблицею (A-B). Для цього з'єднуємо C з A АБО C з B.

 

Якщо виводити всі таблиці A, B і C без зв'язків, то отримуємо 3 * 4 * 5 = 60 записів.

 

Нехай зв'язок між таблицями буде Один до Одного (1: 1), тоді:

Якщо з'єднуємо (A-B) за пріоритетом A, отримаємо таблицю (AB) = 3 записи. Додавши таблицю C (без з'єднання) отримуємо 3 * 5 = 15 записів. Якщо з'єднаємо (AB) і С за пріоритетом таблиці (AB) отримаємо 3 записи.

 

Якщо з'єднуємо (A-B) за пріоритетом B, отримаємо таблицю (BA) = 4 записи. Додавши таблицю C (без з'єднання) отримуємо 4 * 5 = 20 записів. Якщо з'єднаємо (BA) і С за пріоритетом таблиці (BA) отримаємо 4 записи.

 

На практиці ще накладаються різні фільтри у вигляді умов, що змінює кількість записів як самих таблиць, так і їх з'єднань.

 

Завдання для самостійного вирішення

 

Завдання 1.

Що являє собою запит?

Розкажіть про первинний ключ і зовнішній ключ.

 

Завдання 2.

Виберіть поля Найменування і ID_C з таблиці Співробітники

 

Завдання 3.

З'єднайте таблицю Міста з таблицею Співробітники правим з'єднанням по полю ID_C. При цьому виведіть поля ID_C і Найменування з таблиці Міста і тільки Найменування з таблиці Співробітники

 

Завдання 4.

З'єднайте таблицю Співробітники з таблицею Міста лівим з'єднанням по полю ID_C, при цьому виведіть поля ID_E, Найменування з таблиці Співробітники і тільки Найменування з таблиці Міста

 

Завдання 5.

Додайте місто Астрахань з кодом 7.

Видаліть з таблиці Співробітники прізвища Петренко і Петров.

Додайте в таблицю Співробітники прізвища Мішина, код міста = 7,

Федоров, код міста = 3.

 

З'єднайте таблиці Співробітники та Міста лівим з'єднанням, виведіть усі поля.

 

///////////////////////////////////////////////////////////

 

Є 3 таблиці: A (3 записи), B (5 записів), С (7 записів).

 

Завдання 1.

Скільки буде записів в результуючій таблиці, отриманій шляхом вибору всіх полів, усіх таблиць, не пов'язаних між собою?

 

Завдання 2.

Ті ж 3 таблиці. Зв'язок між таблицями Один до Одного (1: 1)

 

З'єднуємо таблицю A з таблицею B лівим з'єднанням, скільки записів у результуючій таблиці?

З'єднуємо таблицю A з таблицею B правим з'єднанням, скільки записів у результуючій таблиці?

 

Завдання 3.

Ті ж 3 таблиці. Зв'язок між таблицями Один до Одного (1: 1)

 

Вибираємо дані з таблиць A і C без зв'язку. З'єднуємо отриману таблицю з таблицею B лівим з'єднанням. Скільки записів буде в результуючій таблиці? А якщо правим?

 

Завдання 4.

Ті ж 3 таблиці. Зв'язок між таблицями Один до Одного (1: 1)

 

З'єднуємо таблицю С і B лівим з'єднанням. Таблицю А з'єднуємо з отриманою таблицею правим з'єднанням. Скільки в підсумку записів? А лівим з'єднанням?

 

Завдання 5. SuperTask

Ті ж 3 таблиці. Зв'язок між таблицями B і A Багато до Одного (m: 1). Таблиця C пов'язана з A і B (1: 1)

У таблиці B є тільки два записи, що відносяться до одного і того ж елементу таблиці A.

І в таблиці B є один запис, не пов'язаний ні з чим.

З'єднуємо таблицю B з A лівим з'єднанням.

До отриманої таблиці (BA) з'єднуємо таблицю C правим з'єднанням.

Скільки записів у результаті?

Відповідь дати без створення таблиць у базі.

 

Відповіді будуть дані в уроці №2.

 


Спеціаліст компанії ООО «Кодерлайн»

Ільдар Мінгалеєв


 


Добавить комментарий
Message Text*
Spam bot protection (CAPTCHA)
Load image