首页 > 代码库 > mysql_3

mysql_3

--修改表
alter [ignore] table tbl_name
    alter_specification [, alter_specification] ...
alter_specification:
    add [column] column_definition [first | after col_name ]                /*添加列*/    
    | 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                                                            /*删除列*/
    | 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]        /*修改默认字符集*/
    | table_options
    | 列或表中索引项的增、删、改
--复制表
create [temporary] table [if not exists] tbl_name
    [ () like old_tbl_name [ ] ]--创建一个一模一样的空表
    | [as (select_statement)];--复制创建表并复制内容,但索引和完整性约束是不会复制的
--删除表
drop [temporary] table [if exists] tbl_name [, tbl_name] ...
--------------------------------------------------------------------------------------------------------------------
--创建索引
create [unique | fulltext | spatial] index index_name--spatial表示为空间索引
    [using index_type]--为存储引擎支持的索引类型的名称(btree|hash),默认btree
    on tbl_name (index_col_name,...)
index_col_name:
    col_name [(length)] [asc | desc]--length表示使用列的前length个字符创建索引。
--alter table语句
alter [ignore] table tbl_name
    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]/*添加外键*/
    | disable keys--只在MyISAM表中有用,使用ALTER TABLE...DISABLE KEYS可以让MySQL在更新表时停止更新MyISAM表中的非唯一索引,
    | enable keys --然后使用ALTER TABLe ... enable keys重新创建丢失的索引,这样可以大大地加快查询的速度。
--删除索引
drop index index_name on tbl_name

 

mysql_3