1. 表

sublime 格式化 ctrl+k ctrl+f

-- 创建学生表
DROP TABLE IF EXISTS student_info;
create table if not exists student_info(
    stu_id string COMMENT '学生id',
    stu_name string COMMENT '学生姓名',
    birthday string COMMENT '出生日期',
    sex string COMMENT '性别'
) 
row format delimited fields terminated by ',' 
stored as textfile;

-- 创建课程表
DROP TABLE IF EXISTS course_info;
create table if not exists course_info(
    course_id string COMMENT '课程id',
    course_name string COMMENT '课程名',
    tea_id string COMMENT '任课老师id'
) 
row format delimited fields terminated by ',' 
stored as textfile;

-- 创建老师表
DROP TABLE IF EXISTS teacher_info;
create table if not exists teacher_info(
    tea_id string COMMENT '老师id',
    tea_name string COMMENT '老师姓名'
) 
row format delimited fields terminated by ',' 
stored as textfile;

-- 创建分数表
DROP TABLE IF EXISTS score_info;
create table if not exists score_info(
    stu_id string COMMENT '学生id',
    course_id string COMMENT '课程id',
    score int COMMENT '成绩'
) 
row format delimited fields terminated by ',' 
stored as textfile;

1.1 简单汇总查询

--查询数学成绩不及格的学生信息和其对应的数学学科成绩，按照学号升序排序
SELECT s.stu_id,
       s.stu_name,
       t1.score
FROM student_info s
JOIN
  ( SELECT *
   FROM score_info
   WHERE course_id=
       (SELECT course_id
        FROM course_info
        WHERE course_name='数学')
     AND score < 60 ) t1 ON s.stu_id = t1.stu_id
ORDER BY s.stu_id;

1.2分组查询 对分组结果条件查询

--查询平均成绩大于60分的学生的学号和平均成绩
select
    stu_id,
    avg(score) score_avg
from score_info
group by stu_id
having score_avg > 60;

1.3 按指定条件分组

--查询一共参加三门课程且其中一门为语文课程的学生的id和姓名
select
  si.stu_id,
  si.stu_name
from student_info si
         join
     (
         select stu_id,
                count(*) cc
         from score_info
         where stu_id in (select stu_id
                          from score_info
                          where course_id = (select course_id from course_info where course_name = '语文'))
         group by stu_id
         having count(*) = 3
     ) t1
on
  si.stu_id=t1.stu_id

--没有学全所有课的学生的学号、姓名
SELECT sti.stu_id,
       sti.stu_name,
       count(sci.course_id)
FROM student_info sti
LEFT JOIN score_info sci ON sti.stu_id = sci.stu_id
GROUP BY sti.stu_id,sti.stu_name
HAVING count(sci.course_id) < (SELECT count(*) FROM course_info)