9.12 增删改表字段
9.12.1 命令语法及默认添加用演示
1. 命令语法: alter table 表名 add 字段 类型 其他
2. 测试表数据
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id| int(4) | NO | | NULL| |
| name | char(20)| NO | | NULL| |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL| |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3. 实践
例如在表 student 中添加字段 sex,qq 类型分别为 char(4),varchar(15)
4. 执行的命令演示
a. 添加性别列, 默认语句
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id| int(4) | NO | | NULL| |
| name | char(20)| NO | | NULL| |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL| |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student add sex char(4);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id| int(4) | NO | | NULL| |
| name | char(20)| NO | | NULL| |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL| |
| sex | char(4) | YES | | NULL| |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
提示: 默认添加列添加到所有字段结尾
9.12.2 指定添加列在表里的位置
b. 指定添加 qq 到 name 列的后面
mysql> alter table student add qq varchar(15) after name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id| int(4) | NO | | NULL| |
| name | char(20)| NO | | NULL| |
| qq| varchar(15) | YES | | NULL| |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL| |
| sex | char(4) | YES | | NULL| |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
c. 在第一列添加 address 列
mysql> alter table student add address char(20) first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| address | char(20)| YES | | NULL| |
| id | int(4) | NO | | NULL| |
| name| char(20)| NO | | NULL| |
| qq | varchar(15) | YES | | NULL| |
| age | tinyint(2) | NO | | 0 | |
| dept| varchar(16) | YES | | NULL| |
| sex | char(4) | YES | | NULL| |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
9.13 更改表名
9.13.1 rename 法
1. 命令语法: rename table 原表名 to 新表名;
列如: 表 student 名字更改为 user
mysql> show tables;
+---------------+
| Tables_in_zbf |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
mysql> rename table student to user;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_zbf |
+---------------+
| user |
+---------------+
1 row in set (0.01 sec)
9.13.2 Alter 方法
mysql> alter table user rename to student;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_zbf |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
9.14 删除表
命令语法: drop table < 表名 >
例如: 删除表名为 student 的表
mysql > drop table student;
Query OK,
0 rows affected(0.01 sec) mysql > show tables;
Empty set(0.01 sec)
Mysql DBA 高级运维学习笔记 - 增删表字段更改表名删除表实战
来源: http://www.bubuko.com/infodetail-2478119.html