mysql运维改表_Mysql DBA 高级运维学习笔记-增删表字段更改表名删除表实战

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)

anTazFP.png

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 高级运维学习笔记-增删表字段更改表名删除表实战

标签:utf8   位置   des   tables   运维学习   alter   ant   nta   empty

本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源:http://blog.51cto.com/10642812/2065961

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值