二.查询语句

一.分组查询

SELECT MAX(age) FROM student;         # 查询年龄最大的数字为32
SELECT * FROM student WHERE age=32;   # 查询32的学生信息
SELECT * FROM student WHERE age=(SELECT MAX(age) FROM student);   # 利用子查询合并

# like关键字模糊查询：用%代替任意字符，用_代替一个字符

# 查询学生中姓“张”的学生
SELECT * FROM student WHERE sname LIKE '张%'; 
 
# 查询学生中姓“李”的学生
SELECT * FROM student WHERE sname LIKE '李__';

# 查询学生中包含“雨”的学生
SELECT * FROM student WHERE sname LIKE '%雨%';

# 查询一班的学生平均年龄
SELECT AVG(age) FROM student WHERE classid=1;

# 查询某个条件下的行数
SELECT COUNT(studentid) FROM student 

# 计算C09课程5月的考试平均成绩
SELECT COUNT(studentid) FROM grade WHERE courseid='C09' 
AND BETWEEN createtime '2022-05-01' AND '2022-05-31'; 

# 查询C10课程，考试成绩排名前10的学生（LIMIT 开始行数，查询行数）
SELECT * FROM grade WHERE courseid='C10' 
ORDER BY score DESC LIMIT 0,10;

# 包含并列的成绩
# 先找到排名第10位的成绩，然后查询大于等于第10位成绩的学生
SELECT * FROM grade WHERE courseid='C10' and score>= 
(SELECT score FROM grade WHERE courseid='C10' ORDER BY score DESC LIMIT 9,1);

 

#  查询每门学科的平均成绩

SELECT courseid,AVG(score) FROM grade GROUP BY courseid;

 

其他用法补充：

IN / NOT IN：查询条件在一个集合中 存在/不存在

ANY：用于条件判断的子查询：

SELECT * FROM student WHERE degree='本科' AND 
age<ANY(SELECT age FROM student WHERE degree='研究生');

UNION：联合查询，将两条结果拼成一个结果

1.两条结果的列数必须一样

SELECT * FROM student WHERE sname LIKE '张%'
union SELECT * FROM student WHERE sname LIKE '%雨%';