日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學(xué)無先后，達(dá)者為師

網(wǎng)站首頁 編程語言 正文

教你使用SQL語句進(jìn)行數(shù)據(jù)庫復(fù)雜查詢_MsSql

作者：初階牛 ? 更新時間： 2023-02-14 編程語言

前言

本篇可當(dāng)做例題練習(xí),

1.查詢比”林紅”年紀(jì)大的男學(xué)生信息
語句:

select *
from Student
where Sex='男' and 
	year(Birth)-(select year(Birth)from Student--這里是需要告訴查詢的表名,相當(dāng)于嵌套
	where Sname='林紅')<0

2.檢索所有學(xué)生的選課信息,包括學(xué)號、姓名、課程名、成績,性別.
語句:

select sc.sno,sname, course.Cno,Cname,Grade,Sex
--這里如果兩個表中都有同一個屬性,則需要標(biāo)明在哪個表,如sc.sno
from student,sc,Course
where student.Sno=sc.Sno and Sc.Cno=course.Cno

3.查詢已經(jīng)選課的學(xué)生的學(xué)號、姓名、課程名、成績.
語句:

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

(4)查詢選修了“C語言程序設(shè)計”的學(xué)生的學(xué)號與姓名
–a.用內(nèi)連接查詢
語句:

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語言程序設(shè)計'

–b.用連接查詢
語句:

select sc.Sno,sname from student,sc,course where
student .Sno=sc.Sno and sc.Cno =course.cno
and Cname='C語言程序設(shè)計'

–c.用子查詢
語句:

select Sno,sname from student where Sno in
(select Sno from sc where Cno=
(select cno from course where Cname ='C語言程序設(shè)計'))

(5)查詢與”張虹”在同一個班級的學(xué)生學(xué)號、姓名、家庭住址
–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”班所有學(xué)生年齡大的學(xué)生的學(xué)號、姓名
代碼1:

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

代碼2:

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

(7）(選作)查詢選修了全部課程的學(xué)生姓名。本題使用除運算的方法。
–由題意可得另一種語言，沒有一個選了課的學(xué)生沒有選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）查詢至少選修了學(xué)生“20110002”選修的全部課程的學(xué)生的學(xué)號，姓名。
語句:

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）檢索選修了“高數(shù)”課且成績至少高于選修課程號為“002"課程的學(xué)生的學(xué)號、課程號、成績，并按成績從高到低排列。
語句:

select sc.Sno, sc.cno , grade from sc where
grade >all(select grade from sc where cno='002' ) and
Cno= (select Cno
from course where Cname='高數(shù)')
order by Grade desc

(10)檢索選修了至少3門以上課程的學(xué)生的學(xué)號、總成績(不統(tǒng)計不及格的成績)，并要求按總成績降序排列。
語句:

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
order by SUM (grade) desc

(12）檢索多于3名學(xué)生選修的并以3結(jié)尾的課程號的平均成績。
語句:

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

(13）檢索最高分與最低分之差大于5分的學(xué)生的學(xué)號、姓名、最高分、最底分。

select distinct sc.sno 學(xué)號,sname 姓名,
max (grade) as最高分,min (grade) as最低分
from student,sc
where sc.sno=student.Sno group by sc.sno , Sname
having max(grade) -min (grade) >5

(14)創(chuàng)建一個表Student_other，結(jié)構(gòu)同student,輸入若干記錄，部分記錄和student表中的相同。
–創(chuàng)建過程:

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表中沒有的數(shù)據(jù):

在這里插入圖片描述

–a.查詢同時出現(xiàn)在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

原文鏈接：https://blog.csdn.net/qq_67276605/article/details/128269260

欄目分類
最近更新