MySQL 高級

1、約束

1.1、約束介紹

什么是約束
- 對表中的資料進行限定，保證資料的正確性、有效性、完整性
約束型別
- 約束說明
  
  PRIMARY KEY 主鍵約束
  
  UNIQUE 唯一約束
  
  NOT NULL 非空約束
  
  DEFAULT 默認值約束
  
  FOREIGN KEY 外鍵約束
  
  CHECK 檢查約束（MySQL并不支持）
注意事項
- MySQL不支持檢查約束
- 約束通常是在創建表結構的時候創建
  - 如果在創建表結構的時候沒增加約束，后續再添加約束的話，有可能會導致垃圾資料的進入

約束	說明
PRIMARY KEY	主鍵約束
UNIQUE	唯一約束
NOT NULL	非空約束
DEFAULT	默認值約束
FOREIGN KEY	外鍵約束
CHECK	檢查約束（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查看資料庫，發現資料改變

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499191.html

標籤：其他

上一篇：開源交流丨批流一體資料集成工具ChunJun同步Hive事務表原理詳解及實戰分享

下一篇：ClickHouse(04)如何搭建ClickHouse集群

02-MySQL高級

MySQL 高級

1、約束

1.1、約束介紹

1.2、主鍵約束

1.3、非空約束

1.4、唯一約束

1.5、默認約束

1.6、外鍵約束

1.6.1、使用外鍵約束的意義

1.6.2、外鍵約束的概念

1.6.3、外鍵約束的使用

2、資料庫設計（范式）

2.1、資料庫設計簡介

2.2、表關系

2.2.1、表關系之一對多

2.2.2、表關系之多對多

2.2.3、表關系之一對一

2.3、資料庫設計案例

3、MySQL多表查詢

3.1、MySQL多表查詢介紹

3.2、表連接笛卡爾積現象

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

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

3.4.1、左外查詢

3.4.2、右外查詢

3.5、多表查詢之子查詢

3.6、多表查詢案例

4、資料庫事務

4.1、事務簡介

4.2、轉賬事務案例

4.3、事務的四大特性

4.4、自動提交事務