mySQL

最新推荐文章于 2022-11-04 17:08:12 发布

ljj987123

最新推荐文章于 2022-11-04 17:08:12 发布

阅读量296

点赞数

student表

student表

course表

course表

score表

score表

teacher表

teacher表

#建学生信息表student
create table student
(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
#建立教师表
create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
#建立课程表course
create table course
(
cno varchar(20) not null ,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
#建立成绩表
create table score
(
sno varchar(20) not null,,
foreign key(sno) references student(sno),
cno varchar(20) not null,
foreign key(cno) references course(cno),
degree decimal
);
#添加学生信息
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','1974-06-03','95031');
#添加教师表
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
#添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
#添加成绩表
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','3-105','64');
insert into score values('105','3-105','91');
insert into score values('109','3-105','78');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');

1、查询Student表中的所有记录的Sname、Ssex和Class列。

select Sname,Ssex,Class from Student;

2、查询教师所有的单位即不重复的Depart列。

select distinct Depart from Teacher

3、查询Student表的所有记录。

select * from Student

4、查询Score表中成绩在60到80之间的所有记录。

select *from Score where Degree between 60 and 80;

5、查询Score表中成绩为85，86或88的记录。

select *from Score where Degree in(85,86,88);

6、查询Student表中“95031”班或性别为“女”的同学记录。

select *from Student where Class=95031 or Ssex='女';

7、以Class降序查询Student表的所有记录。

select *from Student order by Class desc;

8、以Cno升序、Degree降序查询Score表的所有记录。

select * from Score order by Cno,Degree desc;

9、查询“95031”班的学生人数。

select COUNT(*) from Student where Class=95031;

10、查询Score表中的最高分的学生学号和课程号。

select Sno,Cno from Score where Degree=(select MAX(Degree) from Score);

11、查询每门课的平均成绩。

 SELECT cno,AVG(degree) FROM score GROUP BY cno;

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT cno,AVG(degree) FROM score GROUP BY cno HAVING COUNT(cno) > 5 AND
cno LIKE '3%';

13、查询分数大于70，小于90的Sno列。

select Sno from Score where Degree >70 and Degree <90--分数大于70，小于90的Sno列

14、查询所有学生的Sname、Cno和Degree列。

select Sname, Cno, Degree from Student,Score where Student.Sno = Score.Sno;

15、查询所有学生的Sno、Cname和Degree列。

16、查询所有学生的Sname、Cname和Degree列。

select Sname,Cname,Degree from student join Score on Student.Sno=Score.Sno join Course on Score.Cno=Course.Cno

17、查询“95031”班学生的平均分。

select AVG(degree)from Student join Score on Student.Sno=Score.Sno and Class='95031'

18、假设使用如下命令建立了一个grade表：

create table grade(low int(3),upp int(3),rank char(1))
insert into grade values(90,100,’A’)
insert into grade values(80,89,’B’)
insert into grade values(70,79,’C’)
insert into grade values(60,69,’D’)
insert into grade values(0,59,’E’)

现查询所有同学的Sno、Cno和rank列。

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select*from Score where Cno='3-105'and degree>(select degree from score where Sno='109' and Cno='3-105')

20、查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

21、查询“张旭“教师任课的学生成绩。

select degree from Score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname = '张旭'))
select degree from Score join Course on Score.Cno=Course.Cno join Teacher on Course.Tno=Teacher.Tno where Tname = '张旭'

22、查询选修某课程的同学人数多于5人的教师姓名。

select Tname from Teacher where Tno=(select Tno from Course where Cno=(select Cno from Score group by Cno having COUNT(Cno)>=5))

23、查询95033班和95031班全体学生的记录。

select*from student inner join Score on Student.Sno=Score.Sno where Class in(95033,95031)
select *from Student,Score where Class in(95033,95031) and Student.Sno=Score.Sno

24、查询存在有85分以上成绩的课程Cno.

select distinct Cno from Score where degree>=85;--去重

25、查询出“计算机系“教师所教课程的成绩表。

select *from Score where Cno in( select Cno from Course where Tno in (select Tno from Teacher where Depart='计算机系'))
select Sno,Score.Cno,Degree from Score join Course on Score.Cno=Course.Cno join Teacher on Course.Tno=Teacher.Tno where Depart='计算机系'

26、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select Tname,Prof from Teacher where Prof not in
(select prof from Teacher where Depart='电子工程系' and Prof in (select Prof from Teacher where Depart='计算机系'))
and Depart in ('计算机系','电子工程系')
--查询两个系中教师相同职称名称，不在这里面的就是除去两个系都有的剩下的，见上面
select prof from Teacher where Depart='电子工程系' and Prof in (select Prof from Teacher where Depart='计算机系')

27、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

select Cno,Sno,Degree from Score a where (select Degree from Score b where Cno='3-105' and b.Sno=a.Sno)>=(select Degree from Score c where Cno='3-245' and c.Sno=a.Sno) order by Degree desc

28、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

select Cno,Sno,Degree from Score a where (select Degree from Score b where Cno='3-105' and b.Sno=a.Sno)>(select Degree from Score c where Cno='3-245' and c.Sno=a.Sno)

29、查询所有教师和同学的name、sex和birthday.--表连接，union（纵向连接，表1和表2数据类型要对应且列数对应）上下链接，与join on左右相关链接不同

select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student
union
select distinct Tname as name,Tsex as sex,Tbirthady as birthday from Teacher

30.查询所有“女”教师和“女”同学的name、sex和birthday.

select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student where Ssex='女'
union
select distinct Tname as name,Tsex as sex,Tbirthady as birthday from Teacher where Tsex='女'

31.查询成绩比该课程平均成绩低的同学的成绩表。--相关子查询--同一门学科的平均分,，每门学科低于自身平均分的

select Sno,Cno,Degree from Score a where a.Degree<(select AVG(Degree) from Score b where a.Cno=b.Cno)

32.查询所有任课教师的Tname和Depart.

select Tname,Depart from Teacher where Tname in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno)
select Tname,Depart from Teacher where tno in (select tno from course where Cno in (select distinct Cno from Score))

33.查询所有未讲课的教师的Tname和Depart.

select Tname,Depart from Teacher where Tname not in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno)

34.查询至少有2名男生的班号。

select Class FROM student where Ssex='男' group by Class having COUNT(*)>1

35.查询Student表中不姓“王”的同学记录。

select * from student where Sname not like ('王%')

36、查询Student表中每个学生的姓名和年龄。

select Sname,YEAR(GETDATE())-year(Sbirthday) from student

37、查询Student表中最大和最小的Sbirthday日期值。

select MAX(Sbirthday) as 最大,MIN(Sbirthday) as 最小 from student

38、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select * from student order by Class desc,Sbirthday asc

39、查询“男”教师及其所上的课程。

select Tname,Cname from Teacher,Course where Tsex='男' and Teacher.Tno=Course.Tno

40、查询最高分同学的Sno、Cno和Degree列。

select Sno,Cno,Degree from Score where degree=(select MAX(Degree)from Score)

41、查询和“李军”同性别的所有同学的Sname.

select Sname from student where Ssex=(select Ssex from student where Sname='李军') and Sname not in ('李军')

42、查询和“李军”同性别并同班的同学Sname.

select Sname from student where Ssex=(select Ssex from student where Sname='李军') and Sname not in ('李军') and Class=(select Class from student where Sname='李军')

表名和字段

--建表
--学生表
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);

练习题和sql语句

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select c.*,a.s_score as 01课程score,b.s_score as 02课程score from score a,score b
   left join student c on b.s_id = c.s_id
       where a.s_id = b.s_id and a.c_id = '01' and b.c_id = '02' and a.s_score > b.s_score;

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.* ,b.s_score as 01课程,c.s_score as 02课程 from student a
   join score b on a.s_id=b.s_id and b.c_id = '01'
   left join score c on b.s_id = c.s_id and c.c_id = '02'
   where b.s_score < c.s_score ;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id,a.s_name,round(avg(b.s_score),2) as 平均成绩 from student a
join score b on a.s_id = b.s_id group by b.s_id having 平均成绩 >= 60;

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select b.*,round(avg(a.s_score),2) as 平均成绩 from
student b
left join score a on b.s_id = a.s_id group by a.s_id having 平均成绩 < 60
union
select b.*,0 as 平衡成绩 from student b where b.s_id not in (select s_id from score);

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as 选课总数 ,sum(b.s_score) as 总分 from student a
left join score b on a.s_id = b.s_id group by s_id ;

-- 6、查询"李"姓老师的数量
select count(*) as 李姓老师数量 from teacher where t_name like '李%';

-- 7、查询学过"张三"老师授课的同学的信息
select a.* from student a join score b on a.s_id = b.s_id where b.c_id in (select c.c_id from course c
join teacher d on c.t_id = d.t_id where d.t_name = '张三');

-- 8、查询没学过"张三"老师授课的同学的信息
select a.* from student a left join score b on a.s_id = b.s_id where a.s_id not in
   (select s_id from score where c_id =
       (select c_id from course where t_id =
           (select t_id from teacher where t_name = '张
               三'))) group by a.s_id;

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select a.s_id from score a join score b on a.s_id = b.s_id
where a.c_id = '01' and b.c_id = '02');

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select s_id from score where c_id = '01' )
and s_id not in (select s_id from score where c_id = '02' );

-- 11、查询没有学全所有课程的同学的信息
select * from student where s_id not in
(select s_id from score group by s_id having count(c_id) = 3);

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct a.* from student a left join score b on a.s_id = b.s_id where b.c_id in
(select c_id from score where s_id = '01') and a.s_id != '01' ;

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student where s_id in
   (select s_id from score group by s_id having count(c_id) =
       (select count(c_id) from score where s_id = '01') and s_id not in
           (select s_id from score where c_id not in
               (select c_id from score where s_id = '01')) and s_id != '01');

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name from student where s_id not in
   (select s_id from score where c_id in
       (select c_id from course where t_id in
           (select t_id from teacher where t_name ='张三')));

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id
where s_score < 60 group by s_id having count(1) >=2;

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.* ,b.c_id ,b.s_score from student a
   left join score b on a.s_id = b.s_id
       where b.c_id = '01' and b.s_score < 60
           order by b.s_score desc;

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_name ,
sum(case when b.c_id = '01' then s_score else null end ) as 语文,
sum(case when b.c_id = '02' then s_score else null end ) as 数学,
sum(case when b.c_id = '03' then s_score else null end ) as 英语,
round(avg(s_score),2) as 平均成绩
from student a left join score b on a.s_id = b.s_id group by a.s_name
order by 平均成绩 desc;

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select b.c_id,b.c_name,
max(a.s_score) as 最高分,
min(a.s_score) as 最低分,
round(avg(a.s_score),2) as 平均分,
round(sum(case when a.s_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,
round(sum(case when a.s_score>= 70 and a.s_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,
round(sum(case when a.s_score>= 80 and a.s_score <90 then 1 else 0 end)/count(s_id),2) as 优良率,
round(sum(case when a.s_score>= 90 then 1 else 0 end)/count(s_id),2) as 优秀率
from score a left join course b on a.c_id = b.c_id group by b.c_id;

-- 19、按各科成绩进行排序，并显示排名
set @pre_c_id:= '01';
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
   (select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
       (case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
           from
               (select * from score order by c_id,s_score desc) tb1 )tb2;

-- 20、查询学生的总成绩并进行排名
set @rank:=0;
   select * ,(@rank:=@rank+1) as rank from
       (select s_id ,sum(s_score) as 总成绩 from score
           group by s_id order by 总成绩 desc) tb1;

-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.c_id, d.t_name,round(avg(a.s_score)) as 平均分 from score a
   left join student b on a.s_id = b.s_id
   left join course c on a.c_id = c.c_id
   left join teacher d on c.t_id = d.t_id group by a.c_id
   order by 平均分 desc;

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
set @pre_c_id:= '01';
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
   (select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
       (case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
           from
               (select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 = 2 or 排名 =3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],(85-70],(70-60],(0-60]及所占百分比

select b.c_id,b.c_name ,
sum(case when a.s_score >=85 then 1 else 0 end) as `100-85`,
concat(round(100*sum(case when a.s_score >=85 then 1 else 0 end)/count(*),2), '%') as 百分比,
sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end) as `85-70`,
concat(round(100*sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end)/count(*),2),'%') as 百分比,
sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end) as `70-60`,
concat(round(100*sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end)/count(*),2) ,'%')as 百分比,
sum(case when a.s_score <60 and a.s_score >=0 then 1 else 0 end) as `60-0`,
concat(round(100*sum(case when a.s_score <60 and a.s_score >=0 then 1
else 0 end)/count(*),2),'%') as 百分比
from score a left join course b on a.c_id = b.c_id group by b.c_id;

-- 24、查询学生平均成绩及其名次
select tb1.*,(@rank:=@rank +1 ) as rank from
(select s_id ,round(avg(s_score),2) as 平均成绩 from score
group by s_id order by 平均成绩 desc) tb1,(select @rank:=0) b;

-- 25、查询各科成绩前三名的记录
set @pre_c_id:= '01';
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 <4;

-- 26、查询每门课程被选修的学生数
select c_id ,count(s_id) as 选修人数 from score group by c_id;

-- 27、查询出只有两门课程的全部学生的学号和姓名
select a.s_id ,b.s_name from score a left join student b on a.s_id = b.s_id group by s_id having count(*) = 2;

-- 28、查询男生、女生人数
select sum(case s_sex when '男' then 1 else 0 end) as 男生人数,
sum(case s_sex when '女' then 1 else 0 end) as 女生人数 from student;

-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like '%风%';

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

--略，不想写

-- 31、查询1990年出生的学生名单
select * from student where s_birth like '1990%';

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id ,round(avg(s_score),2) as 平均成绩 from score group by c_id order by 平均成绩 desc, c_id asc;

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name ,round(avg(s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id group by a.s_id having 平均成绩>=85;

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select b.s_name ,a.s_score from score a
left join student b on a.s_id = b.s_id
where a.c_id=(select c_id from course where c_name = '数学')and a.s_score < 60;

-- 35、查询所有学生的课程及分数情况；
select b.s_name,
sum(case when a.c_id = '01' then a.s_score else null end) as 语文,
sum(case when a.c_id = '02' then a.s_score else null end) as 数学,
sum(case when a.c_id = '03' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select b.s_name,
sum(case when a.c_id = '01' then a.s_score else null end) as 语文,
sum(case when a.c_id = '02' then a.s_score else null end) as 数学,
sum(case when a.c_id = '03' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name having 语文>= 70 or 数学>= 70 or 英语>= 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 c_id,count(*) as 学生人数 from score group by c_id ;

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,b.c_id,max(b.s_score) as 最高成绩 from student a
       right join score b on a.s_id = b.s_id
           group by b.c_id
                   having b.c_id = (select c_id from course
                           where t_id = (select t_id from teacher where t_name = '张三'));

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--（这题我搞不清题目是什么意思,是指查找学生个体参加了的所有课程的成绩各不相同的那个学生信息呢？还是所有课程之间做对比呢，我更倾向于理解为前者）

--理解为前者的写法
select * from
(select * from score group by s_id,s_score) tb1
group by s_id having count(*) = 1;

--理解为后者的写法
select distinct a.s_id,a.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、查询每门课程成绩最好的前两名
set @pre_c_id:= '01';
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score from
   (select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
       (case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
            from
                (select * from score order by c_id,s_score desc) tb1 )tb2
                    join student b on tb2.s_id = b.s_id where 排名 <3;

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人相同，按课程号升序排列
select c_id ,count(*) as 选修人数 from score group by c_id having 选修人数>5 order by 选修人数 desc , c_id asc;

-- 44、检索至少选修两门课程的学生学号
select s_id from score group by s_id having count(*) >= 2;

-- 45、查询选修了全部课程的学生信息
select * from student where s_id in
(select s_id from score group by s_id having count(*) = 3)

--46、查询各学生的年龄
select s_name ,(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、查询本周过生日的学生
---(实现得并不完全，因为例如出生月日为‘01-01’在每一年可能会输入不同周）
select * from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;

-- 48、查询下周过生日的学生
select * from student
where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));

-- 49、查询本月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(now(),'%m')

-- 50、查询下月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')

ljj987123

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mySQL

student表student表course表course表score表score表teacher表teacher表 #建学生信息表student create table student ( sno varchar(20) not null primary key, sname varchar(20) not null, ssex varchar(20) not null, sbirthday .
复制链接

扫一扫