前言
本篇可当做例题练习,
1.查询比”林红”年纪大的男学生信息
语句:
1 2 3 4 5 | select *from Studentwhere Sex='男' and year(Birth)-(select year(Birth)from Student--这里是需要告诉查询的表名,相当于嵌套 where Sname='林红') |
2.检索所有学生的选课信息,包括学号、姓名、课程名、成绩,性别.
语句:
1 2 3 4 | select sc.sno,sname, course.Cno,Cname,Grade,Sex--这里如果两个表中都有同一个属性,则需要标明在哪个表,如sc.snofrom student,sc,Coursewhere student.Sno=sc.Sno and Sc.Cno=course.Cno |
3.查询已经选课的学生的学号、姓名、课程名、成绩.
语句:
1 2 3 | select sc.sno ,sname , Cname , Gradefrom student s , course c, scwhere s.sno=sc.sno and c.cno=sc.cno |
(4)查询选修了“C语言程序设计”的学生的学号与姓名
–a.用内连接查询
语句:
1 2 3 | select sc.Sno,sname from student inner join sc onstudent.Sno=sc.Sno inner join course on sc.Cno =course.cnoand Cname='C语言程序设计' |
–b.用连接查询
语句:
1 2 3 | select sc.Sno,sname from student,sc,course wherestudent .Sno=sc.Sno and sc.Cno =course.cnoand Cname='C语言程序设计' |
–c.用子查询
语句:
1 2 3 | select Sno,sname from student where Sno in(select Sno from sc where Cno=(select cno from course where Cname ='C语言程序设计')) |
(5)查询与”张虹”在同一个班级的学生学号、姓名、家庭住址
–a．用连接查询
语句:
1 2 | 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．用子查询
语句:
1 2 3 | select Sno,sname,Home_addr from student whereclassno=(select classno from student where sname='张虹')and sname!='张虹' |
(6)查询其他班级中比”051”班所有学生年龄大的学生的学号、姓名
代码1:
1 2 | select Sno,sname,Home_addr from student whereclassno!='051' and Birth |
代码2:
1 2 | select Sno,sname,Home_addr from student whereclassno!='051' and Birth |
(7）(选作)查询选修了全部课程的学生姓名。本题使用除运算的方法。
–由题意可得另一种语言，没有一个选了课的学生没有选course表里的课程。那么，我们需要两个NOT EXISTS表示双重否定;
语句:
1 2 3 4 5 6 7 | select Sname from studentwhere not exists (select * from coursewhere not exists (select * from scwhere sno=student. snoand cno=Course.cno)) |
(8）查询至少选修了学生“20110002”选修的全部课程的学生的学号，姓名。
语句:
1 2 3 4 5 6 7 8 9 | select Sno, Sname from studentwhere sno in (select distinct sno from sc as sc1where not exists (select * from sc as sc2 where sc2.sno='20110002'and not exists (select * from sc as sc3 where sc3.Sno=sc1.sno andsc3.cno=sC2.cno) )) |
(9）检索选修了“高数”课且成绩至少高于选修课程号为“002″课程的学生的学号、课程号、成绩，并按成绩从高到低排列。
语句:
1 2 3 4 5 | select sc.Sno, sc.cno , grade from sc wheregrade >all(select grade from sc where cno='002' ) andCno= (select Cnofrom course where Cname='高数')order by Grade desc |
(10)检索选修了至少3门以上课程的学生的学号、总成绩(不统计不及格的成绩)，并要求按总成绩降序排列。
语句:
1 2 3 | select sno,SUM(grade) from sc where sno in (select Sno from sc group by snohaving COUNT(*)>=3) and Grade>=60 group by snoorder by SUM (grade) desc |
(12）检索多于3名学生选修的并以3结尾的课程号的平均成绩。
语句:
1 2 3 4 | select avg(Grade) as 平均成绩from scwhere Cno like '%3' group by cnohaving count (Cno)>3 |
(13）检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。
1 2 3 4 5 | select distinct sc.sno 学号,sname 姓名,max (grade) as最高分,min (grade) as最低分from student,scwhere sc.sno=student.Sno group by sc.sno , Snamehaving max(grade) -min (grade) >5 |
(14)创建一个表Student_other，结构同student,输入若干记录，部分记录和student表中的相同。
–创建过程:
1 2 3 4 5 6 7 8 9 10 11 | 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,Home_addr varchar (40) ,sdept char (2) not null,Postcode char (6)) |
随意插入几条student表中没有的数据:
–a.查询同时出现在Student表和student_other表中的记录
语句:
1 2 | select * from student__other so ,student swhere so.sno=s.sno |
—-b．查询Student表和Student_other表中的全部记录
代码:
1 2 3 | select * from studentunionselect * from student__other |
到此这篇关于教你使用SQL语句进行数据库复杂查询的文章就介绍到这了,更多相关sql复杂查询语句内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！
