Untitled
unknown
plain_text
3 years ago
1.8 kB
8
Indexable
Never
SELECT now(); SELECT concat('INSERT INTO regions (name) VALUES (''',region,''');') FROM world.country GROUP BY region; SELECT region FROM world.country GROUP BY region; SELECT regiao FROM ( SELECT DISTINCT region AS regiao FROM world.country ) tb1; SELECT concat('asfasdf','dfdfdf'); -- Criar tabela regions CREATE TABLE regions ( id_region TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name varchar(50) NOT NULL, population bigint NOT NULL DEFAULT 0 ); SELECT * FROM world.regions; DELETE FROM world.regions; -- Popular a tabela regions INSERT INTO regions (name) SELECT DISTINCT region FROM world.country; -- Criar o campo de regiao em county ALTER TABLE world.country ADD id_region TINYINT UNSIGNED AFTER region; SELECT * FROM world.country; -- Criar o relacionamento ALTER TABLE world.country ADD FOREIGN KEY (id_region) REFERENCES world.regions (id_region) ON UPDATE CASCADE ON DELETE RESTRICT; -- Popular o id_region de country SELECT tb1.name AS 'País', tb1.Region AS 'Região', tb2.name AS 'Regiao de regions', tb1.id_region, tb2.id_region FROM world.country tb1 JOIN world.regions tb2 ON tb1.region = tb2.name; UPDATE world.country tb1 JOIN world.regions tb2 ON tb1.region = tb2.name SET tb1.id_region = tb2.id_region; SELECT * FROM world.country; SELECT * FROM regions; -- Remover o campo region de country ALTER TABLE world.country DROP COLUMN region; SELECT id_region, sum(Population) AS pop FROM world.country GROUP BY id_region; UPDATE world.regions tb1 JOIN ( SELECT id_region AS id, sum(Population) AS pop FROM world.country GROUP BY id_region ) tb2 ON tb1.id_region = tb2.id SET tb1.population = tb2.pop; ALTER TABLE regions DROP population;