第十六章、資料庫之多表關係

来源:https://www.cnblogs.com/demiao/archive/2019/09/24/11581282.html
-Advertisement-
Play Games

[TOC] 第十六章、資料庫之多表關係 欄位操作 多表關係 外鍵 一對一：無級聯關係 一對一：有級聯關係(外鍵加unique約束) 一對多 多對多 ...


目錄

第十六章、資料庫之多表關係

欄位操作

create table tf1(
    id int primary key auto_increment,
    x int,
    y int
);

# 修改
alter table tf1 modify x char(4) default '';
alter table tf1 change y m char(4) default '';

# 增加
mysql>: alter table 表名 add 欄位名 類型[(長度) 約束];  # 末尾
eg>: alter table tf1 add z int unsigned;

mysql>: alter table 表名 add 欄位名 類型[(寬度) 約束] first;  # 首位
eg>: alter table tf1 add a int unsigned first;

mysql>: alter table 表名 add 欄位名 類型[(寬度) 約束] after 舊欄位名;  # 某欄位後
eg>: alter table tf1 add xx int unsigned after x;

mysql>: alter table 表名 drop 欄位名;  # 刪除欄位
eg>: alter table tf1 drop a;

多表關係

"""
一對一：丈夫-妻子，用戶-身份證，作者-作者詳情
一對多：部門-員工，班級-學生，書-出版社
多對多：老師-班級，課程-學生，出版社-作者
"""

# 書 - 出版社 - 作者 - 作者詳情 外鍵分佈
# 外鍵是 建立表與表關聯 的欄位，通常 一個表的外鍵 是 另一個表的主鍵（唯一鍵也可以）

# 一對一：外鍵在任何一方都可以，此時外鍵要設置 唯一鍵
"""
作者(author)：id，name，sex，age，mobile
作者詳情(author_detail)： id，info，address，author_id
----------------------------------------------------
作者(author)：id，name，sex，age，mobile, detail_id
1 Tom 1
2 Bom 2
3 Bob 3

作者詳情(author_detail)： id，info，address
1 Tom_info
2 Bom_info
"""

# 一對多：外鍵必須放在多的一方，此時外鍵值不唯一
"""
書(book)：id，name，price，publish_id
1 西游記 1
2 東游記 2
3 西廂記 1
4 流浪記 1

出版社(publish)： id，name，address，phone
1 老奶奶出版社
2 小奶奶出版社
"""

# 多對多：一定要創建第三張表(關係表)，每一個外鍵值不唯一，看可以多個外鍵建立聯合唯一
"""
作者(author)：id, name, age
出版社(publish)：id, name, address
作者與出版社關係表：id, author_id, publish_id
id      author_id       publish_id
1           1               1
2           1               2
3           2               1
4           2               2
"""

外鍵

# 作者(author)：id，name，sex，age，mobile, detail_id
# 作者詳情(author_detail)： id，info，address

# 1、外鍵的 欄位名 可以自定義(名字隨意)，通常命名規範(關聯表_關聯欄位)

# 2、外鍵要通過 foreign key 語法建立表與表之間的關聯

# 3、foreign key(所在表的外鍵欄位) references 關聯表(關聯欄位)
# eg：foreign key(detail_id) references author_detail(id)

# 4、級聯關係
#   級聯更新 on update cascade
#   級聯刪除 on delete cascade

# 重點：外鍵欄位本身可以唯一或不唯一，但是外鍵關聯的欄位一定唯一

一對一：無級聯關係

# 作者詳情(author_detail)： id，info，address
create table author_detail(
    id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);

# 作者表id，name，sex，age，mobile, detail_id
create table author(
    id int primary key auto_increment,
    name varchar(64) not null,
    mobile char(11) unique not null,
    sex enum('男', '女') default '男',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id)
);

# 必須先創建被關聯表數據，有關聯表外鍵關聯的記錄後，關聯表才可以創建數據
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);

# 修改關聯表 author
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他數據關聯的數據，就可以修改
# 刪除關聯表 author
mysql>: delete from author where detail_id=3;  # 直接刪除

# 修改被關聯表 author_detail
mysql>: update author_detail set id=10 where id=1;  # 無法修改
# 刪除被關聯表 author_detail
mysql>: delete from author_detail where id=1;  # 無法刪除

# 沒有級聯關係下：
# 增加：先增加被關聯表記錄，再增加關聯表記錄
# 刪除：先刪除關聯表記錄，再刪除被關聯表記錄
# 更新：關聯與被關聯表都無法完成 關聯的外鍵和主鍵 數據更新 - (如果被關聯表記錄沒有被綁定，可以修改)

一對一：有級聯關係(外鍵加unique約束)

mysql>: drop table author;
mysql>: drop table author_detail;


# 作者詳情(author_detail)： id，info，address
create table author_detail(
    id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);

# 作者表id，name，sex，age，mobile, detail_id
create table author(
    id int primary key auto_increment,
    name varchar(64) not null,
    mobile char(11) unique not null,
    sex enum('男', '女') default '男',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id)
    on update cascade 
    on delete cascade
);



# 必須先創建被關聯表數據，有關聯表外鍵關聯的記錄後，關聯表才可以創建數據
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);  # 錯誤
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);

# 修改關聯表 author
mysql>: update author set detail_id=3 where detail_id=2;  # 失敗，3詳情不存在
mysql>: update author set detail_id=1 where detail_id=2;  # 失敗，1詳情已被關聯
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他數據關聯的數據，就可以修改
# 刪除關聯表 author
mysql>: delete from author where detail_id=3;  # 直接刪除

# 修改被關聯表 author_detail
mysql>: update author_detail set id=10 where id=1;  # 級聯修改，同步關係關聯表外鍵

# 刪除被關聯表 author_detail
mysql>: delete from author where detail_id=10;  # 可以刪除對被關聯表無影響
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 10);
mysql>: delete from author_detail where id=10;  # 可以刪除，將關聯表的記錄級聯刪除掉

一對多

# 一對多：外鍵必須放在多的一方，此時外鍵值不唯一

# 出版社(publish)： id，name，address，phone
create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
);

# 書(book)：id，name，price，publish_id, author_id
create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一對多的外鍵不能設置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
);

# 增：先增加被關聯表(publish)的數據，再增加關聯表(book)的數據
mysql>: insert into publish(name, address, phone) values
('人民出版社', '北京', '010-110'),
('西交大出版社', '西安', '010-119'),
('老男孩出版社', '上海', '010-120');

mysql>: insert into book(name, price, publish_id) values
('西游記', 6.66, 1),
('東游記', 8.66, 1),
('python從入門到入土', 2.66, 2),
('輪程式員修養之道', 3.66, 3),
('好好活著', 88.88, 3);
# 沒有被關聯的欄位，插入依舊錯誤
mysql>: insert into book(name, price, publish_id) values ('打臉之道', 0.3, 4);  # 失敗


# 更新：直接更新被關聯表的(publish) 主鍵，關聯表(book) 外鍵 會級聯更新
mysql>: update publish set id=10 where id=1;
# 更新：直接更新關聯表的(book) 外鍵，修改的值對應被關聯表(publish) 主鍵 如果存在，可以更新成功，反之失敗
mysql>: update book set publish_id=2 where id=4;  # 成功
mysql>: update book set publish_id=1 where id=4;  # 失敗


# 刪：
#   刪被關聯表，關聯表會被級聯刪除
mysql>: delete from publish where id = 2;

#   刪關聯表，被關聯表不會發生變化
mysql>: delete from book where publish_id = 3;
# 假設：書與作者也是 一對多 關係，一個作者可以出版多本書
create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一對多的外鍵不能設置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
    
    # 建立與作者 一對多 的外鍵關聯
    author_id int,  
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

多對多

# 多對多：一定要創建第三張表(關係表)，每一個外鍵值不唯一，看可以多個外鍵建立聯合唯一

mysql>: drop table author;
mysql>: drop table author_detail;
mysql>: drop table book;
mysql>: drop table publish;

# 作者(author)：id, name, age
create table author(
    id int primary key auto_increment,
    name varchar(64),
    age int unsigned default 0
);

# 出版社(publish)：id, name, address
create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256)
);

# 作者與出版社關係表：id, author_id, publish_id
create table author_publish(
    id int primary key auto_increment,
    # 關係表一定有多個外鍵，關聯著多張表
    # 關聯作者表
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    # 關聯出版社表
    publish_id int,
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade,
    # 建立兩個欄位的聯合唯一
    unique(author_id, publish_id)
);

# 註：關係表 關聯著 作者 和 出版社 兩張表，在表結構上 作者 與 出版社 兩表鍵沒有任何關係


# 增：兩張被關聯表，沒有前後關係，但關係表必須在兩個表都提供數據後才能進行 關係匹配
mysql>: insert into author(name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);
mysql>: insert into publish(name, address) values('老男孩出版社', '上海'),('小女孩出版社', '北京');

# 操作關係表：
mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);

# 關係表操作：增、刪、改，只要兩張被關係表有提供對應的操作數據，都可以操作成功，且對兩張被關係表沒有影響


# 操作兩張被關係表：
#       增：不會影響關係表
mysql>: insert into publish(name, address) values('西交大出版社', '西安');
#       改：關係表都會級聯更新
mysql>: update publish set id=10 where id=1;
#       刪：關係表都會級聯刪除
mysql>: delete from author where name='ruakei';

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 可能是東半球最好的 Curl 學習指南，強烈建議收藏！
    本文首發於：微信公眾號「運維之美」，公眾號 ID：Hi Linux。 「運維之美」是一個有情懷、有態度，專註於 Linux 運維相關技術文章分享的公眾號。公眾號致力於為廣大運維工作者分享各類技術文章和發佈最前沿的科技信息。公眾號的核心理念是：分享，我們認為只有分享才能使我們的團體更強大。如果你想第一 ...
  • 豆瓣電影TOP250和書籍TOP250爬蟲
    豆瓣電影 TOP250 和書籍 TOP250 爬蟲 最近開始玩 Python , 學習爬蟲相關知識的時候，心血來潮，爬取了豆瓣電影TOP250 和書籍TOP250, 這裡記錄一下自己玩的過程。 電影 TOP250 爬蟲 書籍 TOP250 爬蟲 總結 " 點擊查看我的Github " " 點擊查看我 ...
  • 2. Linux-3.14.12記憶體管理筆記【系統啟動階段的memblock演算法（2）】
    memory：表示可用可分配的記憶體； 結束完memblock演算法初始化前的準備工作，回到memblock演算法初始化及其演算法實現上面。memblock是一個很簡單的演算法。 memblock演算法的實現是，它將所有狀態都保存在一個全局變數__initdata_memblock中，演算法的初始化以及記憶體的申請 ...
  • Linux05 文件或目錄的許可權（ls、lsattr、chattr、chmod、chown、chgrp、file）
    一、查看文件或目錄的許可權：ls -al 文件名/目錄名 1 keshengtao@LAPTOP-F9AFU4OK:~$ ls -al 2 total 16 3 drwxr-xr-x 1 keshengtao keshengtao 512 Sep 24 11:03 . 4 drwxr-xr-x 1 r ...
  • linux就該這麼學-第一節課
    #學習Linux,從小白開始，知識是由淺到深，一步一步慢慢來。總有一天你就是大佬~ 首先學習下關於linux的發展和一些歷史，就像你想瞭解一個人，你得瞭解他的過去，才能足夠瞭解他。 先瞭解下開源共用精神。 複製自由：允許把軟體複製到任何人的電腦中，不限制複製數量 傳播自由：允許軟體以各種形式傳播 收 ...
  • Linux—系統關機命令詳解
    不管是重啟系統還是關閉系統，首先要運行 sync 命令，把記憶體中的數據寫到磁碟中。將數據由記憶體同步寫入到硬碟中。 一、shutdown命令 二、reboot命令 三、halt命令 四、poweroff命令（網上說是halt命令的鏈接，基本用法和 halt 差不多） 五、init命令 Linux系統有 ...
  • 內部命令和外部命令幫助
        查看內部命令和外部命令幫助 一、內部命令   可以用type來判斷該命令是內部命令還是外部命令，type後加要查看的命令. 示例寫法：type enable   有的命令既有內部命令也有外部命令但是系統會優先使用 ...
  • 高通平臺開機LOGO修改LK(bootloader)下實現【轉】
    網路上已經有許多在kernel中修改開機Logo的文章，本文就LK下實現開機logo進行簡述 需要用到ffmpeg工具，沒有安裝ffmpeg請參考這裡： "http://blog.csdn.net/redstarofsleep/article/details/45092145" 運行"ffmpeg ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類