02-MySQL高級

来源:https://www.cnblogs.com/OnlyOnYourself-lzw/archive/2022/07/12/16471573.html
-Advertisement-
Play Games

MySQL 高級 1、約束 1.1、約束介紹 什麼是約束 對錶中的數據進行限定，保證數據的正確性、有效性、完整性 約束類型 | 約束 | 說明 | | : : | : : | | PRIMARY KEY | 主鍵約束 | | UNIQUE | 唯一約束 | | NOT NULL | 非空約束 | | ...


MySQL 高級

1、約束

1.1、約束介紹

  • 什麼是約束

    • 對錶中的數據進行限定，保證數據的正確性、有效性、完整性

  • 約束類型

    • 約束 說明
      PRIMARY KEY 主鍵約束
      UNIQUE 唯一約束
      NOT NULL 非空約束
      DEFAULT 預設值約束
      FOREIGN KEY 外鍵約束
      CHECK 檢查約束（MySQL並不支持）

  • 註意事項

    • MySQL不支持檢查約束
    • 約束通常是在創建表結構的時候創建
      • 如果在創建表結構的時候沒增加約束，後續再添加約束的話，有可能會導致垃圾數據的進入

1.2、主鍵約束

  • 主鍵的作用

    • 用來區分表中的數據

  • 主鍵的特點

    • 主鍵必須是唯一不重覆的值
    • 主鍵不能包含NULL值

  • 建表的時候添加主鍵約束

    • CREATE TABLE 表名 (
	欄位名 欄位類型 PRIMARY KEY,
	欄位名 欄位類型
);

CREATE TABLE 表名(
   列名 數據類型,
   [CONSTRAINT] [約束名稱] PRIMARY KEY(列名)
);

  • 刪除主鍵約束

    • ALTER TABLE 表名 DROP PAIMARY KEY;	-- 非空主鍵不會隨著主鍵約束的刪除而消失，在MySQL中會保存下來

    • 註意事項

      • 非空主鍵不會隨著主鍵約束的刪除而消失，在MySQL中會保存下來

  • 建表後單獨添加主鍵約束

    • ALTER TABLE 表名 ADD PRIMARY KEY (欄位名);

    • 註意事項

      • 當添加主鍵約束的時候，欄位的值如果在表中存在有重覆值，那麼建表後單獨添加主鍵約束會報錯

  • 主鍵自增

    • 主鍵如果讓我們自己添加很有可能重覆，我們通常希望在每次插入新紀錄時，資料庫自動生成主鍵欄位的值
    • 主鍵設置為自增後，允許插入的主鍵為NULL值，自增的主鍵會自動把NULL值改為自增後的數據
    • 格式
      • 欄位名 欄位類型 PRIMARY KEY AUTO_INCREMENT
      • 註意事項：AUTO_INCREMENT 的欄位必須是數值類型

  • 面試題：修改自動增長的開始值

    • ALTER TABLE st2 AUTO_INCREMENT = 1000;
INSERT INTO st2 (NAME, age) VALUES ('校長', 22);

ALTER TABLE st2 AUTO_INCREMENT = 500;
INSERT INTO st2 (NAME, age) VALUES ('coolman', 23);

    • 註意事項

      • 自增以出現過的最大值為基準而+1

1.3、非空約束

  • 非空約束的作用

    • 讓欄位的值不能為NULL

  • 非空約束的格式

    • CREATE TABLE 表名 (
	欄位名 欄位類型 NOT NULL,
    欄位名  欄位類型
);

1.4、唯一約束

  • 唯一約束的作用

    • 讓欄位的值唯一，不能重覆

  • 唯一約束的格式

    • CREATE TABLE 表名 (
	欄位名 欄位類型 UNIQUE,
  	欄位名 欄位類型
);

1.5、預設約束

  • 預設約束的作用

    • 如果這個欄位不設置值，就使用預設值

  • 預設約束的格式

    • CREATE TABLE 表名(
  	欄位名 數據類型 DEFAULT 值,
  	欄位名 欄位類型
);

1.6、外鍵約束

1.6.1、使用外鍵約束的意義

  • 當我們在employee的dep_id裡面輸入不存在的部門，數據依然可以添加，但是並沒有對應的部門，不能出現在這種情況。employee的dep_id的內容只能是department表中存在的id
    • 使用外鍵約束的意義
  • 解決方式
    • 需要約束dep_id只能是department表中已經存在id
    • 可以使用外鍵約束來解決這類問題
  • 外鍵約束的作用
    • 1.限製表中的數據只能使用另外一張表的數據
    • 2.保證數據的一致性、完整性

1.6.2、外鍵約束的概念

  • 什麼是外鍵
    • A1表中的欄位C1，引用了A2表中欄位C2，那麼C1欄位叫做外鍵，A2表交主表，A1表叫從表（也叫副表）
    • 主表：將數據給別人用的表
    • 副表：使用別人數據的表

1.6.3、外鍵約束的使用

  • 新建表的時候增加外鍵約束

    • CREATE TABLE 表名 (
	欄位名 欄位類型,
  	欄位名 欄位類型,
  	-- 添加外鍵約束
  	[CONSTRAINT 外鍵約束名] FOREIGN KEY (外鍵欄位名) REFERENCES 主表(主表欄位名)
);

    • 關鍵字解釋

      • CONSTRAINT
        • 表示約束外鍵約束名：給外鍵取個名字，將來通過約束名可以刪除這個約束
      • FOREIGN KEY(外鍵欄位名)
        • 指定某個欄位左外外鍵
      • REFERENCES 主表(主鍵欄位名)
        • 引用主表的主鍵的值

  • 刪除外鍵約束

    • ALTER TABLE 表名 DROP FOREGIN KEY 外鍵約束名;

    • 註意事項

      • 刪除外鍵的時候，外鍵名不需要添加單引號（外鍵名等同於其他欄位名）

  • 已有表增加外鍵約束

    • ALTER TABLE 從表 ADD [CONSTRAINT 外鍵約束名稱] FOREIGN KEY (外鍵欄位名) REFERENCES 主表(主鍵欄位名);

2、資料庫設計（範式）

2.1、資料庫設計簡介

  • 1.軟體的研發步驟
  • 2.資料庫設計概念
    • 數據設計就是根據業務系統的具體需求，結合我們所選用的DBMS，為這個業務系統構造出最優的數據存儲模型
    • 建立資料庫中的表結構以及表與表之間的關聯關係的過程
    • 有哪些表？表裡有哪些欄位？表和表之間有什麼關係？
  • 3.資料庫設計的步驟
    • 需求分析（數據是什麼，數據具有哪些屬性，數據與屬性的特點是什麼）
    • 邏輯分析（通過ER圖對資料庫進行邏輯建模，不需要考慮我們所選用的資料庫管理系統）
    • 物理設計（根據資料庫自身的特點把邏輯設計轉換為物理設計）
    • 維護設計（對新的需求進行建模；表優化）
  • 論壇系統設計案例

2.2、表關係

2.2.1、表關係之一對多

  • 一對多（多對一）
    • 部門表和員工表
    • 一個部門對應多個員工，一個員工對應一個部門
  • 實現方式
    • 在多的一方建立外鍵，指向一的一方的主鍵

2.2.2、表關係之多對多

  • 多對多
    • 訂單表和商品表
    • 一個商品對應多個訂單，一個訂單包含多個商品
  • 實現方式
    • 建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵

2.2.3、表關係之一對一

  • 一對一
    • 用戶表和用戶詳情表
    • 一對一關係多用於表拆分，將一個實體中經常使用的欄位放在一張表，不經常使用的欄位放另一張表，用於提升查詢性能
  • 實現方式
    • 在任意一方加入外鍵，關聯另一方主鍵，並且設置外鍵為唯一（UNIQUE）

2.3、資料庫設計案例

3、MySQL多表查詢

3.1、MySQL多表查詢介紹

  • 為什麼要有多表查詢
    • 例如要查詢某員工的名字和他所在的部門名字（這裡假設資料庫中員工表和部門表是關聯的）
    • 需要查詢多張表才能得到我們想要的數據
  • 多表查詢的分類
    • 表連接查詢（同時查詢多張表）
      • 內連接
      • 外連接
    • 子查詢

3.2、表連接笛卡爾積現象

  • 查詢孫悟空員工的信息，包括所在的部門名稱
    • 左表的每條數據和右表的每條數據組合，這種效果稱為笛卡爾乘積
    • 我們發現不是所有的數據組合都是游泳的，只有員工表.dept_id = 部門表.id的數據才是游泳的。所以需要通過條件過濾掉沒用的數據。
    • 過濾掉沒用數據的條件稱為表連接條件

3.3、表連接查詢--內連接

  • 隱式內連接

    • SELECT 欄位列表 FROM 表1, 表2,... WHERE 條件;

    • 看不到 JOIN關鍵字，條件使用WHERE指定

  • 顯式內連接

    • SELECT 欄位列表 FROM 表1 [INNER] JOIN 表2 ON 條件；

    • 使用INNER JOIN ... ON 條件，可以省略INNER

  • 內連接效果

3.4、表連接查詢--外連接

3.4.1、左外查詢

  • SELECT 欄位列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件;

  • 使用LEFT OUTER JOIN ... ON，OUTER可以省略

  • 左外連接效果

    • 左外連接可以理解為：將滿足要求的數據顯示 ，左表不滿足要求的數據也顯示

3.4.2、右外查詢

  • SELECT 欄位列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件;

  • 使用RIGHT OUTER JOIN ... ON，OUTER可以省略

  • 右外連接效果

    • 右外連接可以理解為：滿足要求的數據顯示，並且右表不滿足要求的也顯示

3.5、多表查詢之子查詢

  • 什麼是子查詢

    • 一個查詢語句的結果作為另一個查詢語句的一部分

  • Demo

    • SELECT 查詢欄位 FROM 表 WHERE 條件;

SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

    • 子查詢要放在()中

    • 先執行子查詢，將子查詢的結果作為父查詢的一部分

  • 子查詢結果的三種情況

    • 子查詢結果是單行單列

      • SELECT 查詢欄位 FROM 表 WHERE 欄位=(子查詢);

      • 子查詢結果是單行單列，在WHERE後面作為條件，WHERE後面使用的是比較運算符：=，>, <=, <>

    • 子查詢結果是多行單列

      • SELECT 查詢欄位 FROM 表 WHERE 欄位 IN (子查詢);

      • 子查詢結果是多行單列，結果集類似於一個數組，在WHERE後面作為條件，父類使用IN/ANY/ALL運算符

    • 子查詢結果是多行多列

      • SELECT 查詢欄位 FROM (子查詢) 表別名 WHERE 條件;

      • 子查詢結果是多行多列，在FROM後面作為虛擬表

3.6、多表查詢案例

  • 我們在公司開發中，根據不同的業務需求往往需要通過2張及以上的表中去查詢需要的數據。所以我們有必要學習2張及以上的表的查詢。其實不管是幾張表的查詢，都是有規律可循的。
    • 準備數據在備註中
    • 練習1:查詢所有員工信息。顯示員工編號, 員工姓名, 工資, 職務名稱, 職務描述
    • 練習2:查詢所有員工信息。顯示員工編號, 員工姓名, 工資, 職務名稱, 職務描述, 部門名稱, 部門位置
    • 練習3:查詢經理的信息。顯示員工姓名, 工資, 職務名稱, 職務描述, 部門名稱, 部門位置, 工資等級
    • 練習4:查詢出部門編號、部門名稱、部門位置、部門人數
    • 練習5:列出所有員工的姓名及其直接上級的姓名, 沒有上級領導的員工也需要顯示,顯示自己的名字和領導的名字
    • 練習6:查詢出所有的普通員工
    • 練習7:查詢工資高於公司平均工資的所有員工信息。顯示員工id, 員工姓名, 員工工資, 部門名稱, 工資等級

4、資料庫事務

4.1、事務簡介

  • 資料庫的事務（Transaction）是一種機制、一個操作序列，包含了一組資料庫操作命令

  • 事務把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組資料庫命令要麼同時成功，要麼同時失敗；

  • 事務是一個不可分割的工作邏輯單元

  • 事務的使用

    • -- 開啟事務
START TRANSACTION; -- 或者 BEGIN; 效果一樣
-- 提交事務
COMMIT;
-- 回滾事務
ROLLBACK;

  • 事務Demo

4.2、轉賬事務案例

  • 演示手動提交事務
    • 模擬張三給李四轉500元錢（成功）
      • 在DOS命令行執行以下SQL語句： 1.開啟事務 2.張三賬號-500 3.李四賬號+500
      • 在DOS命令行執行commit：提交事務
      • 使用SQLYog查看資料庫：發現數據改變
    • 模擬張三給李四轉500元錢（失敗）
      • 在DOS命令行執行以下SQL語句：1.開啟事務， 2.張三賬號-500
      • 在DOS命令行執行rollback回滾事務
      • 使用SQLYog查看資料庫：發現數據沒有改變

4.3、事務的四大特性

  • 原子性（Atomicity）
    • 事務是不可分割的最小操作單位，要麼同時成功，要麼同時失敗
  • 一致性（Consistency）
    • 事務前後數據的完整性必須保持一致
  • 隔離性（Isolation）
    • 指多個事務併發訪問資料庫時，一個事務不能被其他的事務所干擾，多個併發事務之間數據要相互隔離，不能互相影響
  • 持久性（Durability）
    • 事務一旦提交或回滾，它對數據中的數據的改變就是永久的

4.4、自動提交事務

  • 在沒有手動開啟的情況下，每條增刪改語句執行完畢自動提交事務，MySQL預設開始自動提交事務
  • 查看MySQL是否開啟自動提交事務
    • SELECT @@autocommit;
      • 0：關閉自動提交
      • 1：開啟自動提交
  • 關閉自動提交事務
    • set autocommit = 0;
  • 關閉事務後的案例
    • 在控制台執行以下SQL語句：張三-500
    • 使用SQLYog查看資料庫，發現數據並沒有改變
    • 在控制台執行commit提交任務
    • 使用SQLYog查看資料庫，發現數據改變

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

-Advertisement-
Play Games
更多相關文章
  • C++實現ETW進行進程變動監控
    C++實現ETW進行進程變動監控 文章地址:https://www.cnblogs.com/Icys/p/EtwProcess.html 何為Etw ETW(Event Tracing for Windows)提供了一種對用戶層應用程式和內核層驅動創建的事件對象的跟蹤記錄機制。為開發者提供了一套快速 ...
  • java版VR全景漫游製作平臺- 2介紹
    為大家帶來一款適用於MacOS的應用加密軟體Cisdem AppCrypt Mac，只需設置密碼並將應用程式和網站添加到鎖定列表中，沒有人能夠在沒有正確密碼的情況下訪問受保護的應用程式和網站,在假期孩子們需要使用電腦上網課的時候，也可以使用它防止孩子自製力不好瀏覽其他應用或網站的情況。 詳情：Cis ...
  • STM32與物聯網01-ESP8266基本操作
    Screenflow mac是一款優秀的屏幕錄像軟體，使用它不僅可以完成Mac電腦桌面操作的視頻錄製，還可以根據自己的需求進行後期剪輯處理，可以進行視頻分割、添加背景音樂、文字，改變視頻播放速率等操作，功能強大，使用非常方便。 詳情：ScreenFlow for mac(屏幕錄像軟體) 新增特性 1 ...
  • ExecStart=/usr/bin/dockerd (code=exited, status=1/FAILURE)
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 問題：搭建私庫認證不通過x509: certificate signed by unknown authority 首先確保配置harbor私庫地址 [root@master harbor]# grep hostname harbor.cfg # ...
  • 演算法工程師的開發基礎-Linux的常用Shell命令
    一 Linux操作系統和Shell 簡介 操作系統（Operating Systems, OS）實際上是一種用於電腦的軟、硬體資源管理調度的系統級軟體，它的主體是內核（Kernel），其主要負責進程管理、記憶體管理、文件管理和外設管理等功能，而它也向外界提供了內核的介面即系統調用（System Ca ...
  • MVCC - Read View的可見性判斷理解
    讀了 @SnailMann大佬【MySQL筆記】正確的理解MySQL的MVCC及實現原理 收益頗豐，非常感謝！ 但對其中如何判斷事務是否可見性還是不太理解，於是作了本文，在原博客基礎上，舉例畫圖論證、理解了**Read View**的可見性判斷。 引用 @SnailMann大佬【MySQL筆記】正確 ...
  • Redis 漸進集群介紹
    redis 憑藉著強大的功能和可靠的穩定性，應用場景越來越廣。逐漸成為軟體開發工程師必備的技能之一。 本篇文章，暫不做基本功能的介紹。直接教大家如何部署redis集群。 集群演進主要分為2部分。 ##一、主從備份機制 一個redis 主服務可以擁有多個從服務;一個從伺服器，只可擁有一個主服務。從服務 ...
  • ClickHouse(04)如何搭建ClickHouse集群
    ClickHouse集群的搭建和部署和單機的部署是類似的，主要在於配置的不一致，如果需要瞭解ClickHouse單機的安裝設部署，可以看看這篇文章,ClickHouse(03)ClickHouse怎麼安裝和部署。 ClickHouse集群部署流程大概如下： 環境準備 在每台機器上安裝單機版Click ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類