29 lines
1.2 KiB
MySQL
29 lines
1.2 KiB
MySQL
|
CREATE SCHEMA cities;
|
||
|
|
|
||
|
|
CREATE TABLE cities.Cities (
|
||
|
|
id BINARY(16) NOT NULL, /* UUID */
|
||
|
|
name NVARCHAR(32) NOT NULL,
|
||
|
|
countryName CHAR(2) CHARACTER SET ASCII NOT NULL, /* country code such DE, US, CH, ... */
|
||
|
|
countryCode NUMERIC(3) NOT NULL, /* numeric country code */
|
||
|
|
url VARCHAR(100),
|
||
|
|
history MEDIUMTEXT,
|
||
|
|
icon BLOB
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO cities.Cities SET
|
||
|
|
id = UUID_TO_BIN(UUID()),
|
||
|
|
name = 'Berlin',
|
||
|
|
countryName = 'DE',
|
||
|
|
countryCode = 268,
|
||
|
|
url = 'https://www.berlin.de/en/',
|
||
|
|
history = 'German Empire, 3rd Reich, split into two, reunited :)';
|
||
|
|
|
||
|
|
INSERT INTO cities.Cities SET
|
||
|
|
id = UUID_TO_BIN(UUID()),
|
||
|
|
name = 'Berlin',
|
||
|
|
countryName = 'US',
|
||
|
|
countryCode = 840,
|
||
|
|
url = 'https://www.berlinnh.gov/',
|
||
|
|
history = 'some weird country. They like guns';
|
||
|
|
|
||
|
|
SELECT name, countryName, countryCode, url, history FROM cities.Cities ORDER BY countryName;
|