创建表

#### 特定结构和约束条件
```asp
null：允许为空（默认）
not null：不允许为null空
default：设置默认值，缺省为null
extra：额外设置
key：键值类型 种类包括：
index（普通索引）、unique（唯一索引）、primary key（主键）、foreign key（外键）、fulltext（全文索引）

```
```asp
insert into t9 values(null,null,null);					#约束条件null为yes时可以插入null空值
create table db2.t1(
    -> name char(10) not null default "",				 #name字段不允许为空，默认值修改为""空字符
    -> age tinyint unsigned default 19,   				#age字段不允许为负数，默认值修改为19
    -> sex enum("man","woman") not null default "man" 	#sex字段使用枚举单选不允许为空，默认值修改为man
    -> );
desc db2.t1;											  #查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     |         |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
| sex   | enum('man','woman') | NO   |     | man     |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
insert into db2.t1(name)values("bob");					#只写入name字段gae和sex将使用自定义默认的值
select * from db2.t1;									 #查看
```
```asp
+------+------+-----+
| name | age  | sex |
+------+------+-----+
| bob  |   19 | man |
+------+------+-----+
```
#### 调整字段位置
调整字段位置，类型和约束调试要抄写下来，否则会恢复默认
```asp
desc db2.t1;														#查看原始排序sex在age下
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     |         |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
| sex   | enum('man','woman') | NO   |     | man     |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
alter table db2.t1 modify sex enum("man","woman") default "man" after name;	#调整字段位置，抄类型和约束条件
desc db2.t1;														#查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
+-------+---------------------+------+-----+---------+-------+
```
#### 修改字段类型
修改的字段类型不能与已经存储的数据冲突，类型和约束调试要抄写下来，否则会恢复默认
```asp
desc db2.t1;													#查看原始name为char（10）
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
alter table db2.t1 modify name varchar(15) not null default "";  #name字段修改为varchar（15），抄类型和约束条件
desc db2.t1;													 #查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(15)         | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
+-------+---------------------+------+-----+---------+-------+
```
#### 修改字段名称
类型和约束调试要抄写下来，否则会恢复默认
```asp
desc db2.t1;															 #查看原始字段
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(15)         | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
mysql> alter table db2.t1 change age agee tinyint unsigned default 19;	#修改字段名字
desc db2.t1;															  #查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(15)         | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
| agee  | tinyint(3) unsigned | YES  |     | 19      |       |
+-------+---------------------+------+-----+---------+-------+
```
#### 添加字段
```asp
desc db2.t1;	#查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     |         |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
| sex   | enum('man','woman') | NO   |     | man     |       |
| email | varchar(50)         | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
alter table db2.t1 add likes set("eat","drink") not null default "eat" after age;	#添加likes字段enum多选指定在age后插入
 desc db2.t1;		#查看
 ```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     |         |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
| likes | set('eat','drink')  | NO   |     | eat     |       |
| sex   | enum('man','woman') | NO   |     | man     |       |
| email | varchar(50)         | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
alter table db2.t1 add class char(7) default "101" first;			#添加class字段在第一位
desc db2.t1;														 #查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| class | char(7)             | YES  |     | 101     |       |
| name  | char(10)            | NO   |     |         |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
| likes | set('eat','drink')  | NO   |     | eat     |       |
| sex   | enum('man','woman') | NO   |     | man     |       |
| email | varchar(50)         | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
```
#### 删除字段
```asp
desc db2.t1;	#查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(15)         | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
| agee  | tinyint(3) unsigned | YES  |     | 19      |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
alter table db2.t1 drop agee;   #删除agee字段
desc db2.t1;					#查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(15)         | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
+-------+---------------------+------+-----+---------+-------+
```
#### 修改表名
```asp
show tables;	#查看表名
```
```asp
+---------------+
| Tables_in_db2 |
+---------------+
| t1            |
+---------------+
```
```asp
alter table db2.t1 rename db2.t111;  #修改表名
show tables;	#查看
```
```asp
+---------------+
| Tables_in_db2 |
+---------------+
| t111          |
+---------------+
```