【mysql】约束（非空约束，唯一性约束，主键约束，外键约束），存储引擎

小小Yi学编程

已于 2022-10-28 17:32:01 修改

阅读量605

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 sql 开发语言

于 2022-10-28 17:27:54 首次发布

本文链接：https://blog.csdn.net/m0_69171095/article/details/127525761

版权

mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

文章目录

约束
存储引擎

约束

什么是约束

约束对应的英语单词: constraint
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性! ! !
约束的作用就是为了保证:表中的数据有效!!

约束包括哪些


非空约束	not null
唯一性约束	unique
主键约束	primary key ( 简称pK)
外键约束	foreign key (简称Fk)
检查约束	check ( mysql不支持，oracle支持 )

非空约束not null

约束的字段不能为NULL.

drop table if exists t_vip ;
create table t_vip (
id int,
name varchar (255) not null
);
insert into t_ vip (id, name) values (1, ' zhangsan') ;
insert into t_vip (id, name) values (2, 'lisi') ;

insert into t_vip(id) values (3) ;
ERROR 1364 (HY000) : Field 'name' doesn't have a default value

唯一性约束unique

约束的字段不能重复，但是可以为NULL.

drop table if exists t_vip; 
create table t_vip (
id int,
name varchar (255) unique ,
email varchar (255)
);
insert into t_ vip (id, name, email) values (1, ' zhangsan' , ' zhangsan@123. com') ; 
insert into t_ vip (id, name , email) values(2, 'lisi' , 'lisig123.com') ;
insert into t_ vip (id, name, email) values (3, 'wangwu' , 'wangwu@123.com') ;

insert into t_ vip (id, name, email) values (4, ' wangwu' , 'wangwuesina . com') ;
ERROR 1062 (23000) : Duplicate entry 'wangwu' for key ' name

新需求:
name和email两个字段联合起来具有唯一性! !
怎么创建这样的表，才能符合新需求呢?

drop table if exists t-vip;
create table t vip (
id int,
name varchar (255)，
email varchar (255) ,
unique (name , email)
);

unique和not null也可以联合

drop table if exists t vip;
create table t vip (
id int,
name varchar (255) not null unique
);

在mysql当中，如果一个字段同时被not null和unique约束的话,
该字段自动变成主键字段。(注意: oracle中不一样! )

主键约束(primary key, 简称PK)

主键约束的相关术语

主键约束:就是一种约束。
主键字段:该字段上添加了主键约束，这样的字段叫做主键字段
主键值:主键字段中的每一个值都叫做:主键值。

什么是主键

主键值是每一行记录的唯一标识。
主键值是每一行记录的身份证号!!!
记住:任何一张表都应该有主键，没有主键，表无效! !

主键的特征:not null+unique(主键值不能是NULL,同时也不能重复!)

主键的分类

根据主键字段的字段数量来划分：
- 单一主键（推荐的，常用的。）
- 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
- 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
- 业务主键：主键值和系统的业务挂钩，
  -例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

一张表的主键约束只能有1个。（必须记住）

怎么给一张表添加主键约束呢?

drop table if exists t_vip;
create table t_ vip (
id int primary key,
name varchar (255)
);
insert into t_vip (id, name) values (1, ' zhangsan') ;
insert into t_ vip(id, name) values(2, 'lisi') ;

//错误:不能重复
insert into t_ vip(id, name) values (2, 'wangwu') ;
ERROR 1062 (23000) : Duplicate entry '2' for key ' PRIMARY '

//错误:不能为NULL
insert into t_ vip (name) values (' zhaoliu') ;
ERROR 1364 (HY000) : Field 'id' doesn't have a default value

可以这样添加主键，使用表级约束

drop table if exists t_vip;
create table t_vip(
id int,
name varchar (255) ,
primary key (id)
);

一个表中主键约束能加两个吗?

drop table if exists t_vip;
create table t vip (
id int primary key,
name varchar (255) primary key
);
ERROR 1068 (42000) : Multiple primary key de fined
结论:一张表，主键约束只能添加1个。( 主键只能有1个。)

怎样添加复合主键（注：这仍然只是一个主键，只是这个主键的字段有两个）

		drop table if exists t_user;
		create table t_user(
			id int,
			username varchar(255),
			password varchar(255),
			primary key(id,username)
		);
		insert .......

主键值建议使用:
int
bigint
char
等类型。
不建议使用:varchar来做主键。主键值一般都是数字，一般都是定长的!

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值

drop table if exists t_vip;
create table t_vip (
id int primary key auto_ increment,
name varchar (255)
);
insert into t_ vip (name) values (' zhangsan') ;
insert into t_ vip (name) values ( ' zhangsan') ;
insert into t_ vip (name) values ( ' zhangsan') ;
insert into t_ vip (name) values (' zhangsan') ;
insert into t_ vip (name) values (' zhangsan') ;
insert into t_ vip (name) values ( ' zhangsan') ;
insert into t vip (name ) values (' zhangsan') ;
insert into t_ vip (name) values (' zhangsan') ;
select *from t_vip;
//当主键加上auto_increment后，会自动从自然数1开始自增

在这里插入图片描述

外键约束( foreign key,简称FK)

外键约束相关术语

外键约束:一种约束( foreign key)
外键字段:该字段上添加了外键约束
外键值:外键字段当中的每一个值.

业务背景:
请设计数据库表，来描述班级和学生的信息?

第一种方案:班级和学生存储在一张表中
-

数据冗余，空间浪费!

第二种方案:班级一张表、学生一张表
- 在这里插入图片描述

怎样给一张表添加外键

实现上面的业务，创建表

 t_student中的classno字段引用t_class表中的cno字段，
 t_student表叫做子表。
 t_class表叫做父表。

 顺序要求：
 	删除数据的时候，先删除子表，再删除父表。
 	添加数据的时候，先添加父表，在添加子表。
 	创建表的时候，先创建父表，再创建子表。
 	删除表的时候，先删除子表，在删除父表。
 drop table if exists t_class;
 drop table if exists t_student;
 create table t_class(
 	cno int,
 	cname varchar(255),
 	primary key(cno)
 );

 create table t_student(
 	sno int,
 	sname varchar(255),
 	classno int,
 	primary key(sno),
 	foreign key(classno) references t_class(cno)
 );

 insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
 insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');

 insert into t_student values(1,'zs1',101);
 insert into t_student values(2,'zs2',101);
 insert into t_student values(3,'zs3',102);
 insert into t_student values(4,'zs4',102);
 insert into t_student values(5,'zs5',102);
 insert into t_student values(6,'zs6',102);
 select * from t_class;
 select * from t_student;

 insert into t_student values(7,'lisi',103);
 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))


 当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个103,
 但是103班级不存在；所以为了保证cno字段中的值都是100和101,
 需要给cno字段添加外键约束。那么: cno字段就是外键字段。
 cno字段中的每一个值都是外键值.

思考:子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗?
不一定是主键，但至少具有unique约束。

外键可以为NULL吗?
外键值可以为NULL.

存储引擎

2.1、完整的建表语句

		CREATE TABLE t_x (
		  id int(11) DEFAULT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在MySQL当中，凡是标识符是可以使用''括起来的。最好别用，不通用。

建表的时候可以指定存储引擎，也可以指定字符集。

mysql默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF8

什么是存储引擎呢

	存储引擎这个名字只有在mysql中存在。
	（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，
	就是“表的存储方式”）

mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

查看当前mysql支持的存储引擎？

	show engines \G

mysql中的存储引擎

mysql 5.5.36版本支持的存储引擎有9个：

		*************************** 1. row ***************************
				Engine: FEDERATED
			  Support: NO
			  Comment: Federated MySQL storage engine
		Transactions: NULL
					 XA: NULL
		  Savepoints: NULL
		*************************** 2. row ***************************
				Engine: MRG_MYISAM
			  Support: YES
			  Comment: Collection of identical MyISAM tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 3. row ***************************
				Engine: MyISAM
			  Support: YES
			  Comment: MyISAM storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 4. row ***************************
				Engine: BLACKHOLE
			  Support: YES
			  Comment: /dev/null storage engine (anything you write to it disappears)
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 5. row ***************************
				Engine: CSV
			  Support: YES
			  Comment: CSV storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 6. row ***************************
				Engine: MEMORY
			  Support: YES
			  Comment: Hash based, stored in memory, useful for temporary tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 7. row ***************************
				Engine: ARCHIVE
			  Support: YES
			  Comment: Archive storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 8. row ***************************
				Engine: InnoDB
			  Support: DEFAULT
			  Comment: Supports transactions, row-level locking, and foreign keys
		Transactions: YES
					 XA: YES
		  Savepoints: YES
		*************************** 9. row ***************************
				Engine: PERFORMANCE_SCHEMA
			  Support: YES
			  Comment: Performance Schema
		Transactions: NO
					 XA: NO
		  Savepoints: NO

常见的存储引擎

		      Engine: MyISAM
			  Support: YES
			  Comment: MyISAM storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		
		MyISAM这种存储引擎不支持事务。
		MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
		MyISAM采用三个文件组织一张表：
			xxx.frm（存储格式的文件）
			xxx.MYD（存储表中数据的文件）
			xxx.MYI（存储表中索引的文件）
		优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
		缺点：不支持事务。

	-----------------------------------------------------------------------------

			  Engine: InnoDB
			  Support: DEFAULT
			  Comment: Supports transactions, row-level locking, and foreign keys
		Transactions: YES
					 XA: YES
		  Savepoints: YES
			
		优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
		
		表的结构存储在xxx.frm文件中
		数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
		这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
		InnoDB支持级联删除和级联更新。
	
	-------------------------------------------------------------------------------------

			  Engine: MEMORY
			  Support: YES
			  Comment: Hash based, stored in memory, useful for temporary tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		
		缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
		优点：查询速度最快。
		以前叫做HEPA引擎。