Structured Query Language (SQL) є основою реляційних баз даних. Незалежно від того, чи ви початківець, чи досвідчений адміністратор баз даних, розуміння SQL команд є важливим для ефективного проектування, управління та запитів до баз даних. Цей блог проведе вас через базові, середні та просунуті SQL команди, охоплюючи все — від створення таблиць до індексації та оптимізації.
1. Базові SQL Команди
В основі SQL знаходяться команди для створення, зміни та взаємодії з об'єктами баз даних, такими як таблиці та дані. Почнемо з основ.
Мова визначення даних (DDL): Визначення структури бази даних
Команди DDL допомагають визначити структуру баз даних, таблиць та зв'язків.
CREATE: Створення бази даних або таблиці.
CREATE DATABASE my_database;
CREATE TABLE my_table (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT );
ALTER: Зміна існуючих таблиць, наприклад, додавання або видалення стовпців.
ALTER TABLE my_table ADD address VARCHAR(255);
ALTER TABLE my_table DROP COLUMN address;
DROP: Постійне видалення бази даних або таблиці.
DROP DATABASE my_database; DROP TABLE my_table;
TRUNCATE: Швидке видалення всіх рядків з таблиці.
TRUNCATE TABLE my_table;
Мова маніпулювання даними (DML): Управління даними
Команди DML використовуються для маніпулювання фактичними даними в таблицях.
INSERT: Додавання нових рядків даних.
INSERT INTO my_table (name, age) VALUES ('Alice', 25);
SELECT: Отримання даних.
SELECT name, age FROM my_table WHERE age > 20;
UPDATE: Зміна існуючих рядків.
UPDATE my_table SET age = 30 WHERE name = 'Alice';
DELETE: Видалення рядків за певною умовою.
DELETE FROM my_table WHERE age < 20;
Мова запитів до даних (DQL): Запити до даних
SELECT — це основна команда для запитів до даних.
SELECT name, age FROM my_table WHERE age > 20 ORDER BY age DESC;
2. Середні SQL Команди
Коли ви освоїте базові команди, середні SQL команди дозволяють працювати з кількома таблицями, агрегувати дані та накладати обмеження.
Об'єднання: Об'єднання даних з кількох таблиць
Об'єднання є потужним інструментом для поєднання рядків з двох або більше таблиць за спільними стовпцями.
INNER JOIN: Отримання відповідних рядків з обох таблиць.
SELECT orders.id, customers.name FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id;
LEFT JOIN: Усі рядки з лівої таблиці, з відповідними рядками з правої.
SELECT customers.name, orders.id FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
RIGHT JOIN: Усі рядки з правої таблиці, з відповідними рядками з лівої.
SELECT orders.id, customers.name FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;
Групування та агрегація
Агрегуйте дані та групуйте їх для отримання цінних результатів.
Агрегатні функції: Виконання обчислень.
SELECT COUNT(*), AVG(age), MAX(age) FROM my_table;
GROUP BY: Організація даних у групи.
SELECT age, COUNT(*) FROM my_table GROUP BY age;
HAVING: Фільтрація агрегованих даних.
SELECT age, COUNT(*) FROM my_table GROUP BY age HAVING COUNT(*) > 2;
Обмеження
Обмеження накладають правила на дані.
Primary Key: Унікально ідентифікує рядки.
ALTER TABLE my_table ADD PRIMARY KEY (id);
Foreign Key: Забезпечує зв'язки між таблицями.
ALTER TABLE orders ADD CONSTRAINT
fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);
Unique: Забезпечує унікальні значення у стовпці.
ALTER TABLE my_table ADD UNIQUE (name);
Транзакції
Транзакції забезпечують консистентність даних.
BEGIN, COMMIT, і ROLLBACK:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;
3.
Просунуті SQL Команди
На просунутому рівні SQL пропонує інструменти для оптимізації, складних запитів та роботи з великими наборами даних.
Підзапити
Підзапити дозволяють вкладати запити для складнішої логіки.
SELECT * FROM orders WHERE customer_id IN (
SELECT id FROM customers WHERE city = 'New York'
);
Віконні функції
Віконні функції обчислюють значення по «вікну» рядків.
SELECT customer_id,
RANK() OVER (PARTITION BY city ORDER BY total_spent DESC) AS rank
FROM customers;
Індексація
Індекси покращують швидкість виконання запитів.
Створення індексу:
CREATE INDEX idx_customer_name ON customers(name);
Унікальний індекс:
CREATE UNIQUE INDEX idx_unique_email ON customers(email);
Композиційний індекс:
CREATE INDEX idx_name_city ON customers(name, city);
Видалення індексу:
DROP INDEX idx_customer_name ON customers;
Збережені процедури
Інкапсулюють SQL логіку для повторного використання.
CREATE PROCEDURE GetCustomerOrders (IN customer_id INT)
BEGIN
SELECT * FROM orders WHERE customer_id = customer_id;
END;
CALL GetCustomerOrders(1);
Тригери
Автоматизують дії при певних подіях в таблицях.
CREATE TRIGGER after_insert_orders
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_log (order_id, created_at) VALUES (NEW.id, NOW());
END;
Загальні вирази таблиць (CTE)
CTE спрощують складні запити і роблять їх більш зрозумілими.
WITH customer_totals AS (
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT * FROM customer_totals WHERE total_spent > 1000;
Пошук за повним текстом
Використовується для ефективного пошуку тексту.
CREATE FULLTEXT INDEX idx_fulltext ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST ('keyword');
4. Спеціалізовані SQL Техніки
Рекурсивні запити
Операції з ієрархічними даними, як-от організаційні схеми.
WITH RECURSIVE org_chart AS (
SELECT employee_id, manager_id, name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.name
FROM employees e
INNER JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;
Розподілення
Розподіляє велику таблицю на менші, керовані частини.
CREATE TABLE orders_partitioned (
order_id INT,
order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN (2023)
);
Висновок
SQL — це велика та потужна мова, яка включає команди для управління базами даних на кожному рівні — від створення базових таблиць до оптимізації продуктивності за допомогою індексації та розподілення. Оволодіння всіма командами SQL не лише зробить вас експертом з запитів та управління даними, але й дозволить проектувати ефективні та масштабовані системи баз даних.
Досліджуйте ці команди, практикуйте їх на реальних наборах даних і відкривайте справжній потенціал своїх навичок управління базами даних! 💻
Дайте знати, якщо хочете побачити туторіал або приклади для конкретних команд! 😊
Перекладено з: The Ultimate Guide to SQL Commands: From Basics to Advanced