创建一个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