MySQL之数据表控制语句

你的名字 2023-06-19 09:43 2阅读 0赞

> 博文大纲：
> 
>  *  一、约束条件相关语句
>     
>      *  主键约束
>      *  非空约束
>      *  设置值的唯一性
>      *  设置列的默认值
>      *  设置自增值
>  *  二、ALTER指令的使用
>     
>      *  修改列值的数据长度
>      *  修改字段名
>      *  向表中插入一个新的字段
>      *  添加字段时添加约束
>      *  添加一个外键
>      *  删除外键
>      *  删除列
>      *  修改列的顺序
>      *  删除表

### 一、约束条件相关语句 ###

#### 1、主键约束（主键约束要求主键列的数据唯一，并且不允许为空） ####

mysql> create database test01; 
    
    mysql> use test01;
    
    mysql> create table t1(
        -> id int(10),
        -> name varchar(10) primary key,
        -> sex varchar(5),
        -> info varchar(200)
        -> );

**确定创建的是否为主键（该列是否有PRI字样）：**

![MySQL之数据表控制语句][MySQL]  
**上述方法，是在定义列的同时定义主键，下面来写一下定义完所有列之后指定主键：**

mysql> create table t2(
        -> id int(10),
        -> name varchar(10),
        -> sex varchar(5),
        -> primary key(id)
        -> );

**确认是否有主键：**

![MySQL之数据表控制语句][MySQL 1]

#### 2、非空约束（不允许列的值为空） ####

mysql> create table t3(
        -> id int(6) not null,
        -> name varchar(10)
        -> );

查看表信息确认：

![MySQL之数据表控制语句][MySQL 2]

#### 3、设置值的唯一性（不允许重复数据，可以为空，但只能有一个空，否则就会被视为重复） ####

mysql> create table t7(
        -> id int not null unique,
        -> name varchar(20)
        -> );

查看其表结构：

![MySQL之数据表控制语句][MySQL 3]

可以看到其被标识为了主键，但是在创建时并没有指定它是主键，而是这一列的属性基本满足了主键的要求，如唯一、不可以为空。

#### 4、设置列的默认值（如果该列为空，则写入默认值） ####

mysql> create table t4(
        -> id int(2) not null,
        -> name varchar(20),
        -> project varchar(20) default 'mysql'
        -> );

查看确认：

![MySQL之数据表控制语句][MySQL 4]

#### 5、设置自增值（一般用于id列，自增列必须设置为主键） ####

注：mysql只允许设置初始值，而不允许设置自增值，也就是说，可以设置为第一个值为5，然后依次递增，如：5、6、7.....但不可以设置其一次递增2个数，比如：5、7、9......

mysql> create table t5(
        -> id int not null primary key auto_increment,
        -> name varchar(20)
        -> );

查看表结构进行确认：

![MySQL之数据表控制语句][MySQL 5]

测试其自增效果：

mysql> insert into t5(name) values('zhangsan'),('lisi');
    mysql> select * from t5;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    +----+----------+

从上面的测试可以看出，只插入了两个name的值，并没有插入id的值，但是查看表数据时，id已经有值了，说明自增生效。

**设置自增的起始值**

#定义初始值为5
    mysql> create table t6(                                  
        -> id int primary key auto_increment,
        -> name varchar(20)
        -> ) auto_increment=5;
    #插入数据进行测试
    mysql> insert into t6(name) values('zhangsan'),('lisi');

验证其自增值：  
![MySQL之数据表控制语句][MySQL 6]

### 二、ALTER指令的使用 ###

#### 1、修改列值的数据长度 ####

确认修改后的表结构：

![MySQL之数据表控制语句][MySQL 7]

#### 2、修改字段名（在修改字段名的同时，还可以修改其新字段名的数据类型及数据长度） ####

验证修改后的结果：

![MySQL之数据表控制语句][MySQL 8]

#### 3、向表中插入一个新的字段 ####

**1）在最后一列插入新列：**

查看插入后的列：

![MySQL之数据表控制语句][MySQL 9]  
**2）在表格开头插入新列：**

mysql> alter table t3 add sex char(1) first;

**3）在指定的列后面插入新列：**

mysql> alter table t3 add loc varchar(255) after name;

#### 4、添加字段时添加约束 ####

mysql> alter table t3 add hobyy varchar(255) default 'work';

#### 5、添加一个外键 ####

进行此操作前，需要自行找一个有主键的表（我这里t1表的name列是主键）。

现在t1表的结构如下：  
![MySQL之数据表控制语句][MySQL 10]  
t3表的结构如下：  
![MySQL之数据表控制语句][MySQL 11]

现在将t3表的name列添加为t1表的name列的外键（其中t3\_t1\_name为自定义的约束名称）：

mysql> alter table t3 add constraint t3_t1_name foreign key(name) references t1(name);

查看t3表的变化：

![MySQL之数据表控制语句][MySQL 12]

#### 6、删除外键 ####

将上面添加的外键删除，t3\_t1\_name是外键的名称。

mysql> alter table t3 drop foreign key t3_t1_name;
    mysql> alter table t3 drop  key t3_t1_name;

#### 7、删除列 ####

mysql> alter table t3 drop tel;

**注意：如果要删除的列和其他表中的列有关联关系，则需要先删除关系，再删除列。否则当以后再创建了相同名称的列时，会自动将其建立关系。**

#### 8、修改列的顺序 ####

mysql> alter table t3 modify name varchar(10) first;

#### 9、删除表 ####

mysql> drop table t5;            
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> drop table t5;            
    ERROR 1051 (42S02): Unknown table 'test01.t5'
    mysql> drop table if exists t5;       
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> show warnings;    
    +-------+------+---------------------------+
    | Level | Code | Message                   |
    +-------+------+---------------------------+
    | Note  | 1051 | Unknown table 'test01.t5' |
    +-------+------+---------------------------+
    1 row in set (0.00 sec)

**同样，当执行删除表操作时，如果存在关联关系，则需要先删除关联关系，再删除表。**

———————— 本文至此结束，感谢阅读 ————————

[MySQL]: https://s1.51cto.com/images/blog/201912/04/e2dffd14e0961086cecdfb8bbdfdc6ec.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 1]: https://s1.51cto.com/images/blog/201912/04/522a35f1ecf2c39e5c6467b83c0b48e6.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 2]: https://s1.51cto.com/images/blog/201912/04/03fef5282b49d119f4d626548f8efff5.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 3]: https://s1.51cto.com/images/blog/201912/04/2b34ba1060ea8b0357861ffe119f57b3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 4]: https://s1.51cto.com/images/blog/201912/04/219e4ca9191d071fe8a9452b2cc0aa56.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 5]: https://s1.51cto.com/images/blog/201912/04/2d79e80b7026afe475840d371cab5a60.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 6]: https://s1.51cto.com/images/blog/201912/04/a225badeddd13aa51452208c6b9108e4.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 7]: https://s1.51cto.com/images/blog/201912/04/d147f1a988cc5abeb6e3f538a2ad2dc3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 8]: https://s1.51cto.com/images/blog/201912/04/d54f8a5de86448741aa3af9b70b35dcf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 9]: https://s1.51cto.com/images/blog/201912/04/d4cfa542ef9c3d30db7a5f84c9bdd447.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 10]: https://s1.51cto.com/images/blog/201912/04/85dfff56cd72eae63c7057c309edd306.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 11]: https://s1.51cto.com/images/blog/201912/04/c40ed7511894ff3f632dd1e961d083c6.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 12]: https://s1.51cto.com/images/blog/201912/04/2ee04d6d16592e0409348d8d2a4d9aa3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=