MySQL 基礎

MySQL 基礎
- SQL 介紹
- DDL（資料定義語言）
  - 資料庫操作
  - 資料表操作
  - 常用約束
    - 約束操作
  - 索引操作
- DML（資料操作語言）
  - SELECT - 從資料庫表中獲取資料
  - UPDATE - 更新資料庫表中的資料
  - DELETE - 從資料庫表中洗掉資料
  - INSERT INTO - 向資料庫表中插入資料
    - 常規插入
    - 插入查詢結果集
  - MySQL 計算列（虛擬列）
- DCL（資料控制語言）
  - GRANT - 授權
  - REVOKE - 回收授權

CURD 是一個資料庫技術中的縮寫詞，一般的專案開發的各種引數的基本功能都是CURD，作用是用于處理資料的基本原子操作，它代表創建（Create）、更新（Update）、讀取（Retrieve）和洗掉（Delete）操作，

SQL 介紹

SQL (Structured Query Language:結構化查詢語言) 是用于管理關系資料庫管理系統（RDBMS）， SQL 的范圍包括資料插入、查詢、更新和洗掉，資料庫模式創建和修改，以及資料訪問控制，

SQL 是什么？
- SQL 指結構化查詢語言
- SQL 使我們有能力訪問資料庫
- SQL 是一種 ANSI 的標準計算機語言
  
  note： ANSI（American National Standards Institute），美國國家標準化組織
SQL 能做什么？
- SQL 面向資料庫執行查詢
- SQL 可從資料庫取回資料
- SQL 可在資料庫中插入新的記錄
- SQL 可更新資料庫中的資料
- SQL 可從資料庫洗掉記錄
- SQL 可創建新資料庫
- SQL 可在資料庫中創建新表
- SQL 可在資料庫中創建存盤程序
- SQL 可在資料庫中創建視圖
- SQL 可以設定表、存盤程序和視圖的權限
名詞解釋
- SQL（Structured Query Language）結構化查詢語言, 分為 DDL, DML和 DCL，
- DDL（Data Definition Language）資料庫定義語言，宣告用于定義資料庫結構或模式，主要包括三個關鍵字： CREATE, ALTER, DROP, 主要操作物件有資料庫、表、索引、視圖等，
- DML（Data Manipulation Language）資料操縱語言，用于管理模式物件中的資料，以 INSERT, UPDATE, DELETE 三種指令為核心，分別代表插入、更新與洗掉，
- DQL（Data Query Language, DQL）資料查詢語言，是SQL語言中，負責進行資料查詢而不會對資料本身進行修改的陳述句，這是最基本的SQL陳述句，保留字SELECT是DQL（也是所有SQL）用得最多的動詞，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY，這些DQL保留字常與其他型別的SQL陳述句一起使用，
- DCL（Data Control Language）資料庫控制語言，由 GRANT 和 REVOKE 兩個指令組成；DCL以控制用戶的訪問權限為主，GRANT為授權陳述句，對應的REVOKE是撤銷授權陳述句，
- TCL（Transaction Control Language）事務控制語言，
- RDBMS（Relational Database Management System）關系型資料庫管理系統，

DDL（資料定義語言）

用于定義SQL模式、基本表、視圖和索引的創建和洗掉操作，

資料庫操作

CREATE DATABASE - 創建新資料庫

CREATE DATABASE [IF NOT EXISTS] db_name;

Tips: IF NOT EXISTS 判斷資料庫是否存在，不存在則創建資料；存在則忽略創建陳述句，不再創建資料庫，
DROP DATABASE - 洗掉資料庫

DROP DATABASE [IF EXISTS] db_name;

Tips: IF EXISTS 判斷資料庫是否存在，存在則洗掉，不存在就結束，不會報錯，（沒有該關鍵字，洗掉不存在的資料庫時會報錯）

資料表操作

CREATE TABLE - 創建新表

CREATE TABLE [IF NOT EXISTS] table_name(col1 type,col2 type);

Tips: IF NOT EXISTS 判斷當前資料庫中是否存在該表，不存在則創建資料表；存在在則忽略建表陳述句，不再創建資料表，
```
CREATE TABLE [IF NOT EXISTS] 表名(
  欄位名1 資料型別 [約束條件] [默認值],
  欄位名2 資料型別 [約束條件] [默認值],
  欄位名3 資料型別 [約束條件] [默認值],
  ...
) [chrset="字符編碼"];
```
ALTER TABLE - 變更（改變）資料庫表
- 修改表名字
  
  ALTER TABLE old_table RENAME AS new_table;
- 新增欄位，并排在某一欄位后面
  
  ALTER TABLE 表名 ADD [COLUMN] 欄位名資料型別 [完整性約束條件…] [FIRST | AFTER 欄位名];
- 修改欄位
  - MODIFY 只能修改 -資料型別- 及其 -完整性約束條件-
    
    ALTER TABLE 表名 MODIFY 欄位名新資料型別 [完整性約束條件…];
  - CHANGE 能修改 -欄位名-、-資料型別- 及其 -完整性約束條件-
    
    ALTER TABLE 表名 CHANGE 舊欄位名新欄位名新資料型別 [完整性約束條件…];
- 洗掉欄位
  
  ALTER TABLE table_name DROP [COLUMN] column_name;
DROP TABLE - 洗掉表

DROP TABLE [IF EXISTS] table_name;

Tips: IF EXISTS 判斷當前資料庫中是否存在該表，存在則洗掉資料表；不存在則忽略洗掉陳述句，不再執行洗掉資料表的操作，
TRUNCATE TABLE - 截斷表（清空表）

TRUNCATE TABLE table_name;

常用約束

約束（Constraint）是Microsoft SQL Server 提供的自動保持資料庫完整性的一種方法，定義了可輸入表或表的單個列中的資料的限制條件，在SQL Server 中有5 種約束：主關鍵字約束（Primary Key Constraint）、外關鍵字約束（Foreign Key Constraint）、惟一性約束（Unique Constraint）、檢查約束（Check Constraint）和預設約束（Default Constraint）

主鍵約束 (Primary Key Constraint) 要求主鍵列唯一，并且不允許為空
唯一約束 (Unique Constraint) 要求該列唯一，允許為空，但只能出現一個空值
檢查約束 (Check Constraint) 某列取值范圍限制、格式限制等，（如：年齡，性別）
默認約束 (Default Constraint) 某列的默認值（如：男性學員比較多，性別默認設為男）
外鍵約束 (Foreign Key Constraint) 用于在兩表之間建立關系，需要指定參考主表的哪一列

Tips:
- 主鍵約束：指定表的 一列或幾列 的組合的值在表中具有惟一性，即能惟一地指定一行記錄，且IMAGE 和TEXT 型別的列不能被指定為主關鍵字，也不允許指定主關鍵字列有NULL 屬性，
- 多列組成的主鍵叫 聯合主鍵，而且聯合主鍵約束只能設定為表級約束；單列組成的主鍵，既可設定為列級約束，也可以設定為表級約束，
- 唯一約束：指定 一個或多個列 的組合的值具有惟一性，以防止在列中輸入重復的值，惟一性約束指定的列可以有NULL 屬性，由于主關鍵字值是具有惟一性的，因此主關鍵字列不能再設定惟一性約束，惟一性約束最多由16 個列組成，
- 檢查約束：對輸入列或整個表中的值設定檢查條件，以限制輸入值，保證資料庫的資料完整性，可以對每個列設定復合檢查，
- 默認約束：通過定義列的預設值或使用資料庫的預設值物件系結表的列，來指定列的預設值，

約束操作

添加約束

ALTER TABLE 表名 ADD CONSTRAINT 約束名約束型別具體的約束條件;
洗掉約束

ALTER TABLE 表名 DROP CONSTRAINT 約束名;
關閉約束

ALTER TABLE 表名 DISABLE CONSTRAINT 約束名 CASCADE;

Tips: 如果沒有被參考則不需 CASCADE 關鍵字
打開約束

ALTER TABLE 表名 ENABLE CONSTRAINT 約束名;

Tips: 打開一個先前關閉的被參考的主鍵約束, 并不能自動打開相關的外部鍵約束

栗子

-- 添加主鍵約束（將 stuNo 作為主鍵）
Alter Table stuInfo
Add Constraint  PK_stuNO primary Key(stuNo);

-- 添加唯一約束（stuID 唯一）
Alter Table stuInfo
Add Constraint UQ_stuID unique(stuID);

-- 添加默認約束（stuAddress 的默認值為 "地址不詳"）
Alter Table stuInfo
Add Constraint DF_stuAddress default('地址不詳') for stuAddress;

-- 添加檢查約束（對 stuAge 加以限制，15~20之間）
Alter Table stuInfo
Add Constraint CK_stuAge check(stuAge between 15 and 20);
Alter Table stuInfo
Add Constraint CK_stuSex check(stuSex='男' or stuSex='女');

-- 添加外鍵約束（主表 stuInfo 和從表 stuMarks 建立關系，關聯欄位 stuNo）
Alter Table stuMarks
Add Constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo);

Tips：約束名的命名規則推薦采用 約束型別_約束欄位 的形式

索引操作

索引是一種特殊的查詢表，可以被資料庫搜索引擎用來加速資料的檢索，簡單說來，索引就是指向表中資料的指標，資料庫的索引同書籍后面的索引非常相像，

CREATE INDEX - 創建索引（搜索鍵）
- 單列索引：基于單一的欄位創建
  
  CREATE INDEX index_name ON table_name (column_name);
- 唯一索引：不止用于提升查詢性能，還用于保證資料完整性，（不允許向表中插入任何重復值）
  
  CREATE UNIQUE INDEX index_name ON table_name (column_name);
- 聚簇索引：在表中兩個或更多的列的基礎上建立
  
  CREATE INDEX index_name ON table_name (column1, column2);
DROP INDEX - 洗掉索引

DROP INDEX table_name.index_name;

Tips:

洗掉索引時應當特別小心，資料庫的性能可能會因此而降低或者提高
隱式索引由資料庫服務器在創建某些物件的時候自動生成，（例如，對于主鍵約束和唯一約束，資料庫服務器就會自動創建索引）
索引創建原則：
- 僅在被頻繁檢索的欄位上創建索引;
- 針對大資料量的表創建索引，而不是針對只有少量資料的表創建索引;
- 盡量不要在有大量重復值得欄位上建立索引（比如性別欄位、季度欄位等）
- 不在頻繁進行大批量的更新或者插入操作的表創建索引；
- 不在頻繁操作的列創建索引;

DML（資料操作語言）

資料操縱分成資料查詢和資料更新兩類，資料更新又分成插入、洗掉、和修改三種操作，

SELECT - 從資料庫表中獲取資料

SELECT column_name(s) FROM table_name WHERE condition AND|OR condition;

UPDATE - 更新資料庫表中的資料

常規更新

UPDATE table_name SET col_name1=value1 WHERE condition;
更新日期
- NOW() 函式
  
  UPDATE table_name SET birthday=NOW() WHERE condition;
- STR_TO_DATE() 函式
  
  UPDATE table_name SET birthday=STR_TO_DATE('2022-04-18','%Y-%m-%d') WHERE condition;
把原表（src_table）的值設定到本表（tar_table）中

UPDATE src_table st,tar_table tt SET tt.tar_column=st.src_column WHERE tt.tar_condition | tt.tar_column=st.src_column;

DELETE - 從資料庫表中洗掉資料

請空表

DELETE FROM table_name [WHERE condition];
截斷表

TRUNCATE TABLE table_name;

INSERT INTO - 向資料庫表中插入資料

常規插入

單條記錄插入
- 全表插入
  
  INSERT INTO table_name VALUES (value1, value2, value3, ...);
- 插入指定欄位
  
  INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

批量插入

批量全表插入

INSERT INTO table_name 
VALUES 
  (value1, value2, value3, ...), 
  (value1, value2, value3, ...), 
  ...
  (value1, value2, value3, ...);

批量插入指定欄位

INSERT INTO table_name (column1, column2, column3) 
VALUES 
  (value1, value2, value3), 
  (value1, value2, value3),
  ...
  (value1, value2, value3);

Tips:

全表插入時，值串列中需要為表的每一個欄位指定值，并且值的順序必須和資料表中欄位定義時的順序相同;
插入指定欄位時，在INSERT陳述句中只向部分欄位中插入值，而其他欄位的值為表定義時的默認值；
在 INSERT 子句中隨意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen需要與column1,…columnn 列一一對應，如果型別不同，將無法插入，并且MySQL會產生錯誤，
INSERT陳述句可以同時向資料表中插入多條記錄，插入時指定多個值串列，每個值串列之間用逗號分隔開;

插入查詢結果集

將查詢的結果集插入表中

INSERT INTO tar_table (tar_column1, tar_column2, ...) 
SELECT (src_column1, src_column2, ...) FROM src_table 
[WHERE src_condition];

Tips:

在 INSERT 陳述句中加入子查詢;
不必書寫 VALUES 子句;

MySQL 計算列（虛擬列）

MySQL 5.7 引入了生成列，支持虛擬和存盤兩種型別的生成列;
- 語法:
```
col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED]
  [NOT NULL | NULL] 
  [UNIQUE [KEY]] 
  [[PRIMARY] KEY]
  [COMMENT 'string']
```
  Tips:
  - GENERATED ALWAYS 可以省略;
  - AS (expr) 用于生成計算列值的運算式;
  - VIRTUAL 或 STORED 關鍵字表示是否存盤計算列的值：
    - VIRTUAL：列值不存盤，虛擬列不占用存盤空間，默認設定為 VIRTUAL
    - STORED：在添加或更新行時計算并存盤列值; 存盤列需要存盤空間，并且可以創建索引;
  - 如果運算式的結果型別與欄位定義中的資料型別不同，將會執行隱式的型別轉換
  - 生成列支持NOT NULL、UNIQUE、主鍵、CHECK以及 外鍵 約束，但是不支持 DEFAULT 默認值;
  - Generated column 運算式必須遵循以下規則:
    - 允許使用常量、確定性的內置函式以及運算子；
      - 確定性函式 意味著對于表中的相同資料，多次呼叫回傳相同的結果，與當前用戶無關;
      - 非確定性函式 包括 CONNECTION_ID()、CURRENT_USER()、NOW() 等；
    - 不允許使用存盤函式和自定義函式；
    - 不允許使用存盤程序和函式的引數；
    - 不允許使用變數（系統變數、自定義變數或者存盤程式中的本地變數）；
    - 不允許子查詢；
    - 允許參考表中已經定義的其他生成列；允許參考任何其他非生成列，無論這些列出現的位置在前面還是后面；
    - 不允許使用 AUTO_INCREMENT 屬性；
    - 不允許使用 AUTO_INCREMENT 欄位作為生成列的基礎列；
    - 可以在計算列上創建索引，但不能在 VIRTUAL 型別的計算列上創建聚集索引；
- 栗子
```
-- 創建表 t_test ，其中計算列 sum 的值為 (a + b)
CREATE TABLE t_test (
  a INT NOT NULL,
  b INT NOT NULL,
  sum INT GENERATED ALWAYS AS (a + b) [VIRTUAL]
);

-- 添加計算列 area 值為 (a * b)
ALTER TABLE t_test ADD area INT AS (a * b) STORED;
```
其他 SQL 型別
- 其他 SQL 型別的計算列

DCL（資料控制語言）

DCL 包括對基本表和視圖的授權，完整性規則的描述，事務控制等內容，用于控制不同資料段直接的許可和訪問級別的陳述句，這些陳述句定義了資料庫、表、欄位、用戶的訪問權限和安全級別，

GRANT - 授權

將指定 操作物件 的指定 操作權限 授予指定的 用戶; 發出該 GRANT陳述句的可以是資料庫管理員，也可以是該資料庫物件的創建者;

查詢
- 查看用戶自己權限
  
  SHOW GRANTS;
- 查看其他用戶權限
  
  SHOW GRANTS FOR 'username'@'host';
Tips: host 可以使用通配符 %；如 'user'@'%', 'user'@'192.168.0.%';
授權
- 語法
  
  GRANT 權限 ON 資料庫物件 TO 用戶 [WITH GRANT OPTION];
- 栗子
  - 授予 super用戶所有權限
    
    GRANT ALL [PRIVILEGES] ON . TO username;
  - 授予用戶 INSERT 權限
    
    CREATE INSERT ON db_name.* username@'localhost' IDENTIFIED BY 'newpasswd';
  - 授予用戶 SELECT, UPDATE, DELETE 權限
    
    GRANT SELECT, UPDATE, DELETE ON db_name.* TO username
重繪

flush privileges;

Tips:

WITH GRANT OPTION: 表示是否能傳播其權限；（授權命令是由資料庫管理員使用的）
- 指定 WITH GRANT OPTION，則獲得該權限的用戶可以把這種權限授予其他用戶；但不允許回圈傳授，即被授權者不能把權限在授回給授權者或祖先；
- 未指定，則獲得某種權限的用戶只能自己使用該權限，不能傳播該權限；
在 GRANT 關鍵字之后指定一個或多個特權，如果要授予用戶多個權限，則每個權限都將以逗號分隔(見下表中的特權串列);
指定確定特權應用級別的privilege_level;
- MySQL支持全域(*.*)，資料庫(database.*)，表(database.table)和列級別;
- 如果您使用列權限級別，則必須在每個權限之后使用逗號分隔列的串列;
如果授予權限的用戶已經存在，則GRANT陳述句修改其特權; 如不存在，則GRANT陳述句將創建一個新用戶; 可選的條件 IDENTIFIED BY 允許為用戶設定新密碼;

REVOKE - 回收授權

語法

REVOKE 權限 ON 資料庫物件 FROM 用戶 [CASCADE | RESTRICT];
栗子
- 回收全部權限
  
  REVOKE ALL [PRIVILEGES] ON . FROM username;
- 回收 INSERT 權限
  
  REVOKE INSERT ON db_name.* FROM username;

Tips: CASCADE | RESTRICT 當檢測到關聯的特權時，RESTRICT(默認值) 導致REVOKE失敗；CASCADE 可以回收所有這些關聯的特權；（如U1授權U2, U2授權U3，此時使用級聯（CASCADE）識訓了U2和U3的權限，否則系統將拒絕執行該命令）

更多關于 GRANT和 REVOKE

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

標籤：其他

上一篇：MySQL 基礎

下一篇：優秀的Kafka GUI客戶端、可視化管理工具、監控工具