/*
SQLyog Professional v12.09 (64 bit)
MySQL - 5.7.29 : Database - school
*********************************************************************
*//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;CREATEDATABASE/*!32312 IF NOT EXISTS*/`school`/*!40100 DEFAULT CHARACTER SET utf8 */;USE`school`;/*Table structure for table `Course` */DROPTABLEIFEXISTS`Course`;CREATETABLE`Course`(`c_id`varchar(20)NOTNULL,`c_name`varchar(20)NOTNULLDEFAULT'',`t_id`varchar(20)NOTNULL,PRIMARYKEY(`c_id`))ENGINE=InnoDB DEFAULTCHARSET=utf8;/*Data for the table `Course` */
insert into `Course`(`c_id`,`c_name`,`t_id`)values('01','语文','02'),('02','数学','01'),('03','英语','03');/*Table structure for table `Score` */DROPTABLEIFEXISTS`Score`;CREATETABLE`Score`(`s_id`varchar(20)NOTNULL,`c_id`varchar(20)NOTNULL,`s_score`int(3)DEFAULTNULL,PRIMARYKEY(`s_id`,`c_id`))ENGINE=InnoDB DEFAULTCHARSET=utf8;/*Data for the table `Score` */
insert into `Score`(`s_id`,`c_id`,`s_score`)values('01','01',80),('01','02',90),('01','03',99),('02','01',70),('02','02',60),('02','03',80),('03','01',80),('03','02',80),('03','03',80),('04','01',50),('04','02',30),('04','03',20),('05','01',76),('05','02',87),('06','01',31),('06','03',34),('07','02',89),('07','03',98);/*Table structure for table `Student` */DROPTABLEIFEXISTS`Student`;CREATETABLE`Student`(`s_id`varchar(20)NOTNULL,`s_name`varchar(20)NOTNULLDEFAULT'',`s_birth`varchar(20)NOTNULLDEFAULT'',`s_sex`varchar(10)NOTNULLDEFAULT'',PRIMARYKEY(`s_id`))ENGINE=InnoDB DEFAULTCHARSET=utf8;/*Data for the table `Student` */
insert into `Student`(`s_id`,`s_name`,`s_birth`,`s_sex`)values('01','赵雷','1990-01-01','男'),('02','钱电','1990-12-21','男'),('03','孙风','1990-05-20','男'),('04','李云','1990-08-06','男'),('05','周梅','1991-12-01','女'),('06','吴兰','1992-03-01','女'),('07','郑竹','1989-07-01','女'),('08','王菊','1990-01-20','女');/*Table structure for table `Teacher` */DROPTABLEIFEXISTS`Teacher`;CREATETABLE`Teacher`(`t_id`varchar(20)NOTNULL,`t_name`varchar(20)NOTNULLDEFAULT'',PRIMARYKEY(`t_id`))ENGINE=InnoDB DEFAULTCHARSET=utf8;/*Data for the table `Teacher` */
insert into `Teacher`(`t_id`,`t_name`)values('01','张三'),('02','李四'),('03','王五');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
启动mysql
[root@hadoop100 jars]# mysql -uroot -pok
导入建表脚本
mysql> source /opt/schoolmysql50Bak.sql
查看表
mysql> use school;
mysql> show tables;
使用Java连接至MySQL
package nj.zb.kb09
import java.util.Properties
import org.apache.spark.sql._
object ConnectMySQL {
def main(args: Array[String]): Unit ={
val spark: SparkSession = SparkSession.builder().master("local[*]").appName("ConnectMysql").getOrCreate()
val url ="jdbc:mysql://192.168.136.100:3306/school"
val user ="root"
val pwd ="ok"
val driver ="com.mysql.jdbc.Driver"import spark.implicits._
val prop =newProperties()
prop.setProperty("user", user)
prop.setProperty("password", pwd)
prop.setProperty("driver", driver)
val courseTable ="Course"
val scoreTable ="Score"
val studentTable ="Student"
val teacherTable ="Teacher"
val courseTableDF: DataFrame = spark.read.jdbc(url, courseTable, prop)
val scoreTableDF: DataFrame = spark.read.jdbc(url, scoreTable, prop)
val studentTableDF: DataFrame = spark.read.jdbc(url, studentTable, prop)
val teacherTableDF: DataFrame = spark.read.jdbc(url, teacherTable, prop)}}
MySQL练习题
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
val frame1: DataFrame = scoreTableDF.as("s1").join(scoreTableDF.as("s2"),"s_id").filter("s1.c_id=01 and s2.c_id=02 and s1.s_score>s2.s_score").join(studentTableDF,"s_id")
frame1.show()+----+----+-------+----+-------+------+----------+-----+|s_id|c_id|s_score|c_id|s_score|s_name| s_birth|s_sex|+----+----+-------+----+-------+------+----------+-----+|02|01|70|02|60| 钱电|1990-12-21| 男||04|01|50|02|30| 李云|1990-08-06| 男|+----+----+-------+----+-------+------+----------+-----+
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
val frame2: DataFrame = scoreTableDF.as("s1").join(scoreTableDF.as("s2"),"s_id").filter("s1.c_id=01 and s2.c_id=02 and s1.s_score<s2.s_score").join(studentTableDF,"s_id")
frame2.show()+----+----+-------+----+-------+------+----------+-----+|s_id|c_id|s_score|c_id|s_score|s_name| s_birth|s_sex|+----+----+-------+----+-------+------+----------+-----+|01|01|80|02|90| 赵雷|1990-01-01| 男||05|01|76|02|87| 周梅|1991-12-01| 女|+----+----+-------+----+-------+------+----------+-----+
val jige = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt >60)(x(1).toString,1)else(x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","jige")
val zhongdeng = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt >70)(x(1).toString,1)else(x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","zhongdeng")
val youliang = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt >80)(x(1).toString,1)else(x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","youliang")
val youxiu = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt >90)(x(1).toString,1)else(x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","youxiu")
val s1 = scoreTableDF.groupBy("c_id").agg("s_score"->"max","s_score"->"min","s_score"->"avg","s_score"->"count")
val frame18: DataFrame = s1.join(jige,"c_id").join(zhongdeng,"c_id").join(youliang,"c_id").join(youxiu,"c_id").withColumn("jgl",$"jige"/$"count(s_score)").withColumn("zdl",$"zhongdeng"/$"count(s_score)").withColumn("yll",$"youliang"/$"count(s_score)").withColumn("yxl",$"youxiu"/$"count(s_score)").drop("jige","zhongdeng","youliang","youxiu")
frame18.show()+----+------------+------------+-----------------+--------------+------------------+------------------+------------------+------------------+|c_id|max(s_score)|min(s_score)|avg(s_score)|count(s_score)| jgl| zdl| yll| yxl|+----+------------+------------+-----------------+--------------+------------------+------------------+------------------+------------------+|01|80|31|64.5|6|0.6666666666666666|0.5|0.0|0.0||03|99|20|68.5|6|0.6666666666666666|0.6666666666666666|0.3333333333333333|0.3333333333333333||02|90|30|72.66666666666667|6|0.6666666666666666|0.6666666666666666|0.5|0.0|+----+------------+------------+-----------------+--------------+------------------+------------------+------------------+------------------+
19、按各科成绩进行排序,并显示排名
val frame19: DataFrame = scoreTableDF.selectExpr("*","row_number() over(partition by c_id order by s_score desc)")
frame19.show()+----+----+-------+---------------------------------------------------------------------------------------+|s_id|c_id|s_score|row_number()OVER(PARTITIONBY c_id ORDERBY s_score DESCNULLSLAST UnspecifiedFrame)|+----+----+-------+---------------------------------------------------------------------------------------+|01|01|80|1||03|01|80|2||05|01|76|3||02|01|70|4||04|01|50|5||06|01|31|6||01|03|99|1||07|03|98|2||02|03|80|3||03|03|80|4||06|03|34|5||04|03|20|6||01|02|90|1||07|02|89|2||05|02|87|3||03|02|80|4||02|02|60|5||04|02|30|6|+----+----+-------+---------------------------------------------------------------------------------------+
20、查询学生的总成绩并进行排名
val frame20: DataFrame = scoreTableDF.selectExpr("*","sum(s_score) over(partition by s_id) as sum_score").dropDuplicates("s_id").selectExpr("*","row_number() over(order by sum_score desc)")
frame20.show()+----+----+-------+---------+-----------------------------------------------------------------------+|s_id|c_id|s_score|sum_score|row_number()OVER(ORDERBY sum_score DESCNULLSLAST UnspecifiedFrame)|+----+----+-------+---------+-----------------------------------------------------------------------+|01|01|80|269|1||03|01|80|240|2||02|01|70|210|3||07|02|89|187|4||05|01|76|163|5||04|01|50|100|6||06|01|31|65|7|+----+----+-------+---------+-----------------------------------------------------------------------+
21、查询不同老师所教不同课程平均分从高到低显示
val frame21: Dataset[Row]= scoreTableDF.join(courseTableDF,"c_id").join(teacherTableDF,"t_id").groupBy("t_id","c_id").avg("s_score").orderBy($"avg(s_score)".desc)
frame21.show()+----+----+-----------------+|t_id|c_id|avg(s_score)|+----+----+-----------------+|01|02|72.66666666666667||03|03|68.5||02|01|64.5|+----+----+-----------------+
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
val frame22: DataFrame = scoreTableDF.selectExpr("*","row_number() over(partition by c_id order by s_score desc) num").where("num between 2 and 3").join(studentTableDF,"s_id")
frame22.show()+----+----+-------+---+------+----------+-----+|s_id|c_id|s_score|num|s_name| s_birth|s_sex|+----+----+-------+---+------+----------+-----+|07|03|98|2| 郑竹|1989-07-01| 女||07|02|89|2| 郑竹|1989-07-01| 女||05|01|76|3| 周梅|1991-12-01| 女||05|02|87|3| 周梅|1991-12-01| 女||03|01|80|2| 孙风|1990-05-20| 男||02|03|80|3| 钱电|1990-12-21| 男|+----+----+-------+---+------+----------+-----+
val frame24: DataFrame = scoreTableDF.groupBy("s_id").avg("s_score").selectExpr("*",s"row_number() over(order by 'avg(s_score)')")
frame24.show()+----+------------------+--------------------------------------------------------------------------+|s_id|avg(s_score)|row_number()OVER(ORDERBYavg(s_score)ASCNULLSFIRST UnspecifiedFrame)|+----+------------------+--------------------------------------------------------------------------+|07|93.5|1||01|89.66666666666667|2||05|81.5|3||03|80.0|4||02|70.0|5||06|32.5|6||04|33.333333333333336|7|+----+------------------+--------------------------------------------------------------------------+
25、查询各科成绩前三名的记录
val frame25: Dataset[Row]= scoreTableDF.selectExpr("*","row_number() over(partition by c_id order by s_score desc) num").where("num<=3")
frame25.show()+----+----+-------+---+|s_id|c_id|s_score|num|+----+----+-------+---+|01|01|80|1||03|01|80|2||05|01|76|3||01|03|99|1||07|03|98|2||02|03|80|3||01|02|90|1||07|02|89|2||05|02|87|3|+----+----+-------+---+
26、查询每门课程被选修的学生数
val frame26: DataFrame = scoreTableDF.groupBy("c_id").count()
frame26.show()+----+-----+|c_id|count|+----+-----+|01|6||03|6||02|6|+----+-----+
val frame28: DataFrame = studentTableDF.groupBy("s_sex").count()
frame28.show()+-----+-----+|s_sex|count|+-----+-----+| 男|4|| 女|4|+-----+-----+
29、查询名字中含有"风"字的学生信息
val frame29: Dataset[Row]= studentTableDF.where("s_name like '%风%'")
frame29.show()+----+------+----------+-----+|s_id|s_name| s_birth|s_sex|+----+------+----------+-----+|03| 孙风|1990-05-20| 男|+----+------+----------+-----+
30、查询同名同姓学生名单,并统计同名人数
val frame30: Dataset[Row]= studentTableDF.groupBy("s_name").count().where("count>1")
frame30.show()+------+-----+|s_name|count|+------+-----++------+-----+
val frame32: Dataset[Row]= scoreTableDF.groupBy("c_id").avg("s_score").orderBy($"avg(s_score)".desc,$"c_id")
frame32.show()+----+-----------------+|c_id|avg(s_score)|+----+-----------------+|02|72.66666666666667||03|68.5||01|64.5|+----+-----------------+
val frame38: DataFrame = scoreTableDF.where("c_id=01 and s_score>80").join(studentTableDF,"s_id")
frame38.show()+----+----+-------+------+-------+-----+|s_id|c_id|s_score|s_name|s_birth|s_sex|+----+----+-------+------+-------+-----++----+----+-------+------+-------+-----+
39、求每门课程的学生人数
val frame39: DataFrame = scoreTableDF.groupBy("c_id").count()
frame39.show()+----+-----+|c_id|count|+----+-----+|01|6||03|6||02|6|+----+-----+
val frame41: Dataset[Row]= scoreTableDF.as("s1").join(scoreTableDF.as("s2"),"s_id").where("s1.s_score=s2.s_score and s1.c_id!=s2.c_id")
frame41.show()+----+----+-------+----+-------+|s_id|c_id|s_score|c_id|s_score|+----+----+-------+----+-------+|03|01|80|02|80||03|01|80|03|80||03|02|80|01|80||03|02|80|03|80||03|03|80|01|80||03|03|80|02|80|+----+----+-------+----+-------+
42、查询每门课程成绩最好的前三名
val frame42: Dataset[Row]= scoreTableDF.selectExpr("*","row_number() over(partition by c_id order by s_score desc)rank").where("rank<=3")
frame42.show()+----+----+-------+----+|s_id|c_id|s_score|rank|+----+----+-------+----+|01|01|80|1||03|01|80|2||05|01|76|3||01|03|99|1||07|03|98|2||02|03|80|3||01|02|90|1||07|02|89|2||05|02|87|3|+----+----+-------+----
val frame43: Dataset[Row]= scoreTableDF.groupBy("c_id").count().where("count>=5").orderBy($"count".desc).orderBy("c_id")
frame43.show()+----+-----+|c_id|count|+----+-----+|01|6||02|6||03|6|+----+-----+
44、检索至少选修两门课程的学生学号
val frame44: DataFrame = scoreTableDF.groupBy("s_id").count().where("count>2").drop("count")
frame44.show()+----+|s_id|+----+|01||03||02||04|+----+
val frame47: Dataset[Row]= studentTableDF.where("unix_timestamp(cast(concat_ws('-',date_format(current_date(),'yyyy'),date_format(s_birth,'MM'),date_format(s_birth,'dd'))as date),'yyyy-MM-dd') between unix_timestamp(current_date()) and unix_timestamp(date_sub(next_day(current_date(),'MON'),1),'yyyy-MM-dd')")
frame47.show()+----+------+-------+-----+|s_id|s_name|s_birth|s_sex|+----+------+-------+-----++----+------+-------+-----+
48、查询下周过生日的学生: 下周1到+6天
val frame48: Dataset[Row]= studentTableDF.where(" unix_timestamp( cast( concat_ws('-',date_format(current_date(),'yyyy'),date_format(s_birth,'MM'),date_format(s_birth,'dd') ) as date ),'yyyy-MM-dd') between unix_timestamp(date_sub(next_day(current_date(),'MON'),1),'yyyy-MM-dd') and unix_timestamp(date_add(next_day(current_date(),'MON'),6),'yyyy-MM-dd') ")
frame48.show()+----+------+-------+-----+|s_id|s_name|s_birth|s_sex|+----+------+-------+-----++----+------+-------+-----+
49、查询本月过生日的学生
val frame49: Dataset[Row]= studentTableDF.where("month(s_birth)=month(current_date)")
frame49.show()+----+------+-------+-----+|s_id|s_name|s_birth|s_sex|+----+------+-------+-----++----+------+-------+-----+
50、查询12月份过生日的学生
val frame50: Dataset[Row]= studentTableDF.where("month(s_birth)=12")
frame50.show()+----+------+----------+-----+|s_id|s_name| s_birth|s_sex|+----+------+----------+-----+|02| 钱电|1990-12-21| 男||05| 周梅|1991-12-01| 女|+----+------+----------+-----+