上次給大家介紹了ITIS數(shù)據(jù)庫的常用表，由于數(shù)據(jù)庫的存儲(chǔ)方式和日常習(xí)慣不太一樣，因此計(jì)劃重新組織一下。

-- 等級(jí)表

select replace(hierarchy_string,'-',','),a.* from hierarchy a where tsn=769899;? ?

分類關(guān)系是以下面的形式組織的

202423-914154-914155-914158-82696-563886-99208-100500-563890-914213-152741-152864-709253-154344-154310-768180-768205-768289-769899

-- 1、將物種分類拆開，有兩個(gè)字段：物種ID，物種所在的各級(jí)分類的ID

create table bio_hierarchy as

? SELECT a.tsn,b.help_topic_id,substring_index( substring_index( replace(hierarchy_string,'-',','), ',', b.help_topic_id + 1 ), ',',- 1 ) rel_tsn

? FROM hierarchy a

? ? JOIN mysql.help_topic b ON b.help_topic_id < ( length( replace(hierarchy_string,'-',',') ) - length( REPLACE ( replace(hierarchy_string,'-',','), ',', '' ) ) + 1 )?

? ?--where a.tsn=678130

? order by a.tsn,b.help_topic_id

??

create index i_bio_hierarchy on bio_hierarchy(tsn);

select * from bio_hierarchy;

-- 2、將物種的各級(jí)分類的名字以及級(jí)別查出來

create table bio_fenlei as

select a.*,b.completename ,e.shortauthor,c.rank_id,d.rank_name?

? from bio_hierarchy a?

? left join longnames b?

? ? on a.rel_tsn=b.tsn?

? left join taxonomic_units c? ? ? ? ? ? ? ?-- 分類單元

? on b.tsn =c.tsn?

? left join taxon_unit_types d? ? ? ? ? ? ? -- 分類類別

? ? on c.kingdom_id =d.kingdom_id?

? ?and c.rank_id =d.rank_id?

? left join strippedauthor e? ? ? ? ? ? ? ? -- 命名人

? ? on c.taxon_author_id =e.taxon_author_id

? where a.tsn=678130

?;

create index i_bio_fenlei_1 on bio_fenlei(tsn);

-- 3、將窄表（一個(gè)物種對(duì)應(yīng)多條記錄）變?yōu)閷挶恚ㄒ粋€(gè)物種對(duì)應(yīng)一條記錄）

?-- 亞種

insert into bio_names_zzt (name_code? ,? Subspecies,author? ? ? ? )

select tsn,a.completename,shortauthor from bio_fenlei a where rank_name ='Subspecies';

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Species' set a.Species=b.completename;

?-- 種

insert into bio_names_zzt (name_code? ,? Species,author? ? ? ? )

select tsn,a.completename,shortauthor from bio_fenlei a where rank_name ='Species'?

and not exists (select 1 from bio_fenlei where tsn=a.tsn and rank_name ='Subspecies' );

create index i_bio_names_zzt_1 on bio_names_zzt(name_code);

-- 界

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Kingdom' set a.Kingdom=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subkingdom' set a.Subkingdom=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Infrakingdom' set a.Infrakingdom=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Superphylum' set a.Superphylum=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Phylum' set a.Phylum=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subphylum' set a.Subphylum=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Class' set a.Class=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subclass' set a.Subclass=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Infraclass' set a.Infraclass=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Superorder' set a.Superorder=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='order' set a.order_e=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Suborder' set a.Suborder=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Infraorder' set a.Infraorder=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Superfamily' set a.Superfamily=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Family' set a.Family=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subfamily' set a.Subfamily=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Tribe' set a.Tribe=b.completename;

-- 屬

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Genus' set a.Genus=b.completename;

select * from bio_fenlei

select Subspecies,Species,a.* from bio_names_zzt a where Subspecies like 'Upupa epops%';

最終格式是這樣的，以戴勝鳥（有亞種）為例：

select Subspecies,Species,a.* from bio_names_zzt a where name_code='702522';?

云斑白條天牛（沒有亞種）