CREATE DATABASE IF NOT EXISTS Country
USE Country
CREATE TABLE IF NOT EXISTS district
( districtId INT(10) PRIMARY KEY COMMENT ‘区域编号’ AUTO_INCREMENT,
districtName VARCHAR(30) NOT NULL COMMENT ‘区域名称’
)
CREATE TABLE IF NOT EXISTS air_quality_index
(id INT(10) PRIMARY KEY COMMENT ‘记录编号’ AUTO_INCREMENT,
districtId INT(10) COMMENT ‘区域编号’ ,
monitorTime VARCHAR(22) NOT NULL COMMENT ‘检测时间’,
pm10 INT(10) NOT NULL COMMENT ‘pm10值’,
pm2.5
INT(10)NOT NULL COMMENT’pm2.5值’,
monitoringStation VARCHAR(200) NOT NULL COMMENT ‘监测站’,
lastModifyTime VARCHAR(22) NOT NULL COMMENT’最后检测时间’
)
ALTER TABLE air_quality_index
ADD CONSTRAINT Fk_districtId FOREIGN KEY(districtId) REFERENCES district(districtId) ON DELETE CASCADE
INSERT INTO district(districtName) VALUES
(‘西城区’),
(‘东城区’),
(‘海淀区’),
(‘丰台区’),
(‘朝阳区’)
INSERT INTO air_quality_index VALUES
(1,1,‘2013-02-02’,24,17,‘灵境胡同检测站’,‘2013-02-12’)