MySQL添加only by限制 full group mysql限制条件

MySQL之表的约束条件

• 一、约束

• 1.什么是约束?
• 2. 约束分类

• 二、主键约束：primary key

• 1.什么是主键约束?
• 2.主键约束的用法
• 3.示例

• 三、非空约束：not null

• 1.什么是非空约束?
• 2.非空约束的用法
• 3.示例

• 四、唯一约束：unique

• 1.什么是唯一约束?
• 2.唯一约束的用法
• 3.示例

• 五、外键约束：foreign key

• 1.什么是外键约束?
• 2.外键约束的用法
• 3.示例

一、约束

1.什么是约束?

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。

2. 约束分类

1.主键约束：primary key

2. 非空约束：not null

3. 唯一约束：unique

4.外键约束：foreign key

二、主键约束：primary key

1.什么是主键约束?

用来唯一标识数据库中的每一条记录。非空且唯一,一张表只能有一个字段为主键,主键就是表中记录的唯一标识

2.主键约束的用法

1. 在创建表时，添加主键约束
		create table student(
			id int primary key,-- 给id添加主键约束
			name varchar(20)
		);

2. 删除主键
		ALTER TABLE student DROP PRIMARY KEY;

3. 创建完表后，添加主键
		ALTER TABLE student  MODIFY id INT PRIMARY KEY;

4. 自动增长：
		1.如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

		2. 在创建表时，添加主键约束，并且完成主键自增长
		create table student  (
			id int primary key auto_increment,-- 给id添加主键约束并且自动增长
			name varchar(20)
		);
		
		3. 删除自动增长
		ALTER TABLE student  MODIFY id INT;
		
		4. 添加自动增长
		ALTER TABLE student  MODIFY id INT AUTO_INCREMENT;

		5.修改自增长的默认值起始值
		CREATE TABLE 表名(
			列名 int primary key AUTO_INCREMENT
			) AUTO_INCREMENT= 起始值;
	
		6.创建表以后修改起始值		
		ALTER TABLE 表名 AUTO_INCREMENT=起始值;

		7.DELETE 和 TRUNCATE 对自增长的影响
			1.DELETE：删除所有的记录之后，自增长没有影响。
			2.TRUNCATE：删除以后，自增长又重新开始。

3.示例

1.插入重复的主键值
insert into user values (1, '大白', 22);
错误提示： Duplicate entry '1' for key 'PRIMARY'
insert into user values (1, '小白', 20);

2.插入 NULL 的主键值
错误提示： Column 'id' cannot be null
insert into user values (null, '小白', 20);

主键自增
insert into user (name,age) values ('大白',22);
insert into user (name,age) values ('小白',20);

3.写法2:
insert into user values(null,'大白',22)

4.指定起始值为 100
create table user(
	id int primary key auto_increment,
	name varchar(20)
	) auto_increment = 100;
	
insert into user values (null, '小白');
select * from user ;

三、非空约束：not null

1.什么是非空约束?

not null，值不能为null (某一列不能为 null。)

2.非空约束的用法

1. 创建表时添加约束
	CREATE TABLE student  (
		id INT,
		NAME VARCHAR(20) NOT NULL -- name为非空
	);
	
2. 创建表完后，添加非空约束
	ALTER TABLE student  MODIFY NAME VARCHAR(20) NOT NULL;

3. 删除name的非空约束
	ALTER TABLE student  MODIFY NAME VARCHAR(20);

4.默认值
	CREATE TABLE student  (
		id INT,
		NAME VARCHAR(20) default '小白'
	);

3.示例

1.添加一条记录其中姓名不赋值
错误提示: Column 'name' cannot be null
insert into student values (2,null,'男');

2.添加一条记录,使用默认值
insert into student values (1, default);

3.添加一条记录,不使用默认值
insert into student (id,name) values (2, '李白');

四、唯一约束：unique

1.什么是唯一约束?

unique，值不能重复 。mysql中，唯一约束限定的列值可以有多个null

2.唯一约束的用法

1. 创建表时，添加唯一约束
	CREATE TABLE student  (
		id INT,
		name VARCHAR(20) UNIQUE -- 添加了唯一约束,不能出现相同姓名
	
	);

	2. 删除唯一约束
	ALTER TABLE student  DROP INDEX name ;

3. 在创建表后，添加唯一约束
	ALTER TABLE student   MODIFY name VARCHAR(20) UNIQUE;

3.示例

1.添加一个同名的学生
insert into student values (1, '小白');

错误提示: Duplicate entry '小白' for key 'name'
insert into student values (2, '小白');

2.重复插入多个 null ,由于null 没有数据，不存在重复的问题
insert into student values (2, null);
insert into student values (3, null);

五、外键约束：foreign key

1.什么是外键约束?

foreign key,让表和表产生关系，从而保证数据的正确性。从表中与主表主键对应的那一列就是外键约束。主表： 一方，用来约束别人的表 。 从表： 多方，被别人约束的表。

2.外键约束的用法

1. 在创建表时，添加外键
		create table 表名(
			....
			外键列
			constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
		);

2. 删除外键
	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 创建表之后，添加外键
	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
	
4. 级联操作: 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
	1. 添加级联操作
		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
			FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

	2. 分类：
		1. 级联更新：ON UPDATE CASCADE 
		2. 级联删除：ON DELETE CASCADE

3.示例

1.创建从表 employee 并添加外键约束 emp_depid_fk
	多方是从表
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int, -- 外键对应主表的主键
	-- 创建外键约束
	constraint emp_depid_fk foreign key (dep_id) references department(id)
)

2.正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('大白', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小白', 22, 2);

3.插入不存在的部门
错误提示: Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES ('大小白', 18, 3);


4.把部门表中的 id为2，改成 3
错误提示: Cannot delete or update a parent row: a foreign key constraint fails
update department set id=2 where id=3;

5. 删除部门 id 等于 1 的部门
错误提示:  Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;


6.添加级联更新和级联删除
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int, -- 外键对应主表的主键
	-- 创建外键约束
	constraint emp_depid_fk foreign key (dep_id) references department(id) 
	--添加级联更新和级联删除
	on update cascade on delete cascade
)

7.再次添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('大白', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小白', 22, 2);

8. 把部门表中 id 等于 1 的部门改成 id 等于 5
update department set id=5 where id=1;

9.删除部门号是 2 的部门
delete from department where id=2;


10.删除部门表
drop table department;