数据库实验一——数据库定义与操作语言实验

一、实验题目

数据库定义与操作语言实验

二、实验内容和要求

数据库定义与操作语言实验包含6 个实验项目，其中5 个必修实验项目， 1 个选修实验项目。其中实验项目1至5为设计型实验，实验项目6为验证型实验。
六个实验的实验目的和内容如下：
1.理解和掌握SQL DDL 语句的语法，特别是各种参数的具体含义和使用方法； 使用SQL 语句创建、修改和删除数据库、模式和基本表。
2. 针对某个数据库设计各种单表查询SQL 语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。
3.针对自定义数据库，正确分析用户查询要求，设计各种嵌套查询和集合查询。
4.针对自定义数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。
5.针对给定的数据库模式，以及相应的应用需求，创建视图和带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。
6.针对给定的数据库模式和具体应用需求，创建唯一索引、函数索引、复合索引等；修改索引；删除索引。设计相应的SQL查询验证索引有效性。

三、实验步骤

（一）数据库定义实验

创建基本表时，为不同的列选择合适的数据类型，正确创建表级和列级完整性约束，如列值是否允许为空、主码和外码等。

这里创建了三个表，Student表中以Sno作为主码，Sname加上unique约束、Course表中Cno作为主码，Cname加上not null约束，同时引用本身的Cno（主码）为Cpno属性列（非主码）作为外码、表SC以Sno、Cno属性组作为主码，同时Sno引用自Student的主键Sno，Cno引用自Course表的主键Cno；
数据库testdb与三个表都创建成功；

注意：数据完整性约束，可以在创建基本表时定义，也可以先创建表然后定义完整性约束。由于完整性约束的限制，被引用的表要先创建。

（二）数据基本查询实验

已经向三个表中填充了一些元组，可以进行基本的查询操作；
实验重点：分组统计查询、单表自身连接查询、多表连接查询。
2.1.查询指定列（投影）、选择

2.2. 不带分组过滤条件 和 带分组过滤条件的分组统计查询

前者查询选修每门课程的选修人数，后者查询平均成绩大于等于80的学生学号与他的平均成绩，两者都用到了GROUPY BY分组查询，后者还用到HAVING过滤条件；

2.3. 两表连接 与 自身连接

两表连接查询student、sc表中Sno相同的并且将元组连接，而后者同样如此但却将相同的属性仅保留一个，从结果也可以看出；

2.4. 三表连接

可以看到，三表连接涉及到Student、Course、SC三个表，目的是将所有的学生信息查询出来，计算查询表示将查询的结果（某一列的值）经过计算得到新的值打印出来；

（三）数据高级查询实验

针对自定义数据库，正确分析用户查询要求，设计各种嵌套查询和集合查询。
实验难点：嵌套查询、相关子查询、多层EXIST嵌套查询。
3.1. 相关子查询与不相关子查询
相关子查询表示子查询的查询条件依赖于父查询，不相关子查询则相反；

第一个查询表示不相关子查询，第一步的查询确定刘晨所在的系名，第二部查询是查找所有在CS系中的学生，第一部查询嵌入到第二部查询中，先执行子查询再进行父查询；
第二个查询是相关子查询，旨在找出每个学生查过他自己选修课程平均成绩的课程号；
X是表SC的别名，用来表示SC的一个元组，内层循环是求一个学生所有选修课程平均成绩，至于是哪一个学生的平均成绩需要看x.Sno的值，与父查询相关；
其中该语句的执行过程如下：
①外层查询中取出SC的一个元组x，将x的Sno传入内层查询
②执行内层查询，得到平均值，用该值代替内层查询，得到外层查询
③执行该外层查询

3.2. 谓词ANY查询 和 带EXISTS的查询
子查询返回单值时可以用比较运算符，返回多值时要用ANY；
EXISTS代表存在量词，带有该谓词的子查询不返回任何数据，只产生逻辑真或假。

前者为带ANY谓词的查询，查找非计算机系中比任意一个计算机系学生年纪小的学生姓名与学号。后者为带EXISITS的查询，查询所有选修了1号课程的学生。

3.3. 多层嵌套EXISTS 与 集合查询
与EXISTS相对应的就是NOT EXISTS谓词，若内层查询结果为空，则外层的WHERE字句返回真值，否则返回假值。
SLELE语句的查询结果是元组的集合，所以多个SELETE语句的结果可进行集合操作，包括并操作UNION、交操作INTERSET和差操作EXCEPT。
注意，参加集合操作的各查询结果列数必须相同，对应项的数据类型也必须相同。

多层嵌套EXISTS查询，查询了全部课程的学生学号、姓名；它表达的语义为;不存在这样的课程y，学生201215122选修了y，而学生x没有选。
集合UNION查询，查询了计算机科学系的学生与年龄不大于19岁学生的并集，简单的将两个SELETE查询结果并起来；

（四）数据更新实验

熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作。
实验重点：插入、修改和删除数据的SQL。
实验难点：与嵌套SQL子查询相结合的插入、修改和删除数据的SQL语句；利用一个表的数据来插入、修改和删除另外一个表的数据。
4.1. 插入数据

插入操作比较简单，使用insert into语句即可；
但需要注意的是：我们插入的信息需要满足各个表的约束条件，如UNIQUE、PRIMARY KEY条件等；

4.2. 与嵌套子查询相结合的插入

这里建立一个表dept_age用于存储各个系的平均年龄；
然后使用与嵌套子查询相结合的插入方式对Student表按系分组求平均年龄，再把结果存入新表中；

4.3. 单条更新与批量更新数据

UPDATE语句用于修改指定表中的元组，SET自居给出的表达式的值用于取代相应的属性列值，省略WHERE则表示要修改表中所有元组。
上面的语句中前者更新一条课程数据，将Cpno为8的元组的Cpno修改为2；
后者省略where语句，将表中所有语句的Sage加1；

4.4.单条删除与批量删除

DELETE语句的功能是从指定的表中删除满足WHERE子句条件的所有元组，省略Where子句则表示删除表中全部元组，但表定义仍在。


4.5. 带子查询的删除

子查询同样可以嵌套在DELETE语句中，用于构造执行删除操作的条件。
上述语句为删除MBA系所有学生的选课记录。

注：对某个基本表中数据的增、删、改操作有可能会破坏参照完整性！

（五）视图

熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。
实验重点：创建视图。
实验难点：可更新的视图和不可更新的视图的区别， WITH CHECK OPTION的验证。
5.1. 创建视图（带和不带检查选项）

与创建表的方法类似，关键字为CREATE VIEW；
子查询可以是任意的SELECT语句，是可以包含ORDER BY子句和DISTINCT短语的；
WITH CHECK OPTION表示对视图进行UPDATE、INSERT、DELETE操作时要保证更新、插入、删除的行满足视图定义中的谓词条件。
第一个不带检查选项的视图是信息系学生的视图，忽略了IS_Student的列名，隐含了由子查询中三个列名组成。
第二个带检查选项的视图是成年学生的学号、年龄组成的视图，当我们对视图数据进行更改时需要首先判断是否可行。

5.2. 验证WITH CHECK OPTION选项

我们从adult视图中删除一个元素，那么如果可以删除对应的表中的元组同样会删除；

删除失败！
这里失败的原因在于student中的Sno 201215121已经被SC表引用作为SC表的外键了，因此不能删除；
这里可以看出OPTION CHECK OPTION选项有效。
5.3. 分组视图

用带有聚集函数和GROUPY BY字句查询来定义视图，这种视图成为分组视图。
上述语句中，将学生的学号以及平均成绩定义为一个视图；
AS语句中的SELECT语句的目标列平均成绩是通过作用聚集函数得到的，所以CREATE VIEW中必须明确定义组成S_G视图的各个属性列名。S_G是一个分组视图。

5.4. 可更新与不可更新视图
由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。

前者将信息系学生视图IS_Student中学号为201215125的学生年龄改为19。

更新成功；
而后一个更新是对视图S_G表中学号为201515121的学生平均成绩改为90；

可以看到，更新一个无法更新的表必然失败！
关系数据库中，并不是所有视图都是可更新的，有些视图的更新不能唯一的有意义地转换成对相应基本表地更新。
上面对S_G地更新无法转换成对基本表的更新，因为系统无法修改各科成绩使得平均成绩为90。

5.5. 视图消解

视图消解就是指将对视图的操作转换成对基本表的操作。

两者结果相同，视图消解一致。

（六）索引实验

为了验证索引效果，要求实验数据集达到10万条记录以上的数据量，因此我在MySql官网中下载了employees数据库并且导入到我的本机Mysql中。

6.1.展示索引

通过SHOW函数来展示出当前表的索引；

此时有处于主键上的索引，类型为BTREE；
6.2.唯一索引

通过指定UNIQUE字段来创建唯一索引，但是这里的emp_no并不是UNIQUE约束类型的，含有很多重复元素，因此无法创建成功：

6.3.简单索引和复合索引

简单索引就是在单列上进行创建的，而复合索引则是在两列或两列以上创建。

这里可以看到创建索引需要的时间很多，说明索引是需要消耗资源的。

6.4.使用索引与不使用索引的对比

可以看到，这里通过ignore index字段来指示查询忽略主键，同时通过EXPLAIN来判断是否有使用索引；

• 使用索引（EXPLAN语句查询是否使用索引）
  

• 不使用索引（ignore index）
  
  可以看出，使用索引后速度是不使用索引的27倍左右，提高的效率百分比为276.3%；

四、实验心得

本次实验通过实践学习了书本上对于数据库的DDL语句的语法、使用SQL 语句创建、修改和删除数据库的语句，SQL 查询语句，SQL嵌套查询和集合查询等各种高级查询，SQL语句对数据库进行数据的插入、修改、删除操作，创建视图和带WITH CHECK OPTION的视图，尤其是掌握索引设计原则和技巧，能够创建合适的索引以提高数据库查询，收获巨大。
不过碍于实验内容过多，最近太忙，不能非常完整的完成每一个实验所涉及的所有细节，比较遗憾，希望下次实验有充足的时间进行设计、修正。