數(shù)據(jù)完整性
如何添加數(shù)據(jù)完整性
- 在創(chuàng)建表的時候給表添加約束
完整性分類
- 實體完整性
- 域完整性
- 引用完整性
實體完整性
- 什么是實體完整性
- 表中的一行(一條記錄)代表一個實體(entity)
- 實體完整性的作用
- 標(biāo)識(標(biāo)記)每一行數(shù)據(jù)不重復(fù).行級約束
3. 約束類型 - 主鍵約束(primary key)
- 唯一約束(unique)
- 自動增長(auto_increment)
3.1 主鍵約束
特點:
- (創(chuàng)建)每個表中要有一個主鍵
- 數(shù)據(jù)唯一,且不能為null
添加方式:
- 方式1:
CREATE TABLE 表名(字段名1 數(shù)據(jù)類型 primary key,字段名2 數(shù)據(jù)類型);
舉例:
創(chuàng)建person表
CREATE TABLE person(ID BIGINT primary key,NAME VARCHAR(50))
- 方式2:
CREATE TABLE 表名(字段1 數(shù)據(jù)類型,字段名2 數(shù)據(jù)類型,primary key(要設(shè)置主鍵的字段));
舉例:
CREATE TABLE person(
ID BIGINT,
name VARCHAR(50),
age INT,
primary key(ID)
);
聯(lián)合主鍵：兩個字段數(shù)據(jù)同時相同時,才違反聯(lián)合主鍵約束
CREATE TABLE 表名(字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型,primary key(主鍵1,主鍵2));
CREATE TABLE student(
ID BIGINT,
snum BIGINT,
name VARCHAR(50),
primary key(ID,snum))
先創(chuàng)建表再添加主鍵
CREATE TABLE student(sid INT,name VARCHAR(50));
**如果表中你之前已經(jīng)添加了數(shù)據(jù),數(shù)據(jù)不符合主鍵規(guī)則就會報錯
ALTER TABLE student ADD CONSTRAINT primary key(sid);
唯一約束
特點
- 指定列的數(shù)據(jù)不能重復(fù)
- 可以為空值
格式:
CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)
自動增長列
特點
- 指定列的數(shù)據(jù)自動增長
- 即使數(shù)據(jù)刪除,還是從刪除的序號繼續(xù)往下
- 一般我們會給主鍵設(shè)置為自動增長列
格式:
CREATE TABLE 表名(
字段名1 數(shù)據(jù)類型 primary key AUTO_INCREMENT,
字段2 數(shù)據(jù)類型 UNIQUE);
舉例:
CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE)
);
域完整性
- 域完整性
數(shù)據(jù)類型
- 數(shù)值類型
- 日期類型
- 字符串類型
非空約束(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 數(shù)據(jù)類型 primary key AUTO_INCREMENT,字段2 數(shù)據(jù)類型 UNIQUE NOT NULL,字段3 數(shù)據(jù)類型 DEFAULT '默認值');
舉例:
CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL,
gender CHAR(1) DEFAULT '男'
);
參照完整性介紹
- 什么是參照完整性
- 是指表與表之間的一種對應(yīng)關(guān)系
- 通常情況下可以通過設(shè)置兩表之間的主鍵拍冠、外鍵關(guān)系,或者編寫兩表的觸發(fā)器來實現(xiàn)
- 有對應(yīng)參照完整性的兩張表格,再對他們進行數(shù)據(jù)插入,更新,刪除的過程中,系統(tǒng)都會將被修改表格與另外一張對應(yīng)表格進行對照,從而阻止一些不正確的數(shù)據(jù)的操作
- 數(shù)據(jù)庫的主鍵和外鍵類型一定要一致
- 兩個表必須要是InnoDB引擎
- 設(shè)置參照完整性后,外鍵當(dāng)中的內(nèi)值,必須得是主鍵當(dāng)中的內(nèi)容
- 一個表設(shè)置當(dāng)中的字段設(shè)置為主鍵,設(shè)置主鍵的為主表
- 創(chuàng)建表時,設(shè)置外鍵,設(shè)置外鍵的為子表
參照完整性添加
1. 通過一張表里面的某個字段,關(guān)聯(lián)另外一張表
1. 對已有表添加外鍵關(guān)系
ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);
表之間關(guān)系[一對一,一對多,多對多]
1. 一對一
- 一夫一妻(開發(fā)中使用比較少)
2. 一對多關(guān)系
- 一個人可以擁有多輛汽車,要求查詢某個人擁有的所有車輛
- 創(chuàng)建Person表
CREATE TABLE person(
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(50),
p_age INT,
p_sex CHAR(1)
);
- 創(chuàng)建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. 多對多關(guān)系
- 例如:學(xué)生選課,一個學(xué)生可以選修多門課程,每門課程可以供多個學(xué)生選擇
- 再例如: 老師和學(xué)生的多對多關(guān)系(一個老師有多個學(xué)生,一個學(xué)生有多個老師)
- 創(chuàng)建老師表
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(50),
t_age INT,
t_genger CHAR(1) DEFAULT '男'
);
- 創(chuàng)建學(xué)生表
CREATE TABLE students(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50) NOT NULL,
s_age INT,
s_gender CHAR(1) DEFAULT '男'
);
- 中間表(創(chuàng)建學(xué)生與老師 關(guān)系表)
CREATE TABLE tea_stu_rel(
t_id INT,
s_id INT
);
- 添加外鍵
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);
合并結(jié)果集UNION與UNION_ALL
1. 什么是合并結(jié)果集
- 合并結(jié)果集就是把兩個SELECT語句的查詢結(jié)果合并到一起展示
2. 合并結(jié)果集的兩種方式
1.UNION
- 合并時去除重復(fù)記錄
2.UNION ALL
- 合并時不去除重復(fù)記錄
3. 格式
SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
4. 示例
- 創(chuàng)建表
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;
注意事項
- 被合并的兩個結(jié)果集:列數(shù),列類型必須相同
