數(shù)據(jù)庫的完整性
什么是數(shù)據(jù)的完整性
保證用戶輸入的數(shù)據(jù)保存到數(shù)據(jù)庫中是正確的。
如何添加數(shù)據(jù)完整性
在創(chuàng)建表時給表中添加約束
完整性分類
- 實(shí)體完整性
- 域完整性
- 參照完整性
實(shí)體完整性
- 什么是實(shí)體完整性
表中的一行(一條記錄)代表一個實(shí)體（entity）
- 實(shí)體完整性的作用
標(biāo)識每一行數(shù)據(jù)不重復(fù)。行級約束
- 約束類型
- 主鍵約束（primary key）
- 唯一約束(unique)
- 自動增長列(auto_increment)
- 主鍵約束
特點(diǎn)：
- 每個表中要有一個主鍵
- 數(shù)據(jù)唯一，且不能為null
添加主鍵約束的方式
CREATE TABLE 表名(字段名1 數(shù)據(jù)類型 primary key,字段2 數(shù)據(jù)類型);
CREATE TABLE 表名(字段1 數(shù)據(jù)類型, 字段2 數(shù)據(jù)類型,primary key(要設(shè)置主鍵的字段));
CREATE TABLE 表名(字段1 數(shù)據(jù)類型, 字段2 數(shù)據(jù)類型,primary key(主鍵1，主鍵2));
聯(lián)合主鍵： 兩個字段數(shù)據(jù)同時相同時，才違反聯(lián)合主鍵約束。
1.先創(chuàng)建表
2.再去修改表，添加主鍵
ALTER TABLE student ADD CONSTRAINT PRIMARY KEY (id);
- 唯一約束：
特點(diǎn)：
- 指定列的數(shù)據(jù)不能重復(fù)
- 可以為空值
CREATE TABLE 表名(字段名1 數(shù)據(jù)類型 字段2 數(shù)據(jù)類型 UNIQUE);
- 自動增長列
特點(diǎn)：
- 指定列的數(shù)據(jù)自動增長
- 即使數(shù)據(jù)刪除，還是從刪除的序號繼續(xù)往下
CREATE TABLE 表名(字段名1 數(shù)據(jù)類型 PRIMARY KEY AUTO_INCREMENT ，字段2 數(shù)據(jù)類型 UNIQUE);
域完整性
限制此單元格的數(shù)據(jù)正確，不對照此列的其它單元格比較
域代表當(dāng)前單元格
域完整性約束：
-
數(shù)據(jù)類型 ：
數(shù)值類型、日期類型、字符串類型
-
非空約束（not null）
CREATE TABLE 表名(字段名1 數(shù)據(jù)類型 PRIMARY KEY AUTO_INCREMENT ，字段2 數(shù)據(jù)類型 UNIQUE not null);
-
默認(rèn)值約束(default)
CREATE TABLE 表名(字段名1 數(shù)據(jù)類型 PRIMARY KEY AUTO_INCREMENT ，字段2 數(shù)據(jù)類型 UNIQUE not null default '男');
? 插入的時候，values當(dāng)中的值直接給default
參照完整性
-
什么是參照完整性
是指表與表之間的一種對應(yīng)關(guān)系
通常情況下可以通過設(shè)置兩表之間的主鍵、外鍵關(guān)系，或者編寫兩表的觸發(fā)器來實(shí)現(xiàn)。
有對應(yīng)參照完整性的兩張表格，在對他們進(jìn)行數(shù)據(jù)插入、更新、刪除的過程中，系統(tǒng)都會將被修改表格與另一張對應(yīng)表格進(jìn)行對照，從而阻止一些不正確的數(shù)據(jù)的操作。
數(shù)據(jù)庫的主鍵和外鍵類型一定要一致；
兩個表必須得要是InnoDB類型
設(shè)置參照完整性后 ，外鍵當(dāng)中的內(nèi)值，必須得是主鍵當(dāng)中的內(nèi)容
-
一個表設(shè)置當(dāng)中的字段設(shè)置為主鍵，設(shè)置主鍵的為主表
CREATE TABLE student(sid int PRIMARY key,name varchar(50) not null,sex varchar(10) default '男');
-
創(chuàng)建表時，設(shè)置外鍵，設(shè)置外鍵的為子表
CREATE TABLE score(
sid INT,
score DOUBLE,
CONSTRAINT fk_stu_score_sid FOREIGN KEY(sid) REFERENCES student(id));
?
表之間關(guān)系
- 一對一
一夫一妻
- 一對多關(guān)系
一個人可以擁有多輛汽車，要求查詢某個人擁有的所有車輛。
創(chuàng)建Person表
創(chuàng)建Car表 
- 多對多關(guān)系
- 學(xué)生選課，一個學(xué)生可以選修多門課程，每門課程可供多個學(xué)生選擇。
- 一個學(xué)生可以有多個老師，而一個老師也可以有多個學(xué)生
創(chuàng)建老師表
創(chuàng)建學(xué)生表
創(chuàng)建學(xué)生與老師關(guān)系表
關(guān)系圖
添加外鍵
多表操作
合并結(jié)果集
- 什么是合并結(jié)果集
合并結(jié)果集就是把兩個select語句的查詢結(jié)果合并到一起
- 合并結(jié)果集的兩種方式
- UNION
合并時去除重復(fù)記錄
- UNION ALL
合并時不去除重復(fù)記錄
格式：
UNION:
SELECT * FROM 表1 UNION SELECT * FROM 表2；
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
創(chuàng)建表：
UNION：
UNION ALL：
注意事項(xiàng)：被合并的兩個結(jié)果：列數(shù)、列類型必須相同。
多表聯(lián)查：
- 什么是連接查詢
也可以叫跨表查詢，需要關(guān)聯(lián)多個表進(jìn)行查詢
- 什么是笛卡爾集
假設(shè)集合A={a,b}，集合B={0,1,2}，
則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
可以擴(kuò)展到多個集合的情況
同時查詢兩個表，出現(xiàn)的就是笛卡爾集結(jié)果
- 查詢時給表起別名
- 多表聯(lián)查，如何保證數(shù)據(jù)正確
逐行判斷，相等的留下，不相等的全不要
連接查詢
1. 內(nèi)連接
內(nèi)連接
圖示：
作用：查詢兩張表的共有部分
語句：
Select <select_list> from tableA A Inner join tableB B on A.Key = B.Key
示例：
SELECT * from employee e INNER JOIN department d on e.depart_id = d.id;
多表連接：
建表：
使用99連接法：
使用內(nèi)聯(lián)查詢
2. 左連接
圖示
作用： 把左邊表的內(nèi)容全部查出,右邊表只查出滿足條件的記錄
語句：
Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key
示例
SELECT * from employee e LEFT JOIN department d on e.depart_id = d.id;
3. 右連接
圖示：
作用
把右邊表的內(nèi)容全部查出,左邊表只查出滿足條件的記錄
語句
Select <select_list> from tableA A Right Join tableB B on A.Key = B.Key
示例
SELECT * from employee e RIGHT JOIN department d on e.depart_id = d.id;
子查詢
什么是子查詢
一個select語句中包含另一個完整的select語句。 或兩個以上SELECT，那么就是子查詢語句了。
子查詢出現(xiàn)的位置
- where后，把select查詢出的結(jié)果當(dāng)作另一個select的條件值
- from后，把查詢出的結(jié)果當(dāng)作一個新表；
示例表
使用
查詢與項(xiàng)羽同一個部門人員工
- 先查出項(xiàng)羽所在的部門編號
- 再根據(jù)編號查同一部門的員工
把第1條查出來的結(jié)果當(dāng)?shù)?天語句的條件
查詢工資高于程咬金的員工
- 查出程咬金的工資
- 再去根據(jù)查出的結(jié)果查詢出大于該值的記錄員工名稱
工資高于30號部門所有人的員工信息
- 先查出30號部門工資最高的那個人
- 再到整個表中查詢大于30號部門工資最高的那個人
查詢工作和工資與妲己完全相同的員工信息
- 先查出妲已的工作和工資
- 根據(jù)查詢結(jié)果當(dāng)作條件再去查詢工作和工資相同的員工
由于是兩個條件，使用 IN進(jìn)行判斷
有2個以上直接下屬的員工信息
-
對所有的上級編號進(jìn)行分組
-
找出大于2個的，大于2個說明有兩個下屬
-
把上條的結(jié)果當(dāng)作員工編號時行查詢
查詢員工編號為7788的員工名稱、員工工資、部門名稱、部門地址
|
