约束
基本概念:
约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确、有效性和完整性。
约束是为了保证进入数据库的数据都是有效的、可靠的,会对列的值进行一些约束,
确保存进去的数据都是有效的。
约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
查看约束:show create table students(表名);
主键约束(PK)
主键约束最显著的特征是主键列中的值是不允许重复的,通过主键约束可强制表的实
体完整性。当创建或更改表时可通过定义 PRIMARY KEY
约束来创建主键。一个表只
能有一个 PRIMARY KEY
约束,且 PRIMARY KEY
约束中的列不能接受NULL值。
alter table table_name add constranit pk_name primary key(字段名);
# 设置该字段为主键,主键约束名称为pk_name
-
设置主键的几种方式
-- 1.创建表的时候指定主键约束 create table user( id int primary key, name varchar(10) ); create table user( id int, name varchar(10), primary key (id) ); create table user( id int, name varchar(10), constranit pk_name primary key (id) ); -- 2.修改某一列为主键 alter table 表名 add primary key(id); alter table 表名 modify 字段 类型 primary key(id); alter table 表名 change 字段名 字段名 类型 primary key(id);
-
删除主键
alter table 表名 drop primary key; [mysql 8.0.22 之后的版本] alter table 表名 drop index primary key; [mysql 8.0.22 之前的版本]
自增长列(标识列)
并不是所有表在设计完成后都能找到适合作为主键的列,为此数据库提供了自增长
列,自增长列是int类型的,其值是由数据库自动维护的,是永远都不会重复的,因此
自增长是最适合作为主键列的。在创建表时,通过 AUTO_INCREMENT
关键字来标识自
增长列,在MySQL数据库中自增长列必须是主键列。
CREATE TABLE goods(
no INT PRIMARY KEY AUTO_INCREMENT, -- 直接设置自增长
name VARCHAR(10)
)
alter table 表名 modify 字段名 类型 auto_increment; -- 修改为自增长列
alter table 表名 change 字段名 字段名 类型 auto_increment;
-- 删除自增长列
alter table 表名 modify 字段名 类型;
联合主键
联合主键(Composite Primary Key)是指在数据库表中,由多个列共同组成的主键,
用来唯一标识表中的每一行数据。它的作用类似于单一列的主键,但不是由单个列组
成,而是由多个列组合而成。联合主键可以确保表中的每一行都具有唯一性,并且每
个列组合的值都不会重复。
在创建表时,可以在列定义中指定多个列作为联合主键。
CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
PRIMARY KEY (列名1, 列名2, 列名3)
);
-- 修改列的时候创建
alter table 表名 add primary key (字段1,字段2,字段3);
-- 删除
alter table 表名 drop primary key;
联合主键在以下情况非常有用:
- 当单个列无法唯一标识表中的每一行,但多个列组合在一起可以唯一标识每一行数据时。
- 提高查询性能:联合主键可以更有效地支持涉及多个列的查询,避免创建额外的索引。
- 在具有多个外键的关联表中,可以使用联合主键来确保外键引用的准确性。
- 联合主键要求每个列组合的值都是唯一的
唯一约束
对于非主键列中的值也要求唯一性时,就需要唯一约束
-- 创建表时
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20) UNIQUE # 唯一约束
);
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20),
CONSTRAINT uq UNIQUE(name) #唯一约束
);
-- 修改表时
alter table 表名 add unique(字段);
alter table 表名 change 字段名 字段名 类型 unique;
alter table 表名 modify 字段名 类型 unique;
-- 删除时
alter table 表名 drop index 设置唯一时的名称;-- 如果没有设置,通过 show create table 表名; 查看
默认约束
为列中的值设置默认值, DEFAULT value
-- 创建表时
CREATE TABLE `table_name` (
`id` int DEFAULT value,
`name` varchar(20) unique,
);
-- 修改表时
alter table 表名 modify 字段名 类型 default 默认值;
alter table 表名 change 字段名 字段名 类型 default 默认值;
-- 删除
alter table 表名 modify 字段名 类型 ;
ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT; 【8.0.23以上的版本】
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT NULL;【8.0.23以前的版本】
非空约束
NOT NULL :
非空,用于保证该字段的值不能为空
-- 创建表时
CREATE TABLE `table_name` (
`id` int NOT NULL, # 非空约束
`name` varchar(20),
);
-- 修改表时
alter table 表名 modify 字段名 类型 not null;
alter table 表名 change 字段名 字段名 类型 not null;
-- 删除
alter table 表名 modify 字段名 类型;
修改列的约束确保现有数据满足非空约束条件,否则可能导致操作失败。
检查约束
从 MySQL 8.0.19 版本开始, MySQL 支持了检查约束。检查约束允许你在表定义中声
明条件,并确保符合该条件的数据才能插入或更新到相应的列中。
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100),
CHECK (age >= 18) -- 添加检查约束:年龄必须大于等于18
);
-- 修改表的时候
alter table 表名 change 字段名 字段名 类型 check (约束条件);
alter table 表名 modify 字段名 类型 check (约束条件);
ALTER TABLE 表名 ADD CONSTRAINT 列名 CHECK(condition);
-- 删除时
ALTER TABLE 表名 DROP CONSTRAINT 约束名;-- show create table 表名; 可以查看
外键约束
用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
表中列的值来自于另外一张表的主键或唯一键的列称为外键 FK ,将被引用值的表称
为主表或父表,将引用值的表称为从表或子表。
-- 创建表时
create table user(
id int primary key,
name varchar(10) unique,
rid int,
constraint fk_a_b foreign key(rid) references b(id)
);
-- 修改时
alter table 从表表名 add constraint 约束名称 foreign key(从表字段) references (主表表名);
-- 删除
ALTER TABLE 表名称 DROP FOREIGN KEY 索引名;-- 设置外键时的名称
- 外键列类型需要与引用列类型一致
- 外键列的值必须是主表中引用列的值或者 NULL
- 一个表可以有多个外键列
- 从表列可以随便删除
- 删除主表数据时,会先检查从表中有没有对此数据的关联,如果有就不能直接删除
- 在 设 置 外 键 的 时 候 后 面 添 加 on delete cascade / on update cascade 在删除/更新主表时,级联删除/更新外键列的数据
- 在设置外键的时候后面添加 on delete set null / on update set null 在删除/更新主表时,外键列的值会变成null
聚合函数
MYSQL 中内置了 5 种聚合函数,分别是: SUM 、 MAX 、 MIN 、 AVG 、 COUNT 。
-
sun : 求和
select sum(列) from table_name [其他子句];
-
max : 求最大值
select max(列) from table_name [其他子句];
-
min : 求最小值
select min(列) from table_name [其他子句];
-
avg : 求平均值
select avg(列) from table_name [其他子句];
-
count : 求数量
select count(列) from table_name [其他子句];
group by
group by
是对数据进行分组,分组时,表中有相同值的分为一组。分组后可以进行
聚合查询。
group by
分组后的查询中, select 的列不能出现除了 group by 分组条件以及聚
合函数外的其他列。
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2; 1
having
having
是对 group by
分组后的结果集进行筛选。
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;