实验二

前言

  • 实验内容和要求

1．熟练掌握简单查询操作：包括投影、选择、数据排序、分组等查询操作。

2．熟练掌握连接查询操作：包括等值连接、自然连接、笛卡尔积、交叉连接、内连接、外连接、自身连接等操作。

3．熟练掌握数据查询中的嵌套和统计查询的操作方法。

4．具体内容：

（1）用SQL语句表示下列操作，在学生库中实现数据查询。

①求数学系学生的学号和姓名。

②求选修了课程的学生学号。

③求选修001号课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

④求选修课程001且成绩在80～90分之间的学生学号和成绩，并将成绩乘以系数0.8输出。

⑤求数学系或计算机系姓张的学生的信息。

⑥查看选修了课程、但没有成绩学生的学号、姓名、课程号和所在是系部。

⑦查询学生的学号、姓名、课程名和成绩。

⑧分别实现学生和系的交叉连接、内连接、外连接。

（2）在SQL Server查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。具体内容如下：

用SQL语句表示，在学生选课库中实现其数据嵌套查询操作。

（1）求选修了高等数学的学生学号和姓名。

（2）求001课程的成绩高于张力的学生学号和成绩。

（3）求其他系中年龄小于计算机系年龄最大者的学生。

（4）求其他系中比计算机系学生年龄都小的学生。

（5）求选修了001课程的学生姓名。

（6）求没有选修001课程的学生姓名。

（7）查询选修了全部课程的学生的姓名。

  • 参考教材《数据库系统概论第五版》==P89-P111 数据查询==

SQL知识点

实验前,先熟悉常用的查询语句,方便理解以下代码

一、单表查询

仅涉及一个表的查询

1.ORDER BY

对查询结果按照升序(ASC)或降序(DESC)排列,默认为升序

2.WHERE 子句常用的查询条件

比较:=,>,<,>=,<=,!=,!>,!<;NOT+以上运算符

确定集合的范围: BETWEEN AND(闭区间),NOT BETWEEN AND

确定集合: IN,NOT IN(返回为单值时可以使用”=”代替IN)

字符匹配: LIKE,NOT LIKE(如果LIKE后面的匹配串中不含通配符,则可以使用”=”代替LIKE)

空值: IS NULL,IS NOT NULL

多重条件: AND(交集),OR(并集),NOT

3.通配符:”%”,”_”

所有张姓学生: WHERE Sname LIKE '张%

第二个字为”阳”:WHERE Sname LIKE '_阳%'

当需要查询的数据中包含通配符,使用转义字符,例如查找包含”_1920”的数据,WHERE Sname LIKE '\_1920' ESCAPE '\'

4.聚集函数

统计元组个数:COUNT(*)

统计一列中值的个数:COUNT(列名)

计算一列值的总和(此列必须是整数型):SUM(列名)

计算一列值的平均值(此列必须是整数型):AVG(列名)

求一列总的最大值:MAX(列名)

求一列中的最小值:MAX(列名)

5.GROUP BY

GROUP BY指将查询结果按一列或多列的值分组

求各个课程号及对应的选课人数

SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno

查询选修了三门以上课程的学生学号

SELECT Sno
FROM SC
GROUP BY Sno
HAVING GOUNT(*)>3;

二、连接查询

1.自然连接

将目标列中重复的属性列去掉则为自然连接

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

2.自身连接

在Course中,每门课程只有先修课信息,而没有先修课的先修课,要得到这个信息,必须对一门课找到其先修课.再按此先修课的课程号查找它的先修课程.

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

3.外连接

查询SC表,某个学生没有选课,在SC表中为NULL,查询不显示该学生,使用外连接则可以显示所有学生的选课情况

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);

4.多表连接

两个以后上的表进行连接

查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student,Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

三、嵌套查询

1.带有IN谓词的子查询

查询与张帆同一个系学习的学生

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
	(SELECT Sdept
	FROM Student
	WHERE Sname='张帆');

２.带有运算符的子查询

返回值是单值时使用运算符

查询与张帆同一个系学习的学生

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
	(SELECT Sdept
	FROM Student
	WHERE Sname='张帆');

找出每个学生超过他自己选修课平均成绩的课程号

SELECT Sno,Cno
FROM SC x
WHERE Grade >=(SELECT AVG(Grade)
				FROM SC y
				WHERE y.Sno=x.Sno);

３.ANY(SOME)或ALL谓词的子查询

子查询返回单值时可以用比较运算符,但返回多值时要用ANY或ALL谓词修饰符.语义为比较运算符+谓词,例如.大于子查询结果中的某个值>ANY,大于子查询结果中的所有值>ALL

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

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
              FROM Student
              WHERE Sdept='计算机工程系')
AND Sdept<>'计算机工程系';/*注意这是父查询块中的条件*/

使用聚集函数

SELECT Sname,Sage
FROM Student
WHERE Sage<(SELECT MAX(Sage)
              FROM Student
              WHERE Sdept='计算机工程系')
AND Sdept<>'计算机工程系';

4.带有EXISTS谓词的子查询

EXISTS代表存在量词”∃”,带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值true或逻辑假值”false”

查询与张帆在同一个系学习的学生

SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
		(SELECT *
        FROM Student S2
        WHERE S2.Sdept=S1.Sdept AND S2.Sname='张帆');

四、综合实践

查询与张帆在同一个系学习的学生

EXISTS:

SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
		(SELECT *
        FROM Student S2
        WHERE S2.Sdept=S1.Sdept AND S2.Sname='张帆');

IN:

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
		(SELECT Sdept
        FROM Student
        WHERE Sname='张帆');

自身连接:

SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='张帆';

image-20211206113033277

实验内容

一、用SQL语句表示下列操作，在学生库中实现数据查询

1.1求数学系学生的学号和姓名

SELECT Sno,Sname
FROM Student
WHERE Sdept IN('数学系');

image-20211206132423799

1.2求选修了课程的学生学号

SELECT Sno
FROM SC;

image-20211206084118841

1.3求选修001号课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列

SELECT Sno,Grade
FROM SC
WHERE Cno LIKE '001'
ORDER BY Grade DESC;

image-20211206085123587

1.4求选修课程001且成绩在80～90分之间的学生学号和成绩，并将成绩乘以系数0.8输出

第一个Grade表示*0.8,第二个Grade表示命名这列的名称(默认为空)

SELECT Sno,Grade*0.8 Grade
FROM SC
WHERE Grade BETWEEN 80 and 90 AND Cno LIKE '001';

image-20211206085710032

1.5求数学系或计算机系姓张的学生的信息

SELECT *
FROM Student
WHERE Sdept IN ('数学系','计算机工程系') AND Sname LIKE '张%';

image-20211206090244775

1.6查看选修了课程、但没有成绩学生的学号、姓名、课程号和所在的系部

SELECT Student.Sno,Sname,Cno,Sdept
FROM Student,SC
WHERE Grade IS NULL AND Student.Sno=SC.Sno;/*将Student与SC中同一学生的元组连接起来*/

image-20211206091920313

1.7查询学生的学号、姓名、课程名和成绩

==有相同属性时必须进行连接!!!==这里Course与SC中都有Cno,可以删掉Course.Cno=SC.Cno,查看输出结果有什么区别

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno;

image-20211206094743405

1.8分别实现学生和系的交叉连接、内连接、外连接

select Student.*,Dept.* from Dept cross join Student;
select Student.*,Dept.* from Student inner join Dept on Student.Sdept = Dept.Dno;
select Student.*,Dept.* from Student left outer join Dept on Student.Sdept = Dept.Dno;

二、在SQL Server查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作

2.1求选修了高等数学的学生学号和姓名

嵌套查询

SELECT Student.Sno,Sname
FROM Student
WHERE Sno IN
		(SELECT Sno
        FROM SC
        WHERE Cno IN
        	(SELECT Cno 
            FROM Course
            WHERE Cname='数学'));

扩展：连接查询

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Cname='数学' AND Student.Sno=SC.SNo AND Course.Cno=SC.Cno;

image-20211206094516790

2.2求001课程的成绩高于张力的学生学号和成绩

SELECT Student.Sno,Grade
FROM Student,SC
WHERE Grade >ANY
		(SELECT Grade
        FROM SC
        WHERE Cno='001' AND Sno IN
        (SELECT Sno
        FROM Student
        WHERE Sname='张力'))
        AND Student.Sno=SC.Sno;

image-20211206135925891

2.3求其他系中年龄小于计算机系年龄最大者的学生

SELECT Sname,Sage
FROM Student
WHERE Sage <ALL
	(SELECT MAX(Sage)
    FROM Student
    WHERE Sdept='计算机工程系')
    AND Sdept<> '计算机工程系';

image-20211206134617934

2.4求其他系中比计算机系学生年龄都小的学生

SELECT Sname,Sage
FROM Student
WHERE Sage <ALL
	(SELECT Sage
    FROM Student
    WHERE Sdept='计算机工程系')
    AND Sdept<> '计算机工程系';

image-20211206134708152

2.5求选修了001课程的学生姓名

SELECT Sname
FROM Student
WHERE Sno IN
	(SELECT Sno
    FROM SC
    WHERE Cno='001');

image-20211206140330688

2.6求没有选修001课程的学生姓名

SELECT Sname
FROM Student
WHERE NOT EXISTS
	(SELECT *
    FROM SC
    WHERE Sno=Student.Sno AND Cno='001');
SELECT Sname
FROM Student
WHERE Sno NOT IN
	(SELECT Sno
    FROM SC
    WHERE Cno='001');

image-20211206140710873

2.7查询选修了全部课程的学生的姓名

SELECT Sname
FROM Student
WHERE NOT EXISTS
	(SELECT *
	FROM Course
	WHERE NOT EXISTS
	(SELECT *
	FROM SC 
	WHERE Sno=Student.Sno AND Cno=Course.Cno));

image-20211206142551854