筆記内容輸出來源：拉勾教育Java大資料學科 訓練營；
目錄
1. 多表
1.1 多表簡述
1.2.2 單表的問題
1.3 解決方案
1.3.1 設計為兩張表
1.3.3 多表設計上的問題
1.4 外鍵限制
1.4.1 什麼是外鍵
1.4.2 建立外鍵限制
1.4.3 删除外鍵限制
1.4.4 外鍵限制的注意事項
1.4.5 級聯删除操作(了解)
3. 多表查詢
3.1 什麼是多表查詢
3.2 資料準備
3.4 多表查詢的分類
3.4.1 内連接配接查詢
3.4.1.1 隐式内連接配接
3.4.1.2 顯式内連接配接
3.4.2 外連接配接查詢
3.4.2.2 右外連接配接
3.4.3 各種連接配接方式的總結
4. 子查詢 (SubQuery)
4.1 什麼是子查詢
子查詢常見分類
4.2 子查詢的結果作為查詢條件
4.3 子查詢的結果作為一張表
4.4 子查詢結果是單列多行
4.5 子查詢總結
5. 資料庫設計
5.1 資料庫三範式(空間最省)
5.1.1 第一範式 1NF
5.1.2 第二範式 2NF
5.1.3 第三範式 3NF
5.2.4 總結
1. 多表
1.1 多表簡述
1.2.2 單表的問題
1) 備援, 同一個字段中出現大量的重複資料
1.3 解決方案
1.3.1 設計為兩張表
1.3.3 多表設計上的問題
當我們在 員工表的 dept_id 裡面輸入不存在的部門id ,資料依然可以添加 顯然這是不合理的.
使用外鍵限制,限制 dept_id ,必須是 部門表中存在的id
1.4 外鍵限制
1.4.1 什麼是外鍵
- 外鍵指的是在 從表 中 與 主表 的主鍵對應的那個字段,比如員工表的 dept_id,就是外鍵
- 使用外鍵限制可以讓兩張表之間産生一個對應關系,進而保證主從表的引用的完整性
多表關系中的主表和從表
- 主表: 主鍵id所在的表, 限制别人的表 從表:
- 外鍵所在的表多, 被限制的表
1.4.2 建立外鍵限制
文法格式:
1. 建立表時添加外鍵
[CONSTRAINT] [外鍵限制名稱] FOREIGN KEY(外鍵字段名) REFERENCES 主表名(主鍵字段名)
-- 先删除 employee表
DROP TABLE employee;
-- 重新建立 employee表,添加外鍵限制
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
-- 添加外鍵限制
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);
1. 已有表添加外鍵
ALTER TABLE 從表 ADD [CONSTRAINT] [外鍵限制名稱] FOREIGN KEY (外鍵字段名) REFERENCES
主表(主 鍵字段名);
1.4.3 删除外鍵限制
alter table 從表 drop foreign key 外鍵限制名稱
-- 删除employee 表中的外鍵限制,外鍵限制名 emp_dept_fk
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
2) 再将外鍵 添加回來
ALTER TABLE 從表 ADD [CONSTRAINT] [外鍵限制名稱] FOREIGN KEY (外鍵字段名) REFERENCES
主表(主 鍵字段名);
-- 可以省略外鍵名稱, 系統會自動生成一個
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);
1.4.4 外鍵限制的注意事項
1) 從表外鍵類型必須與主表主鍵類型一緻 否則建立失敗.
2) 添加資料時, 應該先添加主表中的資料
3) 删除資料時,應該先删除從表中的資料.
1.4.5 級聯删除操作(了解)
級聯删除 ON DELETE CASCADE
1) 删除 employee表,重新建立,添加級聯删除
-- 重新建立添加級聯操作
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- 添加級聯删除
ON DELETE CASCADE
);
-- 添加資料
INSERT INTO employee (ename, age, dept_id) VALUES ('張百萬', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('趙四', 21, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('廣坤', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('豔秋', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2);
表與表之間的三種關系
一對多關系: 最常見的關系, 學生對班級,員工對部門
多對多關系: 學生與課程, 使用者與角色
一對一關系: 使用較少,因為一對一關系可以合成為一張表
員工表中 外鍵值是2的記錄,也被删除了
2. 多表關系設計
實際開發中，一個項目通常需要很多張表才能完成。例如：一個商城項目就需要分類表(category)、
商品表(products)、訂單表(orders)等多張表。且這些表的資料之間存在一定的關系，接下來我們一起
學習一下多表關系設計方面的知識
2.1 一對多關系(常見)
一對多關系（1:n）
例如：班級和學生，部門和員工，客戶和訂單，分類和商品
一對多建表原則
在從表(多方)建立一個字段,字段作為外鍵指向主表(一方)的主鍵
2.2 多對多關系(常見)
多對多（m:n）
例如：老師和學生，學生和課程，使用者和角色
n 多對多關系建表原則
需要建立第三張表，中間表中至少兩個字段，這兩個字段分别作為外鍵指向各自一方的 主
鍵。
-- 删除部門編号為2 的記錄
DELETE FROM department WHERE id = 2;
3. 多表查詢
3.1 什麼是多表查詢
DQL: 查詢多張表,擷取到需要的資料
比如 我們要查詢家電分類下 都有哪些商品,那麼我們就需要查詢分類與商品這兩張表
3.2 資料準備
1) 建立db3_2 資料庫
-- 建立 db3_2 資料庫,指定編碼
CREATE DATABASE db3_2 CHARACTER SET utf8;
2) 建立分類表與商品表
#分類表 (一方 主表)
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
#商品表 (多方 從表)
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标記為：1表示上架、0表示下架
category_id VARCHAR(32),
-- 添加外鍵限制
FOREIGN KEY (category_id) REFERENCES category (cid)
);
3) 插入資料
#分類資料
INSERT INTO category(cid,cname) VALUES('c001','家電');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妝品');
INSERT INTO category(cid,cname) VALUES('c004','汽車');
#商品資料
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米電視
機',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空
調',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰
箱',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','籃球
鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','運動
褲',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T
恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','沖鋒
衣',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙
水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大
寶',200,'1','c003');
3.4 多表查詢的分類
3.4.1 内連接配接查詢
内連接配接的特點:
通過指定的條件去比對兩張表中的資料, 比對上就顯示,比對不上就不顯示
比如通過: 從表的外鍵 = 主表的主鍵 方式去比對
3.4.1.1 隐式内連接配接
from子句 後面直接寫 多個表名 使用where指定連接配接條件的 這種連接配接方式是 隐式内連接配接. 使用where條件過濾無用的資料
SELECT 字段名 FROM 左表, 右表 WHERE 連接配接條件
# 隐式内連接配接
SELECT * FROM products,category WHERE category_id = cid
可以通過給表起别名的方式, 友善我們的查詢(有提示)
SELECT
p.`pname`,
p.`price`,
c.`cname`
FROM products p , category c WHERE p.`category_id` = c.`cid`;
查詢 格力空調是屬于哪一分類下的商品
#查詢 格力空調是屬于哪一分類下的商品
SELECT p.`pname`,c.`cname` FROM products p , category c
WHERE p.`category_id` = c.`cid` AND p.`pid` = 'p002';
3.4.1.2 顯式内連接配接
使用 inner join ...on 這種方式, 就是顯式内連接配接
文法格式
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 條件
-- inner 可以省略
查詢所有商品資訊和對應的分類資訊
-- 顯式内連接配接查詢
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
3.4.2 外連接配接查詢
3.4.2.1 左外連接配接
- 左外連接配接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
- 左外連接配接的特點
- 以左表為基準, 比對右邊表中的資料,如果比對的上,就展示比對到的資料 如果比對不到, 左表中的資料正常展示, 右邊的展示為null.
) 文法格式
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 條件
-- 左外連接配接查詢
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
2) 左外連接配接, 查詢每個分類下的商品個數
# 查詢每個分類下的商品個數
/*
1.連接配接條件: 主表.主鍵 = 從表.外鍵
2.查詢條件: 每個分類 需要分組
3.要查詢的字段: 分類名稱, 分類下商品個數
*/
SELECT
c.`cname` AS '分類名稱',
COUNT(p.`pid`) AS '商品個數'
FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;
3.4.2.2 右外連接配接
- 右外連接配接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
- 右外連接配接的特點 以右表為基準，比對左邊表中的資料，如果能比對到，展示比對到的資料 如果比對不到，右表中的資料正常展示, 左邊展示為null
) 文法格式
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 條件
-- 右外連接配接查詢
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`ci
3.4.3 各種連接配接方式的總結
- 内連接配接: inner join , 隻擷取兩張表中 交集部分的資料.
- 左外連接配接: left join , 以左表為基準 ,查詢左表的所有資料, 以及與右表有交集的部分
- 右外連接配接: right join , 以右表為基準,查詢右表的所有的資料,以及與左表有交集的部分
4. 子查詢 (SubQuery)
4.1 什麼是子查詢
子查詢概念‘
’一條select 查詢語句的結果, 作為另一條 select 語句的一部分
子查詢的特點
- 子查詢必須放在小括号中
- 子查詢一般作為父查詢的查詢條件使用
子查詢常見分類
- where型 子查詢: 将子查詢的結果, 作為父查詢的比較條件
- from型 子查詢 : 将子查詢的結果, 作為 一張表,提供給父層查詢使用
- exists型 子查詢: 子查詢的結果是單列多行, 類似一個數組, 父層查詢使用 IN 函數 ,包含子查 詢的結果
4.2 子查詢的結果作為查詢條件
SELECT 查詢字段 FROM 表 WHERE 字段=（子查詢）;
# 通過子查詢的方式, 查詢價格最高的商品資訊
-- 1.先查詢出最高價格
SELECT MAX(price) FROM products;
-- 2.将最高價格作為條件,擷取商品資訊
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
2. 查詢化妝品分類下的 商品名稱 商品價格
#查詢化妝品分類下的 商品名稱 商品價格
-- 先查出化妝品分類的 id
SELECT cid FROM category WHERE cname = '化妝品';
-- 根據分類id ,去商品表中查詢對應的商品資訊
SELECT
p.`pname`,
p.`price`
FROM products p
WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妝品');
3. 查詢小于平均價格的商品資訊
-- 1.查詢平均價格
SELECT AVG(price) FROM products; -- 1866
-- 2.查詢小于平均價格的商品
SELECT * FROM products
WHERE price < (SELECT AVG(price) FROM products);
4.3 子查詢的結果作為一張表
SELECT 查詢字段 FROM （子查詢）表别名 WHERE 條件;
1. 查詢商品中,價格大于500的商品資訊,包括 商品名稱 商品價格 商品所屬分類名稱
-- 1. 先查詢分類表的資料
SELECT * FROM category;
-- 2.将上面的查詢語句 作為一張表使用
SELECT
p.`pname`,
p.`price`,
c.cname
FROM products p
-- 子查詢作為一張表使用時 要起别名 才能通路表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;
注意： 當子查詢作為一張表的時候，需要起别名，否則無法通路表中的字段。
4.4 子查詢結果是單列多行
SELECT 查詢字段 FROM 表 WHERE 字段 IN （子查詢）;
1. 查詢價格小于兩千的商品,來自于哪些分類(名稱)
# 查詢價格小于兩千的商品,來自于哪些分類(名稱)
-- 先查詢價格小于2000 的商品的,分類ID
SELECT DISTINCT category_id FROM products WHERE price < 2000;
-- 在根據分類的id資訊,查詢分類名稱
-- 報錯: Subquery returns more than 1 row
-- 子查詢的結果 大于一行
SELECT * FROM category
WHERE cid = (SELECT DISTINCT category_id FROM products WHERE price < 2000);
使用in函數, in( c002, c003 )
-- 子查詢擷取的是單列多行資料
SELECT * FROM category
WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);
1. 查詢家電類 與 鞋服類下面的全部商品資訊
# 查詢家電類 與 鞋服類下面的全部商品資訊
-- 先查詢出家電與鞋服類的 分類ID
SELECT cid FROM category WHERE cname IN ('家電','鞋服');
-- 根據cid 查詢分類下的商品資訊
SELECT * FROM products
WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家電','鞋服'));
4.5 子查詢總結
- 子查詢如果查出的是一個字段(單列), 那就在where後面作為條件使用
- . 2. 子查詢如果查詢出的是多個字段(多列), 就當做一張表使用(要起别名).
5. 資料庫設計
5.1 資料庫三範式(空間最省)
概念: 三範式就是設計資料庫的規則為了建立備援較小、結構合理的資料庫，設計資料庫時必須遵循一定的規則。在關系型資料 庫中這種規則就稱為範式。範式是符合某一種設計要求的總結。要想設計一個結構合理的關 系型資料庫，必須滿足一定的範式 滿足最低要求的範式是第一範式（1NF）。在第一範式的基礎上進一步滿足更多規範要求的 稱為第二範式（2NF） ， 其餘範式以此類推。一般說來，資料庫隻需滿足第三範式(3NF）就 行了
5.1.1 第一範式 1NF
- 原子性, 做到列不可拆分
- 第一範式是最基本的範式。資料庫表裡面字段都是單一屬性的，不可再分, 如果資料表中每個 字段都是不可再分的最小資料單元，則滿足第一範式。
5.1.2 第二範式 2NF
概念:
- 在第一範式的基礎上更進一步，目标是確定表中的每列都和主鍵相關。
- 一張表隻能描述一件事.
5.1.3 第三範式 3NF
- 消除傳遞依賴
- 表的資訊，如果能夠被推導出來，就不應該單獨的設計一個字段來存放
5.2.4 總結
建立一個關系型資料庫設計，我們有兩種選擇
- 1，盡量遵循範式理論的規約，盡可能少的備援字段，讓資料庫設計看起來精緻、優雅、讓人心 醉。
- 2，合理的加入備援字段這個潤滑劑，減少join，讓資料庫執行性能更高更快。
![查找入職員工時間排名倒數第三的員工所有資訊[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)