Проектируем базу данных

При проектировании таблицы можно начинать с жизненных и понятных нам понятий. Для симуляции лиги нам понадобятся команды, игроки, тренера (для разнообразия), игры и информация о результате игр. Ок, возьмем эти четыре параметра за отправную точку.

У нас уже есть таблица player, ан которой мы немного тренировались. Давайте грохнем ее и начнем проектирование с нуля. Выполняем следующую команду, чтобы удалить таблицу:

drop table player

Команда

Здесь у нас будет название команды и пока наверно все. Ок, то есть пока наша таблица будет состоять из двух колонок:

- ID команды – автоматически увеличиваемое число, которое будет первичным ключом и обеспечивать уникальность.

- Название – название команды, думаю 50 символов хватит

Ок, вроде бы все просто, давайте создадим такую таблицу:

CREATE TABLE team (
    teamid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    name varchar(50) NOT NULL 
)

Сразу же вставим в таблицу несколько команд. Я выбрал английскую лигу:

insert into team (name) values ('Manchester United');
insert into team (name) values ('Liverpool');
insert into team (name) values ('Arsenal');
insert into team (name) values ('Everton');
insert into team (name) values ('Chelsea');
insert into team (name) values ('Manchester City');

Проверяем, что у нас теперь в таблице:

select * from team;

Результат:

+--------+-------------------+
| teamid | name              |
+--------+-------------------+
|      1 | Manchester United |
|      2 | Liverpool         |
|      3 | Arsenal           |
|      4 | Everton           |
|      5 | Chelsea           |
|      6 | Manchester City   |
+--------+-------------------+

Отлично, у нас есть лига.

Игроки и тренера

И там и там будет имя и фамилия. Теоретически можно создать две отдельные таблицы:

В принципе это работает и ничего смертельного в таком дизайне не будет. Самый страшный вариант на мой взгляд, может быть только если у нас будет играющий тренер. Да, такое бывает не часто и даже если произойдет, мы просто дважды запишем имя и фамилию в две разных базы данных. Имя и фамилия меняются не часто, поэтому трудно представить себе ситуацию, когда придется поменять их и столкнуться с необходимостью поддерживать данные в двух различных таблицах.

Нужно же знать еще к какой команде принадлежит игрок или тренер, а значит нужно вставить в обе таблицы team id.

Но можно выпендрица и создать три таблицы:

Теперь все, что касается людей храниться в одной таблице. Плюс – у нас все люди в одном месте. Если мы захотим хранить день рождения, мы должны будем вставить его только в таблицу Person, а не в две разные таблицы.

Position ID это будет определять, кто этот человек – тренер или игрок. 1 будет тренер, 2 будет вратарь. Мы могли бы вместо числа хранить целое название Тренер, Вратарь, но тогда на подобные имена уйдет слишком много места. Чтобы сэкономить место вместо имен указываем числа, а чтобы не запоминать числа, мы можем создать таблицу, где четко скажем, кто есть кто:

CREATE TABLE position (
    position id INT NOT NULL PRIMARY KEY, 
    name varchar(50) NOT NULL 
)

Во время записи видеоверсии на Windows 10 + WSL я заметил, что сервер не позволяет создавать таблицу с именем position, потому что это имя уже занято встроенной функцией.

В macOS версия MySQL отличается, она на много старее у меня и здесь таблица с этим именем создалась без проблем. Не удивлюсь, если у вас достаточно свежая версия MySQL и вы тоже столкнетесь с проблемой конфликта имени. Если запрос на создание таблицы Position выше у вас завершился ошибкой, то просто поставьте вокруг имени одинарную косую кавычку `

CREATE TABLE `position` (
    position id INT NOT NULL PRIMARY KEY, 
    name varchar(50) NOT NULL 
)

Это же придется делать и во всех последующих запросах, если вы где-то обращаетесь к таблице Position, то обязательно ставьте `

И заполним ее значениями:

insert into position values (1, 'Coach');
insert into position values (2, 'Goalkeeper');
insert into position values (3, 'Defender');
insert into position values (4, 'Midfielder');
insert into position values (5, 'Forward');

Отлично, теперь мы точно знаем, что тренер – это 1.

Давайте создадим таблицу person:

CREATE TABLE person (
    personid INT AUTO_INCREMENT PRIMARY KEY, 
    teamid INT NULL,
    firstname varchar(50) NOT NULL,
    lastname varchar(50) NOT NULL,
    positionid int,
    CONSTRAINT fk_teamid FOREIGN KEY (teamid) REFERENCES team (teamid),
    CONSTRAINT fk_positionid FOREIGN KEY (positionid) REFERENCES position (positionid)
)

Обратите внимание, что в этой таблице аж два внешних ключа. teamid ссылается на team, а positioned на таблицу position. Такое без проблем возможно.

Отлично, давайте вставим несколько записей в эту таблицу. Я взял в википедии несколько человек из команды Manchester United:

-- тренеры
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Ole Gunnar', 'Solskjær', 1);
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Michael', 'Carrick', 1);
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Kieran', 'McKenna', 1);
-- вратарь
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'David', 'de Gea', 2);
-- защитники
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Victor', 'Lindelöf', 3);
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Eric', 'Bailly', 3);
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Phil', 'Jones', 3);
-- полузащитники
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Paul', 'Pogba', 4);
-- foreward
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Edinson', 'Cavani', 5);
insert into person (teamid, firstname, lastname, positionid) 
values (1, 'Anthony', 'Martial', 5);

Круто, я поставил комментарии напротив имен, чтобы мы понимали, но ведь если посмотреть теперь на таблицу, как мы можем сказать, кто из этих людей тренер, а кто игрок и какого плана:

+----------+--------+------------+-----------+------------+
| personid | teamid | firstname  | lastname  | positionid |
+----------+--------+------------+-----------+------------+
|        1 |      1 | Ole Gunnar | Solskjær  |          1 |
|        2 |      1 | Michael    | Carrick   |          1 |
|        3 |      1 | Kieran     | McKenna   |          1 |
|        4 |      1 | David      | de Gea    |          2 |
|        5 |      1 | Victor     | Lindelöf  |          3 |
|        6 |      1 | Eric       | Bailly    |          3 |
|        7 |      1 | Phil       | Jones     |          3 |
|        8 |      1 | Paul       | Pogba     |          4 |
|        9 |      1 | Edinson    | Cavani    |          5 |
|       10 |      1 | Anthony    | Martial   |          5 |
+----------+--------+------------+-----------+------------+

Ну и теперь запрос, который создает еще немного игроков для различных команд

insert into person (teamid, firstname, lastname, positionid) values (2, 'Jürgen', 'Klopp', 1);
insert into person (teamid, firstname, lastname, positionid) values (2, 'Alisson', 'Ramsess', 2);
insert into person (teamid, firstname, lastname, positionid) values (2, 'Virgil', 'van Dijk', 3);
insert into person (teamid, firstname, lastname, positionid) values (2, 'Georginio', 'Wijnaldum', 4);
insert into person (teamid, firstname, lastname, positionid) values (2, 'Thiago', 'Alcântara', 4);
insert into person (teamid, firstname, lastname, positionid) values (2, 'Roberto', 'Firmino', 5);

insert into person (teamid, firstname, lastname, positionid) values (3, 'Mikel', 'Arteta', 1);
insert into person (teamid, firstname, lastname, positionid) values (3, 'Bernd', 'Leno', 2);
insert into person (teamid, firstname, lastname, positionid) values (3, 'Kieran', 'Tierney', 2);
insert into person (teamid, firstname, lastname, positionid) values (3, 'Bukayo', 'Saka', 3);
insert into person (teamid, firstname, lastname, positionid) values (3, 'Rob', 'Holding', 2);
insert into person (teamid, firstname, lastname, positionid) values (3, 'Nicolas', 'Pépé', 5);

insert into person (teamid, firstname, lastname, positionid) values (4, 'Carlo', 'Ancelotti', 1);
insert into person (teamid, firstname, lastname, positionid) values (4, 'Jordan', 'Pickford', 2);
insert into person (teamid, firstname, lastname, positionid) values (4, 'Fabian', 'Delph', 4);

insert into person (teamid, firstname, lastname, positionid) values (5, 'Thomas', 'Tuchel', 1);
insert into person (teamid, firstname, lastname, positionid) values (5, 'Kepa', 'Arrizabalaga', 2);
insert into person (teamid, firstname, lastname, positionid) values (5, 'Antonio', 'Rüdiger', 3);
insert into person (teamid, firstname, lastname, positionid) values (5, 'Christian', 'Pulisic', 4);

insert into person (teamid, firstname, lastname, positionid) values (6, 'Pep', 'Guardiola', 1);
insert into person (teamid, firstname, lastname, positionid) values (6, 'Kyle', 'Walker', 3);
insert into person (teamid, firstname, lastname, positionid) values (6, 'Rúben', 'Dias', 3);
insert into person (teamid, firstname, lastname, positionid) values (6, 'Zack', 'Steffen', 2);
insert into person (teamid, firstname, lastname, positionid) values (6, 'Gabriel', 'Jesus', 5);
insert into person (teamid, firstname, lastname, positionid) values (6, 'Ferran', 'Torres', 5);
insert into person (teamid, firstname, lastname, positionid) values (6, 'Riyad', 'Mahrez', 5);

Игры

Ок, у нас есть команды, и мы можем теперь создавать. В течение сезона могут идти игры, в каждой из них учувствует по 2 команды. Нам понадобиться таблица со следующими данными:

- домашняя команда

- гостевая команда

- счет

- дата игры

CREATE TABLE game (
  gameid int AUTO_INCREMENT PRIMARY KEY,
  hometeamid int NOT NULL,
  guestteamid int NOT NULL,
  hometeamgoals int DEFAULT 0,
  guestteamgoals int DEFAULT 0,
  CONSTRAINT fk_hometeamid FOREIGN KEY (hometeamid) REFERENCES team (teamid),
  CONSTRAINT fk_guestteamid FOREIGN KEY (guestteamid) REFERENCES team (teamid)
)

Наполнение игр. Вот тут самое интересное, мне лень заполнять таблицы вручную, потому что чтобы 6 команды сыграли с каждым соперником хотя бы один раз, нужно вставить в таблицу 30 записей (команда не может играть сама с собой). Как это сделать?

Так как нас не очень волнуют конкретные данные, то мы можем сгенерировать что-то и в данном случае нужно начинать с SELECT запроса. Каждая команда должна сыграть с каждой командой, а значит мы должны написать запрос, в котором две таблицы Team связаны друг с другом:

select * 
from Team ht 
   join Team gt 

Чтобы было удобнее первой таблице Team я дал псевдоним ht (home team или команда, играющая дома) и gt (guest team, гостевая команда).

Мы не указываем, по каким колонкам происходит связь и в таком случае каждая строка из таблицы Team будет связана с каждой строкой таблицы Team. Так как я создал 6 команд, то в результате будет 36 строк:

+--------+-------------------+--------+-------------------+
| teamid | name              | teamid | name              |
+--------+-------------------+--------+-------------------+
|      1 | Manchester United |      1 | Manchester United |
|      2 | Liverpool         |      1 | Manchester United |
|      3 | Arsenal           |      1 | Manchester United |
|      4 | Everton           |      1 | Manchester United |
|      5 | Chelsea           |      1 | Manchester United |
|      6 | Manchester City   |      1 | Manchester United |
|      1 | Manchester United |      2 | Liverpool         |
|      2 | Liverpool         |      2 | Liverpool         |
|      3 | Arsenal           |      2 | Liverpool         |
|      4 | Everton           |      2 | Liverpool         |
|      5 | Chelsea           |      2 | Liverpool         |
|      6 | Manchester City   |      2 | Liverpool         |
|      1 | Manchester United |      3 | Arsenal           |
|      2 | Liverpool         |      3 | Arsenal           |
|      3 | Arsenal           |      3 | Arsenal           |
|      4 | Everton           |      3 | Arsenal           |
|      5 | Chelsea           |      3 | Arsenal           |
|      6 | Manchester City   |      3 | Arsenal           |
|      1 | Manchester United |      4 | Everton           |
|      2 | Liverpool         |      4 | Everton           |
|      3 | Arsenal           |      4 | Everton           |
|      4 | Everton           |      4 | Everton           |
|      5 | Chelsea           |      4 | Everton           |
|      6 | Manchester City   |      4 | Everton           |
|      1 | Manchester United |      5 | Chelsea           |
|      2 | Liverpool         |      5 | Chelsea           |
|      3 | Arsenal           |      5 | Chelsea           |
|      4 | Everton           |      5 | Chelsea           |
|      5 | Chelsea           |      5 | Chelsea           |
|      6 | Manchester City   |      5 | Chelsea           |
|      1 | Manchester United |      6 | Manchester City   |
|      2 | Liverpool         |      6 | Manchester City   |
|      3 | Arsenal           |      6 | Manchester City   |
|      4 | Everton           |      6 | Manchester City   |
|      5 | Chelsea           |      6 | Manchester City   |
|      6 | Manchester City   |      6 | Manchester City   |
+--------+-------------------+--------+-------------------+
36 rows in set (0.00 sec)

Можете проверить, есть абсолютно каждое сочетание пары команд.

База данных может не позволить связывать две таблицы без указания ON и связи. Ничего страшного, ведь база данных не может знать, какую связь мы предоставляем, тут главное указать хоть что-то, какую-то команду, которая ничего не делает. Классическая такая команда 1=1. Это истина, потому что 1 всегда равна единицы и это сравнение бесполезно, но оно достаточно для базы данных, чтобы заткнуться. Так что можно выполнить такой запрос и получить тот же самый результат:

select * 
from Team ht 
   join Team gt on 1=1

Но в этой результате все команды связаны со всеми и даже сами с собой, а мы договорились, что команды не могут играть сами с собой, разве что на тренировке. Так и скажем, что гостевой teamid не может быть равен домашней teamid (gt.teamid != ht.teamid). Это можно написать в секции WHERE:

select * 
from Team ht 
   join Team gt on 1=1
where gt.teamid != ht.teamid;

Или можно использовать ON как раз для этого:

select * 
from Team ht 
   join Team gt on gt.teamid != ht.teamid;

В этом примере мы связываем все записи, где гостевая команда не равна домашней, то есть все со всеми, кроме равенства, и это как раз то, что нам нужно.

Отлично, для заполнения таблицы игр у нас есть id двух команд и осталось только как-то получить счет.

Тут можно использовать функцию RAND, которая возвращает случайное число от 0 (включительно) до 1 (не включительно), то есть в математическом выражении результат будет в пределах >= 0 and

SELECT RAND();

Результат каждый раз будет разный.

Счет не может быть от 0 до 1, это как, вроде бы забил один гол, но не до конца? Гол или засчитан или нет и хотелось бы, чтобы счет хоть иногда был больше 1. Ну это решается просто, нужно просто это случайное значение умножить на максимум, который мы хотим. То есть если умножить на 3, то результат уже будет от 0 до трех.

SELECT RAND() * 3;

Как это происходит чисто на пальцах – допустим, что выпало случайное число 0, то в результате будет ноль. Если выпадет 0.99999, то при умножении на 3 в результате будет почти 3. Если выпадет 0.3333, то при умножении на 3 будет почти единица. Таким образом мы получаем значения от 0 до 3. Остается только округлить их до ближайшего целого, а для этого есть функция ROUND:

SELECT round(RAND() * 3);

Попробуйте выполнить этот запрос несколько раз, теперь вы должны видеть числа от 0 до 3. Отлично, это уже практически то, что нам нужно.

select ht.teamid, 
       round(RAND() * 3) , 
       round(RAND() * 3), 
       gt.teamid 
from Team ht 
   join Team gt on gt.teamid != ht.teamid;

Этот запрос вернет – ID из таблицы team, которую мы назвали home team. Потом два случайных числа и снова id из таблицы команд, которую мы воспринимаем, как гостевую. Результат будет примерно таким:

+--------+-------------------+-------------------+--------+
| teamid | round(RAND() * 3) | round(RAND() * 3) | teamid |
+--------+-------------------+-------------------+--------+
|      2 |                 1 |                 1 |      1 |
|      3 |                 1 |                 0 |      1 |
|      4 |                 3 |                 2 |      1 |
|      5 |                 0 |                 1 |      1 |
|      6 |                 2 |                 3 |      1 |

Все строки приводить не буду, а только первые пять.

Теперь нам нужно все это поместить в таблицу games. Легко, это делается практически также, как и при простом insert запросе, когда мы вставляли только одну строку. Тогда мы писали INSERT INTO, потом список колонок, в которые мы вставляли и после этого VALUES b значения для одной строки. В данном случае мы вставляем не одну строку, а целую выборку из таблицы, а для этого нужно нам нужно только INSERT INTO и список колонок, который будет соответствовать тому же порядку, как и в SELECT. При выборе SELECT я поставил колонки как домашняя команда, голы домашней команды, голы гостевой команды, гостевая команда. Значит и INSERT нужно написать в том же порядке:

insert into game (hometeamid, hometeamgoals, guestteamgoals, guestteamid) 

Ок, теперь у нас есть команда SELECT и INSERT, так что объединяем все вместе, чтобы проще было видеть соответствие колонок, я имена записал каждый в отдельной строке:

insert into game (
       hometeamid, 
       hometeamgoals, 
       guestteamgoals,
guestteamid) 
select ht.teamid, 
       round(RAND() * 3) , 
       round(RAND() * 3), 
       gt.teamid 
from Team ht 
   join Team gt on gt.teamid != ht.teamid;

Отлично, у нас каждая команда сыграла со своим соперником по одному разу и есть счет. Погнали тренироваться на этой базе данных.

SQL.скрипт создания базы данных можно скачать https://www.flenov.info/files/League.sql.zip