索引,视图

使用索引是提高select操作性能的最佳途径
创建索引:
create index username on user(user_name);
查看索引:
show index from table_name;
删除索引:
drop index index_name on table_name


视图是一中虚拟存在的表,并不在数据库中实际存在
创建视图:
create or replace view selectuser as
select user_name from user where user_id=1;
mysql中定义的视图有限制:在from关键字后面不能包含子查询
以下视图不能更新:
包含聚合函数
create or replace view view_name as
select sum(amount) from table_name;
常量视图:
create or replace view pi as select 3.1415 as pi;
select中包含子查询
create view view_name as select (select city from city where city_id=1);
with[cascaded|local]chech option
local:只要满足本视图就可以更新
cascaded:必须满足所有针对该视图的所有视图的条件才可以更新 (默认)
例子:
create or replace view playment_view as
select payment_id,amount from payment
where amount<10 with check option;

create or replace view playment_view1 as
select payment_id,amount from payment_view
where amount>5 with local check option;

create or replace view playment_view2 as
select payment_id,amount from payment_view
where amount>5 with cascaded check option;

update payment_view1 set amount=10 where payment_id=3;正确
update payment_view2 set amount=10 where payment_id=3;错误

查看视图:
show tables;

删除视图:
drop view view_name;