MySQL對表的修改(增加或刪減列，創(chuàng)建或取消索引等)
2024-07-24 12:58:39
供稿：網(wǎng)友
MySQL創(chuàng)建了一個數(shù)據(jù)庫后，接著創(chuàng)建了一張表，并添加了一些字段，那么我以后還想再增加字段怎么做？
解答：使用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);
