mysql sql50题详解

电玩女神 2022-12-08 15:31 19阅读 0赞

# #

## 1、表结构 ##

–1.学生表   
Student(s\_id,s\_name,s\_birth,s\_sex) –学生编号,学生姓名, 出生年月,学生性别   
–2.课程表   
Course(c\_id,c\_name,t\_id) – –课程编号, 课程名称, 教师编号   
–3.教师表   
Teacher(t\_id,t\_name) –教师编号,教师姓名   
–4.成绩表   
Score(s\_id,c\_id,s\_score) –学生编号,课程编号,分数

## 2 、创建数据库 ##

--建表
    --学生表
    CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
    );
    --课程表
    CREATE TABLE `Course`(
    `c_id` VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
    );
    --教师表
    CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
    );
    --成绩表
    CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id` VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
    );
    --插入学生表测试数据
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '1990-12-21' , '男');
    insert into Student values('03' , '孙风' , '1990-05-20' , '男');
    insert into Student values('04' , '李云' , '1990-08-06' , '男');
    insert into Student values('05' , '周梅' , '1991-12-01' , '女');
    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
    insert into Student values('08' , '王菊' , '1990-01-20' , '女');
    --课程表测试数据
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');
    
    --教师表测试数据
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');
    
    --成绩表测试数据
    insert into Score values('01' , '01' , 80);
    insert into Score values('01' , '02' , 90);
    insert into Score values('01' , '03' , 99);
    insert into Score values('02' , '01' , 70);
    insert into Score values('02' , '02' , 60);
    insert into Score values('02' , '03' , 80);
    insert into Score values('03' , '01' , 80);
    insert into Score values('03' , '02' , 80);
    insert into Score values('03' , '03' , 80);
    insert into Score values('04' , '01' , 50);
    insert into Score values('04' , '02' , 30);
    insert into Score values('04' , '03' , 20);
    insert into Score values('05' , '01' , 76);
    insert into Score values('05' , '02' , 87);
    insert into Score values('06' , '01' , 31);
    insert into Score values('06' , '03' , 34);
    insert into Score values('07' , '02' , 89);
    insert into Score values('07' , '03' , 98);

课程表：

![20200920142003378.png][]

学生表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70][]

成绩表：

![20200920142040719.png][]

教师表：

![20200920142100591.png][]

## 3、答题 ##

**1）查询"01"课程比"02"课程成绩高的学生的信息及课程分数**

SELECT
    	stu.*, s.s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN score s1 ON stu.s_id = s1.s_id
    WHERE
    	s.s_score > s1.s_score
    AND s.c_id = '01'
    AND s1.c_id = '02'

![20200920142826733.png][]

**2）查询"01"课程比"02"课程成绩低的学生的信息及课程分数**

SELECT
    	stu.s_id,
    	stu.s_name,
    	s.s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    AND s.c_id = '01'
    LEFT JOIN score s1 ON stu.s_id = s1.s_id
    AND s1.c_id = '02'
    WHERE
    	s.s_score < s1.s_score

![2020092014291981.png][]

**3）查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩**

SELECT
    	AVG(s.s_score),stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    GROUP BY
    	stu.s_id
    HAVING
    	AVG(s.s_score) > 60

![20200920143012631.png][]

**4）查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩**

SELECT
    	stu.*, (
    		CASE 
    		WHEN AVG(s.s_score) IS NULL THEN
    			0
    		ELSE
    			AVG(s.s_score)
    		END
    	) AS '平均成绩'
    FROM
    	student stu
    LEFT JOIN score s ON s.s_id = stu.s_id
    GROUP BY
    	stu.s_id
    HAVING
    	AVG(s.s_score) > 60
    OR AVG(s.s_score) IS NULL

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 1][]

**5）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩**

SELECT
    	stu.s_id '学生编号',
      stu.s_name '学生姓名',
      count(c.c_id) '选课总数',
      (case when SUM(s.s_score) is null then 0 else SUM(s.s_score) end) '总成绩'
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    GROUP BY
    	stu.s_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 2][]

**6）查询"李"姓老师的数量 **

select count(0) from student stu where s_name like '李%';

![2020092014323236.png][]

**7）查询学过"张三"老师授课的同学的信息 **

SELECT
    	stu.*
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    LEFT JOIN teacher t ON c.t_id = t.t_id
    WHERE
    	t.t_name = '张三'
    GROUP BY
    	stu.s_id;

![20200920143317145.png][]

**8) 查询没学过"张三"老师授课的同学的信息 **

SELECT
    	*
    FROM
    	student
    WHERE
    	s_id NOT IN (
    		SELECT
    			stu.s_id
    		FROM
    			student stu
    		LEFT JOIN score s ON stu.s_id = s.s_id
    		LEFT JOIN course c ON s.c_id = c.c_id
    		LEFT JOIN teacher t ON c.t_id = t.t_id
    		WHERE
    			t.t_name = '张三'
    		GROUP BY
    			stu.s_id
    	)

![20200920143404753.png][]

**9) 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息**

SELECT
    	stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    GROUP BY
    	stu.s_id
    HAVING GROUP_CONCAT(s.c_id) like '%01%' and GROUP_CONCAT(s.c_id) like '%02%';
    
    或者
    
    SELECT
    	stu.*
    FROM
    	student stu
    LEFT JOIN score s1 ON stu.s_id = s1.s_id
    LEFT JOIN score s2 ON stu.s_id = s2.s_id
    WHERE
    	s1.c_id = '01'
    AND s2.c_id = '02'；

![20200920143559693.png][]

**10）查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息**

SELECT
      GROUP_CONCAT(s.c_id),
    	stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    GROUP BY
    	stu.s_id HAVING GROUP_CONCAT(s.c_id) LIKE '%01%'
    AND GROUP_CONCAT(s.c_id) not LIKE '%02%';
    
    或者
    
    SELECT
    	stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    WHERE
    	s.c_id = 01
    AND stu.s_id NOT IN (
    	SELECT
    		stu.s_id
    	FROM
    		score s
    	LEFT JOIN student stu ON s.s_id = stu.s_id
    	WHERE
    		s.c_id = 02
    )

![2020092014364555.png][]

**11）查询没有学全所有课程的同学的信息**

select * from student where s_id not in (
    select s.s_id from student s where s.s_id in(
     select stu.s_id from score s left join student stu on s.s_id = stu.s_id where c_id = 01 ) and s_id in (
     select stu.s_id from score s left join student stu on s.s_id = stu.s_id where c_id = 02) and s_id in (
     select stu.s_id from score s left join student stu on s.s_id = stu.s_id where c_id = 03)
    );
    
    或者
    
    SELECT
    	GROUP_CONCAT(s.c_id order by s.c_id) as cIds,
    	stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    GROUP BY
    	stu.s_id
    HAVING
    	cIds != (select GROUP_CONCAT(c_id order by c_id) from course);
    
    或者  最优解
    
    select a.*
    from student a
    left join score b on a.s_id = b.s_id
    group by
    a.s_id
    having
    count(b.c_id) != '3';

![20200920143820851.png][]

**12) 查询至少有一门课与学号为"01"的同学所学相同的同学的信息**

SELECT
    	GROUP_CONCAT(s.c_id),
    	stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    WHERE
    	s.c_id IN (
    		SELECT
    			c_id
    		FROM
    			score
    		WHERE
    			s_id = 01
    	)
    GROUP BY
    	stu.s_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 3][]

**13) 查询和"01"号的同学学习的课程完全相同的其他同学的信息**

SELECT
    	GROUP_CONCAT(s.c_id ORDER BY c_id) cIds,
    	stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    where stu.s_id != 01
    GROUP BY
    	s_id
    HAVING
    	cIds = (
    		SELECT
    			GROUP_CONCAT(c_id ORDER BY c_id)
    		FROM
    			score
    		WHERE
    			s_id = 01
    	)

![20200920144145874.png][]

**14) 查询没学过"张三"老师讲授的任一门课程的学生姓名**

SELECT
    	*
    FROM
    	student
    WHERE
    	s_id NOT IN (
    		SELECT
    			stu.s_id
    		FROM
    			score s
    		LEFT JOIN student stu ON s.s_id = stu.s_id
    		WHERE
    			s.c_id IN (
    				SELECT
    					c.c_id
    				FROM
    					course c
    				LEFT JOIN teacher t ON c.t_id = t.t_id
    				WHERE
    					t.t_name = '张三'
    			)
    	)

![20200920144223908.png][]

**15) 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩**

SELECT
    	stu.*, round(avg(s.s_score), 2)
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    WHERE
    	s.s_score < 60
    GROUP BY
    	stu.s_id
    HAVING
    	count(s.c_id) >= 2

![20200920144448131.png][]

**16) 检索"01"课程分数小于60，按分数降序排列的学生信息**

SELECT
    	stu.*
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    WHERE
    	s.c_id = 01
    AND s.s_score < 60
    ORDER BY
    	s.s_score DESC

![2020092014453091.png][]

**17) 成绩从高到低显示所有学生的所有课程的成绩以及平均成绩**

SELECT
    	round(avg(s4.s_score)) '平均分',
    	stu.s_name,
    	s1.s_score '语文',
    	s2.s_score '数学',
    	s3.s_score '英语'
    FROM
    	student stu
    LEFT JOIN score s1 ON stu.s_id = s1.s_id and s1.c_id = '01'
    LEFT JOIN score s2 ON stu.s_id = s2.s_id and s2.c_id = '02'
    LEFT JOIN score s3 ON stu.s_id = s3.s_id and s3.c_id = '03'
    LEFT JOIN score s4 ON stu.s_id = s4.s_id
    GROUP BY
    	stu.s_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 4][]

**18) 询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率 优良率，优秀率**

SELECT
    	c.c_id '课程ID',
    	c.c_name '课程名称',
    	round(avg(s.s_score)) '平均分',
    	max(s.s_score) '最高分',
      min(s.s_score) '最低分',
      round((select count(0) from score where c_id = c.c_id and s_score >= 60)/count(0),2) '及格率',
      round((select count(0) from score where c_id = c.c_id and s_score >= 70)/count(0),2) '中等率',
      round((select count(0) from score where c_id = c.c_id and s_score >= 80)/count(0),2) '优良率',
      round((select count(0) from score where c_id = c.c_id and s_score >= 90)/count(0),2) '优秀率'
    FROM
    	course c
    LEFT JOIN score s ON c.c_id = s.c_id
    GROUP BY
    	c.c_id

![20200920144728180.png][]

**19) 按各科成绩进行排序，并显示排名 有点坑这题**

SELECT
    	tab.s_name '学生姓名',
      tab.s_score '语文成绩',
      (@i:=@i+1) '排名'
    FROM
    	(
    		SELECT
    			stu.s_name ,
    			s.s_score 
    		FROM
    			student stu
    		LEFT JOIN score s ON stu.s_id = s.s_id
    		LEFT JOIN course c on s.c_id = c.c_id
    		WHERE
    			c.c_name = '语文'
    		ORDER BY
    			s.s_score DESC
    	) tab,(select @i:=0) indexTab
    
    UNION
    select '***','****','***'
    UNION
    select '学生姓名','数学成绩','排名'
    UNION
    SELECT
    	tab.s_name '学生姓名',
      tab.s_score '数学成绩',
      (@j:=@j+1) '排名'
    FROM
    	(
    		SELECT
    			stu.s_name ,
    			s.s_score 
    		FROM
    			student stu
    		LEFT JOIN score s ON stu.s_id = s.s_id
    		LEFT JOIN course c on s.c_id = c.c_id
    		WHERE
    			c.c_name = '数学'
    		ORDER BY
    			s.s_score DESC
    	) tab,(select @j:=0) indexTab1
    
    UNION
    select '---','---','---'
    UNION
    select '学生姓名','英语成绩','排名'
    UNION
    SELECT
    	tab.s_name '学生姓名',
      tab.s_score '英语成绩',
      (@k:=@k+1) '排名'
    FROM
    	(
    		SELECT
    			stu.s_name ,
    			s.s_score 
    		FROM
    			student stu
    		LEFT JOIN score s ON stu.s_id = s.s_id
    		LEFT JOIN course c on s.c_id = c.c_id
    		WHERE
    			c.c_name = '英语'
    		ORDER BY
    			s.s_score DESC
    	) tab,(select @k:=0) indexTab2

![2020092014483265.png][]

**20) 查询学生的总成绩并进行排名**

SELECT
    	stu.s_name '学生姓名',
    	sum(s.s_score) '总成绩'
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    GROUP BY
    	stu.s_id
    ORDER BY
    	sum(s.s_score) DESC

![20200920144909786.png][]

**22) 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩**

select * from (SELECT
    	stu.s_name,c.c_name,s.s_score
    FROM
    	score s
    LEFT JOIN  student stu ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id where c.c_id = 01
    group by  stu.s_id,c.c_id  order by s.s_score desc limit 1,2) a
    
    UNION
    
    select * from (SELECT
    	stu.s_name,c.c_name,s.s_score
    FROM
    	score s
    LEFT JOIN  student stu ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id where c.c_id = 02
    group by  stu.s_id,c.c_id  order by s.s_score desc limit 1,2) a1
     
    UNION
    
    select * from (SELECT
    	stu.s_name,c.c_name,s.s_score
    FROM
    	score s
    LEFT JOIN  student stu ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id where c.c_id = 03
    group by  stu.s_id,c.c_id  order by s.s_score desc limit 1,2) a2

![20200920145000363.png][]

**23) 统计各科成绩各分数段人数：课程编号,课程名称,\[100-85\],\[85-70\],\[70-60\],\[0-60\]及所占百分比**

SELECT
      c.c_id '课程编号',
      c.c_name '课程名称',
      (select count(0) from score where s_score >= 85 and s_score <= 100 and c_id = c.c_id) '[100-85]',
      round((select count(0) from score where s_score >= 85 and s_score <= 100 and c_id = c.c_id)/count(0),2) '[100-85]百分比',
    
      (select count(0) from score where s_score >= 70 and s_score < 85 and c_id = c.c_id) '[85-70]',
      round((select count(0) from score where s_score >= 70 and s_score < 85 and c_id = c.c_id)/count(0),2) '[85-70]百分比',
    
      (select count(0) from score where s_score >= 60 and s_score < 70 and c_id = c.c_id) '[70-60]',
      round((select count(0) from score where s_score >= 60 and s_score < 70 and c_id = c.c_id)/count(0),2) '[70-60]百分比',
    
      (select count(0) from score where s_score >= 0 and s_score < 60 and c_id = c.c_id) '[0-60]',
      round((select count(0) from score where s_score >= 0 and s_score < 60 and c_id = c.c_id)/count(0),2) '[0-60]百分比'
    FROM
    	score s
    LEFT JOIN student stu ON s.s_id = stu.s_id
    left join course c on s.c_id = c.c_id
    GROUP BY
    	c.c_id

![20200920145028934.png][]

**24) 查询学生平均成绩及其名次 **

SELECT
    	tab.s_name '学生名称',
      tab.score '平均成绩',
      (@i:=@i+1) '名次'
    FROM
    	(
    		SELECT
    			stu.s_name,
    			round(avg(s.s_score)) as score
    		FROM
    			student stu
    		LEFT JOIN score s ON stu.s_id = s.s_id
    		GROUP BY
    			stu.s_id
    		ORDER BY
    			round(avg(s.s_score)) DESC
    	) as tab ,(select @i:=0) ind

![2020092014515196.png][]

**25) 查询各科成绩前三名的记录**

select * from (select c.c_name '科目',stu.s_name '学生名称',sum(s.s_score) '成绩' from course c left join score s on c.c_id = s.c_id 
    left join student stu on stu.s_id = s.s_id  where c.c_id = '01'
    group by stu.s_id  order by s.s_score desc LIMIT 0,3) a 
    UNION
    select * from (select c.c_name '科目',stu.s_name '学生名称',sum(s.s_score) '成绩' from course c left join score s on c.c_id = s.c_id 
    left join student stu on stu.s_id = s.s_id  where c.c_id = '02'
    group by stu.s_id  order by s.s_score desc LIMIT 0,3) a1
    UNION
    select * from (select c.c_name '科目',stu.s_name '学生名称',sum(s.s_score) '成绩' from course c left join score s on c.c_id = s.c_id 
    left join student stu on stu.s_id = s.s_id  where c.c_id = '03'
    group by stu.s_id  order by s.s_score desc LIMIT 0,3) a2

![2020092014523694.png][]

**26) 查询每门课程被选修的学生数 **

SELECT
    	c.c_name,
    	count(s.s_id)
    FROM
    	course c
    LEFT JOIN score s ON c.c_id = s.c_id
    GROUP BY
    	c.c_id;

![20200920150525781.png][]

**27) 查询出只有两门课程的全部学生的学号和姓名**

SELECT
    	stu.s_id,
    	stu.s_name
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    GROUP BY
    	stu.s_id
    HAVING
    	count(s.c_id) = 2

![20200920150604538.png][]

**28) 查询男生、女生人数**

SELECT
    	stu.s_sex '性别',
    	count(0) '数量'
    FROM
    	student stu
    GROUP BY
    	stu.s_sex

![20200920150635979.png][]

**29) 查询名字中含有"风"字的学生信息**

select * from student where s_name like '%风%'

![20200920150705822.png][]

**30）查询同名同性学生名单，并统计同名人数 **

select stu.s_name , count(0) from student stu group by stu.s_name ,s_sex HAVING count(0) > 1;

![20200920150741422.png][]

**31）查询1990年出生的学生名单**

select * from student where s_brith >= '1990-01-01' and s_brith <= '1990-12-31'

![20200920150815782.png][]

**32) 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 **

SELECT
    	c.c_name,
    	round(avg(s.s_score)) avgS
    FROM
    	course c
    LEFT JOIN score s ON s.c_id = c.c_id
    group by c.c_id
    ORDER BY
    	avgS DESC,
    	c.c_id;

![20200920150903393.png][]

**33) 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩**

SELECT
    	stu.s_id,
    	stu.s_name,
    	round(avg(s.s_score)) AS avgS
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    GROUP BY
    	stu.s_id
    HAVING
    	avgS >= 85

![20200920151121995.png][]

**34) 查询课程名称为"数学"，且分数低于60的学生姓名和分数  **

SELECT
    	stu.s_name,
    	s.s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    WHERE
    	c.c_name = '数学'
    AND s.s_score < 60

![20200920151203188.png][]

**35) 查询所有学生的课程及分数情况；**

SELECT
    	stu.s_name,
      c.c_name,
    	s.s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id

![20200920151241870.png][]

**36) 查询任何一门课程成绩在70分以上的姓名、课程名称和分数**

SELECT
      stu.s_id,
    	stu.s_name,
      c.c_name,
    	s.s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    where s.s_score > 70;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 5][]

**38）查询课程编号为01且课程成绩在80分以上的学生的学号和姓名**

SELECT
      stu.s_id,
    	stu.s_name,
      c.c_name,
    	s.s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    where c.c_id = 01 and s.s_score > 80

![20200920151407239.png][]

**39）求每门课程的学生人数**

SELECT
    	c.c_name,
    	count(s.c_id)
    FROM
    	course c
    LEFT JOIN score s ON s.c_id = c.c_id
    GROUP BY
    	c.c_id

![20200920151444445.png][]

**40）查询选修"张三"老师所授课程的学生中，总成绩最高的学生信息及其成绩 **

SELECT
    	stu.*, sum(s.s_score) toScore
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    LEFT JOIN teacher t ON c.t_id = t.t_id
    WHERE
    	t.t_name = '张三'
    GROUP BY
    	stu.s_id
    ORDER BY
    	toScore DESC
    LIMIT 1

![20200920151516212.png][]

**41）查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 **

SELECT
    	stu.s_id,stu.s_name,c.c_id,c.c_name,s.s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    where (
    	SELECT
    		 count(0)
    	FROM
    		student stu1
    	LEFT JOIN score s1 ON stu1.s_id = s1.s_id
    	LEFT JOIN course c1 ON s1.c_id = c1.c_id
      where s.s_score = s1.s_score and c.c_id != c1.c_id 
    ) > 1;

![20200920151547498.png][]

**42） 查询每门功成绩最好的前两名 **

select * from (SELECT
    	c.c_name,
    	stu.s_name,
    	s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    WHERE
    	s.c_id = 01
    ORDER BY
    	s.s_score desc limit 0,2) a
    
    union 
    
    select * from (SELECT
    	c.c_name,
    	stu.s_name,
    	s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    WHERE
    	s.c_id = 02
    ORDER BY
    	s.s_score desc limit 0,2) a1
    
    union 
    
    select * from (SELECT
    	c.c_name,
    	stu.s_name,
    	s_score
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    LEFT JOIN course c ON s.c_id = c.c_id
    WHERE
    	s.c_id = 03
    ORDER BY
    	s.s_score desc limit 0,2) a2

![20200920151638775.png][]

**43）统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，**

SELECT
    	c.c_id,
    	c.c_name,
    	count(0)
    FROM
    	course c
    LEFT JOIN score s ON c.c_id = s.c_id
    GROUP BY
    	c.c_id
    HAVING
    	count(0) > 5
    ORDER BY
    	count(0) DESC

![20200920151713818.png][]

**44）检索至少选修两门课程的学生学号 **

SELECT
    	stu.*
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    GROUP BY
    	stu.s_id
    HAVING
    	count(0) >= 2

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 6][]

**45）查询选修了全部课程的学生信息**

SELECT
    	stu.*
    FROM
    	student stu
    LEFT JOIN score s ON stu.s_id = s.s_id
    GROUP BY
    	stu.s_id
    HAVING
    	count(0) = (SELECT count(0) FROM course)

**![20200920151830963.png][]**

**46）查询各学生的年龄**

select s_name,timestampdiff(year,s_brith,now()) from student

![20200920151923514.png][]

**47）查询本周过生日的学生**

select * from student where week(now()) = week(date_format(s_brith,'%Y%m%d'));

![20200920152016346.png][]

**48）查询下周过生日的学生**

select * from student where month(now()) = month(DATE_FORMAT(s_brith,'%Y%m%d'))

**49) 查询本月过生日的学生**

select * from student where month(now()) = month(DATE_FORMAT(s_brith,'%Y%m%d'))

**50) 查询下月过生日的学生**

select * from student where month(now())+1 = month(DATE_FORMAT(s_brith,'%Y%m%d'))

到此结束！

[20200920142003378.png]: /images/20221123/d58d3ed096114a50b4f7743b8dbea71a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70]: /images/20221123/8e3a4b9a7c304d488eb9ad182cb0278a.png
[20200920142040719.png]: /images/20221123/95e4db107d384350a7466c1cf67b57bc.png
[20200920142100591.png]: /images/20221123/f33e393390da4a958ce216e3afc8092e.png
[20200920142826733.png]: /images/20221123/27ac067e51f34bf2b20138c467f3c4fc.png
[2020092014291981.png]: /images/20221123/4668be980b504dff93609d1c55433a21.png
[20200920143012631.png]: /images/20221123/b53342289c4241d39fd6d16dc43f9b5c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 1]: /images/20221123/3533ca63985741aca7c338094ca0e7a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 2]: /images/20221123/bfad45c544a84b0580fb1512f9e577a0.png
[2020092014323236.png]: /images/20221123/4aa0f8cacd6143a287b5c6bca5d965ac.png
[20200920143317145.png]: /images/20221123/254fe66ea2e54131aa7b28b362967213.png
[20200920143404753.png]: /images/20221123/72615df977354287ac1bd95312cdcbf3.png
[20200920143559693.png]: /images/20221123/f31aa21a7b1d4e8f99ae455ef22e39e8.png
[2020092014364555.png]: /images/20221123/224d181a1caf4ca491fd7a427bdcf817.png
[20200920143820851.png]: /images/20221123/9c43527e59b84c37b9dd8cc8b842077f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 3]: /images/20221123/de5378d926d84a4dabd508494b0bf85b.png
[20200920144145874.png]: /images/20221123/2aafa84827a44fc78f57af69aaecbb32.png
[20200920144223908.png]: /images/20221123/1453ae11dce54469a4f5b39c2e251af6.png
[20200920144448131.png]: /images/20221123/24fa6ece28914c7f9d524a87632affcb.png
[2020092014453091.png]: /images/20221123/3fc862f7189b41cf9902f413bfe192e5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 4]: /images/20221123/1fdf997bd415482391cb18e6357b026d.png
[20200920144728180.png]: /images/20221123/f83442f2d92e44808513eea0bb693c67.png
[2020092014483265.png]: /images/20221123/5c224202ef56493a95ee57471bf4852b.png
[20200920144909786.png]: /images/20221123/af37f6f767ef4a64a82d36e3668b358d.png
[20200920145000363.png]: /images/20221123/a6802274a90f4ff1baea98d15e7dc559.png
[20200920145028934.png]: /images/20221123/54707f5a6b044c8e9d59b59a00243414.png
[2020092014515196.png]: /images/20221123/e1f3f0cbc88e41dc85b5684f7e9f3f87.png
[2020092014523694.png]: /images/20221123/910439e30bd045bcaa7949a9736e4876.png
[20200920150525781.png]: /images/20221123/2e9521390e2e48958960a300ac84abd0.png
[20200920150604538.png]: /images/20221123/ba3c2d7f1fa246378f27076dedbb56c9.png
[20200920150635979.png]: /images/20221123/f2f6931e80dd4d0dae57fba3731fd5d2.png
[20200920150705822.png]: /images/20221123/62b62ff703c0452b9cf2c96d33cd1062.png
[20200920150741422.png]: /images/20221123/d3951317dc1b47c69ceb3ece0aeec0d3.png
[20200920150815782.png]: /images/20221123/de958e305e8c4c24ae8815ddf01cc893.png
[20200920150903393.png]: /images/20221123/52357a8f53fb49368e9387f203809219.png
[20200920151121995.png]: /images/20221123/44d863d5943c4d21aa123a942ef3e3c9.png
[20200920151203188.png]: /images/20221123/f756c12e969f48e3bc86ddd95b9d70b5.png
[20200920151241870.png]: /images/20221123/f5a179e731274116a3ebb35758c809c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 5]: /images/20221123/d062ab62021f49cfac3ea105ed274607.png
[20200920151407239.png]: /images/20221123/79434d92383447f0be2a38d763911d4b.png
[20200920151444445.png]: /images/20221123/ab39513b79694e1c9141efed60b1a9cd.png
[20200920151516212.png]: /images/20221123/0f4fb18be83e443b8219ead4b7dbd387.png
[20200920151547498.png]: /images/20221123/f4f6002500e04b3ebbd7e8a9a7b5c887.png
[20200920151638775.png]: /images/20221123/c98302cc444646f58536f2e0c6ccf010.png
[20200920151713818.png]: /images/20221123/dc581ad32fee46128c7d4e10a62558b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nbGVpMDgyMzE5_size_16_color_FFFFFF_t_70 6]: /images/20221123/68873b799d87489792bccafb2fdedc2b.png
[20200920151830963.png]: /images/20221123/2bea65b32e7c40b590b3911f47713f10.png
[20200920151923514.png]: /images/20221123/6ab63322c234442699fafc9d62c9976b.png
[20200920152016346.png]: /images/20221123/5483b471a9384a5cbc8d778fe823b1b3.png