數據庫的高級特性和新方法
- 數據庫的高級操作
- 數據庫事務
- 用戶權限控制
- 數據的備份與還原
- Binlog運行日志
- 數據庫的新特性
- 窗口函數的使用
閱讀指南：
本文探討了數據庫的高級特性和新方法，詳細介紹了這些高級特性及其操作方式，并涵蓋了一些最新的操作方法。如果讀者感興趣，我們將會在后續的數據庫教程合集里更新更多高級操作內容，歡迎大家與我們一起學習和討論。這篇文章是本系列的最后一篇，感謝大家的閱讀和支持。
合集鏈接：
數據庫詳細基礎教程
數據庫的高級操作
數據庫事務
數據庫事務概述：
-
數據庫事務是一套操作數據命令的有序集合，一個不可分割的工作單位。
-
事務中單個命令不會立刻改變數據庫數據，當內部全部的命令都生效且成功時，才算一次成功，如果有任一任務失敗，可以進行狀態回滾
-
事務喲事務開始與事務結束之間執行的全部數據庫操作組成
事務的作用：
- 為數據庫提供了一個從失敗中恢復到正常狀態的方法
- 當對個應用程序在并發訪問數據庫時，可以有效避免彼此的操作互相干擾
??事務的ACID特性：
- 原子性（Automicity）
? 原子性是指事務是一個不可分割的工作單位，事務中的操作要么都發生，要么都不發生。
- 一致性（Consistency）
? 一致性是指事務內部的操作的狀態前后一致，即成功則都成功，失敗則都失敗。
- 隔離性（Isolation）
? 隔離性是指一個事物的執行不能被其他事物干擾，即一個事物內部的操作即使用的數據對并發的其他事務是隔離的，并發實現的各個事務之間都不能相互干擾。
- 持久性（Durability）
? 持久性是指一個事物一旦被提交，他對數據庫中數據的改變就是永久性的，后面的操作對其不成影響。
事務的開啟，提交，回滾：
前提：
? MySQL默認情況下是自動提交事務的。
? 默認每一條語句都是一個獨立的事務，一旦成功就提交了。語句失敗報錯就回滾。
? 那要將多條語句作為一個事物，有如下方法
方法一：手動提交模式
# 開啟手動提交模式，這個模式的生命周期只在一次服務時間內，也就是說，如果關閉了sql服務，則再次打開時需要再一次開啟，最好在終端進行操作
SET AUTOCOMMIT = FALSE;
或
SET AUTOCOMMIT = 0;# 恢復自動提交
SET AUTOCOMMIT = TRUE;
或
SET AUTOCOMMIT = 1;# 查看是否自動提交
SHOW VARIABLES LIKE 'AUTOCOMMIT';# 成功 —> 提交，失敗 -> 回滾COMMIT; ROLLBACK;
方法二： 自動提交模式下開啟獨立事務
START TRANSACTION; # 開啟獨立事務.......... # 多個sql命令COMMIT; 或 ROLLBACK;
注：
事務只支持在update，insert，delete這類對數據產生變化的方法中，不支持刪表，刪庫那種操作。
事務的隔離性：
? 一個書屋內部的操作即使用的數據對并發的其他事物是隔離的，并發執行的各事務之間不能相互干擾
| 隔離級別 | 概述 | 臟讀 | 不可重復讀 | 幻讀 |
|---|---|---|---|---|
| read-uncommitted | 讀未提交事務數據 | √ | √ | √ |
| read-committed | 讀已提交事務數據（orcle默認） | × | √ | √ |
| repeatable-read | 可重復度（MySQL默認） | × | × | √（小概率） |
| aseializable | 串行化和序列化 | × | × | × |
臟讀：一個事務讀取了另一個事務未提交的數據
不可重復讀取：一個事務讀取了另一個事務所提交的修改數據，不符合數據的一致性
幻讀：一個事務讀取了另一個事務新增，刪除的記錄情況，與實際情況不同。
# 改變隔離級別
SET TRANSACTION_ISOLATION = '隔離級別';# 查看隔離級別
SELEC T@@TRANSACTION_ISOLATION;
注：
隔離等級越高，越安全，但是性能越低。
用戶權限控制
? 授予的權限可分為三個級別：數據庫級別，表級別或特定操作上。
創建用戶語法：
CREATE USER 'username' @ '服務器ip地址（%代表所有ip）' IDENTIFIED BY 'password';
# username 表示要創建的用戶的用戶名
# password 表示創建的用戶的用戶名
賦予權限語法：
# 1. 賦予全部權限
GRANT ALL PRIVIEGES ON 庫名(*代表全部).表名(*代表全部) TO 'username' @ '服務器ip地址（%代表所有ip）';
# 2.指定庫和權限
GRANT SELECT, INSERT ON 庫名.表名 TO 'username' @ '服務器ip地址（%代表所有ip）';
回收權限語法：
# 撤銷全部權限
REMOVE ALL PRIVILEGES ON database_name.* FROM 'username' @ 'IP';
# 撤銷部分權限
REMOVE SELECT, INSERT,UPDATE ON database_name.table_name FROM 'username' @ 'IP';
查看權限語法：
# 查看權限
SHOW GRANTS FOR 'username' @ 'IP';# 查看有用戶列表
SELECT User, Host FROM mysql.user;
刪除用戶語法：
# 刪除用戶
DROP USER '用戶名';
數據的備份與還原
全量備份
# 備份單庫和單表的數據
mysqldump -u username -p database_name 表名>backup.sql# 備份單庫和多表
mysqldump -u username -p database_name 表名1 表名2...>backup.sql# 備份單庫的所有表
mysqldump -u username -p database_name > backup.sql#-p如果寫密碼，必須緊貼著
# 以上命令必須在未連mysql的狀態下執行（CMD執行）
全量恢復
# 還原數據
mysql -u username -p database_name < backup.sql# 需要提前準備數據庫，導入已存在的數據庫，同時，數據庫的MySQ版本要兼容
Binlog運行日志
? Binlog日志是一個MySQL的二進制的日志記錄，里面記錄了數據庫所有的增刪改查的操作，同時也包括了操作的執行時間，所以，我們可以通過該日志查詢誤刪除數據的恢復，增量復制，主從同步等。
Binlog配置文件的地址
? 默認在：MySQL服務/my.ini
? 其中有一部分是
[mysqld]
……
datadir = MySQL服務/Data # 默認的存儲詳細Binlog的二進制文件地址
……
log-bin = Binlog的文件名 # 此處Binlog的文件名默認是以電腦名稱命名，可以修改
對日志的操作（以下操作在 SQLyog 中使用）：
示例：
# 清空原有日志文件
RESET MASTER;# 準備數據，插入數據 --> 00000001日志文件
CREATE DATABASE text_binlog;
USE text_binlog;
CREATE TABLE table_binlog(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL
);
INSERT INTO table_binlog (NAME) VALUES ('二狗子'),('驢蛋蛋');# 重啟一個新的日志文件 --> 00000002日志文件
FLUSH LOGS; # 創建一個新日志# 將刪除數據和插入數據植入到第二個日志文件中-->00000002日志文件
DELETE FROM table_binlog WHERE id = 2; # 刪除驢蛋蛋
INSERT INTO table_binlog(NAME) VALUES('狗剩子');
SELEC *FROM table_binlog;# 查看日志文件目錄
SHOW BINARY LOGS;# 查蘭某個日志的命令清單
# 語法：
SHOW BINARY EVENTS; 查看第一個日志的清單
SHOW BINARY EVENTS IN '清單名' [FROM pos(具體信息位置) LIMIT OFSET, NUMBER]0;
# 實例：
SHOW BINARY EVENTS IN 'MY_LOGIN.00000002' FROM 391 LIMIT 1, 3;
CMD下的日志操作：
注：在binlog的文件目錄下進行cmd
# 跳過步驟找回數據
mysqlbinlog my-logbin.000001> d:/my_binlog.000001.sql # 將其他的日志完整導出
mysqlbinlog --stop-POSITION=刪除命令的開始的pos my-logbin.000002> d:/my_binlog.391.sql # 02日志刪除之前
mysqlbinlog --start-POSITION=刪除命令的下一個命令開始pos my-logbin.000002> d:/my_binlog.441.sql # 02日志刪除之后
數據庫的新特性
窗口函數的使用
窗口函數：
-
序號函數: row_number()
-
序號函數: rank()
-
序號函數: dense_rank()
-
分步函數: PERCENT_RANK()
(rank - 1) 1 / (rows - 1)
-
前后函數: LAG(expr,n)
LAG(expr,n)函數返回當前行的前n行的expr的值。
-
首尾函數: FIRST_VALUE(expr)
FIRST_VALUE(expr)函數返回第一個expr的值。
示例：
數據的準備
CREATE TABLE goods(id INT PRIMARY KEY AUTO_INCREMENT,category_id INT,category VARCHAR(15),NAME VARCHAR(30),price DECIMAL(10,2),stock INT,upper_time DATETIME
);INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女裝/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '連衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '衛衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '牛仔褲', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '呢絨外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '戶外運動', '自行車', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '戶外運動', '山地自行車', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '戶外運動', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '戶外運動', '騎行裝備', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '戶外運動', '運動外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '戶外運動', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
使用聚合函數的方式
# 聚合函數也是窗口函數 [理解]
# 查詢所有的商品編號,價格,和類別名以及整體平均價格SELECT id , NAME ,price, category , AVG(price) OVER () FROM goods;SELECT id , NAME , price , category , AVG(price) OVER() FROM goods ;# 查詢所有的商品編號,價格,和類別名以及類別平均價格
SELECT id , NAME ,price, category , AVG(price) OVER (PARTITION BY category) FROM goods;SELECT id , NAME , price , category , AVG(price) OVER(PARTITION BY category_id) FROM goods ;
使用窗口函數：
# 查詢 goods 數據表中每個商品分類下價格降序排列的各個商品信息。
SELECT ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) AS num,id , category , NAME ,price FROM goods;SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id,category_id, category, NAME, price, stock FROM goods;
# 窗口函數 over (partition by 分組 order by 排序 )# 查詢 goods 數據表中每個商品分類下價格最高的3種商品信息。
SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock FROM goods) tWHERE row_num <= 3;# 使用RANK()函數獲取 goods 數據表中各類別的價格從高到低排序的各商品信息。
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stockFROM goods;# 使用RANK()函數獲取 goods 數據表中類別為“女裝/女士精品”的價格最高的4款商品信息SELECT * FROM(SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stockFROM goods) tWHERE category_id = 1 AND row_num <= 4;# 使用DENSE_RANK()函數獲取 goods 數據表中各類別的價格從高到低排序的各商品信息。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock FROM goods;# 使用DENSE_RANK()函數獲取 goods 數據表中類別為“女裝/女士精品”的價格最高的4款商品信息。
SELECT * FROM( SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock FROM goods) t WHERE category_id = 1 AND row_num <= 3;# 計算 goods 數據表中名稱為“女裝/女士精品”的類別下的商品的PERCENT_RANK值。
# 寫法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,PERCENT_RANK()
OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,id, category_id, category,
NAME, price, stock FROM goods WHERE category_id = 1;# 寫法二:
SELECT RANK() OVER w AS r,PERCENT_RANK() OVER w AS pr,id, category_id, category, NAME, price, stockFROM goodsWHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);# 查詢goods數據表中前一個商品價格與當前商品價格的差值。
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_priceFROM (SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price FROM goodsWINDOW w AS (PARTITION BY category_id ORDER BY price)) t;# 按照價格排序，查詢第1個商品的價格信息。
SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS first_priceFROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
![[MQTT]Mosquitto的權限管理_使用者/密碼(pwfile)和訪問控制清單(aclfile)](http://pic.xiahunao.cn/[MQTT]Mosquitto的權限管理_使用者/密碼(pwfile)和訪問控制清單(aclfile))
![從0開始學習pyspark--pyspark的核心概念[第0節]](http://pic.xiahunao.cn/從0開始學習pyspark--pyspark的核心概念[第0節])
