MySQL查询操作示例

---

1.查询宋丹丹老师的学生中成绩最高的学生信息

代码如下：

SELECT
	b.*,
	a.cla_teacher 
FROM
	stu_info b
	LEFT JOIN cla_info a ON a.id = b.cla_id 
WHERE
	a.cla_teacher = "宋丹丹" 
ORDER BY
	b.grade DESC 
	LIMIT 1;

查询结果：

2.查询成绩最高的学生所在的班级信息

代码如下：

SELECT
	a.* 
FROM
	cla_info a
	LEFT JOIN stu_info b ON a.id = b.cla_id 
WHERE
	b.grade =(
	SELECT
		max( grade ) 
	FROM
		stu_info 
	);

查询结果：

3.查询人数最多的班级中所有男生的信息

代码如下：

//法1
SELECT
	a.id,a.stu_name,a.sex,a.cla_id,a.grade
FROM
	stu_info a
	RIGHT JOIN ( SELECT cla_id, count(*) aa FROM stu_info GROUP BY cla_id ) b ON a.cla_id = b.cla_id 
WHERE
	a.sex = 1 and b.aa =(
SELECT
	max(b.aa)
FROM
	 ( SELECT cla_id, count(*) aa FROM stu_info GROUP BY cla_id) b)

//法2
SELECT * FROM 
  stu_info,( SELECT cla_id, count(*) aa FROM stu_info GROUP BY cla_id ORDER BY aa DESC LIMIT 1 ) aa 
WHERE
	stu_info.cla_id = aa.cla_id 
	AND stu_info.sex = 1;

查询结果：