SQL视图的定义与操纵

第1关：创建行列子集视图

任务描述

本关任务：创建计算机系的学生信息的视图 student_cs。

相关知识

行列子集视图是指视图的结果集来源于基本表，没有经过二次计算。 #####创建视图

1. CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
2. VIEW view_name [(column_list)]
3. AS select_statement
4. [WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明：

• OR REPLACE：表示替换已有视图；

• ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)： MySQL 自动选择要使用的算法 ；merge合并；temptable临时表；

• column_list：可选参数，指定视图中各个属性的名词，默认情况下与 select 语句中查询的属性相同；

• select_statement：表示 select 语句；

• [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内；cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件；local表示更新视图的时候，要满足该视图定义的一个条件即可。

  编程要求

  根据提示，在右侧编辑器补充代码：

  创建计算机系的学生信息视图 student_cs。

  代码如下：

   create view student_cs

   as select *

   from student

   where sdept='计算机';

  第2关：创建带聚合函数的视图

  任务描述

  本关任务： 创建由学号和平均成绩两个字段组成的视图 v_grade_avg。

  相关知识

  创建带聚合函数的视图时，一定要给聚合函数列取别名。

  编程要求

  根据提示，在右侧编辑器补充代码， 创建由学号和平均成绩两个字段组成的视图 v_grade_avg。

  代码如下：

  create view v_grade_avg

  as select sno'学号',AVG(grade)'平均成绩'

  from score

  group by sno;

  第3关：在视图上查询平均成绩

  任务描述

  本关任务：在视图 v_grade_avg 上查询平均成绩大于90分的学生信息。

  相关知识

  为了完成本关任务，你需要掌握：视图的使用。

  视图的使用

  查询视图数据

  对视图进行查询，这个是非常简单的（和使用SQL语句去查询数据表一样的语法）。虽然视图是虚拟表，但它依旧是一张表，可通过Select语句进行查询数据。

  例：在视图 V_AVG_S_G 上，查询平均成绩为90分及以上的学生学号、姓名和成绩；

  1. SELECT sno,sname,grade from V_AVG_S_G WHRER 平均成绩>=90;

  编程要求

  在视图 v_grade_avg 上查询平均成绩大于90分的学生信息，并将代码补充在右侧编辑器中。

  代码如下：

    select *

    from v_grade_avg

    where 平均成绩>=90;

  第4关：创建信息系学生的视图

  任务描述

  本关任务：建立信息系学生的视图 v_information。

  相关知识

  为了完成本关任务，你需要掌握：如何创建视图。

  创建视图

  基本语法

  可以使用 CREATE VIEW 语句来创建视图。若要求该视图在修改和插入数时要符合WHERE中的条件，创建时需加with check option选项。

  编程要求

  建立信息系学生的视图 v_information，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生,并将代码补充在右侧编辑器中。

  代码如下：

   create view v_information

    as

    select *

    from student

    where sdept='信息'

    with check option;

  第5关：修改信息系学生的视图

  任务描述

  本关任务：修改信息系学生的视图 v_information。

  相关知识

  为了完成本关任务，你需要掌握：如何修改视图。

  修改视图

  1.CREATE OR REPLACE VIEW 语句

  语法结构：

  1. CREATE OR REPLACE
  2. VIEW view_name [{column_list}]
  3. AS SELECT_STATEMENT
  4. WITH CHECK OPTION

  释义： CREATE OR REPLACE ： 【创建或替换已创建的】视图 view_name ： 视图名称； column_list ： 属性列； SELECT_STATEMENT ：SELECT语句； WITH CHECK OPTION 表示视图在更新时保证在视图的权限范围内。

  编程要求

  修改信息系学生的视图 v_information，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生，并且只返回学号、姓名和专业三个字段的值，并将代码补充在右侧编辑器中。

  代码如下：

   create or replace

   view v_information

    as

    select sno,sname,sdept

    from student

    where sdept='信息'

    with check option;

  第6关：向信息系学生的视图中插入数据

  任务描述

  本关任务：使用 INSERT 语句向视图 v_information 中插入一条数据。

  相关知识

  为了完成本关任务，你需要掌握：如何向视图添加数据。

  视图添加数据

  在视图上使用INSERT语句添加数据时，要符合以下规则：

  1. 使用INSERT语句向数据表中插入数据时，用户必须有插入数据的权利；
  2. 由于视图只引用表中的部分字段，所以通过视图插入数据时只能明确指定视图中引用的字段的取值。而那些表中并未引用的字段，必须知道在没有指定取值的情况下如何填充数据，因此视图中未引用的字段必须具备下列条件之一；

  （1）该字段允许空值； （2）该字段设有默认值； （3）该字段是标识字段，可根据标识种子和标识增量自动填充数据； （4）该字段的数据类型为 timestamp 或 uniqueidentifier。 3. 视图中不能包含多个字段值的组合，或者包含使用统计函数的结果； 4. 视图中不能包含 DISTINCT 或 GROUP BY子句； 5. 如果视图中使用了 WITH CHECK OPTION，那么该子句将检查插入的数据是否符合视图定义中 SELECT 语句所设置的条件。如果插入的数据不符合该条件，SQL Server 会拒绝插入数据； 6. 不能在一个语句中对多个基础表使用数据修改语句。因此，如果要向一个引用了多个数据表的视图添加数据时，必须使用多个 INSERT 语句进行添加。

  编程要求

  使用 INSERT 语句向视图 v_information 中插入一条数据('98001','王立红','信息','02')，并将代码补充在右侧编辑器补充代码中。

  代码如下：

    insert  

    into v_information(sno,sname,sdept,sclass)

    values  ('98001','王立红','信息','02');

  索引与数据库完整性

  第1关：创建一般索引

  任务描述

  本关任务：为 student 表按姓名升序建立索引，索引名为 idx_sname。

  相关知识

  为了完成本关任务，你需要掌握：

  1. 索引是什么；

  2. 索引的分类；

  3. 索引的创建和删除；

  4. 查询索引。

  索引是什么

  数据库索引是一种提高数据库系统性能的方法。索引能让数据库服务器更快地查找和获取表中指定的行。

  例如，为了方便读者快速查找书中的术语，很多书籍在最后附加了索引页，术语按字母排序，同时给出页码。这样读者可以根据术语名，快速获取页码，而不用翻阅整本书。

  但是索引也给数据库系统带来了一定的开销，所以我们应该谨慎地使用它们。

  索引的分类

  索引大体可分为单列索引（普通索引，唯一索引，主键索引）、组合索引、全文索引、空间索引四类。本实训我们主要介绍单例索引和组合索引：

  • 单例索引：一个索引只包含单个列，但一个表中可以有多个单列索引；

    • 普通索引：仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引；
    • 唯一索引：索引列中的值必须是唯一的，但允许为空值；
    • 主键索引：是一种特殊的唯一索引，不允许有空值。

  • 组合索引：在表的多个字段上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

    索引的创建和删除

    创建索引

    创建索引有两种方式，一种是在建表时创建，另一种是建表后创建：

    • 普通索引：

      1. 创表时创建普通索引：

         1. CREATE table mytable(
         2. id INT NOT NULL,
         3. username VARCHAR(16) NOT NULL,
         4. INDEX [indexName] (username)
         5. );

      2. 建表后创建普通索引：

         1. create INDEX 索引名称 on 表名(字段名 desc/asc);
         2. #或者
         3. ALTER TABLE 表名 ADD INDEX 索引名称 (字段名);

         asc 代表升序索引，desc 代表降序，mysql 默认升序索引。

      • 唯一索引：

        1. CREATE UNIQUE INDEX 索引名称 ON 表名(字段名);
        2. #或者
        3. ALTER TABLE 表名 ADD UNIQUE (字段名);

      • 主键索引：主键索引一般在建表时创建，会设为 int 而且是 AUTO_INCREMENT 自增类型的，例如一般表的 id 字段。

        1. CREATE TABLE mytable (
        2. id int(11) NOT NULL AUTO_INCREMENT,
        3. PRIMARY KEY (id)
        4. );

      • 组合索引：组合索引就是在多个字段上创建一个索引。（应用场景：当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度）

        1. CREATE INDEX 索引名称 ON 表名(字段1,字段2，字段3);
        2. #或者
        3. ALTER TABLE 表名 ADD INDEX 索引名称(字段1，字段2，字段3);

        查询表中索引

        查询索引 SQL：

        1. show index from 表名;

        编程要求

        在右侧编辑器补充代码，要求如下：

        为 student 表按姓名升序建立索引，索引名为 idx_sname。

        代码如下：create index idx_sname on student(sname asc);

        第2关：删除索引-练习

        任务描述

        本关任务：删除索引 idx_sname。

        相关知识

        删除索引

        同样，删除索引也有两种方式。

        1. #使用drop删除索引
        2. drop index index_name on table_name ;
        4. #使用alter删除索引
        5. alter table table_name drop index index_name ;
        6. alter table table_name drop primary key ; #删除主键索引

        编程要求

        根据提示，在右侧编辑器补充代码，删除索引 idx_sname。

        代码如下：drop index idx_sname on student;

        第3关：创建联合索引

        任务描述

        本关任务：创建联合索引 idx_sname_sdept。

        相关知识

        为了完成本关任务，你需要掌握：如何创建联合索引。

        创建联合索引

        • 语法：

          1. create index 索引名称 on 表名(字段名称)

        • 示例：

          1. create index firstIndex on student(id, name, address)；

        • 注意：

          1.索引名称、表名、字段名称 都不要用引号括起来；

          2.对于联合索引而言，字段名称可以有多个，中间用英文逗号分隔即可； 3..普通索引数据可以重复。

          编程要求

          在 student 表 sname 字段和 sdept 字段上创建联合索引 idx_sname_sdept，并将代码补充在右侧编辑器中。

          代码如下：  create index idx_sname_sdept on student(sname,sdept);

          第4关：创建唯一索引

          任务描述

          本关任务：创建唯一索引 uk_cname。

          相关知识

          为了完成本关任务，你需要掌握：如何创建唯一索引。

          如何创建唯一索引

          • 语法：

            1. create unique index 索引名称 on 表名(字段名称)；

          • 示例：

            1. create unique index secondIndex on student(id, name, address);

          • 注意：

            1. 索引名称、表名、字段名称 都不要用引号括起来；
            2. 对于联合索引而言，字段名称可以有多个，中间用英文逗号分隔即可；
            3. 唯一索引数据不可重复。

            编程要求

            在 course 表的 cname 字段上创建唯一索引 uk_cname ，并将代码补充在右侧编辑器中。

            代码如下： create unique index uk_cname on course(cname);

            第5关：创建前缀索引

            任务描述

            本关任务：创建4个字符的前缀索引 pf_cname。

            相关知识

            为了完成本关任务，你需要掌握：如何创建前缀索引。

            创建前缀索引

            创建前缀索引的两种方式

            1. //1.使用CREATE INDEX语句
            2. CREATE INDEX index_name ON table_name (column_name (length));
            4. //2.使用ALTER TABLE语句
            5. ALTER TABLE table_name ADD INDEX (column_name (length));

            编程要求

            在 course 表的 cname 字段上创建4个字符的前缀索引 pf_cname，并将代码补充在右侧编辑器中。

            代码如下： create index pf_cname on course(cname(4));

            第6关：创建全文索引

            任务描述

            本关任务：创建全文索引 ft_cname 。

            相关知识

            为了完成本关任务，你需要掌握：如何创建全文索引。

            创建全文索引

            创建全文索引的两种方式

            1. //1.ALTER添加
            2. ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column1,column2,...);
            4. //2.CRATE INDEX添加
            5. CREATE FULLTEXT INDEX index_name ON table_name (column1,column2,...);

            编程要求

            在 course 表的 cname 字段上创建全文索引 ft_cname ，并将代码补充在右侧编辑器中。

            代码如下：  create fulltext index  ft_cname on course(cname);

            如有错误，欢迎指正。

             
             深度网络科技有限公司  magento建站  好看的官网设计  以下哪个软件为网页制作软件  做网站哪家公司比较好而且不贵  编写网页的软件