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

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

实验 1.1 数据库定义实验

1.实验目的

理解和掌握数据库DDL语言，能够熟练地使用SQL DDL语句创建、修改和删除数据库、模式和基本表。

2.实验内容和要求

理解和掌握SQL DDL语句的语法，特别是各种参数的具体含义和使用方法；使用SQL语句创建、删除和修改数据库、模式和基本表。掌握SQL常见语法错误的调试方法。

3.实验重点和难点

实验重点：创建数据库、基本表。 ​ 实验难点：创建基本表时，为不同的列选择合适的数据类型，正确创建表级和列级完整性约束，如列值是否允许为空、主码和外码等。注意：数据完整性约束可以在创建基本表时定义，也可以先创建表然后定义完整性约束。由于完整性约束的限制，被引用的表要先创建。

4.实验过程

本实验建立AAS数据库模式。AAS数据库模式由学生表(student)，课程表(course)，选课表(sc)三个基本表组成。后续的实验也使用该数据库，但根据不同的实验设计添加了其他的基本表。

(1)定义模式

在数据库中创建名为AAS的模式。并切换到该模式。

CREATE SCHEMA AAS;
USE AAS;

(3)定义基本表

在数据库的AAS模式中建立基本表。

CREATE TABLE student(   /*学生信息表*/
    Sno CHAR(9),
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    );
CREATE TABLE Course(    /*课程信息表*/
    Cno CHAR(9),
    Cname CHAR(20),
    Cpno CHAR(9),
    Ctype CHAR(5),
    Cdept CHAR(20),
    Chours SMALLINT,
    Ccredit SMALLINT
    );  
CREATE TABLE SC(        /*选课信息表*/
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT
    );  

5.实验总结

使用CREATE语句定义三个基本表，实体完整性和参照完整性在后续实验中进行定义。

6.思考题

(1)SQL语法规定，双引号括定的符号串为对象名称，单引号括定的符号串为常量字符串，那么什么情况下需要用双引号来界定对象名?

MYSQL中，双引号和单引号都用于表示常量字符串。反引号用于区分保留字和与保留字同名的对象。

(2)数据库对象的完整引用是"服务器名.数据库名.模式名.对象名"，但通常可以省略服务器名和数据库名，甚至模式名，直接用对象名访问对象即可。清设计相应的实验验证基本表及列的访问方法。

MYSQL中不区分数据库名和模式名。如果直接访问对象，需要以模式名.表名访问表，如果表名已经出现了，列名就可以直接使用。

SELECT sno FROM aas.SC;

MYSQL中可以使用USE命令选择数据库或模式，选择后可以直接使用对象名访问对象，不需要添加模式名或数据库名。

USE aas;
SELECT sno FROM SC;

实验 1.2 数据基本查询实验

1.实验目的

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

2.实验内容和要求

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

3.实验重点和难点

实验重点：分组统计查询、单表自身连接查询、多表连接查询。 ​ 实验难点：区分元组过滤条件和分组过滤条件；确定连接属性，正确设计连接条件。

4.实验过程

(1)单表查询(实现投影操作)

查询学生的姓名，性别和年龄。

SELECT Sname,Ssex,Sage
FROM student;

(2)单表查询(实现选择操作)

查询信息科学与工程学院所有男生的所有信息。

SELECT *
FROM student
WHERE sdept="CS" AND ssex="男";

(3)不带分组过滤条件的分组统计查询

查询每个学生的课程门数。

SELECT s.sno,COUNT(cno) "课程数"
FROM student s,sc
WHERE sc.sno=s.sno
GROUP BY s.sno;

(4)带分组过滤条件的分组统计查询

查询课程数大于等于4的学生学号和姓名。

SELECT s.sno,MAX(sname),COUNT(cno) 课程数
FROM student s,sc
WHERE sc.sno=s.sno
GROUP BY s.sno
HAVING 课程数>=4;

(5)单表自身连接查询

查询与学号为202012210的学生修读相同课程的学生学号。

SELECT sc2.sno
FROM sc sc1,sc sc2
WHERE sc1.sno="202012210" AND sc1.cno = sc2.cno;

(6)两表连接查询

查询选修了课程号为5的学生的学生信息。

SELECT *
FROM student,sc
WHERE sc.cno='5' AND sc.sno=student.sno;
/*结果中含有重复的属性列sno，连接时去掉重复的属性列，使用下面的自然连接*/

(7)两表连接查询(自然连接)

查询选修了课程号为5的学生的学生信息。

SELECT *
FROM student
NATURAL JOIN sc
WHERE sc.cno='5';

(8)三表连接查询

查询"姜和光"修读的所有课程的课程编号，课程名，课程学时，课程学分。

SELECT c.cno,c.cname,c.chours,c.ccredit
FROM student s,course c,sc
where s.sname="姜和光" AND s.sno=sc.sno AND sc.cno=c.cno; 

5.实验总结

正确查询需要对数据库模式和各个表的结构充分理解。查询中常用的分组，连接查询需要熟练掌握。需要注意GROUP BY子句使用时，SELECT的属性必须都在GROUP子句中。

6.思考题

(1)不在GROUP BY子句出现的属性，是否可以出现在SELECT子句中？请举例并验证。

不可以，例如以下查询语句是不可以执行的。

SELECT cno,COUNT(*) FROM sc GROUP BY sno;

(2)请举例说明分组统计查询中的WHERE和HAVING有何区别?

WHERE是在产生结果之前约束查询到的数据的，且不能使用聚集函数。而HAVING在查询返回结果集以后过滤结果，并可以使用聚合函数。例如以下的语句，只能使用HAVING而不能使用WHERE：

SELECT AVG(grade) FROM SC GROUP BY sno
HAVING AVG(grade) > 80;

(3)连接查询速度是影响关系数据库性能的关键因素，请讨论如何提高连接查询速度。

使用WHERE添加条件，减少需要连接的数据量。充分利用连接条件，当两个表有不止一个连接条件时，全部添加到WHERE子句当中。尽量使用数字型字段，因为连接时数字型字段的比较比字符型更快。

实验 1.3 数据高级查询实验

1.实验目的

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

2.实验内容和要求

针对数据库正确分析用户查询要求，设计各种嵌套查询和集合查询。

3.实验重点和难点

实验重点：嵌套查询。

实验难点：相关子查询、多层EXIST嵌套查询。

4.实验过程

(1)IN嵌套查询

查询选择了信息科学与工程学院开设的课程的学生。

SELECT Sno,Sname
FROM Student
WHERE Sno IN (
    SELECT Sno
    FROM SC,Course C
    WHERE SC.Cno=C.Cno AND C.Cdept="信息科学与工程学院"
);

(2)带有比较运算符的子查询

查询每个学生大于等于他自己选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC
WHERE GRADE>=(
    SELECT AVG(GRADE)
    FROM SC SC1
    WHERE SC.Sno=SC1.Sno
);

(3)带有ANY或ALL的子查询

查询非计算机科学系比计算机科学系中任意一个学生年龄小的学生姓名和年龄。

SELECT Sname,Sage
FROM Student 
WHERE Sdept!="CS" AND Sage<ANY(
    SELECT Sage
    FROM Student
    WHERE Sdept="CS"
);

查询非计算机科学系比计算机科学系中所有学生年龄都小的学生姓名和年龄。

SELECT Sname,Sage
FROM Student 
WHERE Sdept!="CS" AND Sage<ALL(
    SELECT Sage
    FROM Student
    WHERE Sdept="CS"
);

(4)单层EXISTS嵌套查询

查询没有修读算法设计与分析课程的计科专业的学生。

SELECT Sno,Sname
FROM Student
WHERE NOT EXISTS (
    SELECT SC.Sno
    FROM SC,Course
    WHERE SC.Cno=Course.Cno AND Course.Cname="算法设计与分析"
        AND Student.Sno=SC.Sno
) AND Sdept="CS";

(5)双层EXISTS嵌套查询

查询修读了所有"刘晨"修读的课程的学生。

/*查找一个学生，不存在刘晨修读而他没有修读的课程*/
SELECT Sno,Sname
FROM Student S
WHERE NOT EXISTS (
    SELECT *            /*刘晨修读而他没有修读的课程*/
    FROM SC,Student S1    /*从SC中找出了所有刘晨修读的课程*/
    WHERE SC.Sno=S1.Sno AND S1.Sname="刘晨" AND NOT EXISTS(
        SELECT *
        FROM SC SC1
        WHERE SC1.Cno=SC.Cno AND SC1.Sno=S.Sno    
    )
);

(6)FROM子句中的嵌套查询(基于派生表的查询)

查询修读课程平均成绩超过80的计科专业的学生。

SELECT S.*
FROM Student S,(                     /*子查询生成的临时派生表为T表*/
    SELECT Sno,AVG(GRADE) avg_grade
    FROM SC 
    GROUP BY SC.Sno
    ) T
WHERE S.Sno = T.Sno AND S.Sdept='CS' AND T.avg_grade>80;

(7)集合查询(交)

查询"刘晨"和"李勇"都修读过的课程的信息。使用的MYSQL不支持INTERSECT语句，以下是SQL语句的方式。

SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="刘晨"
INTERSECT
SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="李勇";

使用嵌套查询替代INTERSECT语句进行以上查询如下：

SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="刘晨" AND C.Cno IN(
    SELECT C1.Cno
    FROM Student S1,SC SC1,Course C1
    WHERE S1.Sno=SC1.Sno AND SC1.Cno=C1.Cno AND S1.Sname="李勇"
);

(8)集合查询(并)

查询"刘晨"和"李勇"修读过的课程的信息

SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="刘晨"
UNION
SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="李勇";

(9)集合查询(差)

查询"刘晨"修读过而"李勇"没有修读过的课程信息。MYSQL不支持EXCEPT语句，SQL语句使用EXCEPT进行查询的方式如下：

SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="刘晨"
EXCEPT
SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="李勇";

使用嵌套查询替代EXCEPT语句进行查询如下：

SELECT C.* 
FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND S.Sname="刘晨" AND C.Cno NOT IN(
    SELECT C1.Cno
    FROM Student S1,SC SC1,Course C1
    WHERE S1.Sno=SC1.Sno AND SC1.Cno=C1.Cno AND S1.Sname="李勇"
);

5.实验总结

SQL中没有全称量词和蕴含，可以通过等价转换，使用EXISTS和NOT EXISTS语句实现。集合查询的交INTERSECT和差EXCEPT在MYSQL中没有语句，可以使用嵌套查询的方式实现。

6.思考题

(1)试分析什么类型的查询可以用连接查询实现，什么只能用嵌套查询实现。

当所查询的内容在多个表中时，需要通过连接查询实现，而当所查询的内容在一个表中，而查询的条件需要对其他表的数据进行统计时，就只能使用嵌套查询。

(2)试分析不相关子查询和相关子查询的的区别。

不相关子查询中，子查询的查询条件不依赖于父查询，查询的一种方法是先执行子查询，子查询的结果用于父查询的条件。而相关子查询中，子查询的查询条件依赖于父查询，查询中需要先从外层查询取出一个元组，执行内层查询，再执行外层查询，然后从外层查询中取出下个元组重复上述过程。

实验 1.4 数据更新实验

1.实验目的

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

2.实验内容和要求

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

3.实验重点和难点

实验重点：插入、修改和删除数据的SQL。

实验难点：与嵌套SQL子查询相结合的插入、修改和删除数据的SQL语句；利用一个表的数据来插入、修改和删除另外一个表的数据。

4.实验过程

(1)插入单个元组

不指明属性列，按照顺序插入单个元组。

INSERT
INTO Student
VALUES("202015705","张玥","男",20,"MA");

指明属性列，没有赋值的属性列将自动赋空值。

INSERT 
INTO SC(Sno,Cno)
VALUES("202015705","9");

(2)插入子查询结果

对每一个课程，求课程的平均成绩，并把结果存入数据库。

CREATE TABLE C_avg_grade(
    Cno CHAR(9),
      avg_grade SMALLINT  
);
INSERT 
INTO C_avg_grade(Cno,avg_grade)
SELECT Cno,AVG(GRADE)
FROM SC
GROUP BY Cno;

(3)修改单个或多个元组的值

将学生"刘晨"的年龄改为21岁。

UPDATE Student
SET Sage=21
WHERE Sname="刘晨";

将所有学生的年龄+1。

UPDATE Student
SET Sage=Sage+1;

(4)带子查询的修改语句

将计算机科学系学生的成绩清0。

UPDATE SC
SET GRADE=0
WHERE Sno IN(
    SELECT Sno
    FROM Student
    WHERE Sdept="CS"
);

(5)删除单个或多个元组的值

删除学号为"202014019"的学生。

DELETE
FROM Student
WHERE Sno="202014019";

删除所有选课记录。

DELETE
FROM SC;

(6)带子查询的删除语句

删除计算机科学系学生的选课记录。

DELETE 
FROM SC
WHERE Sno IN(
    SELECT Sno
    FROM Student
    WHERE Sdept="CS"
);

5.实验总结

更新或删除数据时，需要考虑完整性约束，此时该表中还没有添加约束，因此暂时没有考虑。在使用带有子查询的更新时，更新的表不能在查询中也使用，需要借助临时表进行更新。

6.思考题

(1)请分析数据库模式更新和数据更新SQL语句的异同。

两种语句都会改变数据库中的数据。数据库模式更新使用ALTER语句，改变的是关系模式。数据更新使用UPDATE语句，改变的是关系的值。

(2)请分析数据库系统除了INSERT、UPDATE和DELETE等基本的数据更新语句，还有哪些可以用来更新数据库基本表数据的SQL语句?

MERGE INRO语句可以用来更新或插入基本表。用法如下：

merge into products p using newproducts np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name 
when not matched then 
insert values(np.product_id, np.product_name, np.category) 

TRUNCATE语句可以用于清空表：

truncate table tbl_name

实验 1.5 视图实验

1.实验目的

熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。

2.实验内容和要求

针对数据库模式的相应应用需求，创建视图和带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。

3.实验重点和难点

实验重点：创建视图。

实验难点：可更新的视图和不可更新的视图之区别，WITH CHECK OPTION的验证。

4.实验过程

(1)创建视图

省略视图列名，建立计科专业学生的视图，该视图由单个表导出，且保留了主码，为行列子集视图。

CREATE VIEW CS_Student
AS
SELECT Sno,Sname,Sage,Sdept
FROM Student
WHERE Sdept="CS";

建立学号和平均成绩的视图，这种视图带有聚集函数和GROUP BY子句查询，称为分组视图。

CREATE VIEW S_G(Sno,avg_grade)
AS
SELECT Sno,AVG(GRADE)
FROM SC
GROUP BY Sno;

(2)建立视图(WITH CHECK OPTION)

使用WITH CHECK OPTION，建立计科专业学生的视图。

CREATE VIEW CS_Student
AS
SELECT Sno,Sname,Sage,Sdept
FROM Student
WHERE Sdept="CS"
WITH CHECK OPTION;

通过该视图对计科专业学生的记录分别进行增加，删除，修改。

/*不满足Sdept="CS"，CHECK OPTION FAILED，不能增加*/
INSERT 
INTO CS_Student
VALUES("201902156","张权",21,"EE");
/*增加*/
INSERT 
INTO CS_Student
VALUES("201902156","张权",21,"CS");
/*删除*/
DELETE
FROM CS_Student
WHERE Sname="张权";
/*不满足CHECK OPTION的修改*/
UPDATE CS_Student
SET Sdept="EE"
WHERE Sname="刘晨";
/*修改*/
UPDATE CS_Student
SET Sage=22
WHERE Sname="刘晨";

(3)可更新的视图

建立机械专业学生的视图。并对其进行更新。

/*建立视图*/
CREATE VIEW ME_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept="ME";
/*增加*/
INSERT 
INTO ME_Student
VALUES("201802056","张三",21);
/*修改*/
UPDATE ME_Student
SET Sage=22
WHERE Sname="张三";
/*删除*/
DELETE
FROM ME_Student
WHERE Sname="张三";

以上的行列子集视图是可以更新的，因为视图中的属性和子查询中的表达式无关，所以可以进行任何增加，删除和修改。下面建立一个视图，仍然是机械专业学生的视图，但视图的属性列Sdept在建立视图的子查询中出现了。

CREATE VIEW ME_Student
AS
SELECT Sno,Sname,Sage,Sdept
FROM Student
WHERE Sdept="ME";
/*插入*/
INSERT
INTO ME_Student
VALUES("201802056","张三",21,"CS");
/*删除*/
DELETE
FROM ME_Student
WHERE Sname="张三";

对以上视图插入一个计科专业的学生，可以正常插入，因为没有添加WITH CHECK OPTION，更新的行不满足视图定义也可以插入，对视图的插入会通过视图消解转换为对基本表的插入。因此完成插入后，基本表中可以查询到插入的元组，但视图中查询不到，也不能在视图中删除该元组。

(4)不可更新的视图

一般情况下，只有行列子集视图是可以更新的，如果视图由两个表导出，或定义中含有聚集函数和GROUP BY子句，以及定义中含有DISTINCT短语等情况，视图都是不可更新的。例如对(1)中建立的学号和平均成绩的视图就是不可更新的，因为使用了聚集函数，不可以通过修改其他数据将平均成绩修改。

/*不可以对该视图进行更新*/
INSERT
INTO S_G
VALUES("201714021",80);

(5)删除视图

创建学生选课明细视图V_SC，列出学生学号，姓名，课程，课程类型，成绩，在该视图的基础上创建平均专业核心课成绩视图V_avgGrade，列出学生学号，姓名，核心课程成绩。分别利用RESTRICT 选项和CASCADE选项删除V_SC。

/*创建选课明细视图V_SC*/
CREATE VIEW V_SC
AS
SELECT S.Sno,S.Sname,C.Cname,C.Ctype,SC.GRADE
FROM Student S,Course C,SC
WHERE S.Sno=SC.Sno AND C.Cno=SC.Cno;
/*创建平均核心课成绩视图V_avgGrade*/
CREATE VIEW V_avgGrade(Sno,Sname,avg_grade)
AS
SELECT Sno,MAX(Sname),AVG(GRADE)
FROM V_SC
WHERE Ctype="专业核心"
GROUP BY Sno;
/*RESTRICT删除*/
DROP VIEW V_SC RESTRICT;
/*CASCADE删除*/
DROP VIEW V_SC CASCADE;

使用RESTRICT删除后，V_SC视图被删除，V_avgGrade没有被删除，但不能进行查询了。使用CASCADE删除的情况是一样的。在MYSQL的文档中，对于DROP VIEW语句有如下补充：RESTRICT and CASCADE, if given, are parsed and ignored.因此MYSQL中在删除视图时RESTRICT和CASCADE选项无效。

5.实验总结

视图可以简化用户查询，并可以提供一定的安全保护，向用户隐藏一些数据。视图是虚表，查询与更新都需要通过视图消解，转换为对基本表的查询与更新。通常行列子集视图是可以更新的。

6.思考题

(1)请分析视图和基本表在使用方面有哪些异同，并设计相应的例子进行验证。

视图是虚表，而基本表是存在的表。视图不占用物理空间，只存在于数据字典当中。使用视图和表的基本方法一样，但视图可能无法更新。视图的删除不会对基本表产生影响，但基本表删除后会导致视图无效。

/*创建一个视图*/
CREATE VIEW v_stu_grade
AS
SELECT student.sno,AVG(grade) avg_grade
FROM SC,Student
GROUP BY student.sno; 
/*该视图是不可更新的*/
UPDATE v_stu_grade
SET avg_grade = 100;      /*无法执行*/
/*删除基本表，该视图将无法查询*/
DROP TABLE SC;
SELECT * FROM v_stu_grade;/*无法执行*/

(2)请具体分析修改基本表的结构对相应的视图产生何种影响。

如果修改基本表中，出现在视图的条件子句中的列的结构，会导致视图无效，如果修改结构但与视图涉及的列无关，则不影响视图。

实验 1.6 索引实验

1.实验目的

掌握索引设计原则和技巧，能够创建合适的索引以提高数据库查询、统计分析效率。

2.实验内容和要求

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

3.实验重点和难点

实验重点：创建索引。

实验难点：设计SQL查询验证索引有效性。

4.实验过程

为了验证索引效果，需要数据量足够大，因此使用实验指导提供的零件供应商模式。该模式包括Part，Supplier和PartSupp三个基本表。

建立如下：

CREATE SCHEMA PS;
USE PS;
CREATE TABLE Supplier(/*供应商基本表*/
    suppkey INTEGER PRIMARY KEY,
    sname CHAR(25),
    address VARCHAR(40),
    nationkey INTEGER,
    PHONE CHAR(15),
    acctbal REAL,
    comment VARCHAR(101)
);
CREATE TABLE Part(/*零件基本表*/
    partkey INTEGER PRIMARY KEY,
    pname VARCHAR(55),
    mfgr CHAR(25),
    brand CHAR(10),
    type VARCHAR(25),
    size INTEGER,
    container CHAR(10),
    retailprice REAL,
    comment VARCHAR(23)
);
CREATE TABLE PartSupp(/*零件供应联系表*/
    partkey INTEGER REFERENCES Part(partkey),
    suppkey INTEGER REFERENCES Suppliers(suppkey),
    availqty INTEGER,
    supplycost REAL,
    comment VARCHAR(199),
    PRIMARY KEY(partkey,suppkey)
);

(1)创建唯一索引

在供应商基本表建立供应商名的唯一索引。

CREATE UNIQUE INDEX idx_supplier_name ON Supplier(name);

(2)创建复合索引

在零件表的制造商和品牌两个字段上创建一个复合索引。

CREATE UNIQUE INDEX idx_part_mfgr ON Part(mfgr,brand);

(3)创建函数索引

对某个属性的函数创建索引，称为函数索引。

函数索引用适用于一种情况：被索引的列或列集合包含在某个表达式或是函数中，而表达式又经常出现在WHERE子句或ORDER BY子句中。

例如有一个表t，包含a，b两个字段，并且经常要执行类似的如下的查询：

SELECT *
FROM t
WHERE a*b>100;

为了加速查询，就可以创建如下函数索引：

CREATE INDEX idx ON t(a*b);

综上，如果查询条件为一个表达式，且经常进行查询，就可以添加函数索引。

在零件供应联系表上创建一个函数索引。

CREATE INDEX idx_part_name_fun 
ON 
PartSupp((supplycost/availqty));

(4)创建聚簇索引

聚簇存取方法：

为了提高某个属性或属性组的查询速度，把这个或这些属性上具有相同值的元组集中存放在连续的物理块中称为聚簇。该属性称为聚簇码。聚簇功能可以大大提高按聚簇码查询的效率。例如查询信息系的学生，尽管按照系的信息建立了索引，但数据可能分布在不同的物理块上，需要多次I/O。如同一系的学生集中存放，就能减少访问磁盘的次数。

聚簇索引：

使用CLUSTER语句可以使数据库管理系统根据指定的索引将表聚集，即让表按索引信息的顺序排序。建立聚簇索引必须在表中已有索引，然后根据索引使表聚集。在MYSQL使用的InnoDB中，主键将作为聚簇索引，如果表没有主键，MySQL将搜索UNIQUE所有键列所在的第一个索引，并将此UNIQUE索引用作聚簇索引。如果InnoDB表没有主键或合适的UNIQUE索引，MySQL会在内部生成一个隐藏的聚簇索引GEN_CLUST_INDEX，值为行ID。

以下是使用CLUSTER建立聚簇索引的语句，在PostgreSQL中可以执行。

在零件表的制造商字段上创建一个聚簇索引。

CREATE UNIQUE INDEX idx_part_mfgr ON Part(mfgr);
CLUSTER idx_part_mfgr ON Part;

(5)创建Hash索引

在供应商表的名称字段上创建一个Hash索引。

CREATE INDEX idx_supplier_name_hash USING HASH ON Supplier(sname);

MYSQL提示存储引擎不支持HASH索引。经查询，MYSQL使用InnoDB，用户无法手动创建HASH索引，但InnoDB会自调优，如果判断建立自适应哈希索引能够提升查询效率，InnoDB会自己建立相关的哈希索引。

(6)修改索引名称

修改供应商表的名称字段的索引名。

ALTER TABLE supplier RENAME INDEX idx_supplier_name TO idx_supplier_name1;

(7)分析某个SQL查询语句执行时是否使用了索引

EXPLAIN SELECT * FROM supplier WHERE sname="上海江宏路基材料有限公司";

从EXPLAIN语句的结果可以确认以上查询使用了供应商表的名称字段的索引。

(8)验证索引效率

创建一个TestIndex，计算SQL查询执行的时间。

/*开启了bin-log, 需要制定函数类型*/
SET GLOBAL log_bin_trust_function_creators = 1;
/*delimiter // 的意思是把 // 当作结束符，mysql在编译的时候就不会因为语句中有多个符号而报错。*/
delimiter //
CREATE 
FUNCTION TestIndex(p_partname CHAR(55)) RETURNS INTEGER
BEGIN
    DECLARE begintime,endtime TIMESTAMP(3);
    DECLARE durationtime,result INTEGER;
    SELECT current_timestamp(3) INTO begintime;
    SELECT partkey FROM Part WHERE pname=p_partname INTO result ;
    SELECT current_timestamp(3) INTO endtime;
    select TIMESTAMPDIFF(MICROSECOND,begintime,endtime) INTO durationtime;
    RETURN durationtime;
END//
delimiter ;

先查看零件表Part数据规模比较小，并且无索引时的执行时间。发现返回值是0。

/*查看当零件表Part数据规模比较小，并且无索引时的执行时间*/
SELECT TestIndex("单轴振动筛");

TIMESTAMPDIFF仅支持到秒的精度，在这个精度无法比较有无索引对查询速度的影响，因此不使用函数进行比较，直接查看SQL语句执行的duration time比较查询时间。

倍增零件表的数据，这里直接倍增到了百万级别。

INSERT 
INTO Part
    SELECT partkey + 100*(SELECT COUNT(*) FROM Part),
        pname,mfgr,brand,type,size,container,retailprice,comment
    FROM Part;

先在无索引的情况下执行以下查询，用时32ms

select * from part where pname="压滤机";

创建零件表名称的索引，然后再执行查询。用时小于ms级。

CREATE INDEX idx_partname ON Part(pname);
select * from part where pname="压滤机";

通过以上的比较，尽管不能计算创建索引后查询效率提高的百分比，但可以验证创建索引后，查询效率明显提高了。

5.实验总结

创建索引可以显著提高查询效率。索引有多种，包括唯一索引，复合索引，函数索引，Hash索引等。通过实验可以看出，存储引擎会使用索引加速查询，对于如何建立一些类型的索引，不同的存储引擎有不同的策略。

6.思考题

在一个表的多个字段上创建的复合索引，与在相应的每个字段上创建的多个简单索引有何异同？请设计相应的例子加以验证。

复合索引和多个简单索引都能加快查询速度。差别使用以下的例子来说明。

假设创建一个组合索引和一个单个索引。

CREATE INDEX combined_idx ON Student(Sdept,Sname);
CREATE INDEX single_idx ON Student(Sdept);

使用以下两个查询时：

EXPLAIN SELECT * FROM student WHERE Sdept='CS' AND Sname = '王欣';
EXPLAIN SELECT * FROM student WHERE  Sdept = 'CS';

两个索引都包含了Sdept，但MYSQL在第二次搜索时没有使用单个列的简单索引，而是使用了复合索引。如果使用多个条件查询而没有复合索引时，就只能使用单个索引，会降低查询速度。

再建立一个单独的索引，进行查询：

CREATE INDEX single_idx2 ON Student(Sname);
EXPLAIN SELECT * FROM student WHERE  Sname = '王欣';

可以看到这次使用的是单个索引，而不是复合索引。这是因为创建复合索引时，会先按复合索引中的第一列进行排序，然后使用第二列排序。在以上的查询中，Sname是复合索引的第二列，因此不如直接使用单个索引。从以上例子可以看出索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引更快且会被使用；仅对后面的任意列执行搜索时，复合索引没有作用，使用单个索引才能提高查询速度。