cen's blog
cen
2025-03-05
目录

表的约束

# 空属性

  • 空属性有两个值，分别是null和not null。
  • 数据库默认字段基本都是允许为空的，但在实际开发中我们要尽可能保证字段不为空，因为空值无法参与运算

如果要让某个字段不允许为空，在创建表的时候就可以给对应字段设置not null属性：

mysql> create table if not exists class (
    -> class_name varchar(10) not null,
    -> class_room varchar(10) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc class;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(10) | NO   |     | NULL    |       |
| class_room | varchar(10) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into class values('1班','1楼');
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+------------+------------+
| class_name | class_room |
+------------+------------+
| 1班        | 1楼        |
+------------+------------+
2 rows in set (0.00 sec)

mysql> insert into class values('1班');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 默认值

  • 如果某一个字段会经常性的出现某个值，那么就可以考虑将这个值设置成该字段的默认值。
  • 向表中插入数据时如果不给带有默认值的字段赋值，那么就会使用默认值进行插入
mysql> create table if not exists user ( name varchar(20) not null, age tinyint unsigned not null default 0, gender char(1) default '男' );
Query OK, 0 rows affected (0.02 sec)

mysql> desc user;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| name   | varchar(20)      | NO   |     | NULL    |       |
| age    | tinyint unsigned | NO   |     | 0       |       |
| gender | char(1)          | YES  |     | 男      |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into user values('zhangsan', 22);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into user (name, age) values('zhangsan', 22);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----------+-----+--------+
| name     | age | gender |
+----------+-----+--------+
| zhangsan |  22 | 男     |
+----------+-----+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 列描述

列描述是在创建表的时候用来对各个字段进行描述的，列描述会根据表创建语句保存，一般是用来给程序员或DBA了解表的相关信息的，相当于一种注释。

mysql> create table if not exists stu ( 
    -> id varchar(20) not null comment 'ID', 
    -> name varchar(20) comment '姓名' 
    -> );
Query OK, 0 rows affected (0.03 sec)
1
2
3
4
5

# zerofill

数值类型后面的圆括号中的数字，代表的是显示宽度，对应数值类型设置zerofill属性后，如果数据的宽度小于设定的宽度则自动填充0。

zerofill属性是针对数字类型字段的，它表示该字段的值前面补0，直到字段长度为指定的值。

mysql> create table if not exists t1 (
    -> a int not null,
    -> b int not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

mysql> alter table t1 modify a int zerofill not null;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> insert into t1 values(200, 10);
Query OK, 1 row affected (0.01 sec)

mysql> select* from t1;
+------------+----+
| a          | b  |
+------------+----+
| 0000000200 | 10 |
+------------+----+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 主键

主键用来唯一的约束该字段里面的数据，表当中每条记录的主键不能重复也不能为空，并且一张表里面只能有一个主键，此外，主键所在的列通常是整数类型。

例如：每个用户的ID都不会重复，所以可以使用用户ID作为主键。

mysql> create table if not exists User (
    -> id int primary key,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into User values(1, 'lisi');
Query OK, 1 row affected (0.01 sec)

mysql> insert into User values(1, 'wangwu');
ERROR 1062 (23000): Duplicate entry '1' for key 'User.PRIMARY'
mysql> insert into User values(2, 'wangwu');
Query OK, 1 row affected (0.00 sec)

mysql> desc User;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from User;
+----+--------+
| id | name   |
+----+--------+
|  1 | lisi   |
|  2 | wangwu |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from User where id=2;
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)

mysql> alter table User drop primary key;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc User;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table User add primary key(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc User;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
  • 删除主键：alter table 表名 drop primary key
  • 增加主键：alter table 表名 add primary key(列名)

复合主键

一张表里面只能有一个主键，但一个主键可以由多个字段来承担，这种主键叫做复合主键。

mysql> create table if not exists process (
    -> ip varchar(20),
    -> port int,
    -> info varchar(20),
    -> primary key(ip, port)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into process values('127.0.0.1', 8080,'无');
Query OK, 1 row affected (0.01 sec)

mysql> insert into process values('127.0.0.1', 8080,'测试');
ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'process.PRIMARY'
1
2
3
4
5
6
7
8
9
10
11
12
13

# 自增长

  1. 设置了自增长属性的字段，插入数据时如果不给该字段值，那么系统会自动找出当前字段当中已有的最大值，将最大值进行加一后的值插入该字段。
  2. 任何一个字段要做自增长，前提是其本身必须是一个索引（Key一栏有值），并且自增长字段必须是数值类型，一张表最多只能有一个自增长字段。
  3. 自增长通常和主键搭配使用，作为逻辑主键。一般而言，建议将主键设计成与当前业务无关的字段，避免因为业务逻辑的调整而需要修改主键。
mysql> create table if not exists auto_increment_table (
    -> id int primary key auto_increment,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into auto_increment_table (name) values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into auto_increment_table (name) values('b');
Query OK, 1 row affected (0.01 sec)

mysql> insert into auto_increment_table (name) values('c');
Query OK, 1 row affected (0.00 sec)

mysql> select* from auto_increment_table;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 唯一键

  1. 一张表中往往有很多字段需要唯一性，但一张表中只能有一个主键，而唯一键就可以解决表中有多个字段需要唯一性约束的问题（互相补充）。
  2. 唯一键和主键都能保证字段中数据的唯一性，但唯一键允许字段为空，并且可以多个字段为空，空字段不做唯一性比较。需要注意的是，不是主键具有唯一性，而是某个具有唯一性的字段被选择成为了主键，而那些不是主键但是同样需要唯一性约束的字段就应该设置成唯一键。
Database changed
mysql> create table if not exists Info (
    -> id int primary key,
    -> telephone char(11) unique key
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into Info values(1, '1234567899');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Info values(2, '1234567899');
ERROR 1062 (23000): Duplicate entry '1234567899' for key 'Info.telephone'
mysql> insert into Info values(2, '1234567229');
Query OK, 1 row affected (0.00 sec)

mysql> desc Info;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| id        | int      | NO   | PRI | NULL    |       |
| telephone | char(11) | YES  | UNI | NULL    |       |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select* from Info;
+----+------------+
| id | telephone  |
+----+------------+
|  2 | 1234567229 |
|  1 | 1234567899 |
+----+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

由上面例子可见：telephone被设置为唯一键，所以插入了重复的数据报错了。

# 外键

语法：foreign key (外键列名) references 主表名(主键列名)

外键是数据库中一个重要的概念，它允许一个表中的数据与另一个表中的数据建立关系，从而实现数据的完整性和一致性。

  • 外键用来定义主表和从表之间的关系，外键约束主要定义在从表上，主表必须有主键约束或唯一键约束。
  • 外键定义后，要求插入外键列的数据必须在主表对应的列存在或为null。

例如：我们创建学生表，包含一些信息：id，class_id，其中class_id是班级id，这个id是班级表里面的主键；班级表里面有id，name等等，其中class_id要和这个id保持一致，此时就要使用外键。

mysql> create table if not exists class (
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table if not exists student (
    -> id int primary key,
    -> name varchar(10),
    -> class_id int,
    -> foreign key(class_id) references class(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into class values(1, '软工1班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into class values(2, '软工2班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(3, '软工3班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(1, 'zahngsan', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(2, 'lisi', 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(3, 'wangwu', 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(4, 'zhaoliu', 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 综合案例

有一个商店的数据，记录客户及购物情况，由以下三个表组成：

  • 商品表goods：商品编号goods_id，商品名称goods_name，单价unitprice，商品类别category，供应商provider。
  • 客户表customer：客户编号customer_id，姓名name，住址address，邮箱email，性别sex，身份证card_id。
  • 购买表purchase：订单号order_id，客户编号customer_id，商品编号goods_id，购买数量nums。

要求：

  • 设置主键、外键
  • 客户的姓名不能为空值
  • 邮箱不能重复
  • 客户的性别（男，女）
上次更新: 2025/03/14, 11:20:46
MySQL数据类型
表的增删查改

← 表的增删查改→

最近更新
01
线程安全
05-21
02
cmake教程
05-08
03
项目
05-07
更多文章>
Theme by Vdoing | Copyright © 2024-2025 京ICP备2020044002号-3 京公网安备11010502056119号