mysql 查看及修改表结构 r囧r小猫 2022-06-04 09:07 250阅读 0赞 ### 创建一个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
还没有评论，来说两句吧...