创建索引+视图

create table city(

city_id int(11) not null auto_increment,

city_name varchar(100) not null,

country_id int(11) not null,

primary key(city_id)

)engine=innodb default charset=utf8;

create table country(

country_id int(11) not null auto_increment,

country_name varchar(100) not null,

primary key(country_id)

)engine=innodb default charset=utf8;

insert into city(city_id,city_name,country_id) value(null,'西安',1);

insert into city(city_id,city_name,country_id) value(null,'NEWYORK',2);

insert into city(city_id,city_name,country_id) value(null,'北京',1);

insert into city(city_id,city_name,country_id) value(null,'上海',1);

insert into country(country_id,country_name) value(null,'China');

insert into country(country_id,country_name) value(null,'America');

insert into country(country_id,country_name) value(null,'Japan');

insert into country(country_id,country_name) value(null,'UK');

alter table city add COLUMN jrq varchar(100) not null;

-- 创建索引  默认使用BTREE

create index idx_city_name on city(city_name);

-- 查看索引 

show index from city;

-- 删除索引

drop index idx_name_id on city;

-- 创建联合索引

create index idx_name_id on city(jrq,country_id);

-- 创建视图   无则创建 有则替换

create or replace view city_country_view

as

select t.*,c.country_name from city t,country c where t.country_id = c.country_id;

-- 查看库的视图  即相当于把视图看成表即可

show tables;

-- 查看视图数据

select * from city_country_view;

-- 删除视图

drop view city_country_view;