😎P03 DB 資料庫的約束條件、表關係、修改表語法以及複製表😎

来源:https://www.cnblogs.com/nagase/archive/2020/06/19/13167112.html
-Advertisement-
Play Games

內容概要 約束條件 表與表之間建立關係（約束） 修改表的完整語法 複製表 約束條件 default預設值 """ # 補充：在插入數據的時候可以指定欄位 create table t1 ( id int, name varchar(16) ); insert into t1 (name, id) v ...


內容概要

  • 約束條件
  • 表與表之間建立關係（約束）
  • 修改表的完整語法
  • 複製表

約束條件

default預設值

"""
# 補充：在插入數據的時候可以指定欄位
create table t1 (
    id int, 
    name varchar(16)
);

insert into t1 (name, id) values ('samuel', 1);

create table t2 (
    id int, 
    name varchar(16),
    gender enum('male', 'female', 'others') default 'male'
);

insert into t2 (id, name) values (1, 'samuel');
insert into t2 values (2, 'nagase', 'female');
"""

unique唯一

"""
# 單列唯一
create table t3 (
    id int unique,
    name varchar(16)
);

insert into t3 values (1, 'samuel'), (1, 'nagase');
insert into t3 values (1, 'samuel'), (2, 'nagase');

# 聯合唯一
# ip和port，單個可以重覆，但是加在一起必須是唯一的
create table t4 (
    id int, 
    ip varchar(16),
    port int,
    unique (ip, port)
);

insert into t4 values (1, '192.168.1.1', 8000);
insert into t4 values (2, '192.168.1.1', 8001);
insert into t4 values (3, '192.168.1.2', 8000);
insert into t4 values (4, '192.168.1.1', 8000);
"""

primary key主鍵

"""
# 單單從約束效果上來看，primary key等價於not null + unique
# 非空且唯一！！！
create table t5 (
    id int primary key
);

insert into t5 values (null);
insert into t5 values (1), (1);
insert into t5 values (1), (2);

# 它除了有約束效果之外，它還是Innodb存儲引擎在組織數據的依據。
# Innodb存儲引擎在創建表的時候必須有primary key
# 因為它類似於書的目錄，能夠幫助提升查詢效率，並且也是建表的依據

# 1、一張表中，有且只有一個主鍵，如果沒有設置主鍵，那麼會從上至下搜索，直到遇到一個非空且唯一的欄位將其升級為primary key
create table t6 (
    id int,
    name varchar(16), 
    age int not null unique, 
    addr varchar(32) not null unique
);
# 2、如果表中沒有主鍵也沒有其他任何的非空且唯一欄位，那麼Innodb會採用自己內部提供的一個隱藏欄位作為primary key，隱藏意味著你無法使用它，進而就無法提升查詢效率

# 3、一張表中通常都應該有一個主鍵欄位，並且通常將id作為主鍵
# 單個欄位主鍵
create table t7 (
    id int primary key,
    name varchar(16)
);
# 聯合主鍵（多個欄位聯合起來作為表的主鍵，本質還是一個主鍵）
create table t7 (
    ip varchar(16),
    port int,
    primary key (ip, port)
);
"""

auto_increment自增

"""
# 當編號特別多的時候，人為的去維護太過繁瑣，因此這就涉及到了自增
create table t8 (
    id int primary key auto_increment,
    name varchar(16)
);

insert into t8 (name) values ('samuel'), ('nagase'), ('matthew');
# 註意：auto_increment通常都是加在primary key鍵上，不能給普通欄位加
"""

補充：刪除表的兩種方法

"""
delete from 表名

truncate 表名
"""

兩種方法的區別：delete from 表名這種方法，在刪除表中數據的時候，主鍵的自增不會停止，會繼續編號

然而，truncate 表名這種方式，除了清空數據表之外還會重置主鍵的自增，使其從1重新開始

表與表之間建立關係

"""
# 定義了一張員工表 表中有很多欄位
# id name gender dep_name dep_desc

# 分析目前的表所存在的問題：
# 1、該表的組織結構不是很清晰
# 2、浪費磁碟空間
# 3、數據的擴展性極差

# 那麼該如何優化呢？那麼多的欄位全部寫在了一張表裡，那就相當於把所有的代碼全部寫在一個文件中，這樣是有極大問題的。
# 因此我們應該將該表拆分為：員工表和部門表
"""

外鍵約束

# 外鍵就是用來幫助我們建立表與表中間關係的
foreign key

表與表之間的關係

# 表與表之間的關係只有4種

# 一對多關係
    # 註意：在MySQL中，沒有多對一關係這麼一說，一對多、多對一 ，都是一對多
# 多對多關係
# 一對一關係
# 沒有關係

一對多關係

"""
判斷表與表中間關係的時候，一定要一步一步的判斷，使用正確的方法，那就是
“換位思考”分別站在兩張表的角度考慮問題

比如：員工表與部門表
先站在員工表
    思考一個員工能夠對應多個部門------不能！！！
再站在部門表
    思考一個部門可以不可以包含多個員工-------可以！！！
得出結論：
    員工表與部門表表示單向的一對多，所以表關係就是一對多。
"""
foreign key
【原則1】一對多關係的表 外鍵欄位要建立在多的那一方
【原則2】在創建表的時候，一定要先創建被關聯的那一方

# SQL語句
create table dep (
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);

create table emp (
    id int primary key auto_increment,
    name varchar(16), 
    gender enum('male', 'female', 'others') default 'male',
    dep_id int,
    foreign key (dep_id) references dep (id)
);

insert into dep (dep_name, dep_desc) values ('教學部', '教書育人'), ('技術部', '技術能力有限'), ('外交部', '外交');

insert into emp (name, dep_id) values ('samuel', 2), ('nagase', 1), ('matthew', 1), ('garnet', 3);

# 修改dep表的id欄位
update dep set id = 200 where id = 2;    操作失敗

# 刪除dep表裡的數據
delete from dep;    操作失敗

以上兩個語句都失敗了，是因為dep表被emp表所引用，因此，dep表不可以被刪除。解決辦法如下：
# 1、先刪除教學部對應的員工數據，之後再刪除部門
 這樣做太過繁瑣

# 2、真正做到數據之間有關係，更新就同步更新，刪除就同步刪除
# 那也就是級聯更新、級聯刪除，那麼如何做到呢？？？
# 解決辦法：在創建表的時候添加額外的約束
create table dep (
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);

create table emp (
    id int primary key auto_increment,
    name varchar(16),
    gender enum('male', 'female', 'others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) on update cascade on delete cascade
);

多對多關係

"""
判斷表與表之間關係的時候，一定要使用正確的方法，那就是“換位思考”，分別站在兩張表的角度考慮

例如：圖書表與作者表
先站在圖書表的角度考慮，思考一個圖書可不可以有多個作者-----可以！！！
再站在作者表的角度考慮，思考一個作者可不可以出版多本圖書----可以！！！
得出結論：
圖書表與作者表是雙向的一對多，所以表關係就是“多對多”關係
"""
create table book (
    id int primary key auto_increment,
    title varchar(32),
    price int, 
    author_id int,
    foreign key(author_id) references author(id) on update cascade on delete cascade
);

create table author (
    id int primary key auto_increment,
    name varchar(32), 
    age int, 
    book_id int, 
    foreign key(book_id) references book(id) on update cascade on delete cascade
);

# 如果按照以上的語句創建表，結果是：：：失敗的！！！
回想我們的兩大原則：
【原則1】針對於一對多關係的表，外鍵欄位要建立在多的那一方
【原則2】在創建表的時候，一定要先創建被關聯的那一方
針對於圖書表與作者表，圖書表引用了作者表的id，對於圖書表來說，作者表是被關聯的那一方；那麼反過來，對於作者表來說，圖書表是被關聯的那一方，如此一來，雙方都是被關聯的那一方，那，，，，怎麼辦。。。雙方就這麼拖著。。。於是乎兩張表均創建失敗！！！

# 其實我們只是想記錄圖書與作者的關係
# 針對多對多的關係，不能在兩張原表中創建外鍵
# 我們需要單獨創建一張表出來專門用於存儲兩張表之間的關係
create table book (
    id int primary key auto_increment,
    title varchar(16),
    price int
);

create table author (
    id int primary key auto_increment, 
    name varchar(16), 
    age int
);

create table book2author (
    id int primary key auto_increment, 
    book_id int, 
    author_id int,
    foreign key(book_id) references book(id) on update cascade on delete cascade,
    foreign key(author_id) references author(id) on update cascade on delete cascade
);

一對一關係

"""
id name age gender addr phone hobby email...
如果一張表的欄位特別多，每次查詢又不是所有的欄位都能用到，於是我們將表一分為二
用戶表
    id name age
用戶詳情表
    id addr phone hobby email...

“換位思考”的方法：
先站在用戶表的角度考慮：一個用戶能否對應多個用戶詳情----不可以！！！
再站在用戶詳情的角度考慮：一個用戶詳情是否屬於多個用戶---不可以！！！
得出結論：單向的一對多都不成立，那麼這個時候兩者之間的關係要麼就是一對一的關係要麼就是沒有關係
註意，針對於一對一的關係，外鍵欄位要建立在查詢頻率高的那張表中
"""
create table user_detail (
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);

create table user (
    id int primary key auto_increment,
    name varchar(16),
    age int,
    user_detail_id int unique,
    foreign key(user_detail_id) references user_detail(id) on update cascade on delete cascade 
);

總結

"""
表關係的創建需要用到foreign key
    一對多
        外鍵欄位建立在多的一方
    多對多
        自己開設第三張表
    一對一
        建立在任意一張表中都可以，但是建議在查詢頻率高的表中建立
        
建立表之間關係的方式
    換位思考！！！
        員工 與 部門
        圖書 與 作者
        用戶 與 用戶詳情
"""

修改表

# MySQL對大小寫是不敏感的
# 1、修改表名
alter table 表名 rename 新表名;

# 2、增加欄位
alter table 表名 add 欄位名 欄位類型(寬度) 約束條件; # 預設是加在表的最後
alter table 表名 add 欄位名 欄位類型(寬度) 約束條件 first; # 加在表的開頭
alter table 表名 add 欄位名 欄位類型(寬度) 約束條件 after 欄位名; # 手動說明要加在哪裡

# 3、刪除欄位
alter table 表名 drop 欄位名;

# 4、修改欄位
alter table 表名 modify 欄位名 欄位類型(寬度) 約束條件;
alter table 表名 change 舊欄位名 新欄位名 欄位類型(寬度) 約束條件;

複製表

# 我們在執行SQL語句的時候，顯示的結果是一張虛擬的表
create table 新表名 select * from 表名; # 但是不可以複製主鍵、外鍵、索引...只是複製了結構和數據而已

 


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

-Advertisement-
Play Games
更多相關文章
  • MySQL資料庫字元集
    1.MySQL資料庫字元集知識 1.1.什麼是字元集 電腦只能識別0和1這樣的二進位數字，無論是處理電腦程式，還是進行科學運算，最終都要轉換為二進位數據來完成操作；例如，我們輸入一個數字“8”，電腦會將其識別成二進位數字“1000”。 但是，電腦要處理的數據不僅僅是數字，還會有字母，為了處理 ...
  • MySQL常用日誌文件知識
    1.MySQL常用日誌文件知識 MySQL常用日誌種類： MySQL日誌種類 解釋說明 錯誤日誌（error log） 當資料庫啟動、運行、停止時產生該日誌 普通查詢日誌（general query log） 客戶端連接資料庫執行語句時產生該日誌 二進位日誌（binary log） 當資料庫內容發生 ...
  • linux 終端輸出有顏色字體
    使用方式： \033[顯示方式;前景色;背景色m 顯示方式 0（預設值）、1（高亮）、22（非粗體）、4（下劃線）、24（非下劃線）、5（閃爍）、25（非閃爍）、7（反顯）、27（非反顯） 前景色 30（黑色）、31（紅色）、32（綠色）、 33（黃色）、34（藍色）、35（洋紅）、36（青色）、3 ...
  • Linux 系統歡迎信息設置；登錄提示信息設置；
    在項目合作的時候，有時候伺服器被多人使用；有時候需要設置提示信息，提醒登錄的同學，當前系統正在被我占用；當然在公司場景中，比如發佈更新維護之類的信息也能使用提示信息進行通知： 登錄信息可以修改三個文件： /etc/issue 本地登陸顯示的信息，本地登錄前 /etc/issue.net 網路登陸顯示 ...
  • 不同類別存儲器基本原理
    存儲器是用來存儲程式和各種數據信息的記憶部件。存儲器可分為主存儲器(簡稱主存或記憶體)和輔助存儲器(簡稱輔存或外存)兩大類。和CPU直接交換信息的是主存。主存的工作方式是按存儲單元的地址存放或讀取各類信息，統稱訪問存儲器。電腦的存儲器可分成記憶體儲器和外存儲器。記憶體儲器在程式執行期間被電腦頻繁地使用 ...
  • 003.OpenShift網路
    一 OpenShift網路實現1.1 軟體定義網路(SDN)預設情況下，Docker網路使用僅使用主機虛機網橋bridge，主機內的所有容器都連接至該網橋。連接到此橋的所有容器都可以彼此通信，但不能與不同主機上的容器通信。通常，這種通信使用埠映射來處理，其中容器埠綁定到主機上的埠，所有通信都通... ...
  • git push 提交時出錯 the remote end hung up unexpectedly
    出現此錯誤的原因一般是與遠程服務的連接中斷，但是檢查發現origin還在，可能是文件太大，緩存不夠，增加緩存大小。正常以上就可以解決問題，如果問題還在，就要考慮是否是網路太慢的原因，增加網路延時。 ...
  • 002.OpenShift安裝與部署
    一 前置條件說明 1.1 安裝準備概述 Red Hat OpenShift容器平臺是由Red Hat作為RPM包和容器映像兩種類型存在。RPM包使用訂閱管理器從標準Red Hat存儲庫(即Yum存儲庫)下載，容器映像來自Red Hat私有倉庫。 OpenShift容器平臺安裝需要多個伺服器，支持服務 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類