数据库第四章 MySQL的单表查询

一、条件查询

1.查询课程表中，前3门课程的课程号（Cno）、课程名称 （Cname）和学分（Ccredit），显示结果用中文显示属性列名

SELECT Cno AS"课程编号", Cname AS"课程名称", Ccredit AS"学分" FROM Courses LIMIT 3;

2.查询学生表中，所有学生的学号(Sno)、姓名(Sname)和年龄

SELECT Sno, Sname, (YEAR(NOW())-YEAR(Sbirth)) AS "Sage" FROM Students;

3.查询教师表中，年龄小于等于40岁的教师的教师编号、教师姓名、出生年份以及职称

SELECT Tno, Tname, YEAR(Tbirth) 'BornYear', Tprof FROM Teachers

WHERE YEAR(NOW())-YEAR(Tbirth)<=40;

4.查询所有入学年龄在20~21岁之间的学生的学号(Sno)、姓名 (Sname)

SELECT Sno, Sname FROM Students

WHERE (CONVERT(LEFT(Sno,4), UNSIGNED ) -YEAR(Sbirth))>=20 AND (CONVERT(LEFT(Sno,4), UNSIGNED ) - YEAR(Sbirth))<=21;

CONVERT(expr, type) ：将字符串expr 转换为指定数据类型，返回值：指定数据类型

LEFT(expr, n) ：获得字符串expr前端指定长度的子串，返回值：字符类型

5.谓词条件：

①[不]在两值之间：[NOT]  BETWEEN   v1   AND   v2

②[不]等于其中某个值：[NOT]  IN (Value1,Value2,…,Valuen)

③[不]为空值：IS  [NOT]  NULL

④模糊匹配：[NOT]  LIKE'<匹配串>' [ESCAPE'<换码字符>']

注意：<匹配串>可以是一个完整的字符串，也可以含有通配符： ① %(百分号):代表任意长度(长度可以为0)的字符串。例如a%b 表示以a开头，以b结尾的任意长度的字符串。如acb，abcdb， ab等。 ②_(下横线):代表任意单个字符。例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，adb等。

ESCAPE'<换码字符>'的作用： 在模式中，当换码字符(可以任意定义为除'%'和'_'以外的字符， 如'/')置于通配符('%'和'_')之前时，该通配符就解释为普通字符。 注意：换码字符不能使用’\’

例子：WHERE Column A LIKE '%5/%%' ESCAPE '/'等同于包含“5%”的字符串

二、分组查询

1.查询学生表中，每个专业的男、女学生的人数，查询结果显示专业编号、性别和人数，并按照专业编号降序排序

SELECT Mno,Sgender, COUNT(*)AS StuNum

FROM Students

GROUP BY Mno,Sgender

ORDER BY Mno  DESC;

2.查询选课表中，每位学生选修课程的平均成绩，查询结果 显示学生学号和平均成绩，并按平均成绩升序排序

SELECT Sno, FORAMT (AVG(Grade) '平均分'

FROM Reports

GROUP BY Sno  WITH ROLLUP;

FORMAT(number, n) :显示指定小数位数的数值  返回值：实数类型

在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量 注意：ROLLUP和ORDERBY是互相排斥的

3.查询授课表中，教授的课程数至少有3门的教师的教师编号及授课门数，并按照授课门数升序排序

SELECT Tno, COUNT(DISTINCT Cno) TutCnt   ( 必须用DISTINCT先过滤掉所有重复的Cno的数)

FROM Tutors

Group by Tno

HAVING COUNT(DISTINCT Cno)>=3;