• 欢迎光临~

# 前言

🎈个人主页:🎈 :✨✨✨初阶牛✨✨✨
🐻推荐专栏: 🍔🍟🌯 c语言初阶
🔑个人信条: 🌵知行合一
🍉本篇简介:>:上一篇学习了如何使用SQL语句进行简单的数据查询,本篇记录一些在简单查询的基础上稍微复杂一点的查询,希望对大家有帮助.

1.查询比”林红”年纪大的男学生信息

``````select *
from Student
where Sex='男' and
year(Birth)-(select year(Birth)from Student--这里是需要告诉查询的表名,相当于嵌套
where Sname='林红')<0
``````

1.检索所有学生的选课信息,包括学号、姓名、课程名、成绩,性别.

``````select sc.sno,sname, course.Cno,Cname,Grade,Sex
--这里如果两个表中都有同一个属性,则需要标明在哪个表,如sc.sno
from student,sc,Course
where student.Sno=sc.Sno and Sc.Cno=course.Cno
``````

3.查询已经选课的学生的学号、姓名、课程名、成绩.

``````select sc.sno ,sname , Cname , Grade
from student s , course c, sc
where s.sno=sc.sno and c.cno=sc.cno
``````

(4)查询选修了“C语言程序设计”的学生的学号与姓名
–a.用内连接查询

``````select sc.Sno,sname from student inner join sc on
student.Sno=sc.Sno inner join course on sc.Cno =course.cno
and Cname='C语言程序设计'
``````

–b.用连接查询

``````select sc.Sno,sname from student,sc,course where
student .Sno=sc.Sno and sc.Cno =course.cno
and Cname='C语言程序设计'
``````

–c.用子查询

``````select Sno,sname from student where Sno in
(select Sno from sc where Cno=
(select cno from course where Cname ='C语言程序设计'))
``````

(5)查询与”张虹”在同一个班级的学生学号、姓名、家庭住址
–a．用连接查询

``````select a.Sno,a.sname,a.Home_addr from student a,student b
where a.Classno =b.Classno and b.Sname ='张虹' and a.Sname!='张虹'
``````

–b．用子查询

``````select Sno,sname,Home_addr  from student where
classno=(select classno from student where sname='张虹')
and sname!='张虹'
``````

(6)查询其他班级中比”051”班所有学生年龄大的学生的学号、姓名

``````select Sno,sname,Home_addr  from student where
classno!='051' and Birth<all (select Birth  from student where classno='051')
``````

``````select Sno,sname,Home_addr  from student where
classno!='051' and Birth<(select min(Birth)  from student where classno='051')
``````

(7）(选作)查询选修了全部课程的学生姓名。本题使用除运算的方法。
–由题意可得另一种语言，没有一个选了课的学生没有选course表里的课程。那么，我们需要两个NOT EXISTS表示双重否定;

``````select Sname from student
where not exists (
select * from course
where not exists (
select * from sc
where sno=student. sno
and cno=Course.cno))
``````

(8）查询至少选修了学生“20110002”选修的全部课程的学生的学号，姓名。

``````select Sno, Sname from student
where sno in (
select distinct sno from sc as sc1
where not exists (
select * from sc as sc2 where sc2.sno='20110002'
and not exists (
select * from sc as sc3 where sc3.Sno=sc1.sno and
sc3.cno=sC2.cno) )
)
``````

(9）检索选修了“高数”课且成绩至少高于选修课程号为“002"课程的学生的学号、课程号、成绩，并按成绩从高到低排列。

``````select sc.Sno, sc.cno , grade from sc where
Cno= (select Cno
from course where Cname='高数')
``````

(10)检索选修了至少3门以上课程的学生的学号、总成绩(不统计不及格的成绩)，并要求按总成绩降序排列。

``````select sno,SUM(grade) from sc where sno in (select Sno from sc group by sno
having COUNT(*)>=3) and Grade>=60 group by sno
``````

(12）检索多于3名学生选修的并以3结尾的课程号的平均成绩。

``````select avg(Grade) as 平均成绩
from sc
where Cno like '%3' group by cno
having count (Cno)>3
``````

(13）检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。

``````select distinct sc.sno 学号,sname 姓名,
from student,sc
where sc.sno=student.Sno group by sc.sno , Sname
``````

(14)创建一个表Student_other，结构同student,输入若干记录，部分记录和student表中的相同。
–创建过程:

``````create table student__other (
Sno char (8) primary key,
Sname varchar (8) not null,
sex char(2) not null,
Birth smalldatetime not null,
Classno char (3) not null,
Entrance_date smalldatetime not null,
sdept char (2) not null,
Postcode char (6)
)
``````

–a.查询同时出现在Student表和student_other表中的记录

``````select * from student__other so ,student s
where so.sno=s.sno
``````

----b．查询Student表和Student_other表中的全部记录

``````select * from student
union
select * from student__other
``````