exp 4

实验四

视图

基本概念

• 用户可以用SQL语言对视图(View)和基本表(Base Table)进行查询等操作，在用户观点里，视图和表一样，都是关系。

• 视图是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。

• 基本表是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。

• 数据库中只存放视图的定义，视图对应的数据仍存放在原来的表中，随着表中数据的变化，视图的数据随之改变。

• 对视图的查询与基本表一样，对视图的更新将受到一定的限制

应用举例

下面是一个示例引入，说明如何创建、使用和管理SQL视图：

假设我们有一个简单的数据库，其中包含两个表：employees（员工信息）和salaries（工资信息）。

1. 创建视图：

假设我们想要一个包含员工姓名、工资和部门的视图。我们可以这样创建一个视图：

CREATE VIEW employee_salaries AS
SELECT e.name, s.salary, e.department
FROM employees e
JOIN salaries s ON e.id = s.employee_id;
1
2
3
4

这将创建一个名为 employee_salaries 的视图，它包含了员工的姓名、工资和所在部门，通过将 employees 表和 salaries 表进行联接。

2. 使用视图：

一旦视图创建完成，我们可以像查询表一样使用它：

SELECT * FROM employee_salaries;
1

这将返回视图中的所有员工姓名、工资和部门信息。

3. 更新视图：

虽然视图本身不存储数据，但我们可以通过视图对基础表进行更新：

UPDATE employee_salaries
SET salary = 50000
WHERE name = 'John';
1
2
3

这将更新视图中名为 “John” 的员工的工资信息，并且如果权限允许的话，也会更新 salaries 表中对应员工的工资。

4. 删除视图：

DROP VIEW employee_salaries;
1

这将删除我们之前创建的视图。

通过视图，我们可以隐藏复杂的查询逻辑，提高了数据访问的灵活性和安全性。它们还可以简化应用程序中的数据访问，因为应用程序只需与视图交互，而无需直接访问底层表。

视图的作用

• 视图能够简化用户的操作（使数据库看起来结构简单、清晰、可简化用户的数据查询操作）

• 视图使用户能以多种角度看待同一数据

• 视图对重构数据库提供了一定程度的逻辑独立性

• 视图能够对机密数据提供安全保护(对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户的视图上。)

实验一

create database exp4_1;

\c exp4_1

create table Course(
    Cno char(10) primary key, 
    Cname char(15) not null, 
    teacher char(10)
);

create table Student(
    Sno char(10) primary key, 
    Sname char(5) not null, 
    Ssex char(2) check(Ssex = '男' or Ssex = '女'), 
    Sage int check(Sage >= 16 and Sage <= 40), 
    Sdept char(15)
);

create table SC(
    Sno char(10),
    Cno char(10), 
    grade int,
    constraint cst1 foreign key (Sno) references Student(Sno),
    constraint cst2 foreign key (Cno) references Course(Cno)
);

insert into Student values('1','张三','男',18,'CS');
insert into Student values('2','王玲','女',17,'CS');
insert into Student values('3','赵四','男',20,'法学院');
insert into Student values('103','wpc','男',20,'CS');
insert into Student values('50','a','男',20,'IS');
insert into Student values('51','b','女',21,'IS');
insert into Student values('52','c','男',22,'IS');

insert into Course values('1','数据库原理','张俊虎');
insert into Course values('101','操作系统','程玉');
insert into Course values('210','现代文学','王丹');
insert into Course values('243','有机化学','沈彤');
insert into Course values('204','数据结构','程玉');

insert into SC values('2','210',99);
insert into SC values('3','243',90);
insert into SC values('103','204',100);
insert into SC values('50','1',90);
insert into SC values('51','1',95);

3.5.1 定义视图

例1：建立信息系学生视图
CREATE VIEW IS_Student AS 
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE Sdept = 'IS';

例2：建立计算机系学生视图, 并要求进行修改和插入操作时
仍需保证该视图只有计算机系的学生。

CREATE VIEW CS_Student AS 
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE Sdept= 'CS'
WITH CHECK OPTION;

// 由于加上了WITH CHECK OPTION子句, 以后对该视图进行插入、修改、删除操作时, DBMS会自动加上Sdept=‘CS’的条件。

例3：建立信息系选修了1号课程的学生的视图。
CREATE VIEW IS_S1(Sno, Sname, Grade) AS 
SELECT Student.Sno, Sname, Grade
FROM Student, SC
WHERE Sdept= 'IS' AND SC.Cno= '1' AND Student.Sno=SC.Sno;

例4：建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2 AS 
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade>=90;

例5：定义一个反映学生出生年份的视图。

CREATE VIEW  BT_S(Sno, Sname, Sbirth) AS 
SELECT Sno, Sname, 2006-Sage
FROM Student;

例6：将学生的学号及他的平均成绩定义为一个视图。

CREATE VIEW S_G(Sno, Gavg) AS 
SELECT  Sno, AVG(Grade)
FROM SC
GROUP BY Sno;

例7：将Student表中所有女生记录定义为一个视图。

CREATE VIEW F_Student(stdnum, name, sex, age, dept) AS 
SELECT *
FROM Student
WHERE Ssex= '女';
如果修改了基本表Student的结构, 则视图与基本表之间的映象关系被破坏, 视图就不能正确工作。为避免出现该类问题, 最好在修改基本表之后删除有该基本表导出的视图, 然后重建（同名）视图。


例8：删除视图IS_S1（IS_S2由IS_S1导出）。

DROP VIEW IS_S2;
DROP VIEW IS_S1;

3.5.2 查询视图

例1：在信息系学生的视图中查找年龄小于20的学生。
SELECT  Sno, Sname
FROM    IS_Student
WHERE   Sage < 20; 

例2：查询信息系选修了1号课程的学生。
    SELECT  SC.Sno, Sname
    FROM    IS_Student, SC
    WHERE   SC.Sno=IS_Student.Sno AND SC.Cno= '1';


例3：在S_G视图中查询平均成绩在90分以上的学生学号和
     平均成绩。
SELECT   *
FROM     S_G
WHERE    Gavg >= 90;

3.5.3 更新视图
例1：将信息系学生视图IS_Student中学号为95002的学生的姓名改为“刘辰”。
UPDATE   IS_Student
SET      Sname= '刘辰'
WHERE    Sno = '95002';

例2：向信息系学生视图IS_Student中插入一名新生, 学号为95006, 姓名为赵新, 年龄为20岁的学生。
INSERT
INTO IS_Student
VALUES   ('95006', '赵新', '女', 20);

例3：删除信息系学生视图IS_Student中学号为95006的
     记录。
DELETE
FROM     IS_Student
WHERE    Sno= '95006';

例： 将SC中成绩在平均成绩之上的元组定义成一个视图
GOOD_SC。
     CREATE VIEW GOOD_SC
      AS SELECT Sno, Cno, Grade
               FROM SC
               WHERE Grade >
                     (SELECT AVG(Grade)
                      FROM SC);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148

实验二

create database exp4_2;

create table s(
sno varchar(50) primary key,
sname varchar(50),
status varchar(50),
city varchar(50)
);

create table p(
pno varchar(50) primary key,
pname varchar(50),
color varchar(50),
weight decimal
);


create table j(
jno varchar(50) primary key,
jname varchar(50),
city varchar(50)
);


create table spj(
sno varchar(50),
pno varchar(50),
jno varchar(50),
qty int,
foreign key(sno) references s(sno),
foreign key(pno) references p(pno),
foreign key(jno) references j(jno)
);


insert into s values('s1', '精益', '20', '天津');
insert into s values('s2', '盛锡', '10', '北京');
insert into s values('s3', '东方红', '30', '北京');
insert into s values('s4', '丰盛泰', '20', '天津');
insert into s values('s5', '为民', '30', '上海');

insert into p values('p1', '螺母', 'red', 12);
insert into p values('p2', '螺栓', 'green', 17);
insert into p values('p3', '螺丝刀', 'blue', 14);
insert into p values('p4', '螺丝刀', 'red', 14);
insert into p values('p5', '凸轮', 'blue', 40);
insert into p values('p6', '齿轮', 'red', 30);

insert into j values('j1', '三建', '北京');
insert into j values('j2', '一汽', '长春');
insert into j values('j3', '弹簧厂', '天津');
insert into j values('j4', '造船厂', '天津');
insert into j values('j5', '机车厂', '唐山');
insert into j values('j6', '无线电厂', '常州');
insert into j values('j7', '半导体厂', '南京');

insert into spj values('s1', 'p1', 'j1', 200);
insert into spj values('s1', 'p1', 'j3', 100);
insert into spj values('s1', 'p1', 'j4', 700);
insert into spj values('s1', 'p2', 'j2', 100);

insert into spj values('s2', 'p3', 'j1', 400);
insert into spj values('s2', 'p3', 'j2', 200);
insert into spj values('s2', 'p3', 'j5', 500);
insert into spj values('s2', 'p3', 'j5', 400);
insert into spj values('s2', 'p5', 'j1', 400);
insert into spj values('s2', 'p5', 'j2', 100);

insert into spj values('s3', 'p1', 'j1', 200);
insert into spj values('s3', 'p3', 'j1', 200);

insert into spj values('s4', 'p5', 'j1', 100);
insert into spj values('s4', 'p6', 'j3', 300);
insert into spj values('s4', 'p6', 'j4', 200);

insert into spj values('s5', 'p2', 'j4', 100);
insert into spj values('s5', 'p3', 'j1', 200);
insert into spj values('s5', 'p6', 'j2', 200);
insert into spj values('s5', 'p6', 'j4', 500);

首先，我们需要在 SPJ 数据库的基础上创建一个三建工程项目的供应情况视图。
这个视图将包括供应商代码（SNO）、零件代码（PNO）和供应数量（QTY）。

create view Supply as
select sno, pno, qty
from spj
join j on spj.jno = j.jno
where j.jname = '三建';

接下来，我们可以基于这个视图执行所需的查询：

1. 找出三建工程项目使用的各种零件代码及其数量：

select pno, sum(qty) as total_qty
from Supply
group by pno;

(这条查询将返回三建工程项目使用的每种零件代码以及每种零件的总数量。)

2. 找出供应商 S1 的供应情况：
select pno, qty
from Supply
where sno = 's1';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103