2022-08-17第八组---MySQL查询案例练习

目录

一、构建数据库

（1）student表

（2）course表

（3）teacher表

（4）scores表

（5）给四张表插入数据

二、需求

-- 1.查询'01'号学生的姓名和各科成绩 **

-- 2.查询各个学科的平均成绩和最高成绩**

-- 3.查询每个同学的最高成绩和科目名称****（子查询）

-- 4.查询所有姓张的同学的各科成绩**

-- 5.查询每个课程的最高分的学生信息*****（子查询）

 -- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。

-- 7.查询平均成绩及格的同学的信息。（子查询）

 -- 8.将学生按照总分数进行排名。（从高到低）

--- 9.查询数学成绩的最高分、最低分、平均分。

-- 10.将各科目按照平均分排序。

-- 11.查询老师的信息和他所带的科目的平均分

-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分

-- 13.查询每个学生的最好成绩的科目名称（子查询）

-- 14.查询所有学生的课程及分数

-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）

-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩

-- 17.查询有不及格课程的学生信息

-- 18.查询每门课程有成绩的学生人数

-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列

-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩

-- 21. 查询有且仅有一个课程成绩在80分以上的学生信息

-- 22 .查询出只有三门课程的学生的学号和姓名

-- 23.查询有不及格课程的课程信息

-- 24 .查询至少选择4门课程的学生信息

-- 25. 查询没有选全所有课程的同学的信息

-- 26.查询选全所有课程的同学的信息

-- 27.查询各学生都选了多少门课

-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数

-- 29.查询学过"Tom"老师授课的同学的信息

-- 30.查询没学过"Tom"老师授课的学生信息

---

一、构建数据库

 （1）student表

DROP TABLE IF EXISTS student;/*如果存在student表就删除*/
CREATE TABLE `student` (
	`id` INT ( 10 ) PRIMARY KEY,/*主键*/
	`name` VARCHAR ( 10 ),
	`age` INT ( 10 ) NOT NULL,
    `gender`VARCHAR(2)
);

（2）course表

DROP TABLE IF EXISTS course;
CREATE TABLE `course` (
	`id` INT ( 10 ) PRIMARY KEY,/*主键*/
	`name` VARCHAR ( 10 ),
	`t_id` INT(10)
);

（3）teacher表

DROP TABLE IF EXISTS teacher;
CREATE TABLE `teacher` (
	`id` INT ( 10 ) PRIMARY KEY,/*主键*/
	`name` VARCHAR ( 10 )
);

（4）scores表

DROP TABLE IF EXISTS scores;
CREATE TABLE `scores` (
	`s_id` INT ( 10 ),
	`score` INT ( 10 ),
	`c_id`int(10),
	PRIMARY KEY(s_id,c_id)
);

（5）给四张表插入数据

insert into  student (id,name,age,gender)VALUES(1,'张小明',19,'男'),(2,'李小红',19,'男'),(3,'小刚',24,'男'),(4,'小龙',11,'男'),(5,'小丽',18,'男'),(6,'张小军',18,'女'),(7,'小航',16,'男'),(8,'小亮',23,'男'),(9,'小杰',22,'女'),(10,'李小虎',21,'男');

insert into  course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);


insert into  teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose');


insert into  scores (s_id,score,c_id)VALUES(1,80,1);
insert into  scores (s_id,score,c_id)VALUES(1,56,2);
insert into  scores (s_id,score,c_id)VALUES(1,95,3);
insert into  scores (s_id,score,c_id)VALUES(1,30,4);
insert into  scores (s_id,score,c_id)VALUES(1,76,5);

insert into  scores (s_id,score,c_id)VALUES(2,35,1);
insert into  scores (s_id,score,c_id)VALUES(2,86,2);
insert into  scores (s_id,score,c_id)VALUES(2,45,3);
insert into  scores (s_id,score,c_id)VALUES(2,94,4);
insert into  scores (s_id,score,c_id)VALUES(2,79,5);

insert into  scores (s_id,score,c_id)VALUES(3,65,2);
insert into  scores (s_id,score,c_id)VALUES(3,85,3);
insert into  scores (s_id,score,c_id)VALUES(3,37,4);
insert into  scores (s_id,score,c_id)VALUES(3,79,5);

insert into  scores (s_id,score,c_id)VALUES(4,66,1);
insert into  scores (s_id,score,c_id)VALUES(4,39,2);
insert into  scores (s_id,score,c_id)VALUES(4,85,3);

insert into  scores (s_id,score,c_id)VALUES(5,66,2);
insert into  scores (s_id,score,c_id)VALUES(5,89,3);
insert into  scores (s_id,score,c_id)VALUES(5,74,4);


insert into  scores (s_id,score,c_id)VALUES(6,80,1);
insert into  scores (s_id,score,c_id)VALUES(6,56,2);
insert into  scores (s_id,score,c_id)VALUES(6,95,3);
insert into  scores (s_id,score,c_id)VALUES(6,30,4);
insert into  scores (s_id,score,c_id)VALUES(6,76,5);

insert into  scores (s_id,score,c_id)VALUES(7,35,1);
insert into  scores (s_id,score,c_id)VALUES(7,86,2);
insert into  scores (s_id,score,c_id)VALUES(7,45,3);
insert into  scores (s_id,score,c_id)VALUES(7,94,4);
insert into  scores (s_id,score,c_id)VALUES(7,79,5);

insert into  scores (s_id,score,c_id)VALUES(8,65,2);
insert into  scores (s_id,score,c_id)VALUES(8,85,3);
insert into  scores (s_id,score,c_id)VALUES(8,37,4);
insert into  scores (s_id,score,c_id)VALUES(8,79,5);

insert into  scores (s_id,score,c_id)VALUES(9,66,1);
insert into  scores (s_id,score,c_id)VALUES(9,39,2);
insert into  scores (s_id,score,c_id)VALUES(9,85,3);
insert into  scores (s_id,score,c_id)VALUES(9,79,5);

insert into  scores (s_id,score,c_id)VALUES(10,66,2);
insert into  scores (s_id,score,c_id)VALUES(10,89,3);
insert into  scores (s_id,score,c_id)VALUES(10,74,4);
insert into  scores (s_id,score,c_id)VALUES(10,79,5);

二、需求

-- 1.查询'01'号学生的姓名和各科成绩 **

SELECT
	s.id sid,
	s.`name` sname,
	c.`name` cname,
	sc.score 
FROM
	student s
	LEFT JOIN scores sc ON s.id = sc.s_id
	LEFT JOIN course c ON c.id = sc.c_id 
WHERE
	s.id = 1;

-- 2.查询各个学科的平均成绩和最高成绩**

SELECT
	c.id,
	c.`name`,
	AVG( sc.score ),
	max( sc.score ) 
FROM
	course c
	LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
	c.id,
	c.`name`;

-- 3.查询每个同学的最高成绩和科目名称****（子查询）

SELECT
	t.id 学生编号,
	t.`NAME` 学生姓名,
	c.id 课程编号,
	c.`NAME` 课程名称,
	r.score 最高成绩
FROM
	(
	SELECT
		s.id,
		s.`NAME`,(
		SELECT
			MAX( score ) 
		FROM
			scores r 
		WHERE
			r.s_id = s.id 
		) score 
	FROM
		student s 
	) t
	LEFT JOIN scores r ON r.s_id = t.id 
	AND r.score = t.score
	LEFT JOIN course c ON r.c_id = c.id;

-- 4.查询所有姓张的同学的各科成绩**

SELECT
	s.id,
	s.`name`,
	c.`name` cname,
	sc.score 
FROM
SELECT
	s.id,
	s.`name`,
	c.`name` cname,
	sc.score 
FROM
	student s
	LEFT JOIN scores sc ON sc.s_id = s.id
	LEFT JOIN course c ON c.id = sc.c_id 
WHERE
	s.`name