mysql 查看及修改表结构

创建一个student表

mysql> create table student(id int,name  varchar(20),age int);
Query OK, 0 rows affected

查看表结构

1.desc tablename

mysql> desc/describe student;
+-------+------------+------+------+--------+-------+
| Field  | Type       | Null  | Key | Default | Extra |
+-------+------------+------+------+--------+-------+
| id    | int(11)     | YES |      | NULL   |       |
| name | varchar(20) | YES |      | NULL   |       |
| age   | int(11)     | YES |      | NULL   |       |
+-------+------------+------+------+--------+-------+
3 rows in set

2.show columns from tablename

mysql> show columns from student;
+-------+------------+------+------+--------+-------+
| Field  | Type       | Null  | Key | Default | Extra |
+-------+------------+------+------+--------+-------+
| id    | int(11)     | YES |      | NULL   |       |
| name | varchar(20) | YES |      | NULL   |       |
| age   | int(11)     | YES |      | NULL   |       |
+-------+------------+------+------+--------+-------+
3 rows in set

3.show create table tablename

mysql> show create table student;
+--------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                          |
+--------+--------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

4.use information_schema select * from columns where table_name=’student’;

mysql> use information_schema;
Database changed
mysql> select * from columns where table_name='student';
+-----------------+----------------+--------------+---------------+-------------------+------------------+--------------+-------------+------------------------------+---------------------------+-------------------+----------------+--------------------+-----------------------+------------------+---------------+--------------+--------+----------------------------+-------------------+-------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                 | COLUMN_COMMENT | GENERATION_EXPRESSION |
+-----------------+----------------+--------------+---------------+-------------------+------------------+--------------+-------------+------------------------------+---------------------------+-------------------+----------------+--------------------+-----------------------+------------------+---------------+--------------+--------+----------------------------+-------------------+-------------------------+
| def             | kaner          | student       | id            |                 1 | NULL             | YES         | int          | NULL                         | NULL                      |                10 |              0 | NULL               | NULL                  | NULL             | int(11)        |              |        | select,insert,update,references |                   |                         |
| def             | kaner          | student       | name         |                 2 | NULL             | YES         | varchar     |                           20 |                        60 | NULL              | NULL           | NULL               | utf8                  | utf8_general_ci    | varchar(20)    |              |        | select,insert,update,references |                   |                         |
| def             | kaner          | student       | age           |                 3 | NULL             | YES         | int          | NULL                         | NULL                      |                10 |              0 | NULL               | NULL                  | NULL             | int(11)        |              |        | select,insert,update,references |                   |                         |
| def             | mydb          | student       | id            |                 1 | NULL             | NO          | int          | NULL                         | NULL                      |                10 |              0 | NULL               | NULL                  | NULL             | int(10)        | PRI          |        | select,insert,update,references |                   |                         |
| def             | mydb          | student       | name         |                 2 | NULL             | NO          | varchar     |                           20 |                        60 | NULL              | NULL           | NULL               | utf8                  | utf8_general_ci    | varchar(20)    |              |        | select,insert,update,references |                   |                         |
| def             | mydb          | student       | sex           |                 3 | NULL             | YES         | varchar     |                            4 |                        12 | NULL              | NULL           | NULL               | utf8                  | utf8_general_ci    | varchar(4)     |              |        | select,insert,update,references |                   |                         |
| def             | mydb          | student       | birth          |                 4 | NULL             | YES         | year        | NULL                         | NULL                      | NULL              | NULL           | NULL               | NULL                  | NULL             | year(4)        |              |        | select,insert,update,references |                   |                         |
| def             | mydb          | student       | department     |                 5 | NULL             | YES         | varchar     |                           20 |                        60 | NULL              | NULL           | NULL               | utf8                  | utf8_general_ci    | varchar(20)    |              |        | select,insert,update,references |                   |                         |
| def             | mydb          | student       | address       |                 6 | NULL             | YES         | varchar     |                           50 |                       150 | NULL              | NULL           | NULL               | utf8                  | utf8_general_ci    | varchar(50)    |              |        | select,insert,update,references |                   |                         |
+-----------------+----------------+--------------+---------------+-------------------+------------------+--------------+-------------+------------------------------+---------------------------+-------------------+----------------+--------------------+-----------------------+------------------+---------------+--------------+--------+----------------------------+-------------------+-------------------------+
9 rows in set

修改表结构

增加字段 ALTER TABLE 表名 ADD 列名 属性

mysql> alter table student add grade int;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------+------+------+--------+-------+
| Field  | Type       | Null  | Key | Default | Extra |
+-------+------------+------+------+--------+-------+
| id    | int(11)     | YES |      | NULL   |       |
| name | varchar(20) | YES |      | NULL   |       |
| age   | int(11)     | YES |      | NULL   |       |
| grade | int(11)     | YES |      | NULL   |       |
+-------+------------+------+------+--------+-------+
4 rows in set

删除字段 ALTER TABLE 表名 DROP COLUMN 列名

mysql> alter table student drop column age;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+-------+------------+------+------+--------+-------+
| Field  | Type       | Null  | Key | Default | Extra |
+-------+------------+------+------+--------+-------+
| id    | int(11)     | YES |      | NULL   |       |
| name | varchar(20) | YES |      | NULL   |       |
| grade | int(11)     | YES |      | NULL   |       |
+-------+------------+------+------+--------+-------+
3 rows in set

修改字段 ALTER TABLE 表名 modify 列名 属性

mysql> alter table student modify grade varchar(20);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+-------+------------+------+------+--------+-------+
| Field  | Type       | Null  | Key | Default | Extra |
+-------+------------+------+------+--------+-------+
| id    | int(11)     | YES |      | NULL   |       |
| name | varchar(20) | YES |      | NULL   |       |
| grade | varchar(20) | YES |      | NULL   |       |
+-------+------------+------+------+--------+-------+
3 rows in set