1. 視圖

1.1 視圖的基本概念

視圖是一個(gè)虛擬的表，它是基于一個(gè)或多個(gè)基本表或其他視圖的查詢結(jié)果集。視圖本身不存儲(chǔ)數(shù)據(jù)，而是通過執(zhí)行查詢來動(dòng)態(tài)生成數(shù)據(jù)。用戶可以像操作普通表?樣使用視圖進(jìn)行查詢、更新和管理。視圖本身并不占用物理存儲(chǔ)空間，它僅僅是一個(gè)查詢的邏輯表示，物理上它依賴于基礎(chǔ)表中的數(shù)據(jù)。

1.2 試圖的基本操作

1.2.1 創(chuàng)建視圖

create view 列名1，列名2，列名3 as select 查詢語句...

view 是視圖的關(guān)鍵字

1.2.2 使用視圖

??例如：只查詢用戶的姓名和總分(隱藏學(xué)號(hào)和各科成績(jī))

# 使?真實(shí)表進(jìn)?查詢
select s.name, sum(sc.score) total from student s, score sc 
where s.id = sc.student_id
group by sc.student_id order by s.id;

-- 缺點(diǎn)：可以隨時(shí)在select關(guān)鍵字后加上學(xué)號(hào)和各科成績(jī)字段，會(huì)暴露學(xué)生信息，不安全

# 創(chuàng)建視圖
create view v_student_total_points as
select s.id, s.name, sum(sc.score) total from student s, score sc 
where s.id = sc.student_id 
group by s.id order by s.id;

-- 使用視圖進(jìn)行查詢
select * from v_student_total_points;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 469 |
| 孫悟空 | 179.5 |
| 豬悟能 | 200 |
| 沙悟凈 | 218 |
| 宋江 | 118 |
| 武松 | 178 |
| 李逹 | 172 |
+-----------+-------+
-- 只能查詢出姓名和總分，進(jìn)一步保護(hù)了學(xué)生的個(gè)人信息

??例如：視圖和真實(shí)表進(jìn)行表連接查詢

select * from v_student_total_points v, student s where v.id = s.id;

-- 視圖本質(zhì)上是一張?zhí)摂M的表，所以可以用視圖與真實(shí)表進(jìn)行表連接查詢

1.2.3 修改數(shù)據(jù)

• 通過真實(shí)表修改數(shù)據(jù)，會(huì)影響視圖

# 修改唐三藏的JAVA成績(jī)?yōu)?9分
 update score set score = 99 where student_id = 1 and course_id = 1;

# 查詢視圖，發(fā)現(xiàn)唐三藏這條記錄已被修改
select * from v_student_socre;

• 通過視圖修改數(shù)據(jù)會(huì)影響基表

# 更新視圖
update v_student_socre_v1 set score = 99 where score_id = 3;

# 是看真實(shí)表數(shù)據(jù)已被修改
select * from score where student_id = 1 and course_id = 5;

注意事項(xiàng)：

1. 修改真實(shí)表會(huì)影響視圖，修改視圖同樣也會(huì)影響真實(shí)表

2. 以下視圖不可更新：

-------創(chuàng)建視圖時(shí)使用聚合函數(shù)的視圖
-------創(chuàng)建視圖時(shí)使用 DISTINCT
-------創(chuàng)建視圖時(shí)使用 GROUP BY 以及 HAVING子句
-------創(chuàng)建視圖時(shí)使用 UNION 或 UNION ALL
-------查詢列表中使用子查詢
-------在FROM子句中引用不可更新視圖

1.2.4 刪除視圖

# 語法
drop view 視圖名;

1.3 視圖的優(yōu)點(diǎn)

1. 簡(jiǎn)單性：視圖可以將復(fù)雜的查詢封裝成一個(gè)簡(jiǎn)單的查詢。例如，針對(duì)一個(gè)復(fù)雜的多表連接查詢，可以創(chuàng)建一個(gè)視圖，用戶只需查詢視圖而無需了解底層的復(fù)雜邏輯。
2. 安全性：通過視圖，可以隱藏表中的敏感數(shù)據(jù)。例如，?個(gè)系統(tǒng)的用戶表中，可以創(chuàng)建一個(gè)不包含密碼列的視圖，普通用戶只能訪問這個(gè)視圖，而不能訪問原始表，進(jìn)一步保證了安全問題。
3. 邏輯數(shù)據(jù)獨(dú)立性：視圖提供了一種邏輯數(shù)據(jù)獨(dú)立性，即使底層表結(jié)構(gòu)發(fā)生變化，只需修改視圖定義，而無需修改依賴視圖的應(yīng)用程序。確保了應(yīng)用程序與數(shù)據(jù)庫(kù)的解耦
4. 重命名列：視圖允許用戶重命名列名，以增強(qiáng)數(shù)據(jù)可讀性。

2. 用戶與權(quán)限管理

數(shù)據(jù)庫(kù)服務(wù)安裝成功后默認(rèn)有一個(gè)root用戶，可以新建和操縱數(shù)據(jù)庫(kù)服務(wù)中管理的所有數(shù)據(jù)庫(kù)。在真

實(shí)的使用過程中，通常每個(gè)應(yīng)用對(duì)應(yīng)著一個(gè)數(shù)據(jù)庫(kù)，我們只希望某個(gè)用戶只能操縱和管理當(dāng)前應(yīng)用對(duì)

應(yīng)的那個(gè)數(shù)據(jù)庫(kù)，而不能操縱和管理其他應(yīng)用的數(shù)據(jù)庫(kù)，這時(shí)就可以添加?個(gè)用戶并指定用戶的權(quán)限

如上圖所示：

root 可以訪問和操縱所有的數(shù)據(jù)庫(kù)：DB1, DB2, DB3, DB4
普通用戶1 只能訪問和操縱數(shù)據(jù)庫(kù)DB1
普通用戶2 只能訪問和操縱數(shù)據(jù)庫(kù)DB3
只讀用戶1 只能訪問數(shù)據(jù)庫(kù)DB3
只讀用戶2 只能訪問數(shù)據(jù)庫(kù)DB4

2.1 用戶

2.1.1 查看用戶

-- 選擇數(shù)據(jù)庫(kù)
use mysql;

-- 查看表結(jié)構(gòu)
desc user;

-- 查看用戶表
select * from user;

host： 允許登錄的主機(jī)，相當(dāng)于白名單，如果是localhost，表示只能從本機(jī)登陸
user： 用戶名
*_priv： 用戶擁有的權(quán)限,Y表示有權(quán)限,N表示沒有權(quán)限
authentication_string： 加密后的用戶密碼

2.1.2 創(chuàng)建用戶

create user if  not exists 'user_name'@'host_name' identified by 'auth_string';

• user_name: 用戶名，用單引號(hào)包裹，區(qū)分大小寫
• host_name: 主機(jī)或IP(段)，?單引號(hào)包裹
• auth_string: 真實(shí)密碼，有些密碼策略不允許使用簡(jiǎn)單密碼

例如:創(chuàng)建名為zhuxulong 密碼為123456 的賬戶

create user if  not exists 'zhuxulong'@'172.20.109.85' identified by '123456';

注意事項(xiàng):

• 如果不指定host_name相當(dāng)于’user_name’@‘%’， %表示所有主機(jī)都可以連接到數(shù)據(jù)庫(kù)，強(qiáng)烈建
  議不要這樣設(shè)置，因?yàn)闀?huì)導(dǎo)致嚴(yán)重的安全問題
• user_name和host_name分別用單引號(hào)包裹，如果寫成’user_name@host_name’, 相當(dāng)于’user_name@host_name’@‘%’
• host_name可以通過子網(wǎng)掩碼設(shè)置主機(jī)范圍
  A: 198.0.0.0 : A段網(wǎng)絡(luò)中的任意一臺(tái)主機(jī)
  B: 198.51.0.0 B段網(wǎng)絡(luò)中的任意一臺(tái)主機(jī)’
  C: 198.51.100.0 C段網(wǎng)絡(luò)中的任意一臺(tái)主機(jī)
  D: 198.51.100.1 :只包含特定IP地址的主機(jī)

2.1.3 修改密碼

# 為指定??設(shè)置密碼 【推薦】
ALTER USER 'user_name'@'host_name' IDENTIFIED BY 'auth_string';

# 為指定??設(shè)置密碼
SET PASSWORD FOR 'user_name'@'host_name' = 'auth_string';

# 為當(dāng)前登錄??設(shè)置密碼
SET PASSWORD = 'auth_string';

2.1.4 刪除用戶

DROP USER [IF EXISTS] 'user_name'@'host_name'[, ...];

2.2 權(quán)限與授權(quán)

MySQL內(nèi)置支持的權(quán)限列表

2.2.1 給用戶授權(quán)

剛剛創(chuàng)建的用戶沒有任何權(quán)限,我們需要手動(dòng)為新用戶授權(quán).

grant 權(quán)限名 on priv_level to 'user_name'@'host_name' [WITH GRANT OPTION]

權(quán)限名：根據(jù)類型，參考根據(jù)列表4.1中的Privilege列

• priv_level： * | . | db_name.* | db_name.tbl_name | tbl_name，比如*.*表示所有數(shù)據(jù)庫(kù)下的所有表
• ‘user_name’@‘host_name’：指定用戶
• [WITH GRANT OPTION]：可選，允許用戶將自己的權(quán)限授權(quán)給其它用戶

示例: 為剛剛創(chuàng)建的zhuxulong用戶授予test數(shù)據(jù)庫(kù)student表的select權(quán)限

grant select on  test.student to 'zhuxulong'@'172.20.109.85';

2.2.2 回收用戶授權(quán)

revoke 權(quán)限名  on 數(shù)據(jù)庫(kù)名.表名 from 'user_name'@'host_name';

總結(jié) 

到此這篇關(guān)于MySQL視圖與用戶權(quán)限管理從入門到精通的文章就介紹到這了,更多相關(guān)MySQL視圖與用戶權(quán)限管理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論