欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久

視圖的創(chuàng  )建
視圖:MySql從5.0.1版本開(kāi)始提供視圖功能。一種虛擬存在的表，
行和列的數據來(lái)定義視圖的查詢(xún)中使用的表，并且是在使用視圖的動(dòng)態(tài)生成，只保存了SQL邏輯，
不保存查詢(xún)結果。
應用場(chǎng)景:
多個(gè)地方用到同樣的的查詢(xún)結果。
該查詢(xún)結果使用的SQL語(yǔ)句教復雜。

先來(lái)一個(gè)案例領(lǐng)略一下視圖的魅力。
#正常查詢(xún)寫(xiě)法
查詢(xún)姓張的同學(xué)與學(xué)科
SELECT s.studentname,m.majorname
FROM student s
INNER JOIN major m ON s.majorid = m.majorid
WHERE s.studentname LIKE '%張%';

#視圖封裝寫(xiě)法
CREATE VIEW shitu
AS
SELECT s.studentname,m.majorname
FROM student s
INNER JOIN major m ON s.majorid = m.majorid;

#運用視圖(得到與上面圖片一樣的結果)
SELECT * FROM shitu WHERE studentname LIKE '%張%';

#創(chuàng  )建視圖

語(yǔ)法:
CREATE VIEW 視圖名
AS
查詢(xún)語(yǔ)句
優(yōu)點(diǎn):
重用SQL語(yǔ)句
簡(jiǎn)化復雜的SQL的操作，不必知道它的查詢(xún)細節
保護數據，提高安全性(隱藏原始表的信息)

#切換庫
USE myemployees;
#1.查詢(xún)郵箱中包含a字符的員工名，部門(mén)名和工種信息。
#1.正常查詢(xún)方法
SELECT e.last_name,d.department_name,j.job_title,e.email
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.email LIKE'%a%';
#視圖查詢(xún)方法

#①創(chuàng  )建視圖
CREATE VIEW ayuan AS
SELECT e.last_name,d.department_name,j.job_title,e.email
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id;

#②使用視圖查詢(xún)
SELECT * FROM ayuan WHERE email LIKE '%a%';

#2.查詢(xún)各部門(mén)的平均工資級別
#第一步創(chuàng  )建視圖，查出平均工資
CREATE VIEW pj
AS
SELECT ROUND(AVG(salary),2) ag,department_id
FROM employees
GROUP BY department_id;

#第二步連接查詢(xún)
SELECT pj.ag,g.grade_level
FROM pj
INNER JOIN job_grades g
ON pj.ag BETWEEN g.lowest_sal AND g.highest_sal;

#3.查詢(xún)平均工資最低的部門(mén)
#復用上一題的SQL語(yǔ)句邏輯，查出平均工資。
CREATE VIEW pj
AS
SELECT ROUND(AVG(salary),2) ag,department_id
FROM employees
GROUP BY department_id;
#查出最低工資
SELECT * FROM pj ORDER BY ag LIMIT 1;

#4.查詢(xún)平均工資最低的部門(mén)名和工資
#思路1.視圖也可以套視圖
#1.第一步
CREATE VIEW pj2
AS
SELECT *
FROM pj
ORDER BY ag LIMIT 1;
#2.第二步
SELECT d.department_id,d.department_name,p.ag
FROM pj2 p
INNER JOIN departments d
ON p.department_id=d.department_id;

#思路2.復用第二題的SQL語(yǔ)句邏輯，查出平均工資
SELECT d.department_name,pj.ag,d.department_id
FROM pj
INNER JOIN departments d
ON pj.department_id=d.department_id
ORDER BY ag LIMIT 1;

視圖的修改
修改視圖:
語(yǔ)法方式一
CREATE OR REPLACE VIEW 視圖名
AS
查詢(xún)語(yǔ)句;
視圖存在修改，視圖不存在創(chuàng  )建。

#示例:修改視圖
#1.查看原視圖
SELECT * FROM pj2;

#2.修改視圖
CREATE OR REPLACE VIEW pj2
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

修改視圖
語(yǔ)法:方式二
ALTER VIEW 視圖名
AS
查詢(xún)語(yǔ)句;
只能修改，不能創(chuàng  )建。

ALTER VIEW pj2
AS
SELECT * FROM employees;

刪除視圖
語(yǔ)法:
DROP VIEW 視圖名,...;
可以一次性刪除多個(gè)。

DROP VIEW pj,pj2;

查看視圖結構
#第一種方式
DESC pj2;

#第二種方式:
完整的展示視圖創(chuàng  )建的語(yǔ)句(LINUX下查看)
SHOW CREATE VIEW pj2;

案例:視圖的創(chuàng  )建
#一.創(chuàng  )建視圖emp_v1,要求查詢(xún)電話(huà)號碼以'011’開(kāi)頭的員工姓名和工資，郵箱。
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,first_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
查看視圖
SELECT * FROM emp_v1;

#二.創(chuàng  )建視圖emp_v2,要求查詢(xún)部門(mén)的最高工資高于12000的部門(mén)信息。

#常規寫(xiě)法，第一步查出每個(gè)部門(mén)最高工資
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
#第二步,查詢(xún)最高工資高于12000的部門(mén)信息
SELECT d.*,m.mx_dep
FROM departments d
INNER JOIN (
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000
) m
ON m.department_id=d.department_id;

#視圖第一步，獨立最高工資
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;

#視圖第二步.視圖也可以起別名
SELECT d.*,m.mx_dep
FROM departments d
INNER JOIN emp_v2 m
ON m.department_id = d.department_id;

視圖的更新
(視圖內的數據修改)
#不能更新/不是所有的視圖都能更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;

#重新建立視圖
CREATE OR REPLACE VIEW myv2
AS
SELECT last_name,email
FROM employees;

INSERT INTO myv2 VALUES('guangtouqiang','gtq@qq.com');
/* Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 
query: 0.016 sec. */
受影響的行：1 找到的行：0 警告：0 1 查詢(xún)的持續時(shí)間：0.016 秒。

#查看視圖中的數據
SELECT * FROM myv2;
SELECT * FROM employees;
/數據插入成功后，視圖與原始表會(huì )同時(shí)更新數據

#2修改數據
UPDATE myv2 SET last_name = '高倉健' WHERE last_name = 'guangtouqiang';

#查看視圖中的數據
SELECT * FROM myv2;
SELECT * FROM employees;

#3.刪除數據
DELETE FROM myv2 WHERE last_name = '高倉健';
/語(yǔ)句執行后，視圖與原始表會(huì )同時(shí)刪除數據/

#視圖的可更新性和視圖中查詢(xún)的定義有關(guān)系，以下類(lèi)型的視圖是不能更新的。
發(fā)現包含以下的字段都是涉及到全表的數據，所以不允許修改。
包含以下關(guān)鍵字的SQL語(yǔ)句:分組函數，DISTINCT，GROUP BY,HAVING,
常量視圖
UNION或者UNION ALL
SELECT中包含子查詢(xún)
JOIN
FROM一個(gè)不能更新的視圖
WHERE子句的子查詢(xún)引用了FROM 子句中的表。

①　#分組函數，新建視圖
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) M,department_id
FROM employees
ORDER BY department_id;

#查看視圖
SELECT * FROM myv1;

②　#常量視圖
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;

③　#SELECT中包含子查詢(xún)
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT AVG(salary) FROM employees) AS 平均工資;
SELECT * FROM myv3;

④　#JOIN
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;
SELECT * FROM myv4;

#更新
UPDATE myv4 SET last_name = 'GuangTouQiang' WHERE last_name='Fay';

⑤　#FROM一個(gè)不能更新的視圖
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT AVG(salary) FROM employees) AS 平均工資,department_id
FROM employees;
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
SELECT * FROM myv5;

⑥　#WHERE子句的子查詢(xún)引用了FROM 子句中的表。
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
SELECT * FROM myv6;