MySQL（创表）

数据完整性

如何添加数据完整性

• 在创建表的时候给表添加约束

完整性分类

• 实体完整性
• 域完整性
• 引用完整性

实体完整性

1. 什么是实体完整性

• 表中的一行(一条记录)代表一个实体(entity)

2. 实体完整性的作用

• 标识(标记)每一行数据不重复.行级约束
  3. 约束类型
• 主键约束(primary key)
• 唯一约束(unique)
• 自动增长(auto_increment)

3.1 主键约束
特点:

• (创建)每个表中要有一个主键
• 数据唯一,且不能为null

添加方式:

• 方式1:

CREATE TABLE 表名(字段名1 数据类型 primary key,字段名2 数据类型);

举例:
创建person表

CREATE TABLE person(ID BIGINT primary key,NAME VARCHAR(50))

• 方式2:

CREATE TABLE 表名(字段1 数据类型,字段名2 数据类型,primary key(要设置主键的字段));

举例:

CREATE TABLE person(
ID BIGINT,
name VARCHAR(50),
age INT,
primary key(ID)
);

联合主键：两个字段数据同时相同时,才违反联合主键约束

CREATE TABLE 表名(字段1 数据类型,字段2 数据类型,primary key(主键1,主键2));

CREATE TABLE student(
ID BIGINT,
snum BIGINT,
name VARCHAR(50),
primary key(ID,snum))

先创建表再添加主键

CREATE TABLE student(sid INT,name VARCHAR(50));

**如果表中你之前已经添加了数据,数据不符合主键规则就会报错
ALTER TABLE student ADD CONSTRAINT primary key(sid);

唯一约束
特点

• 指定列的数据不能重复
• 可以为空值
  格式:

CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)

自动增长列
特点

• 指定列的数据自动增长
• 即使数据删除,还是从删除的序号继续往下
• 一般我们会给主键设置为自动增长列
  格式:

CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE);

举例:

CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE)
);

域完整性

1. 域完整性

数据类型

• 数值类型
• 日期类型
• 字符串类型

非空约束(NOT NULL)

CREATE TABLE stu(

id　int primary key auto_increment,
name varchar(20) unique not null,
gender char(1) default '男'
);

默认值约束

(DEFAULT)CREATE TABLE 表名(字段名1 数据类型 primary key AUTO_INCREMENT,字段2 数据类型 UNIQUE NOT NULL,字段3 数据类型 DEFAULT '默认值');

举例:

CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL,
gender CHAR(1) DEFAULT '男'
);

参照完整性介绍

1. 什么是参照完整性

• 是指表与表之间的一种对应关系
• 通常情况下可以通过设置两表之间的主键、外键关系,或者编写两表的触发器来实现
• 有对应参照完整性的两张表格,再对他们进行数据插入,更新,删除的过程中,系统都会将被修改表格与另外一张对应表格进行对照,从而阻止一些不正确的数据的操作

2. 数据库的主键和外键类型一定要一致
3. 两个表必须要是InnoDB引擎
4. 设置参照完整性后,外键当中的内值,必须得是主键当中的内容
5. 一个表设置当中的字段设置为主键,设置主键的为主表
6. 创建表时,设置外键,设置外键的为子表

参照完整性添加

1. 通过一张表里面的某个字段,关联另外一张表

image

1. 对已有表添加外键关系

ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);

表之间关系[一对一,一对多,多对多]

1. 一对一

• 一夫一妻(开发中使用比较少)

2. 一对多关系

• 一个人可以拥有多辆汽车,要求查询某个人拥有的所有车辆
• 创建Person表

CREATE TABLE person(
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(50),
p_age INT,
p_sex CHAR(1)
);

• 创建Car表

CREATE TABLE car(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50),
c_color VARCHAR(25),
p_id INT,
CONSTRAINT fk_Person FOREIGE KEY(c_id) REFERENCES person(p_id)
);

3. 多对多关系

• 例如:学生选课,一个学生可以选修多门课程,每门课程可以供多个学生选择
• 再例如: 老师和学生的多对多关系(一个老师有多个学生,一个学生有多个老师)

1. 创建老师表

CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(50),
t_age INT,
t_genger CHAR(1) DEFAULT '男'
);

1. 创建学生表

CREATE TABLE students(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50) NOT NULL,
s_age INT,
s_gender CHAR(1) DEFAULT '男'
);

1. 中间表(创建学生与老师 关系表)

CREATE TABLE tea_stu_rel(
t_id INT,
s_id INT
);

1. 添加外键

ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_tid
FOREIGN KEY(t_id)
REFERENCES teacher(t_id);

ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_sid
FOREIGN KEY(sid)
REFERENCES students(sid);

合并结果集UNION与UNION_ALL

1. 什么是合并结果集

• 合并结果集就是把两个SELECT语句的查询结果合并到一起展示

2. 合并结果集的两种方式
1.UNION

• 合并时去除重复记录

2.UNION ALL

• 合并时不去除重复记录

3. 格式

SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;

4. 示例

1. 创建表

CREATE TABLE A(name VARCHAR(10),score INT);
CREATE TABLE B(name VARCHAR(10),score INT);
--
INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO A VALUES('a',10),('b',20),('c',40);

UNION

SELECT * FROM AUNIONSELECT * FROM B;

UNION ALL

SELECT * FROM AUNION ALLSELECT * FROM B;

注意事项

• 被合并的两个结果集:列数,列类型必须相同

作者：天王的
链接：https://www.jianshu.com/p/3518e1586d35
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。