Привет, отважный герой, мы ждали тебя! Мир на грани разрушения, злобный Доктор Релятивистус собирается устроить хаос в нашем упорядоченном мире данных. Собери команду и останови конец света. Вся надежда только на тебя!
Секретная база
Ты получишь все, что потребуется, чтобы подготовиться к битве с Релятивистусом. На этой секретной базе ты сможешь собрать и подготовить лучших героев современности.
В левом поле можно создать все нужные таблицы, определить их структуру, указать нужные параметры и добавить данные.
В правом поле можно выполнять запросы на выборку данных из созданных таблиц.
Набираем команду
Анкета
Нам нужны только сильнейшие герои современности – Супермен, Бэтмен, Железный Человек и, конечно, Черепашки Ниндзя.
Давай подготовим шаблон анкеты для всех желающих вступить в твою суперкоманду – создадим первую таблицу. Введи этот код в левое поле:
CREATE TABLE superheroes (
id INT AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 0,
gender VARCHAR(1),
height INT DEFAULT 0
);Оператор CREATE создает в базе данных новую таблицу с названием superheroes. Каждая строка внутри скобок – это отдельная колонка, или поле таблицы. Она определяет, какие именно данные нужно хранить.
Сначала указываем имя поля, потом тип данных, а дальше перечисляем необходимые атрибуты, которых может и не быть.
id– число (INT), которое будет автоматически увеличиваться для каждой следующей записи (AUTO_INCREMENT). Это поле служит первичным ключом (PRIMARY KEY) таблицы, то есть однозначно идентифицирует строку, в которой находится.name– строка (VARCHAR) длиной не более 20 символов с именем супергероя, которая не может быть пустой (NOT NULL).age– возраст. Если его не указать, будет автоматически подставлено значение по умолчанию (DEFAULT), то есть0.gender– пол (mилиf).height– рост.
Атрибуты, которые каким-либо образом ограничивают возможные значения поля (длина, уникальность) называются ограничениями.
Нажми на кнопку Build Schema, чтобы зафиксировать структуру базы.
Если эта анкета кажется тебе недостаточно полной, ее всегда можно изменить. Например, было бы неплохо отдельно хранить геройское имя и реальное. Переименуем поле name на hero_name (заодно сделаем его уникальным – UNIQUE) и добавим новую колонку (код опять добавляется в левое поле):
ALTER TABLE superheroes CHANGE name hero_name VARCHAR(20) NOT NULL UNIQUE;
ALTER TABLE superheroes ADD real_name VARCHAR(20);А возраст, пожалуй, нам не понадобится, удалим его:
ALTER TABLE superheroes DROP COLUMN age;Первые герои
Стоило только объявить о наборе в суперкоманду, как подтянулись первые желающие – компания Мстителей. Нужно занести в базу их данные (опять в левое поле):
INSERT INTO superheroes (hero_name, real_name, gender, height)
VALUES ('Spider Man', 'Peter Parker', 'm', 175),
('Captain America', 'Steven Rogers', 'm', 188),
('Black Widow', 'Natalia Romanoff', 'f', 170),
('Iron Man', 'Tony Stark', 'm', 198),
('Halk', 'Bruce Banner', 'm', 229);Оператор INSERT добавляет данные в таблицу superheroes. Перечисляем поля, которые хотим заполнить, и в нужном порядке добавляем значения. Содержимое каждых скобок после VALUES станет отдельной строкой, или записью таблицы.
Обрати внимание, тут нет поля id – оно заполняется автоматически увеличивающимся числом. Как и поле age, в него устанавливается дефолтное значение 0.
Вселенные и команды
Суперкоманда для битвы с Релятивистусом получится разношерстной. Многие из героев даже не будут знакомы между собой, а в битве требуется сплоченность и согласованность действий. Поэтому имеет смысл сохранить данные о том, в какие отряды изначально входили герои.
Создай еще две таблицы в левом поле:
CREATE TABLE universes (
id INT PRIMARY KEY,
universe VARCHAR(20)
);
INSERT INTO universes (id, universe) VALUES
(1, 'DC'),
(2, 'Marvel'),
(3, 'TMNT'),
(4, 'Dark Horse Comics');
CREATE TABLE teams (
id INT PRIMARY KEY,
team VARCHAR(100)
);
INSERT INTO teams (id, team) VALUES
(1, 'Avengers'),
(2, 'X-men'),
(3, 'Justice League'),
(4, 'Ninja Turtles'),
(5, 'Бюро паранормальных исследований и обороны');Теперь добавь в супергеройскую анкету поля universe и team:
ALTER TABLE superheroes ADD universe INT;
ALTER TABLE superheroes ADD team INT;Почему у этих полей числовые значения? Мы поместим сюда ссылки на записи в таблицах universes и teams. Это позволит не привязываться к названиям, а также обеспечит целостность данных. Указать можно будет только одну из зафиксированных в базе вселенных.
Обеспечить связь между таблицами поможет внешний ключ (FOREIGN KEY).
ALTER TABLE superheroes ADD FOREIGN KEY hero_universe(universe) REFERENCES universes(id);
ALTER TABLE superheroes ADD FOREIGN KEY hero_team(team) REFERENCES teams(id);Указываем название ключа (hero_universe, hero_team), поле, к которому он относится, а также ссылку на поле связанной таблицы.
Сейчас в твоей команде только представители вселенной Marvel из отряда Мстителей, поэтому можно сразу же заполнить нужные поля для всех записей:
UPDATE superheroes SET universe = 2, team = 1;Слишком много Флешей
Вот еще одна порция заявок на вступление в суперкоманду для битвы с Релятивистусом. На этот раз от Лиги Справедливости. Это крутые ребята, можешь добавить их в левое поле:
INSERT INTO superheroes (hero_name, real_name, gender, height, universe, team)
VALUES ('Batman ', 'Bruce Wayne', 'm', 188, 1, 3),
('Superman ', 'Clark Kent', 'm', 190, 1, 3),
('Wonder Woman', 'Diana Prince', 'f', 183, 1, 3),
('Flash', 'Bartholomew Allen', 'm', 180, 1, 3),
('Flash', 'Wally West', 'm', 183, 1, 3),
('Flash', 'Jay Garrick', 'm', 180, 1, 3);Ух ты, сразу все Флеши пришли. Хьюстон, у нас проблемы!
База не позволяет внести записи с дублирующимся полем hero_name, ведь мы установили для него атрибут UNIQUE. Но это совершенно очевидно три разных человека, хоть и под маской одного и того же героя. Изменим ограничение уникальности:
ALTER TABLE superheroes DROP INDEX hero_name;
ALTER TABLE superheroes ADD CONSTRAINT name UNIQUE (hero_name, real_name);Теперь можно внести данные новых героев.
Супергерои в сборе
Вот еще желающие:
INSERT INTO superheroes (hero_name, real_name, gender, height, universe, team)
VALUES ('Wolverine', 'James Howlett', 'm', 160, 2, 2),
('Storm', 'Ororo Munroe', 'f', 175, 2, 2),
('Hellboy', 'Anung Un Rama', 'm', 195, 4, 5),
('Donatello', 'Donatello', 'm', 157, 3, 4),
('Raphael', 'Raphael', 'm', 158, 3, 4),
('Michelangelo' , 'Michelangelo', 'm', 157, 3, 4),
('Leonardo', 'Leonardo', 'm', 157, 3, 4);Добавь их в базу и начинай готовиться к решающей битве с Релятивистусом.
Подготовка команды
Найти участников тебе поможет оператор SELECT и множество способов фильтрации данных. Вводи запросы в правом поле.
Перекличка!
SELECT * FROM superheroes;
По росту становись!
SELECT hero_name, real_name, height FROM superheroes ORDER BY height DESC;
Заодно узнаем средний, минимальный и максимальный рост героев:
SELECT AVG(height), MIN(height), MAX(height) FROM superheroes;
Ради развлечения можно вычислить высоту пирамиды из супергероев:
SELECT SUM(height) FROM superheroes;
На боевые группы разбейся!
SELECT team, COUNT(*) FROM superheroes GROUP BY team;
Группы получились неравномерные, лучше их объединить в отряды.
Первый отряд – Мстители:
SELECT hero_name, real_name FROM superheroes WHERE team =
(SELECT id FROM teams WHERE team = "Avengers")
Второй отряд – Черепашки и Люди-Икс:
SELECT hero_name, real_name FROM superheroes WHERE team IN
(SELECT id FROM teams WHERE team = "X-men" OR team = "Ninja Turtles" );
Третий отряд Лига Справедливости и Хэллбой:
SELECT hero_name, real_name FROM superheroes
WHERE team NOT IN (SELECT id FROM teams
WHERE team IN ("Avengers", "X-men", "Ninja Turtles"));
Выработка стратегии
В разведку пойдут самые маленькие и шустрые:
SELECT hero_name FROM superheroes WHERE height BETWEEN 0 AND 160;
Решающий удар нанесут два самых мощных героя:
SELECT hero_name FROM superheroes ORDER BY height DESC LIMIT 2;
А Флеши будут изматывать и запутывать противника.
Перед боем
Чтобы сбросить напряжение перед решающей битвой с Релятивистусом супергеройская команда устроила вечеринку и решила выбрать короля вечера. По традиции в выборах участвовали только герои, в именах которых есть слова MAN или BOY.
Отбирая кандидатов, не забудьте исключить женщин – они будут участвовать в выборе королевы.
SELECT hero_name FROM superheroes
WHERE hero_name LIKE "%man%"
AND hero_name NOT LIKE "%woman%"
OR hero_name LIKE "%boy%";
Решающая битва
Объединение
Твоя команда выстраивается в боевом порядке:
SELECT superheroes.hero_name,
superheroes.real_name,
universes.universe,
teams.team
from superheroes INNER JOIN universes INNER JOIN teams
ON superheroes.universe = universes.id AND
superheroes.team = teams.id;
Этот полный список получен путем радикального тройного внутреннего объединения таблиц базы. Сначала мы перечислили все интересующие нас поля, а затем установили условие соответствия.
Герои из разных отрядов и разных вселенных объединились и плечом к плечу встали против злобного доктора Релятивистуса.
Победа
Разумеется, ты победил! Разве могло быть иначе с такой потрясающей командой супергероев?
Порядок в мире данных восстановлен, а доктор Релятивистус убежал.
Пришла пора наградить всех героев, участвовавших в битве:
SELECT DISTINCT hero_name FROM superheroes;Благодаря оператору DISTINCT в итоговой таблице мы получим только одного Флеша, а не трех.
Сцена после титров
В любом супергеройском приключении обязательно должна быть сцена после титров, может быть даже и не одна.
Супергеройская команда распалась, она выполнила свою функцию. Все расходятся по своим вселенным. Пора удалять таблицу из базы:
DROP TABLE superheroes;Подождите секунду! Кто это там выглядывает из-за угла, коварно хихикая?
Да это же злобный доктор Релятивистус!
Нет, мы не будем совсем удалять таблицу. Просто очистим ее, чтобы в нужный момент снова собрать команду героев.
TRUNCATE TABLE superheroes;Полный код базы здесь.
0 комментариев