数据库原理 头歌实训 数据库常用对象

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 ;
3. #使用alter删除索引
4. alter table table_name drop index index_name ;
5. 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));
3. //2.使用ALTER TABLE语句
4. 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,...);
3. //2.CRATE INDEX添加
4. CREATE FULLTEXT INDEX index_name ON table_name (column1,column2,...);

编程要求

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

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

如有错误，欢迎指正。