neTPDZz1

1SET NAMES 'utf8';
2CREATE DATABASE IF NOT EXISTS `country_city`;
3USE `country_city`;
4DROP TABLE IF EXISTS `country`;
5CREATE TABLE `country` (
6    `id` INT NOT NULL AUTO_INCREMENT,
7    `name` varchar(255) NOT NULL,
8    `city_id` INT NOT NULL,
9    PRIMARY KEY (id)
10) ENGINE=INNOERDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
11
12DROP TABLE IF EXISTS `city`;
13CREATE TABLE `city` (
14    `id` INT NOT NULL AUTO_INCREMENT,
15    `name` varchar(255) NOT NULL,
16    `country_id` INT NOT NULL,
17    PRIMARY KEY (`id`)
18) ENGINE=INNOERDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
19
20CREATE INDEX city_name ON city(name) USING BTREE;
21CREATE INDEX country_name ON country(name) USING BTREE;
22
23INSERT INTO `city` VALUES
24    (NULL, 'Beijing', 1),
25    (NULL, 'ShangHai',1),
26    (NULL, 'ShenYang', 1),
27    (NULL, 'JiLin', 1),
28    (NULL, 'DaLian', 1),
29    (NULL, 'Haerbin', 1),
30    (NULL, 'SuiHua', 1),
31    (NULL, 'HangHou', 1),
32    (NULL, 'SiChuan', 1),
33    (NULL, 'LanZhou', 1);
34
35INSERT INTO `country` VALUES
36    (NULL, 'China', 001),
37    (NULL, 'Amercia', 002),
38    (NULL, 'India', 003);
39
40SELECT ci.name FROM city AS ci, country AS co
41    WHERE ci.country_id = co.city_id;
42EXPLAIN SELECT * from city WHERE name = 'Beijing';
43SELECT current_timestamp();
44SHOW warnings;
45#DESC city;,
46#DESC country;