package TEST2;

import java.util.Scanner;

public class PE2 {

	public static void main(String[] args) {
		// TODO 自动生成的方法存根
		//让学生输入学号，取出中间的第6至8位表示班级编号，并输出
		System.out.println("请输入学号");
		
		Scanner sc =new Scanner(System.in);
		String name=sc.next();
		String strh = name.substring(5,8);
		System.out.println("班级"+strh);
	}

}
 

 
查询课程编号为01的学生比课程编号为02的学生成绩高的所有学生的编号
 
1. 首先建立数据表
 
建立四张数据表(, 成绩表(score))
 
1. 学生表(students)
 
1.学生id  ----  s_id
 
2. 学生姓名  ----  s_name
 
3. 学生生日 ----  s_birth
 
4. 学生性别 ----  s_sex
 
2. 教师表(teacher)
 
1. 教师id ---- t_id
 
2. 教师姓名  ---- t_name
 
3. 课程表(course)
 
1.课程id ---- c_id
 
2. 课程名 ----  c_name
 
3. 教师id   -----   t_id(与教师表对应, 方便建立数据库连接)
 
4. 成绩表(score)
 
1. 学生id ---- s_id(与学生表对应, 方便建立数据库连接)
 
2. 课程id ---- c_id(与课程表对应, 方便建立数据库连接)
 
3. 学生成绩  -----  s_score
 
============   表的建立代码:
 
1 CREATE TABLEstudents(2 s_id VARCHAR(20) PRIMARY KEY,3 s_name VARCHAR(20) NOT NULL DEFAULT '',4 s_birth VARCHAR(20) NOT NULL DEFAULT '',5 s_sex VARCHAR(10) NOT NULL DEFAULT ''
 
6 );7
 
8 CREATE TABLEcourse(9 c_id VARCHAR(20) PRIMARY KEY,10 c_name VARCHAR(20) NOT NULL DEFAULT '',11 t_id VARCHAR(20) NOT NULL
 
12 );13
 
14 CREATE TABLEteacher(15 t_id VARCHAR(20) PRIMARY KEY,16 t_name VARCHAR(20) NOT NULL DEFAULT ''
 
17 );18
 
19 CREATE TABLEscore(20 s_id VARCHAR(20),21 c_id VARCHAR(20),22 s_score INT(3),23 PRIMARY(s_id, c_id)24 );
 
2. 导入数据
 
导入数据信息:insert into ... values(信息)
 
============数据导入代码
 
1 INSERT INTO students VALUES('01', '赵雷', '1990-01-01', '男');2 INSERT INTO students VALUES('02', '钱电', '1990-12-21', '男');3 INSERT INTO students VALUES('03', '孙风', '1990-05-20', '男');4 INSERT INTO students VALUES('04', '李云', '1990-12-01', '男');5 INSERT INTO students VALUES('05', '周梅', '1991-12-01', '女');6 INSERT INTO students VALUES('06', '吴兰', '1992-03-01', '女');7 INSERT INTO students VALUES('07', '郑竹', '1989-07-01', '女');8 INSERT INTO students VALUES('08', '王菊', '1990-01-20', '女');9
 
10 INSERT INTO course VALUES('01', '语文', '02');11 INSERT INTO course VALUES('02', '数学', '01');12 INSERT INTO course VALUES('03', '英语', '03');13
 
14 INSERT INTO teacher VALUES('01', '张三');15 INSERT INTO teacher VALUES('02', '李四');16 INSERT INTO teacher VALUES('03', '王五');17
 
18 INSERT INTO score VALUES('01', '01', 80);19 INSERT INTO score VALUES('01', '02', 90);20 INSERT INTO score VALUES('01', '03', 99);21 INSERT INTO score VALUES('02', '01', 70);22 INSERT INTO score VALUES('02', '02', 60);23 INSERT INTO score VALUES('02', '03', 80);24 INSERT INTO score VALUES('03', '01', 80);25 INSERT INTO score VALUES('03', '02', 80);26 INSERT INTO score VALUES('03', '03', 80);27 INSERT INTO score VALUES('04', '01', 50);28 INSERT INTO score VALUES('04', '02', 30);29 INSERT INTO score VALUES('04', '03', 20);30 INSERT INTO score VALUES('05', '01', 76);31 INSERT INTO score VALUES('05', '02', 87);32 INSERT INTO score VALUES('06', '03', 31);33 INSERT INTO score VALUES('06', '01', 34);34 INSERT INTO score VALUES('07', '02', 89);35 INSERT INTO score VALUES('07', '03', 98);
 
效果图:
 
course表:
 

 
score表:
 

 
teacher表:
 

 
students表:
 

 
3. 处理问题
 
1. 思路: 我们希望得到如下一张表, 只需要用简单的select * from table where 01成绩>02成绩 就能解决问题
 
---- 我们无法直接将这个直观表构建出来, 所以我们就可以想到---构建子查询, 一步一步向外嵌套
 
s_id
 
c_id_01
 
c_id_02
 
学生编号
 
课程01的成绩
 
课程02的成绩
 
select * from table where 01>02
 
2. 首先, 我们先把课程为01的学生信息, 成绩寻找出来.
 
select s_id, c_id, s_score from score where c_id = '01';
 
输出结果:
 

 
3. 再寻找课程等于02的学生信息
 
select s_id, c_id, s_score from score where c_id = '02';
 
输出结果:
 

 
4. 我们希望把01和02的成绩拼成一张表
 
-------把两张(分别命名(as)为a, b)表inner join起来, 做一个连接查询, 并用学生编号(s_id, c_id)给关联(=)起来(on)
 
select * from(select s_id, c_id, s_score from score where c_id = '01')asainner join(select s_id, c_id, s_score from score where c_id = '02')as b on a.s_id = b.c_id
 
输出结果:
 

 
5. 再带入条件(where), 我们的条件是学生01科目成绩要大于02科目的成绩(a.s_score > b.s_score), 并且需要的是学生的编号(* = a.s_id(或者b.s_id,两者一样随便取一个))
 
select a.s_id from
 
(
 
select s_id, c_id, s_score FROM score where c_id = '01'
 
) as a
 
inner join
 
(
 
select s_id, c_id, s_score FROM score where c_id = '02'
 
) as b on a.s_id = b.c_id
 
where a.s_score > b.s_score
 
输出结果:
 

 
内容来源于网络如有侵权请私信删除

 先写上最终语句，后面进行分析
 
SELECT
st.s_id,
st.s_name,
ROUND(AVG(sc.s_score),2) as score
FROM  student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;
 
 
 
 
 
建表语句（由于测试使用，没有建立索引）
 
-- 学生表  
-- 学生编号,学生姓名, 出生年月,学生性别

CREATE TABLE `Student`(
`s_id` int,
`s_name` VARCHAR(32) NOT NULL DEFAULT '',
`s_birth` VARCHAR(16) NOT NULL DEFAULT '',
`s_sex` CHAR(2) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

-- 教师表 
-- 教师编号,教师姓名
CREATE TABLE `Teacher`(
`t_id` int,
`t_name` VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
); 


-- 课程表 
-- 课程编号, 课程名称, 教师编号
CREATE TABLE `Course`(
`c_id` int,
`c_name` VARCHAR(32) NOT NULL DEFAULT '',
`t_id` int,
PRIMARY KEY(`c_id`)
);

-- 成绩表
-- 学生编号,课程编号,分数
CREATE TABLE `Score`(
`s_id` int,
`c_id` int,
`s_score` INT(4),
PRIMARY KEY(`s_id`,`c_id`)
);


		 
-- 插入学生表测试数据
insert into Student values(1 , '赵雷' , '1990-11-01' , '男');
insert into Student values(2 , '钱电' , '1990-12-21' , '男');
insert into Student values(3 , '孙风' , '1990-05-20' , '男');
insert into Student values(4 , '李云' , '1990-08-06' , '男');
insert into Student values(5 , '周梅' , '1991-12-01' , '女');
insert into Student values(6 , '吴兰' , '1992-03-01' , '女');
insert into Student values(7 , '郑竹' , '1989-07-01' , '女');
insert into Student values(8 , '王菊' , '1990-01-20' , '女');

-- 教师表测试数据
insert into Teacher values(1 , '张三');
insert into Teacher values(2 , '李四');
insert into Teacher values(3 , '王五');
insert into Teacher values(4 , '林一');


-- 课程表测试数据
insert into Course values(1 , '语文' , 2);
insert into Course values(2 , '数学' , 1);
insert into Course values(3 , '英语' , 3);



-- 成绩表测试数据
insert into Score values(1 , 1 , 80);
insert into Score values(1 , 2 , 90);
insert into Score values(1 , 3, 99);
insert into Score values(2 , 1, 70);
insert into Score values(2 , 2, 60);
insert into Score values(2 , 3 , 80);
insert into Score values(3 , 1 , 80);
insert into Score values(3 , 2 , 80);
insert into Score values(3 , 3 , 80);
insert into Score values(4 , 1 , 50);
insert into Score values(4 , 2 , 30);
insert into Score values(4 , 3 , 20);
insert into Score values(5 , 1 , 76);
insert into Score values(5 , 2 , 87);
insert into Score values(6 , 1 , 31);
insert into Score values(6, 3 , 34);
insert into Score values(7 , 2 , 89);
insert into Score values(7 , 3 , 98);
 
下面分析语句
 
①查询所有人的所有成绩
 
-- 查询所有人的所有成绩
select 
st.s_id,st.s_name,sc.s_score 
FROM student st left join score sc on st.s_id=sc.s_id
 
 
 
②查询所有人的平均成绩
  
 
-- 查询所有人的平均成绩
select
st.s_id,st.s_name,AVG(sc.s_score)
FROM student st left join score sc on st.s_id=sc.s_id
group by st.s_id
 
 
备注：此时可以根据成绩排序
 
 
③根据成绩筛选大于等于60分的
 
select 
st.s_id,st.s_name,AVG(sc.s_score) as score
FROM student st left join score sc on st.s_id=sc.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;
 
 
 
 可以使用ROUND函数优化显示平均成绩小数点后两位
 
SELECT
st.s_id,
st.s_name,
ROUND(AVG(sc.s_score),2) as score
FROM  student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;
 

以下内容是本人自己写的，并没有找到此题的官方解答，如有更好的解题过程，请不吝赐教
 
详细问题描述
 
 
解答
 
第一问
 
完整答案
 
SELECT 
student.s_id,student.s_name,COUNT(score.c_id),IF(SUM(score.`s_score`) IS NULL,0,SUM(score.`s_score`))
FROM student LEFT JOIN score ON student.`s_id` = score.`s_id`   GROUP BY student.s_id 
 
效果
 
 
第二问
 
完整答案
 
SELECT * FROM student JOIN score ON student.`s_id`=score.`s_id` JOIN course ON score.`c_id` = course.`c_id` 
WHERE (score.`c_id`,score.`s_score`) IN (
		SELECT c_id,MAX(s_score) AS s_score FROM score WHERE c_id IN (
			SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name= "张三") 
		) GROUP BY c_id
	)
 
效果（有门课有两个最高成绩的学生）
 
 
第二问步骤描述
 
步骤一（查找这个老师所带课程的id和此课程最高成绩）
 
		SELECT c_id,MAX(s_score) AS s_score FROM score WHERE c_id IN (
			SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name= "张三") 
		) GROUP BY c_id
 
效果展示
 
 
步骤二（将所需要的信息表关联）
 
SELECT * FROM student JOIN score ON student.`s_id`=score.`s_id` JOIN course ON score.`c_id` = course.`c_id`
 
效果展示
 
 
步骤三（要注意in的字段）
 
WHERE (score.`c_id`,score.`s_score`) in  (步驟一的結果)
 
 
 
帮助到你的话，点个赞，鼓励一下，欢迎加入我的置顶博客设置的技术交流群，不搞代课推广，让我们一起向诗靠拢。
 
 
附件sql表
 
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.29-log : Database - review
*********************************************************************
*/


/*!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 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`review` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `review`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `c_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c_name` varchar(255) NOT NULL,
  `t_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`t_id`) REFERENCES `teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`c_id`,`c_name`,`t_id`) values (1,'语文',1),(2,'编译原理',2),(3,'Linux',3),(4,'数学',1);

/*Table structure for table `score` */

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `s_id` int(10) unsigned NOT NULL,
  `c_id` int(10) unsigned NOT NULL,
  `s_score` int(10) unsigned NOT NULL,
  KEY `s_id` (`s_id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`),
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `score` */

insert  into `score`(`s_id`,`c_id`,`s_score`) values (1,1,80),(2,1,80),(3,1,60),(7,1,50),(1,2,81),(2,2,71),(3,2,61),(7,2,51),(1,4,82),(2,4,72),(3,4,62),(7,4,52);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `s_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_name` varchar(255) NOT NULL,
  `s_birth` datetime NOT NULL,
  `s_sex` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`s_id`,`s_name`,`s_birth`,`s_sex`) values (1,'马云','2020-07-08 09:47:50','男'),(2,'孙传明','2020-07-08 09:48:08','男'),(3,'臧山松','2020-07-08 09:48:47','男'),(4,'贾小明','2020-07-08 09:49:02','男'),(5,'徐海露','2020-07-08 09:49:16','女'),(6,'尤路英','2020-07-08 09:49:36','女'),(7,'胡吃海塞','2020-07-08 09:49:56','男');

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_name` varchar(255) NOT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

insert  into `teacher`(`t_id`,`t_name`) values (1,'张三'),(2,'李华'),(3,'牛宝丽');

/*!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 */;