对Mysql数据表本身进行操作
2024-09-01 13:01:03
创建实验环境
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec) mysql> use test_db;
Database changed
mysql> create table test_table(id int(10),name varchar(20),age int);
Query OK, 0 rows affected (0.04 sec)
(1).查看表结构
查看表结构有四种方法，如果查找的不是当前数据库里的表，一定要使用[数据库名].[表名]的格式使用。最常用的一般是desc [表名]。
mysql> desc test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec) mysql> explain test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec) mysql> show columns from test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec) mysql> show fields from test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(2).修改表名
alter table [旧的表名] rename [新的表名];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> alter table test_table rename table_newname;
Query OK, 0 rows affected (0.04 sec) mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| table_newname |
+-------------------+
1 row in set (0.00 sec)
(3).只修改表的字段类型
alter tabel [表名] modify [字段名] [修改后的字段类型];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
修改已有数据的表的字段类型，请谨慎。
mysql> desc table_newname;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec) mysql> alter table table_newname modify name char(22);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(22) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(4).修改表的字段名和字段类型
alter table [表名] change [旧的字段名] [新的字段名] [新的字段类型];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
修改已有数据的表的字段类型，请谨慎。如果不想修改字段类型，请保持类型的一致。
mysql> desc table_newname;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(22) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec) mysql> alter table table_newname change name newname char(30);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(5).添加字段
alter table [表名] add [字段名] [字段类型];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> desc table_newname;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec) mysql> alter table table_newname add job char(40);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(6).在表的指定位置添加字段
字段添加到第一位：alter table [表名] add [字段名] [字段类型] first;
字段添加到某一位的后面：alter table [表名] add [字段名] [字段类型] after [字段名];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> desc table_newname;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec) mysql> alter table table_newname add sex enum('M','W') first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table table_newname add address varchar(40) after newname;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| sex | enum('M','W') | YES | | NULL | |
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
(7).删除表的指定字段
alter table [表名] drop [字段名];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> desc table_newname;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| sex | enum('M','W') | YES | | NULL | |
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec) mysql> alter table table_newname drop sex;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
如果还有其他的操作，以后再加
最新文章
- sqlServer去除字符串空格
- 设计模式(三)：“花瓶+鲜花”中的装饰者模式(Decorator Pattern)
- mac中显示隐藏文件
- Javascript:来一个AJAX封装函数
- Struts2从一个action转到另一个action的两种方法
- Python 监控nginx服务是否正常
- Thrift 个人实战--Thrift RPC服务框架日志的优化
- php短信发送
- 600字读懂 Git
- 对CNN模块的分析
- Android获取文件夹路径 /data/data/
- jq获取浏览器的高度
- CodeForces776-A.Serial Killer-string
- JasperReport报表开发(一)--原理介绍
- 洛谷 [P1341]无序字母对
- Aizu - 0531 Paint Color
- spark2.1：使用df.select(when(a===b,1).otherwise(0))替换(case when a==b then 1 else 0 end)
- web前端页面设计小笔记
- modbus调试工具
- 设置 sideload Outlook Add-ins