mysql 查看及修改表结构
来源:互联网 发布:外交部流氓回答知乎 编辑:程序博客网 时间:2024/06/05 20:58
创建一个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 changedmysql> 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 affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> 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 affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> 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 affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> 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
阅读全文
0 0
- 查看mysql字符集及修改表结构
- 查看mysql字符集及修改表结构
- 查看mysql字符集及修改表结构
- mysql 查看及修改表结构
- MySQL 表结构查看以及表结构修改
- 查看及修改MySQL注释
- mysql修改表名,查看表结构,删除表
- Mysql学习—查看表结构、修改和删除数据表
- mysql查看表结构
- mysql 查看表结构
- mysql查看表结构
- mysql查看表结构
- MySQL查看表结构
- mysql查看表结构
- Oracle 查看、修改表结构
- MYSQL查看及修改表使用的存储引擎
- 查看mysql字符集及修改表结…
- MYSQL之查看及修改表使用的存储引擎
- Spinner android:entries属性/ xml 中 @array 写法
- POJ 3984 迷宫问题(BFS+路径记录)
- uva 1584
- java(循环,分支)语句总结
- JavaEE之反射
- mysql 查看及修改表结构
- 在Windows平台上安装Node.js及NPM模块管理
- Markdown入门操作
- java--集合--Collection--Set
- 传统定时器技术入门-java 类Timer
- sklearn官网学习入门二
- luogu3381【模板】最小费用最大流(zkw费用流板子)
- FZU2278(大数)
- WINDOWS svn 不显示图标状态