MySQL集锦3-基础

? contents

1、复制表结构、复制表数据

create table t1(
id int not null auto_increment primary key,
name varchar(30)
);
insert into t1(name) values("user1");
insert into t1(name) values("user2");

create table t2 like t1; #复制表结构
desc t2;
insert into t2 select * from t1;

2、索引
方式1

show index from t1;
create index index_name on t1(name);
create unique index un_name on t1(name);
drop index index_name on t1;

方式2

alter table t1 add index index_name(name);
alter table t1 drop index index_name;
alter table t1 drop primary key;

会报错，理由是因为我们设置的主键是primary key


解决办法

alter table t1 modify id int not null;
alter table t1 drop primary key;
alter table t1 add primary key(id);
alter table t1 modify id int not null auto_increment;

3、视图

insert into t1(name) values('user2'),('user3'),('user4'),('user5');
create view v_t1 as select * from t1 where id>2 and id<5;

4、函数




5、事物
mysql engine=innodb的时候才支持事物
set autocommit=0;#关闭事物自动提交功能
savepoint p1;

6、重排auto_increment
alter table t1 auto_increment=1;