object mysql50 {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession.builder().appName("ReadJson").master("local[*]").getOrCreate()
val url="jdbc:mysql://192.168.192.151:3306/mysql50"
val pwd="root"
val driver="com.mysql.jdbc.Driver"
val user="root"
val properties = new Properties()
properties.setProperty("user",user)
properties.setProperty("password",pwd)
properties.setProperty("driver",driver)
import org.apache.spark.sql.functions._
import spark.implicits._
val studentDF: Dataframe = spark.read.jdbc(url, "student", properties)
val scoreDF: Dataframe = spark.read.jdbc(url, "score", properties)
val courseDF: Dataframe = spark.read.jdbc(url, "course", properties)
val teacherDF: Dataframe = spark.read.jdbc(url, "teacher", properties)
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
//scoreDF.as("s1").groupBy("student_id").avg("score").filter($"avg(score)">=60).join(studentDF,"student_id").show()
4、查询平均成绩小于60 分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的):
//5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
//6、查询"李"姓老师的数量:
//teacherDF.filter("teacher_name ='李%'").groupBy("teacher_id").count().show()
//7、查询学过"张三"老师授课的同学的信息:
//scoreDF.where("course_id = 01").join(studentDF,"student_id").show()
//8、查询没学过"张三"老师授课的同学的信息:
//scoreDF.filter("course_id != 01").join(studentDF,"student_id").show()
//9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:
//10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:
//11、查询没有学全所有课程的同学的信息:
//12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:
// studentDF.where("student_id=01").join(scoreDF,"student_id").show()
// studentDF.where("student_id!=01").join(scoreDF,"student_id").show()
//13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:
//14、查询没学过"张三"老师讲授的任一门课程的学生姓名:
//15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩:
//16、检索"01"课程分数小于60，按分数降序排列的学生信息:
// scoreDF.where("score<60 and course_id=01").orderBy($"score".desc).join(studentDF,"student_id").show()
//17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:
//18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最
//高分，最低分，平均分，及格率，中等率，优良率，优秀率:
//scoreDF.groupBy("course_id").agg(max("score"),min("score"),avg("score")).show()
//19、按各科成绩进行排序，并显示排名:
//scoreDF.selectExpr("*","row_number() over(partition by course_id order by score desc) rank").show()
//20、查询学生的总成绩并进行排名
//21、查询不同老师所教不同课程平均分从高到低显示
//scoreDF.groupBy("course_id").agg(avg("score").as("avg_score")).sort($"avg_score".desc).show()
//22查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
//23 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
//24、查询学生平均成绩及其名次
//25查询各科成绩前三名的记录
//`26.查询每门课程被选修的学生数`
//scoreDF.groupBy("course_id").count().show()
//27、查询出只有两门课程的全部学生的学号和姓名
//28、查询男生、女生人数
//studentDF.groupBy("sex").count().show()
//29、查询名字中含有"风"字的学生信息
//studentDF.filter(x=>x.get(1).toString.contains("风")).show()
//30、查询同名同性学生名单，并统计同名人数
//studentDF.groupBy("student_name").count().where("count>1").show()
//31查询1990年出生的学生名单
//32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
// scoreDF.groupBy("course_id").avg("score").sort($"avg(score)".desc).show()
//33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
//34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
//35、查询所有学生的课程及分数情况
//36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
//scoreDF.join(courseDF,"course_id").where("score>70").show()
//37、查询不及格的课程
//scoreDF.join(courseDF,"course_id").where("score<60").show()
//38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
//39、求每门课程的学生人数
//40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
//41查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
//42、查询每门功成绩最好的前两名
//43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
// scoreDF.groupBy("course_id").count().where("count>5").sort($"count".desc).orderBy("course_id")show()
//44、检索至少选修两门课程的学生学号
// scoreDF.groupBy("student_id").count().where("count>=2").show()
//45、查询选修了全部课程的学生信息
// scoreDF.groupBy("student_id").count().where("count=3").show()
//46、查询各学生的年龄
//studentDF.selectExpr("*","cast(date_format(current_date,'yyyy')as Int)- cast(date_format(birth,'yyyy')as Int)age").show()
//47、查询本周过生日的学生
//studentDF.where("weekofyear(current_date)=weekofyear(birth)").show()
//48、查询下周过生日的学生
//studentDF.where("weekofyear(current_date)+1=weekofyear(birth)").show()
//49、查询本月过生日的学生
//studentDF.where("month(current_date)=month(birth)").show()
//50、查询下月过生日的学生
studentDF.where("month(current_date)+1=month(birth)").show()
