MySQL数据库基本操作三：表的完整性约束、表操作

笨小孩呢

已于 2023-04-08 16:16:50 修改

阅读量1.8k

点赞数 4

分类专栏： MySQL数据库文章标签：数据库 mysql

于 2023-04-03 23:22:15 首次发布

本文链接：https://blog.csdn.net/x13613850206/article/details/129939610

版权

MySQL数据库专栏收录该内容

4 篇文章

订阅专栏

MySQL ：表的完整性约束

MySQL表级完整性约束
表操作

MySQL表级完整性约束

为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供
了一种机制来检查数据库是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。

**作用：**用于保证数据的完整性和一致性

约束条件与数据类型的宽度一样，都是可选参数，MySQL中主要支持六种完整性约束，如表所示。

约束条件	约束描述
PRIMARY KEY （PK）	主键约束，可以唯一，不可以为空。主键= UNIQUE KEY （UK） + NOT NULL
NOT NULL	非空字段，不能为空，可以修改
UNIQUE KEY （UK）	唯一约束，标识该字段的值是唯一的，可以为空。一个表中可以有多个字段，就有多个UNIQUE KEY （UK）
AUTO_INCREMENT	自增，自动增加位数，标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT	默认值约束，为该字段设置默认值
FOREIGN KEY (FK)	外键约束，实现表与表之间的关联

约束从作用上可以分为两类：

（1）表级约束（对整张表进行约束）：可以约束表中任意一个或多个字段。与列定义的相互独立，不包含
在列表定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

（2）列级约束（对单个字段进行约束）：包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；

1、PRIMARY KEY （PK）（主键）

每张表里只能有一个主键，不能为空，而且唯一，主键保证记录的唯一性，主键自动为NOT NULL。

当一个字段是 UNIQUE KEY 又是 NOT NULL的时候，那么它被当做PRIMARY KEY主键。

使用主键约束可以快速查找表中的记录，就像人的身份证、学生的学号等等

语法：

添加主键约束分为两种：

第一种：  创建表，并指定约束
 1. create tabel 表名(字段 类型，字段 类型，primary key(字段));
 2. create tabel 表名(字段 类型 primary key，字段 类型);

示例：    
mysql> create table t1(hostname char(20),ip char(150),primary key(hostname));
mysql> create table t2(id int primary key,name char(5));


第二种：   表存在，添加约束
 1. alter table 表名 and primary key(hostname);
 2. alter table 表名 modify column 字段名 字段类型 新约束(primary key)

示例：
mysql> alter table t1 and primary key(hostname);
mysql> alter table t2 modify column hostname char(20) primary key;

删除主键：

alter table 表名  drop  primary key;

请添加图片描述

2、NOT NULL （非空字段）

非空约束（NOT NULL,缩写NK），规定一张表中指定的某个字段的值不能为空（NULL），设置了非空约束
的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）

null（可空）与 not null （不可空）介绍：

是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
字段是否有默认值，默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

语法：

添加NOT NULL（非空字段） 分为两种：

第一种：    创建表，并指定约束
 create table 表名(字段 类型 not null);

示例：
mysql> create table t1(id int not null);


第二种：   表存在，添加约束
 alter table 表名 modify column 字段名 字段类型 新约束(not null);

示例：
mysql> alter table t1 modify column id int not null;

删除 not null 约束(也是使用 alter table 命令)：

alter table 表名 modify column 字段名 字段类型 新约束(null);

示例：
mysql> alter table t1 modify column id int null;

3、UNIQUE KEY （UK）（唯一约束）

字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。

语法（与主键语法相同）：

添加唯一约束分为两种：

第一种：  创建表，并指定约束
 1. create tabel 表名(字段 类型，字段 类型，unique key(字段));
 2. create tabel 表名(字段 类型 unique key，字段 类型);

第二种：   表存在，添加约束
 1. alter table 表名 and unique key(hostname);
 2. alter table 表名 modify column 字段名 字段类型 新约束(unique key);

删除唯一约束：

alter table 表名  drop index 添加约束的字段;

4、AUTO_INCREMENT （自增）

自增--------自动编号，且必须与主键组合使用默认情况下，起始值为1，每次的增量为1。当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值

则会出现两种情况：

如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；
如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。

（每张表只能有一个字段为自曾）（自增只能和主键配合使用，成了key才可以自动增长）

语法：

添加自增：

第一种：  创建表，并指定约束
create tabel 表名(字段 类型 primary key auto_increment，字段 类型);
在创建表时，直接设置主键，并上设置自增。

第二种：   表存在，添加约束
alter table 表名 modify column 字段名 字段类型 新约束(auto_increment); 
# 在设定自增之前查看是否，已经设定过主键
# 若没有设定主键，则要一起设置 自增和主键

删除自增：

使用ALTER TABLE语句删除自增约束
alter table 表名 modify column 字段 类型;
# 直接使用 alter table 命令 在字段后不加 约束

5、DEFAULT （默认值约束）

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。

默认值约束可以跟枚举类型（enum）一起使用。

语法：

添加 默认值约束：

第一种：  创建表，并指定约束
create table 表名(字段 类型 ,字段 类型 not null default 自增所设置的字符/值);
# 在添加 默认值约束是，一般设置字段不为空

第二种：   表存在，添加约束
alter table 表名 modify column 字段名 字段类型 新约束(not null default 自增所设置的字符/值);

删除 默认值约束；

使用ALTER TABLE语句删除自增约束
alter table 表名 modify column 字段 类型;
# 直接使用 alter table 命令 在字段后不加 约束，
  和自增删除类似

表操作

1、添加字段、约束

添加新字段

alter table 表名 add 字段 类型;

示例：
mysql> alter table t3  add math int(10);-------添加的字段
mysql> alter table t3  add (chinese int(10),english int(10));------添加多个
字段,中间用逗号隔开。

把新添加的字段，放到某个字段后面

alter table 表名 add 添加的字段(和类型) after name;

示例：
mysql> alter table t9 add id char(10) after home;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t9;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | char(10)      | YES  |     | NULL    |       |
| hostname | char(10)      | NO   |     | NULL    |       |
| ip       | char(20)      | YES  |     | NULL    |       |
| math     | int(3)        | YES  |     | NULL    |       |
| chinese  | int(10)       | YES  |     | NULL    |       |
| english  | int(10)       | YES  |     | NULL    |       |
| home     | char(10)      | YES  |     | NULL    |       |
| id       | char(10)      | YES  |     | NULL    |       |
| sex      | enum('w','m') | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

把添加的字段放在第一个

alter table 表名 add 添加的字段(和类型) first;

示例：
mysql> alter table t9 add name char(10) first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t9;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | char(10)      | YES  |     | NULL    |       |
| hostname | char(10)      | NO   |     | NULL    |       |
| ip       | char(20)      | YES  |     | NULL    |       |
| math     | int(3)        | YES  |     | NULL    |       |

添加字段，并修改字段的位置

alter table 表名 add 添加的字段(和类型) after 字段名;       ## 创建新字段并把字段添加到
某个字段名之后

修改字段的约束

mysql> alter table 表名 and 约束类型(字段)    
 ## 给字段添加约束（只能添加（key类型）的月约束）

2、修改字段的名称、类型、约束、顺序

使用 change 命令

alter table 表名 change 旧字段名 新字段名 类型;    #使用 change 修改字段名

语法：
## 使用 change 命令，就相当于完全定义新字段
所以 change 命令的语法： 就相当于在change后跟，创建新字段的语法一致就可以

使用 modify 命令

modify 命令，不能修改字段名称

alter table 表名 modify 字段 类型；

示例：
mysql> alter table t3 modify maxs int(20) after math;    #修改类型并更换位置

3、删除字段

alter table 表名 drop 要删除的字段;

4、插入数据(添加纪录)

values 插入的数 ‘值’ 可以是数字，也可以是字符，但是插入字符，需要加引号（ “字符” ）。

添加一条记录

insert into 表名(字段1,字段2,字段3,...) values(值，值，值，...);  

示例：
mysql> insert into t3(id,name,sex,age) values(1,"tom","m",18);
Query OK, 1 row affected (0.00 sec)

插入多条记录

insert into 表名(字段1,字段2,字段3,...) values(值，值，值，...)，(值，值，值,...);

示例：
mysql> insert into t3(id,name,sex,age) values(2,"jack","m",19),(3,"xiaoli","f",20);
Query OK, 2 rows affected (0.34 sec)

用set添加记录

insert into 表名 set 字段=4,字段="zhangsan",字段="m",字段=21;

示例：
mysql> insert into t3 set id=4,name="zhangsan",sex="m",age=21;
Query OK, 1 row affected (0.00 sec)

更新记录

update 表名 set  修改的字段  where  给谁修改;

示例：
mysql> update t3 set id=6 where name="xiaoli";

删除记录

1.删除表的单条记录：
 delete from 表名 where 字段; 

示例：
mysql> delete from t3 where id=6;   #直接删除select命令查看时，id=6 这一行所有的字段
Query OK, 1 row affected (0.35 sec)

2.删除表的所有记录
delete from 表名;