mysql 第二版 117页解析

USE yggl;
1
– 1

CREATE     OR REPLACE
    VIEW Emp_view1

    AS

    (SELECT employeeID,    NAME,    WorkYear,    Education
        FROM     employees
        WHERE     sex=1
    );
  
– 2

SELECT    *
    FROM     Emp_view1
    WHERE    WorkYear>2;
1
2
3
– 3

CREATE     OR REPLACE
    VIEW Emp_view2
    
    AS

    (SELECT employees.EmployeeID,    NAME,    DepartmentName,    InCome
        FROM     employees     JOIN    salary
                ON(employees.`EmployeeID`=salary.`EmployeeID`)
                    JOIN    departments
                ON(employees.`DepartmentID`=departments.`DepartmentID`)
    );
1

– 4

SELECT    employeeID,    NAME,    InCome
    FROM     Emp_view2
    WHERE    DepartmentName="研发部";
1
2
3
– 5

CREATE     OR REPLACE
    VIEW Emp_view3
    
    AS

    (SELECT employees.EmployeeID,    NAME,    Education,    Birthday,    sex,    WorkYear,    DepartmentID
        FROM     employees     
        WHERE     WorkYear>2
    )
    WITH CASCADED CHECK OPTION;

1
2

– 6

insert     into    Emp_view3    
      values ("041110", "钟晓玲",  "博士",  "1973-12-01",  "男",  3,  4);

1
2
3
– 7

UPDATE     Emp_view2
    SET      InCome=InCome+200
    WHERE     NAME="李丽";
1
2
3
4
– 8

DELETE     FROM     Emp_view3
    WHERE     Education="本科";
1
2
3
– 9

ALTER     VIEW    Emp_view1
    
    AS

    (SELECT employees.`EmployeeID`,     NAME,    InCome
        FROM     employees, salary
    );
1
2
3
4
5
6
7
8
– 10

DROP     VIEW    Emp_view2,    Emp_view3;

1
2
3

思考6

一、简答题

底部
– 1

作用：
     1、提高了重用性，就像一个函数。
     2、对数据库重构，却不影响程序的运行。
     3、 提高了安全性能。可以对不同的用户，设定不同的视图。
     4、让数据更加清晰。
    
1
2
3
4
5
6
7
– 2

优点：
    1、数据库视图允许简化复杂查询。
        数据库视图由与许多基础表相关联的SQL语句定义。 
        您可以使用数据库视图来隐藏最终用户和外部应用程序的基础表的复杂性。 
        通过数据库视图，
        您只需使用简单的SQL语句，而不是使用具有多个连接的复杂的SQL语句。
    2、数据库视图有助于限制对特定用户的数据访问。 
        您可能不希望所有用户都可以查询敏感数据的子集。
        可以使用数据库视图将非敏感数据仅显示给特定用户组。
    3、数据库视图提供额外的安全层。
        安全是任何关系数据库管理系统的重要组成部分。 
        数据库视图为数据库管理系统提供了额外的安全性。 
        数据库视图允许您创建只读视图，
        以将只读数据公开给特定用户。 
        用户只能以只读视图检索数据，但无法更新。
    4、数据库视图启用计算列。
         数据库表不应该具有计算列，但数据库视图可以这样。 
         假设在orderDetails表中有quantityOrder(产品的数量)和priceEach(产品的价格)列。 
         但是，orderDetails表没有一个列用来存储订单的每个订单项的总销售额。
         如果有，数据库模式不是一个好的设计。 
         在这种情况下，您可以创建一个名为total的计算列，
         该列是quantityOrder和priceEach的乘积，以表示计算结果。
         当您从数据库视图中查询数据时，计算列的数据将随机计算产生。
    5、数据库视图实现向后兼容。 
        假设你有一个中央数据库，许多应用程序正在使用它。 
        有一天，您决定重新设计数据库以适应新的业务需求。
        删除一些表并创建新的表，并且不希望更改影响其他应用程序。
        在这种情况下，可以创建与将要删除的旧表相同的模式的数据库视图。

缺点：
    1、性能。
        从数据库视图查询数据可能会很慢，
        特别是如果视图是基于其他视图创建的。
    2、表依赖关系。
        将根据数据库的基础表创建一个视图。
        每当更改与其相关联的表的结构时，都必须更改视图。
1
2

– 3

注意要点--（百度）
    1).与创建表一样，创建视图的名称必须唯一

    2).创建视图的个数并没限制，但是如果一张视图嵌套或者关联的表过多，同样会引发性能问题，在实际生产环节中部署时务必进行必要的性能检测。

    3).在过滤条件数据时如果在创建视图的sql语句中存在where的条件语句，而在使用该视图的语句中也存在where条件语句时，这两个where条件语句会自动组合

    4).order by 可以在视图中使用，但如果从该视图检索数据的select语句中也含有order by ，那么该视图中的order by 将被覆盖。
        视图中不能使用索引，也不能使用触发器(索引和触发器后面会分析)

    5).使用可以和普通的表一起使用，编辑一条联结视图和普通表的sql语句是允许的。
1
2
3
4
5
6
7
8
9
10
11
– 4

能反映出来
    基本表数据发生改变,改变会从视图反映出来。
    
        视图是一个虚拟表，其内容由查询定义。
        视图中的数据是由一张或多张表中的数据组成的。
        
        所以说，如果你改动了基本表，如果你的视图来源于这个基本表，那视图给你呈现的结果也会随之发生变化。
    
    
    从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。
    从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。
    从数据库系统内部来看，视图是由一张或多张表中的数据组成的，
    从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，
    例如查询，插入，修改，删除操作等。
    
       视图是一个虚拟表，其内容由查询定义。
       同真实的表一样，视图的作用类似于筛选。
       定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。
       分布式查询也可用于定义使用多个异类源数据的视图。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
– 5

    视图view是查询数据的逻辑表，它的数据不正真存在，在数据库中存在数据字典中，
    而是查询基本表的数据的得到。那么能否通过视图update数据呢？

    能！但是有条件

条件：
    1、简单单表或者是一对一的表

    2、不能带聚合函数

    3、没有with read only
1
2
3
4
5
6
7
8
9
10
11
12

二、写SQL语句

底部
USE xscj;
1
– 1

CREATE     OR REPLACE     VIEW cs_kc
    
    AS 

    SELECT  xs.学号,    课程号,        成绩 
        FROM  xs_kc     JOIN     `xs` 
            ON(`xs`.`学号` = `xs_kc`.`学号`)
            
        WHERE (`xs`.`专业名` = '计算机')
    WITH  CHECK  OPTION;
1
2
3
4
5
6
7
8
9
10
– 2

CREATE     OR REPLACE     VIEW cs_kc_AVG(num,  score_avg)
    
    AS 

    SELECT  xs.学号,    AVG(成绩)
        FROM  xs_kc     JOIN     `xs` 
            ON(`xs`.`学号` = `xs_kc`.`学号`)
            
        WHERE     (`xs`.`专业名` = '计算机')
        GROUP BY     成绩  DESC
    ;
1
2
3
4
5
6
7
8
9
10
11
12
– 3

SELECT     学号, 课程号
    FROM    cs_kc;
1
2
3
– 4

SELECT     学号,     AVG(`成绩`) AS "平均成绩"
    FROM     cs_kc
        GROUP BY `学号`
        HAVING (AVG(`成绩`) > 80);
1
2
3
4
5
– 5

CREATE     OR REPLACE     VIEW cs_xs 

    AS 
    
    SELECT *    FROM `xs`     WHERE (`xs`.`专业名` = '计算机') 
    ;
    
INSERT INTO     cs_xs 
    VALUES(    "081255",  "李牧",  "计算机",  1, "1990-10-21", 50, NULL, NULL    );
1
2
3
4
5
6
7
8
9
10
– 6

UPDATE     cs_xs
    SET    总学分=总学分+8;
1
2
3
– 7

UPDATE     xs_kc
    SET     成绩=90
    WHERE    学号="081101"  AND  课程号="101";
1
2
3
4
– 8

DELETE     FROM     cs_xs
    WHERE     性别=0;
1
2
3
– 9

ALTER    VIEW     cs_xs

    AS

    (SELECT 学号,    姓名,    总学分
        FROM     xs
        WHERE    专业名="计算机");