-肆行无忌网

发布时间：2025-06-24 17:23:52 作者：北方职教升学中心阅读量：386

搭建环境

学生表 Student
科目表 Course
教师表 Teacher
成绩表 SC

三、搭建环境

学生表 Student

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-12-20' , '男');insert into Student values('04' , '李云' , '1990-12-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-01-01' , '女');insert into Student values('07' , '郑竹' , '1989-01-01' , '女');insert into Student values('09' , '张三' , '2017-12-20' , '女');insert into Student values('10' , '李四' , '2017-12-25' , '女');insert into Student values('11' , '李四' , '2012-06-06' , '女');insert into Student values('12' , '赵六' , '2013-06-13' , '女');insert into Student values('13' , '孙七' , '2014-06-01' , '女');

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);

三、女生人数

> select ssex, count(*) from Student    -> group by ssex;

在这里插入图片描述

查询名字中含有「风」字的学生信息

> select *    -> from Student    -> where Student.Sname like '%风%'    -> ;

在这里插入图片描述

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

> select sname, count(*) from Student    -> group by sname    -> having count(*)>1;

在这里插入图片描述

查询 1990 年出生的学生名单

> select *    -> from Student    -> where YEAR(Student.Sage)=1990;

在这里插入图片描述
25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

查询平均成绩大于等于 85 的所有学生的学号、学生成绩
查询每门功成绩最好的前两名

统计每门课程的学生选修人数（超过 5 人的课程才统计）。数据表介绍

数据表介绍
–1.学生表

Student(SId,Sname,Sage,Ssex)--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

–2.课程表

Course(CId,Cname,TId)--CId 课程编号,Cname 课程名称,TId 教师编号

–3.教师表

Teacher(TId,Tname)--TId 教师编号,Tname 教师姓名

–4.成绩表

--SId 学生编号,CId 课程编号,score 分数

二、

> select SC.cid, count(sid) as cc from SC    -> group by cid    -> having cc >5;

在这里插入图片描述

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

> select sid, count(cid) as cc from SC    -> group by sid    -> having cc>=2;

在这里插入图片描述

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

查询各学生的年龄，只按年份来算

SELECT s.SId, s.SNameFROM Student sINNER JOIN SC sc ON s.SId = sc.SIdWHERE sc.Score > 80 AND sc.CId != '01';

在这里插入图片描述

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

> select Student.SId as 学生编号,Student.Sname  as  学生姓名,    -> TIMESTAMPDIFF(YEAR,Student.Sage,CURDATE()) as 学生年龄    -> from Student    -> ;

在这里插入图片描述

查询本周过生日的学生

select *from Student where WEEKOFYEAR(Student.Sage)=WEEKOFYEAR(CURDATE());

在这里插入图片描述

查询下周过生日的学生

select *from Student where WEEKOFYEAR(Student.Sage)=WEEKOFYEAR(CURDATE())+1;

在这里插入图片描述

查询本月过生日的学生

l> select *    -> from Student    -> where MONTH(Student.Sage)=MONTH(CURDATE());

在这里插入图片描述

查询下月过生日的学生

> select *    -> from Student    -> where MONTH(Student.Sage)=MONTH(CURDATE())+1;

在这里插入图片描述

练习题

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

select * from  (    ->     select t1.SId, class1, class2    ->     from    ->         (SELECT SId, score as class1 FROM SC WHERE SC.CId = '01') AS t1,     ->         (SELECT SId, score as class2 FROM SC WHERE SC.CId = '02') AS t2    ->     where t1.SId = t2.SId and t1.class1 > t2.class2    -> ) r    -> LEFT JOIN Student    -> ON Student.SId = r.SId;

在这里插入图片描述

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

select * from      (select * from SC where SC.CId = '01') as t1,     (select * from SC where SC.CId = '02') as t2 where t1.SId = t2.SId;

在这里插入图片描述

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

select * from    -> (select * from SC where SC.CId = '01') as t1    -> left join    -> (select * from SC where SC.CId = '02') as t2    -> on t1.SId = t2.SId;

在这里插入图片描述

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

> select * from SC    -> where SC.SId not in (    ->     select SId from SC    ->     where SC.CId = '01'    -> )    -> AND SC.CId= '02';

在这里插入图片描述

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

> select Student.SId, Student.Sname, r.ss from Student right join(    ->       select SId, AVG(score) AS ss from SC    ->       GROUP BY SId    ->       HAVING AVG(score)> 60    -> )r on Student.SId = r.SId;

在这里插入图片描述

查询在 SC 表存在成绩的学生信息

> select DISTINCT Student.*    -> from Student,SC    -> where Student.SId=SC.SId    -> ;

在这里插入图片描述

查询所有同学的学生编号、姓名和平均成绩
查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

> select Student.sname, SC.score from Student, SC, Course    -> where Student.sid = SC.sid    -> and Course.cid = SC.cid    -> and Course.cname = "数学"    -> and SC.score < 60;

在这里插入图片描述

查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select Student.sname, cid, score from Studentleft join SCon Student.sid = SC.sid;

在这里插入图片描述

查询任何一门课程成绩在 70 分以上的姓名、学生姓名、所有课程的总成绩(没成绩的显示为 null )

> select Student.sid, Student.sname,r.coursenumber,r.scoresum    -> from Student,    -> (select SC.sid, sum(SC.score) as scoresum, count(SC.cid) as coursenumber from SC    -> group by SC.sid)r    -> where Student.sid = r.sid;

在这里插入图片描述

4.1查有成绩的学生信息

> select * from Student    -> where Student.sid in (select SC.sid from SC);

在这里插入图片描述

查询「李」姓老师的数量

> select count(*)    -> from Teacher    -> where tname like '李%';

在这里插入图片描述

查询学过「张三」老师授课的同学的信息

> select Student.* from Student,Teacher,Course,SC    -> where    ->     Student.sid = SC.sid    ->     and Course.cid=SC.cid    ->     and Course.tid =Teacher.tid    ->     and tname = '张三';

在这里插入图片描述

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

> select * from Student    -> where Student.sid not in (    ->   select SC.sid from SC    ->   group by SC.sid    ->   having count(SC.cid)= (select count(cid) from Course)    -> );

在这里插入图片描述

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

> select * from Student    -> where Student.sid in (    ->     select SC.sid from SC    ->     where SC.cid in(    ->         select SC.cid from SC    ->         where SC.sid = '01'    ->     )    -> );

在这里插入图片描述

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

> SELECT a.SId, a.Sname, a.Sage, a.Ssex    -> FROM Student a, SC b    -> WHERE a.SId = b.SId AND b.CId IN (    ->     SELECT CId    ->     FROM SC    ->     WHERE SId = '01'    -> ) AND a.SId <> '01';

在这里插入图片描述

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

> select * from Student    ->      where Student.sid not in(    ->         select SC.sid from SC,Course,Teacher    ->         where    ->             SC.cid = Course.cid    ->              and Course.tid = Teacher.tid    ->             and Teacher.tname= "张三");

在这里插入图片描述

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

select Student.SId, Student.Sname,b.avg    -> from Student RIGHT JOIN    -> (select sid, AVG(score) as avg from SC    ->     where sid in (    ->               select sid from SC    ->               where score<60    ->               GROUP BY sid    ->               HAVING count(score)>1)    ->     GROUP BY sid) b on Student.sid=b.sid;

在这里插入图片描述

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

> select Student.*, SC.score from Student, SC    -> where Student.sid = SC.sid    -> and SC.score < 60    -> and cid = "01"    -> ORDER BY SC.score DESC;

在这里插入图片描述

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

> select *  from SC    -> left join (    ->     select sid,avg(score) as avscore from SC    ->     group by sid    ->     )r    -> on SC.sid = r.sid    -> order by avscore desc;

在这里插入图片描述