1. 含義
虛擬的表戚炫，和普通表一樣使用
mysql5.1版本出現(xiàn)的新特性稠肘，是通過表動(dòng)態(tài)生成的數(shù)據(jù)
只保存了sql邏輯猖毫，不保存查詢結(jié)果
2. 應(yīng)用場(chǎng)景
多個(gè)地方用到同樣的查詢結(jié)果
該查詢結(jié)果使用的sql語(yǔ)句比較復(fù)雜
3. 視圖的創(chuàng)建和使用
查詢部門名為IT的員工的相關(guān)信息
CREATE VIEW v1 AS
SELECT
`employee_id`,
`last_name`,
d.`department_name`,
`salary`,
`commission_pct`
FROM
`employees` AS e
INNER JOIN `departments` AS d
ON e.`department_id` = d.`department_id` ;
SELECT
*
FROM
v1
WHERE `department_name` = 'IT' ;
查詢姓名中包含a字符的員工名保礼，部門名，和工種信息
CREATE VIEW myv1 AS
SELECT
`last_name`,
`department_name`,
`job_title`
FROM
`departments` AS d
INNER JOIN `employees` AS e
ON d.`department_id` = e.`department_id`
INNER JOIN `jobs` AS j
ON e.`job_id` = j.`job_id` ;
SELECT
*
FROM
myv1
WHERE last_name LIKE '%a%' ;
查詢各部門的平均工資級(jí)別
CREATE VIEW myv2 AS
SELECT
`department_id`,
AVG(`salary`) AS avg_salary
FROM
`employees`
GROUP BY `department_id` ;
SELECT
v2.`department_id`,
`grade_level`
FROM
myv2 AS v2
INNER JOIN `job_grades` AS jg
ON v2.avg_salary BETWEEN `lowest_sal`
AND `highest_sal` ;
查詢平均工資最低的部門信息
SELECT
*
FROM
`myv2`
ORDER BY avg_salary
LIMIT 1 ;
查詢平均工資最低的部門名和工資
CREATE VIEW myv3 AS
SELECT
*
FROM
`myv2`
ORDER BY avg_salary
LIMIT 1 ;
SELECT
d.*
FROM
`departments` AS d
INNER JOIN myv3 AS m
ON d.`department_id` = m.`department_id` ;
4. 視圖的修改
方式一
CREATE OR REPLACE VIEW myv3 AS
SELECT
`job_id`,
AVG(`salary`) AS avg_salary
FROM
`employees`
GROUP BY `job_id` ;
方式二
ALTER VIEW myv3
AS
SELECT
*
FROM
`employees` ;
5. 視圖的更新
視圖的增刪改操作與表語(yǔ)法一樣义钉，但以下視圖無法更新：
- 包含分組函數(shù)昧绣，distinct，group by捶闸，having夜畴，union或者union all關(guān)鍵字的sql語(yǔ)句
- 常量視圖
- select中包含子查詢
- join
- from一個(gè)不能更新的視圖
- where子句的子查詢引用了from子句中的表
6. 視圖的其他操作
刪除視圖
DROP VIEW v1,
vi ;
查看視圖
DESC `myv1` ;
-- 在sqlyog客戶端效果不太詳細(xì)
SHOW CREATE VIEW `myv1` ;
總結(jié)：
可以重用sql語(yǔ)句
簡(jiǎn)化復(fù)雜的sql操作，無需知道查詢細(xì)節(jié)
保護(hù)數(shù)據(jù)删壮，提高安全性
