MYSQL如何查詢成績(jī)?yōu)榍?名的學(xué)生

更新時(shí)間：2023年11月28日 09:20:21 作者：晚風(fēng)吹兒

這篇文章主要介紹了MYSQL如何查詢成績(jī)?yōu)榍?名的學(xué)生問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

需求：查詢成績(jī)?yōu)榍?名的學(xué)生

拿到需求后，先對(duì)需求進(jìn)行分析，找出篩選的條件

Mysql查詢

1、最基本的查詢

select * from table where name="panpan"

2、查前5名

在mysql中篩選數(shù)據(jù)的前幾名，可以用關(guān)鍵字：limit

select * from table where name="panpan" limit 1

說(shuō)明：查詢表中名為panpan的第一條記錄

3、以什么條件進(jìn)行篩選，需求是以成績(jī)進(jìn)行篩選，則可以用order by 排序篩選

select * from table order by grade

查詢應(yīng)用

合并后查詢：

select * from score order by grade limit 5

說(shuō)明：查詢到score表里的grade ,以grade篩選，提取出前5條記錄

查詢成績(jī)最高和最低的人

簡(jiǎn)單描述

成績(jī)表中有學(xué)生姓名、學(xué)生科目以及學(xué)生分?jǐn)?shù)。查詢分?jǐn)?shù)最高和分?jǐn)?shù)最低的學(xué)生的以及學(xué)生的分?jǐn)?shù)。注意的點(diǎn)如下：

1、group by，select 選擇的列，必須是group by 中的列，或者是函數(shù)形式。

2、利用MAX（）和MIN（）sql函數(shù)。

3、建立的表結(jié)構(gòu)如下。

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `stu_name` varchar(255) NOT NULL,
  `stu_subject` varchar(255) NOT NULL,
  `score` int(255) DEFAULT NULL,
  PRIMARY KEY (`stu_subject`,`stu_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('lilei', 'shuxue', '99');
INSERT INTO `grade` VALUES ('xiaoyong', 'shuxue', '66');
INSERT INTO `grade` VALUES ('lilei', 'yingyu', '80');
INSERT INTO `grade` VALUES ('xiaoyong', 'yingyu', '55');
INSERT INTO `grade` VALUES ('lilei', 'yuwen', '78');
INSERT INTO `grade` VALUES ('xiaoyong', 'yuwen', '88');

思路

1) 按照科目分組查詢成績(jī)最高的人

SELECT grade.stu_subject, MAX(grade.score) as score from grade GROUP BY stu_subject;

2) 顯示出成績(jī)最高的人的姓名和科目名稱

SELECT grade.* from grade,
(SELECT grade.stu_subject, MAX(grade.score) as score from grade GROUP BY stu_subject )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject;

3) 顯示出列標(biāo)題.

SELECT “成績(jī)最高”,grade.* from grade,
(SELECT grade.stu_subject, MAX(grade.score) as score from grade GROUP BY stu_subject )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject;

成績(jī)最低的也是如此就是利用MIN()函數(shù), 如果需要同時(shí)查出, 可以用union 如下sql

SELECT "成績(jī)最高",grade.* from grade, 
(SELECT  grade.stu_subject, MAX(grade.score) as score  from grade GROUP BY  stu_subject  )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject
UNION
SELECT "成績(jī)最低",grade.* from grade, 
(SELECT  grade.stu_subject, MIN(grade.score) as score  from grade GROUP BY  stu_subject  )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject;

查詢結(jié)果如下圖所示：