Робота з базами SQL

CREATE TABLE: Створюємо таблицю

Для створення таблиць в SQL використовується вираз CREATE TABLE. Він приймає в якості параметра імена стовпців, які ми хочемо внести, а також їх типи даних.

Створимо три таблиці з назвою "genders", "users" та "contacts",

У таблиці "genders" буде три стовпці:

id - порядковий номер ґендера тип INT, це унікальний ключ
name - назва ґендера тип VARCHAR(30)
created_at - час створення запису тип TIMESTAMP, значення за замовчуванням поточний час та дата

Код для створення:

CREATE TABLE genders (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

У таблиці "users" у нас буде сім стовпців:

id - порядковий номер контакту тип INT, це унікальний ключ
name - ім'я користувача, тип VARCHAR(30)
email - електронна адреса користувача, тип VARCHAR(30)
password - пароль користувача, тип VARCHAR(30)
age - вік користувача, тип TINYINT UNSIGNED
gender_id - це foreign key, який пов'язує таблицю "users" та "genders" зв'язком один до багатьох. Один гендер може бути у багатьох користувачів.
created_at - час створення запису тип TIMESTAMP, значення за замовчуванням поточний час та дата

Код для створення:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    email VARCHAR(30),
    password VARCHAR(30),
    age TINYINT UNSIGNED,
    gender_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (gender_id) REFERENCES genders (id)
          ON DELETE SET NULL
          ON UPDATE CASCADE
);

Тут у нас з'явилася інструкція:

    FOREIGN KEY (gender_id) REFERENCES genders (id)
          ON DELETE SET NULL
          ON UPDATE CASCADE

Вона каже, що для стовпця gender_id у таблиці users необхідно створити посилання на таблицю genders, причому значення стовпця буде збігатися з значенням стовпця id у таблиці genders для конкретного запису. Запис ON DELETE SET NULL каже, що якщо буде видалено запис у таблиці genders ми повинні значення для стовпця gender у таблиці users встановити у значення NULL. Запис ON UPDATE CASCADE каже, що якщо буде змінено значення поля id у таблиці genders, то значення для стовпця gender_id у таблиці users також автоматично буде змінено.

У таблиці "contacts" буде сім стовпців:

id - порядковий номер контакту тип INT, це унікальний ключ
name - назва контакту тип VARCHAR(30)
email - електронна адреса контакту тип VARCHAR(30)
phone - телефон контакту тип VARCHAR(30)
favorite - контакт знаходиться в обраному чи ні, логічного типу
user_id - це foreign key, який пов'язує таблицю "contacts" та "users" зв'язком один до багатьох. Один користувач може мати багато контактів.
created_at - час створення запису тип TIMESTAMP, значення за замовчуванням поточний час та дата

Код виглядатиме ось так:

CREATE TABLE contacts (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    email VARCHAR(30),
    phone VARCHAR(30),
    favorite BOOLEAN,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id)
          ON DELETE CASCADE
          ON UPDATE CASCADE
);

Після цього наша база даних матиме наступну ER-діаграму

INSERT: Ввід данних

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

Вставимо значення в таблицю genders:

INSERT INTO genders (id, name)
VALUES (1, 'male'), (2, 'female');

Ми не вказали при вставці значення для поля created_at, але завдяки інструкції DEFAULT CURRENT_TIMESTAMP значення буде підставлено автоматично.

Вставимо значення до таблиці users:

INSERT INTO users (id, name, email, password, age, gender_id)
VALUES (1, 'Boris', 'boris@test.com', 'password', 23, 1),
(2, 'Alina', 'alina@test.com', 'password', 32, 2),
(3, 'Maksim', 'maksim@test.com', 'password', 40, 1);

Вставимо значення до таблиці contacts:

INSERT INTO contacts (id, name, email, phone, favorite, user_id)
VALUES (1, 'Allen Raymond', 'nulla.ante@vestibul.co.uk', '(992) 914-3792', 0, 1),
(2, 'Chaim Lewis', 'dui.in@egetlacus.ca', '(294) 840-6685', 1, 1),
(3, 'Kennedy Lane', 'mattis.Cras@nonenimMauris.net', '(542) 451-7038', 1, 2),
(4, 'Wylie Pope', 'est@utquamvel.net', '(692) 802-2949', 0, 2),
(5, 'Cyrus Jackson', 'nibh@semsempererat.com', '(501) 472-5218', 0, null);

SELECT: Отримання даних

Цей запит використовується у випадку, якщо нам потрібно показати дані у таблиці. Напевно, найпростішим прикладом використання SELECT буде наступний запит:

SELECT * FROM contacts

Результатом цього запиту буде таблиця з усіма даними у таблиці contacts.

id	name	email	phone	favorite	user	created_at
1	Allen Raymond	nulla.ante@vestibul.co.uk	(992) 914-3792	0	1	2021-06-06 23:18:08
2	Chaim Lewis	dui.in@egetlacus.ca	(294) 840-6685	1	1	2021-06-06 23:18:08
3	Kennedy Lane	mattis.Cras@nonenimMauris.net	(542) 451-7038	1	2	2021-06-06 23:18:08
4	Wylie Pope	est@utquamvel.net	(692) 802-2949	0	2	2021-06-06 23:18:08
5	Cyrus Jackson	nibh@semsempererat.com	(501) 472-5218	0		2021-06-06 23:18:08

Знак зірочки * означає те, що ми хочемо показати всі стовпці з таблиці без винятків. Так як у базі даних зазвичай більше однієї таблиці, нам необхідно вказувати назву таблиці, дані з якої хочемо подивитися. Це робиться використовуючи ключове слово FROM.

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

SELECT name, email FROM contacts ORDER BY name

Також іноді нам потрібно відсортувати дані, що виводяться. Для цього ми використовуємо ORDER BY "назва стовпця". ORDER BY має два модифікатори: ASC сортувати за зростання, значення за замовчуванням і DESC сортувати за спаданням

name	email
Allen Raymond	nulla.ante@vestibul.co.uk
Chaim Lewis	dui.in@egetlacus.ca
Cyrus Jackson	nibh@semsempererat.com
Kennedy Lane	mattis.Cras@nonenimMauris.net
Wylie Pope	est@utquamvel.net

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

У наступному запиті ми виведемо лише вибрані контакти.

SELECT name, email
FROM contacts
WHERE favorite = true
ORDER BY name

Вивід:

name	email
Chaim Lewis	dui.in@egetlacus.ca
Kennedy Lane	mattis.Cras@nonenimMauris.net

Умови WHERE можуть бути написані з використанням логічних операторів AND та OR, а також математичних операторів порівняння (=, <, >, <=, >=, <>).

Умови WHERE можуть бути записані з використанням ще декількох команд, якими є:

IN - порівнює значення в стовпці з кількома можливими значеннями і повертає true, якщо значення збігається хоча б із одним значенням

SELECT name, email
FROM users
WHERE age IN(20, 30, 40)
ORDER BY name

Вивід:

name	email
Maksim	maksim@test.com

BETWEEN - перевіряє, чи є значення в якомусь проміжку

SELECT name, email, age
FROM users
WHERE age BETWEEN 30 AND 40
ORDER BY name

Вивід:

name	email	age
Alina	alina@test.com	32
Maksim	maksim@test.com	40

LIKE - шукає по шаблону

Також, якщо ми хочемо вивести всі контакти, в імені яких є буква 'L', ми можемо використати наступний запис:

SELECT name, email
FROM contacts
WHERE name LIKE '%L%'
ORDER BY name

Знак % означає будь-яку послідовність символів (0 символів також вважається за послідовність).

Вивід:

name	email
Allen Raymond	nulla.ante@vestibul.co.uk
Chaim Lewis	dui.in@egetlacus.ca
Kennedy Lane	mattis.Cras@nonenimMauris.net
Wylie Pope	est@utquamvel.net

В SQL також є інверсія. Для цього треба написати NOT перед будь-яким логічним висловом в умові (NOT BETWEEN і так далі).

SELECT name, email, age
FROM users
WHERE age NOT BETWEEN 30 AND 40
ORDER BY name

Вивід:

name	email	age
Boris	boris@test.com	23

Функції агрегації

SQL має безліч вбудованих функцій для виконання різних операцій. Ми розглянемо найчастіше використовувані:

COUNT() - повертає число рядків
SUM() - повертає суму всіх полів з числовими значеннями в них
AVG() - повертає середнє значення серед рядків
MIN()/MAX() - повертає мінімальне/максимальне значення серед рядків

Знайти мінімальний вік серед користувачів

SELECT min(age) as minAge
FROM users

Вивід:

minAge
23

Знайти середній вік користувачів:

SELECT avg(age) as averageAge
FROM users

Вивід:

averageAge
31.666666666666668

Давайте знайдемо кількість контактів для кожного користувача за допомогою функції COUNT. Але в операторі SELECT нам слід додати рядок GROUP BY user_id. Оскільки функція є агрегованою по полю user_id, і необхідно групувати значення по імені.

SELECT COUNT(user_id) as total_contacts, user_id
FROM contacts
GROUP BY user_id

Вивід:

total_contacts	user_id
0
2	1
2	2

Як і очікувалося, у кожного користувача ми додали два контакти. І є контакт без користувача.

Вкладені SELECT

Поки що ми розглядали лише прості запити. Але часто необхідно використовувати так звані вкладені запити або підзапити.

Нехай необхідно вивести контакти для користувачів, вік яких менше 30 років.

Перший запит, знайти id користувачів молодше 30 років

SELECT id
FROM users
WHERE age < 30

Потім ми об'єднуємо запити за допомогою WHERE:

SELECT *
FROM contacts
WHERE user_id IN (SELECT id
    FROM users
    WHERE age < 30)

Вивід:

id	name	email	phone	favorite	user_id	created_at
1	Allen Raymond	nulla.ante@vestibul.co.uk	(992) 914-3792	0	1	2021-06-07 07:59:35
2	Chaim Lewis	dui.in@egetlacus.ca	(294) 840-6685	1	1	2021-06-07 07:59:35

Псевдоніми

У попередніх прикладах ми вже використали псевдоніми. Щоб привласнити стовпцю псевдонім, можна використовувати ключове слово AS:

SELECT id, name as fullName, phone as mobile
FROM contacts

Вивід:

id	fullName	mobile
1	Allen Raymond	(992) 914-3792
2	Chaim Lewis	(294) 840-6685
3	Kennedy Lane	(542) 451-7038
4	Wylie Pope	(692) 802-2949
5	Cyrus Jackson	(501) 472-5218

Псевдоніми часто використовують коли починають працювати зі зв'язаними таблицями.

JOIN: Приєднання таблиць

У базах даних найчастіше таблиці пов'язані між собою. Наприклад, у нас таблиці users та genders пов'язані між собою по полю gender_id, а таблиці contacts та users по полю user_id.

Для приєднання таблиць використовують оператор JOIN. Розглянемо конкретні приклади.

Як ми вже говорили у таблиці users є стовпець gender_id, в даному випадку він є так званим foreign key і сполучною ланкою між двома таблицями. Якщо ми хочемо вивести всю інформацію про користувача, включаючи інформацію про нього гендері, нам необхідно підключити другу таблицю genders. Чтобы это сделать, можна використовувати INNER JOIN , де умова об'єднання задається за допомогою ON:

SELECT u.id, u.name, u.email, g.name AS gender
FROM users AS u
INNER JOIN genders AS g ON g.id = u.gender_id

Вивід:

id	name	email	gender
1	Boris	boris@test.com	male
2	Alina	alina@test.com	female
3	Maksim	maksim@test.com	male

Це найпростіший приклад використання JOIN. Є ще кілька варіантів його використання:

(INNER) JOIN: Повертає записи, значення яких збігаються в обох таблицях.
LEFT (OUTER) JOIN: Повертає всі записи з лівої таблиці та відповідні записи із правої таблиці.
RIGHT (OUTER) JOIN: Повертає всі записи з правої таблиці та відповідні записи з лівої таблиці.
FULL (OUTER) JOIN: Повертає всі записи, якщо є збіги в лівій або правої таблиці

JOIN

У круглих дужках слово за замовчуванням не писати, тобто запису INNER JOIN та JOIN еквівалентні

Розглянемо конкретний приклад, щоб зрозуміти різницю між INNER JOIN та LEFT JOIN

SELECT c.id, c.name, c.email, u.name AS owner
FROM contacts AS c
JOIN users AS u ON u.id = c.user_id

Вивід:

id	name	email	owner
1	Allen Raymond	nulla.ante@vestibul.co.uk	Boris
2	Chaim Lewis	dui.in@egetlacus.ca	Boris
3	Kennedy Lane	mattis.Cras@nonenimMauris.net	Alina
4	Wylie Pope	est@utquamvel.net	Alina

У цьому випадку, у вибірці, ми не отримуємо контакту без користувача, так як використовувався перетин таблиць. Щоб отримати всі контакти, навіть якщо вони не мають власників з таблиці users нам треба використовувати LEFT JOIN

SELECT c.id, c.name, c.email, u.name AS owner
FROM contacts AS c
LEFT JOIN users AS u ON u.id = c.user_id

Вивід:

id	name	email	owner
1	Allen Raymond	nulla.ante@vestibul.co.uk	Boris
2	Chaim Lewis	dui.in@egetlacus.ca	Boris
3	Kennedy Lane	mattis.Cras@nonenimMauris.net	Alina
4	Wylie Pope	est@utquamvel.net	Alina
5	Cyrus Jackson	nibh@semsempererat.com	NULL

UPDATE: Зміна даних

Зміна даних у таблиці SQL це робиться за допомогою команди UPDATE.

Використання UPDATE включає в себе: перший вибір таблиці, в якій знаходиться поле, яке ми хочемо змінити, друге встановлення в запис нового значення за допомогою SET і третє це використання WHERE, щоб позначити конкретне місце у таблиці

У таблиці contacts у нас є запис з id = 5 у якого немає значення поля user_id. Давайте встановимо, що власником цього контакту буде користувач Maksim з id = 3 у таблиці users

UPDATE contacts SET user_id = 3 WHERE id = 5;

DELETE: Видалення записів з таблиці

Видалення запису з таблиці через SQL теж проста операція. Головне позначити за допомогою WHERE , що саме ми хочемо видалити. Інакше ми видалимо всі записи з таблиці, чого б хотілося уникнути.

DELETE FROM contacts WHERE id = 4;

Видалення таблиць

Якщо ми хочемо видалити всі дані з таблиці, але при цьому саму таблицю залишити, то слід використати команду TRUNCATE:

TRUNCATE TABLE contacts;

Якщо ми хочемо видалити саме саму таблицю, то нам слід використовувати команду DROP:

DROP TABLE contacts;

CREATE TABLE: Створюємо таблицю​

INSERT: Ввід данних​

SELECT: Отримання даних​

Функції агрегації​

Вкладені SELECT​

Псевдоніми​

JOIN: Приєднання таблиць​

UPDATE: Зміна даних​

DELETE: Видалення записів з таблиці​

Видалення таблиць​

CREATE TABLE: Створюємо таблицю

INSERT: Ввід данних

SELECT: Отримання даних

Функції агрегації

Вкладені SELECT

Псевдоніми

JOIN: Приєднання таблиць

UPDATE: Зміна даних

DELETE: Видалення записів з таблиці

Видалення таблиць