mysql学习笔记-DDL(二)-表结构修改

# mysql中的数据类型有很多，主要分为三类 数值类型，字符串类型，日期时间类型。

# 数值类型，字符串类型，时间和日期类型

/* DDL-表操作-修改
1、创建表
2、添加字段名
ALTER TABLE 表名 ADD 字段名 类型（长度）[comment注释][约束];
3、修改字段名，修改字段类型；
4、删除字段名
5、修改表名
6、删除表名
*/

# 创建表
create table employee(
			id int comment'编号',
			worknomber varchar(10) comment'员工工号',
			name varchar(10) comment'姓名',
			gender char(1) comment'性别',
			age tinyint unsigned comment'年龄',
			idcard char(18) comment'身份证号',
			entrydate date comment'入职日期'
)comment'员工信息表';

# 查看创建的表结构
mysql> desc employee;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id         | int              | YES  |     | NULL    |       |
| worknomber | varchar(10)      | YES  |     | NULL    |       |
| name       | varchar(10)      | YES  |     | NULL    |       |
| gender     | char(1)          | YES  |     | NULL    |       |
| age        | tinyint unsigned | YES  |     | NULL    |       |
| idcard     | char(18)         | YES  |     | NULL    |       |
| entrydate  | date             | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


# 查看当前数据库所有的表
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employee         |
| tb_uset          |
+------------------+
2 rows in set (0.00 sec)

# 添加字段名，新增nickname，数据类型为varchar(20)
mysql> alter table employee add nickname varchar(20) comment '昵称';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 查看添加后的效果
mysql> desc employee;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id         | int              | YES  |     | NULL    |       |
| worknomber | varchar(10)      | YES  |     | NULL    |       |
| name       | varchar(10)      | YES  |     | NULL    |       |
| gender     | char(1)          | YES  |     | NULL    |       |
| age        | tinyint unsigned | YES  |     | NULL    |       |
| idcard     | char(18)         | YES  |     | NULL    |       |
| entrydate  | date             | YES  |     | NULL    |       |
| nickname   | varchar(20)      | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

# 修改字段类型，将nickname由varchar(20) 改为30
mysql> alter table employee modify nickname varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id         | int              | YES  |     | NULL    |       |
| worknomber | varchar(10)      | YES  |     | NULL    |       |
| name       | varchar(10)      | YES  |     | NULL    |       |
| gender     | char(1)          | YES  |     | NULL    |       |
| age        | tinyint unsigned | YES  |     | NULL    |       |
| idcard     | char(18)         | YES  |     | NULL    |       |
| entrydate  | date             | YES  |     | NULL    |       |
| nickname   | varchar(30)      | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

# 修改字段名，将nickname改为username ，类型改为varchar(15)
mysql> alter table employee change  nickname username varchar(15);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id         | int              | YES  |     | NULL    |       |
| worknomber | varchar(10)      | YES  |     | NULL    |       |
| name       | varchar(10)      | YES  |     | NULL    |       |
| gender     | char(1)          | YES  |     | NULL    |       |
| age        | tinyint unsigned | YES  |     | NULL    |       |
| idcard     | char(18)         | YES  |     | NULL    |       |
| entrydate  | date             | YES  |     | NULL    |       |
| username   | varchar(15)      | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

# 删除字段名，删除username
mysql> alter table employee  drop username;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc employee;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id         | int              | YES  |     | NULL    |       |
| worknomber | varchar(10)      | YES  |     | NULL    |       |
| name       | varchar(10)      | YES  |     | NULL    |       |
| gender     | char(1)          | YES  |     | NULL    |       |
| age        | tinyint unsigned | YES  |     | NULL    |       |
| idcard     | char(18)         | YES  |     | NULL    |       |
| entrydate  | date             | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

# 修改表名，将employee修改为emp；
mysql> alter table employee rename to emp;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| emp              |
| tb_uset          |
+------------------+
2 rows in set (0.00 sec)

# 删除表 tb_uset
mysql> drop table if exists tb_uset;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)


# 删除并创建表 employee
mysql> truncate table employee;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)