#### 【例7-1】
* 建庫
CREATE DATABASE chapter07;
* 建表
USE chapter07;
CREATE TABLE student(
s_id INT(3),
name VARCHAR(20),
math FLOAT,
chinese FLOAT
);
* 添加數據
INSERT INTO student(s_id,name,math,chinese) VALUES (1,'Tom',80,78);
INSERT INTO student(s_id,name,math,chinese) VALUES (2,'Jack',70,80);
INSERT INTO student(s_id,name,math,chinese) VALUES (3,'Lucy',97,95);
* 創建視圖
CREATE VIEW view_stu AS SELECT math,chinese,math+chinese FROM student;
* 查看視圖
SELECT * FROM view_stu;
#### 【例7-2】
CREATE VIEW view_stu2(math,chin,sum) AS SELECT math,chinese,math+chinese FROM student;
* 查看視圖
SELECT * FROM view_stu2;
#### 【例7-3】
* 建表
CREATE TABLE stu_info(
s_id INT(3),
glass VARCHAR(50),
addr VARCHAR(100)
);
* 添加數據
INSERT INTO stu_info(s_id,glass,addr) VALUES (1,'erban','anhui');
INSERT INTO stu_info(s_id,glass,addr) VALUES (2,'sanban','chongqing');
INSERT INTO stu_info(s_id,glass,addr) VALUES (3,'yiban','shandong');
* 查詢結果
SELECT * FROM stu_info;
* 創建視圖
CREATE VIEW stu_glass(id,NAME,glass)
AS
SELECT student.s_id ,student.name,stu_info.glass
FROM student,stu_info
WHERE student.s_id=stu_info.s_id;
* 查看視圖
SELECT * FROM stu_glass;
#### 【例7-4】
DESCRIBE stu_glass;
#### 【例7-5】
SHOW TABLE STATUS LIKE 'stu_glass'\G
#### 【例7-6】
SHOW CREATE VIEW stu_glass\G
#### 【例7-7】
CREATE OR REPLACE VIEW view_stu AS SELECT * FROM student;
#### 【例7-8】
ALTER VIEW view_stu AS SELECT chinese FROM student;
#### 【例7-9】
UPDATE view_stu SET chinese = 100;
* 查看視圖
SELECT * FROM view_stu2;
#### 【例7-10】
INSERT INTO student VALUES(4,'Lily',100,100);
* 查看視圖
SELECT * FROM view_stu2;
#### 【例7-11】
DELETE FROM view_stu2 WHERE math=70;
* 查看視圖
SELECT * FROM view_stu2;
#### 【例7-12】
DROP VIEW IF EXISTS view_stu2;
* 查看視圖
SELECT * FROM view_stu2;
-------------------------------------------------------------------------------------------------
#### 7.3 應用案例—視圖的應用
（1）
* 建表
CREATE TABLE stu
(
s_id INT(11) PRIMARY KEY,
s_name VARCHAR(20) NOT NULL,
addr VARCHAR(50) NOT NULL,
tel VARCHAR(50) NOT NULL
);
* 添加數據
INSERT INTO stu
VALUES(1,'ZhangPeng','Hebei','13889075861'),
(2,'LiXiao','Shandong','13953508223'),
(3,'HuangYun','Shandong','13905350996');
* 查詢結果
SELECT * FROM stu;
（2）
* 建表
CREATE TABLE sign
(
s_id INT(11) PRIMARY KEY,
s_name VARCHAR(20) NOT NULL,
s_sch VARCHAR(50) NOT NULL,
s_sign_sch VARCHAR(50) NOT NULL
);
* 添加數據
INSERT INTO sign
VALUES(1,'ZhangPeng','High School1','Peking University'),
(2,'LiXiao','High School2','Peking University'),
(3,'HuangYun','High School3','Tsinghua University');
* 查詢結果
SELECT * FROM sign;
（3）
* 建表
CREATE TABLE stu_mark
(
s_id INT(11) PRIMARY KEY,
s_name VARCHAR(20) NOT NULL,
mark INT NOT NULL
);
* 添加數據
INSERT INTO stu_mark VALUES(1,'ZhangPeng',730),(2,'LiXiao'
,725),(3,'HuangYun',736);
* 查詢結果
SELECT * FROM stu_mark;
（4）
* 創建視圖
CREATE
VIEW beida(id,NAME,mark,sch)
AS
SELECT stu_mark.s_id,stu_mark.s_name,stu_mark.mark,sign.s_sign_sch
FROM stu_mark,SIGN
WHERE stu_mark.s_id=sign.s_id
AND stu_mark.mark >=720
AND sign.s_sign_sch='Peking University';
* 查看視圖
SELECT * FROM beida;
（5）
* 創建視圖
CREATE
VIEW qinghua(id,NAME,mark,sch)
AS
SELECT stu_mark.s_id,stu_mark.s_name,stu_mark.mark,sign.s_sign_sch
FROM stu_mark,SIGN
WHERE stu_mark.s_id=sign.s_id
AND stu_mark.mark >=725
AND sign.s_sign_sch='Tsinghua University';
* 查看視圖
SELECT * FROM qinghua;
（6）
* 更新視圖
UPDATE stu_mark SET mark=mark-10 WHERE stu_mark.s_name='HuangYun';
