4..修改数据表

1.修改表名

ALTER    TABLE    <旧表名>  RENAME    [TO]   <新表名>；

mysql> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sqltest            |

| sys                |

| teat_db            |

+--------------------+

6 rows in set (0.00 sec)

 

mysql> USE teat_db;

Database changed

mysql> SHOW TABLES;

+-------------------+

| Tables_in_teat_db |

+-------------------+

| student           |

| student_sclre     |

| yuangong          |

| yuangong1         |

+-------------------+

4 rows in set (0.00 sec)

mysql> ALTER TABLE student RENAME TO newstudent;

Query OK, 0 rows affected (0.02 sec)

 

mysql> SHOW TABLES;

+-------------------+

| Tables_in_teat_db |

+-------------------+

| newstudent        |

| student_sclre     |

| yuangong          |

| yuangong1         |

+-------------------+

4 rows in set (0.00 sec)

2.修改字段名

ALTER  TABLE  <表名>   CHANGE   <旧字段名>   <新字段名>  <新字段类型>；

 

3.修改字段类型

ALTER  TABLE  <表名>  MODIFY  <字段名>   <新字段类型>；

修改字段类型有两种方法第一种就是上面的这种，另外一种就是通过2中的语句，只是在写新字段名和旧字段名的时候都是原有的字段名，但是不能省略，新字段类型处更改为新的字段类型。同时要注意在已经有数据计录的时候不要轻易更改字段类型，这样会影响已经有的数据计录。

4.添加字段

ALTER   TABLE   <表名>  ADD   <新字段名称>   <新字段类型>   [约束条件]    [FIRST |AFTER  已存在的字段名]；

如果没有后面的FIRST| AFTER ，默认是添加到所有字段的后面。

5.删除字段

ALTER  TABLE  <表名>   DROP  <字段名>；

6.修改字段的排列位置

ALTER   TABLE   <表名>   MODIFY    <字段1>  <数据类型>    FIRST|AFTER  <字段2>；

mysql> SHOW CREATE TABLE newstudent\g;

+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table      | Create Table                                                                                                                                                                                                         |

+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| newstudent | CREATE TABLE `newstudent` (

  `id` int(12) NOT NULL,

  `name` varchar(25) NOT NULL,

  `minzu` varchar(10) DEFAULT NULL,

  `idcard` int(18) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> ALTER TABLE newstudent CHANGE id newid INT(20) ;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> ALTER TABLE newstudent MODIFY newid  INT(30);

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> ALTER  TABLE newstudent ADD school VARCHAR(30) AFTER name ;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> ALTER TABLE newstudent DROP idcard;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC newstudent;

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| newid  | int(30)     | NO   | PRI | NULL    |       |

| name   | varchar(25) | NO   |     | NULL    |       |

| school | varchar(30) | YES  |     | NULL    |       |

| minzu  | varchar(10) | YES  |     | NULL    |       |

+--------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

7.更改表的存储引擎

ALTER  TABLE   <表名>   ENGING=<更改后的存储引擎名>； 

8.删除表的外键约束

ALTER   TABLE   <>   DROP  FOREIGN   KEY  <外键约束名>；

mysql> SHOW  CREATE TABLE student_sclre;

+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table         | Create Table                                                                                                                                                                                                                                                                                                                                |

+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student_sclre | CREATE TABLE `student_sclre` (

  `student_id` int(12) DEFAULT NULL,

  `chinese` float DEFAULT NULL,

  `math` float DEFAULT NULL,

  `english` float DEFAULT NULL,

  KEY `student_id` (`student_id`),

  CONSTRAINT `student_sclre_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `newstudent` (`newid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

 

mysql> ALTER TABLE student_sclre DROP FOREIGN KEY student_sclre_ibfk_1;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> ALTER TABLE student_sclre ENGINE=MyISAM;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> SHOW CREATE TABLE student_sclre;

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table         | Create Table                                                                                                                                                                                                                              |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student_sclre | CREATE TABLE `student_sclre` (

  `student_id` int(12) DEFAULT NULL,

  `chinese` float DEFAULT NULL,

  `math` float DEFAULT NULL,

  `english` float DEFAULT NULL,

  KEY `student_id` (`student_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)