软件设计之MySQL(6)

软件设计之MySQL(6)

此篇应在JavaSE之后进行学习:
路线图推荐：
【Java学习路线-极速版】【Java架构师技术图谱】
Navicat可以在软件管家下载
``
使用navicat连接mysql数据库创建数据库、表、转储sql文件，导入sql数据
MySQL数据库入门到大牛，mysql安装到优化，百科全书级，全网天花板
资料可以去尚硅谷官网免费领取

学习内容：

1. 约束概述
2. 非空约束
3. 唯一性约束
4. 主键约束
5. 外键约束
6. 检查约束
7. 默认值约束

1、约束概述

为什么要约束

数据完整性是指数据的精确性和可靠性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的
1、实体完整性 ：例如，同一个表中，不能存在两条完全相同无法区分的记录
2、域完整性 ：例如：年龄范围0-120，性别范围“男/女”
3、引用完整性：例如：员工所在部门，在部门表中要能找到这个部门
4、用户自定义完整性 ：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

约束的分类

1、角度1：约束的字段的个数
单列约束vs多列约束
2、角度2：约束的作用范围
列级约束：将此约束声明在对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段后面声明的约束
3、角度3：约束的作用(功能)
① not null 非空约束
②unique 唯一性约束
③ primary key 主键约束
④ foreign key 外键约束
⑤ check 检查约束
⑥ default 默认值约束

如何添加约束

1、CREATE TABLE 时添加约束
2、ALTER TABLE 时添加约束、删除约束

如何查看表中的约束

SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

2、非空约束

限定某个字段/某列的值不允许为空

# 在CREATE TABLE时添加约束
CREATE TABLE student(
sid INT,
sname VARCHAR(20) NOT NULL,
tel CHAR(11) ,
cardid CHAR(18) NOT NULL
);
#下段代码会报错，因为sname和cardid默认为NULL了
#INSERT INTO student (sid,tel)
#VALUES(1,'111');
#在ALTER TABLE时添加约束
ALTER TABLE student
MODIFY tel CHAR(11) NOT NULL;
#在ALTER TABLE时删除约束
ALTER TABLE student
MODIFY sid INT NULL;

3、唯一性约束

1、 同一个表可以有多个唯一约束。
2、唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
3、唯一性约束允许列值为空(可以允许多个NULL存在)。
4、在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
5、MySQL会给唯一约束的列上默认创建一个唯一索引。

#在CREATE TABLE时添加约束
CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25) UNIQUE, #列级约束  #NAME为该约束的名称(默认和列名相同)
PASSWORD VARCHAR(16),
-- 使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(PASSWORD)  #uk_name_pwd为该约束的名称
);
#添加NULL值，可以多次添加并且不报错
INSERT INTO USER (id,NAME,PASSWORD)
VALUES
(1,'TOM',NULL),
(2,'MIKE',NULL);
#在ALTER TABLE时添加约束
#方式1：alter table 表名称 add unique key(字段列表);
ALTER TABLE USER
ADD CONSTRAINT user_info UNIQUE (id);#user_info为该约束名称（类似表级约束）
#方式2：alter table 表名称 modify 字段名 字段类型 unique;
#先添加一个字段用来实验
ALTER TABLE USER
ADD COLUMN email VARCHAR(20);
ALTER TABLE USER 
MODIFY email VARCHAR(20) UNIQUE;

复合的唯一性约束

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25) 
PASSWORD VARCHAR(16),
-- 使用表级约束语法
#CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)  #uk_name_pwd为该约束的名称(功能上等价)
UNIQUE KEY (NAME,PASSWORD) #复合唯一约束,该约束名称为NAME(默认和()中排在第一个的列名相同)
);
#只限制NAME和PASSWORD都相等的情况
INSERT INTO USER
VALUES
(1,'TOM','abc'),
(1,'TOM1','abc');

删除唯一性约束

1、添加唯一性约束的列上也会自动创建唯一索引。
2、删除唯一约束只能通过删除唯一索引的方式删除。
3、删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
4、如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
5、可以通过show index from 表名称;查看表的索引

#删除语句
ALTER TABLE USER
DROP INDEX uk_name_pwd;

4、主键约束

用来唯一标识表中的一行记录
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
1、一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
2、主键约束对应着表中的一列或者多列（复合主键）
3、如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
4、MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
5、当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
6、需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性

#id非空且不允许重复
CREATE TABLE test(
id INT PRIMARY KEY,# 列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
#表级约束
-- CONSISTENT pk_test_id PRIMARY KEY(id) 没必要取名
#ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);
#复合主键
CREATE TABLE test1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY (NAME,PASSWORD)
);
#NAME、PASSWORD都不允许为空值
--INSERT INTO test1
--VALUES(1,,'TOM',NULL);
#删除主键约束(实际开发中，不会删除表中主键约束)
ALTER TABLE test drop primary key;

AUTO_INCREMENT

作用：某个字段的值自增
（1)一个表最多只能有一个自增长列
（2）当需要产生唯一标识符或顺序值时，可设置自增长
（3）自增长列约束的列必须是键列（主键列，唯一键列）
（4）自增约束的列的数据类型必须是整数类型
（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。
开发中，一旦主键作用的字段声明了AUTO_INCREMENT，则在添加数据时，不要给主键对应字段去赋值了

CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
INSERT INTO test(last_name)
VALUES('TOM');
INSERT INTO test(id,last_name)
VALUES(0,'TOM'); #2 TOM
INSERT INTO test(id,last_name)
VALUES(NULL,'TOM');# 3 TOM
INSERT INTO test(id,last_name)
VALUES(10,'TOM');# 10 TOM
INSERT INTO test(last_name)
VALUES('TOM');# 11 TOM
#删除AUTO_INCREMENT
ALTER TABLE test
MODIFY id INT;

5、外键约束

作用：限定某个表的某个字段的引用完整性比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。
图中上表称为主表(父表)，下表称为从表(子表)

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
特点
1、从表的外键列，必须引用/参考主表的主键或唯一约束的列
2、在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名
3、删表时，先删从表（或先删除外键约束），再删除主表
4、当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据[关联性问题]
5、在“从表”中指定外键约束，并且一个表可以建立多个外键约束
6、从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致
7、创建外键约束会建立对应索引，删除外键约束后，必须 手动 删除对应的索引

#先创建主表
CREATE TABLE dept1(
dept_id INT 
dept_name VARCHAR(15)
);
#要设置主键或唯一性约束
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);
#再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);
#
INSERT INTO dept1
VALUES(10,'IT')
#主表添加完部门以后再在从表添加部门相关数据
INSERT INTO emp1
VALUES(1001,'TOM',10);

约束等级

Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子
表的外键列不能为not null
No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式 ：同no action， 都是立即检查外键约束
Set default方式 ：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
建议:ON UPDATE CASCADE ON DELETE RESTRICT 的方式,即更新采用Cascade，删除采用Restrict

删除外键约束

一个表可以有多个外键约束，所以要指明外键约束名称

6、检查约束

检查某个字段的值是否符号xx要求，一般指的是值的范围
MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用；MySQL 8.0中可以使用check约束

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

7、默认值约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

#在CREATE TABLE添加默认值约束
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char ,
tel char(11) not null default '' #默认是空字符串
);
#ALTER TABLE添加默认值约束
alter table employee 
modify gender char default '男';
##ALTER TABLE删除默认值约束
alter table employee 
modify gender char ;