當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL(三)——函数、事务（ACID）、索引、权限管理和备份、数据库三大范式

發(fā)布時間：2025/3/13 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL(三)——函数、事务（ACID）、索引、权限管理和备份、数据库三大范式小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

1. MySQL函數
- 1.1 常用函數
- 1.2 聚合函數（常用）
- 1.3 數據庫級別的MD5加密
2. 事務
- 2.1 簡述
- 2.2 事務原則
- 2.3 隔離所導致的一些問題
- 2.4 執(zhí)行事務
- 2.5 模擬場景
3. 索引
- 3.1 索引的分類
- 3.2 測試索引
- 3.3 索引原則
4. 權限管理和備份
- 4.1 用戶管理
- 4.2 MySQL備份
5. 規(guī)范數據庫設計
- 5.1 設計的原因
- 5.2 三大范式

1. MySQL函數

官網：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

1.1 常用函數

數學運算

SELECT ABS(-8) -- 絕對值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 返回一個 0~1 之間的隨機數 SELECT SIGN(10) -- 判斷一個數的符號 0-0 負數返回-1，正數返回 1

字符串函數

SELECT CHAR_LENGTH('即使再小的帆也能遠航') -- 字符串長度 SELECT CONCAT('我','愛','你們') -- 拼接字符串 SELECT INSERT('我愛編程helloworld',1,2,'超級熱愛') -- 查詢，從某個位置開始替換某個長度 SELECT LOWER('Dalao') -- 小寫字母 SELECT UPPER('Dalao') -- 大寫字母 SELECT INSTR('Dalao','a') -- 返回第一次出現的子串的索引 SELECT REPLACE('大佬說堅持就能成功','堅持','努力') -- 替換出現的指定字符串 SELECT SUBSTR('大佬說堅持就能成功',4,6) -- 返回指定的子字符串 (源字符串,截取的位置，截取的長度) SELECT REVERSE('我是年少的歡喜') -- 反轉

時間和日期函數（記住）

SELECT CURRENT_DATE() -- 獲取當前日期 SELECT CURDATE() -- 獲取當前日期 SELECT NOW() -- 獲取當前的時間 SELECT LOCALTIME() -- 本地時間 SELECT SYSDATE() -- 系統(tǒng)時間 SELECT YEAR(NOW()) SELECT MONTH(NOW()) SELECT DAY(NOW()) SELECT HOUR(NOW()) SELECT MINUTE(NOW()) SELECT SECOND(NOW())

系統(tǒng)

SELECT SYSTEM_USER() SELECT USER() SELECT VERSION()

1.2 聚合函數（常用）

都能夠統(tǒng)計表中的數據（想查詢一個表中有多少個記錄，就使用這個count() ）

SELECT COUNT(`BornDate`) FROM student; -- Count(字段)，會忽略所有的 null 值，即不統(tǒng)計null值 SELECT COUNT(*) FROM student; -- Count（*），不會忽略 null 值，本質計算行數 SELECT COUNT(1) FROM result; -- Count（1），不會忽略所有的 null 值本質計算行數 SELECT SUM(`StudentResult`) AS 總和 FROM result SELECT AVG(`StudentResult`) AS 平均分 FROM result SELECT MAX(`StudentResult`) AS 最高分 FROM result SELECT MIN(`StudentResult`) AS 最低分 FROM result

1.3 數據庫級別的MD5加密

（1）什么是MD5？
主要增強算法復雜度和不可逆性。
MD5 不可逆，具體的值的 md5 是一樣的
MD5 破解網站的原理，背后有一個字典， MD5加密后的值，加密的前值
（2）測試MD5 加密

明文密碼
加密指定密碼
加密全部的密碼
插入的時候加密
如何校驗：將用戶傳遞進來的密碼，進行md5加密，然后比對加密后的值

2. 事務

2.1 簡述

MySQL 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統(tǒng)中，你刪除一個人員，你既需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數據庫操作語句就構成一個事務！

2.2 事務原則

一般來說，事務是必須滿足4個條件（ACID）：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）

原子性（Atomicity）
原子性是指事務是一個不可分割的工作單位，事務中的操作要么都發(fā)生，要么都不發(fā)生。

這個過程包含兩個步驟
A: 800 - 200 = 600
B: 200 + 200 = 400

原子性表示，這兩個步驟一起成功，或者一起失敗，不能只發(fā)生其中一個動作

一致性（Consistency）
事務前后的數據完整性要保證一致

操作前A：800，B：200
操作后A：600，B：400

一致性表示事務完成后，符合邏輯運算，始終都是1000

隔離性（Isolation）
事務的隔離性是多個用戶并發(fā)訪問數據庫時，數據庫為每一個用戶開啟的事務，不能被其他事務的操作數據所干擾，多個并發(fā)事務之間要相互隔離。
持久性（Durability）
事務一旦提交則不可逆，被持久化到數據庫中
表示事務結束后的數據不隨著外界原因導致數據丟失
操作前A：800，B：200
操作后A：600，B：400
如果在操作前（事務還沒有提交）服務器宕機或者斷電，那么重啟數據庫以后，數據狀態(tài)應該為
A：800，B：200
如果在操作后（事務已經提交）服務器宕機或者斷電，那么重啟數據庫以后，數據狀態(tài)應該為
A：600，B：400

2.3 隔離所導致的一些問題

臟讀
指一個事務讀取了另外一個事務未提交的數據。
不可重復讀
在一個事務內讀取表中的某一行數據，多次讀取結果不同。（這個不一定是錯誤，只是某些場合不對）
虛讀(幻讀)
是指在一個事務內讀取到了別的事務插入的數據，導致前后讀取不一致。

2.4 執(zhí)行事務

-- mysql 是默認開啟事務自動提交的 SET autocommit = 0 -- 關閉 SET autocommit = 1 -- 開啟（默認的）-- 手動處理事務 SET autocommit = 0 -- 關閉自動提交 -- 事務開啟 START TRANSACTION -- 標記一個事務的開始，從這個之后的 sql 都在同一個事務內 INSERT xx INSERT xx -- 提交：持久化（成功！） COMMIT -- 回滾：回到的原來的樣子（失敗！） ROLLBACK -- 事務結束 SET autocommit = 1 -- 開啟自動提交 -- 了解 SAVEPOINT 保存點名 -- 設置一個事務的保存點 ROLLBACK TO SAVEPOINT 保存點名 -- 回滾到保存點 RELEASE SAVEPOINT 保存點名 -- 撤銷保存點

2.5 模擬場景

A 2000 SQL 執(zhí)行 A 給 B 轉賬500
B 10000 SQL 執(zhí)行 B 收到 A 的500

3. 索引

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。
提取句子主干，就可以得到索引的本質：索引是數據結構。

3.1 索引的分類

在一個表中，主鍵索引只能有一個，唯一索引可以有多個
（1）主鍵索引（PRIMARY KEY ）
唯一的標識，主鍵不可重復，只能有一個列作為主鍵
（2）唯一索引（UNIQUE KEY）
避免重復的列出現，唯一索引可以重復，多個列都可以標識位唯一索引
（3）常規(guī)索引（KEY/INDEX）
默認的， index、key 關鍵字來設置
（4）全文索引（FullText）
在特定的數據庫引擎下才有，MyISAM
快速定位數據

基礎語法

-- 索引的使用 -- 1、在創(chuàng)建表的時候給字段增加索引 -- 2、創(chuàng)建完畢后，增加索引 -- 顯示所有的索引信息 SHOW INDEX FROM student -- 增加一個全文索引（索引名）列名 ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`); -- EXPLAIN 分析sql執(zhí)行的狀況 EXPLAIN SELECT * FROM student; -- 非全文索引 EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('劉');

3.2 測試索引

CREATE TABLE `app_user` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT '' COMMENT '用戶昵稱', `email` VARCHAR(50) NOT NULL COMMENT '用戶郵箱', `phone` VARCHAR(20) DEFAULT '' COMMENT '手機號', `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性別（0:男；1：女）', `password` VARCHAR(100) NOT NULL COMMENT '密碼', `age` TINYINT(4) DEFAULT '0' COMMENT '年齡', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用戶表'-- 插入100萬數據 DELIMITER $$ -- 寫函數之前必須要寫，標志 CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i<num DO INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用戶',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*((999999999- 100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)); SET i = i+1; END WHILE; RETURN i; END; SELECT mock_data(); SELECT * FROM app_user WHERE `name` = '用戶9999'; -- 0.344 sec EXPLAIN SELECT * FROM app_user WHERE `name` = '用戶9999';-- id _ 表名 _ 字段名 -- CREATE INDEX 索引名 on 表(字段) CREATE INDEX id_app_user_name ON app_user(`name`); SELECT * FROM app_user WHERE `name` = '用戶9999'; -- 0 sec EXPLAIN SELECT * FROM app_user WHERE `name` = '用戶9999';

索引在小數據量的時候，用處不大，但是在處理大數據的時候，區(qū)別十分明顯

3.3 索引原則

索引不是越多越好
不要對進程變動數據加索引
小數據量的表不需要加索引
索引一般加在常用來查詢的字段上！

參考閱讀：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

4. 權限管理和備份

4.1 用戶管理

SQL yog 可視化管理
SQL 命令操作
用戶表： mysql.user
本質：讀這張表進行增刪改查

-- 創(chuàng)建用戶 CREATE USER 用戶名 IDENTIFIED BY '密碼' CREATE USER dalao IDENTIFIED BY '123456' -- 修改密碼（修改當前用戶密碼） SET PASSWORD = PASSWORD('123456') -- 修改密碼（修改指定用戶密碼） SET PASSWORD FOR dalao = PASSWORD('123456') -- 重命名 RENAME USER 原來名字 TO 新的名字 RENAME USER dalao TO dalao2 -- 用戶授權 ALL PRIVILEGES 全部的權限，庫.表 -- ALL PRIVILEGES 除了給別人授權，其他都能夠干 GRANT ALL PRIVILEGES ON *.* TO dalao2 -- 查詢權限 SHOW GRANTS FOR dalao2 -- 查看指定用戶的權限 SHOW GRANTS FOR root@localhost -- ROOT用戶權限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -- 撤銷權限 REVOKE 哪些權限，在哪個庫撤銷，給誰撤銷 REVOKE ALL PRIVILEGES ON *.* FROM dalao2 -- 刪除用戶 DROP USER dalao

練習：創(chuàng)建用戶 CREATE USER 用戶名 IDENTIFIED BY ‘密碼’

4.2 MySQL備份

（1）備份的原因

保證重要的數據不丟失
數據轉移

（2）MySQL 數據庫備份的方式

直接拷貝物理文件
在Sqlyog 這種可視化工具中手動導出
使用命令行導出 mysqldump 命令行使用

# mysqldump -h 主機 -u 用戶名 -p 密碼數據庫表名 > 物理磁盤位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # mysqldump -h 主機 -u 用戶名 -p 密碼數據庫表1 表2 表3 > 物理磁盤位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql # mysqldump -h 主機 -u 用戶名 -p 密碼數據庫> 物理磁盤位置/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql # 導入 # 登錄的情況下，切換到指定的數據庫 # source 備份文件 source d:/a.sql mysql -u用戶名 -p密碼庫名< 備份文件

5. 規(guī)范數據庫設計

5.1 設計的原因

當數據庫比較復雜的時候，我們就需要設計了
（1）糟糕的數據庫設計

數據冗余，浪費空間
數據庫插入和刪除都會麻煩、異常【屏蔽使用物理外鍵】
程序的性能差

（2）良好的數據庫設計

節(jié)省內存空間
保證數據庫的完整性
方便我們開發(fā)系統(tǒng)

（3）軟件開發(fā)中，關于數據庫的設計

分析需求：分析業(yè)務和需要處理的數據庫的需求
概要設計：設計關系圖 E-R 圖

（4）設計數據庫的步驟（個人博客）

收集信息，分析需求
用戶表（用戶登錄注銷，用戶的個人信息，寫博客，創(chuàng)建分類）
分類表（文章分類，誰創(chuàng)建的）
文章表（文章的信息）
評論表
友鏈表（友鏈信息）
自定義表（系統(tǒng)信息，某個關鍵的字，或者一些主字段） key ： value
說說表（發(fā)表心情 … id… content….create_time）
標識實體（把需求落地到每個字段）
標識實體之間的關系
寫博客：user --> blog
創(chuàng)建分類：user –> category
關注：user –>user
友鏈：links
評論：user-user-blog

5.2 三大范式

（1）為什么需要數據規(guī)范化？

信息重復
更新異常
插入異常
無法正常顯示信息
刪除異常
丟失有效的信息

（2）三大范式

第一范式（1NF）
要求屬性不可分割。即保證每一列不可再分（原子性）

第二范式（2NF）
前提：滿足第一范式
要求要有主鍵，要求其他字段都依賴于主鍵，即保證每張表只描述一件事情

不符合第二范式

符合第二范式

第三范式（3NF）
前提：滿足第一范式和第二范式
要求消除傳遞依賴，即保證數據表中的每一列數據都和主鍵直接相關，而不能間接相關

不符合第三范式

（3）規(guī)范性和性能的問題
關聯(lián)查詢的表不得超過三張表

考慮商業(yè)化的需求和目標，（成本，用戶體驗！）數據庫的性能更加重要
在規(guī)范性能的問題的時候，需要適當的考慮一下規(guī)范性！
故意給某些表增加一些冗余的字段。（從多表查詢中變?yōu)閱伪聿樵?#xff09;
故意增加一些計算列（從大數據量降低為小數據量的查詢：索引）

總結

以上是生活随笔為你收集整理的MySQL(三)——函数、事务（ACID）、索引、权限管理和备份、数据库三大范式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： MySQL(二)——DQL数据查询语言
下一篇： JDBC(一)——statement对象