MySQL集锦3-基础

最新推荐文章于 2024-09-19 21:52:00 发布

xiaokang930

最新推荐文章于 2024-09-19 21:52:00 发布

阅读量102

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/xiaokang930/article/details/84652999

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

? 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

[img]http://dl2.iteye.com/upload/attachment/0101/1623/12ce9c7c-dcf1-3913-9a17-e93fedb1a93c.jpg[/img]
解决办法


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、函数

[img]http://dl2.iteye.com/upload/attachment/0101/1648/d1162f9c-aae3-3309-9b01-d6655f1fb61f.jpg[/img]

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

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