mysql innodb 老顽童_mysql老顽童笔记

hepl create;--查看创建

show global variable like 'innodb_%'--查看innodb

perror 17 --查看错误

create table `table`(id int);--重复的table用反引号

create table aa(`database` int);

create table `merchant`(

`Merchant_ID` int(4) not null auto_increment,

`Merchant_Name` varchart(40) Not null,

`address` text,

primary key (`merchant_id`),

key `merchant_name`(`merchant_name`)

)engine=innodb charset=utf8;

drop table merchant\c

show create table merchant;

create table `stu`(

`id` int(11) default null,

`id2` int(11) default null,

`id3` char(10) default null,

key id123 (id,id2,id3));

show index from stu;

alter table merchant add id int first;

desc merchant;

alter table merchant add sex enum('m','f') default 'm';

alter table merchant add email text after address ---在address下面

alter table merchant add key (email(15));--添加索引

show index form merchant;

删除

alter table merchant drop address;

修改

alter table merchant change Merchant_ID int(6) not null；

alter table merchant add primary key(id);

perror 1068

alter table merchant drop PRIMERY key;

show index from merchant;

alter table merchant add primary key(id);

desc merchant

rename table table_name to new_table_name

alter练习1

alter table t2 drop column c,drop column d;

alter table t1 change a b integer;

alter table t1 change  b b bigint not null;

alter table t1 modify b bigint not null;

alter table yourtablename frop foreign key fk_symbol;

alter table tal_name convert to character set charset_name;

如果您有一列使用一种字符集(如latin1),但是存储的值实际上使用了其它

的字符集(如utf8)，这种情况不是您想要的，此时您必须这样的列进行以下操作。

alter table t1 change c1 c1 blob;

alter table t1 change c1 c1 text character set utf8;

alter table tbl_name default character charset_name;

alter table tal_name discard tablespace;--删除当前的.ibd文件

alter table tbl_name import tablespace;--把备份的.ibd文件还原到表中，需把

此文件复制到数据库目录中

alter table t1 add partition p3 values less than(2002);

alter table t2 coalesce partition 2; 把t2使用的分区数目由6个减少到4个；

alter table t2 add d timestamp;

alter tabke t2 add index(d), add index(a);

alter练习2

alter table t2 add c int unsigned not null auto_increment,

add primary key(c;)