БД MySQL (типи даних, таблиці, запити, тригери, індекси)

Reg.ru: домени та хостинг

Найбільший реєстратор та хостинг-провайдер в Укаини.

Понад 2 мільйони доменних імен на обслуговуванні.

Понад 700 тис. клієнтів у всьому світі вже зробили свій вибір.

Безкоштовний Курс "Практика HTML5 та CSS3"

з основ адаптивної верстки

на HTML5 та CSS3 з повного нуля.

Фреймворк Bootstrap: швидка адаптивна верстка

Навчіться верстати просто, швидко та якісно, ​​використовуючи потужний та практичний інструмент.

Верстайте на замовлення та отримуйте гроші.

Безкоштовний курс "Сайт на WordPress"

Бажаєте освоїти CMS WordPress?

Отримайте уроки з дизайну та верстки сайту на WordPress.

Навчіться працювати з темами та нарізати макет.

*Наведіть курсор миші, щоб призупинити прокручування.

БД MySQL (типи даних, таблиці, запити, тригери, індекси)

Вступ

Досить часто, створюючи або замовляючи будь-який ІТ-проект, ми чуємо про бази даних.

Що таке база даних? Навіщо вона потрібна? Як їй скористатися? На ці та інші питання я намагатимусь відповісти в даному курсі статей про бази даних.

Усі аспекти я намагатимуся описати прикладами. Тож почнемо.

БД - сукупність даних, систематизованих таким чином, щоб отримати їх було просто та зручно.

СУБД - система управління базами даних, сукупність функцій та процедур для роботи з БД.

Існує безліч СУБД. Для web-розробки, як правило, використовується СУБДMySQL.

Чому саме вона? Це проста, безкоштовна та досить потужна система.

Наприклад, можна взяти Facebook. Дані зберігаються там саме у MySQL. MySQL оптимізована для отримання даних із БД. Практично на всіх сайтах мив основному читаємо дані з БД, перш ніж надіслати їх на сторінку.

Виходячи з усіх переваг СУБД MySQL, усі приклади будуть вказані в кодах цієї СУБД.

Установка СУБД

Для початку встановимо MySQL. Найпростіше це зробити встановивши т.зв. Джентльменський набір веб-розробника. Дистрибутив та порядок встановлення ви можете знайти на сайті http://www.denwer.ru/.

Також ви можете вивчити наступний матеріал, присвячений основ роботи з Денвером.

Для початку створимо БД, з якою працюватимемо. Для цього на вкладціБази даних(обрана на малюнку) вкажемо реквізити нової бази даних. Назвемо її test.

даних

Типи даних

У MySQL, як та інших середовищах розробки, розрізняють кілька типів даних. Вивчаючи СУБД, насамперед необхідно вивчити всі типи даних. Загалом вони ідентичні в усіх СУБД, але кожна має свої особливості.

1) INT - Цілочисельний формат. В основному використовується для ідентифікаторів та перемикачів (0 - ні, 1 - є).

2) CHAR – Текстовий формат. Обмежено 32 тис. символів. Використовується для зберігання невеликого обсягу текстової інформації. Приклад CHAR(128).

3) TEXT - Текстовий формат. Практично необмежений. Використовується для зберігання великої кількості текстової інформації.

4) DATE – Дата.

5) DATETIME – Дата та час.

6) DECIMAL – Числовий формат із роздільником. Приклад DECIMAL(14, 2) дозволить нам записати число, довжиною 14 символів з двома знаками після коми.

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

Таблиці

Основна сутність БД - це таблиця. Інші, зазвичай, допоміжні.

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

Роль таблиці в БД - зберігання структурованого набору даних і лише. У MySQL таблиця є стандартною таблицею з колонками і рядками.

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

Розглянемо приклад. Створимо таблицю "Users" (довідник користувачів) за допомогою наступного коду:

Прагніть працювати з БД саме за допомогою скриптів. Це спростить розуміння команд та функцій. Крім того, якщо ви не маєте доступу до середовища розробки (phpMyAdmin), це вам дуже допоможе. Працюючи зі скриптами, використовуємо вкладку SQL (див. мал. нижче)

mysql

УВАГА! При створенні таблиці намагайтеся називати її відповідно до даних, які зберігатимуться в ній. Також у кожній таблиці бажано створювати поле ідентифікатора запису (ID). Це дуже знадобиться.

Для видалення таблиці використовується код:

Для множинного видалення

Будьте уважні під час виклику вказаних команд.

Для зміни таблиці використовується код:

Поки що на цьому зупинимося.

Запити

БД як така повинна мати механізми введення-виведення інформації. Для вставки, зміни, отримання або видалення необхідно використовувати запити. У цьому курсі опишу лише запити SQL (Structured Query Language — «Структурована мова запитів»), т.к. більшість БД працюють саме із запитами такого типу.

Існує 4 види запитів:

Наприклад, пограємо з нашим довідником "Users":

1) Select - Отримання даних із таблиці

Давайте розберемозапит. SELECT[Вибрати] *[Всі стовпці] FROM[З таблиці] Users. Цей запит поверне всі записи по всіх колонках таблиці.

Щоб обмежити вибірку (повні вибірки потрібні лише у разі отримання звітів, і то не завжди), додамо до нашого запиту умову WHERE.

Тут ми отримуємо дані щодо користувача №1. Начебто все просто, чи не так? Йдемо далі.

2) Update - Оновлення даних у таблиці

Давайте розберемо запит. UPDATE[Оновити] Users SET[Встановити [поле] user_name = 'Іванов І.І.' WHERE[Де] user_id = 1. В даному випадку ми змінили ПІБ користувача №1 на "Іванов І.І.".

3) Insert - Вставка нових даних у таблицю

На хороший, цей запит потрібно писати насамперед, т.к. довідник у нас спочатку порожній, але зазвичай розробники створюють таблиці через інтерфейс (phpMyAdmin) і одразу додають кілька тестових записів.

Розбираємо запит. INSERT INTO[Вставити в] Users ([перелік полів]user_id, user_name) VALUES ([Перелік значень]2, 'Петров П.П.'). Запит вставить запис у таблицю зі значеннями user_id рівному 2 і user_name, що дорівнює 'Петров П.П.'.

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

Однак при цьому важливо дотримуватися порядку стовпців.

При вставці кількох рядків можна скористатися запитом множинної вставки.

4) Delete - Видалення даних із таблиці

Тут усе просто. DELETE[Видалити] *[Всі] FROM[З] Users.

Запиту неважливо, що буде вказано після команди DELETE, чи це перелік полів або одне поле, він видалить весь рядок даних.

УВАГА! У цьому запиті не забувайте писати умову WHERE (Це стосується і запиту UPDATE), інакше запит очистить всю таблицю.

У разі налагодження програми намагайтеся при написанні команди DELETE спочатку описувати запит у вигляді SELECT. Потрібно нам, наприклад, написати запит на видалення одного користувача, ми пишемо:

Перевіряємо. Запит виводить один рядок, який треба видалити. Замінюємо тепер SELECT на DELETE та спокійно виконуємо запит, не побоюючись за дані.

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

даних

Приклад вставки рядків у таблицю. Середовище сповіщає нас про вставку рядка.

Тригери

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

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

Розглянемо з прикладу.

Допустимо ми бажаємо зберігати історію змін у нашому додатку. Іноді це дуже корисно. Створимо таблицю історії. Як правильно описати поля ми з'ясували раніше.

Визначимо типи змін:

1 - додавання запису 2 - зміна запису

Тепер створимо тригер на нашій таблиці Users:

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

У деяких СУБД тригери використовуються для заповнення унікального ідентифікатора таблиць, але MySQL це не потрібно.

Індекси

Індекси - це набори даних у БД, які використовуються для оптимізації пошуку записів.

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

Розглянемо з прикладу нашого довідника. Припустимо, ми хочемо прив'язати співробітників відділів. Модифікуємо наш довідник користувачів, додавши до нього посилання на групу.

Тепер у нас довідник користувачів має прив'язку до групи. Заповнимо порожні поля записами 1 та 2. При виконанні запиту виду:

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

Щоб прискорити пошук, необхідно додати індекс.

ВИМАНИЕ! Ця процедура разова і проводиться на етапі розробки. Часу багато не займає, тож раджу не опускати цей пункт. Крім того, це основа швидкодії вашої програми.

Тепер розберемо, чим нам допоможе цей індекс. Візьмемо той самий запит:

Алгоритм пошуку по таблиці йтиме лише за групою записів, group_id_ref яких дорівнює 1.

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

Тепер, щоби якось визначити групи, створимо довідник.

Також нам може стати в нагоді первинний індекс. Навіщо він потрібний? Для того щоб при додаванні записів до довідників кожна з них мала свій ідентифікатор.

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

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

За відсутності унікального ідентифікатора запит на зміну другого запису виглядав би так:

За наявності унікального ідентифікатора user_id запит виглядає трохи простіше:

При цьому пошук у таблиці за текстом - не найкраще рішення. СУБД дозволяє це робити, але ви сильно втрачаєте на швидкості. Недоліком такого пошуку є також передача в запит текстових параметрів. Їх потрібно екранувати.

MySQL має дуже простий спосіб створення унікального індексу.

Розглянемо докладніше код:

УВАГА! ALTER це зміна структури БД. Необхідно мінімізувати чи взагалі намагатися уникати цієї команди у скриптах. Усі індекси та лічильники мають бути враховані під час проектування БД.

Так виглядає скрипт створення таблиці Users без використання ALTER:

Підсумок

У цій статті ми познайомилися з Базами даних, дізналися про таблиці, запити, тригери та індекси. Пройдемося ще раз основними об'єктами.

1) Таблиця - об'єкт БД для зберігання даних. 2) Запит - засіб введення-виведення інформації. 3) Тригер - процедура, що виконується при зміні даних у таблиці. 4) Індекс - покажчик на записи в таблиці.

По суті цього набору знань цілком достатньо, щоб розробити сайт-візитку.

Вихідники:

P.S. Хочете поглибити свої знання та навички? Придивіться до преміум-уроків з різних аспектів сайтобудування, включаючи SQL і роботу з БД, а також безкоштовний курс зі створення своєї CMS-системи на PHP з нуля.