MySQL中的視圖特性和用戶權(quán)限管理詳解

更新時(shí)間：2026年05月21日 09:54:48 作者：思麟呀

文章詳細(xì)介紹了MySQL視圖和用戶管理與權(quán)限控制,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧

1：MySQL視圖

1：什么是視圖

視圖是一個(gè)虛擬表，其內(nèi)容由一條 SELECT 查詢語(yǔ)句定義。它本身不存儲(chǔ)數(shù)據(jù)，所有數(shù)據(jù)都來(lái)自于底層的 "基表"。

核心特性：數(shù)據(jù)雙向聯(lián)動(dòng)

修改視圖中的可更新數(shù)據(jù)，會(huì)直接影響基表
基表的數(shù)據(jù)發(fā)生變化，視圖的查詢結(jié)果也會(huì)實(shí)時(shí)更新

主要作用：

簡(jiǎn)化復(fù)雜的多表查詢，將常用查詢封裝為視圖
隱藏基表的敏感字段（如密碼、薪資），提升數(shù)據(jù)安全性
實(shí)現(xiàn)數(shù)據(jù)隔離，不同用戶只能看到自己權(quán)限內(nèi)的數(shù)據(jù)

2：視圖基本操作

1：創(chuàng)建員工表

-- 創(chuàng)建部門表
CREATE TABLE DEPT (
    deptno INT PRIMARY KEY,
    dname VARCHAR(20),
    loc VARCHAR(20)
);
-- 創(chuàng)建員工表
CREATE TABLE EMP (
    empno INT PRIMARY KEY,
    ename VARCHAR(20),
    job VARCHAR(20),
    mgr INT,
    hiredate DATE,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT,
    FOREIGN KEY (deptno) REFERENCES DEPT(deptno)
);
-- 插入測(cè)試數(shù)據(jù)
INSERT INTO DEPT VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

2：創(chuàng)建視圖并查詢

-- 創(chuàng)建視圖
CREATE VIEW v_ename_dname AS
SELECT e.ename, d.dname
FROM EMP e
JOIN DEPT d ON e.deptno = d.deptno;
-- 查詢視圖（和查詢普通表語(yǔ)法完全一致）
SELECT * FROM v_ename_dname ORDER BY dname;

視圖本質(zhì)是保存了一條 SELECT 語(yǔ)句，每次查詢視圖都會(huì)執(zhí)行這條語(yǔ)句并返回結(jié)果。

3：修改視圖數(shù)據(jù)，驗(yàn)證對(duì)基表的影響

-- 先查看基表中CLARK的信息
SELECT ename FROM EMP WHERE ename = 'CLARK'; -- 結(jié)果：CLARK
-- 修改視圖中的數(shù)據(jù)
UPDATE v_ename_dname SET ename = 'TEST' WHERE ename = 'CLARK';
-- 再次查看基表
SELECT ename FROM EMP WHERE ename = 'CLARK'; -- 無(wú)結(jié)果
SELECT ename FROM EMP WHERE ename = 'TEST';  -- 結(jié)果：TEST

可更新視圖的修改會(huì)直接作用于底層基表。

4：修改基表數(shù)據(jù)，驗(yàn)證對(duì)視圖的影響

-- 修改基表中JAMES的部門編號(hào)
UPDATE EMP SET deptno = 10 WHERE ename = 'JAMES';
-- 查詢視圖中JAMES的部門
SELECT * FROM v_ename_dname WHERE ename = 'JAMES';

基表數(shù)據(jù)變化會(huì)實(shí)時(shí)反映在視圖中，因?yàn)橐晥D是動(dòng)態(tài)查詢的。

5：刪除視圖

-- 刪除視圖
DROP VIEW v_ename_dname;
-- 驗(yàn)證刪除
SHOW TABLES; -- 視圖不再出現(xiàn)在列表中

3：視圖的規(guī)則與限制

命名唯一：視圖名不能和已有表或視圖重名
性能影響：基于復(fù)雜查詢創(chuàng)建的視圖，每次查詢都會(huì)執(zhí)行復(fù)雜 SQL，注意性能優(yōu)化
無(wú)索引 / 觸發(fā)器：視圖不能創(chuàng)建索引，也不能關(guān)聯(lián)觸發(fā)器或設(shè)置默認(rèn)值
權(quán)限控制：可以給不同用戶分配不同視圖的權(quán)限，實(shí)現(xiàn)數(shù)據(jù)隔離
ORDER BY 覆蓋：如果查詢視圖時(shí)也指定了 ORDER BY，視圖定義中的 ORDER BY 會(huì)被覆蓋
可更新限制：包含以下內(nèi)容的視圖不可更新：
- 聚合函數(shù)（SUM、COUNT、AVG 等）
- GROUP BY、HAVING、UNION、DISTINCT
- 子查詢、多表 JOIN（部分簡(jiǎn)單 JOIN 可更新）

2：MySQL用戶管理與權(quán)限控制

1：為什么需要用戶管理

root 用戶擁有所有權(quán)限，日常使用存在極大安全風(fēng)險(xiǎn)
多用戶協(xié)作場(chǎng)景下，需要給不同角色分配不同權(quán)限（如開發(fā)只能查測(cè)試庫(kù)，運(yùn)維有生產(chǎn)庫(kù)權(quán)限）
遵循最小權(quán)限原則：只給用戶完成工作所需的最小權(quán)限

2：MySQL用戶信息存儲(chǔ)

MySQL 的所有用戶信息都存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù) mysql的user 表中，核心字段如下：

字段	含義
`host`	允許該用戶登錄的主機(jī)地址- `localhost`：只能從本機(jī)登錄- `%`：任意主機(jī)- `192.168.1.%`：指定網(wǎng)段
`user`	用戶名
`authentication_string`	經(jīng)過(guò) password 函數(shù)加密后的用戶密碼
`*_priv`	各種權(quán)限字段（如 Select_priv、Insert_priv 等）

查看所有用戶：

USE mysql;
SELECT host, user, authentication_string FROM user;

3：用戶基本操作

所有實(shí)驗(yàn)均使用 root 用戶登錄 MySQL 執(zhí)行

1：創(chuàng)建新用戶

-- 創(chuàng)建只能從本機(jī)登錄的用戶dev，密碼為Dev@123456
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'Dev@123456';
-- 驗(yàn)證用戶創(chuàng)建成功
SELECT host, user FROM mysql.user WHERE user = 'dev';

常見錯(cuò)誤

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因：MySQL 默認(rèn)開啟密碼復(fù)雜度驗(yàn)證，要求密碼長(zhǎng)度≥8，包含大小寫字母、數(shù)字和特殊字符。解決：使用符合要求的強(qiáng)密碼（生產(chǎn)環(huán)境禁止關(guān)閉密碼驗(yàn)證）。

2：刪除用戶

-- 錯(cuò)誤寫法（默認(rèn)匹配%主機(jī)，會(huì)報(bào)錯(cuò)）
DROP USER dev; -- ERROR 1396 (HY000): Operation DROP USER failed for 'dev'@'%'
-- 正確寫法（必須完整指定'用戶名'@'主機(jī)'）
DROP USER 'dev'@'localhost';

MySQL 用戶是'用戶名'@'主機(jī)'的組合，兩者共同唯一標(biāo)識(shí)一個(gè)用戶。

3：修改用戶密碼

-- 1. 用戶自己修改自己的密碼
SET PASSWORD = PASSWORD('NewDev@123456');
-- 2. root用戶修改指定用戶的密碼
SET PASSWORD FOR 'dev'@'localhost' = PASSWORD('RootSet@123456');

退出 MySQL，使用新密碼登錄 dev 用戶。

4：權(quán)限管理

1：給用戶授權(quán)

-- 1. 授予dev用戶test庫(kù)所有表的查詢權(quán)限
GRANT SELECT ON test.* TO 'dev'@'localhost';
-- 2. 授予dev用戶test庫(kù)所有表的增刪改查權(quán)限
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'dev'@'localhost';
-- 3. 授予所有庫(kù)所有權(quán)限（生產(chǎn)環(huán)境絕對(duì)禁止?。?
-- GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost';
-- 查看用戶權(quán)限
SHOW GRANTS FOR 'dev'@'localhost';
-- 權(quán)限生效（如果授權(quán)后不生效，執(zhí)行此命令）
FLUSH PRIVILEGES;

登錄

-- 查看數(shù)據(jù)庫(kù)（應(yīng)該能看到test庫(kù)）
SHOW DATABASES;
-- 查詢test庫(kù)的表
USE test;
SHOW TABLES;
-- 執(zhí)行查詢（成功）
SELECT * FROM account;
-- 執(zhí)行刪除（如果只給了SELECT權(quán)限，會(huì)報(bào)錯(cuò)）
DELETE FROM account; -- ERROR 1142 (42000): DELETE command denied to user 'dev'@'localhost' for table 'account'

2：回收用戶權(quán)限

-- 回收dev用戶對(duì)test庫(kù)的所有權(quán)限
REVOKE ALL ON test.* FROM 'dev'@'localhost';
-- 驗(yàn)證：dev用戶執(zhí)行show databases，只能看到information_schema庫(kù)
-- 查看權(quán)限
SHOW GRANTS FOR 'dev'@'localhost'; -- 顯示 GRANT USAGE ON *.* TO 'dev'@'localhost'（USAGE表示無(wú)任何權(quán)限）

5：常見權(quán)限說(shuō)明

權(quán)限類別	權(quán)限名稱	說(shuō)明
數(shù)據(jù)操作	SELECT	查詢數(shù)據(jù)
	INSERT	插入數(shù)據(jù)
	UPDATE	更新數(shù)據(jù)
	DELETE	刪除數(shù)據(jù)
結(jié)構(gòu)操作	CREATE	創(chuàng)建數(shù)據(jù)庫(kù)、表、索引
	DROP	刪除數(shù)據(jù)庫(kù)、表、視圖
	ALTER	修改表結(jié)構(gòu)
視圖相關(guān)	CREATE VIEW	創(chuàng)建視圖
	SHOW VIEW	查看視圖定義
管理權(quán)限	CREATE USER	創(chuàng)建用戶
	GRANT OPTION	將自己的權(quán)限授予其他用戶
	SHOW DATABASES	查看所有數(shù)據(jù)庫(kù)