У цій статті ми спробуємо розглянути все, що стосується ключів у SQL:навіщо потрібні, створення, обмеження ключів. Загалом: буде нудно 😉
План на сьогодні такий:
Теоретично реляційних баз даних ключіце деякі сутності, створені задля встановлення певних обмежень, які підтримують цілісність і доступність даних у таблицях баз даних.
Якщо казати простими словами, то ключі в sqlстворено для того, щоб вказати додаткову функціональність стовпця. Будь то унікальність або те, що стовпець посилається на іншу таблицю (зовнішній ключ).
Первинний ключ
Стовпець, який у базі даних має бути унікальним позначають первинним ключем. Первинний ключ або primary key означає, що у таблиці значення колонки primary key може повторюватися. Таким чином даний ключдозволяє однозначно ідентифікувати запис у таблиці не побоюючись у своїй, що значення стовпця повторитися. Відразу приклад: припустимо, у Вас є таблиця користувачів. У цій таблиці є поля: ПІБ, рік народження, телефон. Як ідентифікувати користувача? Таким параметрам як ПІБ та телефон довіряти не можна. Адже у нас може бути кілька користувачів не лише з однаковим прізвищем, а й з ім'ям. Телефон може змінюватися з часом і користувач з номером телефону може виявитися не тим, хто у нас у базі даних.
Ось для цього і вигадали первинний ключ. Одного разу надали унікальний ідентифікатор і все. У mySqlна прикладі якої ми виконуємо всі приклади з поля AUTO_INCREMENTне можна задати, якщо не вказати, що це первинний ключ.
Думаю, що не варто згадувати, що поле позначене як первинний ключ не може бути порожнім під час створення запису.
Зовнішній ключ ( foreign key)
Є ще зовнішній ключ (foreign key). Його ще називають посилальним. Він необхідний зв'язування таблиць між собою.
Якщо подивитися на картинку вище, то зовнішнім ключем буде поле постачальника в таблиці взуття. Як правило, під час створення таблиці задають колонку унікальних цілочисельних значень. Як ми це робили коли створювали таблицю supplier.
Колонка supplier_idбуде унікальним для кожного запису. Її значення і виступатиме на помсті колонки постачальникв таблиці взуття.Пропоную відразу і розглянути на прикладі, як створюється зовнішній ключ.
Створення зовнішнього ключа
create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, foreign key (supplier) references supplier (supplier_id));
Як видно на прикладі вище, синтаксис створення зовнішнього ключа досить простий. Потрібно до таблиці додати поле, а потім оголосити це поле як зовнішній ключ і вказати, куди він посилатиметься. В даному випадку поле supplierбуде посилатися на поле supplier_idв таблиці supplier.
Складовий ключ (Composite key)
Щодо складеного ключа — це кілька первинних ключів у таблиці. Таким чином, створивши composite key, унікальність запису буде перевірятися по полях, об'єднаних у цей ключ.
Трапляються ситуації, коли при вставці в таблицю потрібно перевіряти запис на унікальність відразу по кількох полях. Ось для цього і придуманий складовий ключ. Для прикладу я створю просту таблицю з composite key , щоб показати синтаксис:
Create table test(field_1 int, field_2 text, field_3 bigint, primary key (field_1, field_3));
У прикладі вище два поля об'єднані в складовий ключ і таблиці не буде записів з цими однаковими полями.
Це все, що стосується ключів у SQL.Це невеликий посібник – підготовка до статті, де ми докладно розглянемо як об'єднувати таблиці, щоб вони становили єдину базу даних.
) ми розбирали, як влаштовано реляційну (табличну) базу даних і з'ясували, що основними елементами реляційної бази даних є: таблиці, стовпці та рядки, а в математичних поняттях: відносини, атрибути та кортежі. Також часто рядки називають записами, стовпці називають колонками, а перетин запису і колонки називають осередком.
Важливо згадати, що зміст рядка та назви стовпців мають бути унікальними в межах однієї бази даних.
Типи даних у базах
Важливо розуміти, що можна створювати бази будь-яких типів даних: текстів, дат, часу, подій, цифр. Залежно від типу інформації, реляційні бази даних ділять на типи. Кожен тип даних (атрибут) має своє позначення:
- INTEGER-дані з цілих чисел;
- FLOAT - дані з дробових чисел, так звані дані з плаваючою точкою;
- CHAR, VARCHAR - текстові типи даних (символьні);
- LOGICAL - логічний тип даних (так/ні);
- DATE/TIME – тимчасові дані.
Це основні типи даних, яких насправді набагато більше. Причому кожна мова програмування має свій набір системних атрибутів (типів даних).
Що таке первинний ключ та зовнішній ключ таблиць реляційних баз даних
Первинний ключ
Вище ми згадували: кожен рядок (запис) БД має бути унікальним. Саме первинний ключ у вигляді наборів певних значень максимально ідентифікують кожен запис. Можна визначити інакше. Первинний ключ: набір певних ознак, унікальних кожного запису. Позначається первинний ключ як primary key.
Primary key (PK)дуже важливий кожної таблиці. Поясню чому.
- Primary key не дозволяє створювати однакових записів (рядків) у таблиці;
- PK забезпечують логічний зв'язок між таблицями однієї бази даних (для реляційних БД).
На логічному зв'язку між таблицями, варто зупинитися докладніше.
Ключ зовнішній
Foreign key, коротко FK.Забезпечує однозначний логічний зв'язок між таблицями однієї БД.
Наприклад, є дві таблиці А і В. У таблиці А (взуття) є первинний ключ: розмір, в таблиці В (колір) повинна бути колонка з назвою розмір. У цій таблиці «розмір» і буде зовнішній ключ для логічного зв'язку таблиць і А.
Більш складний приклад.
Дві таблиці даних: Люди та номери телефонів.
Таблиця: Люди
Таблиця: Номери телефонів
У таблиці Номери телефонів PK є унікальним. FK цієї таблиці є PK таблиці Люди. Зв'язок між телефонними номерами та людьми забезпечує FK таблиці телефонів. Тобто:
- У Зайцева два телефони;
- У Волкова два телефони;
- У Бєлкіна один телефон.
На завершення додам, що будь-яка , управляюча базою даних, має технічні можливостіскласти первинний ключ.
PRIMARY KEY— первинний ключ, обмеження, що дозволяє однозначно ідентифікувати кожен запис у таблиці SQL.
PRIMARY KEY Oracle
Первинний Ключ ( PRIMARY KEY) може обмежувати таблиці чи його стовпці. Це обмеження працює так само, як і обмеження UNIQUE. Але слід враховувати різницю між первинними ключами і унікальністю стовпців у способі їх використання із зовнішніми ключами. Первинні ключі не можуть дозволяти значення NULL. Це означає, що, подібно до поля в обмеженні UNIQUE, будь-яке поле, що використовується в обмеженні PRIMARY KEY, має бути оголошено NOT NULL.
PRIMARY KEY Oracle. Приклад №1.
Приклад створення таблиці SQLз обмеженням PRIMARY KEY:
Student
(Kod_stud integer NOT NULL PRIMARY KEY,
Fam char(30) NOT NULL UNIQUE,
Adres char(50),
Ball decimal);
Найкраще поміщати обмеження PRIMARY KEYу полі (або поля), яке буде утворювати унікальний ідентифікатор рядка, та зберегти обмеження UNIQUE для полів які мають бути унікальними логічно (такі як номери телефону або поле sname), а не для ідентифікації рядків. Обмеження PRIMARY KEYможе також бути застосовано для численних полів, що становлять унікальну комбінацію значень:
PRIMARY KEY Oracle. Приклад №2.
CREATE TABLE Student
(Fam char (30) NOT NULL,
Im char (30) NOT NULL
Adres char (50),
PRIMARY KEY(Fam, Im));
PRIMARY KEY MySQL
PRIMARY KEY SQL / MySQL. Приклад №3.
CREATE TABLE Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY(P_Id));
PRIMARY KEY SQL / MySQL. Приклад №4.
CREATE TABLE`ad_packages` (
`id` int(111) NOT NULL auto_increment,
`title` varchar(132) NOT NULL default »,
`price` float NOT NULL default ‘0’,
`type` varchar(22) NOT NULL default »,
`c_type` enum('cash','points','rur') NOT NULL default 'cash',
PRIMARY KEY(`id`)
);
PRIMARY KEY SQL / MySQL. Приклад №5.
CREATE TABLE `gamestat` (
`id` int(11) NOT NULL auto_increment,
`game` varchar(10) NOT NULL default ‘tuz’,
`stavok` int(11) NOT NULL default ‘0’,
`usd` float NOT NULL default ‘0’,
`rur` float NOT NULL default ‘0’,
`point` float NOT NULL default ‘0’,
`bank_usd` decimal(12,2) NOT NULL default ‘0.00’,
`bank_rur` decimal(12,2) NOT NULL default ‘0.00’,
`bank_point` decimal(12,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`id`)
);
Типи даних власними силами обмежують безліч даних, які можна зберегти у таблиці. Однак для багатьох програм такі обмеження занадто грубі. Наприклад, стовпець, що містить ціну продукту, повинен, ймовірно, набувати лише позитивних значень. Але такого стандартного типуДанних нема. Можливо, ви також захочете обмежити дані стовпця по відношенню до інших стовпців або рядків. Наприклад, у таблиці з інформацією про товар має бути лише один рядок із певним кодом товару.
Для вирішення подібних задач SQL дозволяє визначати обмеження для стовпців і таблиць. Обмеження дають можливість управляти даними в таблицях так, як ви захочете. Якщо користувач спробує зберегти в стовпці значення, що порушує обмеження, виникне помилка. Обмеження діятимуть, навіть якщо це значення за промовчанням.
5.3.1. Обмеження-перевірки
Обмеження-перевірка - найбільш загальний типобмежень. У його визначенні ви можете вказати, що значення даного стовпця має задовольняти логічного виразу(Перевірці істинності). Наприклад, ціну товару можна обмежити позитивними значеннями так:
Name text, price numeric CHECK (price > 0) );
Як бачите, обмеження визначається після типу даних, як і значення за промовчанням. Значення за промовчанням та обмеження можуть вказуватись у будь-якому порядку. Обмеження-перевірка складається з ключового слова CHECK, за яким йде вираз у дужках. Цей вислів повинен включати стовпець, для якого задається обмеження, інакше воно не має великого сенсу.
Ви також можете призначити обмеження окреме ім'я. Це покращить повідомлення про помилки та дозволить вам посилатися на це обмеження, коли вам знадобиться змінити його. Зробити це можна так:
CONSTRAINT positive_price CHECK (price > 0));
Тобто, щоб створити іменоване обмеження, напишіть ключове слово CONSTRAINT , а за ним ідентифікатор та власне визначення обмеження. (Якщо ви не визначите ім'я обмеження таким чином, система вибере ім'я за вас.)
Обмеження-перевірка може також посилатися кілька стовпців. Наприклад, якщо ви зберігаєте звичайну ціну та ціну зі знижкою, то ви можете гарантувати, що ціна зі знижкою буде завжди меншою за звичайну:
CREATE TABLE products (product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );
Перші два обмеження визначаються схожим чином, але третього використовується новий синтаксис. Воно не пов'язане з певним стовпцем, а представлене окремим елементом у списку. Визначення стовпців і такі визначення обмежень можна переставляти у довільному порядку.
Про перші два обмеження можна сказати, що це обмеження шпальт, тоді як третє є обмеженням таблиці, так як воно написано окремо від ухвал стовпців. Обмеження стовпців також можна записати у вигляді обмежень таблиці, тоді як зворотне не завжди можливе, тому що мається на увазі, що обмеження стовпця посилається тільки на зв'язаний стовпець. (Хоча Postgres Pro цього не вимагає, але для сумісності з іншими СУБД краще дотримуватись цього правила.) Раніше наведений приклад можна переписати і так:
CREATE TABLE products (product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CHECK (price > discounted_price));
Або навіть так:
CREATE TABLE products (product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price));
Це справа смаку.
Обмеженням таблиці можна надавати імена так само, як і обмеженням стовпців:
CREATE TABLE products (product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CONSTRAINT valid_discount CHECK (price > discounted_price));
Слід зазначити, що обмеження-перевірка задовольняється, якщо вираз набуває значення true чи NULL. Оскільки результатом багатьох виразів з операндами NULL буде значення NULL, такі обмеження перешкоджатимуть записи NULL у зв'язані стовпці. Щоб гарантувати, що стовпець не містить значення NULL, можна використовувати обмеження NOT NULL, як описано в наступному розділі.
5.3.2. Обмеження NOT NULL
Обмеження NOT NULL просто вказує, що стовпцю не можна надавати значення NULL. Приклад синтаксису:
CREATE TABLE products (product_no integer NOT NULL, name text NOT NULL, price numeric);
Обмеження NOT NULL завжди записується як обмеження стовпця та функціонально еквівалентне обмеженню CHECK ( ім'я_стовпця IS NOT NULL) , але в Postgres Pro явне обмеження NOT NULL працює ефективніше. Хоча такий запис має недолік - призначити ім'я таким обмеженням не можна.
Звичайно, для стовпця можна визначити більше одного обмеження. Для цього їх потрібно просто вказати одне за одним:
CREATE TABLE products (product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0));
Порядок тут немає значення, він обов'язково відповідає порядку перевірки обмежень.
Для обмеження NOT NULL є зворотне: обмеження NULL . Воно не означає, що стовпець повинен мати лише значення NULL, що, звичайно, було б безглуздо. Суть його у простій вказівці, що стовпець може мати значення NULL (це поведінка за умовчанням). Обмеження NULL відсутнє в стандарті SQL і використовувати його в програмах, що переносяться, не слід. (Він був доданий у Postgres Pro лише для сумісності з деякими іншими СУБД.) Однак деякі користувачі люблять його використовувати, оскільки воно дозволяє легко перемикати обмеження у скрипті. Наприклад, ви можете почати з:
CREATE TABLE products (product_no integer NULL, name text NULL, price numeric NULL);
а потім вставити ключове слово NOT , де потрібно.
Підказка
При проектуванні баз даних найчастіше більшість стовпців мають бути позначені як NOT NULL.
5.3.3. Обмеження унікальності
Обмеження унікальності гарантують, що дані у певному стовпці або групі стовпців є унікальними серед усіх рядків таблиці. Обмеження записується так:
CREATE TABLE products (product_no integer UNIQUE
у вигляді обмеження стовпця і так:
CREATE TABLE products (product_no integer, name text, price numeric, UNIQUE (product_no) );
як обмеження таблиці.
Щоб визначити обмеження унікальності для групи стовпців, запишіть його у вигляді обмеження таблиці, перерахувавши імена стовпців через кому:
UNIQUE (a, c) );
Таке обмеження вказує, що поєднання значень перерахованих стовпців має бути унікальним у всій таблиці, тоді як значення кожного стовпця окремо не повинні бути (і зазвичай не будуть) унікальними.
Ви можете призначити унікальне обмеження ім'я звичайним чином:
CREATE TABLE products (product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric);
При додаванні обмеження унікальності буде автоматично створено унікальний індекс B-дерево для стовпця або групи стовпців, перерахованих в обмеженні. Умову унікальності, яка розповсюджується лише на деякі рядки, не можна записати у вигляді обмеження унікальності, проте таку умову можна встановити, створивши унікальний частковий індекс .
Взагалі, обмеження унікальності порушується, якщо в таблиці виявляється кілька рядків, у яких збігаються значення всіх стовпців, включених до обмеження. Однак два значення NULL у порівнянні ніколи не вважаються рівними. Це означає, що навіть при наявності обмеження унікальності в таблиці можна зберегти рядки з значеннями, що дублюються, якщо вони містять NULL в одному або декількох стовпцях обмеження. Ця поведінка відповідає стандарту SQL, але ми чули про СУБД, які поводяться по-іншому. Майте на увазі цю особливість, розробляючи програми, що переносяться.
5.3.4. Первинні ключі
Обмеження первинного ключа означає, що стовпець, що утворює його, або група стовпців може бути унікальним ідентифікатором рядків у таблиці. Для цього потрібно, щоб значення були одночасно унікальними та відмінними від NULL. Таким чином, таблиці з наступними двома визначеннями прийматимуть однакові дані:
CREATE TABLE products (product_no integer UNIQUE NOT NULL, name text, price numeric); PRIMARY KEY CREATE TABLE products (product_no integer
, name text, price numeric);
Первинні ключі можуть містити кілька стовпців; синтаксис схожий на запис обмежень унікальності: CREATE TABLE example (a integer, b integer, c integer, );
При додаванні первинного ключа автоматично створюється унікальний індекс-B-дерево для стовпця або групи стовпців, перерахованих у первинному ключі, і дані стовпці позначаються як NOT NULL.
Таблиця може мати максимум один первинний ключ. (Обмежень унікальності та обмежень NOT NULL, які функціонально майже рівнозначні первинним ключам, може бути скільки завгодно, але призначити обмеженням первинного ключа можна лише одне.) Теорія реляційних баз даних каже, що первинний ключ має бути у кожній таблиці. У Postgres Pro такого жорсткого вимоги немає, але зазвичай краще слідувати йому.
Первинні ключі корисні і для документування, і клієнтських додатків. Наприклад, графічному додаткуз можливостями редагування вмісту таблиці, ймовірно, знадобиться первинний ключ таблиці, щоб однозначно ідентифікувати її рядки. Первинні ключі знаходять інше застосування в СУБД; зокрема, первинний ключ у таблиці визначає цільові стовпці за замовчуванням для сторонніх ключів, що посилаються на цю таблицю.
5.3.5. Зовнішні ключі
Обмеження зовнішнього ключа вказує на те, що значення стовпця (або групи стовпців) повинні відповідати значенням у деякому рядку іншої таблиці. Це називається посилальною цілісністюдвох пов'язаних таблиць.
Нехай у вас є таблиця продуктів, яку ми неодноразово використовували раніше:
CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);
Припустімо, що у вас є таблиця із замовленнями цих продуктів. Ми хочемо, щоб у таблиці замовлень містилися лише замовлення справді існуючих продуктів. Тому ми визначимо в ній обмеження зовнішнього ключа, яке посилається на таблицю продуктів:
REFERENCES products (product_no) , quantity integer);
З таким обмеженням створити замовлення зі значенням product_no, відсутнім у таблиці products (і не рівним NULL), буде неможливо.
У такій схемі таблицю orders називають підлеглійтаблицею, а products - головною. Відповідно, стовпці називають так само підлеглим і головним (або таким, що посилається і цільовим).
Попередню команду можна скоротити так:
CREATE TABLE orders (order_id integer PRIMARY KEY, product_no integer REFERENCES products, quantity integer);
тобто якщо опустити список стовпців, зовнішній ключ буде неявно пов'язаний з первинним ключем головної таблиці.
Зовнішній ключ може також посилатися на групу стовпців. І тут потрібно записати як звичайного обмеження таблиці. Наприклад:
CREATE TABLE t1 (a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );
Природно, число та типи стовпців в обмеженні повинні відповідати числу та типу цільових стовпців.
Обмеження зовнішнього ключа можна призначити ім'я стандартним способом.
Таблиця може мати кілька обмежень зовнішнього ключа. Це корисно для зв'язку таблиць щодо багатьох-багатьом. Скажімо, у вас є таблиці продуктів та замовлень, але ви хочете, щоб одне замовлення могло містити кілька продуктів (що неможливо в попередній схемі). Для цього ви можете використати таку схему:
CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric); CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text, ...); CREATE TABLE order_items (product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id));
Зверніть увагу, що в останній таблиці первинний ключ покриває зовнішні ключі.
Ми знаємо, що зовнішні ключі забороняють створення замовлень, які не стосуються жодного продукту. Але що робити, якщо після створення замовлень із певним продуктом ми захочемо видалити його? SQL впорається із цією ситуацією. Інтуїція нагадує такі варіанти поведінки:
Заборонити видалення продукту
Видалити також пов'язані замовлення
Щось ще?
Для ілюстрації давайте реалізуємо таку поведінку у наведеному вище прикладі: при спробі видалення продукту, на який посилаються замовлення (через таблицю order_items), ми забороняємо цю операцію. Якщо ж хтось спробує видалити замовлення, то видалиться його вміст:
CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric); CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text, ...); CREATE TABLE order_items (product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id));
Обмежувальні та каскадні видалення - два найпоширеніші варіанти. RESTRICT запобігає видаленню зв'язаного рядка. NO ACTION означає, що якщо залежні рядки продовжують існувати під час перевірки обмеження, виникає помилка (це стандартна поведінка). (Головною відмінністю цих двох варіантів є те, що NO ACTION дозволяє відкласти перевірку в процесі транзакції, а RESTRICT - ні.) CASCADE вказує, що при видаленні зв'язаних рядків залежні від них будуть автоматично видалені. Є ще два варіанти: SET NULL і SET DEFAULT. При видаленні зв'язаних рядків вони призначають залежним стовпцям у підпорядкованій таблиці значення NULL або значення за промовчанням відповідно. Зауважте, що це не буде підставою для порушення обмежень. Наприклад, якщо задано SET DEFAULT , але значення за замовчуванням не задовольняє обмеження зовнішнього ключа, операція закінчиться помилкою.
Аналогічно вказівкою ON DELETE існує ON UPDATE, яке спрацьовує при зміні заданого стовпця. У цьому можливі дії самі, а CASCADE у разі означає, що змінені значення пов'язаних стовпців будуть скопійовані залежні рядки.
Зазвичай залежний рядок не повинен задовольняти обмеження зовнішнього ключа, якщо один із зв'язаних стовпців містить NULL. Якщо до оголошення зовнішнього ключа додано MATCH FULL , рядок буде задовольняти обмеження, тільки якщо всі пов'язані стовпці дорівнюють NULL (тобто при різних значеннях(NULL і NULL) гарантується невиконання обмеження MATCH FULL). Якщо ви хочете, щоб залежні рядки не могли уникнути цього обмеження, оголосіть пов'язані стовпці як NOT NULL .
Зовнішній ключ повинен посилатися на стовпці, які утворюють первинний ключ або обмеження унікальності. Таким чином, для зв'язаних стовпців завжди існуватиме індекс (визначений відповідним первинним ключем або обмеженням), а значить перевірки відповідності зв'язаного рядка виконуватимуться ефективно. Так як команди DELETE для рядків головної таблиці або UPDATE для залежних стовпців вимагають просканувати підпорядковану таблицю і знайти рядки, що посилаються на старі значення, буде корисно мати індекс і для підлеглих стовпців. Але це потрібно не завжди, і створити відповідний індекс можна по-різному, тому оголошення зовнішнього ключа не створює автоматично індекс зв'язаних стовпців.
До вашої уваги вільний переклад статті SQL for Beginners Part 2
Для кожного веб-розробника важливо вміти взаємодіяти з базами даних. У другій частині ми продовжуємо вивчення мови SQLта застосовуємо свої навички до СУБД MySQL. Ми познайомимося з індексами, типами даних та більш складними запитами.
Що вам потрібно
Будь ласка, зверніться до розділу "Що вам потрібно" першої частини, що знаходиться .
Якщо Ви хочете виконувати наведені приклади на своєму сервері, зробіть таке:
- Відкрийте консоль MySQLта авторизуйтесь.
- Створіть базу my_first_db за допомогою запиту CREATEякщо вона не була створена раніше.
- Змініть базу, використовуючи оператор USE.
Індекси
Індекси (або ключі) зазвичай використовуються для підвищення швидкості виконання операторів, які вибирають дані (такі як SELECT) з таблиць.
Вони є важливою частиною гарної архітектури баз даних, складно їх зарахувати до "оптимізації". Зазвичай індекси додаються спочатку, але можуть бути додані пізніше за допомогою запиту ALTER TABLE.
Основні аргументи на користь індексації стовпців бази даних:
- Майже кожна таблиця має первинний ключ ( PRIMARY KEY), зазвичай це стовпець "id".
- Якщо у стовпці передбачається зберігати унікальні значення, він повинен мати унікальний індекс ( UNIQUE).
- Якщо потрібний частий пошук по стовпцю (його використання у пропозиції WHERE), він повинен мати звичайний індекс ( INDEX).
- Якщо стовпець використовується для взаємозв'язку з іншою таблицею, він повинен бути по можливості зовнішнім ключем ( FOREIGN KEY) або звичайним індексом.
Первинний ключ (PRIMARY KEY)
Майже всі таблиці мають первинний ключ, зазвичай це ціле число з опцією автоінкремента ( AUTO_INCREMET).
Дуже часто підзапити ведуть до значного зниження продуктивності, тому використовуйте з обережністю.
UNION: Поєднання даних
Використовуючи запит UNION, можна об'єднувати результати кількох запитів SELECT.
У цьому прикладі поєднуються штати, назва яких починається з літери "N", зі штатами з великим населенням:
(SELECT * FROM states WHERE name LIKE "n%") UNION (SELECT * FROM states WHERE population > 10000000);
Зверніть увагу, що штат New York належить великим і починається з літери "N". Однак у списку він трапляється один раз, т.к. дублікати видаляються автоматично.
Так само принадність запитів UNIONполягає в тому, що їх можна використовувати для поєднання запитів до різних таблиць.
Наприклад, у нас є таблиці employees (співробітники), managers (менеджери) та customers (клієнти). У кожній таблиці є поле з адресою електронної пошти. Якщо ми хочемо отримати все E-mail адресив одному запиті, то можемо вчинити так:
(SELECT email FROM employees) UNION (SELECT email FROM managers) UNION (SELECT email FROM customers WHERE subscribed = 1);
Виконавши цей запит, ми отримаємо поштові адреси всіх співробітників та менеджерів, і лише тих клієнтів, які підписані на розсилку.
INSERT Продовження
Ми вже говорили про запит INSERTу попередній статті. Після того, як ми розглянули індекси, ми можемо поговорити про додаткові можливостізапитів INSERT.
INSERT ... ON DUPLICATE KEY UPDATE
Це найпоширеніша умова. Спочатку запит намагається виконати INSERT, і якщо запит зазнає невдачі внаслідок дублювання первинного ( PRIMARY KEY) або унікального ( UNIQUE KEY) ключа, то виконується запит UPDATE.
Давайте спочатку створимо тестову таблицю.
Це таблиця зберігання продуктів. Поле "stock" зберігає кількість продуктів, доступних на складі.
Тепер спробуємо вставити вже існуюче значення в таблицю і подивимося, що станеться.
Ми отримали помилку.
Припустимо, ми отримали нову хлібопекарню і хочемо оновити базу даних, але не знаємо, чи є вже запис у базі даних. Ми можемо спершу перевірити існування запису, а потім виконати інший запит для вставки. Або можна виконати все в одному простому запиті:
Працює так само як і INSERTале з однією важливою особливістю. Якщо запис вже існує, він видаляється, а потім виконується запит INSERT, при цьому ми не отримаємо жодних повідомлень про помилку.
Зверніть увагу, т.к. вставляється зовсім новий рядок, поле автоінкремента збільшується на одиницю.
Це спосіб запобігання появі помилки про дублювання, перш за все для того, щоб не зупиняти виконання програми. Може знадобиться вставити новий рядокбез виведення будь-яких помилок, якщо навіть відбулося дублювання.
Немає жодних помилок та оновлених рядків.
Типи даних
Кожен стовпець у таблиці має бути певного типу. Ми вже використовували типи INT, VARCHARі DATE, але не зупинялися на них докладно. Також ми розглянемо ще кілька типів даних.
Почнемо з числових типів даних. Я поділяю на дві групи: Цілі і дробові.
Цілі
Стовпець з типом цілі може зберігати лише натуральні числа (без десяткової точки). За замовчуванням вони можуть бути позитивними чи негативними. Якщо вибрано опцію UNSIGNED, можуть зберігатися лише позитивні числа.
MySQL підтримує 5 типів цілих чисел різних розмірівта діапазонів:
Дробові числові типи даних
Ці типи можуть зберігати дробові числа: FLOAT, DOUBLE та DECIMAL.
FLOAT займає 4 байти, DOUBLE займає 8 байт і аналогічний попередньому. DOUBLE точніший.
DECIMAL(M,N) має змінну точність. M максимальне число цифр, N – число цифр після десяткової точки.
Наприклад, DECIMAL(13,4) має 9 знаків до коми та 4 після.
Рядкові типи даних
За назвою можна здогадатися, що можна зберігати рядки.
CHAR(N) може зберігати N символів та має фіксовану величину. Наприклад, CHAR(50) повинен завжди містити 50 символів у кожному рядку у всьому стовпці. Максимально можливе значення 255 символів
VARCHAR(N) також працює, але діапазон може змінюватися. N – позначає максимальне значення. Якщо рядок, що зберігається, коротше N символів, то він займатиме менше місця на жорсткому диску. Максимально можливе значення 65535 символів.
Різновиди типу TEXT більше підходять для довгих рядків. TEXT має обмеження у 65535 символів, MEDIUMTEXT у 16.7 мільйонів, та LONGTEXT у 4.3 мільярди символів. MySQL зазвичай зберігає їх в окремих сховищах на сервері, щоб головне сховище було по можливості менше і швидше.
.Висновок
Дякуємо за читання статті. SQL – це важлива мова та інструмент в арсеналі веб-розробника.
