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
  • 移動開發（一）：使用.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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類