首页 > 代码库 > mysql-索引

mysql-索引

连接查询join,联合查询union(删除重复的)、 union all不删除重复的</p>
        索引:普通索引index(),唯一索引unique key(),主键索引primary key(),联合索引,全文索引;
        注意:主键索引不能为空,唯一索引可以为空
        建立索引,语法:
            create table table_name(
                列定义 primary key,
                ...
            )
            create table table_name(
                列定义 
                primary key(字段名)/index()
            )
            alter table table_name add unique key(index_name);
            联合索引:
            create table firewall(
                host varchar(34) not null,
                port smallint(3) not null,
                access enum(‘deny‘,‘allow‘) not null,
                primary key(host,port)
            )
        删除索引:alter table或者drop index,
        格式:
            drop index index_name on table_name;
            alter table table_name drop index index_name;
            alter table table_name drop primary key;

        复制表数据like,如:create table gg_goods like ecs_goods;
        蠕虫复制:insert into gg_goods(goods_name,shop_price) select goods_name,shop_price from ecs_goods;
                insert into gg_goods(goods_name,shop_price) select goods_name,shop_price from gg_goods;
    创建视图:select goods_id,goods_name,shop_price from ecs_goods a join ecs_category b on a.cat_id=b.cat_id;
            create view vvgoods as select goods_id,goods_name,shop_price from ecs_goods a join ecs_category b on a.cat_id=b.cat_id;
            select * from vvgoods;
        修改视图,重写一次;
        视图和表的关系:表数据的改变会影响视图的结果;视图的改变不一定对表有影响

 

mysql-索引