数据库小实验

数据库小实验

---

总述

这次数据库实验我选择了实验一，并且计划通过一个学生选课系统的建立来展现实验内容。

实验过程

实验0：准备实验环境与条件

因为想测试不同sql软件的性能，这里我分别下载了mysql和sqlserver两种不同的sql语言软件。不过在做此实验时主要采用mysql进行操作，本文也以mysql作为范例。

安装mysql的过程较为简单，只需一路按照缺省值确认便可，sqlserver则稍为繁琐些，先要下载各种组件，配置好服务器之后还需下载SSMS进行可视化的sql语言操作。

安装好mysql之后，我们首先打开mysql workbench，新建一个连接后可以进入 local instance MySQL。此时我们便可进入操作主界面。

实验1.1 数据库定义

• 实验目的
  理解和掌握数据库DDL语言，能够熟练地使用SQL DDL语句创建、修改和删除数据库、模式和基本表。
• 实验内容和要求
  理解和掌握SQL DDL语句的语法，特别是各种参数的具体含义和使用方法；使用SQL语句创建、修改和删除数据库、模式和基本表。掌握SQL语句常见语法错误的调试方法。
• 实验结果
  首先，我们需要创建一个数据库。运用语句CREATE DATABASE或者直接在mysql workbench中新建一个数据库。

CREATE DATABASE selectcourse;
use selectcourse;

然后创建各个数据表。
创建学生：

CREATE TABLE STUDENTS
(sid int NOT NULL AUTO_INCREMENT,
sname CHAR (10) NOT NULL,
age int,
gender CHAR(2),
PRIMARY KEY(sid)
);

创建老师、选课、任课等。

CREATE TABLE teachers
(tid int NOT NULL AUTO_INCREMENT,
tname CHAR (10) NOT NULL,
age int,
PRIMARY KEY(tid)
);
CREATE TABLE course
(cid int NOT NULL AUTO_INCREMENT,
cname CHAR (10) NOT NULL,
PRIMARY KEY(cid)
);
CREATE TABLE enroll
(eid int NOT NULL AUTO_INCREMENT,
sid int NOT NULL,
cid int NOT NULL,
grade int ,
PRIMARY KEY(eid),
FOREIGN KEY(sid) REFERENCES students(sid),
FOREIGN KEY(cid) REFERENCES course(cid)
);
CREATE TABLE teaching
(gid int NOT NULL AUTO_INCREMENT,
tid int NOT NULL,
cid int NOT NULL,
PRIMARY KEY(gid),
FOREIGN KEY(tid) REFERENCES teachers(tid),
FOREIGN KEY(cid) REFERENCES course(cid)
);

插入各个元素

INSERT INTO students values(1,'tom',19,'m'),(2,'lisa',18,'f'),(3,'tony',20,'m'),(4,'june',19,'f');
INSERT INTO teachers VALUES (1,'thomas',46),(2,'kroos',36),(3,'elen',27)
INSERT INTO course VALUES(1,'math'),(2,'physics'),(3,'chemistry'),(4,'chinese');
INSERT INTO teaching VALUES(1,1,1),(2,2,1),(3,3,1),(4,1,2)
INSERT INTO enroll VALUES(1,1,1,80),(2,2,1,85),(3,3,1,90),(4,1,2,60)

这样我们便可完成数据库的基本定义了。

实验1.2 数据基本查询

• 实验目的
  掌握SQL程序设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询、分组统计查询和连接查询。

• 实验内容和要求
  针对TPC-H数据库设计各种单表查询SQL语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握SQL查询语句各个子句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并调试通过。

• 实验过程
  本次实验中我们使用了多种查询方式，首先我们使用分组统计查询。
  分组统计查询的语法为：

SELECT [DISTINCT] 分组字段 [AS] [列别名] ,… | 统计函数 [AS] [别名] , ….
FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….
[WHERE 条件(s)]
[GROUP BY 分组字段]
[ORDER BY 排序字段 ASC|DESC] ;

统计每门课的选课人数和平均分数

SELECT cid,COUNT(sid),AVG(grade)
FROM enroll
GROUP BY cid;

![此处输入图片的描述][1]
如果我们希望找出平均分高于70分的课程的选课人数和平均分数，则需要添加HAVING语句

SELECT cid,COUNT(sid),AVG(grade)
FROM enroll
GROUP BY cid
HAVING AVG(grade) >70;

查询结果为
![此处输入图片的描述][2]
之后实现多表连接查询
先是用内连接，找出每个学生每门课的所有分数，语句为：

SELECT s.sid,s.sname,e.cid,e.grade as mark
FROM students s INNER JOIN enroll e
on s.sid=e.sid;

然后是外连接

SELECT s.sid,s.sname,e.cid,e.grade as mark
FROM students s left join enroll e
on s.sid=e.sid;

![此处输入图片的描述][3]
可以看出外连接与内连接相比多出了内容不完整的信息，而缺失的那部分则用null表示

实验1.3数据高级查询

• 实验目的
  掌握SQL嵌套查询和集合查询等各种高级查询的设计方法等

• 实验内容和要求
  针对TPC-H数据库，正确分析用户查询要求，设计各种嵌套查询和集合查询。

• 实验过程
  在SQL语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询。例如：

SELECT Sname /外层查询或父查询/
FROM Student
WHERE Sno IN
(SELECT Sno /内层查询或子查询/
FROM SC
WHERE Cno=’2’);

嵌套查询也分为许多种类，首先我们进行带有in谓词的子查询。
找出选修了数学课的学生信息

SELECT sid,sname
from students
where sid in
(SELECT sid
from enroll
where cid IN
(select cid
from course
where cname=’math’)
);

查询结果为:
![此处输入图片的描述][4]
之后进行带有比较运算符的子查询：
找出每个学生超过他自己选修课程平均成绩的课程号

SELECT sid,cid
from enroll e1
where grade>=(select AVG(grade)
from enroll e2
where e2.sid=e1.sid);

带有ANY（SOME）或ALL谓词的子查询。
子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。
查询哪些课程只有男生选读：

SELECT cname
from course c
where ‘m’=all
(select gender
from enroll,students
where enroll.sid=students.sid and enroll.cid=c.cid);

![此处输入图片的描述][5]
最后是带有 EXISTS 谓词的子查询
带有EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
找出所有选修了一号课程的学生

select sname
from students
where exists
(select *
from enroll
where sid=students.sid and cid=1)

![此处输入图片的描述][6]

实验1.4 数据更新

• 实验目的
  熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作。

• 实验内容和要求
  针对TPC-H数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。

• 实验过程
  之前的实验中已经实现了插入操作，这里我们主要进行修改和删除操作。
  修改sid为1的学生的年龄为22

update students
set age=22
where sid=1

删除sname为anna的学生

delete from students
where sname=’anna’

实验1.5 视图

• 实验目的
  熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。
• 实验内容和要求
  针对给定的数据库模式，以及相应的应用需求，创建视图、创建带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。
• 实验过程
  视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。
  视图不能被修改，表修改或者删除后应该删除视图再重建。
  视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。
  视图可以被嵌套，一个视图中可以嵌套另一个视图。
  视图不能索引，不能有相关联的触发器和默认值，sql server不能在视图后使用order by排序。
  视图的功能有
  1.简化用户操作
  2.能以不同的角度观察同一个数据库
  3.对重构数据库提供了逻辑独立性：

首先我们创建一个只包含男生的视图

create view male as
select sid,sname
from students
where gender=’m’

之后成功插入视图一个新值

insert into male
values(23,’sny’)

然后我们加上with check option，再 创建一个新视图

create view male1 as

select sid,sname
from students
where gender=’m’
with check option;

这时我们再插入一个新值时却报错了。报错信息为Error Code: 1369. CHECK OPTION failed ‘selectcourse.male1’
为什么会出现这种情况呢？经查询后得知，with check option可以这么解释：通过视图进行的修改，必须也能通过该视图看到修改后的结果。比如你insert，那么加的这条记录在刷新视图后必须可以看到；如果修改，修改完的结果也必须能通过该视图看到；如果删除，当然只能删除视图里有显示的记录。因为我们所加的值无法确定是属于男生的，所以自然无法进行插入操作，诸如update和delete操作也是如此的。

实验1.6 索引

• 实验目的
  掌握索引设计原则和技巧，能够创建合适的索引以提高数据库查询、统计分析效率。
• 实验内容和要求
  针对给定的数据库模式和具体应用需求，创建唯一索引、函数索引、复合索引等；修改索引；删除索引。设计相应的SQL查询验证索引有效性。学习利用EXPLAIN命令分析SQL查询是否使用了所创建的索引，并能够分析其原因，执行SQL查询并估算索引提高查询效率的百分比。要求实验数据集达到10万条记录以上的数据量，以便验证索引效果。

• 实验过程
  由于我们的表数据不是很多，所以建立索引比较简单。这里只是举一个简单的例子。

  CREATE INDEX ID
  ON studnets (sid)

  实验心得和体会

  这次的数据库小实验可以说是对sql语言实践的初试水，不得不说，从中的收获还是很大的。数据库在很多语言的编程中都有使用，所以也算一项很基本的技能了。而在数据库语言中，sql又是一门最为常用也最为广泛的关系型语言。于是熟练掌握sql语言便成为了我们很重要的一个任务。相对于其他高级编程语言，sql由于本身结构化的特点，较为容易入手。不过，由于sql语言自身的特性和数据结构的复杂性，要想深入掌握sql语言还是需要花费一定精力的。
  这次实验中，我分别使用了mysql和sqlserver两种数据库程序，发现两者的语句大部分还是类似的。只是在某些特定语句上，sql语句会出现一些兼容性的问题。譬如，在mysql中可以使用AUTO_INCREMENT语句，但是在移植至sqlserver中时却出现了错误。这便说明不同的平台上我们还需掌握不同的语法。SQL语句是一种关系型数据库语言，因此我们可以为数据库中的各个元素创建一定的关系，这也算是数据库中的一个重点了。通过创建外键等关联手段将各个表联系起来，这样便可使查找的时候更方便。
  sql中比较难的一点是一些复杂的嵌套查询，如果没有搞清楚数据之中的逻辑，很容易造成一些奇怪的错误，甚至有时候自己都没有察觉出。不过与此同时，嵌套查询给我们带来的收益也是很大的，可以通过嵌套查询得到几乎所有想要的结果。
  除此以外，建立索引对于数据查找的效率帮助还是很大的。在建立大型表的时候基本上都会建立一些必要的索引来提高搜索效率。
  总而言之，熟练掌握sql语言对于程序员来说还是蛮重要的，通过这次实验我也收获了许多新知识，希望在以后的学习开发中能够应用起来。