Основи SQL запитів в Базу даних

На цій сторінці зібрані основні SQL запити до бази даних.

Якщо потрібно поміняти місцями стовпці email   user_id у таблиці dle_users

ALTER TABLE `dle_users` MODIFY `email` varchar(50) AFTER `user_id`

Порядок виконання команд для запиту в БД:

1. FROM - список таблиць
2. WHERE - це умовний оператор, який використовується для обмеження рядка за якою-небудь умовою
3. GROUP BY - використовується для групування рядка
4. HAVING -  застосовується після групування рядків для фільтрації за значеннями агрегатних функцій
5. SELECT
6. DISTINCT - використовується для виключення повторюваних рядків із результату
7. ORDER BY -використовується для сортування. У нього є два параметри:
   1. ASC (за умовчанням) за збільшенням
   2. DESC — за зменшенням
8. LIMIT - використовується для обмеження кількості рядків для виведення

DISTINCT використовується для виключення повторюваних рядків із результату
ALL (за умовчанням) використовується для отримання всіх даних, в тому числі і повторень

За допомогою оператора AS змінють назву стовбця або визначать його псевдонім:

---

про SELECT - читати

Операції з'єднання SQL

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

Різні типи пропозиції JOIN такі:

• INNER JOIN: внутрішнє з'єднання повертає лише ті записи, значення яких збігаються в обох таблицях;
• CROSS JOIN: перехресне з'єднання повертає тільки ті записи, які мають однакові значення в лівій або правій таблиці;
• LEFT JOIN: ліве з'єднання повертає всі записи з лівої таблиці і тільки збігаються записи з правої таблиці;
• RIGHT JOIN: праве з'єднання повертає всі записи з правої таблиці і тільки записи з лівої таблиці.

На наступному нижче малюнку показано короткий приклад для розгляду:

INNER JOIN

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

 Далі наведемо приклад таблиці клієнтів customer, яка використовується в додатках електронної комерції, яка містить дані про клієнтів:

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

SELECT order.order_id, customer.name
FROM order
INNER JOIN customer ON order.customer_id = customer.customer_id;

LEFT JOIN

Ліве з'єднання LEFT JOIN витягує всі записи з лівої таблиці і тільки записи, що впадають з правої таблиці. Якщо застосувати приклад таблиць клієнтів і замовлень з лівим з'єднанням, воно витягуватиме всі записи з таблиці замовлень order, навіть якщо у правій таблиці немає збігів (customer). Щоб отримати всі відомості про замовлення клієнта, можна використовувати наступний запит:

SELECT order.order_id, customer.name
FROM order
LEFT JOIN customer ON order.customer_id = customer.customer_id;

RIGHT JOIN

Праве з'єднання RIGHT JOIN витягує всі записи з правої таблиці та загальні записи з лівої таблиці. Якщо застосувати приклад таблиць клієнтів та замовлень з правим з'єднанням, воно буде витягувати всі записи з таблиці клієнтів customer, навіть якщо в лівій таблиці (order) немає збігів. Щоб отримати всі відомості про клієнтів із замовленням, можна використовувати наступний запит:

SELECT order.order_id, customer.name
FROM order
RIGHT JOIN customer ON order.customer_id = customer.customer_id;

CROSS JOIN

Перехресне з'єднання CROSS JOIN повертає всі записи, в яких є збіг у лівому або правому записі таблиці. Якщо ми візьмемо приклад із таблицями замовлень та клієнтів, то воно поверне п'ять рядків із відомостями про клієнтів та замовлення:

SELECT order.order_id, customer.name
FROM order
OUTER_JOIN customer ON order.customer_id = customer.customer_id;

UNION

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

Наприклад, наведемо приклад таблиці співробітників Employee:

 Далі наведемо приклад таблиці підрядників Contractor:

Як показано в наведеному нижче прикладі, імена стовпців з першого запиту SELECT використовуються як стовпці для результатів, що повертаються: 

SELECT 'Employee' As Type, name, city, country FROM Employee
UNION
SELECT 'Contractor', name, city, country FROM Contractor;

Наступний приклад об'єднання UNION видаляє повторювані рядки з результату; якщо ми не хочемо видаляти рядки, що повторюються, можна застосувати ключове слово ALL:

SELECT country
FROM Employee
UNION SELECT country FROM Contractor;

Цей запит повертає два рядки з попередньої таблиці - Germany та Mex&sy;ico. Щоб отримати всі рядки з обох таблиць, можна використовувати ключове слово ALL таким чином:

SELECT country
FROM Employee
UNION ALL
       SELECT country FROM Contractor;

Підзапит

Оператор SELECT всередині оператора < code>SELECT називається підзапитом. Цей підпит може застосовуватися для вибору стовпця або в умовних пропозиціях. Підзапит також може бути вкладений в інший запит типу INSERT, UPDATE або DE­LETE.

Наступний запит повертає безліч користувачів, які знаходяться в Ahmedabad, використовуючи вкладений запит:

SELECT firstName, lastName FROM users
WHERE city IN
    (SELECT city FROM cities WHERE city = 'Ahmedabad');

Оптимізація запитів SELECT

Запити SELECT використовуються для отримання даних на динамічних веб-сторінках, тому налаштування цих операторів забезпечує хорошу продуктивність, що дуже важливо . Нижче наведено деякі рекомендації щодо оптимізації запитів.

• Переконайтеся, що в таблицях є індекси. Індекси завжди допомагають прискорити фільтрацію та отримання результатів. Індекси можна задавати в операті WHERE запиту SELECT.
• Індекси також мінімізують кількість повних сканувань у великих таблицях.
• Налаштування буферного пулу InnoDB , кеша ключів MylSAM та кеша запитів MySQL допомагає кешувати результати, які призведуть до більш швидких; рим вилученням повторюваних результатів. Розмір кеш-пам'яті можна налаштувати так, щоб вона забезпечувала швидший доступ, надаючи результати тільки з кеша. InnoDB - огляд движка MySQL InnoDB, кеш ключів MylSAM та кеш запитів MySQL. Це допомагає виконувати повторні запити SELECT швидше.
• Ми повинні використовувати оператор WHERE замість HAVING, якщо ми не використовуємо пропозиція GROUP BY або інші агрегатні функції, такі як COUNT(), MIN(), MAX(), AVG() і т. д.
• Використовуйте інструкцію EXPLAIN для аналізу запиту з операторами WHERE, JOIN та індексами.

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

 EXPLAIN SELECT * FROM `sales_order_item` i
INNER JOIN sales_order o ON i.order_id = o.entity_id
INNER JOIN catalog_product_entity p ON p.sku = i.sku
INNER JOIN customer_entity c ON c.entity_id = o.customer_id
WHERE i.order_id = 42

Наведений нижче результат є висновком виконаного питання: 

************* ************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: o
  partitions: NULL
  type: const
possible_keys: PRIMARY,SALES_ORDER_CUSTOMER_ID
  key: PRIMARY
  key_len: 4
  ref: const
  rows: 1
  filtered: 100.00
  Extra: NULL
*************************** 2. row ******************** *******
  id: 1
  select_type: SIMPLE
  table: c
  partitions: NULL
  type: const
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 4
  ref: const
  rows: 1
  filtered: 100.00
  Extra: NULL
*************************** 4. row ******************** *******
  id: 1
  select_type: SIMPLE
  table: p
  partitions: NULL
  type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_SKU
  key: CATALOG_PRODUCT_ENTITY_SKU
  key_len: 195
  ref: ecommerce.i.sku
  rows: 1
  filtered: 100.00
  Extra: Using index condition
  4 rows in set (0.01 sec)

У наведених вище результатах possible_keys показує, які індекси застосовуються до цього запиту, а KEY каже про те, який із них був фактично використаний. У нашому прикладі кожне з'єднання JOIN використовує індексний ключ; шляхом створення індексу ми повинні спробувати уникнути NULL ключа. Поле ROWS повідомляє нам, скільки рядків сканується в запиті, виконуваному для ідентифікації, і це число повинно бути зменшено для кращої продуктивності запиту, оскільки це мінімізує передачу даних.

Оптимізаця(дефрагментація) таблиць БД.

//Оптимізує одну таблицю:
mysql> OPTIMIZE TABLE Egle;
//Оптимізує декілька таблиць:
mysql> OPTIMIZE TABLE Egle, Depart, Manager

Декілька моментів, які потрібно знати про оптимізацію таблиць:

Оптимізація таблиць може бути виконана для InnoDB або MyISAM або архівів таблиць.
Для таблиці MyISAM буде створено аналіз таблиці та дефрагментацію відповідних даних файлу MySQL та звільнення невикористовуваного простору.
Якщо у вас є індекси, вони також змінюють сторінки індексу та оновлюють статистику.

Хороший мануал - http://www.mysql.ru/docs/man/