初识MySQL之综合复习篇（干货）

# SELECT 生成一个结果集（二维表）
SELECT 1,2;
SELECT 'abc';
SELECT CURRENT_TIME();

# FROM 扫描一个二维表结构（实体表、临时表、查询结果集）
SELECT tb_course.NAME, CODE
FROM tb_course;

# FROM 表连接（新的临时表）
-- 交叉连接（笛卡尔积） A CROSS JOIN B => 列数=A+B 行数=A*B
SELECT * from tb_course,tb_teacher;
SELECT * FROM tb_course CROSS JOIN tb_teacher;
# 内连接
SELECT * from tb_course as c,tb_course_class as cc WHERE c.CODE=cc.COURSE_CODE;

# 查询课程班级的课程名称
# 查课程班级对应的课程编号
SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09';
# 查课程名称
SELECT * FROM tb_course WHERE CODE = (SELECT COURSE_CODE FROM tb_course_class WHERE CODE='3SL1113A.09');

SELECT a.*,b.NAME FROM 
        (SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
     , 
        tb_course b
WHERE a.COURSE_CODE=b.CODE;

SELECT a.*,b.NAME FROM 
        (SELECT Teacher_id,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
     INNER JOIN
        tb_teacher b
   ON a.teacher_id=b.ID;  -- join 条件
     
     
SELECT cc.CODE,cc.COURSE_CODE,t.NAME FROM 
    tb_teacher t INNER JOIN tb_course_class cc ON t.ID=cc.teacher_id
WHERE cc.CODE='3SL1113A.09';

SELECT cc.CODE,c.NAME,t.NAME from 
    tb_course c, tb_teacher t, tb_course_Class cc
WHERE c.CODE=cc.COURSE_CODE and t.ID=cc.Teacher_id;

SELECT cc.CODE 班级编号,c.NAME 课程名称,t.NAME 授课老师 from 
   tb_course_class cc 
     JOIN tb_course c ON cc.COURSE_CODE=c.CODE 
     JOIN tb_teacher t ON cc.teacher_id=t.ID
where cc.CODE='3SL1113A.09';
     
# 外连接 （左连接，右连接）



# WHERE 条件判断 => 是否SELECT
SELECT 'a' FROM tb_course WHERE 1=1;
# 查询2学分的课程信息
SELECT * FROM tb_course WHERE CREDIT=2;
# 查询不及格的学生成绩
SELECT * FROM tb_electives WHERE SCORE < 60;
# LIKE (不是喜欢，是像）  % 通配符，匹配任意0个或多个字符
SELECT * FROM tb_course WHERE NAME like '%程序设计';
# 查询和‘我’同姓的学生信息
SELECT * FROM tb_student WHERE NAME like '谭%飞';
# IN  ==> OR OR OR 
SELECT * FROM tb_student WHERE NAME='谭飞' OR NAME='谭华' OR NAME='谭贝';
SELECT * FROM tb_student WHERE NAME IN ('谭飞','谭华','谭贝');

# 子查询
# 查询3SL1037A.01班分数最高的学生的ID
# 1.先要知道最高分
SELECT MAX(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.01';
# 2.再知道谁是最高分
SELECT * FROM tb_electives WHERE SCORE=100 AND COURSE_CLASS_CODE='3SL1037A.01';
SELECT * FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.02' ORDER BY SCORE ASC LIMIT 1;

UPDATE tb_electives SET SCORE=100 WHERE COURSE_CLASS_CODE='3SL1037A.01' and SCORE=99;



SELECT * FROM tb_electives 
WHERE 
        SCORE IN (SELECT MIN(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE like '3SL1037A.0%') 
        AND 
        COURSE_CLASS_CODE like '3SL1037A.0%';


# 查询最低分
SELECT MIN(SCORE) FROM tb_electives ;
# 分班查询最低分
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;

# 分班查询最低分的学生
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;

select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE 
from 
    tb_electives t 
        INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore ;

        
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID;

select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE);
    
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名,cc.TEACHER_ID
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
     ON t.COURSE_CLASS_CODE=cc.CODE;


select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名,te.NAME 教师姓名,te.TITLE 教师职称
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
     ON t.COURSE_CLASS_CODE=cc.CODE
  LEFT JOIN tb_teacher te 
     ON cc.TEACHER_ID=te.ID;
     
select 
  mc.COLLEGE 学院,
  concat(mc.MAJOR,mc.GRADE,'-',mc.CLASS) as 专业班级,
  t.STUDENT_ID 学生学号,
    s.NAME 学生姓名,
    t.COURSE_CLASS_CODE 课程班编号,
    c.NAME 课程名称,
    t.SCORE 分数,
    te.NAME 教师姓名,
    te.TITLE 教师职称
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
     ON t.COURSE_CLASS_CODE=cc.CODE
  LEFT JOIN tb_teacher te 
     ON cc.TEACHER_ID=te.ID
  LEFT JOIN tb_major_class mc 
     ON mc.ID=s.MAJOR_CLASS;
     
 
# 查询‘我’所在专业班级的信息
# 专业班在哪儿找： tb_major_class
# 已知条件：我=》 数理学院，大数据专业，2019级， 3班
SELECT * 
FROM tb_major_class 
WHERE COLLEGE='数理与大数据学院' 
                AND 
            MAJOR='大数据' 
                AND 
            GRADE=2019 
                AND 
            CLASS=3;

# 我的专业班级编号为43
# 统计我这个班有多少人
# 统计数量COUNT()
# 人-学生人数，学生在哪里找： tb_student
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
WHERE MAJOR_CLASS=43;

# 分组统计每个班的学生人数
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
GROUP BY MAJOR_CLASS;

# 分组统计每个班的学生人数, 按学生人数从多到少排序
# major class 专业班人数
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
GROUP BY MAJOR_CLASS
ORDER BY 学生人数 DESC;

# course class 课程班人数
# 课程班学生在哪里找： tb_electives
SELECT COUNT(*) as 学生人数, COURSE_CLASS_CODE as 课程班编号
FROM tb_electives
GROUP BY COURSE_CLASS_CODE;