MySQL数据库学习日志（三）：约束和策略

最新推荐文章于 2022-12-03 17:15:20 发布
最新推荐文章于 2022-12-03 17:15:20 发布
阅读量1k 收藏 11
点赞数 4
分类专栏： 数据库 文章标签： sql mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_52344565/article/details/123785609
版权

MySQL数据库学习日志（三）：约束和策略

约束和策略

约束的作用：对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。约束都是加在列上的，对一列的数据进行限制。

分类：数据库约束主要包括：主键约束、非空约束、唯一约束、默认约束(缺省约束)、外键约束、检查约束(MySQL不支持检查约束)

（一）主键约束

1. 主键的作用

用来唯一标识一条记录，每个表都应该有一个主键，并且每个表只能有一个主键。有些记录的 name,age,score 字段的值都一样时，那么就没法区分这些数据，造成数据库的记录不唯一，这样就不方便管理数据。
在这里插入图片描述

哪个字段应该作为表的主键？

通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

2. 主键的特点

主键列的特点：非空、唯一。

添加了主键约束的列，列中的值必须是非空（不能是null）和唯一的（各个行的数据都不相同）

3. 创建主键

（1）建表时添加主键

方式一

示例

create table stu1(
  id int primary key,
  name varchar(20)
)

方式二

示例

create table stu1(
  id int ,
  name varchar(20),
  primary key(id)
)

（2）修改表方式添加主键

示例

create table stu1(
  id int ,
  name varchar(20),
)

alter table stu1 add CONSTRAINT stu1_pk primary key(id) 
# stu1_pk 约束名，可有可无

（3）Navicat中添加主键

建表时，在要作为主键的列处单击

在这里插入图片描述

4. 删除主键

语法

ALTER TABLE 表名 DROP PRIMARY KEY;

示例

# 删除stu1表的主键
ALTER TABLE stu1 DROP PRIMARY KEY;

5. 主键自增策略

主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值。可以在主键列上添加自增策略。

（1）建表时添加主键自增策略

语法

列名 数据类型  primary key auto_increment 
自动增长字段类型必须是整数类型

示例

/*创建学生表stu2, 包含字段(id, name, age)。将id做为主键并自动增长*/
CREATE TABLE stu2 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT
    );
-- 插入数据
-- 主键默认从1开始自动增长
INSERT INTO stu2(NAME, age) VALUES ('唐僧', 22);
INSERT INTO stu2(NAME, age) VALUES ('孙悟空', 26);
INSERT INTO stu2(NAME, age) VALUES ('猪八戒', 25);
INSERT INTO stu2(NAME, age) VALUES ('沙僧', 20);

在这里插入图片描述

（2）修改表方式添加自增策略

语法

alter table 表名modify 主键列列名 数据类型 auto_increment

示例

CREATE TABLE stu2 (
	 id INT PRIMARY KEY,
	 NAME VARCHAR(20),
	 age INT
    );
alter table stu2 modify id int auto_increment;

（3）Navicat中添加自增策略

在这里插入图片描述

（4）扩展

默认地AUTOINCREMENT 的开始值是1，如果希望修改起始值，请使用下列SQL

语法：

ALTER TABLE 表名 AUTOINCREMENT=起始值;

6. truncate截断表

truncate表示截断表，会删除表中的全部数据，相当于删除表后重新创建该表。

语法

truncate table 表名

示例

truncate table stu2;

在这里插入图片描述

DELETE和TRUNCATE对于主键自增策略的区别

示例

1.DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值。

在这里插入图片描述

delete from stu2;

在这里插入图片描述

# 重新添加数据
INSERT INTO stu2(NAME, age) VALUES ('唐僧', 22);
INSERT INTO stu2(NAME, age) VALUES ('孙悟空', 26);
INSERT INTO stu2(NAME, age) VALUES ('猪八戒', 25);
INSERT INTO stu2(NAME, age) VALUES ('沙僧', 20);

在这里插入图片描述

2.TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为1

示例

在这里插入图片描述

truncate table stu2;

在这里插入图片描述

# 重新添加数据
INSERT INTO stu2(NAME, age) VALUES ('唐僧', 22);
INSERT INTO stu2(NAME, age) VALUES ('孙悟空', 26);
INSERT INTO stu2(NAME, age) VALUES ('猪八戒', 25);
INSERT INTO stu2(NAME, age) VALUES ('沙僧', 20);

在这里插入图片描述

（二）非空约束

添加非空约束的字段表示这个字段的每一行的值都不能为null值。

（1）建表时添加非空约束

语法

字段名 字段类型 NOT NULL

示例

# 创建表学生表stu3，包含字段(id,name,gender) ，其中name不能为NULL
CREATE TABLE stu3 (
	  id int,
	  name varchar(20) NOT NULL,
	  gender char(1)
    );

INSERT INTO st8 VALUES (1, '张三', '男');
INSERT INTO st8 VALUES (2, '李四', '男');
INSERT INTO st8 VALUES (3, '王五', '男');
INSERT INTO st8 VALUES (4, '赵六', '男');

-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

（2）修改表的方式添加非空约束

语法

alter table 表名 modify 列名 数据类型 not null

示例

CREATE TABLE stu3 (
	 id int,
	 name varchar(20) NOT NULL,
	 gender char(1)
  );

alter table stu3 modify name varchar(20) not null;

（三）唯一约束

添加了唯一约束的列的值不能重复，但是可以为null。

1. 创建唯一约束

（1）建表时添加唯一约束

语法

列名 数据类型 UNIQUE

示例

创建stu4表，包含字段(id, name,email) ，email这一列设置唯一约束，不能重复。
CREATE TABLE stu4 (
	id INT primary key,
	NAME VARCHAR(20),
email varchar(200) unique
)
insert into stu4 values(1,'张三','zs@aaa.com');
insert into stu4 values(2,'李四','ls@aaa.com');
insert into stu4 values(3,'王五','ww@aaa.com');

-- Duplicate entry 'zs@aaa.com' for key 'stu4.email'  email 重复
insert into stu4 values(4,'赵六','zs@aaa.com');

（2）修改表方式添加唯一约束

语法

alter table 表名 add constraint [约束名] unique(字段名)

示例

create table stu4(
   id int primary key auto_increment,
   name varchar(20),
   email varchar(200)
)
alter table stu4 add constraint stu4_unq unique(email)   #stu4_unq是约束名，可以省略

（3）Navicat中添加唯一约束

在这里插入图片描述

2. 删除唯一约束

语法

alter table 表名 drop index 唯一约束的约束名

示例

alter table stu4 drop index stu4_unq

（四）缺省（默认）约束

缺省约束是指指定某个列的缺省值，当该列在插入数据时不指定值，将会使用缺省值。

1. 创建缺省约束

（1）建表时添加缺省约束

语法

列名 数据类型 default ‘缺省值’

示例

CREATE TABLE stu5 (
	id INT primary key,
	NAME VARCHAR(20),
  address varchar(200) default '北京'
)
insert into stu5(id,name) values(1,'张三');
insert into stu5(id,name,address) values(2,'李四','上海');
select * from stu5;

在这里插入图片描述

（2）修改表的方式添加缺省约束

语法

alter table 表名 change column 列名 列名 数据类型 default ‘缺省值’

示例

CREATE TABLE stu5 (
	id INT primary key,
	NAME VARCHAR(20),
  address varchar(200)
)
alter table stu5 change column address address varchar(200) default '北京';

（3）Navicat中添加缺省约束

在这里插入图片描述

2. 删除缺省约束

语法

alter table 表名 change column 列名 列名 数据类型 default null

示例

alter table stu5 change column address adress varchar(200) default null

（五）外键约束

1. 单表的缺点

创建一个员工表，包含如下列(id, name, age, depname, deplocation) ，id主键并自动增长，添加6条数据

CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);

-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

缺点:表中出现了很多重复的数据(数据冗余)，如果要修改研发部的地址需要修改3个地方。

在这里插入图片描述

2. 解决方案

将一张表分成2张表(员工表和部门表)

-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 创建员工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);

-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

在这里插入图片描述

问题： 当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id

在这里插入图片描述

目标：需要约束dep_id只能是department表中已经存在id

解决方式：使用外键约束

3. 什么是外键约束

一张表中的某个字段引用另一个表的主键。

主表： 约束别人的表

副表/从表： 使用别人的数据，被约束的表

在这里插入图片描述

4. 创建外键

（1）建表时添加外键约束

语法

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释： 
CONSTRAINT：约束关键字 
FOREIGN KEY(外键字段名)：某个字段作为外键 
REFERENCES  主表名(主键字段名)：表示参照主表中的某个字段
要求：外键列的数据类型和长度必须和被参照的主键列的数据类型和长度一致

示例

-- 在创建副表/从表之前要先创建主表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
    );
-- 副表/从表: 被别的表约束，表结构添加外键约束
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 添加一个外键
	-- 外键取名公司要求,一般fk结尾
	CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);
-- 正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);
-- 部门错误的数据添加失败
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);

（2）修改表的方式添加外键

语法

alter table 从表名 add constraint [约束名] foreign key(外键列) reference 主表(主表主键列)

示例

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
	);
alter table employee add constraint emp_fk1 foreign key(dept_id) reference department(id)

5. 外键的级联

（1）问题

要把部门表中的id值2，改成5，能不能直接修改呢？

UPDATE department SET id=5 WHERE id=2;

-- 不能直接修改：Cannot delete or update a parent row: a foreign key constraint fails

如果副表(员工表)中有引用的数据，不能直接修改主表(部门表)主键。

要删除部门id等于1的部门，能不能直接删除呢？

DELETE FROM department  WHERE id=1;

-- 不能直接删除：Cannot delete or update a parent row: a foreign key constraint fails

如果副表(员工表)中有引用的数据，不能直接删除主表(部门表)数据。

（2）级联操作

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。

语法

ON UPDATE CASCADE -- 级联更新，主键发生更新时，外键也会更新 
ON DELETE CASCADE -- 级联删除，主键发生删除时，外键也会删除

示例

-- 删除employee表
-- 重新创建employee表，添加级联更新和级联删除
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,
	-- 添加外键约束,并且添加级联更新和级联删除(还可以直接在navicat中直接设置)
	CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);
-- 再次添加数据到员工表和部门表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

把部门表中id等于1的部门改成id等于10

UPDATE department SET id=10 WHERE id=1;

在这里插入图片描述

删除部门号是2的部门

DELETE FROM department WHERE id=2;

在这里插入图片描述

注意：要慎用级联操作

end

好久不写了呜呜，数据库也快结课了
最近把数据库基础基本完结一下。奥里给！！！！
在这里插入图片描述

程序猿小张的日常笔记
关注
专栏目录
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL
dvlinker的技术专栏
10-08 4万+
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL。
MySQL数据库学习日志（六）：索引和视图
张小憨憨的博客
04-05 2221
MySQL数据库学习日志（六）：索引和视图 MySQL数据库学习日志（六）：索引和视图索引（一）索引概述（二）优缺点（三）分类1. 按照底层实现的方式2. 按照功能划分普通索引唯一索引组合索引（四）创建索引的条件（五）索引使用注意事项（六）索引的内部原理相关算法介绍谈谈你对b树和b+树的理解：视图（一）视图概述（二）视图的作用（三）视图的基本操作1. 创建视图2. 修改视图3. 更新视图3. 其它操作end 索引 腾讯云数据库负责人林晓斌说过：“我们面试 MySQL 同事时只考察两点，索引和锁”。 （一）
day24_SQL语言（四）约束和策略
qq_36972345的博客
08-07 718
约束的作用：对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。约束都是加在列上的，对一列的数据进行限制。 分类：数据库约束主要包括：主键约束、非空约束、唯一约束、默认约束(缺省约束)、外键约束、检查约束(MySQL不支持检查约束) 一、主键约束 1.主键的作用 用来唯一标识一条记录，每个表都应该有一个主键，并且每个表只能有一个主键。 有些记录的 name,age,score 字段的值都一样时，那么就没法区分这些数据，造成数据库的记录不唯一，这样就不方便管理数据。 哪个字.
SQLServer技术(57)
zgqtxwd的专栏
04-26 641
<!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--><script type="text/javascript"
MySQL基础练习-题目
xlxlxl的博客
10-01 1604
mysql基础
实操-数据库和数据表的创建与管理
chensuyin_gz的博客
03-30 2782
数据库和数据表的创建与管理 1.数据库的创建、修改、删除 1.在MySQL中创建一个名为db_school的数据库，要求设置默认字符集为GB2312，字符集的校对规则为gb2312_chinese_ci。 create database db_school default character set GB2312 collate gb2312_chinese_ci; 2.查看当前用户可查看的数据库列表。 show databases; 3.修改已有数据库db_school的默认字符集和校对规则分别为G
MySQL快速上手
qq_44776943的博客
02-18 268
MySQL快速上手 1、MySQL 1.1、为什么学数据库 岗位需求 现在的世界，大数据时代~ 得数据者得天下！ 被迫需求，存数据！ 数据库是所有软件体系中最核心的存在！ 1.2、什么是数据库 数据库（DB：DataBase） 概念：数据仓库，软件，安装在操作系统（win，linux，mac）之上！SQL，可以存储大量的数据！500万！ 作用：存数据，管理数据！ 1.3、数据库分类 关系型数据...
MySQL数据库培训手册附：国产三大数据库.doc
10-11
MySQL数据库培训手册主要介绍了MySQL的基础配置和管理，包括数据库路径、配置文件的位置、常用命令以及配置文件`my.cnf`的详细设置。手册也提到了MySQL与其他数据库类型的对比，特别是InnoDB和MyISAM的区别。 1. **...
Java学习日志(二十七): MySQL基础，SQL语句操作数据库和表
12-14
本篇日志主要介绍了MySQL的基础知识，包括如何启动和关闭MySQL服务器、登录数据库以及使用SQL语句来操作数据库和表。 首先，数据库是存储数据的系统，而SQL（Structured Query Language）是用于与数据库进行交互的...
有关MySQL主键及主键自增
qq_47627751的博客
06-02 332
此时我们在数据库joiner中建立名为data的表： //引入数据库 use joiner; //创建表data create table data( id int, name varchar(20), age int, major varchar(20), score int, //将id设为主键 primary key(id) //或是直接在id int 后直接写primary key; ); 则查看表时可得到以下结果（此时可看到id被设为了主键）： 此时
Mysql字段设置默认值失效问题
最新发布
人的一生就是在学习的道路上不断进步
12-03 5972
Mysql字段设置默认值失效问题
mysql 缺省date值_关于MySQL中date类型缺省值
weixin_30086041的博客
01-19 2714
正确的语句：alter table test_table modify column create_time_2 date default '2001-01-01';控制台打印信息如下：[SQL]alter table test_table modify column create_time_2 date default '2001-01-01';受影响的行: 0时间: 0.015s错误的语句：A...
mysql 外键相关
程序人生
05-21 4536
约束（Constraint）是Microsoft SQL Server 提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件（有关数据完整性的介绍请参见第9 章）。在SQL Server 中有5 种约束：主关键字约束（Primary Key Constraint）、外关键字约束（Foreign Key Constraint）、惟一性约束（Unique Constrai
Sql获取表结构的描述与缺省值
编程开发资料库
07-26 406
--获取数据库中的所有表 SELECT name FROM sysobjects WHERE type = 'U' --获取SqlServer中表结构 主键，及描述declare @table_name as varchar(max)set @table_name = 'tableName' --表名自己改select sys.columns.name, sys.types.name, sys.c...
MYSQL添加约束的两种方法
热门推荐
keep
10-05 3万+
MYSQL添加约束 第一种:创建表的时候 create table table_name( 列名1 数据类型 (int) primary key auto_increment, 列名2 数据类型 not null, 列名3 数据类型 unique, 列名4 数据类型 default '值', constraint 索引名 foreign key(外键列) references 主键表...
SQL Server缺省约束、列约束和表约束
baitu5537的博客
06-03 3498
SQL Server缺省约束是SQL Server数据库中的一种约束，下面就为您介绍SQL Server缺省约束、列约束和表约束的定义方法啊，供您参考。 SQL Server缺省约束 SQL Server缺省约束通过定义列的缺省值或使用数据库的缺省值对象绑定表的列，来指定列的缺省值。SQL Server 推荐使用缺省约束，而不使用定义缺省值的方式来指定列的缺省值。 定义SQL Se...
mysql 缺省配置 崩溃_MySQL_MySQL5.6基本优化配置，随着 大量默认选项的改进, MySQ...
weixin_36393352的博客
01-19 116
随着 大量默认选项的改进, MySQL 5.6比以前版本需要调优的选项大为减少. 在本文中我将讲述需要优化的配置项.InnoDB设置1.innodb_buffer_pool_size —— 默认值为 128M. 这是最主要的优化选项,因为它指定 InnoDB 使用多少内存来加载数据和索引(data+indexes). 针对专用MySQL服务器,建议指定为物理内存的 50-80%这个范围. 例如,...
MySQL数据库优化详解：索引、执行计划与优化策略
本文主要针对MySQL数据库进行优化，涵盖了多种技术和策略。 一、MySQL优化概述 数据库优化包括从设计阶段就开始考虑的优化策略，如选择合适的存储引擎、字段类型以及遵循数据建模的范式原则。MySQL提供了两种主要的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序猿小张的日常笔记

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值