索引、视图、触发器

select count(*) from extend_mobile

select * from extend_mobile em where em.city='北京';
select * from extend_mobile em where em.provice='江苏';
-- 组合查询
select * from extend_mobile em where em.city='巢湖' and em.provice='安徽'
select * from extend_mobile em where em.city='巢湖' and em.provice='安徽' and em.code='565'

-- 创建普通索引
alter table extend_mobile add index idx_city (city);
alter table extend_mobile add index idx_provice (provice);

-- 创建唯一索引（索引列的值必须唯一，但允许空值）
alter table extend_mobile add unique index idx_code (code);

-- 创建组合普通索引
alter table extend_mobile add index idx_city_provice (city,provice);
alter table extend_mobile add unique index idx_city_provice_code (city,provice,code);

-- 查看索引
show index from extend_mobile
-- 删除索引
drop index idx_city_provice_code on extend_mobile

-- 创建视图

create view vw_test as select pre,provice,city from extend_mobile

create view vw_test1 as select pre,provice,city from extend_mobile limit 0,3 
create view vw_test1 as select pre,provice,city from extend_mobile where  pre between '1300000' and '1300002'
 
select * from vw_test where pre=1300996
select * from vw_test1

-- 查看视图
show create view vw_test
-- 查看所有视图
show table status where comment='view'

-- 删除视图
drop view vw_test1

-- 触发器 
-- 监听insert事件
create trigger tr_goodsisert after insert  on t_goods 
for each row
begin
        insert into t_operation values (null,user(),'insert操作',now());

end
-- 删除触发器
drop trigger tr_goodsisert;

insert into t_goods values(null,'java','100');

-- 监听update事件

-- 监听detele事件

-- 监听订单表
create trigger tr_orderinsert after insert on t_order
for each row 

begin
  update t_goods set stock=stock - new.num where id=new.goodsid ;

end 

insert into t_order values(null,'54','20')
-- 监听订单表
create trigger tr_orderinsert after insert on t_order
for each row 

begin
  update t_goods set stock = stock + old.num where id=old.goodsid ;

end 
-- 查看触发器 (不能修改，只能删除后重建)
show triggers

-- 删除触发器
drop trigger tr_orderinsert