Luckean's blog

Appearance

表名和字段 ​

–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) –学生编号,课程编号,分数

测试数据 ​

[复制代码](javascript:void(0)😉

sql
#–1.学生表 
#Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
CREATE TABLE `Student` (
    `s_id` VARCHAR(20),
    s_name VARCHAR(20) NOT NULL DEFAULT '',
    s_brith VARCHAR(20) NOT NULL DEFAULT '',
    s_sex VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(s_id)
);

#–2.课程表 
#Course(c_id,c_name,t_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)
);

/*
–3.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
*/
CREATE TABLE Teacher(
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

/*
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
*/
Create table Score(
    s_id VARCHAR(20),
    c_id VARCHAR(20) not null default '',
    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);

插入数据 ​

sql
/*--插入学生表测试数据 */
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);

练习题和sql语句 ​

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

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

sql
select * FROM (select s_id,s_score FROM sc where c_id = '01') as t1,(SELECT s_id,s_score from sc where c_id='02') as t2 WHERE t1.s_id = t2.s_id and t1.s_score > t2.s_score;

+------+---------+------+---------+ | s_id | s_score | s_id | s_score | +------+---------+------+---------+ | 02 | 70 | 02 | 60 | | 04 | 50 | 04 | 30 | +------+---------+------+---------+

sql
select a.*,b.s_score as score01,c.s_score as score02 FROM
　　student a
　　JOIN score b ON a.s_id=b.s_id and b.c_id='01'
　　LEFT JOIN score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL
WHERE b.s_score>c.s_score ;

视频答案 ​

自链接

sql
select
 c.*,
 a.s_score as s01,
 b.s_score as s02
FROM
sc a, sc b, student c
WHERE 
a.s_id = b.s_id
and a.s_id = c.s_id -- 自链接方式
and a.c_id = '01' --条件
and b.c_id = '02'
and a.s_score > b.s_score;

长型数据变 宽行数据

sql
SELECT
	s.*,
	t.s01,
	t.s02
from
(select
	a.s_id,
	max(case WHEN a.c_id = '01' THEN a.s_score end) s01,
	max(case WHEN a.c_id = '02' THEN a.s_score end) s02
from sc a
GROUP BY a.s_id) t,student s
where s.s_id = t.s_id
and t.s01 > t.s02;

| s_id | s_name | s_brith | s_sex | s01 | s02 | | 02 | 钱电 | 1990-12-21 | 男 | 70 | 60 | | 04 | 李云 | 1990-08-06 | 男 | 50 | 30 |

img

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

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

子查询

sql
select
	a.s_id,
	(select s_name from student s where s.s_id = a.s_id) s_name,
	AVG(a.s_score) as01
from
	sc a
GROUP BY
	a.s_id
HAVING
	as01 >= 60

两个表连接

sql
select
	a.s_id,
	s.s_name,
	AVG(a.s_score) as01
from
	sc a,student s
where
	s.s_id = a.s_id
GROUP BY
	a.s_id
HAVING
	as01 >= 60;

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

sql
select 
	a.s_id,
	s.s_name,
	AVG(a.s_score) as avg_01
FROM
	sc a
right join
	 student s
on
	s.s_id = a.s_id
GROUP BY s.s_id
HAVING avg_01 < 60;

ps: 08号学生,没有考试,null,上面没有查询到

sql
select 
	s.s_id,
	s.s_name,
	IFNULL(AVG(a.s_score),0) as avg_01
FROM
	sc a
right join
	 student s
on
	s.s_id = a.s_id
GROUP BY s.s_id
HAVING avg_01 < 60;

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

student score count group sum

sql
select
	a.s_id,
	s.s_name,
	count(a.c_id) as c,
	sum(a.s_score) as score
from
	sc a
inner join
	student s
ON
	a.s_id = s.s_id
GROUP BY
	a.s_id;

+------+--------+---+-------+ | s_id | s_name | c | score | +------+--------+---+-------+ | 01 | 赵雷 | 3 | 269 | | 02 | 钱电 | 3 | 210 | | 03 | 孙风 | 3 | 240 | | 04 | 李云 | 3 | 100 | | 05 | 周梅 | 2 | 163 | | 06 | 吴兰 | 2 | 65 | | 07 | 郑竹 | 2 | 187 | +------+--------+---+-------+

没有08的学员

解决很简单,由内连接改成外连接

sql
select
	s.s_id,
	s.s_name,
	count(a.c_id) as c,
	IFNULL(sum(a.s_score),0) as score
from
	sc a
right join
	student s
ON
	a.s_id = s.s_id
GROUP BY
	s.s_id;

+------+--------+---+-------+ | s_id | s_name | c | score | +------+--------+---+-------+ | 01 | 赵雷 | 3 | 269 | | 02 | 钱电 | 3 | 210 | | 03 | 孙风 | 3 | 240 | | 04 | 李云 | 3 | 100 | | 05 | 周梅 | 2 | 163 | | 06 | 吴兰 | 2 | 65 | | 07 | 郑竹 | 2 | 187 | | 08 | 王菊 | 0 | 0 | +------+--------+---+-------+

6、查询"李"姓老师的数量 ​

sql
select count(t_name) cnt_t from teacher where t_name like '李%';

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

teacher ->cource->score->->student

sql
select
	s.*
FROM
	teacher t,course c, sc sc,student s
WHERE
	t.t_id = c.t_id
AND c.c_id = sc.c_id
AND sc.s_id = s.s_id
AND t.t_name = '张三';

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

sql
select * from student WHERE s_id not IN (select
	sc.s_id
FROM
	teacher t,course c, sc sc
WHERE
	t.t_id = c.t_id
AND c.c_id = sc.c_id
AND t.t_name = '张三');

还有一种 NOT EXISTS

NOT EXISTS比 not in 的查询效率比较高

sql
select * from student s WHERE not EXISTS(	
	select 1 from (
		select
			sc.s_id
		FROM
			teacher t,course c, sc sc
		WHERE
			t.t_id = c.t_id
		AND c.c_id = sc.c_id
		AND t.t_name = '张三'
	) t1
	WHERE t1.s_id = s.s_id
);

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

student score

sql
SELECT
	s.*
FROM
	sc s01,sc s02, student s
WHERE
	s01.s_id = s02.s_id
AND s.s_id = s01.s_id
AND	s01.c_id = '01'
AND s02.c_id = '02'

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

sql
SELECT s.* FROM	
	(select 
		s_id,
		max(case WHEN c_id = '01' then s_score end) c01,
		max(case when c_id = '02' then s_score end) c02
	FROM
		sc
	GROUP BY
		sc.s_id) t, student s
WHERE
	s.s_id = t.s_id
AND t.c01 is not NULL
and t.c02 is null;

+------+--------+------------+-------+ | s_id | s_name | s_brith | s_sex | +------+--------+------------+-------+ | 06 | 吴兰 | 1992-03-01 | 女 | +------+--------+------------+-------+

null是不可被比较的

11、查询没有学全所有课程的同学的信息 ​

没有学全所有课程 -> 没有学全所有 是条件 -> 课程cource -> 同学所学课程 score ->同学信息student

sql
select 
	s.s_id,
	count(sc.c_id) cnt
FROM
	student s
LEFT JOIN
	sc
ON --连接两个表
	s.s_id = sc.s_id
GROUP BY
	s.s_id
HAVING
	COUNT(sc.c_id) < (select count(c_id) from course);

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

distinct 去重复项

sql
SELECT
	DISTINCT s.*  --DISTINCT重复项
FROM
	student s
left JOIN
	sc
ON
	s.s_id = sc.s_id
WHERE
	sc.c_id IN (select c_id from sc WHERE s_id = '01')
sql
SELECT
	s.*
FROM
	student s
left JOIN
	sc
ON
	s.s_id = sc.s_id
WHERE
	sc.c_id IN (select c_id from sc WHERE s_id = '01')
GROUP BY
	s.s_id

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

sql
create table s01_s_temp as
    select 
        t.*,b.s_id sid2,b.c_id cid2
    from     
        (select 
            a.*,b.c_id
        from  
            student a,
            (select c_id from sc where s_id='01') b) t  
    left join         
        sc b 
    on  t.s_Id=  b.s_id
    and t.c_id=b.c_id
        
    union 
    
    select 
        t.*,b.s_id sid2,b.c_id cid2
    from 
        (select 
            a.*,b.c_id
        from  
            student a,
            (select c_id from sc where s_id='01') b) t  
    right join         
        sc b 
    on  t.s_Id=  b.s_id
    and t.c_id=b.c_id;

select * from student 
where s_id not in(
    select s_id from s01_s_temp where cid2 is null)
and s_id not in(
    select sid2 s_id from s01_s_temp where c_id is null)    
and s_id!='01';

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

和第八题重复

sql
select

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

解题思路:先拿学号,姓名,平均成绩(group),两门的条件后面写

sql
SELECT
	s.s_id,
	s.s_name,
	avg(sc.s_score) as avg_s
FROM
	student s
LEFT JOIN
	sc
ON
	sc.s_id = s.s_id
GROUP BY
	s.s_id
HAVING --查询两门及其以上不及格
	sum(case WHEN sc.s_score >=60 then 0 ELSE 1 END) >=2

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

sql
SELECT
	s.*,sc.s_score
FROM
	student s, sc
WHERE
		s.s_id = sc.s_id
AND sc.s_score < 60
AND sc.c_id = '01'
ORDER BY 
	sc.s_score DESC

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

sql
SELECT
	t1.*,
	t2.avg_s
FROM
	(SELECT * from sc) t1,
	(select s_id, ROUND(AVG(s_score),2) avg_s FROM sc GROUP BY s_id) t2
WHERE
	t1.s_id = t2.s_id
ORDER BY
	t2.avg_s DESC;

+------+------+---------+-------+ | s_id | c_id | s_score | avg_s | +------+------+---------+-------+ | 07 | 03 | 98 | 93.50 | | 07 | 02 | 89 | 93.50 | | 01 | 02 | 90 | 89.67 | | 01 | 03 | 99 | 89.67 | | 01 | 01 | 80 | 89.67 | | 05 | 01 | 76 | 81.50 | | 05 | 02 | 87 | 81.50 | | 03 | 01 | 80 | 80.00 | | 03 | 02 | 80 | 80.00 | | 03 | 03 | 80 | 80.00 | | 02 | 01 | 70 | 70.00 | | 02 | 02 | 60 | 70.00 | | 02 | 03 | 80 | 70.00 | | 04 | 01 | 50 | 33.33 | | 04 | 02 | 30 | 33.33 | | 04 | 03 | 20 | 33.33 | | 06 | 01 | 31 | 32.50 | | 06 | 03 | 34 | 32.50 | +------+------+---------+-------+

mysql 8.0以上版本加入了开窗函数over

sql
SELECT
	sc.*,
	round(AVG(s_score) over(PARTITION by sc.s_id),2) avg_S
FROM
	sc
ORDER BY
	avg_s DESC;

+------+------+---------+-------+ | s_id | c_id | s_score | avg_S | +------+------+---------+-------+ | 07 | 03 | 98 | 93.50 | | 07 | 02 | 89 | 93.50 | | 01 | 03 | 99 | 89.67 | | 01 | 01 | 80 | 89.67 | | 01 | 02 | 90 | 89.67 | | 05 | 01 | 76 | 81.50 | | 05 | 02 | 87 | 81.50 | | 03 | 01 | 80 | 80.00 | | 03 | 02 | 80 | 80.00 | | 03 | 03 | 80 | 80.00 | | 02 | 01 | 70 | 70.00 | | 02 | 02 | 60 | 70.00 | | 02 | 03 | 80 | 70.00 | | 04 | 01 | 50 | 33.33 | | 04 | 03 | 20 | 33.33 | | 04 | 02 | 30 | 33.33 | | 06 | 01 | 31 | 32.50 | | 06 | 03 | 34 | 32.50 | +------+------+---------+-------+

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 ​

COUNT(1) 会计算所有行的数量,计算某种比率或者作为其他指标的一部分

sql
SELECT
	c.c_id,
	c.c_name,
	MAX(s.s_score) max_s,
	MIN(s.s_score) min_s,
	AVG(s.s_score) avg_s,
	sum(case WHEN s.s_score >= 60 THEN 1 ELSE 0 END)/COUNT(1) jige,
	sum(case when s.s_score >=70 AND s.s_score < 80 THEN 1 ELSE 0 END)/COUNT(1) zhongdeng,
	sum(case when s.s_score >=80 AND s.s_score < 90 THEN 1 ELSE 0 END)/COUNT(1) youliang,
	sum(case when s.s_score >=90 THEN 1 ELSE 0 END)/COUNT(1) youxiu
FROM
	course c
LEFT JOIN
	sc s
ON
	s.c_id = c.c_id
GROUP BY
	c.c_id;

+------+--------+-------+-------+---------+--------+-----------+----------+--------+ | c_id | c_name | max_s | min_s | avg_s | jige | zhongdeng | youliang | youxiu | +------+--------+-------+-------+---------+--------+-----------+----------+--------+ | 01 | 语文 | 80 | 31 | 64.5000 | 0.6667 | 0.3333 | 0.3333 | 0.0000 | | 02 | 数学 | 90 | 30 | 72.6667 | 0.8333 | 0.0000 | 0.5000 | 0.1667 | | 03 | 英语 | 99 | 20 | 68.5000 | 0.6667 | 0.0000 | 0.3333 | 0.3333 | +------+--------+-------+-------+---------+--------+-----------+----------+--------+

19、按各科成绩进行排序，并显示排名(实现不完全) ​

PARTITION BY

在SQL中，PARTITION BY子句用于将结果集分割成不同的分区，每个分区内的数据将被独立处理。通常，PARTITION BY结合窗口函数一起使用，以便在分组内对数据进行分析或计算。

在你提供的查询中，PARTITION BY c_id表示将数据按照 c_id 列的值进行分区。换句话说，对于每个不同的 c_id 值，都会创建一个独立的分区。然后，窗口函数 RANK() over(PARTITION BY c_id ORDER BY c_id, s_score DESC) 将在每个分区内按照 c_id 的值进行排序，并为每个分区中的每一行分配一个排名（按照 s_score 的降序排列）

开窗函数

RANK() 会根据index,并列省略 ​

SQL
SELECT
	a.*,
	RANK() over(PARTITION BY c_id ORDER BY c_id,s_score DESC) rk
FROM
	sc a;

+------+------+---------+----+ | s_id | c_id | s_score | rk | +------+------+---------+----+ | 01 | 01 | 80 | 1 | | 03 | 01 | 80 | 1 | | 05 | 01 | 76 | 3 | | 02 | 01 | 70 | 4 | | 04 | 01 | 50 | 5 | | 06 | 01 | 31 | 6 |

还有两种开窗函数

dense_rank() 排名不会跳过 ​

+------+------+---------+----+ | s_id | c_id | s_score | rk | +------+------+---------+----+ | 01 | 01 | 80 | 1 | | 03 | 01 | 80 | 1 | | 05 | 01 | 76 | 2 | | 02 | 01 | 70 | 3 | | 04 | 01 | 50 | 4 | | 06 | 01 | 31 | 5 |

row_number() 没有并列,排名 ​

+------+------+---------+----+ | s_id | c_id | s_score | rk | +------+------+---------+----+ | 01 | 01 | 80 | 1 | | 03 | 01 | 80 | 2 | | 05 | 01 | 76 | 3 | | 02 | 01 | 70 | 4 | | 04 | 01 | 50 | 5 | | 06 | 01 | 31 | 6 |

用子查询 rank

sql
SELECT
	a.*,
	(SELECT COUNT(s_score) FROM sc b WHERE b.c_id = a.c_id and a.s_score < b.s_score) + 1 rk
FROM
	sc a
ORDER BY
	c_id,s_score DESC

distinct

sql
SELECT
	a.*,
	(SELECT COUNT(distinct s_score) FROM sc b WHERE b.c_id = a.c_id and a.s_score <= b.s_score) rk
FROM
	sc a
ORDER BY
	c_id,s_score DESC

20、查询学生的总成绩并进行排名 ​

sql
SELECT
	t.*,
	RANK() over(ORDER BY t.sum_s DESC) rk
FROM 
	(SELECT
			s_id,
			SUM(s_score) sum_s
		FROM
			sc
		GROUP BY
			s_id) t

子查询

sql
CREATE TABLE temp as SELECT s_id,SUM(s_score) sum_s FROM sc GROUP BY s_id 

SELECT 
	a.*,
	(SELECT COUNT(sum_s) FROM temp b WHERE a.sum_s < b.sum_s) + 1 rk
FROM	
	temp a
ORDER BY
	sum_s DESC

21、查询不同老师所教不同课程平均分从高到低显示 ​

sql
SELECT
	a.*,
	RANK() over(ORDER BY avg_s DESC)
FROM
	(SELECT
	AVG(s_score) avg_s
FROM
	sc b
GROUP BY
	c_id) a

自查询

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

sql
SELECT
	a.*,
	b.s_score,
	b.rk
FROM
	student a
LEFT JOIN
	(SELECT
		sc.*,
		RANK() over(ORDER BY s_score desc) rk
	FROM
	 sc) b
on a.s_id = b.s_id
WHERE b.rk in (2,3);

+------+--------+------------+-------+---------+------+ | s_id | s_name | s_brith | s_sex | s_score | rk | +------+--------+------------+-------+---------+------+ | 07 | 郑竹 | 1989-07-01 | 女 | 98 | 2 | | 01 | 赵雷 | 1990-01-01 | 男 | 90 | 3 | +------+--------+------------+-------+---------+------+

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

sql
SELECT
	a.c_id,a.c_name,
	sum(case WHEN b.s_score >= 85 THEN 1 ELSE 0 END) "[100-85]",
	sum(case WHEN b.s_score >= 85 THEN 1 ELSE 0 END)/count(1) "[100-85]%"
FROM
	course a
left JOIN
	sc b
ON a.c_id = b.c_id
GROUP BY
	a.c_id,a.c_name;

24、查询学生平均成绩及其名次 ​

sql
SELECT
	a.*,
	RANK() over(ORDER BY b.avg_s DESC) rk
FROM
	student a
LEFT JOIN
	(SELECT
		s_id,
		AVG(b.s_score) avg_s
	FROM
		sc b
	GROUP BY
		s_id) b
	ON a.s_id = b.s_id

子查询(有缺陷,第八显示第一)

sql
create table temp24 as 
	SELECT
		s_id,
		AVG(b.s_score) avg_s
	FROM
		sc b
	GROUP BY
		s_id;
		
SELECT
	a.*,
	b.avg_s,
	(SELECT COUNT(avg_s) FROM temp24 c WHERE avg_s  AND b.avg_s < c.avg_s) + 1 rk
FROM
	student a
LEFT JOIN
	temp24 b
ON
	a.s_id = b.s_id;

25、查询各科成绩前三名的记录 ​

sql
SELECT
   a.*
FROM
   (SELECT
   	c_id,
   	s_score,
   	RANK() over(PARTITION by c_id ORDER BY s_score DESC) rk
   FROM sc) a
WHERE
   a.rk in (1,2,3)

26、查询每门课程被选修的学生数 ​

sql
SELECT
	a.c_id,a.c_name,COUNT(b.s_id) cns
FROM
	course a
LEFT JOIN
	sc b
ON
	a.c_id = b.c_id
GROUP BY
	a.c_id

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

sql
SELECT
	a.*
FROM
	student a
LEFT JOIN
	sc b
ON
	a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	COUNT(c_id) = 2

28、查询男生、女生人数 ​

sql
SELECT
	s_sex,count(s_id) cnt
FROM
	student a
GROUP BY
	a.s_sex

29、查询名字中含有"风"字的学生信息 ​

sql
SELECT
 a.*
FROM
	student a
WHERE
	s_name LIKE '%风%'

30、查询同名同性学生名单，并统计同名人数 ​

根据名跟性分组

sql
SELECT
	s_name,COUNT(s_name) cns
FROM
 student a
GROUP BY
	s_name,s_sex
HAVING
	cns > 1

31、查询1990年出生的学生名单 ​

sql

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

sql

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

sql

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

sql

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

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

sql

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

   select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a
       left join student b on a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85

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

       select a.s_name,b.s_score from score b LEFT JOIN student a on a.s_id=b.s_id where b.c_id=(
                   select c_id from course where c_name ='数学') and b.s_score<60

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


       select a.s_id,a.s_name,
                   SUM(case c.c_name when '语文' then b.s_score else 0 end) as '语文',
                   SUM(case c.c_name when '数学' then b.s_score else 0 end) as '数学',
                   SUM(case c.c_name when '英语' then b.s_score else 0 end) as '英语',
                   SUM(b.s_score) as  '总分'
       from student a left join score b on a.s_id = b.s_id 
       left join course c on b.c_id = c.c_id 
       GROUP BY a.s_id,a.s_name


-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 
           select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
               left join student a on a.s_id=c.s_id where c.s_score>=70



-- 37、查询不及格的课程
       select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id
           where a.s_score<60 

--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； 
       select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
           where a.c_id = '01' and a.s_score>80

-- 39、求每门课程的学生人数 
       select count(*) from score GROUP BY c_id;

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


       -- 查询老师id   
       select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三'
       -- 查询最高分（可能有相同分数）
       select MAX(s_score) from score where c_id='02'
       -- 查询信息
       select a.*,b.s_score,b.c_id,c.c_name from student a
           LEFT JOIN score b on a.s_id = b.s_id
           LEFT JOIN course c on b.c_id=c.c_id
           where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三')
           and b.s_score in (select MAX(s_score) from score where c_id='02')
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
   select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score
-- 42、查询每门功成绩最好的前两名 
       -- 牛逼的写法
   select a.s_id,a.c_id,a.s_score from score a
       where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
       select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC
-- 44、检索至少选修两门课程的学生学号 
       select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2
-- 45、查询选修了全部课程的学生信息 
       select * from student where s_id in(        
           select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))
--46、查询各学生的年龄
   -- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
   select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - 
               (case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age
       from student;


-- 47、查询本周过生日的学生
   select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)
   select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))

   select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))

-- 48、查询下周过生日的学生
   select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth)

-- 49、查询本月过生日的学生

   select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)

-- 50、查询下月过生日的学生
   select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)

Lucking

粤ICP备2022078904号-1