物种2000数据库结构SQL脚本

   1:  # SQL Manager 2007 for MySQL 4.3.2.3

   2:  # ---------------------------------------

   3:  # Host     : localhost

   4:  # Port     : 3306

   5:  # Database : sp2000cn

6:

7:

   8:  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

   9:  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

  10:  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

  11:  /*!40101 SET NAMES utf8 */;

12:

  13:  SET FOREIGN_KEY_CHECKS=0;

14:

  15:  DROP DATABASE IF EXISTS `sp2000cn`;

16:

  17:  CREATE DATABASE `sp2000cn`

  18:      CHARACTER SET 'utf8'

  19:      COLLATE 'utf8_unicode_ci';

20:

  21:  USE `sp2000cn`;

22:

  23:  #

  24:  # Structure for the `common_names` table :

  25:  #

26:

  27:  CREATE TABLE `common_names` (

  28:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  29:    `name_code` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

  30:    `common_name` VARCHAR(500) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

  31:    `language` VARCHAR(21) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

  32:    `country` VARCHAR(44) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

  33:    `reference_id` INTEGER(10) DEFAULT '0',

  34:    `database_id` INTEGER(10) NOT NULL DEFAULT '0',

  35:    `is_infraspecies` INTEGER(1) NOT NULL DEFAULT '0',

  36:    PRIMARY KEY (`record_id`),

  37:    KEY `common_name` (`common_name`),

  38:    KEY `language` (`language`, `common_name`, `name_code`),

  39:    KEY `common_name_2` (`common_name`, `name_code`, `database_id`),

  40:    KEY `name_code` (`name_code`),

  41:    KEY `common_name_3` (`common_name`, `database_id`)

42:

  43:  )ENGINE=MyISAM

  44:  AUTO_INCREMENT=1661048 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

45:

  46:  #

  47:  # Structure for the `databases` table :

  48:  #

49:

  50:  CREATE TABLE `databases` (

  51:    `database_name_displayed` VARCHAR(100) COLLATE latin1_swedish_ci DEFAULT NULL,

  52:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  53:    `database_name` VARCHAR(150) COLLATE latin1_swedish_ci DEFAULT NULL,

  54:    `database_full_name` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

  55:    `web_site` LONGTEXT,

  56:    `organization` VARCHAR(255) COLLATE latin1_swedish_ci DEFAULT NULL,

  57:    `contact_person` VARCHAR(255) COLLATE latin1_swedish_ci DEFAULT NULL,

  58:    `taxa` VARCHAR(255) COLLATE latin1_swedish_ci DEFAULT NULL,

  59:    `taxonomic_coverage` LONGTEXT,

  60:    `abstract` LONGTEXT,

  61:    `version` VARCHAR(255) COLLATE latin1_swedish_ci DEFAULT NULL,

  62:    `release_date` DATE DEFAULT '0000-00-00',

  63:    `SpeciesCount` INTEGER(11) DEFAULT '0',

  64:    `SpeciesEst` INTEGER(11) DEFAULT '0',

  65:    `authors_editors` LONGTEXT,

  66:    `accepted_species_names` INTEGER(10) DEFAULT '0',

  67:    `accepted_infraspecies_names` INTEGER(10) DEFAULT '0',

  68:    `species_synonyms` INTEGER(10) DEFAULT '0',

  69:    `infraspecies_synonyms` INTEGER(10) DEFAULT '0',

  70:    `common_names` INTEGER(10) DEFAULT '0',

  71:    `total_names` INTEGER(10) DEFAULT '0',

  72:    PRIMARY KEY (`record_id`),

  73:    KEY `database_name` (`database_name`)

74:

  75:  )ENGINE=MyISAM

  76:  AUTO_INCREMENT=55 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

77:

  78:  #

  79:  # Structure for the `distribution` table :

  80:  #

81:

  82:  CREATE TABLE `distribution` (

  83:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  84:    `name_code` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

  85:    `distribution` LONGTEXT,

  86:    PRIMARY KEY (`record_id`),

  87:    KEY `name_code` (`name_code`)

88:

  89:  )ENGINE=MyISAM

  90:  AUTO_INCREMENT=1695430 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

91:

  92:  #

  93:  # Structure for the `families` table :

  94:  #

95:

  96:  CREATE TABLE `families` (

  97:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  98:    `hierarchy_code` VARCHAR(250) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

  99:    `kingdom` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 100:    `phylum` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 101:    `class` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 102:    `order` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 103:    `family` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 104:    `superfamily` VARCHAR(50) COLLATE latin1_swedish_ci DEFAULT NULL,

 105:    `family_common_name` VARCHAR(50) COLLATE latin1_swedish_ci DEFAULT NULL,

 106:    `database_name` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 107:    `is_accepted_name` INTEGER(1) DEFAULT '0',

 108:    `database_id` INTEGER(10) DEFAULT NULL,

 109:    PRIMARY KEY (`record_id`),

 110:    KEY `kingdom` (`kingdom`),

 111:    KEY `phylum` (`phylum`),

 112:    KEY `class` (`class`),

 113:    KEY `order` (`order`),

 114:    KEY `family` (`family`),

 115:    KEY `superfamily` (`superfamily`),

 116:    KEY `is_accepted_name` (`is_accepted_name`)

 117:

 118:  )ENGINE=MyISAM

 119:  AUTO_INCREMENT=17919 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 120:

 121:  #

 122:  # Structure for the `hard_coded_species_totals` table :

 123:  #

 124:

 125:  CREATE TABLE `hard_coded_species_totals` (

 126:    `taxon` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 127:    `species_count` INTEGER(20) NOT NULL DEFAULT '0',

 128:    KEY `taxon` (`taxon`)

 129:

 130:  )ENGINE=MyISAM

 131:  CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 132:

 133:  #

 134:  # Structure for the `hard_coded_taxon_lists` table :

 135:  #

 136:

 137:  CREATE TABLE `hard_coded_taxon_lists` (

 138:    `rank` VARCHAR(12) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 139:    `accepted_names_only` INTEGER(1) NOT NULL DEFAULT '0',

 140:    `name` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 141:    KEY `accepted_names_only` (`accepted_names_only`),

 142:    KEY `rank` (`rank`),

 143:    KEY `name` (`name`)

 144:

 145:  )ENGINE=MyISAM

 146:  CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 147:

 148:  #

 149:  # Structure for the `references` table :

 150:  #

 151:

 152:  CREATE TABLE `references` (

 153:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

 154:    `author` VARCHAR(200) COLLATE latin1_swedish_ci DEFAULT NULL,

 155:    `year` VARCHAR(50) COLLATE latin1_swedish_ci DEFAULT NULL,

 156:    `title` LONGTEXT,

 157:    `source` LONGTEXT,

 158:    `database_id` INTEGER(10) DEFAULT NULL,

 159:    PRIMARY KEY (`record_id`)

 160:

 161:  )ENGINE=MyISAM

 162:  AUTO_INCREMENT=1898869 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 163:

 164:  #

 165:  # Structure for the `scientific_name_references` table :

 166:  #

 167:

 168:  CREATE TABLE `scientific_name_references` (

 169:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

 170:    `name_code` VARCHAR(42) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 171:    `reference_type` VARCHAR(10) COLLATE latin1_swedish_ci DEFAULT NULL,

 172:    `reference_id` INTEGER(10) DEFAULT '0',

 173:    PRIMARY KEY (`record_id`),

 174:    KEY `name_code` (`name_code`, `reference_id`, `reference_type`),

 175:    KEY `name_code_2` (`name_code`, `reference_type`)

 176:

 177:  )ENGINE=MyISAM

 178:  AUTO_INCREMENT=7117561 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 179:

 180:  #

 181:  # Structure for the `scientific_names` table :

 182:  #

 183:

 184:  CREATE TABLE `scientific_names` (

 185:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

 186:    `name_code` VARCHAR(42) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 187:    `web_site` LONGTEXT,

 188:    `genus` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 189:    `species` VARCHAR(50) COLLATE latin1_swedish_ci DEFAULT NULL,

 190:    `infraspecies` VARCHAR(50) COLLATE latin1_swedish_ci DEFAULT NULL,

 191:    `infraspecies_marker` VARCHAR(50) COLLATE latin1_swedish_ci DEFAULT NULL,

 192:    `author` VARCHAR(100) COLLATE latin1_swedish_ci DEFAULT NULL,

 193:    `accepted_name_code` VARCHAR(36) COLLATE latin1_swedish_ci DEFAULT NULL,

 194:    `comment` LONGTEXT,

 195:    `scrutiny_date` TEXT COLLATE latin1_swedish_ci,

 196:    `sp2000_status_id` INTEGER(1) DEFAULT '0',

 197:    `database_id` INTEGER(10) NOT NULL DEFAULT '0',

 198:    `specialist_id` INTEGER(10) DEFAULT '0',

 199:    `family_id` INTEGER(10) DEFAULT '0',

 200:    `is_accepted_name` INTEGER(1) NOT NULL DEFAULT '0',

 201:    PRIMARY KEY (`record_id`),

 202:    KEY `family_id` (`family_id`),

 203:    KEY `species` (`species`),

 204:    KEY `infraspecies` (`infraspecies`),

 205:    KEY `accepted_name_code` (`accepted_name_code`),

 206:    KEY `name_code` (`name_code`, `family_id`),

 207:    KEY `record_id` (`record_id`, `family_id`),

 208:    KEY `genus` (`genus`, `species`, `infraspecies`),

 209:    KEY `sp2000_status_id` (`sp2000_status_id`),

 210:    KEY `sp2000_status_id_2` (`sp2000_status_id`, `database_id`, `infraspecies`)

 211:

 212:  )ENGINE=MyISAM

 213:  AUTO_INCREMENT=5628035 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 214:

 215:  #

 216:  # Structure for the `simple_search` table :

 217:  #

 218:

 219:  CREATE TABLE `simple_search` (

 220:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

 221:    `taxa_id` INTEGER(10) NOT NULL DEFAULT '0',

 222:    `words` VARCHAR(250) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 223:    PRIMARY KEY (`record_id`),

 224:    KEY `words` (`words`)

 225:

 226:  )ENGINE=MyISAM

 227:  AUTO_INCREMENT=6978450 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 228:

 229:  #

 230:  # Structure for the `sp2000_statuses` table :

 231:  #

 232:

 233:  CREATE TABLE `sp2000_statuses` (

 234:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

 235:    `sp2000_status` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 236:    PRIMARY KEY (`record_id`),

 237:    KEY `sp2000_status` (`sp2000_status`)

 238:

 239:  )ENGINE=MyISAM

 240:  AUTO_INCREMENT=9 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 241:

 242:  #

 243:  # Structure for the `specialists` table :

 244:  #

 245:

 246:  CREATE TABLE `specialists` (

 247:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

 248:    `specialist_name` VARCHAR(255) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 249:    `database_id` DECIMAL(10,0) DEFAULT NULL,

 250:    PRIMARY KEY (`record_id`)

 251:

 252:  )ENGINE=MyISAM

 253:  AUTO_INCREMENT=925 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 254:

 255:  #

 256:  # Structure for the `taxa` table :

 257:  #

 258:

 259:  CREATE TABLE `taxa` (

 260:    `record_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,

 261:    `lsid` VARCHAR(78) COLLATE latin1_swedish_ci DEFAULT NULL,

 262:    `name` VARCHAR(137) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 263:    `name_with_italics` VARCHAR(151) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 264:    `taxon` VARCHAR(12) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',

 265:    `name_code` VARCHAR(42) COLLATE latin1_swedish_ci DEFAULT NULL,

 266:    `parent_id` INTEGER(10) NOT NULL DEFAULT '0',

 267:    `sp2000_status_id` INTEGER(1) NOT NULL DEFAULT '0',

 268:    `database_id` INTEGER(2) NOT NULL DEFAULT '0',

 269:    `is_accepted_name` INTEGER(1) NOT NULL DEFAULT '0',

 270:    `is_species_or_nonsynonymic_higher_taxon` INTEGER(1) NOT NULL DEFAULT '0',

 271:    PRIMARY KEY (`record_id`),

 272:    KEY `name` (`name`, `is_species_or_nonsynonymic_higher_taxon`, `database_id`, `sp2000_status_id`),

 273:    KEY `sp2000_status_id` (`sp2000_status_id`),

 274:    KEY `parent_id` (`parent_id`),

 275:    KEY `database_id` (`database_id`),

 276:    KEY `taxon` (`taxon`),

 277:    KEY `is_accepted_name` (`is_accepted_name`),

 278:    KEY `name_code` (`name_code`),

 279:    KEY `is_species_or_nonsynonymic_higher_taxon` (`is_species_or_nonsynonymic_higher_taxon`)

 280:

 281:  )ENGINE=MyISAM

 282:  AUTO_INCREMENT=2080793 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 283:

 284:

 285:

 286:  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

 287:  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

 288:  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

posted on 2009-04-26 13:24 虎克阅读(242) 评论(0) 收藏举报

刷新页面返回顶部

缤纷多彩的植物信息世界

公告