Spark SQL测试 学生课程信息

package org.hnsw

import org.apache.spark.sql.SparkSession
import java.util.Properties
import org.apache.spark.sql.functions._

object SparkLearn {

  def main(args: Array[String]): Unit = {
    val sparksql = SparkSession.builder().appName("sparksql_jxq").master("local").getOrCreate()
    // 指定数据库连接地址
    val sqlUrl = "jdbc:mysql://192.168.3.66:3306/sqljxq?useSSL=false&serverTimezone=GMT&characterEncoding=utf-8"
    // 指定访问表
    val Course_table = "Course"
    val Student_table = "Student"
    val Teacher_table = "Teacher"
    val Score_table = "Score"
    // 设置配置参数，数据库访问的用户名和密码
    val properties = new Properties()
    properties.setProperty("user","root")
    properties.setProperty("password","root")
    val dfCourse_jxq = sparksql.read.jdbc(sqlUrl,Course_table,properties)
    val dfStudent_jxq = sparksql.read.jdbc(sqlUrl,Student_table,properties)
    val dfTeacher_jxq = sparksql.read.jdbc(sqlUrl,Teacher_table,properties)
    val dfScore_jxq = sparksql.read.jdbc(sqlUrl,Score_table,properties)
    //查看表
    dfCourse_jxq.show()
    dfStudent_jxq.show()
    dfTeacher_jxq.show()
    dfScore_jxq.show()
    //1、查询"李"姓老师的数量
    dfTeacher_jxq.where("t_name like '%李%'").agg(count("t_name")).as("李姓老师的数量").show()

    //2、查询1990年出生的学生名单
    dfStudent_jxq.where("s_birth like '%1990%'").as("1990年出生").show()




    //3、查询男生、女生人数
    dfStudent_jxq.groupBy("s_sex").agg(count("s_sex")).show()


    //4、查询不及格的课程
    val result_jxq = dfCourse_jxq.join(dfScore_jxq, dfCourse_jxq("c_id")===dfScore_jxq("c_id"),"inner")
    result_jxq.select(dfCourse_jxq("c_name")).where("s_score < 60").show()


    //5、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
//    val result_jxq1 = dfCourse_jxq.join(dfScore_jxq, dfCourse_jxq("c_id")===dfScore_jxq("c_id"),"inner")
//    val result_jxq2 = result_jxq1.join(dfStudent_jxq,dfCourse_jxq("s_id")===dfStudent_jxq("s_id"),"inner")
//    result_jxq2.select(dfStudent_jxq("s_id"),dfStudent_jxq("s_name"),dfStudent_jxq("s_birth"),dfStudent_jxq("s_sex")).show()
    dfStudent_jxq.join(dfScore_jxq.filter("c_id = 1"),"s_id").join(dfScore_jxq.filter("c_id = 2"),"s_id").show()


    //6、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
    dfStudent_jxq.join(dfScore_jxq.where("c_id = 2"),Seq("s_id"),"left_outer").as("s2").where("s2.c_id is null").join(dfScore_jxq.where("c_id = 1"),"s_id").show

    //7、查询没有学全所有课程的同学的信息
    dfStudent_jxq.join(dfScore_jxq.groupBy("s_id").count.as("s1"),Seq("s_id"),"left_outer").where("s1.count < 3 or s1.count is null").show
    //8、查询没学过"张三"老师讲授的任一门课程的学生姓名
    dfStudent_jxq.join(dfScore_jxq,"s_id").join(dfCourse_jxq,"c_id").join(dfTeacher_jxq.where("t_name='张三'"),"t_id").as("a").select("s_id").join(dfStudent_jxq.as("b"),Seq("s_id"),"right_outer").where("a.s_id is null").select("s_name").show


    //9、查询不同老师所教不同课程平均分从高到低显示
    dfTeacher_jxq.join(dfCourse_jxq,dfTeacher_jxq("t_id")===dfCourse_jxq("t_id"),"left_outer").join(dfScore_jxq,dfCourse_jxq("c_id")===dfScore_jxq("c_id"), "left_outer").groupBy(dfTeacher_jxq("t_id")).avg("s_score").show()

  }
}