Я готовлюсь на вакансию Python Junior разработчик и мне так или иначе понадобятся знания по базам данных. В данный момент я доделываю, своё первое приложение, о котором я писал тут, чтобы оно могло сохранять заметки в базу данных. Правда я использую базу данных sqlite3, в ней присутствует не все операторы, не все функции SQL по сравнению например с MySQL или MariaDB и остальными.
Однако преимущество sqlite3 состоит в том что не нужно дополнительно ничего устанавливать на компьютер, я имею ввиду отдельную программу-сервер для базы данных. А также, то что sqlite3 чуть попроще остальных, то для меня, как для начинающего это только плюс.
Но, есть определенный язык SQL, это основной синтаксис, от которого уже отталкиваются различные существующие базы данных и делают свой синтаксис похожим, дополняют уже существующий язык SQL. Для себя я сделал по нему небольшой справочник. Справочник в котором указаны основы и может не годиться для обучения с нуля, но если вы уже пытались учить базы данных, то справочник как раз то что нужно, это то куда можно подглядывать в случае чего.
Заранее извиняюсь что нет подсветки кода, да, это справочник не прям для тех кто с нуля. Зато можно копировать. Просто в Дзене еще нет удобных инструментов для вставки кода и даже Markdown еще не поддерживается, так что я жду, просто жду пока это все появится)
1-ая нормальная форма
Все значения должны быть простыми. Одна ячейка содержит одно значение
2-ая нормальная форма
Каждая запись должна иметь уникальный идентификатор.
Запускаем MySQL консоль
mysql -u root -p
И вводим пароль пользователя root.
Посмотреть существующие базы данных
SHOW DATABASES;
Создание базы данных
CREATE DATABASE db_name;
Удаление базы данных
DROP DATABASE db_name;
Выбрать (использовать) конкретную базы данных
USE DATABASE db_name;
Посмотреть существующие таблицы
SHOW TABLES;
Операторы
<><=>=IS NULL/ IS NOT NULL<> - не равно
Создание таблицы
CREATE TABLE teacher (
id INT AUTO_INCREMENT PRIMARY KEY,
surname VARCHAR(255) NOT NULL
);
Посмотреть поля в таблице
DESC teacher;
либо
SHOW COLUMNS FROM teacher;
Создания отношения один ко многим
one-to-many
CREATE TABLE lesson (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(id));
Внесение данных в таблицы
INSERT INTO teacher (surname) VALUES ("Иванов"), ("Петров"), ("Самойлов"), ("Чижин"), ("Петров");
DELETE. Удаление записи таблицы
DELETE FROM teacher WHERE id > 8;
Посмотреть все данные таблицы
SELECT * FROM teacher;
Посмотреть данные определенного поля таблицы
SELECT surname FROM teacher;
Посмотреть данных нескольких полей таблицы
SELECT id, surname FROM teacher;
DISTINCT. Получить уникальные значения поля таблицы
SELECT DISTINCT surname FROM teacher;
WHERE. Получить значения поля по определенному условию
SELECT * FROM teacher WHERE id=1;
или
SELECT * FROM teacher WHERE surname="Петров";
LIMIT. Ограничения по количеству получаемых записей
SELECT * FROM teacher WHERE id > 1 LIMIT 2;
либо буз указания какого-либо условия
SELECT * FROM teacher LIMIT 2;
Либо можно вывести отрезок, указав сколько элементов в начале пропустить и сколько элементов показать.
SELECT * FROM teacher LIMIT 2, 5;
Важно помнить что LIMIT записывается в сегда в конце.
AS. Изменяем отображение имен колонок таблицы при вывое данных
SELECT id AS 'Идентификатор', surname AS 'Фамилия' FROM teacher;
AS. Псевдонимы для таблиц
SELECT t.id, t.surname FROM teacher AS t;
ORDER BY. Сортировка данных
SELECT * FROM teacher ORDER BY surname;
ORDER BY DESC. Сортировка в обратном порядке
SELECT * FROM teacher ORDER BY id DESC;
ALTER ADD. Добавление колонки в существующую таблицу
ALTER TABLE teacher ADD age INT;
Значения в каждой строчке нового столбца равны NULL.
ALTER CHANGE. Меняем параметры существующего поля в таблице
ALTER TABLE article CHANGE create_date create_date DATE NOT NULL;
ALTER DROP. Удаление поля из существующей таблицы
ALTER TABLE article DROP COLUMN create_date;
TRUNCATE. Очистка таблицы
TRUNCATE article;
UPDATE SET. Обновление, изменение данных в существующей таблице
UPDATE teacher SET age=28 WHERE id=1;
либо указать без условия чтобы присвоить значение каждой записи
UPDATE teacher SET age=25;
WHERE LIKE. поиск по шаблону
SELECT * FROM teacher WHERE surname LIKE "%ов";
% - означает любое количество любых символов
_ - один любой символ
Показать все записи у которых в фамилии вторая буква "е":
SELECT * FROM teacher WHERE surname LIKE "_е%";
Показать все записи у кого фамилия из трех буква:
SELECT * FROM teacher WHERE surname LIKE "___";
AND
SELECT * FROM teacher WHERE id > 2 AND age < 50;
OR
SELECT * FROM teacher WHERE id > 4 OR age < 50;
NOT
SELECT * FROM teacher WHERE NOT id = 2;
BETWEEN AND
SELECT * FROM teacher WHERE age BETWEEN 35 AND 45;
IN(). Список элементов
SELECT * FROM teacher id IN(4, 5, 2, 1);
JOIN/INNER JOIN. Внутреннее объединение
Получим список учителей которые ведут уроки и только их
SELECT teacher.surname, lesson.title FROM teacher INNER JOIN lesson ON teacher.id = lesson.teacher_id;
LEFT OUTER JOIN. Левостороннее внешнее объединение
Выведем не только тех учителей у кого есть уроки, но всех учителей, учетеля это левая таблица.
SELECT teacher.surname, lesson.title FROM teacher LEFT OUTER JOIN lesson ON teacher.id=lesson.teacher_id;
RIGHT OUTER JOIN. Правостороннее внешнее объединение
Вернем спосок учителей которые ведут уроки и весь список уроков
SELECT teacher.id, lesson.title FROM teacher RIGHT OUTER JOIN lesson ON teacher.id = lesson.teacher_id;
Вертикальное объединение таблиц
SELECT * FROM teacher UNION SELECT * FROM lesson;
AVG. Функция возвращает среднее значение
SELECT AVG(age) FROM teacher;
MAX. Функция возвращает максимальное из значений
SELECT MAX(age) FROM teacher;
MIN. Функция возвращает минимальное из значений
SELECT MIN(age) FROM teacher;
SUM. Функция которая считает сумму всех значений
SELECT SUM(age) FROM teacher;
COUNT, GROUP BY. Группировка значений таблицы
SELECT surname, COUNT(surname) FROM teacher GROUP BY surname;
UCASE/LCASE. Функции для работы с текстом
SELECT UCASE(title), LCASE(title) FROM shop;
UCASE - WORD, LCASE - word.
CONCAT. Функция конкатенации записей
С помощью данной функции можно вывести данные из разных записей в одной записи, ячейке
SELECT CONCAT(teacher.surname, ' купил ', shop.title) FROM teacher
JOIN shop ON teacher.id = shop.id;
Создание индексов полей
Индекс создается для определенного поля, чтобы увеличть скорость поиска по записям этого поля. Внешне ничего не меняется, это только внутреннее изменение. Изменится только скорость поиска по полю.
CREATE INDEX surname_index ON teacher(surname);
Удаление существующего индекса поля
DROP INDEX surname_index ON teacher;
Примеры интересные
CREATE TABLE orders(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_number INT,
shop_id INT,
person_id INT,
date_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_number INT,
shop_id INT NOT NULL,
person_id INT NOT NULL,
date_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (shop_id) REFERENCES shop (id),
FOREIGN KEY (person_id) REFERENCES teacher (id)
);
SELECT orders.order_number, teacher.surname, shop.title FROM teacher
INNER JOIN orders ON teacher.id = orders.person_id
INNER JOIN shop ON shop.id = orders.shop_id;
DROP TABLE T1, T2;
CREATE TABLE T1 (num1 INT);
CREATE TABLE T2 (num2 INT);
INSERT INTO T1 (num1) VALUES (1), (1), (1);
INSERT INTO T2 (num2) VALUES (1), (1), (1), (1);
SELECT * FROM T1;
SELECT * FROM T2;
SELECT * FROM T1 LEFT JOIN T2 ON T1.num1 = T2.num2;
DROP TABLE T1, T2;
Заметки
С NULL ничего сравнивать нельзя.
UNION при вертикальном объединении смотрит есть ли одинаковые значения и объединяет их без повторения, он уберёт одинаковые значения которые даже есть в рамках одной таблицы, а UNION ALL при объединении добавляет всё несмотря на значения.
SELECT ID_ITEM, NAME_ITEM, EXTRACT(YEAR FROM DATE_IMPORT) AS YEAR_IMPORT
FROM Table1
WHERE YEAR_IMPORT > 2010;
Это пример с ошибкой, так как в конце кода мы используем новое имя для проверки, но следует помнить что переименование делается в самую последнюю очередь, но не позже блока сортировки, то есть в коде нового имени еще не существует.
FULL JOIN не существует в MariaDB, вместо него придется использовать UNION ALL ... WHERE t1.id IS NULL.