50條sql查詢技巧、查詢語句示例
來源:易賢網 閱讀:4443 次 日期:2016-11-14 16:07:53
溫馨提示:易賢網小編為您整理了“50條sql查詢技巧、查詢語句示例”,方便廣大網友查閱!
這篇文章主要介紹了50條sql查詢技巧、查詢語句示例,本文以學生表、課程表、成績表、教師表為例,講解不同需求下的sql語句寫法,需要的朋友可以參考下
 

student(s#,sname,sage,ssex) 學生表
course(c#,cname,t#) 課程表
sc(s#,c#,score) 成績表
teacher(t#,tname) 教師表
 
問題:
1、查詢“001”課程比“002”課程成績高的所有學生的學號;

代碼如下:

select a.s# from (select s#,score from sc where c#='001') a,(select s#,score
from sc where c#='002') b
where a.score>b.score and a.s#=b.s#;

2、查詢平均成績大于60分的同學的學號和平均成績;
代碼如下:

select s#,avg(score)
from sc
group by s# having avg(score) >60;

3、查詢所有同學的學號、姓名、選課數(shù)、總成績;
代碼如下:

select student.s#,student.sname,count(sc.c#),sum(score)
from student left outer join sc on student.s#=sc.s#
group by student.s#,sname

4、查詢姓“李”的老師的個數(shù);
代碼如下:

select count(distinct(tname))
from teacher
where tname like '李%';

5、查詢沒學過“葉平”老師課的同學的學號、姓名;
代碼如下:

select student.s#,student.sname
from student
where s# not in (select distinct( sc.s#) from sc,course,teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='葉平');

6、查詢學過“001”并且也學過編號“002”課程的同學的學號、姓名;
代碼如下:

select student.s#,student.sname from student,sc where student.s#=sc.s# and sc.c#='001'and exists( select * from sc as sc_2 where sc_2.s#=sc.s# and sc_2.c#='002');

7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名;
代碼如下:

select s#,sname
from student
where s# in (select s# from sc ,course ,teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='葉平' group by s# having count(sc.c#)=(select count(c#) from course,teacher where teacher.t#=course.t# and tname='葉平'));

8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名;
代碼如下:

select s#,sname from (select student.s#,student.sname,score ,(select score from sc sc_2 where sc_2.s#=student.s# and sc_2.c#='002') score2
from student,sc where student.s#=sc.s# and c#='001') s_2 where score2 <score;

9、查詢所有課程成績小于60分的同學的學號、姓名;
代碼如下:

select s#,sname
from student
where s# not in (select student.s# from student,sc where s.s#=sc.s# and score>60);

10、查詢沒有學全所有課的同學的學號、姓名;
代碼如下:

select student.s#,student.sname
from student,sc
where student.s#=sc.s# group by student.s#,student.sname having count(c#) <(select count(c#) from course);

11、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名;
代碼如下:

select s#,sname from student,sc where student.s#=sc.s# and c# in select c# from sc where s#='1001';

12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名;
代碼如下:

select distinct sc.s#,sname
from student,sc
where student.s#=sc.s# and c# in (select c# from sc where s#='001');

13、把“sc”表中“葉平”老師教的課的成績都更改為此課程的平均成績;
代碼如下:

update sc set score=(select avg(sc_2.score)
from sc sc_2
where sc_2.c#=sc.c# ) from course,teacher where course.c#=sc.c# and course.t#=teacher.t# and teacher.tname='葉平');

14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名;
代碼如下:

select s# from sc where c# in (select c# from sc where s#='1002')
group by s# having count(*)=(select count(*) from sc where s#='1002');

15、刪除學習“葉平”老師課的sc表記錄;
代碼如下:

delect sc
from course ,teacher
where course.c#=sc.c# and course.t#= teacher.t# and tname='葉平';

16、向sc表中插入一些記錄,這些記錄要求符合以下條件:沒有上過編號“003”課程的同學學號、2、
號課的平均成績;
代碼如下:

insert sc select s#,'002',(select avg(score)
from sc where c#='002') from student where s# not in (select s# from sc where c#='002');

17、按平均成績從高到低顯示所有學生的“數(shù)據(jù)庫”、“企業(yè)管理”、“英語”三門的課程成績,按如下形式顯示: 學生id,,數(shù)據(jù)庫,企業(yè)管理,英語,有效課程數(shù),有效平均分
代碼如下:

select s# as 學生id
,(select score from sc where sc.s#=t.s# and c#='004') as 數(shù)據(jù)庫
,(select score from sc where sc.s#=t.s# and c#='001') as 企業(yè)管理
,(select score from sc where sc.s#=t.s# and c#='006') as 英語
,count(*) as 有效課程數(shù), avg(t.score) as 平均成績
from sc as t
group by s#
order by avg(t.score)

18、查詢各科成績最高和最低的分:以如下形式顯示:課程id,最高分,最低分
代碼如下:

select l.c# as 課程id,l.score as 最高分,r.score as 最低分
from sc l ,sc as r
where l.c# = r.c# and
l.score = (select max(il.score)
from sc as il,student as im
where l.c# = il.c# and im.s#=il.s#
group by il.c#)
and
r.score = (select min(ir.score)
from sc as ir
where r.c# = ir.c#
group by ir.c#
);

19、按各科平均成績從低到高和及格率的百分數(shù)從高到低順序
代碼如下:

select t.c# as 課程號,max(course.cname)as 課程名,isnull(avg(score),0) as 平均成績
,100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) as 及格百分數(shù)
from sc t,course
where t.c#=course.c#
group by t.c#
order by 100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) desc

20、查詢如下課程平均成績和及格率的百分數(shù)(用1行顯示): 企業(yè)管理(001),馬克思(002),oo¨ (003),數(shù)據(jù)庫(004)
代碼如下:

select sum(case when c# ='001' then score else 0 end)/sum(case c# when '001' then 1 else 0 end) as 企業(yè)管理平均分
,100 * sum(case when c# = '001' and score >= 60 then 1 else 0 end)/sum(case when c# = '001' then 1 else 0 end) as 企業(yè)管理及格百分數(shù)
,sum(case when c# = '002' then score else 0 end)/sum(case c# when '002' then 1 else 0 end) as 馬克思平均分
,100 * sum(case when c# = '002' and score >= 60 then 1 else 0 end)/sum(case when c# = '002' then 1 else 0 end) as 馬克思及格百分數(shù)
,sum(case when c# = '003' then score else 0 end)/sum(case c# when '003' then 1 else 0 end) as uml平均分
,100 * sum(case when c# = '003' and score >= 60 then 1 else 0 end)/sum(case when c# = '003' then 1 else 0 end) as uml及格百分數(shù)
,sum(case when c# = '004' then score else 0 end)/sum(case c# when '004' then 1 else 0 end) as 數(shù)據(jù)庫平均分
,100 * sum(case when c# = '004' and score >= 60 then 1 else 0 end)/sum(case when c# = '004' then 1 else 0 end) as 數(shù)據(jù)庫及格百分數(shù)
from sc

21、查詢不同老師所教不同課程平均分從高到低顯示
代碼如下:

select max(z.t#) as 教師id,max(z.tname) as 教師姓名,c.c# as 課程id,max(c.cname) as 課程名稱,avg(score) as 平均成績
from sc as t,course as c ,teacher as z
where t.c#=c.c# and c.t#=z.t#
group by c.c#
order by avg(score) desc

 

22、查詢如下課程成績第 3 名到第 6 名的學生成績單:企業(yè)管理(001),馬克思(002),uml (003),數(shù)據(jù)庫(004)
[學生id],[學生姓名],企業(yè)管理,馬克思,uml,數(shù)據(jù)庫,平均成績

代碼如下:

select distinct top 3
sc.s# as 學生學號,
student.sname as 學生姓名 ,
t1.score as 企業(yè)管理,
t2.score as 馬克思,
t3.score as uml,
t4.score as 數(shù)據(jù)庫,
isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0) as 總分
from student,sc left join sc as t1
on sc.s# = t1.s# and t1.c# = '001'
left join sc as t2
on sc.s# = t2.s# and t2.c# = '002'
left join sc as t3
on sc.s# = t3.s# and t3.c# = '003'
left join sc as t4
on sc.s# = t4.s# and t4.c# = '004'
where student.s#=sc.s# and
isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0)
not in
(select
distinct
top 15 with ties
isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0)
from sc
left join sc as t1
on sc.s# = t1.s# and t1.c# = 'k1'
left join sc as t2
on sc.s# = t2.s# and t2.c# = 'k2'
left join sc as t3
on sc.s# = t3.s# and t3.c# = 'k3'
left join sc as t4
on sc.s# = t4.s# and t4.c# = 'k4'
order by isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0) desc);

23、統(tǒng)計列印各科成績,各分數(shù)段人數(shù):課程id,課程名稱,[100-85],[85-70],[70-60],[ <60]
代碼如下:

select sc.c# as 課程id, cname as 課程名稱
,sum(case when score between 85 and 100 then 1 else 0 end) as [100 - 85]
,sum(case when score between 70 and 85 then 1 else 0 end) as [85 - 70]
,sum(case when score between 60 and 70 then 1 else 0 end) as [70 - 60]
,sum(case when score < 60 then 1 else 0 end) as [60 -]
from sc,course
where sc.c#=course.c#
group by sc.c#,cname;

24、查詢學生平均成績及其名次
代碼如下:

select 1+(select count( distinct 平均成績)
from (select s#,avg(score) as 平均成績
from sc
group by s#
) as t1
where 平均成績 > t2.平均成績) as 名次,
s# as 學生學號,平均成績
from (select s#,avg(score) 平均成績
from sc
group by s#
) as t2
order by 平均成績 desc;

 

25、查詢各科成績前三名的記錄:(不考慮成績并列情況)

代碼如下:

select t1.s# as 學生id,t1.c# as 課程id,score as 分數(shù)
from sc t1
where score in (select top 3 score
from sc
where t1.c#= c#
order by score desc
)
order by t1.c#;

26、查詢每門課程被選修的學生數(shù)
代碼如下:

select c#,count(s#) from sc group by c#;

27、查詢出只選修了一門課程的全部學生的學號和姓名
代碼如下:

select sc.s#,student.sname,count(c#) as 選課數(shù)
from sc ,student
where sc.s#=student.s# group by sc.s# ,student.sname having count(c#)=1;

28、查詢男生、女生人數(shù)
代碼如下:

select count(ssex) as 男生人數(shù) from student group by ssex having ssex='男';
select count(ssex) as 女生人數(shù) from student group by ssex having ssex='女';

29、查詢姓“張”的學生名單
代碼如下:

select sname from student where sname like '張%';

30、查詢同名同性學生名單,并統(tǒng)計同名人數(shù)
代碼如下:

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

 

 


31、1981年出生的學生名單(注:student表中sage列的類型是datetime)

 

 

代碼如下:

select sname, convert(char (11),datepart(year,sage)) as age
from student
where convert(char(11),datepart(year,sage))='1981';

32、查詢每門課程的平均成績,結果按平均成績升序排列,平均成績相同時,按課程號降序排列
代碼如下:

select c#,avg(score) from sc group by c# order by avg(score),c# desc ;

33、查詢平均成績大于85的所有學生的學號、姓名和平均成績
代碼如下:

select sname,sc.s# ,avg(score)
from student,sc
where student.s#=sc.s# group by sc.s#,sname having avg(score)>85;

34、查詢課程名稱為“數(shù)據(jù)庫”,且分數(shù)低于60的學生姓名和分數(shù)
代碼如下:

select sname,isnull(score,0)
from student,sc,course
where sc.s#=student.s# and sc.c#=course.c# and course.cname='數(shù)據(jù)庫'and score <60;

35、查詢所有學生的選課情況;
代碼如下:

select sc.s#,sc.c#,sname,cname
from sc,student,course
where sc.s#=student.s# and sc.c#=course.c# ;

36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數(shù);
代碼如下:

select distinct student.s#,student.sname,sc.c#,sc.score
from student,sc
where sc.score>=70 and sc.s#=student.s#;

37、查詢不及格的課程,并按課程號從大到小排列
代碼如下:

select c# from sc where scor e <60 order by c# ;

38、查詢課程編號為003且課程成績在80分以上的學生的學號和姓名;
代碼如下:

select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#='003';

39、求選了課程的學生人數(shù)
代碼如下:

select count(*) from sc;

40、查詢選修“葉平”老師所授課程的學生中,成績最高的學生姓名及其成績
代碼如下:

select student.sname,score
from student,sc,course c,teacher
where student.s#=sc.s# and sc.c#=c.c# and c.t#=teacher.t# and teacher.tname='葉平' and sc.score=(select max(score)from sc where c#=c.c# );

41、查詢各個課程及相應的選修人數(shù)
代碼如下:

select count(*) from sc group by c#;

42、查詢不同課程成績相同的學生的學號、課程號、學生成績
代碼如下:

select distinct a.s#,b.score from sc a ,sc b where a.score=b.score and a.c# <>b.c# ;

43、查詢每門功成績最好的前兩名
代碼如下:

select t1.s# as 學生id,t1.c# as 課程id,score as 分數(shù)
from sc t1
where score in (select top 2 score
from sc
where t1.c#= c#
order by score desc
)
order by t1.c#;

44、統(tǒng)計每門課程的學生選修人數(shù)(超過10人的課程才統(tǒng)計)。要求輸出課程號和選修人數(shù),查詢結果按人數(shù)降序排列,查詢結果按人數(shù)降序排列,若人數(shù)相同,按課程號升序排列
代碼如下:

select c# as 課程號,count(*) as 人數(shù)
from sc
group by c#
order by count(*) desc,c#

45、檢索至少選修兩門課程的學生學號
代碼如下:

select s#
from sc
group by s#
having count(*) > = 2

46、查詢全部學生都選修的課程的課程號和課程名
代碼如下:

select c#,cname
from course
where c# in (select c# from sc group by c#)

47、查詢沒學過“葉平”老師講授的任一門課程的學生姓名
代碼如下:

select sname from student where s# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c# and tname='葉平');

48、查詢兩門以上不及格課程的同學的學號及其平均成績
代碼如下:

select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score <60 group by s# having count(*)>2)group by s#;

49、檢索“004”課程分數(shù)小于60,按分數(shù)降序排列的同學學號
代碼如下:

select s# from sc where c#='004'and score <60 order by score desc;

50、刪除“002”同學的“001”課程的成績
代碼如下:

delete from sc where s#='001'and c#='001';

 

更多信息請查看技術文章
易賢網手機網站地址:50條sql查詢技巧、查詢語句示例

2025國考·省考課程試聽報名

  • 報班類型
  • 姓名
  • 手機號
  • 驗證碼
關于我們 | 聯(lián)系我們 | 人才招聘 | 網站聲明 | 網站幫助 | 非正式的簡要咨詢 | 簡要咨詢須知 | 加入群交流 | 手機站點 | 投訴建議
工業(yè)和信息化部備案號:滇ICP備2023014141號-1 云南省教育廳備案號:云教ICP備0901021 滇公網安備53010202001879號 人力資源服務許可證:(云)人服證字(2023)第0102001523號
聯(lián)系電話:0871-65099533/13759567129 獲取招聘考試信息及咨詢關注公眾號:hfpxwx
咨詢QQ:526150442(9:00—18:00)版權所有:易賢網