mysql复习_alter table app_member change uid uid int primary -CSDN博客

本文链接：https://blog.csdn.net/ailinyingai/article/details/103488966

create database app default charset utf8; # 指定字符集建库

show create database app\G #查看建库语句以及库的字符集

注意:MySQL5.1和MySQL5.5环境的默认建表语句中的引擎是不同的。如果希望控制表的引擎，就要在建表语句中显示的指定引擎建表
MySQL5.1以及之前默认引擎为MyISM
MySQL5.5.5以后默认引擎为InnoDB
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。

CREATE TABLE app_member (
uid int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘用户ID’,
nickname char(16) NOT NULL DEFAULT ‘’ COMMENT ‘昵称’,
sex tinyint(3) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘性别’,
birthday date NOT NULL DEFAULT ‘0000-00-00’ COMMENT ‘生日’,
qq char(10) NOT NULL DEFAULT ‘’ COMMENT ‘qq号’,
score mediumint(8) NOT NULL DEFAULT ‘0’ COMMENT ‘用户积分’,
login int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘登录次数’,
reg_ip bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘注册IP’,
reg_time int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘注册时间’,
last_login_ip bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘最后登录IP’,
last_login_time int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘最后登录时间’,
status tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘会员状态’,
PRIMARY KEY (uid),
UNIQUE KEY nickname (nickname)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT=‘会员表’

show create table app_member\G

mysql> alter table app_member change uid uid int primary key auto_increment not null ;
mysql> alter table app_member drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

创建主键索引
alter table app_member change uid uid int primary key;

删除主键索引
alter table app_member drop primary key;

创建普通索引
alter table app_member add index qq(qq(6));

根据列的前n个字符创建索引
create index qq on app_member(qq);

根据多个列创建联合索引
create index reg_time_ip on app_member(reg_time,reg_ip);

根据多个列的前n个字符创建联合索引
create index reg_time_ip on app_member(reg_time(8),reg_ip(8));

删除普通索引
alter table app_member drop index nickname;

出错代码列表

1005：创建表失败

1006：创建数据库失败

1007：数据库已存在，创建数据库失败

1008：数据库不存在，删除数据库失败