xiaoyh 的个人博客

一个只会敲代码的咸鱼

0%

SQL 进阶（4）

发表于 更新于 分类于

约束

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

NOT NULL

NOT NULL 约束强制字段不接受 NULL 值，即强制字段始终有值。

这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建
create table persons(
    id int NOT NULL,
    age int,
    username varchar(255) NOT NULL
);

-- 添加
alter persons modify age int NOT NULL;

-- 撤销
alter persons modify age int NULL;

UNIQUE

UNIQUE 约束强制字段不可出现重复值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建
create table example(
    age int UNIQUE,
    num int
);

-- 命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
create table example(
    age int,
    num int,
    constraint custom_name UNIQUE(age, num)
);

-- 添加
alter table example add UNIQUE(age);
alter table example add constraint custom_name UNIQUE(age, num);

-- 撤销
alter table example drop index custom_name;
alter table example drop constraint custom_name;

PRIMARY KEY

主键 PRIMARY KEY 约束的字段不可重复，不能包含 NULL（即具有 NOT NULL 与 UNIQUE 的特性）。

每个表都应该有一个主键，并且每个表只能有一个主键。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 创建
create table example(
    id int PRIMARY KEY,
    ...
);

-- 下面的实例中，只有一个主键（pk）。然而 pk 的值是由两个列（id 和 age）组成的
create table example(
    id int,
    age int,
    ...
    constraint pk PRIMARY KEY(id, age)
);

-- 添加
alter table example add PRIMARY KEY(id);
alter table example add constraint pk PRIMARY KEY(id, age);

-- 撤销
alter table example drop PRIMARY KEY;
alter table example drop constraint pk;

FOREIGN KEY

一个表中的外键 FOREIGN KEY 指向另一个表中的 UNIQUE KEY 或 PRIMARY KEY。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建
create table example(
    ...
    age int FOREIGN KEY REFERENCES persons(id)
);

create table example(
    ...
    p_id int,
    age int，
    constraint fk FOREIGN KEY(p_id, age) REFERENCES persons(id, age)
);

-- 添加
alter table example add FOREIGN KEY(age) REFERENCES persons(id);
alter table example add constraint fk FOREIGN KEY(p_id, age) REFERENCES persons(id, age);

-- 撤销
alter table example drop FOREIGN KEY;
alter table example drop constraint fk;

CHECK

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建
create table example(
    ...
    age int CHECK (age > 18),
);

create table example(
    ...
    id int,
    age int，
    constraint ck CHECK (age > 18 and id == 100)
);

-- 添加
alter table example add CHECK(age > 18);
alter table example add constraint ck CHECK (age > 18 and id == 100);

-- 撤销
alter table example drop CHECK ck;
alter table example drop constraint ck;

DEFAULT

DEFAULT 约束用于向列中插入默认值。如果再插入数据时对这一列没有规定值，那么会将默认值添加到这条新记录。

1
2
3
4
5
6
7
8
9
10
11
-- 创建
create table example(
    ...
    age int DEFAULT 18
);

-- 增加
alter table example alter age set DEFAULT 18;

-- 撤销
alter table example alter age drop DEFAULT;

AUTO INCREMENT

我们通常希望在每次插入新记录时，自动更新主键字段的值。

MySQL 通过 AUTO_INCREMENT 字段来实现自增的功能。

1
2
3
4
create table example(
    id int primary key AUTO_INCREMENT,
    ...
);

MySQL 默认 AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，得：

1
alter table example AUTO_INCREMENT=100;

在插入数据时，可以不必为自增字段设置值。

视图

在 SQL 中，视图是基于 SQL 语句结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建视图
create view view_name as
select col1, col2, ... from table_name;

-- 查询视图
select * from view_name;

-- 更新视图
create or replace view view_name as
select col1, col2, ... from table_name;

-- 撤销视图
drop view view_name;

视图的作用

  1. 视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
  2. 视图提供了一个统一访问数据的接口，即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限，从而加强了安全性，使用户只能看到视图所显示的数据。
  3. 视图还可以被嵌套，一个视图中可以嵌套另一个视图。