MySQL编程-单表查询与部分函数的使用

崇云

已于 2023-11-30 09:51:38 修改

阅读量299

点赞数

文章标签： mysql 数据库

于 2023-11-27 16:05:23 首次发布

本文链接：https://blog.csdn.net/m0_54079285/article/details/134646436

版权

实验目的

掌握使用SELECT语句进行基本查询的方法。
掌握使用SELECT语句进行条件查询的方法。
掌握SELECT语句的GROUP BY、ORDER BY子句的作用和使用方法。

实验内容

（本次实验基于实验1数据进行）

（1）查询每个学生的学号、姓名、出生日期信息

SELECT sno,sname,sbirth 
FROM student;

（2）查询所有课程的全部信息

SELECT *
FROM course;

（3）查询所有学生的姓名，年龄

不需要额外插入年龄属性及对应记录。年龄可由当前年份减去出生年份求得

SELECT sname,(YEAR(CURRENT_DATE) - YEAR(sbirth))
FROM student;

（4）查询学号为 0002的学生的姓名和家庭住址

SELECT sname,saddress
FROM student
WHERE sno = '0002';

（5）查询所有出生日期在95年以后的女同学的姓名和出生日期

日期可以进行比较，早者小，晚者大

SELECT sname,sbirth
FROM student
WHERE ssex = '女' AND sbirth >= '1995-01-01';

（6）查询分数在70-80范围内的学生的学号、课程编号和成绩

SELECT sno,cno,grade
FROM sc
WHERE grade >=70 AND grade <= 80;

（7）查询课程编号为’0002’的学生的平均成绩，最高成绩，最低成绩

SELECT AVG(grade), MAX(grade), MIN(grade)
FROM sc
WHERE sno = '0002';

（8）查询选修课程编号为’0003’，且该课程有成绩的人数

IS NOT NULL

SELECT COUNT(sno)
FROM sc
WHERE cno  = '0003' AND grade IS NOT NULL;

（9）查询学生姓名和出生日期，查询结果按出生日期从大到小排序

desc即descent，表示降序；asc即ascend，表示升序

SELECT sname,sbirth
FROM student
ORDER BY sbirth DESC;

（10）查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序

一次查询可以针对多个列分别排序，在ORDER BY后按顺序，以逗号分隔写下排序条件即可

SELECT sno, sname, ssex, sbirth, saddress
FROM student
ORDER BY ssex ASC, sno DESC;

（11）查询所有姓名“张”的学生的学号和姓名

字符匹配：%表示任意字符串；_表示任意单个字符。一般与LIKE连用

SELECT sno, sname
FROM student
WHERE sname LIKE '张%';

（12）查询姓名中含有“小”字的学生的全部信息

SELECT *
FROM student
WHERE sname LIKE '%小%';

（13）查询姓名中最后一个字是“阳”字的学生的姓名，性别

SELECT sname, ssex
FROM student
WHERE sname LIKE '%阳';

（14）查询各个学生的平均成绩，显示学号，平均成绩

*GROUP BY，分组。
’HAVING COUNT(grade) >=0‘ 似乎意义不明……;
*

SELECT sno, AVG(grade)
FROM sc
GROUP BY sno
HAVING COUNT(grade) >=0 ;

（15）查询前5个课程成绩记录的学号，课程，成绩，按成绩降序排列

‘JOIN course ON sc.cno = course.cno’的作用是连接课程表与学生成绩表，并保留课程号相等的行。这题其实是联表查询了

SELECT sno, course.cname, grade
FROM sc
JOIN course 
ON `sc`.cno = `course`.cno
ORDER BY grade DESC
LIMIT 5;

（16）查询选课成绩在5至10名的选课记录

LIMIT 数量 OFFSET 偏移量。如’LIMIT 6 OFFSET 4‘即跳过前4条记录，从第5条开始，一直列到第10条

SELECT *
FROM sc
ORDER BY grade DESC
LIMIT 6 OFFSET 4;

（17）查询有选课的学生的学号

一个学生可选多门课程，在选课表中存在重复学号，需要用DISTINCT限制学号

SELECT DISTINCT sno
FROM sc;

（18）查询出生年份是1996，1998和2000年的学生的基本信息

SELECT *
FROM student
WHERE YEAR(sbirth) = '1996' 
OR YEAR(sbirth) = '1998'
OR YEAR(sbirth) = '2000';

（19）查询每门课程的课程编号、选修人数

AS ‘别名’

SELECT cno, count(DISTINCT sno) AS `student_num ` 
FROM sc
GROUP BY cno;

（20）查询选课人数大于等于3人的课程的编号、人数

‘HAVING 条件’常与GROUP BY连用，用于过滤分组后的记录

SELECT cno,COUNT(sno) AS `student_num `
FROM sc
GROUP BY cno 
HAVING COUNT(sno) >= 3 ;

注意

1.在（14）题中：
语句：
SELECT sno, AVG(grade)
FROM sc;

出错信息：
1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘studb.sc.sno’; this is incompatible with sql_mode=only_full_group_by

解决：

该聚合查询中缺少GROUP BY子句，不符合“sql_mode=only_full_group_by”，需要用GROUP BY语句分组后才能执行AVG(grade)语句。

2.在（3）中
查询所有学生年龄时不必再额外插入年龄属性，只需调用YEAR()函数，将当前年份减去出生年份即可，额外插入年龄属性徒增复杂程度。

3.在（15）中
语句：
SELECT sno, course.cname, grade
FROM sc
ORDER BY grade DESC
LIMIT 5;

报错：
1054 - Unknown column ‘course.cname’ in ‘field list’

解决：
sc表需连接course表，才能查询到课程名，所以在FROM语句后添加
JOIN course
ON sc.cno = course.cno
此外，连接后产生的表有大量重复成绩（同一名学生，同一门课程的成绩多次出现），若不添加一定的约束条件，最终查询到的数据会是某个最高分的重复。添加约束条件sc.cno = course.cno，使得连接得到的表没有重复的记录

总结

初步掌握单表查询的基本语法，学会使用ORDER BY、GROUP BY子句，初步认识了如MAX(),MIN(),AVG(),COUNT()这类聚集函数。
认识HAVING语句与WHERE语句的区别，在使用GROUP BY语句将数据分组后，此时若要加上限定条件，则应该使用HAVING语句而不是WHERE语句，HAVING语句在数据分组后才进行过滤，而WHERE语句在分组前进行过滤。