HNU_数据库_实验1（2021级）-数据库定义与操作语言实验

《数据库》第一次实验

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

0.LAB1-SQL语句汇总

CREATE DATABASE ST;
CREATE TABLE student(   /*学生信息表*/
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);
CREATE TABLE Course(    /*课程信息表*/
    Cno CHAR(9) PRIMARY KEY,
    Cname CHAR(20),
    Cpno CHAR(9) NULL,
    Ccredit SMALLINT,
    FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);  
CREATE TABLE SC(        /*选课信息表*/
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    FOREIGN KEY(Cno) REFERENCES Course(Cno)
);  
# 查询学生的姓名，性别和年龄
SELECT Sname,Ssex,Sage FROM student;

# 查询信息科学与工程学院所有男生的所有信息
SELECT * FROM student WHERE Sdept='cs' AND Ssex='男';

# 查询每个学生的课程门数
SELECT Sno,count(Cno) FROM sc group by Sno; 
SELECT cno,COUNT(*) FROM sc group by Sno; # 报错
SELECT S.Sno,Count(Cno) FROM STUDENT S,SC WHERE SC.Sno=S.Sno GROUP BY S.Sno;

# 查询课程数大于2的学生学号,姓名,选课数量
SELECT S.Sno,Sname,count(Cno) FROM student S,sc WHERE SC.Sno=S.Sno GROUP BY S.Sno HAVING count(Cno)>2;

# 查询与学号为200215121的学生修读相同课程的学生学号
SELECT DISTINCT sc2.Sno FROM SC sc1,SC sc2 WHERE sc1.Sno='200215121' AND sc1.Cno=sc2.Cno;

# 查询选修了课程号为1的学生的学生信息
SELECT * FROM student,sc WHERE sc.Cno='1' AND student.Sno=sc.Sno;
SELECT student.*,Cno,Grade FROM student,sc WHERE sc.Cno='1' AND student.Sno=sc.Sno;

# 查询"李勇"修读的所有课程的课程编号，课程名，课程学分
SELECT course.Cno,course.Cname,course.Ccredit FROM student,sc,course 
	WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND sc.Cno=course.Cno;
    
# 查询选择了学分为4的课程的学生
SELECT Sno,Sname FROM student WHERE Sno IN(
	SELECT Sno FROM sc WHERE Cno IN(
		SELECT Cno FROM course WHERE Ccredit=4
    )
);

# 查询每个学生大于等于他自己选修课程平均成绩的课程号
SELECT Sno,Cno FROM sc WHERE Grade>=(
	SELECT AVG(Grade) FROM sc sc1 WHERE sc.Sno=sc1.Snocustomer
);

# 查询非计算机科学系比计算机科学系中任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM student WHERE Sdept!='CS' AND Sage < ANY(
	SELECT Sage FROM student s1 WHERE Sdept='CS'
);
# 查询非计算机科学系比计算机科学系中所有一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM student WHERE Sdept!='CS' AND Sage < ALL(
	SELECT Sage FROM student s1 WHERE Sdept='CS'
);

# 查询没有修读数学课程的IS专业的学生：
SELECT Sno,Sname FROM student WHERE  Sdept='IS' and
	NOT EXISTS(
	SELECT sc.Sno FROM sc,course WHERE sc.Cno=course.Cno AND course.Cname='数学' AND Student.Sno=SC.Sno
);

# 查询修读了所有"刘晨"修读的课程的学生 
/*查找一个学生，不存在刘晨修读而他没有修读的课程*/
SELECT Sno,Sname FROM student s WHERE
	NOT EXISTS(
		SELECT * FROM sc,student s1 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
		)
);

# 查询修读课程平均成绩超过80的计科专业的学生
SELECT S.*,t.avg_grade FROM student s,(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;
    
# 查询"刘晨"和"李勇"都修读过的课程的信息。
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
INTERSECT
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;
#使用嵌套查询替代INTERSECT语句进行以上查询如下：
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
	AND course.CNO IN (SELECT course.Cno FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno);
    
# 查询"刘晨"和"李勇"修读过的课程的信息
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
UNION
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;

# 查询"李勇"修读过而"刘晨"没有修读过的课程信息。MYSQL不支持EXCEPT语句，SQL语句使用EXCEPT进行查询的方式如下：
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
EXCEPT
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;
#使用嵌套查询替代EXCEPT语句进行查询如下：
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
	AND course.Cno NOT IN(SELECT course.Cno FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno);
    
# 不指明属性列，按照顺序插入单个元组
INSERT INTO student VALUES('202103008','段富钊','男',21,'CS');
SELECT * FROM student;
# 指明属性列，没有赋值的属性列将自动赋空值
INSERT INTO sc(Sno,Cno) Values('202103008',5);
SELECT * FROM sc;

# 对每一个课程，求课程的平均成绩，并把结果存入数据库。
CREATE TABLE C_avg_grade(
	Cno Char(9),
    avg_grade SMallINT
);
INSERT INTO C_avg_grade SELECT Cno,AVG(GRADE) FROM SC GROUP BY Cno;
SELECT * FROM C_avg_grade;

# 将学生"段富钊"的年龄改为81岁
SET sql_safe_updates = false;
UPDATE Student SET Sage=81 WHERE Sname='段富钊';
SELECT * FROM student;
#将所有学生的年龄+1
UPDATE student SET Sage=Sage+1;
SELECT * FROM student;

# 将计算机科学系学生的成绩清0。
UPDATE sc SET Grade=0 WHERE Sno IN(
	SELECT Sno FROM student WHERE Sdept='CS'
);
SELECT * from sc;

# 删除学号为"202103008"的学生的选课记录
DELETE FROM sc where sno='202103008';
select * from sc;

# 删除所有选课记录
DELETE from sc;
select * from sc;

# 删除计算机科学系学生的选课记录
DELETE FROM sc WHERE sno in(
	select sno from student where sdept='cs'
);
select * from sc;

# 省略视图列名，建立计科专业学生的视图，该视图由单个表导出，且保留了主码
create view cs_student as 
	select sno,sname,sage,sdept from student where sdept='cs';
select *  from cs_student;

# 建立学号和平均成绩的视图，这种视图带有聚集函数和GROUP BY子句查询
create view s_g(sno,avg_grade) as
	select sno,AVG(grade) from sc group by sno;
select * from s_g;

# 使用WITH CHECK OPTION，建立计科专业学生的视图。
create view cs_student1 as 
	select sno,sname,sage,sdept from student where sdept='cs'
    with check option;
select *  from cs_student1;

/*不满足Sdept="CS"，CHECK OPTION FAILED，不能增加*/
INSERT INTO cs_student1 values('202103001','张飞',28,'EE');
INSERT INTO cs_student1 values('202103001','张飞',28,'cs');
select * from student;
delete from cs_student1 where sname='张飞';
select * from student;
/*不满足CHECK OPTION的修改*/
UPDATE cs_student1 set sdept='ee' where sname='段富钊';
UPDATE cs_student1 set sage=19 where sname='段富钊';
select * from student;

# 建立机械专业学生的视图。并对其进行更新
create view me_student as
	select sno,sname,sage from student where sdept='me';
select * from me_student;
insert into me_student values('201802056','陈真',24);
select * from student;
update me_student set sage=80 where sname='陈真';
delete from me_student where sname='陈真';

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


/*创建选课明细视图V_SC*/
create view v_sc as
	select s.sno,s.sname,c.cname,sc.grade from student s,course c,sc where s.sno=sc.sno and c.cno=sc.cno;
select * from v_sc;
/*创建学生平均成绩视图V_avgGrade*/
create view v_avgGrade(sno,sname,avg_grade) as
	select sno,max(sname),avg(grade) from v_sc group by sno;
select * from v_avgGrade;
DROP view v_sc restrict;
DROP view v_sc cascade;

# 在学生表建立学生姓名的唯一索引
CREATE UNIQUE INDEX student_name ON student(Sname);
show index from student;

# 在选课表的学号和课号两个字段上创建一个复合索引
CREATE UNIQUE INDEX idx_sno_cno ON SC(Sno,Cno);
SHOW INDEX FROM SC;

# 在选课表上创建一个平均成绩索引
CREATE UNIQUE INDEX idx_avg_grade ON SC((Cno*Grade));
show index from sc;

# 在课程表的课号字段上创建一个聚簇索引
CREATE UNIQUE CLUSTER INDEX idx_cluster ON Course(Cno);
show index from course;
CLUSTER idx_cluster ON Course;

# 在课程表的名称字段上创建一个Hash索引
CREATE UNIQUE INDEX idx_hash USING HASH ON Course(Cname);
show index from course;

# 修改课程表的名称字段的索引名
ALTER TABLE Course RENAME INDEX idx_hash TO idx_hash_alter;
SHOW INDEX FROM Course;

# 分析某个SQL查询语句执行时是否使用了索引
EXPLAIN SELECT * FROM Course where Cno='1';

use st;
# 创建一个TestIndex，计算SQL查询执行的时间
/*开启了bin-log, 需要制定函数类型*/
SET GLOBAL log_bin_trust_function_creators = 1;
/*delimiter // 的意思是把 // 当作结束符，mysql在编译的时候就不会因为语句中有多个符号而报错。*/
delimiter //
CREATE 
FUNCTION TestIndex(name CHAR(55)) RETURNS INTEGER
BEGIN
	DECLARE begintime,endtime TIMESTAMP(3);
	DECLARE durationtime,result INTEGER;
	SELECT current_timestamp(3) INTO begintime;
	SELECT Cno FROM Course WHERE Cno=name INTO result ;
	SELECT current_timestamp(3) INTO endtime;
	select TIMESTAMPDIFF(MICROSECOND,begintime,endtime) INTO durationtime;
	RETURN durationtime;
END//
delimiter ;
/*查看当课程表Course数据规模比较小，并且无索引时的执行时间*/
SELECT TestIndex('1');

INSERT INTO Course SELECT Cno + 100*(SELECT COUNT(*) FROM Course),Cname,Cpno,Ccredit FROM Course;
select * from course;
select * from Course where Cname="数据库";

# 复合索引和多个简单索引的不同
#创建一个组合索引和一个单个索引
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';
#再建一个单独索引
CREATE INDEX single_idx2 ON Student(Sname);
EXPLAIN SELECT * FROM student WHERE  Sname = '段富钊';

1.1 数据库定义实验

1.实验目的

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

2.实验内容和要求

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

3.实验过程

(1)创建数据库ST

CREATE DATABASE ST;

(2)创建数据库表

在ST数据库中创建3个基本表。

• 学生表（student）

CREATE TABLE student(   /*学生信息表*/
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

• 课程表（course）

CREATE TABLE Course(    /*课程信息表*/
    Cno CHAR(9) PRIMARY KEY,
    Cname CHAR(20),
    Cpno CHAR(9) NULL,
    Ccredit SMALLINT,
    FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);  

• 选课表（sc）

CREATE TABLE SC(        /*选课信息表*/
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    FOREIGN KEY(Cno) REFERENCES Course(Cno)
); 

4.实验总结

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

         

5.思考题

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

MYSQL中，双引号和单引号都用于表示常量字符串。

反引号用于区分保留字和与保留字同名的对象。

（2）数据库对象的完整引用是“服务器名.数据库名.模式名.对象名”,但通常可以省略服务器名和数据库名,甚至模式名,直接用对象名访问对象即可。

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

SELECT * FROM sales.customer;

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

use sales;

SELECT * FROM customer;

1.2 数据基本查询实验

1.实验目的

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

2.实验内容和要求

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

        说明:简单地说,SQL程序设计规范包含SQL关键字大写、表名、属性名、存储过程名等标识符大小写混合、SQL程序书写缩进排列等编程规范。

3.实验过程

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

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

SELECT Sname,Ssex,Sage FROM student;

(2)单表查询（实现投影操作）

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

SELECT * FROM student WHERE Sdept='cs' AND Ssex='男';

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

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

SELECT Sno,count(Cno) FROM sc group by Sno;

另解：

SELECT S.Sno,Count(Cno) FROM STUDENT S,SC WHERE SC.Sno=S.Sno GROUP BY S.Sno;

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

查询课程数大于2的学生学号,姓名,选课数量:

SELECT S.Sno,Sname,count(Cno) FROM student S,sc WHERE SC.Sno=S.Sno GROUP BY S.Sno HAVING count(Cno)>2;

(5)单表自身连接查询

查询与学号为200215121的学生修读相同课程的学生学号:

SELECT DISTINCT sc2.Sno FROM SC sc1,SC sc2 WHERE sc1.Sno='200215121' AND sc1.Cno=sc2.Cno;

(6)两表连接查询

查询选修了课程号为1的学生的学生信息:

SELECT * FROM student,sc WHERE sc.Cno='1' AND student.Sno=sc.Sno;

结果中含有重复的属性列sno，连接时去掉重复的属性列，使用下面的自然连接

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

查询选修了课程号为1的学生的学生信息:

SELECT student.*,Cno,Grade FROM student,sc WHERE sc.Cno='1' AND student.Sno=sc.Sno; 

(8)三表连接查询

查询"李勇"修读的所有课程的课程编号，课程名，课程学时，课程学分:

SELECT course.Cno,course.Cname,course.Ccredit FROM student,sc,course

WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND sc.Cno=course.Cno;

4.实验总结

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

5.思考题

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

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

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

WHERE是在产生结果之前约束查询到的数据的，且不能使用聚集函数。

HAVING在查询返回结果集以后过滤结果，并可以使用聚合函数。

例如以下的语句，只能使用HAVING而不能使用WHERE：

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

使用WHERE添加条件，减少需要连接的数据量。

充分利用连接条件，当两个表有不止一个连接条件时，全部添加到WHERE子句当中。

尽量使用数字型字段，因为连接时数字型字段的比较比字符型更快。

1.3 数据高级查询实验

1.实验目的

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

2.实验内容和要求

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

3.实验过程

(1)IN嵌套查询

查询选择了学分为4的课程的学生：

SELECT Sno,Sname FROM student WHERE Sno IN(

SELECT Sno FROM sc WHERE Cno IN(

SELECT Cno FROM course WHERE Ccredit=4

    )

);

(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 s1 WHERE Sdept='CS'

);

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

SELECT Sname,Sage FROM student WHERE Sdept!='CS' AND Sage < ALL(

SELECT Sage FROM student s1 WHERE Sdept='CS'

);

(4)单层EXISTS嵌套查询

查询没有修读数学课程的IS专业的学生：

SELECT Sno,Sname FROM student WHERE  Sdept='IS' and

NOT EXISTS(

SELECT sc.Sno FROM sc,course WHERE sc.Cno=course.Cno AND course.Cname='数学' AND Student.Sno=SC.Sno

);

(5)双层EXISTS嵌套查询

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

/*查找一个学生，不存在刘晨修读而他没有修读的课程*/

SELECT Sno,Sname FROM student s WHERE

NOT EXISTS(

SELECT * FROM sc,student s1 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.*,t.avg_grade FROM student s,(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 course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno

INTERSECT

SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;

(8)集合查询(并)

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

SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno

UNION

SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;

(9)集合查询(差)

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

SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno

EXCEPT

SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;;

4.实验总结

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

5.思考题

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

当所查询的内容在多个表中时，需要通过连接查询实现；

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

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

不相关子查询中，子查询的查询条件不依赖于父查询，查询的一种方法是先执行子查询，子查询的结果用于父查询的条件。

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

1.4 数据更新实验

1.实验目的

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

2.实验内容和要求

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

3.实验过程

(1)插入单个元组：

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

INSERT INTO student VALUES('202103008','段富钊','男',21,'CS');

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

INSERT INTO sc(Sno,Cno) Values('202103008',5);

(2)插入子查询结果 

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

INSERT INTO C_avg_grade SELECT Cno,AVG(GRADE) FROM SC GROUP BY Cno;

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

将学生"段富钊"的年龄改为81岁：

UPDATE student SET Sage=81 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)删除单个或多个元组的值

删除学号为"202103008"的学生的选课记录

DELETE FROM sc where sno='202103008';

删除所有选课记录：

DELETE from sc;

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

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

DELETE FROM sc WHERE sno in(

select sno from student where sdept='cs'

);

4.实验总结

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

5.思考题

（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.实验过程

(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_student1 as

select sno,sname,sage,sdept from student where sdept='cs'

    with check option;

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

• 增加

报错：

正确格式：

• 删除

• 修改

报错：

正确格式：

(3)可更新的视图

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

• 建立视图

• 增加

• 修改

• 删除

        对以上视图插入一个计科专业的学生，可以正常插入，因为没有添加WITH CHECK OPTION，更新的行不满足视图定义也可以插入，对视图的插入会通过视图消解转换为对基本表的插入。

        因此完成插入后，基本表中可以查询到插入的元组，但视图中查询不到，也不能在视图中删除该元组。

(4)不可更新的视图

        一般情况下，只有行列子集视图是可以更新的，如果视图由两个表导出，或定义中含有聚集函数和GROUP BY子句，以及定义中含有DISTINCT短语等情况，视图都是不可更新的。

        例如对(1)中建立的学号和平均成绩的视图就是不可更新的，因为使用了聚集函数，不可以通过修改其他数据将平均成绩修改：

(5)删除视图

        创建学生选课明细视图V_SC，列出学生学号，姓名，课程，课程类型，成绩，在该视图的基础上创建平均专业核心课成绩视图V_avgGrade，列出学生学号，姓名，核心课程成绩。

        分别利用RESTRICT 选项和CASCADE选项删除V_SC：

        使用RESTRICT删除后，V_SC视图被删除，V_avgGrade没有被删除，但不能进行查询了。

使用CASCADE删除的情况是一样的。

        在MYSQL的文档中，对于DROP VIEW语句有如下补充：RESTRICT and CASCADE, if given, are parsed and ignored.因此MYSQL中在删除视图时RESTRICT和CASCADE选项无效。

4.实验总结

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

5.思考题

（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.实验过程

(1)创建唯一索引：

在学生表建立学生姓名的唯一索引：

CREATE UNIQUE INDEX student_name ON student(Sname);

(2)创建复合索引 

在选课表的学号和课号两个字段上创建一个复合索引：

CREATE UNIQUE INDEX idx_sno_cno ON SC(Sno,Cno);

(3)创建函数索引

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

在选课表上创建一个函数索引：

(4)创建聚簇索引

聚簇存取方法：

        为了提高某个属性或属性组的查询速度，把这个或这些属性上具有相同值的元组集中存放在连续的物理块中称为聚簇。该属性称为聚簇码。

        聚簇功能可以大大提高按聚簇码查询的效率。

        例如查询信息系的学生，尽管按照系的信息建立了索引，但数据可能分布在不同的物理块上，需要多次I/O。如同一系的学生集中存放，就能减少访问磁盘的次数。

聚簇索引：

        使用CLUSTER语句可以使数据库管理系统根据指定的索引将表聚集，即让表按索引信息的顺序排序。

        建立聚簇索引必须在表中已有索引，然后根据索引使表聚集。在MYSQL使用的InnoDB中，主键将作为聚簇索引，如果表没有主键，MySQL将搜索UNIQUE所有键列所在的第一个索引，并将此UNIQUE索引用作聚簇索引。如果InnoDB表没有主键或合适的UNIQUE索引，MySQL会在内部生成一个隐藏的聚簇索引GEN_CLUST_INDEX，值为行ID。

在课程表的课号字段上创建一个聚簇索引：

        此处mysql引擎是innodb,而在innodb里的聚簇索引不能单独建立聚簇索引，一般默认主键为聚簇索引。

(5)创建Hash索引

在课程表的名称字段上创建一个Hash索引：

CREATE UNIQUE INDEX idx_hash USING HASH ON Course(Cname);

        MYSQL提示存储引擎不支持HASH索引：

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

(6)修改索引名称

修改课程表的名称字段的索引名：

ALTER TABLE Course RENAME INDEX idx_hash TO idx_hash_alter;

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

从EXPLAIN语句的结果可以确认以上查询使用了课程表的课号字段的索引。

(8)验证索引效率

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

查看当课程表Course数据规模比较小，并且无索引时的执行时间

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

4.实验总结

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

5.思考题

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

• 同：都能加快查询速度
• 异：
  • 创建一个组合索引和一个单个索引

• 查询

两个索引都包含了Sdept，但MYSQL在第二次搜索时没有使用单个列的简单索引，而是使用了复合索引。

如果使用多个条件查询而没有复合索引时，就只能使用单个索引，会降低查询速度。

• 再建立一个单独索引

可以看到这次使用的是单个索引，而不是复合索引。

这是因为创建复合索引时，会先按复合索引中的第一列进行排序，然后使用第二列排序。在以上的查询中，Sname是复合索引的第二列，因此不如直接使用单个索引。

从以上例子可以看出索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引更快且会被使用；仅对后面的任意列执行搜索时，复合索引没有作用，使用单个索引才能提高查询速度。