1、创建表格时添加: `create table tablename(id int auto_increment primary key [,...])`
2、创建表格后添加: `alter table tablename add id int auto_increment primary key`
3、设置自增字段初始值:`alter table tablename auto_increment =x ;`
4、设置主键:`alter table tablename add primary key(field_name);`
5、创建复合主键:
```sql
create table tablename (
studentno int,
courseid int,
score int,
primary key (studentno,courseid) );
```
6、设置复合主键:`alter table tablename add primary key (列1,列2,列3); `
7、重命名表: `alter table table_old_name rename table_new_name;`
8、改变字段的类型:`alter table tableName modify field_name field_type;`
9、重命名字段:`alter table tableName change old_field_name new_field_name new_field_type;`
10、删除字段:`alter table tableName drop column field_name;`
11、增加一个新字段:`alter table tableName add new_field_name field_type;
12、新增一个字段,默认值为0,非空:alter table tableName add new_field_name field_type not null default '0';`
13、新增一个字段,默认值为0,非空,自增,主键:`alter table tabelname add new_field_name field_type default 0 not null auto_increment ,add primary key (new_field_name); `
#### 索引操作
```sql
1.PRIMARY KEY(主键索引)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column` )
3.INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
5.多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
```