数据库原理SQL查询（习题+知识点）

一、查询学生表所有学生记录

1.题目内容+代码编写

select *
from stu;

2.知识点提醒

1）选择表中的所有属性列有两种方法

• 在select关键字后列出所有列名
• 若列的显示顺序与其在表中的顺序相同，则也可用 * 表示所有列

二、查询学生表中部分信息

1.题目内容+代码编写

select sno,sname,case when sex = '1' then '男' else '女' end as sex
from stu;

2.知识点提醒

1）性别表示转换（case语句）

• case语句用于根据条件返回不同的值
• eg.when sex = '1' then '男'：如果sex字段的值为'1'，则返回'男'
• AS关键字：使用AS关键字为列名提供别名，使结果更易于理解

三、查询学生表中的女生信息

1.题目内容+代码编写

select sno as 学号,sname as 姓名
from stu
where sex = '0';

2.知识点提醒

1）where语句

• 常用于查询满足指定条件的元组
• 用于条件限定
• where子句不能用聚集函数作为条件表达式

四、查询学生表中大于19岁的女生

1.题目内容+代码编写

select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,2020-year(birdate) as 年龄,memo as 备注
from stu
where 2020-year(birdate) > 19 and sex = '0';

2.知识点提醒

1）年龄求法

• 若遇到输出样例需要的是年龄而不是出生年份，需要编写代码进行计算，通常为(给定的当前年份 - year(出生年份)）

2）多个条件并存

• 遇到多个判别条件时，可以使用and进行连接

五、查询年龄18-20之间的学生信息

1.题目内容+代码编写

select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,2020-year(birdate) as 年龄,memo as 备注
from stu
where 2020-year(birdate) between 18 and 20;

2.知识点提醒

1）判别范围圈定

• 若判别条件为一段范围，可以使用between...and...进行范围的规定

六、查询姓‘李’的学生记录

1.题目内容+代码编写

select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,birdate as 出生日期,memo as 备注
from stu
where sname LIKE '李%';

2.知识点提醒

1）字符串匹配

• 谓词LIKE可以用来进行字符串的匹配
• %代表任意长度的字符串，eg.a%b表示以a开头，以b结尾的任意长度的字符串
• _（下划线）代表任意单个字符，eg.a%b表示以a开头，以b结尾的长度为3的任意字符串

七、查询部分专业的学生

1.题目内容+代码编写

select sno as 学号,sname as 姓名,sex as 性别,mname as 专业
from stu,major
where stu.mno = major.mno and mname in('计算机工程' , '软件工程')
order by sno; 

2.知识点提醒

1）表的连接 

• 将两个表连接时，可以使用 = 进行等值连接

2）确定集合

• 谓词IN可以用来查找属性值属于指定集合的元组

3）排序

• 使用order by进行排序，order by ... desc为降序，order by ... asc为升序（默认值为升序）

八、查询所有学生的平均成绩

1.题目内容+代码编写

SELECT stu.sno as 学号,COALESCE(AVG(sc.grade), 0) as 平均成绩
FROM stu
LEFT JOIN sc ON stu.sno = sc.sno
GROUP BY stu.sno;

2.知识点提醒

1）成绩显示不为NULL

• COALESCE() 函数确保了即使某个学生没有成绩记录，其平均成绩也不会显示为NULL，而是显示为0

九、查询各专业学生的平均成绩

1.题目内容+代码编写

SELECT mname as 专业,ifnull(AVG(grade),0) as 平均成绩
FROM major left outer join
    (SELECT mno,grade 
    FROM stu,sc 
    WHERE stu.sno=sc.sno)as a on major.mno=a.mno
GROUP BY major.mno
ORDER BY major.mno;

2.知识点提醒

1）连接操作

• LEFT OUTER JOIN将 major 表和子查询结果a进行左外连接。左外连接保证了即使a表中没有对应的记录，major 表中的记录仍然会出现在结果中。
• 子查询a从 stu 和 sc 表中选择专业编号 mno 和成绩 grade ，其中stu.sno = sc.sno

十、查询平均成绩高于75分的学生

1.题目内容+代码编写

select sno as 学号,AVG(grade) as 平均成绩
from sc
group by sno
having AVG(grade) > 75;

2.知识点提醒

1）求平均成绩

• 求平均成绩时，可以使用聚集函数AVG()，且聚集函数不能和where一起使用

2）相同项相加

• group by子句将查询结果按某一列或多列的值分组，值相等的为一组