Використовуйте потужність MySQL за допомогою передових команд - Buzut

У попередній статті ми бачили, як керувати MySQL з командного рядка. Тож ми дізналися, як створювати, змінювати, видаляти таблиці та бази даних. Ми також бачили, як читати, писати, стирати та сортувати дані в цих таблицях. Ці операції складають основну частину роботи, яку потрібно виконати в базах даних SQL.

потужність

Однак нам іноді доводиться управляти складними даними, організація яких вимагає більш широкої обробки. Тому ми будемо говорити про індекс, первинні ключі, зовнішні ключі або пошук ФУЛТЕКСТ. Так багато методів, які допоможуть нам організувати та управляти складними схемами даних.

Індекс стовпця

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

Отже, індекси дозволяють пришвидшити запити, які використовують критерії пошуку, такі як WHERE .

З іншого боку, індекси займають місце в пам'яті та сповільнюють ВСТАВКУ та ОНОВЛЕННЯ, оскільки вам доведеться писати індекс кожного разу. Потім необхідно розумно вибирати стовпці для індексації та надавати привілеї стовпцям, за якими можна часто здійснювати пошук, наприклад.

Перш ніж вникати в деталі індексів і створювати їх, знайте, що їх, звичайно, можна відобразити:

У зв’язку з цим можна зробити індекс по декількох стовпцях одночасно, і ми можемо використовувати цей індекс, навіть якщо ми не робимо пошук по всіх індексованих стовпцях. Це називається вказівним пальцем зліва.

У вас є таблиця учасників:

  • прізвище
  • ім'я
  • псевдо

Ви робите індекс на всіх трьох одночасно. Індекс сортуватиме прізвище, потім ім’я, потім псевдонім. Отже, якщо ви хочете шукати лише за іменами, порядок буде точно таким же, як якщо б ми створили індекс лише для імен.

Подібним чином, якщо ми виконуємо пошук за прізвищем та ім’ям, порядок все одно не рухається порівняно з індексом у цих двох стовпцях. З іншого боку, якщо ми хочемо здійснити пошук лише на псевдо або лише на ім’я, це не буде працювати.

Насправді прізвиська упорядковуються за іменами та прізвищами, що передують їм у лівих стовпцях, перед тим, як самі сортувати за алфавітом. Ось чому він називається "зліва", він працює до тих пір, поки ви виконуєте пошук у всіх стовпцях, які знаходяться ліворуч. В іншому випадку потрібно створити інший індекс.

Кілька типів ІНДЕКСУ

УНІКАЛЬНИЙ

Як випливає з назви, це гарантує, що в таблиці може бути лише одне значення один раз. Це, наприклад, індекс, який ми використовуємо в ОСНОВНОМУ КЛЮЧІ .

ПОВНИЙ ТЕКСТ

Цей індекс дозволяє ефективно шукати текстовий вміст. Цей тип індексу працює лише на полях типу TEXT, CHAR та VARCHAR (інакше це вже не текст!). Потім ми побачимо, що це дозволяє робити.

Примітка: Неможливо використовувати техніку “вказівник ліворуч” із FULLTEXT. Крім того, цей індекс доступний лише для двигуна myisam.

ПРОСТОРНА

Цей індекс є дещо специфічним, оскільки стосується просторових даних. Якщо вам потрібна додаткова інформація, RTFM;)

Створення індексів

Ви також можете створювати індекси безпосередньо в стовпцях. Вам слід використовувати слово КЛЮЧ, або УНІКАЛЬНИЙ, якщо потрібно унікальний індекс. Цей синтаксис є більш обмеженим, оскільки ви можете одночасно визначати індекси лише в одному стовпці, а також не вказувати обмеження розміру в індексі (наприклад, x перші символи символу).

Додавання після створення таблиці

Іноді ми не про все думаємо одразу, і ми хотіли б додати індекс до вже існуючої таблиці. ALTER TABLE ви знаєте ?

Існує ще одна команда, яка дозволяє робити те саме: СТВОРИТИ ІНДЕКС .

Особисто мені не подобається такий спосіб робити, тому що ви повинні запам'ятати додаткову команду, коли ALTER TABLE робить це дуже добре. Крім того, пізніше ви побачите, що CREATE INDEX дозволяє менше речей, ніж ALTER TABLE, наприклад, видалення індексу:

Легко, чи не так ?!

Вставка або оновлення

Унікальні індекси заважають вставляти дублікати даних. У разі спроби такої вставки виникає помилка. Однак вони відкривають нову можливість: UPSERT, розуміння UPDATE або INSERT .

Ця команда дозволяє вставляти дані, лише якщо вони ще не існують. В іншому випадку ви можете не робити нічого або оновлювати дані.

У наступних прикладах ми припускаємо, що на телефоні є унікальний індекс .

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

Звичайно, ми можемо визначити значення для модифікації.

Тут, якщо значення не існує, воно створюється, але якщо воно існує, стовпці телефону та прізвища оновлюються вибраними нами значеннями.

Нарешті, ви можете виконувати оновлення динамічно. Це робиться за допомогою ключового слова VALUE (VALUES до MariaDB 10.3.3). За цим ключовим словом значення VALUE (стовпець) дорівнює значенню, як би було вставлене. З іншого боку, якщо вказати column = column, то це поточне значення. Як і в першому прикладі, ця можливість дозволяє визначити вираз після UPDATE .

Цього разу, якщо номер телефону вже існує, ми просто оновлюємо ім’я та прізвище контакту.

ПОЛНОТЕКСТОВЕ дослідження

FULLTEXT дуже потужний для досліджень. Це розрізає нитки на слова, щоб аналізувати їх по черзі. Слово - це послідовність символів без пробілів (о ?!) Або два слова, розділені символом підкреслення «_» або апострофом. Складені слова, розділені дефісами, вважаються двома окремими словами.

Крім того, для проведення цього дослідження FULLTEXT враховує наступні правила:

  • слово, яке з’являється в 50% рядків, ігнорується (відповідним),
  • слово менше чотирьох літер ігнорується,
  • занадто поширені англійські слова ігноруються. Ви французи, не щастить! Але, тим не менше, можна вказати слова, які потрібно ігнорувати.

Пошук ПОВНОГО ТЕКСТУ виконується так:

Існує 3 типи пошуку ФУЛТЕКСТ:

  • Пошук В РЕЖИМІ ПРИРОДНОЇ МОВИ. Це за замовчуванням.
  • Дослідження В РЕЖИМІ BOOLEAN. Цей пошук ігнорує правило 50%, обговорене вище, і дозволяє здійснювати пошук у стовпцях, які не були проіндексовані (однак, слідкуйте за часом виконання!). Остання точка з логічним режимом, у результатах немає сортування за релевантністю.
  • Пошук за допомогою QUERY EXTENSION. Це велике дослідження. Цей тип пошуку насправді робить два пошуки в одному запиті. Спочатку він подає пошуковий рядок у НАТУРАЛЬНИЙ пошук, потім запускає другий із параметрами, вже не той рядок, який ви шукали, а результати першого пошуку. Ось чому це великий розшук.

Щоб вказати тип пошуку:

Ви зрозумієте, що індекси FULLTEXT представляють великий інтерес, коли мова йде про пошук у таблиці. На жаль, цей тип індексу присутній лише в таблицях MyISAM. І згодом ви побачите, що InnoDB має багато сильних сторін, яких MyISAM не має. Словом, нам доведеться зробити вибір.

Первинні шефи

Ключі є обмеженнями. Ми знаємо первинні ключі, які ми зазвичай застосовуємо до стовпця id наших таблиць. Первинний ключ або ПЕРВИННИЙ КЛЮЧ насправді є ІНДЕКСОМ НЕ НУЛЬНИМ, отже це сума двох обмежень. Якщо ви хочете розмістити індекс у стовпці PRIMARY KEY, це зайве, оскільки KEY означає індекс. Отже, первинний ключ - це індекс.

Цей ключ гарантує, що:

  • Відповідні стовпці не містять одне і те ж значення двічі,
  • Кожен рядок має значення (НЕ НУЛЕВО).

Для його створення, я думаю, ви знаєте, як це зробити (ми це вже бачили кілька разів):

Звичайно, ви можете вказати це безпосередньо в колонці. Я не показую вам, як. Крім того, ми можемо створити первинний ключ на другому кроці за допомогою ALTER TABLE, чого ми не можемо зробити за допомогою CREATE INDEX (я вже казав вам!).

Так само, щоб видалити його ... ну ви знаєте як.

Іноземні ключі

Зовнішні ключі використовуються для перехрещення даних з кількох таблиць, забезпечуючи їх правильне збіг. Дозвольте пояснити: у вас є дві таблиці, перша містить список усіх ваших фільмів, друга - список категорій, за якими класифікуються фільми. Чому б нам не покласти все в одну таблицю ?

Скажімо, існує п’ять категорій: трилери, комедія, анімація, документальне кіно, сімейне. Якщо ви повторите це за кількістю фільмів, які у вас є (скажімо, 500), це дає вам купу зайвих даних для запису, і стільки місця витрачено даремно.

Отже, ви помістите ці 5 категорій в іншу таблицю та помістите ідентифікатор, що відповідає формату, у таблицю фільмів. Таким чином, мета зовнішніх ключів полягає в тому, що якщо у вас є 5 форматів, а їх ідентифікатори - 1, 2, 3, 4 і 5 відповідно, вони не дозволять вам мати ідентифікатор 7, який нічого не збігається. Словом, це перевірка цілісності.

2, 3 речі, які слід знати:

  • Для механізму таблиць це обов’язково буде INNODB, MyISAM не підтримує зовнішні ключі,
  • Ми можемо створити ці ключі на декількох стовпцях,
  • У цьому випадку ми повинні мати однакову кількість стовпців у посиланні та в ключі (логічний ...),
  • Створюючи ключ, автоматично додається індекс, ми побачили, що ключ та індекс є більш-менш синонімами,
  • З іншого боку, стовпець, який служить посиланням (отже, не той, який отримує ключ), повинен брати індекс,
  • Довідковий стовпець і ключ повинні бути одного типу.

Тепер, коли ви це знаєте, вам добре йти! Спочатку потрібно створити таблицю, яка служить посиланням, інакше MySQL видасть помилку і не зможе створити таблицю, яка отримує зовнішні ключі. Як ви хочете створити зовнішній ключ на чомусь, що не існує ?!

[CONSTRAINT [my_clef_etrangere]] корисний, якщо ми хочемо визначити ім'я ключа. Незважаючи на те, що ви також можете зробити КОНТРОЛЬНИЙ ЗОВНІШНИЙ КЛЮЧ, не вказуючи імені, завжди корисно називати свої зовнішні ключі, щоб мати можливість позначити їх пізніше, щоб змінити або видалити.

Як завжди, ми можемо додати його після створення таблиці:

Варіанти зовнішнього ключа

Зовнішні ключі дозволяють підтримувати певну узгодженість у наших таблицях. Тому ми не повинні мати змоги видаляти або змінювати посилальну таблицю, інакше всі зовнішні ключі більше не матимуть сенсу. Більше того, якщо ви спробуєте видалити посилання, тоді як в інших таблицях є ключі, пов’язані з ним, це призведе до помилки. Точно можна визначити поведінку за замовчуванням для такого роду справ.

У нашому розпорядженні 4 варіанти:

  • RESTRICT: поведінка за замовчуванням, запобігає видаленню чи зміні,
  • НІЯКИХ ДІЙ: схоже на ОБМЕЖЕННЯ,
  • SET NULL: встановлює NULL як значення для полів, посилання на які видалено,
  • КАСКАД: видаляє рядки, посилання на які видалено.

У нашому попередньому прикладі. Якщо я поміщу ВСТАНОВИТИ НУЛЬ на ключ зовнішньої категорії, якщо я видалю категорію трилерів у моїй таблиці Категорії, усі фільми, які були для категорій трилери, відтепер матимуть НУЛЬ як категорію. З іншого боку, якби постановка була зроблена на КАСКАДІ, усі фільми з категорією трилери були б видалені зі таблиці.

ІНОЗЕМНА КЛЮЧОВА проблема

Іноді ми стикаємося з проблемами під час видалення або оновлення стовпців у таблиці через зовнішні ключі. Часто дуже інформативним є перегляд стану механізму таблиць (INNODB) ПОКАЗАТИ СТАТУС ДВИГАТЕЛЯ INNODB та поглянути на розділ останніх помилок щодо ІНОЗЕМНИХ КЛЮЧІВ (ОСТАННЯ ПОМИЛКА ІНОЗЕМНИХ КЛЮЧІВ) .

Приєднується

Об’єднання дозволяють об’єднати дві (або більше) таблиці. За зовнішніми ключами ми побачили, що, можливо, ми хочемо зберегти певну інформацію у другій таблиці. Якщо ми хочемо отримати цю інформацію так, ніби вона є частиною єдиної таблиці, ми будемо використовувати об’єднання !

Існує два види об’єднання, внутрішнє та зовнішнє. Внутрішні об’єднання повертають результати для стовпців, які мають збіги в кожній з таблиць, тоді як зовнішні об’єднання повертають усі стовпці.

У нашому прикладі категорій, якщо у нас немає фільму, який має категорію відпусток (скажімо, ми додали деякі), із внутрішнім об’єднанням ця категорія не відображатиметься, оскільки немає фільму, який би відповідав їй. З іншого боку, при зовнішньому приєднанні ми знайдемо вакансії в нашій таблиці.

Внутрішнє з'єднання

Для SELECT не потрібно вказувати ім'я таблиці, якщо воно не існує в обох таблицях. Тут доцільно поставити псевдонім. Приєднання є внутрішнім за замовчуванням, тому вам не потрібно вказувати INNER. ON визначає критерії, які слід виконати для об'єднання, найчастіше це буде =, але інші оператори також є дійсними.

Наприклад, якщо ми хочемо отримати всі фільми, що відповідають категорії трилери:

Зовнішнє приєднання

Ви повинні знати, що для зовнішнього з'єднання існує два режими. Праве з'єднання та ліве з'єднання. Тобто, якщо ми робимо об’єднання зліва, нам потрібні всі дані з лівої таблиці, навіть якщо вона не має відповідності в правій таблиці, і навпаки.

Що право, що ліво. Легко як abc, дитино! Ми читаємо зліва направо, тому таблиця зліва є першою, про яку згадується. Оскільки все це незрозуміло, візьмемо приклад. Скажімо, я хочу показати всі свої фільми з відповідними категоріями. Назва категорій, не їх ідентифікатор, звичайно ... Тому ми зробимо ліве приєднання, щоб також показати фільми, які не мають категорії.

Внутрішнє об’єднання тут не повернуло б фільми без категорії. Так само ми могли б відображати всі категорії. Думаю, ви зрозуміли принцип. Це абсолютно однаково для правильних об’єднань, тому я не наводжу приклад.

Альтернативні синтаксиси

Можна опустити речення JOIN для внутрішніх з'єднань, і в цьому випадку речення ON замінено на WHERE. Однак стає важко розрізнити умови приєднання (як правило, вказані в ON) та нормальні умови (WHERE). До вас ...

Практика, яка спрощує життя і яку цього разу можна використовувати без проблем, ПРИРОДНЕ СПІЛКУВАННЯ. Це внутрішнє об'єднання уникає вказівки пропозиції ON, якщо поля двох таблиць, в яких ми хочемо управляти об'єднанням, мають однакову назву.

За запитами

Це свого роду вкладені запити. Вони дозволяють нам зробити в одному запиті те, що ми мали зробити за кілька. Серед своїх фільмів я хотів би вибрати найновіший з трилерів:

Очевидно, що дужки використовуються для диференціації підзапиту від основного запиту без них, тому ви отримаєте помилку. Також обов’язковим є присвоєння псевдоніма для проміжного запиту, інакше помилка також з’явиться.

Порівняння

У підзапитах можна використовувати речення, крім FROM, проте підзапит повинен повертати або одне значення (отже, один рядок і один стовпець), або один рядок, або один стовпець.

Логічні оператори

У випадку, коли відповідь, повернута підзапитом, є єдиним значенням, ми можемо використовувати всі звичайні логічні оператори замість з (, = ...).

Якщо відповідь повертає рядок, ми можемо використовувати оператори = і! = .

IN та NOT IN

Ці оператори означають “знайдено” або “не знайдено” у підзапиті. Їх можна використовувати з запитами, які повертають значення або стовпець.

БУДЬ-ЯКІ, НЕКОТОРІ і ВСІ

БУДЬ-ЯК означає "принаймні одне зі значень", тоді як ALL означає "всі значення". НЕЩЕ є синонімом БУДЬ-ЯКОГО. Ці оператори характерні для підзапитів, їх ви не знайдете ніде в MySQL. Вони застосовуються за тих самих умов, що IN та NOT IN, тобто коли запит повертає одне значення або стовпець.

У мене немає прикладу для порівняння, проте вони використовуються так само, як і з FROM .

Кореляція запитів

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

Будьте обережні, проте ця операція працює, піднімаючись на один або більше рівнів. Це означає, що підзапрос для отримання відповідної таблиці на два рівні вище (або більше), але не в іншому підзапиті того самого рівня або нижчого рівня.

СОЮЗ

UNION дозволяє об'єднати кілька SELECT в одному запиті:

Запити повинні мати однакову кількість стовпців, відповідні стовпці також повинні бути одного типу (хоча MySQL не видає помилки, це добре для послідовності).

UNION автоматично усуває дублікати. Якщо ви хочете відобразити їх, вам потрібно зробити UNION ALL .

Випадки ОБМЕЖЕННЯ І ЗАМОВЛЕННЯ

Можна використовувати ОБМЕЖЕННЯ для одного запиту або для обох запитів:

Щоб ОБМЕЖИТИ другий (або більше) запит, використовуйте дужки:

Для ORDER BY його можна використовувати лише для загального результату, а не для окремих запитів.

За винятком випадку, коли до нього додано ОБМЕЖЕННЯ !

Приєднуйтесь до обговорення !

Квентін Бусуттіл - ліцензія Creative Commons BY-NC-ND