MySQL对表的修改(增加或删减列,创建或取消索引等)

12/7/2009来源:MySQL教程人气:16491

MySQL创建了一个数据库后,接着创建了一张表,并添加了一些字段,那么我以后还想再增加字段怎么做?

解答:使用alter table(修改表)!

    ALTER TABLE语法:

    ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

| DISCARD TABLESPACE | IMPORT TABLESPACE | table_options
  | partition_options
  | ADD PARTITION partition_definition
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#alter-table

我的实例:

增加一个字段:

alter table book add name varchar(20);

觉得20太小,修改为50

alter table book change name name varchar(50);

增加几个字段:

alter table book add authors varchar(100),add category varchar(20),add

price double(10,2);

删除一列:

alter table book drop cover;

在某个位置加上1列:

alter table book add cover varchar(100) after(first) publishdate;

修改某条记录某个字段或多个字段的值:

update book set column_name1="" where column_name2="";

删除一条记录:

delete from table_name where where_contion;

修改某个表中的记录的顺序:

alter table book order by bookid(默认为升序降序为desc);