【2020-2021春学期】数据库作业5：单表查询例题练习

单表查询

1.选择表中若干列

【例3.19】查询全体学生姓名和出生年份

select Sname,2021-Sage --查询出生年份需要利用算术表达式（今年年份-学生年龄）
from Student

在这里第二行列名为空，但在标准SQL中会直接显示算术表达式。在【例3.20】会解决这个问题

【例3.20】查询全体学生姓名、出生年份、院系（用小写）

select Sname,'Year of Birth:',2021-Sage,LOWER(Sdept)
from Student;

程序里的’Year of Birth:'可以用来增加表的易读性；

LOWER( ) 返回小写的字符串，UPPER( ) 返回大写的字符串

还有列名为空的问题怎么解决？方法如下

*方法一：

select Sname NAME,'Year of Birth:' BIRTH ,2021-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
from Student;

但这样似乎也有一些局限性，比如列名不能带空格，这时就需要用到方法二

*方法二

select Sname[Name],'Year of Birth:'[Year of Birth:],2021-Sage[2021-Sage],LOWER(Sdept)[LOWER(Sdept)]
from Student;

2.选择表中的若干元组

【例3.21】查询选修了课程的学生学号

select Sno
from SC

如果想去掉结果中的重复行，必须指定DISTINCT，没有则默认为ALL

select DISTINCT Sno
from SC

【例3.22】查询计算机科学系全体学生名单

首先列出常用的查询条件

查询条件	谓词
比较	=，<，>，<=，>=，!=，<>，!>，!<；NOT+上述比较运算符
确定范围	BETWEEN AND; NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重条件（逻辑运算）	AND, OR, NOT

select Sname
from Student
where Sdept='CS';

【例3.23】查询所有年龄在20岁以下的学生姓名和年龄

select Sname,Sage
from Student
where Sage<20;

因为我的student表里面没有小于20岁的学生，可以改为<21再试试

【例3.24】查询考试成绩不及格的学生的学号

select DISTINCT Sno
from SC
where Grade<60;

说明没有任何一门课程为不及格的学生

【例3.25】查询年龄在20~23岁（包括20和23）之间的学生姓名、系别、年龄

select Sname,Sdept,Sage
from Student
where Sage BETWEEN 20 AND 23;

【例3.26】查询年龄不在20~23岁之间的学生姓名、系别、年龄

select Sname,Sdept,Sage
from Student
where Sage NOT BETWEEN 20 AND 23;

说明没有符合标准的学生

【例3.27】查询CS、MA的学生姓名和性别

select Sname,Ssex
from Student
where Sdept IN('CS','MA');

【例3.28】查询不是CS也不是MA的学生姓名和性别

select Sname,Ssex
from Student
where Sdept NOT IN('CS','MA');

【例3.29】查询学号为201911020的学生的详细情况

这里介绍一下LIKE的字符匹配语法：[NOT] LIKE ‘<匹配码>’ [ESCAPE ‘<换码字符>’]
匹配串可以是完整的字符串也可以含有通配符 % 和 _ 。 其中%为任意长度（可为0）字符串，_ 代表单个字符（在标准SQL中似乎不可为空，但T-SQL中可以）。

select *
from Student
where Sno LIKE '201911020'; --等价于 Sno='201911020'

【例3.30】查询所有姓刘学生的姓名、学号和性别。

因为之前student表里面数据比较少，在这里我又补充了一些数据。

INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215129','欧阳询','男','IS',20);

select Sname,Sno,Ssex
from Student
where Sname LIKE '刘%';

【例3.31】查询姓"欧阳"且全名为三个汉字的学生的姓名。

select Sname
from Student
where Sname LIKE '欧阳_';

这里舍友出现了问题，用%可以，但_不可用。最后发现是表的设计上的问题，我们都是char（4）而他是nchar（4），而nchar和char的区别就是nchar是固定长度的。

【例3.32】查询名字中第2个字为"阳"字的学生的姓名和学号。

select Sname,Sno
from Student
where Sname LIKE '_阳%';

【例3.33】查询所有不姓刘的学生姓名、学号和性别。

select Sname,Sno,Ssex
from Student
where Sname NOT　LIKE '_阳%';

【例3.34】查询DB_Design课程的课程号和学分。

select Cno,Ccredit
from Course
where Cname LIKE 'DB\_Design' ESCAPE'\'; --ESCAPE是对‘\’进行转义

在用编辑前200行插入数据时，记得按回车…

【例3.35】查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。

select *
from Course
where Cname LIKE 'DB\_%i__' ESCAPE'\' --是% 不是& c语言写多了...

【例3.36】某些学生选修课程后没有参加考试。查询缺少成绩的学生的学号和相应的课程号。

select Sno,Cno
from SC
where Grade IS NULL

这里我在SC表多添加了一列，原本是空

【例3.37】查所有有成绩的学生学号和课程号。

select Sno,Cno
from SC
where Grade IS NOT NULL

【例3.38】查询计算机系年龄在20岁以下的学生姓名。

select Sname
from Student
where Sdept= 'CS' AND Sage<20;

3.ORDER BY 子句

【例3.39】查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

select Sno,Grade
from SC
where Cno='3'
ORDER BY Grade DESC;

【例3.40】查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

select *
from Student
ORDER BY Sdept,Sage DESC;

4.聚集函数

首先介绍聚集函数的功能和实现：

功能	实现
统计元组个数	COUNT（*）
统计一列中值的个数	COUNT([DISTINCT或ALL] <列名>)
计算一列值的总和	SUM([DISTINCT或ALL] <列名>)
计算一列值的平均值	AVG([DISTINCT或ALL] <列名>)
求一列中的最大值和最小值	MAX([DISTINCT或ALL] <列名>) MIN([DISTINCT或ALL] <列名>)

【例3.41】查询学生总人数。

select COUNT(*)
from Student;

【例3.42】查询选修了课程的学生人数。

select COUNT(DISTINCT Sno)
from SC;

【例3.43】计算1号课程的学生平均成绩。

select AVG(Grade)
from SC
where Cno='1';

【例3.44】查询选修1号课程的学生最高分数。

select MAX(Grade)
from SC
where Cno='1';

这里跟【例3.43】结果一样的原因是选修1号课的只有一人

【例3.45】查询学生201215121选修课程的总学分数。

select SUM(Ccredit)
from Course,SC
where Sno='201215121' AND Course.Cno=SC.Cno --这句要好好理解一下

5.GROUP BY 子句

【例3.46】求各个课程号及相应的选课人数。

select Cno,COUNT(Sno)
from SC
GROUP BY Cno;

【例3.47】查询选修了2门以上课程的学生学号。

select Sno
from SC
GROUP BY Sno
HAVING COUNT(*)>2;

最后这个例题做的也比较懵，这里HAVING的作用是指定筛选条件，一开始感觉和WHERE挺像，但是我后来理解的是后者是在from的表中做筛选，而前者是在GROUP后的每个小单元中做筛选。