常用MySQL语句练习

Question

1. 一张表包含字段：user_id，登录时间
   • 查出每个用户的最近登录时间，并按照user_id升序排序
2. 学生表：学生id、学生姓名、xx
   • 查出id最大的前3条记录
3. 学生表：学生id、学生姓名、xx
   • 查出重名的学生信息
4. 学生表和分数表，通过学生号关联。
   • 找出成绩前10名的学生姓名
   • 分数是各科成绩，按照总分排序找出第一名的学生信息
   • 分数是各科成绩，按照总分排序并找出成绩前10名的学生。
   • 找出单科成绩最高的学生信息

Answer

1. 查出每个用户的最近登录时间，并按照user_id升序排序

CREATE DATABASE test;
use test;
CREATE TABLE user (
    user_id int,
    logindate DATETIME
);

INSERT INTO user VALUES (1, '2024-10-22 10:16:02');
INSERT INTO user VALUES (2, '2024-10-22 10:16:02');
INSERT INTO user VALUES (1, '2024-10-23 10:16:03');
INSERT INTO user VALUES (2, '2024-10-21 10:16:01');
INSERT INTO user VALUES (4, '2024-10-20 10:16:02');

select user_id, max(logindate) from user group by user_id order by user_id

2. 查出id最大的前3条记录

CREATE DATABASE test;
use test;
CREATE TABLE user (
    user_id int,
    logindate DATETIME
);

INSERT INTO user VALUES (1, '2024-10-22 10:16:02');
INSERT INTO user VALUES (2, '2024-10-22 10:16:02');
INSERT INTO user VALUES (1, '2024-10-23 10:16:03');
INSERT INTO user VALUES (2, '2024-10-21 10:16:01');
INSERT INTO user VALUES (4, '2024-10-20 10:16:02');

select * from user order by user_id desc limit 3;
select * from user order by user_id desc limit 0, 3;

3. 查出重名的学生信息

select name from student group by name having count(*) >= 2

4. 学生表和分数表，通过学生号关联

CREATE DATABASE test;
use test;
CREATE TABLE student (
    id int,
    name varchar(255)
);
CREATE TABLE score (
    id int,
    stuid int,
    course varchar(255),
    score varchar(255)
);

INSERT INTO student VALUES (1, '刘一');
INSERT INTO student VALUES (2, '陈二');
INSERT INTO student VALUES (3, '张三');
INSERT INTO student VALUES (4, '李四');
INSERT INTO student VALUES (5, '王五');
INSERT INTO student VALUES (6, '赵六');
INSERT INTO student VALUES (7, '孙七');
INSERT INTO student VALUES (8, '周八');
INSERT INTO student VALUES (9, '吴九');
INSERT INTO student VALUES (10, '郑十');


INSERT INTO score VALUES (1, 1,  '语文', '80');
INSERT INTO score VALUES (2, 1,  '数学', '80');
INSERT INTO score VALUES (3, 1,  '英语', '85');

INSERT INTO score VALUES (4, 2,  '语文', '81');
INSERT INTO score VALUES (5, 2,  '数学', '80');
INSERT INTO score VALUES (6, 2,  '英语', '85');

INSERT INTO score VALUES (7, 3,  '语文', '81');
INSERT INTO score VALUES (8, 3,  '数学', '82');
INSERT INTO score VALUES (9, 3,  '英语', '90');

INSERT INTO score VALUES (10, 4,  '语文', '81');
INSERT INTO score VALUES (11, 4,  '数学', '82');
INSERT INTO score VALUES (12, 4,  '英语', '90');

-- 1. 如果分数表是总分，或者只有单科成绩，且不存在2个人分数相同的情况，找出成绩前10名的学生姓名，解法如下
select name from stu a join score b on a.stuid = b.stuid order by b.score desc limit 10;


-- 2. 求排名第1名的学生信息，解法如下
-- 1). 找出最高的分数是多少
select sum(score) from score group by stuid order by sum(score) desc limit 1;

-- 2). 找出等于上述最高分数的stuid
select stuid from score group by stuid having sum(score) = (select sum(score) from score group by stuid order by sum(score) desc limit 1);

-- 3). 用上述结果联查学生表，得到学生信息
select * from student a join (select stuid from score group by stuid having sum(score) = (select sum(score) from score group by stuid order by sum(score) desc limit 1)) c
on a.id = c.stuid;




-- 3. 求排名前2名的学生信息，解法1如下
-- 1). 查出最高的2个分数，需要去重
select distinct(sum(score)) from score group by stuid order by sum(score) desc limit 2;

-- 2). 算出总分和id
select stuid, sum(score) as total from score group by stuid

-- 3). 查出等于上述分数的id
select * from (select stuid, sum(score) as score from score group by stuid) sc1 
join 
(select distinct(sum(score)) as score from score group by stuid order by sum(score) desc limit 2) sc2 on 
sc1.score = sc2.score

-- 4). 上述表联查student表，得出最终结果
select s.id, s.name from student s 
join 
(select * from (select stuid, sum(score) as score1 from score group by stuid) sc1 
join 
(select distinct(sum(score)) as score2 from score group by stuid order by sum(score) desc limit 2) sc2 on 
sc1.score1 = sc2.score2)  b on s.id = b.stuid




-- 3. 求排名前2名的学生信息，解法2如下（需要高版本的mysql才能够支持in语法和limit子句共同使用）
-- 1). 查出分数
select distinct(sum(score)) from score group by stuid order by sum(score) desc limit 2;

-- 2). 查出总分等于上述分数的stuid
-- 低版本语法会报错
select * from score s group by stuid HAVING SUM(score) in (select distinct(sum(score)) from score group by stuid order by sum(score) desc limit 2)

-- 3). 联查学生表和上述表
select * from student s join 
(select * from score s group by stuid HAVING SUM(score) in (select distinct(sum(score)) from score group by stuid order by sum(score) desc limit 2)) scoretmp
on s.id = scoretmp.stuid



-- 4. 找出单科成绩最高的学生
-- 1). 找出每科的最高成绩
select course cid, max(score) as st from score group by course

-- 2). 找出每个科目成绩最高的学生id
select * from score sc 
join 
(select course cid, max(score) as st from score group by course) maxscore 
on sc.course = maxscore.cid and sc.score = maxscore.st

--3). 联查学生表，找出学生信息
select * from student s 
join 
(select sc.stuid from score sc join (select course cid, max(score) as st from score group by course) maxscore on sc.course = maxscore.cid and sc.score = maxscore.st) scoretmp 
on s.id = scoretmp.stuid

Reference

1. 牛客网：总分排名前三的学生