一些数据库练习

--------------------------------表结构私发----------------------------------

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT ss.*, 语文.s_score, 数学.s_score 
FROM score 语文, score 数学, student ss 
WHERE ss.s_id = 语文.s_id    AND          -- 找同一个学生
            ss.s_id = 数学.s_id AND 
            语文.c_id = '01' AND
            数学.c_id = '02' AND
            语文.s_score > 数学.s_score ;

SELECT ss.*, 语文.s_score as yuwen, 数学.s_score as math
FROM student ss 
LEFT JOIN score 语文 ON ss.s_id = 语文.s_id    AND 语文.c_id = '01' 
LEFT JOIN score 数学 ON ss.s_id = 数学.s_id AND 数学.c_id = '02' 
WHERE 语文.s_score > 数学.s_score ;

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT ss.*, 语文.s_score as yuwen, 数学.s_score as math
FROM student ss 
LEFT JOIN score 语文 ON ss.s_id = 语文.s_id    AND 语文.c_id = '01' 
LEFT JOIN score 数学 ON ss.s_id = 数学.s_id AND 数学.c_id = '02' 
WHERE 语文.s_score < 数学.s_score ;

-- 3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT ss.s_id, ss.s_name, count(score.c_id), SUM(score.s_score)
FROM student ss 
LEFT JOIN score ON ss.s_id = score.s_id  -- 将学生id一样的课程统计
GROUP BY ss.s_id, ss.s_name ;

-- 4、查询学过"张三"老师授课的同学的信息

SELECT ss.*
FROM student ss
LEFT JOIN score on ss.s_id = score.s_id
LEFT JOIN course on score.c_id = course.c_id
LEFT JOIN teacher on teacher.t_id = course.t_id
where  teacher.t_name = '张三' ;

-- 1.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)

SELECT ss.s_id, ss.s_name, AVG(score.s_score) as avgscore
FROM student ss
LEFT JOIN score ON ss.s_id = score.s_id
GROUP BY ss.s_id, ss.s_name
HAVING avgscore < 60 OR avgscore IS null ;

-- 2.查询"李"姓老师的数量

SELECT COUNT(tt.t_name)
FROM teacher tt 
WHERE tt.t_name LIKE '李%' ;

-- 3.查询没学过"张三"老师授课的同学的信息

SELECT ss.*
FROM student ss
where ss.s_id not in(
    SELECT score.s_id
    FROM score, course, teacher
    where score.c_id = course.c_id AND teacher.t_id = course.t_id
    AND teacher.t_name = '张三' 
) ;

-- 4.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT ss.*
FROM student ss
INNER JOIN score sc1 ON sc1.s_id = ss.s_id AND sc1.c_id='01'
INNER JOIN score sc2 ON sc2.s_id = ss.s_id AND sc2.c_id='02' ;

# 1.按平均成绩从高到低显示所有学生的所有课程成绩以及平均成绩

SELECT a.s_name, 语文.s_score as yuwen, 数学.s_score as math, 英语.s_score as english, AVG(s4.s_score) as avgScore
FROM student a
LEFT JOIN score 语文 ON a.s_id = 语文.s_id AND 语文.c_id = '01' 
LEFT JOIN score 数学 ON a.s_id = 数学.s_id AND 数学.c_id = '02' 
LEFT JOIN score 英语 ON a.s_id = 英语.s_id AND 英语.c_id = '03' 
LEFT JOIN score s4 ON a.s_id = s4.s_id
GROUP BY a.s_name
ORDER BY avgScore DESC ;

# 总成绩2，3名
-- SELECT a.*, 语文.s_score as yuwen, 数学.s_score as math, 英语.s_score as english, SUM(s4.s_score) as sumScore
-- FROM student a
-- LEFT JOIN score 语文 ON a.s_id = 语文.s_id AND 语文.c_id = '01' 
-- LEFT JOIN score 数学 ON a.s_id = 数学.s_id AND 数学.c_id = '02' 
-- LEFT JOIN score 英语 ON a.s_id = 英语.s_id AND 英语.c_id = '03' 
-- LEFT JOIN score s4 ON a.s_id = s4.s_id
-- GROUP BY a.s_name
-- ORDER BY sumScore DESC 
-- LIMIT 1,2    ;

# 2.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

(SELECT s.*, sc.c_id as '课程ID', sc.s_score as "成绩"
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id 
WHERE sc.c_id='01' ORDER BY sc.s_score desc LIMIT 1,2)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score 
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id 
WHERE sc.c_id='02' ORDER BY sc.s_score desc LIMIT 1,2)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score 
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id 
WHERE sc.c_id='03' ORDER BY sc.s_score desc LIMIT 1,2) ;

#方法二：
-- 先把课程里面的id和名称挑出来
SELECT kc.c_id, kc.c_name, "第二名" as "名次",
    (SELECT s_name from student 
        LEFT JOIN score ON score.s_id = student.s_id
        WHERE score.c_id = kc.c_id ORDER BY score.s_score desc LIMIT 2,1
    ) as 姓名, 
    (SELECT s_score from score 
        where score.c_id = kc.c_id 
        ORDER BY score.s_score DESC LIMIT 2,1
    ) as 成绩,
    "第三名" as "名次",
    (SELECT s_name from student 
        LEFT JOIN score ON score.s_id = student.s_id
        WHERE score.c_id = kc.c_id ORDER BY score.s_score desc LIMIT 3,1
    ) as 姓名,
    (SELECT s_score from score 
        where score.c_id = kc.c_id 
        ORDER BY score.s_score DESC LIMIT 3,1
    ) as 成绩
FROM course kc GROUP BY kc.c_id;

# 3.查询学生平均成绩及其名次 

set @rankScore:=0; 
SELECT @rankScore:= @rankScore+1 as '排名',tab.* FROM
(SELECT student.s_id, student.s_name, IFNULL(AVG(score.s_score),0) as avgScore
FROM student 
LEFT JOIN score ON score.s_id = student.s_id
GROUP BY student.s_id 
ORDER BY avgScore desc) tab ;

# 4.查询各科成绩前三名的记录

(SELECT s.*, sc.c_id as '课程ID', sc.s_score as "成绩"
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id 
WHERE sc.c_id='01' ORDER BY sc.s_score desc LIMIT 3)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score 
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id 
WHERE sc.c_id='02' ORDER BY sc.s_score desc LIMIT 3)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score 
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id 
WHERE sc.c_id='03' ORDER BY sc.s_score desc LIMIT 3)

# 5.查询本月过生日的学生

SELECT s.s_name, s.s_birth
FROM student s
WHERE MONTH(s.s_birth) = MONTH(CURDATE()) ;

# 6.查询下周过生日的学生

SELECT s.s_name, s.s_birth
FROM student s
WHERE WEEK(s.s_birth) = WEEK(NOW())+1 ;