mysql中的数据约束操作

1.1 默认值default
-- 创建数据表的过程中,指定字段可以带有默认值,如果用户没有指定数据的情况下,当前
-- 字段会采用默认值方式来进行数据赋值操作。
-- default
create table person1
(
id int,
name varchar(50),
country varchar(50) default 'PRC' -- 默认值字段
);

desc person1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| country | varchar(50) | YES | | PRC | |
+---------+-------------+------+-----+---------+-------+

-- 不给予带有默认值字段对应的数据,会采用默认值方式赋值当前子弹
insert into person1(id, name) value (1, '骚磊');

-- 给予当前带有默认值字段数据赋值操作,会采用给予的数据赋值当前字段
insert into person1(id, name, country) value (2, '航海中路彭于晏', '中华人民共和国');

1.2 非空not null
-- not null 非空,要求当前字段必须有对应的数据,如果没有赋值报错
-- NN
-- 实际使用中必要字段!!!

create table person2
(
id int,
name varchar(50) not null,
country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | NO | | NULL | |
| country | varchar(50) | YES | | PRC | |
+---------+-------------+------+-----+---------+-------+

insert into person2(id, name) VALUE (1, '46号技师');
-- ERROR Field 'name' doesn't have a default value
insert into person2(id) VALUE (2);

1.3 唯一unique
-- 字段使用unique约束,当前字段中保存的数据在当前表内不可以重复

create table person3
(
id int unique,
name varchar(50) not null,
country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(50) | NO | | NULL | |
| country | varchar(50) | YES | | PRC | |
+---------+-------------+------+-----+---------+-------+

insert into person3(id, name) value (1, '哆啦A磊');
-- ERROR Duplicate entry '1' for key 'id'
-- 对应当前ID值已经存在,不能再次插入相同id数据
insert into person3(id, name) value (1, '老骚');

-- id使用unique限制唯一,但是null不作为唯一判断范围以内
insert into person3(name) value ('老骚');
insert into person3(name) value ('超超');
insert into person3(id, name) value (null, '贱贱的我就长大了');
insert into person3(id, name, country) value (null, '贱贱的我就长大了',null);

1.4 主键primary key
-- 主要要求唯一,非空!!!
-- primary key 主键
-- 主键一般用于在开发中涉及到数据的唯一性参照物,但是不能使用带有业务逻辑要求数据作为
-- 主键,例如 性别 年龄 工资....

create table person4
(
id int primary key,
name varchar(50) not null,
country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| country | varchar(50) | YES | | PRC | |
+---------+-------------+------+-----+---------+-------+

insert into person4(id, name) value (1, '骚磊');
-- ERROR Duplicate entry '1' for key 'PRIMARY'
-- 对应使用primary key修饰的主键id已存在。
insert into person4(id, name) value (1, '骚磊');
-- ERROR Field 'id' doesn't have a default value
insert into person4(name) value ('骚磊');
-- ERROR Column 'id' cannot be null
insert into person4(id, name) value (null, '骚磊');

1.5 自增长 auto_increment
-- 自增长修饰字段会在数据添加的过程中自动赋值叠加操作
-- auto_increment修饰的字段必须是一个Key
-- AI ==> auto_increment
create table person5
(
id int primary key auto_increment,
name varchar(50) not null,
country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| country | varchar(50) | YES | | PRC | |
+---------+-------------+------+-----+---------+----------------+

-- 自增长字段会从1开始
insert into person5(name) value ('郭德纲'); -- id = 1
insert into person5(name) value ('于谦'); -- id = 2

-- 自增长可以指定数值,而且存在影响自增长计数情况
insert into person5(id, name) value (5, '高峰'); -- id = 5
insert into person5(name) value ('栾云平'); -- id = 6

-- 自增长可以指定数值,而且存在影响自增长计数情况
insert into person5(id, name) value (8,'岳云鹏'); -- id = 8
insert into person5(name) value ('孙越'); -- id = 9

-- 指定id为7,但是当前id数据小于自增长,不影响自增长结果 数据保存排序
insert into person5(id, name) value (7, '郭麒麟'); -- id = 7
insert into person5(name) value ('阎鹤祥'); -- id = 10;

-- 删除数据
delete from person5 where id = 7;
delete from person5 where id = 10;

-- 被删除的id可以指定使用
insert into person5(id, name) value (7, '郭麒麟'); -- id = 7
-- delete删除不会印象自增长计数
insert into person5(name) value ('阎鹤祥'); -- id = 11
delete from person5 where id = 11;

-- 清空数据表,同时会重置自增长
truncate person5;

-- 数据库彻底关闭,会影响到自增长保存

1.6 外键约束
create table employee
(
id int primary key auto_increment,
name varchar(50) not null,
deptName varchar(50)
);

-- 这里会导致数据冗余问题。并且存在资源的浪费
insert into employee(name, deptName) value ('骚磊', '帅部');
insert into employee(name, deptName) value ('彭于晏', '帅部');
insert into employee(name, deptName) value ('吴彦祖', '帅部');
insert into employee(name, deptName) value ('烧饼', '骚气无敌部');
insert into employee(name, deptName) value ('岳云鹏', '贱到无敌部');
insert into employee(name, deptName) value ('曾小贤', '贱到无敌部');
insert into employee(name, deptName) value ('孟鹤堂', '骚气无敌部');

drop table employee;

-- 部门表
create table dept
(
id int primary key auto_increment,
deptName varchar(50) not null
);

-- 员工表,员工表中存在字段和部门表有直接联系,deptId ==》 dept表内的id
create table employee
(
id int primary key auto_increment,
name varchar(50) not null,
deptId int
);

-- 准备部门表数据
insert into dept(deptName) value ('帅部');
insert into dept(deptName) value ('贱到无敌部');
insert into dept(deptName) value ('骚气无敌部');

-- 插入员工数据
insert into employee(name, deptId) value ('骚磊', 1);
insert into employee(name, deptId) value ('彭于晏', 1);
insert into employee(name, deptId) value ('吴彦祖', 1);
insert into employee(name, deptId) value ('烧饼', 3);
insert into employee(name, deptId) value ('岳云鹏', 2);
insert into employee(name, deptId) value ('曾小贤', 2);
insert into employee(name, deptId) value ('孟鹤堂', 3);

-- 内连接联表查询目标数据
select e.id, e.name, d.deptName
from nzgp2001.employee e
inner join dept d on d.id = e.deptId;

-- 1. 目前数据存储效率是高于第一种方法
-- 2. 数据存储的复杂度,修改操作都是高于第一种方法

-- 当前数据插入操作是完全就可以执行,但是郭德纲的数据在存储到数据表中之后
-- 部门ID号是不合理的!!!
insert into employee(name, deptId) value ('郭德纲', 4);

-- SQL语句可以执行,但是存在部门表删除后,对应的员工数据存储存在隐患
delete from dept where id = 3;

drop table dept;
drop table employee;

-- 使用外键约束

-- 部门表 【主表】
create table dept
(
id int primary key auto_increment,
deptName varchar(50) not null
);

-- 员工表,员工表中存在字段和部门表有直接联系,deptId ==》 dept表内的id
-- 【从表】
create table employee
(
id int primary key auto_increment,
name varchar(50) not null,
deptId int,
-- 外键声明约束
-- constraint 声明使用关键字
-- fk_emp_dept 外键名称 fk ==> foreign key emp ==> employee dept ==> 部门表
-- foreign key 外键声明关键字(从表外键字段) deptId employee表内的外键是deptId
-- references 选择当前外键关联的主表字段关键字 主表(字段) dept(id)
constraint fk_emp_dept foreign key (deptId) references nzgp2001.dept(id)
);

-- 准备部门表数据
insert into dept(deptName) value ('帅部');
insert into dept(deptName) value ('贱到无敌部');
insert into dept(deptName) value ('骚气无敌部');

-- 插入员工数据
insert into employee(name, deptId) value ('骚磊', 1);
insert into employee(name, deptId) value ('彭于晏', 1);
insert into employee(name, deptId) value ('吴彦祖', 1);
insert into employee(name, deptId) value ('烧饼', 3);
insert into employee(name, deptId) value ('岳云鹏', 2);
insert into employee(name, deptId) value ('曾小贤', 2);
insert into employee(name, deptId) value ('孟鹤堂', 3);

-- Cannot add or update a child row: a foreign key constraint fails (`nzgp2001`.`employee`,
-- CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 该数据条件时会检查主表,询问主表中是否存在指定的字段数据
insert into employee(name, deptId) value ('郭德纲', 4);

-- Cannot add or update a child row: a foreign key constraint fails (`nzgp2001`.`employee`,
-- CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 修改部门id为3的数据,改为10,deptId == 10 在主表中不存在
update employee set deptId = 10 where deptId = 3;

-- Cannot delete or update a parent row: a foreign key constraint fails (`nzgp2001`.`employee`,
-- CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 删除主表数据行,需要询问从表中是否有使用当前主表数据的数据行,如果有,无法删除。
delete from nzgp2001.dept where nzgp2001.dept.id = 3;

update nzgp2001.dept set id = 10 where id = 3;

-- 使用外键约束操作优势和问题
-- 优势:
-- 1. 主表从表关联性更强
-- 2. 主表从表数据操作时存在更多约束
-- 3. 操作规范性更强’

-- 问题:
-- 1. 从表操作过程中添加和修改约束过强,需要首先操作主表
-- 2. 主表操作过程中删除和修改约束过程,需要首先操作从表
-- 3. 整个数据库更新,删除操作过于繁琐,并且后期维护压力过大

-- 外键约束虽然可以从SQL语句的语法上约束一定操作,但是会导致更多的问题,在开发中不允许使用。

1.7级联操作
-- 在外键约束的情况下,操作主表和从表都有很多约束
-- SQL中引入的级联操作
-- 级联删除 on delete cascade
-- 级联修改 on update cascade
-- 主表操作执行删除或者修改时,对于外键约束的从表会对应操作。

drop table dept;
drop table employee;

-- 使用外键约束

-- 部门表 【主表】
create table dept
(
id int primary key auto_increment,
deptName varchar(50) not null
);

-- 员工表,员工表中存在字段和部门表有直接联系,deptId ==》 dept表内的id
-- 【从表】
create table employee
(
id int primary key auto_increment,
name varchar(50) not null,
deptId int,
-- 外键声明约束
-- constraint 声明使用关键字
-- fk_emp_dept 外键名称 fk ==> foreign key emp ==> employee dept ==> 部门表
-- foreign key 外键声明关键字(从表外键字段) deptId employee表内的外键是deptId
-- references 选择当前外键关联的主表字段关键字 主表(字段) dept(id)
constraint fk_emp_dept foreign key (deptId) references nzgp2001.dept(id)
on delete cascade -- 级联删除
on update cascade -- 级联修改
);

-- 准备部门表数据
insert into dept(deptName) value ('帅部');
insert into dept(deptName) value ('贱到无敌部');
insert into dept(deptName) value ('骚气无敌部');

-- 插入员工数据
insert into employee(name, deptId) value ('骚磊', 1);
insert into employee(name, deptId) value ('彭于晏', 1);
insert into employee(name, deptId) value ('吴彦祖', 1);
insert into employee(name, deptId) value ('烧饼', 3);
insert into employee(name, deptId) value ('岳云鹏', 2);
insert into employee(name, deptId) value ('曾小贤', 2);
insert into employee(name, deptId) value ('孟鹤堂', 3);

-- 主表中有外键约束的字段数据发生修改,从表中对于字段同时发生修改,级联修改
update dept set id = 10 where id = 3;

-- 主表字段删除,从表中使用主表约束的数据行也需要删除
delete from nzgp2001.dept where id = 1;


作者:small_Stone1.0

相关推荐

[ Python ] 原来集合、元组、列表、字典类型及操作这么简单,看完简直秒懂!

Hadoop大数据学习–CentOS7–精华篇

数据库基本命令的使用和数据库的创建

在这里插入图片描述

MySQL学习笔记(3)——索引练习