4.20数据库课堂笔记

小fufufufufufu

已于 2023-04-27 16:33:35 修改

阅读量40

点赞数

文章标签：数据库笔记 sql

于 2023-04-20 17:56:10 首次发布

本文链接：https://blog.csdn.net/qq_53871862/article/details/130270517

版权

# 约束

## 准备

```sql
create database bigdata02;
```

```sql
use bigdata02;
```

```sql
create table student(
   student_id int unsigned comment '学生ID',
   student_name varchar(50) comment '学生名',
   student_sex char(2) comment '学生性别'
);
```

```sql
insert into student(student_id, student_name)
   values
   (1, '笨笨'),
   (2, '静静');
```

```sql
select * from student;
```

```
+------------+--------------+-------------+
| student_id | student_name | student_sex |
+------------+--------------+-------------+
| 1 | 笨笨 | NULL |
| 2 | 静静 | NULL |
+------------+--------------+-------------+
```

性别: 男女保密

## 默认值（default）

当字段未被赋值时，自动填充默认值

```sql
create table student01(
   student_id int unsigned comment '学生ID',
   student_name varchar(50) comment '学生名',
   student_sex char(2) default '保密' comment '学生性别'
);
```

```sql
insert into student01(student_id, student_name)
   values
   (1, '笨笨'),
   (2, '静静');
```

```sql
insert into student01(student_id)
   values
   (3),
   (4);
```

```sql
select * from student01;
```

```
+------------+--------------+-------------+
| student_id | student_name | student_sex |
+------------+--------------+-------------+
| 1 | 笨笨 | 保密 |
| 2 | 静静 | 保密 |
| 3 | NULL | 保密 |
| 4 | NULL | 保密 |
+------------+--------------+-------------+
```

## 非空（not null）

如果一个字段被设置为非空，插入数据时，必须对字段赋值

```sql
create table student02(
   student_id int unsigned not null comment '学生ID',
   student_name varchar(50) not null comment '学生名',
   student_sex char(2) default '保密' comment '学生性别'
);
```

错误例子

```sql
insert into student02(student_id)
   values
   (3),
   (4);
```

```
ERROR 1364 (HY000): Field 'student_name' doesn't have a default value
```

正确写法

```sql
insert into student02(student_id, student_name)
   values
   (1, '笨笨'),
   (2, '静静');
insert into student02(student_id, student_name)
   values
   (3, '困困'),
   (4, 'ikun');
```

## 唯一(unique)

设置了唯一约束的字段，值不允许重复的

```sql
create table student03(
   student_id int unsigned not null unique comment '学生ID',
   student_name varchar(50) not null comment '学生名',
   student_sex char(2) default '保密' comment '学生性别'
);
```

```sql
insert into student03(student_id, student_name)
   values
   (1, '笨笨'),
   (2, '静静');
```

错误的SQL

```sql
insert into student03(student_id, student_name)
   values
   (1, '笨笨'),
   (2, '静静');
```

```
ERROR 1062 (23000): Duplicate entry '1' for key 'student_id'
```

正确的SQL

```sql
insert into student03(student_id, student_name)
   values
   (3, '笨笨'),
   (4, '静静');
```

```sql
select * from student03;
```

```
+------------+--------------+-------------+
| student_id | student_name | student_sex |
+------------+--------------+-------------+
| 1 | 笨笨 | 保密 |
| 2 | 静静 | 保密 |
| 3 | 笨笨 | 保密 |
| 4 | 静静 | 保密 |
+------------+--------------+-------------+
```

## 自增 (auto_increment)

auto_increment 和 unique 配合使用

```sql
create table student04(
   student_id int unsigned auto_increment not null unique comment '学生ID',
   student_name varchar(50) not null comment '学生名',
   student_sex char(2) default '保密' comment '学生性别'
);
```

```sql
insert into student04(student_name)
   values
   ('笨笨'),
   ('静静'),
   ('困困'),
   ('ikun');
insert into student04(student_name)
   values
   ('笨笨'),
   ('静静'),
   ('困困'),
   ('ikun');
```

```sql
select * from student04;
```

```
+------------+--------------+-------------+
| student_id | student_name | student_sex |
+------------+--------------+-------------+
| 1 | 笨笨 | 保密 |
| 2 | 静静 | 保密 |
| 3 | 困困 | 保密 |
| 4 | ikun | 保密 |
| 5 | 笨笨 | 保密 |
| 6 | 静静 | 保密 |
| 7 | 困困 | 保密 |
| 8 | ikun | 保密 |
+------------+--------------+-------------+
```

添加数据省略字段名时，自增的字段使用 null 占位

```sql
insert into student04 values (null, '甜甜', '女');
```

```sql
select * from student04
```