Основы SQL. Часть2.

[Gudaus]

Суть реляционной базы данных​

Современные БД реляционные. Это значит, что данные во избежание избыточности хранятся не в одной таблице, а в нескольких. Таблицы связываются между собой по определённым полям. Зачем это сделано?
Когда вы заходите в веб-часть бан-листа, то видите инфу о бане: админ, игрок, срок, причина, начало, конец. Там же хранятся админки, у них тоже есть сроки истечения.
Если всё хранить в одной таблице, то типичная структура выглядит примерно так.

SELECT * FROM TestBans



Обратите внимание на поля AdminTill(дата окончания админки), и AdminFlags(флаги админа).
Логично ли это держать в одной таблице с банами?
Нет. Банов у нас будет много тысяч, а админов человек 15. И в каждой строчке тянется абсолютно левая инфа, не относящаяся к бану напрямую. А если мы продлим срок админки? Придётся во всех тысячах строк менять поле AdminTill.
Представьте заодно работу базы. При заходе на сервер игрока мы должны проверить, админ ли он. Идём в эту таблицу, ищем админа по AdminSteamid, берём первую по совпадению запись и смотрим на его AdminTill. При этом может так оказаться, что не во всех строках продлена админка, в итоге игрок не пройдёт валидацию как админ.
У админа может смениться SteamID. Как быть в таком случае? Мы можем по всей базе изменить поле AdminSteamid, но тогда во всех банах будет указан новый, а не старый. Уже искажаются данные. Придётся вводить ещё одно поле. В одном будем писать нынешний реальный SteamId админа, а другом - SteamID на момент бана.
Во избежание подобных ситуаций часть информации об админе выделяют в отдельную таблицу.
Таблица с банами будет называться TestBans, а с админами - TestAdmins.

Для начала создадим TestAdmins.

CREATE TABLE IF NOT EXISTS TestAdmins (
AdminID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
AdminSteamid VARCHAR(32),
AdminTill Datetime,
AdminFlags VARCHAR(32),
AdminName VARCHAR(100) );

Из TestBans перенесём инфу в TestAdmins. Сделаем при помощи подзапроса.

INSERT TestAdmins (AdminSteamid,  AdminTill,  AdminFlags, AdminName)
SELECT AdminSteamid,  AdminTill,  AdminFlags, AdminName FROM TestBans;

AdminID автоинкрементный, посему проставится сам.
Обратите внимание, что я не указал оператор VALUES. Мы сейчас вставляем не конкретные значения, а выборку записей.

Что дальше? В TestAdmins 4 записи, 3 из которых одинаковые. Такое оставлять нельзя, иначе теряется смысл декомпозиции. который заключается в оптимизации. Надо удалить дубликаты. 1 строка = 1 админ.
Для начала найдём полностью уникальные строки и сгруппируем. То есть строки в которых все поля идентичны.

SELECT * FROM TestAdmins GROUP BY AdminSteamid, AdminTill, AdminFlags, AdminName



Теперь содержимое TestAdmins надо заменить на это. Можно через промежуточную таблицу, можно просто из TestAdmins удалить строки, в которых AdminID не равно 1 или 3. Более правильным является создание промежуточной таблицы по 2 причинам:

1. Строк может быть не 2 как у нас, а 102. Сколько угодно.
2. Ручное перечисление AdminID противоречит автоматизации. В дальнейшем мы можем просто написать серверную функцию, которая всё это делает сама в 1 клик.

Посему алгоритм будет таков:

1. Создаём таблицу с такой же структурой, как у TestAdmins, назовём её PromAdmins
2. Записываем в неё выборку.
3. Удаляем все записи из TestAdmins.
4. Переписываем содержимое PromAdmins в TestAdmins
5. Удаляем PromAdmins

CREATE TABLE IF NOT EXISTS PromAdmins (
      AdminID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      AdminSteamid VARCHAR(32),
      AdminTill Datetime,
      AdminFlags VARCHAR(32),
      AdminName VARCHAR(100) );

INSERT PromAdmins (AdminSteamid,  AdminTill,  AdminFlags, AdminName)
SELECT AdminSteamid, AdminTill, AdminFlags, AdminName
FROM TestAdmins GROUP BY AdminSteamid, AdminTill, AdminFlags, AdminName;

DELETE FROM TestAdmins;

INSERT TestAdmins (AdminSteamid,  AdminTill,  AdminFlags, AdminName)
SELECT AdminSteamid, AdminTill, AdminFlags, AdminName
FROM PromAdmins;

DROP TABLE PromAdmins;

SELECT * FROM TestAdmins;


Отлично, в TestAdmins 2 записи. Теперь их надо как-то связать в TestBans. Чтобы мы посмотрели в TestBans и сказали - это админ такой то из TestAdmins.
Как связать? По нику, флагам, сроку админки как-то глупо. По Steamid тоже. У админа может Steamid меняться, тогда в TestBans будет 2 разных Steamid, а в TestAdmins один. Как итог - ряд старых банов будет без админа. Остаётся только 1 параметр - AdminID. Он всегда неизменен и уникален поскольку AUTO_INCREMENT PRIMARY KEY.
Добавим в TestAdmins поле AdminID. Делать первичным автоинкрементным ключём не следует, так как на много банов придётся 1 админ и это поле не будет уникальным.

ALTER TABLE TestBans
ADD COLUMN AdminID SMALLINT UNSIGNED NOT NULL;

Добавили. Внесём в это поле номера из TestAdmins. Берём поле AdminSteamid и там, где оно в обеих таблицах совпадает, проставляем AdminID.
Такой запрос - небольшое забегание вперёд, про работу с 2 таблицами будет дальше.

UPDATE TestBans t1
JOIN TestAdmins t2 ON t2.AdminSteamid = t1.AdminSteamid
SET t1.AdminID = t2.AdminID
WHERE t2.AdminSteamid = t1.AdminSteamid

Всё, теперь мы можем точно сказать, что такой-то игрок забанен админом №9, а такой-то админом №10. У админа срок до такого-то числа, флаги такие-то и.т.д.
Нужна нам информация про админа в таблице TestBans? Нет, не нужна, она уже вся содержится в TestAdmins.
Удаляем лишние поля из TestBans

ALTER TABLE TestBans
DROP COLUMN AdminSteamid,
DROP COLUMN AdminTill,
DROP COLUMN AdminFlags,
DROP COLUMN AdminName ;

Избыточность устранена, косяков в архитектуре нет.

Select * from TestBans


Select * from TestAdmins


Хорошо, у нас 2 таблицы. Как теперь с ними работать? Как нам понять, например, сколько игроков забанил каждый админ?
Для осуществления выборок из нескольких объединённых таблиц используются операторы JOIN и WHERE.

INNER JOIN или "Внутреннее объединение"
​

Синтаксис: SELECT <поля> FROM <Таблица1> Inner join <Таблица2> on <поле Таблицы1>=<поле Таблицы2>

При этом желательно поля указывать в формате <Имя Таблицы>.<Имя Поля>. Почему? Посмотрите на поле AdminID, у нас оно присутствует в обоих таблицах. Откуда SQL может знать, какое именно мы имеем в виду?
Если в обеих таблицах имя поля уникально(то есть одно имя на все таблицы), то SQL поймёт что мы имеем в виду. Если же имя поля не уникально(в таблицах есть одинаковые имена) - потребует уточнить.
В дальнейшем при разработке базы вы столкнётесь с одной неприятной вещью - в таблицы регулярно добавляются новые поля. И если сегодня у 2 таблиц нет полей с одинаковым названием, то завтра обязательно будут. Поэтому сразу берите за привычку писать в формате
<Имя Таблицы>.<Имя Поля>.

SELECT * FROM TestBans
INNER JOIN TestAdmins on TestBans.AdminID = TestAdmins.AdminID

Вернёт первоначальную таблицу "всё-одном".
Объединение произошло по ключевому полю AdminID.
Что делает SQL?
Берёт поле TestAdmins.AdminID и ищет такое же значение в TestBans.AdminID. Когда находит - объединяет строки из обоих таблиц в одну.

Аналогом INNER JOIN является запрос с WHERE. Выглядит так

SELECT * FROM TestBans, TestAdmins WHERE TestBans.AdminID=TestAdmins.AdminID

Оба запроса выполняют одно и то же.

Так всё-таки, сколько же игроков забанил каждый админ?

SELECT AdminName, count(*) FROM TestBans, TestAdmins
WHERE TestBans.AdminID=TestAdmins.AdminID
GROUP BY TestBans.AdminID

LEFT JOIN или "Левое внешнее объединение"
​

Берётся таблица, к ней слева присоединяется ещё одна таблица.
Иногда вы встретите LEFT OUTER JOIN. Это то же самое.
Есть RIGHT JOIN и RIGHT OUTER JOIN, работают аналогично.

Алгоритм работы LEFT JOIN:

1. Сначала происходит внутреннее объединение (INNER JOIN) левой и правой таблиц.
2. Затем, в результат добавляются записи левой таблицы не вошедшие в результат формирования таблицы внутренним объединением. Для них, соответствующие записи из правой таблицы заполняются значениями NULL.

SELECT TestBans.Name, TestBans.Steamid,TestBans.Reason, TestAdmins.AdminID,TestAdmins.AdminName
FROM TestBans
LEFT JOIN TestAdmins on TestBans.AdminID=TestAdmins.AdminID

Результат как у INNER JOIN.
Добавим в список админов ещё одного и посмотрим, что будет в этом случае

INSERT TestAdmins (AdminSteamid, AdminTill, AdminFlags, AdminName)
VALUES ('STEAM_0:1:4904612032', '2018-09-11 21:09:50','abchfr', 'Варвара');

SELECT TestBans.Name, TestBans.Steamid,TestBans.Reason, TestAdmins.AdminID,TestAdmins.AdminName
FROM TestAdmins
LEFT JOIN TestBans on TestBans.AdminID=TestAdmins.AdminID ;

СТОП! Почему получили снова 4 строки? добавляли же вроде админа.
Всё дело в том, что у LEFT JOIN важен порядок. Сперва мы указываем основную таблицу, а затем - внешнюю, которую присоединяем к основной. Основная была TestBans, новый админ из TestAdmins просто не присоединился. Делаем наоборот.

SELECT TestBans.Name, TestBans.Steamid,TestBans.Reason, TestAdmins.AdminID,TestAdmins.AdminName
FROM TestAdmins
LEFT JOIN TestBans on TestBans.AdminID=TestAdmins.AdminID

SQL взял список ВСЕХ админов. Там, где есть совпадения по AdminID, все записи добавлены. Где нету - проставлен NULL.

А теперь представим, что этот запрос мы выгружаем в отчёт начальству. Нужен им NULL? Нет, им привычнее прочерки или нули. Лучше прочерки.

SELECT IFNULL(TestBans.Name,'-'),  IFNULL(TestBans.Steamid,'-'), IFNULL(TestBans.Reason,'-') ,
TestAdmins.AdminID,TestAdmins.AdminName
FROM TestAdmins
LEFT JOIN TestBans on TestBans.AdminID=TestAdmins.AdminID

Оператор IFNULL имеет синтаксис IFNULL( <поле>, <значение, если в строке поля стоит NULL> ) и преображает NULL в любое другое значение.

Один вопрос решили, переходим ко второму. Посмотрите на заголовки полей, они отвратные. Ещё и IFNULL туда залез.
Переименовывать поля поможет оператор AS. Синтаксис: <имя поля>AS<новое имя поля>

SELECT IFNULL(TestBans.Name,'-') AS 'Имя игрока',
       IFNULL(TestBans.Steamid,'-') AS 'СтимАйди игрока',
       IFNULL(TestBans.Reason,'-') AS 'Причина бана',
       TestAdmins.AdminID AS 'Номер Админа',
       TestAdmins.AdminName AS 'Имя админа'
FROM TestAdmins
LEFT JOIN TestBans on TestBans.AdminID=TestAdmins.AdminID

Все права на статью принадлежат Dev-CS.ru TEAM. При копировании материала активная ссылка обязательна.

Основы SQL Часть 1.
Основы SQL Часть 3.

 

[Gudaus]

Перенесено в общий раздел.